mysql学习笔记

最新推荐文章于 2024-08-17 13:24:35 发布

Openlyg

最新推荐文章于 2024-08-17 13:24:35 发布

阅读量643

点赞数 1

文章标签： mysql 学习 sql

本文链接：https://blog.csdn.net/qq_53218564/article/details/123134757

版权

一.选择语句

1.SELECT

USE sql_store;

SELECT *
FROM customers
-- WHERE customer_id = 1;
order by first_name;

SELECT（*）返回全部列或者可以指定想要返回列

2.别名

SELECT
    last_name,
    first_name,
    points,
    (points + 10) * 100 AS discount_factor
FROM customers;

AS 别名

别名中想要有空格可以用‘ ’ “ ” 括起来

练习：

SELECT name,unit_price,unit_price * 1.1 AS 'new price'
FROM products;

3.WHERE

SELECT *
FROM Customers
WHERE birth_date > '1990-01-01';

WHERE 单条件判断

练习：

SELECT *
FROM orders
WHERE order_date >= '2019-01-01';

4.多条件筛选数据

AND 条件连接符需两个条件都成立才执行

OR 条件连接符有一个条件成立就执行

AND 优先级大于 OR

NOT 取反用在WHERE后面

小技巧使用NOT 和把sql语句里面的关系运算符和逻辑运算符取反相同

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

SELECT *
FROM customers
WHERE  birth_date <='1990-01-01' AND points <= 1000;

练习：

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

5.IN运算符

SELECT *
FROM customers
WHERE  state = 'VA' OR state = 'GA' OR state = 'FL';

SELECT *
FROM customers
WHERE  state IN ('VA','GA','FL');

SELECT *
FROM customers
WHERE  state NOT IN ('VA','GA','FL');

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

6.BETWEEN运算符

SELECT *
FROM customers
WHERE points >=1000 AND points <=3000;

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000;

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

7.LIKE运算符

SELECT *
FROM customers
WHERE last_name LIKE 'b%';

% 匹配所有字符，代表任意字符数

— 匹配一个字符，代表一个单字符

SELECT *
FROM customers
WHERE last_name LIKE '_y';

练习：

SELECT *
FROM customers
WHERE address LIKE '%trail%' OR address LIKE '%avenue%';

-- SELECT *
-- FROM customers
-- WHERE phone NOT LIKE '%9';

8.REGEXP运算符

SELECT *
FROM customers 
WHERE last_name LIKE '%field%'
WHERE last_name REGEXP 'field'
WHERE last_name REGEXP '^field'
WHERE last_name REGEXP 'field$'
WHERE last_name REGEXP 'field|mac'
WHERE last_name REGEXP '^field|mac|rose'
WHERE last_name REGEXP 'field|mac|rose'
WHERE last_name REGEXP 'field$|mac|rose'
WHERE last_name REGEXP '[gim]e'
WHERE last_name REGEXP 'e[fmq]'
WHERE last_name REGEXP '[a-h]e';

REGEXP正则表达式，用于更复杂的查询

用^表示字符串的开头，用$表示字符串的结尾

|表示连接符表示多个搜寻模式代表一个逻辑上的or

[ ] 匹配里面的任意字符一个-代表一个范围

练习：

SELECT *
FROM customers 
WHERE first_name REGEXP 'Elka|Ambur';

SELECT *
FROM customers 
WHERE last_name REGEXP '[a-z]ey|[a-z]on'
WHERE last_name REGEXP 'ey$|on$';

SELECT *
FROM customers 
WHERE last_name REGEXP '^my[a-z]|[a-z]se[a-z]'
WHERE last_name REGEXP '^my|se';

SELECT *
FROM customers 
WHERE last_name REGEXP 'b[ru]'
WHERE last_name REGEXP 'br|bu';

9.IS NULL

SELECT *
FROM customers
WHERE phone IS NULL;

SELECT *
FROM customers
WHERE phone IS NOT NULL;

SELECT *
FROM orders
WHERE shipper_id IS NULL;

10.ORDER BY子句

SELECT *
FROM customers
ORDER BY first_name;

SELECT *
FROM customers
ORDER BY first_name DESC;

DESC 降序

SELECT first_name, last_name, 10 + 1 AS points
FROM customers
ORDER BY first_name, last_name, points;

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

11.LIMIT子句

SELECT *
FROM customers
LIMIT 3;

SELECT *
FROM customers
LIMIT 6, 3;

偏移变量,去掉前六条

SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3;

LIMIT 语句永远放在最后

SELECT
FROM
WHERE
ORDER BY    DESC
LIMIT

二.连接

12.内连接INNER JOIN

SELECT *
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

SELECT order_id, orders.customer_id, first_name, last_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

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

别名简化例如 orders o 或者 customers c

SELECT p.product_id, order_id, o.quantity, o.unit_price
FROM order_items o
JOIN products p on o.product_id = p.product_id;

13.跨数据库连接

SELECT *
FROM order_items o
JOIN sql_inventory.products p ON o.product_id = p.product_id;

USE sql_inventory;
SELECT *
FROM sql_store.order_items o
JOIN products p ON o.product_id = p.product_id;

只需给不在当前数据库的表加前缀

14.自连接

USE sql_hr;
SELECT *
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id;

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;

要使用不同的别名，而且还要给每列都加个别名

15.多表连接

USE sql_store;
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_statuses os ON o.status = os.order_status_id;

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;

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

16.复合连接条件

SELECT *
FROM order_items oi
JOIN order_item_notes oin ON oi.order_id = oin.order_id AND oi.product_id = oin.product_id;

17.隐式连接语法

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

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

不建议使用，如忘打WHERE 就形成交叉连接

18.外连接（左连接，右连接）

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

