【Mosh】MySQL笔记

选择语句

1. 关键字尽量大写(MySQL不区分大小写)
2. USE：打开数据库
3. SELECT：选择显示某几列，下面按顺序跟着FROM、WHERE、ORDER BY几步
4. FROM：打开某个表
5. WHERE：查找出列中的符合要求数据
6. ORDER BY：按某一列进行升序排序
7. –：单行注释(等效于#)
8. <>：不等于(等效于!=)
9. '1974-04-14’是日期字符串

USE sql_store;

SELECT *
FROM customers
-- WHERE birth_date<>'1974-04-14'
ORDER BY first_name

与或非

1. AND：与运算符
2. OR：或运算符
3. NOT：非运算符

USE sql_store;

SELECT * 
FROM order_items
WHERE order_id=6 AND (quantity*unit_price) > 30

IN

1. T IN (1,2,3)：等效于T=1 OR T=2 OR T=3

USE sql_store;

SELECT * 
FROM products
WHERE quantity_in_stock NOT IN (49,38,72)

BETWEEN

1. T BETWEEN 0 AND 100：等效于T>=0 AND T<=100

USE sql_store;

SELECT * 
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'

LIKE

1. %：表示任意数量的任意字符
2. _：表示单个任意字符

USE sql_store;

SELECT * 
FROM customers
WHERE (address LIKE '%trail%' OR address LIKE '%avenue%') AND phone NOT LIKE '%6'

REGEXP

1. REGEXP：正则表达式
2. T REGEXP ‘ABC’：T中包含ABC
3. T REGEXP ‘A|B’：T中包含A或B

USE sql_store;

SELECT * 
FROM customers
WHERE first_name REGEXP 'ELKA|AMBUR'

1. T REGEXP ‘ABC$’：T以ABC结尾

USE sql_store;

SELECT * 
FROM customers
WHERE last_name REGEXP 'EY$|ON$'

1. T REGEXP ‘^ABC’：T以ABC开头

USE sql_store;

SELECT * 
FROM customers
WHERE last_name REGEXP '^MY|SE'

1. T REGEXP ‘A[BC]’：T包含AB或AC，连续字符可用’-‘符号表省略，例如[B-P]

USE sql_store;

SELECT * 
FROM customers
WHERE last_name REGEXP 'B[RU]'

IS NULL

1. T IS NULL：T为null

USE sql_store;

SELECT * 
FROM orders
WHERE shipper_id IS NULL

排序

1. 表达式 AS Column：新建列Column，内容为表达式的结果
2. DESC：降序排序(默认升序)
3. 进行排序的列可以不被SELECT选中显示出来(MySQL特性)

USE sql_store;

SELECT *,quantity*unit_price AS total_price
FROM order_items
WHERE order_id=2
ORDER BY total_price DESC

LIMIT

1. T LIMIT 3：显示T列前三行数据
2. T LIMIT 6,3：跳过T列前6行数据后显示T列接下来三行数据(6又称偏移量)
3. LIMIT按顺序永远在最后，否则MySQL报错

USE sql_store;

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

连接

1. 可以在列名后添加简写，用简写来代替列名
2. 表名.列名：表示引用某张表的某一列(解决了两个表的列名完全相同无法区分的问题)
3. JOIN T ON 表达式：在满足表达式的情况下将表T添加在当前表的后面(默认内连接)

USE sql_store;

SELECT order_id,oi.product_id,quantity,oi.unit_price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id

跨数据库连接

1. 引用其它数据库的表时也需要加上前缀来引用

USE sql_store;

SELECT *
FROM sql_store.order_items oi
JOIN sql_inventory.products p
	ON oi.product_id=p.product_id

自连接

1. 给表取别名
2. 给列加上前缀用以区分

USE 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. 重复JOIN实现多表连接，注意修改重复的列名

USE sql_invoicing;

SELECT 
	p.date,
	p.invoice_id,
    p.amount,
	c.name,
    pm.name AS status
FROM payments p
JOIN payment_methods pm
	ON p.payment_method=pm.payment_method_id
JOIN clients c
	ON p.client_id=c.client_id

复合连接条件

1. 用AND符合连接多个条件

USE sql_store;

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. FROM接多个表会导致“交叉连接”

USE sql_store;

SELECT *
FROM orders o,customers c
WHERE o.customer_id=c.customer_id

外连接

1. JOIN默认为INNER JOIN
2. LEFT JOIN默认为LEFT OUTER JOIN
3. 左表：FROM的表
4. 右表：JOIN的表
5. LEFT JOIN：左连接，完全保留左表(无论ON后面的条件是否满足)
6. RIGHT JOIN：右连接，完全保留右表(无论ON后面的条件是否满足)

USE sql_store;

SELECT 
	p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
	ON p.product_id=oi.product_id

多表外连接

1. 尽量都用左连接

USE sql_store;

SELECT 
	o.order_date,
    o.order_id,
    c.first_name,
    s.name AS shipper,
    os.name AS status
FROM orders o
LEFT JOIN customers c
	ON c.customer_id=o.customer_id
LEFT JOIN shippers s
	ON s.shipper_id=o.shipper_id
LEFT JOIN order_statuses os
	ON os.order_status_id=o.order_id
ORDER BY o.order_date DESC

自外连接

1. 自连接+外连接

USE sql_hr;

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

USING

1. USING §：等效于ON A.P=B.P
2. USING (P,Q)：等效于ON A.P=B.P AND A.Q=B.Q

USE sql_invoicing;

SELECT 
	p.date,
    c.name AS client,
    p.amount,
    pm.name
FROM payments p
LEFT JOIN clients c
	USING (client_id)
LEFT JOIN payment_methods pm
	ON pm.payment_method_id=p.payment_method

自然连接

1. 随机，不建议使用

USE sql_store;

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

交叉连接

1. 隐式交叉

USE sql_store;

SELECT 
	p.name AS product,
    s.name AS shipper
FROM shippers s,products p

1. 显示交叉

USE sql_store;

SELECT 
	p.name AS product,
    s.name AS shipper
FROM shippers s
CROSS JOIN products p

Union

1. 多次SELECT查询，中间通过UNION连接

USE sql_store;

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

列属性

1. PK：primary key
2. NN：not null
3. AI：automatic increase
4. Default/Expression：默认值

插入单行

1. 方法一：选择表，与其列名一一对应填入值，可以是默认值

USE sql_store;

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

2. 方法二：与给出的列名一一对应(可更改顺序)，省略的列自动赋默认值

USE sql_store;

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

插入多行

1. 多个括号用逗号隔开

USE sql_store;

INSERT INTO products (
    name,
    quantity_in_stock,
    unit_price)
VALUES 
	('product1',0,0),
	('product2',0,0),
	('product3',0,0)

插入分层行

1. LAST_INSERT_ID()：获取最近一次插入的ID（自增）

USE sql_store;

INSERT INTO orders (
    customer_id,
    order_date,
    status)
VALUES (1,'1990-01-01',1);

INSERT INTO order_items
VALUES (LAST_INSERT_ID(),1,1,2.95),
	(LAST_INSERT_ID(),2,1,3.95)

创建表复制

1. CREATE TABLE table_name AS 子查询：创建一个名为table_name的表，将子查询中的表格复制到表中

USE sql_invoicing;

CREATE TABLE invoices_archive AS

SELECT 
	c.name,
    i.invoice_id,
    i.number,
    i.invoice_total,
    i.payment_total,
    i.invoice_date
FROM invoices i
JOIN clients c
	USING (client_id)
WHERE i.payment_date IS NOT NULL

更新单行

1. UPDATE：更新表
2. SET：更改对应列上的值
3. WHERE：查询在哪一行

USE sql_invoicing;

UPDATE invoices
SET payment_total = 10,
	payment_date = due_date
WHERE invoice_id=1

更新多行

1. 关闭Safe Updates并重连

USE sql_store;

UPDATE customers
SET points = points+50
WHERE birth_date<'1990-01-01'

在Updates中用子查询

USE sql_store;

UPDATE orders
SET comments = 'gold client'
WHERE comments IS NULL AND
	customer_id IN (
		SELECT customer_id
		FROM customers
		WHERE points > 3000)

删除行

1. DELETE FROM：删除表中数据

USE sql_invoicing;

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

聚合函数

1. SUM(列名)：求和函数，函数名和括号之间没有空格
2. 括号中可写入表达式
3. 通过查询来规定计算范围

USE sql_invoicing;

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

GROUP BY

1. GROUP BY 列名T：按照列T分类
2. 永远在FROM和WHERE子句之后
3. WITH ROLLUP(仅限MySQL)：获得一行汇总结果(仅应用于聚合值的列)

USE sql_invoicing;

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

HAVING子句

1. HAVING：用来在GROUP BY分组行之后筛选数据
2. 用WHERE在GROUP BY分组行之前筛选数据
3. HAVING中提到的列必须被SELECT选中

USE sql_store;

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

ROLLUP子句

1. 自动插入一行汇总
2. 使用WITH ROLLUP时，不能在GROUP BY中使用列的别名

USE sql_invoicing;

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

复杂查询

子查询

USE sql_hr;

SELECT 
	employee_id,
    first_name,
    last_name,
    salary
FROM employees
WHERE salary > (
	SELECT AVG(salary) 
    FROM employees
)

IN

1. DISTINCT：去重（样例中没有）

USE sql_invoicing;

SELECT *
FROM clients
WHERE client_id NOT IN (
	SELECT i.client_id
    FROM invoices i
)

子查询VS连接

1. 子查询

USE sql_store;

SELECT 
	c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
WHERE customer_id IN (
	SELECT DISTINCT o.customer_id
    FROM orders o
    WHERE o.order_id IN (
		SELECT DISTINCT oi.order_id
        FROM order_items oi
        WHERE oi.product_id = 3
    )
)

2. 连接（可读性更佳）

USE sql_store;

SELECT 
	DISTINCT c.customer_id,
	c.first_name,
	c.last_name
FROM customers c
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items USING (order_id)
WHERE product_id = 3

ALL关键字

1. ALL：表示集合中的所有值

USE sql_invoicing;

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

ANY关键字

1. ANY：表示集合中的任何一个值
2. COUNT()：计数

USE sql_invoicing;

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

相关子查询

1. 子查询与外查询有关联

USE sql_invoicing;

SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE i.client_id = client_id
)

