xy's blog

Connect the dots

0%

sql Notes

Posted on Edited on
Symbols count in article: 18k Reading time ≈ 15 mins.

Complete SQL Mastery | Code with Mosh

1. Introduction

SQL database

Relational DBMS: table and relationship

NoSQL

sql or MySequal

2. 在单一表中检索数据：

SELECT

1
2
3
4
5
USE sql_store; -- 或者双击选中
SELECT * -- return all columns
FROM customers;

SELECT first_name, last_name FROM customers;

WHERE

1
2
3
4
5
6
7
8
SELECT * FROM customers WHERE customer_id=1 ORDER BY first_name; -- order of each clause matters
SELECT 
	first_name, 
	points,
    (points+10)*100 AS 'discount factor'
FROM customers;
SELECT DISTINCT state FROM customers; -- 不重复
SELECT DISTINCT state FROM customers WHERE state!='va'; -- 大小写不敏感

AND, OR, NOT

1
2
3
-- 优先级 and or
-- WHERE NOT (birth_date >'1990-01-01' OR points <=1000) 依次取反
-- WHERE birth_date <='1990-01-01' AND points > 1000

IN

1
SELECT * FROM custormers WHERE state IN ('VA','FL','GA'); -- 一个属性与列表比较

BETWEEN

1
SELECT * FROM customers WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'; -- 包含两端

LIKE 模糊查找

1
2
3
4
5
SELECT * FROM customers WHERE last_name LIKE 'b%'; 
'%b%'
'b____y'
SELECT * FROM customers WHERE address LIKE '%trail%' OR address LIKE '%AVENUE%';
SELECT * FROM customers WHERE phone NOT LIKE '%9'

% : any character (zero or more)

_: one character

REGEXP 通配符

1
2
3
4
SELECT * FROM customers WHERE last_name REGEXP 'field'
'field$|mac|ch'
'[gim]e' ge,ie,me
'[a-h]e'

^: begin with

$: end with

|: logical or/ multiple search pattern

[ ]: single character in

IS NULL

1
SELECT * FROM orders WHERE shipped_date IS NULL; -- is NOT NULL

primary key: unique and

ORDER BY 排序

sql可以用任意列排序，不管是否选中了该列

1
SELECT *, unit_price*quantity as total_price FROM order_items WHERE order_id=2 ORDER BY total_price DESC;

LIMIT

1
2
3
SELECT * FROM customers LIMIT 300;
SELECT * FROM customers LIMIT 6, 3;-- offset, num to show; show 7.8.9
SELECT * FROM customers ORDER BY points DESC LIMIT 3; -- LIMIT 顺序永远在最后

3. 在多个表中检索数据

Inner Joins

1
2
3
4
5
SELECT * FROM orders JOIN customers ON orders.customer_id=customers.customer_id; -- 默认inner join

SELECT order_id, o.customer_id, first_name, last_name
 FROM orders o
 JOIN customers ON o.customer_id=customers.customer_id; -- use alias to simplify

Joining across database

1
2
SELECT * FROM order_items oi
JOIN sql_inventory.products p ON oi. product_id=p.product_id; -- 跨库检索

Self Join

1
2
3
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; -- self join

Joining multiple tables

1
2
3
4
SELECT p.date, p.invoice_id, p.amount, c.name as client_name, pm.name as payment_method
FROM payments p
JOIN clients c ON p.client_id=c.client_id
JOIN payment_methods pm ON p.payment_method=pm.payment_method_id;

Compound join conditions

复合主键 composite primary key: 包含多个主键

1
JOIN ... ON ... AND...

Implicit JOIN

用explicit比较好！

1
2
3
4
5
6
7
SELECT * FROM orders 
JOIN customers ON orders.customer_id=customers.customer_id; 

-- implicit JOIN
SELECT *
FROM orders o, customers c
WHERE o.customer_id=c.customer_id;