使用左连接，所有左表的记录都会被返回，不管条件正确还是错误

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

使用右连接，所有右表的记录都会被返回，不管条件正确还是错误

练习：

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

19.多表外连接

SELECT c.customer_id, c.first_name, o.order_id, sh.name
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;

尽量少使用右连接

练习：

SELECT o.order_id, o.order_date, c.first_name AS customer, sh.name, os.name AS status
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN shippers sh ON o.shipper_id = sh.shipper_id
LEFT JOIN order_statuses os ON o.status = os.order_status_id;

20.自外连接

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

21.USING子句

USING只能在不同表中列名字完全相同时使用

SELECT o.order_id, c.first_name, sh.name AS shipper
FROM orders o
JOIN customers c 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);

练习：

USE sql_invoicing;
SELECT p.date, c.name, p.amount, pm.name
FROM payments p
JOIN clients c USING (client_id)
JOIN payment_methods pm ON p.payment_method = pm.payment_method_id;

22.自然连接（NATURAL）

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

23.交叉连接（CROSS）

显式：

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

隐式：

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

练习：

SELECT *
FROM shippers s, products p
ORDER BY s.shipper_id;

SELECT *
FROM products p
CROSS JOIN shippers s
ORDER BY s.shipper_id;

24.联合

SELECT order_id, order_date, 'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT order_id, order_date, 'Archived' AS status
FROM orders
WHERE order_date <= '2019-01-01';

联合可以是一张表格，也可以是不同表格里面的列

查询返回的列数量要相同，否则错误提示

第一段查询决定列名

SELECT first_name
FROM customers
UNION
SELECT name
FROM shippers;

SELECT name
FROM shippers
UNION
SELECT first_name
FROM customers;

练习：

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

三.插入,更新和删除数据

25.列属性

VARCHAR 给多少就是多少

CHAR 给多少后自动填充

PK 主键 NN非空 AI自动递增

26.INSERT INTO （插入单行）VALUES

INSERT INTO customers
VALUES (
	DEFAULT,
    'john',
    'smith',
    '1990-01-01',
    NULL,
    'address',
    'city',
    'CA',
    'DEFAULT');

INSERT INTO customers(
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
    'john',
    'smith',
    '1990-01-01',
    'address',
    'city',
    'CA');

27.插入多行

INSERT INTO products(name,quantity_in_stock,unit_price)
VALUES ('c1',7,1.25),
	   ('c2',9,5.21),
       ('c3',8,3.21);

INSERT INTO shippers(name)
VALUES ('c1'),
	   ('c2'),
       ('c3');

28.插入分层行

往多张表中插入数据

LAST_INSERT_ID() 插入最近的ID

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

SELECT LAST_INSERT_ID();

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);

29.创建表复制

CREATE TABLE AS

CREATE TABLE orders_archived AS
SELECT * FROM orders;

使用选择语句作为插入语句中的子查询

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

练习:

USE sql_invoicing;
CREATE TABLE invoices_archived AS
SELECT 
		i.invoice_id,
		i.number,
        c.name AS client,
        i.invoice_total,
        i.payment_total,
        i.invoice_date,
        i.payment_date,
		i.due_date
FROM invoices i 
JOIN clients c USING (client_id)
WHERE payment_date IS NOT NULL;

30.更新单行(UPDATE)

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

UPDATE invoices
SET payment_total = 0, payment_date = NULL
WHERE invoice_id = 1;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE invoice_id = 3;

31.更新多行

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id = 3;

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE client_id IN (3,4);

练习：

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

32.在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');

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

UPDATE invoices
SET payment_total = invoice_total * 0.5,
	payment_date = due_date
WHERE payment_date IS NULL ;

练习：

UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN
		(SELECT customer_id
        FROM customers
        WHERE points > 3000);

33.删除行（DELETE FROM）

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

DELETE FROM invoices
WHERE invoice_id =1;

DELETE FROM invoices;

34.恢复数据库

mysql工作台>File>Open SQL Soript>选择create-databases.sql>选择打开>然后执行>刷新即可

错误1241：Operand should contain 1 column(s) ：操作数应包含一列

我们只需要在输入mysql语句的地方，我们输入: SET SQL_SAFE_UPDATES = 0; 然后我们运行这条语句就可以解决问题。请注意一定要确保这条语句成功执行

四.汇总数据

35.聚合函数

只运行非空值，有空值不会被计算，重复项也会被计算，distinct 不计算重复项

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_date) AS count_of_payments, #有空值不会被计算
    COUNT(*) AS total_records #不管非空还是有数值，全部计算
FROM invoices;

SELECT 
	MAX(invoice_total) AS highest,
	MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    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 if 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';

36.GROUP BY 子句

单列分组：

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;

利用GROUP BY 进行数据分组

多列分组：

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 pm.payment_method_id = p.payment_method
GROUP BY date, payment_method
ORDER BY date;

37.HAVING子句

对数据进行分组后不能使用WHERE

WHERE可以对数据分组之前进行筛选数据

HAVING可以对数据分组之后进行筛选数据

SELECT
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
HAVING total_sales > 500;

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;

HAVING 使用的列必须存在SELECT子句中

WHERE使用的列可以不在SELECT子句中，可以使用任何列

练习：

USE sql_store;
SELECT 
	c.customer_id,
    c.customer_id,
    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.customer_id, c.last_name
HAVING total_sales > 100;

38.ROLLUP运算符

WITH ROLLUP

ROLLUP运算符只能用于聚合值的列

SELECT
	client_id,
	SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP; #将total_sales列相加

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

进行多列分组，并运用rollup运算符，可以得到每个组以及整个结果集的汇总值。

Rollup不是标准的SQL语言，无法在sql server 和oracle中使用，但有相似的运算符。

