MySQL学习记录3、4-数据的增删改&聚合函数

学习内容来自B站SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！

3数据的增、删、改

1 插入单行

（1）-- 只插入部分列，其它列为默认值，注意插入数据和对应列顺序对齐
INSERT INTO customers(first_name, last_name, birth_date, address, city, state)
VALUE ( 'John', 'Smith', NULL, NULL, 'address', 'city', 'CA');
（2）-- 表的每列都插入（可省略customers后面的参数）
INSERT INTO customers
VALUE (DEFAULT, 'John', 'Smith', NULL, NULL, 'address', 'city', 'CA', DEFAULT);

2.插入多行

（1）-- 直接插入
INSERT INTO shippers(name)
VALUE 
	('shipper1'),
	('shipper2'),
    ('shipper3');
（2）-- 直接插入
INSERT INTO products(name, quantity_in_stock, unit_price)
VALUE 
	('product1', 4, 3.3),
    ('product2', 4, 3.3),
    ('product3', 4, 3.3);
（3）-- 根据条件插入
INSERT INTO order_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01';

3.插入分层行

-- SELECT LAST_INSERT_ID()：得到刚 insert 进去记录的主键值，只适用与自增主键
-- 该代码意思是 在orders表中插入了一些数据，第二个插入语句的LAST_INSERT_ID()获得上次插入语句的主键值，在第二个插入语句的整个插入中这个值不变。
INSERT INTO orders (customer_id, order_date, status)
VALUE 
	(1, '2019-01-02', 1);

INSERT INTO order_items
VALUES 
	(LAST_INSERT_ID(), 1, 1, 3.12),
(LAST_INSERT_ID(), 2, 1, 3.32);

4.创建表复制

（1）-- 创建一个表，数据与orders相同，（注意：order_archived并没有主键与自动递增等字段，而原来的orders表中有）
CREATE TABLE order_archived AS
SELECT * FROM orders; 
（2）-- 配合连接查询进行表复制
CREATE TABLE invoices_archive 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 AND i.payment_date IS NOT NULL;

5.更新单行

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

6.更新多行

-- 后面如果没有WHERE语句则表示更新整个表
UPDATE customers
SET points = points + 50
WHERE birth_date < '1990-01-01';

7.在UPDATE中用子查询

UPDATE invoices
SET 
	payment_total = 0.5  * invoice_total,
    payment_date = due_date
WHERE client_id  IN 
		(
			SELECT client_id 
            FROM clients
            WHERE State IN ('CA','NY')
        );

8.删除行

-- 后面也可以加条件，类似UPDATE，切记要跟WHERE语句，否则删除整个表的内容
DELETE FROM clients
WHERE client_id = 133;

4.聚合函数

1.使用聚合函数

-- 聚合函数只运行非空值
SELECT 
	MAX(invoice_total) AS hightest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS sum,
    COUNT(invoice_total) AS number_of_invoices,
    -- 因为payment_date 允许非空，所以显示出来的不一定等于COUNT(*),COUNT(*)代表所有行
    COUNT(payment_date) AS count_of_payments,
    COUNT(*) AS total_records,
    -- 去除相同行，如下面这句选择多少用户开了发票，需加DISTINCT只保留一个相同的client_id
    COUNT(client_id) AS count_of_clients
FROM invoices;

2.GROUP BY 子句

（1）
-- GROUP BY 子句要放在SELECT , FROM ,WHERE后面
SELECT 
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC;
（2）
SELECT 
	p.date,
    pm.name,
    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,p.payment_method
ORDER BY p.date;

（2）的结果

3.HAVING 子句

-- WHERE子句无法和聚合函数一起使用, 所以需要使用HAVING子句
-- WHERE子句作用于基本表或试图，HAVING子句作用于组（即放在GROUP BY后面）
（1）
SELECT 
	client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500;
（2）
-- 得到位于’VA’的花费超过100$的顾客
-- ON也可以改为USING子句（因为有同名列，简单点）
SELECT 
	c.customer_id,
    c.last_name
FROM customers c
JOIN orders o
	ON c.customer_id = o.customer_id
JOIN order_items oi
	ON oi.order_id = o.order_id
WHERE c.State = 'VA'
GROUP BY c.customer_id
HAVING SUM(oi.quantity*unit_price) > 100;

（2）的结果

4.WITH ROLLUP运算符

-- Mysql中有一个with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息；
SELECT 
	pm.name,
    SUM(amount) AS total
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP