本文是Mosh MySQL系列视频教程的笔记。详情搜索关键字：Mosh MySQL

小试牛刀

图片.png

SELECT

多列选择

图片.png

运算符

图片.png

AS 重命名输出

图片.png

修改值

图片.png

DISTINCT

图片.png

练习

图片.png

WHERE

输出特定值

图片.png

逻辑运算符

图片.png

筛选日期

图片.png

多表达式筛选

AND OR
NOT

运算

图片.png

IN

图片.png

某一范围内筛选

图片.png

BETWEEN

图片.png

LIKE

% : 任意字符 - : 匹配单个字符

REGEXP 使用正则表达式

^123: begin with 123
123$: end with 123
|:多个表达式
- []:

查询 NULL 值

图片.png

ORDER

primary key column

图片.png

ORDER BY

DESC:降序

图片.png

数字代替列

图片.png

例子

图片.png

加上表达式

图片.png

LIMIT

选择输出

图片.png

加上偏移量

图片.png

JOIN

JOIN的用法

图片.png

本表JOIN

图片.png

多表JOIN

图片.png

使用AND匹配多个表达式

图片.png

隐式JOIN

图片.png

外结合：LEFT JOIN

图片.png

RIGHT JOIN

图片.png

USING

图片.png

NATURAL（不推荐！）

图片.png

CROSS

图片.png

隐式组合

SELECT s.name,p.name
FROM shippers s,products p
ORDER BY s.shipper_id
-- 隐式语法。每个Shipper与货物不同的组合

图片.png

UNION

组合

SELECT
	o.order_id,
    o.order_date,
    'This Yera' AS status
FROM orders o
WHERE o.order_date>='2019-01-01'
UNION
SELECT
	o.order_id,
    o.order_date,
    'Before' AS status
FROM orders o
WHERE o.order_date<'2019-01-01'

图片.png

使用UNION可以组合两个查询输出。注意，两个查询结果列数应该相同。

多个查询结果结合

SELECT c.customer_id,c.first_name,c.points,'黄金客户' AS type
FROM customers c
WHERE c.points>=3000
UNION
SELECT c.customer_id,c.first_name,c.points,'白银客户' AS type
FROM customers c
WHERE c.points BETWEEN 1000 AND 3000
UNION
SELECT c.customer_id,c.first_name,c.points,'普通客户' AS type
FROM customers c
WHERE c.points<1000

图片.png

插入

表格操作简介

图片.png

INSERT INTO

顺序可以变化

INSERT INTO customers(
    last_name,
    first_name,
    birth_date,
    address,
    city,
    state)
VALUES(
	'Smith',
    'Join',
    '1998-01-01',
    'address',
    'city',
    'CA'
)

图片.png

多行插入

INSERT INTO products
VALUES
	(DEFAULT,'Apple phone',20,59),
    (DEFAULT,'Xiao Mi',30,29),
    (DEFAULT,'Steam Games',999,10)

图片.png

多表插入

INSERT INTO orders(customer_id,order_date,status)
VALUES (1,'2022-01-01',1);

INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.95),
    (LAST_INSERT_ID(),2,2,3.95)

图片.png

CREATE

复制出一个新表

CREATE TABLE orders_copy_archived AS
SELECT * FROM orders

图片.png

巧妙使用SELECT

CREATE TABLE invoices_archived AS
SELECT
    i.invoice_id,
    c.name,
    i.payment_date
FROM invoices i
JOIN clients c
	ON i.client_id=c.client_id
WHERE (payment_date IS NOT NULL)

图片.png

UPDATE

更新一条记录

UPDATE invoices
SET payment_total=10,payment_date='2022-01-02'
WHERE invoice_id=1

图片.png

更新多条记录

默认情况下，使用MySQL WorkBrench无法更新多条记录，因为软件启用安全模式。需要关闭安全模式才能执行。
- Edit -> Preference...
- 取消“Safe Updates”

UPDATE invoices
SET payment_total=10,payment_date='2022-05-02'
WHERE client_id=1

图片.png

根据姓名值更新

图片.png

UPDATE invoices
SET
    payment_total=invoice_total * 0.5,
    payment_date=due_date
WHERE client_id=
            (SELECT client_id
            FROM clients
            WHERE name='Myworks')

图片.png

如果返回多个结果，改成 IN

UPDATE invoices
SET
    payment_total=invoice_total * 0.5,
    payment_date=due_date
WHERE client_id IN
			(SELECT client_id
			FROM clients
			WHERE state IN('CA','YN'))

DELETE

删除特定记录

DELETE FROM invoices
WHERE client_id=(
    SELECT *
    FROM clients
    WHERE name= 'Myworks'
)

聚合函数

MAX MIN AVG SUM COUNT DISTINCT

