SQL语言-1

1.SQL语言基础

1.1什么是SQL语言

1. SQL,Structured Query Language,结构化查询语言
2. SQL是最重要的关系数据库操作语言，是所有关系数据库管理系统的标准语言。
3. SQL语言是非过程化的语言，只需要告诉做什么，不需要关注怎么做，简单

1.2SQL语言可以做什么

1. 增删改查（CRUD）
2. 操作数据库对象（用户，表格，序列，索引…）
3. 操作用户权限和角色的授予跟取消
4. 事务（Transaction）管理

1.3SQL语言的分类

1. DQL，Data Query Language，数据查询语言，执行数据库的查询操作，select
2. DML, Data Manipulation Language,数据操作语言，操作表格中的数据，执行增删改，insert,delete,update
3. DDL,Data Definition Language，数据定义语言，用于操作数据库对象,create,alter,drop
4. DCL,Data Control Language,数据控制语言，操作用户权限,grant,revoke
5. TCL,Transaction Control Language,事务控制语言，用于管理事务，commit,rollback

2.Select子句

用于指定查询表格中的列信息

2.1通配符*

a）查询emp表格的所有数据
> * 通配符，表示所有的列

-- select子句,注释
select * from emp;

2.2指定列

查询所有员工的编号，姓名和职位

select empno,ename,job from emp;

2.3支持算术运算

查询所有员工的姓名，职位和年薪

select ename,job,sal*12 from emp;

2.4列别名

select字句中，可以通过as关键字给列起别名

select ename,job,sal*12 as nianxin from emp;

as关键字可以被省略，一般都省略

select ename,job,sal*12 nianxin from emp;

别名中，尽量不要使用特殊符号，例如空格;如果非要有特殊符号，可以使用双引号括起来
在Oracle中，双引号表示原样输出

select ename,job,sal*12 "nian xin" from emp;

3distinct_字符串连接符_order by

3.1distinct

用于去除重复行信息
a）查询所有的职位信息

select distinct job from emp;

b）查询所有员工的姓名和职位
distinct只能去除重复行，distinct的作用范围是它后面的所有列

select distinct ename,job from emp;

3.2字符串连接符||

Oracle中，用单引号 表示字符串
a）查询所有员工的姓名，职位和薪资，以姓名:xxx,职位:xxx,薪资：xxx的形式显示

select '姓名:'||ename||',职位:'||job||',薪资:'||sal info from emp;

3.3 order by子句

用于进行排序，永远写在语句的最后
a）查询所有员工的信息，按照工资升序排序
asc 表示升序
默认情况下，按照升序排序，所以，asc一般被省略

select * from emp order by sal asc;

b）查询所有员工的信息，按照标号降序排序
desc,表示降序排序(descend)

select * from emp order by empno desc;

c）查询所有员工的信息，按照入职日期降序排序

select * from emp order by hiredate desc;

d）查询所有员工的信息，按照姓名排序

select * from emp order by ename;

e）查询所有员工信息，按照薪资降序排序，如果薪资相同，将新员工排在前面。

select * from emp order by sal desc,hiredate desc;

f) 查询所有员工的姓名和年薪，按照年薪排序

select ename,sal*12 nianxin from emp order by nianxin;

4.where子句

用于进行条件过滤

4.1 等值条件

a）查询10部门所有员工的信息

select * from emp where deptno = 10;

b）查询SCOTT的详细信息
字符串必须加单引号，而且大小写敏感

select * from emp where ename = 'SCOTT';

c）查询在1982-01-23入职的员工信息
日期必须用单引号括起来
日期格式必须是:DD-MM月-YY

select * from emp where hiredate = '23-1月-82';

4.2非等值条件

a）查询工资在1500到3000之间的员工信息
between…and…表示一个范围，包含边界

select * from emp where sal>=1500 and sal<=3000;
select * from emp where sal between 1500 and 3000;

b）查询SCOTT和KING的详细信息

select * from emp where ename='SCOTT' or ename='KING';
select * from emp where ename in ('SCOTT','KING');

c）查询所有员工的信息，排除20部门

select * from emp where deptno != 20;
select * from emp where deptno <> 20;

5.模糊查询

使用like(像)实现，配合通配符实现。
_,表示任意一个字符
%，表示任意个任意字符
a）查询姓名首字母为A的员工的信息。

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

b）查询姓名第二个字母为A的员工的信息。

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

c）查询姓名中带有字母C的员工的信息

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

d) 查询姓名中带有下划线的员工的信息
escape用于声明转义字符。

select * from emp where ename like '%a_%' escape 'a';

6. IS NULL

用于判断空值
a）查询所有没有提成的员工信息

