1. SQL查询

1. SQL查询

1.1. 基本格式

select 
    [all|distinct]
    <目标列或表达式> [别名], 
    <目标列或表达式> [别名], 
    ...
from 
    <表名或视图名> [别名],
    <表名或视图名> [别名],
	...
[where <条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名>] [asc|desc]
[limit offset,rows]

1.1.1. SQL查询顺序

在现代世界中，SQL查询规划者（SQL query planners ）可以使用各种技巧来提高查询的运行效率，但它们必须始终达到与按照标准SQL执行顺序执行的查询相同的最终答案。该命令是：参考：https://www.sisense.com/blog/sql-query-order-of-operations/

1.2. 简单查询

-- 查询所有列
select * FROM product;

-- 查询指定列
SELECT  pname,price FROM product;

-- 表别名
select * from product as p;

-- 列别名
SELECT  pname as 'product_name',price 'price' FROM product;

-- 去重复列，返回的是对应的列
SELECT DISTINCT price FROM product;

-- 对查询列进行运算
SELECT price+50 from product;

1.3. 条件查询

-- 查询价格大于等于1000的商品
select *from product where price>=1000;

-- 查询价格在100-1500之间的商品
select *from product where price BETWEEN 100 and 1500;

-- 查询价格不在100-1500之间的商品
select *from product where price NOT BETWEEN 100 and 1500;

-- 查询价格为3000或5000的商品
select *from product where price IN(3000,5000);
select *from product where price=3000 OR price=5000;
select *from product where price=3000 || price=5000;

-- 查询价格不是3000且不是5000的商品
select *FROM product where price NOT IN(3000,5000);

-- 查询商品名称带有'洗衣机'的商品
select *FROM product where pname LIKE '%洗衣机%';

-- 查询商品名第二个字为'力'的商品
select * FROM product where pname LIKE '_力%';

-- 查询商品目录id为Null的商品
SELECT *FROM product where category_id IS NULL;

-- 查询价格在1000-5000之间的洗衣机
select * FROM product where (pname LIKE '%洗衣机%') AND (price BETWEEN 1000 AND 5000);

1.4. 排序查询

ORDER BY 字段列表或表达式
asc：ascend，升序
desc：descend，降序

-- 按照价格降序排列
SELECT * FROM product ORDER BY price desc;

-- 按照分类id升序，价格降序
SELECT *FROM product order by category_id asc,price desc; 

1.5. 聚合函数

函数	描述	备注
COUNT([DISTINCT] expr1,expr2,…)	统计参数不为Null的个数
SUM([DISTINCT] expr)	对参数求和	忽略NULL值
MIN([DISTINCT] expr)	求参数的最小值	忽略NULL值
MAX([DISTINCT] expr)	求参数最大值	忽略NULL值
AVG([DISTINCT] expr)	求参数平均值	忽略NULL值

1.5.1. Example

-- 统计商品条目
select count(*) FROM product;

-- 统计商品分类的数量
select COUNT(DISTINCT category_id) FROM product; 

-- 查询所有商品总价
select SUM(price) from product;

-- 查询最低价格，最高价格
select MIN(price),MAX(price) from product; 

-- 查询所有商品均价
SELECT AVG(price) from product;

1.6. 分组查询

关键字：GROUP BY、HAVING

GROUP BY：将所有的行根据指定的字段的值分组，相同为一组。后面写分组字段列表，以”,”分隔
如：GROUP BY category_id
HAVING：对分组后的结果进行筛选，后面跟的是条件表达式
如：HAVING COUNT(*)>=10

1.6.1. Example

-- 查询每个商品分类的商品总量
SELECT category_id,COUNT(*) from product GROUP BY category_id;

-- 相同分类、相同价格为一组，求这样的分组的每个组的商品数量
SELECT category_id,price FROM product GROUP BY category_id,price;

-- 相同分类、相同价格为一组，求这样的分组的每个组的商品数量且筛选出平均价格在1000以下的分组
SELECT category_id,price,count(*) FROM product GROUP BY category_id,price HAVING AVG(price)<=1000;

注意

GROUP BY后面可以有多个分组字段
使用了分组关键词后，SELECT后面只能跟分组字段或者聚合函数。
GROUP BY一般配合聚合函数使用才有意义。
HAVING后面跟表达式，表达式的操作数可以是分组字段或聚合函数。

1.7. LIMIT查询

用法：LMIMIT [offset,]rows，表示从第offset条记录开始，往后取ROWS条记录并返回，offset不写则默认为0。

1.7.1. Example