使用ROLLUP运算符时，不能在GROUP BY子句中使用别名。

练习：

SELECT
	pm.name AS payment_method,
	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;

五.编写复杂查询

39.子查询

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

练习:

USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees);

40.IN运算符

DISTINCT 去重, 多列表使用IN,单一值使用>，<，=之类的运算符

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

练习:

USE sql_invoicing;
SELECT *
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices);

41.子查询VS连接

USE sql_invoicing;
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;

练习:

子查询:
USE sql_store;
SELECT customer_id, first_name, last_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);
    
连接:
USE sql_store;
SELECT DISTINCT customer_id, first_name, last_name
FROM customers
LEFT JOIN orders o USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3;

42.ALL关键字

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

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

子查询返回一列值，不使用ALL就无法进行比较，ALL和MAX聚合函数可以进行转化

43.ANY关键字

= ANY 和 IN等效

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);

44.相关子查询

子查询和外查询具有相关性

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

练习:

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

45.EXISTS运算符

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

SELECT *
FROM clients
WHERE EXISTS (
	SELECT DISTINCT client_id
    FROM invoices);

EXISTS能提高查询的效率，子查询并没有把真的结果集返回给外查询

练习:

USE sql_store;
SELECT *
FROM products
WHERE product_id NOT IN(
	SELECT  product_id
    FROM order_items);

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

46.SELECT子句中的子查询

SELECT 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
    FROM invoices) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS difference
FROM invoices;

练习:

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;

47.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;

六.MySQL的基本函数

48.数值函数

ROUND (轮函数),四舍五入

SELECT ROUND(5.74);			#6，四舍五入
SELECT ROUND(5.74,1);		#5.7,保留小数后一位
SELECT ROUND(5.7345,2);		#5.73，保留小数后两位

TRUNCATE (截断数字)

SELECT TRUNCATE(5.7395,2);		#5.73,截断小数点后两位

CEILING (上限函数),返回大于或等于这个数的最小整数

SELECT CEILING(5.7);		#6
SELECT CEILING(5.2);		#6

FLOOR (地板函数),返回小于或等于这个数的最大整数

SELECT FLOOR(5.7);		#5
SELECT FLOOR(5.2);		#5

ABS(绝对值函数), 计算绝对值

SELECT ABS(5.2);		#5.2
SELECT ABS(-5.2);		#5.2

RAND 用来生成0-1区间的随机浮点数

SELECT RAND();

49.字符串函数

LENGTH 得到字符串的字符数

SELECT LENGTH('sky');		#3

UPPER 将字符串转化为大写

SELECT UPPER('sky');		#SKY

LOWER 将字符串转化为小写

SELECT UPPER('Sky');		#sky

LTRIM (left trim) 移除字符串左侧的空白字符或其他预定义字符

SELECT LTRIM('    sky');		#sky

RTRIM 移除字符串右侧的空白字符或其他预定义字符

SELECT RTRIM ('sky   ');		#sky

TRIM 移除字符串左右两侧的空白字符或其他预定义字符

SELECT RTRIM ('     sky   ');		#sky

LEFT 返回字符串左侧的几个字符

SELECT LEFT('Kindergarten',4);		#Kind

RIGHT 返回字符串右侧的几个字符

SELECT RIGHT('Kindergarten',6);		#garten

SUBSTR (字符截取函数)

SELECT SUBSTR('Kindergarten', 3, 5);		#nderg	(3是起始位置，5是结束位置，结束位置可写可不写)

LOCATE 返回第一个字符或者一串字符匹配位置(不区分大小写)

没有搜索的字符则返回0

SELECT LOCATE('n', 'Kindergarten');		#3
SELECT LOCATE('garten', 'Kindergarten');	#7

REPLACE 替换字符或字符串

SELECT REPLACE('Kindergarten', 'garten', 'garden');		#Kindergarden

CONCAT 串联两个字符串

SELECT CONCAT('first', 'last');		#firstlast

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

50.日期函数

NOW 调用当前日期和时间

SELECT NOW();	#2021-07-06 14:46:04

CURDATE 调用当前日期

SELECT CURDATE();	#2021-07-06

CURTIME 返回当前时间

SELECT CURTIME();	#14:49:33

YEAR 截取当前年份

SELECT YEAR(NOW());	#2021

MONTH 截取当前月份

SELECT MONTH(NOW());	#7

DAY 截取当前日期

SELECT DAY(NOW());	#6

HOUR 获取当前时刻(小时)

SELECT HOUR(NOW());		#14

DAYNAME 获取字符串格式的星期数

SELECT DAYNAME(NOW());		#Tuesday

MONTHNAME 获取字符串格式的月份

SELECT MONTHNAME(NOW());	#July

EXTRACT 将代码录入别的DBMS

SELECT EXTRACT(YEAR FROM NOW());	#2021

练习:查询当前年份的订单

USE sql_store;
SELECT *
FROM orders
WHERE YEAR(order_date) >= YEAR(NOW());

51.格式化的日期和时间

DATE_FORMAT 格式化日期

SELECT DATE_FORMAT(NOW(), '%y','%Y');		# %y是21 %Y是2021
SELECT DATE_FORMAT(NOW(), '%m','%M');		# %m是07 %M是July
SELECT DATE_FORMAT(NOW(), '%d','%D');		# %d是06 %D是6th

TIME_FORMAT 格式化时间

SELECT TIME_FORMAT(NOW(), '%H:%i %p');		# 15:42 PM	%H是小时，%i是分钟，%p是PM或AM

52.计算日期和时间

DATE_ADD 增加日期

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); #增加一天
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); #增加一年
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); #减少一年

DATE_SUB 减少日期

SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR); #减少一年

DATEDIFF 计算两个日期的间隔