select * from emp where comm is null;

b）查询所有有提成的员工信息

select * from emp where comm is not null;
select * from emp where not comm is null;

6.1 and和or的优先级

and的优先级高于or的优先级
a）查询所欲CLERK的信息和工资大于1250的SALESMAN的信息

select * from emp where job='CLERK' or job='SALESMAN' and sal>1250;

b）查询所有的CLERK和SALESMAN的信息同时工资要大于1250.

select * from emp where (job='CLERK' or job='SALESMAN') and sal>1250;

7.字符函数

7.1lower,upper,initcap

a）lower，将所有字母小写
b）upper，将所有字母大写
c）initcap，所有单词首字母大写
查询所有员工的姓名，分别显示小写，大写和首字母大写

select ename,lower(ename),upper(ename),initcap(ename) from emp;

在Oracle中，提供了一个虚拟表格，叫dual，专门用于进行测试，可以从dual中查询任何数据。

select upper('abcd') from dual;

7.2 length

用于计算字符串的长度
a）查询所有员工的姓名及姓名的长度

select ename,length(ename) from emp;

b）查询所有姓名长度为5的员工的信息

select * from emp where length(ename)=5;

7.3 replace

用于进行字符串的替换
a）查询所有员工的姓名，将A替换为a

select ename,replace(ename,'A','a') from emp;

7.4 substr

用于进行字符串的截取
a）查询所有员工的姓名，并显示姓名的前3个字母

select ename,substr(ename,1,3) from emp;

b）查询所有员工的姓名，并显示姓名的后3个字母

select ename,substr(ename,length(ename)-2,3) from emp;

第三个参数可以省略，表示一直截取到末尾，所以可以简写为：

select ename,substr(ename,length(ename)-2) from emp;

第二个参数可以是负数，表示倒着截取，又可以简写为：

select ename,substr(ename,-3) from emp;

8.数值函数

8.1 ceil

向上取整

select ceil(123.000001) from dual;

8.2 floor

向下取整

select floor(123.999999) from dual;

8.3 round

四舍五入
第二个参数可以控制四舍五入的位数，正数表示小数点后，负数表示小数点前

select round(123.5656,1) from dual;

8.4 trunc

截断

select trunc(123.5656,2) from dual;

9.日期函数

9.1 sysdate

获取系统时间

select sysdate from dual;

9.2 months_between

计算两个日期间的月数
a）查询所有员工的入职的月数

select ename,months_between(sysdate,hiredate) from emp;

9.3 add_months

给日期加减月数

select sysdate,add_months(sysdate,5) from dual;

9.4 last_day

计算给定日期所在月份的最后一天是哪个日期

select sysdate,last_day(sysdate) from dual;

9.5 next_day

基于给定日期计算下个给定的星期几是什么日期

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

10.转换函数

用于在不同类型间进行转换。数值类型，字符串类型，日期类型

10.1 to_number

将字符串转换为数字，涉及到钱的时候。
$123,123,123.00->123123123

select to_number('$123,123,123.00','$999,999,999.99')+1 from dual;

10.2 to_date

将字符串转换为日期

select to_date('2019-3-11 19:38:50','YYYY-MM-DD HH24:MI:SS') from dual;

10.3 to_char

将数字或日期转换为字符串

select to_char('123123123123','L999,999,999,999.99') from dual;
select sysdate,to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

11.通用函数

11.1 nvl

用来处理空值，如果某个字段为空，则使用对应的数据进行替换
注意：两者的数据类型必须一致
查询所有员工的姓名，工资，提成和总工资（工资+提成）

select ename,sal,comm,sal+nvl(comm,0) total from emp;

11.2 nvl2

有三个参数，如果第一个参数不为空，则使用第二个参数，如果为空，则使用第三个参数

select ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp;

11.3 decode

类似于switch…case…,
查询所有的职位，并显示对应的中文描述

select distinct job,decode(job,'CLERK','职员','SALESMAN','销售','PRESIDENT','董事长','MANAGER','经理','ANALYST','分析师') job_zh from emp;

12.分组函数（聚组函数）

12.1 sum

求和
查询所有员工的工资总和

select sum(sal) from emp;

12.2 avg

求平均值
查询平均工资

select avg(sal) from emp;

12.3 max

求最大值
统计公司的最高工资

select max(sal) from emp;

12.4 min

求最小值
统计公司的最低工资

select min(sal) from emp;

12.5 count

计数
统计公司的员工总数

select count(empno) from emp;
select count(*) from emp;

13. group by_having

进行分组查询，group by子句可以将数据分为若干个组

13.1 分组查询

