列属性

Datatype 注意CHAR(50)可能会浪费空间，一般使用VARCHAR(50)
PK主键 识别唯一
NN 打勾表示不能有空值
AI 自动递增，通常用在主键列：每次在表中插入一条新纪录，就是让MySQL或者数据库引擎在列中插入一个值，即在最后一行加入一个顾客（如果PK是customer_id）
会在加入新纪录的同时，将顾客id增加1

插入单行

INSERT INTO customers
VALUES (DEFAULT, 'John', 'Smith', '1990-01-01',NULL, 'address', 'city', 'CA', NULL)

NULL也可以用DEFAULT替代。

也可以不用DEFAULT或NULL，即在INSERT INTO customers 后打出需要赋值的列名，忽略NULL值的

INSERT INTO customers (
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (DEFAULT, 'John', 'Smith', '1990-01-01', 'address', 'city', 'CA')

插入多行

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

分层插入

一个order id对应多个单子
INSERT INTO oredrs (customer_id, price）
VALUES(1, 2.23)

INSERT INTO order_items
VALUES
(LAST_INSERT_ID(), 1

创建表复制

CREATE TABLE orders_archived AS
SELECT * FROM orders
之后刷新，创建的表就会显示
创建了名为orders_archived的表，内容与orders表相同
删除表：右键truncat table

只复制一部分：
INSERT INTO orders_archived #如果前面没有创建过一个空的orders列名的空表，就在后面加上(列名)
SELECT *
FROM orders
WHERE order_date < '2019-01-01'

更新单行

UPDATE invoices
SET payment_total = 10, payment_data = '2019-03-01' #要设置进去的内容
WHERE invoice_id = 1

Updates中用子查询

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

先运行括号内的内容，选出name为Myworks的行的client_id，对这些id对应的部分update

---多个id
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_data = due_date
WHERE client_id IN
(SELECT client_id
FROM clients
WHERE state IN ('CA', 'NY'))

删除行

DELETE FROM invoices
WHERE invoice_id = 1

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

恢复数据库

在MySQL工作台找到file菜单，打开SQL脚本，打开creat-databases执行

第五章

聚合函数

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_total) AS count_of_payments,
SUM(invoice_total * 1.1) AS total,
COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

--
SELECT
'First 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-06-30'
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-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-09-01' AND '2019-06-30'

GROUP BY句子

根据client_id分组求和：
SELECT
client_id
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id #根据client_id进行分组
ORDER BY total_sales DESC #降序排序

如果需要插入WHERE:
SELECT
client_id
SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id #根据client_id进行分组
ORDER BY total_sales DESC #降序排序

根据多列分组：
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state, city

图片.png

--
SELECT
date,
pm.name AS payment_method,
SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id

关联两个表

GROUP BY date, payment_method
ORDER BY date

HAVING子句-筛选

SELECT
client_id,
SUM(invoice_total） AS total_sales,
COUNT(*） AS numeber_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoice

（与WHERE的区别，HAVING在分组之后，WHERE在分组之后；WHERE可以筛选没有选择的列，HAVING只能筛选选择的列）

SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(oi.quantity * oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE state = 'VA'
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_sales > 100

ROLL UP运算符

对每一组的总计做了计算
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

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

图片.png

第六章

Subqueries

SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)

IN运算符

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

唯一一个从来没有订购过的产品

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

子查询vs连接

oi里没有customer_id，在子查询里先连接orders表

SELECT customer_id, last_name, first_name
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM order_items oi
JOIN orders o USING (order_id)
WHERE product_id = 3
) #得到购买3号产品的顾客

图片.png

与下面的连接语法结果一样：
SELECT DISTINCT customer_id, last_name, first_name
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

图片.png

第二种方法更加清晰

ALL关键字

USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
) #得到大于客户3所有发票额（客户3的最大发票额）的发票

图片.png

与以下方法相等：
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)

ALL关键字的都可以用MAX改写

ANY关键字

SELECT *
FROM clients
WHERE client_id IN (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)

等于：
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)

也就是说IN与=ANY的效果是一样的

Correlated Subqueries 相关子查询

--for each employee
-- calculate the avg salary for emplyee.office
-- return the employee if salary > avg
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id #不想计算所有员工的平均工资，加上筛选，只面向在同一个部门的员工
)
对每个员工执行子查询，因此e.office_id是每次这个员工所在的部门的id，所以平均的是这个部门的工资

EXISTS运算符

SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
等于：
SELECT *
FROM clients
WHERE client_id IN (
SELECT clinet_id
FROM invoices
)
--
SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT product_id
FROM order_items
WHERE product_id = p.product_id
)

SELECT子句中的子查询

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

FROM子句中的子查询

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 - avergae) AS difference
FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL

在选择语句的FROM子句中使用子查询的例子