SQL笔记

以MySQL为例。

基础

唯一标识每行数据的，称为主键。一个数据表只能有一个主键。

用于关联其它表某一列的列，称为外键（foreign key）。

SQL 语句以 ; 作为结束的标志。

增删改查

SELECT 语句用于从数据库中选取数据，并将结果存储在一个临时结果表中，这个表称为结果集。结果集实际上也是一种包含行与列的表，只不过是未持久化的，即临时表。

使用 SELECT DISTINCT 查询不同行

SELECT DISTINCT teacher_id  FROM courses

使用 NOT 过滤不满足条件的数据

SELECT * FROM  teachers WHERE NOT (age > 20 AND country = 'CN')

使用 IN（NOT IN）查询多条件

SELECT * FROM teachers WHERE country  IN（NOT IN） ('CN', 'UK')

使用 BETWEEN AND 查询两值间的数据范围

SELECT * FROM teachers WHERE age BETWEEN 20 AND 25

NULL 并不是值，它表示数值未知或者不确定。NULL 用作未知的或不适用的值的占位符。因此，NULL 无法和 0 或空格字符串 "" 进行比较，甚至 NULL 与 NULL 之间也无法比较。默认地，表的列可以存放 NULL 。

使用 IS NULL（IS NOT NULL）查询空（非空）数据

SELECT * FROM  teachers WHERE email IS NULL（IS NOT NULL）

使用 LIKE 模糊查询

SELECT * FROM courses WHERE name LIKE 'D%'

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序，其具有 ASC（升序）和 DESC（降序）两个关键字，且默认按照升序排列。

ASC ：按升序排列，ORDER BY 默认按照升序对记录进行排序，因此升序的关键字 ASC 可以省去不写。
DESC：按降序排列，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

在对多列进行排序时，ORDER BY 关键字后面列的顺序就是排序的顺序，先按照第一个 column_name 排序，再按照第二个 column_name 排序，依次类推。

结果先按照teacher_id进行排序，如果teacher_id相同则再按照创建时间created_at排序

SELECT name,teacher_id,created_at FROM courses WHERE teacher_id in (1,2,3) ORDER BY teacher_id,created_at DESC

使用 LIMIT 限制输出行数

SELECT * FROM courses ORDER BY student_count LIMIT 3

函数

使用 AVG() 函数求数值列的平均值

SELECT AVG(student_count) AS average_student_count FROM courses

其中 AS 关键字的作用是赋予 AVG(student_count) 计算结果列显示在列表中的别名。

使用 MAX() 函数返回指定列中的最大值、MIN() 函数返回指定列中的最小值、SUM() 函数统计数值列的总数

select max(age)/min(age)/sum(age) as teacher_age from teachers where country = ‘CN’

使用 ROUND() 函数将数值四舍五入：

ROUND( X )：返回参数 X 四舍五入后的一个整数。SELECT ROUND(-1.49)
ROUND(X, D)：返回参数 X 四舍五入且保留 D 位小数后的一个数字。如果 D 为 0，结果将没有小数点或小数部分。

SELECT ROUND(AVG(student_count), 2) AS avg_student_count FROM courses

使用 NULL() 函数判断空值:

ISNULL() 函数用于判断字段是否为 NULL，它只有一个参数 column_name 为列名，根据column_name 列中的字段是否为 NULL 值返回 0 或 1。
IFNULL() 函数也用于判断字段是否为NULL，但是与ISNULL() 不同的是它接收两个参数，第一个参数column_name为列名，第二个参数value 相当于替换值。COALESCE() 用法与IFNULL()相同。

SELECT name, email, ISNULL(email), IFNULL(email, 0), COALESCE(email, 0) FROM teachers

使用 COUNT() 函数计数

