Code With Mosh 学习笔记

sosososoon

已于 2022-11-20 16:57:57 修改

阅读量2.2k

点赞数

分类专栏： SQL 文章标签： mysql sql 数据库

于 2020-11-26 19:09:39 首次发布

本文链接：https://blog.csdn.net/sosososoon/article/details/108455418

版权

SQL 专栏收录该内容

2 篇文章 3 订阅

订阅专栏

Retrieving Data From a Single Table

REGEXP

Get the customers whose

first names are ELKA or AMBUR
last names end with EY or ON
last names start with MY or contains SE
last names contain B followed by R or U

	use sql_store;
	select * from customers
	-- where first_name regexp '^ELKA$|^AMBUR$'
	-- where last_name regexp 'ey$|on$'
	-- where last_name regexp '^my|se'
	where last_name regexp 'b[ru]'

IS NULL

Get the orders that are not shipped

USE sql_store;
SELECT * FROM orders
WHERE shipped_date IS NULL

ORDER BY

选择所有order id为2的项目，根据总价排序

USE sql_store;
SELECT * FROM order_items
WHERE order_id='2'
ORDER BY quantity*unit_price DESC

LIMIT

从第4条记录开始，返回5条记录

USE sql_store;
SELECT * FROM customers
LIMIT 4,5

Get the top three loyal customers

USE sql_store;
SELECT * FROM customers
ORDER BY points DESC
LIMIT 3

Retrieving Data From Multiple Tables

INNER JOIN

返回每笔订单的订单id,产品id，名字，订购数量和单价

USE sql_store;
SELECT order_id,p.product_id,name,quantity,o.unit_price 
FROM products p JOIN order_items o
ON p.product_id = o.product_id

Joining Across Databases

只需给不在当前数据库的表前加上其所在的数据库

USE sql_store;
SELECT *
FROM sql_inventory.products p JOIN order_items o
ON p.product_id = o.product_id

Self Join

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

Joining Multiple Tables

连接payments,payment_methods,cilents表

USE sql_invoicing;
SELECT p.invoice_id,date,c.name,pm.name as payment,amount,state FROM payments p
JOIN payment_methods pm 
	ON p.payment_method = pm.payment_method_id 
JOIN clients c
	ON c.client_id = p.client_id

Compound Join Conditions

USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
	ON oi.order_id = oin.order_id
	AND oi.product_id = oin.product_id

Implicit Join Syntax

(可以这样做，但最好不要)

USE sql_store;
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

Outer Joins

USE sql_store;
SELECT c.customer_id,c.first_name,o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
ORDER BY c.customer_id

连接order_items和products表，找出产品被订购的次数

USE sql_store;
SELECT p.product_id,p.name,sum(oi.quantity)
FROM products p
LEFT JOIN order_items oi
	ON p.product_id = oi.product_id
GROUP BY p.product_id

Outer Join Between Multiple Tables

USE sql_store;
SELECT order_date,first_name,order_id,s.name as shipper,os.name as status
FROM orders o 
LEFT JOIN shippers s
	ON o.shipper_id = s.shipper_id
LEFT JOIN order_statuses os
	ON os.order_status_id = o.status
LEFT JOIN customers c
	ON c.customer_id = o.customer_id

Self Outer Joins

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

The USING Clause

USE sql_invoicing;
SELECT date,c.name as client,amount,pm.name as payment
FROM payments p 
JOIN clients c USING (client_id)
JOIN payment_methods pm 
	ON pm.payment_method_id = p.payment_method

Natural Joins

(尽量不要用，让数据库来判断如何连接，容易出现意外)

USE sql_store;
SELECT o.order_id,c.first_name
FROM orders o
NATURAL JOIN customers c

Cross Joins

（用途：一个表存产品的颜色，另一个表存产品的尺寸，想要将所有的型号和所有的颜色组合）

显示

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

隐式

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

Unions

USE sql_store;
SELECT customer_id, first_name, points, 'Gold' as type
FROM customers 
WHERE points>3000
UNION
SELECT customer_id, first_name, points, 'Silver' as type
FROM customers 
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT customer_id, first_name, points, 'Bronze' as type
FROM customers 
WHERE points<2000
ORDER BY first_name

Inserting, Updating, and Deleting Data

Column Attributes

