sql基础

USE sql_store;

SELECT *
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name;
-- 注意sql子句的使用顺序必须是：SELECT ----> FROM----> WHERE -----> ORDER BY
-- 如果使用顺序写错了就会报语法错误

1.1 选择子句select

1.1.1 为列起别名AS

SELECT 
	last_name,
    first_name,
    points,
    (points + 10) * 5 AS 'discontNum'
FROM customers;

1.1.2 去重distinct

查询到的city内容可能有重复，使用distinct去重

SELECT DISTINCT city
FROM customers;

1.2 where 子句

SELECT *
FROM customers
WHERE points > 3000;

sql的比较运算符：

>

>=

<

<=

=

!=或者<> ——> 都表示不等于

SELECT *
FROM customers
WHERE birth_date > '1990-01-01'; 
-- 对于日期sql标准需要加引号，标准格式就是1990-01-01

1.3 and，or，not运算符

and的优先级大于or

and前后的条件都满足的数据

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' AND points > 1000;

满足or前后任意一个条件即可

SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR points > 3000;

not运算符

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000);

1.4 in运算符

SELECT *
FROM customers
WHERE city IN ('Arlington', 'Chicago', 'Orlando');

-- 也可以用or达到筛选的效果，不过看着有点呆
SELECT *
FROM customers
WHERE city = 'Arlington' OR city = 'Chicago' OR city = 'Orlando'

SELECT *
FROM customers
WHERE city NOT IN ('Arlington', 'Chicago', 'Orlando');

1.5 between ...and...运算符

SELECT *
FROM customers
WHERE  points >= 1000 AND points <= 3000;

-- 可以写成between and的形式

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;

1.6 like 运算符

SELECT *
FROM customers
WHERE last_name LIKE '%y';

-- % 可以表示任意数量的字符

SELECT *
FROM customers
WHERE last_name LIKE '%b%';

SELECT *
FROM customers
WHERE last_name LIKE 'br%';

-- _代表一个字符

SELECT *
FROM customers
WHERE last_name LIKE 'b____y';


SELECT *
FROM customers
WHERE phone NOT LIKE '%9';

1.7 regexp 运算符

SELECT *
FROM customers
WHERE last_name LIKE '%y';
-- 以y结尾的名字

-- % 可以表示任意数量的字符

SELECT *
FROM customers
WHERE last_name LIKE '%b%';
-- 中间含b的名字

SELECT *
FROM customers
WHERE last_name LIKE 'br%';
-- 以br开头的名字


-- 可以使用正则表达式regexp来实现以上3种筛选
SELECT *
FROM customers
WHERE last_name REGEXP 'y$';
-- 以y结尾的名字

SELECT *
FROM customers
WHERE last_name REGEXP 'b';
-- 中间含b的名字

SELECT *
FROM customers
WHERE last_name REGEXP '^br';
-- 以br开头的名字


-- 查询名字以field开头或者  含mac或者   含rose的顾客信息
--  | 表示多个搜索模式
SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose';



-- 查询名字里含有ge   ie   se 的顾客信息
SELECT *
FROM customers
WHERE last_name REGEXP '[gis]e';
-- WHERE last_name REGEXP 'ge|ie|se';


-- 查询名字里含 el ey  eb的顾客信息
SELECT *
FROM customers
WHERE last_name REGEXP 'e[lyb]';


-- 查询名字第一个字符是a-h，且第二个字符是o的顾客的信息
SELECT *
FROM customers
WHERE last_name REGEXP '^[a-h]o';

1.8 is null 运算符

SELECT *
FROM customers
WHERE phone IS NULL;

SELECT *
FROM customers
WHERE phone IS NOT NULL;

1.9 order by 子句

SELECT *
FROM customers
ORDER BY first_name;
-- 默认按照firstName升序排列
-- 改成order by first_name DESC  就是降序排列了

SELECT first_name, last_name, points, points + 10 AS discountNum
FROM customers
ORDER BY city, first_name DESC;
-- 先按照city升序 排序，city相同的按照first_name降序排序
-- 即使select查询字段里没有city，order by后面也可以使用这个列，这就是mysql的特别指之处

1.10 limit 子句

SELECT *
FROM customers
LIMIT 3;
-- 按照limit指定的n返回前n条数据，如果n比全部查询的结果数量都大，那就返回全部查询结果即可

-- 如果想获取前7~9位的顾客信息，使用偏移量 limit 6, 3
SELECT *
FROM customers
LIMIT 6, 3;
-- 偏移量是6，获取3条数据


-- 获取积分最高的前3位顾客信息
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;

二、链接

2.1 内连接inner join

2.1.1 同数据库内连接查询

USE sql_store;
-- 操作的表都在同一个数据库中

SELECT order_id, first_name, last_name
FROM orders
INNER JOIN customers
	ON orders.customer_id = customers.customer_id;

-- 可以写成inner join  也可以写成 join


-- 如果要查询的字段是在多个表中都存在的，需要在字段前面加上表名称 orders.customer_id, 不然会报语法错误
SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
INNER JOIN customers
	ON orders.customer_id = customers.customer_id;


-- 可以给表起别名，避免多次写不方便
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
INNER JOIN customers c
	ON o.customer_id = c.customer_id;

2.1.2 跨数据库内连接查询

USE sql_store;

-- 查询数据库sql_store里order_items和数据库sql_inventory里products的数据

-- 需要给不在当前数据库的表名前面加上它的数据库名称sql_inventory.products

SELECT order_id, p.name, quantity, oi.unit_price
FROM order_items oi
INNER JOIN sql_inventory.products p
    ON oi.product_id = p.product_id

2.1.3 表的自内连接

USE sql_hr;


-- 数据库表自连接
-- 查找员工表中员工id，first_name，上级的first_name
SELECT e.employee_id, e.first_name, m.first_name AS manager
FROM employees e
INNER JOIN employees m
    ON e.reports_to = m.employee_id;

-- 但是表的自内连接会查不到经理这条数据，因为经理的reports_to是null

2.1.4 多表连接

USE sql_store;

-- 从orders  customers   order_statuses查询订单id、下单日期、客户名、客户电话、客户所在城市、客户详细地址、订单状态
SELECT 
	o.order_id,
    o.order_date,
    c.first_name AS customer,
    c.phone,
    c.city,
    c.address,
    os.name AS status
FROM orders o
INNER JOIN customers c
	ON o.customer_id = c.customer_id
INNER JOIN order_statuses os
	ON o.status = os.order_status_id;

USE sql_invoicing;

-- 查询支付表payments  下单客户信息表clients   支付方式表payment_methods
-- 获取支付id、支付日期、发票id、支付金额、客户名称、支付方式
SELECT
	p.payment_id,
    p.date,
    p.invoice_id,
    p.amount,
    c.name,
    pm.name AS payment_method
FROM payments p
INNER JOIN clients c
	ON p.client_id = c.client_id
INNER JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

2.1.5 复合连接条件

有的表需要多个主键才能定位一条数据，如订单详情表order_items

订单id+产品id才能唯一定位一条记录

订单注释表order_item_notes内有每个订单的客户备注信息

USE sql_store;

-- 要查询订单详情表order_items 和订单备注表order_item_notes 
-- 查询条件匹配时需要使用and连接order_items 的多个主键字段
SELECT oi.order_id, oi.product_id, quantity, unit_price, note
FROM order_items oi
INNER JOIN order_item_notes oin
	ON oi.order_id = oin.order_Id
    AND oi.product_id = oin.product_id

2.1.6 隐式连接语法

上面学习的表连接方式都是显式的，即使用的是inner join.... on的方式

-- 先写一个显式的表连接
USE sql_store;

SELECT *
FROM customers c
INNER JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- 下面使用隐式表连接的方式
SELECT *
FROM customers c, orders o
    WHERE c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 需要注意的是隐式表连接一定不能忘了where子句，不然查询到的数据就不对了
-- 尽量不要使用隐式表连接的方式，仅仅了解即可

2.2 外连接

外连接包括left join 和right join

2.2.1 两个表的外连接

USE sql_store;

-- 外连接包括left join 和right join

-- left join是把位于left join左侧的customers表里全部数据都查询出来，不管是否满足on后面的条件
SELECT 
    c.customer_id, 
    c.first_name, 
    order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 同样的，right join是把位于right join右侧的orders表里全部数据都查询出来，不管是否满足on后面的条件
-- 但是这里建议最好不要使用right join ，特别是多表外连接的时候，太容易出错了

2.2.2 多表外连接

USE sql_store;

-- 查询订单表orders、 客户表customers、订单状态表order_statuses、 承运方表shippers
-- 获取订单时间order_date, 订单id ，下单客户名、承运人、订单状态
-- 并按照订单状态，订单id升序排列,
SELECT 
    o.order_date, 
    o.order_id, 
    c.first_name, 
    s.name AS shipper, 
    os.name AS status
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id
LEFT JOIN shippers s
    ON o.shipper_id = s.shipper_id
JOIN order_statuses os
    ON o.status = os.order_status_id
ORDER BY os.name, o.order_id;
-- order by后面也可以使用上面给os.name起的别名 status，但是这样对别人不太好理解，因为orders里面也有status这个列，所以还是用os.name比较好
-- ORDER BY status, o.order_id;

2.2.3 表的自外连接

USE sql_hr;

SELECT 
    e.employee_id, 
    e.first_name, 
    e.job_title, 
    e.reports_to as manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id;

-- 表的自外连接就可以把经理这条数据查询出来，因为会把left join左侧的表内全部数据输出

2.3 using子句

使用场景：当进行表连接查询时，可以使用using子句替代on 条件判断，代码编写更清新，

使用前提：如果判断条件里的2个表的列名称是完全一样的，就可以使用using

USE sql_store;

SELECT 
	o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
INNER JOIN customers c
	-- ON o.customer_id = c.customer_id
	USING(customer_id)
LEFT JOIN shippers sh
	-- ON o.shipper_id = sh.shipper_id;
		USING(shipper_id);

USE sql_store;

SELECT 
	oi.order_id, 
    oi.product_id, 
    oin.note
FROM order_items oi
INNER JOIN order_item_notes oin
	-- ON oi.order_id = oin.order_id
    -- AND oi.product_id = oin.product_id;
    USING(order_id, product_id);
-- 复合条件查询时，当条件内的列名称一样时使用using子句

注意：当on后面条件里的列名称不一样时就不能使用using子句

USE sql_invoicing;

SELECT 
	p.date,
    c.name AS client,
    p.amount,
    pm.name AS payment_method
FROM payments p
INNER JOIN clients c
	-- ON p.client_id = c.client_id
    USING(client_id)
INNER JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id;

2.4 自然连接natural join

建议不要使用这种方式，了解即可

使用自然连接，搜索引擎自己会根据2个表拥有的相同名称的列进行查询，得到你需要的查询结果，不需要你写查询条件，但同时这种查询也是我们不能控制的

USE sql_store;

SELECT *
FROM orders o
NATURAL JOIN customers c;

2.5 交叉连接cross join

让左侧的表中的每一条数据和右侧表中的每一条数据组合，不写查询条件

2.5.1 交叉连接的显式语法

使用关键字cross join

USE sql_store;
	
SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name;

2.5.2 交叉连接的隐式语法

不使用关键字cross join ，直接在from后面写需要用到的表名称

SELECT 
	c.first_name AS customer,
    p.name AS product
FROM customers c, products p
ORDER BY c.first_name;

得到的结果和上面显式语法一样

2.6 联合查询 union

查询结果的列名称会以第一个sql查询里面的字段为准。

如果union前后2个查询语句获取的字段个数不一致，就会报错——重要！。

union前后2个查询语句内操作的表可以是同一个表也可以不同表

USE sql_store;


-- 给查询到的数据加一个标签
-- 2019年下单的订单标签为active
-- 2019年之前下单的订单标签为archived
SELECT 
	order_id AS order_num,
    order_date AS date,
    'active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
	order_id,
    order_date,
    'archived' AS status
FROM orders
WHERE order_date < '2019-01-01';

SELECT
	customer_id,
    first_name,
    points,
    '青铜' AS type
FROM customers
WHERE points < 2000
UNION 
SELECT
	customer_id,
    first_name,
    points,
    '白银' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
	customer_id,
    first_name,
    points,
    '黄金' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name

三、数据库表数据操作

3.1 插入单行insert into...values...

插入单行有2种方式：

第一种插入就是每一个字段都会传递要插入的值，并且需要和数据库表中字段的先后顺序进行赋值。

USE sql_store;

INSERT INTO customers
VALUES (
	DEFAULT,
    'John',
    'Smith',
    '1990-01-05',
	NULL,
    'address',
	'city',
	'CA',
    DEFAULT);
-- 主键是自增的，插入数据时没办法指定，可以是的default让数据库自己去生成
-- 有默认之的字段也可以使用default让数据库自己填充为默认值，也可以自己写

第二种单行插入是指定要插入的字段值，对于有默认值的或者数据库自动会生成的字段就不用传递了。

只要字段名和赋值对应上即可，不用非要按照数据库表里字段的顺序。


INSERT INTO customers (
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
	'Lisa',
    'Smith',
    '1990-01-05',
    'address',
    'city',
    'CA');

3.2 插入多行

USE sql_store;

-- 在shippers中插入多行数据
INSERT INTO shippers (name)
VALUES ('京东'),
	('菜鸟裹裹'),
    ('中通'),
    ('韵达');

3.3 插入分层行（操作父子表）

对多张表操作，orders表和order_items表，其中orders里面一条数据对应order_items里面1或n条数据，orders和order_items是父子表。

可以看到orders表中有一个主键order_id

order_items表中有2个主键order_id和product_id

要插入一个订单时需要在orders表插入下单信息，在order_items中添加具体购买的商品信息

last_insert_id()获取当前连接下的自增值，也就是上一条sql语句对应的自增值（上一条sql是向orders表插入订单记录，获取最后插入的那条订单记录对应的子增值---也就是订单记录的主键order_id）

USE sql_store;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-08-16', 1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 1.95),
	(LAST_INSERT_ID(), 2, 2, 2.15);

3.4 创建表复制create table... as...

USE sql_invoicing;

