MySQL笔记

NJU_Hope

已于 2023-06-01 15:34:31 修改

阅读量1.1k

点赞数

文章标签： mysql 数据库

于 2023-03-09 00:29:20 首次发布

本文链接：https://blog.csdn.net/NJU_Hope/article/details/129407781

版权

文章详细介绍了SQL语言中的关键概念，包括表的创建、修改和删除，如CREATETABLE、ALTERTABLE、DROPTABLE，以及TRUNCATE和DELETE的区别。还深入讨论了视图的创建和使用，以及索引的类型、优缺点和创建方法，如主键索引、唯一索引和普通索引。此外，提到了触发器和数据库事务的四大特性，强调了它们在数据完整性维护中的作用。

摘要由CSDN通过智能技术生成

删除表 drop、truncate和delete的区别

字符长度函数char_ength()和字节长度函数ength()

Datediff()与Timestampdiff( )间区别

Date_add(datetime,interva expr type)

Hive和Oracle、MySQL之间的区别

查询语句的执行顺序

FROM(including JOINs) ---> WHERE ---> GROUP BY ---> HAVING ---> SELECT ---> DISTINCT ---> ORDER BY ---> LIMIT/OFFSET

正确的执行顺序应该是：

1.先找到要查询表格或连接要查询的表格，因此FROM才是第一步；

2.接下来是进行条件筛选，所以是WHERE紧随其后；

3.然后如果遇到表格有分组的需要，则需要先GROUP BY；

4.分组时如果也存在筛选条件，这里就要用HAVING进行分组筛选；

5.这些执行过后才是查询操作SELECT；

6.SELECT的时候如果遇到重复数据，就需要去重，即使用DISTINCT;

7.接下来如果要对查询后的数据进行排序，会用到ORDER BY；

8.最后如果要指定返回的查询数据范围、条数则要用LIMIT/OFFSET函数。

SQL语言

共分为四大类：

DDL数据定义语言(Data Definition Language)用来定义数据库对象-----表、视图、索引、同义词、聚簇等如：CREATE TABLE / VIEW / INDEX / SYN / CLUSTER| 表视图索引同义词簇。DDL操作是隐性提交的！不能rollback

DML数据操纵语言(Data Manipulation Language)用来对数据库中的表进行增删改操作；主要有三种形式：1) 插入：INSERT；2) 更新：UPDATE；3) 删除：DELETE

DQL数据查询语言(Data Query Language)用来查询数据库中表的记录；数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE子句组成的查询块：SELECT <字段名表>FROM <表或视图名>WHERE <查询条件>

DCL数据控制语言(Data Control Language)用来定义数据库的访问控制权限和安全级别，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。

表的创建

1.直接创建表：

CREATE TABLE

