一、DQL
DQL:数据查询语言:select、from、where
(1)基础查询
SELECT 列名 FROM 表名
1、查询所有字段
SELECT * FROM student;
SELECT id,name,sex,birthday FROM student;
2、查询指定字段
SELECT name FROM student;
注意:生产环境下,优先使用列名查询。*的方式需转换成全列名,效率低,可读性差。
3、查询常量
SELECT 常量值; -- 字符型和日期型的常量,必须用单引号引起来
4、查询计算表达式(+、-、*、/)
SELECT 100/25-5 AS 计算结果;
5、查询函数(后面跟函数)
SELECT CONCAT('姓名',`name`) AS 新名字 FROM student;
6、取别名(AS)
SELECT `id` AS 学号,`name` AS 学生姓名 FROM student AS 学生表; -- AS 可以省略
7、去重(DISTINCT)
SELECT DISTINCT name FROM student;
(2)条件查询
SELECT 列名 FROM 表名 WHERE 条件;
1、等值查询:=、<=>
#查询薪资是11000的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;
注意:=只能判断普通类型的数值,而<=>不仅可以判断普通类型的数值还可以判断NULL
2、非等值查询(>、<、>=、<=、!=、<>)
#查询员工的薪资在6000~10000之间的员工信息(编号,名字,薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <= 10000;
3、逻辑判断查询(and、or、not)
#查询薪资是11000并且提成是0.30的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct = 0.30;
4、区间查询(between and)
#查询员工的薪资在6000~10000之间的员工信息(编号,名字,薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000; #闭区间,包含区间边界的两个值 [6000,10000]
5、空值查询(IS NULL、IS NOT NULL)
#查询没有提成的员工信息(编号,名字,薪资 , 提成)
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;
6、枚举查询( IN (值 1,值 2,值 3 ) )
#查询部门编号为70、80、90的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN(70,80,90);
-- 注:in的查询效率较低,可通过多条件拼接。
7、模糊查询(like)
_ (单个任意字符)
%(任意长度的任意字符)
#查询名字以"L"开头的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';
#查询名字以"L"开头并且长度为4的员工信息(编号,名字,薪资 , 部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L___';
8、分支结构查询(case end)
注意:通过使用CASE END进行条件判断,每条数据对应生成一个值。
类似 Java 中的switch。
CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
WHEN 条件3 THEN 结果3
ELSE 结果
END
#eg:
#查询员工信息(编号,名字,薪资 , 薪资级别<对应条件表达式生成>)
SELECT employee_id , first_name , salary , department_id ,
CASE
WHEN salary>=10000 THEN 'A'
WHEN salary>=8000 AND salary<10000 THEN 'B'
WHEN salary>=6000 AND salary<8000 THEN 'C'
WHEN salary>=4000 AND salary<6000 THEN 'D'
ELSE 'E'
END as "LEVEL"
FROM t_employees;
(3)排序查询
语法:
SELECT 列名 FROM 表名 ORDER BY 排序列 [ASC | DESC];
排序规则 | 描述 |
---|---|
ASC | 对前面排序列做升序排序 |
DESC | 对前面排序列做降序排序 |
1、单字段排序
# 学生表按身高升序
SELECT * FROM student ORDER BY height;
2、多字段排序
# 按年龄升序,如果年龄相同就按身高降序
SELECT age,height FROM students ORDER BY age ASC,height DESC;
(4)分组查询
SELECT 聚合函数,分组后的字段
FROM 表
[WHERE 筛选条件]
GROUP BY 分组的字段
[HAVING 分组后的筛选]
[ORDER BY 排序列表]
#eg:
# 1.查询每个工种有奖金的员工且最高工资>6000的最高工资和公众编号
SELECT
MAX(salary) m,
job_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m > 6000;
# 2.查询每个工种每个部门的最低工资并按最低工资降序
SELECT
MIN(salary),
job_id,
department_id
FROM
employees
GROUP BY job_id, department_id
ORDER BY MIN(salary) DESC ;
注:分组查询中,select显示的列只能是分组依据列,或者聚合函数列,不能出现其他列。
区别:
- 分组查询的字段必须是group by后出现的字段
- 筛选区别
关键字 | 位置 | 针对的表 | |
---|---|---|---|
分组前筛选 | where | group by的前面 | 分组前的原始表 |
分组后筛选 | having | group by 的后面 | 分组后的虚拟表 |
- 分组可以按单个字段也可以按多个字段
- 分组可以搭配着排序使用
技巧:
-- 知道sql执行顺序,对分组、分组前筛选、分组后筛选就容易理解了
1.FROM :指定数据来源表
2.WHERE : 对查询数据做第一次过滤
3.GROUP BY : 分组
4.HAVING : 对分组后的数据第二次过滤
5.SELECT : 查询各字段的值
6.ORDER BY : 排序
7.LIMIT : 限定查询结果
(5)分页查询
SELECT 查询列表
FROM 表
LIMIT offset,pagesize; -- offset:起始的条目索引,默认从0开始 ;pagesize:显示的多少条
#eg:
SELECT *
FROM student
LIMIT 0,10;
offset = (pageNum-1)*pagesize
pageNum : 当前页数
第一页 limit 0,10
第二页 limit 10,10
第三页 limit 20,10
…
第n页 limit (n-1)*pagesize
(6)关联查询
1、交叉连接(cross join)
SELECT * FROM tb_course CROSS JOIN tb_students_info; -- 产生笛卡尔积
SELECT * FROM tb_course CROSS JOIN tb_students_info
WHERE tb_students_info.course_id = tb_course.id;
-- 先生成两个表的笛卡尔积,然后再选择满足 WHERE 条件的记录。一般不建议使用
笛卡尔积(Cartesian product)是指两个集合 X 和 Y 的乘积。
例如,有 A 和 B 两个集合,它们的值如下:
A = {1,2}
B = {3,4,5}集合 A×B 和 B×A 的结果集分别表示为:
A×B={(1,3), (1,4), (1,5), (2,3), (2,4), (2,5) };
B×A={(3,1), (3,2), (4,1), (4,2), (5,1), (5,2) };以上 A×B 和 B×A 的结果就叫做两个集合的笛卡尔积。
并且,从以上结果我们可以看出:
- 两个集合相乘,不满足交换率,即 A×B≠B×A。
- A 集合和 B 集合的笛卡尔积是 A 集合的元素个数 × B 集合的元素个数。
2、内连接(inner join)
-- 1.查询所有有部门的员工信息(不包括没有部门的员工) SQL 标准
SELECT * FROM t_employees INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID
-- 2.查询所有有部门的员工信息(不包括没有部门的员工) MYSQL
SELECT * FROM t_employees,t_jobs WHERE t_employees.JOB_ID = t_jobs.JOB_ID
3、左外连接(left join)
-- 查询所有员工信息,以及所对应的部门名称(没有部门的员工,也在查询结果中,部门名称以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name
FROM t_employees e
LEFT JOIN t_departments d
ON e.department_id = d.department_id;
4、右外连接(right join)
-- 查询所有部门信息,以及此部门中的所有员工信息(没有员工的部门,也在查询结果中,员工信息以NULL 填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name
FROM t_employees e
RIGHT JOIN t_departments d
ON e.department_id = d.department_id;
(7)联合查询(union)
查询语句1
union [all]
查询语句2
union [all]
...
应用场景:
要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点:
1、要求多条查询语句的查询列数是一致的!
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重,如果使用union all 可以包含重复项
-- 查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id > 90 OR email LIKE '%a%';
-- 使用UNION联合查询
SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
-- 查询中国用户中男性的信息以及外国用户中年男性的用户信息
SELECT id,name FROM `user` WHERE csex='男'
UNION ALL -- 包含重复项
SELECT f_id,f_name FROM `foreign` WHERE tGender='male';
(8)子查询
根据查询结果分类 | 可能存在的位置 |
---|---|
单行单列 | select、where、having、exists |
多行单列 | where、having、exists |
多行多列 | from、exists |
1、单行单列子查询
标量子查询
单行操作符使用:>、>=、<、<=、!=、<>、=、<=>
-- select后:1.查询每个部门的员工个数
SELECT d.*, (
SELECT
COUNT(*)
FROM
employees e
WHERE e.department_id = d.`id`
) 个数
FROM
departments d ;
-- where后:2.查询工资大于 Bruce 的员工信息
SELECT * FROM employees WHERE salary >
(SELECT salary FROM employees WHERE FIRST_NAME = 'Bruce' );
2、多行单列子查询
列子查询
多行操作符使用:in、not in、any、some、all、exits
-- 1. 查询与名为'King'同一部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM employees
WHERE department_id in
(SELECT department_id from employees WHERE last_name = 'King'); #N行一列
-- 2.查询高于 001 部门所有人的工资的员工信息(高于所有)
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id= 001);
-- 3.查询高于 001 部门的工资的员工信息(高于子查询中最小的数据)
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE department_id= 001);
3、多行多列子查询
表子查询
将子查询 ”多行多列“的结果作为外部查询的一张表,做第二次查询。
注意:子查询作为临时表,为其赋予一个临时表名
-- 找出每个部门平均工资的工资等级。
SELECT a.*, s.grade
FROM
(SELECT deptno, AVG(sal) AS avgsal
FROM emp
GROUP BY deptno
) AS a
JOIN salgrade s
ON a.avgsal BETWEEN s.lowsal AND s.highsal;