mysql查询某个字段like mysql查询某个字段长度大于10

MySql的基本Sql学习记录

select用法

基本语法

SELECT Column,Column... FROM tableName

字段名尽量不要写 * ，指定查询的字段名提高查询效率

WHERE 使用条件查询

SELECT id,name,tel FROM tableName
WHERE id = 1

字段名尽量不要写 * ，指定查询的字段名提高查询效率

WHERE 使用条件查询

SELECT id,name,tel FROM tableName
WHERE id = 1

WHERE 大于大于等于 小于小于等于 不等于 查询

-- 查询id大于1的记录
SELECT id,name,tel FROM tableName
WHERE id > 1
-- 查询id大于等于1的记录
SELECT id,name,tel FROM tableName
WHERE id >= 1
-- 查询id小于1的记录
SELECT id,name,tel FROM tableName
WHERE id < 1
-- 查询id小于等于1的记录
SELECT id,name,tel FROM tableName
WHERE id <= 1
-- 查询id不等于1的记录
SELECT id,name,tel FROM tableName
WHERE id != 1     
-- 查询id不等于1的记录
SELECT id,name,tel FROM tableName
WHERE id <> 1

ORDER BY 根据字段对结果进行排序

--查询id 大于10的记录 并将结果降序返回
select user_id,user_name,address,user_tel 
from sys_user 
where user_id >10
ORDER BY user_id DESC

-- 查询地址为空的记录，返回结果根据id进行升序和根据父级id降序
SELECT user_id,user_name FROM aoa_user
WHERE address is NULL 
ORDER BY user_id ASC ,father_id DESC 

--查询地址为空的记录，并根据father_id进行降序排序
SELECT user_id,father_id FROM aoa_user
WHERE address is NULL 
ORDER BY 2 desc 

--查询地址为空的记录，并根据user_id和father_id进行排序
SELECT user_id,father_id FROM aoa_user
WHERE address is NULL 
ORDER BY 1 , 2  

--ORDER BY 子句的表达式不一定为列名称 也可以是别名或者是算数表达式
SELECT 
order_id,
product_id,
quantity,
unit_price, 
(quantity * unit_price) as total_price 
FROM order_items WHERE order_id = 2
ORDER BY total_price DESC

ASC 升序
DESC 降序

AS 为字段/表名 赋予别名

--返回结果为 user_id,user_name,address,tel
select user_id,user_name,address,user_tel as tel
from sys_user 
where user_id 
ORDER BY user_id DESC

对字段结果去重 DISTINCT

--返回address 重复不反悔
select DISTINCT address
from sys_user 
GROUP BY user_id DESC

多个搜索条件 使用 AND，OR ，NOT

-- and 返回dept为1并且地址为中国广东的记录
select real_name,salary,user_tel
from sys_user 
where dept_id =1 and address = '中国广东'

-- or 返回dept为1的记录或者返回地址为中国广东的记录
select real_name,salary,user_tel
from sys_user 
where dept_id =1 or address = '中国广东'

-- or 返回dept为1的记录或者返回地址为中国广东和年龄等于18的记录
select real_name,salary,user_tel
from sys_user 
where dept_id =1 or (address = '中国广东' and age =18)

-- NOT 返回部门id不为1 或者 地址不为中国广东 的记录
select real_name,salary,user_tel
from sys_user 
where NOT (dept_id =1 or address = '中国广东')

IN 运算符

-- IN 返回address等于清华大学和泰山西路的记录
select user_id,real_name,salary,user_tel
from sys_user 
where address IN ('清华大学','泰山西路') 

-- NOT IN 返回address不等于清华大学和泰山西路的记录
select user_id,real_name,salary,user_tel
from sys_user 
where address  NOT IN ('清华大学','泰山西路')

BETWEEN AND 区间运算

--返回id在10到15区间的记录 包括10和15
select user_id,real_name,salary,user_tel
from sys_user
where user_id BETWEEN 10 AND 15

LIKE 模糊查询

-- LIKE '%' 查询 返回地址前缀为清华的记录
select user_id,real_name,salary,user_tel
from aoa_user
where address LIKE '清华%'

-- LIKE '%' 查询 返回地址后缀为广东的记录
select user_id,real_name,salary,user_tel
from aoa_user
where address LIKE '%广东'

-- LIKE '%%' 查询 返回地址中含有广东的记录
select user_id,real_name,salary,user_tel
from aoa_user
where address LIKE '%广东%'

-- LIKE '_' 占位，返回地址只有俩个字符，第一个字符不关注，只关注第二个字符为华的记录
select user_id,real_name,salary,user_tel
from aoa_user
where address LIKE '_华'

-- LIKE '_' 占位，返回地址只有4个字符，第一个字符和第三，四个字符不关注，只关注第二个字符为华的记录
select user_id,real_name,salary,user_tel
from aoa_user
where address LIKE '_华__'

正则查询 对于查询字符串功能强大

--REGEXP  返回地址中包含大学的所有记录
select user_id,real_name,salary,user_tel
from aoa_user
where address REGEXP '大学'

--REGEXP  返回地址中前缀为大学的所有记录
select user_id,real_name,salary,user_tel
from aoa_user
where address REGEXP '^大学'

--REGEXP  返回地址中后缀为大学的所有记录
select user_id,real_name,salary,user_tel
from aoa_user
where address REGEXP '大学$'

--REGEXP  返回地址中包含清华或中南或者泰山的所有记录
select user_id,address,real_name,salary,user_tel
from aoa_user
where address REGEXP '清华|中南|泰山'

--REGEXP  返回地址中包含 以清华开头 或 中南 或者 以泰山结尾 的所有记录
select user_id,address,real_name,salary,user_tel
from aoa_user
where address REGEXP '^清华|中南|泰山$'

--REGEXP  返回地址中包含中华或者清华的所有记录
select user_id,address,real_name,salary,user_tel
from aoa_user
where address REGEXP '[中清]华'

--REGEXP  返回地址中包含华中或者华清的所有记录
select user_id,address,real_name,salary,user_tel
from aoa_user
where address REGEXP '华[中清]'

--REGEXP  返回昵称中包含 as,bs,cs,ds,es,fs
select user_id,address,real_name,salary,user_tel
from aoa_user
where nikename REGEXP '[a-f]s'

为空判断 IS NOLL

--返回地址为空的数据
SELECT user_id,user_name FROM aoa_user
WHERE address IS NULL

不为空判断 IS NOT NOLL

--返回地址不为空的数据
SELECT user_id,user_name FROM aoa_user
WHERE address IS NOT NULL

LIMIT 控制返回记录条数

--返回查询结果的前5条记录
SELECT user_id,user_name FROM aoa_user
LIMIT 5

--返回查询结果，从结果集的第5条开始，返回5条数据
SELECT user_id,user_name FROM aoa_user
LIMIT 5,5

LIMIT最好放在sql语句的最后

多表查询

JOIN ON 联接 语句

-- 查询商品和类型表，返回匹配的结果 and 连接多个条件
SELECT g.goods_id,g.goods_name,mt.management_name 
FROM goods g
 JOIN management_type mt
 ON g.goods_category = mt.management_name 
 	AND g.goods_id = mt.goods_id
 	AND  management_name='饮品'

--多表联查 返回匹配的记录
SELECT b.buylist_id ,b.buylist_number,d.depot_name,
		g.goods_name,s.supplier_name 
FROM buylistdetails b
JOIN depot d ON b.depot_id=d.depot_id
JOIN goods g ON b.goods_id=g.goods_id
JOIN supplier s ON b.supplier_id =s.supplier_id

同表 JOIN 联接查询

--查询user表中所有记录以及记录对应的父级名称
SELECT s.user_id,s.user_name,su.user_name as father_name 
FROM sys_user s
JOIN sys_user su
 ON s.father_id= su.user_id

LEFT JOIN 左联接 与 RIGHT JOIN 右联接

返回左表所有记录，返回加入表符合条件的记录，不匹配则返回null
返回右表所有记录，返回主表符合条件的记录
常用开发中，尽量使用左连接 ， 避免使用右连接

--查询详情表中所有记录并返回符合结果的商品记录，不符合记录的返回null 
SELECT b.buylist_id ,b.buylist_number,
		g.goods_name
FROM buylistdetails b
LEFT JOIN goods g ON b.goods_id=g.goods_id
ORDER BY b.buylistdetails_id

联接查询 USING

俩个表的连接中，如果列名完全相同，USING 则可以替换on子句
()中 可以添加多个字段，以逗号分隔

-- USING (goods_id) 相当于 on b.goods_id = g.goods_id
SELECT b.buylist_id ,b.buylist_number,
		g.goods_name
FROM buylistdetails b
LEFT JOIN goods g 
USING (goods_id)
ORDER BY b.buylistdetails_id

自然联接 NATURAL JOIN

无需指定列名，数据库引擎将查看这俩张表，将其公共列联接
但控制性差，不建议使用

SELECT b.buylist_id ,b.buylist_number,
		g.goods_name
FROM buylistdetails b
NATURAL JOIN goods g 
ORDER BY b.buylistdetails_id

交叉联接

左表的所有记录与右表的所有记录进行组合返回

SELECT b.buylist_id ,b.buylist_number,
		g.goods_name
FROM buylistdetails b
CROSS JOIN goods g

联合运算符 UNION

联接查询 合并返回结果
注：俩个sql中查询的字段的数量要保持一致，否则报错

--模拟查询
SELECT goods_id,goods_name,goods_buyreprice,'热度数据' AS 'status'
		FROM goods
		WHERE goods_id >=30