COUNT( column_name )函数会对指定列的行数进行计数，但是会除去值为 NULL 的行。该函数主要用于查看各列数据的数量情况，便于统计数据的缺失值。
COUNT()函数会对表中行的数目进行计数，包括值为 NULL 所在行和重复项所在行。该函数主要用于查看表中的记录数。正常来说，表都会有主键，而主键不为空，所以COUNT()在有主键的表中等同于 COUNT(PRIMARY_KEY)，即查询有多少条记录。
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目。

SELECT COUNT(DISTINCT id) AS teacher_count FROM teachers where (country in ('CN','UK')) and (age between 20 and 28)

使用 NOW() 、 CURDATE()、CURTIME() 获取当前时间：

NOW() 可以用来返回当前日期和时间格式：YYYY-MM-DD hh:mm:ss
CURDATE() 可以用来返回当前日期格式：YYYY-MM-DD
CURTIME() 可以用来返回当前时间格式：hh:mm:ss
在使用NOW()和CURTIME()时，如果要精确的秒以后的时间的话，可以在( )中加数字，加多少，就表示精确到秒后多少位，比如，NOW(3)就是精确到毫秒，表示为： 2021-03-31 15:27:20.645

使用 DATE()、TIME() 函数提取日期和时间

SELECT DATE('2021-03-25 16:16:30') AS date,TIME('2021-03-25 16:16:30')  AS time

使用 EXTRACT(unit FROM date)函数提取指定的时间信息：EXTRACT() 函数用于返回日期/时间的unit单独部分，如 YEAR (年)、MONTH (月)、DAY (日)、HOUR (小时)、MINUTE (分钟)、 SECOND (秒)。在一般情况下，EXTRACT(unit FROM date) 与 unit() 的结果相同。如下：

SELECT name, EXTRACT(HOUR FROM created_at) AS created_hourFROM courses
SELECT name, HOUR(created_at) AS created_hourFROM courses

使用 DATE_FORMAT() 格式化输出日期

SELECT DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s') as DATE_FORMAT FROM courses

使用 DATE_ADD() 增加时间、DATE_SUB() 减少时间

SELECT name, DATE_ADD/DATE_SUB(created_at, INTERVAL 1 YEAR) AS new_created FROM courses

使用时间函数 DATEDIFF() 和 TIMESTAMPDIFF() 计算日期差

DATEDIFF() 在 MySQL 中默认只能计算天数差：

SELECT DATEDIFF(created_at, '2019-03-26') AS date_diff FROM courses

TIMESTAMPDIFF() 可以计算两个日期相差的年(YEAR，时间1,时间2)，月(MONTH，时间1,时间2)，周(WEEK，时间1,时间2)，日(DAY，时间1,时间2)，小时(HOUR，时间1,时间2)：

SELECT TIMESTAMPDIFF(MONTH,created_at,'2020-04-22') AS MonthDiff FROM courses

DATEDIFF(时间1,时间2)，TIMESTAMPDIFF (类型,时间1,时间2)，差值计算规则：时间 1 - 时间 2

约束

非空约束 NOT NULL：NOT NULL 约束强制列不接受 NULL 值，强制字段始终包含值，这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录。不要把 NULL 值与空串相混淆。NULL 值是没有值，它不是空串。如果指定' '，这在 NOT NULL 列中是允许的。空串是一个有效的值，它不是无值。NULL 值用关键字 NULL 而不是空串指定。

CREATE TABLE `Persons` (
    `ID` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255) NOT NULL,
    `Age` int
);

唯一约束 UNIQUE：

UNIQUE 约束唯一标识数据库表中的每条记录
UNIQUE 和主键约束均为列或列集合提供了唯一性的保证
主键约束会自动定义一个 UNIQUE 约束，或者说主键约束是一种特殊的 UNIQUE 约束。但是二者有明显的区别：每个表可以有多个 UNIQUE 约束，但只能有一个主键约束。

定义一个 UNIQUE 约束：
CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
UNIQUE (`P_Id`)
)

命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束:
CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)
)

