MySQL之基本查询语言

3. 基本查询语言(DQL)

*：通配符，查找一条数据的所有内容，并且效率很低。

3.1 基本查询语言的结构

最简单的查询语句： 
select...from.... 
一个完整的普通查询语句结构如下： 
select [distinct].....from....
[where....]
[group by .....]
[having.....]
[order by.....]
[limit.....]
操作说明：
select子句和from子句必不可少。
select:后面跟需要查询的字段名，用逗号分开
from:用于指定要查询的表。

以上[]子句可以没有，一旦存在，排序不可颠倒。

3.2 查询语句的执行顺序

1. 先执行from子句:基于表进行查询操作 
2. 再执行where子句:进行条件筛选或者条件过滤 
3. 再执行group by子句:对剩下的数据进行分组查询。 
4. 再执行having子句：分组后，再次条件筛选或过滤 
5. 然后执行select子句：目的是选择业务需求的字段进行显示 
6. 再执行order by子句：对选择后的字段进行排序 
7. 最后执行limit子句:进行分页查询，或者是查询前n条记录

3.3 别名的用法

reg: select name 姓名, (year(now())-year(birth)) 年龄 from test1 t;
reg: select 
	e.empno 员工编号,
	e.ename,
	e.job,
	mgr '领导编号',
	hiredate,sal,
	comm,deptno
from emp e;
补充：可以在字段名和别名中间插入as,也可以不插

注意事项：在select语句中，可以对表或者是列起别名操作。在使用汉字作为列别名时，可以加单双引号，也可不加。 表别名不能加单双引号。

3.4 where条件子句的使用

作用：用于条件筛选或过滤

1. 关系表达式:

   >,>=,<,<=,=,!=,<> 
   

2. 多条件连接符:

   and，or, [not] between ..and.. 
   

3. 集合操作:

   [not] in (set)
   >all(set), <all(set), >any(set), <any(set) 
   

   注意：

   • mysql不支持简单的集合查询操作，但是支持子查询,是针对于all和any集合操作来说的。
   • not in相当于 and
   • in相当于or

4. NULL判断:

   IS [NOT] NULL
   

   NULL的注意事项:

   • 在NULL在set后时必须使用等号(=)
   • 当NULL作为条件时必须使用IS NULL或者ISNOT NULL

5. 模糊查询:

   like 
       _:占位符，表示匹配任意一个字符 。
       *:通配符，代替表中所有的字段名。
       %:表示匹配任意N个字符，大于等于0。	
   

   注意:like一般情形都是匹配字符串，所有需要加单引号。

reg:查询工资大于1600的所有员工的编号，姓名，职位，工资 
select empno,ename,job,sal from emp where sal>1600 
reg:查询工资大于1600并且小于2500的所有员工的编号，姓名，职位，工资 
select empno,ename,job,sal from emp where sal>1600 and sal<2500; 
select empno,ename,job,sal from emp where sal between 1600 and 2500; 
reg:查询10，20号部门的所有员工的信息。 
select * from emp where deptno not in (10,20); 
reg:查询不是10号部门中姓名第二个字符是m的员工信息。 
select * from emp where deptno<>10 and ename like '_m%';
reg:判断字段field_A中是否包含23。
select * from table_test where FIND_IN_SET("23", field_A) ;
reg:查询emp表中名字不是以'K'开头的员工的所有信息
select * from emp where ename not like 'K%';
reg:查询emp表中名字中不包含‘A’的所有员工的信息
select * from emp where comm not like '%A%';

3.5 order by排序子句

用于查询排序的，通常放置在一个查询语句的最后部分。

 语法:order by colName [asc|desc] [,colName [asc|desc]] 
	asc：升序， 默认情况就是升序 
	desc:降序 

reg:查询员工表中的所有员工信息，按照工资降序排序 
select * from emp order by sal desc; 
reg:查询员工表中的所有员工信息，按照工资降序排序,如果相同，再按照奖金升序排序 
select * from emp order by sal desc,comm asc;

3.6 group by分组子句

需求：

有的时候，需要分组统计一些，最大值，最小值，平均值，和，总数之类的这样的信息，此时需要分组查询。

1.聚合函数：也叫分组函数
	- count(): 统计每组满足的记录总数 
	- max():统计每组满足条件的最大值 
	- min():统计每组满足条件的最小值 
	- avg():统计每组满足条件的平均值 
	- sum():统计每组满足条件的总和。 

注意：

• 所有的聚合函数，都会忽略字段为null的那条记录。

  如果需要空记录(一般情况下的AVG求平均要求)：

  聚合函数(ifnull(字段名,0));
  

• count(*),不会忽略null值所在的行记录，即通常用于统计总行数。

• 分组函数的分组后的数据不是首数据，而是所有组数据合并起来了。

2. 在分组查询时，只有分组字段可以写在select子句中,其他不是分组的字段，不应该写在select子句中
3. 聚合函数处理null值，可以使用ifnull（colName, value）.

• ifnull(colName,value): 如果colName对应的值不为空，就使用本身的值，如果为null,使用 value.

# 案例：查询每个部门中的每种职位的最高工资，最低工资，工资之和 
select deptno,job ,max(sal),min(sal),sum(sal) from emp group by job,deptno; 
# 案例: 查询所有员工的平均工资，平均奖金 使用ifnull函数 
select avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp;
#案例:做文员的员工人数（job= CLERK 的）
select count(*) from emp where job like 'CLERK';

3.7 having分组过滤子句

只能使用在分组查询子句后面（其实可以直接在select后，但规范上不允许）。起到再过滤的作用。

 # 查询部门平均工资大于1000部门号，平均工资。 
select deptno,avg(ifnull(sal,0)),max(sal) avg_sal from emp group by deptno having avg(ifnull(sal,0)) >1000; 
# 查询每种职位的最高工资大于1500的职位、最高工资，平均工资，平均奖金。 
select job,max(sal),avg(ifnull(sal,0)),avg(ifnull(comm,0)) from emp group by job having max(sal)>1500;

HAVING和WHERE的区别：

• HAVING后面可以写聚合函数，WHERE后面不可以写聚合函数
• HAVING是对分组后的数据进行过滤，WHERE是对分组前的数据进行过滤
  • WHERE对分组前的数据过滤，如果不存在满足WHERE条件过滤的数据，将不参与分组。
  • HAVING是分组后的数据做过滤。

3.8 去重查询

有的时候，我们需要查询表中有那些不同的数据。不需要重复出现，此时可以使用distinct关键字进行去重处理
注意：distinct关键字只能放在select关键字之后。 

3.9 分页查询

需求：当一页的数据量过大时，我们可以进行分页显示操作。

注意：分页查询时，一般都要进行先排序，再分页。 
- 关键字limit. 
- 语法:limit m[,n]; 
	m 表示从第几条记录开始查询， n表示要查询的记录数目。 
注意：mysql的记录index从0开始。 一个参数的含义：limit n 表示从0开始查询n条记录 

案例1：每页5条记录，查询第二页的数据。 
select empno,ename from emp limit 5,5; 
案例2：查询第page页的数据, 每页大小为pageSize。limit的写法如下： 
limit (page-1)*pageSize , pageSize

实际生产环境中，分页有两种实现：
第一种：假分页，一次性把所有的数据都查询出来，保存到内存中。通过其他运算逻辑获取每一页的数据
第二种：真分页，每次都要与数据库进行真实交互，查询一页的数据