MySQL总结一

前言

本文主要对尚硅谷《MySQL核心技术》课程笔记做了相关整理，分为一二两个部分，第一部分主要是数据查询语言DQL相关语法，第二部分主要是数据操纵语言DML，数据定义语言DDL，数据控制语言DCL相关语法，以方便各位同学查询和复习，再次感谢尚硅谷能免费公开相关课程视频。

SQL脚本文件：MySQL总结（脚本文件）

一、MySQL常用指令

1、启动数据库服务：
net start mysql0815;

2、停止数据库服务：
net stop mysql0815;

3、登录：
mysql -h localhost -P 3306 -u root -proot;

4、退出：
exit;

5、显示数据库：
show databases;

6、切换数据库：
use d_stuinfo;

7、显示数据表：
show tables;

8、显示数据表结构：
desc t_stuinfo;

9、查看字符集
SHOW VARIABLES LIKE '%char%'

二、DQL语言

2.1、基础查询

2.1.1、语法：

select 
    查询列表
from 
    表名;

2.1.2、特点：

• 查询列表可以是表中字段、常量值、表达式、函数
• 查询结果是一个虚拟表格

2.1.3、样例：

• 表中字段
• 常量值和表达式
• 函数
• AS 空格 起别名
• 去重 DISTINCT
• • 的作用

# 一、AS 空格 起别名
SELECT
	first_name AS fn,
	last_name ln
FROM
	employees;

# 二、''避免和关键字冲突，所以字段命名时需注意
SELECT 
    `first_name`,
    `last_name`
FROM
    employees ;

# 三、只要一方是数值型，则会将字符型数值转换成数值型，成功则数值相加，否则转换成0
select '123'+90; 

# 四、null+任意类型结果为null
select null+10;

2.2、条件查询

2.2.1、语法：

select 
    查询列表
from
    表名
where
    筛选条件

2.2.2、分类：

• 按条件表达式筛选：

条件运算符：> < = <>（不建议使用!=） >= <=

• 按逻辑表达式筛选：

逻辑运算符：&& || !（and or not）

3、模糊查询：

• like

通配符：

1. % 任意多个字符,包含0个字符
2. _ 任意单个字符

• between and

1. 使用between and 可以提高语句的简洁度
2. 包含临界值
3. 两个临界值不要调换顺序

• in

1. 使用in提高语句简洁度
2. in列表的值类型必须一致或兼容
3. in列表中不支持通配符

• is null

1. =或<>不能用于判断null值
2. is null或is not null 可以判断null值

2.2.3、举例：

#一、按条件表达式筛选
#案例：查询部门编号不等于90号的员工名和部门编号
SELECT *
FROM employees
WHERE department_id <> 90;

#二、按逻辑表达式筛选
#案例：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

#三、模糊查询
#案例：查询员工名中第二个字符为_的员工名
SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';
last_name LIKE '_\_%';

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT *
FROM employees
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT *
FROM employees
WHERE job_id IN ('IT_PROG', 'AD_VP', 'AD_PRES');

#案例：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct
FROM employees
WHERE commission_pct IS NULL;

2.3、排序查询

2.3.1、语法

SELECT
    查询列表
FROM
    表名    
WHERE
    筛选条件
ORDER BY
    排序条件

2.3.2、特点

• asc升序，默认；desc降序
• 排序子句可以是单个字段，别名，表达式，函数，多个字段
• 排序子句放在查询语句的最后面，limit子句前面

2.3.3、举例

#案例：查询员工信息 按年薪降序
SELECT *,salary * 12 * (1 + IFNULL(commission_pct, 0)) annual
FROM employees
ORDER BY annual DESC;

#案例：查询员工名，并且按名字的长度降序
SELECT last_name, first_name
FROM employees
ORDER BY LENGTH(last_name) DESC, LENGTH(first_name) DESC;

2.4、常见函数

2.4.1、概念

将一组逻辑语句封装在方法体内，对外暴露方法名

2.4.2、优点

• 隐藏实现细节
• 提高代码重用性

2.4.3、调用

select 函数名（实参列表） from 表名

2.4.4、分类

• 单行函数

concat、length、ifnull等

• 分组函数

做统计使用，又称统计函数、聚合函数、组函数