主键约束 PRIMARY KEY：PRIMARY KEY 约束唯一标识数据库表中的每条记录，简单的说，PRIMARY KEY = UNIQUE + NOT NULL。PRIMARY KEY 一般在逻辑设计中用作记录标识，这也是设置 PRIMARY KEY 的本来用意，而 UNIQUE 只是为了保证域/域组的唯一性。PRIMARY KEY 可以与外键配合，从而形成主从表的关系。

如：
表一：用户 id (主键)，用户名
表二: 银行卡号 id (主键)，用户 id (外键)
则表一为主表，表二为从表。

定义一个主键：
CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    PRIMARY KEY (`P_Id`)
);
命名并定义多个列的 PRIMARY KEY 约束：
CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
)

外键约束 FOREIGN KEY：一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY 。外键最根本的作用：保证数据的完整性和一致性。

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

检查约束 CHECK：CHECK 约束用于限制列中的值的范围，评估插入或修改后的值。满足条件的值将会插入表中，否则将放弃插入操作。可以为同一列指定多个 CHECK 约束。

添加 一个 CHECK 约束：
CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)
为多个列添加 CHECK 约束：
CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

默认约束 DEFAULT：DEFAULT 约束用于向列中插入默认值。

联结

SQL JOIN 连接子句用于将数据库中两个或者两个以上表中的记录组合起来。主要分为 INNER JOIN（内连接）、OUTER JOIN（外连接）、全连接（FULL JOIN）和交叉连接（CROSS JOIN），其中 OUTER JOIN 又可以细分为 LEFT JOIN（左连接）和 RIGHT JOIN（右连接）。

因此，我们主要使用的 JOIN 连接类型如下：

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行
CROSS JOIN：又称笛卡尔积，两个表数据一一对应，返回结果的行数等于两个表行数的乘积

内连接 INNER JOIN：取两个表的交集，返回的结果是连接的两张表中都满足条件的部分。

SELECT courses.id,courses.name as course_name,teachers.name as teacher_name from courses inner join teachers on courses.teacher_id=teachers.id where teachers.name='Eastern Heretic'

左外连接 LEFT JOIN：左连接会返回左表中的所有记录，加上右表中匹配到的记录。如果左表的某行在右表中没有匹配行，那么在相关联的结果行中，右表的所有选择列表均为空值。

SELECT courses.name as course_name,teachers.name as teacher_name from teachers left join courses on courses.teacher_id=teachers.id where teachers.country='CN'

右外连接 RIGHT JOIN：同上，如果右表的某行在左表中没有匹配行，左表就返回空值。

全外连接 FULL (OUTER) JOIN：FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行。FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。MySQL 数据库不支持全连接，想要实现全连接可以使用 UNION（ ALL）来将左连接和右连接结果组合在一起实现全连接。

如果子句中有 order by、limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

full join方式：
SELECT courses.name as course_name,teachers.name as teacher_name,teachers.country as teacher_country from courses full join teachers on courses.teacher_id=teachers.id

union方式：
SELECT courses.name as course_name,teachers.name as teacher_name,teachers.country as teacher_country from courses left join teachers on courses.teacher_id=teachers.id
union
SELECT courses.name as course_name,teachers.name as teacher_name,teachers.country as teacher_country from courses right join teachers on courses.teacher_id=teachers.id

交叉连接 CROSS JOIN：返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接不存在 ON 子句，可增加联结条件，使用 WHERE 子句筛选过滤无效的数据。

隐式交叉连接：不需要使用 CROSS JOIN 关键字，只要在 SELECT 语句的 FROM 语句后将要进行交叉连接的表名列出即可。
显式交叉连接：与隐式交叉连接的区别就是它使用 CROSS JOIN 关键字。

隐式交叉连接
SELECT  c.name, t.name FROM c,t
显式交叉连接
SELECT  c.name, t.name FROM c CROSS JOIN t

分组