EXISTS运算符

1. 适用于结果集庞大的情况，比IN更有效率

USE sql_store;

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

SELECT子句中的子查询

1. 表达式中不能写列的别名，需要加上SELECT
2. 需要注意加上逗号

USE sql_invoicing;

SELECT 
	c.client_id,
    c.name,
    (SELECT SUM(invoice_total)
		FROM invoices i
		WHERE i.client_id = c.client_id
	) AS total_sales,
	(SELECT AVG(invoice_total)
		FROM invoices i
	) AS average,
	(SELECT total_sales)-(SELECT average) AS difference
FROM clients c

FROM子句中的子查询

1. 把子查询作为一个表单来进行查询

USE sql_invoicing;
SELECT *
FROM(
	SELECT 
		c.client_id,
		c.name,
		(SELECT SUM(invoice_total)
			FROM invoices i
			WHERE i.client_id = c.client_id
		) AS total_sales,
		(SELECT AVG(invoice_total)
			FROM invoices i
		) AS average,
		(SELECT total_sales)-(SELECT average) AS difference
	FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL

数值函数

1. ROUND()：四舍五入，可指定精确到小数点后某一位
2. TRUNCATE()：截断，可指定精确到小数点后某一位
3. CEILING()：向上取整
4. FLOOR()：向下取整
5. ABS()：绝对值
6. RAND()：生成0~1之间的浮点数

SELECT
	ROUND(3.1415,3),
    TRUNCATE(3.1415,3),
    CEILING(3.4),
    FLOOR(3.5),
    ABS(-0.1),
    RAND()

字符串函数

SELECT
	LENGTH('sky'),-- 长度
    UPPER('sky'),-- 转大写
    LOWER('SKY'),-- 转小写
    LTRIM('     sky'),-- 左修整
    RTRIM('sky     '),-- 右修整
    TRIM('   sky   '),-- 修整
    LEFT('abcdefg',4),-- 取左
    RIGHT('abcdefg',4),-- 取右
    SUBSTRING('abcdefg',2,3),-- 截取
    LOCATE('c','abcdefg'),-- 定位
    REPLACE('abcdefg','g','z'),-- 替换
    CONCAT('abc','de','fg')-- 串接

日期函数

SELECT
	NOW(),-- 电脑当前日期时间
    CURDATE(),-- 当前日期
    CURTIME(),-- 当前时间
    YEAR(NOW()),
    MONTH(CURDATE()),
    DAY(CURDATE()),
    HOUR(NOW()),
    MINUTE(CURTIME()),
    SECOND(NOW()),
    DAYNAME(NOW()),-- 提取日期名称
    MONTHNAME(CURDATE()),-- 提取月份名称
    EXTRACT(YEAR FROM CURDATE())

USE sql_store;

SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())

