mysql总结-长

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql总结-长，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含28470字，纯文字阅读大概需要41分钟。

内容图文

数据库类型介绍
https://www.cnblogs.com/zhaojingyu/p/8873991.html
三大范式
https://www.cnblogs.com/wsg25/p/9615100.html
https://www.cnblogs.com/knowledgesea/p/3667395.html
第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，
但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表。
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

mysql服务与多实例
https://www.cnblogs.com/zhaojingyu/p/8874125.html

三级模式和两级映像
数据库系统设计员可在视图层、逻辑层和物理层对数据抽象，通过外模式、概念模式和内模式来描述不同层次上的数据特性。

模式（Schema）
定义：也称逻辑模式，是数据库中全体数据的逻辑结构和特征的描述，是所有用户的公共数据视图。
模式（Schema）
理解：
① 一个数据库只有一个模式；
② 是数据库数据在逻辑级上的视图；
③ 数据库模式以某一种数据模型为基础；
④ 定义模式时不仅要定义数据的逻辑结构（如数据记录由哪些数据项构成，数据项的名字、类型、取值范围等），而且要定义与数据有关的安全性、完整性要求，定义这些数据之间的联系。
又称概念模式或逻辑模式。是对所有用户数据逻辑结构和特征的所有描述。主要由数据库设计者进行DDL语言进行描述和定义。体现了数据库的整体观。

外模式（External Schema）
定义：也称子模式（Subschema）或用户模式，是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示。
理解：
① 一个数据库可以有多个外模式；
② 外模式就是用户视图；
③ 外模式是保证数据安全性的一个有力措施。
对应于用户级，是某个或某几个用户所能看到的数据库的数据视图，是从模式导出的一个子集，故又称子模式。用户主要通过DML语言对外模式数据进行操作。外反应了数据库的用户观。

内模式（Internal Schema）
定义：也称存储模式（Storage Schema），它是数据物理结构和存储方式的描述，是数据在数据库内部的表示方式（例如，记录的存储方式是顺序存储、按照B树结构存储还是按hash方法存储；索引按照什么方式组织；数据是否压缩存储，是否加密；数据的存储记录结构有何规定）。
理解：
① 一个数据库只有一个内模式；
② 一个表可能由多个文件组成，如：数据文件、索引文件。
它是数据库管理系统(DBMS)对数据库中数据进行有效组织和管理的方法下一题
其目的有：
② 为了减少数据冗余，实现数据共享；
② 为了提高存取效率，改善性能。
又称存储模式，对应于物理级。描述了数据在物理介质上的存储方式和物理结构。体现了数据库的存储观。

1.共享表空间：MySQL服务实例承载的所有数据库的所有InnoDB表的数据信息、索引信息、各种元数据信息以及事务的回滚（UNDO）信息，全部存放在共享表空间文件中。
默认情况下该文件位于数据库根目录下，文件名是ibdata1，且文件的初始大小为10M。可以使用MySQL命令“show variables like ‘innodb_data_file_path’;”查看该文件的的属性
2.独享表空间：如果将全局系统变量innodb_file_per_table的值设置为ON（innodb_file_per_table的默认值为OFF），那么之后再创建InnoDB存储引擎的新表，这些表的数据信息、索引信息都将保存到独享表空间文件。.frm .ibd文件

使用SQL语句
drop table table_name;
即可删除名为table_name的表。
删除表后，MySQL服务实例会自动删除该表结构定义文件（例如second_table.frm文件），以及数据、索引信息。该命令慎用！

MySQL数据库备份和恢复
步骤1：准备工作
方法一：停止MySQL服务
方法二：使用MySQL命令“flush tables with read lock;”将服务器内存中的数据“刷新”到数据库文件中，同时锁定所有表，以保证备份期间不会有新的数据写入。
步骤2：备份文件的选取
如果数据库中全部是MyISAM存储引擎的表，最为简单的数据库备份方法就是直接“备份”整个数据库目录。
如果某个数据库中还存在InnoDB存储引擎的表，此时不仅需要“备份”整个数据库目录，还需要备份ibdata1表空间文件以及重做日志文件ib_logfile0与ib_logfile1。
步骤2：备份文件的选取
数据库备份时，建议将MySQL配置文件（例如my.ini配置文件）一并进行备份。
步骤3：数据库恢复
首先停止MySQL服务；然后将整个数据库目录、MySQL配置文件（例如my.ini配置文件）、ibdata1共享表空间文件以及重做日志文件ib_logfile0与ib_logfile1复制到新MySQL服务器对应的路径，即可恢复数据库中的数据。