GROUP BY 子句：用来实现分组的函数，其用于结合聚合函数，能根据给定数据列的每个成员对查询结果进行分组统计，最终得到一个分组汇总表。

SELECT age,IFNULL(count(age),0) as age_count from teachers group by age order by age DESC
SELECT teachers.name as teacher_name,IFNULL(count(courses.name),0) as course_count from teachers left join courses on teachers.id=courses.teacher_id group by teacher_name order by course_count desc,teacher_name

HAVING 子句：HAVING 子句经常与 GROUP BY 联合使用，HAVING 子句就是对分组统计函数进行过滤的子句。HAVING 子句对于 GROUP BY 子句设置条件的方式其实与 WHERE 子句与 SELECT 的方式类似，语法也相近，但 WHERE 子句搜索条件是在分组操作之前，而 HAVING 则是在之后。

SELECT * FROM teachers where country in (SELECT country FROM teachers group by country having avg(age)>(SELECT avg(age) FROM teachers))

子查询

当一个查询是另一个查询的条件时，称之为子查询。即在查询语句中的 WHERE 条件子句中，又嵌套了另一个查询语句。子查询本质上就是一个完整的 SELECT 语句，它可以使一个 SELECT、INSERT INTO 语句、DELETE 语句或 UPDATE 语句嵌套在另一子查询中。子查询的输出可以包括一个单独的值（单行子查询）、几行值（多行子查询）、或者多列数据（多列子查询）。

select name from teachers where id=(select teacher_id from courses where name='Big Data') # 查询数据
insert into teachers_bkp select * from teachers where age>20  # 数据备份
update courses set name='PHP',student_count='300' where teacher_id=(select id from teachers where name='Eastern Heretic') # 修改数据
delete from teachers where id in (select teacher_id from courses where created_at<'2020-01-01') # 删除数据

内联视图子查询：将子查询插入到表名table_name的位置。前面的子查询的方法，是将子查询插入到列名column_name 的位置，将子查询的结果作为列名。内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作。

普通子查询：
select courses.name as course_name,courses.student_count,teachers.name as teacher_name from teachers left join courses on courses.teacher_id=teachers.id where student_count=(select max(student_count) from courses)
内联视图子查询:
select * from (select courses.name as course_name,courses.student_count,teachers.name as teacher_name from teachers left join courses on courses.teacher_id=teachers.id) T where student_count=(select max(student_count) from courses)

IN (NOT IN)操作符多行子查询：将子查询返回的集合和外层查询得到的集合进行交集（差集）运算。

select name from courses where teacher_id in (select id from teachers where age > 20)

ANY 操作符进行多行子查询：在子查询中使用 ANY ，表示与子查询返回的任何值比较为真，则返回真。

select name from courses where created_at > any(select created_at from courses where teacher_id = (select id from teachers where name='Southern Emperor')) and  teacher_id <> (select id from teachers where name='Southern Emperor')

ALL 操作符多行子查询：在子查询中使用 ALL ，表示与子查询返回的所有值比较为真，则返回真。

select * from courses where student_count > all (select student_count from courses where teacher_id in (select id from teachers where age = (select max(age) from teachers)))

子查询结果是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝，>，<，>=，<=， <> 等）来进行比较
子查询结果是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ANY，ALL 等）来进行比较

select name,student_count from courses where (teacher_id,student_count) in (select teacher_id,max(student_count) from courses group by teacher_id)

HAVING 子句中的子查询：子查询出现在 HAVING 子句中时，表示要进行分组过滤，它被用作行组选择的一部分，一般返回单行单列的数据。

select teachers.country,IFNULL(sum(courses.student_count),0) as student_count from teachers 
left join courses on courses.teacher_id=teachers.id 
group by teachers.country having (student_count between 2000 and 5000) and teachers.country like 'U%' 
order by student_count desc, teachers.country

事务