SELECT
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    COUNT(invoice_total) AS number_of_invoices,
    COUNT(payment_date) AS count_of_payments,
    COUNT(*) AS total_records,
    COUNT(client_id) AS Cl,
    COUNT(DISTINCT client_id) AS Dist_cl
FROM invoices

图片.png

SELECT
    'First hald 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 BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT
    'Last 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 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_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

图片.png

GROUP BY

SELECT
    p.date AS Date,
    m.name AS 'Pay Method',
    SUM(p.amount) AS Total
FROM payments p
JOIN payment_methods m
	ON p.payment_method=m.payment_method_id
GROUP BY p.date,p.payment_method
ORDER BY p.date

图片.png

HAVING

HAVING 用于筛选GROUP BY后的数据，而WHERE用于筛选GROUP BY 之前的数据。
筛选生于1985年后并且消费超过100￥的人：

SELECT
    cs.customer_id,
    cs.first_name,
    cs.birth_date,
    SUM(oi.quantity*oi.unit_price) AS Spend_Total
FROM customers cs
JOIN orders od
	ON cs.customer_id=od.customer_id
JOIN order_items oi
	ON oi.order_id=od.order_id
WHERE cs.birth_date>='1985-01-01'
GROUP BY cs.customer_id
HAVING Spend_Total>=100

图片.png

WITH ROLLUP

使用ROLLUP可以汇总的数据

SELECT
    pm.name,
    SUM(p.amount) AS Total
FROM payments p
JOIN payment_methods pm
	ON pm.payment_method_id=p.payment_method
GROUP BY p.payment_method WITH ROLLUP

图片.png

复杂查询

子查询

-- 查询工资大于平均工资的员工
SELECT
    first_name,
    last_name,
    salary
FROM employees
WHERE salary>=(
	SELECT SUM(salary)/COUNT(*)
    FROM employees
)

图片.png

NOT IN

-- 找出没有invoice的客户
SELECT
    client_id,
    name
FROM clients
WHERE client_id NOT IN(
	SELECT 
		DISTINCT client_id
	FROM invoices
)

图片.png

等同于下面

SELECT
    client_id,
    name
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

复合子查询

-- 找出购买lettuce(id=3)的顾客
SELECT
    customer_id,
    first_name,
    last_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id=3
	)
)

图片.png

-- JOIN version:
SELECT
	DISTINCT cs.customer_id,
    cs.first_name,
    cs.last_name
FROM customers cs
JOIN orders od
	ON od.customer_id=cs.customer_id
JOIN order_items oi
	ON oi.order_id=od.order_id
WHERE oi.product_id=3

ALL

-- 找到所有比3号客户的所有invoice还要大的客户
SELECT *
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
    FROM invoices
    WHERE client_id=3
)

图片.png 使用MAX

SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id=3
)

ANY SOME

-- 输出至少有2个invoice的客户
SELECT *
FROM clients
WHERE client_id = ANY(
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

图片.png

EXISTS

EXISTS可以理解为重合部分。他会返回True或False给WHERE

-- 找出没有订单的商品
SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id=p.product_id
)

图片.png

SELECT中的子查询

SELECT 
    invoice_id,
    invoice_total,
    (
		SELECT AVG(invoice_total)
		FROM invoices
	) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices

图片.png

FROM中的子查询

-- MAX difference
SELECT
	MAX(difference) AS Max_difference
FROM (
		SELECT 
			invoice_id,
			invoice_total,
			(
				SELECT AVG(invoice_total)
				FROM invoices
			) AS invoice_average,
			invoice_total - (SELECT invoice_average) AS difference
		FROM invoices
	) AS dfference_table

图片.png

数据处理

数值函数

ROUND(5.637) 6
ROUND(5.637,1) 5.6
TRUNCATE(5.637,0) 5
CEILING(5.637) 6
FLOOR(5.637) 5
ABS(-5.637) 5.637
RAND() 随机数0~1

字符串

LENGTH('apple') 5
UPPER('apple') APPLE
LOWER('AjsaiJIhiHI') ajsaijihihi
TRIM(' SKY ') SKY
- LTRIM 左裁剪
- RTRIM 右裁剪
RIGHT('kindergarden',6) garden
LEFT('kindergarden',6) kinder
SUBSTRING('kindergarden',3,5) nderg 第三个参数可选
LOCATE('n','kindergarden') 3
- LOCATE(' ','kindergarden') 0
- LOCATE('garden','kindergarden') 7
REPLACE('kindergarden','garden','gardon') kindergardon
字符串链接

SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM customers

图片.png

时间函数

NOW() 2022-10-23 15:00:11
- CURDATE() 2022-10-23
- CURTIME() 15:00:57
年月日时分秒

SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())
2022	10	23	15	3	13

DAYNAME(NOW()),MONTHNAME(NOW()) Sunday October
EXTRACT(DAY FROM NOW()) 23
查询当年结果

