笔记之路，3 小时从零学会 MySQL

下载 & 安装

配合该文的数据库资源
MySQL 官网
MySQL 下载
MySQL Community Server 8.0.19
MySQL Community Server 8.0.19 for Windows
windows 下安装基本就是 next next，输个密码就完了。。

1. Creating the Databases

。。。

2. The SELECT Statement

选择 sql_store，左侧 schemas 中该 database 就变成粗体了。

USE sql_store;

选择哪些列查询

SELECT *
SELECT customer_id, first_name
-- 这是注释

选择要查询的表

FROM customers

添加限制

WHERE customer_id = 1

排序

ORDER BY first_name

不能更改上述语句的顺序，否则语法错误。

3. The SELECT Clause

可以对列做计算，AS 起别名

SELECT customer_id, first_name, points,
       points + 10 AS 'new_col name'

DISTINCT 去掉重复项

SELECT DISTINCT state FROM customers

4. The WHERE Clause

与 C 语言不同的地方在于判断相等是使用 “=” 而不是 “==”，
不等于可以是 “!=” 或 “<>”。
字符串的值应该使用单引号或双引号括起来

select *
from customers
where points > 3000

日期的标准格式

select *
from customers
where birth_date > '1990-01-01'

4.1 The AND, OR and NOT Operators

优先级：( ) > NOT > AND > OR

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

4.2 The IN Operator

SELECT * FROM sql_store.customers
WHERE state = 'VA' OR state = 'FL' OR state = 'TX'

等价于

SELECT * FROM sql_store.customers
WHERE state IN ('VA', 'FL', 'TX')

取反

SELECT * FROM sql_store.customers
WHERE state NOT IN ('VA', 'FL', 'TX')

4.3 The BETWEEN Operator

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

等价于

SELECT * FROM sql_store.customers
WHERE points BETWEEN 1000 AND 3000

字符串也是通用的

SELECT * FROM sql_store.customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

4.3 The LIKE Operator

last_name 以 b 开头的记录

SELECT * FROM sql_store.customers
WHERE last_name LIKE 'b%'

phone 不以 9 结尾的记录

SELECT * FROM sql_store.customers
WHERE phone NOT LIKE '%9'

“%” （百分号）匹配任意字符，不限制数量
“_” （下划线）匹配一个任意字符

4.4 The REGEXP Operator

SELECT * FROM sql_store.customers
WHERE last_name REGEXP 'field'

正则 90% 的情况下只需要使用下面这几个：

^  : beginning
$  : end
|  : logical or
[aged] : 匹配 a g e d 中的一个
[b-f]  : 匹配 b-f 中的一个

4.5 The IS NULL Operator

SELECT * FROM sql_store.customers
WHERE phone IS NULL

SELECT * FROM sql_store.customers
WHERE phone IS NOT NULL

5. The ORDER BY Clause

降序排列

SELECT * FROM sql_store.customers
ORDER BY first_name DESC

SELECT * FROM sql_store.customers
ORDER BY state DESC, first_name DESC

使用序号排列，对 SELECT 出的第 1 列降序排列后，再对第二列升序排列

SELECT * FROM sql_store.customers
ORDER BY 1 DESC, 2

还可以对 AS 后的别名进行排序，就不演示了。

6. The LIMIT Clause

只获取前 3 个

SELECT * FROM sql_store.customers
LIMIT 3

拥有偏移量，跳过前 6 个后的前 3 个

SELECT * FROM sql_store.customers
LIMIT 6, 3

7. Joins

7.1 Inner Joins

通过 customer_id 相同的条件把 orders 表和 customers 表连接在一起

select order_id, customers.customer_id, first_name, last_name
from orders
-- 可以省略 INNER
INNER JOIN customers 
	ON orders.customer_id = customers.customer_id

取别名后必须使用别名，否则报错

select order_id, oi.product_id, quantity, p.unit_price
from order_items oi
JOIN products p 
	ON oi.product_id = p.product_id

7.2 Joining Across Databases

sql_inventory 为另一个 Databases

SELECT *
FROM order_items oi
JOIN sql_inventory.products p
	ON oi.product_id = p.product_id

7.3 Self Joins

自己和自己连接，可以找到类似员工的管理者的情况，因垂丝汀

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

