MySQL

一，数据库

1，数据库的好处： （1）实现数据持久化 （2）使用完整的管理系统同意管理，便于查询 2，数据库的概念 （1）数据库（database）简称 DB （2）数据库管理系统（DBMS) （3）结构化查询语言(SQL)

3，数据库管理系统 （1）MySQL （2）Oracle （3）SQL Server （4）PostgreSQL （5）MongoDB

4，数据库储存数据的特点 将数据放表中，表放在库中

二，Linux中下载MySQL

1，MySQL安装版本：8.x

# 卸载冲突软件
rpm –e –nodeps `rpm -qa|grep mariadb`
rpm –e –nodeps `rpm -qa|grep mysql`

# 离线安装mysql
rpm –ivh mysql–community–common–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–client–plugins–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–libs–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–libs–compat–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–client–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–icu–data–files–8.0.34–1.el7.x86_64.rpm
rpm –ivh mysql–community–server–8.0.34–1.el7.x86_64.rpm

# 初始化
mysqld –initialize
# 改权限
chown –R mysql:mysql /var/lib/mysql
# 启动MySQL
systemctl start mysqld.service
# 查看状态
systemctl status mysqld.service
# 开机自启动
systemctl enable mysqld.service
# 获取初始密码
grep "password" /var/log/mysqld.log
# 登录Mysql
mysql –uroot –p密码
# 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 刷新权限
FLUSH PRIVILEGES;
# 退出
exit
# 切换数据库
USE mysql;
# 查看用户
SELECT user,host,plugin,authentication_string FROM user;
# 创建新用户
CREATE user 'root'@'%';
# 设置密码
ALTER USER 'root'@'%' IDENTIFIED BY '123456';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# 增加库和表的权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

三，MySQL

MySQL是目前最流行的开源关系型数据库管理系统（RDBMS）。它使用 SQL（结构化查询语言）进行数据库操作。

1，MySQL的入门使用 查看状态：systemctl status mysqld.service 启动：systemctl start mysqld 停止：systemctl stop mysqld 登录：mysql -u用户名 -p密码 退出：exit/quit 2，MySQL常用命令 查看当前数据库：show database 进入指定库：use 库名 查看当前库中所有表：show tables 查看mysql版本：mysql –version/select version() 3,MySQL语法规范 不区分大小写，但是建议关键字大写，表名，字段名小写 每条SQL语句用分号结束 进行缩进 单行注释：#/– 多行注释：/**/ 4，DQL语言 下表是我数据库中表的信息

（1）基础查询

select 查询列表 from 表名;
–查询所有
select * from 表名;
–起别名 AS(可以省略并使用空格替代)，显示的结果字符串中有空格需要加引号，否则报错
select
first_name AS 名,
last_name 姓
from
employees;
–去重 DISTINCT
select DISTINCT 查询列表 from 表名;
/*
1、两个操作数都为数值型，则做加法运算
2、只要其中一方为字符型，试图将字符型数值转换成数值型。
如果转换成功，则继续做加法运算，如果转换失败，则将字符型数值转换成0后再做运算。
3、只要其中一方为 null，则结果肯定为null
*/
select 66 + '九十';
select null + 10;
–concat()函数
select CONCAT('h','e','l','l','o') as 结果;–结果为holle连在一起
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;
–显示表 departments 的结构
desc departments;
— 6、显示出表 employees 的 first_name、last_name、job_id、commission_pct 列，各个列之间用逗号连接，列头显示成 OUT_PUT
— ifnull(expr1, expr2)
–如果 expr1 不是 NULL，则返回 expr1；
–如果 expr1 是 NULL，则返回 expr2。
SELECT
CONCAT(first_name,",",last_name,",",job_id,",",ifnull(commission_pct,0)) as OUT_PUT
FROM
employees;

（2）条件查询 [– 条件查询总结语法格式： – select – 查询列表(3) – from – 表名(1) – where – 筛选条件(2);

– 筛选条件分类： – （1）按条件表达式筛选 – 简单条件运算符：> 、< 、>=、<=、!=(<>) – （2）按逻辑表达式筛选 – &&(and)、||(or)、!(not) – &&(and):两个条件都为true，结果为true，反之为false – ||(or)：只要有一个条件为true，结果为true，反之为false – !(not)：如果连接的条件本身为false，结果为true，反之为false – （3）模糊查询 – LIKE、BETWEEN … AND …、IN()、IS、IS NOT、、IS NOT NULL、IS NULL – LIKE：一般和通配符搭配使用 – 通配符： – % 任意多个字符，包含0个加粗样式字符 – _ 任意单个字符加粗样式 – BETWEEN … AND …：包含两个临界值，两个临界值不要调换顺序，提高语法的简洁度。 – IN()：判断某个字段的值是否属于in列表中的某一项，in列表的值类型必须一致或兼容，提高语法的简洁度。 – IS NULL：is null 或者 is not null 判断控制，注意：= 或 <>/!= 不能判断null值。]

select
查询列表 （3）
from
表名v （1）
where
筛选条件 （2）
/*
筛选条件分类
（1）按条件表达式筛选
简单条件运算符： >、<、=、!= (<>)、>=、<=
（2）按逻辑表达式筛选
逻辑运算符： &&（and）、|| （or）、!（not）
&&（and）：两个条件都为true，结果为true，反之为false
||（or）：只要有一个条件为true，结果为true，反之为false
!（not）：如果连接的条件本身为false，结果为true，反之为false
（3）模糊查询
like、between and、in、is null
*/
–按条件表达式筛选
SELECT
*
FROM
employees
WHERE
salary > 12000;
–按逻辑表达式筛选
#eg:查询工资在10000到20000之间的员工名、工资以及奖金 （奖金使用奖金率替代）
SELECT
first_name,
salary,
commission_pct
FROM
employees
WHERE
salary >= 10000 and salary <=20000;
–模糊查询
# eg:查询员工名中包含字符a的员工信息
SELECT
*
FROM
employees
WHERE
first_name like '%a%';
# eg:查询员工名中第三个字符为e，第五个字符为a的员工名和工资
SELECT
first_name,
salary
FROM
employees
WHERE
first_name like '__e_a%';
# eg:查询员工姓中第二个字符为 _ 的员工名和员工姓
SELECT
first_name,
last_name
FROM
employees
WHERE
last_name like '_\\_%';
# eg:查询员工编号在100到200之间的员工信息
SELECT
*
FROM
employees
WHERE
employee_id BETWEEN 100 AND 200;
# eg:查询员工工种编码是：IT_PROG、AD_ASST、AD_VP 中的一个员工名和工种编号。
SELECT
first_name,
job_id
FROM
employees
WHERE
job_id in('IT_PROG','AD_ASST','AD_VP');
# eg:查询没有奖金的员工名和奖金率
SELECT
first_name,
commission_pct
FROM
employees
WHERE
commission_pct is null;

SELECT
first_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> null;
# is null VS <=>
# is null 仅仅可以判断null值，可读性较高，建议使用
# <=> 既可以判断null值，又可以判断普通的数值，可读性较低，不建议使用。

(3)排序查询 [排序查询总结语法格式： – select – 查询列表 (3) – from – 表名 (1) – where 筛选条件 – order by 排序列表 ASC | DESC

– （1）ASC:代表升序；DESC代表降序，如果不写，默认升序。 – （2）order by 子句中可以支持单个字段、多个字段、表达式、函数、别名 – （3）order by 子句一般是放在查询语句的最后面，但是limit子句除外。]

