本文所用到的素材放在资源中了~可自行下载,下面先看一下表结构:
girls库有两张表:beauty(女神)表和boys(男神)表
myemployees有四张表:department(部门)、employees(雇员)、locations(地址)、jobs(工种)表
1.相关概念
DQL(Data Query Language):数据查询语言,用来查询记录(数据) 执行"select操作",数据库执行DQL语句不会对数据进行改变,而是让数据库发送结果集给客户端。
2.运算符
2.1算数运算符
- +:相加
- -:相减
- *:相乘
- /:相除
- %:取模
2.2比较运算符
- > :大于
- <:小于
- >=:大于等于
- <=:小于等于
- =:等于
- !=和<>:不等于
- <=>:安全等于
- like:模糊匹配,搭配通配符使用。"%"表示多个字符,"_"表示单个字符
- between and:在两值范围之内
- in / not in:在/不在集合中
- is null / is not null:为空/不为空
2.3逻辑运算符
- and和&&:且
- or和||:或
- not和!:非
3.基础查询
(1)语法:select 查询列表 from 表名 where 筛选条件
(2)特点:查询的列表可以是字段、常量、函数、表达式,也可以是多个查询结果的虚拟表
(3)起别名:用as或者直接用空格,后根别名
(4)去重:去除查询结果中重复的结果,使用distinct关键字,select distinct 字段 from 表名
(5)符号 "+"的作用:
-"+"只作加法运算
-数值+数值:直接运算
-字符+数值:试图将字符转换成数值,转换不成功,字符变为0,再做加法运算
-null+任意值均为null
4.排序查询
(1)语法:select 查询列表 from 表名 where 筛选条件 order by 排序列表 [asc|desc]
(2)asc:升序(默认) desc:降序
(3)特点:
-排序列表支持单个或者多个字段、函数、表达式、别名
-order by一般放在查询语句最后(limit语句除外)
5.分组查询
5.1分组函数
- MAX():最大值
- MIN():最小值
- SUM():求和
- AVG():平均数
- COUNT():计算个数
注意点:
- MAX MIN COUNT可以处理任意数据类型,而SUM AVG一把只用于处理数值类型
- 分组函数均会忽略null值
- 可搭配distinct实现去重统计
5.2分组查询
语法:select 分组函数,分组后的字段 from 表名 [where 筛选条件] group by 分组后字段 [having 分组后的筛选条件] [order by 排序列表]
- having和where比较
关键字 | 筛选的表 | 位置 |
where | 原始表 | group by前面 |
having | 分组后的表 | group by后面 |
#例子:每个工种有奖金的员工的最高工资大于12000的工种编号和最高工资
SELECT job_id, MAX(salary) as 最高工资
FROM employees
GROUP BY job_id
HAVING MAX(salary) > 12000
5.3连接查询
(1)相关概念:连接查询又称为多表查询,当查询的字段需要来自于多个表的时候,就会用到连接查询。
(2)笛卡尔乘积现象:表1有m行,表2有n行,结果就有m * n行,表1每一行都会和表2的每行进行匹配,如何避免:添加有效的连接条件即可
(3)分类
- 按年代分类:
- sql92标准:仅仅支持内连接
- sql99标准(推荐):支持内连接、外连接(左外连接、右外链接、不支持全外连接)和交叉连接
- 按功能分类:
- 内连接:等值连接、非等值连接、自连接
- 外连接:左外连接、右外链接、全外连接(mysql不支持)
- 交叉连接
5.3.1sql92标准
(1)等值连接:有n个表的时候,至少需要n-1个连接条件
语法:select 查询列表 from 表1,表2 [where 筛选条件] 表1.key = 表2.key
案例:
USE girls;
#查询女神名对应的男神名
SELECT name, boyname
FROM beauty b,boys bo
WHERE b.boyfriend_id = bo.id;
USE myemployees;
#查询每个城市部门的个数
SELECT COUNT(*), city
FROM departments d,locations l
WHERE d.location_id = l.location_id
GROUP BY city;
(2)非等值连接
非等值连接条件不是用"="进行条件连接的,可用between and、like、in等比较运算符进行连接
#新建工资等级表
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal int,
highest_sal int);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
#查询员工的工资和工资等级
SELECT DISTINCT(salary),grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal;
(3)自连接:把一张表当作多张表使用,进行条件连接
#查询员名对应的上级名称
SELECT
e.employee_id as 员工id, e.last_name as 员工名, m.employee_id as 上级id, m.last_name as 上级名字
FROM employees as e, employees as m
WHERE e.manager_id = m.employee_id;
5.3.2sql99标准
(1)内连接:
语法:select 查询列表 from 表1
[inner] joim 表2
on 连接条件
[where 筛选条件] [group by 分组列表] [having 分组后筛选] [order by 排序列表]
表连接的数据范围示意:
#等值连接案例:查询部门个数大于3,的城市名和部门个数
SELECT count(*) as `count`, city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY city
HAVING count > 3;
#非等值连接案例:查询工资级别的个数大于20的级别名称,并按照工资级别降序
SELECT COUNT(*) as `count`, grade_level
FROM employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal and g.highest_sal
GROUP BY grade_level
HAVING count > 20
ORDER BY grade_level desc;
#自连接:查询员工的名字和他的上级名字
SELECT e.last_name, m.last_name
FROM employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id;
(2)外连接
分为主表和从表,主表显示全部,从表没有匹配到的将用null进行填充,左外连接,左边为主表。右外链接,右边为主表,全外连接,两边都是主表(mysql不支持)。查询的结果为主表中所有行,如果从表和它匹配的将显示匹配行,如果从表没有匹配项,则用null填充
语法:
select 查询列表
from 表1
left|right|full [outer] join 表2
on 连接条件
[...]
USE girls;
#1.查询没有男朋友的女神名(左外连接)
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE bo.id is NULL;
#2.查询没有男朋友的女神名(右外连接)
SELECT b.name
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.boyfriend_id = bo.id
WHERE bo.id is NULL;
(3)交叉连接(笛卡尔乘积)
语法:
select 查询列表
from 表1
corss join 表2
join连接图示总结:
5.4子查询
(1)相关概念:出现在其他语句中的select语句成为子查询或者内部查询;外部查询语句成为主查询和外查询
(2)分类
-按子查询出现的位置
- select 后面:仅仅支持标量子查询
- from后面:支持表子查询
- where/having后面:标量子查询、列子查询、行子查询
- exists后面(相关子查询):表子查询
-按结果集行列数不同进行分类:
- 标量子查询:一行一列
- 行子查询:一行多列
- 列子查询:一列多行
- 表子查询:多行多列
(3)特点:
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询一般搭配单行操作符"> < = <> <= <="使用
- 列子查询一般配合多行操作符"in any/some all"使用
- 子查询执行优先于子查询(因为主查询要用到子查询的结果)
(1)where/having后面(支持标量/列/行子查询)
- 标量子查询
USE myemployees;
#案例1:查询比Abel工资高的员工姓名
#(1)查询Abel的工资
SELECT salary
from employees
where last_name='Abel';
#(2)查询员工工资,条件满足salary > (1)
SELECT last_name
FROM employees
WHERE salary > (
SELECT salary
from employees
where last_name='Abel'
);
#案例2:查询最低工资大于50部门的最低工资 部门id和其最低工资
#(1)查询部门id为50的最低工资
SELECT MIN(salary)
FROM employees
WHERE department_id = 50;
#(2)查询每个部门的最低工资
SELECT MIN(salary)
FROM employees
GROUP BY department_id;
#(3)添加(2)的分组筛选条件(1)
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
- 列子查询
多行操作符
操作符 | 含义 |
IN | NOT IN | 等于列表中的任意一个 |
ANY|SOME | 和子查询返回的某一个值进行比较 |
ALL | 和子查询返回的所有值比较 |
#案例1:查询location_id为1400/1700的所有员工姓名
#(1)查询location_id为1400/1700的部门id
SELECT department_id
FROM departments
WHERE location_id IN(1400, 1700);
#(2)查询部门id是(1)的员工姓名
SELECT last_name,department_id
from employees
WHERE department_id IN(
SELECT department_id
FROM departments
WHERE location_id IN(1400, 1700)
);
#案例2:返回其他工种中比job_id为`IT_PROG`工种所有工资都低的员工的员工号、姓名、工种id以及薪资
#(1)查询job_id为`IT_PROG`的所有工资
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG';
#(2)返回其他工种工资 < (1)的员工信息
SELECT last_name, salary, employee_id, job_id
FROM employees
WHERE salary < ANY(
SELECT salary
FROM employees
WHERE job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
#或者
SELECT last_name, salary, employee_id, job_id
FROM employees
WHERE salary < (
SELECT MIN(salary)
FROM employees
WHERE job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
- 行子查询(了解)--一行多列,where(列表1,列表2)= (行子查询列表1,列表2)
#案例:查询员工编号最小而且工资最高的员工信息
#方法1(常规)
#(1)查询员工编号最小的员工
SELECT MIN(employee_id)
FROM employees;
#(2)查询最高工资
SELECT MAX(salary)
FROM employees;
#(3)查询同时满足(1)和(2)的员工信息
SELECT *
FROM employees
WHERE employee_id = (
SELECT MIN(employee_id)
FROM departments
) and salary = (
SELECT MAX(salary)
FROM employees
);
#或者使用行子查询
SELECT *
FROM employees
where(salary, employee_id) = (
SELECT MAX(salary), MIN(employee_id)
FROM employees
);
(2)select后面(仅支持标量子查询)
#1.查询每个部门的员工个数
#方法1:标量子查询
SELECT d.*, (
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.department_id
) as 个数
FROM departments d;
#方法2:连接查询
SELECT d.department_id, COUNT(*) as 个数
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
GROUP BY d.department_id;
#案例2:查询员工号为102的部门名
SELECT (
SELECT department_name
FROM departments d
JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id = 102
) AS 部门;
(3)from后面
from后面必须是表子查询,子查询必须起别名
#案例:查询每个部门的平均工资等级
#(1)查询每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
GROUP BY department_id;
#(2)用(1)连接工资等级表
SELECT ag_dep.*, g.grade_level
FROM job_grades g
JOIN (
SELECT AVG(salary) as ag, department_id
FROM employees
GROUP BY department_id
) as ag_dep
ON ag_dep.ag BETWEEN g.lowest AND g.highest;
(4)exists后面(相关子查询,支持表子查询)
语法:exists(完整的查询语句)
结果为1或者0
USE myemployees;
#案例1:查询有员工的部门名
#EXISTS子查询
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.department_id = e.department_id
);
#或者用IN
SELECT department_name
FROM departments
WHERE department_id IN(
SELECT department_id
FROM employees
);
USE girls;
#案例2:查询单身的男神信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
SELECT boyfriend_id
FROM beauty b
WHERE bo.id = b.boyfriend_id
);
#或者
SELECT *
FROM boys
WHERE id NOT IN(
SELECT boyfriend_id
FROM beauty
);
5.5分页查询
(1)应用场景:当数据表数据太多,一页显示不完,这个时候就需要按页返回数据
(2)语法:
select 查询列表 from 表 [join 表2 on 连接条件] [where 筛选条件] [group by 分组字段] [having 分组后筛选] [order by 排序字段] limit [offset,]size offset:起始索引,从0开始 size:要返回的数据数量
(3)特点:
- 分页查询放在查询语句的最后
- 公式:select 查询列表 from 表 limit (page-1) * size,size
5.6联合查询(union)
(1)union联合、合并:将多条查询语句合并成一个结果
(2)应用场景:要查询的结果来自于多个表,且多个表并没有直接的连接关系,但查询的信息一致时。
(3)语法:
查询语句1 union 查询语句2 union ...
(4)特点:
- 要求多条查询语句的查询列数是一样的
- 要求多条查询语句的每一列的类型和顺序最好一致
- union默认去重,如果使用union all可以包含重复项
#案例:查询部门编号>90或者邮箱包含'a'的员工信息
#(1)常规做法
SELECT *
FROM employees
WHERE department_id > 90
OR email LIKE '%a%';
#(2)使用联合查询
SELECT *FROM employees WHERE department_id > 90
UNION
SELECT *FROM employees WHERE email LIKE '%a%';