DQL部分
1.什么是DQL?
DQL:Data QueryLanguage 数据查询语言标准语法
2.基础查询
select 查询列表 from 表名
查询列表:表中字段,常量值,表达式,函数
-- 查询单个字段
SELECT `name` FROM employee;
-- 查询多个字段
SELECT id,`name` FROM employee;
-- 查询所有
SELECT * FROM employee;
-- 查询常量值
SELECT 100;
-- 查询表达式
SELECT 100+101;
-- 查询函数
SELECT VERSION();
-- as关键字起别名
SELECT `name` as 姓名 FROM student
-- distinct关键字去重
SELECT DISTINCT NAME,chinese FROM student
-- CONCAT(str1,str2,...)拼接字符
SELECT CONCAT(id,name) as 人员信息 FROM student
3.条件查询
select 查询列表 from 表名 where 筛选条件
条件运算符: > >= < <= = !=(<>)
逻辑运算符: && || ! and or not
模糊查询:
like:通配符 %表示任意一个字符,_表示一个字符,使用 \ 进行转义
between and :字段属于一个区间内
in:判断字段值是否属于in列表某一项,比or简洁简单
is null 、is not null
-- 查询语文成绩大于90的学生
SELECT * FROM student WHERE chinese > 90
-- 查询语文成绩大于80 并且数学成绩大于60的学生
SELECT * FROM student WHERE chinese > 80 AND math > 60
-- 查询姓李的学生
SELECT * FROM student WHERE `name` LIKE '李%'
-- 查询姓李并且只有两个字的学生
SELECT * FROM student WHERE `name` LIKE '李_'
4.排序查询
select 字段列表 from 表名 where 条件筛选 order by 排序条件【ASC(升序)/DESC(降序)】
-- 按照学生的出生日期降序
SELECT * FROM student ORDER BY borndate DESC
-- 按照学生的出生日期降序,再按照名字长度升序
SELECT * FROM student ORDER BY borndate DESC,length(studentname) ASC
5.分组查询
select 聚合函数,groupby中字段
from 表面
【where 筛选条件】
group by 字段
【order by 排序条件】
-- 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct is not null
GROUP BY manager_id
注:having子句,根据分组后的表继续筛选
-- 查询领导编号大于102的每个领导手下的最低工资大于5000的编号是哪个
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id > 102
GROUP BY manager_id
HAVING MIN(salary) > 5000
多个条件分组
-- 查询每个部门每个工种员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
6.连接查询
select 查询列表 from 表1
连接类型 join 表2 on 连接条件
【where 筛选条件】
【group by 分组条件】
【having 筛选条件】
【order by 排序列表】
连接查询分类:
内连接(inner join):等值连接,非等值连接,自连接
以下都是92SQL语法
-- 等值连接 查询员工名、工种号、工种名
SELECT e.last_name,e.job_id,j.job_title
FROM employees e,jobs j
WHERE e.job_id = j.job_id
-- 非等值连接 查询员工的工资和工资的级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE e.salary BETWEEN g.loweset_sal AND g.highest_sal
-- 自连接 查询 员工名和对应领导名
SELECT e1.employee_id,e1.last_name,e2.employee_id,e2.last_name
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id
以下是99SQL语法
-- 内连接 等值连接 查询员工名与部门名
SELECT last_name,department_name
from employees
INNER JOIN departments ON employees.department_id = departments.department_id
-- 内连接 非等值连接 查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
on e.salary BETWEEN g.lowest_sal AND g.highest_sal;
-- 内连接 自连接 查询员工名和对应的领导名
SELECT e1.last_name,e1.employee_id,e2.last_name,e2.employee_id
FROM employees e1
JOIN employees e2
on e1.manager_id = e2.employee_id
外连接:左外连接(left join),右外连接(right join) 不匹配的数据用null代替
全外连接(full join) = 左右外连接结果 + 表1有表2没有的 + 表2有表1没有的(MySQL不支持)
-- 外连接 查询哪个部门没有员工
SELECT d.*,e.employee_id FROM departments d
LEFT JOIN employees e
on d.department_id = e.department_id
WHERE e.employee_id IS NUL
交叉连接(cross join):相当于笛卡尔积的结果集
7.子查询:出现在其他语句内容的查询
分类:
按照子查询出现的位置 select后面,from后面,where或者having后面,exists后面
按照子查询的结果 标量子查询(返回结果一行一列) ,列子查询(一列多行),行子查询(一行多列),
1)放在where和having后面的子查询:
单行子查询:标量子查询
--where后面
-- 查询比Abel工资高的员工信息并按照工资降序排序
SELECT *
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
)
ORDER BY salary DESC
-- 查询job_id与141员工相同,salary比143号员工多的员工的姓名,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary > (
SELECT salary
FROM employees
WHERE employee_id = 143
)
-- Having后面
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
)
多行子查询:列子查询 需要配合以下操作符使用
-- 查询location_id 是1400或1700的部门中所有员工的姓名
SELECT last_name
FROM employees
WHERE department_id IN(
select DISTINCT department_id
FROM departments
WHERE location_id in(1400,1700)
)
-- 查询其他部门中比job_id为 IT_PROG部门中任一工资低的员工的工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
from employees
WHERE salary < ANY(
select DISTINCT salary
from employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG'
SELECT employee_id,last_name,job_id,salary
from employees
WHERE salary < (
select MAX(salary)
from employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG'
-- 查询其他部门中比job_id为 IT_PROG部门中所有的工资都低的员工的工号、姓名、job_id以及salary
SELECT employee_id,last_name,job_id,salary
from employees
WHERE salary < ALL(
select DISTINCT salary
from employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG'
SELECT employee_id,last_name,job_id,salary
from employees
WHERE salary < (
select MIN(salary)
from employees
WHERE job_id = 'IT_PROG'
) AND job_id != 'IT_PROG'
行子查询 用的不是很多
2)放在select后面的子查询
标量子查询:
-- 查询每个部门的员工数
SELECT d.*,
(select COUNT(*)
from employees e
WHERE e.department_id = d.department_id) as 员工个数
from departments d
-- 连接查询写法
SELECT d.*,COUNT(*) 员工个数
from departments d
inner JOIN employees e
on d.department_id = e.department_id
GROUP BY e.department_id
3)放在where后面的子查询
-- 查询每个部门的平均工资的工资等级
SELECT AVG_SAL.*,JOB_G.grade_level
FROM (
SELECT AVG(salary) as avsal,department_id
FROM employees
GROUP BY department_id
) AVG_SAL
JOIN job_grades JOB_G
ON AVG_SAL.avsal
BETWEEN JOB_G.lowest_sal AND JOB_G.highest_sal
4)放在exists后面的子查询(用的很少,可以用前面的替换)
8.分页查询(当显示的数据,一页显示不全,需要分页提交sql)
select 字段列表 from 表1 【
join类型 join on 表2
.... join on 表n
where 筛选条件
group by 分组条件
having 分组后的筛选
order by 排序的字段
】
limit offset ,size;
offset:要显示数据的起始索引,从0开始
size:要显示的数据大小
-- 查询前5个员工信息
SELECT * FROM employees LIMIT 0,5
-- 查询10-15个员工的信息
SELECT * FROM employees LIMIT 9,5
分页查询的公式,要显示的页数 page,每页的条目数size
SELECT * FROM employees LIMIT (page-1)*size,page
9.联合查询(将多条查询语句的结果合并成一个结果)
查询语句1 union 查询语句2 union 查询语句3 union .... union 查询语句N
应用场景:
要查询的结果来自于多个表,并且这些表之间没有什么联系,但查询的字段都是一致时
要求查询的字段顺序保持一致
使用union all 显示两个表中都有的结果