Outer Joins

LEFT JOIN: 无论是否匹配都返回左表的记录

1
2
3
4
SELECT p.product_id, p.name, oi.quantity
FROM products p
LEFT JOIN order_items oi 
ON p.product_id=oi.product_id; -- outer join

outer join between multiple tables

1
2
3
4
5
6
7
8
SELECT o.order_date, o.order_id, c.first_name, s.name as shipper, os.name as status
FROM orders o
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

Self Outer JOIN

Using Clause

如果列名一致

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM orders o
JOIN customers c
	USING (customer_id);
	-- ON o.customer_id=c.customer_id

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

Natural Join

1
2
3
SELECT *
FROM orders o
NATURAL JOIN customers c; -- 自动匹配相同列；不建议使用

Cross Join

结果集将包括两个表中的所有行，其中结果集中的每一行都是第一个表中的行与第二个表中的行的组合。 当连接的表之间没有关系时，会使用这种情况。例如color和size组合

1
2
3
4
5
6
7
8
9
10
-- CROSS JOIN
-- explicit
SELECT sh.name AS shipper, p.name AS product
FROM shippers sh
CROSS JOIN products p;

-- implicit
SELECT sh.name AS shipper, p.name AS product
FROM shippers sh
ORDER BY sh.name

Unions

组合查询结果，注意列数要一致

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT 
	customer_id, first_name, points,
	'Bronze' AS type
FROM customers
WHERE points<2000
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,
	'Gold' AS type
FROM customers
WHERE points>=3000
ORDER BY first_name

4.增删改

NN: not null

PK: primary key

varchar: 长度可变的字符

char：如果字符长度不足，会自动填充空格

AI: auto increment

Default: default value

Inserting a row

提前设置默认值 （e.g. null)

1
2
INSERT INTO customers VALUES (DEFAULT,'John',NULL,'CA');
INSERT INTO customers (name,state) VALUES ('John','CA');

一次插入多行数据

1
INSERT INTO shippers (name) VALUES ('Shipper1') ('Shipper2'),('Shipper3');

Insert data to multiple tables/ hierarchical data

last_insert_id

1
2
INSERT INTO orders (customer_id,order_date,status) VALUES (1,'2021-1-1',1);
INSERT INTO order_items VALUES (LAST_INSERT_ID(),1,2,2.5);

CREATE a copy of a Table

1
2
CREATE TABLE orders_achived AS
SELECT * FROM orders;

子查询语句 subquery

1
2
3
4
5
CREATE TABLE invoices_achived AS
SELECT i.invoice_id, i.number, c.name, i.invoice_total, i.payment_total, i.invoice_date, i.due_date, i.payment_date 
FROM invoices i
JOIN clients c ON c.client_id=i.client_id
WHERE NOT (payment_date IS NULL) ;

Update

  1. update single row

    1
    2
    3
    UPDATE invoices
    SET payment_total=100
    WHERE invoice_id=6;

  2. update multiple row

    1
    2
    3
    UPDATE customers
    SET points=points+50
    WHERE birth_date<'1990-01-01';

  3. subquery, 多条件用in，单条件用=

    1
    2
    3
    4
    5
    6
    UPDATE orders
    SET comments='Gold customer'
    WHERE customer_id IN 
    		(SELECT customer_id
            FROM customers
    		WHERE points>3000);

Delete

1
2
3
4
5
6
DELETE FROM invoices
WHERE client_id=(
			SELECT client_id 
			FROM clients 
            WHERE name='Myworks'
)

5.聚合函数

1
2
3
4
5
6
7
8
9
10
11
MAX()
MIN()
AVG()
SUM()
COUNT() --作用于所有非空记录
SELECT 
	MAX(invoice_total) AS highest
	COUNT(*) AS total_records --所有记录
	COUNT(DISTINCT client_id) AS total_clients
