MySQL语法--03--查询select

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

查询 select

判断条件，子查询，统计查询，聚合，分组，表的关联

案例表格 emp

案例表格 dept

sql

CREATE TABLE emp(
  empno NUMERIC(4) NOT NULL,
  ename VARCHAR(10),
  job VARCHAR(9),
  mgr NUMERIC(4),
  hiredate DATE,
  sal NUMERIC(7,2),
  comm NUMERIC(7,2),
  deptno NUMERIC(2)
);

INSERT INTO emp VALUES(100,'jack','副总',NULL,'2002-05-03',90000,NULL,1);
INSERT INTO emp VALUES(200,'tony','总监',100,'2015-02-02',10000,2000,2);
INSERT INTO emp VALUES(300,'hana','经理',200,'2017-02-02',8000,1000,2);
INSERT INTO emp VALUES(400,'leo','员工',300,'2019-02-22',3000,200.12,2);
INSERT INTO emp VALUES(500,'liu','员工',300,'2019-03-19',3500,200.58,2);

7.11.2	部门表 dept

Mysql:
CREATE TABLE dept(
  deptno NUMERIC(2) NOT NULL,
  dname VARCHAR(14),
  loc VARCHAR(13)
);

INSERT INTO dept VALUES(1,'accounting','一区');
INSERT INTO dept VALUES(2,'research','二区');
INSERT INTO dept VALUES(3,'operations','二区');

7.11.3	工资级别表 salarygrade

MySQL:
CREATE TABLE salarygrade (
  grade DECIMAL(10,0) DEFAULT NULL,
  losal DECIMAL(10,0) DEFAULT NULL,
  hisal DECIMAL(10,0) DEFAULT NULL
)

====================================================================

判断条件

distinct 去重

SELECT DISTINCT job FROM emp;

like 通配符：%（n个字符） _下划线（1个字符）

查询emp表中,名字 t 开头 n个字符

SELECT * FROM  emp WHERE ename LIKE 't%';

查询emp表中,名字 含有 n 的

SELECT * FROM  emp WHERE ename LIKE '%n%';

查询emp表中,名字 t 开头 3个字符

SELECT * FROM  emp WHERE ename LIKE 't___';

and和or；业务需求表述，并列and，或者or

查询人名含有n的,且编号等于200的人员信息

SELECT * FROM emp WHERE ename LIKE '%n%' AND empno=200;

null 字段如果没有赋值初始化值Null

where name is null
	where name is not null

nvl 、不同数据库版本支持不同，ifnull

转化，nvl(name,‘空’)，nvl(price,0)

需求: 查询 年薪 ,sal工资,comm奖金
分析: 计算数值类型时 ,null计算不正确

SELECT ename,sal*13+nvl(comm,0)*13 FROM emp;

SELECT mgr ,nvl(mgr,'空')FROM emp;
SELECT mgr ,IFNULL(mgr,'空')FROM emp;

between-and 区间

需求:薪资范围 5000-10000(包含5000 ,10000)

SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000;
SELECT * FROM emp WHERE sal >=5000 AND sal<=10000;

union 联合

union可以将两条SQL语句执行的结果合并，并且默认会去除重复记录(如果不希望去除重复记录，可以使用union all)

使用union有前提:
(1)两条SQL语句查询的结果列数必须一致
(2)两条SQL语句查询的结果列名、顺序也必须一致

需求 :员工编号和名称,部门编号和名称
AS 重新命名

SELECT  empno AS '编号', ename AS '名称' FROM emp
UNION
SELECT deptno  AS '编号' ,dname  AS '名称' FROM dept;

默然列名 取 第一句的列名

limit 分页 ( startIndex, pageSize)

limit （startIndex, pageSize） 参数含义（ 起始页面值, 每页显示记录数）

• pageSize; 页面大小

• pageCurrent: 当前页

• startIndex: 起始页面值

• startIndex: 起始页面值=(页码-1)*每页显示记录数

Limit 单个数字 默认 从第一页 0 开始 查询

SELECT * FROM emp LIMIT 3;     默认 limit 0 ,3

limit startIndex, pageSize

SELECT * FROM emp LIMIT 0,2;    第一页数据
SELECT * FROM emp LIMIT 2,2;    第二页数据
SELECT * FROM emp LIMIT 4,2;    第三页数据

WHERE 子句查询

WHERE子句查询语法：SELECT 列名称 | * FROM 表名称 WHERE 列 运算符 值

WHERE子句后面跟的是条件，条件可以有多个，多个条件之间用连接词（or | and）进行连接

下面的运算符可在 WHERE 子句中使用：

子查询：

子查询是指嵌入在其他select语句中的select语句,也叫嵌套查询

单行子查询

需求:查询tony(emp)所在部门,把部门信息展现出来(dept)
涉及两张表

