八、DQL 查询操作

8.1、语法说明

SELECT 列1,列2,列3... FROM 表名 [WHERE];
-- SELECT 选择要查询的列
-- FROM 提供数据源 (表、视图或其他的数据源)
-- 可以写*表示查询所有列，但是在实际开发中基本上不会使用，性能低，实际开发中是将所有字段列出来

8.2、普通查询

8.2.1、设置别名

8.2.1.1、语法

SELECT 列名 AS 别名 FROM 表名 [WHERE];
SELECT 列名 别名 FROM 表名 [WHERE]

8.2.1.2、作用

1. 改变列的标题头。
2. 作为计算结果的含义。
3. 作为列的别名。
4. 如果别名中使用特殊字符，或是强制大小写或有空格时都需要加单引号。

8.2.1.3、例子

-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本（使用别名）
SELECT id,productName,(costPrice + 1) * 50 1 AS allPrice FROM product
SELECT id,productName,(costPrice + 1) * 50 allPrice FROM product

8.2.2、按照格式输出

    为方便用户浏览查询结果数据，有时需要设置查询结果的显示格式，可以使用 CONCAT 函数来 连接字符串。

8.2.2.1、语法

CONCAT(字符串1,字符串2,...)

8.2.2.2、实战

-- 查询商品的名字和零售价。格式： xxx 商品的零售价为：ooo
SELECT CONCAT(productName,'商品的零售价为：',salePrice) FROM product

8.2.3、消除重复的数据

    distinct前面不能接其他的字段，他只能出现在所有字段的最前方。他表示的意思是后面所有的字段联合起来一起去重。

SELECT DISTINCT 列名, ... FROM 表名;

8.2.4、算数运算符

    对 number 型数据可以使用算数操作符创建表达式
    他有如下优先级：

1. 乘法和除法的优先级高于加法和减法。
2. 同级运算的顺序是从左到右。
3. 表达式中使用"括号"可强行改变优先级的运算顺序

-- 查询所有货品的id，名称和批发价(批发价=卖价*折扣)
SELECT id,productName,salePrice * cutoff FROM product
-- 查询所有货品的id，名称，和各进50个的成本价(成本=costPirce)
SELECT id,productName,costPrice * 50 FROM product
-- 查询所有货品的id，名称，各进50个，并且每个运费1元的成本
SELECT id,productName,(costPrice + 1) * 50 FROM product

8.2.5、比较运算符

    比较运算符有如下几个：

1. =
2. >
3. <
4. <=
5. != （<> 等价 !=）

-- 查询商品名为 罗技G9X 的货品信息
SELECT * FROM product WHERE productName='罗技G9X';
-- 查询零售价小于等于 200 的所有货品信息
SELECT * FROM product WHERE salePrice <= 200
-- 查询批发价大于 350 的货品信息
SELECT *,salePrice * cutoff allPrice FROM product WHERE salePrice * cutoff > 350

8.2.6、逻辑运算符

运算符	含义
AND	如果组合的条件都是TRUE，返回TRUE
OR	如果组合的条件之一是TRUE，返回TRUE
NOT	如果下面的条件是FALSE，返回 TRUE，如果是 TRUE ,返回 FALSE

8.2.7、范围匹配

    范围匹配：BETWEEN AND 运算符,一般使用在数字类型的范围上。但对于字符数据和日期类型同样可

用。需要两个数据。

8.2.7.1、语法

WHERE 列名 BETWEEN minValue AND maxValue; -- 闭区间

8.2.7.2、例子

-- 查询零售价在300-400 之间的货品信息
SELECT * FROM product WHERE salePrice BETWEEN 300 AND 400
-- 查询零售价不在300-400之间的货品信息
SELECT * FROM product WHERE NOT salePrice BETWEEN 300 AND 400

8.2.8、集合查询

    集合查询： 使用 IN 运算符，判断列的值是否在指定的集合中。

8.2.8.1、语法

WHERE 列名 IN (值1，值2....);

8.2.8.2、例子

-- 查询分类编号为2,4的所有货品的id，货品名称，
SELECT id,productName FROM product WHERE dir_id IN (2,4)
-- 查询分类编号不为2,4的所有货品的id，货品名称，
SELECT id,dir_id,productName FROM product WHERE dir_id NOT IN (2,4)

8.2.9、判空

    IS NULL: 判断列的值是否为空值，非空字符串，空字符串使用==判断。

8.2.9.1、语法

WHERE 列名 IS NULL;

8.2.9.2、例子

-- 查询商品名为NULL的所有商品信息。
SELECT * FROM product WHERE productName IS NULL;
SELECT * FROM product WHERE supplier =''

8.2.9.3、注意

    使用=来判断只能判断空字符串,不能判断null 的，而使用IS NULL只能判断null值，不能判断空

字符串。

