MySQL中的查询总结

以前存在电脑上的笔记，发到博客上顺便复习一下

条件查询

执行顺序

1.from子句

2.where子句

3.select子句

特点：

1、按照关系表达式筛选

关系运算符： > < >= <= = ； !=和<>都为不等于，但不建议使用!=

2、按照逻辑表达式筛选

逻辑运算符： && || ! 在mysql中建议用 and or not

3、模糊查询

like

in

between and

is null

一、按关系表达式筛选

查询奖金率>0.03或者 员工编号在60-110之间的员工信息

SELECT *
FROM employees
WHERE commission_pct>0.03 OR (employee_id>=60 AND employee_id <=100);

二、模糊查询

1、like

一般和通配符搭配使用，对字符型数据进行部分匹配查询

常见的通配符：

_ 任意单个字符

% 任意多个字符 0-多个

案例1：查询姓名中包含字符a的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%a%';

案例2：查询姓名中最后一个字符为e的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '%e';

案例3：查询姓名中包含第三个字符为x的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '__x%';

案例4：查询姓名中第二个字符为_的员工信息

SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';

ESCAPE为 设定 $ 为转义字符符号

三、in/not in

功能：查询某字段的值是否属于指定的列表之内

a in(常量值1，常量值2,…) a字段的值是否为括号中常量值的某一个

a not in(常量值1，常量值2,…) a字段的值是否不等于括号中常量值的某一个

案例1：查询部门编号是30/50/90的员工名、部门编号

SELECT last_name,department_id
FROM employees
WHERE department_id IN(30,50,90);

案例2：查询工种编号不是SH_CLERK或IT_PROG的员工信息

SELECT *
FROM employees
WHERE job_id NOT IN('SH_CLERK','IT_PROG');

四、between and

功能：判断某个字段的值是否介于xx之间

between and / not between and

案例1：查询部门编号是30-90之间的员工

SELECT *
FROM employees
WHERE department_id BETWEEN 30 AND 90;

案例2：查询年薪不是100000-200000之间的员工姓名、工资、年薪

SELECT last_name,salary,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
FROM employees
WHERE salary*12*(1+IFNULL(commission_pct,0)) NOT BETWEEN 100000 AND 200000;

五、is null / is not null

案例1：查询某个字段为null的员工信息

SELECT *
FROM employees
WHERE xxxx IS NULL

= 只能判断普通的内容

IS 只能判断NULL值

<=> 安全等于，既能判断普通内容，又能判断NULL值

排序查询

语法：

SELECT 查询列表
FROM 表名
[WHERE 筛选条件]
order by 排序列表

执行顺序：

1、from子句

2、where子句

3、select子句

4、order by子句

特点：

1、排序列表可以是单个字段、多个字段、表达式、函数、列数、以及以上的组合

2、升序通过asc指定，默认

​ 降序通过desc

一、按单个字段排序

案例1：将员工编号>120的员工信息进行工资升序的排序

SELECT *
FROM employess
WHERE emoloyee_id > 12 ORDER BY salary ASC;

二、表达式查询

案例2：对有奖金的员工，按年薪降序

SELECT *,salary*()+IFNULL(commission_pct,0) AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary*()+IFNULL(commission_pct,0) DESC;

三、按别名排序

同上

SELECT *,salary*()+IFNULL(commission_pct,0) AS 年薪
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY 年薪 DESC;

四、按函数的结果排序

案例1：按姓名的字数长度进行升序

SELECT last_name
FROM employees
ORDER BY LENGTH(last_name);

五、按多个字段排序

案例1：查询员工的姓名、工资、部门编号，先按工资升序，再按部门编号降序

SELECT last_name,salary,department_id

FROM employees

ORDER BY salary ASC , department_id DESC;

分组查询

当需要分组查询时使用GROUP BY语句

例如查询每个部门的工资和:

SELECT SUM(salary),department_id
FROM employees
GROUP BY department_id;

语法：

SELECT 查询列表

FROM 表名

WHERE 分组前筛选条件

GROUP BY 分组列表

HAVING 分组后筛选条件

ORDER BY 排序列表；

执行顺序：

1、from

2、where

3、group by

4、having

5、select

6、order by

特点：

1、查询列表往往是分组函数和被分组的字段