PK 主键
NN 非空
AI 自动增加
VARCHAR 可变长度的字符串，CHAR不可变长度

Inserting Multiple Rows

USE sql_store;
INSERT INTO products(name,quantity_in_stock,unit_price)
VALUES ('Product1',10,1.95),
	   ('Product2',11,1.95),
	   ('Product3',12,1.95)

Inserting Hierarchical Rows

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

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

Creating a Copy of a Table

USE sql_invoicing;
CREATE TABLE invoices_archive AS
SELECT invoice_id, c.name, number, payment_total, invoice_total, invoice_date, due_date, payment_date
FROM invoices i
JOIN clients c USING(client_id)
WHERE payment_date IS NOT NULL

Updating Multiple Rows

Give any customers born before 1990 50 extra points

USE sql_store;
UPDATE customers 
SET points = points + 50
WHERE birth_date < '1990-01-01'

Using Subqueries in Updates

USE sql_store;
UPDATE orders
SET comments = 'Golden customer'
WHERE customer_id IN  (
	SELECT customer_id 
	FROM customers 
	WHERE points > 3000 )

Deleting Rows

USE sql_invoicing;
DELETE FROM invoices
WHERE client_id = (
	SELECT client_id 
	FROM clients
	WHERE name = 'Myworks' )

Summarizing Data

Aggregate Functions

USE sql_invoicing;
SELECT 
	'First half of 2019' AS date_range, 
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payment,
	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_payment,
	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_payment,
	SUM(invoice_total-payment_total) AS what_we_expect
FROM invoices 
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

MAX()
MIN()
AVG()
SUM()
COUNT()

The GROUP BY Clause

USE sql_invoicing;
SELECT p.date, pm.name AS payment_method, SUM(p.amount)
FROM payments p JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
GROUP BY date,p.payment_method
ORDER BY date

The HAVING Clause

WHERE对分组前的数据进行筛选，HAVING对分组后的数据进行筛选

WHERE进行筛选的时候可以选择没有在SELECT中出现的列，HAVING进行筛选只能选择SELECT中出现的列

USE sql_store;
SELECT 
	c.customer_id,
	c.state,
	o.order_id,
	SUM(oi.quantity * oi.unit_price) AS total
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
GROUP BY c.customer_id
HAVING state = 'VA' AND total > 100

The ROLLUP Operator

USE sql_invoicing;
SELECT 
	pm.name AS payment_method,
	SUM(p.amount) AS total
FROM payments p JOIN payment_methods pm 
ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP
ORDER BY total

Writing Complex Query

Subqueries

Find employees whose earn more than average

USE sql_hr;
SELECT * FROM employees
WHERE salary > (
	SELECT AVG(salary) FROM employees )

The IN Operator

Find clients without invoices

USE sql_invoicing;
SELECT *  
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id FROM invoices )

Subqueries vs Joins

返回购买了3号产品的顾客

使用子查询

USE sql_store;
SELECT customer_id, first_name, last_name
FROM customers WHERE customer_id IN (
	SELECT DISTINCT customer_id FROM orders
	WHERE order_id IN (
		SELECT order_id FROM order_items 
		WHERE product_id = 3 )  
)

使用JOIN

USE sql_store;
SELECT DISTINCT c.customer_id, c.first_name, c.last_name 
FROM customers c 
JOIN orders o USING (customer_id) 
JOIN order_items USING (order_id)
	WHERE product_id = 3

The ALL Keyword

(>ALL等同于>MAX())

USE sql_invoicing;
SELECT DISTINCT *
FROM invoices
WHERE invoice_total > ALL (
	SELECT invoice_total
	FROM invoices
	WHERE client_id = 3 )

The ANY Keyword

(=ANY等同于IN)

USE sql_invoicing;
SELECT * 
FROM clients
WHERE client_id = ANY (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >=2 )

Correlated Subqueries

Get invoices that are larger than the client’s average invoice amount

USE sql_invoicing;
SELECT * 
FROM invoices i1
WHERE invoice_total > (
	SELECT AVG(invoice_total) 
	FROM invoices WHERE client_id = i1.client_id )

The EXISTS Operator

(效率更高)

Find the products that have never been ordered

USE sql_store;
SELECT * 
FROM products p
WHERE NOT EXISTS (
	SELECT product_id FROM order_items
    WHERE product_id = p.product_id )