FROM invoices
WHERE invoice_date<'2019-07-01'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
	'Fist half of 2019' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total) - SUM(payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date<'2019-07-01'
UNION
SELECT 
	'Second half of 2019' AS date_range,
	SUM(invoice_total) AS total_sales,
	SUM(payment_total) AS total_payments,
	SUM(invoice_total- payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-07-01';

Group By

1
2
3
4
5
6
7
8
9
SELECT 
	p.date,
	pm.name AS payment_method,
	SUM(p.amount) AS total_payments
FROM payments p
JOIN payment_methods pm
	ON p.payment_method=pm.payment_method_id
GROUP BY p.date, payment_method
ORDER BY p.date;

Having

1
2
3
4
5
6
7
8
-- having 在group by后筛选，用到的列必须是select中选中的列, where用到的列可以是任意列
SELECT 
client_id,
SUM(invoice_total) AS total_sales,
COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales >500 AND number_of_invoices >5;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
	c.customer_id,
	c.first_name,
	-- oi.order_id,
    SUM(oi.quantity*oi.unit_price) AS total_sales
FROM orders o
JOIN order_items oi
	USING (order_id)
JOIN customers c
	USING (customer_id)
WHERE c.state='VA'
GROUP BY -- 使用了聚合函数，group by要包含所有field
	c.customer_id,
	c.first_name
HAVING total_sales>100;

With Rollup

1
2
3
4
5
6
7
8
9
-- with rollup 汇总
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 -- 有with rollup的话不能用别名
ORDER BY total;

6.复杂查询

子查询

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 子查询返回多个值
USE sql_invoicing;
SELECT * 
FROM clients
WHERE client_id NOT IN (
                SELECT DISTINCT client_id
				FROM invoices
);

用join代替子查询，考虑可读性和性能

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- subquery
USE sql_store;
SELECT customer_id,first_name,last_name
FROM customers c
WHERE customer_id IN (
		SELECT DISTINCT customer_id 
        FROM order_items oi
        JOIN orders o
        USING (ORDER_ID)
        WHERE product_id=3
                );

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

ALL, ANY, SOME
— > ALL() 相当于 > MAX()
— IN () 相当于 = ANY()

CORRELATED SUB-QUERY

1
2
3
4
5
6
7
8
-- CORRELATED SUB-QUERY 在主查询的每条记录都进行一次相关子查询
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total> (
		SELECT AVG(invoice_total)
		FROM invoices
		WHERE client_id=i.client_id);

Exists

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- exists 不将所有结果返回外部查询，避免返回大 结果集，适合多结果的集合
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
	FROM invoices
	WHERE client_id=c.client_id
    );
    
USE sql_store;
SELECT *
FROM products p
WHERE NOT EXISTS (
		SELECT product_id
		FROM order_items
		WHERE product_id=p.product_id
        );

在select中使用子查询

1
2
3
4
5
6
7
-- 复杂查询,在select中使用子查询
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) -(SELECT average) AS difference
FROM clients c;

在from中使用子查询

需要增加别名
FROM () AS

7.基础内建函数

  • ROUND()
  • TRUNCATE()
  • EILING() — 大于等于这个数的最小整数
  • FLOOR() — 小于等于这个数的最小整数
  • ABS()
  • RAND()

String functions

  • LENGTH()
  • UPPER()
  • LOWER()
  • LTRIM() —LEFT TRIM
  • RTRIM()

  • TRIM()

  • LEFT()

  • RIGHT()
  • SUBSTRING()

  • LOACTE(‘n’,’kindergarten’) — not case sensitive

  • REPLACE()

  • CONCATE()

Time/Date

NOW()
CURDATE()
CURTIME()
YEAR()
MONTH() — INTERGER
MONTHNAME() — STRING

EXTRACT (YEAR FROM NOW())
DATE_FORMAT(NOW(),’&M &Y’)
TIME_FORMAT(NOW(),’%h:%i %p’)