2.4.5、常见函数

• 字符函数

1. length：获取字节个数（utf8 汉字3个字节；gbk 2个字节）
2. concat:
3. substr:
4. instr:返回子串第一次出现的索引，如果找不到返回0，索引从1开始
5. trim
6. upper
7. lower
8. lpad
9. rpad

#lpad rpad用指定的字符实现左填充指定长度，如果长度小于源字符串长度，则会截取
SELECT LPAD('殷素素',2,'*') result;
殷素
SELECT RPAD('殷素素',12,'ab') AS out_put;
殷素素ababababa

10. replace

• 数学函数

1. round
2. ceil
3. floor
4. truncate：小数点后截断

SELECT TRUNCATE(1.69999,3);

5. mod

• 日期函数

1. now
2. curdate
3. curtime
4. year
5. month
6. day
7. hour
8. minute
9. second
10. str_to_date

SELECT 
* 
FROM
employees
WHERE
hiredate < STR_TO_DATE('2000-1-1', '%Y-%c-%d');

11. date_format

#查询有奖金的员工名和入职日期(xx月/xx日 xx年)
SELECT last_name, DATE_FORMAT(hiredate, '%c月/%d日 %y年')
FROM employees
WHERE commission_pct IS NOT NULL;

• 其他函数

1. version
2. database
3. user

• 控制函数

1. if

SELECT IF(10=10,'等于','不等于');

2. case

# 部门id为10的员工薪资乘以1.1，部门id为20的员工薪资乘以1.2，其他部分的员工薪资不变
SELECT
salary, department_id, 
CASE department_id
WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
ELSE salary
END after_salary
FROM
employees;

# 显示员工薪资级别
SELECT last_name, salary, 
CASE
WHEN salary > 20000 THEN 'a'
WHEN salary > 15000 THEN 'b'
WHEN salary > 10000 THEN 'c'
ELSE 'd'
END 'level'
FROM employees;

2.5、分组查询

2.5.1、语法

SELECT
    查询列表
FROM
    表名
WHERE
    筛选条件
GROUP BY
    分组条件
HAVING
    分组后筛选条件
ORDER BY
    排序条件

2.5.2、特点

• 和分组函数一同查询的字段必须是group by后出现的字段

• 筛选条件分为两类：分组前筛选和分组后筛选。一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

分组条件	针对的表	位置	连接的关键字
分组前筛选	原始表	group by前	where
分组后筛选	group by后的结果集	group by后	having

• 分组可以按单个字段也可以按多个字段

2.5.3、举例

#引入：查询每个部门的员工个数

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

#案例1：查询每个工种的员工平均工资
SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;

#案例2：查询每个位置的部门个数
SELECT location_id, COUNT(*)
FROM departments
GROUP BY location_id;

#2、可以实现分组前的筛选
#案例1：查询邮箱中包含a字符的 每个部门的最高工资

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

