第一章 SQL的基本语法规范
1.sql不区分大小写，但是习惯上关键字、系统保留字、函数名称大写，表名和列名小写
2.sql使用空格或者回车来分隔每个单词都一样，习惯上每个子句换行
WHERE子句中的每个条件占一行，子查询单独占一行
3.复杂的语句最好加上注释进行说明
-- 单行注释（空格必须有）
/*
多行注释
*/
4.sql语句中使用分号作为分隔符，系统读取到分号才会执行语句
/*
select 列名1，列名2，……列名n定义查询数据所在的列
from 表名1，表名2，……表名n 定义查询数据所在的表
where 限定条件1 and/or 限定条件2 ...... 定义查询数据的限定条件（行）
group by 列名1，列名2，……列名n定义分组函数的分组方式
having 分组条件1 and/or 分组条件2…… 定义分组函数结果的限定条件
order by 列名1 asc/desc,列名2 asc/desc ...... 定义查询结果的排序方式
*/
第二章 SELECT基础
1.*代表所有列
SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;
SELECT * FROM bonus;
2.查询指定的列
SELECT ename,job,sal,deptno FROM emp;
3.对查询结果进行运算
# + - * /
查询每个员工的年薪
SELECT ename,12*sal FROM emp;
#员工的年终奖定为年薪的10%再加上500
SELECT ename,(12*sal)*0.1+500 FROM emp;
4.空值参与运算得到结果也是空值
#计算员工的年收入
SELECT ename,sal,comm,12*(sal+comm) FROM emp;
5.别名
#查询结果中可以给列起别名加以说明
/*
列名 别名
列名 AS 别名
别名中如果包含特殊字符（空格），需要加双引号
双引号只会在起别名的时候出现，其他的所有地方都只能用单引号而不能用双引号
*/
SELECT ename xingming,job zhiwei FROM emp;
SELECT ename AS xingming,job AS zhiwei FROM emp;
SELECT ename 姓名,job 职位 FROM emp;
SELECT ename "xing ming",job "zhi wei" FROM emp;
#别名主要用来标识运算结果
SELECT ename,(12*sal)*0.1+500 AS nianzhongjiang FROM emp;
6.表达式
#表达式根据数据类型的不同分为数字表达式、字符表达式和日期表达式
#手动输入的表达式可以写在select后面，表中有多少行，表达式就显示多少次
#手动输入数字表达式可以直接写，日期和字符表达式必须加上单引号
SELECT 123.456,12*60*48,'I am the king' FROM emp;
#mysql允许只写select，不写from;oracle必须要写select和from，不能缺少
#如果要查询的数据都是手动输入，与表中数据无关，可以不写from
SELECT 123.456,12*60*48,'I am the king';
SELECT 'who are you',ename FROM emp;
SELECT job,'JOB' FROM emp;
#手动字符串必须加单引号，mysql在windows平台字符串不区分大小写，但是在linux区分大小写
#其他的数据库包括编程语言，字符串都要区分大小写
7.distinct去重
SELECT job FROM emp;
SELECT DISTINCT job FROM emp;
SELECT DISTINCT deptno FROM emp;
#多列去重
SELECT DISTINCT job,deptno FROM emp;
#普通列不能和去重的列同时查询，因为行数不匹配，不能组成一张表
#select ename,distinct job from emp;
8.limit分页查询
SELECT * FROM emp;
#如果在查询结果中，想要得到固定某几行的结果，使用limit
#limit 写在查询语句最后
#limit m,n 表示从第m行开始显示n行数据，第一行的行号是0
SELECT * FROM emp
LIMIT 2,5;
#limit n 表示显示前n行数据
SELECT * FROM emp
LIMIT 3;
第三章 限定和排序
1.where子句限定范围
#where子句后面必须是一个完整的逻辑表达式，结果只有两种true或者false
#在where子句中，逻辑表达式通常是比较运算，比较条件是否满足
# = > < >= <= !=(<>)
#where子句限定条件通常是用表中的某一列数据作为条件，返回满足条件的数据
#查询20号部门的员工信息
SELECT * FROM emp
WHERE deptno = 20;
#查询工资高于2000的员工信息
SELECT * FROM emp
WHERE sal > 2000;
#查询职位不是manager的员工信息
SELECT * FROM emp
WHERE job != 'MANAGER';
#手动输入字符串要加单引号，最好在匹配的时候大小写一致
#查询年薪小于20000的员工信息
#列的别名不能出现在where子句中
SELECT ename,job,sal,12*sal nianxin,deptno FROM emp
WHERE 12*sal < 20000;
#其他的数据库不支持*加上其他列一起查询，mysql允许
SELECT *,12*sal nianxin FROM emp;
#where子句中的条件也可以是两列数据相互比较
SELECT * FROM emp
WHERE sal < comm;
#where子句中作为条件的列可以不出现在select后面，但是这样的语句结果不够直观
SELECT ename,job,deptno FROM emp
WHERE sal <= 1500;
#where子句中比较运算符的两端数据类型必须一致
#如果是其他数据库，数据类型不一致时会直接报错
#mysql由于是弱数据类型的语言，所以不会报错，只是这样的语句没有意义
SELECT * FROM emp
WHERE ename > 2;
#如果没有数据满足条件，则一行数据都没有
SELECT * FROM emp
WHERE deptno > 200;
#如果有满足条件的数据，至少会返回一行，只是可能数据的值是空值
SELECT comm FROM emp
WHERE ename = 'SMITH';
2.常用的特殊比较运算
#in(value1,value,2...valueN)， 列表匹配
SELECT * FROM emp
WHERE job IN ('MANAGER','CLERK','PRESIDENT');
#between 最小值 and 最大值，范围匹配，最大值和最小值的位置不能变
SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000;
#like, 模糊匹配（对字符）
#通配符，可以匹配或者代表其他字符的特殊符号
# _ 表示1个任意字符
# % 表示任意个任意字符，包括0个1个和多个
SELECT * FROM emp
WHERE ename LIKE 'S%';
SELECT * FROM emp
WHERE ename LIKE '%S';
SELECT * FROM emp
WHERE ename LIKE '%A%';
SELECT * FROM emp
WHERE ename LIKE '_A%';
SELECT * FROM emp
WHERE ename LIKE '_____';
#is null, 匹配空值（不能写成 =null）
SELECT * FROM emp
WHERE comm IS NULL;
3.逻辑运算
#and 与运算，所有的条件都要满足才能返回结果
SELECT * FROM emp
WHERE deptno = 20
AND sal > 2000;
#or 或运算，多个条件满足任意一个就能返回结果
SELECT * FROM emp
WHERE deptno = 20
OR sal > 2000;
#not 非运算，返回不满足条件的结果
SELECT * FROM emp
WHERE NOT sal > 2000;
#如果是数学符号的比较运算，not必须写在最前面
#如果是英文的比较运算，not可以写在最前也可以写在运算符中间
SELECT * FROM emp
WHERE deptno NOT IN (10,20);
4.运算优先级：数学运算>比较运算>NOT>AND>OR
#可以使用括号改变运算优先级
SELECT * FROM emp
WHERE deptno = 20
OR sal > 2000
AND job IN ('MANAGER','CLERK');
SELECT * FROM emp
WHERE (deptno = 20
OR sal > 2000)
AND job IN ('MANAGER','CLERK');
5.排序ORDER BY子句
#order by也在查询语句的后面，但是在limit之前
#asc 表示升序排列，默认可以不写
#desc 表示降序排列
SELECT * FROM emp
ORDER BY sal ASC;
#所有的数据类型都可以排序
SELECT * FROM emp
WHERE deptno = 20
ORDER BY hiredate DESC;
SELECT * FROM emp
ORDER BY ename;
#可以使用别名排序
SELECT *,12*sal nianxin FROM emp
ORDER BY nianxin DESC;
#多列排序
SELECT * FROM emp
ORDER BY deptno,sal DESC;
#多列排序时，有重复数据的列写在前面
#查询工资排名前三的员工信息
SELECT * FROM emp
ORDER BY sal DESC
LIMIT 3;
【练习】
1.查询薪水大于等于1500，且工作类别是SALESMAN的雇员信息
SELECT * FROM emp
WHERE sal >= 1500
AND job = 'SALESMAN';
2.查询年薪大于30000，工作类别不是MANAGER的雇员信息
SELECT *,12*sal nianxin FROM emp
WHERE 12*sal > 30000
AND job != 'MANAGER';
3.查询薪水在1500到3000之间，工作类别以“M”开头的雇员信息
SELECT * FROM emp
WHERE sal BETWEEN 1500 AND 3000
AND job LIKE 'M%';
4.查询佣金为空并且部门号为20或30的雇员信息(佣金=薪水SAL+津贴COMM)
SELECT *,sal+comm yongjin FROM emp
WHERE sal+comm IS NULL
AND deptno IN (20,30);
5.查询佣金不为空或者部门号为20的雇员信息，要求按照薪水降序排列 (佣金=薪水+津贴)
SELECT *,sal+comm yongjin FROM emp
WHERE sal+comm IS NOT NULL
OR deptno = 20
ORDER BY sal DESC;
6.查询年薪大于30000工作类别不是MANAGER，
且部门号不是10或40的雇员信息，要求按照雇员姓名进行排列
SELECT *,12*sal nianxin FROM emp
WHERE 12*sal > 30000
AND job != 'MANAGER'
AND deptno NOT IN (10,40)
ORDER BY ename;
7.查询30号部门工资排名前三的员工
SELECT * FROM emp
WHERE deptno = 30
ORDER BY sal DESC
LIMIT 3;
第四章 单行函数
#函数可以实现特定的功能或者运算，给函数输入数据，函数经过处理，得到相应的结果
f1(X)=3x+2
f1(4)=14
f2(X,Y)=5x+4y
f2(3,2)=23
#mysql中系统自带的单行函数，提供了数据特殊处理的方式
#可以协助我们快速对特定数据进行处理
#系统中的函数数量非常多，这里介绍一些常用函数
#单行数据针对表中的每一行数据分别进行处理或运算，得到相应的结果
#单行函数按照处理的数据类型分为：数值函数，字符函数，日期函数，逻辑函数
1.数值函数
#round(m,n) 将数字m四舍五入到小数点后n位
SELECT ROUND(123.456,1),ROUND(123.456,2),ROUND(123.456,0);
#n可以不写，默认精确到整数
SELECT ROUND(123.456);
#n可以为负数，精确到小数点前n位
SELECT ROUND(126.456,-1),ROUND(173.456,-2),ROUND(823.456,-3);
#truncate(m,n) 将数字m截取到小数点后n位
SELECT TRUNCATE(123.456,1),TRUNCATE(123.456,2),TRUNCATE(123.456,0);
#truncate必须两个参数都有，不能省略n
#n可以为负数，截取到小数点前n位
SELECT TRUNCATE(126.456,-1),TRUNCATE(173.456,-2),TRUNCATE(823.456,-3);
#floor(m) 向下取整，返回小于等于m的最大整数
#ceil(m) 向上取整，返回大于等于m的最小整数
SELECT FLOOR(23),FLOOR(23.45),FLOOR(-23.45);
SELECT CEIL(23),CEIL(23.45),CEIL(-23.45);
#mod(m,n) 计算m除以n的余数
SELECT MOD(12,5),MOD(123.45,5.7),MOD(12,0);
#rand() 生成一个0到1之间的随机数
SELECT RAND();
#要得到一个1到10之间的随机整数
SELECT ROUND(1+RAND()*9);
2.字符函数
#concat(str1,str2,...,strN) 将多个字符串连接在一起
SELECT CONCAT('abcdefg','xyz');
SELECT CONCAT(ename,job) FROM emp;
SELECT CONCAT(ename,' is a ',job) FROM emp;
#length(str) 计算字符串的长度
SELECT LENGTH('abcdefg');
SELECT ename,LENGTH(ename) FROM emp;
#函数处理过后的数据也可以写在where子句中作为条件
SELECT * FROM emp
WHERE LENGTH(ename)=4;
#left(str,n) 截取字符串最左边n个字符
#right(str,n) 截取字符串最右边n个字符
SELECT ename,LEFT(ename,2),RIGHT(ename,2) FROM emp;
#mid(str,m,n) 从字符串的第m个字符开始截取长度为n的字符串
SELECT ename,MID(ename,2,3) FROM emp;
#n可以不写，默认截取后面所有的字符
SELECT ename,MID(ename,3) FROM emp;
#m可以为负数，从右向左数第m个字符开始截取
SELECT ename,MID(ename,-2) FROM emp;
#replace(str1,str2,str3) 在str1中找到str2替换为str3
SELECT REPLACE('He love you','He','I');
SELECT ename,REPLACE(ename,'A','XXXXX') FROM emp;
3.日期函数
#日期的格式是'YYYY-MM-DD'
#时间的格式是'HH:MM:SS'
#查询1981年6月之前入职的员工信息
SELECT * FROM emp
WHERE hiredate < '1981-06-01';
#curdate() 返回当前系统日期，curtime 返回当前系统时间
SELECT CURDATE(),CURTIME();
#now() 返回当前系统日期+时间
SELECT NOW();
#year(date) 返回日期所在的年份，month(date) 返回日期所在的月份，day(date)返回日期的天数
SELECT *,YEAR(hiredate),MONTH(hiredate),DAY(hiredate) FROM emp;
#查看冬天入职的员工
SELECT * FROM emp
WHERE MONTH(hiredate) IN (10,11,12);
#DATE_ADD(date,interval n 单位) 给日期加上n个单位时间，单位可以是year，month，day
SELECT DATE_ADD(CURDATE(),INTERVAL -5 MONTH);
4.逻辑函数
#if(条件判断,value1,value2) 如果条件成立则返回value1，如果条件不成立则返回value2
#对员工的工资进行判断，员工工资大于等于3000的显示高富帅，其他显示矮矬穷
SELECT *,IF(sal>=3000,'高富帅','矮矬穷') FROM emp;
#对员工的工资进行判断，员工工资大于等于3000的显示高富帅，
#2000到3000之间的显示中产
#1000到2000之间显示小资
#1000以下矮矬穷
SELECT *,IF(sal>=3000,'高富帅',IF(sal>=2000,'中产',IF(sal>=1000,'小资','矮矬穷'))) FROM emp;
#ifnull(x,y) 如果x的值为空，则返回y，如果x不为空则返回x
SELECT *,12*(sal+IFNULL(comm,0)) nianshouru FROM emp;
/*
case x
when value1 then result1
when value2 then result2
...
when valueN then resultN
else resultN+1
end
判断x的值，如果x=value1则返回result1
如果x=value2则返回result2
……
如果x=valueN则返回resultN
如果以上值都不满足返回resultN+1
*/
#根据员工不同的职位涨不同幅度的工资
#clerk涨10%，salesman涨15%
#manager涨20%，其他人不变
SELECT *,
CASE job
WHEN 'CLERK' THEN sal*1.1
WHEN 'SALESMAN' THEN sal*1.15
WHEN 'MANAGER' THEN sal*1.2
ELSE sal
END addsal
FROM emp;
【作业】
1.用一个SQL语句完成在字符串”hello”左右各添加5个*，
使其最终返回*****hello*****(用多种方法实现)
SELECT CONCAT('*****','hello','*****');
SELECT RPAD(LPAD('hello',10,'*'),15,'*');
2.写一条SQL语句返回”abcdefg”的后三位字符“efg”(用多种方法实现)
SELECT RIGHT('abcdefg',3);
SELECT MID('abcdefg',-3);
SELECT MID('abcdefg',LENGTH('abcdefg')-2);
#trim(str1 from str2) 将str1从str2的两端删除
#可以添加leading/trailing/both both可以不写，默认从两边删除
SELECT TRIM(LEADING 'abcd' FROM 'abcdefg');
3.查询emp表，显示删除掉第一个字符后的员工姓名
（如员工ALLEN，显示为LLEN）（多种方法实现）
SELECT ename,MID(ename,2) FROM emp;
SELECT ename,RIGHT(ename,LENGTH(ename)-1) FROM emp;
SELECT ename,TRIM(LEADING LEFT(ename,1) FROM ename) FROM emp;
4.查询emp表，显示姓名中不包含字符’A’的员工信息
#locate(),position(),instr()
SELECT ename,INSTR(ename,'A') FROM emp;
SELECT * FROM emp
WHERE INSTR(ename,'A') = 0;
5.查询emp表，使用员工姓名的第一个字符将员工姓名从左边补齐到长度为10
SELECT LPAD(ename,10,LEFT(ename,1)) FROM emp;
6.查询emp表，显示下半年入职的员工信息
SELECT * FROM emp
WHERE MONTH(hiredate)>6;
7.查询在周一入职的员工信息
SELECT * FROM emp
WHERE WEEKDAY(hiredate)='MONDAY';
8.写一条SQL语句查询员工编号、员工姓名、工资、部门号，
要求当员工在10号部门时，显示’财务部’，20号部门时显示’研发部’，
30号部门时显示’销售部’，其余部门显示’未知部门’
SELECT empno,ename,sal,deptno,
CASE deptno
WHEN 10 THEN '财务部'
WHEN 20 THEN '研发部'
WHEN 30 THEN '销售部'
ELSE '未知部门'
END deptname
FROM emp;
第五章 多表查询
1.笛卡尔积
#查询员工的信息和其所在部门的信息
SELECT * FROM emp;
SELECT * FROM dept;
SELECT ename,job,sal,dname,loc FROM emp,dept;
#集合A中的所有元素和集合B中的所有元素，组成的有序对成为笛卡尔积，记作A×B
#在关系型数据库中，多表查询时如果没有连接条件，会产生笛卡尔积
#连接条件就是两张表之间数据的关系，是A表中的某几列和B表中的某几列数据的联系
#连接条件写在where子句中，通常为：表1.列1=表2.列2
#多表查询本质是先生成笛卡尔积，再在笛卡尔积中筛选满足条件的数据
#查询n张表，至少要有n-1个连接条件
2.多表查询的语法规范
#多表查询时，必须使用表名来限定所有列名，表名.列名
#1是为了避免重名的列报错
#2是为了提高查询效率
SELECT emp.ename,emp.job,emp.sal,emp.deptno,dept.dname,dept.loc FROM emp,dept;
#多表查询时，给表起别名，再使用表的别名来限定列名
#表的别名尽量简单，用一个字母能够区分多个表就可以
#多表查询时，先写from，from单独占一行
SELECT e.ename,e.job,e.sal,e.deptno,d.dname,d.loc
FROM emp e,dept d;
#多表查询时，如果查询结果中有同名的列，起不同的别名加以区分
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d;
3.等值连接
#两张表之间数据的关系是相等关系
#通常是指主键和外键的对应
#查询员工的信息和其所在部门的信息
SELECT e.ename,e.job,e.sal,e.deptno eno,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;
#如果多表查询时还有其他的普通限定条件，也写在where子句中，用and和连接条件连接
#查询工资高于2000的员工信息以及其所在的部门信息
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.sal > 2000;
4.不等值连接
SELECT * FROM salgrade;
#查询员工的信息和其所在的工资等级
SELECT e.*,s.*
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
5.自连接
#如果一张表的数据内部有联系，主键和外键在同一张表
#要将这样的关系体现出来使用自连接
#首先将一张表看作是两张表，再将两张表的主键和外键进行对应
#给同一张表起不同的别名，用不同的别名代表不同的表
#查询emp表中员工的姓名和其上级的姓名
SELECT w.ename,m.ename manager
FROM emp w,emp m
WHERE w.mgr = m.empno;
6.外连接
#在多表查询中，有的数据不满足连接条件，在内连接中是无法显示
#如果要显示这样的数据，使用外连接
#如果要显示左边表中不满足连接条件的数据，使用左外连接：left outer join
#如果要显示右边表中不满足连接条件的数据，使用右外连接：right outer join
#查询emp表中员工的姓名和其上级的姓名，没有上级的员工也显示
SELECT w.ename,m.ename manager
FROM emp w LEFT OUTER JOIN emp m
ON w.mgr = m.empno;
#查询员工信息和部门信息，没有员工的部门也显示
SELECT e.*,d.deptno dno,d.dname,d.loc
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
#全外连接full outer join，mysql暂时不支持
【练习】
1.查询EMP、DEPT表，输出的列包含员工姓名、工资、部门编号、部门名称、部门地址.
SELECT e.ename,e.sal,d.deptno,d.dname,d.loc
FROM emp e,dept d
WHERE e.deptno = d.deptno;
2.查询工资等级为3/4/5级的员工姓名，工资，工资等级
SELECT e.ename,e.sal,s.grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
AND s.grade > 2;
3.显示职位是CLERK的员工姓名，工资，工资等级，部门名称
SELECT e.ename,e.job,e.sal,s.grade,d.dname
FROM emp e,dept d,salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
AND e.job = 'CLERK';
4.查询emp表，显示员工姓名及其经理的姓名，没有经理的员工也需要显示
SELECT w.ename,m.ename manager
FROM emp w LEFT OUTER JOIN emp m
ON w.mgr = m.empno;
5.列出EMP表中部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname,e.*
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
第六章 多行函数
1.多行函数
#针对多行数据进行运算，只得到一个结果，就是多行函数
#又称为分组函数，或聚合函数
#sum() 求和
SELECT SUM(sal) FROM emp;
#avg() 求平均值
SELECT AVG(sal) FROM emp;
#sum和avg都只能对数字进行运算
#count() 计数（行数）
SELECT COUNT(ename) FROM emp;
SELECT COUNT(*) FROM emp;
#max() 最大值，min() 最小值
SELECT MAX(sal),MIN(sal) FROM emp;
SELECT MAX(hiredate),MIN(hiredate) FROM emp;
SELECT MAX(ename),MIN(ename) FROM emp;
#分组函数在计算时会忽略空值
SELECT AVG(comm),COUNT(comm) FROM emp;
SELECT SUM(comm)/14,SUM(comm)/4 FROM emp;
#如果要考虑空值，使用ifnull转换
SELECT AVG(IFNULL(comm,0)) FROM emp;
#可以使用where子句去限定分组函数计算的范围
#找出职位是salesman的员工的最高工资
SELECT MAX(sal) FROM emp
WHERE job = 'SALESMAN';
#找出10号部门的平均工资
SELECT AVG(sal) FROM emp
WHERE deptno = 10;
#oracle中普通列和分组函数是不能直接一起查询的
#mysql虽然允许这样的语句执行，但是结果没有意义
#select ename,count(sal) from emp;
2.group BY子句
#查询每个部门的平均工资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno;
#select后面既有普通列又有分组函数时，必须写group by子句
#并且出现在select后面的普通列必须也在group by后面
#查询每个职位的人数
SELECT job,COUNT(*) FROM emp
GROUP BY job;
#可以多列分组
#查询每个部门各个职位的最低工资
SELECT deptno,job,MIN(sal) FROM emp
GROUP BY deptno,job;
3.having子句
#查询部门人数多于5人的部门编号
/*
select deptno,count(*) from emp
where count(*) > 5
group by deptno;
where子句中不能出现分组函数
*/
#要对分组函数的结果进行限定，使用having子句
#having必须在group by后面
#使用group by可以不用having，但是使用having必须使用group by
SELECT deptno,COUNT(*) FROM emp
GROUP BY deptno
HAVING COUNT(*) > 5;
【练习】
#1.查询每个职位的职位名称及平均工资，按平均工资升序排列.
SELECT job,AVG(sal) FROM emp
GROUP BY job
ORDER BY AVG(sal);
#2.查询每个部门的各个职位的平均工资，并按部门编号升序、平均工资降序排序
SELECT deptno,job,AVG(sal) FROM emp
GROUP BY deptno,job
ORDER BY deptno,AVG(sal) DESC;
3.查询每个工资等级所对应的人数
SELECT s.grade,COUNT(e.ename)
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
GROUP BY s.grade;
4.查询EMP表，要求写一条SQL语句统计出如下图所示结果
10号部门人数 20号部门人数 30号部门人数
------------ ------------ ------------
3 5 6
SELECT SUM(deptno=10) dept10,
SUM(deptno=20) dept20,
SUM(deptno=30) dept30 FROM emp;
SELECT sal>2000 FROM emp;
第七章 子查询
#查询工资高于allen的员工信息
SELECT sal FROM emp
WHERE ename = 'ALLEN';
SELECT * FROM emp
WHERE sal > 1600;
#当查询语句的条件不是客观数据，而是表中的数据时，使用子查询
#子查询又叫做嵌套查询，将一个查询的结果作为另一个查询的条件
#子查询写在主查询中，必须使用括号表示运算顺序
SELECT * FROM emp
WHERE sal >
(SELECT sal FROM emp WHERE ename = 'ALLEN') ;
#习惯上，将子查询写在比较运算的右边，而且子查询单独占一行
#主查询中作为条件的列，必须和子查询的结果数据类型一致
1.单行子查询
#子查询的结果如果是一行一列数据，就是单行子查询
#查询跟allen的部门和职位都一样的员工信息
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN')
AND job =
(SELECT job FROM emp WHERE ename = 'ALLEN');
#主查询和子查询的数据可以不在同一张表
#查询allen所在的部门名称
1）多表查询
SELECT d.dname
FROM emp e,dept d
WHERE e.deptno = d.deptno
AND e.ename = 'ALLEN';
#多表查询先生成笛卡尔积，再在笛卡尔积中找满足条件的数据
#一共找了14*4=56次
2)子查询
SELECT dname FROM dept
WHERE deptno =
(SELECT deptno FROM emp WHERE ename = 'ALLEN');
#先执行子查询，找了14次，再在主查询中查询，找了4次
#一共找了14+4=18次
#子查询的效率高于多表查询
#如果查询结果来自多张表，只能使用多表查询
#如果查询结果来自一张表，只是用到另一张表的数据作为条件
#可以用子查询也可以用多表查询，推荐使用子查询
#子查询中可以使用分组函数
#查询emp表中工资最高的员工姓名
SELECT ename FROM emp
WHERE sal =
(SELECT MAX(sal) FROM emp);
#having子句中可以使用子查询
#查询部门的平均工资高于所有员工平均工资的部门
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal) >
(SELECT AVG(sal) FROM emp);
2.多行子查询
#子查询的结果如果是多行一列数据，就是多行子查询
#多行子查询必须使用多行比较运算符
#in 匹配多个值
#查询工资大于等于3000的员工所在的部门名称
SELECT dname FROM dept
WHERE deptno IN
(SELECT DISTINCT deptno FROM emp WHERE sal >= 3000);
#any，多个条件满足其中任意一个就可以返回结果
#查询工资高于任意一个部门的平均工资的员工信息
SELECT * FROM emp
WHERE sal > ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);
#大于any表示大于最小值，小于any表示小于最大值
#all，多个条件必须全部满足才能返回结果
SELECT * FROM emp
WHERE sal > ALL
(SELECT AVG(sal) FROM emp GROUP BY deptno);
#大于all表示大于最大值，小于all表示小于最小值
3.多列子查询
#子查询的结果如果是多行多列数据，就是多列子查询
#in 匹配多列的值
#查询跟allen的部门和职位都一样的员工信息
SELECT * FROM emp
WHERE (deptno,job) IN
(SELECT deptno,job FROM emp WHERE ename = 'ALLEN');
#主查询中列的数量、顺序以及数据类型必须和子查询中的列一致
#子查询的结果可以看作是一张表，再进行查询
#此时子查询写在from后面，必须起别名
SELECT * FROM
(SELECT ename,job,sal,comm,12*sal nianxin,sal+IFNULL(comm,0) yongjin FROM emp) e
WHERE yongjin > 2000;
#先用一个查询语句构造出一张表，再和其他表做多表查询
【作业】
1.使用子查询，找出哪个部门下没有员工
SELECT * FROM dept
WHERE deptno NOT IN
(SELECT DISTINCT deptno FROM emp);
2.使用子查询，列出领导是BLAKE的所有员工
SELECT * FROM emp
WHERE mgr =
(SELECT empno FROM emp WHERE ename = 'BLAKE');
3.使用子查询，列出在NEW York工作的员工信息
SELECT * FROM emp
WHERE deptno IN
(SELECT deptno FROM dept WHERE loc = 'NEW YORK');
4.使用子查询，找出那些工资低于平均工资的员工
SELECT * FROM emp
WHERE sal <
(SELECT AVG(sal) FROM emp);
5.使用子查询，找出那些工资低于任意一个部门的平均工资的员工
SELECT * FROM emp
WHERE sal < ANY
(SELECT AVG(sal) FROM emp GROUP BY deptno);
6.使用SQL语句查出各个部门工资最高的员工的部门编号、员工姓名及其工资的信息
SELECT deptno,ename,sal FROM emp
WHERE (deptno,sal) IN
(SELECT deptno,MAX(sal) FROM emp GROUP BY deptno);
7.列出所有部门的相应信息和部门人数，没有员工的部门则部门人数显示为0
1)多表查询
SELECT d.*,COUNT(e.ename)
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.deptno,d.dname,d.loc;
2)子查询+多表查询
#部门信息在dept表可以直接查询
#部门人数不在任何一张表，但是可以通过emp表算出来
#先使用查询语句计算出部门人数，再将该结果看作一张表跟dept表做多表查询
SELECT deptno,COUNT(ename) ct FROM emp
GROUP BY deptno;
SELECT d.*,IFNULL(c.ct,0)
FROM dept d LEFT OUTER JOIN
(SELECT deptno,COUNT(ename) ct FROM emp
GROUP BY deptno) c
ON d.deptno = c.deptno;
SELECT sal FROM emp
WHERE deptno = 40;
SELECT AVG(sal) FROM emp
WHERE deptno = 40;
SELECT COUNT(sal) FROM emp
WHERE deptno = 40;
8.查询高于自己部门平均工资的员工名字,部门号,工资,平均工资（保留2位小数）
#先使用查询语句计算出每个部门的平均工资
SELECT deptno,AVG(sal) average FROM emp
GROUP BY deptno;
SELECT e.ename,e.deptno,e.sal,ROUND(a.average,2)
FROM emp e,
(SELECT deptno,AVG(sal) average FROM emp
GROUP BY deptno) a
WHERE e.deptno = a.deptno
AND e.sal > a.average;
第八章 集合操作
#集合操作是指，将查询结果看作是一个集合，集合与集合之间可以做集合操作
#集合操作：合集、交集、差集、补集
#mysql中只支持合集操作，union，union all
#集合操作的效率高于条件组合的查询效率
#进行集合操作的查询语句，查询的列必须一致
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;
#union all会显示重复数据
SELECT ename,job,sal,deptno FROM emp
WHERE deptno = 20
UNION ALL
SELECT ename,job,sal,deptno FROM emp
WHERE sal < 2000;
网友评论