数据库（1）

sqlplus的基本操作：

显示当前用户：show  user;

查看当前用户下的表：select * from tab;

查看表结构：desc tablename

设置行宽：set col empno for 9999(9999表示4位数)    set col  job  for a10(a表示字符串，10表示宽度)数据库

设置页面显示的行数：set pagesize 100;                                set linesize 140；（一行显示的数）

永久设置行宽和页面显示的行数：D:\app\Administrator\product\11.2.0\client_1\sqlplus\admin

设置员工名列宽：col ename for a10

设置薪水列为4位数字：col sal for 9999

 

基本的SELECT语句

语法格式：SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; 

案例：

1.查询所有员工的所有记录 

select * from emp;

2.查询员工号，姓名，月薪，奖金，年薪

select empno,ename,sal,comm,sal*12 from emp;

结论： 

使用select *和select 列名 的区别 ：使用select * 会先查有哪些列，影响效率。

3.对案例2使用别名：

select empno as eno,ename as 姓名，sal 工资，comm“奖金”，sal*12 "年 薪" from emp;

结论：

as可以省略；如果别名中间有空格，需要使用“”引起来，建议别名为中文是全部使用“”将其引起来。 

4. 查询员工号，姓名，月薪，奖金，年薪，年收入

select empno,ename,sal,comm,sal*12 "年薪",sal*12+comm "年收入" from emp;

select empno,ename,sal,comm,sal*12 "年薪",sal*12+nvl(comm,0) "年收入" from emp;

结论：

1.包含null的表达式为空，如 sal*12+comm

2.nvl函数，nvl(a,b),若a为空，则取b的值。

5.查看员工表不同的部门编号

select deptno from emp;

select distinct deptno from emp;

结论：

使用distinct 去重复。

6.查看不同部门的不同工种

select  distinct depno from emp;

结论：

distinct的作用范围是后面所有的列

7.输出计算表达式3+20*5，显示当前日期

select 3+20*5,sysdate from dual;

注意：dual表是一个伪表，主要是为了满足sql的语法规定。

 

SQL和sqlplus

select update delete insert create    SQL语句关键字

ed c set col desc   sqlplus命令

SQL——>语言，关键字不能缩写，ANSI标准，适用语句控制数据库中的表的定义信息和表中的数据。

sqlplus——>oracle提供的工具，一种环境，可在里面执行SQL语句，它配有自己的命令，特点是缩写关键字，命令不能改变数据库中的数据的值，集中运行。

 

使用where进行过滤：

若在编写sql语句的时候，书写错误，则可以进行修改：

如 SQL> select * ffom

    SQL> emp;

    先输入：错误发生的那一行，然后输入c /ffom/from，最后输入/执行sql语句。

    SQL> 2

    SQL> c/ffom/from

    SQL> /

基本结构：select...from table where condition;

若编写的sql语句比较长，可以使用edit打开一个记事本，在记事本上编写

如：SQL>edit ,然后按/执行sql语句

 

保存查询结果：

spool名的使用：

    spool d:\results

    select * from emp;

    spool off

这样会将查询结果保存到文件中。

 

1.使用比较运算符：> >= < <= !=(<>) between and

案例：

    1.查询10号部门的员工信息

        select * from emp where deptno=10;

    2.查询员工名字为king的员工信息

        select *from emp where ename = 'KING';

       结论：表中的；列的值是区分大小写的；但是关键字不区分大小写

    3.查询薪水不等于1250的员工的信息

        select * from emp where sal!=1250;

        select * from emp where sal<>1250;

    4.查询入职日期为1981年11月17日的员工信息

        开始根据表里的格式进行查询:SQL> select * from emp where hiredate='17-11月-81'

        获取系统当前日期格式： SQL> select * from v$nls_parameters(数据字典，类似于tab)

        修改日期格式：SQL> alter session set NLS_DATE_FORMAT='yyyy-mm-dd'

        再次查询：SQL> select * from emp where hiredate='1981-11-17'

        显示带有时间的日期：SQL> alter session set NLS_DATE_FORMAT ='yyyy-mm-dd hh24:mi:ss'

        再次查看系统时间：SQL> select sysdate from dual

        改回系统默认格式：SQL> alter session set NLS_DATE_FORMAT = 'DD-MON-RR';

    5.查找工资介于1000-2000之间的员工信息

        select * from emp where sal>=1000 and sal<=2000;

        select * from emp where sal between 1000 and 2000;

        结论：between and是闭区间

 