SELECT *
FROM orders
WHERE YEAR(order_date)=YEAR(NOW())

格式化日期与时间

DATE_FORMAT(NOW(),'%D %M %Y') 23rd October 2022
TIME_FORMAT(NOW(),'%H:%i %p') 15:11 PM

计算日期与时间

DATE_ADD(NOW(),INTERVAL 1 DAY) 2022-10-22 15:12:26（昨天）
上面=DATE_ADD(NOW(),INTERVAL -1 DAY)
计算日期差：DATEDIFF('2020-01-01','2020-12-31') -365
TIME_TO_SEC('00:00'):0 TIME_TO_SEC('02:00'):7200
- TIME_TO_SEC('09:00') - TIME_TO_SEC('08:00'):3600

IFNULL COALESCE

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

图片.png

SELECT 
    order_id,
    COALESCE(shipper_id,comments,'未分配') AS shipper
FROM orders
ORDER BY order_id

图片.png

SELECT 
    CONCAT(first_name,last_name) AS full_name,
    COALESCE(phone,'Unknow')
FROM customers
ORDER BY customer_id

图片.png

IF

IF(EXP,first,second) EXR为真，是first，否则SECOND

-- 查看商品所对应订单数量并给出状态
SELECT
    product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*)>1,'Many Times','Once') AS Frequency
FROM products
JOIN order_items USING (product_id)
GROUP BY product_id

图片.png

CASE

SELECT
    order_id,
    CASE
		WHEN YEAR(order_date) = YEAR('2019-01-01') THEN 'Active'
        WHEN YEAR(order_date) = YEAR('2019-01-01') - 1 THEN 'Last Year'
        WHEN YEAR(order_date) < YEAR('2019-01-01') - 1 THEN 'Archived'
        ELSE 'Fulture'
	END AS status
FROM orders

图片.png

SELECT
    CONCAT(first_name,last_name) AS full_name,
    points,
    CASE
        WHEN points>3000 THEN '黄金会员'
        WHEN points BETWEEN 1000 AND 3000 THEN '白银会员'
        ELSE '青铜会员'
	END AS category
FROM customers

图片.png

视图

创建视图

CREATE VIEW clients_balance AS
SELECT 
    i.client_id,
    c.name,
    SUM(i.invoice_total)-SUM(i.payment_total) AS balance
FROM invoices i
JOIN clients c ON c.client_id=i.client_id
GROUP BY i.client_id

图片.png

更改、删除视图

DROP VIEW view_name

CREATE OR REPLACE VIEW clients_balance AS
SELECT 
....

可更新视图 updated view

SELECT中没有下面东西的视图为可更新视图
- DISTINCT
- 聚合函数(MIN/MAX/SUM)
- GROUP BY / HAVING
- UNION

WITH OPTION CHECK

WITH OPTION CHECK 这条语句起警示作用，防止用户通过UPDATE或者DELETE语句将行从视图中删除

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

这样，执行更新语句时会报错：

图片.png

视图的优点

减小数据库设计改动的影响
- 如果原表改动列名，使用原表的查询都得更改代码
- 可以在视图里用AS将改动使用别名，查询使用视图，这样查询不用改代码了
限制对基础表的访问

存储过程与函数

存储过程 stored procedure

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

创建存储过程

DELIMITER $$
-- 更改默认分隔符为 $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END$$
DELIMITER ;
-- 改回去

图片.png

CALL get_clients()

图片.png

使用 WorkBench 创建存储过程

图片.png

删除存储过程

DROP PROCEDURE IF EXISTS get_clients

参数 Parameters

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
    SELECT * FROM clients c
    WHERE c.state=state;
END$$
DELIMITER ;

图片.png

带默认值的参数 Parameters with Default Value

DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	IF state IS NULL THEN
		SET state = 'CA';
	END IF;
	SELECT * FROM clients c
    WHERE c.state=state;
END$$
DELIMITER ;

图片.png


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 ;

-- 如果为空，返回整个
DELIMITER $$
CREATE PROCEDURE get_clients_by_state(state CHAR(2))
BEGIN
	SELECT * FROM clients c
	WHERE c.state=IFNULL(state,c.state);
END$$
DELIMITER ;

参数验证

CREATE PROCEDURE make_payment(
    invoice_id INT,
    payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
	UPDATE invoices i
    SET
        i.payment_total=paymtne_amount,
        i.payment_date=payment_date
	WHERE i.invoice_id=invoice_id;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
    invoice_id INT,
    payment_amount DECIMAL(9,2),
    payment_date DATE
)
BEGIN
	IF payment_amount <= 0 THEN
		SIGNAL SQLSTATE '22003' 
			SET MESSAGE_TEXT = 'Invalid payment amount';
	END IF;
        -- search SQLSTATE on google
	UPDATE invoices i
    SET
        i.payment_total=payment_amount,
        i.payment_date=payment_date
	WHERE i.invoice_id=invoice_id;