SELECT deptno FROM emp WHERE ename='tony'

SELECT * FROM dept
 WHERE deptno =
 (SELECT deptno FROM emp WHERE ename='tony')

多行子查询

需求:把总监 副总 和经理的信息展现

SELECT * FROM emp 
WHERE job  IN ('总监','副总','经理')

子查询 = 查询结果只能是一个 , in集合 可以多条

聚合函数：

多行函数也叫做聚合(聚集)函数，根据某一列或所有列进行统计。

提示：

1. 多行函数不能用在where子句中
2. 多行函数和是否分组有关，分组与否会直接影响多行函数的执行结果。
3. 多行函数在统计时会对null进行过滤，直接将null丢弃，不参与统计。

count(*),count(1),count(ename) 记录总数

需求: 人员表记录总数

SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
SELECT COUNT(ename)AS '总数' FROM emp;

---

max( ) 最大值

min( ) 最小值

avg( ) 平均值

sum( ) 总计值

需求 :求最高薪资人员 和最低薪资人员

SELECT MAX(sal)FROM emp;   最高薪资
SELECT MIN(sal)FROM emp;   最低薪资
SELECT AVG(sal)FROM emp;   平均薪资
SELECT SUM(sal)FROM emp;   薪资总和

多行函数需要注意的问题:

• 多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。
• 如果查询结果中的数据经过分组（分的组不止一个），多行函数会根据分的组进行统计，有多少个组，就会统计出多少个结果。

分组：group by 条件having

查询分组后，配合聚合函数一起来实现
group by分组，select字段 必须是
1.分组字段，或者和分组字段一一对应的唯一字段(部门标号,部门名称,部门位置)
2. 聚合函数,
3. 其余的字段报错

需求:每个部门的人数

(查询字段是 分组字段 deptno ,聚合函数 count() )

SELECT deptno , COUNT(*) FROM emp GROUP BY deptno

需求:每个部门薪资最高的人

错误写法

查询字段 ename 既不是分组字段 ,也不是聚合函数

SELECT deptno,ename ,MAX(sal) FROM emp GROUP BY deptno

正确写法
第一步求每个部门最高薪资

SELECT deptno,MAX(sal) FROM emp GROUP BY deptno

第二步 求最高薪资 对应的具体人选 (同一张表 ,则自关联)

SELECT  s.ename ,s.deptno ,s.sal
FROM
(SELECT deptno ,MAX(sal) AS ssal  FROM emp GROUP BY deptno) e
LEFT JOIN 
(SELECT * FROM emp) s
ON e.deptno =s.deptno AND e.ssal=s.sal

---

法2

SELECT e.ename,e.sal,e.deptno  FROM emp e,
(SELECT MAX(sal) AS sal,deptno FROM emp GROUP BY deptno)t
WHERE e.sal = t.sal AND e.deptno = t.deptno

---

法3

SELECT ename,sal,deptno FROM emp WHERE (deptno,sal) IN (
SELECT deptno , MAX(sal)AS sal FROM emp GROUP BY deptno )

---

需求:列出最低薪资大于1500的各种职位，显示职位和该职位的最低薪资

– 根据职位进行分组，求出每种职位的最低薪资

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

– 求出最低薪资大于3000的职位

select job,min(sal) from emp where min(sal)>3000 group by job; – 错误写法

select job,min(sal) from emp group by job having min(sal) > 1500; -- 正确

where和having子句的区别:

1. 相同点: where和having都可以对记录进行筛选过滤。
2. 区别：where是在分组之前,对记录进行筛选过滤,并且where子句中不能使用多行函数以及列别名(但是可以使用表别名)
3. 区别：having是在分组之后,对记录进行筛选过滤,并且having子句中可以使用多行函数以及列别名、表别名。
   

如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。后者执行效率较低

难点

• 查询结果集当中 如果出现了,聚合函数以外的非聚和字段,一定要对该字段进行分组
• where子句中不能使用聚和函数以及列别名(但是可以使用表别名)

查询公司最高薪资

查询字段 只有聚合函数,不用分组

SELECT MAX(sal) FROM emp   

查询每个部门薪资最高的人

查询聚合函数max( ),还要查询非聚合字段 deptno ,所以需要对 deptno分组

SELECT deptno,MAX(sal) FROM emp GROUP BY deptno  

order by 排序

• ASC 默认升序
• DESC 降序

字符串按ascii顺序，数字按大小，日期字符串

需求 :按员工姓名排序

SELECT * FROM emp ORDER BY ename;   默认升序 ASCII顺序

需求:按部门升序,如果相同就按雇用时间降序排序

SELECT * FROM emp ORDER BY deptno ASC ,hiredate DESC;

表关联

表的关系