MySQL 事务允许你执行一组 MySQL 操作，以确保数据库从不包含部分操作的结果。在一组操作中，如果其中一个操作失败，就会发生回滚，将数据库恢复到其原始状态。如果没有发生错误，整个语句集就会提交到数据库中。

控制事务：

为了启动一个事务，你使用 START TRANSACTION 语句。BEGIN 或 BEGIN WORK 是 START TRANSACTION`的别名。
要提交当前事务并使其变化永久化，你要使用COMMIT`语句。
要回滚当前事务并取消其变化，你可以使用ROLLBACK语句。
要禁用或启用当前事务的自动提交模式，你可以使用SET autocommit语句。

BEGIN;
select @id:=max(id)+1 from teachers;
insert into teachers values(id,'Kansas',NULL,41,'UK');
COMMIT;

锁

锁：计算机协调多个进程或线程并发访问某一资源的机制。

如何保证数据并发访问的一致性，有效性，是所有数据库必须要解决的问题。

加锁是消耗资源的，锁的各种操作，包括：获得锁、检测锁是否已经解除、释放锁等，都会增加系统的开销。

SHOW VARIABLES like '%isolation%';# 查看当前数据库的事务隔离级别
show variables like '%innodb_autoinc_lock_mode%'; # 查询当前数据库的自增锁模式

自增锁：是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

innodb_autoinc_lock_mode = 0
在这一模式下，所有的insert语句都要在语句开始的时候得到一个表级的auto_inc锁，在语句结束的时候才释放，这个就影响到了并发的插入，但是主从同步时候是安全的
innodb_autoinc_lock_mode = 1
这一模式是MySQL的默认模式，这个模式的好处是auto_inc锁不要一直保持到语句的结束，只要语句得到了相应的值后就可以提前释放锁
innodb_autoinc_lock_mode = 2
无自增锁，会导致数据自增id不是连续的问题，同一批insert提交自增不连续

共享锁（Shared Locks）：简称为 S 锁，读取数据时候可以加 S 锁。共享锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。

排它锁（Exclusive Locks）：简称为 X 锁，修改数据时候加 X 锁。排它锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

意向锁（Intention Locks; table-level lock）：一种特殊的表级锁，意向锁是为了让 InnoDB 多粒度的锁能共存而设计的。取得行的共享锁和排他锁之前需要先取得表的意向共享锁（IS）和意向排他锁（IX）。意向共享锁和意向排他锁都是系统自动添加和自动释放的，整个过程无需人工干预。意向锁就是指未来的某一个时刻事务可能要加共享锁或者排它锁，提前声明一个意向。

意向共享锁（Intention Shared Lock） IS：事务有意向对表中的某些行加共享锁（S锁）
意向排它锁（Intention Exclusive Lock）IX：事务有意向对表中的某些行加排他锁（X锁）

记录锁（Record Locks）：记录锁不是锁定记录数据本身，而是锁定索引记录。如果要锁的列没有索引，则会进行全表记录加锁。

间隙锁（Gap Locks）：默认存在于可重复读的事务隔离级别中的锁，锁定被圈定的范围不允许insert，防止不可重复读。我们的事务隔离级别都是读已提交，默认会产生不可重复读的问题。

临键锁（Next-Key Locks）：本质是记录锁加上间隙锁，数据库可重复读事务隔离级别默认存在。

插入意向锁（Insert Intention Locks）：间隙锁的一种，专门针对 insert 操作。多个事务在同一个索引同一个范围区间插入记录时候，如果插入位置不冲突，不会彼此阻塞。

# 对teachers 表上读锁
LOCK TABLES teachers READ;
UNLOCK TABLES;
select @id:=max(id)+1 from teachers;
insert into teachers values(id,'Feng Qingyang','feng.qingyang@163.com',37,'CN');

隐式上锁（默认，自动加锁、自动释放）：InnoDB 行锁的加锁的方式是自动加锁