8.2.10、过滤查询

    使用 WHERE 子句限定返回的记录

8.2.10.1、语法

SELECT <selectList>
FROM 表名
WHERE 条件;

8.2.10.2、注意

1. WHERE子句在 FROM 子句后。

2. 查询语句的字句的执行顺序 FROM 子句: 从哪张表中去查询数据 => WHERE 子句 : 筛选需要哪些行的数据 => SELECT 子句: 筛选要显示的列。

8.2.11、模糊查询

    模糊查询数据使用 LIKE 运算符执行通配查询，他有两个通配符：

1. %：表示可能有零个或者任意多个字符。
2. _：表示任意的一个字符。

8.2.11.1、语法

WHERE 列名 Like '%M_'

8.2.11.2、例子

-- 查询货品名称匹配'%罗技M9_' 的所有货品信息
SELECT * FROM product WHERE productName LIKE '%罗技M9_'

8.3、结果排序

    使用 ORDER BY 子句将查询结果进行排序，他有两种排序的模式：

1. ASC : 升序（默认）。
2. DESC：降序。

    ORDER BY 子句出现在，SELECT 语句的最后。

8.3.1、例子

--单列排序： 选择id，货品名称，分类编号,零售价并且按零售价降序排序
SELECT id,productName,dir_id,salePrice FROM product ORDER BY salePrice DESC
--多列排序： 选择id，货品名称，分类编号,零售价先按分类编号降序排序,再按零售价升序排序
SELECT * FROM product ORDER BY dir_id DESC,salePrice ASC

8.3.2、注意

1. 谁在前面谁先排序。
2. 如果列的别名使用 ' ' 则按此别名进行的排序无效。

-- 反例
SELECT id,salePrice 'sp' FROM product ORDER BY 'sp'

8.4、分页查询

    limit是mysql特有的，他用于取结果集中的部分数据，Oracle中有一个相同的机制，叫rownum。

    limit是SQL语句最后执行的环节。

8.4.1、语法

SELECT <selectList>
FROM 表名
[WHERE] LIMIT ?,?
-- 第一个? : 开始行的索引数 beginIndex，默认为0
-- 第二个? : 每页显示的最大记录数 pageSize
-- 每页显示 3条数据
-- 第一页： SELECT * FROM product LIMIT 0,3
-- 第三页： SELECT * FROM product LIMIT 6,3
-- 第八页： SELECT * FROM product LIMIT 21,3
-- 当前页 : currentPage
-- 每页显示的最大记录数: pageSize

8.4.2、通用的标准分页sql

    beginIndex = (currentPage - 1) * pageSize

8.4.3、案例

案例

    找出工资排名在4到6名的员工

select name,sal,from emp order by desc limit 3,6;

8.5、分组函数

• COUNT(*) ： 统计表中有多少条记录
• SUM(列) ： 汇总列的总和
• MAX(列) : 获取某一列的最大值
• MIN(列) : 获取某一列的最小值
• AVG(列) : 获取列的平均值

-- 查询货品表中有多少数据
SELECT COUNT(*) FROM product
-- 计算所有货品的总的进货价
SELECT SUM(costPrice) FROM product

注意：

1. 分组忽略null，无需额外过滤是否为null这个条件。**
2. SQL语句中有一个语法规则，分组函数不可以直接使用在where字句当中。
3. count(*)和count(具体的字段的区别)
   • count(*)一定是总记录数，和字段无关。
   • count(具体的某个字段)是这个字段不为空的记录数。

8.6、分组查询

8.6.1、group by

​ group by：按照某个字段或者是某些字段进行分组。

    聚合函数分组会和group by一起联合使用，并且任何一个分组函数都是在group by语句执行结束之后才会执行。当一条sql语句没有group by的话，整张表的数据会自成一组。
    SQL语句中有一个语法规则，分组函数不可以直接使用在where字句当中。原因是因为：group by是在where执行之后才会执行。如下面这条错误的sql语句：

select * from emp where sal > avg(sal);

    当执行到avg(sal)的时候，还没有执行group by，所以没办法执行分组函数。还没有分组就不可以执行分组函数。

需求：求每一个工作岗位的最高薪资

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

    结论：当一条sql语句中有group by的时候，select 后面只允许出现分组函数或者是参加分组的字段。

需求：找出每个部门不同工作岗位的最高薪资）

select
	deptno ,job ,max(sql)
from 
	emp
group by
	deptno,job

8.6.2、having

    having：having是对分组之后的数据进行再次过滤。

需求：找出每个部门的平均薪资，要求显示薪资大于2000的数据

select val(sal),deptno from emp group by deptno having val(sal) > 2000;

8.6.3、group by和having的总结

    能用where过滤的就先用where过滤，无法用where过滤的在用having，但是having一定要搭配group by使用，先分组在过滤。