• 一对一，一张表 有时候会为了性能而拆开(商品信息和商品备注信息–详情-很大）
• 一对多，必须两张表
• 多对一，反过来就是一对多，必须两张表
• 多对多，必须三张表，中间表，来维护它们之间的关系
  

inner join …on 内链接

体现关联关系：在子表中要把主表主键写入

需求:列出人员emp所在部门名称dept
dept（主表）对应emp（子表）

SELECT dept.dname ,emp.empno,emp.ename 
FROM emp
INNER JOIN dept 
ON emp.deptno=dept.deptno

可以简写 表名

SELECT d.dname ,e.empno,e.ename 
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno

left join 左链接…on

SELECT d.dname,e.empno,e.ename
FROM emp e
LEFT JOIN dept d
ON e.deptno = d.deptno

right join 右链接…on

SELECT d.dname,e.empno,e.ename
FROM emp e
RIGHT JOIN dept d
ON e.deptno=d.deptno

总结

• where 要求两边记录完全匹配
• inner join 内链接，两边表中记录都必须以后对应关系，才会展现
• left join 左链接，左边表为主，左边出现就列出，如果右边表没有对应记录，展示null
• right join 右链接，右边表为主，右边都必须出现，如果左边表没有对应记录，展示null

万能链接：左链接（右链接是颠倒，把需要展示所有信息放在左边，）

left join包含inner join，right join反过来

两个表连查，左链接步骤：

1. 先查主表，用小括号括起来，设置别名 c
2. 再查从表，用小括号括起来，设置别名 t
3. left join
4. on主表的主键=从表外键
5. select 字段 from
   

综合题目

题目1:

查出至少有一个员工的部门，显示部门编号、部门名称、部门位置、部门人数。

第一种常规解法

先求至少有一个员工的部门,再多表查询相关条件

SELECT d.deptno ,d.dname ,d.loc,e.ecount
FROM 
(SELECT deptno ,COUNT(*) AS ecount FROM emp GROUP BY deptno HAVING COUNT(*)>=1) e
LEFT JOIN 
(SELECT * FROM dept) d
ON e.deptno=d.deptno

第二解法

查询结果集中:员工数量,为聚合字段
其他字段都是 和分组字段部门deptno,一 一对应的,所以可以分组一起查询

SELECT d.deptno,d.dname,d.loc,COUNT(*) AS '员工数量'
FROM emp e 
LEFT JOIN dept d ON e.deptno=d.deptno
GROUP BY  deptno HAVING COUNT(*)>=1

SELECT d.deptno,d.dname,d.loc,COUNT(*) AS '员工数量'
FROM emp e ,dept d 
WHERE e.deptno=d.deptno
GROUP BY  deptno HAVING COUNT(*)>=1

题目2:

列出受雇日期早于直接上级的所有员工，显示员工编号、员工姓名、员工职位,部门名称。

分析 :

• 显示的列: e1.empno, e1.ename,e1.job,d.dname
• 查询的表: emp e1,emp e2,dept d
• 连接条件: e1.mgr=e2.empno AND e1.deptno=d.deptno
• 筛选条件: e1.hiredate<e2.hiredate

SELECT e1.empno, e1.ename,e1.job,d.dname
FROM emp e1,emp e2,dept d
WHERE e1.mgr=e2.empno AND e1.deptno=d.deptno
AND e1.hiredate<e2.hiredate

题目3:

查询员工表中薪资最高的员工信息

select name, max(sal) from emp; – 错误写法 查询不全面,结果只有一个,
查询字段出现聚合函数,还有非聚合字段,要分组

select name,sal from emp order by sal desc limit 0,1; -- 

多行函数和是否分组有关，如果查询结果中的数据没有经过分组，默认整个查询结果是一个组，多行函数就会默认统计当前这一个组的数据。产生的结果只有一个。

正确解法 实际表中有两个最高工资

根据最高薪资到emp表中查询, 该薪资对应的员工信息

SELECT * 
FROM emp 
WHERE sal =(SELECT MAX(sal) FROM emp)

题目4:

查询人员的部门名称和人员名称

多表查询，dname部门名称在dept表，ename人员名称在emp表
万能连接：left join

错误写法: 因为返回结果 只有两边表都有的记录会返回

SELECT e.ename,d.dname
FROM emp e , dept d
WHERE  e.deptno=d.deptno

一般写法:写法固定无法优化

SELECT e.ename,d.dname
FROM emp e LEFT JOIN dept d
ON  e.deptno=d.deptno

优化写法 缩小中间查询范围,以后可以按业务需求灵活变通

SELECT e.ename, d.dname
FROM
( SELECT deptno,ename FROM emp ) e
LEFT JOIN
( SELECT deptno,dname FROM dept ) d
ON  e.deptno=d.deptno