2.在where条件中使用逻辑运算符：or and not

案例：

        1.查询10号部门或者20部门的员工信息

           select * from emp where deptno=10 or deptno=20;

        2.查询10号部门员工工资为1300的员工信息

           select * from emp where deptno=10 and sal=1300;

        3.查询81年2月（含2月）职82年2月（不含2月）入职的员工信息（大于等于81年2月1日，小于等于82年1月31日）

           说明：注意日期格式问题，注意月份单月不要在前面加0，否则会报错。

           select * from emp where hiredate>='1981-2-1' and hiredate<='1982-1-31';

           select * from emp where hiredate between'1981-2-1' and '1982-1-31';

 

           结论：关于and or 操作符的sql优化问题

           where条件在进行逻辑表达式计算的时候，是从右往左进行的，所以对于and来说，要把容易出现假的表达式放在最右边，对于or来说，要把容易出现真的表达式放在最右边。

           where a and b and c;

           where a or b or c or d;

 

        4.查询奖金为空的员工信息-null

           select * from emp where comm = null;

           select * from emp where comm is null;

        5.查询奖金不为空的员工信息

           select * from emp where comm is not null;

           结论:用is null表示为空，用is not null表示不为空

 

         分析下面的sql语句：

         select * from emp where deptno=10 or deptno=30 and sal=1250;

         在有or和and的where条件语句中，and的优先级比or的高

         所以表示部门为10或30且sal为1250的应该这样写

         select * from emp where （deptno=10 or deptno=30） and sal=1250;

         要给or加括号！

 

3.在where条件中使用in--in后面是一个集合

案例：

        1.查询部门号是10或20的员工信息

           select * from emp where deptno in (10,20);

        可以在in的集合中使用null

        deptno in (10,20,null)等价于 deptno=10 or deotno=20 or 的deptno=null；

        2.查询不是10和20号部门的员工信息

            select * from emp where deptno not in (10,20);

        不可以在not in的集合中有null

        select * from emp where deptno not in (10,20,null);

        因为deptno not in (10,20,null) 等价于deptno!=10 and deptno!=20 and deptno!=null;

        包含有null的表达式为假。

 

4.在where条件中使用like--模糊查找，其中：‘%’匹配任意多个字符，‘_’匹配一个字符

案例：

        1.查询员工首字母是S的员工信息

            select * from emp where ename like 'S%';

        2.查询员工编号为79开头的员工信息

            select * from emp where empno like '79%';

        3.查询名字为四个字母长度的员工信息

            select * from emp where ename like '____';

        4.查询员工姓名带_的员工信息

            select * from emp where ename like '%\_%' escape '\';

 

排序：使用order by关键字，基本格式：

select...from...where condition order by colname|alias|expr|number(序号)

order by有两种排序方法：升序（asc），降序（desc）。。默认为升序

1.使用列名进行排序：

案例:

        1.员工信息按入职日期先后排序

            select * from emp order by hiredate;

            select * from emp order by hiredate asc;

        2.员工薪水按从小到大排序

            select * from emp order by sal desc；

        3.查询员工信息按奖金逆序

            select * from emp order by comm desc；

            select * from emp order by comm desc nulls last；（把空放在后面）null值表现为无穷大

        4.  员工信息按部门升序、薪水降序排列

             select * from emp order by deptno asc，sal desc； 

    结论：asc和desc作用于最近的前面一列，按照多个列进行排序的时候，先按照第一列进行排序，若第一列相同，则按照第二列排序。

2.使用序号进行排序

案例:

        1.查询员工编号，员工姓名和工资，按照序号（工资）进行排序

            select empno,ename,sal from emp order by 3;

        结论：序号就是select后面的列出现的次序。

3.使用别名进行排序

案例：

        1.按员工的年收入进行排序

            select ename，sal，sal*12+nvl（comm，0）"年收入"from emp order by "年收入"；

4.使用表达式进行排序

        1.按员工的年薪进行排序

            select ename，sal，sal*12 "年薪" from emp order by sal*12；

 

说明：排序可以用的方式：列名、序号、别名、表达式

          如果有多列进行排序，首先按照第一列进行排序，若第一列相同的话 再按照第二列进行排序。

 

单行函数：只针对一行进行，返回一行记录。

 

字符串相关函数：

1.lower 小写，upper 大写，initcap  单词的首字母大写

select lower（'HELLO WORLD'）"小写"，upper（‘Hello world’）"大写"，initcap('hello world') "首字母大写" from dual;

 