8.6、DQL字句的执行顺序

select 
	...
from 
	...
where
	...
group by
	...
having
	...
order by
	...
limit
	...

1. from: 从哪张表中去查数据。
2. where: 筛选需要的行数据。
3. group by :分组
4. having：对分组的数据进行再次过滤
5. SELECT : 筛选需要显示的列的数据。
6. ORDER BY : 排序操作。

九、多表查询

9.1、连接查询

    在实际开发中，大部分的情况下都不是从单表中查询数据，一般是多张表进行联合查询取出最终的结果，一般一个业务都会对应多张表。

​ 连接查询的分类有两种：

1. SQL92（语法较老，过时）。
2. SQL99（语法比较新）。

9.2、笛卡尔积现象

    当两张表进行连接查询的时候，没有任何条件进行限制，最终查询的结果条数是两张表记录条数的乘积，这个现象称为笛卡尔积现象。

    我们在开发的时候一般会给表起别名，他有两个好处：

1. 执行效率高。
2. 可读性好

select 
	e.ename,d.dname
from
	emp e,dept d

    这样出现的条数就是两张表条数的乘积。

    既然出现了笛卡尔积现象，我们就要避免笛卡尔积现象，避免笛卡尔积现象的措施就是增加条件进行过滤，但是避免了笛卡尔积你现象，会减少记录匹配的次数吗？答案是不会，次数还是两张表条数的乘积，只不过显示的是有效的记录数。

9.3、内连接

    假设A和B两张表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录都会被查询出来，AB两张表是平等的，没有主副之分，这就是内连接。

9.3.1、等值连接

    内连接最大的特点是：条件是等量关系。

select
	...
from
	...
inner join -- inner是可以省略的，带着inner可读性更好
	...
on 
	连接条件
where
	...

select 
	e.name,d.name 
from 
	emp e 
inner join -- inner是可以省略的，带着inner可读性更好
	dept d 
on 
	e.deptno =  d.deptno

    SQL99语法结构更清晰一些，表的连接条件和后来的where过滤条件分离了。

9.3.2、非等值连接

    连接条件中的关系是非等量关系。

select e.name,e.sal,e.grade
from emp e
join salgrade s
on e.sal between s.local and s.hisal

9.3.3、自连接

    最大的特点是一张表看成两张表，自己连接自己。（不常用）

9.4、外连接

    假设A表和B表进行连接，使用外连接的话，AB两张表有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表。

    当副表中的数据没有和主表中的数据匹配上的时候副表自动模拟出NULL与之匹配。主表的数据会无条件的全部查询出来。

9.4.1、外连接的分类

​ 外连接分为两类：

1. 左外连接（左连接 LEFT）：表示左边的这张表是主表。
2. 右外连接（右连接 RIGHT）：表示右边的这张表是主表。

​ 左连接有连接的写法，右连接也有对应的左连接的写法。用左连接LEFT的时候，说明上面（左边）的表是主表。

9.4.1.1、左连接

SELECT * FROM emp e LEFT OUTER JOINdept d ON e.deptno=d.deptno;

​ 注意：OUTER可以省略

    左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。

9.4.1.2、右连接

    右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

需求：

    dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno

9.4.2、注意

    内连接说明两张表是平等的，没有主副之分。

    外连接说明有一张表是主表，另一张表是副表。

    在开发中外连接居多，因为内连接查询的数据会丢失。

9.5、三张表连接

select ...
from
	...
join
	...
on
	...
join
	...
on
	...(条件)

9.6、union

    union关键字可以用于将查询结果集相加。他是连接两个查询结果的，可以用于两张不相干的表中的数据拼接在一起显示。

    注意：union必须用于两张列数相同的表进行查询，否则无法显示。

案例

    查询工作岗位是MANAGER和SALESMAN的员工

select ename,job from  emp where job = 'MANAGER'
union
select ename,job from  emp where job = 'SALESMAN'

十、子查询

10.1、子查询概述

    select语句中嵌套select语句，被嵌套的select语句就是子查询，他可以出现的位置有select、from、where后。

10.2、where子句中使用

案例

​     找出高于平均薪资的员工信息

select * 
from emp 
where sal > (select avg(sal) from emp);

10.3、from字句后使用

案例

    找出每个部门平均薪水的薪资等级。

1. 先找出每个部门的平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) avgsal from emp group by deptno

2. 将以上的查询结果作为临时表t，让t表和salgrade（薪水等级表） s连接，条件是：t.avgsal between s.losal and s.hisal

select 
	t.*,s.grade
from
	(select deptno,avg(sal) avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal

10.4、在select后使用

需求

    找出每个员工所在的部门名称，要求显示员工名和部门名。

select 
	e.ename,
	(select d.dname from dept d where e.deptno = d.deptno) dname
from 
	emp e;