格式化日期和时间

1. 区分大小写，自己试一下

SELECT 
	DATE_FORMAT(NOW(),'%Y %m %d'),
	TIME_FORMAT(NOW(),'%h %i %p')

计算日期和时间

SELECT 
	DATE_ADD(NOW(),INTERVAL -1 DAY),-- 添加日期
	DATE_SUB(NOW(),INTERVAL 1 YEAR),-- 减去日期
    DATEDIFF('2021-03-07','2020-03-01'),-- 计算天数差
	TIME_TO_SEC('09:02') - TIME_TO_SEC('09:00')-- 返回从零点开始计时的秒数

IFNULL和COALESCE函数

1. IFNULL：替换空值
2. COALESCE：返回第一个非空值

USE sql_store;

SELECT 
	CONCAT(first_name,' ',last_name) AS customer,
    COALESCE(phone,'Unknown') AS phone
FROM customers

IF函数

1. IF：类似于？：结构
2. JOIN+GROUP BY+COUNT结构进行分组计数
3. SELECT注意加逗号

USE sql_store;

SELECT 
	product_id,
    name,
    COUNT(*) AS orders,
	IF(COUNT(*) = 1,'Once','Many times') AS frequency
FROM products p
JOIN order_items USING(product_id)
GROUP BY product_id,name

