安装配置Go

发表于 2021-09-06 分类于 Setup Valine：

按照微软教程安装即可：

https://docs.microsoft.com/zh-cn/learn/paths/go-first-steps/

踩坑

国内下载Go链接:

https://golang.google.cn/dl/

VS Code 安装 Go插件失败解决：

1 2	$ go env -w GO111MODULE=on //开启mod $ go env -w GOPROXY=https://goproxy.cn,direct //设置七牛云goproxy代理

MySQL笔记(8)--存储过程(Stored Procedures)

发表于 2021-05-19 更新于 2021-05-20 分类于 Database Valine：

1 优点

Store and organize SQL
Faster execution
Data security

2 创建一个存储过程

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;	-- body of procedure
END$$

DELIMITER ;				-- 将分隔符定义回分号

DELIMITER 重定义分隔符, 也就是将新分隔符包含的内容视为一个unit, 而不是将”;”结束的一个个语句视为unit.

调用存储过程:

1	CALL get_clients()

3 删除存储过程

1	DROP PROCEDURE IF EXISTS get_clients;

创建存储过程的基本框架

DROP PROCEDURE IF EXISTS get_clients;

DELIMITER $$
CREATE PROCEDURE get_clients()
BEGIN
    SELECT * FROM clients;	
END$$

DELIMITER ;

4 参数

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

调用:

1	CALL get_clients_by_state('CA')

4.1 带默认值的参数

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    IF state IS NULL THEN			----------
        SET state = 'CA';			----------
	END IF;							----------
    
    SELECT * FROM clients c
    WHERE c.state = state;
END$$

DELIMITER ;

调用：

1	CALL get_clients_by_state(NULL)

IF-ELSE

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    IF state IS NULL THEN
        SELECT * FROM clients;
	ELSE
        SELECT * FROM clients c
        WHERE c.state = state;
	END IF;
    
END$$

DELIMITER ;

更简洁的写法：

DROP PROCEDURE IF EXISTS get_clients_by_state;

DELIMITER $$
CREATE PROCEDURE get_clients_by_state
(
    state CHAR(2)
)
BEGIN
    SELECT * FROM clients c
    WHERE c.state = IFNULL(state, c.state);    
END$$

DELIMITER ;

IFNULL(state, c.state) 如果 state = NULL，则返回 c.state，而 c.state = c.state 永远为 TRUE

4.2 参数验证

DROP PROCEDURE IF EXISTS make_payment;