2、分组查询中的筛选分为两类

	筛选的基表	关键词	位置
分组前筛选	原始表	WEHRE	GROUP BY 前
分组后筛选	分组后的结果集	HAVING	GROUPBY后

3、MySQL 5.7之后，默认启用一个SQL MODE：ONLY_FULL_GROUP_BY，该设置要求SELECT中的列必须在GROUP BY中出现。

一、分组函数

常见函数中介绍的都是单行函数，一般操纵一个数值返回一个结果

分组函数也可以称作聚合函数

分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

分组数清单：

sum(字段名) ：求和

avg(字段名)：求平均数

max(字段名)：求最大值

min(字段名)：求最小值

count(字段名)：计算非空字段值的个数

---

案例1：查询员工表中，所有员工的工资和、工资平均值、最低工资、最高工资、有工资的个数

SELECT SUM(salary),AVG(salary),MIN(salary).COUNT(salary) FROM employees;

案例2：添加筛选条件

1查询emp表中记录数

SELECT COUNT(employee_id) FROM employees;

2查询emp表中有佣金的人数

SELECT COUNT(salary) FROM employees;

二、count(*)和count(1)的补充介绍

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的
通常情况下优先优先考虑count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。

可以查出表中数据个数，统计结果集行数

SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

2、搭配distinct实现去重的统计

需求：查询有员工的部门个数

SELECT COUNT(DISTINCT department_id) FORM employees;

三、分组查询案例

案例1：查询每个工种的员工平均工资

SELECT AVG(salary),job_id
FROM emoloyees
GROUP BY job_id;

案例2：查询每个领导的手下人数

SELECT COUNT(*),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

案例3：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary) 最高工资,department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

四、实现分组后的筛选

案例4：查询部门的员工个数>5的部门

SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

案例5：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT MAX(salary),job_id

FROM employees

WHERE commision_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

五、按多个字段分组

案例：查询每个工种每个部门的最低工资，并按最低工资降序

工种部门都一样才是一组

SELECT MIN(salary) 最低工资,job_id,,department_id
FROM employees
GROUP BY job_id,department_id;

分页查询

应用场景：当页面上的数据，一页显示不全，则需要分页显示

分页查询的sql请求数据库服务器—>服务器响应查询到的多条数据---->前台页面

语法：

SELECT 查询列表
FROM 表1	别名
JOIN 表2 别名
WHERE 筛选条件
GROUP BY 分组
HAVING 分组后筛选
ORDER BY 排序列表
LIMIT 起始条目数，显示的条目数

LIMIT最后执行

特点：

1、起始条目索引从0开始，默认是0

2、LIMIT支持两个参数，1：起始条目索引，2、查询条目数

如果要显示的页数是page，每页显示的条目数为size

SELECT *
FROM employees
LIMIT size*(page-1),size;

联合查询

说明：当查询结果来自于多张表，但多张表之间没有关联，这个时候通常使用联合查询，也称为union查询。

SELECT 查询列表 FROM 表1 WHERE 筛选条件
UNION
SELECT 查询列表 FROM 表2 WHERE 筛选条件

特点：

1、多条待联合的查询语句的查询列数必须一致，查询类型、字段意义最好一致

2、union实现去重查询，union all实现全部查询

案例：显示所有国家年龄大于20岁的用户信息。

SELECT * FROM chinese WHERE age>20 
UNION
SELECT * FROM usa WHERE uage>20

案例2：查询所有国家的用户姓名和年龄

错误，查询列数不相同：

SELECT uname,uage FROM usa
UNION
SELECT name FROM chinese;

一般搜索的列数和意义都要对应

案例3：UNION字段自动去重 / union all 可以支持重复项

SELECT 1,'范冰冰'
UNION
SELECT 2,'范冰冰'
UNION
SELECT 3,'范冰冰'
UNION
SELECT 4,'范冰冰';

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询；

笛卡尔积的错误情况

表1有m行，表2有n行，结果=m*n行

发生原因：没有有效的链接条件

如何避免：添加有效的链接条件

select count(*) from beauty; 输出12行

select count(*) from boys; 输出4行

select name,boyName from beauty,boys; 输出12*4=48行 错误

SELECT NAME,boyName FROM boys,beauty
WHERE beauty.boyfriend_id = boys.id;