-- 创建一个新表test
CREATE TABLE `test`(
	`test_id` tinyint not null auto_increment,
    `name` varchar(50) not null,
    primary key(`test_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into test (name)
values ('test1'),
	('test2'),
    ('test3');

如果你想快速创建一张和orders表一模一样的表，可以使用表复制:

create table orders_archived as select * from orders

但是注意新建的表orders_archived没有设置主键和主键的自增属性

USE sql_store;

CREATE TABLE orders_archived AS
SELECT * FROM orders;
-- 这个sql语句会把orders里面的全部数据复制到orders_archived里面
-- 但是orders_archived里面没有设置主键和主键的自增属性



-- 如果只是想复制orders表里面的部分数据到新表orders_archived中
CREATE TABLE orders_archived AS
SELECT * FROM orders
WHERE order_date < '2019-01-01';


-- 如果想把orders表里面特定数据复制到orders_archived里面
-- 直接使用insert into即可，因为orders_archived和orders表的字段名完全一样，
-- 所以不需要指定插入的字段名称
INSERT INTO orders_archived
SELECT * FROM orders
WHERE order_date = '2019-08-16';

-- 切换到发票数据库
USE sql_invoicing;

-- 新建一个表invoices_archived，复制invoices表的数据，但是不要client_id这一列，
-- 把这一列变成客户名，并且只要已完成支付操作的发票信息
CREATE TABLE invoices_archived AS
SELECT 
	i.invoice_id,
    i.number,
    c.name AS client,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
INNER JOIN clients c
	USING(client_id)
WHERE payment_date IS NOT NULL;

3.5 更新单行update... set ... where...

USE sql_invoicing;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE invoice_id = 1;

UPDATE invoices
SET payment_total = DEFAULT,
	payment_date = NULL
WHERE invoice_id = 1;
-- 使用default前提是知道表设计时这个字段有默认值
-- 使用null前提是知道这个字段可以为nul

3.6 更新多行

MySQL工作台workbench要求一次只能更新一条数据，所以如果你要更新多条数据时会有报错信息。

设置方法：Edit--->Preferences--->SQL Editor，取消页面底部的勾选

USE sql_invoicing;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id = 3;


UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id IN (1, 5);


SELECT * FROM invoices
WHERE client_id IN (1, 3, 5)
ORDER BY client_id;

3.7 在update中用子查询

USE sql_invoicing;

UPDATE invoices
SET 
	payment_total = invoice_total * 0.1,
    payment_date = due_date
WHERE client_id = 
	(SELECT client_id
	FROM clients
	WHERE name = 'Yadel');
--在UPDATE里面的WHERE后面的条件子句中插入子查询
    
SELECT * FROM invoices
WHERE client_id = 3;

USE sql_store;
UPDATE orders
SET comments = '金牌客户'
WHERE customer_id IN (
	SELECT customer_id
	FROM customers
	WHERE points > 3000);
    
SELECT * FROM orders
WHERE customer_id IN (
	SELECT customer_id
	FROM customers
	WHERE points > 3000)

3.8 删除行delete from... where...

USE sql_invoicing;

DELETE FROM invoices
WHERE client_id = (
	SELECT client_id
	FROM clients
    WHERE name = 'QQ');
-- 同样有删除一行和删除多行的操作，子查询得到的时多个结果时上面用in就行了
    
SELECT * 
FROM invoices
ORDER BY client_id;

四、汇总数据

4.1 聚合函数

聚合函数只能对非null的列进行计算。

MAX(column_name)

—— 求最大值/距今最近的日期

MIN(column_name)

—— 求最小值/距今最久的日期

AVG(column_name)

—— 求平均值

SUM(column_name)

—— 求和

COUNT(DISTINCT column_name)

—— 只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(*)可以返回表内数据记录的总数，不会计较某个列值为null。

count(column_name)不加distinct时可以把列值相同的记录也进行统计。

USE sql_invoicing;

SELECT 
	MAX(invoice_total) AS Highest,
    MIN(invoice_total) AS Lowest,
    AVG(invoice_total) AS Average,
    SUM(invoice_total) AS Total,
    COUNT(*) AS Total_records
FROM invoices
WHERE invoice_date > '2019-07-01';


SELECT COUNT(payment_date)
FROM invoices
WHERE invoice_total > 180;

USE sql_invoicing;

SELECT 
	'First half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT 
	'Second half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT 
	'Total' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_mayments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
	BETWEEN '2019-01-01' AND '2019-12-31';

4.2 group by子句

需要注意的是sql子句的顺序：

select ---> from ---> where ---> group by ---> order by ---> limit
顺序错了就会报语法错误

group by后面的列名可以是表中原来存在的，也可以是select 子句中AS 后面的新列名

USE sql_invoicing;

-- 从invoices表中获取2019年下半年每个客户的消费额度，
-- 并按照消费额度降序排列
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC;
-- 需要注意的是sql子句的顺序：select ---> from ---> where ---> group by ---> order by ---> limit
-- 顺序错了就会报语法错误

USE sql_invoicing;

-- 从invoices表中获取2019年下半年每个客户的消费额度，
-- 并按照消费额度降序排列
SELECT 
	client_id AS customer_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY customer_id
ORDER BY total_sales DESC;
-- 需要注意的是sql子句的顺序：select ---> from ---> where ---> group by ---> order by ---> limit
-- 顺序错了就会报语法错误

USE sql_invoicing;

-- 按州和城市获取对应的销售额
SELECT 
	c.state,
    c.city,
    SUM(invoice_total) AS total_sales
FROM invoices i
INNER JOIN clients c
	USING (client_id)
GROUP BY c.state, c.city;

-- 获取每种支付方式每天的支付总额
SELECT 
	date,
    pm.name AS payment_method,
    SUM(amount) AS total_payment
FROM payments p
INNER JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUP BY date, payment_method
ORDER BY date

4.3 having子句

-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
-- where子句中可以使用表中任何已存在的列名进行判断筛选
-- having子句中使用的列名必须在select筛选子句里，如果使用了select子句中没有，having子句就使用某个列名就会报错，报错信息如下：
-- Error Code: 1054. Unknown column 'payment_date' in 'having clause'

USE sql_invoicing;

-- 获取销售额大于500的用户和其销售额
SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE total_sales > 500
-- 使用where会有报错，内容如下：
-- 报错具体信息：Unknown column 'total_sales' in 'where clause'
-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
GROUP BY client_id
HAVING total_sales > 500;

USE sql_invoicing;

-- 获取销售额大于500的用户和其销售额，以及发票数量大于5并获取发票数
SELECT 
	client_id,
    -- 一条数据就是一个发票记录，使用count(*)即可计算发票数量
    COUNT(*) AS number_of_invoice,
    SUM(invoice_total) AS total_sales
FROM invoices
-- WHERE total_sales > 500
-- 使用where会有报错，内容如下：
-- 报错具体信息：Unknown column 'total_sales' in 'where clause'
-- 对于使用计算得到的新列，并且用AS 起了别名的，不能用在where子句中做判断，会报错
-- where 子句是在分组查询前进行的筛选操作，此时表中没有你的新列名，所以会报错
-- 所以此时可以使用having子句，having子句是在分组查询得到结果后再次筛选的操作，此时已经有新列名了
-- where子句中可以使用表中任何已存在的列名进行判断筛选
-- having子句中使用的列名必须在select筛选子句里，如果使用了select子句中没有，
-- having子句就使用某个列名就会报错，报错信息如下：
-- Error Code: 1054. Unknown column 'payment_date' in 'having clause'
GROUP BY client_id
-- having payment_date > '2019-01-01'
HAVING total_sales > 500 AND number_of_invoice > 5;

USE sql_store;

-- 获取state为VA，消费超过100的用户信息
SELECT 
	c.customer_id,
    c.first_name,
    c.last_name,
    SUM(oi.quantity *oi.unit_price) AS total_spend
FROM customers c
INNER JOIN orders o USING (customer_id)
INNER JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY c.customer_id
HAVING total_spend > 100;

4.4 数据汇总with rollup运算符

在group by...后面使用with rollup可以按照分组列名进行数据汇总，还会汇总整个数据

使用with rollup汇总的是select子句中使用聚合函数得到的那个数据

需要注意：当在group by后面使用with rollup运算符时，group by后面不能跟列别名

USE sql_invoicing;

-- 查询每个州 的每个城市的销售额
-- 并汇总所有每个州、每个城市以及销售总额
SELECT 
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
INNER JOIN clients c USING (client_id)
GROUP BY state, city WITH ROLLUP
-- with rollup汇总的是SUM函数计算后得到的数据，即total_sales的值

USE sql_invoicing;

-- 计算不同支付方式的支付金额，并汇总支付总额
SELECT 
	pm.name AS payment_method,
    SUM(p.amount) AS total
FROM payments p
INNER JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
-- GROUP BY payment_method WITH ROLLUP;
-- 需要注意：当在group by后面使用with rollup运算符时，group by后面不能跟列别名
GROUP BY pm.name WITH ROLLUP;

五、复杂查询

5.1 子查询

where子句中添加子查询

子查询返回的是一个单一的值：平均工资、指定商品的价格

USE sql_store;

-- 查询价格大于生菜价格的商品，生菜的product_id = 3
SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price
    FROM products
    WHERE product_id = 3
);

USE sql_hr;

-- 查询工资大于平均工资的员工信息
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
)

5.2 in运算符+子查询

子查询返回的是某个列字段的值集合

USE sql_store;

-- 查询没有被订购过的商品信息
-- order_items表中是用户订单内具体商品信息
-- products表里面是全部商品的信息集合

SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
	FROM order_items
)

USE sql_invoicing;

-- 查询没有发票的客户
-- clients表存储全部客户信息
-- invoices表内存储发票信息
SELECT *
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices
)

5.3 子查询 vs 连接

USE sql_store;

-- 查询购买了生菜的用户，生菜的商品id=3
-- 获取customer_id,first_name,last_name
-- 方法一：使用表连接
SELECT 
	DISTINCT c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
INNER JOIN orders o
	USING (customer_id)
INNER JOIN order_items oi
	USING (order_id)
WHERE product_id = 3;


-- 方法二：使用子查询
SELECT 
	c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
WHERE customer_id IN (
	SELECT customer_id
    FROM orders
    WHERE order_id IN (
		SELECT order_id 
        FROM order_items
        WHERE product_id = 3
    )
)

5.4 all关键字

> (select MAX(列1) ......) ——》 > all (select 列1 .....)

< (select MIN(列1) .......) ——》 < all (select 列1 ......)

USE sql_invoicing;

-- 获取发票信息，发票的面值大于用户client_id = 3最大值发票
-- 使用聚合函数 max()
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
);


-- 使用all关键字
SELECT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);

5.5 any关键字

IN 可以替换为 = ANY

USE sql_invoicing;

-- 获取至少有2张发票的用户信息
SELECT client_id, COUNT(*) AS invoice_number
FROM invoices
GROUP BY client_id
HAVING invoice_number >= 2;

-- 使用in关键字
SELECT *
FROM clients
WHERE client_id IN (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
);

-- 使用any关键字
SELECT * 
FROM clients
WHERE client_id = ANY (
	SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);

5.6 相关子查询

上面讲过的子查询都是非相关子查询，也就是执行完子查询后把结果给主查询，之后主查询根据自己的条件进行再次查询，子查询完全不依赖主查询。

相关子查询是指在子查询的筛选条件中会有和主查询表内字段的判断。

USE sql_hr;

-- 查询工资高于所属部门平均工资的员工信息
SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
);


USE sql_invoicing;

-- 获取面值大于客户自己发票平均面值的发票信息
SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
);

5.7 exists运算符

exists关键字后面的要加一个相关子查询。

USE sql_invoicing;

-- 获取有发票的客户信息
-- 使用in关键字
-- in关键字后面的子查询会返回一个查询结果集给到主查询，
-- 如果有上万上亿的数据那使用in就会效率很低，比较耗机器性能
SELECT *
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
    FROM invoices
);


-- 使用exists关键字
-- exists 后面的相关子查询如果返回True，
-- 主查询就会从当前进行判断的这条客户数据获取需要的字段值
-- exists 后面的子查询并没有返回数据量庞大的结果集，这样就效率高，也不耗机器性能
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
	WHERE client_id = c.client_id
);

USE sql_store;

-- 查询没有被订购过的商品信息
-- 方法一：使用not in
SELECT *
FROM products
WHERE product_id NOT IN (
	SELECT DISTINCT product_id
    FROM order_items
);


-- 方法二： 使用not exists
SELECT *
FROM products p
WHERE NOT EXISTS (
	SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
);

5.8 select子句中的子查询

USE sql_invoicing;

SELECT 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total) 
		FROM invoices) 
	AS invoice_average,
    -- 所以不能写写成  (invoice_total - invoice_average) as difference，会报语法错误
    -- 可以替换为下方的写法
    -- invoice_total - (SELECT AVG(invoice_total) 
		-- FROM invoices)
	-- AS difference
	-- 可以按照上面的方式写，但是这样需要把获取平均值的sql语句重写一遍，看着很麻烦
	-- 需要注意的是：在select子句中不允许直接使用列别名
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

USE sql_invoicing;

-- 查询每个客户的消费总金额total_sales，客户消费平均额average，以及二者的差值
SELECT 
	c.client_id,
    c.name,
    (SELECT SUM(invoice_total) 
		FROM invoices
        WHERE client_id = c.client_id)
	AS total_sales,
    -- 获取总的消费金额SELECT SUM(invoice_total)
    -- 获取发票表中的客户id SELECT client_id FROM invoices
    -- 因为一个客户可能存在多张发票，所以需要对获取的客户id去重，之后再计算行数count()，即得消费过的客户数量
    -- 获取消费过的客户数量 SELECT COUNT(DISTINCT client_id) FROM invoices
    -- 用总的消费金额除以 消费过的客户数量 得到客户的消费平均额度
    (SELECT SUM(invoice_total)/(SELECT COUNT(DISTINCT client_id) FROM invoices)
		FROM invoices)
	AS average,
    (SELECT total_sales) - (SELECT average) AS difference
FROM clients c

5.9 from子句中的子查询

USE sql_invoicing;

SELECT *
FROM (
	-- 查询每个客户的消费总金额total_sales，客户消费平均额average，以及二者的差值
	SELECT 
		c.client_id,
		c.name,
		(SELECT SUM(invoice_total) 
			FROM invoices
			WHERE client_id = c.client_id)
		AS total_sales,
		-- 获取总的消费金额SELECT SUM(invoice_total)
		-- 获取发票表中的客户id SELECT client_id FROM invoices
		-- 因为一个客户可能存在多张发票，所以需要对获取的客户id去重，之后再计算行数count()，即得消费过的客户数量
		-- 获取消费过的客户数量 SELECT COUNT(DISTINCT client_id) FROM invoices
		-- 用总的消费金额除以 消费过的客户数量 得到客户的消费平均额度
		(SELECT SUM(invoice_total)/(SELECT COUNT(DISTINCT client_id) FROM invoices)
			FROM invoices)
		AS average,
		(SELECT total_sales) - (SELECT average) AS difference
	FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL;

但是这种写法看着非常复杂，所以我们可以把之前查询到的数据存储为sales_summary视图，这个知识点下面讲。

六、常用的内置函数

6.1 数值函数

6.1.1 round()

获取四舍五入后的数值

-- 四舍五入后取整数
-- 返回的是6
SELECT ROUND(5.73);

-- 四舍五入后保留1位小数
-- 返回的是5.8
SELECT ROUND(5.76, 1);

-- 四舍五入后保留2位小数
-- 返回5.73
SELECT ROUND(5.734, 2);

6.1.2 truncate()

数据截断，保留指定位数的小数

-- 需要注意的是truncate()内必须指明保留几位小数
-- 不进行四舍五入，直接进行数据截断，保留3位小数
SELECT TRUNCATE(5.7365, 3);
-- 不进行四舍五入，直接进行数据截断，保留1位小数
SELECT TRUNCATE(5.7365, 1);

6.1.3 ceiling()

返回大于或等于这个数字的最小整数

SELECT CEILING(5.2);

SELECT CEILING(5.7);

-- 以上都返回6

SELECT CEILING(5);
-- 返回5

6.1.4 floor()

返回小于或等于这个数字的最大整数

SELECT FLOOR(5.2);

SELECT FLOOR(5.7);

SELECT FLOOR(5);

-- 以上都返回5

6.1.5 abs()

返回绝对值

-- 返回9
SELECT ABS(-9);

-- 返回9
SELECT ABS(9);

-- 返回9.2
SELECT ABS(9.2);

6.1.6 rand()

生成0-1区间的随机浮点数

SELECT RAND();

6.2 字符串函数

6.2.1 length(str)

获取字符串长度

SELECT LENGTH('SKY');
-- 返回3

6.2.2 upper(str)

把字符串转换为大写

SELECT UPPER('sky');
-- 返回SKY

6.2.3 lower(str)

把字符串转换为小写

SELECT LOWER('SKY');
-- 返回sky

6.2.4 ltrim(str)

left trim

删除字符串左侧的多余的空格

SELECT LTRIM('     SKY')
-- 返回SKY

6.2.5 rtrim(str)

right trim

删除字符串右侧的多余的空格

SELECT RTRIM('sky     ');
-- 返回sky

6.2.6 trim(str)

删除字符串左右两侧的多余的空格

SELECT TRIM('     sky     ');
-- 返回sky

6.2.7 left(str, n)

返回字符串左侧n个字符组成的字符串

SELECT LEFT('kindergarten', 4);
-- 返回kind

6.2.8 right(str, n)

返回字符串从右侧数n个字符组成的字符串

SELECT RIGHT('Kindergarten', 6);
-- 返回garten

6.2.9 substring(str, m, n)

返回字符串从左第m个字符开始，长度为n的字符串

n可以省略，省略的时候就表示一直到str的最后

SELECT SUBSTRING('Kindergarten', 3, 5);
-- 返回nderg

6.2.10 locate(str1, str)

返回str1在str中从左至右第一次出现的位置

locate()查找是不区分大小写的

SELECT LOCATE('n', 'Kindergarten');
-- 返回3

SELECT LOCATE('der', 'asndersdkjhngmfderloipfder');
-- 返回4

SELECT LOCATE('XCD', 'xfgtrhuj');
-- 如果没有查找到则返回0

6.2.11 replace(str, old, new)

用new替换str中的old，并返回新的字符串

SELECT REPLACE('Kindergarten', 'ten', 'den');
-- 返回Kindergarden

6.2.12 合并多个字符串concat(str1, str2,...)

把str1和str2合并，并返回新的字符串

SELECT CONCAT('first', 'last');
-- 返回firstlast

USE sql_store;
SELECT CONCAT(first_name, ' ', last_name)
FROM customers;

6.3 MySQL中的日期函数

6.3.1 now()

获取当前的日期和时间，返回的是int

SELECT NOW();
-- 获取当前的日期和时间
-- 2023-05-23 07:13:03

6.3.2 curdate()

current date

获取当前的日期，返回的是int

SELECT CURDATE();
-- 获取当前的日期
-- 2023-05-23

6.3.3 curtime()

current time

获取当前的时间，返回的是int

SELECT CURTIME();
-- 获取当前的时间
-- 07:16:19

6.3.4 year()

获取指定时间的年份，返回的是int

SELECT YEAR(NOW());
-- 获取当前时间的年份
-- 2023

6.3.5 month()

获取指定时间的月份，返回的是int

SELECT MONTH(NOW());
-- 获取当前时间的月份
-- 5

6.3.6 day()

获取指定时间的日期，返回的是int

SELECT DAY(NOW());
-- 获取当前时间的日期
-- 23

6.3.7 hour()

获取指定时间的小时，返回的是int

SELECT HOUR(NOW());
-- 获取当前时间的小时
-- 7

6.3.8 minute()

获取指定时间的分钟，返回的是int

SELECT MINUTE(NOW());
-- 获取当前时间的分钟数
-- 27

6.3.9 second()

获取指定时间的秒数，返回的是int

SELECT SECOND(NOW());
-- 获取当前时间的秒数
-- 59

6.3.10 dayname()

获取指定时间是在星期几，返回的是字符串

SELECT DAYNAME(NOW());
-- 获取当前时间是星期几

6.3.11 monthname()

获取指定时间的月份，返回的是字符串

SELECT MONTHNAME(NOW());
-- 获取当前时间的月份

6.3.12 SQL通用函数extract()

如果还想把获取的数据写入其他数据库，最好使用extract()

SELECT EXTRACT(YEAR FROM NOW());


SELECT EXTRACT(MONTH FROM NOW());


SELECT EXTRACT(DAY FROM NOW());


SELECT EXTRACT(HOUR FROM NOW());


SELECT EXTRACT(MINUTE FROM NOW());


SELECT EXTRACT(SECOND FROM NOW());

USE sql_store;

-- 查询本年度的订单信息
SELECT *
FROM orders
WHERE year(order_date) = year(now());

6.4 格式化日期和时间

date_format(str, fomatStr)

需要2个参数第一个是日期字符串，第二个是格式化参照字符串

-- 日期格式化

SELECT DATE_FORMAT(NOW(), '%M %d %Y');
-- May 23 2023
-- 一般使用第一种格式化样式


SELECT DATE_FORMAT(NOW(), '%M %D %Y');
-- May 23rd 2023


SELECT DATE_FORMAT(NOW(), '%m %d %y');
-- 05 23 23


SELECT DATE_FORMAT('2019-04-08', '%M %d %Y');
-- April 08 2019

-- 时间格式化

SELECT TIME_FORMAT(NOW(), '%H:%i %p');
-- 08:29 AM

SELECT TIME_FORMAT(NOW(), '%h:%i %p');
-- 08:30 AM

-- 以上写法没有区别

6.5 计算日期和时间

6.5.1 增加/减少日期时间date_add()和date_sub()

date_add(日期字符串，interval n year/month/day/hour/minute/second)：

——在当前时间基础上加上n年/月/天/小时/分钟/秒，n可以是正数也可以是负数

date_sub(日期字符串，interval 数字 year/day)：

——在当前时间基础上减去n年/月/天/小时/分钟/秒，n可以是正数也可以是负数


SELECT NOW();

-- 在当前时间基础上加1天，时间不变
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);

-- 在当前时间基础上加1年，其他不变
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);

-- 在当前时间基础上减1天，其他不变
-- 方式1：使用date_add()函数，需要传递负数
SELECT DATE_ADD(NOW(), INTERVAL -1 DAY);

-- 方式2：使用date_sub()函数，传递正数即可
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

6.5.2 计算2个时间的间隔datediff()和time_to_sec()

-- 使用datediff() 计算2个时间间隔的天数，用第1个时间减去第2个时间,返回的是间隔的天数，不会精确计算时间

- 使用time_to_sec()计算指定时间距离同日期0点的秒数，所以得到的都是正数

-- 使用datediff() 计算2个时间间隔的天数，用第1个时间减去第2个时间
-- 返回的是间隔的天数，不会精确计算时间
SELECT DATEDIFF('2019-07-25 17:00', '2019-10-12 09:30');
-- 返回-79

SELECT DATEDIFF('2019-10-12', '2019-07-25');
-- 返回79

SELECT DATEDIFF('2019-07-25 09:30', '2019-07-25 12:00');
-- 返回0

-- 使用time_to_sec()计算指定时间距离0点的秒数
SELECT TIME_TO_SEC('01:00');
-- 返回3600

SELECT TIME_TO_SEC('09:30') - TIME_TO_SEC('12:00');
-- 返回-9000

6.6 ifnull和coalesce函数

ifnull(参数1，参数2):

如果参数1为null，则返回参数2

USE sql_store;

SELECT 
	order_id,
    IFNULL(shipper_id, '未分配') AS shipper
FROM orders;

-- coalesce(参数1, 参数2, 参数3)
-- 查询参数1的值，如果参数1的值是null，就返回2的值，
-- 如果参数2的值也是null，就返回参数3

-- coalesce(shipper_id, comments, '未分配')
-- 查询shipper_id的值，如果shipper_id的值是null，就查询comments的值，
-- 如果comments的值也是null，就使用“未分配”替代
SELECT
	order_id,
    COALESCE(shipper_id, comments, "未分配")
FROM orders;

USE sql_store;


-- 最好给coalesce()得到的结果起个别名，这样查询结果意思容易理解
SELECT
	order_id,
    COALESCE(shipper_id, comments, "未分配") AS shipper
FROM orders;

USE sql_store;

SELECT 
	CONCAT(first_name, " ", last_name) AS customer,
    COALESCE(phone, 'Unknown')
FROM customers;

6.7 if函数

if(条件表达式, value1, value2) AS new_name

如果条件表达式为True，则输出值value1，否则输出值value2

为了让输出内容更好理解，最好给起个别名new_name

USE sql_store;

-- 注意if里面的条件表达式中使用的是一个等号
SELECT 
	order_id,
    order_date,
    IF(
		YEAR(order_date) = YEAR(NOW()),
        '活跃',
        '存档'
    ) AS status
FROM orders;

SELECT 
	product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1, 'many times', 'once') AS frequence
-- 注意如果写成if((select orders) > 1, 'many times', 'once') AS frequence
-- 会报错，select子句的子查询中允许使用(select 别名)的方式，但是if中可能不允许
FROM products
INNER JOIN order_items
	USING (product_id)
GROUP BY product_id;

6.8 case运算符

如果有多个条件判断，if就不能满足了可以使用:

case

when 条件判断 then '输出'

when 条件判断 then '输出'

when 条件判断 then '输出'

else '输出'

end as 别名

USE sql_store;

SELECT 
	order_id,
    CASE
		WHEN YEAR(order_date) = YEAR(NOW()) THEN '活跃'
        WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN '去年活跃'
        WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN '不活跃'
        ELSE '未来订单'
	END AS 'category'
FROM orders

USE sql_store;

SELECT 
	CONCAT(first_name, " ", last_name) AS customer,
    points,
    CASE
		WHEN points > 3000 THEN '黄金'
        WHEN points >= 2000 THEN '白银'
        ELSE '青铜'
	END AS category
FROM customers
ORDER BY points DESC;

七、视图

7.1 创建视图

USE sql_invoicing;

-- 创建client_balance视图
-- 计算每位客户的待付款金额
-- 发票表invoices 中的订单发票金额  减去 客户已支付金额即可
CREATE VIEW client_balance AS
SELECT 
	i.client_id,
    c.name,
    (SUM(i.invoice_total) - SUM(i.payment_total)) AS balance
FROM invoices i
INNER JOIN clients c USING (client_id)
GROUP BY client_id
ORDER BY client_id

7.2 更改/删除视图

7.2.1 删除视图

如果你需要修改视图内的字段逻辑，可以先删除视图之后修改sql语句重新创建视图即可

USE sql_invoicing;

DROP VIEW client_balance;

7.2.2 更改视图

在不删除视图的前提下，在你创建视图时搭配使用replace关键字，可以在需要修改视图内容时，直接修改sql语句之后执行sql即可

-- 修改视图
CREATE OR REPLACE VIEW client_balance AS
SELECT 
	i.client_id,
    c.name,
    (SUM(i.invoice_total) - SUM(i.payment_total)) AS balance
FROM invoices i
INNER JOIN clients c USING (client_id)
GROUP BY client_id
ORDER BY client_id

7.3 可更新视图

什么样的视图是可更新的视图？？
-- 创建视图的sql语句中没有使用以下函数和关键字，那么则个视图就是可以更新的
-- distinct
-- 聚合函数（sum()、min()、 max()等）
-- having / group by
-- union

-- 可以对这个视图直接进行insert、update、delete操作

USE sql_invoicing;

-- 什么样的视图是可更新的视图？？
-- 创建视图的sql语句中没有 使用以下函数和关键字，那么则个视图就是可以更新的
-- distinct
-- 聚合函数（sum()、min()、 max()等）
-- having / group by
-- union

-- 可以对这个视图直接进行insert、update、delete操作


CREATE OR REPLACE VIEW invoice_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;

-- 上面创建的这个视图sql里面没有用到
-- distinct
-- 聚合函数（sum()、min()、 max()等）
-- having / group by
-- union

-- 所以这个视图是可以直接进行insert update和delete操作的 
DELETE FROM invoice_with_balance
WHERE invoice_id = 1;

UPDATE invoice_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id =2;

-- 视图插入数据时需要满足原始表中字段属性的要求，如
-- invoice_with_balance是根据invoices表数据创建的视图，如果要对invoice_with_balance视图进行插入操作，需要按照对invoices表插入时的值要求

7.4 with check option子句

需要注意的是：

修改（insert、update、delete）视图内数据实际就是修改原始表内的数据，
因为视图只是数据库在磁盘上的一个缩小范围的逻辑影像,任何修改都会修改到原始表

-- 创建视图的sql语句最后使用with check option子句
-- with check option子句的作用：创建视图时获取到的初始数据，
-- 后续通过视图对这些数据操作后，这些数据也必须还能通过这个视图查看到，
-- 如果某些操作会导致这些数据消失，就会报错

USE sql_invoicing;

-- 创建视图
CREATE OR REPLACE VIEW invoice_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;


-- 需要注意的是：
-- 修改（insert、update、delete）视图内数据实际就是修改原始表内的数据，
-- 因为视图只是数据库在磁盘上的一个缩小范围的逻辑影像,任何修改都会修改到原始表


-- 如果之后对视图内的某条数据进行修改，并且修改后不再满足视图创建时数据筛选逻辑 
-- 比如 修改invoice_id=2的invoice_total = payment_total，那就不满足where子句中的条件
UPDATE invoice_with_balance
SET invoice_total = payment_total
WHERE invoice_id = 2;

-- 再次查看视图会发现不再显示invoice_id=2的数据
-- 但是查看invoices表里面还是有这条数据的，并且已经被更新了 
-- 也就是invoice_with_balance视图中的invoice_id=2的这条数据没有了 
SELECT * FROM invoice_with_balance;


-- 所以在update时除了需要关注原始表中字段值属性，还要注意创建视图时对数据筛选的逻辑 
-- 创建视图的sql语句最后使用with check option子句 
-- with check option子句的作用就是，创建视图时获取到的初始数据，
-- 后续通过视图对这些数据操作后，这些数据也必须还能通过这个视图查看到，
-- 如果某些操作会导致这些数据消失，就会报错
CREATE OR REPLACE VIEW invoice_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 CHECK OPTION;

-- 使用了with check option后可以再次执行上面的delete和update语句
-- 之后查看视图数据
UPDATE invoice_with_balance
SET invoice_total = payment_total
WHERE invoice_id = 2;

DELETE FROM invoice_with_balance
WHERE invoice_id = 3;


SELECT * FROM invoice_with_balance;

-- 我目前遇到的问题是，虽然使用了with check option子句
-- 但是进行可使数据消失的update语句或者直接删除delete语句时，没有报错
-- 也就是with check option子句失效了，没有查到原因，暂时记录在这里

视图的主要作用是为了简化我们的查询，所以尽量不要使用增、删、改语句对视图内数据进行操作，因为会影响原始表

7.5 视图的其他优点

7.5.1 简化数据库查询

7.5.2 减少原始表修改带来的影响

在创建视图时给每一个原始表字段都起一个别名，如果原始表改列名或者删除某一列，我们直接修改创建视图的sql语句就行了

如果原始表把某一列移动到其他数据表存储，我们只需要改下创建视图sql，使用表连接即可。

我们之前根据视图写好的sql都不用修改。

7.5.3 限制对原始表某些数据的访问

在创建视图时可以在where子句中加筛选条件，得到的就是允许其他人操作的安全数据

之后其他人根据视图对数据进行操作，不会影响到安全级别较高的数据。

八、存储过程&函数

我们写的sql语句不能和业务代码在一起，因为业务代码的修改涉及编译和发布，如果修改sql很频繁那每次上线都会很麻烦。

那么我们需要把sql代码存储在它对于的数据库中，具体在哪里呢？？————存储过程&函数中

8.1 什么是存储过程

存储过程是一个包含一堆sql代码的数据库对象。

在业务代码中可以调用这些存储过程来获取或保存数据。

1、存储过程用来存储和管理sql代码

2、数据库管理系统DBMS会对存储过程里的代码做了优化，所以在存储过程中sql代码运行的很快

3、和视图类似，存储过程能加强数据安全性

如：取消所有人直接操作数据库表的权限，并且大多数的操作（增删改）由存储过程完成。

然后指定某些人可以操作指定的存储过程，可以防止数据被删

8.2 创建一个存储过程

USE sql_invoicing;
-- 在创建存储过程之前要先选择是哪个数据库
-- 存储过程创建代码中不能有数据库选择的命令
DELIMITER $$
-- 我需要的是把整个存储过程都打包给MySQL，而不是里面的一条一条sql语句
-- 为此我么需要把mysql默认的分隔符换成其他的，这样执行的时候遇到分号就不会认为结束了
-- 执行到我们指定的分隔符才会认为结束了
-- 一般都会设置成2个$$，当然你也可以设置成别的
-- 设置的关键字是delimiter
-- 创建一个没有参数的存储过程
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
    -- MySQL要求每条sql语句末尾都需要加上分号分隔符，即使只有一条sql语句也要加上
END$$
-- 在存储过程的最后加上你设置的结束符号，告诉MySQL结束了

-- 一个存储过程执行结束了需要把MySQL默认的结束符号改回来
DELIMITER ;

-- 执行完存储过程，可以在目录栏Stored Procedures里面看到刚才新建的存储过程
-- 点击目录里存储过程名称右侧的执行按钮就可以执行里面的sql语句
-- 也可以新建一个查询窗口，使用命令call 存储过程名称()
-- CALL get_clients();


USE sql_invoicing;
DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
	SELECT
		invoice_id,
        client_id,
        balance
	FROM invoice_with_balance
    WHERE balance > 0;
END$$
DELIMITER ;

8.3 使用MySQL工作台创建存储过程

自己写创建存储过程的代码，还需要每次修改结束符，最后还不能忘记改回来，比较麻烦，而且容易忘记最后改回来，在MySQL工作台可以比较简单的新建存储过程，方法如下：

在MySQL workbench里面的目录面板内点击Stored Procedures ——>右击选择Create Stored Procedures，之后给要新建的存储过程起个名字，下面写业务需要的sql就好了，上截图

写完了点击右下角的apply

会弹出即将执行的代码内容让你确认，截图在下方：

可以看到这里面已经写好了，结束符号的替换、数据库选择、并判断了之前是否已经存在了同名的存储过程。

8.4 删除存储过程

drop procedure 存储过程名称

-- 但是如果写的存储过程不存在，就会报语法错误

-- 建议加上if exists

drop procedure if exists 存储过程名称

建议一个sql文件中只写一个存储过程的删除和创建

USE sql_invoicing;
DROP PROCEDURE IF EXISTS get_payments;

DELIMITER $$
CREATE PROCEDURE `get_payments`()
BEGIN
	SELECT 
		invoice_id,
		client_id,
		payment_total
	FROM invoices;
END$$

DELIMITER ;

8.5 参数

DROP PROCEDURE IF EXISTS `get_clients_by_state`;
USE sql_invoicing;
-- 按州获取用户信息
DELIMITER $$
CREATE PROCEDURE `get_clients_by_state`
(
	state CHAR(2)
    -- state的数据类型和clients表里面的state保持一致就行，
    -- 也可以给这个参数起一个别的名字，方便区分，如p_state
    -- 含多个参数时用逗号分隔
)
BEGIN
	SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

-- 需要注意的是：
-- 因为存储过程get_clients_by_state里面的参数没有默认值，所以调用存储过程时记得传参，不然会报错
-- call sql_invoicing.get_clients_by_state('CA');

DROP PROCEDURE IF EXISTS `get_invoices_by_client`;
USE sql_invoicing;
-- 按用户获取发票信息
DELIMITER $$
CREATE PROCEDURE `get_invoices_by_client`
(
	client_id INT
)
BEGIN
	SELECT * FROM invoices i
    WHERE i.client_id = client_id;
END$$

DELIMITER ;
-- 调用存储过程的代码是：call sql_invoicing.get_invoices_by_client(5);

8.6 带默认值的参数

DROP PROCEDURE IF EXISTS `get_clients_by_state`;
USE sql_invoicing;

DELIMITER $$
CREATE PROCEDURE `get_clients_by_state`
(
	state CHAR(2)
)
BEGIN
	IF state IS NULL THEN
		SET state = 'CA';
        -- 注意不要少分号
    END IF;
    -- 如果调用存储过程时没有给state赋值，那么这里的if判断会给它一个默认值
	-- 注意：if和end if要配对出现
	SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;



-- 注意调用存户过程时，需要传入NULL，不然会报错
-- call sql_invoicing.get_clients_by_state(NULL);

DROP PROCEDURE IF EXISTS `get_clients_by_state`;
USE sql_invoicing;
-- 如果传入的state参数null就返回全部的client信息
-- 如果传入的state不为null，就按照州返返回指定用户 
DELIMITER $$
CREATE PROCEDURE `get_clients_by_state`
(
	state CHAR(2)
)
BEGIN
	IF state IS NULL THEN
		SELECT * FROM clients;
	ELSE
		SELECT * FROM clients c
		WHERE C.state = state;
	END IF;
END$$

DELIMITER ;

-- 上面的if...then...else....end if 可以替换为更简洁的写法

-- 上面的if...then...else....end if 可以替换为更简洁的写法
DROP PROCEDURE IF EXISTS `get_clients_by_state`;
USE sql_invoicing;
-- 如果传入的state参数null就返回全部的client信息
-- 如果传入的state不为null，就按照州返返回指定用户 
DELIMITER $$
CREATE PROCEDURE `get_clients_by_state`
(
	state CHAR(2)
)
BEGIN
		SELECT * FROM clients c
		WHERE C.state = IFNULL(state, c.state);
        -- 如果state传入的是null，则返回c.state
        -- 那此时等号左右都是c.state所以就会返回全部的client数据
END$$

DELIMITER ;

DROP PROCEDURE IF EXISTS `get_payments_by_client_and_payment_method`;
USE sql_invoicing;

DELIMITER $$
-- 创建存储过程，获取付款信息
-- 有2个参数：client_id   int类型和payment_method_id   tinyint类型，这2个参数都不是必选参数
-- 有参数时就按照参数进行数据选择，没有时就返回全部的付款信息 
CREATE PROCEDURE `get_payments_by_client_and_payment_method`
(
	client_id INT,
    payment_method_id TINYINT
)
BEGIN
	SELECT * FROM payments p
    WHERE p.client_id = IFNULL(client_id, p.client_id) 
		AND p.payment_method = IFNULL(payment_method_id, p.payment_method);
END$$

DELIMITER ;

-- 调用存储过程
-- call sql_invoicing.get_payments_by_client_and_payment_method(5, 2);
-- call sql_invoicing.get_payments_by_client_and_payment_method(null, 1);
-- call sql_invoicing.get_payments_by_client_and_payment_method(5, null);

8.7 参数验证

在向数据库内更新数据时，需要对即将插入的数据验证下，避免出错

可以在官网查看不同错误对应的编码

IBM Documentation

更新数据库数据前，先验证传入的数据是否符合要求

但是也要注意不要写太多验证，这样会使得存储过程变得很复杂，如如果数据库建表的时候已经设置了字段属性非null，那你就不用在存储过程中加上这种验证了

USE `sql_invoicing`;
DROP procedure IF EXISTS `make_payment`;

DELIMITER $$
USE `sql_invoicing`$$
CREATE PROCEDURE `make_payment` 
(
	invoice_id INT,
    payment_amount DECIMAL(9, 2),
    -- decimal是小数类型，第一个参数代表数据整个数据的总长度，
    -- 第二个参数代表小数点后的位数
    payment_date DATE
)
BEGIN
    -- 添加if判断验证传入的参数是否符合要求
	IF payment_amount <= 0 THEN
		SIGNAL SQLSTATE '22003'
			SET MESSAGE_TEXT = 'Invalid payment_amount';
	END IF;
    
	UPDATE invoices i
    SET 
		i.payment_total = payment_amount,
        i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END$$

DELIMITER ;

8.8 输出参数

创建存储过程时，使用OUT关键字指定输出参数的名称和类型

在存储过程主体代码中使用INTO关键字把获取到的数据传递给对应的输出参数

USE sql_invoicing;
DROP PROCEDURE IF EXISTS `get_unpaid_invoices_for_client`;
-- 查询发票表中指定用户的支付金额为0的发票数量
-- 定义输出参数    使用out关键字
-- 并把符合条件的发票总数和金额传递给输出参数    使用into关键字
-- 在调用这个存储过程时，只需传入client_id，输出参数不用传值
DELIMITER $$
USE sql_invoicing$$
CREATE PROCEDURE `get_unpaid_invoices_for_client`
(
	client_id INT,
    -- out关键字定义输出参数的名称和类型
    OUT invoices_count INT,
    OUT invoices_total DECIMAL(9, 2)
)
BEGIN
	SELECT COUNT(*), SUM(invoice_total)
    -- 使用INTO关键字 把获取到的数据按照顺序传递给输出参数
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id
		AND payment_total = 0;
END$$

DELIMITER ;

调用存储过程，传入client_id=2，之后会自动生成代码如下

set @invoices_count = 0;
set @invoices_total = 0;
-- 使用set @定义2个变量并给变量赋值
call sql_invoicing.get_unpaid_invoices_for_client(2, @invoices_count, @invoices_total);
-- 调用存储过程并传递需要的3个参数
select @invoices_count, @invoices_total;
-- 调用存储过程后，使用select语句获取输出变量的值

注意：使用输出参数时，在读取数据时还需要进行赋值，要注意赋值顺序，不能出错，比较麻烦，

不建议使用这种方法。

8.9 变量

8.9.1 用户变量/会话变量

使用set @定义变量，在用户会话过程中这些变量会被保存，用户断开MySQL时这些变量会被删除

所以这些变量又称为用户变量/会话变量

set @invoices_count = 0

8.9.2 本地变量

在存储过程或者函数内定义，本地变量不会在整个客户端会话过程中保存，存储过程执行完后这些变量就会被清空。

在存储过程主体代码内定义使用declare关键字定义本地变量的名称和类型，

并在读取数据时使用into关键字给变量赋值

USE sql_invoicing;
DROP PROCEDURE IF EXISTS `get_risk_factor`;

DELIMITER $$
USE sql_invoicing$$
CREATE PROCEDURE `get_risk_factor`()
-- 在存储过程主体代码内定义 本地变量，并在读取数据时进行赋值
BEGIN
	DECLARE risk_factor DECIMAL(9, 2);
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    -- 使用declare关键字定义本地变量的名称和类型
    
	SELECT COUNT(*), SUM(invoice_total)
    -- 查询时把对应数据赋值给对应的本地变量
    INTO invoices_count, invoices_total
    FROM invoices;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    
    SELECT risk_factor;
END$$

DELIMITER ;

8.10 函数

函数只能返回单一的值，不能像存储过程一样返回多行多列的结果集。

创建函数的语法和创建存储过程很像

USE sql_invoicing;
DROP FUNCTION IF EXISTS `get_risk_factor_for_client`;

DELIMITER $$
USE sql_invoicing$$

CREATE FUNCTION `get_risk_factor_for_client`
(
	client_id INT
)
RETURNS INT
-- 明确了函数返回值的数据类型int，这是和存储过程不同的地方
-- 在明确了函数返回值类型后，要设置函数的属性，每个MySQL函数至少有一个函数属性
-- 可选的属性有： 
-- 1）deterministic（确定性）意思是如果给这个函数同样的一组值，
-- 它每次都会返回一样的值，适用于函数没有传参，且返回的数据有一个固定的计算公式，
-- 比如最大值、最小值，计算总纳税额度等，只要处理的数据集不变，那返回的数据就是固定的
-- 我们本次计算的是用户的风险值，不同用户通过公式计算得到的风险值大概率不同，
-- 所以这个函数属性不需要
-- 2）reads sql data 意思是函数中会有选择语句来读取数据库内数据
-- 3）modifies sql data 意思是函数内会有插入、更新、删除等语句
READS SQL DATA


BEGIN
	DECLARE risk_factor DECIMAL(9, 2);
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    -- 使用declare关键字定义本地变量的名称和类型
    
	SELECT COUNT(*), SUM(invoice_total)
    -- 查询时把对应数据赋值给对应的本地变量
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    
	-- 函数一定要有一个return语句，返回单一值
    -- ifnull(a, b) 如果a是null，就返回b
    RETURN IFNULL(risk_factor, 0);
END$$

DELIMITER ;

自定义的函数调用和MySQL内置函数的调用方式一样，需要注意的是调用的是不是同一个数据库内的函数，如果不是需要在函数名前面加上所在的数据库。

select sql_invoicing.get_risk_factor_for_client(2);

USE sql_invoicing;

SELECT 
	client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients;

九、触发器和事件

9.1 触发器

-- 发票表invoices中一条记录可能对应付款表payments中一条或多条记录 
-- 即一张发票肯有多个付款记录
-- invoices表里面的payment_total应该是payments表中相同invoice_id的付款总和 
-- 所以在向payments插入数据的同时，需要同步更新invoices表中对应invoice_id里的payment_total

-- 此时会用到触发器
USE sql_invoicing;
DROP TRIGGER IF EXISTS `payments_after_insert`;
DELIMITER $$

-- 创建触发器 
-- 触发器命名格式，表名_after/before_操作类型
-- 在payments表执行完插入语句后再触发 触发器
CREATE TRIGGER payments_after_insert
    -- 声明什么时候引爆这个触发器
	AFTER INSERT ON payments
    -- 除了after关键字，还有before关键字
    -- 操作类型除了insert还有update  和 delete

    -- 声明这个触发器的影响范围
    FOR EACH ROW
    -- for each row意思是触发器会作用于每一个受影响的行，
    -- 如果我们在payments表插入5条数据，则触发器会对这5条数据对应的invoice_id在invoices表中的payment_total字段的值进行操作
BEGIN
	-- 在触发器主体里面可以直接写sql语句，也可以调用存储过程
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    -- 因为本触发器是payments表插入新数据后触发的，所以使用NEW关键字获取的是payments表插入的最新数据
    -- new后面加.字段名来获取你想要的值
    
    -- new关键字用于获取能够引爆触发器的表操作后的最新数据 
    -- 还有old关键字，在更新和删除操作时很有用，它返回的是更新/删除前行数据的值 
    WHERE invoice_id = NEW.invoice_id;
END$$

DELIMITER ;

新建插入操作引爆触发器

USE sql_invoicing;
INSERT INTO payments
VALUES(DEFAULT, 2, 1, '2023-03-15', 10, 1);

执行完后查看invoices发票表invoice_id = 1的payment_total已经更新了

练习题：

USE sql_invoicing;
DROP TRIGGER IF EXISTS `payments_after_delete`;

DELIMITER $$

CREATE TRIGGER `payments_after_delete`
	-- 声明什么时候引爆这个触发器
    AFTER DELETE ON payments
    -- 声明这个触发器的影响范围
    FOR EACH ROW
BEGIN
	UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;

END$$

DELIMITER ;

新建查询编写引爆这个触发器的sql：

USE sql_invoicing;

DELETE 
FROM payments
WHERE payment_id = 9;

删除前payments表内数据：

删除前invoices表内数据：

执行删除sql后，触发器自动引爆更新了invoices表内对数据：

9.2 查看触发器

USE sql_invoicing;

-- 获取当前数据库全部的触发器
-- SHOW TRIGGERS;


-- 获取当前数据库中指定表引爆的触发器
SHOW TRIGGERS LIKE 'payments%';

9.3 删除触发器

USE sql_invoicing;
DROP TRIGGER IF EXISTS `payments_after_delete`;
-- 需要加上if exists，不然万一要删除的触发器不存在就会报错了

9.4 使用触发器进行审计

新建2个触发器

USE sql_invoicing;
DROP TRIGGER IF EXISTS `payments_after_insert`;

DELIMITER $$
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$$

DELIMITER ;

USE sql_invoicing;
DROP TRIGGER IF EXISTS `payments_after_delete`;

DELIMITER $$
CREATE TRIGGER `payments_after_delete`
	-- 声明怎么引爆触发器
    AFTER 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$$

DELIMITER ;

新建查询引爆上面2个触发器

USE sql_invoicing;

-- 引爆payments_after_insert触发器
INSERT INTO payments
VALUES(DEFAULT, 2, 1, '2023-03-15', 10, 1);

-- 引爆payments_after_delete触发器
DELETE 
FROM payments
WHERE payment_id = LAST_INSERT_ID();
-- last_insert_id()获取当前连接下的自增值，也就是上一条sql语句对应的自增值，一般数据库表的自增值都是表的主键

分开执行引爆sql语句，可以多执行几遍，可以在payments_audit看到操作记录

9.5 事件

事件是根据计划执行的任务或sql代码，可以定时执行

如：定时删除已过期的数据、把数据从一张表赋值到存档表、汇总数据生成报告

先看下事件调度器是否打开了：

SHOW VARIABLES LIKE 'event%';

-- 如果看到事件调度器是关闭的，可以使用set 来开启
SET GLOBAL event_scheduler = ON

-- 如果你不需要调度事件，既可以关闭这个全局设置，节省系统资源 
SET GLOBAL event_scheduler = OFF;

USE sql_invoicing;

-- 要求审计表中只保留一年内的操作记录，事件开始执行时间是2019-01-01
-- 每年都要的1月1号都要删除去年的操作记录
-- 到2029-01-01就结束
DROP EVENT IF EXISTS `yearly_delete_stale_audit_rows`;
DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
-- 事件命名规则：
-- 开头是事件执行的频次 yearly  monthly   daily   hourly  minutely  once这种
-- 后面跟此事件执行的操作描述
ON SCHEDULE
-- 设置事件执行的频次
	-- 如果是只执行一次
    -- AT '2023-05-10'
    -- 如果是每隔几分钟执行一次 
    -- EVERY 10 MINUTE
    -- 如果是每隔几小时执行一次  
    -- EVERY 1 HOUR
    -- 如果是每隔几天执行一次
    -- EVERY 2 DAY
    -- 如果是每隔几个月执行一次 
    -- EVERY 2 MONTH
    -- 如果是每隔几年执行一次
    -- EVERY 1 YEAR 
    -- 如果不是只执行一次，可以使用starts设置一个起始时间
	-- STARTS '2023-01-01' 
	-- 如果有明确的结束事时间，可以使用ends设置一个结束时间
	-- ENDS '2050-01-01'
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
-- 注意这里begin前面有一个do
	DELETE FROM payments_audit
	-- WHERE action_date < DATE_ADD(NOW(), INTERVAL -1 YEAR);
    -- date_add(日期字符串，interval n year/day)：在当前时间基础上加上n天或者n年，n可以是正数也可以是负数
    -- 也可以写成下面这种，是一样的效果
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END$$

DELIMITER ;

9.6 查看、删除和更改事件

USE sql_invoicing;

-- 查看当前数据库存在的事件
SHOW EVENTS;
SHOW EVENTS LIKE 'yearly%';

-- 删除已存在的事件，最好配合在新建事件使用
DROP EVENT IF EXISTS `yearly_delete_stale_audit_rows`;

-- 修改已存在的事件内容
DELIMITER $$
ALTER EVENT `yearly_delete_stale_audit_rows`
ON SCHEDULE
-- 设置事件执行的频次
	EVERY 1 YEAR STARTS '2020-01-01' ENDS '2050-01-01'
DO BEGIN
-- 注意这里begin前面有一个do
	DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END$$
DELIMITER ;

-- 暂时关闭事件、启动事件
ALTER EVENT `yearly_delete_stale_audit_rows` DISABLE;
ALTER EVENT `yearly_delete_stale_audit_rows` ENABLE;

十、事务

10.1 什么是事务

事务是一组sql操作，基于业务逻辑这些sql操作要么全部成功，只要有一个失败，就会撤销之前的sql操作，避免数据库内数据不一致。

事务的属性：

1）原子性

每个事务都是一个工作单元，不管包含多少sql语句，要么全部语句执行成功且事务被提交，要么事务被撤回所有更改被撤销。

2）一致性

通过事务数据库会保持数据的一致性，不会出现订单表orders内有订单，但是订单商品表order_items内没有对应商品的情况，数据库内表始终保持数据和理性。

3）隔离性

事务之间是相互隔离的，互不影响。

如果多个事务都要更新同一条数据，受影响的行会被锁定，一次只有一个事务可以对这行数据进行修改。

4）持久性

一旦事务提交成功，数据的更改是永久性的，即使断电、服务器崩溃也不会影响已完成修改的数据。

10.2 创建事务

USE sql_store;

START TRANSACTION;
-- start transaction 事务开始的标志
INSERT INTO orders (customer_id, order_date, status)
VALUES(1, '2023-05-30', 2);

INSERT INTO order_items
VALUES(LAST_INSERT_ID(), 7, 5, 8.85);
-- last_insert_id()获取当前连接下的自增值，也就是上一条sql语句对应的自增值，一般数据库表的自增值都是表的主键

COMMIT;
-- commit 事务结束的标识
-- 还有一个rollback表示退回事务并撤销所有更改 
-- ROLLBACK;

我们写的每一条sql语句，MySQL都会自动把它放进事务中执行，如果sql语句没有返回错误，就提交这个事务，由自动提交系统管理。

SHOW VARIABLES LIKE 'autocommit';

自动提交系统默认是开启的。

10.3 并发和锁定

多个事务修改同一行数据时，这行数据会被第一个先执行的事务锁定，直到这个事务执行完才会释放这些行，其他的事务才能继续对这行数据进行更改。

mysql默认锁定机制：事务开始执行时会给要修改的数据行上锁，防止其他事务修改这些行，直到事务执行完毕（被提交或者被退回）才会释放这些数据行。

多数情况MySQL默认锁定机制可以避免并发导致的数据问题，但是有的业务情况MySQL的默认锁定机制不能满足，需要修改MySQL的锁定机制。

10.4 并发问题

10.4.1 lost updates丢失更新

例子：2个事务都更新相同数据，如客户表clients，一个事务要更新改客户的积分，另一个事务要更新该客户所属的州，如果没有锁就会导致2个事务修改额内容相互覆盖，这个问题MySQL默认的锁定机制可以解决。

10.4.2 dirty reads脏读

脏读的意思是当一个事务b读取了尚未被提交的数据（事务a），如果事务b针对读取的脏数据进行了其他操作，但是事务a在事务b完成之前退回了。

例子：事务a修改了customers表内某个用户的积分，如增加了1000，事务a还没有提交

此时事务b读取了这个用户的积分信息，并进行了积分抵扣现金的逻辑计算，100积分抵扣1￥，事务b还没有完成时事务a回撤了

为了解决这个问题我们需要为事务建立隔离级别，这样事务修改的数据在事务提交之前不会立马被其他事务读取。

为事务设置read committed（只读已提交）的隔离级别可以避免事务读取脏数据，这个隔离级别对于通过读取数据做商业决策十分重要，我们必须保证做决策的数据时真实存在的。

10.4.3 Non-repeating Reads 不可重复读

这种情况发生在一个事务在读取数据时，前后读取的数据值不一致

例子：事务a修改了某用户的积分，在原有基础上增加1000，但还未进行提交

事务b设置了read committed隔离级别，读取了该用户的积分points=5000

在事务b未完成时，事务a提交了，此时数据库表中该用户的points=6000，在事务b执行过程中又再次读取了改用户的积分数据，拿到的是points=6000，要以哪次读取的数据为准？？

为了解决这个问题，我们需要给事务b增加另一个隔离级别——》repeatable read可重复读，这个隔离级别把事务b和其他事务隔离开，其他事务对数据的修改对事务b是不可见的，在这个隔离级别上重复读取数据得到的结果和第一次是一样的。

10.4.4 Phantom reads 幻读

例子：事务a读取积分>3000的用户信息，给他们发一个折扣码可以参与活动

事务b更新了某个用户的积分数据，更新后该用户的积分>3000，

事务a 事务b分别提交，那事务b修改的那个用户就不能参加活动，会导致用户投诉，怎么解决？？

这种问题就看你当前处理的这个商业问题有多重要了，有2种方法解决：

1、再次执行事务a来获取满足条件的用户信息

2、如果你就希望一次执行就获取全部满足条件的用户，就只能保证在事务a执行期间不能有其他修改用户信息的事务运行了

MySQL有一个事务隔离级别serializable（序列化）可以保证当有别的事务b在更新数据时，事务a可以知晓变动，如果事务b修改了可能影响查询结果的数据，事务a会等事务b执行完提交后继续进行查询。

序列化serializable是我们可以应用于事务的最高隔离级别，它为我们的操作提供了最大的确定性，但是这个代价很大，如果影响事务a的其他事务很多，那么我们就需要等它们全部执行提交后才能继续。所以除非你能确认处理的问题必须保证数据的正确性，否则就不要使用这个隔离级别，性能很差。

10.5 事务隔离级别

在MySQL中默认的事务隔离级别是 repeatable read 可重复读，这个可以解决大部分的并发问题

read uncommitted 读未提交和read committed 读已提交这2个事务隔离级别可以在不需要精确的一致性的批量报告或者数据不怎么更新的情况下使用，这样可以保证数据库服务器的性能。

-- 查看当前数据库默认的事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

USE sql_store;

-- 查看当前数据库默认的事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
-- 给下一个事务设置隔离级别 为 serializable可序列化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 加上session就可以设置在当前会话/连接中使用的事务隔离级别
-- 也就是只要不断开数据库连接，当前设置的事务隔离级别就可以应用到本次会话执行的全部事务中
-- 连接数据库之后设置事务隔离级别，执行事务，完成后再断开数据库连接，不会影响其他事务
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 使用global关键字可以为所有会话中的所有新事务设置全局隔离级别 
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

10.6 read uncommitted读未提交隔离级别

workbench新建2个会话窗口，保证初始数据一致，不同的会话窗口（即不同的数据库连接中），模拟的是并发操作。

会话窗口1新建查询，代码如下：

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 下面的sql语句虽然没有start transaction  和 commit
-- 但是MySQL自动提交系统也会把sql放到事务中执行
SELECT points
FROM customers
WHERE client_id = 1;

会话窗口2新建查询，代码如下：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
COMMIT;
-- ROLLBACK;

步骤：

1、单步执行（Ctrl+回车）会话窗口1中前2条sql语句

2、单步执行会话窗口2前3条sql代码，不执行commit提交事务

3、执行会话窗口1的select语句，可以看到查询到的points是窗口2更新过的数据

因为会话窗口1事务设置的事务隔离级别是read uncommitted读未提交数据，所以只要别的会话窗口修改了会话窗口1中读取的数据，会话窗口1就会读取那个最新的数据，但是如果比的会话窗口最后米有提交事务而是rollback撤回了，那就会导致会话窗口1读到的数据是数据库表中不存在的数据。

read uncommitted适用范围：对读取的数据准确性要求不高，或者要读取的数据更新频率极低，这样数据库服务器的性能会比较好，速度会很快。

10.7 read committed读已提交隔离级别

新建2个会话窗口，保证初始数据一致，不同的会话窗口（即不同的数据库连接中），模拟的是并发操作。

会话窗口1，代码：

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 下面的sql语句虽然没有start transaction  和 commit
-- 但是MySQL自动提交系统也会把sql放到事务中执行
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

会话窗口2，代码：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 2273
WHERE customer_id = 1;
COMMIT;

步骤：

1、会话窗口1的事务单步执行到start transaction

2、进入会话窗口2执行完update语句

3、执行会话窗口1的第1个select语句，读取的是旧数据

4、进入会话窗口2执行commit完成事务提交

5、执行会话窗口1的第2个select语句，读取的是新数据，因为会话窗口1中事务设置的是读已提交数据，所以只有当会话窗口2的事务提交后，会话窗口1的select语句才会读取最新数据。

read committed 会存在不可重复读的问题。

不可重复读：事务a设置了read committed隔离级别，事务a读取用户积分，事务b更新用户积分数据，事务b在事务a执行完select语句后开始运行，并在事务a提交前先提交，那此时数据库表中用户的积分已经更新了，如果事务a中下一条sql再次读取了同一个用户的积分数据，那第二次读取的数据和第一次读取的数据是不一致的。

10.8 repeatable read可重复读隔离级别

新建2个会话窗口，保证初始数据一致，不同的会话窗口（即不同的数据库连接中），模拟的是并发操作。

会话窗口1，代码：

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 下面的sql语句虽然没有start transaction  和 commit
-- 但是MySQL自动提交系统也会把sql放到事务中执行
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

会话窗口2，代码如下：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 22
WHERE customer_id = 1;
COMMIT;

步骤：

1、会话窗口1单步执行到第一个select语句，不继续

2、会话窗口2单步执行到update语句，不执行commit

3、执行会话窗口1的第二个select语句，发现得到points还是旧数据

4、进入会话窗口2把事务提交了

5、回到会话窗口1再次执行第二个select语句，发现还是读取的旧数据，并没有因为会话窗口2的事务提交而读取新数据。

所以repeatable read可以把会话窗口1中的事务和其他会话窗口的事务隔离开，即使在其他会话窗口事务中更新了数据，也不会影响当前会话窗口事务读取到的数据，就避免了脏读和不可重复读的问题，只要在当前会话中没有修改数据，那就读取最原始的数据。

但是读到的数据不一定是最新的数据，如果会话窗口2的事务提交了，那会话窗口1的事务读取的就是旧数据，也就是会存在幻读的情况。

10.9 序列化隔离级别

新建2个会话窗口，保证初始数据一致，不同的会话窗口（即不同的数据库连接中），模拟的是并发操作。

会话窗口1，代码：

USE sql_store;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- 下面的sql语句虽然没有start transaction  和 commit
-- 但是MySQL自动提交系统也会把sql放到事务中执行
SELECT * FROM customers WHERE state = 'VA';
COMMIT;

会话窗口2，代码：

USE sql_store;
START TRANSACTION;
UPDATE customers 
SET state = 'VA'
WHERE customer_id = 3;
COMMIT;

步骤：

1、会话窗口1 单步执行到start transaction，不执行下面的select语句

2、进入会话窗口2，单步执行到update语句，不执行commit提交事务

3、回到会话窗口1执行select语句，会发现查询一直转圈，因为它在等待会话窗口2中的事务提交

4、进入会话窗口2执行commit进行事务的提交

5、回到会话窗口1发现select语句执行完毕了，并且查询到了最新的数据

序列化隔离级别类似于套娃，从最外一层进入，再从最里层退出，最后才能得到结果，这种事务隔离级别是最安全的，保证了数据的完全正确并且没有并发问题，但是当数据量庞大且嵌套的事务很多时，这种隔离级别很耗数据库服务器的性能。

10.10 死锁

死锁：指的是相互影响的2个事务均握住了对方的锁，都在等对方执行完毕后自己才能继续执行

新建2个会话窗口，保证初始数据一致，不同的会话窗口（即不同的数据库连接中），模拟的是并发操作。

会话窗口1，代码如下：

USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id =1;
UPDATE customers SET state = 'VA' WHERE customer_id = 4;
COMMIT;

会话窗口2，代码如下：

USE sql_store;
START TRANSACTION;
UPDATE customers SET state = 'VA' WHERE customer_id = 4;
UPDATE orders SET status = 1 WHERE order_id =1;
COMMIT;

步骤：

1、单步执行会话窗口1代码的前3条sql语句，此时事务1锁住了orders表中order_id= 1这条数据

2、进入会话窗口2，单步执行代码的前3条sql语句，此时事务2锁住了customers表中customer_id = 4这条数据，打住不再继续

3、继续执行会话窗口2中的第4条sql语句，发现窗口一直处于等待，因为要处理的是orders表中order_id= 1这条数据，但是这条数据被会话窗口1中的事务1锁定了，需要等事务1中全部sql执行完毕并提交事务后才能释放这条数据。

4、进入会话窗口1，因为只有事务执行完毕后才能释放事务中涉及的数据，所以需要继续执行事务1中的第4条sql语句，但是第4条sql处理的是customers表中customer_id = 4这条数据，但是这条数据被会话窗口2中的事务2锁定了，需要等事务2中的sql执行完毕且事务提交后才能释放这条数据。

所以会话窗口1中的事务1和会话黄口2中的事务2互相拿到了对方需要的数据锁，陷入死锁状态，死锁抛出异常后，会断开数据库连接，这样锁定的数据就会解锁。

死锁的情况不可避免，但可以尽量减少，方法有2种：

1）可以在经常发生死锁的2个事务中查看释放存在2条完全一样但是顺序颠倒的update语句，这个可能是导致死锁的原因。所以在编写事务代码时时，如果存在2条或多条完全一样的update语句，需要让它们的顺序一致。

2）尽量减少事务中处理数据的复杂程度，增删改sq语句少一些也可以避免死锁发生概率。

十一、数据类型

11.1 字符串类型

char(n) 用来存储固定长度的字符串，如：州state 手机号固定电话号码身份证号

varchar(n) 用来存储可变长度的字符串，如：用户名密码人名城市名地址

对于短长度可变字符串可以使用varchar(50)，如：用户名密码人名

对于中长度可变的字符串可以使用varchar(255)，如：地址

这样设置可以简化数据库维护

varchar(n)最多存储65535个字符约64KB，对于有的数据如json对象、scv字符串、短中长度的书籍可能存储不下，可以使用mediumtext类型，可以存储16MB

longtext类型，最多可以存储4GB，可以用于存储日志文件

tinytext 微文本类型，最多存储255个字符

text 文本类型，最多存储65000字符约64KB，和varchar一样，但是对于种情况最好使用varchar类型，因为varchar可以被编入索引。

英文字符占1个字节，中文和日文中3个字节，如char(10)MySQL会留出30个字节

11.2 整数类型

11.2.1 分类

tinyint 占1字节，存储数据范围[-128, 127]

unsigned tinyint 存储数据范围[0, 255]，可以存储如：年龄

smallint 占2字节存储数据范围[-32K, 32K]

mediumint 占3字节存储数据范围[-8M, 8M]

int 占4字节存储数据范围[-2B, 2B]

bigint 占8字节存储数据范围[-9Z, 9Z]

在选择类型时尽量选择能够满足你存储需要的且占用内存更小的整数类型，这样的好处是一不浪费数据库服务器空间，二是能让数据处理的更快。

11.2.2 用0占位

int(4) 如果存储1，则MySQL展示这个数据时会显示成0001，但这并不是存储

11.3 定点数类型和浮点数类型

11.3.1 定点数类型decimal(p, s)

p是整个数据的最大长度，s是小数点后面的数据长度，可以存储价格

decimal(9, 2)表示你最大可以存储长9位的数据，并且这个数据的小数点后有2位如1234567.89

decimal(9, 2) 还有几个同义词，有的人会使用，如

dec(9, 2)

numeric(9, 2)

fixed(9, 2)

11.3.2 双精度和浮点类型

float 占4字节

double 占8字节

double 和 float 常用于科学计算，如果你需要计算非常大或者非常小的数据，可以使用这2种类型。

double和float存户的是近似值不是精确值

11.4 布尔类型

Bool 和Boolean 有2个值 TRUE FALSE

它的TRUE 可以替换为1，FALSE可以替换为0 来表示同样的意思

UPDATE posts

SET is_published = TRUE

-- 也可以写成SET is_published= 1

11.5 枚举和集合类型

11.5.1 枚举类型enum

enum在定义数据库表内字段时可以选择这个类型

enum('small', 'medium', 'large')

输入完enum里面的枚举值后按tab键完成提交即可

后面插入数据时必须按照这里面的值选择其中一个

不推荐使用枚举值

11.5.2 集合set

使用这个类型会引入很多坑

不推荐使用

11.6 日期和时间类型

date 用来存储没有时间成分的日期

time 用来存储一个时间值

datetime 日期时间类型 8字节

timestamp 时间戳 4字节只能存储到2038年以前的日期

year 用来存储4位数的年份

11.7 blob类型

blob 二进制长对象用来存储大型的二进制数据

tinyblob 最大存储255字节的二进制数据

blob 最大存储65KB的二进制数据

mediumblob 最大存储16MB的二进制数据

longblob 最大存储4GB的二进制数据

但是注意最好不要把文件存储在数据库中，因为数据库是为了处理结构化关系型数据设计的，不是为了二进制数据。

把文件存储在数据库服务器有以下缺点：

1、增加数据库容量

2、弱化数据备份功能

3、还会有性能问题，如把图像从数据库提取出来比从文件系统中读取要慢很多

4、为了在数据库读取/存储图像还需要额外写代

11.8 json类型

在workbench的sql_store数据库，表products中添加一列

USE sql_store;

-- 有以下2种方式给数据类型是json的列进行赋值
-- 1、使用json标准格式赋值json对象
UPDATE products
SET properties = '{
	"dimensions": [1, 2, 3],
    "weight": 10,
    "manufacturer": {"name": "sony"}
}'
WHERE product_id = 1;

-- 2、使用MySQL的内部函数json_object()，返回的是一个json对象
UPDATE products
SET properties = JSON_OBJECT(
	'weight', 11,
    'dimensions', JSON_ARRAY(2, 3, 4),
    'manufacturer', JSON_OBJECT('name', 'sony')
)
WHERE product_id = 2;

获取json对象内某个属性值：

USE sql_store;

-- 怎么从数据库表中读取json对象内部的某个属性的值 ??
-- 使用json_extract(json对象名称，'$.json对象的属性名')
-- $意思是当前文档，json对象类似一个文件
SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
FROM products
WHERE product_id = 2;

USE sql_store;

-- 怎么从数据库表中读取json对象内部的某个属性的值 ??
-- 如果不使用json_extract()来获取json对象内部的属性值 
-- 还可以使用 json对象名 -> '$.属性名'
-- $意思是当前文档，json对象类似一个文件
SELECT 
	product_id, 
    -- 获取json对象内某个属性值
    properties -> '$.weight' AS weight, 
    -- json对象内属性值是数组，获取数组内某个下标对应的值
    properties -> '$.dimensions[0]',
    -- json对象内部嵌套json对象，获取内部嵌套对象内的属性值
    properties -> '$.manufacturer.name',
    -- 需要注意的是：如果属性值是字符串，使用->获取道德是带引号的 
	-- 可以使用->>就可以获取到不带引号的字符串内容
    properties ->> '$.manufacturer.name'
FROM products
WHERE product_id = 1;

json对象的属性也可以放进where子句作为条件判断：

USE sql_store;

--  json对象的属性properties ->> '$.manufacturer.name' 也可以放在where子句中作为条件判断的key
SELECT 
	product_id, 
	name, 
    properties -> '$.weight',
    properties ->> '$.manufacturer.name'
FROM products
WHERE properties ->> '$.manufacturer.name' = 'sony';

更新json对象内部属性值json_set()：

USE sql_store;

-- 怎么更新数据库表中的json对象？？
-- 只想重置json对象内部某个属性的值
-- json_set()可以用来更新json对象现有的属性值，也可以新增键值对，返回的也是json对象
UPDATE products
SET properties = JSON_SET(
	-- 先声明本次改动涉及的json对象的名称
	properties,
    -- 要更新的属性名和最新值
    '$.weight', 20,
    -- 也可以新增属性键值对
    '$.age', 101
)
WHERE product_id = 1;

删除json对象内部属性 json_remove()：

USE sql_store;

-- json_remove()用来删除一个或多个属性键值对，返回一个新的json对象 
UPDATE products
SET properties = JSON_REMOVE(
	-- 先声明本次改动涉及的json对象的名称
	properties,
    -- 要删除的属性名
    '$.age'
)
WHERE product_id = 1;

十二、数据库设计

12.1 数据建模

1、理解和分析业务需求

和业务参与方、领域专家甚至终端用户沟通，

查看现有的表单、文档、应用程序、电子表格、数据库

2、概念建模

识别业务中的实体、事务或概念，以及它们之间的关系，便于业务方各角色理解和对齐

3、逻辑模型

根据概念模型设计一个数据模型或数据结构来存储数据

逻辑模型是独立于数据技术的抽象数据模型，它只显示你需要的表和列。

4、实体模型

根据逻辑模型为特定数据库管理系统构建一个实体模型，实体模型是围绕特定数据库技术的逻辑模型的实现，在这个模型中你需要具备由数据库技术支持的确切的数据类型、列默认值、表主键，以及其他对象如视图、存储过程、触发器等等。

12.2 概念模型

以一个线上教学系统为例，如何设计概念模型？？

一般需要找出涉及的人、东西、地点等

可以使用的设计工具有：Microsoft Visio 在线工具draw.io 和LucidCharts

Flowchart Maker & Online Diagram Software

12.3 逻辑模型

即使是同一个课程每次购买的时间和价格可能不同，所以新增一个购买实体

12.4 实体模型

在mysql工作台新建模型：

workbench——》file——》new model

注意的是 decimal不要为了预防未来有较大的值就设置的很大，这样会浪费空间

12.5 主键和外键

为表设置主键来唯一标识一条数据，并且主键最好设置成自增的，这样MySQL会自动给你添加，方便插入数据，而且主键一般都是表的第一列。

下一步要给enroll表设置主键，有2种选择：

1）直接给enroll表新增一个注册id，让它唯一标识一条记录，但是有一个问题是肯会存在同一个学生注册了同一门课程，而且有可能另外2个字段date和price都是一样的，例子：2个人同时给一个人买课并都成功了，这样会导致数据库存入了重复数据

2）使用学生id student_id和课程id course_id 作为复合主键，来唯一确定一条购买记录，避免插入重复数据，如果未来enroll表又和其他表有了链接关系，那么那个表就需要包含enroll的这复合主键，但是我们不必为了肯发生的情况担忧，目前看来使用复合主键四最优解。

注意这时不需要给主键添加自增属性，注册表的记录是由students和courses2个表内的记录共同确认的

12.6 外键约束

外键student_id 关联了enroll和students两个表，如果学生表students内某个学生的student_id被人为修改或者删除了，student_id对应不上了，我们需要提前明确这种情况enroll表与之对应的数据记录怎么处理，可以看到右侧有四种设置：restrict、cascade（级联）、set null、no action，分别介绍：

restrict：限制，意思是拒绝修改（更新和删除）。

cascade：级联，意思是如果主键更改了，MySQL会自动更新子表中记录对应的外键值；如果是主键扽条记录在主键表删除了，那子表也会删除对应的记录。

set null：设置为空值null，把对应子表中外键值设置为null，这样就会使这条记录失去了父表，成为了孤儿记录，最好别用这个。

no action：无操作，和restrict是一个意思，就是不做修改（更新和删除）。

因为enroll中涉及了财务记录，所以当主键表有删除操作时，我们能时不想望同步删除enroll表内对应记录的所以选择no action或者restrict也行；对应主键表更新student_id为了数据一致性并能很好的对应主键表，子表enroll选择cascade同步更新。同样这种设置也是我们一般遵循的，除非业务人为同步删除没关系，我们就可以设置为级联cascade，最好设计时和业务方确认。

12.7 标准化

数据库表设计需要遵守一定的规则，有7条规则，也叫七范式，前三范式可以覆盖99%的应用场景。

12.8 第一范式

第一范式：

要求一行中的每个列都应该有单一值，且不能出现重复列。

上面设计的courses表中的tags列就不满足单一值，一门课可以有多个标签那tags里面就不是单一值了，需要优化，新增tags表就可以满足每个单元格都是单一值。

现在的需要链接courses和tags两个表。

12.9 链接表

需要注意的是在关系型数据库中不存在多对多的表链接，需要我们新建一个链接表（名字按照关联的2个表起名就好），让这个关联表和和左右2个表都存在1对多的关系就可以实现2个的多对多的链接关系。

上面的students和courses就是通过enroll表实现的多对多的关系。

同样的courses和tags表也是多对多的关系，也需要新建一个关联表来实现它们之间的多对多关系。

course_tags表中一条记录可以被course_id和tag_id唯一标识，所以把它们设置成复合主键。

12.10 第二范式

第二范式：

首先必须满足第一范式

第二每张表都应该有一个单一目的，换句话就是一张表只能用于描述一种实体类型，表中的每一列都应该是描述那个实体的。

例子：订单表

上面订单表满足了第一范式，但是里面的customer_name不应该是描述orders的属性，会存在2个问题：

1、如果一个用户有多个订单，那这一列其实是浪费了数据库空间

2、如果这个用户改名字了，那修改也是很大工作量

修改为：

重复存储customer_id比重复存储customer_name要节省空间很多，其次如果要修改用户名，只需要在customers表中改一行数据就好了

现在看下我们上面设计的数据库表，哪里不满足第二范式：

可以看到courses表中的instructor（讲师）不满足第二范式，一个讲师可以教多门课程，那courses中就会有多行数据的instructor是一样的，所以需要把instructor单独建表，父表就是instructors 子表就是courses。

12.11 第三范式

第三范式：

1、首先要满足第二范式的要求

2、表中列不能派生自其他列

例子：表中有invoice_total、payment_total和balance（结余），

其中balance = invoice_total - payment_total

这就是不符合第三范式的要求，如果人为更改了invoice_total 或者payment_total就会使得实际的balance值有变化，但是我们已存储的balance就不对了，需要使用结余数据时用哪个？？

另外一个例子：

full_name其实是first_name 拼接 last_name，其中一个变了那full_namede值就不对了

12.12 实用建议

不可能完全满足3范式的要求，现实需求中我们尽量减少数据存储的冗余，但不要把模型设计的过于复杂，不要沉溺在自己想象的复杂现实中，要关注当下需求的真实情况。

12.13 模型的正向工程

workbench中的diagram页面，点击Database——》Forward Engineer（正向工程）

可以选择不创建哪个表。

可以把脚本进行保存save to file，也可以复制脚本到工作台查询框copy to clipboard，点击next即可自动创建数据库和里面的表。

12.14 数据库同步模型

如果这个数据库只有你一个人在使用，那么你可以通过以下方法修改数据库表的设置：

但是现实工作中不是一人独享的操作权限，且数据库有多台服务器，不能上面的方式，使用以下方式：

修改模型之后，如果数据库表中没有数据可以使用Forward Engineer，如果已经存储的数据就不能用正向工程了，要使用Database——》Synchronize Model（同步模型）。

在执行过程第一步可以选择要同步的是哪个数据库环境（线上、线下、测试、开发等环境）

12.15 模型的逆向工程

在workbench工作台关闭其他模型，点击Database——》Reverse Engineer

12.16 创建和删除数据库

新建数据库：

加上if not exists 避免下次时候执行报错。

CREATE DATABASE IF NOT EXISTS sql_store2;

删除数据库：

加上if exists避免因为数据库已被删除，重复执行删除sql报错

DROP DATABASE IF EXISTS sql_store2;

12.17 创建表

在create表之前加上drop table if exists 表名称，因为如果你后续要修改表时就不会报错了

每个表都需要设置主键primary key字段，最好设置成auto_increment自增的

有些字段是不能为空的所以需要加上not null

需要给字段设置默认值使用：default 默认值

有些字段不能时重复的，如身份证、邮箱等，在列名最后加上使用unique

USE sql_store2;
drop table if exists customers;
CREATE TABLE customers
(
	customer_id int primary key auto_increment,
    first_name varchar(50) not null,
    points int not null default 0,
    email varchar(255) not null unique
);

12.18 更改表

先选择要更改的表：alter table 表名

增加列：add column

可以使用after 关键字指定增加的这个列在哪个列名的后面

修改列：modify column

删除列：drop column

USE sql_store2;

alter table customers
	-- 在指定位置插入一个新列
	add column `last_name` varchar(50) not null after `first_name`,
    -- 修改已存在的列属性
    modify column first_name varchar(55) default '',
    -- 删除指定列
    drop column points;
    -- add  modify drop 后面的column这个关键字可以省略

12.19 创建关系

设置外键：

foreign key 外键名（外键表内那个要设置成外键的列名）

-- 注意是references不是reference

references 主键表（对应的主键表的那个列名）

-- 设置外键表和主键表在更新&删除时的关联关系

on update cascade

on delete no action

USE sql_store2;

drop table if exists orders;
drop table if exists customers;
CREATE TABLE customers
(
	customer_id int primary key auto_increment,
    first_name varchar(50) not null,
    points int not null default 0,
    email varchar(255) not null unique
);

create table orders
(
	order_id int primary key auto_increment,
    order_customer_id int not null,
    -- 定义外键
    -- 外键命名规则：fk_外键表名_主键表名  后面括号内写上要添加外键的这个列的名字
    foreign key fk_orders_customers (order_customer_id)
		-- 告诉MySQL  orders表中的order_customer_id是引用的 customers表中的哪一列
        references customers (customer_id)
		-- 当customers表中更新或删除外键数据时，
		-- 外键表orders是级联cascade同步更新 删除还是set null或者no action？？
		on update cascade
        on delete no action
        -- 设置on delete no action，当你要删除customers表中某个用户时，mysql会提醒你不能删除这个用户，因为他在orders里面有订单数据
);
-- 如果再次执行sql代码会在drop table if exists customers;报错，因为如果要删除customers表必须要先删除orders表，因为我们的外键属性设置为：on delete no action
-- 所以需要把drop table if exists orders;  这个语句放在drop table if exists customers;前面

12.20 更改主键和外键约束

alter table orders
	-- 删除主键
    drop primary key,
	-- 添加主键 add primary key (列名1, 列名2...)
    add primary key (order_id),
    -- 删除外键，需要说明要删除的外键名称
    drop foreign key fk_orders_customers,
    add foreign key fk_orders_customers (order_customer_id)
		references customers (customer_id)
        on update cascade
        on delete no action;

12.21 字符集和排序规则

-- 查看mysql支持的字符集
show charset;

可以修改数据库的字符编码：

手动需改数据库表的字符编码：

-- 查看mysql支持的字符集
show charset;

-- 在新建数据库时设置字符编码
create database test
	character set latin1;
    
-- 修改数据库的字符编码
alter database test
	character set latin1;
    
    
-- 新建数据库表的时候设置字符编码
create table users
(
	user_id int primary key auto_increment,
	first_name varchar(50) not null,
    last_name varchar(50) not null
)character set latin1;

-- 修改数据库表的字符编码
alter table users
	character set latin1;

12.22 存储引擎

存储引擎决定了数据是如何被存储的，以及哪些功能我们可以使用，

可以手动修改数据库表数据引擎

-- 查看MySQL支持的数据引擎有哪些
show engines;
-- 我们常用的是InnoDB


-- 怎么修改数据库表使用的数据引擎？？
-- 需要注意修改数据库表的数据引擎会花费较长时间，MySQL会重建这张表，在此期间这个表是不可以访问的
alter table customers
engine = InnoDB;

十三、索引

13.1 创建索引

索引存储在内存中，数据存储在磁盘，从内存中读取数据比从磁盘读取数据快很多。

索引的特点：

1、索引会增加数据库的大小，它必须永久存储在表旁边

2、每次增删改数据库表时，MySQL需要更新对应的索引，影响操作性能。

需要注意：你不应该基于表来创建索引，应该基于查询创建索引

使用索引的目的就是为了加快运行较慢的查询。

索引内部一般被存储为二进制树。

USE sql_store;

-- 直接查询customers表中复合要求的客户id
select customer_id from customers where state = 'CA';

-- 使用explain 可以看到本次sql查询处理的数据量
explain select customer_id from customers where state = 'CA';

可以看到查询的对象是全部的customers内的客户信息，随着表内数量增加，这个查询sql会越来越慢。

为了解决这个问题，可以在state列上加索引，代码如下：

-- 在state列加索引，提升sql查询速度
-- 索引命名规范：前缀idx，后面_列名 
-- 后面使用on说明是给哪张表的哪个列加索引的
create index idx_state on customers (state);

再看一下加索引后，使用sql查询处理的数据量是多少吧


-- 使用explain 可以看到本次sql查询处理的数据量
explain select customer_id from customers where state = 'CA';

可以看到加了索引后处理的数量从1010骤减到112条。

上面的possible_keys就是MySQL为了执行这个查询可能会考虑的几个索引。

-- 看下添加索引前sql查询的数据量
explain select customer_id from customers where points > 1000;


-- 添加索引后看下sql查询额数据量
create index idx_points on customers (points);
explain select customer_id from customers where points > 1000;

13.2 查看索引

查看指定表内的索引有哪些

-- 查看指定表有几个索引
show indexes in customers;

collation表示这个索引字段再表中的排序方式，A表示升序，D表示降序。

Index_type是BTREE也就是二进制树。

cardinality表示索引唯一值的估计数量--不一定正确

如果想获取到准确的索引唯一值的数量，在执行show indexes in customers; 之前先执行analyze table customers;

analyze table customers;

-- 查看指定表有几个索引
show indexes in customers;

第一个索引是表的主键，也叫聚集索引，每个表最多有1个聚集索引。

可以看下orders表包含的索引：

设置表关系时，MySQL会自动为外键创建索引，因此可以快速连接表。

可以在workbench工作台左侧的目录中看到每个表包含的索引：

点击每个索引在下方就可以看到它的属性介绍：

13.3 前缀索引

如果要给字符串列创建索引，如

char varchar text blob

因为索引时存储在内存中的，所以索引越小越好，如果包含字符整个列就会太大，所以可以只是包含列的前几个字符或列前缀，这样索引会更小。

USE sql_store;

-- 如果要创建索引的列时字符串类型，为了让索引尽可能的小，减少占的内存空间，使查询更快速
-- 给字符串列加 前缀索引，也就是只选取列的前几个字符或前缀作为索引内容 
-- 那么怎么应该选取几个字符呢？？要尽可能的唯一确认一条数据
-- 方法如下：
-- 1、先看下整个表有多少条数据 ,可以看到有1010条
select count(*) from customers;

-- 2、从左侧选取指定列里的字符，随机试探选取的个数，去重后看有多少条，
-- 越是接近整表数据且选取的字符越少就越复合前缀索引选取的字符个数
select 
	count(distinct left(last_name, 1)),
    count(distinct left(last_name, 5)),
    count(distinct left(last_name, 10)),
    count(distinct left(last_name, 15)),
    count(distinct left(last_name, 20))
from customers;
-- 可以看到选取5，和选取10、15、20字符，得到的索引效果差不多，但是5个字符会占用更少的内存
-- 所以可以选取last_name前5个字符作为前缀索引 

-- 为last_name创建前缀索引 
create index idx_lastname on customers (last_name(5))

需要知道的是，选取的前缀索引不一定非要唯一确认一条数据，只要用尽可能少的前缀字符可以区分尽可能多的数据即可

13.4 全文索引

博客网站内存储了很多文章，数据表结构如下：

如果用户需要搜索包含 react redux的文章，react 和redux可能存在文章标题title也可能存在于文章内容，而且 react和redux不一定都出现了，即使同时出现两个单词不一定挨着，顺序也不一定是react redux，这个时候我们发现普通的查询sql：

-- % 可以代表任意数量的字符
select *
from posts
where title like '%reat redux%' or
body like '%reat redux%';

不能获取到符合需求的全部数据而且查询效率很低。

但是使用前缀索引也达不到目的，因为搜索的内容不是在title和body的开头，此时我们可以用全文索引。

全文索引包括整个字符串列，而不是只存储前缀，它会忽略任何停止词如in、on、the，它存储了一套单词列表，对于每个单词它又存储了一列这些单词会出现的行或记录。

全文索引的有点是它包含了“相关性得分”，MySQL会基于若干因素为包含了搜索短语的每一行计算相关性得分，相关性得分是一个介于0到1的浮点数，0表示没有相关性。

use sql_blog;

-- 全文索引的查询包含2种模式：
-- 1）自然语言模式   也是默认模式      
-- 2）布尔模式  这种模式可通过在单词前面加上 +  - 号，来包括或排除某些单词

-- 在title和body这两列 新建 全文索引
create fulltext index idx_title_body on posts (title, body);

-- 编写查询时需要使用MySQL的2个内置函数来支持全文索引
-- match(列名1, 列名2)，要和你新建的全文索引里面的列对应
-- against('查询内容')

-- 1、使用自然语言模式进行查询
select *, match(title, body) against('react redux')
from posts
where match(title, body) against('react redux');

use sql_blog;

-- 全文索引的查询包含2种模式：
-- 1）自然语言模式   也是默认模式      
-- 2）布尔模式  这种模式可通过在单词前面加上 +  - 号，来包括或排除某些单词

-- 在title和body这两列 新建 全文索引
create fulltext index idx_title_body on posts (title, body);

-- 编写查询时需要使用MySQL的2个内置函数来支持全文索引
-- match(列名1, 列名2)，要和你新建的全文索引里面的列对应
-- against('查询内容')



-- 2、使用布尔模式查询
-- 在查询内容里的 单词前加上-或者+号，后面跟in boolean mode，
-- 来获取包含+号后面的单词 且 不包含-号后面单词的文章数据
select *
from posts
where match(title, body) against('react -redux +form' in boolean mode);

也可以查询包含指定短语的文章数据：

在against('查询内容')函数里面用不同于外侧引号的引号包含就好

外侧是单引号里面就用双引号，外面是双引号里面用单引号即可。

-- 1、使用自然语言模式进行查询
select *, match(title, body) against('react redux')
from posts
where match(title, body) against('"handling a form"');

-- 2、使用布尔模式查询
-- 在查询内容里的 单词前加上-或者+号，后面跟in boolean mode，
-- 来获取包含+号后面的单词 且 不包含-号后面单词的文章数据
select *
from posts
where match(title, body) against('"handling a form"' in boolean mode);

13.5 复合索引

use sql_store;
-- 看下customers表里有几个索引
show indexes in customers;
-- 有3个索引：
-- customer_id 主键
-- idx_state和idx_points

-- 查询state = 'CA' 并且points > 1000的客户信息 
explain select customer_id from customers
where state = 'CA' and points > 1000;

-- 可以看到possible_keys是idx_state和idx_points，但是实际使用的是idx_state这个索引，
-- 需要查询的数据行rows=112 
-- 所以MySQL会从idx_state和idx_points选取了一个查询效率最高的索引，之后在筛选出的state='CA'的112个客户信息中查询满足points > 1000 的客户


-- 怎么能够用上possible_keys里面的全部索引进行查找，提高效率呢？？
-- 这个时候可以创建基于state和points的复合索引 
create index idx_state_points on customers (state, points);

show indexes in customers;

-- 查询state = 'CA' 并且points > 1000的客户信息 
explain select customer_id from customers
where state = 'CA' and points > 1000;

复合索引在优化查询时很有用。

需要注意的是：不要提前创建索引，只有在你写的sql查询效率很低时再根据需要创建索引，不要提前为可能不存在的问题创建索引，因为索引占内存，索引越多写入操作越慢。

可以看出复合索引比单列索引的查询效率高很多，但是一个索引中的列数量是有限制的，MySQL中一个索引中最多包含16列。

use sql_store;
show indexes in customers;

-- 删除无用的索引
drop index idx_state on customers;
drop index idx_points on customers;

13.6 创建复合索引时列的顺序

创建复合索引需要遵循2条规则：

1）会对列进行排序，让使用更频繁的列排在前面

2）把基数更高的列排在最前面

但是有的时候第二条规则也不一定正确，例子如下：

USE sql_store;

-- 查询customers表中state = 'CA'且last_name 以A开头的客户id
-- 一般我们会按照需求描述中的条件顺序进行数据列的筛选 
select customer_id
from customers
where state = 'CA' and last_name like 'A%';

-- 但是这样的查询效率是怎样的呢？？
-- 我们先看下无重复值的state和last_name有多少条数据 
select 
	count(*),
	count(distinct state),
    count(distinct last_name)
from customers;
-- 可以看到count(distinct state) 有48条数据，count(distinct last_name)有996条数据 
-- 已知的是数据总量是1010，如果按照state进行分组得到的子集内的数量一定比last_name的大  
-- 所以在创建复合索引时应该先按照last_name分组，也就是优先把非重复基数大的列名放在前面


-- 我们来验证下 
create index idx_lastname_state on customers (last_name, state);

show indexes in customers;

-- 重新执行查询sql，看下这个sql处理的数据量是多少
explain select customer_id
from customers
where state = 'CA' and last_name like 'A%';

可以看到当前customers表有2个索引：

按照第2条规则在创建索引时把last_name放在前面，MySQL按照索引idx_lastname_state进行查询需要处理的数据量为：

use sql_store;
-- 我们再看下如果创建索引时把state放在前面，sql语句处理的数据量是多少 
create index idx_state_lastname on customers (state, last_name);

show indexes in customers;

explain select customer_id
from customers
where state = 'CA' and last_name like 'A%';

会发现把state放前面处理的数据量更少了，为什么？？

此时我们分析下sql语句：

select customer_id
from customers
where state = 'CA' and last_name like 'A%';

在条件里面state使用的是等号，而last_name使用的是like

使用等号的条件筛选对列的约束性更强，它可以快速定位复合条件的sate，之后再找到以某个字母开头的客户。

而like筛选器会更自由，因为它处理的是一个范围。

如果查询语句变为：

select customer_id
from customers
where state like '%A' and last_name like 'A%';

可以看到这个时候MySQL使用的索引是idx_lastname_state，处理的数据量是40条，此时就和第2条规则对上了。

所以在创建复合索引时，不但要考虑上面2个规则，还要观察我们写的sql语句。

use sql_store;

-- 可以在查询sql中指定要使用的索引 
explain select customer_id
from customers
use index(idx_state_lastname)
where state like '%A' and last_name like 'A%';

13.7 当索引无效时

13.7.1 条件中有or判断

use sql_store;

show indexes in customers;

可以看到当前的customers表中有3个索引

explain select customer_id from customers
where state = 'CA' or points > 1000;

使用索引查询了1010条数据，不是扫描的磁盘，不涉及从磁盘读取。

但是也可以发现，在这个sql里面我们添加的索引没有起作用，此时我们需要重写查询sql，尽可能利用好索引。

思考的角度是拆分查询语句，让每个sql查询的功能更单一。

use sql_store;

show indexes in customers;

explain 
	select customer_id from customers
	where state = 'CA' 
    union 
    select customer_id from customers 
    where points > 1000;
    -- 把or条件查询拆分成2个sql语句  并使用union 合并
    -- 对于第一个sql可以利用上已有的索引：idx_state_points或者idx_state_lastname
    
    -- 那对于第二个sql查询，如果不在points列添加新的索引，就还会选idx_state_points 索引进行扫描，
    -- 即MySQL会查看每个州，再从中筛选出points>1000的客户id

为了提高第二个sql查询的效率，需要给points列加索引

create index idx_points on customers (points);
explain 
	select customer_id from customers
	where state = 'CA' 
    union 
    select customer_id from customers 
    where points > 1000;

但是发现加了idx_points后，第二个sql查询扫描的数据量变大了😂😂

13.7.1 条件中使用表达式

在表达式中用到了列，这时候会发现索引失效了，这个时候需要重写下条件判断，尽量不要用表达式，MySQL不能用最优的方式使用索引。

可以看到上面的sql查询 MySQL扫描了整个索引。

下面进行代码优化：

13.8 使用索引排序

索引不仅可以用来过滤数据，还可以给数据排序。

use sql_store;

show indexes in customers;

explain select customer_id from customers
order by state;

可以看到使用的时索引排序，扫描了整个索引。

换一个字段排序：

explain select customer_id from customers
order by first_name;

type是all，表示进行了全表扫描，使用的是外部排序（extra - using filesort）

注意哈：filesort和文件没关系，是MySQL的一种数据排序算法名称，这种方法很耗时，非必要不用。可以看下是否可以添加一个索引来实现你要的排序效果，避免使用MySQL的这个filesort

-- 查看数据库服务器变量 
show status;

show status like 'last_query_cost';

里面的last_query_cost存储的是上一次查询的时间成本，上一次是用first_name排序

我们看下用state排序耗时：

可以看出用索引排序比MySQL的filesort算法排序快了10倍。

想要使用索引排序，你sql里面order by后面的列名应该从左到右选取创建索引时使用的列名，不然就会使用到filesort算法排序了。

create index idx_列名1_列名2 on 表名 (列名1, 列名2)

-- 注意列名1, 列名2前后要顺序一致

-- 在使用索引进行排序时，如果没有where子句条件来圈定数据，只能使用下面的排序

select 列名 from 表名

order by 列名1

order by 列名1 列名2

order by 列名1 DESC, 列名2 DESC

-- 除了上面列的这些情况，其他排序逻辑都会用到filesort，导致全表扫描

-- 不确认是否会导致全表扫描的，可以执行完之后看下last_query_cost

use sql_store;


explain select customer_id from customers
order by state;
show status like 'last_query_cost';

explain select customer_id from customers
order by points;
show status like 'last_query_cost';

explain select customer_id from customers
order by state, points;
show status like 'last_query_cost';

explain select customer_id from customers
order by state DESC, points;
show status like 'last_query_cost';

explain select customer_id from customers
order by state DESC, points DESC;
show status like 'last_query_cost';

-- 如果sql查询内存在where子句并把数据按照已存在的索引进行了筛选

create index idx_列名1_列名2 on 表名 (列名1, 列名2)

select 列名 from 表名

where 列名1 = ’***‘

order by 列名2

-- 这么写是没问题的，因为where选定了一组数据，且数据内部就是按照列名2升序排列的

explain select customer_id from customers
where state ='CA'
order by points;
show status like 'last_query_cost';

13.9 覆盖索引

为什么我们上面的sql查询都只获取customer_id这一个字段？？

首先表的主键（customer_id）也叫聚集索引，当给表创建二级索引时MySQL会自动把主键包含在第二索引中，所以我们查询show indexes in customers时会包含customer_id

如果我们的sql查询只需要获取索引内包含的列字段，那么这个索引就叫做覆盖索引，因为创建索引时已经覆盖了对应列，查询时自然会使用此索引进行快速扫描，而不用去扫描磁盘表，这种方式sql查询非常快。

所以在写sql查询时先看下你写的where子句中的列，再看下order by子句内的列是否可以创建包含这些列的索引，或者已经创建了包含这些列的索引，这样会使sql查询速度非常快。

13.10 维护索引

使用索引会使sql查询变得高效，但是物极必反，索引毕竟会占用内存，是数据库变大，所以需要注意你创建的索引是否存在：”重复索引“和”多余索引“

重复索引指的是创建索引的列名和顺序完全一样，MySQL不会拒绝你创建重复索引，所以需要自己注意，可以在创建索引前show indexes in 表名看下当前表有哪些索引。

多余索引：如你创建了索引idx_列1_列2，之后你又创建了idx_列1，那么idx_列1就属于多余索引

但是如果你创建的是idx_列2_列1 或者 idx_列2，就不是多余索引。

总之提醒一下在创建索引时先看已存在的索引有哪些，记得不要有”重复索引“、”多余索引“和未被使用的索引。

十四、数据库安全

14.1 创建用户



-- 新建用户时限制用户的连接位置 输入计算机的IP地址
-- 这就要求用户只能从同一台安装了MySQL的计算机连接
-- 如果他试图通过其他计算机连接数据库，MySQL会拒绝连接 
create user john@127.0.0.1;
-- 这种方式通常适用于云环境，通常有一个网络服务器和一个数据库服务器，
-- 在数据库服务器上创建新用户时，需要确保这个用户只能从网络服务器连接 
-- 所以在新建用户时需要指定网络服务器的IP地址 

-- 新建用户时指定主机名 
-- localhost值得时当前安装了MySQL的这台计算机
create user lisa@locahost;


-- 新建用户时指定域名
-- 这个用户可以通过这域名codewithmosh.com中的任何计算机连接到数据库
-- 但不能从其他域名连接数据库，包括codewithmosh.com的子域网 
-- 如果希望用户可以使用任何codewithmosh.com和它子域网中的计算机连接数据库 
-- 可以使用 %.  
create user lili@'codewithmosh.com';
create user anna@'%.codewithmosh.com';


-- 如果不想给用户任何限制 在用户名后什么也不加即可
create user mary;
-- mary可以从任何地方连接数据库  无限制


-- 在新建用户的时候需要设置登录的密码 
create user john identified by 'john1234';
-- 需要注意最好设置成强密码

14.2 查看用户

-- 查看MySQL服务器有哪些用户
select * from mysql.user;

在workbench工作台也可以查看数据库服务器有哪些用户：

14.3 删除用户

-- 创建MySQL服务器用户账号
create user bob@codewithmosh.com identified by '1234';
select * from mysql.user;
-- 删除bob
drop user bob@codewithmosh.com;
select * from mysql.user;

14.4 修改密码

-- 为指定账号修改密码
set password for john = '1234';

-- 修改自己的密码 
set password = '123456';

还可以在workbench工作台修改账号密码：

14.5 授予权限

查看MySQL账号可以分配哪些权限：MySQL :: MySQL 5.7 Reference Manual :: 6.2.2 Privileges Provided by MySQL

all是最高权限，这个账号会拥有全部权限。

-- 一个网站或者应用程序，有查看和提交数据的权限，但是不能有创建新表和修改现有数据表的权限
-- 假设这个应用名字叫moon，我们创建一个moon_app的账号 
create user moon_app@localhost identified by '1234';
-- 给这个账号分配权限 目标数据库是sql_store下的全部数据表，也可以指定表
grant select, insert, update, delete, execute
on sql_store.*
to moon_app@localhost;
-- 需要注意在分配权限的时候to后面的用户需要和创建此用户时写法一样，创建时对账号有限制，这里也应该有限制

执行完上述代码，验证下该账号是否创建成功，并验证账号的数据库权限是否开通。

-- 对于管理员账号可以有查看、提交、新建、修改数据表的权限
-- 给之前添加的John添加权限  目标是全部数据库的全部数据表
grant all
on *.*
to john;

14.6 查看权限

有2种方法可以查看账号的权限：

1、使用sql

show grants for john;

show grants for moon_app@localhost;

-- 查看当前登录账号的权限
show grants;

2、在workbench工作台查看账号的权限和角色：

14.7 撤销权限

-- 给moon_app分配创建视图的权限 
grant create view
on sql_store.*
to moon_app@localhost;

-- 撤销某个账号的某个权限
revoke create view
on sql_store.*
from moon_app@localhost;

也可以在workbench工作台给账号删除某个权限：

甜甜的凉拌苦瓜

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
sql基础

存储过程是一个包含一堆sql代码的数据库对象。在业务代码中可以调用这些存储过程来获取或保存数据。1、存储过程用来存储和管理sql代码2、数据库管理系统DBMS会对存储过程里的代码左优化，所以在存储过程中sql代码可以运行的很快3、和视图类似，存储过程能加强数据安全性如：取消所有人直接操作数据库表的权限，并且大多数的操作（增删改）由存储过程完成。然后指定某些人可以操作指定的存储过程，可以防止数据被删。
复制链接

扫一扫