DELIMITER $$
CREATE PROCEDURE make_payment(
    invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
    IF payment_amount <= 0 THEN
        SIGNAL SQLSTATE '22003' 		-- 错误码
            SET MESSAGE_TEXT = 'Invalid payment amount';	-- 输出错误信息
	END IF;
        
    UPDATE invoices i
    SET
        i.payment_total = payment_amount,
        i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END$$

DELIMITER ;

4.3 输出参数

DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;

DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client(
    client_id INT,
    OUT invoices_count INT,						-- ----------------
    OUT invoices_total DECIMAL(9, 2)			-- ----------------
)
BEGIN
    SELECT COUNT(*), SUM(invoice_total)
	INTO invoices_count, invoices_total			-- ----------------
    FROM invoices i
    WHERE i.client_id = client_id
	    AND payment_total = 0;
END$$

DELIMITER ;

调用：

set @invoices_count = 0;
set @invoices_total = 0;
call sql_invoicing.get_unpaid_invoices_for_client
    (3, @invoices_count, @invoices_total);
select @invoices_count, @invoices_total;

5 变量

-- User or session variables
set @invoices_count = 0;

-- Local variable
-- 在存储过程或函数中定义

Demo

DROP PROCEDURE IF EXISTS get_risk_factor;

DELIMITER $$
CREATE PROCEDURE get_risk_factor()
BEGIN
    -- Local variables
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    
    SELECT risk_factor;
END$$

DELIMITER ;

6 函数

函数：只能返回单一值
存储过程：可以返回拥有多行多列的结果集

👉 函数属性

DETERMINISTIC: 一样的输入总是会返回一样的输出

READS SQL DATA: 函数中有读sql数据的内容

MODIFIES SQL DATA：函数中有修改sql数据的内容

DROP FUNCTION IF EXISTS get_risk_factor_for_client;

DELIMITER $$
CREATE FUNCTION get_risk_factor_for_client
(
    client_id INT
) 
RETURNS int
READS SQL DATA
BEGIN
    DECLARE risk_factor DECIMAL(9, 2) DEFAULT 0;
    DECLARE invoices_total DECIMAL(9, 2);
    DECLARE invoices_count INT;
    
    SELECT COUNT(*), SUM(invoice_total)
    INTO invoices_count, invoices_total
    FROM invoices i
    WHERE i.client_id = client_id;
    
    SET risk_factor = invoices_total / invoices_count * 5;
    RETURN IFNULL(risk_factor, 0);
END$$

DELIMITER ;

使用函数：

SELECT
    client_id,
    name,
    get_risk_factor_for_client(client_id) AS risk_factor
FROM clients

MySQL笔记(9)--触发器(Triggers)

发表于 2021-05-19 更新于 2021-05-20 分类于 Database Valine：

Trigger: A block of SQL code that automatically gets executed before or after an insert, update or delete statement.

To enhance data consistency

1 设置触发器

DELIMITER $$

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW		-- 作用于受影响的每一行
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
END $$

DELIMITER ;

可以触发除了 payments 之外的任何表的更新。NEW表示新 insert 的 payment (amount是payments 表的 column)。如果是DELETE，就用 OLD 表示删除的行。

2 查看触发器

1	SHOW TRIGGERS LIKE 'payments%' -- show filtered triggers

3 删除触发器

1	DROP TRIGGER IF EXISTS payments_after_insert

4 使用触发器进行审计

使用触发器进行记录

首先建立审计表：

USE sql_invoicing;

CREATE TABLE payments_audit
(
    client_id    INT              NOT NULL,
    date         DATE             NOT NULL,
    amount       DECIMAL(9, 2)    NOT NULL,
    action_type  VARCHAR(50)      NOT NULL,
    action_date  DATETIME         NOT NULL
)

假如有这两个触发器：

DROP TRIGGER IF EXISTS payments_after_insert;

DELIMITER $$

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW	
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (NEW.client_id, NEW.date, NEW.amount, 'Insert', NOW());
END $$

DELIMITER ;

DROP TRIGGER IF EXISTS payments_after_delete;

DELIMITER $$

CREATE TRIGGER payments_after_delete
    AFTER DELETE ON payments
    FOR EACH ROW	
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (OLD.client_id, OLD.date, OLD.amount, 'Delete', NOW());
END $$

DELIMITER ;

然后触发事件发生：

1 2	INSERT INTO payments VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1)

1 2	DELETE FROM payments WHERE payment_id = 11

刷新审计表可以看到：

client_id	date	amount	action_type	action_date
5	2019-01-01	10.00	Insert	2021-05-20 19:12:31
5	2019-01-01	10.00	Delete	2021-05-20 19:13:31

5 事件

Event: A task (or block of SQL code) that gets executed according to a schedule.

1	SHOW VARIABLES LIKE 'event%' -- 查看命名中有event的变量

1	SET GLOBAL event_scheduler = OFF -- 关掉它

Demo

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows		 -- 每年触发一次：yearly开头
ON SCHEDULE
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;  -- 删除所有超过一年的审计记录
END $$

DELIMITER ;

6 查看，删除和更改事件

查看事件

1	SHOW EVENTS LIKE 'yearly%';

删除事件

1	DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;

更改事件

ALTER EVENT：跟 CREATE EVENT 语法一样。

可以用来展示启用/禁用事件：

1	ALTER EVENT yearly_delete_stale_audit_rows ENABLE

1	ALTER EVENT yearly_delete_stale_audit_rows DISABLE

MySQL笔记(7)--视图(Views)

发表于 2021-05-19 分类于 Database Valine：

1 创建视图

USE sql_invoicing;

CREATE VIEW sales_by_client AS
SELECT
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

可以在”Views”看到这个视图.可以像使用table一样用视图.

2 更改或删除视图

删除视图

1	DROP VIEW sales_by_client

更改视图

USE sql_invoicing;

CREATE OR REPLACE VIEW sales_by_client AS 	-- create or replace
SELECT
    c.client_id,
    c.name,
    SUM(invoice_total) AS total_sales
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id, name

3 可更新视图

如果视图中没有:

DISTINCT
Aggregate Functions (MIN, MAX, SUM)
GROUP BY / HAVING
UNION

就是可更新视图,就可以增删改:

比如:

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total AS balance,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

invoices_with_balance 就是可更新视图,可以增删改:

1 2	DELETE FROM invoices_with_balance WHERE invoice_id = 1

1
2
3

UPDATE invoices_with_balance
SET due_date = DATE_ADD(due_date, INTERVAL 5 DAY)
WHERE invoice_id = 2

4 WITH OPTION CHECK 子句

上例中,进行如下更改:

1
2
3

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

该视图中 invoice_id = 2 的行消失了(因为不再满足(invoice_total - payment_total) > 0的条件)

如果不想 UPDATE 或 DELETE 语句使得某些行从视图中删除, 就这样:

CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT
    invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total AS balance,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION			-- 增加这个子句,防止删除行

后面再执行这样的:

1
2
3

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 3

就会报错:

Error Code: 1369. CHECK OPTION failed

5 视图的优点

Simplify queries
Reduce the impact of changes
Restrict access to the data

MySQL笔记(6)--MySQL的基本函数

发表于 2021-05-19 分类于 Database Valine：

1 数值函数

SELECT ROUND(5.7355, 2) 		-- 保留小数点后2位, 四舍五入: 5.74
SELECT TRUNCATE(5.7355, 2) 		-- 保留小数点后2位: 5.73
SELECT CEILING(5.2)				-- 6 (上取整)
SELECT FLOOR(5.7)				-- 5 (下取整)
SELECT ABS(-5.2)				-- 求绝对值
SELECT RAND()					-- 得到0~1的随机值

2 字符串函数

1
2
3

SELECT LENGTH('sky') 				-- 3
SELECT UPPER('sky') 				-- SKY
SELECT LOWER('Sky')					-- sky

1
2
3

SELECT LTRIM('     Sky')			-- 去除左边的空格：Sky
SELECT RTRIM('Sky     ')			-- 去除右边的空格：Sky
SELECT TRIM('    Sky  ')			-- 去除两边的空格：Sky

子串

1
2
3

SELECT LEFT('Kindergarten', 4)			-- Kind
SELECT RIGHT('Kindergarten', 6)			-- garten
SELECT SUBSTRING('Kindergarten', 3, 5)	-- nderg

查找位置

1 2	SELECT LOCATE('N', 'Kindergarten') -- 3 不区分大小写，若不在字符串中则返回0 SELECT LOCATE('garten', 'Kindergarten') -- 7

替换

1	SELECT REPLACE('Kindergarten', 'garten', 'garden') -- Kindergarden

拼接

1	SELECT CONCAT('first', 'last') -- firstlast

USE sql_store;

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers

3 日期

3.1 日期函数

1	SELECT NOW(), CURDATE(), CURTIME()

NOW()	CURDATE()	CURTIME()
2021-05-19 19:08:56	2021-05-19	19:08:56

获取指定的时间构成元素

SELECT YEAR(NOW())		-- 2021
SELECT MONTH(NOW())		-- 5
SELECT DAY(NOW())		-- 19
SELECT HOUR(NOW())		-- 19
SELECT MINUTE(NOW())	-- 08
SELECT SECOND(NOW())	-- 56

获取时间的字符串

1	SELECT DAYNAME(NOW()), MONTHNAME(NOW())

DAYNAME(NOW())	MONTHNAME(NOW())
Wednesday	May

EXTRACT

1	SELECT EXTRACT(DAY FROM NOW()) -- 19

3.2 格式化日期和时间

1	SELECT DATE_FORMAT(NOW(), '%M %d %Y')

May 19 2021

1	SELECT DATE_FORMAT(NOW(), '%H:%i %p')

19:17 PM

3.3 计算日期和时间

1
2
3

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY)		-- 	2021-05-20 19:21:32
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR)		-- 	2022-05-19 19:21:32
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)		-- 	2020-05-19 19:21:32

1	SELECT DATEDIFF('2021-01-05', '2021-01-01') -- 4

1	SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02') -- -120 (单位:秒)

4 IFNULL 和 COALESCE

IFNULL

USE sql_store;

SELECT
    order_id,
    IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders

IFNULL(shipper_id, ‘Not assigned’) 即如果shipper_id为NULL, 就改为’Not assigned’

输出:

order_id	shipper
1	Not assigned
3	Not assigned
8	Not assigned
9	1
2	4

COALESCE

USE sql_store;

SELECT
    order_id,
    COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders

如果shipper_id为空,就返回comments中的值,如果comments也为空,则返回’Not assigned’.