连接分类：

​ 按照年代分类：

​ sql92标准

​ sql99标准

​ 按功能分类：

​ 内连接：

​ 等值连接

​ 非等值连接

​ 自连接

​ 外连接

​ 左外连接

​ 右外连接

​ 全外连接

​ 交叉连接

---

sql99语法

SELECT 查询列表
FROM 表1 别名 【连接类型】
JOIN 表2 别名 
ON 连接条件
【WHERE 筛选条件】
【group by】
【having】
【order by】;

连接类型分类：

内连接：inner

外连接：

​ 左外：left 【outer】

​ 右外：riht 【outer】

​ 全外：full 【outer】

交叉连接：cross

一、内连接

语法：

SELECT 查询列表
FROM 表1 别名
inner join 表2 别名
ON 连接条件;

分类：

1、等值连接

特点：

1、添加排序、分组、筛选

2、inner可以省略

3、筛选条件放在where后面，连接条件放在on后面

4、inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集。

案例1：查询员工名、部门名

SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.'department_id' = d.'department_id';

案例2：查询名字中包含e的员工名和工种名

SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.'job_id' = j.'job_id'
WHERE e.'last_name' LIKE '%e%';

使用INNER JOIN时 MySQL会自动判断哪个表适合做驱动表 哪个表适合做被驱动表，当认为MYSQL选择有问题时，可以使用STRAIGHT_JOIN （直连）

STRAIGHT_JOIN功能和INNER JOIN一样，但是可以指定谁是驱动表、谁是被驱动表

2、非等值连接

连接条件不是等号

案例1：查询员工的工资和工资级别

SELECT salary,grade_level
FROM employees e
JOIN job grades g
ON e.'salary' BETWEEN g.'lowest_sal' AND g.'higest_sal';

3、自连接

相当于等值连接，自连接是自己连接自己

案例1：查询员工的名字、上级的名字，因为上级也是员工，在员工表中 所以是内连接。

SELECT e.last_name , m.last_name
FROM employees e
JOIN employees m
ON e.'manager_id' = m.'employee_id';

二、外连接（左连接、右连接）

说明：查询结果为主表中所有的记录，如果从表有匹配项，则显示匹配项；如果从表没有匹配项，则显示null；

应用场景：一般用于查询主表中有但从表中没有的记录

特点：

1、外连接分主从表，两表的顺序不能调换

2、左连接的话，左边为主表；右连接的话，右边为主表

语法：

SELECT 查询列表
FROM 表1 别名
LEFT | RIGHT 【OUTER】 JOIN 表2
ON 连接条件
【WHERE】筛选条件

案例1：查询所有的女生记录，以及对应的男生名，如果没有对应的男生则显示null

SELECT b.*,bo.*
FROM beauty b
LEFT JOIN boys bo ON b.'boyfriend_id' = bo.'id';

案例2：查询哪个部门没有员工，并显示其部门编号和部门名

SELECT d.'department_id',d.'department_name'
FROM departments d
LEFT JOIN employees e ON d.'department_id' = e.'department_id'
WHERE e.'employee_id' IS NULL;

全外连接

MYSQL不支持 查询结果会将主表和从表的内容都展示出来

子查询

说明：当一个查询语句中又嵌套了另一个完整的select语句，则被嵌套的select语句称为子查询或内查询

外面select语句称为主查询或外查询

子查询不一定必须出现在select语句内部

示例：

SELECT first_name 
FROM employees 
WHERE department_id>(
SELECT department_id
    FROM departments
    WHERE location id = 1700
)

分类：

按子查询出现的位置进行分类

1、select后面

​ 要求：子查询的结果为单行单列（标量子查询）

2、from后面

​ 要求：子查询的结果可以为多行多列

3、where或者having后面

​ 要求：子查询的结果必须为单列

4、exists后面 (相关子查询)

​ 要求 子查询结果必须为单列

特点：

​ 1、子查询放在条件中，要求必须放在条件的右侧

​ 2、子查询一般放在小括号中

​ 3、子查询的执行优先于主查询

​ 4、单行子查询对应了单行操作符 ：>< >= <=

​ 5、多行子查询对应了 多行操作符： any /some all in

子查询效率较低