数据库day01（2）

DQL

概述

数据库查询语言
数据库执行DQL语句不会对数据进行改变，只是让数据库发送结果集（一张虚拟表）给客户端。
查询关键字：SELECT
语法：SELECT 列名 FROM 表名 [WHERE --> GROUP BY -->HAVING--> ORDER BY]

SELECT selection_list /要查询的列名称/
FROM table_list /要查询的表名称/
WHERE condition /行条件/
GROUP BY grouping_columns /对结果分组/
HAVING condition /分组后的行条件/
ORDER BY sorting_columns /对结果分组/
LIMIT offset_start, row_count /结果限定/
（LIMIT是MySQL独有）

1、基础查询

1.1 查询所有列

SELECT * FROM table_name

1.2 查询指定列

SELECT {列名1，列名2...} FROM table_name;

2、条件查询

2.1 介绍

在查询时给出where子句，在where子句中可以使用如下运算符及关键字
 =、!=、<>、<、<=、>、>=；
 BETWEEN…AND； （取值范围）
 IN(set)（固定的取值集合）; NOT IN(set)（不在集合set中的）
 IS NULL（空值）； IS NOT NULL（不是空值）
 AND；
 OR；
 NOT；

2.2

举例说明
-- 查询性别为女，并且年龄大于等于50的记录
SELECT * FROM stu WHERE gender='female' AND age>=50;

-- 查询学号为s_1001，或者姓名为liSi的记录
SELECT * FROM stu WHERE sid='S_1001' OR sname = 'liSi';

-- 查询学号为S_1001,S_1002,S_1003的记录
SELECT * FROM stu WHERE sid IN('S_1001','S_1002','S_1003');

-- 查询年龄为null的记录
SELECT * FROM stu WHERE age IS NULL;

-- 查询年龄在20到40之间
SELECT * FROM stu WHERE age>=20 AND age<=40;
-- 或
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
**error：SELECT * FROM stu WHERE age BETWEEN 40 AND 20;**

-- 查询性别非男的记录
SELECT * FROM stu WHERE gender='female';
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE NOT gender='male';

3.模糊查询

比如查询姓名中包含a字母的学生时，就需要使用模糊查询
关键字：LIKE
通配符：_ ：任意一个字符 %：任意0~n个字符
SELECT * FROM stu;
-- 查询姓名由5个字母构成的学生记录
SELECT * FROM stu WHERE sname LIKE '_____';

-- 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
SELECT * FROM stu WHERE sname LIKE '____i';

-- 查询姓名以‘z’开头的的学生记录
SELECT * FROM stu WHERE sname LIKE 'z%';

-- 查询姓名中第2个字母为‘i’的学生记录
SELECT * FROM stu WHERE sname LIKE '_i%';

-- 查询姓名中包含'a'的学生记录
SELECT * FROM stu WHERE sname LIKE '%a%';


4.字段查询

4.1 去除重复记录

关键字：DISTINCT
语法：SELECT DISTINCT 列名 FROM 表名;
若列名有多个，则两个记录的多个列值完全一致时才去除重复的，否则记为不重复的数据。
-- 去除重复数据
SELECT DISTINCT deptno FROM emp;

4.2

过滤函数IFNULL(列名，0)：如果列值为NULL，则转换成数值0，但原数据不变。
-- 查询雇员的月薪和佣金之和
SELECT IFNULL(comm,0) FROM emp;
SELECT sal+IFNULL(comm,0) FROM emp;

4.3给列名添加别名

可以省略AS关键字
-- 给列起别名
SELECT sal+IFNULL(comm,0) AS total FROM emp;

SELECT sname AS 姓名, gender 性别 FROM stu;

5.排序

order by 列名 asc(升序，默认) desc(降序)
NULL值比任何数都小
-- 查询所有学生记录，按年龄升序排序
SELECT * FROM stu ORDER BY age;

-- 查询所有学生记录，按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;

-- 查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序
SELECT * FROM emp ORDER BY sal DESC, empno ASC;

6.聚合函数

聚合函数是用来做纵向运算的函数：
COUNT()：统计指定列不为NULL的记录行数；
MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

6.1 COUNT

当需要纵向统计时可以使用COUNT()。
-- 查询emp表中的记录数
SELECT COUNT(*) FROM stu;

-- 查询emp表中有佣金的人数
SELECT COUNT(comm) FROM emp;

-- 查询emp表中月薪大于2500的人数
SELECT COUNT(*) FROM emp WHERE sal>2500;

-- 统计月薪与佣金大于2500元的人数
SELECT COUNT(*) FROM emp WHERE (sal+IFNULL(comm,0))>2500;

-- 查询有佣金的人数，有领导的人数
SELECT COUNT(comm),COUNT(mgr) FROM emp;
-- 结果是两个结果

-- 查询有佣金，以及有领导的人数
-- 结果是一个结果
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL AND mgr IS NOT NULL;

6.2 SUM和AVG

-- 查询所有雇员的月薪和
SELECT SUM(sal) FROM emp;

-- 查询所有雇员月薪和，以及所有雇员佣金和
SELECT SUM(sal),SUM(comm) FROM emp;

-- 查询所有雇员月薪+佣金和
SELECT SUM(sal+IFNULL(comm,0)) FROM emp;
或 SELECT SUM(sal)+SUM(comm) FROM emp;

-- 统计所有员工的平均工资
SELECT AVG(sal) FROM emp;

6.3 MAX和MIN

-- 查询最高工资和最低工资
SELECT MAX(sal),MIN(sal) FROM emp;

7.分组查询

使用group by子句，结果个数取决于该列能分成多少个组
注：凡和聚合函数同时出现的列名，一定要出现在group by 之后

7.1 分组查询

-- 查询班级里男生的人数和女生的人数
SELECT COUNT(*) FROM stu GROUP BY gender;

-- 查询各部门的人数
SELECT * FROM emp;
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

-- 查询每个部门的部门编号和每个部门的工资和：
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

-- 查询每个部门的部门编号以及每个部门工资大于1500的人数
SELECT deptno, COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

7.2 HAVING子句

HAVING子句：在分组之后再去筛选
-- 查询工资总和大于9000的部门编号以及工资和
SELECT * FROM emp;
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;
注：
where和having的区别：1、having（group by后）是在分组后对数据进行过滤，而where（from后）是在分组前对数据进行过滤；2、having后面可以使用聚合函数（统计函数），而where后面不可以使用聚合函数；3、where是对分组前记录的条件，如果某行记录不满足where子句的条件，则不参与分组；having是对分组后数据的约束。

各关键字顺序及效果

查询代码的书写顺序和执行顺序

查询语句书写顺序：select – from- where- group by- having- order by-limit
查询语句执行顺序：from - where -group by - having - select - order by-limit

-- 查询工资大于1500，工资总和大于6000的部门编号及工资和
SELECT deptno,SUM(sal) FROM emp WHERE sal>1500 GROUP BY deptno HAVING SUM(sal)>6000 ORDER BY SUM(sal);

8. LIMIT

LIMIT用来限定查询结果的起始行，以及总行数

-- LIMIT
-- 查询5行记录，从0行开始（第一行）
SELECT * FROM emp;
SELECT * FROM emp LIMIT 0,5;

-- 查询10条记录，起始行从3开始（第四行）
SELECT * FROM emp LIMIT 3,10;

分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
第一页记录起始行为0，一共查询10行；
第二页记录起始行为10，一共查询10行；
第三页记录起始行为20，一共查询10行；