单表查询
SELECT *
FROM products;
SELECT product_id, unit_price
FROM products ;
SELECT product_id, unit_price + 100 AS 'new price' -- 重新赋列名
FROM products ;
SELECT unit_price -- 去重
FROM products
WHERE unit_price >= 0.5 AND unit_price <= 2 ;
-- WHERE unit_price >= 0.5 OR unit_price <= 2;
-- WHERE NOT (unit_price >= 0.5 AND unit_price <= 2);
-- WHERE unit_price BETWEEN 0.5 AND 2 等于 WHERE unit_price >= 0.5 AND unit_price <= 2 ;
-- WHERE quantity_in_stock IN (49, 38, 72) ;-- 在。。。之中
SELECT *
FROM customers
-- WHERE first_name LIKE 'baba%'; --以baba开头
-- WHERE first_name LIKE '%e'; --以e结尾
-- WHERE first_name LIKE '%a%'; -- 包含a
WHERE first_name LIKE 'a_'; -- 以a开头的两个字符 一个_代表一个字符
REGEXP
use sql_store;
SELECT *
FROM customers
-- WHERE last_name LIKE '%gey%';
-- WHERE last_name REGEXP 'gey' 与上一行相等
-- WHERE last_name REGEXP '^gey' ^表示以后面的字符串开头
-- WHERE last_name REGEXP 'gey$' $表示以前面的字符串结尾
-- WHERE last_name REGEXP 'gey|asd|^aaa' 使用|代表一个逻辑上的OR 这样就可以给出多个搜索模武
-- WHERE last_name REGEXP '[abc]e'
-- WHERE last_name REGEXP '[a-f]e'-- 使用[]匹配在插号里列举的单字符
IS NULL
USE sql_store;
SELECT *
FROM customers
WHERE phone IS NULL
ORDER BY
SELECT customer_id + 1 AS 'new id'
FROM customers
-- ORDER BY city, first_name; -- 升序 先以city排序，再以first_name
-- ORDER BY customer_id DESC, city -- 降序
ORDER BY `new id`
LIMIT
SELECT points
FROM customers
-- LIMIT 5 -- 显示前5个
-- LIMIT 3 -- 下标从0开始 所以这里的1 是第二个 从第二个开始的 5 个
ORDER BY points DESC
LIMIT 3 -- LIMIT 语句放最后
合并表查询
合并不同表
-- JOIN ON
SELECT quantity, oi.product_id, p.product_id -- 对于二者都有的列要注明是哪一个表
FROM order_items oi -- oi为数据库简化名
JOIN products p -- 简化名字 p
ON oi.product_id = p.product_id
### 自连接
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
跨数据库连接
SELECT quantity, oi.product_id, p.product_id -- 对于二者都有的列要注明是哪一个表
FROM sql_store.order_items oi -- oi为数据库简化名
JOIN sql_inventory.products p -- 简化名字 p
ON oi.product_id = p.product_id
多表合并查询
USE sql_invoicing;
SELECT
p.date,
p.invoice_id,
p.amount
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
多个条件连接两个表格
一般是某个表有两个主键，即复合主键
USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
隐式链接
-- JOIN ON 显式连接
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
-- 隐式链接 一般不要用 容易忘写 where导致问题
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id
外连接
-- 外连接 OUTER JOIN ON (OUTER可省略)
-- 左连接 所有左表(customers)的记录都会被返回 不考虑条件符合与否
SELECT c.customer_id, c.first_name, o.order_id
FROM customers c
LEFT OUTER JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 右连接 所有右表(customers)的记录都会被返回 不考虑条件符合与否
SELECT c.customer_id, c.first_name, o.order_id
FROM orders o
RIGHT OUTER JOIN customers c
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- 多表外连接
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
JOIN order_statuses os
ON os.order_status_id = o.status
-- 自外连接
USE sql_hr;
SELECT
e.employee_id,
e.last_name,
e.reports_to
FROM employees e
LEFT JOIN employees m
ON e.reports_to = m.employee_id
USING 子句
SELECT *
FROM orders o
RIGHT JOIN customers c
-- ON o.customer_id = c.customer_id -- 等于下面这句
USING (customer_id)
order BY c.customer_id
--
SELECT *
FROM order_items oi
JOIN order_item_notes oin
USING (order_id, product_id)
交叉连接
-- 显示
SELECT *
FROM customers c
CROSS JOIN products p
-- 隐式
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c, products p
ORDER BY c.first_name
UNION 联合
union all 不去重 union 去重
-- UNION 联合 即合并两个查询的结果 要保证两个查询的SELECT后的列个数相同
-- 所得结果以第一个select的列名为列名
SELECT o.order_id
FROM orders o
WHERE order_date >= '2018-01-01'
UNION
SELECT p.name
FROM products p
test：
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
-- ！！！注意 ORDER BY 只能在最后放，前面的查询不能添加
INSERT 插入
-- 1、
INSERT user(name, birth, note) -- 上面的几个列一一对应下面的值
VALUES('lisa', '1997-03-27', 'super-star'),
('Lubin', '2000-01-08', 'xxx\'s husbad');
-- 2、
INSERT user
VALUES(DEFAULT, 'lisa', '1997-03-27', 'super-star'),
(DEFAULT, 'Lubin', '2000-01-08', 'xxx\'s husbad');
-- SELECT LAST_INSERT_ID() -- 自带的方法，返回刚插入的 id
INSERT user
VALUES(LAST_INSERT_ID() + '10', 'yuri', '1995-08-31', 'sex and hot')
表的复制
1、
CREATE TABLE new_user AS -- 这里的新建的表名不用加引号！！！
SELECT * FROM user
2、
CREATE TABLE client AS
SELECT
customer_id,
first_name,
birth_date,
address
FROM `customers`
WHERE birth_date >= '1980-01-01'
根据select结果建表
更新 UPDATE
-- 根据条件进行单行、或多行更新
UPDATE products
SET unit_price = unit_price + '15', name = 'aaa'
WHERE unit_price <= '12.0'
-- 根据select返回结果
UPDATE customers
SET points = points + '1000'
WHERE birth_date >=
(SELECT birth
FROM user
WHERE name = 'Levy')
-- 注意 不支持update和子查询操作的是同一张表
-- 子查询返回的是多个结果时：
WHERE birth_date IN
(SELECT birth
FROM user
WHERE name = 'Levy')
delete 删除
DELETE FROM products -- 删除整个表内容
1、
DELETE FROM `user`
WHERE name = 'xxx'
2、
DELETE FROM user
WHERE name = 'lisa'
3、子查询不能是同一张表
DELETE FROM products
WHERE name = (
SELECT name
from user
where id = '5'
)
数据库创建、恢复
执行创建数据库的sql文件
聚合函数
USE sql_invoicing;
-- 聚合函数
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total * '1.1') AS average,
COUNT(invoice_total) AS number_of_invoices,
COUNT(DISTINCT client_id) AS id
FROM invoices;
-- GROUP BY
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-21'
GROUP BY client_id
ORDER BY total_sales DESC
注意 where、group、order顺序不能变
SELECT
client_id,
sum(invoice_total) AS total_sales,
count(*) AS number
FROM invoices
-- WHERE total_sales > '500 ' AND total_sales <= '1000' 会出错 因为where在group by 之前执行，此时还未分组，无法生成total_sales
GROUP BY client_id
HAVING total_sales > '500 ' AND total_sales <= '1000' -- HAVING 在group by 之前后执行，用到的条件必须是select后出现的
ALL
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = '3'
)
当返回结果里有多个时，用 ALL ，比如上面的表示 大于返回结果中所有的，即 可换成
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = '3'
)
ANY
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MIN(invoice_total)
FROM invoices
WHERE client_id = '3'
)
WHERE invoice_total > ANY(
SELECT invoice_total
FROM invoices
WHERE client_id = '3'
)
当返回结果里有多个时，用 ANY ，比如上面的表示 大于返回结果中某一个即可，即 可换成 MIN
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT client_id
FROM clients
WHERE client_id > '3'
)
或者
WHERE client_id IN (
SELECT client_id
FROM clients
WHERE client_id > '3'
)
EXISTS
-- EXISTS
SELECT *
FROM clients
WHERE client_id IN(
SELECT DISTINCT client_id
FROM invoices
)
-- 执行过程：先执行子查询，返回结果类似如下
SELECT *
FROM clients
WHERE client_id IN(1，2，3，4.。。。。。。)
然后根据结果是否满足条件查询
-- 用 EXISTS 查询，exists会根据条件判断是否符合，返true、false
SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
-- 相比之下， EXISTS 效率更高
IN -> EXISTS
NOT IN -> NOT EXISTS
case when
select
device_id,
gender,
case
when age>=25 then '25岁及以上'
when age>=20 then '20-24岁'
when age<20 then '20岁以下'
else '其他'
end as age_cut
from user_profile
SELECT 中使用子查询
SELECT
client_id,
name,
(SELECT sum(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_prices
FROM clients c
FROM 中使用子查询
SELECT *
FROM (
SELECT
client_id,
name,
(SELECT sum(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_prices
FROM clients c
) AS sales -- 必须重命名
WHERE sales IS NOT NULL
-- 不知道为啥执行不了
数值函数
-- ROUND
-- 1、四舍五入
SELECT ROUND(123.5)
SELECT ROUND('123.5')
-- 2、控制小数位数
SELECT ROUND(12.1234,3)
-- TRUNCATE
-- 控制小数位数
SELECT TRUNCATE(12.1234,3)
-- CEILING(X)返回大于或等于这个数字的最小整数 即向上取整
SELECT CEILING(12.6) -- 返回13
-- FLOOR(X)返回小于或等于这个数字的最小整数 即向下取整
SELECT FLOOR(12.6) -- 返回12
-- ABS(X) 返回绝对值
SELECT ABS(-123.5)
SELECT ABS(-123)
-- RAND() 返回0-1随机值
SELECT RAND()
字符串函数
-- LENGTH 返回长度
SELECT LENGTH('str')
-- UPPER(str) 转换大写
SELECT UPPER('str')
-- LOWER(str) 转换小写
SELECT LOWER('str')
-- LTRIM 移除前导空格
SELECT LTRIM(' str')
-- RTRIM 移除后面空格
SELECT RTRIM('str ')
-- trim 移除前、后空格
SELECT TRIM(' 1111 ')
-- LEFT 返回左边x字符
SELECT LEFT('123456789', 5)
-- LEFT 返回右边x个字符
SELECT RIGHT('123456789', 5)
-- SUBSTRING
SELECT SUBSTRING('12345678', 3, 5) -- 从第三个开始，返回其后5个
SELECT SUBSTRING('12345678', 3) -- 从第三个开始到最后
-- LOCATE(substr,str) 返回substr 在str的位置 从1开始 没有则返回0
SELECT LOCATE('a','fdgdfgaghfd') -- 返回7
SELECT LOCATE('aghfd','fdgdfgaghfd') -- 返回7
SELECT LOCATE('aghfdz','fdgdfgaghfd') -- 返回0
SELECT LOCATE('z','fdgdfgaghfd') -- 返回0
-- REPLACE(str,from_str,to_str) 将str中的 from_str替换为to_str
SELECT REPLACE('12345678', '1234', '0000')
-- CONCAT(str1,str2,...) 合并多个字符串
SELECT CONCAT('12', '3', '456', '7')
时间和日期处理函数
SELECT NOW(); -- 2022-03-04 17:12:16
SELECT CURDATE(); -- 2022-03-04
SELECT CURTIME(); -- 17:12:38
-- 元素提取
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
-- 返回英文名
SELECT DAYNAME(NOW()) -- Friday
SELECT MONTHNAME(NOW()) -- March
-- EXTRACT 适用于所有dbms
SELECT EXTRACT(DAY FROM NOW())
SELECT EXTRACT(YEAR FROM NOW())
SELECT EXTRACT(MONTH FROM NOW())
SELECT EXTRACT(HOUR FROM NOW())
SELECT EXTRACT(MINUTE FROM NOW())
SELECT EXTRACT(SECOND FROM NOW())
格式化日期和时间
-- 日期
SELECT DATE_FORMAT(NOW(), '%y %m %d') -- 22 03 04
SELECT DATE_FORMAT(NOW(), '%Y %M %D') -- 2022 March 4th
-- 时间
SELECT TIME_FORMAT(NOW(), '%h:%i:%s %p') -- 05:35:19 PM 12小时
SELECT TIME_FORMAT(NOW(), '%H:%i:%s %p') -- 17:35:36 PM 24小时
计算时间和日期
SELECT DATE_ADD(NOW(),INTERVAL 10 DAY) -- 往后加10天
SELECT DATE_ADD(NOW(),INTERVAL 10 YEAR) -- 往后加10年
SELECT DATE_ADD(NOW(),INTERVAL 10 MONTH) -- 往后加10月
SELECT DATE_ADD(NOW(),INTERVAL -10 DAY) -- 往后减10天
SELECT DATE_ADD(NOW(),INTERVAL -10 YEAR) -- 往后减10年
SELECT DATE_ADD(NOW(),INTERVAL -10 MONTH) -- 往后减10月
等于
SELECT DATE_SUB(NOW(),INTERVAL -10 DAY) -- 往后加10天
SELECT DATE_SUB(NOW(),INTERVAL -10 YEAR) -- 往后加10年
SELECT DATE_SUB(NOW(),INTERVAL -10 MONTH) -- 往后加10月
SELECT DATE_SUB(NOW(),INTERVAL 10 DAY) -- 往后减10天
SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR) -- 往后减10年
SELECT DATE_SUB(NOW(),INTERVAL 10 MONTH) -- 往后减10月
SELECT DATEDIFF('2018-01-28', '2010-09-18') -- 返回天数间隔
SELECT TIME_TO_SEC('09:18:36') -- 从0:0:0开始的秒数
IFNULL COALESCE
-- IFNULL(expr1,expr2) 当expr1为空时，返回值用expr2代替
-- `COALESCE`(value,...) 当value为空时，用其后面一系列值的第一个非空值代替
SELECT
order_id,
IFNULL(shipper_id,'未注册'),
COALESCE(shipper_id, comments, '未注册')
FROM orders
IF
SELECT
order_id,
order_date,
IF(
YEAR(order_date) = YEAR(NOW()), -- 判断条件
'Active', -- 结果为true时 返回这个
'Archived' -- 结果为false时 返回这个
) AS category
FROM orders
CASE WHEN
-- CASE WHEN 同时判断多个表达式
SELECT
order_id,
CASE
WHEN YEAR(order_date) = YEAR(NOW()) - 2 THEN '11111'
WHEN YEAR(order_date) = YEAR(NOW()) - 3 THEN '22222'
WHEN YEAR(order_date) < YEAR(NOW()) - 4 THEN '33333'
ELSE '666666'
END AS category
FROM orders
create view 创建视图
-- create view 创建视图(将select的查询结果创建为视图)
-- 注意！！！视图是一张虚拟表，并不存储数据
但是对视图的修改会体现在原表中
CREATE VIEW 'shitu_name' AS
SELECT *
FROM payments
更改或删除视图 可更新视图
-- create view 创建视图(将select的查询结果创建为视图)
-- 注意！！！视图是一张数据库中的一张虚拟表，并不存储数据
CREATE VIEW shitu_name AS -- 注意这里的视图名字不要加引号
SELECT
payment_id,
client_id
FROM
payments p -- 删除视图
DROP VIEW shitu_name -- 没有则创建，有则删除在创建
CREATE OR REPLACE VIEW shitu_name AS SELECT
payment_id,
client_id
FROM
payments p
-- 可更新视图
-- 创建视图
CREATE OR REPLACE VIEW user_information AS
SELECT
name,
age,
gender,
YEAR(NOW()) - age AS birth
FROM `user`
-- 对视图进行更新
DELETE from user_information
WHERE age >= '30'
UPDATE user_information
SET gender = 'beauty'
WHERE gender = 'girl'
WITH OPTION CHECK
在创建视图时，在where语句后面添加 where check option ，注意：只有放在where句子后面才管用
作用：
首先视图只操作它可以查询出来的数据，对于它查询不出的数据，即使基表有，也不可以通过视图来操作。
1.对于update,有with check option，要保证update后，数据要被视图查询出来
2.对于delete,有无with check option都一样
4.对于insert,有with check option，要保证insert后，数据要被视图查询出来
对于没有where 子句的视图，使用with check option是多余的。
存储、删除过程
-- 创建存储过程 即创建函数
DELIMITER $$ -- 更改默认分隔符 初始是; 改为 $$ ;(分号留给函数体用)
CREATE PROCEDURE get_infor()
BEGIN
SELECT *
FROM `user`; -- 这里要写分号
END$$
DELIMITER ; -- 更改默认分隔符 改回 ;
-- 删除
DROP PROCEDURE get_infor -- 删除存储过程
DROP PROCEDURE IF EXISTS get_infor
完整写法：
DROP PROCEDURE IF EXISTS get_infor;
DELIMITER $$ -- 更改默认分隔符 初始是; 改为 $$ ;(分号留给函数体用)
CREATE PROCEDURE get_infor()
BEGIN
SELECT *
FROM `user`; -- 这里要写分号
END$$
DELIMITER ; -- 更改默认分隔符 改回 ;
在 存储过程里 添加参数 即有参函数
DROP PROCEDURE IF EXISTS get_infor;
DELIMITER $$
CREATE PROCEDURE get_infor
(
name CHAR(4) -- 这里表示传递的参数，左边是参数名，右边是参数类型
-- char 表示定长字符串 VARCHAR 表示可变长字符串
)
BEGIN
SELECT *
FROM user u
WHERE u.name = name;
END $$
DELIMITER ;
-- 调用
CALL get_infor('lisa')
添加默认参数
DROP PROCEDURE IF EXISTS get_infor;
DELIMITER $$
CREATE PROCEDURE get_infor
(
name VARCHAR(100) -- 可变长度的字符串 最长为100
)
BEGIN
IF name IS NULL THEN
SET name = 'lisa';
END IF;
SELECT *
FROM user u
WHERE u.name = name;
END $$
DELIMITER ;
-- 调用
CALL get_infor(NULL)
CALL get_infor('lisa')
-- if else 也可以这样写
DROP PROCEDURE IF EXISTS get_infor;
DELIMITER $$
CREATE PROCEDURE get_infor
(
name VARCHAR(100) -- 可变长度的字符串 最长为100
)
BEGIN
IF name IS NULL THEN
SELECT * FROM user;
ELSE
SELECT *
FROM user u
WHERE u.name = name;
END IF;
END $$
DELIMITER ;
多参数
DROP PROCEDURE IF EXISTS get_infor;
DELIMITER $$
CREATE PROCEDURE get_infor
(
name VARCHAR(100), -- 可变长度的字符串 最长为100
age INT
)
BEGIN
SELECT *
FROM user u
WHERE u.name = IFNULL(name, u.`name`) AND u.age = age;
END $$
DELIMITER ;
----------------
DROP PROCEDURE IF EXISTS get_payments;
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_id = IFNULL(payment_method_id, p.payment_method);
END $$
DELIMITER ;
CALL get_payments(NULL, NULL)
CALL get_payments(NULL, 2)
参数验证
-- 验证传递的参数正确与否
DROP PROCEDURE IF EXISTS make_payment;
DELIMITER $$
CREATE PROCEDURE make_payment
(
invoice_id INT,
payment_amount DECIMAL(10, 2 ),
payment_date DATE
)
BEGIN
IF payment_amount <= 0 THEN
SIGNAL SQLSTATE '22003'
SET MESSAGE_TEXT = 'Invalid data';
END IF; -- 验证参数
UPDATE invoices i
SET
i.payment_total = payment_amount,
i.payment_date = payment_date
WHERE i.invoice_id = invoice_id;
END $$
DELIMITER ;
CALL make_payment(1, 15, '2020-12-10')
CALL make_payment(1, -195, '2020-12-10')
输出参数
DROP PROCEDURE IF EXISTS get_unpaid_invoices_for_client;
DELIMITER $$
CREATE PROCEDURE get_unpaid_invoices_for_client
(
client_id INT,
OUT invoices_count TINYINT, -- OUT 关键字将参数标记为输出参数
OUT invoices_total DECIMAL(9, 2)
)
BEGIN
SELECT COUNT(*), SUM(invoice_total)
INTO invoices_count, invoices_total
FROM invoices i
WHERE i.client_id = client_id AND payment_total = 0;
END $$
DELIMITER;
-- 尽量不用
变量
-- 在过程里定义变量传值并使用
DROP PROCEDURE IF EXISTS get_risk_factor;
-- factor = invoices_total / invoices_count * 5
DELIMITER $$
CREATE 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 -- INTO 表示赋值
FROM invoices;
SET risk_factor = invoices_total / invoices_count * 5;
SELECT risk_factor;
END $$
DELIMITER;
CALL sql_invoicing.get_risk_factor()
函数
-- 函数
-- 与过程的区别： 过程可以返回多个值，函数只能返回一个值
DROP FUNCTION IF EXISTS get_risk_factor_for_client;
CREATE FUNCTION get_risk_factor_for_client
(
client_id INT
)
RETURNS INTEGER -- 返回值的类型
DETERMINISTIC -- 确定性 相同的输入则输出相同
READS SQL DATA -- 读取
MODIFIES 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 -- INTO 表示赋值
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 factor
FROM clients
TRIGGER 触发器
-- TRIGGER 触发器
DELIMITER $$
CREATE TRIGGER payments_after_insert
AFTER/BEFORE UPDATE/INSERT/DELETE ON payments
FOR EACH ROW -- 作用于每一行 每次执行都会触发
BEGIN
UPDATE invoices
SET payment_total = payment_total + NEW.amount
WHERE invoice_id = NEW.invoice_id;
END $$
DELIMITER ;
USE sql_invoicing;
-- 样例
DELIMITER $$
CREATE TRIGGER payments_after_insert
AFTER INSERT ON payments
FOR EACH ROW -- 作用于每一行 每次执行都会触发
BEGIN
UPDATE invoices
SET payment_total = payment_total + NEW.amount -- NEW.amunt 新插入的数据
-- 相对应的是 OLD.amount 对应原本的数据
WHERE invoice_id = NEW.invoice_id;
END $$
DELIMITER ;
INSERT payments
VALUES(DEFAULT, '1', '4', '2020-01-01', '50', '3')
-- 执行insert之后 会在invoices进行update
– 删除触发器
DROP TRIGGER IF EXISTS payments_after_insert;
事件 EVENT
DELIMITER $$
CREATE EVENT yearly_delete_table_rows
ON SCHEDULE
-- AT '2021-02-27' -- 在具体日期执行
EVERY 1 YEAR STARTS '2022-01-01' ENDS '2025-01-01' -- 从开始日期到结束日期每年一次
-- EVERY 2 DAY -- 每两天执行一次
-- EVERY 3 MONTH -- 每三个月执行一次 开始和结束日期不是必须的
DO BEGIN
DELETE FROM payments
WHERE date < NOW() - INTERVAL 1 YEAR ;-- 日期超过一年的
END $$
DELIMITER ;
事件event的查看、删除、修改、启动、暂停
-- 查看事件
SHOW EVENTS;
SHOW EVENTS LIKE 'yearly%';
-- 删除事件
DROP EVENT IF EXISTS yearly_delete_table_rows;
-- 修改事件
ALTER EVENT yearly_delete_table_rows
DELIMITER $$
CREATE EVENT yearly_delete_table_rows
ON SCHEDULE
-- AT '2021-02-27' -- 在具体日期执行
EVERY 1 YEAR STARTS '2022-01-01' ENDS '2025-01-01' -- 从开始日期到结束日期每年一次
-- EVERY 2 DAY -- 每两天执行一次
-- EVERY 3 MONTH -- 每三个月执行一次 开始和结束日期不是必须的
DO BEGIN
DELETE FROM payments
WHERE date < NOW() - INTERVAL 1 YEAR ;-- 日期超过一年的
END $$
DELIMITER ;
-- 代码和创建过程类似
-- 启动或者暂停事件
ALTER EVENT yearly_delete_table_rows DISABLE;
ALTER EVENT yearly_delete_table_rows ENABLE;
事务
USE sql_store;
START TRANSACTION;
INSERT orders(customer_id, order_date, status)
VALUES(1, '2022-03-13', 1);
INSERT order_items
VALUES(LAST_INSERT_ID(), 1, 1, 1);
COMMIT; -- 提交事务内所有语句
-- ROLLBACK ; 当出现问题时会退回事务并撤销所有更改
事务隔离的四个级别
待定
