文章目录
01. 数据准备02. 选择语句 select03. 排序检索数据04. 过滤数据05. 高级数据过滤06. 用通配符进行过滤07. 聚合函数08. 分组数据09. 内连接10. 外连接11. 三张表连接12. 子查询01. 数据准备
drop table if exists dept;drop table if exists salgrade;drop table if exists emp;create table dept(deptno int(10) primary key,dname varchar(14),loc varchar(13));create table salgrade(grade int(11),losal int(11),hisal int(11));create table emp(empno int(4) primary key,ename varchar(10),job varchar(9),mgr int(4),hiredate date,sal double(7,2),comm double(7,2),deptno int(2));insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');insert into salgrade(grade,losal,hisal) values(1,700,1200);insert into salgrade(grade,losal,hisal) values(2,1201,1400);insert into salgrade(grade,losal,hisal) values(3,1401,2000);insert into salgrade(grade,losal,hisal) values(4,2001,3000);insert into salgrade(grade,losal,hisal) values(5,3001,5000);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7369,'SIMITH','CLERK',7902,'1980-12-17',800,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,null,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);select * from dept;select * from salgrade;select * from emp;
02. 选择语句 select
(1) 检索单个列
SELECT语句从emp表中检索一个名为e_name的列。所需的列名写在SELECT关键字之后，FROM关键字指出从哪个表中检索数据
select e_name from emp;
(2) 检索多个列
SELECT关键字后给出多个列名，列名之间必须以逗号分隔。
select empno,ename from emp;
(3) 检索所有列
select * from emp;
(4) 检索结果去重
select distinct deptno from emp;
distinct必须放在检索字段的前面，作用在多列上时，只有job和deptno都相同的数据才会被过滤掉。
select distinct job,deptno from emp;
(5) 限制结果
limit指定返回的行数；offset指定从第几行开始，offset默认为0，offset=0时代表第1行数据。
# 从第1行开始的2行数据，即前两行数据（加上第1行）select ename from emp limit 2;
# 从第6行开始的2行数据（加上第6行）select first_name from customers limit 2 offset 5 ;
03. 排序检索数据
(1) 排序数据
select deptno from emp order by deptno;
(2) 按多个列排序
先按照job进行排序，如果job相同，才会按照deptno排序，如果job是唯一的，则deptno就不排序了。
select job,deptno from emp order by job,deptno;
(3) 按照列的位置排序
除了能用列名指出排序顺序外，ORDER BY还支持按相对列位置进行排序。
select job,deptno,ename from emp order by 2,3;
(4) 指定排序方向
如果没有指定排序方向，则默认是升序排序的，可以指定排序方式为降序排序：
select deptno from emp order by deptno desc;
注意：asc，desc关键字只应用到其前面的列名 。
先对job进行降序排序，再对deptno进行升序排序。因此，只有job相同的时候，deptno才会按照升序排序。因此如果想在多个列上进行降序排序，必须对每一列指定desc关键字。
select job,deptno from emp order by job desc,deptno asc;
04. 过滤数据
(1) 使用where子句
例：从emp表中检索两个列，但是不返回所有行，只返回deptno=20的行：
select empno,ename from emp where deptno=20;
注意：在同时使用order by和where子句时，应该让order by位于where之后，否则将会产生错误。
(2) where 子句操作符
SQL支持表中列出的所有条件操作符 ：
1、 检查单个值
例：从emp表中过滤出deptno>20的行：
select * from emp where deptno>20;
2、不匹配检查
例：从emp表中过滤出deptno!=20的行：
select * from emp where deptno!=20;select * from emp where deptno<>20;
3、范围值检查
例：从emp表中过滤出1500<sal<3000的所有行：
select * from emp where sal between 1500 and 3000;
从结果可以看出，between(1500,3000)既包括sal=1500，也包括sal=3000的数据。
4、空值检查
例：从emp表中过滤出comm不等于null的行。
select * from emp where comm is null;
注意：无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。
05. 高级数据过滤
(1) 组合where子句
1、and操作符
想要通过不止一个列过滤，可以使用and操作符给where子句附加条件。
例：从emp表中过滤出同时满足and前后条件的行。
select * from emp where sal=800 and deptno=20;
2、or操作符
例：从emp表中过滤出满足or前后任意一个条件的行 ：
3、求值顺序
where子句后面可以跟任意数量的and和or操作符，允许两者结合以进行复杂高级的过滤。SQL在处理or操作符之前会优先处理and操作符，换句话说and操作符的优先级更高。
例：从emp表中过滤出deptno>=20，且ename='CLARK’或ename='SIMITH’的行
从结果可以看出，返回的结果中有一行数据deptno<20，并没有按照预期的进行过滤，为什么呢？原因就是and的优先级比or的优先级要高。因此想要过滤出预期的结果，需要使用圆括号，因为圆括号的优先级比and和or要高，所以会首先过滤圆括号中的条件。
select * from emp where ( ename='clark' or ename='simith') and deptno>=20;
任何时候使用具有and和or操作符的where子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认求值顺序，即使它确实如你希望的那样。使用圆括号没有什么坏处，它能消除歧义。
(2) in 操作符
in操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。
例：从emp表中过滤出ename=clark或ename=simith的行
select * from emp where ename in ('clark','simith');
in操作符完成了与or操作符相同的功能
select * from emp where ename='clark' or ename='simith';
为什么要使用IN操作符？其优点如下:
在有很多合法选项时，IN操作符的语法更清楚，更直观。在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理。IN操作符一般比一组OR操作符执行得更快IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。
(3) not 操作符
WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件。因为NOT从不单独使用（它总是与其他操作符一起使用），所以它的语法与其他操作符有所不同。NOT关键字可以用在要过滤的列前，而不仅是在其后。
例：从emp表中过滤出deptno不等于10的行
select * from emp where not deptno=10;
06. 用通配符进行过滤
(1) like 操作符
为了了搜索子句中使用通配符，必须使用like操作符。通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。
1、百分号（%）通配符
在搜索串中，%表示任何字符出现任意次数。需要特别注意，除了能匹配一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
例：从emp表中过滤出ename以S开头的行
select * from emp where ename like 'S%';
例：从emp表中过滤出job包含ER的行，不论它之前或者之后出现什么字符
select * from emp where job like '%er%';
例：从emp表中过滤出job以M开头以R结束的行
select * from emp where job like '%er%';
注意：通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%’不会匹配产品名称为NULL的行。
2、下划线（_）通配符
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
select * from emp where ename like '_lark';
如上：下划线_匹配CLARK中的第一个字符C，注意_只能配置一个字符，不能多也不能少。
3、方括号（[]）通配符
方括号（[]）通配符用来指定一个字符集，匹配方括号中的任意一个字符。
例：从emp表中过滤出ename中包含E或者S的行
select * from emp where job regexp '[ES]';select * from emp where job rlike '[ES]';
结果可以看出，在MySQL中，[]属于正则模式，模糊查询的like不适用了，得使用 ’ rlike '或者 ‘regexp’关键词。
07. 聚合函数
(1) 聚集数据
常用聚集函数
1、avg() 函数
AVG()通过对表中行数计数并计算其列值之和，求得该列的平均值。AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。
例：计算emp表中所有员工的平均薪水sql
select avg(sal) as sal_avg from emp;
例：过滤出deptno=20的员工，并计算这些员工的平均薪水sal
select avg(sal) as sal_avg from emp where deptno=20;
注意：avg()函数会忽略列值为null的行。
2、count() 函数
COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。
COUNT()函数有两种使用方式：
使用COUNT(＊)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
例：利用count(*) 计算emp表的所有行数，不管各列有没有null值
select count(*) as count from emp;
例：利用count(列名) 计算emp表的所有行数，会忽略指定列为null的行
select count(comm) from emp;
3、max() 函数
返回指定列的最大值，max()函数要求指定列名
select max(sal) as sal_max from emp;
注意：max()函数会忽略列值为null的行
4、min() 函数
MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名。
select min(sal) as sal_min from emp;
5、sum() 函数
SUM()用来返回指定列值的和（总计）
select sum(sal) sal_sum from emp;
(2) 聚集不同值
以上5个聚集函数都可以如下使用：
对所有行执行计算，指定ALL参数或不指定参数（因为ALL是默认行为）。只包含不同的值，指定DISTINCT参数。
select avg (distinct sal) sal_ave from emp;
使用distinct后，将对sal去重后再计算平均价格
(3) 组合聚集函数
目前为止的所有聚集函数例子都只涉及单个函数。但实际上，SELECT语句可根据需要包含多个聚集函数。
select count(*) count,min(sal) min_sal,max(sal) max_sal,avg(sal) avg_sal from emp;
08. 分组数据
(1) 创建分组
使用分组可以将数据分为多个逻辑组，对每个组进行聚集计算，分组是使用SELECT语句的GROUP BY子句建立的。
select deptno, count(*) count from emp group by deptno;
group by子句会根据deptno进行分组，然后对每个组而不是整个结果集进行聚集。
group by子句中列出的每一列都必须是检索列或者有效表达式，但是不能是聚集函数！group by后，select中检索的只能是聚集函数，或者group by中分组的字段！如果分组列中包含具有null值的行，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。group by子句必须出现在where子句之后，order by子句之前。
注意：上面这样是错误的！！！，因为出现了一对多的情况，即deptno=10，不仅仅有ename=CLARK
(2) 过滤分组
除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。
WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。
例：按照deptno进行分组后，过滤出count(*)>=4的组
where在数据分组前进行过滤，having在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。
有没有在一条语句中同时使用WHERE和HAVING子句的需要呢？
select deptno,count(*) from emp where sal>2000 group by deptno having count(*)>=3;
上面SQL语句的执行过程：
先从emp表中过滤出sal>2000的数据再将步骤1查询出来的数据按照deptno进行分组然后对步骤2分组后的数组进行过滤，过滤出组内数据行数大于等于3的组
HAVING与WHERE非常类似，如果不指定GROUP BY，则大多数DBMS会同等对待它们。不过，你自己要能区分这一点。使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。
(4) 分组与排序
一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。
select deptno,count(*) from emp where sal>1000 group by deptno order by deptno desc;
(5) select 子句顺序
select 字段 5from表名 1where 查询条件2group by 分组 3having 分组后过滤 4order by 排序 6
09. 内连接
内连接：查询的是A表和B表匹配上的信息
外连接：A表和B表有主副之分，主要查主表，顺便查副表
内连接语法：select 字段from 表1join 表2on 表连接条件where 过滤条件
(1) 内连接之等值连接
例：找出每一个员工的部门名称，要求显示员工名和部门名
select ename,dname from emp join dept on emp.deptno=dept.deptno;
(2) 内连接之非等值连接
例：找出每个员工的工资等级，显示员工名，工资，工资等级
select ename,sal,grade from emp join salgrade on sal between losal and hisal;select ename,sal,grade from emp join salgrade on sal>=losal and sal<=hisal;
(3) 内连接之自连接
例：找出每个员工的上级领导，要求显示员工名和对应的领导名
select e.ename ename,m.ename mgrname from emp e join emp m on e.mgr=m.empno;
10. 外连接
左外连接和右外连接，可以相互装换
可以看到KING这个员工是没有领导的，使用自连接时只有13条记录，但是总共有14个员工。
例：找出每个员工的上级领导
select e.ename,m.ename from emp e left join emp m on e.mgr=m.empno;
例：找出哪个部门没有员工
select d.dname,e.ename from dept d left join emp e on d.deptno=e.deptno;
11. 三张表连接
多表连接语法：select 字段from A表join B表on表连接条件join C表on表连接条件
例：找出每个员工的部门名称和工资等级
select e.ename,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between losal and hisal;
12. 子查询
子查询：select后面嵌套select语句，被嵌套的语句就是子查询
select (select) // select后面嵌套子查询from (select) // from后面嵌套子查询where (select) // where后面嵌套子查询
(1) where 后面嵌套子查询
例：找出高于平均薪资的员工信息
select * from emp where sal > (select avg(sal) from emp);
(2) from后面嵌套子查询
例：找出每个部门平均薪水的薪资等级
select s.deptno, s.grade from (select deptno,avg(sal) sal_avg from emp group by deptno) s join salgrade s on s.sal_avg between losal and hisal;
例：找出每个部门平均的薪水等级
注意：一开始这样查询的，但是查询效率很低
select a.deptno deptno, avg(a.grade) avg_grade from (select e.ename,e.deptno ,s.grade from emp e join salgrade s on e.sal between losal and hisal) a group by a.deptno ;
直接查询出来所有员工的员工等级，然后直接分组，分组后，分组函数会作用于组内函数而不是整个结果集！
select deptno,avg(s.grade) from emp e join salgrade s on e.sal between losal and hisal group by deptno;