SELECT DATEDIFF('2021-09-01', '2021-07-06');	#57,只返回天数

TIME_TO_SEC 计算两个时间的间隔

SELECT TIME_TO_SEC('09:00');		#32400
SELECT TIME_TO_SEC('09:01') - TIME_TO_SEC('09:00');		#60

53.IFNULL和COALESCE函数

IFNULL: 替换字符串，用字符串替换空值

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

COALESCE: 我们提供许多值，这个函数会返回这么多值中的第一个非空值

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

练习:

USE sql_store;
SELECT 
	CONCAT(first_name, ' ', last_name) AS customer,
    IFNULL(phone, 'Unknown') AS phone
    #COALESCE(phone, 'Unknown') AS phone
FROM customers;

54.IF函数

IF适用于单一的测试表达式

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

练习:

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

55.CASE运算符

CASE适用于多个测试表达式

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

练习：

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
ORDER BY points DESC;

七.视图

56.创建视图

CREATE VIEW AS 创建视图，可当表格使用，视图不存储数据

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;

USE invoicing;
SELECT *
FROM sales_by_client
ORDER BY total_sales DESC;

练习:

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

57.更改或删除视图

DROP VIEW +视图名（删除视图）

CREATE OR REPLACE VIEW + 视图名 AS （修改视图，不需要删除）

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

58.可更新视图

DISTINCT

聚合函数（MAX,AVG,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,
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0

DELETE FROM invoices_with_balance
WHERE invoice_id = 1

删除视图中 invoice_id为1的发票数据

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

更新 invoice_id为2的发票到期时间增加两天

59.WITH CHECK OPTION 子句

UPDATE invoices_with_balance
SET payment_total = invoice_total
WHERE invoice_id = 2

使用UPDATE 或 DELETE 语句会导致行从视图中删除

WITH CHECK OPTION 这条语句会防止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,
    invoice_date,
    due_date,
    payment_date
FROM invoices
WHERE (invoice_total - payment_total) > 0
WITH CHECK OPTION

60.视图的其他优点

1.视图可以简化查询

2.视图可以减小数据库设计改动的影响

3.可以使用视图限制基础表访问，加强数据安全性

八.存储过程

61.什么是存储过程

存储过程是一个包含一堆SQL代码的数据库对象

大多数DBMS可以对存储过程里的代码做一些优化

在存储过程里面的SQL代码执行的更快

存储过程能加强数据的安全性

插入，更新，删除数据可以由存储过程来完成

62.创建一个存储过程

格式:

CREATE PROCEDORE +名()

主体

END

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

修改默认分隔符(DELIMITER $$)

CALL get_clients();		#调用这个存储过程

练习:

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

DELIMITER $$
CREATE PROCEDURE get_invoices_with_balance()
BEGIN
    SELECT *
    FROM invoices_with_balance
    WHERE balance > 0;
END$$
DELIMITER ;

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

导航页面>点击>Stored Procedures>Create Stored Procedures

CREATE PROCEDURE `get_payments` ()
BEGIN
	SELECT * FROM payments;
END

64.删除存储过程

DROP PROCEDURE IF EXISTS get_clients;

DROP PROCEDURE IF EXISTS get_clients;

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

65.参数（在存储过程中添加参数）

CHAR(2) 表示有两个字符的字符串

VARCHAR 表示可变长度的字符串

多数情况下使用VARCHAR

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 ;

调用get_clients_by_state存储过程

CALL get_clients_by_state('CA');

练习：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_invoices_by_client`(
	client_id INT
)
BEGIN
	SELECT *
    FROM invoices i
    WHERE i.client_id = client_id;
END

66.带默认值的参数

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 ;

CALL get_clients_by_state(NULL)

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 ;

简化，规范：

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

练习:

DELIMITER $$
CREATE 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 $$
DELIMITER ;

CALL get_payments(NULL, NULL);
CALL get_payments(1, NULL);
CALL get_payments(5, NULL);
CALL get_payments(NULL, 2);

67.参数验证

更新invoices

CREATE DEFINER=`root`@`localhost` PROCEDURE `make_payment`(
	invoice_id INT,
    payment_amount DECIMAL(9, 2),
    payment_date DATE
)
BEGIN
	UPDATE invoices i
    SET
		i.payment_total = payment_amount,
        i.payment_date = payment_date
	WHERE i.invoice_id = invoice_id;
END

call sql_invoicing.make_payment(2, 100, '2019-01-01');

添加参数验证后的代码：

CREATE DEFINER=`root`@`localhost` 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

核心代码：

IF payment_amount <= 0 THEN
		SIGNAL SQLSTATE '22003'
			SET MESSAGE_TEXT = 'Invalid payment amount';
	END IF;

再输入这行代码就会报错

call sql_invoicing.make_payment(2, -100, '2019-01-01');

尽量减少逻辑验证

68.输出参数(OUT)

获取客户未支付发票:

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_unpaid_invoices_for_client`(
	client_id INT
)
BEGIN
	SELECT COUNT(*), SUM(invoice_total)
    FROM invoices i
    WHERE i.client_id = client_id
		AND payment_total = 0;
END

call sql_invoicing.get_unpaid_invoices_for_client(2);
call sql_invoicing.get_unpaid_invoices_for_client(3);

输出客户未支付发票:

CREATE DEFINER=`root`@`localhost` 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

输入3,得到以下代码

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;

尽量避免使用输出参数

69.变量

本地变量在存储过程中执行计算任务

定义变量：

SET @invoices_count = 0;