DATE_ADD(NOW(),INTERVAL 1 YEAR)
DATE_ADD(NOW(),INTERVAL -1 YEAR)
DATE_SUB(NOW(),INTERVAL 1 YEAR)

DATEDIFF(‘2019-01-01’,’2020-01-01’)
TIME_TO_SEC()

IFNULL(shipper_id,’not assigned’) — 将null替换为其他值
COALESCE() — 返回第一个不为null的值

IF

1
2
3
4
5
6
7
8
9
10
-- if()
SELECT 
	oi.product_id, 
	p.name,
	count(*) AS orders,
    IF(COUNT(*)=1,'Once','Many times') AS frequency
FROM order_items oi
JOIN products p
USING (product_id)
GROUP BY oi.product_id, p.name

case when… then…

1
2
3
4
5
6
7
8
9
10
-- case when ... then ...
SELECT CONCAT(first_name,' ',last_name) AS customer,
		points,
		CASE
			WHEN points>3000 THEN 'Gold'
			WHEN points BETWEEN 2000 AND 3000 THEN 'silver'
			WHEN points<2000 THEN 'bronze'
		END AS category
FROM customers
ORDER BY points DESC;

8.视图

创建视图

跟表格一样使用

1
2
3
4
5
6
7
8
CREATE VIEW clients_balance AS
SELECT i.client_id AS client_id,
		c.name AS name,
		SUM(i.invoice_total-i.payment_total) AS balance
FROM invoices i
JOIN clients c
		USING (client_id)
GROUP BY client_id;

updatable view:

  • Distinct
  • sum, min, max
  • group by
  • union

views的好处

  • 简化查询

  • 减少修改带来的影响（例如修改原表的列名后，可以通过view修改，这样后续查询不受影响-类似函数

  • 防止对源数据的访问

不要在存储过程中加入太多逻辑验证，难以管理。在程序中验证输入。

函数只能返回单一值

函数特性：

deterministic
reads sql data
modifies sql data

9.存储过程

create/call/drop

1
2
3
4
5
6
7
8
9
10
11
12
DROP PROCEDURE IF EXISTS get_payments;
DELIMITER $$ -- 修改分隔符
USE `sql_invoicing`$$
CREATE PROCEDURE `get_payments`()
BEGIN
	select * from payments;
END$$
DELIMITER ; -- 还原分隔符
-- 或者右键create procedure

-- call调用
call get_payments;

输入参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 输入参数
DROP PROCEDURE IF EXISTS get_clients_by_state;
DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
	state char(2)
)
begin
	select * from clients c
	where c.state=state;
end$$
DELIMITER ;