[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过

(column_name1 data_type1 -- 列名和类型必选

[ PRIMARY KEY -- 可选的约束，主键

| FOREIGN KEY -- 外键，引用其他表的键值

| AUTO_INCREMENT -- 自增ID

| COMMENT comment -- 列注释（评论）

| DEFAULT default_value -- 默认值

| UNIQUE -- 唯一性约束，不允许两条记录该列值相同

| NOT NULL -- 该列非空

], ...

) [CHARACTER SET charset] -- 字符集编码

[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

2.从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old

3.从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

视图

CREATE VIEW <视图名> AS <SELECT语句>

可以解决检索数据时一个表中得不到一个实体所有信息的问题；

视图是虚拟表，是一个依赖于别的表上的寄生表，其本身不存储数据，它们包含的不是数据，包含的是根据需要检索数据的查询。对视图的操作最终都会转化为对基本表的操作；

视图所引用的表称为视图的基表；

若基表的数据发生变化，则变化也会自动反映到视图中；

数据库存储的是视图的定义，不存放视图对应的数据

视图提供了一种封装SELECT语句的层次，其有一下几个好处：

可以简化数据处理
可以格式化数据
可以保护基础数据

不要在视图的语句中使用‘order by ’，如果想要排序就在使用视图之后排序。

修改表

：ALTER TABLE 表名 {修改选项} 。选项集合：

选项集合	作用
ADD COLUMN <列名> <类型>	增加列
CHANGE COLUMN <旧列名> <新列名> <新列类型 >	修改列名或类型
ALTER COLUMN <列名> { SET DEFAULT <默认值> \| DROP DEFAULT }	修改/删除列的默认值
MODIFY COLUMN <列名> <类型>	修改列类型
DROP COLUMN <列名>	删除列
RENAME TO <新表名>	修改表名
CHARACTER SET <字符集名>	修改字符集
COLLATE <校对规则名>	修改校对规则（比较和排序时）

删除表 drop、truncate和delete的区别

1.delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

2.truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

3.drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，

DELETE操作不会减少表或索引所占用的空间。

drop语句将表所占用的空间全释放掉。

（3）速度上，一般而言，drop > truncate > delete

（4）应用范围。

TRUNCATE 只能对TABLE； DELETE可以是table和view

（5）TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。

（6）truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）。drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）delete语句为DML（data manipulation Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 trigger,执行的时候将被触发。

（8）truncate、drop是DDL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名速度快,而且效率高,因为:

truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12）对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

总结：

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

更新表中数据

UPDATE table_name SET

column1 = value1, columnn = valuen

where conditions

通常和replace(String,from_str,to_str) 函数一起使用

例：

Update table_name set

column_name=replace(column_name,from_str,to_str)

Where conditions

插入记录

插入记录的方式汇总：

1.普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)

2.普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)

3.多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...

4.从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

5.带更新的插入：REPLACE INTO table_name VALUES (value1, value2, ...) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

6.若存在相同数据则忽略：

insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据。

INSERT INTO 与 SELECT INTO

INSERT INTO语句用于向一张表中插入新的行。要求目标表存在

语法：

INSERT INTO table_name VALUES (值1, 值2,....)

指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

SELECT INTO 语句从一张表中选取数据插入到另一张表中。常用于创建表的备份复件或者用于对记录进行存档。要求目标表不存在，因为在插入时会自动创建。

语法：把所有的列插入新表

SELECT *

INTO new_table_name [IN externaldatabase]

FROM old_tablename

只把希望的列插入新表

SELECT column_name(s)

INTO new_table_name [IN externaldatabase]

FROM old_tablename

索引：

索引的定义

一种帮助mysql提高查询效率的数据结构。

索引的介绍

索引主要建立在①经常搜索的列；②主键所在列；③外键所在列。

提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用该程序，不用调sql，只要执行正确的create index,查询速度就可能提高成百上千倍，但是天下没有免费的午餐，查询速度的提高是以插入，更新，删除的速度为代价的，这些写操作，增加了大量的IO。所以他的价值，在于提高一个海量数据的检索速度。

索引的优缺点：

索引的优点：

1可以给所有的 MySQL 列类型设置索引。可以大大加快数据的查询速度，这是使用索引最主要的原因。

2在实现数据的参考完整性方面可以加速表与表之间的连接。

3在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

4通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

索引的缺点：创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

常见索引分为：主键索引，唯一索引，普通索引，全文索引，复合索引。

主键索引的索引列值不能为空，而唯一索引的索引列值可以为空，但只能有一个空值。

主键索引的创建及特点

第一种方式：在创建表的时候，直接在字段名后指定primary key

create table user1(id int primary key, name varchar(30));

第二种方式：在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30),primary key(id));

第三种方式：创建表以后再添加主键

create table user3(id int, name varchar(30));

alter table user3 add primary key (id);

主键索引的特点：

1.一个表中，最多有一个主键索引，当然可以使用复合主键

2.创建主键索引的列，它的值不能为null，且不能重复。

3.主键索引的列基本上是int且不可能（或很难）更新．

4.主键索引的效率高（主键不可重复）

5.主健可作外健，唯一索引不可

唯一索引的创建及特点

第一种方式：在表定义时，在某列后直接指定unique

create table user4(id int primary key, name varchar(30) unique);

第二种方式：创建表时，在表的后面指定某列或某几列为unique

create table user5(id int primary key, name varchar(30)，unique(name));

第三种方式：创建表以后再指定

create table user6(id int primary key,name varchar(30));