2.contact（连接符||）

select concat('hello ','world') from dual;

注意：concat函数只能连接两个字符串，若想连接三个的话只能嵌套调用：

select concat(concat('hello ','world'),' nihao') from dual;

select 'hello '||'world '||'nihao' from dual;

注意：||可以连接多个字符串，建议使用||来连接字符串。

总结：concat只能用于两个字符串的连接，||可以用于多个字符串的连接，建议使用||。

 

3.substr(str,pos,len)截取字符串

select substr('helloworld,1,3'),substr('helloworld',1),substr('helloworld',-3) from dual;

总结：pos是从1开始的，若len为0表示从pos开始截取到最后，若pos为负数，表示从末尾开始截取。

 

4.instr(str,substr):判断substr是否在str中存在，若存在返回第一次出现的位置，若不存在返回0

select instr('hello llo', 'llo'),instr('hello llo', 'ow')from dual;

 

5.lpad和rpad   l(r)pad(str,len,ch):返回len长度的字符串，如果str不够len的话，在它的左右填充ch这个字符。

select lpad('aaaa',10,'$'),rpad('aaaa',10,'#') from dual;

 

6.trim:去掉首部和尾部的空格，中间的空格不去掉

select 'aaa'||trim('   hello world    ')||'bbb' from dual;

 

7.repalce(str,old,new):将str字符串中old字符串替换成new字符串

select replace('hello world','llo','yy') from dual;

 

8.length和lengthb

select length('hello world') 字符数，lengthb（'hello world'）字节数 from dual;

select length('哈喽我的') 字符数，lengthb('哈喽我的') 字节数 from dual；

注意：对于length函数一个汉字是一个字符，对于lengthb函数，一个汉字占两个字节，两个函数对于普通字符串没有区别。

 

数值函数

1.round：四舍五入

select round(45.926,2) 一，round(45.926,1) 二，round(45.926,0) 三，round(45.926,-1) 四，round(45.926,-2);

trunc:截取

select trunc(45.926,2) 一，trunc(45.926,1) 二，trunc(45.926,0) 三，trunc(45.926,-1) 四，trunc(45.926,-2);

 

2.mod

select mod(1600,300) from dual;

 

3.ceil:向上取整

   floor：向下取整

   select ceil(121/30),floor(121/30) from dual;

 

转换函数

1.to_char和to_number

把薪水转换为本地货币字符型

select empno,sal,to_char(sal,'L9,999') from emp;

把上述某个结果转回数值型

select to_number('￥2,957','L9,999') from dual;

 

2.to_char与to_date

显示“yyyy-mm-dd hh24:mi:ss 今天是 星期几”

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss "今天是" day') from dual;

将上述输出字符串反转回日期

select to_date('2017-12-04 01:12:48 今天是 星期一','yyyy-mm-dd hh24:mi:ss "今天是" day');

查询1981-11-17日入职的员工信息

select * from emp where to_char(hiredate,'YYYY-MM-DD')='1981-11-17';

select * from emp where hiredate=to_date('1981-11-17','YYYY-MM-DD');

 

oracle的隐式转换和显示转换

select 11+'22' from dual;

select 11+to_number('22') from dual;

对于select 11+'22' from dual; //会隐式转换，将‘22’转换为22

 

select '11' || 22 from dual;

select '11' || to_char(22) from dual;

对于select 11+'22' from dual;//会做隐式转换将‘22’转换为22

 

select 11-'1a' from dual;   //报错，1a不是数字，所以不能转

总结：当没有明确转换函数的时候，如果类型不一致，会进行隐式转换，隐式转换有一个前提他必须能转换。

 

时间和日期函数

显示当前系统的日期

select system from dual；

 

显示当前的系统日期显示到秒

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 

显示当前日期星期几

select to_char(sysdate,'day') from dual;

显示昨天、今天，明天===oracle日期型+1代表加一天

select sysdate-1 昨天，sysdate 今天，sysdate+1明天 from dual；

 

计算员工工龄 可以按日、周、月、年 日期差减方法

select empno,ename,sysdate-hiredate 日,(sysdate-hiredate)/7 周,(sysdate-hiredate)/30 月,(sysdate-hiredate)/365 年 from emp;

 

日期函数

months_between add_months last_day next_day

select empno,name,months_between(sysdate,hiredate),(sysdate-hiredate)/30 月 from emp;

 

增加月份 add_months

select add_months(sysdate,2) from dual;

 

