SQL
SQL 是 Structured Query Language（结构化查询语言）的首字母缩写词,SQL 是数据库语言，通过 SQL可以实现与 Oracle 服务器的通信。可以执行CRUD等操作。
注：CRUD是指增加(Create)、检索(Retrieve)、更新(Update)和删除(Delete)
SQL分类
DDL（Data Definition Language）：数据定义语言，用来定义表、列等；
如：创建数据表...
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
如：添加数据，修改数据，删除数据
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。
如：查询数据
DTL (Data Transaction Language):数据事务语言,用来操作事务。
如：开启事务，提交事务
DCL (Data Control Language):数据控制语言。用来操作用户和权限
如：创建用户，授权。
其中 总是会说增删改查 在分类中 为什么要将查询和增删改分成两部分那？
1.简单查询
1.简单查询语句的语法
select 列名,列名,...,列名 from 表名;
select ... from ：它是查询语句的关键字
select后面跟是你要从数据库里取的数据
from后面跟表名，表示你要从哪张中读取这些数据
如：
--查询emp表中员工的编号、姓名、工作、工资
select empno,ename,job,sal from emp;
--查询emp表中所有列的信息
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
如果要查询表中所有列的信息，那么select和from之间的列名可以用*来表示,注意：*号的执行效率比写列名时的执行效率低，（*在执行sql语句时，oracle会去查数据库中的数据字典，查询这个张表都有哪些列，然后查出这张表的所有列的数据）
2.注释
单行注释：--
多行注释：以/* 开始，以*/结尾
3.sql中的别名
别名语法：
对象名 as 别名
对象名 别名
如：
select empno as 员工编号,ename 姓名,job 工作,mgr 上级编号,hiredate 入职日期,sal 工资,comm 佣金,deptno 部门编号 from emp;
注意：给表、视图取别名时不能加as
select e.* from emp e; --select * from emp;
select e.empno,e.ename,e.job,e.sal from emp e;
select emp.empno,emp.ename,emp.job,emp.sal from emp;
4.带条件的查询
select 列名,列名,...,列名 from 表名 where 条件表达式;
where:条件关键字，后面跟条件表达式，这个条件一般是和数据库中列相关
条件表达式：
>
<
>=
<=
!=,<>:表示不等于
=
--查询工资大于1500的员工信息
select * from emp where sal>1500;
--查询名字叫SMITH的员工的信息
select * from emp where ename='SMITH';
说明：在oracle数据库中，数字类型直接写就可以，字符串类型的数据要用单引号括起来
--查询不是10号部门的员工的信息
select * from emp where deptno!=10;
select * from emp where deptno<>10;
in：后面跟一个集合或者子查询
oracle中表示一个简单的集合 ，（元素，元素，元素，... 元素） 例如 （1,2,3,4,5） ('a','b','c','d')
--查询emp表中，工资是800，1600，或者3000的员工信息
select * from emp where sal in(800,1600,3000);
not in:和in是相反的
select * from emp where sal not in(800,1600,3000);
like:它只用在字符串列，表示对字符串进行模糊匹配
有两个点位符：
%：表示在这个%出现的地方可以有0个或者多个字符
_: 表示在_出现的地方有且只能一个任意字符
--查询员工姓名S结尾的员工信息
select * from emp where ename like '%S';
--查询emp表中员工姓名的第二个字符是L的员工信息
select * from emp where ename like '_L%';
--查询emp表中员工姓名第三个字符是A的员工信息
select * from emp where ename like '__A%';
--查询员工姓名中有S的员工信息
select * from emp where ename like '%S%';
not like:和like相反
--查询名字中没有S的员工信息
select * from emp swhere ename not like '%S%';
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(7311,'qer_q%oasf','etl',7369,sysdate,3000,200,null);
like中的转义字符：\
在新版的oracle中，使用转义字符需要手动去定义 定义 escape '\'
转义字符，它会把紧跟在它后面的字符当作一个普通的字符去处理
select * from emp where ename like '%\_%' escape '\';
select * from emp where ename like '___\_%' escape '\';
select * from emp where ename like '%a%%' escape 'a';
all、any：后面都跟一个集合或者子查询
>all:表示大于集合中最大的元素 >all（1,2,3,4,5） 等价于 >5
<all:表示小于集合中最小的元素 <all(1,2,3,4,5) 等价于 <1
>any:表示大于集合中最小的元素 >any(1,2,3,4,5) 等价于 >1
<any:表示小于集合中最大的元素 <any(1,2,3,4,5) 等价于 <5
--查询员工信息，要求员工的工资比以下的值都低 1600，2000，3000
select * from emp where sal <all(1600,2000,3000);
--查询比下任意一个（1600,2000,3000）工资高的员工信息
select * from emp where sal>any(1000,2000,3000);
--查询工资比所有人（1600,2000,3000）都高的员工信息
select * from emp where sal > all(1600,2000,3000);
exists:它不和任何列一起使用，后面跟的是一个子查询（查询语句select语句），如果子查询能查出结果，那么这个条件就是成立的，查不出结果条件不成立
not exists:和exists相反
select * from emp where exists(select * from emp where 1=0);
select * from emp where exists(select * from emp);
条件连接符
and:表示并且，当and连接的两个条件同时成立时，总条件才算成立，只要有一个条件不成立，整体不成立
or：表示或者，当or连接的两个条件有一个条件成立，整体条件成立，只有两个条件都不成立时，整体才不成立
between value1 and value2：value1的值比value2值小，value1和value2一般是数字类型，还可以是日期，表示值在value1和value2之间
它的取值是包含value1和value2的。
--查询10号部门，工资大于1000的员工信息
select * from emp where deptno=10 and sal>1000;
--查询10号部门，或者工资小于2000的员工信息
select * from emp where deptno=10 or sal<2000;
--查询工资在1000到3000之间的员工信息
select * from emp where sal>=1000 and sal<=3000;
select * from emp where sal between 1000 and 3000;
--查询工资大于1000并且小于3000的员工信息
select * from emp where sal>1000 and sal<3000;
select * from emp where sal between 1000 and 3000 and sal!=1000 and sal!=3000;
is null:表示这个值是空时条件成立
is not null：表示值不为空间条件成立
select * from emp where comm is null;
select * from emp where comm is not null;
dual表：它是oracle内置的一个单行表，
select 1 from dual;
select 'abc' from dual;
select 2*3 from dual;
select 1,2,3,4 from dual;
select 1 from emp;
--查询员工的年薪
select sal*12 from emp;
2.排序分组
1.排序
select 列名,列名,..,列名 from 表名 where 条件表达式 order by 排序列的列名 asc|desc;
asc|desc: asc表示升序，desc表示降序
--查询员工信息，按照员工的工资升序排序
select * from emp order by sal asc; ---如果是升序排序，asc可以省略
select * from emp order by sal;
--查询员工信息，按照员工编号降序排序
select * from emp order by empno desc;
order by ：后面可以跟多个排序列，跟多个排序列时，
它的含义：按照第一排序列（紧跟前order by 关键字的列）排序，
如果第一个排序字段的值相同时，它会按照第二个排序字段进行排序
--查询员工信息，按照部门编号升序排序，如果部门编号相同时，按照工资的升序排序
select * from emp order by deptno asc,sal asc;
--查询员工信息，按照部门编号降序排序，如果部门编号相同，按照员工编号升序排序
select * from emp order by deptno desc,empno;
--查询员工的编号、姓名、工作、工资、部门编号，按照部门编号升序排序，如果部门相同时，按照工资降序排序
select empno,ename,job,sal,deptno from emp order by deptno,sal desc;
注意：order by 后面除了可以跟表中的列名外，还可以跟select 和from之间查询结果的序号
select empno,ename,job,sal,deptno from emp order by 5,4 desc;
select empno,sal,job,ename,deptno from emp order by 5,2 desc;
2.聚合函数
聚合函数：对一组值执行计算,并返回单个值
count(列名|*|常数):它求记录数（数据条数）
max(列名):取最大值
min(列名):取最小值
avg(列名):取平均值
sum(列名):求和
--查询员工表中的员工人数
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;
count(主键列或索引列)
count(*)
count(常数)
count(普通列)
它们执行效率从上到下，依次降低
--查询emp表中的部门数
select count(deptno) from emp;
select deptno from emp;
distinct:去重关键字,跟在列的最前面
select distinct deptno from emp;
select count(distinct deptno) from emp;
select empno,distinct deptno from emp; --这个sql语句是错误的
select distinct empno,deptno from emp;
注意：distinct后面跟多个列时，判断重复数据，所有列的值完全相同时，它才会认为是重复数据
--查询工资总和
select sum(sal) from emp;
--查询员工的平均工资
select avg(sal) from emp;
--查询10号部门的最高工资
select max(sal) from emp where deptno=10;
--查询最低工资
select min(sal) from emp;
3.分组
select 列名,列名,...,列名 from 表名
where 条件
group by 分组列
order by 排序列 asc|desc
group by:分组的关键字，后面跟分组列名，可以是一个分组列，也可以是多个列
--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select empno,min(sal),max(sal),avg(sal),sum(sal),count(*) from emp group by empno;
--查询各个部门的员工人数
select deptno,count(empno) from emp group by deptno;
select job,deptno,count(*) from emp group by job,deptno;
注意：group by 后面跟多个列时，只有当多个列的值同时相等时，它才会分为同一个组;
4.having
select col_name,col_name,...,col_name
from 表名
where 条件
group by 分组列
having 条件
order by 排序列
having:它是对分组后的数据进行筛选，条件表达式中可以使用聚合函数
--查询各个部门的部门编号和部门的平均工资
select deptno,avg(sal) from emp group by deptno;
--求平均工资大于2000的部门编号和平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
where和having的异同
where:条件，where后面跟的条件比having后的条件先执行，条件中不允许使用聚合函数。
having：条件中可以使用聚合函数，一般having和group by联用。
3. 集合、联合查询
1.集合
集合：每一个sql查询的结果就是一个集合
(1,3,4,65,234,123,12)
select * from emp;
并集：取两个集合中所有元素
union all:表示取两个集合的并集，不删除重复元素
union ：取两个集合的并集，删除重复的元素
--查询员工工资大于1000的员工信息
select * from emp where sal>1000;
--查询员工工资小于3000的员工信息
select * from emp where sal<3000;
select * from emp where sal>1000
union all
select * from emp where sal<3000;
select * from emp where sal>1000
union
select * from emp where sal<3000;
union、union all:union all执行效率高，union执行效率低（先进行union all把两个集合合并，之后对合并后的数据进行排序，去掉重复元素）
交集：取两个集合重叠部分的元素
intersect:表示交集
--查询工资在1000到3000之间的员工信息
select * from emp where sal>1000 and sal<3000;
select * from emp where sal>1000
intersect
select * from emp where sal<3000;
差集：第一个集合的所有元素减去两个集合重叠部分的元素
Minus::表示差集
select * from emp where sal>1000
minus
select * from emp where sal<3000;
select * from emp where sal>=3000;
select * from emp where sal<3000
minus
select * from emp where sal>1000;
select * from emp where sal<=1000;
集合运算，关键字前后的两个sql表示的结果集要完全相同，
select ename,job from emp
union all
select ename,job from emp;
2.联合查询
交叉连接（笛卡尔积连接）、内连接、自连接、外连接（全连接、左外连接、右外连接）、自然连接
(1)交叉连接（笛卡尔积连接）
将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积.
语法：
select 列名,列名,...
from a表 cross join b表;
select * from emp;
select * from dept;
select e.*,d.deptno dno,d.dname,d.loc from emp e cross join dept d;
select e.*,d.deptno dno,d.dname,d.loc from emp e,dept d;
(2)内连接
内连接：是把两个表中符合条件的数据连接为一条数据，如果哪个表中存在不符合连接条件的数据，那么这些数据就会被过滤掉（不显示）
语法：
select 列名,...,列名
from a表 inner join b表 on 连接条件
inner join:中的inner可以省略不写
--查询员工信息及他的部门信息
select e.*,d.* from emp e inner join dept d on e.deptno=d.deptno;
select e.*,d.* from emp e join dept d on e.deptno=no;
select e.*,d.* from emp e,dept d where e.deptno=d.deptno;
（3）自连接
自连接是一个特殊的内连接，内连接一般是两个表的连接，自连接将一个表和它自已进行连接
--查询员工信息，并显示它的上级姓名
select e1.*,e2.ename 上级姓名 from emp e1 join emp e2 on e1.mgr=e2.empno
select e1.*,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
(4)不等连接
不等连接：连接条件是不等条件,（大于、小于、不等于）
--查询员工信息，并显示员工的工资等级
select * from salgrade;
select e.*,s.grade,s.losal,s.hisal from emp e join salgrade s on e.sal between s.losal and s.hisal;
(5) 全连接
全连接：它会查出两个表中的所有数据，
1.如果两个表中的数据都符合连接条件，那么它会和内连接一样，将符合条件的数据连接为一条记录
2.如果第一表中的一条数据，在第二张表中没有找到和它对应的记录（第二张表没有和第一张表这条数据符合连接条件），
那么它同样会显示第一张表的这条记录，同时它对应的第二张表数据的位置会显示为空
3.同理，如果第二张的一条记录，在第一张表中没有找到符合连接条件的数据，那么同样会显示，在显示第一张表数据的位置显示为空
语法：
select 列名,列名,列名,....
from 表A full outer join 表B on 连接条件;
full outer join ：中的outer可以省略
--查询所有部门和员工信息
select e.*,d.* from emp e full outer join dept d on e.deptno=d.deptno;
(6)左外连接
语义：查出左表（left outer join关键字前面的表）的所有数据，根据连接条件去右表中找对应的数据
如果找到变显示出来，如果找不到就显示空
--查询所有部门信息，以及各部门下的员工信息
select 列名,列名,...
from 表A left outer join 表B on 连接条件
left outer join ：其中outer可以省略
select d.*,e.* from dept d left join emp e on d.deptno=e.deptno;
select d.*,e.* from dept d,emp e where d.deptno=e.deptno(+);
注意：用（+）表示的左连接或者右连接，如果是左连接，那么符号加在右表的关系列上，如果是右连接，那么符号加在左表的关系列上
--左连接
select d.*,e.* from dept d,emp e where e.deptno(+)=d.deptno;
--右连接
select d.*,e.* from dept d,emp e where d.deptno(+)=e.deptno;
(7)右外连接
语义：它会查出右表的所有数据，根据连接条件去左表查找对应的数据，如果找到就显示，如果找不到就显示为空语法：
select 列名,列名,列名,...
from 左表 right outer join 右表 on 连接条件
right outer join:其中outer可以省略
--查询员工信息，并显示它的部门信息
select d.*,e.* from dept d right outer join emp e on d.deptno=e.deptno;
select d.*,e.* from dept d,emp e where d.deptno(+) = e.deptno;
（8）自然连接
自然连接是在两张表中寻找那些数据类型和列名都相同的字段，
然后自动地将他们连接起来，并返回所有符合条件按的结果。
语法：
select col_name,col_name,...
from table_a natural join table_b;
select col_name,....
from table_a natural join table_b
using(关系列)
select * from emp natural join dept; --自然连接
select * from emp join dept using(deptno); --自然连接using用来指定关系列
4.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询
1.from后面的子查询
--查询员工的姓名和他上级的姓名
select e.*, e2.empno, e2.ename mname, e2.job, e2.sal, e2.deptno
from emp e, emp e2
where e.mgr = e2.empno(+);
select e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno,
d.empno,
d.ename,
d.job,
d.mgr,
d.hiredate,
d.sal,
d.comm,
d.deptno
from emp e,emp d
where e.mgr=d.empno;
select ename, mname
from (select e.*, d.empno, d.ename mname, d.job, d.sal, d.deptno
from emp e, emp d
where e.mgr = d.empno(+));
2.where后面的子查询
(1)单行子查询
单行子查询：子查询的sql语句只查出一条记录
--查询工资大于10号部门平均工资的员工信息
--查询10号部门的员工平均工资
select avg(sal) from emp where deptno=10;
select * from emp where sal>(select avg(sal) from emp where deptno=10);
(2)多行子查询
多行子查询：子查询的sql语句查出若干条记录
in、not in、exists、not exists、all、any
--查询工资比20号部门所有人工资都高的员工信息
1)用单选子查询来完
--查询20号部门的最高工资
select max(sal) from emp where deptno=20;
select * from emp where sal>( select max(sal) from emp where deptno=20);
2)>all
--20号部门的员工工资是一个集合
select sal from emp where deptno=20;
select * from emp where sal>all(select sal from emp where deptno=20);
3.exists、not exists
--查询有员工的部门信息
select * from dept;
--根据部门编号查询员工人数
select count(*) from emp where deptno=10;
select * from dept d where (select count(*) from emp where deptno=d.deptno)!=0;
exists:后面跟子查询，如果子查询能查出数据，那么条件是真的，如果子查询查不出结果，条件不成立
select * from dept d where exists(select * from emp where deptno=d.deptno);
--查询没有有员工的部门信息
select * from dept d where not exists(select * from emp where deptno=d.deptno);
4.having后面的子查询
--查询各个部门的部门编号和员工人数，要求部门的平均工资大于30号部门的平均工资
select deptno,count(*),avg(sal) from emp group by deptno having avg(sal)>1500;
--查询30号部门的平均工资
select avg(sal) from emp where deptno=30;
select deptno,count(*) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);
5.select 和from 之间的子查询
select empno,ename,(select 1 from dual) from emp;
注意：select 和from之间的子查询，子查询的结果只能是单行单列
原始排序：
order by 9;
5.分页sql
伪列：rownum 它是oracle数据库内置列，任何表都可以使用，它作用是显示表中数字的行号
select * from emp;
select emp.*,rownum from emp;
select t.*,rownum r from (select * from emp order by sal) t;
--查询工资最低的5名员工信息
select t.*,rownum r from (select * from emp order by sal) t where rownum<=5;
--注意如果给伪列加条件，小于一个值是可以，不能让它大于某个值
select t.*,rownum r from (select * from emp order by sal) t where rownum>=2; --伪列不能直接大于一个值
select t.*,rownum r from (select * from emp order by sal) t where rownum=5; --伪列不能直接等于一个不等于1的值
select t.*,rownum r from (select * from emp order by sal) t where rownum=1; --伪列可以直接等于1
使用rownum的分页sql,rownum伪列的序号，只连续的不考虑值相等的情况（值相同序号不相同）
--查询5到10名的员工信息
select * from (select t.*,rownum r from (select * from emp order by sal) t) t2 where t2.r between 5 and 10;--包三层查询
row_number():需要和over分析函数联用，排序的序号和rownum伪列相同，连续序号，不考虑值相等的情况（值相同序号不相同）
dense_rank():可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
rank():可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同
over(partition by 分组列 order by 排序列)：over子句中的分组partition by和group by的分组不同，它不会把数据聚合成一条，在over子名中可以省略
select e.*,row_number()over(order by sal desc) r from emp e;
select e.*,dense_rank()over(order by sal desc) r from emp e;
select e.*,rank()over(order by sal desc) r from emp e;
select e.*,row_number()over(partition by deptno order by sal desc) r from emp e;
select e.*,dense_rank()over(partition by deptno order by sal desc) r from emp e;
select e.*,rank()over(partition by deptno order by sal desc) r from emp e;
使用row_number(),rank(),dense_rank()的分页sql,只需要一层子查询
select * from (select e.*,row_number()over(order by sal) r from emp e) t where t.r between 5 and 10;
6.case when语句
语法：
case
when 条件表达式1 then
值
when 条件表达式2 then
值
when 条件表达式3 then
值
...
else
默认值
end
语义：如果表达式成立，那么整个语句值为表达式，then后面的值，
如果所有表达式都不成立，会取else后的默认值
else语句可以省略
select * from student;
select count(*) from student where ssex='男';
select s.*,(select count(*) from student where ssex='男') 男生人数,(select count(*) from student where ssex='女') 女生人数 from student s;
使用case when统计男生人数
select sum(case when ssex='男' then 1 else 0 end) 男生人数,sum(case when ssex='男' then 0 else 1 end) 女生人数,sum(case when ssex='女' then 1 else 0 end) 女生人数 from student s;
浙公网安备 33010602011771号