Subqueries in the SELECT Clause

USE sql_invoicing;
SELECT 
	c.client_id, 
    c.name, 
    SUM(invoice_total) AS total_sales,
    (SELECT AVG(invoice_total) FROM invoices) AS average,
     SUM(invoice_total) - (SELECT average) AS difference
FROM clients c
LEFT JOIN invoices i USING (client_id)
GROUP BY c.client_id

另一种写法

USE sql_invoicing;
SELECT 
	client_id, 
	name, 
	(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales, 
	(SELECT AVG(invoice_total) FROM invoices) AS average,
	(SELECT total_sales - average) AS difference
FROM clients c

Subqueries in the FROM Clause

USE sql_invoicing;
SELECT * 
FROM (
	SELECT 
		client_id, 
		name, 
		(SELECT SUM(invoice_total) FROM invoices WHERE client_id = c.client_id) AS total_sales, 
		(SELECT AVG(invoice_total) FROM invoices) AS average,
		(SELECT total_sales - average) AS difference
	FROM clients c
) AS summary
WHERE total_sales is NOT NULL

Essential MySQL Functions

Numeric Functions

ROUND( )
CEILING( )
FLOOR( )
ABS( )
RAND( ) 返回0,1之间的随机数

String Functions

LENGTH( )
UPPER( )
LOWER( )
LTRIM( )
RTRIM( )
TRIM( )
LEFT( )
RIGHT( )
SUBSTRAIN( )
LOCATE( )
REPLACE( )
CONCAT( )

Date Functions in MySQL

NOW( ) 返回当前日期+时间
CURDATE( ) 返回当前日期
CURTIME( ) 返回当前时间
YEAR( ), MONTH( ), DAY( ), HOUR( ), MINUTE( ), SECOND( )
DAYNAME( ) 周几
MONTHNAME( ) 几月
EXTRACT()

SELECT EXTRACT(MINUTE FROM NOW())

USE sql_store;
SELECT * 
FROM orders
WHERE YEAR(order_DATE) >= YEAR(NOW())-1

Formatting Dates and Times

SELECT DATE_FORMAT(NOW(),'%M %D %Y'), DATE_FORMAT(NOW(),'%H: %i %p')

Calculating Dates and Times

加

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)

减

SELECT DATE_SUB(NOW(), INTERVAL 1 DAY)

时间间隔(天)

SELECT DATEDIFF('2019-01-05','2019-01-01')

时间间隔(秒)

SELECT TIME_TO_SEC('09:05') - TIME_TO_SEC('09:00')

The IFNULL and COALESCE Functions

IFNULL替换空值，COALESCE返回提供的值里的第一个非空值

USE sql_store;
SELECT
	order_id,
	IFNULL(shipper_id,'Not assigned') AS shipper
FROM orders

COALESCE

USE sql_store;
SELECT
	order_id,
    COALESCE(shipper_id,comments,'Not assigned') AS shipper
FROM orders

The IF Function

USE sql_store;
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, name

The CASE Operator

USE sql_store;
SELECT 
	CONCAT(first_name,' ',last_name) AS customer,
    points,
	CASE 
		WHEN points > 3000 THEN 'Gold'
		WHEN points BETWEEN 2000 AND 3000 THEN 'silver'
		ELSE 'Bronze'
		END AS category
FROM customers

Views

Creating Views

USE sql_invoicing;
CREATE VIEW balance AS
SELECT 
	i.client_id,
	c.name,
	SUM(invoice_total - payment_total) AS balance
FROM invoices i	
JOIN clients c USING (client_id)
GROUP BY i.client_id, c.name

Altering or Dropping Views

删除视图

DROP VIEW balance

替换视图

USE sql_invoicing;
CREATE OR REPLACE VIEW balance AS
SELECT 
	i.client_id,
	c.name,
	SUM(invoice_total - payment_total) AS balance
FROM invoices i	
JOIN clients c USING (client_id)
GROUP BY i.client_id, c.name

Updatable Views

在以下情形视图也可以用在INSERT,UPDATE,DELETE语句中：

视图中没有DISTINCT关键字
视图中没有任何聚合函数，如MIN,MAX,SUM
视图中没有GROUP BY或HAVING子句
视图中没有UNION操作符

THE WITH OPTION CHECK Clause