5 IF 函数

格式:

IF(expression, first, second)
如果expression为真,返回first, 为假则返回second

SELECT
    order_id,
    order_date,
    IF(
        YEAR(order_date) = YEAR(DATE_SUB(NOW(), INTERVAL 2 YEAR)),
        'Active',
        'Archived') AS category
FROM orders

当前年份为2021, 所以2019的为’Active’ (Mosh制作视频时为2019), 其他都是’Archived’:

order_id	order_date	category
1	2019-01-30	Active
2	2018-08-02	Archived
3	2017-12-01	Archived
4	2017-01-22	Archived
5	2017-08-25	Archived

6 CASE 运算符

SELECT
    order_id,
    CASE
        WHEN YEAR(order_date) = YEAR(NOW()) - 2 THEN 'Active'
        WHEN YEAR(order_date) = YEAR(NOW()) - 3 THEN 'Last Year'
        WHEN YEAR(order_date) < YEAR(NOW()) - 3 THEN 'Archived'
        ELSE 'Future'
	END AS category
FROM orders

输出:

order_id	category
1	Active
2	Last Year
3	Archived
4	Archived
5	Archived
6	Last Year
7	Last Year
8	Last Year
9	Archived
10	Last Year

MySQL笔记(5)--编写复杂查询

发表于 2021-05-18 更新于 2021-05-19 分类于 Database Valine：

1 子查询

-- Find products that are more 
-- expensive than Lettuce (id = 3)

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

2 IN 运算符

-- Find the products that have never been ordered

USE sql_store;

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

3 子查询 VS 连接

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

用连接的方法:

SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

在时间一样的情况下, 选择更易读的那种方法

4 ALL 关键字

-- Select invoices larger than all invoices of client 3
USE sql_invoicing;

SELECT *
FROM invoices
WHERE invoice_total > (
    SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

改用all 关键字:

SELECT *
FROM invoices
WHERE invoice_total > ALL (
    SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

5 ANY 关键字

-- Select clients with at least two invoices

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

改用any 关键字

-- Select clients with at least two invoices

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

即 “IN” 相当于 “=ANY”

6 相关子查询

-- Select employees whose salary is 
-- above the average in their office

USE sql_hr;

SELECT *
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

注意到子查询用到了外查询的 e.office_id, 也就是说子查询和外查询是相关子查询.

对于非相关子查询, 执行一次就可以了.

但是对于相关子查询, 外查询每一条record都要执行一次子查询,所以执行时间相对较长.

7 EXISTS 运算符

-- Select clients that have an invoice

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

子查询返回一个列表, 然后外查询看在不在此列表中.如果子查询返回的列表非常大,就会很影响性能.

使用EXISTS运算符

-- Select clients that have an invoice

SELECT *
FROM clients c
WHERE EXISTS (
    SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)

对于外查询的每一条record, EXISTS 子查询返回TRUE / FALSE, 若返回TRUE,就添加到结果集中.

Exercise

-- Find the products that have never been ordered

SELECT *
FROM products p
WHERE NOT EXISTS (
    SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
)

8 SELECT 子句中的子查询

SELECT
    invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
        FROM invoices) AS invoice_average,
	invoice_total - (SELECT invoice_average) AS difference -- 不能直接用别名
FROM invoices

得到这样一张表:

invoice_id	invoice_total	invoice_average	difference
1	101.79	152.388235	-50.598235
2	175.32	152.388235	22.931765
3	147.99	152.388235	-4.398235
4	152.21	152.388235	-0.178235
5	169.36	152.388235	16.971765
6	157.78	152.388235	5.391765
7	133.87	152.388235	-18.518235

Exercise

得到这样一张表:

client_id	name	total_sales	average	difference
1	Vinte	802.89	152.388235	650.501765
2	Myworks	101.79	152.388235	-50.598235
3	Yadel	705.90	152.388235	553.511765
4	Kwideo		152.388235
5	Topiclounge	980.02	152.388235	827.631765

Solution:

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 - average) AS difference
FROM clients c

9 FROM 子句中的子查询

将上面的查询结果当成表来用,放在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 - average) AS difference
    FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL

得到:

client_id	name	total_sales	average	difference
1	Vinte	802.89	152.388235	650.501765
2	Myworks	101.79	152.388235	-50.598235
3	Yadel	705.90	152.388235	553.511765
5	Topiclounge	980.02	152.388235	827.631765