alter table user6 add unique <索引名> (col1, col2, ...col3);

唯一索引的特点：

一个表中，可以有多个唯一索引
设置唯一索引的列允许有空值，但是只能有一个空值。但允许多个NULL值存在。

3.如果一个唯一索引上指定not null,等价于主键索引。

4.如果在某一列建立唯一索引，必须保证这列不能有重复数据

5.只要保证唯一就可以修改和更新

6.查询效率高

普通索引的创建及特点

第一种方式：在表的定义最后，指定某列为索引;

create table user8(id int primary key, name varchar(20),email varchar(30), index(name)

第一种方式：创建表以后再指定

create table user9(id int primary key, name varchar(20), email varchar(30));

alter table user9 add index <索引名> (col1, col2, ...,);

特点：一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。

如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

全文索引的的创建及特点

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。mysql提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文，不支持中文。

索引的删除与使用

索引的删除：

1. drop方式删除索引：DROP INDEX <索引名> ON <表名>

2. alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

3.alter删除主键索引：Alter table <表名> drop primary key

索引的使用：

1.索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；

2.索引不包含有NULL值的列

3.一个查询只使用一次索引，where中如果使用了索引，order by就不会使用

4.like做字段比较时只有前缀确定时才会使用索引

创建外键语句结构：

alter table <表名>

add constraint foreign key (列名)

references <关联表>（关联列）

with rollup

1、在使用group by 语句后，可能会对数据再次进行汇总，这个时候就需要with rollup。

2、with rollup 就是作用在聚合函数上的。如果聚合函数是COUNT(*)则会在统计的记录中再次求COUNT(*),如果是AVG(),则会在分组

3、with rollup 作用在group by 的第一个字段

Having

Having子句即可包含聚合函数作用的字段也可包括普通的标量字段；

having子句必须于group by 子句同时使用，不能单独使用；

where在分组前过滤，having是在分组后过滤，不冲突，可以同时使用；

select语句中没有聚合函数的使用时也可以用having

grouping()

grouping()是一个聚合函数，用在含有CUBE 或 ROLLUP 语句的SQL语句中，当结果集中的数据行是由CUBE 或 ROLLUP 运算产生的（添加的）则该函数返回1，否则返回0。

语法： grouping( column_name )

其中 column_name 是用在CUBE 或 ROLLUP 运算的列或group by 后的列。

注意：

（1）只有使用了CUBE 或 ROLLUP 运算符的SQL中才能使用GROUPING

（2）grouping后面的列名可以是CUBE 或 ROLLUP 运算符中使用的列名，也可以是group by 中的列名

eg:

select ifnull(driver_id,'总体')
,round(avg(grade),1) as avg_grade
from tb_get_car_order
where grade is not null
group by driver_id
with rollup
-- 保证总体平均评分出现在最后一行    并且保证除最后一行外，其余行按id升序排列
order by  grouping(driver_id) ASC, driver_id asc

REGEXP正则表达式

^: 匹配输入字符串的开始位置

$: 匹配输入字符串的结束位置

[]: 匹配中括号里任意1个

|: 并集，就是或（or)

例如：选取居住地址Address以'C'或'h'开头或以 'et' 结尾的所有数据

SELECT * FROM persons WHERE Address REGEXP '^[Ch]|et$';

字符串函数

字符截取函数

left(), right(), substring(), substring_index()，mid(), substr()

1.left(str, length)

从左边第一位开始截取指定长度字符串。

2.right(str, length) 函数

从右边第一位开始截取指定长度字符串。

3.substring(str, index, [length])，substr(str, index, [length])、mid(str, index, [length])

从指定开始位置（包括这个位置）截取指定长度字符串。

Index为负数表示右数第几位（包括这一位）开始往右截取。

不指定length则默认截取到最后。

4.substring_index(string,sep,num)

从tring中截取第num个sep后的字符串

string：用于截取目标字符串的字符串。可为字段，表达式等。

sep：分隔符，string存在且用于分割的字符，比如“，”、“.”等。

num：序号，为非0整数。若为整数则表示从左到右数，若为负数则从右到左数。substring_index("www.mysql.com", '.' ,1) 输出：‘www’