对于 UPDATE、DELETE、INSERT 操作，InnoDB 会自动给涉及数据集添加排他锁
对于 SELECT 操作，InnoDB 不会添加任何锁

显示上锁（手动）：InnoDB 手工加锁方式

LOCK IN SHARE MODE 与 FOR UPDATE`只能在事务内其作用，以保证当前会话事务锁定的行不会被其他会话修改。

-- 读锁 --
SELECT * FROM teachers WHERE id=3 LOCK IN SHARE MODE
-- 写锁 --
SELECT * FROM table_name FOR UPDATE

查看表锁情况：SHOW OPEN TABLES

表锁分析：SHOW STATUS LIKE 'table%'

行锁分析：SHOW STATUS LIKE 'innodb_row_lock%'

乐观锁和悲观锁：用来解决并发控制的问题。

乐观锁：从应用系统层面上做并发控制，去加锁。实现乐观锁常见的方式：版本号version。使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行 +1 操作，并判断当前版本号是不是该数据的最新的版本号。

悲观锁：从数据库层面上做并发控制，去加锁。悲观锁的实现方式有两种：共享锁（读锁）和排它锁（写锁）。

共享锁（IS锁）:实现方式是在 SQL 语句后加 LOCK IN SHARE MODE，比如SELECT ... LOCK IN SHARE MODE，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加 IS 锁，但是无法修改这些记录直到你这个加锁的 session 执行完成（否则直接锁等待超时）。
排它锁（IX锁）:实现方式是在 SQL 语句后加FOR UPDATE，比如SELECT ... FOR UPDATE，即在符合条件的rows上都加了排它锁，其他 session 也就无法在这些记录上添加任何的 S 锁或 X 锁。如果不存在一致性非锁定读的话，那么其他 session 是无法读取和修改这些记录的。

触发器

在 MySQL 中，触发器是一个存储程序，它对相关表中发生的插入、更新或删除等事件自动调用。MySQL 支持响应 INSERT、UPDATE 或 DELETE 事件而调用的触发器。

SQL 标准定义了两种类型的触发器：行级触发器和语句级触发器。

行级触发器是为每条插入、更新或删除的记录激活的。
语句级触发器对每个事务执行一次，不管有多少行被插入、更新或删除。

MySQL 只支持行级触发器，不支持语句级触发器。

创建触发器，可以使用NEW 和OLD修饰符，访问更新前后的值OLD.description和新值NEW.description。

格式：
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

示例：
CREATE TRIGGER before_teachers_update 
    BEFORE update ON teachers 
    FOR EACH ROW 
SET new.country = 'CN';

如果在触发器主体中有多条语句，必须使用 BEGIN END 块并改变默认的分隔符。
格式：
DELIMITER $
CREATE TRIGGER trigger_name
    BEFORE INSERT
    ON table_name FOR EACH ROW
BEGIN
    -- statements
END $    
DELIMITER ;

示例：
DELIMITER $
CREATE TRIGGER before_courses_insert
BEFORE INSERT ON courses FOR EACH ROW
BEGIN
	DECLARE TID INT;
	select id into TID from teachers where name=new.name;
	if TID==NULL then 
		update courses set teacher_id=0,created_at=NULL where name=new.name;
END $
DELIMITER;

显示当前数据库中的所有触发器：SHOW TRIGGERS

删除触发器：DROP TRIGGER trigger_name

如果你删除一个表，MySQL 将自动放弃与该表相关的所有触发器。

MYSQL中触发器中不能对本表进行insert,update,delete操作，以免递归循环触发

对于update只能用set进行操作，insert与delete只能借助第二张表才能实现需求，如果可以最好用存储过程代替触发器

posted @ 2021-11-21 12:52 YJXZ 阅读(107) 评论(0) 编辑收藏举报

刷新页面返回顶部

YJXZ

熟读唐诗三百首，不会作诗也会吟

SQL笔记

基础

增删改查

函数

约束

联结

分组

子查询

事务

锁

触发器

公告