-- 判断参数
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_payments`(	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_id); -- 如果输入参数为null，则返回所有
END$$
DELIMITER ;

-- 输出错误代码
DELIMITER $$
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 MESAAGE_TEXT='Invalid Payment amount';
    END IF;
	UPDATE invoices i
    SET
		 i.payment_total=payment_amount,
		 i.payment_date=payment_date
     WHERE
		invoice_id=invoice_id;
END$$
DELIMITER ;

-- 参数前加out关键字，select into，使用输出参数读取数据

variable

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- User or session variable，客户端与数据库中断时，内存释放
Set @invoices_count=0; -- 存在内存中的变量
-- local varible (inside procedure) 存储过程结束时，内存释放
DELIMITER $$
CREATE DEFINER=`root`@`localhost` 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 ;

Function

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `get_risk_fact`(client_id INT) RETURNS INT
    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$$
DELIMITER ;

调用function：select

1
2
3
4
5
6
7
SELECT
	client_id,
    name,
    get_risk_factor_for_client(client_id) as risk_factor
FROM clients;

DROP FUNCTION IF EXISTS get_risk_factor_for_client;

10.触发和事件

Trigger

确保数据更新:满足条件时触发，并执行语句（insert/update/delete

-OLD

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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; -- 删除触发器，old.columnname; new.columnname 新增行的某列数据
 
 INSERT INTO payments_audit
 VALUES (OLD.client_id,OLD.date,OLD.amount,'Delete',now()); -- wrting logs
END $$
DELIMITER ;
1
2
SHOW TRIGGERS;
SHOW TRIGGERS LIKE 'payments%';

Event

时间触发器，定时执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- create
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 '2019-01-01' ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
    WHERE action_date<NOW()-INTERVAL 1 YEAR;
END$$
delimiter ;

-- view and drop
SHOW EVENTS;
SHOW EVENTS LIKE 'yearly%'; -- 命名时写明时间间隔

-- Alter event
ALTER EVENT yearly_delete_stale_audit_rows DISABLE;
ALTER EVENT yearly_delete_stale_audit_rows ENABLE;

11.Transaction

create

都执行/都不执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Transaction
USE sql_store;
START TRANSACTION;
INSERT INTO orders (customer_id, order_date,status)
VALUES(1,'2019-05-01',1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,1);
COMMIT;

SHOW VARIABLES LIKE 'autocommit'; -- 除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每个查询都被当做一个单独的事务自动执行

-- set isolation level
SHOW VARIABLES LIKE 'transaction_isolation';
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

transaction条件 ACID

  • Atomicity: 所有语句都成功执行才结束，否则回滚

  • Consistency

  • Isolation：如果多个事务修改同一个数据，记录会锁定，只有结束一个事务才会开始下一个

  • Durability：事务提交后，修改是永久的

concurrency and locking

默认锁定机制：两个transaction同时修改一个数据，会锁定，直到事务提交或回滚

并发出现的问题

  1. lost updates：两个事务同时更新一个数据，后提交的事务会覆盖前面的修改

  2. dirty reads: read uncommitted data from other transaction 读取到未提交的数据

  • read committed

  1. Non-repeating Reads：两次读取不一样

  2. phantom reads 幻读-其他事务修改数据影响当前查询，但当前查询没有读取到

    image-20210124150008961

    序列化 serializable 牺牲性能和扩展性

isolation levels of transaction

isolation level Lost Updates Dirty Reads Non-repeating Reads Phantom Reads
read uncommitted
read committed √
repeatable reads √ √ √
serializable √ √ √ √

高等级的隔离等级会减少更多并发问题，速度快，但牺牲性能和扩展性

sql默认的是repeatable reads

deadlock：两个事务相互影响

12. Data types

Strings

  • char(x)

  • varchar(x):可变长度

    • varchar (50): name password
    • varchar(255):

  • mediumtext max:16MB

  • longtext max:4GB

intergers

  • tinyint 1b [-128,127]: age
  • unsigned tinyint [0,255] 正值

zerofill

int(4):0001

rationals

decimal(p,s) 总位数，小数位

科学计算中特别大，特别小的数

float 4b

double 8b

booleans

bool

boolean

enum

枚举，可重复性差

enum(‘small’,’medium’,’large’)

date/time

timestamp 4b (up to 2038)

BLOBS

Binary Large Objects,即大型二进制对象

JSON type

json_object()

json_array()

1
2
3
select product_id,json_extract(propertites,'$.weight') as weight
from products
where product_id=1

$当前路径

. 访问路径

列路径操作符->

properties->’$.manufacturer.name’

json_set

json_remove

13.数据库设计

data modelling

  1. understand the requirements

  2. conceptual model

  3. logical model

  4. physical model

conceptual model

entities and their relationship

entities relationship (ER) or UML

Tools: visio, draw.io, lucidCharts

logical model

type of attribute, 独立于数据库系统

relationship: one to one, one to many, many to many

physical model

联合主键

Notnull, auto-increment

set foreign keys constraints: on update/on delete - set null, restrict, no action, cascade

normalization

1st normal form 第一范式

多对多关系：建立中间连接表

第二范式：没有依赖任何关系的其他子集的非主键字段；每个表只能代表一个实体entity，且所有列均为该实体的属性；

第三范式：all the attributes should be determined by the prime

a column in a table should not be derived from other columns

remove redundancy

Don’t jump into creating tables

Don’t model the universe!

不要为了不存在的未来需求将设计复杂化

forward engineer

create database/table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(15) NOT NULL,
    points INT NOT NULL DEFAULT 0,
    email VARCHAR(255) NOT NULL UNIQUE
);

-- alter
ALTER TABLE customers
	ADD last_name 	VARCHAR(50) NOT NULL AFTER first_name,
	ADD city 		VARCHAR(50) NOT NULL,
	MODIFY COLUMN first_name VARCHAR(50) DEFAULT '',
	DROP points;
    
DROP TABLE IF EXISTS orders;
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
);-- primary key 默认not null

-- character set
ALTER table table1
CHARACTER SET latin1;

14.Indexing for high performance

indexes are usually stored as binary tree

creating indexes

1
2
3
4
5
CREATE INDEX idx_state ON customers (state);
DROP INDEX idx_state ON customers;

SHOW INDEXES IN orders;
EXPLAIN SELECT customer_id FROM customers WHERE state='CA' AND points>1000; -- rows scanned

string

前缀索引

1
2
3
4
5
6
7
-- prefix indexes for string
CREATE INDEX idx_lastname ON customers (last_name(5));
-- 选择索引长度
SELECT COUNT(DISTINCT LEFT(last_name,1)),
 COUNT(DISTINCT LEFT(last_name,5)),
 COUNT(DISTINCT LEFT(last_name,10))
FROM customers;

为全文建立索引

1
2
3
4
5
6
7
-- full-text indexes /long articles
CREATE FULLTEXT INDEX idx_title_body ON posts (title,body);

SELECT *, MATCH (title, body) AGAINST ('react redux')
FROM posts
WHERE MATCH (title, body) AGAINST ('react redux');
-- MATCH (title, body): relevance score

联合索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- composite index
CREATE INDEX idx_state_points ON customers (state,points);
-- order of column: frequently-used column, high cardinality, take your queries into account

-- 指定index
EXPLAIN SELECT customer_id FROM customers USE INDEX (ID_STATE_POINTS) WHERE state='CA' AND points>1000; -- rows scanned

-- when indexes are ignored
-- select or/ 建立index, then union
CREATE INDEX idx_points ON customers (points);
explain select customer_id from customers
where points+10>2010; -- 扫描索引的所有记录 full index scan
explain select customer_id from customers
where points>2000; -- 单独写索引

using indexes for sorting

建立组合索引(a,b)，排序

使用索引排序：

a

a,b

a DESC, b DESC

a,c,b —全表扫描

b —全表扫描

While creating secondary index，sql will include primary key

如果只选择索引内的列(covering index)，则索引扫描；否则全表扫描

index maintaining

  • duplicate indexes

  • redundant indexes: (A,B) (A) x

check existing indexes before creating

15. Users and privilege

Creating a user

1
2
3
4
5
6
7
8
9
10
11
-- creating users
CREATE USER john@localhost;-- host/domain name/ip address
CREATE USER john@'%.localhost'; -- sub domain
CREATE USER john IDENTIFIED BY '1234';-- can connect anywhere, password

-- viewing users
select * from sql.user;
drop user bob@codewithmosh.com;

set password for john ='1234';
set password ='1234';

Privilege

1
2
3
4
5
6
7
8
9
10
11
12
-- granting previleges
-- 1: web/desktop application
create user moon_app identified by '1234';
grant select, insert, update, delete, execute
on sql_store.*
to moon_app; -- also specify host, ip address, domain
-- 2: admin
grant all on *.* to john;

-- viewing
show grants for john;
revoke create view on sql_strore.* from moon_app;