substring_index("www.mysql.com", '.' ,-1) 输出：‘com’

字符连接函数

1.concat(string1,string2,string3...)

直接把string1、string2和string3等等的字符串无缝拼接起来，返回结果为连接参数产生的字符串。

如有任何一个参数为null ，则返回值为 null。

2.concat_ws(separator,string1,string2,string3...)

直接把string1、string2和string3等等的字符串用特殊字符串separator拼接起来，返回结果为连接参数产生的字符串。

分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 null，则结果为 null。

3.group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc] [separator ‘分隔符’] )

默认分隔符为‘，’

类型转换函数

CAST(value AS datatype)

Convert(value, datatype)

字符长度函数char_ength()和字节长度函数ength()

无论是char_length()还是length()都是为了统计字符串的长度。只不过，char_length()是按照字符来统计的，length()是按照字节来统计的。

例如：一个包含5个字符且每个字符占两个字节(比如汉字)的字符串而言，length()返回长度10，char_length()返回长度是5；如果对于单字节的字符，则两者返回结果相同。

转换字母大小写

uppper()

lower()

字符串包含函数

find_in_set(str,strlist)

返回str在strlist（字符串列表）中第一次出现的位置。

如果str不在strlist 或strlist为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号( , )时将无法正常运行。

数学函数

Max()、min()

对文本数据使用max()时，返回按该列排序后的最后一行。

对文本数据使用min()时，返回按该列排序后的第一行。

保留小数函数

truncate( )函数

truncate(expr, int_expr)函数将expr按照int_expr长度在小数点后按照位数直接进行截取。

实例：

SELECT TRUNCATE(200.12567,4);

输出结果：200.1256

round（）

round(expr, int_expr)函数将expr按照int_expr长度在小数点后按照位数进行四舍五入。

实例：

SELECT ROUND(200.12567,4);

输出结果：200.1257

空值处理COALESCE()函数

主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格式如下：
COALESCE ( expression,value1,value2……,valuen)
COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。

如果expression不为空值则返回expression；否则判断value1是否是空值，

如果value1不为空值则返回value1；否则判断value2是否是空值，

如果value2不为空值则返回value2；……以此类推，
如果所有的表达式都为空值，则返回NULL。

时间函数

时间之间的计算

timestampdiff( ) 函数

返回两个日期之间的时间。