WITH CHECK OPTION

如果修改视图的结果会造成记录从视图中消失，则此修改无法实现

Other Benefits of Views

Simplify quaries
Reduce the impact of changes
Restrict access to the data

Stored Procedures

Creating a Stored Procedure

创建存储过程

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
	SELECT * FROM clients;
END $$
DELIMITER ;

调用

call sql_invoicing.get_clients();

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
	SELECT 
		i.client_id, 
		c.name,
		SUM(invoice_total - payment_total) AS balance
	FROM invoices i	
	JOIN clients c USING (client_id)
	GROUP BY i.client_id, c.name 
	HAVING balance > 0 ;
END $$
DELIMITER ;

Dropping Stored Procedures

DROP PROCEDURE IF EXISTS get_clients

Parameters

DELIMITER $$
CREATE PROCEDURE get_invoices_by_client(client_id INT(11))
BEGIN
	SELECT * FROM invoices i
	WHERE i.client_id = client_id ;
END $$
DELIMITER ;

Parameters with Default Value

DELIMITER $$
CREATE PROCEDURE get_payments(client_id INT(4), payment_method_id TINYINT(1))
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 ;

Parameter Validation

DELIMITER $$
CREATE PROCEDURE make_payments(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 ;
	UPDATE invoices i
	SET 
		i.payment_total = payment_amount,
	    i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id ;
END $$
DELIMITER ;

Output Parameters

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(client_id INT, OUT invoices_count INT, OUT invoices_total DECIMAL(9,2))
BEGIN
	SELECT COUNT(*), SUM(invoice_total) 
	INTO invoices_count, invoices_total
	FROM invoices i
	WHERE i.client_id = client_id AND payment_total = 0;
END $$
DELIMITER ;

Variables

DELIMITER $$
CREATE PROCEDURE get_risk_factor()
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;

	SET risk_factor = invoices_total/invoices_count * 5;
	
	SELECT risk_factor;
END $$
DELIMITER ;

Functions

函数与存储过程的区别，函数只能返回一个值，而存储过程可以返回一个结果集

CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_factor_for_client`(client_id INT) RETURNS int(11)
READS SQL DATA
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  IFNULL(risk_factor,0);
END

Triggers and Events

Triggers

触发器就是一个会自动执行的代码块，在插入、更新或删除语句的前后。通常使用触发器来保持数据的连贯性。

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

Viewing Triggers

SHOW TRIGGERS

SHOW TRIGGERS LIKE '%insert%'

Dropping Triggers

DROP TRIGGER IF EXISTS payments_after_insert;

Using Triggers for Auditing

建表

USE sql_invoicing;

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

插入时的触发器

DELIMITER $$
    
    DROP TRIGGER IF EXISTS payments_after_insert;

	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 payment_audit
        VALUES (NEW.client_id, NEW.date, NEW.amount, 'Insert', NOW());
	END $$

	DELIMITER ;

删除时的触发器

DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_delete;

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 payment_audit
    VALUES (OLD.client_id, OLD.date, OLD.amount, 'Delete', NOW());
END $$

DELIMITER ;

Events

Event is a task (or blocks od sql code) that gets excuted according to a schedule.

查看和修改状态

SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON;

创建事件

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
	-- AT '2019-05-01'
	EVERY 1 YEAR STARTS '2021-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payment_audit
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

Viewing, Dropping and Altering Events

查看

SHOW EVENTS LIKE 'yearly%'

删除

DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;

更改

ALTER EVENT yearly_delete_stale_audit_rows ENABLE;

Transactions and Concurrency

Transactions

Transactions are a group of sql statements that represent a single unit of work.

所有的语句必须成功执行，否则事务就会失败

Creating Transactions

USE sql_store;

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;

事务的自动执行由变量autocommit来控制

SHOW VARIABLES LIKE 'autocommit'

Concurrency and Locking

当有多个用户同时对数据库内容进行修改时，只有一个用户的请求执行完毕后，另一个用户的修改才能进行。

Concurrency Problems

丢失更新，可能发生在两个事务同时更新一条数据时后提交的事务会覆盖前面的修改
dirty read 读取到未提交的数据
不可重复读取，两次读取到的内容可能不同(中间有更新改变了数据)
phantom read 丢失符合查询条件的某些行，因为另一个事务修改了数据，但是当前的事务并没有发现这些修改

Transaction Isolation Levels

	Lost Updates	Dirty Reads	Non-repeadting Reads	Phantom Reads
Read Uncommitted
Read Committed		√
Repeatable Read	√	√	√
Serializable	√	√	√	√

Mysql 默认的隔离等级是Repeatable Read

SHOW VARIABLES LIKE 'transaction_isolation'

修改当前会话的隔离等级

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ UNCOMMITTED Isolation Level

第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1;

第二个session

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

当第二个session的COMMIT未执行，同时第一个session的SELECT执行时会发生Dirty Read.

READ COMMITTED Isolation Level

第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1;

第二个session

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

此时不会发生Dirty Read.

但如果第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

当第一个session的第一个SELECT语句执行之后，第二个session执行完毕，然后第一个session的第二个SELECT语句继续执行，会发生Non-repeadting Read.

REPEATABLE READ Isolation Level

第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
COMMIT;

第二个session

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

重复前一节同样的操作，但两次查询会得到同样的结果

第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;

第二个session

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

此时会发生phantom read.

SERIALIZABLE Isolation Level

第一个session

USE sql_store;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
COMMIT;

第二个session

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

第一个session的查询会等到第二个session提交之后才执行，不会发生phantom read.

Deadlocks

第一个session

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

第二个

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

第一个session更新完customers，第二个session开始执行(等待第一个session提交)，此时继续执行第一个session更新orders，发生Deadlock.

如果两个session里的更新语句顺序相反，容易造成Deadlock
尽量保持事务的精简

Data Types

Introduction

String Types
Numeric Types
Data and Time Types
Blod Types
Spatial Types

String Types

CHAR fixed-length
VARCHAR max: 65,535 characters (~64kb)
MEDIUMTEXT max: 16MB
LONGTEXT max: 4GB
TINYTEXT max: 255bytes
TEXT max: 64kb

Integer Types

TINYINT 1b [-128,127]
UNSIGNED TINYINT [0,255]
SMALLINT 2b [-32K,32K]
MEDIUMINT 3b [-8M,8M]
INT 4b [-2B,2B]
BIGINT 8b [-9Z,9Z]

显示的时候会用0来占位：INT(4) => 0001

Use the smallest data type that suits your needs

Fixed-point and Floating-point Types

保存精确值

DECIMAL(p,s)
- DECIMAL(9,2) => 1234567.89

保存近似值

FLOAT 4b
DOUBLE 8b

Boolean Types

BOOL
BOOLEAN

TRUE 和 FALSE 实际上就是 1 和 0

Enum and Set Types

只能插入列表中的内容(可复用性差，修改代价大，尽量避免使用)

ENUM('small','medium','large')

Date and Time Types

DATE 日期数据
TIME 时间数据
DATETIME 日期+时间 8b
TIMESTAMP 4b（up to 2038）
YEAR

Blob Types

TINYBLOB 255b
BLOB 65kb
MEDIUMBLOB 16MB
LONGBLIB 4GB

JSON Type

JSON
{
“key”:value
}

ALTER TABLE `sql_store`.`products` 
ADD COLUMN `properties` JSON NULL AFTER `unit_price`;

插入

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

另一种方式插入

USE sql_store;
UPDATE products
SET properties = JSON_OBJECT(
	'weight', 10,
	'dimensions', JSON_ARRAY(1,2,3),
	'manufacturer', JSON_OBJECT('name','sony')
)
WHERE product_id = 2;

查询

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

另一种方式查询

USE sql_store;
SELECT product_id,
	properties -> '$.dimensions[0]' len,
	properties ->> '$.manufacturer.name' manu
FROM products
WHERE properties ->> '$.manufacturer.name' = 'sony';

更新

USE sql_store;
UPDATE products
SET properties = JSON_SET(
	properties,
	'$.weight', 20,
	'$.age', 10
)
WHERE product_id = 1;

删除

USE sql_store;
UPDATE products
SET properties = JSON_REMOVE(
	properties,
	'$.age'
)
WHERE product_id = 1;

Designing Databases

Data Modelling

Understand the requirements
Build a Conceptual Model
包括识别业务中的实体、事物以及他们之间的关系
Build a Logical Model
独立于数据技术的抽象数据模型
Build a Physical Model
围绕特定的数据库技术的逻辑模型的实现

Conceptual Models

Conceptual Model represents the entities and their relationships.

Logical Models

Tips：尽量拆分成较小的属性，方便根据不同的需求进行查询和排序。

Relationships

One-to-one
One-to-many
Many-to-many

Physical Models

编辑实体模型 File→New Model→mydb→Edit Schema
添加新表

Tips：表名的单数和复数最好保持一致

Primary Keys

一般为INT类型，自动递增

Foreign Keys

在这里插入图片描述

Foreign Key Constraints

在这里插入图片描述

对于Foreign Key一般情况下，更新时级联，删除时视情况而定

Normalization

标准化是审查设计是否遵循预定义的防止数据重复的规则的过程
7范式，最常用的是前3条

1NF- First Normal Form

第一范式要求一行中的每个单元格都有单一值，且不能出现重复列

Link Tables

把多对多拆解成两个多对一

2NF- Second Normal Form

第二范式要求每张表都应有一个单一目的，即它应该只能代表一种且仅有一种实体类型，并且表中的每一列都应该用来描述那个实体

3NF- Third Normal Form

第三范式要求，表中的列不应派生自其他列

My Pragmatic Advice

在实际应用中，主要集中于消除冗余

Don’t Model the Universe

只需要为当下的问题制定最佳的解决方案，不需要考虑未来不会发生的改变

Forward Engineering a Model

如何根据模型生成实体模型
在这里插入图片描述

Synchronizing a Model with a Database

如何将模型的改变同步到实体模型
在这里插入图片描述

Reverse Engineering a Database

如何根据实体模型生成模型（ER图表示的那种）

在这里插入图片描述

Creating and Dropping Databases

创建数据库

create database if not exists sql_store2;

删除数据库

drop database if exists sql_store2;

Creating Tables

create database if not exists sql_store2;
use sql_store2;
drop table if exists customers;
create table if not exists 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
);

Altering Tables

use sql_store2;
alter table customers
	add last_name varchar(50) not null after first_name,
    add city      varchar(50) not null,
    modify column first_name varchar(55) default '',
    drop points
    ;

Creating Relationships

create database if not exists sql_store2;
use sql_store2;
drop table if exists orders;
drop table if exists customers;
create table if not exists 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,
    customer_id  int not null,
    foreign key fk_orders_customers (customer_id)
		references customers (customer_id)
        on update cascade
        on delete no action
);

Altering Primary and Foreign Key Constraints

use sql_store2;
alter table customers
	add last_name varchar(50) not null after first_name,
    add city      varchar(50) not null,
    modify column first_name varchar(55) default '',
    drop points
    ;
    
alter table orders
	add primary key (order_id),
	drop primary key,
	drop foreign key fk_orders_customers
;

alter table orders
	add foreign key fk_orders_customers (customer_id)
		references customers (customer_id)
        on update cascade
        on delete no action
;

Character Sets and Collations

查看sql支持的字符集

show charset;

更改默认字符集的大小
在这里插入图片描述

或

Storage Engines

查看当前版本mysql支持的所有储存引擎

show engines;

修改储存引擎
在这里插入图片描述
或

Indexing for High Performance

索引可以加快查询的速度

Indexes

为了加快运行较慢的查询
不要在表上盲目添加索引
索引内部通常储存为二叉树结构

Creating Indexes

当执行查询时

use sql_store;
explain select customer_id from customers where state = 'CA';

在这里插入图片描述

创建索引之后再执行查询

use sql_store;
create index idx_state on customers (state);
explain select customer_id from customers where state = 'CA';

在这里插入图片描述

Exercse：查找积分大于1000的顾客

直接查询

use sql_store;
explain select customer_id from customers where points > 1000;

创建索引之后执行查询

use sql_store;
create index idx_points on customers (points);
explain select customer_id from customers where points > 1000;

Viewing Indexes

查看索引列表

use sql_store;
show indexes in customers;

在这里插入图片描述

show indexes in orders;

在这里插入图片描述

mysql 会自动为外键创建索引

Prefix Indexes

当索引为字符串类型时，只包含前几个字符或列前缀的索引消耗的储存空间更小

查看last_name的前5个字符可以将顾客分成多少组（可以由此确定索引的字符数取多少合适）

select count(distinct left(last_name,5)) from customers;

将last_name的前5个字符作为索引

create index idx_lastname on customers (last_name(5));

Full-text Indexes

可以实现类似搜索引擎的功能
本质上，全文索引存储了一套单词列表，并且对于每个单词，存储了这些单词会出现的行或列
mysql 会基于若干因素，为包含搜素短语的每一行计算相关性得分

例如，查找关于 react redux 相关的文章

use sql_blog;
select * from posts 
where title like '%react redux%' or body like '%react redux%';

采用全文索引

create fulltext index idx_title_body on posts (title,body);
select * from posts 
where match(title,body) against('react redux');

查看”相关性得分“

select *, match(title,body) against('react redux') from posts 
where match(title,body) against('react redux');

在这里插入图片描述
指定每行不包含与包含指定的关键词

select * from posts 
where match(title,body) against('react -redux + form' in boolean mode);

精确匹配某个短语

select * from posts 
where match(title,body) against('"handling a form"' in boolean mode);

Composite Indexes

例如，查找坐标位于加州，且积分大于1000的顾客

use sql_store;
explain select customer_id from customers 
where state = 'CA' and points > 1000;

在这里插入图片描述
虽然有多个索引，但mysql最多只会选择1个索引。

复合索引允许对多列建立索引（mysql中，一个索引中最多可以包含16列）

create index idx_state_points on customers (state,points);
explain select customer_id from customers 
where state = 'CA' and points > 1000;

在这里插入图片描述

删除索引

drop index idx_state on customers;

Order of Columns in Composite Indexes

将频繁使用的列排在前面

将基数 (索引中唯一值的数量) 更高的列排在前面

create index idx_last_name_state on customers (last_name,state);
create index idx_state_last_name on customers (state,last_name);

explain select customer_id from customers
use index (idx_last_name_state)
where state = 'CA' and last_name like 'A%';

在这里插入图片描述

explain select customer_id from customers
use index (idx_state_last_name)
where state = 'CA' and last_name like 'A%';

在这里插入图片描述

When Indexes are Ignored

在某些场景下，索引可能失效，例如

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

在这里插入图片描述

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

在这里插入图片描述

只要在表达式中用到了列，mysql就无法以最优的方式利用索引

explain select customer_id from customers where points + 10 > 2010;

在这里插入图片描述

explain select customer_id from customers where points  > 2000;

在这里插入图片描述

Using Indexes for Sorting

对没有索引的列进行排序，并查看查询成本

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

在这里插入图片描述

对有索引的列进行排序，并查看查询成本

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

在这里插入图片描述

若存在索引 (a,b)，则以下排序都可利用索引：

a,b
a
a desc, b desc

以下排序不能利用索引：

a,c,b
b
a, b desc
a desc, b

Covering Indexese

覆盖索引：一个包含所有满足查询需要的数据的索引。通过这个索引，mysql可以在不读取表的情况下执行查询（查询速度最快）
若一个查询中 select，where，order by 等使用的列都包含在索引中，则该索引就是一个覆盖索引

Index Maintenance

在创建新索引之前，查看现有索引
确保删除重复、多余和未使用的索引
- 重复索引是同一组列上且顺序一致的索引，如ABC和ABC
- 多余索引指定义不同，但实现功能和其他索引重复的索引，如在已经存在索引AB的情况下，创建新索引A

Securing Databases

Creating a User

创建新用户

create user john identified by 'johnspy';

用户名后+@+ip/域名等，可以对登录环境进行限制

Viewing Users

在这里插入图片描述
或使用命令

select * from mysql.user;

在这里插入图片描述

Dropping Users

create user bob@abc.com identified by '1234';
drop user bob@abc.com;

Changing Passwords

在这里插入图片描述

或者使用命令

set password for john = '1234';

若没写出更改密码的用户名，则默认对当前登录的用户的密码进行更改。

Granting Privileges

-- 1: web/desktop application
create user moon_app identified by '1234';
grant select, insert, update, delete, execute
on sql_store.* 
to moon_app;

-- 2: admin
grant all
on *.*
to john;

Viewing Privileges

在这里插入图片描述

或使用命令

show grants for john;

Revoking Privileges

grant create view 
on sql_store.* 
to moon_app;

撤销权限

revoke create view 
on sql_store.* 
from moon_app;

sosososoon

关注

0
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录