MySQL笔记(4)--数据汇总

发表于 2021-05-18 分类于 Database Valine：

1 聚合函数（Aggregate Functions）

SELECT 
    MAX(invoice_total) AS highest,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total) AS total,
    SUM(invoice_total * 1.1) AS total2,
    COUNT(invoice_total) AS number_of_invoices,
    COUNT(payment_date) AS count_of_payments,		-- COUNT只计算非空值个数
    COUNT(*) AS total_records,
    COUNT(DISTINCT client_id) AS total_clients 		-- 去除重复值
FROM invoices
WHERE invoice_date > '2019-07-01'

Exercise

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-01-01' AND '2019-12-31'

date_range	total_sales	total_payments	what_we_expect
First half of 2019	1539.07	212.97	1326.1
Second half of 2019	1051.53	148.41	903.12
Total	2590.6	361.38	2229.22

2 GROUP BY 子句

Demo 1

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

⚠ 各个子句的顺序不能错，比如不能将WHERE移到ORDER BY 下面

得出每个client在2019年下半年的订单总金额

client_id	total_sales
5	489.52
3	427.54
1	134.47

Demo 2

SELECT
    state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients USING (client_id)
GROUP BY state, city

得到每个（state, city）组合的总金额：

state	city	total_sales
NY	Syracuse	802.89
WV	Huntington	101.79
CA	San Francisco	705.90
OR	Portland	980.02

Exercise

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

根据交易日期和交易方式求总金额。

date	payment_method	total_payments
2019-01-03	Credit Card	74.55
2019-01-08	Credit Card	32.77
2019-01-08	Cash	10.00
2019-01-11	Credit Card	0.03
2019-01-15	Credit Card	148.41
2019-01-26	Credit Card	87.44
2019-02-12	Credit Card	8.18

3 HAVING 子句

👉 使用WHERE子句可以在行分组之前过滤数据
👉 使用HAVING子句可以在行分组之后过滤数据

SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500  		-- 必须是在select子句中的column

client_id	total_sales
1	802.89
3	705.90
5	980.02

复合搜索条件

SELECT
    client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5

获取金额超过500并且多于5个订单的client的结果

client_id	total_sales	number_of_invoices
5	980.02	6

Exercise

-- Get the customers located in VG
-- 		who have spent more than $100
USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    c.last_name,
    c.state,
    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

customer_id	first_name	last_name	state	total_sales
2	Ines	Brushfield	VA	157.92

4 ROLLUP 运算符

Demo 1

SELECT
    client_id,
    SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP

WITH ROLLUP 自动计算总数

client_id	total_sales
1	802.89
2	101.79
3	705.90
5	980.02
	2590.60

Demo 2

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, pm.name WITH ROLLUP

注意: GROUP BY 子句中由于使用了 WITH ROLLUP, 就不能使用别名 payment_method

得到每个组及整个结果集的汇总集:

date	payment_method	total_payments
2019-01-03	Credit Card	74.55
2019-01-03		74.55
2019-01-08	Cash	10.00
2019-01-08	Credit Card	32.77
2019-01-08		42.77
2019-01-11	Credit Card	0.03
2019-01-11		0.03
2019-01-15	Credit Card	148.41
2019-01-15		148.41
2019-01-26	Credit Card	87.44
2019-01-26		87.44
2019-02-12	Credit Card	8.18
2019-02-12		8.18
		361.38

注意到 2019-01-08 对 Cash 和 Credit Card 有一个汇总值.

MySQL笔记(3)--增删改

发表于 2021-05-17 更新于 2021-05-18 分类于 Database Valine：

1 列属性

Data type

👉 VARCHAR: 可变长度字符串（节省空间）
👉 CHAR: 字符串，不满指定长度就会填充满

Properties

👉 PK: Primary Key
👉 NN: Not Null
👉 AI: Automatic Increment

2 插入

2.1 插入单行

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

有些不是必填的, 可以skip掉:

Another Way

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

2.2 插入多行

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

2.3 插入分层行

order_items是orders的子表, 列出更为详细的信息.在orders插入行, 相应的在order_items也要插入行:

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

LAST_INSERT_ID()相当于一个method/function, 直接调用获取最近插入的record的id.

3 创建复制表(a copy of table)

1 2	CREATE TABLE orders_archived AS SELECT * FROM orders

通过这种方式创建的表复制会没有PK, AI这些属性

3.1 子查询