END

输入-100，引发异常：

图片.png

输出参数 Output Parameters

输出参数
参数列表里参数前带OUT。SELECT后跟INTO

变量 Variable

User or session Variable 用户变量或会话变量

SET @a_variavle = 0

Local Variable 局部变量
- 定义并只存在在函数或过程中

CREATE PROCEDURE `get_risk_factoe`()
BEGIN
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
	-- 声明局部变量
	DECLARE invoices_total DECIMAL(9,2);
	DECLARE invoices_count INT;

	SELECT COUNT(*) , SUM(invoice_total)
	INTO invoices_count,invoices_total
	FROM invoices;
	-- INTO 里面的两个变量与 SELECT 里面的相对应
	
	SET risk_factor=invoices_total/invoices_count*5;

	SELECT risk_factor;
END

图片.png

函数 Function

函数只能返回单个值 a single value
在 MySQL WorkBench 创建函数：

图片.png

CREATE FUNCTION `get_risk_factor_for_client` (
	client_id INT
)
RETURNS INTEGER
-- 返回值 RETURNS 后面写的是数据类型

-- 属性
-- DETERMINISTIC 
-- 1. deterministic 确定性：
-- 如果输入的值相同，函数输出的值也是相同的。（确定唯一）
READS SQL DATA 
-- 2. 函数中会配置选择语句 用来读取一些数据
-- MODIFIES SQL DATA
-- 3. 函数中有插入、更新、删除语句

BEGIN
	DECLARE risk_factor DECIMAL(9,2) DEFAULT 0;
	DECLARE invoices_total DECIMAL(9,2);
	DECLARE invoices_count INT;

	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 risk_factor;
END

图片.png

删除函数

DROP FUNCTION IF EXISTS `function_name`;

其他东西

bulabulabula

触发器 Triggers

触发器

Trigger is a block of SQL code that automatically gets excuted before or after an insert, update or delete statement.
触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码。

DELIMITER $$
CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
	-- 在INSERT语句生效
	FOR EACH ROW
	-- 对于插入的每一行都生效
BEGIN
	UPDATE invoices_total
	SET payment_total=payment+_total+ NEW.amount
	-- 通过 NEW.amount 获取新的数额
	WHERE invoice_id=NEW.invoice_id;
	-- OLD 旧值
END $$
DELIMITER ;

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 ;

查看设置过的触发器

SHOW TRIGGERS;
SHOW TRIGGERS like 'payment%';

图片.png

删除触发器

DROP TRIGGER IF EXISTS 'name'

使用触发器进行审计

一个空的审计表

Table: payments_audit
Columns:
client_id int 
date date 
amount decimal(9,2) 
action_type varchar(50) 
action_date datetime

INSERT触发器

DELIMITER $$
DROP TRIGGER IF EXISTS payments_after_insert;
CREATE TRIGGER payments_after_insert
	AFTER INSERT ON payments
	FOR EACH ROW
BEGIN
	INSERT INTO payments_audit
	VALUES (NEW.client_id,NEW.date,NEW.amount,'INSERT',NOW());
	-- NEW 代表新插入值
END $$
DELIMITER ;

DELETE 触发器

DELIMITER $$
DROP TRIGGER IF EXISTS payments_after_delete;
CREATE TRIGGER payments_after_delete
	AFTER DELETE ON payments
	FOR EACH ROW
BEGIN
	INSERT INTO payments_audit
	VALUES (OLD.client_id,OLD.date,OLD.amount,'DELETE',NOW());
END $$
DELIMITER ;

测试

插入
- payments
- payments_audit:

-- 插入
INSERT INTO payments
VALUES(DEFAULT,5 ,3,'2022-01-01',100,1)

删除

DELETE FROM payments
WHERE payment_id = 10

.
- payments_audit

事件 Events

找到并开启事件调度器

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE 
	-- AT '2019-05-01'
	-- 只执行一次
	EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
	DO BEGIN
		DELETE FROM payments_audit
		WHERE action_date<NOW() - INTERVAL 1 YEAR;
		--                DATE_ADD(NOW(),INTERVAL -1 YEAR)
		-- 删除所有超过一年的审计表
END $$

DELIMITER ;

查看删除更改事件

-- 查看、删除

SHOW Events;
DROP EVENT IF EXISTS 'name';

-- 更改事件

ALTER EVENT ...
使用 ALTER 替换 CREATE 就行

-- 启用关闭事件

ALTER EVENT 'name' DISABLE/ENABLE

事务 Transaction

事务基本概念