UNION
SELECT goods_id,goods_name,goods_buyreprice,'过期数据' as 'status'
		FROM goods
		WHERE goods_id <30

结果展示：

插入记录

INSERT INTO

基本语法

INSERT INTO _TABLE (ColumnNames… ) VALUES ( values… )

--基本添加
INSERT INTO settlementaccount 
(settlementaccount_name, settlementaccount_number, settlementaccount_type) 
VALUES ('晋商银行', '1585663211115411', '对公账户')

--添加多条
INSERT INTO settlementaccount 
(settlementaccount_name, settlementaccount_number, settlementaccount_type) 
VALUES ('晋商银行', '1585663211115411', '对公账户'),
	   ('浦发银行', '158123231115411', '对公账户'),
	   ('中国银行', '158566422311115411', '对公账户')
--查看最后添加的id
SELECT  LAST_INSERT_ID()

备份表（创建表的副本）

将settlementaccount 表中的所有数据拷贝到新表-settlementaccount_archived中

CREATE TABLE settlementaccount_archived as 
SELECT * FROM settlementaccount

--根据条件备份表记录
CREATE TABLE settlementaccount_archived2 as 
SELECT * FROM settlementaccount 
where settlementaccount_id >9

--将新记录插入到备份表 将id小于9的记录添加到备份表中
-- 必须要存在该表 并且要与返回结果的字段一致
INSERT INTO settlementaccount_archived2  
SELECT * FROM settlementaccount 
where settlementaccount_id < 9

--将联接查询的记录备份到新表
--新表的字段：depot_id,depot_name,depot_address,shops_name 
CREATE TABLE depotWithShops AS
SELECT d.depot_id,d.depot_name,d.depot_address,s.shops_name 
FROM depot d
JOIN shops s
	ON d.shops_id = s.shops_id

修改记录

UPDATE SET

UPDATE TABLE_NAME SET ColumnNames = VALUE , …

--修改id 为15 的记录 uid 改为 1
UPDATE settlementaccount SET 
uid='1' 
WHERE settlementaccount_id ='15'

--修改多条记录
UPDATE settlementaccount SET 
uid='2' 
WHERE settlementaccount_id in (12,14,15)

子查询

--根据子查询出的id 对 settlementaccount 表中id相等的记录更新
UPDATE settlementaccount SET 
uid='2' 
WHERE settlementaccount_id in (
	SELECT depot_id FROM depot
	where depot_address = '上海市'
)

删除记录

DELETE FROM

DELETE FROM TABLE_NAME

--删除id为15 的记录
DELETE FROM settlementaccount
	where settlementaccount_id =15

--根据子查询 删除记录
DELETE FROM settlementaccount
	where settlementaccount_id IN (
		SELECT management_id FROM management_type
			where management_name = '金融'
)

聚合函数

-- 聚合函数 可以适用于 数值，日期，字符串等
-- 当为日期类型时 MAX 返回最近时间 MIN 返回最长时间
-- 聚合函数只运行计算非空值，如果存在空值则不会被计算
-- 聚合函数 返回符合查询条件的汇总值，没有条件则返回全表汇总值

MAX()  		-- 最大值
MIN()		-- 最小值
AVG()		-- 平均值
SUM()		-- 累加值
COUNT()		-- 统计值

SELECT COUNT(1),MAX(user_age),MIN(user_age),SUM(payment_price),AVG(user_age) from invoices;

--如果 COUNT() 函数指定字段 则会返回所有不为空值的记录数 为空则不统计，如果想得到所有符合条件的记录数 则使用 COUNT(*)

-- 函数中可以可以使用表达式 
-- 在sql执行的过程中 会先获取值 再乘以0.9 最后执行 sum 的累加
SELECT SUM(payment_price * 0.9),AVG(user_age) from invoices;

-- COUNT() 函数去重

SELECT COUNT(DISTINCT client_id) FROM invoices; 

-- 获取2019年 销售额