CASE运算符

1. 从上往下依次判断真假

USE sql_store;

SELECT 
	CONCAT(first_name,' ',last_name) AS customer,
    points,
    CASE 
		WHEN points >= 3000 THEN 'Gold'
		WHEN points >= 2000 THEN 'Silver'
		ELSE 'Bronze'
	END AS category
FROM customers c
ORDER BY points DESC

创建视图

1. CREATE VIEW view_name AS + 查询：创建视图

USE sql_invoicing;

CREATE VIEW clients_balance AS
SELECT 
	c.client_id,
    c.name,
	SUM(invoice_total-payment_total) AS balance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name

更改或删除视图

1. DROP VIEW view_name：删除视图
2. CREATE OR REPLACE VIEW view_name AS + 查询：创建视图
3. 放入源码中（待续）
4. 在编辑视图中修改并应用

可更新视图

1. 若视图中无DISTINCT、聚合函数、GROUP BY、HAVING、UNION，那么就可以在INSERT、UPDATE、DELETE语句中使用这类视图

WITH OPTION CHECK子句

1. 有时UPDATE和DELETE操作会将行从视图中删除，在视图末尾添加WITH OPTION CHECK以防止删除操作

视图的其他优点

1. 减小数据库改动所造成的影响
2. 限制对基础表的访问

存储过程

1. 存储过程是一个包含一堆SQL代码的数据库对象，在应用中调用该过程来存储或管理SQL代码

创建一个存储过程

1. DELIMITER：修改默认分隔符并将过程包含其中
2. 查询语句包含在BEGIN和END之中，以分号分隔

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance() 
BEGIN 
	SELECT *
    FROM invoices
    WHERE invoice_total > payment_total;
END$$

DELIMITER ;

3. 使用CALL语句调用过程

CALL get_invoices_with_balance()

使用MySQL工作台创建存储过程

1. 可以自动修改默认分隔符

删除存储过程

1. DROP PROCEDURE procedure_name：删除过程
2. 一般格式：先删后建

参数

1. 参数必须传入，哪怕是NULL

带默认值的参数

1. 可以使用IS NULL SET方式来设置参数
2. 也可以使用IF THEN ELSE END IF来判断
3. 也可以使用IFNULL()方法来判断

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_payments`(client_id int,payment_method_id tinyint)
BEGIN
	SELECT *
    FROM payments p
    WHERE p.client_id = IFNULL(client_id,p.client_id)
		AND p.payment_method = IFNULL(payment_method_id,p.payment_method);
END

参数验证