语法: timestampdiff(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。

返回的是enddate-startdate (后者数减前者数) （datediff是前者数减后者数）

datepart 参数可以是下列的值：

Datediff()与Timestampdiff( )间区别

1.Datediff()传入两个参数，前参数-后参数，即大的在前。

Timestampdiff( )传入三个参数，第一个是单位，后两个是时间，且是后时间参数-前时间参数，即大的在后面。

2.Datediff()只取年月日求差

Timestampdiff( )比较精细，根据所给时间单位采用向下取整的方法。不足一天按0天算，不足一小时按0小时算。

Date_add(datetime,interva expr type)

返回与给定日期相差interval时间段的日期时间。

Type取值

Last_day()

得出某日期所在月最后一天和该月天数

last_day(date-expression)

Day(last_day(date-expression))

流程控制函数：

窗口函数：

over()窗口函数的语法结构及常与over()一起使用的分析函数

over()窗口函数的语法结构

函数名 ([expr]) over子句

over()函数中包括三个函数：分区partition by 列名、排序order by 列名、指定窗口范围rows between 开始位置 and 结束位置（可用\可以不用）

若over()函数中不使用这三个函数，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据。

partition by：

partition by可理解为group by 分组。over(partition by 列名)搭配分析函数时，分析函数按照每一组每一组的数据进行计算的。

rows between 开始位置 and 结束位置：

是指定窗口范围，比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时，分析函数按照这个范围进行计算的。

常与over()一起使用的分析函数：

聚合函数 Sum(), count(), avg(), max(), min(),

约束和索引的区别

总结：约束是为了保证表数据的完整性，索引是为了提高查询效率，两者作用不一样！其次种类也不一样。

约束

作用：是为了保证数据的完整性而实现的摘自一套机制，它具体的根据各个不同的数据库的实现而有不同的工具（约束）；

这里主要讲解mysql的约束：

1、非空约束：not null;

指示某列不能存储 NULL 值

2、唯一约束：unique();uk

unique约束的字段，要求必须是唯一的，但null除外；

3、主键约束：primary key();pk

主键约束=not null + unique，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

4、外键约束：foreign key () references (参考),fk

保证一个表中的数据匹配另一个表中的值的参照完整性。语法：constraint fk_dept_id foreign key (dept_id) references dept(dept_id);

5、自增约束：auto_increment

6、默认约束：default

给定默认的值

7、检查性约束：check

保证列中的值符合指定的条件。

示例：

create table member(

id int(10),

phone int(15) unsigned zerofill,

name varchar(30) not null,

constraint uk_name unique(name),

constraint pk_id primary key (id),

constraint fk_dept_id foreign key (dept_id，字段2)

references dept(主表1)(dept_id)

);

//constraint uk_name 可以省略不写。uk_name 唯一约束名称；

//pk_id 主键约束名称；

//fk_dept_id 外键约束名称

索引

作用：快速定位特定数据，提高查询效率，确保数据的唯一性，快速定位特定数据；可以加速表和表之间的连接，实现表与表之间的参照完整性，使用分组和排序语句进行数据检索时，可以显著减少分组和排序的时间全文检索字段进行搜索优化；

索引的种类：

1、主键索引（primary key）;

2、唯一索引（unique）;

3、常规索引（index）;

4、全文索引（full text）;

游标

select语句产生的结果集不能直接对某一行进行操作，这时便可以使用游标逐行处理结果集。

游标的组成：

游标结果集：定义游标的select语句返回的结果集

游标当前行指针：指向该结果集中的某一行的指针

游标的特点：

允许定位结果集中的特定行

允许从结果集的当前位置检索一行或多行

支持对结果集中当前行的数据进行修改

为由其他用户对显示在结果集中的数据所做的更改提供不同级别的可见性支持

当遇到以下情况发生时，游标将自动没定成insensitive选项：

在SELECT 语句中使用distinct、group by、having union语句。
使用outer jon｡
所选取的任意表没有索引。
将实数值当作选取的列。

触发器trigger

create trigger trigger_name

trigger_time trigger_event on tbl_name

for each row

trigger_stmt

其中：

trigger_name：标识触发器名称，用户自行指定；

trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。

【NEW 与 OLD 详解】

MySQL 中定义了 NEW 和 OLD，用来表示

触发器的所在表中，触发了触发器的那一行数据。

具体地：

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

使用方法： NEW.columnName （columnName 为相应数据表某一列名）

参考

https://blog.csdn.net/weixin_41177699/article/details/80302987

维护数据库参照完整性的四种策略：

1、不执行操作

对于违反了参照完整性约束的数据更新，系统不予以执行

2、级联

例如：主键列（被参照表）删除/修改的同时，外键列（参照表）的对应列也要删除/修改

3、置空

例如：即主键列（被参照表）删除/修改的时，外键列（参照表）的对应列要置空

4、设置默认值

例如：即主键列（被参照表）删除/修改的时，外键列（参照表）的对应列的值被置为设定好的默认值

数据库事务的四大特性：

1、原子性：事务包含的所有数据库操作要么全部成功，要不全部失败回滚。且事务是最小的执行单位，不允许分割

2、一致性：一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。执行事务前后，数据保持一致，对同一数据读取的结果相同

3、隔离性：一个事务未提交的业务结果是否对于其它事务可见。级别一般有：read_uncommit，read_commit，read_repeatable，串行化访问。并发访问数据库时，各并发事务之间数据库是独立的

4、持久性：一个事务一旦被提交了，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

4个事务隔离级别

在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

● 未授权读取（Read Uncommitted）：允许脏读取，但不允许更新丢失。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。

● 授权读取（Read Committed）：允许不可重复读取，但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

● 可重复读取（Repeatable Read）：禁止不可重复读取和脏读取，但是有时可能出现幻影数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。

● 序列化（Serializable）：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。