学习内容来自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