Transaction is a group of SQL statement that represent a single unit of work.
事务是代表单个工作单元的一组SQL语句。要么语句都执行成功，要么都失败。
属性
- atomicity 原子性：事务是一个整体。要么所有语句全部正常执行，要么撤销至原来的状态
- consistency 一致性：数据库始终保持一致，不会出现有订单没有项目的情况
- isolation 隔离性：不同事务间相互隔离，互不影响。或者是当有相同数据被修改时各自收到保护，事务操作数据时该数据被锁定，期间不能被其它事务所影响。
- durability 持久性：一旦事务被提交，事务的更改是永久的

创建事务

START TRANSACTION;

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

INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,1);

COMMIT;

测试撤销事务
- 逐行执行代码
- 如果中途关闭当前链接，事务就会撤销
手动退回

COMMIT ->> ROLLBACK

并发与死锁

演示

并发问题

Lost Updates 失去更新
- 两个事务更新同一行，最后提交的事务覆盖了先前所做的更改
- 使用锁
Dirty Reads 脏读
- 事务中读取数据两次，但得到了不同的结果。
- 为事务建立隔离级别
- READ COMMITEED 只能读取已提交的数据
Non-repeating Read 不可重复读

图片.png - 实际情况，应该读取最新的值 - REPEATABLE READ 事务读取的数据是可重复且一致的。及时执行过程中其它事务修改了数据，仍然是首次读取就创建的快照。

Phantom Reads 幻读
- 在查询中缺失了一行或多行，因为有另一个事务正在修改数据，我们没有注意到修改。
- 实际情况分析
- 隔离级别:SERIALIZABLE 序列化：当有别的事务更新数据时，此事务能知晓变动。

事务隔离级别 Transaction isolation levels

总结
查看更改当前事务隔离级别

SHOW VARIABLE LIKE 'transaction_isolation';
SET (SESSION、GLOBAL) TRANSACTION ISOLATION LEVEL SERIALIZABLE;
		-- SESSION: 只要在这里写了会话或链接，所有未来的事务都会是这个隔离级别
		-- GLOBAL: 全局

READ UNCOMMITTED Isolation Level

存在脏读

READ COMMITTED Isolation Level

不存在脏读

两次读取不一致

REPEATABLE READ Isolation Level

两次读取的值一致

幻读

SERIALIZABLE Isolation Level

看视频

死锁 Deadlocks

数据类型

String 字符串

CHAR()
- 固定长度 fixed-length
VAARCHAR()
- 可变长度
- max-length 65535 ~ 64KB
MEDIUMTEXT
- max-16MB
LONGTEXT
- max-4GB
TINYTYXT
- max-255Bytes
TEXT
- max:64KB

Integer 整数

TINYINT 1byte [-128,127]
UNSIGNED TINYINT [0,255]
SMALLINT 2bytes [-32K,32K]
MEDIUMINT 3B [-8M,8M]
INT 4B [-2B,2B]
BIGINT 8B [-9Z,9Z]
补零显示：INT(4) => 0001

定点数、浮点数

DECIMAL(p,s)
- p:精度 1~65
- s:尾数
DEC 同上
NUMERIC 同上
FIXED 同上
FLOAT 4B 精度不确定
DOUBLE 8B 精度不确定

Boolean 布尔类型

Boolean/Bool TRUE-1 FALSE-0

枚举类型集合类型 Enum and Set Types

ENUM('small','medium','large')
- 不建议使用ENUM 不如新建一个查询表(Look-up table)
SET(...)
- 不建议使用

日期事件 Date And Time

DATE
TIME
DATETIME 8b
TIMESTAMP 4b <2038年
YEAR

Blob Types

二进制文件数据不建议使用会变慢
TINYBLOB 255Bytes
BLOB 65KB
MEDIUMBLOB 16MB
LONGBLOB 4GB

JSON

{
	"key":value
}

图片.png

创建JSON

UPDATE products
SET properties ='
{
    "dimensions":[1,2,3],
    "weight":10,
    "manufacturer": {"name":"sony"}
}
' 
WHERE product_id = 1

图片.png

-- 使用自带函数
UPDATE products
SET properties =JSON_OBJECT(
    'weight',10,
    'dimensions',JSON_ARRAY(1,2,3),
    'manufacture',JSON_OBJECT('name','sony')
)
WHERE product_id = 2

图片.png

提取键值对

JSON_EXTRACT(columns,'路径 $代表当前JSON文档')

SELECT product_id,JSON_EXTRACT(properties,'$.weight') AS weight
FROM products
WHERE product_id=1;

SELECT product_id,
    properties -> '$.weight' AS 'weight',
    properties -> '$.dimensions' AS di,
    properties -> '$.dimensions[1]' AS 'di[]',
    properties -> '$.manufacturer.name' AS 'ma.na',
    properties ->> '$.manufacturer.name' AS 'name_without_\""'
FROM products
WHERE product_id=1;

图片.png

SELECT product_id,properties ->> '$.manufacturer.name' AS 'name'
FROM products
WHERE properties ->> '$.manufacturer.name' = 'sony'

图片.png

修改

UPDATE products
SET properties = JSON_SET(
    properties, -- column
    '$.weight',20,
    '$.age',10
)
WHERE product_id=1

图片.png

删除

UPDATE products
SET properties = JSON_REMOVE(
    properties, -- column
    '$.age'
)
WHERE product_id=1

图片.png

设计数据库 Design Database

数据建模 Data Modelling

了解业务需求 Understand the requirements
构建概念模型 Build a Conceptual Model
构建逻辑模型 Build a LogicaL Model
构建实体模型 Build a Pyhsical Model

概念模型 Conceptual Model

Represnts the entities and their relationships.
- ER图
- UML

逻辑模型 Logical Model

实体模型 Pyhsical Model

图片.png

主键 Primary Key

图片.png

外键 Foreign Key

复合主键

图片.png

外键约束 Foreign Key Constrains

CASCADE 如果主键更新，外键随即更新

图片.png

标准化 Normalization

标准范式

第一范式 1NF-First Normal Form

一行中的每个单元格都应该有唯一的一个值，且不能出现重复列。
Each cell should have a single value and we cannot have repeated columns.
例如下表中tags违反了第一范式。为了解决，需要单拉出来新建一个表。

图片.png

链接表 Link Tables

在中间加入course_tags表，满足第一范式。

图片.png

第二范式 2NF-Second Normal Form

标准
每张表只能代表一种实体，并且表中的每一列都应该用来描述那个实体。
Each table should descirbe one entity, and every cloumn in that table should descirbe that entity.
下表Courses中instructor 违反了第二范式。解决后如图：

图片.png

第三范式 3NF-Third Normal Form

表中的列不应派生自其他列。
A column in a table should not be derived from other columns.

实用建议

专注于消除冗余

不要什么都建模

只需要为目前的问题制定最佳解决方案就行，更少关注未来。

模型的正向工程 Forward Engineer

图片.png

数据库同步模型 Synchronizing a Model

图片.png

模型反向工程 Reverse Engineering a Database

图片.png

生成模型如下

图片.png

项目：航班订票系统

balabalabala

项目：balabala

创建和删除数据库

CREATE DATABASE IF NOT EXISTS sql_store2;
-- 加入IF NOT EXISTS是因为防止重建同名表
DROP DATABASE IF EXISTS sql_store2;

创建表

CREATE TABLE IF NOT EXISTS customers
(
        customer_id	INTPRIMARY KEY AUTO_INCREMENT,
	first_name	VARCHAR(50) NOT NULL,
	points		INT NOT NULL DEFAULT 0,
	email		varchar(255) NOT NULL UNIQUE
);

删除

DROP ......

更改表 Altering Tables

ALTER TABLE customers
	ADD 			last_name	VARCHAR(50) NOT NULL AFTER first_name,
	-- AFERT first_name 在first_name后插入该列
	ADD 			city		VARCHAR(50) NOT NULL,
	MODIFY COLUMN	first_name	VARCHAR(55) DEFAULT '',
	DROP 			points
	;

创建关系 Creating Relationships

CREATE TABLE orders
(
	order_id	INT PRIMARY KEY,
	-- 当我们对某一列设置为主键时
	-- MySQL会默认该列不允许出现空值
	customer_id	INT NOT NULL,
	-- 外键设置
	FOREIGN KEY fk_orders_customers (customer_id)
		REFERENCES customers (customer_id)
		ON UPDATE CASCADE
		-- ON UPDATE SET NULL
		-- ON UPDATE NO ACTION
		ON DELETE NO ACTION
);

更改主键/外键

-- 在表格创建好后，添加关系
ALTER TABLE orders
	ADD PRIMARY KEY (order_id),
	DROP PRIMARY KEY, 
        -- 删除主键时不用写列名称
	DROP FOREIGN KEY fk_orders_customers,
	ADD FOREIGN KEY fk_orders_customers (customer_id)
		REFERENCES customers (customer_id)
		ON UPDATE CASCADE
		ON DELETE NO ACTION
;

字符集和排序规则

字符集：字符映射数字的表。每个数字表示一个字符。
SHOW CHARSET : 展示MySQL所支持的字符集

图片.png

有时候，使用不同的字符集会改变存储大小（对于字符串类型的值而言）
查看使用的字符集

图片.png

更改字符集：

图片.png

CREATE DATABASE db_name
	CHARACTER SET latin1

ALTER DATABASE db_name
	CHARACTER SET latin1

CREATE TABLE table1(..)
CHARACTER SET latin1

-- 更改指定列的字符集
CREATE TABLE ..
(
	...
	first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL,
	-- 紧跟着类型后面加
	...
);