CREATE TABLE `invoices` (
  `invoice_id` int(11) NOT NULL,
  `number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `invoice_total` decimal(9,2) NOT NULL,
  `payment_total` decimal(9,2) NOT NULL DEFAULT '0.00',
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_date` date DEFAULT NULL,
  PRIMARY KEY (`invoice_id`),
  KEY `FK_client_id` (`client_id`),
  CONSTRAINT `FK_client_id` FOREIGN KEY (`client_id`) REFERENCES `clients` (`client_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (1, '91-953-3396', 2, 101.79, 0.00, '2019-03-09', '2019-03-29', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (2, '03-898-6735', 5, 175.32, 8.18, '2019-06-11', '2019-07-01', '2019-02-12');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (3, '20-228-0335', 5, 147.99, 0.00, '2019-07-31', '2019-08-20', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (4, '56-934-0748', 3, 152.21, 0.00, '2019-03-08', '2019-03-28', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (5, '87-052-3121', 5, 169.36, 0.00, '2019-07-18', '2019-08-07', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (6, '75-587-6626', 1, 157.78, 74.55, '2019-01-29', '2019-02-18', '2019-01-03');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (7, '68-093-9863', 3, 133.87, 0.00, '2019-09-04', '2019-09-24', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (8, '78-145-1093', 1, 189.12, 0.00, '2019-05-20', '2019-06-09', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (9, '77-593-0081', 5, 172.17, 0.00, '2019-07-09', '2019-07-29', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (10, '48-266-1517', 1, 159.50, 0.00, '2019-06-30', '2019-07-20', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (11, '20-848-0181', 3, 126.15, 0.03, '2019-01-07', '2019-01-27', '2019-01-11');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (13, '41-666-1035', 5, 135.01, 87.44, '2019-06-25', '2019-07-15', '2019-01-26');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (15, '55-105-9605', 3, 167.29, 80.31, '2019-11-25', '2019-12-15', '2019-01-15');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (16, '10-451-8824', 1, 162.02, 0.00, '2019-03-30', '2019-04-19', NULL);
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (17, '33-615-4694', 3, 126.38, 68.10, '2019-07-30', '2019-08-19', '2019-01-15');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (18, '52-269-9803', 5, 180.17, 42.77, '2019-05-23', '2019-06-12', '2019-01-08');
INSERT INTO `4aaaa`.`invoices` (`invoice_id`, `number`, `client_id`, `invoice_total`, `payment_total`, `invoice_date`, `due_date`, `payment_date`) VALUES (19, '83-559-4105', 1, 134.47, 0.00, '2019-11-23', '2019-12-13', NULL);


SELECT 'First half of 2019' as date_range,
	SUM(invoice_total) as total_sales,
	SUM(payment_total) as total_payments,
	SUM(invoice_total - payment_total) as what_we_expect
FROM invoices
WHERE invoice_date < '2019-07-01'
UNION
SELECT 'Second half of 2019' as date_range,
	SUM(invoice_total) as total_sales,
	SUM(payment_total) as total_payments,
	SUM(invoice_total - payment_total) as what_we_expect
FROM invoices
WHERE invoice_date >= '2019-07-01'
UNION
SELECT 'Total' as date_range,
	SUM(invoice_total) as total_sales,
	SUM(payment_total) as total_payments,
	SUM(invoice_total - payment_total) as what_we_expect
FROM invoices
WHERE DATE_FORMAT(invoice_date,'%Y') = '2019'

GROUP BY 函数

-- 获取每个客户的总销售额 根据客户id进行分组统计 将每个客户的销售额累加
SELECT SUM(invoice_total) as total_sales
FROM invoices
GROUP BY client_id 

-- 多条件分组 
-- 获取每个城市的销售总额 根据州和城市进行分组 对每个州的城市销售额进行累加，最后对分组结果按销售额进行排序
SELECT state,city, SUM(i.invoice_total) as total_sales FROM invoices i 
INNER JOIN clients c USING (client_id)
GROUP BY c.state,c.city
ORDER BY total_sales desc

-- 根据 支付日期和消费方式进行分组 GROUP BY 可以使用别名进行分组
SELECT p.date as payment_date , pm.name as payment_method_name , SUM(p.amount) as total_payments 
FROM payments p
INNER JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
group by payment_date , payment_method_name

HAVING 子句

having 子句 一般和 GROUP BY 组合使用 ，对筛选及分组后的结果再进行筛选
WHERE 子句 在 聚合函数(分组)之前进行数据筛选 ， having 子句 在 聚合函数(分组)之后对组的记录进行筛选
相同点：WHERE 与 HAVING 都是进行数据筛选 ， 只是执行顺序不同，HAVING 的子句同样可以使用 AND,OR等
不同点：HAVING 子句用到的列必须要在SELECT-FROM的子句中 存在，如果不存在则会抛出没有列的异常
WHERE则可以使用表中所有字段当筛选条件

-- 获取每个城市的销售总额 根据州和城市进行分组 对每个州的城市销售额进行累加，最后返回城市销售额大于200的结果
SELECT state,city, SUM(i.invoice_total) as total_sales FROM invoices i 
INNER JOIN clients c USING (client_id)
GROUP BY c.state,c.city 
HAVING  total_sales > 200

-- 获取每个客户的销售额 返回销售额大于800且发票数量大于3的客户
SELECT 
	client_id,
	SUM(invoice_total) as total_sales,
	COUNT(*) as number_of_invoices
FROM invoices 
GROUP BY client_id
HAVING total_sales > 800 and number_of_invoices > 3

-- 下面的 payment_date 在SELECT 子句中不存在 所有会抛出 Unknown column 'payment_date' in 'having clause'
SELECT 
	client_id,
	SUM(invoice_total) as total_sales,
	COUNT(*) as number_of_invoices
FROM invoices 
GROUP BY client_id
HAVING total_sales > 800 and payment_date is NOT NULL

WITH ROLLUP

ROLLUP 运算符只能用于聚合值的列

-- 将查询结果进行累加 将聚合函数的每一列累加 在最后一行添加统计值
SELECT 
	client_id,
	SUM(invoice_total) as total_sales,
	COUNT(*) as number_of_invoices
FROM invoices 
GROUP BY client_id WITH ROLLUP

-- 将分组结果进行累加 对每个分组都会进行一会累加
SELECT
	state,
	city,
	SUM( i.invoice_total ) AS total_sales 
FROM
	invoices i
	INNER JOIN clients c USING ( client_id ) 
GROUP BY
	c.state,
	c.city 
	WITH ROLLUP

子查询

子查询一般以括号形式出现，当作查询条件返回

-- 获取商品金额大于 ID为3的商品金额 的结果
-- mysql 执行该查询时 会先执行子查询语句，获取到id为3的商品价格，再将子查询语句中的结果传给父查询(外部查询) ，当作父查询条件执行
SELECT * FROM products
WHERE unit_price > (
	SELECT unit_price FROM products
	WHERE product_id = 3
)

-- 获取工资大于平均工资的员工
SELECT * FROM employees
WHERE salary > (
	SELECT avg(salart) FROM employees
)

-- IN 子查询语句
-- 获取没有被订购的商品信息
-- mysql 执行该查询时，先执行子查询，返回一个商品id列表，再将id列表传给父查询条件
-- mysql解析sql为 SELECT * FROM products WHERE product_id NOT IN (1,3,5)
SELECT * FROM products 
WHERE product_id NOT IN (
	SELECT DISTINCT product_id 
	FROM order_items
)

-- 该查询与上面查询结果一致 只是可读性较低
SELECT * FROM products a 
LEFT JOIN order_items b USING (product_id)
WHERE b.product_id IS NULL

-- 查询订购了生菜的客户 返回客户id，客户姓名
-- mysql 执行该sql，先执行子查询 获取到订单明细和订单表的交集并且商品ID为3 的客户ID，返回给父查询 获取客户信息
SELECT customer_id,customer_name FROM customers
WHERE customer_id IN (
	SELECT o.customer_id FROM order_items oi
	INNER JOIN orders o USING (order_id)
	WHERE product_id = 3
)
--mysql执行该sql， 先获取客户表和订单表的交集结果列表，再由交集结果列表和订单明细表获取交集，返回商品ID为3的结果集中客户ID和姓名字段
SELECT DISTINCT customer_id,customer_name FROM customers c
INNER JOIN orders o USING(customer_id)
INNER JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

子查询 ALL

-- 获取发票金额大于 客户ID为3的发票最大金额
SELECT * FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
	FROM invoices WHERE client_id = 3
)
-- 另一种写法
-- mysql 执行该sql时，先执行子查询中的sql，获取到客户ID为3的所有发票金额，将查询的金额列表返回给父查询
-- ALL关键字 
-- 会将子查询中的结果与 父查询的记录对比，只有父查询记录的值都满足子查询的结果，才会返回
-- 例如下面sql 子查询的结果是 100，150，290，那么sql就会成 
-- SELECT * FROM invoices WHERE invoice_total > ALL(100，150，290)
-- invoices表中，每行都会把发票总额和这些数字进行比较，如果大于所有的数字 则返回到最终结果集
-- 父查询的 发票金额为 1-120，2-170，3-300，只有 ID 为3 的发票满足全部大于子查询结果 ，所有只返回3的结果
SELECT * FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
	FROM invoices WHERE client_id = 3
)
-- 字段 > all ，字段的值比子查询中的最大值还要大才返回
-- 字段 < all ，字段的值比子查询中的最小值还要小才返回
-- 字段 <> all 类似于not in 等效于not in

子查询 ANY/SOME

使用 ANY/SOME 每行数据满足子查询中的任意一条 则可以返回行

-- 获取发票数大于等于2的客户
SELECT * FROM clients
WHERE client_id IN (
	SELECT client_id FROM invoices 
	GROUP BY client_id
	HAVING COUNT(1) >= 2
)
-- ANY写法
-- mysql执行该sql语句时，先执行子查询获取到发票数大于2的客户 
-- = ANY 和 IN 运算符等效
SELECT * FROM clients
WHERE client_id = ANY(
	SELECT client_id FROM invoices 
	GROUP BY client_id
	HAVING COUNT(1) >= 2
)

相关子查询

-- 获取每个部门工资大于平均值的员工
-- mysql 执行该sql语句时，现在employees表中对每位员工执行子查询，计算同一个部门员工的平均工资，如果该员工的工资高于平均值，则返回到最终结果集，第二名员工同样获取同一部门的员工平均工资进行对比...
-- 这种子查询和外查询存在相关性，在子查询中引用了外查询的别名，则为相关子查询
SELECT * FROM employees e
WHERE salary > (
	SELECT AVG(salary) FROM employees
	WHERE office_id = e.office_id
)
-- 获取客户大于平均金额的发票
SELECT * FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
	FROM invoices
	WHERE client_id= i.client_id
)

相关子查询与非相关子查询

相关子查询：子查询引用了父查询或与父查询有相关性，子查询会在父查询每一行记录执行时都执行一次 非相关子查询 :
子查询中没有引用父查询也没有和父查询的相关性，所以mysql只会执行一次子查询，之后则会复制到父查询的WHERE子句中
相关子查询的查询速度较慢，查询的数据越多，越会占用内存

EXISTS 运算符

-- 只获取有发票的客户 IN写法
SELECT * FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id FROM invoices
)
-- JOIN 写法
SELECT DISTINCT c.client_id FROM clients c
INNER JOIN invoices i USING(client_id)
--EXISTS 写法
-- 当mysql执行该sql时，对客户表中的每一位客户，它都会检查是否存在符合这个条件的记录
-- 将客户表中的每一个客户id带入到发票表中，只要发票表中有一条符合这个条件的记录，EXISTS子句就会返回true，保留到最终结果集，反之则返回false，不添加到最终结果集
SELECT * FROM clients c
WHERE EXISTS (
	SELECT client_id FROM invoices
	WHERE client_id = c.client_id
);
-- 获取到商品表中没有被订购过的商品
-- 当mysql执行该sql时
--1、首先进行外层查询，在商品表(products)中查询满足条件的商品单价大于3(p.unit_price > 3)的记录
--2、接下来进行内层查询，将满足商品单价大于3的结果集带入内层的表order_items中进行查询，
--3、如果内层的表order_items满足查询条件，则返回true，该条数据保留
--4、如果内层的表order_items不满足查询条件，则返回false，则删除该条数据
--5、最终将外层的所有满足条件的数据进行返回
-- NOT EXISTS 如果子查询返回符合条件的true 则将结果改写为false，不将该记录添加到结果集
SELECT * FROM products p
WHERE p.unit_price > 3 AND NOT EXISTS (
	SELECT * FROM order_items 
	WHERE product_id = p.product_id
)
-- IN 写法
SELECT * FROM products
WHERE p.unit_price > 3 AND product_id NOT IN (
		SELECT DISTINCT product_id FROM order_items 
)
-- JOIN 写法
SELECT DISTINCT p.product_id,o.order_id FROM products p
LEFT JOIN order_items o USING(product_id)
WHERE p.unit_price > 3
GROUP BY p.product_id HAVING o.order_id IS NULL

EXISTS 查询相比 IN查询区别

• IN mysql 会先执行子查询将结果返回到WHERE语句中，如果子查询的结果很多会影响到查询的性能
• EXISTS 查询中，子查询不会给父查询返回具体的查询到的数据,如果父查询的记录符合子查询 则返回true，不符合则返回false
• EXISTS括号中的子查询并不会返回具体的查询到的数据，只是会返回true或者false，如果外层sql的字段在子查询中存在则返回true，不存在则返回false
• EXISTS即使子查询的查询结果是null，只要是对应的字段是存在的，子查询中则返回true
• 如果 IN 的 结果很多 应该替换成EXISTS ，因为EXISTS子查询并没有把真的结果集返回给父查询，只返回true或false

循环嵌套查询执行原理
👉 循环由外向内，外层循环执行一次，内层循环则需要完整的执行一次，内层执行执行完后返回执行结果，外层循环继续执行，直到外层循环完全执行完成

循环优化策略
👉 有了上面的执行原理的说明，我们明白了一个道理：内层循环次数的多少不会影响到外层的次数，但是外层循环的次数直接会影响到内层循环的次数，外层循环每多一次，内层循环就需要多完整的一次循环，所以我们优化的目标其实就是使外层的循环次数尽量少，总结来说：小表驱动大表。小表就是外层循环，大表就是内层循环，也就是尽量减少外层循环的次数

exists和in查询原理的区别
👀 exists ： 外表先进行循环查询，将查询结果放入exists的子查询中进行条件验证，确定外层查询数据是否保留
👀 in ： 先查询内表，将内表的查询结果当做条件提供给外表查询语句进行比较

• 外层小表，内层大表（或者将sql从左到由来看：左面小表，右边大表）： exists 比 in 的效率高
• 外层大表，内层小表（或者将sql从左到由来看：左面大表，右边小表）： in 比 exists 的效率高

SELECT 子句子查询

-- 获取所有发票及发票与平均值的差值
-- ( SELECT AVG( invoice_total ) FROM invoices ) AS invoice_average 获取到表的平均值
-- 在SELECT 子句中不能引用查询别名，只能用 ( SELECT AVG( invoice_total ) FROM invoices ) - invoice_total 或者如下( SELECT invoice_average )
-- 如果在SELECT 子句中使用别名 可以写为( SELECT 别名)
SELECT 
	invoice_id, 
	invoice_total,
	( SELECT AVG( invoice_total ) FROM invoices ) AS invoice_average,
	(( SELECT invoice_average ) - i.invoice_total) as difference
FROM
	invoices i

-- 获取每个客户的发票总额和发票平均值并计算其差
SELECT 
	client_id,
	name,
	(SELECT SUM(invoice_total) FROM invoices
		WHERE client_id = c.client_id) as total_sale,
	(SELECT AVG(invoice_total) FROM invoices
		WHERE client_id = c.client_id) as avg_sale,
	(SELECT total_sale - avg_sale)
FROM clients c

FROM 子查询

-- 获取有发票的客户发票总额和发票平均值
SELECT * FROM (
	SELECT 
		client_id,
		name,
		(SELECT SUM(invoice_total) FROM invoices
			WHERE client_id = c.client_id) as total_sale,
		(SELECT AVG(invoice_total) FROM invoices
			WHERE client_id = c.client_id) as avg_sale,
		(SELECT total_sale	- avg_sale)
	FROM clients c
) sales_summary 
WHERE sales_summary.total_sale IS NOT NULL

MYSQL 内置函数

数值函数

ROUND() > 四舍五入数值

ROUND(5.13,1) -> 保留一位小数点 结果为5.1
ROUND(5.16,1) -> 保留一位小数点 结果为5.2

TRUNCATE() > 用于截断数值

TRUNCATE(5.13,1) -> 截取小数点后1位 结果为5.1

CEILING() > 返回大于或等于数值的最小整数

CEILING(5.13) -> 返回结果6

FLOOR() > 返回小于或等于数值的最小整数

FLOOR(5.13) -> 返回结果5

ABS() > 返回数值的绝对值

ABS(5.13) -> 返回结果5.13
ABS(-5.13) -> 返回结果5.13

RAND() > 返回 0-1区间的随机浮点数

RAND()

字符串函数

LENGTH() -> 返回字符串的长度  例如：LENGTH('SKY')  - 3
UPPER() -> 转大写
LOWER() -> 转小写
LTRIM() -> 去除字符串左边的空格
RTRIM() -> 去除字符串右边的空格
TRIM() -> 去除字符串左右俩边的空格
LEFT() -> 从左边开始获取指定长度的字符串
RIGHT() -> 从右边开始获取指定长度的字符串
SUBSTRING() -> 获取指定位置的字符串 例如:SUBSTRING('ASDFGH',3,1) 从第三位开始获取1个字符 -D
CONCAT() -> 将字符串拼接  例如 CONCAT('1','-','3') -  1-3

日期函数

NOW()   当前时间
CURDATE()   当前年月日
CURTIME()   当前时分秒
YEAR()    获取当前年    ->  YEAR('2022-09-01')   - 2022
MONTH()    获取当前月    ->  YEAR('2022-09-01')   - 9
DAY()    获取当前日    ->  YEAR('2022-09-01')   - 1
-- 获取2019的所有订单
SELECT * FROM orders WHERE YEAR(order_date) = 2019 
  
-- %Y 表示4为年份  %y 表示2位年份  
-- %M 表示月份名称  %m 表示2位月份  
DATE_FORMAT()    ->  DATE_FORMAT('20220901','%Y-%m')  -  2022-09
					 DATE_FORMAT('20220901','%Y-%m%d-%H')   -   2022-0901-00
-- 日期格式化
TIME_FORMAT()  ->  SELECT TIME_FORMAT(NOW(),'%H:%m:%s %p')   -  16:00:39 PM

-- 时间计算
DATE_ADD()   -> DATE_ADD(NOW(),INTERVAL 1 MONTH)  将当前时间加一月
				DATE_ADD(NOW(),INTERVAL 1 DAY)  将当前时间加一天
				DATE_ADD(NOW(),INTERVAL 1 YEAR)  将当前时间加一年
				DATE_ADD(NOW(),INTERVAL -1 YEAR)  将当前时间减一年
DATE_SUB()    -> 减去时间 	
DATEDIFF()   ->  计算日期间隔
TIME_TO_SEC()   ->  计算时间间隔

IFNULL

IFNULL()   ->  将字段为空的数据替换  IFNULL(shipper_id,'未发货')  - 如果shipper_id为空 则函数返回‘未发货’
COALESCE()  -> 将字段为空的数据替换  COALESCE(shipper_id,comments,'未发货') - 如果shipper_id为空 ，则返回comments的数据，如果comments也为空，最后返回未发货

IF

-- expression成立 则返回first 反之返回second
IF(expression,first,second)
IF(shipper_id IS NULL,'未发货','已发货')  - 如果shipper_id为空 则函数返回‘未发货’，反之返回已发货

SELECT 
	product_id,
	name,
	(SELECT COUNT(1) FROM order_items WHERE product_id = p.product_id)  as order_count,
	IF( (SELECT order_count) > 1 , '多次订购', '订购一次')
 FROM products p

CASE WHEN

-- CASE - WHEN -THEN .... ELSE - END
SELECT 
	product_id,
	name,
	(SELECT COUNT(1) FROM order_items WHERE product_id = p.product_id)  as order_count,
	CASE 
	WHEN (SELECT order_count) > 1 THEN '多次'
	WHEN (SELECT order_count) = 1 THEN '一次'
	ELSE
		'零次'
	END AS total
 FROM products p 
-- 获取客户等级
SELECT CONCAT(first_name,' ',last_name),
points,
CASE
WHEN points > 3000 THEN 'Gold'
WHEN points > 2000 AND points < 3000 THEN 'Sliver'
WHEN points < 2000 THEN 'Bronze'
ELSE 'UNDEFINED'
END AS category
FROM customers
ORDER BY points DESC

视图

视图概念

视图，其实就是一张虚拟的数据表，该数据表与其他普通数据表一样，有列和属性。视图和普通的数据表唯一不同的是，视图中本身没有数据或者说视图本身不存储数据，视图中的数据都是从各个数据表中select查询的结果，对视图中数据的插入和修改本质上也是对原始数据表的插入和修改，因此，我们可以修改视图的数据，但是不能删除视图中的数据和向视图内插入数据。
视图（子查询）：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储
视图每次在执行时，相当于重新执行了一次SQL查询，只是简化了查询，不需要重复写复杂的SQL
创建视图时查询SQL返回的字段就会成为视图的列，后续只能通过修改或删除修改列

视图的优缺点

1. 优点

1. 简单化：数据所见即所得，开发人员不需要关心视图对应的数据表的结构，表与表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单的操作视图即可，极大简化了开发人员对数据库的操作
2. 安全性：将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现，用户不比直接查询或操作数据表。这也可以理解为视图具有隔离性，视图相当于在用户和实际的数据表之间加了一层虚拟表。同时，MySQL也可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息，一定程度上保障了数据表中数据的安全性
3. 减少数据冗余：视图跟实际数据表不一样，它存储的是查询语句。所以在使用的时候，我们需要通过定义视图的查询语句来获取结果集，而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余
4. 适应灵活多变的需求：当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减少改动的工作量
5. 能够分解复杂的查询逻辑：数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑

2. 缺点

1. 如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护，特别是嵌套的视图，维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患，创建的视图的SQL查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护成本
2. 不当的使用可能会导致性能问题，视图的定义中可能包含了复杂的查询，例如嵌套的子查询和多个表的连接查询，可能导致使用视图进行查询时性能不佳；
3. 视图通常用于查询操作，可更新视图（Updatable View）需要满足许多限制条件。可更新视图可以支持通过视图对底层表进行增删改的操作

创建视图

• 基本语法

CREATE VIEW view_name(视图名称) AS SELECT语句

例：

-- 创建用户销售额的视图 
-- CREATE VIEW sales_by_client AS 创建视图基本语法
-- 后续的子查询将结果返回到视图
-- 定义视图的列为 client_id,name,total_sales 
CREATE VIEW sales_by_client AS 
SELECT
	c.client_id,
	c.NAME,
	SUM( invoice_total ) AS total_sales 
FROM
	clients c
	INNER JOIN invoices i USING ( client_id ) 
GROUP BY
	client_id

• 使用视图

-- 获取客户的销售额并按销售额排序
-- 视图相对与数据表从查询的角度看基本没有什么不同，数据表查询的写法都可以在视图查询中使用
-- 如 WHERE , ORDER BY ,GROUP BY, JOIN ,函数...
SELECT * FROM sales_by_client ORDER BY total_sales DESC

-- 将客户销售额视图和客户表关联
SELECT * FROM sales_by_client 
INNER JOIN clients USING(client_id)

-- 创建客户结余视图，发票金额 - 支付金额 = 结余金额
CREATE VIEW clients_balance AS 
SELECT
	c.client_id,
	c.NAME,
	SUM( invoice_total - payment_total ) AS clients_balance 
FROM
	clients c
	INNER JOIN invoices i USING ( client_id ) 
GROUP BY
	c.client_id,
	c.NAME

更改或删除视图

• 删除视图

DROP VIEW view-name(视图名称)

-- 删除视图sales_by_client
DROP VIEW sales_by_client

• 修改视图

REPLACE : 用于视图修改
CREATE OR REPLACE VIEW view-name(视图名) AS SELECT 语句

-- 用于修改视图 视图会按新的查询结果进行修改存储
CREATE OR REPLACE VIEW clients_balance AS 
SELECT
	c.client_id,
	c.NAME,
	c.address,
	SUM( invoice_total - payment_total ) AS clients_balance 
FROM
	clients c
	INNER JOIN invoices i USING ( client_id ) 
GROUP BY
	c.client_id,
	c.NAME
ORDER BY clients_balance DESC

在视图中使用的排序，如果在视图外添加排序，则视图内的排序将失效

• 视图SELECT语句 在没有 DISTINCT,聚合函数(MAX,MIN,SUM…)，GROUP BY / HAVING，UNION等关键字时，视图为可更新视图，在可更新视图上可以使用 INSERT,UPDATE,DELETE等语句来修改数据

CREATE VIEW invoices_with_balance AS 
SELECT 
	invoice_id,
	number,
	client_id,
	invoice_total,
	payment_total,
	invoice_total - payment_total AS balance,
	invoice_date,
	due_date,
	payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

-- 删除视图中的记录
DELETE * FROM invoices_with_balance 
WHERE invoice_id = 1

-- 修改视图中的记录
UPDATE invoices_with_balance SET 
due_date = DATE_ADD(due_date , INTERVAL 2 DAY)
WHERE invoice_id = 2

-- 插入记录到视图 如果视图中列有空值，则可以传入空值，反之不能传入空值
INSERT INTO invoices_with_balance(
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total - payment_total AS balance,
invoice_date,
due_date,
payment_date) VALUES (
24,'34-615-4694',8,2200.00,1200.00,1000.00,'2019-07-30','2019-08-19','2019-01-15'
)

WITH OPTION CHECK

在视图更新或删除时，默认会使一些数据消失，如果不希望UPDATE或DELETE将行从视图删除，则在创建视图语句最后添加
WITH OPTION CHECK

CREATE VIEW invoices_with_balance AS 
SELECT 
	invoice_id,
	number,
	client_id,
	invoice_total,
	payment_total,
	invoice_total - payment_total AS balance,
	invoice_date,
	due_date,
	payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH OPTION CHECK

定义视图时的其他选项

视图的完整语法

CREATE [OR REPLACE]
[ALGORTIHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = {user | CURRENT_USER}]
[SQL SECURITY {DEFINER | INVOKER}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] OPTION CHECK]

ALGORITHM选项

选择在处理定义视图的select语句中使用的方法

1. UNDEFINED：MySQL将自动选择所要使用的算法(默认)
2. MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分
3. TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句
4. 缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED

DEFINER选项 指出谁是视图的创建者或定义者

1. definer= ‘用户名’@’登录主机’
2. 如果不指定该选项，则创建视图的用户就是定义者，指定关键字CURRENT_USER(当前用户)和不指定该选项效果相同

SQL SECURITY选项

要查询一个视图，首先必须要具有对视图的select权限，如果同一个用户对于视图所访问的表没有select权限，那会怎么样？
SQL SECURITY选项决定执行的结果：

1. SQL SECURITY DEFINER：定义(创建)视图的用户必须对视图所访问的表具有select权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。
2. SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有select权限。
3. 缺省SQL SECURITY选项等同于SQL SECURITY DEFINER

视图权限

使用root用户定义一个视图：u1、u2
1）u1作为定义者定义一个视图，u1对基表有select权限，u2对视图有访问权限：u2是以定义者的身份访问可以查询到基表的内容；
select权限，也就是说将来其他用户访问表的时候以定义者的身份，此时其他用户并没有访问权限。
– SQL SECURITY INVOKER：访问视图的用户必须对视图所访问的表具有select权限。
缺省SQL SECURITY选项等同于SQL SECURITY DEFINER
使用root用户定义一个视图(推荐使用第一种)：u1、u2
2）u1作为定义者定义一个视图，u1对基表有select权限，u2对视图有访问权限：u2是以定义者的身份访问可以查询到基表的内容；
3）u1作为定义者定义一个视图，u1对基表没有select权限，u2对视图有访问权限，u2对基表有select权限：u2访问视图的时候是以调用者的身份，此时调用者是u2，可以查询到基表的内容。

存储过程和函数

触发器

触发器介绍

触发器是在插入，更新和删除语句前后自动执行的SQL代码，使用触发器来增强 数据的一致性。
对一张表进行操作（插入，更新，删除）时，就会触发执行事先编辑好的若干条SQL语句
触发器里面的sql语句是一个事务，具有原子性，要么全部执行，要么全部不执行

触发器什么时候执行？

INSERT语句，UPDATE语句和DELETE语句执行时触发
触发时机：在增删改前或后触发
会针对增删改的每一行上执行

• 触发器基本语法

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 
表名 FOR EACH ROW 
触发器执行的语句块;

1. 表名 ：表示触发器监控的对象。
2. BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。
3. INSERT|UPDATE|DELETE ：表示触发的事件

• INSERT 表示插入记录时触发；
• UPDATE 表示更新记录时触发；
• DELETE 表示删除记录时触发。

4. 触发器执行的语句块 ：可以是单条SQL语句，也可以是由BEGIN…END结构组成的复合语句块。
5. 触发器执行语句块中可以修改任何表中数据，除了触发器监控的表，否则陷入死循环

-- 创建付款表插入后发票单自算
-- CREATE TRIGGER payment_after_insert 创建payment_after_insert触发器
-- AFTER INSERT ON payments 在payments表插入语句后执行
-- FOR EACH ROW  表示触发器会作用于每一个受影响的行 如果新增5行数据，每一行都会触发
-- BEGIN  END  表明触发器主体部分,触发器主体中可以写任意SQL代码修改数据，用来增强数据的一致性
-- NEW 会返回新插入的行的数据 OLD 在更新或删除行时返回更新前的行以及对应数值
-- UPDATE invoices SET payment_total = payment_total + NEW.amount 表示 在新增时更新发票表中的支付金额 
-- NEW.amount 表示 新增的行中amount列的值
-- WHERE invoice_id = NEW.invoices_id 更新条件 新增的行发票id与发票表中的id相同时更新
-- 该触发器会在payments表插入数据时同步更新发票id相同的支付金额字段数据
DELIMITER $$
CREATE TRIGGER payment_after_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
	UPDATE invoices SET payment_total = payment_total + NEW.amount
	WHERE invoice_id = NEW.invoice_id;
END $$
DELIMITER;

-- 创建触发器 在付款表删除之前对发票表进行数据处理
-- CREATE TRIGGER payment_before_delete 创建触发器payment_before_delete
-- BEFORE DELETE ON payments  在payments表执行删除操作之前
-- FOR EACH ROW 对每条要删除的行进行触发
-- BEGIN END 主体中表示 在删除支付信息之前将发票已支付金额减少
-- OLD 表示删除行的数据
-- OLD.amount 获取要删除行的amount列的值
-- WHERE invoice_id = OLD.invoices_id 将发票行与删除行的发票id进行对比 修改相同id的发票表数据
DELIMITER $$ 
CREATE TRIGGER payment_before_delete
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
	UPDATE invoices SET payment_total = payment_total - OLD.amount
	WHERE invoice_id = OLD.invoice_id;
END $$
DELIMITER;

查看触发器

• 查看当前数据库所有触发器

SHOW TRIGGERS;
SHOW TRIGGERS LIKE ‘payments%’; 获取所有payments开头的触发器

Trigger : 触发器名称
Event : 触发事件
Table : 监听表
Statement：主体SQL代码
Timing：触发时机
Created：创建事件
sql_mode：数据模型
Definer：创建人

• 触发器命名规则

• table_[befoer|after]_[insert|update|delete]

删除触发器

DROP TRIGGER IF EXISTE payment_after_insert

使用触发器做审计功能

当用户对表进行 增删改操作时，可以使用触发器对其操作进行记录

• 例：创建支付审计表

CREATE TABLE payments_audit 
(
	client_id  INT NOT NULL,
	date  DATE NOT NULL,
	amount  DECIMAL(9,2) NOT NULL,
	action_type  VARCHAR(50) NOT NULL,
	action_date  DATETIME NOT NULL
)

-- 在付款表插入数据时，对发票表进行已支付金额累加，同时对支付审计表进行插入操作记录
CREATE TRIGGER payments_after_insert
AFTER INSERT ON payments
FOR EACH ROW
BEGIN
	UPDATE invoices SET payment_total = payment_total + NEW.amount
	WHERE invoice_id = NEW.invoice_id;
	INSERT INTO payments_audit
	VALUES (NEW.client_id,NEW.date,NEW.amount,'INSERT',NOW());
END

-- 在付款表删除数据时，对发票表进行已支付金额减去删除行支付金额，同时对支付审计表进行插入操作记录
-- OLD.client_id,OLD.date,OLD.amount 删除的行的数据
CREATE TRIGGER payment_before_delete
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
	UPDATE invoices SET payment_total = payment_total - OLD.amount
	WHERE invoice_id = OLD.invoice_id; 
	INSERT INTO payments_audit
	VALUES (OLD.client_id,OLD.date,OLD.amount,'DELETE',NOW());
END

事件

• 事件是根据计划执行的任务或一堆SQL代码
• 事件可以执行一次，也可以按照某种规律执行。例如定时任务
• 通过事件可以自动化数据库维护任务，例如删除过期数据或将数据移动到存档表和汇总数据生成报告

查询事件

SHOW VARIABLES; 查询系统变量
SHOW VARIABLES LIKE ‘event%’ 查询事件是否开启
SET GLOBAL event_scheduler = ON/OFF 开启事件或关闭事件

创建事件语法

CREATE EVENT event_name
ON SCHEDULE 
[AT|EVERY] [time|DAY/HOUR/YEAR] [STARTS][ENDS]
DO BEGIN
	SQL代码
END

-- 创建事件 yearly_delete_stale_audit_rows 每年定时删除
--  AT '2022-12-31'   AT 表示该事件只执行一次  执行事件的时间为2022-12-31
-- EVERY 2 DAY/HOUR/YEAR  EVERY表示定期执行，每2天执行一次该事件
-- STARTS '2022-01-01' 表示事件开始时间
-- ENDS '2099-01-01 表示事件结束时间
DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE 
-- AT '2022-12-31'
--EVERY 2 HOUR   -- 2小时执行一次该事件
EVERY 1 YEAR STARTS '2022-01-01'  ENDS '2099-01-01'
DO BEGIN
	-- 删除前一年的审计数据
	DELETE FROM payments_audit
	WHERE action_date  <  NOW() - INTERVAL 1 YEAR
END $$
DELIMITER

查看，更新和删除事件

查看当前数据库中的事件 - SHOW EVENTS;
删除事件 - DROP EVENT IF EXISTS event_name
修改事件 ALTER EVENT … 和创建视图语法相同 只是将CREATE 换成 ALTER
启用/停止 事件 ALTER EVENT event_name [ENABLE |DISABLE]

事务

1. 事务基本概念

事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事务被顺利执行。
即开始事务后执行的所有SQL都是一个整体，一个单元，要么都执行，要么都不执行。这里不执行的意思就row back即回滚，就是回到事务执行前的状态。
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

2. 事务四大特征(ACID)

• 原子性(A | Atomicity)：事务是最小单位，不可再分。原子性是指事务是一个不可分割的工作单位，事务中的操作要么 都发生，要么都不发生。每个事务都是一个工作单元，不管它包含多少语句，要么所有的语句都执行成功并且事务被提交，要么事务被退回所有的更改被撤销
• 一致性(C | Consistency)：事务必须使数据库从一个一致性状态变换到另外一个一致性状态 。事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败。
• 隔离性(I | Isolation)：事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。多个事务之间相互隔离的，互不干扰即事务A和事务B之间具有隔离性
• 持久性(D | Durability)：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是 永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

3. 创建事务

• 创建事务语法

START TRANSACTION; -开启/创建事务
COMMIT; 提交事务
ROLLBACK; 回退事务

• MySQL 在做INSERT,UPDATE,DELETE 操作时，都会添加事务自动提交

• 查看是否开启自动提交 SHOW VARIABLES LIKE ‘autocommit’ ;
  如果没有报错则提交，有错误则回滚

--START TRANSACTION; 创建事务
-- 下面的sql代码，开启事务并执行对订单表和订单明细表的插入，commit将事务提交，如果其中一个插入失败，则会自动撤销之前的更改
START TRANSACTION;

INSERT INTO orders (customer_id,order_date,status) VALUES (1,'2022-09-20',1);
-- 插入order_items表中所有列，LAST_INSERT_ID() 获取上一条插入的id
INSERT INTO order_items VALUES(LAST_INSERT_ID(),1,1,1);

COMMIT;

3.1 并发和锁定

• 并发基本概念

在多用户用时访问相同的数据时，就是并发

如果一个事务试图修改一行或多行，则会对表的这些行进行上锁，这个锁会防止其他事务修改这些行数据，直到事务提交回回滚才会释放锁让后续的事务进行这些行的操作

-- 第一个事务 对客户ID为1的积分增加100
 START TRANSACTION;
 UPDATE clients SET points = points + 100 WHERE client_id = 1;
 COMMIT;
 -- 第一个事务 对客户ID为1的联系方式进行修改
 START TRANSACTION;
 UPDATE clients SET phone = '15988881111' WHERE client_id = 1;
 COMMIT;

- 模拟流程 事务1开启后对客户积分进行修改时第二个事务开启并且修改客户的联系方式
- MYSQL 会在第一个事务执行UPDATE语句的时候对 ID 为 1 的客户这行数据进行上锁 保证该行不会被其他事务进行修改。
- 在第一个事务没有提交之前，ID为1 的行数据一直被上锁，所有在第二个事务开始修改客户联系方式时，会进入等待，等第一个事务提交或者报错回滚，将行所释放后才可以修改联系方式

并发问题

1. 数据丢失： 当俩个事务尝试更新相同的数据并且没有上锁时，就会发生先提交的事务会被后提交的事务进行覆盖

如上图所示，A事务会对客户所在州进行更新，B事务会对客户积分进行更新 , 在不上锁的情况下

A和B同事开启事务，并且拿到行数据 Jogn,NY ,10

A 对该行数据的地址进行修改，B对该行的积分进行修改

当A提交后B提交，则B的修改数据则为 Jogn,NY ,20 而A的修改则会被覆盖

• MySQL 锁定机制

• MySQL 会使用锁定机制来防止多个事务同时更新同样的数据，使多个事务按照顺序运行。

2. 数据脏读

数据并发情况下，A事务读取了B事务还没有提交的数据，B发生错误进行数据回滚，A拿到的数据就为脏数据

• A事务对客户积分进行更新，B事务获取积分进行折扣计算
• B事务读取了A事务没有提交的数据，也就是积分20 进行后续逻辑的时候，A事务发生错误数据回滚
• 这是客户的积分不是20，所以B事务的数据是脏数据，会发生数据无法核算

避免脏读: 使用隔离级别为 READ COMMITTED ，该事务隔离级别会只读取已提交的数据

3. 不可重复读

不可重复读:在同一个事务中，读取同一个数据俩次并且得到了不同的结果

A 事务查询 客户积分进行处理，B事务修改客户积分

A事务在第一次进行读取积分没有提交之前，B事务将客户积分进行修改

假如A事务在子查询中第二次读取积分后 与第一次读取结果不同，会导致计算错误

解决不可重复读：在事务上加入事务隔离级别 可重复读，该隔离级别保证读取的数据是可重复和一直的，保证数据更改对事务不可见

4. 幻读

当前事务读取了一个范围的记录，另一个事务在该范围内插入了新记录，当前事务再次读取该范围内的记录就会发现新插入的记录，这就是幻读

A事务在读取客户积分大于10后并且事务没有提交时，B事务对某个客户的积分改为大于 10

A事务提交后并没有B事务修改的客户，再次查询就会出现，所以叫幻读

解决幻读：添加隔离级别 SERIALIZABLE（序列化），该隔离级别保证当B事务更新数据时，A事务能够获取到最新结果 ，但A需要等待B事务提交

事务的隔离级别

• Lost Updates : 丢失更新 （多个事务更新同一行数据，最后提交的事务覆盖先前所作的更改）
• Dirty Reads: 脏读 (读取了未提交的数据)
• Non-repeating Reads : 不可重复度( 在同一个事务中读取俩次相同的数据，得到不同的结果)
• Phantom Reads : 幻读 （查询缺失一行或多行记录）
• READ UNCOMMITTED : 读取未提交数据，没有事务隔离
  不设置任何锁，并且忽略了其他事务设置的锁
• READ COMMITTED : 读取已提交数据，添加事务隔离级别，解决脏读
• REPEATABLE READ : 可重复读取
• SERIALIZABLE : 可序列化 可以解决所有问题 但会给数据库很大压力，多个事务会一直等待

• 隔离级别

SERIALIZABLE > REPEATABLE READ > READ COMMITTED > READ UNCOMMITTED

• 越低的隔离级别越容易出现并发问题，多个用户可以同时接触相同的数据
• 越高的隔离级别越不会出现并发问题，但会降低性能和可扩展性
• MySQL默认的事务隔离级别时 可重复读取

查看数据库隔离级别

SHOW VARIABLES LIKE ‘transaction_isolation’;

设置数据库隔离级别

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; - 为下一个事务设置隔离级别,只生效于下个事务
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 为当前会话或者连接所有以后的事务设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE; 设置全局隔离级别

5. 死锁

• 死锁概念

死锁就是当不同的事务均因握住了别的事务需要的锁 而无法完成的情况 ，所以事务都在等待对方，并且没有方法释放锁
死锁是2+个线程在执行过程中， 因争夺资源而造成的相互等待的现象，若无外力作用，它们将无法推进下去。

-- A事务
 START TRANSACTION;
 UPDATE customers SET state = 'VA' WHERE customer_id = 1;
 UPDATE orders SET status - 1 WHERE order_id = 1;
 COMMIT;
 
-- B事务
 START TRANSACTION;
 UPDATE orders SET status - 1 WHERE order_id = 1;
 UPDATE customers SET state = 'VA' WHERE customer_id = 1;
 COMMIT;

在A事务第一条修改 客户ID为1的州信息时，ID为1的客户行记录就会被A事务上锁，其他事务将无法更新该行记录必须等A事务提交后释放锁才可以更新
在A事务执行完更新客户信息时，B事务开始修改订单信息，并将订单ID为1的行记录上锁，但当B执行到更新A事务同一个客户信息时需要等待A事务释放锁，并且当A执行更新订单时又会出现更新同一条记录需要等B事务释放行锁

所以 AB事务都在等待对方，所以永远无法完成即为死锁

MySQL 数据类型

• String Types : 字符串类型
• Numberic Types : 数值类型
• Date and Time Types : 日期时间类型
• Blob Types : 二进制数据类型
• Spatial Types : 空间类型

字符串类型

CHAR(X) : 用于存储固定长度字符串
VARCHAR(X) - 最大长度65000(64KB) : 用于存储可变长度字符串，字符串类型的不能使用做数学运算，超过长度会截断
MEDIUMTEXT - 最大长度1600万(16MB) : 用于存储更多的字符（JSON对象，SCV字符串和中长度的书）
LONGTEXT(长文本串) - 最多存储4GB文本数据 : 用于存储脚本或日志文件
TINYTEXT (微文本串) - 最大长度 255(255 bytes)
TEXT(文本类型) - 最大长度65000(64KB) 和VARCHAR一样 ， TEXT不能使用索引

整数类型

TINYINT - (1b) - 长度 [-128,127]
UNSINGED TINYINT - (1b) - 长度[0,255] : 如果标记数值为无符号，那就只能存储正数，常用于年龄
SMALLINT - (2b) - -32K,32K MEDIUMINT - (3b) - -8M , 8M
INT - (4b) - -2B , 2B
BIGINT - (8b) - [-9Z , 9Z]( 2的64方 -1 )

浮点类型

DECIMAL(p,s) , 常用于金额 p - 精度：表示最大位数 [1-65之间]，s - 小数位
DECIMAL(9,2) 表示最多可以存储9位数字，7位整数，2位小数
DEC,NUMERIC,FIXED 和DECIMAL 完全一样

FLOAT (4b): 单精度浮点类型
DOUBLE (8b): 双精度浮点类型
FLOAT与DOUBLE常用于科学计算,但会丢失精度

布尔型

BOOLEAN / BOOL 用于存储 TRUE (1) / FASLE (0) ，常用于存 0 或者 1

枚举

ENUM : 用于限制列的值
ENUM(‘small’,‘medium’,‘large’) 限制列的值只能是这三种，如果存储其他值则会报错
枚举类型应尽量少用，1.改变枚举的组成很麻烦 2.如果枚举变动，表中数据多很费时间
SET(…) 与 枚举类型相似，集合可以存储多个值，但同样不建议使用

日期数据类型

DATE ：用于存储没有时间的日期
TIME ：用于存储时间值
DATETIME ：用于存储 日期和时间
TIMESTAMP ：时间戳格式
YEAR ：用于存储四位数年份
时间戳 4 字节，只能存储到2038年以前的日期，DATETIME 8 字节，可以存储到2038年之后的日期

二进制类型

Blob : 用于存储大型的二进制数据，如图像，视频，PDF，WORD 文件
TINYBLOB - 255b
BLOB - 65KB
MEDIUMBLOB - 16MB
LONGBLOB - 4G
一般不会将文件存入数据库，会增加数据库的内存及备份的速度。

JSON类型

JSON : 一般用于存储前后端的固定json数据

数据库设计

数据建模

1. Understand the requirements : 理解和分析业务需求
2. Build a Conceptual Model ：构建业务的概念模型
3. Build a Logical Model ：生成数据模型或数据结构用于存储数据，构建逻辑模型，逻辑模型是独立于数据技术的抽象数据模型
4. Build a Physical Model ：为数据库管理系统构建一个实体模型

• Conceptuak 概念模型

概念模型 用于表示业务中的实体，事物或者概念以及它们之间的关系，通过可视化的方式观察这些实体之间的关系
通常使用 ER模型或UML模型来展示概念，数据建模
概念模型用于表示实体有哪些属性及实体对应关系(一对一，一对多，多对多)

• Logical 逻辑模型

逻辑模型 在概念模型的基础上生成数据模型或数据结构，用于存储数据
逻辑模型是独立于数据库的技术，它是一个抽象的数据模型，能清楚的显示实体及关系架构
逻辑模型要比概念模型更加细节
逻辑模型在概念模型的基础上指定属性的类型(例如字符串，数值，日期等)，细化实体间关系，拆分实体关系生成中间表等

• Physical 实体模型

实体模型 在逻辑模型的基础上，通过特定的数据库技术来实现（一般通过数据库建模工具 EER 实现对应的数据表及关系图）
实体模型 是可以直接运行在特定数据库数据库的模型
实体模型 在逻辑模型的基础上，将实体属性类型转换为对应的数据库类型，设置表名及对应列的约束(如 不为空，主键，默认值等)
将逻辑实体的关系转换为数据表之间关系，如 主子表(一对多)，在子表添加主表ID。中间表添加关联表的ID或对关联表添加外键等

数据库范式

第一范式

• 第一范式：要求数据表中每一行中的每个单元格都应该有单一值，并且不能出现重复列
  例如A表中的某一列中存在多个值，用逗号分隔记录。这违反了第一范式，应该创建新表B来记录逗号分隔的值并且使用中间表(链接表)对A表和B表关联

第二范式

• 第二范式：一组关系必须符合第一范式并且它不能有任何取决与这组关系任何候选键的任何真子集的非主属性
• 解释为：每张表都应该有一个单一的目的（只能代表一种且仅有一种实体类型），而表中的每一列都应该用来描述这个实体，如果该实体（表）中有一列描述了其他的东西，则应该取出该列单独存放一个实体并关联（新建表并用新表ID进行关联）
  例如 A表为订单表，其中一列记录了客户的名称。该设计则违反第二范式
  应该将客户单独建表，并且在A表只记录客户ID

第三范式

• 第三范式：实体必须符合第二范式并且表中所有属性只能由那组候选键决定
• 解释为：表中的列不应该派生自其他列
  例如 A表（订单表）中 存在 发票金额，支付金额，未支付金额三列，如果发票金额或者支付金额变动，那么未支付金额也需要变动。未支付金额取决于发票金额和支付金额，存在改变发票或支付金额忘记修改未支付金额的问题，导致数据错误。
  应该删除未支付列，在查询时主动计算未支付金额

外键

• 外键概念

外键是指引用另外一个表中的一列或多列数据，被引用的列应该具有主键约束或者唯一性约束（简单来说外键是另一个表的主键或者唯一约束）。外键可以有重复的, 可以是空值，用来建立和加强两个表数据之间的连接
外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接

• 外键使用条件

1. 两张表必须都是InnoDB表，并且它们没有临时表。
2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。
3. 建立外键关系的对应列必须建立了索引。

• 创建外键的基本语法

• 定义语法
  [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)
  REFERENCES tbl_name (index_col_name, …)
  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
• ON UPDATE / DELETE 表示在关联表更新或删除时要做的操作
• 这里 外键约束表称为子表 关联表为主表
  RESTRICT : 如果主表ID或外键字段的值更新或删除，在子表中不去操作数据
  CASCADE : 如果主表ID或外键字段的值更新或删除，在子表中将该ID或外键字段的值也会随之改动，变为主表更新之后的值
  SET NULL : 如果主表ID或外键字段的值更新或删除，子表将原关联ID或外键字段的值设为空 （孤儿记录）
  SET DEFAULT : 如果主表ID或外键字段的值更新或删除，子表将原关联ID或外键字段的值设为默认值
  NO ACTION : 如果主表ID或外键字段的值更新或删除，在子表中不去操作数据

-- 创建表时指定外键
 -- fk_name 表示外键名称，如果没有指定mysql会默认生成
 -- FOREIGN KEY(table_a_column 外键列名) 创建在a表中的列名 表示对该列建立外键约束，通常使用b表的主键名
 -- REFERENCES table_name_b(id / unique_column) 表示该外键列引用的是b表中的列，通常关联b表中的主键或者唯一索引的列名
 -- add fk_name 外键名
 CREATE TABLE table_name_a
 column TYPE, 
 ...
 FOREIGN KEY [fk_name] (table_a_column 外键列名) REFERENCES table_name_b (id / unique_column) 
 [ ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
 [ ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
 DEFAULT charset = 'utf-8'
 
 -- 建表之后添加外键关联
 alter table table_name_a add fk_name foreign key (table_column) references table_name_b (id / unique_column);
 
-- 添加外键并添加级联操作
alter table table_name_a 
 add constraint fk_name
 foreign key(table_column)
 references table_name_b(id / unique_column)
 on delete cascade
 on update cascade;

-- 删除外键
alter table table_name_a drop foreign key table_column;

创建/删除数据库

• 创建数据库基本语法

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];

数据库名 创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
[DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
[DEFAULT] COLLATE：指定字符集的默认校对规则

• 删除数据库基本语法

DROP DATABASE database_name

-- 创建数据库 字符集及规则默认
 CREATE DATABASE test1;
 -- 判断数据库不存在的情况下创建数据库 字符集及规则默认
 CREATE DATABASE IF NOT EXISTS test1;
 -- 删除数据库
 DROP DATABASE test1;
 -- 如果数据库存在则删除数据库
 DROP DATABASE IF EXISTS test1;

创建/删除数据表

• 创建表基本语法

DROP TABLE IF EXISTS table_name;
CREATE TABLE IF NOT EXISTS table_name (
id int NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主表id’,
name string NOT NULL COMMENT ‘名称’
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

• 删除表语法

• 删除表基本语法
  DROP TABLE table_name
• 如果存在则删除表
  DROP TABLE IS EXISTS table_name

-- 创建客户表 
-- NOT NULL 非空约束
-- PRIMARY KEY 主键约束
-- AUTO_INCRMENT 自动增长
-- DEFAULT  默认值
-- UNIQUE 唯一约束
-- ENGINE=InnoDB 指定存储引擎为InnoDB
-- CHARSET=utf8mb4 指定字符集
--
DROP TABLE IS EXISTS customer 
CREATE TABLE IF NOT EXISTS customer 
(
	customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	customer_name VARCHAR(50) NOT NULL,
	points INT NOT NULL DEFAULT 0,
	email VARCHAR(255) NOT NULL UNIQUE 
) ENGINE=InnoDB CHARSET=utf8mb4;

修改数据表

• 基本语法

• 添加列
  ALTER TABLE table_name ADD COLUMN_NAME COLUMN_TYPE [NOT NULL| UNIQUE | DEFAULT VALUE ] [COMMENT comment_value] [AFTER table_column | BEFORE table_column ] ;
• 修改列属性
  MODIFY COLUMN table_column column_type [NOT NULL| UNIQUE | DEFAULT VALUE ] [COMMENT comment_value] [AFTER table_column]
• 删除列
  DROP table_column

• 解释

• table_name ：表名
• table_column ：表中的列
• COLUMN_NAME ：添加的列名
• COLUMN_TYPE ：添加列的属性
• [NOT NULL| UNIQUE | DEFAULT VALUE ] ：设置约束
• COMMENT comment_value：列注释及注释的值
• before table_column ：表示在表中该列之前，
• AFTER table_column ：表示在表中该列之后
• 默认添加列在表最后添加

-- 修改客户表
-- 在first_name之后添加字段last_name 并且设置类型及非空约束
-- 在表最后添加city字段并且设置类型及非空约束
-- 修改first_name字段类型长度
-- 删除points字段
ALTER TABLE customers 
	ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
	ADD city VARCHAR(50) NOT NULL,
	MODIFY COLUMN first_name VARCHAR(55) DEFAULT '',
	DROP points
	;

• 创建及修改表关系

• 创建表时创建关系

CREATE TABLE table_name
(
	column_name column_type(type_value) [NOT NULL | PRIMARY KEY | UNIQUE | DEFAULT ] COMMENT comment_value,
	...
	FOREIGN KEY fk_name (column_name)
)

-- 创建订单表并关联客户表
-- FOREIGN KEY fk_name (customer_id) 表示对列customer_id建立外键约束
DROP TABLE IF EXISTS orders;
CREATE TABLE orders
(
	order_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
	customer_id INT NOT NULL,
	FOREIGN KEY fk_name (customer_id)
	REFERENCES customers (customer_id)
	ON UPDATE CASCADE
	ON DELETE NOT ACTION
);

• 更改主键

• 添加主键
  ALTER TABLE table_name ADD PRIMARY KEY (column …)
• 删除主键
  DROP PRIMARY KEY

• 更改外键

• 添加外键
  ADD FOREIGN KEY fk_name （column_name）REFERENCES table_name ( column_name )
  [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
  [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
• 删除外键
  DROP FOREIGN KEY fk_name

MySQL 账号权限

• 创建用户语法

CREATE USER username [@ { IP address | localhost | 域名 } ] IDENTIFIED BY ‘password’

-- 创建用户test
-- 指定登录IP必须为 www.test.com或其子域网
-- 设置密码为 1234
CREATE USER test@'%.www.test.com' IDENTIFIED BY '1234';

• 查看用户

SELECT * FROM mysql.user;

• user 表说明
• user 表的权限列包括 Select_priv、Insert_ priv 等以 priv 结尾的字段，这些字段值的数据类型为 ENUM，可取的值只有 Y 和 N：Y 表示该用户有对应的权限，N 表示该用户没有对应的权限。从安全角度考虑，这些字段的默认值都为 N

• 列 Host 与 User 说明
• (root,%)，表示可以远程登录，并且是除服务器外的其他任何终端，%表示任意IP地址都可登录。
• (root,localhost)， 表示可以本地登录，即可以在服务器上登陆，localhost则只允许本地登录。
• (root,127.0.0.1 )，表示可以本机登陆，即可以在服务器上登陆
• (root,sv01)，表示主机名为sv1可以登陆，sv01具体指的哪台机器，可以在cat /etc/hostname查看
• (root,::1) ， 表示本机可以登陆， 看密码都是相同嘛，具体::1代表意义，待查

• 其他user列说明

字段名	字段类型	默认值	说明
Select_priv	enum(‘N’,‘Y’)	N	是否可以通过SELECT 命令查询数据
Insert_priv	enum(‘N’,‘Y’)	N	是否可以通过 INSERT 命令插入数据
Update_priv	enum(‘N’,‘Y’)	N	是否可以通过UPDATE 命令修改现有数据
Delete_priv	enum(‘N’,‘Y’)	N	是否可以通过DELETE 命令删除现有数据
Create_priv	enum(‘N’,‘Y’)	N	是否可以创建新的数据库和表
Drop_priv	enum(‘N’,‘Y’)	N	是否可以删除现有数据库和表
Reload_priv	enum(‘N’,‘Y’)	N	是否可以执行刷新和重新加载MySQL所用的各种内部缓存的特定命令，包括日志、权限、主机、查询和表
Shutdown_priv	enum(‘N’,‘Y’)	N	是否可以关闭MySQL服务器。将此权限提供给root账户之外的任何用户时，都应当非常谨慎
Process_priv	enum(‘N’,‘Y’)	N	是否可以通过SHOW PROCESSLIST命令查看其他用户的进程
File_priv	enum(‘N’,‘Y’)	N	是否可以执行SELECT INTO OUTFILE和LOAD DATA INFILE命令
Grant_priv	enum(‘N’,‘Y’)	N	是否可以将自己的权限再授予其他用户
References_priv	enum(‘N’,‘Y’)	N	是否可以创建外键约束
Index_priv	enum(‘N’,‘Y’)	N	是否可以对索引进行增删查
Alter_priv	enum(‘N’,‘Y’)	N	是否可以重命名和修改表结构
Show_db_priv	enum(‘N’,‘Y’)	N	是否可以查看服务器上所有数据库的名字，包括用户拥有足够访问权限的数据库
Super_priv	enum(‘N’,‘Y’)	N	是否可以执行某些强大的管理功能，例如通过KILL命令删除用户进程；使用SET GLOBAL命令修改全局MySQL变量，执行关于复制和日志的各种命令。（超级权限）
Create_tmp_table_priv	enum(‘N’,‘Y’)	N	是否可以创建临时表
Lock_tables_priv	enum(‘N’,‘Y’)	N	是否可以使用LOCK TABLES命令阻止对表的访问/修改
Execute_priv	enum(‘N’,‘Y’)	N	是否可以执行存储过程
Repl_slave_priv	enum(‘N’,‘Y’)	N	是否可以读取用于维护复制数据库环境的二进制日志文件
Repl_client_priv	enum(‘N’,‘Y’)	N	是否可以确定复制从服务器和主服务器的位置
Create_view_priv	enum(‘N’,‘Y’)	N	是否可以创建视图
Show_view_priv	enum(‘N’,‘Y’)	N	是否可以查看视图
Create_routine_priv	enum(‘N’,‘Y’)	N	是否可以更改或放弃存储过程和函数
Alter_routine_priv	enum(‘N’,‘Y’)	N	是否可以修改或删除存储函数及函数
Create_user_priv	enum(‘N’,‘Y’)	N	是否可以执行CREATE USER命令，这个命令用于创建新的MySQL账户
Event_priv	enum(‘N’,‘Y’)	N	是否可以创建、修改和删除事件
Trigger_priv	enum(‘N’,‘Y’)	N	是否可以创建和删除触发器
Create_tablespace_priv	enum(‘N’,‘Y’)	N	是否可以创建表空间

• 如果要修改权限，可以使用 GRANT 语句为用户赋予一些权限，也可以通过 UPDATE 语句更新 user 表的方式来设置权限。
• 安全列

字段名	字段类型	默认值	说明
ssl_type	enum(‘’,‘ANY’,‘X509’,‘SPECIFIED’)		支持ssl标准加密安全字段
ssl_cipher	blob		支持ssl标准加密安全字段
x509_issuer	blob		支持x509标准字段
x509_subject	blob		支持x509标准字段
plugin	char(64)	mysql_native_password	引入plugins以进行用户连接时的密码验证，plugin创建外部/代理用户
password_expired	enum(‘N’,‘Y’)	N	密码是否过期 (N 未过期，y 已过期)
password_last_changed	timestamp		记录密码最近修改的时间
password_lifetime	smallint(5) unsigned		设置密码的有效时间，单位为天数
account_locked	enum(‘N’,‘Y’)	N	用户是否被锁定（Y 锁定，N 未锁定）

• 资源控制列

字段名	字段类型	默认值	说明
max_questions	int(11) unsigned	0	规定每小时允许执行查询的操作次数
max_updates	int(11) unsigned	0	规定每小时允许执行更新的操作次数
max_connections	int(11) unsigned	0	规定每小时允许执行的连接操作次数
max_user_connections	int(11) unsigned	0	规定允许同时建立的连接次数

• 删除用户

• 删除指定用户
  DROP USER username@IP address
  UPDATE mysql.user SET account_locked = ‘Y’ WHERE user.User = username AND user.Host = IP address

• 修改用户密码

• 修改指定用户密码
  SET PASSWORD FOR username = new password
• 修改当前登录用户的密码
  SET PASSWORD = new password

• 设置用户权限

1. GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON test.* TO kang@39.21.115.41

• 上面SQL解释为 为用户 ‘kang’ 授权 test 数据库的查询，添加，删除，修改，存储过程等权限 , 如果用户有指定IP 也要声明

2. GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON . TO kang@39.21.115.41

• 上面SQL解释为 为用户 ‘kang’ 授权 所有数据库的查询，添加，删除，修改，存储过程等权限 , 如果用户有指定IP 也要声明

3. GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON test.orders TO kang@39.21.115.41

• 上面SQL解释为 为用户 ‘kang’ 只授权 test的orders表查询，添加，删除，修改，存储过程等权限，其他表和数据库无权访问

4. GRANT ALL ON test.* TO kang

• 为 ‘kang’ 授权 test数据库的所有的权限

5. GRANT ALL ON . TO kang

• 为 ‘kang’ 授权所有数据库的所有的权限

• 查询用户权限

• 查看当前用户权限
  SHOW GRANTS
• 查看指定用户权限
  SHOW GRANTS FOR username

• 撤销权限

REVOKE CREATE VIEW ON test.* FROM kang;

• 撤销用户kang在数据库test中的创建视图权限