上例中的SELECT * FROM orders是子查询.

INSERT INTO也可以用子查询:

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

orders_archived表中只有满足条件的records.

4 更新

4.1 更新单行

1
2
3

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

1
2
3

UPDATE invoices
SET payment_total = DEFAULT, payment_date = NULL
WHERE invoice_id = 1

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE invoice_id = 3

4.2 更新多行

在MySQL workbench需要取消safe update选项才能更新多行.

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id = 3
-- WHERE client_id IN (3, 4)

client_id=3对应有多项record, 都会被update

4.3 在UPDATE中使用子查询

将上例改为:

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id = 
        (SELECT client_id
         FROM clients
         WHERE name = 'Myworks')

如果子查询返回的是多个client_id:

UPDATE invoices
SET 
    payment_total = invoice_total * 0.5, 	-- 使用表达式更新
    payment_date = due_date
WHERE client_id IN							-- 用IN
        (SELECT client_id
         FROM clients
         WHERE state IN ('CA', 'NY'))

⚠ 在执行查询之前, 应该先执行子查询看看对不对.

5 删除行

根据条件删除某些行

1 2	DELETE FROM invoices_archived WHERE invoice_id = 2

子查询

DELETE FROM invoices
WHERE invoice_id = 
        (SELECT client_id
         FROM clients
         WHERE name = 'Vinte')

删除整表所有records

1	DELETE FROM invoices_archived

MySQL笔记(2)--连接

发表于 2021-05-17 更新于 2021-05-18 分类于 Database Valine：

002_sql-join

1 Inner Joins (内连接)

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

返回表orders在表customers中有对应record的项

o和c分别是orders和customers的别名，以简化代码

关键字INNER可省略

1.1 跨数据库连接

USE sql_store;  -- 选择了数据库sql_store为当前数据库

SELECT *
FROM order_items oi
JOIN sql_inventory.products p   -- 另一个数据库sql_inventory
    ON oi.product_id = p.product_id

1.2 自连接

将表和自己连接，但是给不同的别名以区分。如下，manager也是一名员工，其信息也在表employees中，用自连接可以得到employee和TA的manager的具体员工信息。

USE sql_hr;  -- 选择了数据库sql_hr为当前数据库

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
JOIN employees m  
    ON e.reports_to = m.employee_id

1.3 多表连接

USE sql_store;

SELECT
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

表orders先和表customers连接，然后与表order_statuses连接。

1.4 复合连接条件

对于有两个主键的表，要用两个主键作为条件与别的表连接。例如表order_items的主键是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

1.5 隐式连接语法

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


-- Implicit Join Syntax
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

不建议使用隐式连接语法，要是忘写WHERE就完了。

2 外连接

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
RIGHT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

LEFT (OUTER) JOIN 和 RIGHT (OUTER) JOIN, OUTER 可省略

2.1 多表外连接

SELECT 
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
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
ORDER BY c.customer_id

2.2 自外连接

USE sql_hr;

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

3 USING子句

使用于两张表的键名完全一致的情况

SELECT 
    o.order_id, 
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    -- ON o.customer_id = c.customer_id 
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_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
    USING (order_id, product_id)

4 自然连接

自动找到同名的column来连接。（容易出错，不建议使用）

SELECT
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c

5 交叉连接

连接两张表的所有records

SELECT
    c.first_name AS customers,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name

应用场景：表一是大中小型号，表二是红黄蓝等各种颜色，将所有型号和所有颜色组合起来。

隐式语法

SELECT
    c.first_name AS customers,
    p.name AS product
FROM customers c, products p
ORDER BY c.first_name

6 联合（Unions）

筛选某些records，用union将多次筛选的结果联合起来

SELECT
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'	-- 在此日期后的order, 状态设为active
UNION
SELECT
    order_id,
    order_date,
    'Archived' AS status			-- 在此日期前的order, 状态设为archived
FROM orders
WHERE order_date < '2019-01-01'

也可以联合多张表

SELECT first_name  -- 决定列名
FROM customers
UNION
SELECT name
FROM shippers

练习

根据积分将顾客划分为金银铜三档：

SELECT
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers c
WHERE points < 2000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'silver' AS type
FROM customers c
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers c
WHERE points > 3000
ORDER BY first_name

MySQL笔记(1)--基础语句

发表于 2021-05-16 更新于 2021-05-18 分类于 Database Valine：

SELECT 子句