如何在存储过程中使用本地变量：

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_risk_factor`()
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;
    
    SET risk_factor = invoices_total / invoices_count * 5;

    SELECT risk_factor;
END

70.函数

函数只能返回单一值，无法返回多行，多列的结果集

导航页面>右击Functions>Create Functions

CREATE DEFINER=`root`@`localhost` 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

使用这个函数：

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

可以使用 DROP FUNCTION语句来删除函数

DROP FUNCTION IF EXISTS get_risk_factor_for_client;

九.触发器和事件

71.触发器

触发器是在插入，更新，删除语句前后自动执行的一堆SQL代码

使用触发器增强数据的一致性

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 ;

使用触发器：

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

练习：

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;
END $$

DELIMITER ;

使用触发器：

DELETE
FROM payments
WHERE payment_id = 10

72.查看触发器

可以用 SHOW TRIGGERS 查看所有的触发器

SHOW TRIGGERS LIKE 'payments%';

触发器命名规则：首先是表的名字，然后写之前或之后，最后是SQL语句的类型，即插入，更改，删除

例如 --- table_after_insert

73.删除触发器

和删除存储过程的方法相似

DROP PROCEDURE IF EXISTS payments_after_delete;

DROP PROCEDURE IF EXISTS +触发器名

DELIMITER $$

DROP PROCEDURE IF EXISTS payments_after_delete;

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;
END $$

DELIMITER ;

74.使用触发器进行审计

创建新表：

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
)

DELIMITER $$

DROP PROCEDURE IF EXISTS payments_after_insert;

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 ;

DELIMITER $$

DROP PROCEDURE IF EXISTS payments_after_delete;

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 ;

75.事件

事件是根据计划执行的任务或一堆SQL代码

可以自动化数据库维护

可以看到MySQL所有的系统变量：

SHOW VARIABLES;

寻找事件管理器变量

SHOW VARIABLES LIKE 'event%';
SET GLOBAL event_scheduler = ON; #打开事件管理器

创建一个事件：

DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
	-- AT '2021-09-01'
	EVERY 1 YEAR STARTS '2021-01-01'ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

76.查看，更改，删除事件

查看事件：

SHOW EVENTS;

删除事件：

DROP EVENTS IF EXISTS +事件名

更改事件：

DELIMITER $$

ALTER EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
	-- AT '2021-09-01'
	EVERY 1 YEAR STARTS '2021-01-01'ENDS '2029-01-01'
DO BEGIN
	DELETE FROM payments_audit
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$

DELIMITER ;

也可以用ALTER EVENT 来暂时启用或禁用一个事件：

ALTER EVENT yearly_delete_stale_audit_rows ENABLE;

十.事务和并发

77.事务

事务是代表单个工作单元的一组SQL语句

特性：1.原子性 Atomicity

2.一致性 Consistency

3.隔离性质 lsolation

4.持久性 Durability

78.创建事务

START TRANSACTION; 创建事务 COMMIT; 结束

USE sql_store;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 1);

COMMIT;

成功全部成功，失败一个sql语句全部退回

退回事务并撤消所有更改：ROLLBACK;

USE sql_store;

START TRANSACTION;

INSERT INTO orders (customer_id, order_date, status)
VALUES (1, '2019-01-01', 1);
INSERT INTO order_items
VALUES (LAST_INSERT_ID(), 1, 1, 1);

ROLLBACK;

79.并发和锁定

两个对话框同时进行以下代码(一行一行的执行)：

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = points + 10
WHERE customer_id = 1;
COMMIT;

第一个事务完成，第二个事务才能开始

80.并发问题

第一个问题:丢失更新（两个事务同时进行并没有上锁可能会出现，较晚提交的会覆盖先提交的）

解决方法：使用锁，MySQL会默认使用锁定

第二个问题:脏读问题（一个事务读取了尚未被提交的数据）

解决方法：为事务建立隔离级别（读已提交隔离级别）

第三个问题:不可重复读（不一致读）

解决方法：为事务建立隔离级别（可重复隔离级别）

第四个问题：幻读（在执行后进行的修改，增加，删除）

解决方法：为事务建立隔离级别（序列化）

隔离级别会损坏性能和可扩展性，标准的sql定义了四个隔离级别

81.隔离事务级别

查看事务隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';

设置下一个事务隔离级别：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; #可序列化

未来所有事务的隔离级别:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;	#SESSION

所有对话框的未来所有事务隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;	#GLOBAL

82.读未提交隔离级别

两个对话框，一行一行执行，先执行第一个对话框的前两行，然后执行第二个对话框的前三行

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT points
FROM customers
WHERE customer_id = 1;
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
COMMIT;

83.读已提交隔离级别

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	#COMMITTED
SELECT points
FROM customers
WHERE customer_id = 1;
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 20
WHERE customer_id = 1;
COMMIT;

不存在脏读问题，会存在不可重复读问题

USE sql_store;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 30
WHERE customer_id = 1;
COMMIT;

84.可重复读隔离级别

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;	#REPEATABLE READ
START TRANSACTION;
SELECT points FROM customers WHERE customer_id = 1;
SELECT points FROM customers WHERE customer_id = 1;
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET points = 40
WHERE customer_id = 1;
COMMIT;

USE sql_store;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 1;
COMMIT;

上面两个案例，两个用户正在同时操作，更新数据，可能会幻读。

85.序列化隔离级别

USE sql_store;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM customers WHERE state = 'VA';
ROLLBACK;

USE sql_store;
START TRANSACTION;
UPDATE customers
SET state = 'VA'
WHERE customer_id = 3;
COMMIT;

所有并发问题都可以解决，但降低了性能

86.死锁

两个事务永远在等对方，永远无法完成

USE sql_store;
START TRANSACTION;
UPDATE customers SET state = 'VA'WHERE customer_id = 1;
UPDATE orders SET status = 1 WHERE order_id = 1;
COMMIT;

USE sql_store;
START TRANSACTION;
UPDATE orders SET status = 1 WHERE order_id = 1;
UPDATE customers SET state = 'VA'WHERE customer_id = 1;
COMMIT;

十一.数据类型

87.数据类型的介绍

1.字符串类型

2.数值类型

3.日期时间类型

4.用于存储二进制数据的BLOB类型

5.存储几何空间或者地区值的空间类型

88.字符串类型

CHAR fixed-length

VARCHAR 用于存储可变长度字符串（用户名，密码，地址等等）一般长度50(~64kb)

MEDIUMTEXT max:16MB

LONGTEXT max:4GB

TINYTEXT max:255 bytes

TEXT max:64kb

英文：一个字节

欧洲，中东：两个字节

中文，日文：三个字节

89.整数类型

TINYINT 1b [-128,127]

UNSIGNED TINYINT 1b [0,255] (不可以使用负数)

SMALLINT 2b [-32K,32K]

MEDIUMINT 3b [-8M,8M]

INT 4b [-2B,2B]

BIGINT 8b [-9Z,9Z]

补零自动补零，相同位数

INT(4) => 0001

90.定点数类型和浮点数类型

DECIMAL(p, s) p指定位数,s指定精度

例如DECIMAL(9, 2) => 1234567.89

替代词: DEC, NUMERIC, FIXED

FLOAT 4b 浮点型可以存储非常大或者非常小的数字

DOUBLE 8b 双精度浮点型可以存储更大或更小的数字

91.布尔类型

BOOL

BOOLEAN

UPDATE posts
SET is_published = 1	#or FALSE

92.枚举和集合类型

ENUM 枚举尽量避免使用

ENUM('small','medium','large')

SET(...) 集合尽量避免使用

93.日期和时间类型

DATE 存储一个没有时间成分的日期

TIME 存储一个时间值

DATETIME 8b 存储日期时间类型

TIMESTAMP 4b 存储时间戳（只能存储2038年之前的时间）

YEAR 存储四位数年份

94.Blob类型

使用Blob （二进制长对象）类型来存储大型二进制数据

如图像，视频，PDF，word文件

1.TINYBLOB 255b (能存储的大小)

2.BLOB 65KB

3.MEDIUMBLOB 16MB

4.LONGBLOB 4GB

95.JSON类型

JSON文档

格式：

{

"key":value

}

案例：两种写法

USE sql_store;
UPDATE products
SET properties = '
{
	"dimension": [1, 2, 3],
    "weight": 10,
    "manufacturer": { "name": "sony" }
}
'
WHERE product_id =1;

USE sql_store;
UPDATE products
SET properties = JSON_OBJECT(
    "weight", 10,
    "dimension", JSON_ARRAY(1, 2, 3),
    "manufacturer", JSON_OBJECT('name', 'sony')
)
WHERE product_id =1;

使用JSON提取键值对：

SELECT product_id, JSON_EXTRACT(properties, '$.weight') AS weight
FROM products
WHERE product_id = 1;

简便写法：

SELECT product_id, properties -> '$.weight'
FROM products
WHERE product_id = 1;

-> 列路径运算符

SELECT product_id, properties -> '$.dimension[0]'
FROM products
WHERE product_id = 1;

SELECT product_id, properties ->> '$.manufacturer.name'
FROM products
WHERE product_id = 1;

->> 去掉双引号

SELECT product_id, properties ->> '$.manufacturer.name'
FROM products
WHERE properties ->> '$.manufacturer.name' = 'sony';

更新或添加JSON语句：

USE sql_store;
UPDATE products
SET properties = JSON_SET(		#更新或添加语句
	properties,
    '$.weight', 20,
    '$.age', 10
)
WHERE product_id =1;

删除JSON语句：

USE sql_store;
UPDATE products
SET properties = JSON_REMOVE(		#删除语句
	properties,
    '$.age'
)
WHERE product_id =1;

十二.设计数据库

96.数据建模

1.理解和分析业务需求

2.构建业务的概念模型

3.完善概念模型，生成一个数据模型（逻辑模型）

4.为特定的数据库管理系统构建一个实体模型

97.概念模型

1.概念（Conceptual）

2.逻辑（Logical）

3.实体（Physical）

1.实体关系图（用于数据建模）

2.UML图（标准建模语言图）

数据建模是一个迭代过程

98.逻辑模型

独立于数据库，增加细节

三种类型：

1.一对一

2.一对多

3.多对多

99.实体模型

E:\material\Word\mysql图片cache

100.主键

复合主键包含多个列

每个表都要有个主键

E:\material\Word\mysql图片cache

101.外键

学生表是主键表一对多关系

student_id （外键）

course_id (外键)

enrollments 使用复合主键

102.外键约束

在子表中的 On Update 中选择 CASCADE

如果父表的主键变了，子表中的记录会自动更改

On Delete 中选择 NO ACTION (防止删除数据)

103.标准化

防止数据重复，进行大量数据冗余，提高效率

1.第一范式

2.第二范式

3.第三范式

104.第一范式 (1NF)

第一范式要求一行中的每个单元格都应该有单一值，且不能出现重复列

105.链接表

关系型数据库无法使用多对多，使用链接表的两个一对多，效果相同

E:\material\Word\mysql图片cache

106.第二范式 (2NF)

要符合第二范式，一组关系就必须符合第一范式，

并且它不能有任何取决于这组关系任何候选键的任何真子集的非主属性

第二范式要求每张表都应该有一个单一目的

一张表的每一列都应该在描述该表代表的实体，不应该有其他的描述

ORDERS:

order_id	date	customer_name
1	...	Mosh
2	...

这张表不满足第二范式， customer_name应该去掉，不属于这张表的属性

应该如下:

ORDERS和CUSTOMERS两张表

order_id	date	customer_id
1	...	1
2	...	1

customer_id	name
1	Mosh

107.第三范式（3NF）

实体或表首先应该符合第二范式，表中的所有属性只能由那组关系的候选键决定，而不能是任何非主属性

INVOICES:

...	invoice	payment_total	balance
	100	20	80

应删除balance列，balance列违反了第三范式

108.不要对什么都进行建模

只需要为现下问题制定最佳解决方案就行

一切从简，简单才是最高境界，不要预测未来的变化

109.模型的正向工程

Database>Forward Engineer>next>next>next

110.数据库同步模型

对实体模型进行更改数据后

Database>Synchronize Model...>next>Include SQL Scripts Attached to Model >next>next>next>next

111.模型的逆向工程

Database>Reverse Engineer... >next>next>选择想要逆向工程的数据库>next>next>next>next>next>

112.创建和删除数据库

CREATE DATABASE 创建数据库

CREATE DATABASE IF NOT EXISTS sql_store2;

DROP DATABASE 删除数据库

DROP DATABASE IF EXISTS sql_store2;

113.创建表

CREATE TABLE 创建表

主键 PRIMARY KEY

自动递增 AUTO_INCREMENT

非空 NOT NULL

默认 DEFAULT

唯一 UNIQUE

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
CREATE TABLE IF NOT EXISTS customers
(
	customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name 	VARCHAR(50) NOT NULL,
    points 		INT NOT NULL DEFAULT 0,
    email 		VARCHAR(255) NOT NULL UNIQUE
);

114.更改表

ALTER TABLE 修改表

列名中不应该有空格

ADD 增加

MODIFY COLUMN 修改

DROP 删除

ALTER TABLE customers
	ADD	last_name VARCHAR(50) NOT NULL AFTER first_name;

ALTER TABLE customers
	ADD	last_name VARCHAR(50) NOT NULL AFTER first_name,
    ADD city	  VARCHAR(50) NOT NULL,
    MODIFY coLuMN first_name VARCHAR(55)DEFAULT '',
    DROP points;

115.创建关系

CREATE DATABASE IF NOT EXISTS sql_store2;
USE sql_store2;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE IF NOT EXISTS customers
(
	customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name 	VARCHAR(50) NOT NULL,
    points 		INT NOT NULL DEFAULT 0,
    email 		VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE orders
(
	order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY fk_orders_customers ( customer_id )
		REFERENCES customers ( customer_id)
		ON UPDATE CASCADE
		ON DELETE NO ACTION
);

CASCADE 级联更新

NO ACTION 无操作

REFERENCES 为表创建外键

FOREIGN KEY 外键

116.更改主键和外键

ALTER TABLE orders
	ADD PRIMARY KEY (order_id),
	DROP PRIMARY KEY,
	DROP FOREIGN KEY fk_orders_customers,
	ADD FOREIGN KEY fk_orders_customers ( customer_id)
		REFERENCES customers ( customer_id)
		ON UPDATE CASCADE
		ON DELETE NO ACTION;

ADD PRIMARY KEY 增加主键

DROP PRIMARY KEY 删除主键

DROP FOREIGN KEY 删除外键

ADD FOREIGN KEY 增加外键

117.字符集和排序规则

字符集是将每个字符映射到数字的表

ci 不区分大小写给予大小写同等优先级

SHOW CHARSET;	查看字符集
CHAR(10) ->(10 * 3) = 30	#UTF-8

CREATE DATABASE db_name	#创建数据库时更改字符集
	CHARACTER SET latin1;

ALTER DATABASE db_name	#更改现有字符集
	CHARACTER SET latin1;

CREATE TABLE table1	#更改列级字符集
(
)
CHARACTER SET latin1;

ALTER TABLE table1	#现有表更改字符集
CHARACTER SET latin1;

118.存储引擎

SHOW ENGINES;	#查看引擎

最常用的存储引擎是InnoDB

ALTER TABLE customers	#更改一张表的存储引擎
ENGINE = InnoDB;

十三.高效的索引

119.创建索引

EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';	#不加索引
CREATE INDEX idx_state ON customers (state);	#创建索引

练习：

EXPLAIN SELECT customer_id FROM customers WHERE points > 1000;
CREATE INDEX idx_points ON customers (points);

120.查看索引

SHOW INDEXES IN customers;

PRIMARY 主键，也就是聚集索引

ANALYZE TABLE customers;	#更详细的统计表的信息

我们自己创建的索引是二级索引

BTREE 二进制树

SHOW INDEXES IN orders;	#查看订单表中的索引

121.前缀索引

CREATE INDEX idx_lastname ON customers (last_name(20));	#前缀索引
SELECT COUNT(*) FROM customers;
SELECT COUNT(DISTINCT LEFT(last_name, 1)) FROM customers;

122.全文索引

USE sql_blog;
SELECT *
FROM posts
WHERE title LIKE '%react redux%' OR
		body LIKE '%rect redux%';

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body) ;
SELECT *
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

相关性得分是一个介于0-1之间的浮点数，0表示没有相关性

下面是全文索引的两种模式：

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body) ;
SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body) ;
SELECT *, MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react -redux +form' IN BOOLEAN MODE);

123.复合索引

USE sql_store;
CREATE INDEX idx_state_points ON customers (state, points);
EXPLAIN SELECT customer_id FROM customers
WHERE state = 'CA' AND points > 1000;

索引越多，写入操作越慢

删除索引：

SHOW INDEXES IN customer;
DROP INDEX idx_state ON customers;
DROP INDEX idx_points ON customers;

124.复合索引中列的顺序

把经常使用的列放前面，将基数较高的放在前面

EXPLAIN SELECT customer_id
FROM customers
WHERE state = 'CA'AND last_name LIKE 'A%';
CREATE INDEX idx_state_lastname ON customers(state, last_name);

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_lastname_state)
WHERE state = 'NY' AND last_name LIKE 'A%';
CREATE INDEX idx_state_lastname ON customers(state, last_name);

125.当索引无效时

EXPLAIN SELECT customer_id FROM customers
WHERE state = 'CA' OR points > 1000;

CREATE INDEX idx_points ON customers (points);
EXPLAIN
	SELECT customer_id FROM customers
	WHERE state = 'CA'
    UNION
    SELECT customer_id FROM customers
	WHERE points > 1000;

全索引扫描：

EXPLAIN SELECT customer_id FROM customers
WHERE points +10 > 2010;

优化：

EXPLAIN SELECT customer_id FROM customers
WHERE points > 2000;

126.使用索引排序

EXPLAIN SELECT customer_id FROM customers
ORDER BY state;

可以使用 SHOW STATUS 查看服务器变量

查看上一次

SHOW STATUS LIKE 'last_query_cost';

EXPLAIN SELECT customer_id FROM customers
WHERE state = 'CA'
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

——(a,b) 三种排序方法：

1.——a

2.——a,b

3.——a DESC , b DESC

127.覆盖索引

一个包含所有满足查询需要的数据索引,通过使用这个索引，

MySQL可以不读取表的情况下就执行查询

EXPLAIN SELECT * FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

EXPLAIN SELECT customer_id FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

EXPLAIN SELECT customer_id, state FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

128.维护索引

注意重复索引和多余索引

在创建新索引前，注意查看现有的索引

查看索引：

SHOW INDEXES IN 表名;

删除索引：

DROP INDEX 索引名 ON 表名;

十四.保护数据库

129.创建一个用户

CREATE USER 用户名@(ip,主机名，域名);

CREATE USER john IDENTIFIED BY '1234'; #用户名:john 密码:1234	所有地方都可以连接

130.查看用户

两种方法：1.

SELECT * FROM mysql.user;	#查看所有用户

2.导航窗口>Users and Privileges

131.删除用户

DROP USER 名@连接地点;

132.修改密码

方法1：

SET PASSWORD FOR john = '1234';	#修改john的密码为1234

SET PASSWORD = '1234';	#修改当前用户的密码为1234

方法2：导航窗口>Users and Privileges>点击要修改的用户>修改密码>应用

133.授予权限

赋予一般权限：

CREATE USER moon_app IDENTIFIED BY '1234';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE
ON sql_store.*
To moon_app;

赋予管理员权限：

GRANT ALL
ON *.*
TO john;

134.查看权限

方法1.

SHOW GRANTS FOR john; #john 名

SHOW GRANTS;	#查看当前用户权限

方法2.导航窗口>Users and Privileges>Administrative Roles

135.撤销权限

GRANT CREATE VIEW
ON sql_store.*
TO moon_app;

取消前面给的权限：

REVOKE CREATE VIEW
ON sql_store.*
FROM moon_app;

十五.总结

1Mysql增删改查sql语句

进入mysql 命令行: mysql -uroot –p 查看所有数据库: show databases; 创建数据库： create database wg charset utf8; 删除数据库： drop database wg; 选择数据库： use databases; 查看所有表： show tables; 查看创建数据库的语句：show create database databasename; 查看创建表的语句：show create table tablename; 查看表结构：desc tablename; 增： mysql> use wg； mysql> create table students( id int auto_increment primary key,name varchar(10) not null,sex varchar(12),address varchar(50),phone int not null unique); #自增长 auto_increment #非空 not null #默认值 default ‘xx’ #唯一 unique #指定字符集 charset #主键 primary key mysql> create table scores(id int auto_increment primary key,s_id int not null,grade float not null); 数据： mysql> insert into student (id,name,sex,phone) values(122,’wg’,’男’,’110’); mysql> insert into students values(111,’wg’,’121’,’dd’) ; 删： mysql> drop table tablename; mysql> truncate tablename; 快速删除表数据，自增长id从头在来，快速，从磁盘直接删除，不可恢复 mysql> delete from student; 删除整个表的数据，自增长继续改： mysql> alter table oldtable rename newtable; 改表名

2.MySQL查询语句

select_expr(查询字段) -- 可以用 * 表示所有字段。
```
select * from tb;
```
-- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
```
select stu, 29+25, now() from tb;
```
-- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
```
- 使用 as 关键字，也可省略 as.