-- 查询前10个商品
SELECT * FROM product LIMIT 10;

-- 查询第10-20个商品
SELECT * FROM product LIMIT 10,20;

1.8. 练习-单表查询

脚本：emp.sql

练习：

  -- 不在部门号为10的员工的信息
  SELECT * FROM emp WHERE deptno!=10;

  -- 姓名第二个字母不是'A'且薪水大于1000元的员工，按年薪降序（年薪=12*sal+comm）
  SELECT *FROM emp WHERE ename NOT LIKE '_A%' && sal>1000
  ORDER BY (sal*12+IFNULL(comm,0)) DESC;

  -- 求每个部门的平均薪水
  SELECT deptno,AVG(sal) as avg_sal FROM emp 
  GROUP  BY deptno
  ORDER BY avg_sal;

  -- 求每个部门的最高薪水
  SELECT deptno,MAX(sal) as max_sal FROM emp 
  GROUP  BY deptno
  ORDER BY max_sal;

  -- 求相同部门和相同岗位的员工的人数和平均薪水
  SELECT deptno,job,AVG(sal) as avg_sal,COUNT(*) as number_of_employee  FROM emp
  GROUP BY deptno,job
  ORDER BY avg_sal DESC;

  -- 平均薪水大于2000的部门编号和平均薪水
  SELECT deptno,AVG(sal) FROM emp
  GROUP BY deptno
  HAVING AVG(sal)>2000; 

  -- 有奖金的员工信息
  SELECT *FROM emp WHERE comm IS NOT NULL;

  -- 所有员工最高薪水和最低薪水的差
  SELECT MAX(sal)-MIN(sal) FROM emp;

1.9. 多表查询

在数据库中，将所有数据都存在一张表中是不明智的，造成的隐患主要有：

数据冗余度高
数据独立性差，不易扩展
单表数据量过大，查询性能降低。

所以为了解决这些问题，一般会设计多张表存储数据。

在数据库中，E-R模型（实体-关系模型）被广泛用来数据建模，因此在数据库设计时，会根据实体间的关系来对数据库表进行设计，实体间的关系主要有3种：

一对一
一对多
多对多

一对一和一对多关系的两个实体，一般就是两张表，一个实体一张表。
多对多关系的两个实体，一般会用到3张表，2张表用来存放两个实体各自的记录，还有一张中间表用来存放两个实体的联系，一般有两个字段分别对两个实体的记录进行引用来实现多对多的关系。

1.9.1. 种类

交叉连接（cross join）
select *from A,B，此种方式产生两个集合的笛卡尔积
内连接
- 显式
  select *from A [INNER] JOIN B ON 条件
- 隐式
  select *FROM A,B WHERE 条件
外连接
- 左外连接
  select *FROM A LEFT [OUTER] JOIN B ON 条件
- 右外连接 select *FROM A RIGHT [OUTER] JOIN B ON 条件
完全外连接 select *FROM A FULL [OUTER] JOIN B ON 条件
注意：mysql不支持FULL JOIN，可以对左外连接和右外连接的结果进行UNION操作，结果和全外连接一样：
```
 select *FROM A LEFT [OUTER] JOIN B ON 条件
 UNION
 select *FROM A RIGHT [OUTER] JOIN B ON 条件
```
- UNION是求两个关系的并集，同时还会去除两个集合中相同的元素。
- UNION ALL也是求并集，但不会去重。
子查询（嵌套select）
子查询就是将查询的结果作为数据源再进行查询。子查询有一些关键字，用于子查询的结果是多行单列作为条件的情况：
- ALL
- ANY/SOME，SOME是ANY的别名（alias），因此这两个关键字作用一样
除此之外，IN和EXISTS也可以用于这种情况。
- EXISTS不关心子查询结果的具体值，只关心有没有结果。在子查询中如果用到EXISTS，查询的顺序是：对外表进行loop，拿外部查询的每条记录去子查询中验证，如果子查询结果有元组，EXISTS会返回true，那就保留外部查询的这条元组，否则抛弃。
- IN关心子查询的结果
事实上IN和EXISTS的语义相似，都有“如果存在就保留记录”的意思，但由于它们的执行过程不同，效率也不同，分析它们的区别，需要从它们两个的执行原理入手。
- 对于使用IN的子查询，是先执行子查询再执行外部查询，如果子查询的结果是固定的，会被缓存（在内存），此后对外表进行循环操作，根据IN的返回值判断是否保留外部查询的字段。
- 对于使用EXISTS的子查询，是先执行外部查询再根据外部查询的结果去子查询中查询（每次都要进行数据库查询），如果子查询有结果，那就保留外部查询的字段，否则丢弃。
对于这种广泛的查询场景：要查询A表字段，依赖于子查询B表的查询结果：
- 当A表数据量远大于B表，使用IN关键字
- 当A表数据量远小于B表，使用EXISTS关键字