7.4 Joining Multiple Tables

USE sql_store;
SELECT order_id, c.first_name, os.name
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
	ON o.status = os.order_status_id

7.5 Compound Join Conditions

表的 order_id 和 product_id 都是主键

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

7.6 Implicit Join Syntax

SELECT *
FROM orders o
JOIN customers c
 	ON o.customer_id = c.customer_id

等价于

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

7.7 Outer Joins

LEFT JOIN 保持左边表 (customers) 的信息，不管是否满足 ON 的条件，都会输出

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id

如果 LEFT 改为 RIGHT ，保持右边表 (orders) 的信息，不管是否满足 ON 的条件，都会输出

7.8 Outer Joins Between Multiple Tables

SELECT 
	c.customer_id,
    c.first_name,
    o.order_id,
    sh.shipper_id
FROM customers c
LEFT JOIN orders o
	ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
	ON o.shipper_id = sh.shipper_id

7.9 Self Outer Joins

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

7.10 The USING Clause

列名一样的时候可以把 ON 改为 USING，效果一样

SELECT
	o.order_id,
    c.first_name,
	sh.name
FROM orders o
JOIN customers c
	USING (customer_id)
JOIN shippers sh
	USING (shipper_id)

改写 7.5 多主键情况

SELECT *
FROM order_items oi
JOIN order_item_notes oin
	USING (order_id, product_id)

7.11 Natural Joins

不需要指定列名，列名一样自动连接

SELECT *
FROM orders o
NATURAL JOIN customers c

7.12 Cross Joins

Cross Join 更像是排列组合，比如一个表 有红色，蓝色，黄色，另外一个表有方形，圆形，cross 后就会获得 红色方形、红色圆形、蓝色方形、蓝色圆形、黄色方形、黄色圆形。
该例子显示不出好的效果

SELECT *
FROM customers c
CROSS JOIN products p

隐式 Cross

SELECT *
FROM customers, products

8. Union

把两个表连接在一起，列名无所谓，但是列数必须一样

SELECT order_id, order_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'

9. Column Attributes

1. Datatype
   VARCHAR(50) 长度最大为50，CHAR(50) 长度不足50会自动填充到50
2. PK
   Primary Key 主键，唯一标识符
3. NN
   NOT NULL 不允许为空
4. AI
   AUTO_INCREMENT 自增
5. Default / Expression
   提供默认值

9.1 Inserting a Single Row

INSERT INTO customers
VALUES (
	DEFAULT, 
    'x', 
    'hr',
    '1965-12-12',
    null,
    'addr',
    'city',
    'CH',
    DEFAULT)

INSERT INTO customers (
		first_name,
        last_name,
        birth_date,
        city, address,
        state)
VALUES (
	'xxx',
    'yyy',
    '2019-05-05',
    'xfsdf',
    'sggrgrg',
    'cq')

9.2 Inserting Multiple Rows

INSERT INTO shippers (name)
VALUES  ('ship1'),
		('ship2'),
        ('ship3'),
        ('ship4')

9.3 Inserting Hierarchical Rows

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, 5.95)

9.4 Creating a Copy of a Table

新表没有 PK 主键和 AI 自增

CREATE TABLE orders_copy AS
SELECT * FROM orders

插入一部分

INSERT INTO  orders_copy
SELECT * FROM orders
WHERE order_date < '2019-01-01'

9.5 Updating a Single Row

UPDATE invoices
SET payment_total = 10, 
	payment_date = '2019-03-01'
WHERE invoice_id = 1

9.6 Updating Multiple Rows

关闭安全更新保护：
Edit >> Preferences >> SQL Editor >> Safe Updates

USE sql_invoicing;
UPDATE invoices
SET payment_total = invoice_total * 0.9, 
	payment_date = due_date
WHERE client_id IN (3, 5)

9.7 Using Subqueries in Updates

加个括号就如同调用函数返回一个值之类的，如果返回多个值，就改为 IN

USE sql_invoicing;
UPDATE invoices
SET payment_total = invoice_total * 2, 
	payment_date = due_date
WHERE client_id =
	(SELECT client_id
	FROM clients
    WHERE name = 'myworks')

9.8 Deleting Rows

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

9.9 Restoring the Databases

。。。

---

MySQL