select stu+10 as add10 from tb;
```

2.from(子句) 用于标识查询来源。

-- 可以为表起别名。使用as关键字。

    select * from tb1 as tt, tb2 as bb;

-- from子句后，可以同时出现多个表。

    -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。

    select * from tb1, tb2;

where 子句 -- 从from获得的数据源中进行筛选。

-- 整型1表示真，0表示假。

-- 表达式由运算符和运算数组成。

-- 运算数：变量（字段）、值、函数返回值

-- 运算符：

    =, <=>, <>, !=, <=, <, >=, >, !, &&, ||, 

    in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor

    is/is not 加上ture/false/unknown，检验某个值的真假

    <=>与<>功能相同，<=>可用于null比较

4.group by 子句, 分组子句 group by 字段/别名 [排序方式]

分组后会进行排序。升序：ASC，降序：DESC

having 子句，条件子句与 where 功能、用法相同，执行时机不同。

where 在开始时执行检测数据，对原数据进行过滤。

having 对筛选出的结果再次进行过滤。

having 字段必须是查询出来的，where 字段必须是数据表存在的。

6.order by 子句，排序子句 order by 排序字段/别名排序方式 [,排序字段/别名排序方式]...

升序：ASC，降序：DESC

支持多个字段的排序。

Openlyg

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
mysql学习笔记

mysql完整学习笔记
复制链接

扫一扫