求明年的今天

select add_months(sysdate,12) from dual;

 

最后一天 last_date 指定日期所在月份的最后一天

select last_day(sysdate) from dual;

 

上个月的最后一天

select last_day(add_months(sysdate,-1)) from dual;

 

指定日期的下一个星期几 next_day

select next_day(sysdate,'星期一') from dual;

 

对日期型数据进行四舍五入和截断 round、trunc

select round(sysdate,'month'),round(sysdate,'year') from dual;

select trunc(sysdate,'month'),trunc(sysdate,'year') from dual;

 

条件表达式

老板要给一些职位的人涨工资

CASE expr WHEN comparison_expr1 THEN return_expr1

                  [WHEN comparison_expr2 THEN return_expr2

                   WHEN comparison_exprn THEN return_exprn

                    ELSE else_expr]

END

 

select empno,ename,job,sal"涨前薪水",

          case job,

            when ‘PRESIDENT’then

              sal +1000

            when 'MANAGER' then

              sal + 800

            else

              sal + 400

        end "涨后薪水"

from emp;

 

DECODE(col|expression,search1,result1

                            [,search2,result2,...,]

                            [,default])

select empno,ename,job,

          sal "涨前薪水"，

          decode(job,'PRESIDENT',sal+1000,'MANAGER',sal + 800,sal + 400) "涨后薪水" from emp;

 

分组函数（组函数 聚合函数）:具有滤空功能

oracle提供的常用的分组函数有：sum，avg，count，max，min

案例：

1.统计员工总数

select count(empno) from emp;

select count(*) from emp;

2.统计工种总数

select count(job) from emp;  

select count(distinct job) from emp; //去重

3.求员工的平均工资

 select avg(sal),sum(sal)/count(empno) from emp;

4.求员工的平均奖金

select avg(comm),sum(comm)/count(comm),sum(comm)/count(empno) from emp;

5.求员工表中最高工资和最低工资

select max(sal),min(sal) from emp;

6.如何去掉分组函数中的滤空功能

nvl函数

select count(comm),count(nvl(comm,0)) from emp;

 

分组数据：借助execl理解分组功能

基本格式：select...,count() from emp where...group by..

说明：按照group by 后给定的表达式，将from后面的table进行分组，针对每一组，使用分组函数。

案例：

1.统计各个部门的平均工资

select depno,avg(sal) from emp group by deptno;

2.统计各个部门不同工种的平均工资

select depno,job,avg(sal) from emp group by deptno,job;

结论：

select a,b,c..., count(f) from emp group by a,b,c...;

select 后面没有出现在分组函数中的列名，一定要出现在group by字句中

在group by子句中出现的列，不要求一定出现在select后面的列中

3.统计各个部门平均工资高于2000

select depno,avg(sal) from emp group by deptno where avg(sal)>2000;(错)

select depno,avg(sal) from emp group by deptno having avg(sal)>2000;

结论：对分组数据进行过滤，不能够使用where应该使用having

4.求10号部门员工的平均薪水

1）select depno,avg(sal) from emp group by  deptno having deptno=10;

2)    select depno,avg(sal) from emp where deptno=10 group by deptno;(更好)

第一种方法是对整个表进行分组统计而第二种方法是先进行过滤然后进行分组统计。

笛卡尔积的概念

 

 

回顾：

求10号部门的最低薪水和最高薪水

select min(sal),max(sal) from emp where deptno=10;

select depno,min(sal),max(sal) from emp where deptno=10 group by deptno;

求1980年12月17日入职的员工信息

select * from emp where hiredate=to_date('1980-12-17','YYYY-MM-DD');

select * from emp where to_char(hiredate,'YYYY-MM-DD')='1980-12-17';

求当前日期所在的月份的最后一天和第一天，显示格式为：yyyy-mm-dd

select to_char(last_day(sysdate),'YYYY-MM-DD') from dual;

select to_char(last_day(sysdate),'YYYY-MM') || '-01' from dual;

select to_char(last_day(sysdate),'YYYY-MM-DD'),to_char(last_day(sysdate),'YYYY-MM') || '-01' from dual;

查询名字中有字母A，并且是MANAGER的员工

select *from emp where ename like '%A%' and job='MANAGER';

将员工信息按照部门倒序，薪水升序排序

select * from emp order by deptno desc,sal asc;

求薪资在1200以上的员工数

select count(empno) from emp where sal>1200;

求每个部门的员工数

select depno,count(empno) from emp group by deptno;