字段类型
date表示日期，默认格式为‘YYYY-MM-DD’；
time表示时间，格式为‘HH:ii:ss[.fraction]’；
year表示年份；
datetime与timestamp是日期和时间的混合类型，格式为'YYYY-MM-DD HH:ii:ss'
datetime与timestamp都是日期和时间的混合类型，区别在于：
表示的取值范围不同，datetime的取值范围远远大于timestamp的取值范围。
将NULL插入timestamp字段后，该字段的值实际上是MySQL服务器当前的日期和时间。
同一个timestamp类型的日期或时间，不同的时区，显示结果不同。

TIMESTAMP和DATETIME的不同点：
1> 两者的存储方式不一样
对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
而对于DATETIME，不做任何改变，基本上是原样输入和输出
2> 两者所能存储的时间范围不一样
timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。
总结：TIMESTAMP和DATETIME除了存储范围和存储方式不一样，没有太大区别。当然，对于跨时区的业务，TIMESTAMP更为合适。

学会使用now()函数
注意：now()函数用于获得MySQL服务器的当前时间，该时间与时区的设置密切相关。
一、MySQL中如何表示当前时间？
其实，表达方式还是蛮多的，汇总如下：
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
NOW()
LOCALTIME
LOCALTIME()
LOCALTIMESTAMP
LOCALTIMESTAMP()

create table 表名(
字段名1 数据类型 [约束条件],
…
[其他约束条件],
…
[ unique | fulltext ] index [索引名] ( 字段名 [(长度)] [ asc | desc ] )
) engine=存储引擎类型 default charset=字符集类型

CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`hiredate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf-8

create table book(
isbn char(20) primary key,
name char(100) not null,
brief_introduction text not null,
price decimal(6,2),
publish_time date not null,
unique index isbn_unique (isbn),
index name_index (name (20)),
fulltext index brief_fulltext (name,brief_introduction),
index complex_index (price,publish_time)
) engine=MyISAM default charset=gbk;

复制一个表结构的实现方法有两种。
方法一：在create table语句的末尾添加like子句，可以将源表的表结构复制到新表中，语法格式如下。
create table 新表名 like 源表

拷贝数据： insert into 新表名 select * from 源表
方法二、在create table语句的末尾添加一个select语句，可以实现表结构的复制，甚至可以将源表的表记录拷贝到新表中。下面的语法格式将源表的表结构以及源表的所有记录拷贝到新表中。
create table 新表名 select * from 源表

1．删除字段
删除表字段的语法格式如下。
alter table 表名 drop 字段名
2．添加新字段
向表添加新字段时，通常需要指定新字段在表中的位置。向表添加新字段的语法格式如下。
alter table 表名 add 新字段名 新数据类型 [ 新约束条件 ] [ first | after 旧字段名]
alter table MyClass add passtest int(4) default '0';
3．修改字段名（或者数据类型）
（1）修改表的字段名（及数据类型）的语法格式如下。(或用modify)
alter table 表名 change 旧字段名 新字段名 新数据类型
（2）如果仅对字段的数据类型进行修改，可以使用下面的简写语法格式。
alter table 表名 modify 字段名 新数据类型
https://blog.csdn.net/dba_waterbin/article/details/17884549
1．添加约束条件
向表的某个字段添加约束条件的语法格式如下（其中约束类型可以是唯一性约束、主键约束及外键约束）。
alter table 表名 add constraint 约束名 约束类型 (字段名)
alter table employee add index emp_name (name);
alter table employee add primary key(id);
alter table employee add unique index emp_name2(cardnumber);

2．删除约束条件
（1）删除表的主键约束条件语法格式比较简单，语法格式如下。
alter table 表名 drop primary key
（2）删除表的外键约束时，需指定外键约束名称，语法格式如下（注意需指定外键约束名）。
alter table 表名 drop foreign key 约束名
修改表的其他选项
alter table 表名 engine=新的存储引擎类型
alter table 表名 default charset=新的字符集
alter table 表名 auto_increment=新的初始值
alter table 表名 pack_keys=新的压缩类型
修改表名的语法格式较为简单，语法格式如下。
rename table 旧表名 to 新表名
该命令等效于：alter table 旧表名 rename 新表名

方法二、在已有表上创建索引
语法格式一：
create [ unique | fulltext ] index 索引名 on 表名 ( 字段名 [(长度)] [ asc | desc ] )
语法格式二：
alter table 表名 add [ unique | fulltext ] index 索引名 ( 字段名 [(长度)] [ asc | desc ] )
删除索引的语法格式如下。
drop index 索引名 on 表名

insert into 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
注意：字段列表1与字段列表2的字段个数必须相同，且对应字段的数据类型尽量保持一致。
如果源表与目标表的表结构完全相同，“(字段列表1)”可以省略。

replace语句的语法格式有三种语法格式。
语法格式1：replace into 表名 [（字段列表）] values （值列表）
语法格式2：replace [into] 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
语法格式3：
replace [into] 表名
set 字段1=值1, 字段2=值2
使用replace的最大好处就是可以将delete和insert合二为一，形成一个原子操作，这样就无需将delete操作与insert操作置于事务中了。
例如：清空记录的表如果是父表，那么truncate命令将永远执行失败。如果使用truncate table成功清空表记录，那么会重新设置自增型字段的计数器。truncate table语句不支持事务的回滚，并且不会触发触发器程序的运行。

select语句的语法格式如下。
select 字段列表
from 数据源
[ where条件表达式 ]
[ group by 分组字段
[ having条件表达式 ]
]
[ order by 排序字段 [ asc | desc ] ]

MySQL中的两个谓词distinct和limit可以限制记录的行数。
select字段列表
from数据源
limit [start,]length;
start表示从第几行记录开始检索，length表示检索多少行记录。表中第一行记录的start值为0。

使用union可以将多个select 语句的查询结果集组合成一个结果集。
select 字段列表1 from table1
union [all]
select 字段列表2 from table2...
说明：字段列表1与字段列表2的字段个数必须相同，且具有相同的数据类型。合并产生的新结果集的字段名与字段列表1中的字段名对应。

union 与 union all 的区别：
当使用union时，MySQL 会筛选掉select结果集中重复的记录（在结果集合并后会对新产生的结果集进行排序运算，效率稍低）。而使用union all时，MySQL会直接合并两个结果集，效率高于union。如果可以确定合并前的两个结果集中不包含重复的记录，建议使用union all。

子查询
如果一个select语句能够返回单个值或者一列值，且该select语句嵌套在另一个SQL语句中（例如select语句、insert语句、update语句或者delete语句）中，那么该select语句称为子查询（也叫内层查询），包含子查询的SQL语句称为主查询（也叫外层查询）。为了标记子查询与主查询之间的关系，通常将子查询写在小括号内。
子查询分为相关子查询与非相关子查询。
子查询一般用在主查询的where子句或having子句中，与比较运算符或者逻辑运算符一起构成where筛选条件或having筛选条件。 如果子查询返回单个值，则可以将一个表达式的值与子查询的结果集进行比较。
子查询可以仅仅使用自己定义的数据源，也可以“直接引用”主查询中的数据源，但两者意义完全不同。
如果子查询中仅仅使用了自己定义的数据源，这种查询是非相关子查询，非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给主查询。
如果子查询中使用了主查询的数据源，这种查询是相关子查询，此时主查询的执行与相关子查询的执行相互依赖。

子查询经常与in运算符一起使用，用于将一个表达式的值与子查询返回的一列值进行比较，如果表达式的值是此列中的任何一个值，则条件表达式的结果为true；否则为false。
exists逻辑运算符用于检测子查询的结果集是否包含有记录，如果结果集中至少包含一条记录，则exists的结果为true；否则为false。在exists前面加上not时，与上述结果恰恰相反。
any运算符通常与比较运算符一起使用。使用any运算符时，通过比较运算符将一个表达式的值与子查询返回的一列值逐一进行比较，若某次的比较结果为true，则整个表达式的值为true；否则为false。any逻辑运算符的语法格式如下。
表达式 比较运算符 any(子查询)
“表达式 > any(子查询)”
类似的还有all运算符：表达式 > all(子查询)

使用正则表达式模糊查询
字段名 [not] regexp [binary] '正则表达式'
例如检索以“程序设计”结尾的课程信息，可以使用下面的SQL语句。
select * from course where course_name regexp '程序设计$';
例如检索学生联系方式中以15开头或者18开头，且后面跟着9位数字的学生信息，可以使用下面的SQL语句。
select * from student where student_contact regexp '^1[58][0-9]{9}';
大多数时候，使用like关键字或者正则表达式对字符串进行模糊查询，需要对表进行全表扫描，检索效率较低。

MySQL全文检索使用特定的分词技术、利用查询关键字和查询字段内容之间的相关度进行检索，通过全文检索，可以有效提升字符串检索效率。
全文检索的语法格式如下。
select字段列表
from 表名
where match (全文索引字段1,全文索引字段2,...) against (搜索关键字 [ 全文检索方式 ])
在2011年发布的5.6版本中InnoDB存储引擎表实现了全文检索的支持，这将大幅提升InnoDB存储引擎的字符串检索效率，实现更快速、更高质量的模糊查询。

变量分为系统变量（以@@开头）以及用户自定义变量。
用户自定义变量分为用户会话变量（以@开头）以及局部变量（不以@开头） 。
（1）用户会话变量的定义与赋值
一般情况下，用户会话变量的定义与赋值会同时进行。用户会话变量的定义与赋值有两种方法：使用set命令或者使用select语句。
方法一：使用set命令定义用户会话变量，并为其赋值，语法格式如下：
set @user_variable1＝expression1 [,@user_variable2= expression2 , …]
方法二：使用select语句定义用户会话变量，并为其赋值，语法格式有两种。
第一种语法格式：select @user_variable1:＝expression1 [,user_variable2:= expression2 , …]
第二种语法格式：select expression1 into @user_variable1, expression2 into @user_variable2,…
说明：
第一种语法格式中需要使用“:＝”赋值语句，原因在于“=”是为“比较”保留的。
第一种与第二种语法格式的区别在于：第一种语法格式中的select语句会产生结果集，第二种语法格式中的select语句，仅仅用于会话变量的定义及赋值（但不会产生结果集）。

3．局部变量
declare命令专门用于定义局部变量及对应的数据类型。局部变量必须定义在存储程序中（例如函数、触发器、存储过程以及事件中）
场合一：局部变量定义在存储程序的begin-end语句块（稍后介绍）之间。此时局部变量首先必须使用declare命令定义，并且必须指定局部变量的数据类型。只有定义局部变量后，才可以使用set命令或者select语句为其赋值。
场合二：局部变量作为存储过程或者函数的参数使用，此时虽然不需要使用declare命令定义，但需要指定参数的数据类型。
场合三：局部变量也可以用在SQL语句中。数据检索时，如果select语句的结果集是单个值，可以将select语句的返回结果赋予局部变量，局部变量也可以直接嵌入到select、insert、update以及delete语句的条件表达式中。
如果局部变量嵌入到SQL语句中，由于局部变量名前没有“@”符号，这就要求局部变量名不能与表字段名同名，否则将出现无法预期的结果。

create function 函数名（参数1，参数2，…）returns 返回值的数据类型
[函数选项]
begin
函数体;
return 语句;
end;

[循环标签:]while 条件表达式 do
循环体;
end while [循环标签];
说明：end while后必须以“;”结束。

[循环标签:]repeat
循环体;
until 条件表达式
end repeat [循环标签];
说明：end repeat后必须以“;”结束。

[循环标签:] loop
循环体;
if 条件表达式 then
leave [循环标签];
end if;
end loop;
说明：end loop后必须以“;”结束。

leave语句用于跳出当前的循环语句（例如while语句），语法格式如下。
leave 循环标签;
说明：leave 循环标签后必须以“;”结束。

iterate语句用于跳出本次循环，继而进行下次循环。iterate语句的语法格式如下。
iterate 循环标签;
说明：iterate循环标签后必须以“;”结束。

if 条件表达式1 then 语句块1;
[elseif 条件表达式2 then语句块2] ...
[else语句块n]
end if;
说明：end if后必须以“;”结束。

case 表达式
when value1 then 语句块1;
when value2 then 语句块2;
…
else 语句块n;
end case;
说明：MySQL中的case语句与C语言、Java语言等高级程序设计语言不同，在高级程序设计语言中，每个case的分支需使用“break”跳出，而MySQL无需使用“break”语句。
结论：字符串进行比较时，会截掉字符串尾部的空格字符，然后进行比较。

2．加密函数
加密函数包括不可逆加密函数以及加密-解密函数。
（1）不可逆加密函数
password(x)函数用于对x进行加密，默认返回41位的加密字符串；md5(x)函数用于对x进行加密，默认返回32位的加密字符串。
2．加密函数
（2）加密-解密函数
MySQL提供了两对加密-解密函数分别是：encode(x,key)函数与decode(password, key)函数以及aes_encrypt(x,key) 函数与aes_decrypt(password,key) 函数。其中key为加密密钥（注意读作mìyuè），需要牢记加密时的密钥才能实现密码的解密。
encode(x,key)函数使用密钥key对x进行加密，默认返回值是一个二进制数（二进制的位数由x的字节长度决定）；decode(password, key)函数使用密钥key对密码password进行解密。
aes_encrypt(x,key)函数使用密钥key对x进行加密，默认返回值是一个128位的二进制数；aes_decrypt(password, key)函数使用密钥key对密码password进行解密。
最为常用的数据类型转换函数是convert(x,type)与cast(x as type)函数，另外MySQL还提供了“十六进制字符串”转换为“十六进制数”的函数unhex(x)。

条件控制函数的功能是根据条件表达式的值返回不同的值，MySQL中常用的条件控制函数有if()、ifnull()以及case函数。与先前讲解的if语句以及case语句不同，这些函数可以在MySQL客户机中直接调用，可以像max()统计函数一样直接融入到SQL语句中。
（1）if()函数
if(condition,v1,v2)函数中condition为条件表达式，当condition的值为true时，函数返回v1的值，否则返回v2的值。
（2）ifnull()函数
ifnull(v1,v2)函数中，如果v1的值为NULL，则该函数返回v2的值；如果v1的值不为NULL，则该函数返回v1的值。
（3）case函数
case函数的语法格式如下。如果表达式的值等于when语句中某个“值n”，则case函数返回值为“结果n”；如果与所有的“值n”都不相等，case函数返回值为“其他值”。
case 表达式 when 值1 then 结果1 [ when 值2 then 结果2 ]… [ else 其他值 ] end

（1）获取年、月、日、时、分、秒、微秒等信息的函数
year(x)函数、month(x)函数、dayofmonth(x)函数、hour(x)函数、minute(x)函数、second(x)函数以及microsecond(x)函数分别用于获取日期时间x的年、月、日、时、分、秒、微秒等信息。
（2）获取月份、星期等信息的函数
monthname(x)函数用于获取日期时间x的月份信息。dayname(x)函数与weekday(x) 函数用于获取日期时间x的星期信息；dayofweek(x) 函数用于获取日期时间x是本星期的第几天（星期日为第一天，以此类推）。
（3）获取年度信息的函数
quarter(x)函数用于获取日期时间x在本年是第几季度；week(x)函数与weekofyear(x)函数用于获取日期时间x在本年是第几个星期；dayofyear(x)函数用于获取日期时间x在本年是第几天。
3．时间和秒数之间的转换函数
time_to_sec(x)函数用于获取时间x在当天的秒数；sec_to_time(x)函数用于获取当天的秒数x对应的时间。
4．日期间隔、时间间隔函数
（1）日期间隔函数
to_days(x)函数用于计算日期x距离0000年1月1日的天数；from_days(x)函数用于计算从0000年1月1日开始n天后的日期；
datediff(x1,x2)函数用于计算日期x1与x2之间的相隔天数；adddate(d,n)函数返回起始日期d加上n天的日期；subdate(d,n)函数返回起始日期d减去n天的日期。
（2）时间间隔函数
addtime(t,n)函数返回起始时间t加上n秒的时间；subtime(t,n)函数返回起始时间t减去n秒的时间。
（3）计算指定日期指定间隔的日期函数
date_add(date,interval 间隔 间隔类型)函数返回指定日期date指定间隔的日期。
说明：interval是时间间隔关键字，间隔可以为正数或者负数（建议使用两个单引号括起来）

5．日期和时间格式化函数
（1）时间格式化函数
time_format(t,f)函数按照表达式f的要求显示时间t，表达式f中定义了时间的显示格式，显示格式以%开头。
（2）日期和时间格式化函数
date_format(d,f)函数按照表达式f的要求显示日期和时间t，表达式f中定义了日期和时间的显示格式，显示格式以%开头。
1．获得当前MySQL会话最后一次自增字段值
last_insert_id()函数返回当前MySQL会话最后一次insert或update语句设置的自增字段值。
last_insert_id()函数的返回结果遵循一定的原则。
（1）last_insert_id()函数仅仅用于获取当前MySQL会话时insert或update语句设置的自增字段值，该函数的返回值与系统会话变量@@last_insert_id的值一致。
（2）自增字段值如果是数据库用户自己指定，而不是自动生成，那么last_insert_id()函数的返回值为0。
（3）假如使用一条insert语句插入多行记录，last_insert_id()函数只返回第一条记录的自增字段值。
（4）last_insert_id()函数与表无关。如果向表A插入数据后再向表B插入数据，last_insert_id()函数返回表B的自增字段值。

视图分为普通视图与检查视图。
创建普通视图的语法格式如下。
create view 视图名 [ (视图字段列表) ]
as
select语句
通过检查视图更新基表数据时，只有满足检查条件的更新语句才能成功执行。创建检查视图的语法格式如下。
create view 视图名 [ (视图字段列表) ]
as
select语句
with [ local | cascaded ] check option

触发器主要用于监视某个表的insert、update以及delete等更新操作，这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行，从而实现数据的自动维护。
通过视图虽然可以更新基表的数据，但本书并不建议这样做。原因在于：通过视图更新基表数据，并不会触发触发器的运行。

临时表
按照MySQL临时表的存储位置可以将其分为内存临时表（in-memory）以及外存临时表（on-disk）。
按照MySQL临时表的创建时机可以将其分为自动创建的临时表以及手动创建的临时表。
1．手动创建临时表
手动创建临时表很容易，给正常的create table语句加上temporary关键字即可。
2．查看临时表的定义可以使用MySQL语句“show create table 临时表名;”。
3．断开MySQL服务器的连接，临时表frm表结构定义文件以及表记录将被清除。使用drop命令也可以删除临时表，语法格式如下。
drop temporary table 临时表表名
临时表如果与基表重名，那么基表将被隐藏，除非删除临时表，基表才能被访问。
Memory、MyISAM、Merge或者InnoDB存储引擎的表都支持临时表。
临时表不支持聚簇索引、触发器。
show tables 命令不会显示临时表的信息。
不能用rename来重命名一个临时表。但可以使用alter table重命名临时表。
在同一条select语句中，临时表只能引用一次。例如下面的select语句将抛出“ERROR 1137 (HY000): Can't reopen table: 't1'”错误信息。
select * from temp as t1, temp as t2;
派生表与视图一样，一般在from子句中使用，其语法格式如下（粗体字代码为派生表代码）。
….from (select子句) 派生表名….
派生表必须是一个有效的表，因此它必须遵守以下规则：
每个派生表必须有自己的别名。
派生表中的所有字段必须要有名称，字段名必须唯一。

创建存储过程的语法格式如下。
create procedure 存储过程名（参数1，参数2，…）
[存储过程选项]
begin
存储过程语句块;
end;
与函数相同之处在于：存储过程的参数也是局部变量，也需要提供参数的数据类型；与函数不同的是，存储过程有三种类型的参数：in参数、out参数以及inout参数。
调用存储过程须使用call关键字，另外还要向存储过程传递in参数、out参数或者inout参数。
存储过程与函数之间的共同特点在于：
应用程序调用存储过程或者函数时，只需要提供存储过程名或者函数名，以及参数信息，无需将若干条MySQL命令或SQL语句发送到MySQL服务器，节省了网络开销。
存储过程或者函数可以重复使用，可以减少数据库开发人员，尤其是应用程序开发人员的工作量。
使用存储过程或者函数可以增强数据的安全访问控制。可以设定只有某些数据库用户才具有某些存储过程或者函数的执行权。
存储过程与函数之间的不同之处在于：
函数必须有且仅有一个返回值，且必须指定返回值数据类型（返回值类型目前仅仅支持字符串、数值类型）。存储过程可以没有返回值，也可以有返回值，甚至可以有多个返回值，所有的返回值需要使用out或者inout参数定义。
函数体内可以使用select…into语句为某个变量赋值，但不能使用select语句返回结果（或者结果集）。存储过程则没有这方面的限制，存储过程甚至可以返回多个结果集。
函数可以直接嵌入到SQL语句（例如select语句中）或者MySQL表达式中，最重要的是函数可以用于扩展标准的SQL语句。存储过程一般需要单独调用，并不会嵌入到SQL语句中使用（例如select语句中），调用时需要使用call关键字。
函数中的函数体限制比较多，比如函数体内不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句；不能在函数体内使用预处理SQL语句（稍后讲解）。存储过程的限制相对就比较少，基本上所有的SQL语句或MySQL命令都可以在存储过程中使用。
应用程序（例如Java、PHP等应用程序）调用函数时，通常将函数封装到SQL字符串（例如select语句）中进行调用；应用程序（例如Java、PHP等应用程序）调用存储过程时，必须使用call关键字进行调用，如果应用程序希望获取存储过程的返回值，应用程序必须给存储过程的out参数或者inout参数传递MySQL会话变量，才能通过该会话变量获取存储过程的返回值。
数据库开发人员编写存储过程（或者函数）等存储程序时，有时需要存储程序中的MySQL代码扫描select结果集中的数据，并对结果集中的每条记录进行简单处理，通过MySQL的游标机制可以解决此类问题。

运行期间，如果SQL语句不能发生动态地变化，这种SQL语句称为静态SQL语句。
运行期间，如果SQL语句或SQL所带的参数可以发生动态变化，这种SQL语句称为动态SQL语句或者预处理SQL语句 。
MySQL支持预处理SQL语句，预处理SQL语句的使用主要包含三个步骤：创建预处理SQL语句、执行预处理SQL语句以及释放预处理SQL语句。
1．创建预处理SQL语句
创建预处理SQL语句的语法格式如下。
prepare 预处理SQL语句名 from SQL字符串
2．执行预处理SQL语句
使用execute命令可以执行预处理SQL语句中定义的SQL语句，其语法格式如下。
execute 预处理名 [using 填充数据 [,填充数据...]]
3．释放预处理SQL语句
当预处理SQL语句不再使用时，可以使用deallocate语句将该预处理SQL语句释放。其语法格式如下。
deallocate prepare 预处理名
对于静态SQL语句而言，每次将其发送到MySQL服务实例时，MySQL服务实例都会对其进行解析、执行，然后将执行结果返回给MySQL客户机。
对于预处理SQL语句而言，预处理SQL语句创建后，第一次运行预处理SQL语句时，MySQL服务实例会对其解析，解析成功后，将其保存到MySQL服务器缓存中，为今后每一次地执行作好准备（今后无需再次解析）。

1．锁的粒度
锁的粒度是指锁的作用范围。
锁的粒度可以分为服务器级锁（server-level locking）和存储引擎级锁（storage-engine-level locking）。
##MyISAM存储引擎支持表锁。
##InnoDB存储引擎支持表锁以及行级锁。

2．隐式锁与显式锁
MySQL锁分为隐式锁以及显式锁。
MySQL自动加锁称为隐式锁。
数据库开发人员手动加锁称为显式锁。

3．锁的类型
锁的类型包括读锁（read lock）和写锁（write lock），其中读锁也称为共享锁，写锁也称为排他锁或者独占锁。
读锁允许其它MySQL客户机对数据同时读，但不允许其它MySQL客户机对数据任何写。
写锁不允许其它MySQL客户机对数据同时读，也不允许其它MySQL客户机对数据同时写。

MyISAM表的表级锁
任何针对MyISAM表的查询操作或者更新操作，都会隐式地施加表级锁，隐式锁的生命周期非常短暂，且不受数据库开发人员的控制。
有时需要延长表级锁的生命周期，MySQL为数据库开发人员提供了显示地施加表级锁以及显示地解锁的MySQL命令。read与write选项的功能在于施加表级读锁还是表级写锁。

MySQL客户机A使用lock tables命令可以同时为多个表施加表级锁（包括读锁或者写锁），并且加锁期间，MySQL客户机A不能对“没有锁定的表”进行更新及查询操作，否则将抛出“表未被锁定”的错误信息。
如果需要为同一个表同时施加读锁与写锁，需要为该表起两个别名，以区分读锁与写锁。
read local与read选项之间的区别在于：如果MySQL客户机A使用read选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机A以及MySQL客户机B都不能对该表进行插入操作。如果MySQL客户机A使用read local选项为某个MyISAM表施加读锁，加锁期间，MySQL客户机B可以对该表进行插入操作，前提是新记录必须插入到表的末尾。

InnoDB表的行级锁
InnoDB提供了两种类型的行级锁，分别是共享锁（S）以及排他锁（X），其中共享锁也叫读锁，排他锁也叫写锁。
在查询（select）语句或者更新（insert、update以及delete）语句中，为受影响的记录施加行级锁的方法也非常简单。
方法1．在查询（select）语句中，为符合查询条件的记录施加共享锁，语法格式如下所示。
select * from 表 where 条件语句 lock in share mode;
方法2．在查询（select）语句中，为符合查询条件的记录施加排他锁，语法格式如下所示。
select * from 表 where 条件语句 for update;
方法3．在更新（insert、update以及delete）语句中，InnoDB存储引擎将符合更新条件的记录自动施加排他锁（隐式锁）。
即：InnoDB存储引擎自动地为更新语句影响的记录施加隐式排他锁。

InnoDB表的意向锁
考虑如下场景：MySQL客户机A获得了某个InnoDB表中若干条记录的行级锁，此时MySQL客户机B出于某种原因需要向该表显式地施加表级锁（使用lock tables命令即可），MySQL客户机B为了获得该表的表级锁，需要逐行检测表中的行级锁是否与表级锁兼容，而这种检测需要耗费大量的服务器资源。
MySQL提供了两种意向锁：意向共享锁（IS）和意向排它锁（IX）。
意向共享锁（IS）：向InnoDB表的某些记录施加行级共享锁时，InnoDB存储引擎会自动地向该表施加意向共享锁（IS）。也就是说：执行“select * from 表 where 条件语句 lock in share mode;”后，InnoDB存储引擎在为表中符合条件语句的记录施加共享锁前，InnoDB会自动地为该表施加意向共享锁（IS）。
意向排它锁（IX）：向InnoDB表的某些记录施加行级排它锁时，InnoDB存储引擎会自动地向该表施加意向排它锁（IX）。也就是说：执行更新语句（例如insert、update或者delete语句）或者“select * from 表 where 条件语句 for update;”，InnoDB存储引擎在为表中符合条件语句的记录施加排他锁前，InnoDB会自动地为该表施加意向排它锁（IX）。

InnoDB行级锁与索引之间的关系
InnoDB表的行级锁是通过对“索引”施加锁的方式实现的，这就意味着：只有通过索引字段检索数据的查询语句或者更新语句，才可能施加行级锁；否则InnoDB将使用表级锁，使用表级锁势必会降低InnoDB表的并发访问性能。
间隙锁与死锁
给MyISAM表施加表级锁不会导致死锁问题的发生，这是由于MyISAM总是一次性地获得SQL语句的全部锁。给InnoDB表施加行级锁可能导致死锁问题的发生，这是由于执行SQL语句期间，可以继续施加行级锁。因此这里讨论的死锁问题主要是InnoDB行级锁产生的死锁问题。
默认情况下：InnoDB存储引擎一旦出现锁等待超时异常，InnoDB存储引擎即不会提交事务，也不会回滚事务，而这是十分危险的。一旦发生锁等待超时异常，应用程序应该自定义错误处理程序，由程序开发人员选择进一步提交事务，还是回滚事务。

SQL标准定义了四种隔离级别：
1．read uncommitted（读取未提交的数据）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。该隔离级别很少用于实际应用，并且它的性能也不比其他隔离级别好多少。
2．read committed（读取提交的数据）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已提交事务所做的改变。
3．repeatable read（可重复读）
这是MySQL默认的事务隔离级别，它确保同一事务内相同的查询语句，执行结果一致。
4．serializable（串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突。换言之，它会在每条select语句后自动加上lock in share mode，为每个查询操作施加一个共享锁。在这个级别，可能导致大量的锁等待现象。该隔离级别主要用于InnoDB存储引擎的分布式事务。

并发问题：
脏读(Drity Read)：一个事务可以读到另一个事务未提交的数据，脏读问题违背了事务的隔离性原则
不可重复读(Non-repeatable read)：同一个事务内两条相同的查询语句，查询结果不一致。
幻读(Phantom Read)：同一个事务内，两条相同的查询语句，查询结果应该相同。但是，如果另一个事务同时提交了新数据，本事务再更新时，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。
MySQL默认的事务隔离级别为repeatable read，保持事务的隔离级别repeatable read不变，利用间隙锁的特点，对查询结果集施加共享锁（lock in share mode）或者排他锁（for update），同样可以避免幻读现象，同时也不至于降低MySQL的并发访问性能。

内容总结

以上是互联网集市为您收集整理的mysql总结-长全部内容，希望文章能够帮你解决mysql总结-长所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端