注意： 出现在SELECT子句中的字段，如果不是包含在多行函数中，那么该字段必须同时在GROUP BY子句中出现
a）统计每个部门的编号，最高工资和最低工资

select deptno,max(sal),min(sal)
from emp
group by deptno
order by deptno;

13.2 带where的分组查询

注意： group by子句要写到where子句的后面
a）查询每个部门的人数和平均工资，排除10部门

select deptno,count(*),avg(sal)
from emp
where deptno<>10
group by deptno
order by deptno;

13.3 带having的分组查询

注意
->where子句中不允许使用分组函数，用于分组前过滤
->having用于分组后的条件
a）查询每个部门的总工资和平均工资，排除平均工资低于1600的部门

select deptno,sum(sal),avg(sal)
from emp
group by deptno
having avg(sal)>=1600
order by deptno;

13.4 select语句的执行顺序

from->where->group by->select->having->order by
a）在emp表中，列出工资最小值小于2000的职位

select job,min(sal)
from emp
group by job
having min(sal)<2000;

b）列出平均工资大于1200元的部门和工作搭配组合

select deptno,job,avg(sal)
from emp
group by deptno,job
having avg(sal)>1200
order by deptno;

14 DML

14.1 复制一个测试表格

a) 复制emp表格，命名为temp.

create table temp as (select * from emp);

b) 复制emp表格的表结构，不复制表数据

create table temp2 as (select * from emp where 1=2);

14.2 新增(insert)

a）语法
insert into 表名 [(列1，列2，…)] values (值1，值2，…);
b）向temp2表格中插入一条数据

insert into temp2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)
       values(1234,'小明','学生',1111,to_date('2018-8-20','yyyy-mm-dd'),3000,200,10);

1. 当表格的每一列都要插入数据时，可以省略列名不写。需要保证值的顺序和列的顺序一致

insert into temp2
       values(1235,'小李','学生',1111,to_date('2018-8-20','yyyy-mm-dd'),3000,200,10);

2. 向表格插入一行数据，只有编号和姓名。

insert into temp2 (empno,ename)
       values(1236,'小郑');

14.3 修改

a）语法
update 表名 set 列 列1=值1[,列2=值2…][where条件]
b）将10部门员工的工资调高10%

update temp2 set sal=sal * 1.1 where deptno=10;

14.4 删除(delete)

a）语法
delete [from] 表名 [where 条件];
b) 删除编号为1234的员工信息

delete from temp2 where empno=1234;

15. SQL99连接查询_cross join_natural join

15.1 cross join(交叉连接)

交叉连接会产生一个笛卡尔积

select * from emp cross join dept;

在笛卡尔积中，有很多数据是无意义的，所以需要消除，可以通过where子句来消除。

select * from emp cross join dept where emp.deptno=dept.deptno;

可以在查询时，给表格起别名。

select e.*,dname,loc from emp e cross join dept d where e.deptno=d.deptno;

15.2 natural join（自然连接）

natural join用于针对多张表的同名字段进行等值连接

select *
from emp
natural join dept;

特点：
a）在自然连接时，自动进行所有同名列的等值连接，不需要写连接的条件。
b）同名列只显示一列，而且在使用时，不能加表前缀
查询所有员工的姓名，部门编号和部门名称

select e.ename,deptno,d.dname
from emp e
natural join dept d;

16 using子句

用来指定进行等值连接的同名字段，针对自然连接提供的，同名字段依然不能使用表前缀。
a）查询20部门员工的姓名，工资，部门编号和部门名称。

select e.ename,e.sal,deptno,d.dname
from emp e
natural join dept d
where deptno=20;

select e.ename,e.sal,deptno,d.dname
from emp e
join dept d using(deptno)
where deptno=20;

17.on子句

on子句是使用非常广泛的子句，它可以被用来指定连接的条件。用于将过滤条件和关联条件分开
a）查询所有员工的姓名，工资和工资等级

select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal>=s.losal and e.sal<=s.hisal;

select e.ename,e.sal,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;

b）查询30部门员工的编号，姓名，部门名称和所在地

select e.empno,e.ename,d.dname,d.loc
from emp e
join dept d 
on e.deptno=d.deptno
where e.deptno=30;

c）查询所有员工的姓名，部门名称，工资及工资等级
N张表连接，至少需要N-1个连接条件

select e.ename,d.dname,e.sal,s.grade
from emp e
natural join dept d
join salgrade s
on e.sal between s.losal and s.hisal;

18.外连接

外连接除了能显示满足连接条件的数据外，还用于显示不满足连接田间的数据。

18.1 左外连接

left [outer] join,表示左外连接，可以显示左表中不满足连接条件的数据。
a) 查询所有员工的姓名，职位和部门信息，显示没有员工的部门信息