#案例2：查询有奖金的每个领导手下员工的平均工资
SELECT manager_id, AVG(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#3、分组后筛选
#案例：查询哪个部门的员工个数>5
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT job_id, MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id > 102
GROUP bymanager_id
HAVING MIN(salary) > 5000;

#案例4：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT job_id, MAX(salary) ms
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING ms > 6000
ORDER BY ms ASC;

#案例5：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT job_id, department_id, MIN(salary) mins
FROM employees
GROUP BY job_id, department_id
ORDER BY mins DESC;

2.6、分组函数

2.6.1、功能：

用作统计使用，又称为聚合函数或统计函数或组函数

2.6.2、分类：

sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数

2.6.3、特点：

1. sum、avg一般用于处理数值型；max、min、count可以处理任何类型
2. 以上分组函数都忽略null值
3. 可以和distinct搭配实现去重的运算
4. count函数的单独介绍；一般使用count(*)用作统计行数
5. 和分组函数一同查询的字段要求是group by后的字段

2.6.4、样例：

# 忽略null值
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 
FROM employees;

# 搭配DISTINCT
SELECT SUM(DISTINCT salary),SUM(salary) 
FROM employees;

2.7、连接查询

2.7.1、含义

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

2.7.2、笛卡尔乘积

当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接

2.7.3、分类

• SQL92 等值

  非等值

  自连接

  也支持一部分外连接（用户oracle、sqlserver、mysql不支持）

• SQL99（推荐使用）

1. 内连接

   等值

   非等值

   自连接

2. 外连接

   左外

   右外

   全外（mysql不支持）

3. 交叉连接

2.7.4、SQL92语法

• 等值连接

SELECT 
    查询列表
FROM
    表1 别名1，表2 别名2
WHERE
    表1.key=表2.key
AND
    筛选条件

• 非等值条件

SELECT 
    查询列表
FROM
    表1 别名1，表2 别名2
WHERE
    非等值筛选条件
AND
    筛选条件

• 自连接

SELECT 
    查询列表
FROM
    表1 别名1，表1 别名2
WHERE
    等值连接条件
AND
    筛选条件

2.7.5、SQL99语法

• 内连接

SELECT 
    查询列表
FROM
    表1 别名1
[INNER] JOIN 
    表2 别名2 
ON 
    连接条件
WHERE
    筛选条件

• 外连接

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

• 交叉连接

SELECT 
    查询列表
FROM
    表1 别名1
CROSS JOIN 
    表2 别名2

2.7.6、SQL92样例

#案例1：查询女神名和对应的男神名
SELECT b.name, bo.boyName
FROM beauty b, boys bo
WHERE b.boyfriend_id = bo.id;

#案例2：查询员工名和对应的部门名
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id;

#案例3：查询员工名、工种号、工种名
SELECT e.last_name, e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.`job_id`;

#案例4：查询有奖金的员工名、部门名
SELECT e.last_name, d.department_name
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL;

#案例5：查询城市名中第二个字符为o的部门名和城市名
SELECT d.department_name, l.city
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
AND l.`city` LIKE '_o%';

#案例6：查询每个城市的部门个数
SELECT COUNT(d.department_name) , l.`city`
FROM departments d, locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`;

#案例7：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT d.department_name, e.manager_id, MIN(salary)
FROM employees e, departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL
GROUP BY e.department_id;

#案例8：查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT j.job_title, COUNT(*) 员工数
FROM employees e, jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.job_title
ORDER BY 员工数 DESC;

#案例9：查询员工名、部门名和所在的城市
SELECT e.last_name, d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id`;

#案例10：查询员工的工资和工资级别
SELECT e.last_name, e.salary, jg.grade_level
FROM employees e, job_grades jg
WHERE e.`salary`>jg.lowest_sal AND e.`salary`<jg.highest_sal;

#案例11：查询 员工名和上级的名称
SELECT e1.last_name, e1.`manager_id`,e2.`employee_id`,e2.`last_name`
FROM employees e1, employees e2
WHERE e1.`manager_id`=e2.`employee_id`;

2.7.7、SQL99样例

#案例1.查询员工名、部门名
SELECT e.last_name, d.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 last_name LIKE '%e%'; 

#案例3. 查询部门个数>3的城市名和部门个数，（添加分组+筛选）
SELECT l.city, COUNT(*)
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY l.`city`
HAVING COUNT(*)>3;

#案例4.查询部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
SELECT d.department_name, COUNT(*)
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY d.department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#案例5.查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
SELECT e.last_name, d.department_name, j.job_title
FROM employees e
JOIN departments d 
ON e.`department_id`=d.`department_id` 
JOIN jobs j
ON e.`job_id`=j.`job_id`;

#案例6、查询员工的工资级别
SELECT e.last_name, jg.grade_level
FROM employees e
JOIN job_grades jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`

#案例7、查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*), jg.`grade_level`
FROM employees e
JOIN job_grades jg
ON e.`salary` BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY jg.`grade_level`
HAVING COUNT(*)>20
ORDER BY jg.`grade_level` DESC;

#案例8、查询员工的名字、上级的名字（自连接）
SELECT e1.last_name, e2.last_name manager_name
FROM employees e1
JOIN employees e2
ON e1.`manager_id`=e2.`department_id`;

#查询姓名中包含字符k的员工的名字、上级的名字
SELECT e1.last_name, e2.last_name
FROM employees e1
JOIN employees e2
ON e1.`manager_id`=e2.`employee_id`
WHERE e1.`last_name` LIKE '%k%';

#外连接
#案例1：查询男朋友 不在男神表的的女神名(外连接)
SELECT b.name, b.boyfriend_id, bo.`boyName`
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.`id`
WHERE bo.`boyName` IS NULL;

#案例2：查询哪个部门没有员工(外连接)
SELECT d.*
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

#案例3、查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填
SELECT b.* , bo.*
FROM beauty b
LEFT JOIN boys bo
ON b.boyfriend_id=bo.id
WHERE b.id>3;

#案例4、查询哪个城市没有部门
SELECT l.city 
FROM locations l
LEFT JOIN departments d
ON l.`location_id`=d.`location_id`
WHERE d.`department_id` IS NULL;

#案例5、查询部门名为SAL或IT的员工信息
SELECT e.* , d.`department_name`
FROM employees e
LEFT JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.`department_name` IN ('SAL', 'IT')

2.8、子查询

2.8.1、含义

出现在其他语句中的select语句，称为子查询；外部的查询语句称为主查询。

2.8.2、分类

• 按结果集的行列数

1. 标量子查询
2. 列子查询
3. 行子查询
4. 表子查询

• 按出现位置

1. select后面：

   标量子查询

2. from后面：

   表子查询

3. where或having后面：

   标量子查询

   列子查询

   行子查询

4. exists后面（相关子查询）

   表子查询

2.8.3、举例

#一、where后面
#1.标量子查询
#案例1：谁的工资比 Abel 高?
SELECT *
FROM employees 
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='abel'
)

#案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id 和工资
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
)

#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
)

#案例4：查询最低工资大于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
)

#2.列子查询（多行子查询）
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT department_id
	FROM departments
	WHERE location_id IN (1400, 1700)
)

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
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'

#案例3：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工   的员工号、姓名、job_id 以及salary
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'

#3、行子查询（结果集一行多列或多行多列）
#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id), MAX(salary)
	FROM employees
)

#二、select后面
#案例：查询每个部门的员工个数
SELECT d.*, (
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id=d.department_id
) 'count'
FROM departments d

#案例2：查询员工号=102的部门名
SELECT last_name, (
	SELECT department_name
	FROM departments d
	WHERE e.department_id=d.department_id
) 'department_name'
FROM employees e
WHERE employee_id=102

#三、from后面
#案例：查询每个部门的平均工资的工资等级
SELECT t_avg_salary.department_id, t_avg_salary.avg_salary, jg.grade_level
FROM (
	SELECT department_id, AVG(salary) 'avg_salary'
	FROM employees
	GROUP BY department_id
) t_avg_salary
JOIN job_grades jg
ON t_avg_salary.avg_salary BETWEEN jg.lowest_sal AND jg.highest_sal;

#四、exists后面（相关子查询）
EXISTS(完整的查询语句)
结果：
1或0
SELECT EXISTS(
	SELECT employee_id 
	FROM employees 
	WHERE salary=24000
) result;
#案例1：查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE e.`department_id`=d.`department_id`
)

#案例2：查询没有女朋友的男神信息
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT *
	FROM beauty b
	WHERE b.`boyfriend_id`=bo.id
)

2.9、分页查询

2.9.1、定义

当要显示的数据一页显示不完，需要分页提交sql请求

2.9.2、语法

SELECT 查询列表
FROM 表1
JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组条件
HAVING 分组后筛选条件
ORDER BY 排序条件
LIMIT 起始索引(page-1)*size,条目个数(size)

2.9.3、举例

#年薪最高前10名显示出来
SELECT last_name, salary, commission_pct, salary*(12 + IFNULL(commission_pct,0)) annual_salary
FROM employees
ORDER BY annual_salary DESC
LIMIT 0, 10;

2.10、联合查询

2.10.1、定义

将多条查询语句的结果合并成一个结果

2.10.2、特点

1. 要求多条查询语句的查询列数一致
2. 要求多条查询语句的列类型和顺序一致
3. UNION关键字默认去重，如果使用UNION ALL可以包含重复项

2.10.3、语法

查询语句1
UNION
查询语句2
UNION
...

2.10.4、举例

#查询部门编号>90或邮箱包含a的员工信息
SELECT *
FROM employees
WHERE employee_id>90
UNION
SELECT *
FROM employees
WHERE email LIKE '%a%'