从一张表（customers）中选取某些列

SELECT
    first_name, 
    last_name, 
    points, 
    points * 10 + 100 AS "discount factor"   -- 可以进行数学运算,AS来rename
FROM customers

1 2	SELECT DISTINCT state -- DISTINCT 只保留重复项中的一项 FROM customers

WHERE 子句

过滤作用，根据条件选择records

(各个WHERE子句只是提供不同的示例，运行时选择一个WHERE子句，其余注释掉，下文同)

SELECT *
FROM customers
WHERE state = 'va' -- va大小写一样的
WHERE state <> 'va' -- not in VA
WHERE birth_date > '1990-01-01' -- born after 1990/01/01

运算符有：

>
>=
<
<=
=
(not equal)  !=  or  <>

AND, OR, NOT 运算符

1
2
3

SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000)

AND优先级高于OR

IN 运算符

SELECT *
FROM customers
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
-- 用IN改写：
WHERE state IN ('VA', 'GA', 'FL')
-- WHERE state NOT IN ('VA', 'GA', 'FL') -- 返回不在这些州的 records


-- 这样是错的：WHERE state = 'VA' OR 'GA' OR 'FL' 
-- 因为我们不能用OR连接布尔表达式state = 'VA'和字符串'GA'

BETWEEN 运算符

SELECT *
FROM customers
-- WHERE points >= 1000 AND points <=3000
-- 改写为：
WHERE points BETWEEN 1000 AND 3000

-- 也可用于非数值
SELECT *
FROM customers
WHERE birth_date BETWEEN "1990-01-01" AND "2000-01-01"

LIKE 运算符

-- 字符串模式匹配
SELECT *
FROM customers
WHERE last_name LIKE 'b%' 
-- 得到last name以'B'或'b'开头的records

WHERE last_name LIKE 'brush%' 
-- 得到last name以“brush”开头的records

WHERE last_name LIKE '%b%' 
-- 表示'b'前后可以有任意字符数('b'在开头、中间、结尾出现皆可)

WHERE last_name LIKE '%y' 
-- 得到last name以'y'结尾的records

WHERE last_name LIKE '_____y' 
-- 得到last name以'y'结尾且只有6个字符('_'填补表示任意字符)的records

WHERE last_name LIKE 'b____y' 
-- 运行后得到last name以'b'开头以'y'结尾且只有6个字符的record(本例中为'Boagey')

1
2
3

/***SUMMARY**/ 
% any number of characters
_ single character

REGEXP 运算符

-- regular expression (REGEXP) 正则表达式
SELECT *
FROM customers

WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'

WHERE last_name REGEXP '^field' 
-- last name must starts with "field"

WHERE last_name REGEXP 'field$' 
-- last name must ends with "field"

/* use '|' for multiple search pattern */ 
WHERE last_name REGEXP 'field|mac|rose' 
-- last name must contains "field" or "mac" or "rose"

WHERE last_name REGEXP 'field$|mac|rose' 
-- last name must ends with "field" or contains "mac"/"rose"

WHERE last_name REGEXP '[gim]e' 
-- last name must contains "ge"/"ie"/"me"

WHERE last_name REGEXP 'e[gim]' 
-- last name must contains "eg"/"ei"/"em"

WHERE last_name REGEXP '[a-h]e'
-- the same as WHERE last_name REGEXP '[abcdefgh]e'

^ 	beginning
$ 	end
| 	logical or
[abcd]
[a-f]

IS NULL 运算符

SELECT *
FROM customers
WHERE phone IS NULL  -- 返回没有phone(该字段为NULL)的records
WHERE phone IS NOT NULL -- phone不为空

ORDER BY 子句

默认是按照主键（primary key）排序，使用ORDER BY 子句可以指定排序的键

SELECT *
FROM customers
ORDER BY first_name        -- 根据first_name字段值升序排序
ORDER BY first_name DESC   -- 根据first_name字段值降序排序

SELECT *
FROM customers
ORDER BY state DESC, first_name 
--先根据state降序排列，state相同的根据first_name升序排列

-- birth_date不在选出来的列里，still work
SELECT first_name, last_name
FROM customers
ORDER BY birth_date

LIMIT 子句

SELECT *
FROM customers
LIMIT 3 		-- 返回前3个records
LIMIT 300 		-- 300 > #record, 返回全部records
LIMIT 6, 3  	-- 跳过前6个records，返回第7，8，9个records