一.选择语句
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
}
案例:两种写法
1.
USE sql_store; UPDATE products SET properties = ' { "dimension": [1, 2, 3], "weight": 10, "manufacturer": { "name": "sony" } } ' WHERE product_id =1;
2.
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 支持多个字段的排序。