存储引擎 Storage Engine

SHOW ENGINES 展示引擎
更换存储引擎

图片.png

ALTER TABLE customers
ENGINE = InnoDB

高效的索引（Index）

索引

索引表：值与对应记录的引用
Design indexes based on your queries, not your tables.
基于查找设计索引，而不是表。
索引内部通常被存储为二叉树。

创建索引

解释语句

EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';

图片.png

创建索引

CREATE INDEX idx_state ON customers (state);

再次执行上面的解释语句：

图片.png

练习：

EXPLAIN SELECT points FROM customers WHERE points >= 1000;
CREATE INDEX idx_points ON customers (points);
EXPLAIN SELECT points FROM customers WHERE points >= 1000;

查看索引

SHOW INDEXES IN customers;

图片.png

前缀索引 Prefix Indexes

当为字符串创建索引时，如果存储所有字符串可能会占用大量空间。这时候就可以使用前缀索引。

CREATE INDEX idx_lastname ON customers (last_name(5));

使用合理的索引长度，要考虑对应长度下索引的精确性，也要考虑合理的空间大小：

SELECT
    COUNT(DISTINCT LEFT(last_name, 1)) AS prefix_1,
    COUNT(DISTINCT LEFT(last_name, 3)) AS prefix_3,
    COUNT(DISTINCT LEFT(last_name, 5)) AS prefix_5,
    COUNT(DISTINCT LEFT(last_name, 6)) AS prefix_6,
    COUNT(DISTINCT LEFT(last_name, 7)) AS prefix_7,
    COUNT(DISTINCT LEFT(last_name, 9)) AS prefix_9
FROM customers;

图片.png

如图，随着前缀长度的增加，对应所能准确找到的人名字数量越多。但是当前缀长度超过6时，数量已经很接近总数1010，而且增加的数量越来越少了。所以，此处前缀长度设置为6是比较合适的。

全文索引 Full-text Index

例如有一个博客，里面有许多文章。要搜索有关React的Redux相关的技术文章，如果写出下面的查询语句：

USE sql_blog;
SELECT *
FROM posts
WHERE title LIKE '%react redux%' or
	body LIKE '%react redux%';

这样写有两个缺点：
- 首先不能使用前缀索引提高查询速度。因为前缀索引要求匹配字符串前面几个字符，但是这里的查询关键字不一定只出现在字符串的开头，例如现实中文章标题中间、文章正文中都可能会含有关键字
- 其次，这里只能搜多含有字符串react redux的结果。然而现实中可能还会需要搜索到含有react或redux单个单词的结果。
全文索引本质上存储的是一系列（忽略诸如"the"、"in"、"an"等无关的）单词。对于每个单词，它们又存储了一列这些单词会出现的行或记录。

CREATE FULLTEXT INDEX idx_title_body ON posts(title,body)
	-- 在title与body两个列中搜索

SELECT *
FROM posts
WHERE MATCH(title,body) AGAINST('react redux');
	-- MATCH 里面是对应全文索引的列，要与相应的全文索引对应

图片.png

全文搜索与相关性得分有关。浮点数，从0到1。0代表毫无关系。

SELECT * , MATCH(title,body) AGAINST('react redux') AS Relationship_Score
FROM posts
WHERE MATCH(title,body) AGAINST('react redux');

图片.png

全文搜索两种模式：
- 自然语言模式：默认
- 布尔模式：可以排除或筛选某些单词

SELECT * , MATCH(title,body) AGAINST('react -redux +form') AS Relationship_Score
FROM posts
WHERE MATCH(title,body) AGAINST('react -redux +form' IN BOOLEAN MODE);
-- 搜索包含React但是没有Redux并且必须含有Form
-- 添加双引号："handling a form"。搜索结果必须准确包含字符串“handling a form”

图片.png

复合索引 Composite Index

使用SHOW INDEXES IN customers：

图片.png

新建查询：

EXPLAIN SELECT customer_id FROM customers
WHERE state = 'CA' AND points > 1000;

图片.png

通过解释语句，发现查询语句有两个条件。虽然这两个条件各有对应的索引，并且MySQL推得可能的索引有两个（如上图），但是最终MySQL还是只使用了一个索引：idx_state，之后又在表中查询对应的points，不够快。
创建复合索引

CREATE INDEX idx_state_points ON customers (state,points);

图片.png 如图，再次搜索后，MySQL使用了复合索引，并且所需要搜寻的列更少了。

复合索引中列的顺序

让更频繁使用的列放前面
把基数（Cardinality）更高的列放前面

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_state_lastname)
WHERE state='CA' AND last_name LIKE 'A%';

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_lastname)
WHERE last_name LIKE 'A%';

当索引无效时

EXPLAIN SELECT customer_id FROM customers
WHERE points + 10 > 2010;

