列属性
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
--
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
第六章
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号产品的顾客
与下面的连接语法结果一样：
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
第二种方法更加清晰
ALL关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
) #得到大于客户3所有发票额（客户3的最大发票额）的发票
与以下方法相等：
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子句中使用子查询的例子