表自关联某张表的字段互为主键和外键，即表中的元组参考于表中的另外某条元组。

Example：

create table t_sanguo(
 eid int primary key ,
 ename varchar(20),
 manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
 );
 insert into t_sanguo values(1,'刘协',NULL);
 insert into t_sanguo values(2,'刘备',1);
 insert into t_sanguo values(3,'关羽',2);
 insert into t_sanguo values(4,'张飞',2);
 insert into t_sanguo values(5,'曹操',1);
 insert into t_sanguo values(6,'许褚',5);
 insert into t_sanguo values(7,'典韦',5);
 insert into t_sanguo values(8,'孙权',1);
 insert into t_sanguo values(9,'周瑜',8);
 insert into t_sanguo values(10,'鲁肃',8);


 -- 查询每个人和其直接上级，没有上级的也要显示
 SELECT a.*,b.eid,b.ename 上级 FROM t_sanguo AS a LEFT JOIN t_sanguo AS b ON a.manager_id=b.eid;

 -- 查询每个人和直接上级，以及直接上级的上级
 SELECT a.*,b.ename,c.ename
 FROM t_sanguo AS a 
     LEFT JOIN t_sanguo AS b ON a.manager_id=b.eid 
     LEFT JOIN t_sanguo AS c ON b.manager_id=c.eid
 ;

1.10. 练习-多表查询

SQL脚本：多表查询练习.sql
练习

-- 返回拥有员工的部门的部门号和名称
SELECT *FROM dept D WHERE EXISTS (SELECT *FROM emp E WHERE D.deptno=E.deptno); 

-- 工资比员工'SMITH'高的员工
SELECT *FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='SMITH');

-- 查询员工和所属上级
SELECT * FROM emp as E JOIN emp as M ON E.mgr=M.empno;

-- 查询雇佣日期早于其上级的员工
SELECT * FROM 
	emp as E JOIN emp as M ON E.mgr=M.empno
	WHERE E.hiredate<M.hiredate;

-- 查询员工和其部门的名称
SELECT  E.ename,D.dname FROM
	emp AS E LEFT JOIN dept D ON E.deptno=D.deptno

-- 查询职位为'CLERK'的员工和其部门
SELECT  * FROM
	emp E LEFT JOIN dept D ON E.deptno=D.deptno
	WHERE E.job='CLERK';

-- 查询各部门的最低工资，包括部门号、部门名称、该部门最低工资
SELECT D.deptno,D.dname,MIN(E.sal) FROM
	emp E LEFT JOIN dept D ON E.deptno=D.deptno
	GROUP BY E.deptno;
	
-- 查询销售部的所有员工信息
SELECT  E.* FROM
	emp E LEFT JOIN dept D ON E.deptno=D.deptno
	WHERE dname='SALES';

-- 工资比平均工资高的员工
SELECT *FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);

-- 查询和'SCOTT'相同岗位的员工
SELECT *FROM emp WHERE ename!='SCOTT' AND job=(SELECT job FROM emp WHERE ename='SCOTT');

-- 查询工资高于'SALES'部门的平均工资的员工
SELECT *FROM emp 
	WHERE sal>(SELECT AVG(sal) FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE dname='SALES');

-- 查询所有有员工的岗位和其最低薪资
SELECT job,MIN(sal) min_sal FROM emp GROUP BY job;

-- 查询员工和其年薪，按年薪降序
SELECT  *,sal*12+IFNULL(comm,0) AS annual_salary FROM emp ORDER BY annual_salary DESC ;

-- 查询薪资是级别4的员工
SELECT *FROM emp 
	WHERE sal BETWEEN (SELECT losal FROM salgrade WHERE grade=4)
			AND (SELECT hisal FROM salgrade WHERE grade=4)
;
SELECT *FROM emp E JOIN salgrade S
	ON S.grade=4 && (E.sal BETWEEN S.losal AND S.hisal)

-- 查询薪资级别2的员工及其部门所在地(loc)
SELECT  E.*,D.loc
FROM emp E JOIN dept D ON E.deptno=D.deptno
					JOIN salgrade S ON S.grade=2 && (E.sal BETWEEN S.losal AND S.hisal)
;

SQL查询