上面的查询中，MySQL会搜索全部行。因为表达式含有列points。应该写成points > 2000，这样就可以使用对应的索引。

使用索引排序

使用索引排序

EXPLAIN SELECT customer_id FROM customers ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

图片.png 使用已有的索引排序，可以减少时长。

使用外部排序（默认排序）

EXPLAIN SELECT customer_id FROM customers ORDER BY first_name;
SHOW STATUS LIKE 'last_query_cost';

图片.png

注意上图中的Using Filesort 如图，这次排序耗时时间较长。
不同顺序的排序
- 复合排序对应的栏都以相同的顺序排序。

EXPLAIN SELECT customer_id FROM customers
ORDER BY state DESC, points DESC;
SHOW STATUS LIKE 'last_query_cost';

图片.png - 复合排序对应的栏以不同的顺序排序。这里耗时较长。

EXPLAIN SELECT customer_id FROM customers
ORDER BY state , points DESC;
SHOW STATUS LIKE 'last_query_cost';

图片.png - 更改栏的顺序也会影响查询速度。

EXPLAIN SELECT customer_id FROM customers
ORDER BY points , state ;
SHOW STATUS LIKE 'last_query_cost';

图片.png

覆盖索引 Covering Indexes

如果已经建立起相关的索引，之后查询时SELECT、ORDER BY以及WHERE最好跟上索引对应的列。使用*等可能会执行全表查询。

索引维护 Index Maintenance

重复索引
- 例如两个索引：(A,B,C)与(A,B,C)
- 创建索引时，MySQL不会检查是否重复。
- 在创建索引前，建议检查是否已经存在有满足的索引，防止重复。
多余索引
- 例如(A,B)与(A)，其中(A)多余了。因为(A,B)可以覆盖(A)
- 但是如果存在(B,A)与(A)，二者不多余
总结：创建索引时，查看现有索引。及时维护，减少重复索引与多余索引

保护数据库 Securing Databases

创建一个用户

默认使用的是root用户。现实使用时需要创建用户。

CREATE USER john@127.0.0.1 IDENTIFIED BY '123456';
-- 通过IP链接
CREATE USER john@localhost IDENTIFIED BY '123456';
-- 通过主机名链接
CREATE USER john@'%mywebsite.com' IDENTIFIED BY '123456';
-- 通过域名链接。为了包括子网，使用通配符`%`
CREATE USER john IDENTIFIED BY '123456';

查看用户

SELECT * FROM mysql.user;

图片.png

使用Workbench

图片.png

删除用户

CREATE USER john2@localhost IDENTIFIED BY '123456';
DROP USER john2@localhost;

更改密码

SET PASSWORD FOR john = '123456';
SET PASSWORD = '123456';
-- root用户

使用Workbench

图片.png

授予权限

CREATE USER moon_app@localhost IDENTIFIED BY '123456';

GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE
ON sql_store.*
-- 可以指定表：sql_store.customers
TO moon_app@localhost;

连接账户

图片.png

设置管理员

GRANT ALL
ON *.*
TO john

查看权限

SHOW GRANTS FOR john;
SHOW GRANTS; -- root用户

取消权限

GRANT INSERT
ON sql_store.*
TO moon_app@localhost;

REVOKE INSERT
ON sql_store.*
FROM moon_app@localhost;

2022年11月2日

Mosh MySQL 教程笔记

小试牛刀

SELECT

多列选择

运算符

AS 重命名输出

修改值

DISTINCT

练习

WHERE

输出特定值

逻辑运算符

筛选日期

多表达式筛选

运算

IN

某一范围内筛选

BETWEEN

LIKE

REGEXP 使用正则表达式

查询 NULL 值

ORDER

primary key column

ORDER BY

数字代替列

例子

加上表达式

LIMIT

选择输出

加上偏移量

JOIN

JOIN的用法

本表JOIN

多表JOIN

使用AND匹配多个表达式

隐式JOIN

外结合：LEFT JOIN

RIGHT JOIN

USING

NATURAL（不推荐！）

CROSS

隐式组合

UNION

组合

多个查询结果结合

插入

表格操作简介

INSERT INTO

多行插入

多表插入

CREATE

复制出一个新表

巧妙使用SELECT

UPDATE

更新一条记录

更新多条记录

根据姓名值更新

DELETE

删除特定记录

聚合函数

MAX MIN AVG SUM COUNT DISTINCT

GROUP BY

HAVING

WITH ROLLUP

复杂查询

子查询

NOT IN

复合子查询

ALL

ANY SOME

相关子查询

EXISTS

SELECT中的子查询

FROM中的子查询

数据处理

数值函数

字符串

时间函数

格式化日期与时间

计算日期与时间

枚举类型集合类型 Enum and Set Types

日期事件 Date And Time