/*
select
查询列表 （3）
from
表名 （1）
[where 筛选条件] （2）
order by
排序列表 [asc | desc] （4）
特点：
1、asc代表的是升序，desc代表的是降序，如果不写，默认是升序。
2、order by 子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by 子句一般是放在查询语句的最后面，limit子句除外
*/
# eg:查询员工信息，要求工资从高到底排序
SELECT
*
FROM
employees
ORDER BY
salary desc;
# eg：按年薪的高低显示员工的信息和年薪（按表达式排序）
SELECT
*,
salary * 12 * (1 + ifnull(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY
salary * 12 * (1 + ifnull(commission_pct,0)) desc;
# eg:按年薪的高低显示员工的信息和年薪（按别名排序）
SELECT
*,
salary * 12 * (1 + ifnull(commission_pct,0)) AS 年薪
FROM
employees
ORDER BY
年薪 desc;
# eg:按姓名的长度显示员工的姓名和工资（按函数排序）
# concat() 字符串的拼接
# length() 获取字符串的长度
SELECT
length(concat(last_name,first_name)) AS 姓名长度,
concat(last_name,first_name) AS 姓名,
salary
FROM
employees
ORDER BY
length(concat(last_name,first_name)) desc;
# eg:查询员工信息，要求先按工资升序，再按员工编号降序（按多个字段排序）
SELECT
*
FROM
employees
ORDER BY
salary asc,employee_id DESC;

4，常见函数 [– 函数：将一组逻辑语句封装在方法中，对外暴露方法名。 类比于python中的方法 – 函数好处：隐藏了细节；提高代码的重用性。 – 函数调用语法格式： – select 函数名(实参列表) [from 表名] – 函数特点：叫什么（函数名称）；干什么（函数功能） – 函数的分类：单行函数；分组函数

– 单行函数：字符函数、数值函数、日期函数、其他函数、流程控制函数-if 、流程控制函数-case结构…]

/*
分类：
1、单行函数
如：concat、ifnull、length
包含：字符函数、数学函数、日期函数、其他函数、流程控制函数-if函数、流程控制函数-case结构
2、分组函数
功能：做统计使用又称统计函数（聚合函数、组函数）。
*/
— 查看mysql默认编码格式：
show variables like '%char%';
— ********************************字符函数*************************************
— 1、length :获取参数值的字节个数
# 注意：对于非ASCII字符（如汉字）,length返回的不是字符的个数，而是字符串的字节数
SELECT LENGTH('hello');
SELECT LENGTH('你好hello');–汉字一个字，三个字符
— 2、concat ：拼接字符串
# concat函数用于将两个或多个字符串连接成一个字符串，可以连接任意数量的字符串并返回一个组合后的字符串
# concat_ws:用于指定分隔符连接字符串。
SELECT CONCAT('hello','world') AS out_put;
SELECT CONCAT_WS('-','hello','world');–结果为：hello-world
SELECT CONCAT(last_name,'_',first_name) AS `name` FROM employees;–CONCAT的时候注意‘-’的位置
— 3、upper、lower
# upper ：用于将字符串转换成大写
# UPPER(str)
# lower ：用于将字符串转换成小写
# LOWER(str)
select UPPER('hello');
SELECT LOWER('HELLO');
# eg: 将姓首字母大写，其他所有字母都小写。
–SUBSTR(字段, start, length)：截取字符串，MySQL下标从 1 开始
select
concat_ws('-',upper(substr(last_name,1,1)),lower(substr(last_name,2)),lower(first_name))
from
employees;
— 4、substr、substring,两个函数完全等价。
# substr ：用于从字符串中提取子串。
# SUBSTR(str FROM pos FOR len)
# str 原始字符串
# pos 是子串的起始位置（从1开始）
# len 要提取子串的长度（可选）
# substring ：用于从字符串中提取子串。
# SUBSTRING(str FROM pos FOR len)
# str 原始字符串
# pos 是子串的起始位置（从1开始）
# len 要提取子串的长度（可选）
# **********substr**********
SELECT SUBSTR('欢迎来到兰智数加学院',5) AS out_put;
— 5、instr ：返回字串第一次出现的索引，如果找不到则返回0
select INSTR('欢迎来到兰智数加学院','学院');–返回9
select INSTR('欢迎来到兰智数加学院','数模');–返回0
select INSTR('欢hhhh迎来到HHHH兰智数加学院','H');–返回2
# 8、
# TRIM()：删除前导空格和尾随空格
# RTRIM()：删除尾随空格
# LTRIM()：删除前导空格
select LENGTH(TRIM(' 兰智数加学院 ')) AS out_put;–返回18
select TRIM(' 兰智数加学院 ') AS out_put;
–思考？"兰智 数加 学院" 中间的空格如何删除
— 把字符串里所有空格，替换成空字符串，实现删除全部空格
SELECT REPLACE('兰智 数加 学院',' ','') AS out_put;

# 9、LPAD(str,len,padstr):返回字符串参数，并在左侧填充指定的字符串。
select lpad('数加',4,'*') AS output;–返回**数加
select lpad('数加',6,'*') AS output;–返回****数加

# 10、RPAD(str,len,padstr):将字符串追加指定次数
select rpad('数加',4,'*') AS output;–返回数加**
–马冬梅 —> 马*梅
SELECT CONCAT(LEFT('马冬梅',1),'*',RIGHT('马冬梅',1)) AS output;

# 11、REPLACE(str,from_str,to_str)：替换指定字符串的所有出现位置
select replace('欢迎来到兰智数加学院','学院','大学') AS out_put;–返回欢迎来到兰智数加大学
— ********************************数学函数*************************************
# 1、ROUND(X,D)：将参数四舍五入X到 D小数位。
SELECT ROUND(3.14);–返回3
SELECT ROUND(3.1415926,2) AS out_put;–返回3.14
SELECT ROUND(3.146,2) AS out_put;–返回3.15
# 2、CEIL()：返回不小于某个值的最小整数值 X。向上取整,返回大于或等于该数值的最小整数
SELECT CEIL(3.1415926); –返回4
SELECT CEIL(–3.1415926);–返回-3
# 3、FLOOR(X)：返回不大于某个特定值的最大整数值 X。向下取整，返回小于或等于该数值的最大整数
SELECT FLOOR(3.14);–返回3
SELECT FLOOR(–3.14);–返回-4
# 4、TRUNCATE(X,D)：用于将数值截断到指定的小数位数，直接去掉多余的小数位而不进行四舍五入
SELECT TRUNCATE(123.456,2);–返回123.45
SELECT TRUNCATE(123.456,0);–返回123
# 5、MOD(N,M)：​N % MN MOD M 用于计算两个数之间的余数（模运算）
# N:被除数
# M:除数
# 计算公式：N-（N/M）*M
SELECT 10 % 3;–返回1
SELECT MOD(10,3);–返回1
— ********************************日期函数*************************************
# 日期函数
# 1、now():返回当前日期和时间的值
# YYYY-MM-DD HH:MM:SS
SELECT NOW(); # 2026-09-02 16:10:31
# 2、CURDATE()：返回当前日期
SELECT CURDATE(); # 2026-09-02 YYYY-MM-DD
# 3、CURTIME()：返回当前时间
SELECT CURTIME(); # 16:11:47 HH:MM:SS
# 可以获取指定部分的时间：年、月、日、时、分、秒
# 4、YEAR()：返回年份
SELECT YEAR(NOW()); –返回2026
SELECT YEAR('2027-08-17');–返回2027
— eg:查看员工表中入职时间的年份信息
SELECT YEAR(hiredate) FROM employees;
# 5、MONTH()：返回过去日期之后的月份
SELECT MONTH(NOW()) ;–返回9
# 6、MONTHNAME()：返回月份名称
SELECT MONTHNAME(NOW());–返回September
# 7、DAY()：返回日
SELECT DAY(NOW());–返回2
# 8、HOUR()：提取小时
SELECT HOUR(NOW());–返回16
# 9、MINUTE()：从参数中返回分钟数。
SELECT MINUTE(NOW());–返回14
# 10、SECOND()：返回秒
SELECT SECOND(NOW());–返回58
# 11、STR_TO_DATE(str,format)：将字符串转换为日期
# str：要转换的日期和时间字符串
# format：指定字符串的格式
/*
格式化符号：
%Y：四位数字的年份。
%y：两位数字的年份。
%m：两位数字的月份（01 到 12）。
%c：月份，数值（0 到 12）。
%d：两位数字的日期（00 到 31）。
%e：日期，数值（0 到 31）。
%H：两位数字的小时，24 小时制（00 到 23）。
%h：两位数字的小时，12 小时制（01 到 12）。
%i：两位数字的分钟（00 到 59）。
%s：两位数字的秒（00 到 59）。
%p：AM 或 PM
*/
SELECT STR_TO_DATE('2026-08-17','%Y-%m-%d') AS out_put;–返回2026-08-17
# eg:查询入职日期为：1992-04-03的员工信息
SELECT
*
FROM
employees
WHERE
hiredate = '1992-04-03';

SELECT
*
FROM
employees
WHERE
hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
# 12、DATE_FORMAT(date,format)：将日期或日期时间值格式化为指定的字符串格式
# date：要格式化的日期或者日期时间
# format：指定结果字符串的格式
/*
格式化符号：
%Y：四位数字的年份。
%y：两位数字的年份。
%M：月份名称（January 到 December）。
%m：两位数字的月份（01 到 12）。
%c：月份，数值（1 到 12）。
%D：带有英文序数后缀的月份中的天（1st, 2nd, 3rd, …）。
%d：两位数字的日期（00 到 31）。
%e：日期，数值（0 到 31）。
%H：两位数字的小时，24 小时制（00 到 23）。
%h：两位数字的小时，12 小时制（01 到 12）。
%i：两位数字的分钟（00 到 59）。
%s：两位数字的秒（00 到 59）。
%p：AM 或 PM。
%W：星期名称（Sunday 到 Saturday）。
%w：星期中的天（0 = Sunday, 6 = Saturday）。
%j：一年中的天数（001 到 366）。
*/
select DATE_FORMAT(NOW(),'%y年%m月%d日');–返回26年09月02日
# eg:查询有奖金率的员工名和入职日期（xx月/xx日 xx年）
SELECT
first_name,
DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM
employees
WHERE
commission_pct is not null;
— ********************************流程控制函数*************************************
# 流程控制函数
# 1、IF(expr1,expr2,expr3)：用于查询中进行条件判断的流程控制函数
# expr1：要判断的条件表达式，如果条件为真（非零或非空），则返回expr2，否则返回expr3
# expr2：条件为真时返回的值
# expr3：条件为假时返回的值
SELECT IF(2 < 3,'大','小') AS out_put;–返回小
# eg:查询员工的姓和名以及奖金率，如果有奖金率则返回有，没有则返回无并以备注为列名。
/*
分析：
查询的表：employees
查询的字段：last_name first_name commission_pct 备注
查询的条件：无
排序的条件：无
*/
SELECT
last_name,
first_name,
commission_pct,
IF(commission_pct IS NULL,'无','有') AS 备注
FROM
employees;
# 2、CASE：是一种流程控制函数，类似编程语言中的switch语法。
# 语法格式1：
# CASE value WHEN compare_value THEN result [WHEN compare_value THEN result …] [ELSE result] END
# value：需要进行比较的表达式或者列
# compare_value：进行比较的表达式或值
# result：当value等于compare_value时返回的结果
# [ELSE result]：如果没有条件表达式为真时，则返回的默认结果

# 语法格式2：
# CASE WHEN condition THEN result [WHEN condition THEN result …] [ELSE result] END
# condition：条件表达式，可以时任何布尔表达式
# result：当条件表达式为真时返回的结果
# ELSE result：如果没有条件表达式为真时，则返回的默认结果

# eg:查询员工的工资，要求如下：
# （1）部门号=30，显示的工资为1.1倍
# （2）部门号=40，显示的工资为1.2倍
# （3）部门号=50，显示的工资为1.3倍
# （4）其他部门，显示的工资为原工资
/*
分析：
查询的表：employees
查询的字段：salary department_id 新工资
查询的条件：无
排序的条件：无
*/
# 语法格式1：
SELECT
salary,
department_id,
CASE
department_id
WHEN 30 THEN
salary * 1.1
WHEN 40 THEN
salary * 1.2
WHEN 50 THEN
salary * 1.3 ELSE salary
END AS 新工资
FROM
employees;
# 使用语法格式2做一遍。
SELECT
salary,
department_id,
CASE
WHEN department_id=30 THEN
salary * 1.1
WHEN department_id=40 THEN
salary * 1.2
WHEN department_id=50 THEN
salary * 1.3 ELSE salary
END AS 新工资
FROM
employees;

# eg:查询员工的工资情况：
# （1）如果工资大于20000，显示A级别
# （2）如果工资大于15000，显示B级别
# （3）如果工资大于10000，显示C级别
# （4）否则，显示D级别
SELECT
salary,
CASE
WHEN salary > 20000 THEN
'A'
WHEN salary > 15000 THEN
'B'
WHEN salary > 10000 THEN
'C'
ELSE
'D'
END AS 工资级别
FROM
employees;
— ********************************分组函数*************************************
# 分组函数：用作统计使用，又称为聚合函数或者统计函数或者组函数
# 分组函数：
# max()：返回最大值
# min():返回最小值
# avg():返回参数的平均值
# count():返回返回的行数
# sum():返回总和
# 总结：
# sum、avg一般用于处理数值型，max、min、count可以处理任何类型
# max、min、avg、count、sum聚合函数都忽略null值
# 可以和distinct搭配实现去重的运算
# 一般使用count(*)做统计函数
# 和聚合函数一同查询的字段要求是 group by 关键字后面的字段
# 1、sum()
SELECT
SUM(salary) AS sum_sal
FROM
employees;
# 2、avg()
SELECT
ROUND(AVG( salary ),2) AS avg_sal
FROM
employees;
# 3、max()
SELECT
MAX( salary ) AS max_sal
FROM
employees;
# 4、min()
SELECT min(salary) AS min_sal from employees;
# 5、count()
SELECT COUNT(salary) as count_sal FROM employees;
SELECT COUNT(commission_pct) as count_sal FROM employees; # 忽略null值

# 总结
# *****************处理数值型*****************
SELECT SUM(last_name),AVG(last_name) FROM employees; # sum、avg不建议用处理字符型

SELECT SUM(hiredate),AVG(hiredate) FROM employees; # sum、avg不建议用处理日期型

SELECT MAX(last_name),MIN(last_name) FROM employees; # max、min适合处理任何类型

SELECT MAX(hiredate),MIN(hiredate) FROM employees; # max、min适合处理任何类型

SELECT COUNT(commission_pct) FROM employees; # count 忽略null值

SELECT COUNT(last_name) FROM employees; # count适合处理任何类型
# 是否忽略null值
SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct) / 35,SUM(commission_pct) / 107 FROM employees;–忽略null

SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;

SELECT COUNT(commission_pct) FROM employees; # count 忽略null值

SELECT COUNT(last_name) FROM employees;
# 搭配distinct实现去重
# max min
SELECT max(distinct salary),min(salary) FROM employees;
SELECT SUM(distinct salary),SUM(salary) FROM employees;
# count
SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
# count()详细说明
SELECT COUNT(salary) FROM employees;–忽略 NULL
SELECT COUNT(*) FROM employees;–不会忽略 NULL
SELECT COUNT(1) FROM employees;–不会忽略 NULL

(5)，分组查询 [– select – 分组函数, – 列名（要求出现在group by后面）(5) – FROM – 表名 (1) – [where 筛选条件] (2) – group by – 分组的列表 (3) – having 子句 (4) – [order by 子句] (6)

1、分组查询中的筛选条件分为两类：

分组前筛选 原始表 group by 子句的前面 where

分组后筛选 分组后的结果集（虚表） group by 子句的后面 having 注意： （1）分组函数做条件肯定是放在having子句中。 （2）能用分组前筛选的，就有限考虑使用分组前筛选。 2、group by 子句支持单字段分组、多字段分组（多个字段之间使用逗号隔开没有先后顺序）、表达式或者函数 3、也可以添加排序（排序放在整个分组查询的最后）]

— 分组查询语法总结：
— select
— 分组函数,
— 列名（要求出现在group by后面）(5)
— FROM
— 表名 (1)
— [where 筛选条件] (2)
— group by
— 分组的列表 (3)
— having 子句 (4)
— [order by 子句] (6)

**# 1、分组查询中的筛选条件分为两类：

# 分组前筛选 原始表 group by 子句的前面 where

# 分组后筛选 分组后的结果集（虚表） group by 子句的后面 having
# 注意：
# （1）分组函数做条件肯定是放在having子句中。
# （2）能用分组前筛选的，就有限考虑使用分组前筛选。
# 2、group by 子句支持单字段分组、多字段分组（多个字段之间使用逗号隔开没有先后顺序）、表达式或者函数
# 3、也可以添加排序（排序放在整个分组查询的最后）

# eg:查询邮箱中包含a字符的每个部门的平均工资。
SELECT
AVG(salary) as avg_sal,
department_id
FROM
employees
WHERE
email like '%a%'
GROUP BY
department_id;

–eg:查询有奖金率的每个领导手下员工的最高工资
SELECT
MAX(salary) as max_sal ,
manager_id
FROM
employees
WHERE
commission_pct is not null
GROUP BY
manager_id;

–eg:查询哪个部门的员工个数大于2
SELECT
COUNT(*),
department_id
from
employees
GROUP BY
department_id
HAVING
COUNT(*) > 2;
–先分类后统计用having

#eg:查询每个工种有奖金率的员工的最高工资大于12000的工种编号和最高工资
# 分析（1）：查询每个工种有奖金率的员工的最高工资
SELECT
max(salary) as max_sal,
job_id
FROM
employees
WHERE
commission_pct is not null
GROUP BY
job_id;

# 分析（2）:根据(1)的结果继续筛选，最高工资大于12000

SELECT
max(salary) as max_sal,
job_id
FROM
employees
WHERE
commission_pct is not null
GROUP BY
job_id
HAVING
max_sal > 12000;
# eg:查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资。
# 分析（1）：查询每个领导手下的员工固定最低工资

SELECT
min(salary),
manager_id
FROM
employees
GROUP BY
manager_id;

# 分析（2）：根据（1）的结果继续添加筛选条件：编号大于102
SELECT
min(salary),
manager_id
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id;

# 分析（3）：根据（2）的结果继续筛选，最低工资大于5000
SELECT
min(salary),
manager_id
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
min(salary) > 5000;
# 按表达式或者函数分组
# eg:按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数大于5的有哪些。
# 分析（1）查询每个长度的员工个数
SELECT
COUNT(*),
LENGTH(CONCAT(last_name,first_name)) AS len_name
FROM
employees
GROUP BY
LENGTH(CONCAT(last_name,first_name))
# 分析（2）：添加筛选条件 员工个数大于5
SELECT
COUNT(*),
LENGTH(CONCAT(last_name,first_name)) AS len_name
FROM
employees
GROUP BY
LENGTH(CONCAT(last_name,first_name))
HAVING
COUNT(*) > 5;

# 按多个字段分组
# eg:查询每个部门每个工种的员工的平均工资
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
GROUP BY
department_id,job_id;
# 添加排序条件
# eg:查询每个部门每个工种的平均工工资，并且按平均工资的高低显示
SELECT
AVG(salary),
department_id,
job_id
FROM
employees
WHERE
department_id is not null
GROUP BY
department_id,job_id
HAVING
AVG(salary)> 10000
ORDER BY
AVG(salary) DESC;

（6），连接查询 [ 笛卡尔积:是两个或多个表之间的连接操作。 笛卡尔积现象：表1有m行，表2有n行，结果为 m * n 笛卡儿积产生的条件： 1、省略连接条件 2、连接条件无效 3、所有表中的所有行互相连接 注意：为了避免笛卡尔积产生可以在where子句后面加入有效连接条件。

连接查询：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。 连接查询分类： 1、按年代分类： sql92标准（淘汰！！！，了解即可） sql99标准（推荐使用），支持内连接 + 外连接（左外、右外） + 交叉连接 2、按功能分类： （1）内连接 a、等值连接 b、非等值连接 c、自连接 （2）外连接 a、左外连接 b、右外连接 c、全外连接 （3）交叉连接

– 连接查询语法格式总结： – select (7) – 查询列表 – from 表1 别名 [连接类型] (1) – join 表2 别名 (3) – on 连接条件 (2) – [where 筛选条件] (4) – [group by 子句] (5) – [having 筛选条件] (6) – [order by 子句] (8)

– 连接条件分类： 内连接：INNER 外连接： 左外连接：LEFT [OUTER] 右外连接：RIGHT [OUTER] 全外连接：full [OUTER] mysql不支持全外！ 交叉连接：CROSS]

# 需求：查询所有女明星对应的男朋友。
SELECT `name`,boyname from beauty,boys; — 笛卡尔积

SELECT `name`,boyname from beauty,boys
WHERE beauty.boyfriend_id = boys.id;

# 笛卡尔积:是两个或多个表之间的连接操作。
# 笛卡尔积现象：表1有m行，表2有n行，结果为 m * n
# 笛卡儿积产生的条件：
# 1、省略连接条件
# 2、连接条件无效
# 3、所有表中的所有行互相连接
# 注意：为了避免笛卡尔积产生可以在where子句后面加入有效连接条件。

# 连接查询：又称为多表查询，当查询的字段来自多个表时，就会用到连接查询。
# 连接查询分类：
# 1、按年代分类：
# sql92标准（淘汰！！！，了解即可）
# sql99标准（推荐使用），支持内连接 + 外连接（左外、右外） + 交叉连接
# 2、按功能分类：
# （1）内连接
# a、等值连接
# b、非等值连接
# c、自连接
# （2）外连接
# a、左外连接
# b、右外连接
# c、全外连接
# （3）交叉连接

— 连接查询语法格式总结：
— select (7)
— 查询列表
— from 表1 别名 [连接类型] (1)
— join 表2 别名 (3)
— on 连接条件 (2)
— [where 筛选条件](4)
— [group by 子句](5)
— [having 筛选条件](6)
— [order by 子句](8)

— 连接条件分类：
# 内连接：INNER
# 外连接：
# 左外连接：LEFT [OUTER]
# 右外连接：RIGHT [OUTER]
# 全外连接：full [OUTER] mysql不支持全外！
# 交叉连接：CROSS

# 内连接：INNER
/*
# 语法格式：
select 查询列表
from 表1 别名
INNER JOIN 表2 别名
ON 连接条件;

内连接分为：等值、非等值、自连接
特点：
（1）添加排序、分组、筛选
（2）inner关键字可以省略
（3）筛选条件放在where后面，连接条件要放在on后面，提高分离性，便于阅读
*/
# （1）等值连接
# eg:查询员工名、部门名
select
e.first_name,
d.department_name
from employees AS e
INNER JOIN departments AS d
ON e.department_id = d.department_id;

# eg:查询名字中包含a的员工名和工种名
select
first_name,
job_title
from employees e
INNER JOIN jobs j
ON e.job_id = j.job_id
WHERE
e.first_name like '%a%';

# eg:查询部门个数大于3的城市名和部门个数（添加分组 + 筛选）
# 分析（1）：查询每个部门的个数
select
count(*) AS num,
city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY
city

# 分析（2）：根据（1）的结果筛选部门个数大于3
select
count(*) AS num,
city
FROM departments d
INNER JOIN locations l
ON d.location_id = l.location_id
GROUP BY
city
HAVING
num > 3;
# eg:查询哪个部门的员工个数大于3的部门名和员工个数，并按个数降序（添加排序条件）
# 分析（1）查询每个部门的员工个数
SELECT
COUNT(*) as num,
department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY
department_name

# 分析（2）根据（1）的结果筛选员工个数大于3的并排序
SELECT
COUNT( * ) AS num,
department_name
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
GROUP BY
department_name
HAVING
num > 3
ORDER BY
COUNT( * ) DESC;
# （2）非等值连接
# eg:查询员工的工资级别
/*
分析：
查询的表：employees job_grades
查询思路：查询employees表中的salary 字段在 job_grades 的最低工资和最高工资区间内 确定等级
*/
SELECT
salary,
grade_level
from
employees e
INNER JOIN job_grades g
ON e.salary BETWEEN g.lowest_sal AND g.highest_sal;

# eg:查询工资级别的个数大于20的个数，并且按工资级别降序
SELECT
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;
# （3）自连接
# eg:查询员工的名字、上级的名字
SELECT
e.first_name AS 员工,
m.first_name AS 领导
FROM
employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id

# eg:查询名中包含字符a的员工名字、上级名字
SELECT
e.first_name AS 员工,
m.first_name AS 领导
FROM
employees e
INNER JOIN employees m
ON e.manager_id = m.employee_id
WHERE
e.first_name LIKE '%a%';
外连接：
# 左外连接 LEFT [OUTER]
# 右外连接 RIGHT [OUTER]
/*
应用场景：用于查询一个表中有，另一个表中没有的记录。
特点总结：
(1)外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值，如果从表中没有和它匹配的则显示null；
外连接查询结果 = 内连接结果 + 主表中有从表中没有的记录

(2)左外连接，left join左边是主表，右边是从表；右外连接，right join右边是主表，左边是从表

(3)左外和右外交换两个表的顺序，可以实现相同的结果

(4)全外连接 = 内连接结果 + 表1中有但表2中没有的 + 表2中有但表1中没有的

A left join B
A right join B

连接类型 关键字 保留的记录 未匹配时的处理
左外连接LEFT JOIN 或 LEFT OUTER JOIN 左表全部记录 右表字段填充NULL
右外连接RIGHT JOIN 或 RIGHT OUTER JOIN右表全部记录 左表字段填充NULL
全外连接FULL JOIN 或 FULL OUTER JOIN 两表全部记录 缺失方字段填充NULL

对比维度 LEFT JOIN RIGHT JOIN FULL JOIN
保留表 左表全部 右表全部 两表全部
匹配失败填充右表字段为NULL 左表字段为NULL 对应方字段为NULL
是否可互换 可以（调换表顺序）可以（调换表顺序）不可以（需UNION模拟）
MySQL原生支持 ✅ 支持 ✅ 支持 ❌ 不支持

查询 包含A独有包含交集包含B独有结果集范围
LEFT JOIN ✅ ✅ ❌ A全部
RIGHT JOIN ❌ ✅ ✅ B全部
INNER JOIN ❌ ✅ ❌ 交集
LEFT + WHERE B IS NULL ✅ ❌ ❌ A独有（A-B）
FULL + WHERE A IS NULL ❌ ❌ ✅ B独有（B-A）
FULL JOIN✅✅✅并集

*/
# 引入：查询男朋友不在男神表中的女神名
# 查询没有男朋友的女神

# 左外连接：

SELECT
*
FROM
beauty b
LEFT JOIN boys bo
ON b.boyfriend_id = bo.id
WHERE
bo.id is NULL;

# 右外连接：
SELECT
*
FROM
boys bo
RIGHT JOIN beauty b
ON bo.id = b.boyfriend_id
WHERE
bo.id is NOT NULL;
# eg：查询哪个部门没有员工。
# 左外连接 ：部门表 主表 员工表 从表

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

#右外连接
SELECT
d.*,
e.employee_id
FROM
employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE
e.employee_id IS NULL;

# 全外连接（mysql不支持全外连接）
— SELECT
— *
— FROM
— beauty b
— FULL JOIN boys bo
— ON b.boyfriend_id = bo.id;

# 交叉连接（笛卡尔积）
SELECT
*
FROM
beauty b
CROSS JOIN boys bo ;

(7)，子查询 [子查询：出现在其他语句中的select语句，称之为子查询（内查询） 分类： 按子查询出现的位置可分： （1）select后面：仅支持标量子查询。 （2）where或者having后面：支持标量子查询（单行）、列子查询（多行）、行子查询 （3）exists后面（相关子查询）：支持表子查询 （4）from后面跟子查询 按结果集的行数可分： （1）标量子查询（结果只有一行一列） （2）列子查询（结果只有一列多行） （3）行子查询（结果有一行多列） （4）表子查询（结果多行多列）]

/*
子查询：出现在其他语句中的select语句，称之为子查询（内查询）
分类：
按子查询出现的位置可分：
（1）select后面：仅支持标量子查询。
（2）where或者having后面：支持标量子查询（单行）、列子查询（多行）、行子查询
（3）exists后面（相关子查询）：支持表子查询
（4）from后面跟子查询
按结果集的行数可分：
（1）标量子查询（结果只有一行一列）
（2）列子查询（结果只有一列多行）
（3）行子查询（结果有一行多列）
（4）表子查询（结果多行多列）
*/
— 1、where或者having后面跟子查询
/*
特点：
1、子查询放在小括号内；
2、子查询一般放在条件的右侧；
3、标量子查询，一般搭配着单行操作符使用： > < >= <= !=/<> <=>
4、列子查询，一般搭配着多行操作符使用： in any/some all
5、子查询的执行由于主查询执行，主查询的条件用到了子查询的结果（虚表）
*/
–（1）标量子查询
# eg:谁的工资比Lex的高。
select *
from employees
where salary > (select salary from employees where first_name = 'Lex')
# eg :返回job_id与141号员工相同，salary比143号员工多的员工名、job_id和工资
select first_name , job_id , salary
from employees
where
job_id = (select job_id from employees where job_id = 141 )
salary > (select salary from employees where employee_id = 143 );
# eg:返回公司工资最少的员工的 last_name、job_id、salary
select last_name,job_id,salary
from employees
where salary = (select a from employees where min(salary as a) );
# eg:查询最低工资大于50号部门最低工资的部门id和其最低工资
# （1）查询50号部门最低工资
SELECT
min(salary)
FROM
employees
WHERE
department_id = 50

# (2)查询每个部门的最低工资
SELECT
min(salary),
department_id
FROM
employees
GROUP BY
department_id

# （3）在（2） 的基础上，满足 min(salary) > (1)的结果
SELECT
min( salary ),
department_id
FROM
employees
GROUP BY
department_id
HAVING
min( salary ) > ( SELECT min( salary ) FROM employees WHERE department_id = 50 );
# 注意：非法使用标量子查询
# min( salary ) > 确定的值
# min( salary ) > 很多值 错误，非法使用！！！

–（2）列子查询（多行）
/*
in / not in:等于列表中的任意一个
any / some ：和子查询返回的某一个值进行比较
all：和子查询返回的所有值比较
*/
# eg:返回 location_id 是 1400 或者 1700 的部门中的所有员工名
# （1）查询 location_id是 1400 或者 1700的部门编号
SELECT DISTINCT
department_id
FROM
departments
WHERE
location_id in (1400,1700)

# (2) 查询员工名，要求部门号是：（1）结果中的某一个
SELECT
first_name
FROM
employees
WHERE
department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
# eg: 返回其他工种中比job_id为：IT_PROG 工种任一工资低的员工的工号、名、job_id以及 salary
# （1） 查询 job_id 为 IT_PROG 工资的任一工资
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'

# (2) 查询员工的工号、名、job_id以及salary，要求 salary < (1) 任意一个

SELECT
employee_id,
first_name,
job_id,
salary
FROM
employees
WHERE
salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' );

— 小于最大的

SELECT
employee_id,
first_name,
job_id,
salary
FROM
employees
WHERE
salary < ( SELECT max(salary) FROM employees WHERE job_id = 'IT_PROG' );
# eg:返回其他工种中比 job_id 为：IT_PROG工种所有工资低的员工的工号、名、job_id以及salary

# eg:返回其他工种中比 job_id 为：IT_PROG工种所有工资低的员工的工号、名、job_id以及salary

# （1）查询job_id 为：IT_PROG 的任一工资
SELECT DISTINCT
salary
FROM
employees
WHERE
job_id = 'IT_PROG'

# (2) 查询员工的工号、名、job_id以及salary，要求 salary < (1) 的所有

SELECT
employee_id,
first_name,
job_id,
salary
FROM
employees
WHERE
salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' );
— 小于最小的

SELECT
employee_id,
first_name,
job_id,
salary
FROM
employees
WHERE
salary < ( SELECT min(salary) FROM employees WHERE job_id = 'IT_PROG' );
–(3)行子查询
# eg:查询员工编号最小并且工资最高的员工信息。
SELECT
*
FROM
employees
WHERE
employee_id = ( SELECT MIN( employee_id ) FROM employees )
AND salary = ( SELECT MAX( salary ) FROM employees );
— 2、select 后面跟子查询
/*
仅支持标量子查询
*/
# eg:查询每个部门的员工个数
SELECT
d.*,
(select count(*) from employees e where e.department_id = d.department_id) 个数
FROM departments d
# eg:查询员工工号等于102的部门名
SELECT
(
select
department_name
FROM
departments d
INNER JOIN employees e
ON d.department_id = e.department_id
WHERE
e.employee_id = 102
) 部门名;
— 3、from后面跟子查询
/*
将子查询的结果充当一张表，要求必须取别名
*/
# eg:查询每个部门的平均工资等级
# （1） 查询每个部门的平均工资
SELECT
ROUND(avg(salary)),
department_id
FROM
employees
GROUP BY
department_id

# (2) 连接（1）的结果和 job_grades 表，筛选条件平均工资 between lowest_sal and highest_sal
SELECT
*
FROM
( SELECT ROUND( avg( salary ) ) ag, department_id FROM employees GROUP BY department_id ) ag_dep
INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
— 4、exists后面
/*
语法：
exists （完整sql语句）
结果：1（真） 或者 0 （假）

*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE salary = 80000);–返回0
# eg:查询有员工的部门名
SELECT
department_name
FROM
departments d
WHERE
EXISTS (select * from employees e WHERE d.department_id = e.department_id);—返回所有employees 中存在的department_id

SELECT
department_name
FROM
departments d
WHERE
d.department_id IN (SELECT department_id from employees)

（8）分页查询 [– 总结分页查询

select 查询列表 (7) from 表名 (1) [ join type join 表2 (2) on 连接条件 (3) where 筛选条件 (4) group by 分组字段 (5) having 分组后筛选 (6) order by 排序字段 (8) ] limit [offset] size; (9)

offset:要显示条目数的起始索引（默认起始索引从0开始） size：要显示的条目个数

注意： （1）limit语句放在查询语句的最后 （2）公式： 要显示的页数（page），每页的条目数（size） limit （page – 1） * size,size page 1 0 2 25 3 50 ]

/*
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法格式：
— 总结分页查询
select 查询列表 (7)
from 表名 (1)
[
join type join 表2 (2)
on 连接条件 (3)
where 筛选条件 (4)
group by 分组字段 (5)
having 分组后筛选 (6)
order by 排序字段 (8)
]
limit [offset] size; (9)
offset:要显示条目数的起始索引（默认起始索引从0开始）
size：要显示的条目个数

注意：
（1）limit语句放在查询语句的最后
（2）公式：
要显示的页数（page），每页的条目数（size）
limit （page – 1） * size,size
page
1 0
2 25
3 50
*/
# eg:查询前五条员工信息
SELECT
*
FROM
employees
LIMIT 5;

SELECT
*
FROM
employees
LIMIT 2, 5;
# eg:查询员工表第11条数据到第25条数据
SELECT
*
FROM
employees

LIMIT 10,15;
# eg:有奖金率的员工信息，并且工资较高的前10名
SELECT
*
FROM
employees
WHERE
commission_pct is not null
ORDER BY
salary DESC
limit 10;

(9)union联合查询 [语法格式：

查询语句1 union 查询语句2;

应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时 特点： 1、要求多条查询语句的查询列数是一致的。 2、要求多条查询语句的查询的每一列类型和顺序最好一致。 3、union关键字默认去重，如果使用union all 可以包含重复项的。]

–将多条查询语句的结果合并成一个结果
/*
语法格式：

查询语句1
union
查询语句2;

应用场景：要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列数是一致的。
2、要求多条查询语句的查询的每一列类型和顺序最好一致。
3、union关键字默认去重，如果使用union all 可以包含重复项的。
*/
# eg:查询部门编号大于90或邮箱包含e的员工信息
SELECT * FROM employees WHERE department_id > 90 OR email LIKE '%e%';

select * from employees where department_id > 90
union
select * from employees where email LIKE '%e%';

5，DML操作语言 1、插入：insert 2、修改：update 3、删除：delete

（1）插入方式一：
/*
insert into 表名 (列名,……..) values (值1,……);
insert into 表名 values (值1,……);

（2） 插入方式二：
insert into 表名 set 列名 = 值,列名 = 值,…..

插入方式对比：
（1）方式一支持插入多行，方式二不支持
（2）方式一支持子查询，方式二不支持

— 总结：
（1）修改单表记录
update 表名 （1）
set 列 = 新值,列 = 新值,….（3）
where 筛选条件（2）

（2）修改多表记录(补充)
update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 列 = 新值,列 = 新值,….
where 筛选条件

(1)单表删除
delete from 表名 where 筛选条件;

（2）多表删除（补充）
delete from 表1的别名,表2的别名
from 表1 别名
inner | left | right join 表2 别名 on 连接条件
where 筛选条件

truncate 截断表
语法格式：
TRUNCATE [TABLE] tbl_name

delete VS truncate
（1）delete可以加where条件，truncate不可以加where条件
（2）truncate删除，效率更高
（3）假如要删除的表中有自增长列，如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据自增长列的值从1开始。
（4）truncate删除没有返回值，delete删除有返回值
（5）truncate删除不能回滚（事务），delete删除可以回滚。
*/
# ###############################插入#######################################
— 方式一插入：

# eg:插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty (id,`name`,sex,borndate,phone,photo,boyfriend_id)–错误
VALUES (1,'迪丽热巴','女','1992-06-03','17000000000',NULL,1);
# eg:不可以为null的列必须插入值，可以为null的如何插入值？ –错误
INSERT INTO beauty (id,`name`,sex,borndate,phone,photo,boyfriend_id)
VALUES (2,'迪丽热巴',NULL,NULL,NULL,NULL,1);

INSERT INTO beauty (id,`name`,sex,boyfriend_id) –可以
VALUES (3,'赵丽颖','女',2);
# eg:列的顺序是否可以调换？ 可以
INSERT INTO beauty (`name`,sex,id,boyfriend_id)
VALUES ('赵丽颖2','女',4,2);
# eg:列数和值的个数是否必须一致？ 必须一致
— INSERT INTO beauty (id,`name`,sex,boyfriend_id)
— VALUES (5,'赵丽颖','女',NULL,2);
# eg:可以省略列名，默认是所有列，而且列的顺序和表中的列的顺序一致
INSERT INTO beauty VALUES(6,'杨幂','女','1986-08-12','18000000000',NULL,3);
# eg:可以省略列名，默认是所有列，而且列的顺序和表中的列的顺序一致
INSERT INTO beauty VALUES(6,'杨幂','女','1986-08-12','18000000000',NULL,3);
INSERT INTO beauty VALUES(7,'杨幂1','女',NULL,3);
— 方式二插入：

insert into beauty
SET id=7,name='刘亦菲',sex='女';

insert into beauty
SET name='刘亦菲',sex='女'; — 报错

— 方式一和方式二对比

INSERT INTO beauty VALUES
(8,'杨幂2','女','1986-08-12','15000000000',NULL,3),
(9,'杨幂3','女','1986-08-12','16000000000',NULL,3),
(10,'杨幂4','女','1986-08-12','17000000000',NULL,3),
(11,'杨幂5','女','1986-08-12','18000000000',NULL,3),
(12,'杨幂6','女','1986-08-12','19000000000',NULL,3),
(13,'杨幂7','女','1986-08-12','11000000000',NULL,3),
(14,'杨幂78','女','1986-08-12','12000000000',NULL,3);

— insert into beauty
— SET id=15,name='刘亦菲2',sex='女',
— SET id=16,name='刘亦菲3',sex='女',
— SET id=17,name='刘亦菲4',sex='女';
# ############################### 修改 #######################################
# 修改单 '1919999999'
UPDATE beauty SET phone = '1919999999'
WHERE name LIKE '杨%';

# eg:修改boys表中id为 1 的名称为：汪峰2，魅力值为：10000
UPDATE boys set boyName = '汪峰2',userCP=10000
WHERE id = 1;

# 修改多表记录
# eg:修改汪峰2对应的女明星的手机号为911
UPDATE boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET b.phone = '911'
WHERE bo.boyName = '汪峰2'

# eg:修改没有男朋友的女明星的男朋友的编号为 1
update boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
set b.boyfriend_id = 1
WHERE bo.id is null;
# ###############################删除#######################################
# 单表删除
# eg:删除手机号以91开头的女神信息
DELETE from beauty WHERE phone LIKE '91%';

# 多表删除
# 删除刘恺威的信息以及他女朋友的信息
DELETE b,bo
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '刘恺威';
# ############################### truncate 删除#######################################
SELECT * from beauty;
DELETE FROM beauty;
truncate table beauty;

6，DDL语言 1、库的管理 创建、修改、删除 2、表的管理 创建、修改、删除 3、常见的数据类型 4、常见的约束条件 （1），表级约束 （2），列级约束 5、自增长列

/*
库的管理
（1）库的创建语法格式：
create database [if not exists] 库名;
（2）库的修改语法格式：
alter_option: {
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
| READ ONLY [=] {DEFAULT | 0 | 1}
}
（3）库的删除语法格式：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
表的管理
(1)创建表语法格式：
create table 表名(
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束]
)
(2)修改表语法格式：
alter table 表名 add | drop | modify | change column 列名 [列的类型 约束];
(3)删除表语法格式：
drop table [if exists] 表名;
*/
# ##########################库的管理_创建###############################
# eg:创建bigdata数据库
CREATE DATABASE bigdata;

CREATE DATABASE if not exists bigdata;
# ##########################库的管理_修改###############################
#注意：不建议修改

alter database bigdata character set 'utf8';

ALTER DATABASE bigdata READ ONLY = 0; — 1只读模式
—
— SHOW CREATE DATABASE bigdata\\G; # \\G 标准化输出
# ##########################库的管理_删除###############################
drop DATABASE if exists bigdata;

# ##########################表的管理_创建###############################
# 创建数据库
create DATABASE if not exists book;
# 进入数据库
use book;
# 创建表
drop table if exists book;
create TABLE IF NOT EXISTS book(
id int, — 编号
bName varchar(255), — 图书名
price double, — 价格
authorId int, — 作者编号
publishDate DATETIME — 出版日期
);
# 查看表结构
desc book;
# 创建作者表
create TABLE IF NOT EXISTS author(
id int,
au_name VARCHAR(20),
nation VARCHAR(10)
);

# 查看表结构
desc author;
# ##########################表的管理_修改###############################
# 修改book表的列名
alter table book CHANGE COLUMN publishDate pubDate DATETIME;
# 修改列的类型或约束
alter table book MODIFY COLUMN pubDate TIMESTAMP;
# 添加新列
alter table author add COLUMN age int;
# 删除列
ALTER TABLE author DROP COLUMN age;
# 修改表名
ALTER TABLE author RENAME TO book_author;
#查看表信息
desc book;
# ##########################表的管理_删除###############################
DROP TABLE if EXISTS book_author;
# 创建作者表 author
# 插入数据到作者表 author
INSERT INTO author VALUES
(1,'鲁迅','中国'),
(2,'莫言','中国'),
(3,'余华','中国'),
(4,'村上春树','日本');
# 表的复制(赋值表的结构 + 数据) 创建表的一种方式
CREATE TABLE copy1 select * FROM author;
# 表的复制(仅赋值表的结构) 创建表的一种方式
CREATE TABLE copy2 like author;
# 复制表的部分数据
CREATE TABLE copy3 SELECT
id,
au_name
FROM
author
WHERE
nation = '中国';

# 复制表的某些字段
CREATE TABLE copy4
SELECT id,au_name from author where 0;#–不复制数据

CREATE TABLE copy5
SELECT id,au_name from author where 1;#包含数据

# ##########################常见的数据类型###############################
/*
数值数据类型：
整数类型：int、bigint
定点数据类型：decimal
浮点类型：float、double

日期和时间数据类型：date、time、datetime、timestamp、year

字符串数据类型：char(0~255)、varchar(0~65535)、text、blob(较长的二进制数据)

经验：字符类型：varchar(255)
*/
# ##########################常见的约束条件###############################
/*
常见的约束条件：
一种限制，用于限制表中的数据，为了保证表中的数据的准确性和可靠性。
常见约束条件分类：
1、not null ：非空，用于保证该字段的值不能为空；比如：姓名、学号等等
2、default：默认，用于保证该字段有默认值；比如：性别等等
3、primary key：主键，用于保证该字段的值具有唯一性，并且非空；比如：学号、员工编号等等
4、unique:唯一，用于保证该字段的值具有唯一性，可以为空；比如：座位号
5、check：检查约束；比如：年龄、性别
6、foreign key:外键，用于限制两个表的关系用于保证该字段的值必须来自于主表的关联列的值在从表
添加外键约束，用于引用主表中某列的值。比如：学生表的专业编号、员工表的部门编号、员工表工种编号。

添加约束条件的时机：
1、创建表时添加约束
2、修改表时添加约束

约束的添加分类：
列级约束：六大约束语法上都支持，但外键约束没有效果。
表级约束：除了非空、默认约束，其他都支持。

添加约束语法格式列级约束：

create table 表名(
列名 列的类型 约束条件,
列名 列的类型 约束条件,
列名 列的类型 约束条件,
列名 列的类型 约束条件
….
)

添加约束语法格式表级约束：
create table 表名(
列名 列的类型 ,
列名 列的类型 ,
列名 列的类型 ,
列名 列的类型
表级约束
….
)

################################主键 VS 唯一 ################################
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 可以 不能 至多有一个 允许，但不推荐
唯一 可以 可以 可以有多个 允许，但不推荐

外键设置注意：
1、要求在从表中设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或者兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或者唯一）
4、插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。
*/
# ################################添加列级约束################################
/*
语法格式：直接在字段名和类型后面追加 约束类型 即可。
create table 表名(
列名 列的类型 约束条件,
列名 列的类型 约束条件,
列名 列的类型 约束条件,
列名 列的类型 约束条件
….
)
只支持：默认、非空、主键、唯一、检查
*/
# 创建数据库
CREATE DATABASE if not EXISTS students;

# 进入数据库
use students;

# 创建学生表
— drop table [if exists] 表名;
drop table if exists stuinfo;
create table if not exists stuinfo(
id int primary key, — 主键约束
stuName varchar(20) not null , — 非空约束
gender char(1) check(gender='男'OR gender='女'), — 检查约束，mysql5.x不支持
seat int unique, — 唯一约束
age int default 18, — 默认约束
— 外键约束,列级约束中不生效的
majorId int references major(id)
);
# 创建专业表
drop table if exists major;
create table if not exists major(
id int primary key,
majorName varchar(20)
);
# 查看索引,包括：主键、外键、唯一
show index from stuinfo;
# ################################添加表级约束################################
drop table if exists stuinfo; — 从表
create table if not exists stuinfo(
id int,
stuName varchar(20),
gender char(1),
seat int,
age int,
majorid int,
— 主键约束 pk约束名称
CONSTRAINT pk primary key(id), — 主键
CONSTRAINT uq unique(seat), — 唯一
CONSTRAINT ck check(gender='男'OR gender='女'), — 检查
CONSTRAINT fk_stuinfo_major foreign key(majorid) REFERENCES major(id) — 外键
);
# 4、插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。
desc stuinfo;

# 查看索引包含：主键、唯一、外键
show index from stuinfo;
# ########################修改表时添加约束条件#########################
# 删除非空约束
alter table stuinfo modify column stuName varchar(20) null;

# 删除默认约束
alter table stuinfo modify column age int;

# 删除主键
alter table stuinfo drop primary key;

# 删除唯一 删除的是索引 index
alter table stuinfo drop index seat;

# 删除外键
# 注意：在设置外键约束的时候，建议给别名！！！
alter table stuinfo drop foreign key stuinfo_ibfk_1;

desc stuinfo;
show index from stuinfo;

SELECT
CONSTRAINT_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'students'
AND TABLE_NAME = 'stuinfo'
AND REFERENCED_TABLE_NAME IS NOT NULL;
# ########################自增长列###########################################
# （标识列）自增长列
/*
自增长列：可以不用手动的插入值，系统提供默认的序列值
特点：
1、标识列必须和主键搭配？不一定，但要求是一个key
2、一个表中有几个标识列？至多一个
3、标识列的类型只能是数值类型
4、标识列可以通过 set auto_inrement_increment = xxx;设置
*/
# AUTO_INCREMENT
# 查看默认步长
SHOW VARIABLES LIKE '%auto_increment%';
# 创建表时，设置自增长列
create table tab_stu(
id int primary key auto_increment,
name varchar(20)
);

insert into tab_stu values(null,'张三');
select * from tab_stu;
set auto_increment_increment = 3;
# 注意：只允许设置步长，不允许设置偏移量
insert into tab_stu values
(null,'张三1'),
(null,'张三2'),
(null,'张三3'),
(null,'张三4'),
(null,'张三5');

6，DCL（事务控制语音） 事务：一个或者一组sql语句组成的一个执行单元，这个执行单元要么全部执行，要么全部不执行。 经典案例：银行转装 兰智 1000 数加 1000 update 表 set 兰智余额 = 500 where name = ‘兰智’ 意外 update 表 set 数加余额 = 500 where name = ‘数加’

事务特性：ACID 原子性（A）：一个事务不可以再分割，要么都执行要么都不执行。 一致性（C）:一个事务执行会使数据从一个一致状态切换到另一个一致状态。 隔离性（I）:一个事务的执行不受其他事务的干扰 持久性（D）:一个事务一旦提交，则会永久的改变数据库的数据

事物隔离级别 （读未提交）READ UNCOMMITTED （读提交）READ COMMITTED （可重复读）REPEATABLE READ – MySQL默认 （串行化）SERIALIZABLE

查看mysql隔离级别 select @@transaction_isolation;

设置隔离级别： set session | global transaction isolation level 隔离级别;

set session transaction isolation level READ UNCOMMITTED; set session transaction isolation level READ COMMITTED; set session transaction isolation level REPEATABLE READ; set session transaction isolation level SERIALIZABLE;

视图管理 创建、修改、删除

/*
事务的创建：
（1）隐式事务：事务没有明显的开始和结束标记
比如：insert、update、delete
（2）显示事务：事务具有明显的开启和结束的标记
前提：必须先设置自动提交功能为禁用
set autocommit = 0;
# 步骤一：开启事务
set autocommit = 0;
start transaction; — 可选的

# 步骤二：编写事务中的SQL语句
（select、insert、update、delete）
语句1；
语句2；
…..

# 步骤三：结束事务
commit;提交事务
rollback;回滚事务
savepoint 节点名; 设置保存点
*/

# 查看mysql引擎 # mysql默认引擎 InnoDB
show engines;

# 查看mysql事务是否开启
show variables like 'autocommit';

— 创建表
drop table if exists account;
CREATE TABLE account(
id int primary key auto_increment,
username varchar(20),
balance DOUBLE
);

— 插入数据
INSERT INTO account ( username, balance ) VALUES
( '兰智', 1000 ),
( '数加', 1000 );

set auto_increment_increment = 1;

# 演示事务

set autocommit=0;
start transaction;

update account set balance = 500 where username = '兰智';
update account set balance = 1500 where username='数加';

commit;

select * from account;
/*
事务隔离级别：
（读未提交）READ UNCOMMITTED
（读提交）READ COMMITTED
（可重复读）REPEATABLE READ — MySQL默认
（串行化）SERIALIZABLE

隔离级别 脏读 不可重复读 幻读 实现方式
READ UNCOMMITTED ✅ ✅ ✅ 读未提交数据
READ COMMITTED ❌ ✅ ✅ 每次读生成新快照
REPEATABLE READ（默认） ❌ ❌ ✅ 事务内使用同一快照
SERIALIZABLE ❌ ❌ ❌ 强制加锁串行执行

查看mysql隔离级别
select @@transaction_isolation;

设置隔离级别：
set session | global transaction isolation level 隔离级别;

set session transaction isolation level READ UNCOMMITTED;
set session transaction isolation level READ COMMITTED;
set session transaction isolation level REPEATABLE READ;
set session transaction isolation level SERIALIZABLE;
*/
# 查看mysql隔离级别 # REPEATABLE-READ
select @@transaction_isolation;

# 第一种：
set session transaction isolation level READ COMMITTED;
set autocommit=0;
start transaction;

update account set balance = 1000 where username = '兰智';
update account set balance = 1000 where username='数加';

commit;

select * from account;

# 第二种：
set session transaction isolation level READ UNCOMMITTED;
select @@transaction_isolation;
set autocommit=0;
start transaction;

update account set balance = 1000 where username = '兰智';
update account set balance = 1000 where username='数加';

commit;

select * from account;

# 第四种：
set session transaction isolation level SERIALIZABLE;

select @@transaction_isolation;
set autocommit=0;
start transaction;

update account set balance = 1000 where username = '兰智';
update account set balance = 1000 where username='数加';

commit;

select * from account;

/*
设置保存点savepoint
set autocommit=0;
start transaction;
delete from account where id = 1;
— 设置保存点
savepoint a;
delete from account where id = 2;
rollback to a;
*/
/*
视图:虚拟表，和普通表一样使用，通过表动态生成数据。
视图语法格式：
CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

*/
select * from myemployees.employees;

select
stuname,
gender,
seat,
age,
majorid
FROM
stuinfo
WHERE
stuName like '张%';

— 创建视图
create view myv1
AS
SELECT
stuname,
majorname
FROM
stuinfo s
INNER JOIN major m ON s.majorid = m.id
where
s.stuName like '张%';

select * from myv1;

/*
修改视图：
语法格式：
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = user]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
*/

alter view myv1
AS
select * from stuinfo;

/*
删除视图
语法格式：
DROP VIEW [IF EXISTS]
view_name [, view_name] …
[RESTRICT | CASCADE]
*/

drop view if exists myv1;