select e.ename,e.job,d.deptno,d.dname,d.loc
from dept d
left join emp e
on e.deptno=d.deptno; 

18.2 右外连接

right [outer] join，表示右外连接，可以显示右表中不满足连接条件的数据

select e.ename,e.job,d.deptno,d.dname,d.loc
from emp e
right join dept d
on e.deptno=d.deptno; 

18.3 全外连接

full [outer] join，表示全外连接，可以显示左右两表中不满足连接条件的数据

select e1.empno, e1.ename, e2.empno, e2.ename 
from emp e1 
full join emp e2 
on e1.mgr=e2.empno 
order by e1.empno; 

19.自连接

自连接是发生在同一个表格中的连接
a）查询所有员工的编号，姓名和领导的编号及姓名

select e1.empno,e1.ename,e2.empno mgr,e2.ename
from emp e1
join emp e2
on e1.mgr=e2.empno
order by e1.empno;

b）查询所有员工的编号，姓名和领导的编号及姓名，并显示没有领导的员工信息。

select e1.empno,e1.ename,e2.empno mgr,e2.ename
from emp e1
left join emp e2
on e1.mgr=e2.empno
order by e1.empno;

SQL92多表连接查询

1.1 笛卡尔积

多张表通过逗号分隔

select * from emp,dept;

消除无意义的数据

select * from emp,dept where emp.deptno=dept.deptno;

1.2等值连接

a）查询10部门员工的姓名，职位和部门名称

select emp.ename,emp.job,dept.dname
from emp,dept
where emp.deptno=dept.deptno and emp.deptno=10;

1.3 非等值连接

a）查询所有员工的姓名，工资和工资等级

select e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

1.4外连接

通过“（+）”符号实现外连接

1.4.1 左外连接

查询所有员工的姓名，职位和部门信息，显示没有员工的部门信息

select e.ename,e.job,d.dname,d.loc
from emp e,dept d
where d.deptno=e.deptno(+);

1.4.2 右外连接

select e.ename,e.job,d.dname,d.loc
from emp e,dept d
where e.deptno(+)=d.deptno;

1.5 自连接

查询所有员工的编号，姓名和领导的编号及姓名

select e1.empno,e1.ename,e1.mgr mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno(+);

子查询

用于当一次查询的结果是另一次查询所需要的时候，可以使用子查询

1.1 单行子查询

子查询的返回结果是单行数据
a）查询所有比“CLARK”工资高的员工的信息

select *
from emp
where sal>(select sal from emp where ename = 'CLARK');

b）查询工资高于平均工资的雇员名字和工资

select ename,sal
from emp
where sal>(select avg(sal) from emp);

c）查询和SCOTT同一部门且比他工资低的雇员名字和工资

select ename,sal
from emp
where deptno=(select deptno from emp where ename='SCOTT') 
and sal<(select sal from emp where ename='SCOTT');

d）查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息

select *
from emp
where job=(select job from emp where ename='SCOTT')
and hiredate<(select hiredate from emp where ename='SCOTT');

1.2多行子查询

子查询的返回结果是多行数据。此时，不能再使用普通的比较运算符了。
多行记录比较运算符：
ANY:跟结果中的任何一个数据进行比较
查询工资低于任何一个“CLERK”的工资的雇员信息

select *
from emp
where sal<ANY(select sal from emp where job='CLERK') and job<>'CLERK';

ALL:跟结果中的所有数据进行比较
查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资

select empno,ename,sal
from emp
where sal>ALL(select sal from emp where job='SALESMAN');

IN:等于结果中的任何一个
查询部门20中职务同部门10的雇员一样的雇员信息

select *
from emp
where job in (select job from emp where deptno=10)
and deptno=20;

select *
from emp
where job = any(select job from emp where deptno=10)
and deptno=20;

1.3 相关子查询和不相关子查询

不相关子查询：子查询不会用到外查询的数据，子查询可以独立运行。
相关子查询：子查询会用到外查询的数据，子查询不能独立运行。
查询本部门最高工资的员工
a）不相关子查询的实现方式

select *
from emp e
where (e.deptno,e.sal) in (select deptno,max(sal) from emp group by deptno);

b）相关子查询的实现方式

select * 
from emp e
where sal=(select max(sal) from emp where e.deptno=deptno);

1.4 子查询可以作为一张表格进行多表连接查询

查询每个部门平均薪水的等级

select t.deptno,t.avg_sal,s.grade
from salgrade s
join (select deptno,avg(sal) avg_sal from emp group by deptno) t
on t.avg_sal between s.losal and s.hisal;