数据库-MySQL

1.数据库基础概念

• 1.查看数据库-SQLsever学习基础概念

2.MySQL数据类型

1.MySQL支持多种数据类型，大致可分为四类

• 1.数值类型
• 2.日期和时间类型
• 3.字符串（字符）类型
• 4.二进制类型

1.数值类型

• 1.整数类型
  • 1.TINYINT（小整型数，tinyint）
  • 2.SMALLINT（短整型数，smallint）
  • 3.INTEGER/INT（整数，integer/int）
  • 4.MEDIUMINT（中整型数，mediumint）
  • 5.BIGINT（长整型数，bigint）
• 2.浮点数类型
  • 1.FLOAT（单精度，float）
  • 2.DOUBLE（双精度，double）
• 3.定点数类型
  • 1.DECIMAL（定点数，decimal）
• 4.注意
  • 1.定点数类型DECIMAL的有效取值范围由M和D决定
    • 1.M表示整个数据的位数，不包括小数点
    • 2.D表示小数点后数据的位数
    • 3.例：将3.1415插入数据类型为DECIMAL（5，3）的数据行，显示的结果为3.142
  • 2.浮点数类型FLOAT和DOUBLE在不指定精度时，默认会按照实际的精度（由计算机硬件和操作系统决定），DECIMAL 如果不指定精度，默认为DECIMAL(10，0)
  • 3.不论是定点还是浮点类型，如果指定的精度超出精度范围，则会进行四舍五入处理
  • 4.MySQL 中定点数以字符串形式存储，对精度要求比较高的时（如货币、科学数据）使用DECIMAL的类型较好
  • 5.浮点数进行减法和比较运算时容易出问题，所以在使用浮点数时需要注意并尽量避免
  • 6.整数int的范围是10位，符号1位，一共11位，所以int型默认是11位，因为11位是它的最大长度

2.日期和时间类型

• 1.DATE（date）类型： 表示日期值，不包含时间部分
  • 1.MySQL中DATE类型常用的字符串格式为：YYYY-MM-DD或者YYYYMMDD
  • 2.例：输入2018-04-24或20180424，插入数据库的日期都为2018-04-24
• 2.TIME（time）类型： 表示时间值，不包含日期部分
  • 1.TIME形式一般为HH:MM:SS，其中HH表示小时，MM表示分，SS表示秒
  • 2.例：输入115253，插入数据库中的时间为11:52:53
• 3.YEAR（year）类型： 表示年份
  • 1.MySQL中YEAR类型常使用4位字符串或数字表示，对应的字符串的范围为1901～2155，数字范围为1901～2155
• 4.DATETIME（datetime）类型： 表示日期和时间
  • 1.DATETIME形式为YYYY-MM-DD HH:MM:SS，其中YYYY表示年，MM表示月，DD表示日，HH表示小时，MM表示分，SS表示秒
  • 2.DATETIME类型常使用的字符串格式为YYYY-MM-DD HH:MM:SS或YYYYMMDD HHMMSS
  • 3.例：输入2018-04-24 08:23:52或20180424082352，插入数据库中的DATETIME类型的值为2018-04-24 08:23:52
• 5.TIMESTAMP类型： 表示日期和时间
  • 1.TIMESTAMP的显示形式与DATETIME类型相同，但取值范围比DATETIME类型小
  • 2.TIMESTAMP类型具备专有的自动更新特性，当TIMESTAMP类型的字段输入为NULL时，系统会以当前系统的日期和时间填入
  • 3.当TIMESTAMP类型的字段无输入时，系统也会以当前系统的日期和时间填入
• 6.注意
  • 1.每个时间类型有一个有效值范围和一个零值，当输入不合法的值时MySQL使用零值插入

3. 字符串类型和二进制类型

• 1.为了存储字符串、图片和声音等数据，MySQL提供了字符串和二进制类型

3.MySQL特性

1.MySQL运算符

• 1.MySQL 支持 4 种运算符
  • 1.算术运算符
  • 2.比较运算符
  • 3.逻辑运算符
  • 4.位运算符

1.算术运算符

运算符	作用	使用方法
+	加法运算	用于获得一个或多个值的和
-	减法运算	用于从一个值中减去另一个值
*	乘法运算	使数字相乘，得到两个或多个值的乘积
/	除法运算，返回商	用一个值除以另一个值得到商
%，MOD	求余运算，返回余数	用一个值除以另一个值得到余数

• 1.数学运算中除数为 0 的除法是没有意义的，所以在除法运算和取余运算中，如果除数为 0，那么返回结果为 NULL
  
• 2.对于取余运算还可以使用 MOD(a,b) 函数，MOD(a,b) 相当于 a%b

2.逻辑运算符

运算符	作用
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 和 ||	逻辑或
XOR	逻辑异或

• 1.NOT和!都是逻辑非运算符，返回和操作数相反的结果
  • 1.当操作数为 0（假）时，返回值为 1
  • 2.当操作数为非零值时，返回值为 0
  • 3.当操作数为 NULL 时，返回值为 NULL
    
    
• 2.NOT 1+1和! 1+1的返回值不同，因为 NOT 与 ! 的优先级不同
  • 1.NOT 的优先级低于 +，因此NOT 1+1相当于NOT(1+1)，先计算1+1，然后再进行 NOT 运算，由于操作数不为 0，因此NOT 1+1的结果是 0
  • 2.相反! 的优先级高于 +，因此! 1+1相当于(!1)+1，先计算!1结果为 0再加 1，最后结果为 1
• 3.使用运算符运算时一定要注意运算符的优先级，如果不能确定计算顺序最好使用括号以保证运算结果的正确

2.转义字符

• 1.MySQL中除了常见的字符之外还有特殊的字符（换行符、回车符等）

• 2.这些符号无法用字符来表示，因此需要使用某些特殊的字符来表示特殊的含义，这些字符就是转义字符

• 3.转义字符区分大小写(\b解释为退格，但\B解释为\B)

  转义字符	转义后的字符
  \"	双引号（"）
  \’	单引号（'）
  \\	反斜线（\）
  \n	换行符
  \r	回车符
  \t	制表符
  \0	ASCII 0（NUL）
  \b	退格符

• 注意

  • 1.字符串的内容包含单引号'时，可以用单引号'或反斜杠\来转义
  • 2.字符串的内容包含双引号"时，可以用双引号"或反斜杠\来转义
  • 3.一个字符串用双引号"引用时，该字符串中的单引号 '不需要特殊对待且不必被重复转义
  • 4.同理一个字符串用单引号'引用时，该字符串中的双引号"不需要特殊对待且不必被重复转义

3.MySQL函数

• 1.具体可参考http://c.biancheng.net/mysql/function/

1.数值型函数

函数名称	作用
ABS	求绝对值
SQRT	求二次方根
MOD	求余数
CEIL 和 CEILING	两个函数功能相同，都是返回不小于参数的最小整数，即向上取整
FLOOR	向下取整，返回值转化为一个BIGINT
RAND	生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列
ROUND	对所传参数进行四舍五入
SIGN	返回参数的符号
POW 和 POWER	两个函数的功能相同，都是所传参数的次方的结果值
SIN	求正弦值
ASIN	求反正弦值，与函数 SIN 互为反函数
COS	求余弦值
ACOS	求反余弦值，与函数 COS 互为反函数
TAN	求正切值
ATAN	求反正切值，与函数 TAN 互为反函数
COT	求余切值

2.字符串函数

函数名称	作用
LENGTH	计算字符串长度函数，返回字符串的字节长度
CONCAT	合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个
INSERT	替换字符串函数
LOWER	将字符串中的字母转换为小写
UPPER	将字符串中的字母转换为大写
LEFT	从左侧字截取符串，返回字符串左边的若干个字符
RIGHT	从右侧字截取符串，返回字符串右边的若干个字符
TRIM	删除字符串左右两侧的空格
REPLACE	字符串替换函数，返回替换后的新字符串
SUBSTRING	截取字符串，返回从指定位置开始的指定长度的字符换
REVERSE	字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

3.日期和时间函数

函数名称	作用
CURDATE 和 CURRENT_DATE	两个函数作用相同，返回当前系统的日期值
CURTIME 和 CURRENT_TIME	两个函数作用相同，返回当前系统的时间值
NOW 和 SYSDATE	两个函数作用相同，返回当前系统的日期和时间值
UNIX_TIMESTAMP	获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
FROM_UNIXTIME	将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数
MONTH	获取指定日期中的月份
MONTHNAME	获取指定日期中的月份英文名称
DAYNAME	获取指定曰期对应的星期几的英文名称
DAYOFWEEK	获取指定日期对应的一周的索引位置值
WEEK	获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
DAYOFYEAR	获取指定曰期是一年中的第几天，返回值范围是1~366
DAYOFMONTH	获取指定日期是一个月中是第几天，返回值范围是1~31
YEAR	获取年份，返回值范围是 1970〜2069
TIME_TO_SEC	将时间参数转换为秒数
SEC_TO_TIME	将秒数转换为时间，与TIME_TO_SEC 互为反函数
DATE_ADD 和 ADDDATE	两个函数功能相同，都是向日期添加指定的时间间隔
DATE_SUB 和 SUBDATE	两个函数功能相同，都是向日期减去指定的时间间隔
ADDTIME	时间加法运算，在原始时间上添加指定的时间
SUBTIME	时间减法运算，在原始时间上减去指定的时间
DATEDIFF	获取两个日期之间间隔，返回参数 1 减去参数 2 的值
DATE_FORMAT	格式化指定的日期，根据参数返回指定格式的值
WEEKDAY	获取指定日期在一周内的对应的工作日索引

4.聚合函数

函数名称	作用
MAX	查询指定列的最大值
MIN	查询指定列的最小值
COUNT	统计查询结果的行数
SUM	求和，返回指定列的总和
AVG	求平均值，返回指定列数据的平均值

5.流程控制函数

函数名称	作用
IF	判断，流程控制
IFNULL	判断是否为空
CASE	搜索语句

4.MySQL注释

• 1.注释在SQL语句中用来标识说明或注意事项，对SQL的执行没有任何影响
• 2.任何注释（单行注释和多行注释）都可以插在SQL 语句中且注释可以放在SQL语句中的任意位置

1.单行注释

• 1.单行注释可以使用#注释符，#注释符后直接加注释内容

  #测试修改数据库
  ALTER DATABASE test_db
  DEFAULT CHARACTER SET gbk
  DEFAULT COLLATE gbk_bin;
  

• 2.单行注释也可以使用--注释符，--注释符后需要加一个空格，注释才能生效

  -- 测试修改数据库
  ALTER DATABASE test_db
  DEFAULT CHARACTER SET gbk
  DEFAULT COLLATE gbk_bin;
  

• 3.#和--的区别：#后直接加注释内容，而--后需要跟一个空格符再加注释内容

2.多行注释

• 1.多行注释使用/* */注释符，/*用于注释内容的开头，*/用于注释内容的结尾

  /*
    第一行注释内容
    第二行注释内容
  */
  

5.MySQL大小写规则

• 1.阿里巴巴Java开发手册的MySql建表规约：【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字
• 2.MySQL在Windows系统下不区分大小写，但在Linux系统下默认区分大小写，因此数据库名、表名和字段名都不允许出现任何大写字母避免节外生枝
• 3.建议统一使用小写字母并且 InnoDB 引擎在其内部都是以小写字母方式来存储数据库名和表名的，这样可以有效的防止 MySQL 产生大小写问题

6.系统变量

• 1.MySQL数据库中变量分为系统变量和用户自定义变量
  • 1.系统变量以 @@ 开头
  • 2.用户自定义变量以 @ 开头
• 2.MySQL数据库维护着两种系统变量
  • 1.全局变量(GLOBAL VARIABLES)：全局变量影响MySQL服务的整体运行方式
  • 2.会话变量(SESSION VARIABLES)：会话变量影响具体客户端连接的操作
    • 1.每一个客户端成功连接服务器后都会产生与之对应的会话
    • 2.会话期间MySQL服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝

1.查看系统变量

• 1.查看MySQL中所有的全局变量信息

  SHOW GLOBAL VARIABLES; 
  

  
• 2.查看当前会话的所有会话变量以及全局变量

  //SESSION 关键字可以省略
  SHOW SESSION VARIABLES;
  

  
• 3.MySQL中的系统变量以两个@开头
  • 1.@@global 仅仅用于标记全局变量
  • 2.@@session 仅仅用于标记会话变量
  • 3.@@ 首先标记会话变量，如果会话变量不存在则标记全局变量
• 4.MySQL中有一些系统变量仅仅是全局变量，可以使用以下 3 种方法查看

  SHOW GLOBAL VARIABLES LIKE 'innodb_data_file_path';
  SHOW SESSION VARIABLES LIKE 'innodb_data_file_path';
  SHOW VARIABLES LIKE 'innodb_data_file_path';
  

  
• 5.MySQL中有一些系统变量仅仅是会话变量，可以使用以下 2 种方法查看

  SHOW SESSION VARIABLES LIKE 'pseudo_thread_id';
  SHOW VARIABLES LIKE 'pseudo_thread_id';
  

  
• 6.MySQL中有一些系统变量既是全局变量又是会话变量，可以使用以下 3 种方法查看

  SHOW SESSION VARIABLES LIKE 'character_set_client';
  SHOW GLOBAL VARIABLES LIKE 'character_set_client';
  SHOW VARIABLES LIKE 'character_set_client';
  

  

2.设置系统变量

• 1.通过以下方法可以设置系统变量
  • 1.修改 MySQL 源代码，然后对 MySQL 源代码重新编译（该方法适用于 MySQL 高级用户）
  • 2.修改MySQL 配置文件（my.ini 或 my.cnf）中MySQL系统变量的值（需要重启 MySQL 服务才会生效）
  • 3.MySQL 服务运行期间，使用SET命令重新设置系统变量的值

1.全局变量

• 1.服务器启动时会将所有的全局变量赋予默认值，这些默认值可以在选项文件中或在命令行中对执行的选项进行更改
• 2.更改全局变量必须具有 SUPER 权限，设置全局变量的方法如下

  SET @@global.innodb_file_per_table=default;
  SET @@global.innodb_file_per_table=ON;
  SET global innodb_file_per_table=ON;
  

  
• 3.注意
  • 1.更改全局变量只影响更改后连接客户端的相应会话变量而不会影响目前已经连接的客户端的会话变量（即使客户端执行 SET GLOBAL 语句也不影响）
  • 2.即对于修改全局变量之前连接的客户端只有在客户端重新连接后，才会受到影响

2.会话变量

• 1.客户端连接时当前全局变量的值会对客户端的会话变量进行相应初始化，设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。设置会话变量的方法如下

  SET @@session.pseudo_thread_id=5;
  SET session pseudo_thread_id=5;
  SET @@pseudo_thread_id=5;
  SET pseudo_thread_id = 5;
  

  
• 2.如果没有指定修改全局变量还是会话变量，服务器会当作会话变量来处理

  //没有指定是 GLOBAL 还是 SESSION，服务器会当做 SESSION 处理
  SET @@sort_buffer_size = 50000;
  

• 3.使用SET设置全局变量或会话变量成功后，如果MySQL服务重启，数据库的配置就又会重新初始化，一切按照配置文件进行初始化，全局变量和会话变量的配置都会失效
• 4.MySQL 中还有一些特殊的全局变量（log_bin、tmpdir、version、datadir）， MySQL 服务实例运行期间不能动态使用SET命令进行重新设置，这种变量称为静态变量，数据库管理员可以使用修改源代码或更改配置文件来重新设置静态变量

3.MySQL存储引擎

• 1.数据库存储引擎是数据库底层软件组件，数据库管理系统使用存储引擎进行创建、查询、更新和删除数据操作
• 2.存储引擎指表的类型，数据库的存储引擎决定了表在计算机中的存储方式，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能
• 3.MySQL提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎，MySQL 中不需要在整个服务器中使用同一种存储引擎，针对具体的要求可以对每一个表使用不同的存储引擎

1.存储引擎种类

• 1.通过SHOW ENGINES(show engines);语句查看系统所支持的引擎类型
  

• 2.MySQL 5.7支持的存储引擎如上表所示，其中Support列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎

  存储引擎	描述
  InnoDB	具备外键支持功能的事务处理引擎
  MyISAM	主要的非事务处理存储引擎
  ARCHIVE	用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引
  CSV	在存储数据时，会以逗号作为数据项之间的分隔符
  BLACKHOLE	会丢弃写操作，该操作会返回空内容。
  FEDERATED	将数据存储在远程数据库中，用来访问远程表的存储引擎
  MEMORY	置于内存的表
  MERGE/MRG_MyISAM	用来管理由多个 MyISAM 表构成的表集合
  NDB/NDBCLUSTER	MySQL 集群专用存储引擎

2.InnoDB存储引擎

• 1.InnoDB 是 MySQL 中第一个提供外键约束的存储引擎，而且它支持对事务的处理
• 2.MySQL 5.5 版本以后，默认存储引擎由 MyISAM 修改为 InnoDB

1.InnoDB的优点

• 1.支持事务
  • 1.InnoDB最重要的一点就是支持事务，InnoDB还实现了SQL92标准所定义的 4 个隔离级别
• 2.灾难恢复性好
  • 1.InnoDB通过commit、rollback、crash-recovery来保障数据的安全
  • 2.其中crash-recovery 指如果服务器因为硬件或软件的问题而崩溃，不管当时数据是怎样的状态，重启 MySQL 后InnoDB 都会自动恢复到发生崩溃之前的状态并回到用户离开的地方
• 3.使用行级锁
  • 1.InnoDB改变了MyISAM的锁机制实现了行锁，InnoDB 的行锁机制是通过索引完成
• 4.实现了缓冲处理
  • 1.InnoDB 提供了专门的缓存池实现了缓冲管理，不仅能缓冲索引也能缓冲数据，常用的数据可以直接从内存中处理，比从磁盘获取数据处理速度要快，相比之下MyISAM 只是缓存了索引
• 5.支持外键
  • 1.InnoDB支持外键约束，检查外键、插入、更新和删除，以确保数据的完整性
  • 2.存储表中数据时每张表的存储都按主键顺序存放，如果没有显式地在定义表时指定主键，InnoDB 会为每一行生成一个 6 字节的 ROWID ，并以此作为主键

2.InnoDB的缺点

• 1.InnoDB 存储引擎的读写效率稍差，且占用的数据空间相对较大

3.InnoDB物理存储

• 1.使用 InnoDB 时MySQL会在数据目录Data下创建一个名为 ibdata1 的 10MB大小的自动扩展数据文件以及两个名为 ib_logfile0 和 ib_logfile1 的 5MB 大小的日志文件
• 2.InnoDB 存储引擎和 MyISAM 不太一样，虽然也有 .frm 文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的，至于是每个表单独存放还是所有表存放在一起，用户可以自己设置
  
• 3.InnoDB 的物理存储结构分为两大部分
  • 1.数据文件
  • 2.日志文件

1.数据文件（表数据和索引数据）

• 1.数据文件用来存放数据表中的数据和所有的索引数据，包括主键和其他普通索引
• 2.InnoDB 存储的数据采用表空间（Tablepace）进行存放设计，表空间是用来存放 MySQL 系统相关信息的一个特殊共享表空间
• 3.InnoDB 的表空间分为以下两种形式：
  • 1.共享表空间：表数据和索引都存放在同一个表空间，默认的表空间文件指 MySQL 初始化路径下的 ibdata1 文件
  • 2.独立表空间，每个表的数据和索引被存放在一个单独的 .ibd 文件中
• 4.查看 MySQL 是否使用独立表空间，可以通过设置该参数的值来决定是否使用独立表空间
  
• 5.innodb_file_per_table 值为 ON 时表示开启独立表文件，InnoDB 表的数据和索引都会以单独的形式存放
• 6.innodb_file_per_table 值为 OFF 时表示不开启独立表文件，InnoDB 表的数据和索引都存放在一个表空间

1.共享表空间

• 1.共享表空间的数据文件可以设置为固定大小和可自动扩展大小两种形式
• 2.自动扩展形式的文件可以设置文件的最大大小和每次扩展量，创建自动扩展的数据文件时建议最好加上最大尺寸的属性，因为文件系统本身有一定的大小限制且方便维护
• 3.当表空间快要用完的时必须要为其增加数据文件，只有共享表空间有此操作
• 4.共享表空间增加数据文件的操作只需要在 innodb_data_file_path 参数后面按照标准格式设置好文件路径和相关属性即可
• 5.innodb_data_file_path 参数负责定义共享表空间的路径、初始化大小、自动扩展策略
• 6.查看当前共享表空间文件的路径、大小和自动化策略
  
• 7.用户可以通过 innodb_data_file_path 参数来指定表空间文件

  innodb_data_file_path=datafile_spec1[;datafile_spec2]...
  

• 8.其中datafile_spec1格式：表空间文件路径:大小:属性，可以指定多个文件组成一个共享表空间并同时指定文件的属性

  innodb_data_file_path=/db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
  

• 9.将 /db/ibdata1 和 /dr2/db/ibdata2 两个文件用来组成共享表空间，若这两个文件位于不同的磁盘上，磁盘的负载可能被平均，因此可以提高数据库的整体性能
• 10.指定多个文件时autoextend 属性只在最后一个数据文件中指定，表示表空间自动扩展，这里表示文件 ibdata1 的大小为 2000MB，文件 ibdata2 的大小为 2000MB，如果用完了 2000MB，该文件还可以自动增长
• 11.设置完 innodb_data_file_path 参数后，所有基于 InnoDB 存储引擎的表的数据都会记录到该共享表空间中
• 12.注意：InnoDB 创建新数据文件时不会创建目录，如果指定目录不存在，则会报错并无法启动，另外InnoDB 给共享表空间增加数据文件之后，必须要重启数据库系统才能生效

2.独立表空间

• 1.通过设置 innodb_file_per_table 参数可以将每个基于 InnoDB 存储引擎的表产生一个独立表空间
• 2.独立表空间的命名规则为表名.ibd，通过这样的方式用户不用将所有数据都存放于默认的表空间中
  
• 3.使用 SET 命令打开/关闭独立表空间

  mysql> SET GLOBAL innodb_file_per_table=1;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | innodb_file_per_table | ON    |
  +-----------------------+-------+
  1 row in set, 1 warning (0.03 sec)
  
  mysql> SET GLOBAL innodb_file_per_table=0;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
  +-----------------------+-------+
  | Variable_name         | Value |
  +-----------------------+-------+
  | innodb_file_per_table | OFF   |
  +-----------------------+-------+
  1 row in set, 1 warning (0.00 sec)
  

  
• 4.注意：单独的表空间文件只存储该表的数据、索引和缓冲等信息，所以无论是使用共享表空间还是独享表空间来存放表，共享表空间都是必须存在的

2.日志文件

• 1.默认情况下InnoDB 存储引擎的Data目录下会有两个名为 ib_logfile0 和 ib_logfile1 文件, MySQL 官方手册中将其称为 InnoDB 存储引擎的重做日志文件（redo log file）
• 2.重做日志文件对 InnoDB 存储引擎至关重要，InnoDB 可以通过重做日志将数据库宕机时已经完成但还没有来得及将数据写入磁盘的事务恢复，也能将所有部分完成并已经写入磁盘的未完成事务回滚并且将数据还原以此来保证数据的完整性
• 3.每个InnoDB存储引擎至少有 1 个重做日志文件组（group），每个文件组下至少有 2 个重做日志文件(默认 ib_logfile0 和 ib_logfile1)
• 4.不能全部删除 InnoDB 的日志文件，可能会让的数据库 Crash(数据库不工作或停止响应、进程中断等情况)，无法启动，或丢失数据
• 5.MySQL 启动参数文件设置中InnoDB 的所有参数基本上都带有前缀innodb_
• 6.下面是影响重做日志文件的参数
  • 1.innodb_log_file_size：指定每个重做日志的大小
  • 2.innodb_log_files_in_group：指定日志文件组中重做日志文件的数量，默认为 1
  • 3.innodb_mirrored_log_groups：指定日志镜像文件组的数量，默认为 1
  • 4.innodb_log_group_home_dir：指定日志文件组所在路径，默认为./

3.MyISAM存储引擎

• 1.MyISAM 存储引擎是 MySQL 中常见的存储引擎，曾（MySQL 5.1及之前版本）是 MySQL 的默认存储引擎
• 2.MyISAM 基于 ISAM 存储引擎，ISAM 只是一种算法或数据的处理方式，随着 MySQL 架构的不断发展和演进，最终引入插件式存储引擎的概念，ISAM 也进化为 MyISAM 并一直作为 MySQL 数据库的默认存储引擎，直到 MySQL5.5 版本被 InnoDB 引擎取代了默认存储引擎的地位

1.MyISAM的优点

• 1.占用空间小，访问速度快，对事务完整性没有要求或以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表
• 2.可以配合表锁，实现操作系统下的复制备份
• 3.支持全文检索（InnoDB 在 MySQL 5.6 版本以后也支持全文检索）
• 4.数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能
• 5.说明
  • 1.加锁与并发
    • 1.MyISAM 针对整张表加锁，而InnoDB针对某一行加锁
    • 2.读取时会对需要读到的所有表加共享锁，写入时对表加排他锁，但是在表有读取查询的同时，也可以往表中插入新的记录（这被称为并发插入）
  • 2.修复
    • 1.对于MyISAM表MySQL可以手工（执行命令 CHECK TABLE tablename）或自动执行检查和修复（执行命令 REPAIR TABLE tablename）操作
  • 3.索引特性
    • 1.MyISAM 支持以下 3 种类型的索引
      • 1.B-Tree 索引：指所有的索引节点都按照 balance tree 的数据结构来存储，所有的索引数据节点都在叶节点，最常用
      • 2.R-Tree 索引：R-Tree 索引的存储方式和 B-tree 索引有一些区别，主要用于为存储空间和多维数据的字段做索引，对于目前的 MySQL 版本来说仅支持 geometry 类型的字段作索引
      • 3.Full-text 索引：指全文索引，存储结构也是 B-tree，主要为了解决需要用模糊查询时的低效问题

2.MyISAM的缺点

• 1.不支持事务的完整性和并发性
• 2.不支持行级锁，使用表级锁，并发性差
• 3.主机宕机后，MyISAM表易损坏，灾难恢复性不佳
• 4.数据库崩溃后无法安全恢复
• 5.只缓存索引，数据的缓存是利用操作系统缓冲区来实现的，可能会引发过多的系统调用且效率不佳

3.MyISAM的物理存储

• 1.MyISAM 存储引擎的表在数据库中被存储成 3 个物理文件，文件名与表名相同，扩展名为 frm，MYD 和 MYI
  • 1.frm 为扩展名的文件：存储表的结构
  • 2.MYD 为扩展名的文件：存储数据，是 MYData 的缩写
  • 3.MYI 为扩展名的文件：存储索引，其是 MYIndex 的缩写，不管表有多少索引都是存放在同一个 .MYI 文件中
• 2.MyISAM 类型的数据文件和索引文件可以放置在不同的目录，平均分布 IO以此来获得更快的速度
• 3.指定索引文件和数据文件的路径需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定
• 4.即不同 MyISAM 表的索引文件和数据文件可以放置到不同的路径下，文件路径需要是绝对路径并且具有访问权限
• 5.虽然每一个 MyISAM 的表数据都存放在后缀名为 .MYD 的文件中，但是每个文件的存放格式可能并不完全一样。因为 MyISAM 支持 3 种不同的数据存放格式
  • 1.静态型
    • 1.MyISAM 存储引擎的默认存储格式，其字段是固定长度，这样每个记录都是固定长度的，这种存储方式存储非常迅速，容易缓存，出现故障容易恢复
    • 2.缺点是占用的空间比动态表多，静态型的表的数据在存储的时候会按照列的宽度定义去补足空格，但是在应用访问的时候并不会得到这些空格，空格在返回给应用之前就被去掉了
    • 3.注意：如果需要保存的内容后面本来就带有空格，那么返回结果时也会被去掉，因为静态表是默认的存储格式，可能并没有意识到这一点，从而丢失了尾部的空格
  • 2.动态型
    • 1.动态型包含变长字段，记录的长度不是固定的，这样存储的优点是占用的空间相对较少
    • 2.但是频繁的更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难
  • 3.压缩型
    • 1.压缩型的表需要使用myisampack工具创建，解压缩则用另外的 myisamchk命令，压缩表是制度的，不支持添加或修改记录
    • 2.压缩表是基于静态或动态格式表的，优点在于占用的磁盘空间非常小，可以减少磁盘 I/O，从而提升查询性能
    • 3.因为每个记录都是被单独压缩的，所以只有非常小的开支，如果表在创建并导入数据以后，不会再进行修改操作，这样的表或许适合采用 MyISAM 压缩表
• 6.理论上MyISAM 存储引擎的表可以被多个数据库实例同时使用同时操作，但是MySQL 官方建议尽量不要在多个 mysqld 之间共享MyISAM存储文件

4.不同存储引擎存储数据的方式

• 1.MySQL中的每一个数据表在磁盘上至少被表示为一个文件，即存放着该数据表结构定义的 .frm 文件，不同的存储引擎还有其它用来存放数据和索引信息的文件
• 2.从MySQL 8.0版本开始，.frm表结构定义文件被取消，MySQL 把表结构信息都写到了系统表空间
  

1.InnoDB

• 1.对于InnoDB存储引擎的数据表，一个表对应两个文件
  • 1.*.frm：存储表结构信息
  • 2.*.ibd：存储表中数据和索引

  mysql> SET default_storage_engine=InnoDB;
  Query OK, 0 rows affected (0.00 sec)
  mysql> CREATE TABLE tb_innodb(
  	    -> id INT
  	    -> );
  Query OK, 0 rows affected (0.10 sec)
  

2.MyISAM

• 1.对于MyISAM 存储引擎的数据表，data目录里使用 3 个文件来表示，这些文件的基本名与数据表的名字相同，扩展名则表明了文件的具体用途
  • 1..frm：表结构定义文件，存放着该数据表的结构定义。
  • 2..MYD：MY Data的缩写，数据文件，存放着该数据表中各个行的数据
  • 3..MYI： MY Index的缩写，索引文件，存放着该数据表的全部索引信息

  mysql> SET default_storage_engine=MyISAM;
  Query OK, 0 rows affected (0.02 sec)
  mysql> CREATE TABLE tb_myisam(
      -> id INT
      -> );
  Query OK, 0 rows affected (0.03 sec)
  

3.MERGE

• 1.MERGE 存储引擎的数据表其实是一个逻辑结构，它代表着由一组结构完全相同的 MyISAM 数据表所构成的集合，有关的查询命令会把它当作一个大数据表来对待
• 2.MERGE 存储引擎的数据表除了拥有存储表结构定义的 .frm 文件以外，还有一个扩展名为 .mgr 的文件，这个文件里不保存数据，而是数据的来源地，即一份由多个 MyISAM 数据表的名单构成的 MERGE 数据表

  mysql> SET default_storage_engine=Merge;
  Query OK, 0 rows affected (0.00 sec)
  mysql> CREATE TABLE tb_merge(
      -> id INT
      -> );
  Query OK, 0 rows affected (0.02 sec)
  

4.Memory

• 1.Memory存储引擎的数据表是创建在内存中的数据表，因为 MySQL 服务器把 Memory 数据表的数据和索引都存放在了内存中而不是硬盘上，所以除了相应的 .frm 文件外，Memory 引擎表在文件系统里没有其它相应的代表文件

  mysql> SET default_storage_engine=Memory;
  Query OK, 0 rows affected (0.00 sec)
  mysql> CREATE TABLE tb_memory(
      -> id INT
      -> );
  Query OK, 0 rows affected (0.03 sec)
  

5.Archive

• 1.Archive 存储引擎的数据表除了拥有.frm表结构定义文件外，还有一个扩展名为.arz的数据文件，用来存储历史归档数据，执行优化操作时可能还会出现一个扩展名为 .arn 的文件

  mysql> SET default_storage_engine=Archive;
  Query OK, 0 rows affected (0.00 sec)
  mysql> CREATE TABLE tb_archive(
      -> id INT
      -> );
  Query OK, 0 rows affected (0.04 sec)
  

6.CSV

• 1.CSV 引擎表也会包含一个 .frm 表结构定义文件，此外还会创建一个扩展名为 .CSV 的数据文件，这个文件是 CSV 格式的文本文件，用来保存表中的实际数据
• 2..CSV 文件可以直接在 Excel 中打开或是使用其它文件编辑工具查看，另外还有一个同名的元信息文件，文件扩展名为 .CSM用来保存表的状态及表中保存的数据量
• 3.由于 CSV 文件可被直接编辑，如果操作得当可以不通过 SQL 语句直接修改 CSV 文件中的内容
• 4.CSV 存储引擎基于 CSV 格式文件存储数据，由于自身文件格式的原因，所有列必须强制指定 NOT NULL

  mysql> SET default_storage_engine=csv;
  Query OK, 0 rows affected (0.02 sec)
  mysql> CREATE TABLE tb_csv(
      -> id INT NOT NULL,
      -> name CHAR(10) NOT NULL
      -> );
  Query OK, 0 rows affected (0.04 sec)
  

7.BLACKHOLE

• 1.由于在 BLACKHOLE 存储引擎的数据表中写入任何数据都会消失，所以除了 .frm 文件，BLACKHOLE 引擎表没有其他相应的代表文件

  mysql> SET default_storage_engine=BLACKHOLE;
  Query OK, 0 rows affected (0.00 sec)
  mysql> CREATE TABLE tb_blackhole(
      -> id INT
      -> );
  Query OK, 0 rows affected (0.03 sec)
  

5.查看和修改默认存储引擎

• 1.如果需要操作默认存储引擎，首先需要查看默认存储引擎，查看默认的存储引擎

  SHOW VARIABLES LIKE 'default_storage_engine%';
  

  
• 2.修改数据库临时的默认存储引擎

  SET default_storage_engine=< 存储引擎名 >
  

  
• 3.注意：再次重启客户端时，默认存储引擎仍然是 InnoDB，手动改变只能本次登录有效，可以通过修改配置文件my.ini中的数据达到永久修改的目的

6.手动选择存储引擎

• 1.使用 MySQL 数据库管理系统时，选择一个合适的存储引擎是一个非常复杂的问题，不同的存储引擎都有各自的特性、优势和使用的场合，正确的选择存储引擎可以提高应用的使用效率

  特性	MyISAM	nnoDB	MEMORY
  存储限制	有	支持	有
  事务安全	不支持	支持	不支持
  锁机制	表锁	行锁	表锁
  B树索引	支持	支持	支持
  哈希索引	不支持	不支持	支持
  全文索引	支持	不支持	不支持
  集群索引	不支持	支持	不支持
  数据缓存	不支持	支持	支持
  索引缓存	支持	支持	支持
  数据可压缩	支持	不支持	不支持
  空间使用	低	高	N/A
  内存使用	低	高	中等
  批量插入速度	高	低	高
  支持外键	不支持	支持	不支持

• 1.InnoDB

  • 1.MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB
  • 2.InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间
  • 3.如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择
  • 4.InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统InnoDB 都是合适的选择

• 2.MyISAM

  • 1.MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎
  • 2.MyISAM 存储引擎不支持事务和外键，所以访问速度比较快，如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的

• 3.MEMORY

  • 1.MEMORY 存储引擎将所有数据保存在 RAM 中，所以该存储引擎的数据访问速度快，但是安全上没有保障
  • 2.MEMORY 对表的大小有限制，太大的表无法缓存在内存中，由于使用 MEMORY 存储引擎没有安全保障，所以要确保数据库异常终止后表中的数据可以恢复
  • 3.如果应用中涉及数据比较少，且需要进行快速访问，则适合使用 MEMORY 存储引擎

7.修改数据表的存储引擎

• 1.MySQL 中修改数据表的存储引擎

  ALTER TABLE <表名> ENGINE=<存储引擎名>;
  

  
  
• 2.以上方法适用于修改单个表的存储引擎，如果希望修改默认的存储引擎需要修改 my.ini 配置文件

  default-storage-engine=存储引擎名称
  

4.数据库的基本操作

1.数据库的创建

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];

• 1.指在数据库系统中划分一块存储数据的空间，方便数据的分配，放置和管理
• 2.[]中的内容是可选的，<>中的内容是必填的
• 3.数据库名：创建的数据库的名称，在同一个数据库服务器上必须是唯一的，不允许重复
• 4.MySQL的数据存储区将以目录的方式表示MySQL数据库，因此数据库名称必须符合操作系统的文件夹命名规则并尽量有实际意义
  
• 5.IF NOT EXISTS：创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作，此选项用来避免数据库已经存在而重复创建的错误
• 6.[DEFAULT] CHARACTER SET：指定数据库的字符集，为了避免在数据库中存储的数据出现乱码的情况，如果在创建数据库时不指定字符集，那么就使用系统的默认字符集
• 7.[DEFAULT] COLLATE：指定字符集的默认校对规则
• 8.使用SHOW CREATE DATABASE 数据库名可以查看数据库的定义声明

  //例1：最简单的创建 MySQL 数据库的语句
  CREATE DATABASE test_db;
  //例2：MySQL 不允许在同一系统下创建两个相同名称的数据库，为了避免类似错误可以加上IF NOT EXISTS从句
  CREATE DATABASE IF NOT EXISTS test_db;
  //例3：创建 MySQL 数据库时指定字符集和校对规则
  CREATE DATABASE IF NOT EXISTS test_db_char
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;
  SHOW CREATE DATABASE test_db_char;
  

  
  
  
• 9.注意
  • 1.MySQL 的字符集CHARACTER和校对规则COLLATION是两个不同的概念
  • 2.字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式
  • 3.字符集和校队规则具体可参考https://www.cnblogs.com/geaozhang/p/6724393.html

2.数据库的查看

SHOW DATABASES [LIKE '数据库名'];

• 1.查看或显示当前用户权限范围以内的数据库
  
  
• 2.information_schema、mysql、performance_schema，sakila，sys，world这六个数据库都是在MySQL安装完成后由系统自动创建的
  • 1.information_schema：信息数据库，主要存储了系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、字符集信息和分区信息等
  • 2.mysql：MySQL的核心数据库，主要负责存储数据库的用户、权限设置、关键字等控制和管理信息，可以在mysql 数据库的 user 表中修改 root 用户密码
  • 3.performance_schema：主要用于收集数据库服务器性能参数，该数据库中所有表的存储引擎均为performance_schema，而用户不能创建存储引擎为performance_schema的表
  • 4.sakila：MySQL 提供的样例数据库，该数据库共有 16 张表，这些数据表都是比较常见的在设计数据库时，可以参照这些样例数据表来快速完成所需的数据表
  • 5.sys：MySQL 5.7 安装完成后会多一个 sys 数据库，sys 数据库主要提供了一些视图，数据都来自于 performation_schema，主要是让开发者和使用者更方便地查看性能问题
  • 6.world：MySQL 自动创建的数据库，该数据库中只包括 3 张数据表，分别保存城市，国家和国家使用的语言等内容

SHOW CREATE DATABASE 数据库名称;

例：查看创建好的数据库SSCMS的信息
SHOW CREATE DATABASE SSCMS;

• 1.查看MySQL中某个已经创建的具体数据库的定义信息
  
• 2.结果显示数据库SSCMS的创建信息及编码方式，且数据库创建之后数据库编码方式就确定了

  CREATE DATABASE `SSCMS` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
  

3.数据库的修改

ALTER DATABASE [数据库名] 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>;

• 1.MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中
  
• 2.ALTER DATABASE [数据库名]：用于更改数据库的全局特性，使用 ALTER DATABASE 需要获得数据库 ALTER 权限
  • 1.其中数据库名称是要修改的数据库的名称，数据库名称可以忽略，此时语句对应于默认数据库
• 3.CHARACTER SET：更改默认的数据库字符集

  例：将数据库SSCMS的编码方式修改为gbk
  ALTER DATABASE SSCMS
  DEFAULT CHARACTER SET gbk
  COLLATE gbk_bin;
  

  

4.数据库的删除

DROP DATABASE [IF EXISTS] <数据库名称>；

• 1.<数据库名称>：指定要删除的数据库名
• 2.IF EXISTS：用于防止当数据库不存在时发生错误
• 3.DROP DATABASE：删除数据库中的所有表格并同时删除数据库，使用 DROP DATABASE需要获得数据库 DROP 权限

  例：删除名为SSCMS的数据库
  DROP DATABASE SSCMS;
  

  
• 4.注意
  • 1.MySQL 安装后系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，用于存放一些和数据库相关的信息，如果删除了这两个数据库MySQL 将不能正常工作
  • 2.使用 DROP DATABASE 命令时要非常谨慎，执行该命令后MySQL 不会给出任何提示确认信息，DROP DATABASE 删除数据库后，数据库中存储的所有数据表和数据也将一同被删除而且不能恢复，因此最好在删除数据库之前先将数据库进行备份

5.数据库的选择

USE <数据库名>

• 1.MySQL 中就有很多系统自带的数据库，操作数据库之前要确定是哪一个数据库
• 2.当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库
• 3.USE可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库,该数据库保持为默认数据库直到语段的结尾或直到遇见一个不同的 USE 语句
• 4.只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作
• 5.如果出现Database changed提示，则表示选择数据库成功

5.数据表的基本操作

1.数据表的创建

CREATE TABLE 数据表名称（
字段名1 数据类型 [完整性约束条件]，
字段名2 数据类型 [完整性约束条件]，
...
字段名3 数据类型 [完整性约束条件]
）;

• 1.CREATE TABLE：创建给定名称的表，必须拥有表CREATE的权限
• 2.数据表名称：创建的数据表的名称，必须符合标识符命名规则
• 3.字段名：数据表的列名
• 4.数据类型：当前列的数据类型
• 5.完整性约束条件：字段的特殊约束条件

  例：在SSCMS数据库中创建一个用于存储教师信息的Teacher表
  USE SSCMS;
  CREATE TABLE Teacher(
  ID INT(5),-- 教师的工号
  NAME VARCHAR(10),-- 教师的姓名
  EMAIL VARCHAR(20)-- 教师的邮箱地址
  );
  或 创建表的同时指定存储引擎和字符以及字符排序规则
  CREATE TABLE `teacher` (
  `ID` int DEFAULT NULL,
  `NAME` varchar(10) DEFAULT NULL,
  `EMAIL` varchar(20) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
  

• 6.注意：创建数据表之前一定要使用USE 数据库名;明确是在哪个数据库中创建的，否则系统会抛出No database selected错误

2.数据表的查看

• 1.MySQL中查看数据表的方式有两种
  • 1.SHOW CREATE TABLE 数据表名称
  • 2.DESCRIBE 数据表名称
• 2.查看数据表前使用USE数据库名;明确是查看哪个的数据库中的数据表

1.SHOW CREATE TABLE

SHOW CREATE TABLE 数据表名称; //查看某一个数据表的信息
或
SHOW TABLES;//查看当前数据库所有的数据表

• 1.数据表名称：指定查看的数据表的名字
• 2.SHOW TABLES：查看所有已存在的数据表

  例：使用SHOW CREATE TABLE语句查看Teacher表
  SHOW CREATE TABLE Teacher;
  

  
• 3.SHOW CREATE TABLE查看数据表可以看出表的定义信息以及字符编码方式

2.DESCRIBE

DESCRIBE 数据表名称;
//简写
DESC 数据表名称;

• 1.数据表名称：查看的数据表的名字
• 2.DESCRIBE查看数据表可以看出数据表的字段名，类型，是否为空，是否为主键等信息

  例：使用DESCRIBE语句查看Teacher表
  DESCRIBE Teacher;
  或
  DESC Teacher;
  

  
  • 1.Field： 表示该表的字段名
  • 2.Type： 表示对应字段的数据类型
  • 3.Null： 表示对应字段是否可以存储NULL值
  • 4.Key： 表示对应字段是否编制索引和约束
  • 5.Default： 表示对应字段是否有默认值
  • 6.Extra： 表示获取到的与对应字段相关的附加信息

3.数据表的修改

ALTER TABLE <表名> [修改选项]
//修改选项的语法格式如下：
RENAME TO <新表名>
| ADD COLUMN <列名> <类型>
| MODIFY COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| DROP COLUMN <列名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名>

• 1.数据表创建之后用户可以对表中的某些信息进行修改

1.修改表名

ALTER TABLE 旧表名 RENAME [TO] 新表名;

例：将数据库中SSCMS中Teacher表的表名改为SSCMS_Teacher
ALTER TABLE Teacher RENAME SSCMS_Teacher;

2.添加字段

ALTER TABLE 表名 
ADD [COLUMN] 新字段名 数据类型 [约束条件] [FIRST|AFTER 已经存在的字段名]; 

• 1.新字段名： 新添加的字段名称
• 2.FIRST： 用于将新添加的字段设置为表的第一个字段
• 3.AFTER 已经存在的字段名： 用于将新添加的字段添加到指定字段的后面，如不指定位置则默认将新添加字段追加到表末尾

  例：在SSCMS_Teacher表中添加一个没有约束条件的INT（4）类型的AGE字段
  ALTER TABLE SSCMS_Teacher ADD AGE INT(4);
  

  

3.修改字段的数据类型

ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型;

例：将SSCMS_Teacher表中WORKID字段的数据类型由TINYINT修改为INT(5)
ALTER TABLE SSCMS_Teacher MODIFY WORKID INT(5);

4.修改字段名和数据类型

ALTER TABLE 表名 CHANGE [COLUMN] 旧字段名 新字段名 新数据类型;

• 1.旧字段名：修改之前的字段名称

• 2.新字段名：修改之后的字段名称

• 3.新数据类型：修改后的数据类型

• 4.注意修改后的数据类型不能为空

  • 1.如果只修改字段名，不修改数据类型，可以将新数据类型写为字段原来的数据类型
  • 2.如果只修改数据类型，不修改字段类型，可以将新字段名写为旧字段名

  例：将SSCMS_Teacher表中ID字段改名为WORKID，数据类型保持不变
  ALTER TABLE SSCMS_Teacher CHANGE ID WORKID INT(5);
  

  
  

  例：将SSCMS_Teacher表中WORKID字段的数据类型由INT（5）修改为TINYINT
  ALTER TABLE SSCMS_Teacher CHANGE WORKID WORKID TINYINT;
  

  

5.修改字段的位置

ALTER TABLE 表名 MODIFY 字段名1 新数据类型 [FIRST|AFTER 字段名2]

• 1.FIRST： 用于将字段名1设置为表的第一个字段
• 2.AFTER： 用于将字段名1移动到字段名2的后面
• 3.新数据类型： 要修改字段的新数据类型，如果只修改位置，不修改数据类型，可以将新数据类型写为字段原来的数据类型

  例：将SSCMS_Teacher表中的NAME字段修改为表中的第一个字段
  ALTER TABLE SSCMS_Teacher MODIFY NAME VARCHAR(10) FIRST;
  

  

  例：重新添加AGE字段，并将SSCMS_Teacher表中的WORKID字段移动到AGE字段之后
  USE sscms;
  ALTER TABLE SSCMS_Teacher ADD AGE INT(4);
  ALTER TABLE sscms_teacher MODIFY WORKID INT AFTER AGE; 
  

  

6.删除字段

ALTER TABLE 表名 
DROP [COLUMN] 字段名; 

• 1.字段名： 指明要删除的字段名称

  例：删除SSCMS_Teacher表中
  ALTER TABLE SSCMS_Teacher DROP AGE;
  

  

7.修改表字符集

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

4.数据表的删除

• 1.一般数据库中的多个数据表之间可能会存在关联，要删除具有关联关系的数据表需先删除关联表或删除关联关系
• 2.删除表的同时表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份以免造成无法挽回的损失

1.删除数据表

DROP TABLE [IF EXISTS] 表名1 [,表名2，表名3...]；

• 1.表名1：表示要被删除的数据表的名称，可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可
• 2.IF EXISTS：用于在删除数据表之前判断该表是否存在，如果不加 IF EXISTS当数据表不存在时 MySQL 将提示错误

  例：删除SSCMS_Teacher表
  DROP TABLE SSCMS_Teacher;
  

  DESC SSCMS_teacher	Error Code: 1146. Table 'sscms.sscms_teacher' doesn't exist	0.000 sec
  

• 3.注意
  • 1.用户必须拥有执行 DROP TABLE的权限，否则数据表不会被删除
  • 2.表被删除时用户在该表上的权限不会自动删除。

2.删除被其他表关联的主表

• 1.数据表之间经常存在外键关联的情况，这时如果直接删除父表会破坏数据表的完整性会删除失败
• 2.删除父表有以下两种方法
  • 1.先删除与它关联的子表再删除父表，但是这样会同时删除两个表中的数据
  • 2.将关联表的外键约束取消再删除父表，适用于需要保留子表的数据只删除父表的情况

  # 例：先建一个父表和一个子表，需要先删除父子表之间的外键才能再删除父表
  CREATE TABLE tb_emp1
  (
  id INT(11) PRIMARY KEY,
  name VARCHAR(22),
  location VARCHAR (50)
  );
  CREATE TABLE tb_emp2
  (
  id INT(11) PRIMARY KEY,
  name VARCHAR(25),
  deptId INT(11),
  salary FLOAT,
  CONSTRAINT fk_emp1_emp2 FOREIGN KEY (deptId) REFERENCES tb_emp1(id)
  );
  SHOW CREATE TABLE tb_emp2;
  ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_emp1_emp2;
  DROP TABLE tb_emp1;
  

  

5.数据表的约束

• 1.约束的目的是保证数据库中数据的完整性和一致性
• 2.常见的表约束
  • 1.主键约束（PRIMARY KEY CONSTRAINT）
  • 2.外键约束（FOREIGN KEY CONSTRAINT）
  • 3.非空约束（NOT NULL CONSTRAINT）
  • 4.唯一约束（UNIQUE CONSTRAINT）
  • 5.默认约束（DEFAULT CONSTRAINT）

1.主键约束（PRIMARY KEY ）

• 1.主键： 由表中的一个字段或多个字段组成，能够唯一地标识表中的一条记录
• 2.主键约束： 要求主键字段中的数据唯一并且不允许为空，主键分为两种类型：
  • 1.单字段主键
  • 2.复合主键
• 3.注意：
  • 1.每个数据表最多只能有一个主键
  • 2.主键值必须唯一标识表中的每一行且不能为 NULL，即表中不可能存在有相同主键值的两行数据，这是唯一性原则
  • 3.一个字段名只能在联合主键字段表中出现一次
  • 4.联合主键不能包含不必要的多余字段，当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的，这是最小化原则

1.创建表时设置主键约束

1.单字段主键

//定义字段的同时指定主键
字段名 数据类型 PRIMARY KEY [默认值]
//定义完所有字段之后指定主键
[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

例：创建company表，并设置company_id字段为为主键
USE sscms;
CREATE TABLE company(
 company_id int(11) primary key,
 company_name varchar(50),
 company_address varchar(200)
);
//定义完所有字段之后指定主键
CREATE TABLE tb_emp3
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
PRIMARY KEY(id)
);

• 1.id字段的Key显示为PRI：表示此字段为主键

2.复合主键

//创建表时指定复合主键
PRIMARY KEY（字段名1，字段名2，...，字段名n）；

• 1.指主键是由一张表中多个字段组成的
• 2.字段名1，字段名2，…，字段名n： 指的是构建主键的多个字段的名称
• 3.注意：当主键是由多个字段组成时不能直接在字段名后面声明主键约束，只能定义完所有字段之后指定主键

  例：创建sales表，设置product_id,region_code字段为复合主键
  USE sscms;
  CREATE TABLE sales(
   product_id INT(11),
   region_code varchar(10),
   quantity int(11),
   price float,
   primary key (product_id,region_code)
  );
  

  
• 4.product_id和region_code字段的Key均显示为PRI：表示这两个字段共同作为主键

2.修改表时添加主键约束

1.单字段主键

//为已存在的表添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
//将已存在的表的某个字段改为主键约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 PRIMARY KEY;

例：将company表的company_id字段修改为主键
//（因为每个数据表最多能有一个主键）首先将前面创建的company表删除，再新建company表，再将company表的company_id字段修改为主键
USE sscms;
DROP TABLE company;
CREATE TABLE company(
 company_id int(11),
 company_name varchar(50),
 company_address varchar(200)
);
ALTER TABLE company MODIFY company_id INT(11) PRIMARY KEY;

2.复合主键

//为已存在的表添加复合主键
ALTER TABLE 表名 MODIFY ADD PRIMARY KEY（字段名1，字段名2，...，字段名n）；

例：将sales表的product_id字段和region_code字段作为复合主键
//（因为每个数据表最多能有一个主键）首先将前面创建的sales表删除，再新建sales表，再将sales表的product_id字段和region_code字段作为复合主键
USE sscms;
DROP TABLE sales;
CREATE TABLE sales(
 product_id int(11),
 region_code varchar(10),
 quantity int (11),
 price float
);
ALTER TABLE sales ADD PRIMARY KEY(product_id,region_code);

3.删除主键约束

1.单字段主键

//删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY；

例：删除company表的company_id字段的主键约束
ALTER TABLE company DROP PRIMARY KEY;

• 1.由于主键约束在一个表中只能有一个，因此不需要指定主键名就可以删除一个表中的主键约束

2.复合主键

//删除复合主键约束
ALTER TABLE 表名 DROP PRIMARY KEY；

例：删除sales表的product_id字段和region_code字段作为复合主键
ALTER TABLE company DROP PRIMARY KEY;

4.主键自增长

字段名 数据类型 AUTO_INCREMENT

• 1.MySQL 中当主键定义为自增长后，这个主键的值不再需要用户输入数据而由数据库系统根据定义自动赋值，每增加一条记录主键会自动以相同的步长进行增长
• 2.通过给字段添加 AUTO_INCREMENT属性来实现主键自增长，默认情况下AUTO_INCREMENT 的初始值是 1，每新增一条记录字段值自动加 1
• 3.一个表中只能有一个字段使用 AUTO_INCREMENT 约束，且该字段必须为唯一索引以避免序号重复（即为主键或主键的一部分）
• 4.AUTO_INCREMENT 约束的字段必须具备 NOT NULL 属性
• 4.AUTO_INCREMENT 约束的字段只能是整数类型（TINYINT、SMALLINT、INT、BIGINT 等）
• 5.AUTO_INCREMENT 约束字段的最大值受该字段的数据类型约束，如果达到上限AUTO_INCREMENT 就会失效
  

1.创建表时指定字段值自动增加

字段名 数据类型 PRIMARY KEY AUTO_INCREMENT;

例：创建company表，并设置company_id字段为主键，其值自动增加company_address设置为非空约束，company_name字段设置为唯一约束，company_tel字段的默认值为“0371-”
USE sscms;
DROP TABLE IF EXISTS company;
CREATE TABLE company(
 company_id INT(11) PRIMARY KEY AUTO_INCREMENT,
 company_name VARCHAR(50) UNIQUE,
 company_address VARCHAR(200) NOT NULL,
 company_tel VARCHAR(20) DEFAULT '0371-'
); 
desc company;

• 1.company_id字段的Extra列的值为atuo_increment，表示这个字段是自动增加的
• 2.系统会自动填入自动增加字段的值，用户在插入数据时不需要给出

2.修改表时设置字段值自动增加

ALTER TABLE 表名 MODIFY 字段名 新数据类型 AUTO_INCREMENT；

• 1.此命令可以同时修改字段的数据类型和设置字段值自动增加
• 2.如果不修改字段的数据类型将新数据类型写为字段原来的数据类型即可

  例：设置company表的company_id字段值自动增加
  //首先创建company表并查看company表，再将company表的company_id字段值设置为自动增加
  USE sscms;
  DROP TABLE IF EXISTS company;
  CREATE TABLE company(
   company_id INT(11) PRIMARY KEY,
   company_name VARCHAR(50) UNIQUE,
   company_address VARCHAR(200) NOT NULL,
   company_tel VARCHAR(20) DEFAULT '0371-'
  );
  DESC company;
  ALTER TABLE company MODIFY company_id INT(11) AUTO_INCREMENT;
  DESC company;
  

  
  

3.删除字段值的自动增加

ALTER TABLE 表名 MODIFY 字段名 数据类型;

例：删除company表的company_id字段值的自动增加
ALTER TABLE company MODIFY company_id VARCHAR(11);

• 1.company_id字段的Extra列的值为空，表示这个字段不再自动增加

4.指定自增字段初始值

• 1.如果第一条记录设置了该字段的初始值，那么新增加的记录就从这个初始值开始自增

  CREATE TABLE IF NOT EXISTS tb_student(
  id INT(4) AUTO_INCREMENT,
  name VARCHAR(25) NOT NULL,
  PRIMARY KEY(id)
  )AUTO_INCREMENT=100;
  INSERT INTO tb_student (name)VALUES('Test');
  SELECT * FROM tb_student;
  

  

5.自增字段值不连续

CREATE TABLE IF NOT EXISTS tb_student(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) UNIQUE KEY,
age INT DEFAULT NULL
);
INSERT INTO tb_student VALUES(1,1,1);
INSERT INTO tb_student VALUES(null,1,1);
INSERT INTO tb_student VALUES(null,2,1);
SELECT * FROM tb_student;

• 1.先添加一条数据VALUES(1,1,1)，再添加一条数据VALUES(null,1,1)，由于表中已经存在 name=1 的记录且name为唯一键，所以报 Duplicate key error（唯一键冲突）
• 2.这之后再插入新的数据时自增 id 就是 3，这样就出现了自增字段值不连续的情况

2.外键约束（FOREIGN KEY ）

• 1.外键： 两个表的数据之间建立关联，可以是一个字段或多个字段
• 2.关联： 指关系数据库中相关表之间的联系，通过相同或相容的字段或字段组来表示
• 3.主表： 对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即主表
• 4.从表： 对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即从表
• 5.注意：
  • 1.每个数据表可以有一个或多个外键
  • 2.一个表的外键可以为空值，若不为空值则每一个外键值必须等于另一个表中主键的某个值
  • 3.子表的外键必须关联父表的主键且关联字段的数据类型必须匹配
  • 4.定义外键后不允许在主表中删除与子表具有关联关系的记录
• 6.定义外键时需要遵守下列规则
  • 1.主表必须已经存在于数据库中或是当前正在创建的表，如果是后一种情况则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性
  • 2.必须为主表定义主键
  • 3.主键不能包含空值但允许在外键中出现空值
  • 4.外键中列的数目必须和主表的主键中列的数目相同
  • 5.外键中列的数据类型必须和主表主键中对应列的数据类型相同

1.创建表时添加外键约束

[CONSTRAINT <外键名>] FOREIGN KEY（从表的外键字段名1[,字段名2,...]）REFERENCES 主表名 （主表的主键字段名1[,字段名2,...]）;

• 1.外键名： 指从表创建的外键约束的名字

  例：创建部门表dept和员工表emp，并在员工表上创建外键
  //先创建主表dept，再创建从表emp
  USE sscms;
  CREATE TABLE dept(
   id INT(11) PRIMARY KEY,
   name VARCHAR(22),
   location VARCHAR(50),
   description VARCHAR(200)
  );
  CREATE TABLE emp(
   id INT(11) PRIMARY KEY,
   name VARCHAR(25),
   dept_id INT(11),
   salary FLOAT,
   CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
  );
  

  CREATE TABLE `dept` (
    `id` int NOT NULL,
    `name` varchar(22) DEFAULT NULL,
    `location` varchar(50) DEFAULT NULL,
    `description` varchar(200) DEFAULT NULL,
    PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  
  CREATE TABLE `emp` (
    `id` int NOT NULL,
    `name` varchar(25) DEFAULT NULL,
    `dept_id` int DEFAULT NULL,
    `salary` float DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_emp_dept` (`dept_id`),
    CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  

• 注意：从表的外键关联的必须是主表的主键，且主键和外键的数据类型必须一致

2.修改表时添加外键约束

ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY （从表的外键字段名）REFERENCES 主表名（主表的主键字段名） ;

• 1.外键名： 指从表创建的外键约束的名字

  例：已存在部门表dept和员工表emp，为员工表emp创建外键
  //先删除emp表和dept表(先删除从表emp)，然后创建主表dept，再创建从表emp，最后为员工表emp创建外键
  USE sscms;
  DROP TABLE emp;
  DROP TABLE dept;
  CREATE TABLE dept(
   id INT(11) PRIMARY KEY,
   name VARCHAR(22), 
   location VARCHAR(50),
   description VARCHAR(200)
  );
  CREATE TABLE emp(
   id INT(11) PRIMARY KEY,
   name VARCHAR(25),
   dept_id INT(11),
   salary FLOAT
  );
  ALTER TABLE emp ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id);
  

  CREATE TABLE `dept` (
   `id` int NOT NULL,
   `name` varchar(22) DEFAULT NULL,
   `location` varchar(50) DEFAULT NULL,
   `description` varchar(200) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  
  CREATE TABLE `emp` (
   `id` int NOT NULL,
   `name` varchar(25) DEFAULT NULL,
   `dept_id` int DEFAULT NULL,
   `salary` float DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  

  CREATE TABLE `dept` (
   `id` int NOT NULL,
   `name` varchar(22) DEFAULT NULL,
   `location` varchar(50) DEFAULT NULL,
   `description` varchar(200) DEFAULT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  
  CREATE TABLE `emp` (
   `id` int NOT NULL,
   `name` varchar(25) DEFAULT NULL,
   `dept_id` int DEFAULT NULL,
   `salary` float DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `fk_emp_dept` (`dept_id`),
   CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  

• 注意：从表中外键列中的数据必须与主表中主键列中的数据一致或是没有数据

3.删除外键关联

ALTER TABLE 从表名 DROP FOREIGN KEY 外键名；

例：删除emp表dept_id字段的外键约束，外键约束名是fk_emp_dept
ALTER TABLE emp DROP FOREIGN KEY fk_emp_dept;

CREATE TABLE `emp` (
 `id` int NOT NULL,
 `name` varchar(25) DEFAULT NULL,
 `dept_id` int DEFAULT NULL,
 `salary` float DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_emp_dept` (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

• 1.对比上下结果看出已经成功删除了dept表和emp表的主外键关联
• 2.但是仍出现KEY fk_emp_dept (dept_id)的信息，因为MySQL在创建外键后会自动创建一个同名的索引，外键删除但是索引不会被删除

3.唯一约束（UNIQUE）

• 1.要求该列值唯一，不能重复，唯一约束可以确保一列或者几列不出现重复值
• 2.唯一约束与主键约束相同点：都可以确保列的唯一性
• 3.不同点：唯一约束在一个表中可有多个并且设置唯一约束的列允许有空值但是只能有一个空值，而主键约束在一个表中只能有一个且不允许有空值

1.创建表时添加唯一约束

字段名 数据类型 UNIQUE;

例：创建company表，并设置company_id字段为主键，company_address设置为非空约束，company_name字段设置为唯一约束
USE sscms;
DROP TABLE IF EXISTS company;
CREATE TABLE company(
 company_id INT(11) PRIMARY KEY,
 company_name VARCHAR(50) UNIQUE,
 company_address VARCHAR(200) NOT NULL 
);

• 1.company_name字段的Key值为UNI，表示这个字段具有唯一约束
• 2.注意：
  • 1.一个表中可以有多个字段声明为唯一约束，但是只能有一个主键
  • 2.声明为主键的字段不允许有空值，但是声明为唯一约束的字段允许存在空值且只能有一个空值

2.修改表时添加唯一约束

ALTER TABLE 表名 MODIFY 字段名 新数据类型 UNIQUE；
//或
ALTER TABLE 表名 ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

• 1.此命令可以同时修改字段的数据类型和增加唯一约束
• 2.如果不修改字段的数据类型，将新数据类型写为字段原来的数据类型即可

  例：将company表的company_name字段修改为唯一约束
  //首先创建company表并查看company表，再将company表的company_name字段设置为唯一约束
  USE sscms;
  DROP TABLE IF EXISTS company;
  CREATE TABLE company(
   company_id INT(11) PRIMARY KEY,
   company_name VARCHAR(50),
   company_address VARCHAR(200) NOT NULL
  );
  DESC company;
  ALTER TABLE company MODIFY company_name VARCHAR(50) UNIQUE;
  DESC company;
  

  CREATE TABLE IF NOT EXISTS tb_student(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20),
  age INT DEFAULT NULL
  );
  ALTER TABLE tb_student ADD CONSTRAINT unique_name UNIQUE(name);
  DESC tb_student;
  

  
  
  

3.删除唯一约束

ALTER TABLE 表名 DROP INDEX 字段名;
//或
ALTER TABLE 表名 DROP INDEX 唯一约束名;

例：删除company表的company_name字段的唯一约束
ALTER TABLE company DROP INDEX company_name;
//或
ALTER TABLE tb_student DROP INDEX unique_name;

• 1.company_name字段的Key列的值为空，表示这个字段已没有唯一约束

4.检查约束(CHECK)

• 1.检查约束是用来检查数据表中字段值是否有效的一个手段
• 2.设置字段的检查约束时要根据实际情况进行设置，这样能够减少无效数据的输入

1.创建表时添加检查约束

CHECK(<检查约束>)

例：创建 tb_company 数据表，要求 salary 字段值大于 0 且小于 10000
CREATE TABLE tb_company
(
id INT(11) PRIMARY KEY,
name VARCHAR(25),
deptId INT(11),
salary FLOAT,
CHECK(salary>0 AND salary<100)
);

2.修改表时添加检查约束

ALTER TABLE 表名 ADD CONSTRAINT <检查约束名> CHECK(<检查约束>)

例：修改 tb_company 表，要求 id 字段值大于 0
ALTER TABLE tb_company
ADD CONSTRAINT check_id
CHECK(id>0);

3.删除检查约束

ALTER TABLE 表名 DROP CONSTRAINT 检查约束名;

例：删除 tb_company  表中的 check_id 检查约束
ALTER TABLE tb_company
DROP CONSTRAINT check_id;

5.非空约束（NOT NULL）

• 1.指字段的值不能为空，同一个数据表中可以定义多个非空字段

1.创建表时添加非空约束

字段名 数据类型 NOT NULL;

例：创建company表，并设置company_id字段为主键，company_address设置为非空约束
USE sscms;
DROP TABLE IF EXISTS company;
CREATE TABLE company(
 company_id INT(11) PRIMARY KEY,
 company_name VARCHAR(50),
 company_address VARCHAR(200) NOT NULL 
);

• 1.company_address字段的Null值为NO，表明这个字段不允许为空

2.修改表时添加非空约束

ALTER TABLE 表名 MODIFY 字段名 新数据类型 NOT NULL；
//或
ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 新数据类型 NOT NULL;

• 1.此命令可以同时修改字段的数据类型和增加非空约束
• 2.如果不修改字段的数据类型，将新数据类型写为字段原来的数据类型即可

  例：将company表的company_address字段设置为非空约束
  //首先创建company表并查看company表，再将company表的company_address字段设置为非空约束
  USE sscms;
  DROP TABLE IF EXISTS company;
  CREATE TABLE company(
   company_id INT(11) PRIMARY KEY,
   company_name VARCHAR(50),
   company_address VARCHAR(200)
  );
  DESC company;
  ALTER TABLE company MODIFY company_address VARCHAR(50) NOT NULL;
  DESC company;
  

  
  

3.删除非空约束

ALTER TABLE 表名 MODIFY 字段名 数据类型
//或
ALTER TABLE 表名 CHANGE COLUMN 旧字段名 新字段名 新数据类型 NULL;

例：删除company表的company_address字段的非空约束
ALTER TABLE company MODIFY company_address VARCHAR（50）;

结果：

• 1.company_address字段的Null列的值为YES，表示这个字段允许为空

6.默认值约束（DEFAULT CONSTRAINT）

• 1.若将数据表中某列定义为默认约束，用户插入新的数据行时如果没有为该列指定数据，那么数据库系统会自动将默认值赋给该列，默认值也可以是空值NULL

1.创建表时添加默认值约束

字段名 数据类型 DEFAULT 默认值;

例：创建company表，并设置company_id字段为主键，company_address设置为非空约束，company_name字段设置为唯一约束，company_tel字段的默认值为“0371-”
USE sscms;
DROP TABLE IF EXISTS company;
CREATE TABLE company(
 company_id INT(11) PRIMARY KEY,
 company_name VARCHAR(50) UNIQUE,
 company_address VARCHAR(200) NOT NULL,
 company_tel VARCHAR(20) DEFAULT '0371-'
);

• 1.company_tel字段的Default列的值为0371-，表示这个字段具有默认值0371-
• 注意：
  • 1.默认值为该字段设置的默认值，如果是字符类型的要用单引号括起来

2.修改表时添加默认值约束

ALTER TABLE 表名 MODIFY 字段名 新数据类型 DEFAULT 默认值；
//或
ALTER TABLE 表名 CHANGE COLUMN 字段名 数据类型 DEFAULT 默认值;

• 1.此命令可以同时修改字段的数据类型和增加默认约束
• 2.如果不修改字段的数据类型，将新数据类型写为字段原来的数据类型即可

  例：将company表的company_tel字段添加默认约束，默认值为‘0371-’
  //首先创建company表并查看company表，再将company表的company_tel字段添加默认约束，默认值为‘0371-’
  USE sscms;
  DROP TABLE IF EXISTS company;
  CREATE TABLE company(
   company_id INT(11) PRIMARY KEY,
   company_name VARCHAR(50) UNIQUE,
   company_address VARCHAR(200) NOT NULL,
   company_tel VARCHAR(20)
  );
  DESC company;
  ALTER TABLE company MODIFY company_tel VARCHAR(20) DEFAULT '0371-';
  DESC company;
  

  
  

3.删除默认值约束

ALTER TABLE 表名 MODIFY 字段名 数据类型;
//或
ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 DEFAULT NULL;

例：删除company表的company_tel字段的默认约束
ALTER TABLE company MODIFY company_tel VARCHAR(20);

7.查看数据表中的约束

SHOW CREATE TABLE <数据表名>;

6.数据表记录操作

1.数据表记录的插入

INSERT [INTO] 表名 [(字段名列表)]
VALUES ({DEFAULT | NULL | 值} [,...n]);

• 1.INSERT：通过INSERT语句可以向数据表中插入新的数据
• 2.INTO: 用在INSERT关键字与要插入数据的表名之间的关键字，可省略
• 3.字段名列表： 指定要插入数据的字段名，可省略
  • 1.如果不写字段名，表示要向表中的所有字段插入数据
  • 2.如果部分字段名，表示只为指定的字段插入数据，多个字段名之间用逗号隔开
• 4.{DEFAULT | NULL | 值}： 大括号括起来的多个选项，表示必须选择其中之一
  • 1.DEFAULT: 表示为某字段插入指定的默认值
  • 2.NULL: 表示为某字段插入空值
  • 3.值: 表示为某字段指定一个具有数据值的变量或表达式
• 5.注意： 向CHAR,VARCHAR,DATE类型的字段插入数据时，字段值要用英文半角引号括起来

1.向数据表中插入单条记录

例：用INSERT语句为学生选课数据库中的STUDENTINFO表添加一条记录：
//学号是10101001
//姓名是张永峰
//性别是男
//出生年月是1993年8月1日
//所在班级是电子商务101
INSERT INTO STUDENTINFO (sno，sname，sgender，sbirth，sclass)
VALUES ('10101001','张永峰','男','1993-8-1','电子商务101');
//上述语句为数据表STUDENTINFO的所有字段都指定了值，所以可以简写为：
INSERT INTO STUDENTINFO
VALUES ('10101001','张永峰','男','1993-8-1','电子商务101');

例：用INSERT语句向学生选课数据库中STUDENTINFO表中添加另一条记录：
//学号是10101002
//姓名是何小丽
//性别是女
INSERT INTO STUDENTINFO （sno，sname，sgender）
VALUES ('10101002','何小丽','女');

• 1.使用INSERT语句为部分字段添加值时必须要在表名后写明为哪些字段添加值
• 2.表名后的字段名顺序可以与表中定义的字段顺序不一致，但需要与VALUES语句后面值的顺序一致

2.向数据表中插入多条记录

INSERT [INTO] 表名 [(字段名列表)]
VALUES (值列表),(值列表),...,(值列表);

例：用INSERT语句为学生选课数据库中的STUDENTINFO表添加两条记录：
//第一条记录：学号是10102001，姓名是王斌，性别是男，出生日期是1991-7-14，所在班级是网络技术101
//第二条记录：学号是10102003，姓名是孙平平，性别是女，出生日期是1992-2-27，所在班级是网络技术101
USE SSCDB;
INSERT INTO STUDENTINFO
VALUE ('10102001','王斌','男','1991-7-14','网络技术101'),('10102003','孙平平','女','1992-2-27','网络技术101');

• 1.添加多条记录时可以不指定字段名列表，只需要保证VALUES语句后面的值是依照字段在表中定义的顺序排列的即可
• 2.如果不指定字段名，必须为所以字段添加数据，如果指定了字段名，只需要为指定的字段添加数据即可

2.数据表记录的修改

UPDATE 表名
SET 字段名 = value [,...]
[WHERE 条件表达式];

• 1.MySQL中使用UPDATE语句来实现数据表记录的修改
• 2.value： 为指定的字段赋予新值，新值可以是表达式或数据
• 3.WHERE 条件表达式： 指定要修改记录的条件，如果不写条件则表达式要将所以记录指定字段的值修改成新的值；如果写了条件，则只修改满足条件的记录的指定字段
• 4.注意： 修改表记录时，需要注意表的唯一性约束和表与表之间的外键约束，否则可能会出现修改时报错的情况

  例：在学生选课数据库中把STUDENTINFO表中sclass字段的值改为计算机
  USE SSCDB;
  UPDATE STUDENTINFO
  SET sclass = '计算机';
  //例：同时修改多个数据
  UPDATE notice
  SET title = "测试",details = "测试内容",details = "2021-08-08"
  WHERE n_id = 3;
  

  UPDATE STUDENTINFO
  SET sclass = '计算机'	4 row(s) affected
  Rows matched: 4  Changed: 4  Warnings: 0	0.015 sec
  

  

  例：在学生选课数据库中，把STUDENTINFO表中张宇的出生日期改为1993年8月21日
  USE SSCDB;
  UPDATE STUDENTINFO
  SET sbirth = '1993-08-21'
  WHERE sno = '王斌';
  

  UPDATE STUDENTINFO
  SET sbirth = '1993-8-21'
  WHERE sname = '王斌'	0 row(s) affected
  Rows matched: 1  Changed: 0  Warnings: 0	0.000 sec
  

• 1.王斌需要用英文单引号括起来，否则会出现错误UPDATE STUDENTINFO SET sbirth = '1993-8-21' WHERE sname = 王斌 Error Code: 1054. Unknown column '王斌' in 'where clause' 0.015 sec
• 2.sclass字段下只有一条记录满足WHERE条件，所以只修改了这行数据的值
  

3.数据表记录的删除

• 1.删除数据表记录有两种方式
  • 1.使用DELETE删除数
  • 2.使用TRUNCATE清空数据

1.使用DELETE删除数据

DELETE [FROM] 表名
[WHERE 条件表达式];

• 1.WHERE 条件表达式：指定表中要删除记录的条件
  • 1.如果不写条件，则表示要清空指定的数据表;
  • 2.如果写了条件，则只将满足条件的记录删除
• 2.注意： 删除表记录的时候需要注意表与表之间的外键约束，否则可能会出现删除时报错的情况

  例：在学生选课数据库中，删除张永峰同学的基本信息
  DELETE FROM STUDENTINFO
  WHERE sname = '张永峰';
  

  DELETE FROM STUDENTINFO WHERE sname = '张永峰'	1 row(s) affected	0.016 sec
  

  

  例：在学生选课数据库中，删除数据表STUDENTINFO中的所有记录
  USE SSCDB;
  DELETE FROM STUDENTINFO;
  

  DELETE FROM STUDENTINFO	3 row(s) affected	0.000 sec
  

2.使用TRUNCATE清空数据

TRUNCATE [TABLE] 表名;

• 1.TRUNCATE： 清空指定的数据表

  例：在学生选课数据库中，删除数据表STUDENTINFO中的所有记录
  TRUNCATE TABLE STUDENTINFO;
  

  

3.DELETE和TRUNCATE的区别

• 1.DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句
• 2.DELETE语句后面可以跟WHERE子句，通过指定WHERE子句中的条件表达式可以只删除满足条件的部分记录
• 3.TRUNCATE语句只能删除表中的所有记录，不能加WHERE子句
• 4.使用DELETE语句清空数据表后，AUTO_INCREMENT计数器不会恢复为初始值
• 5.使用TRUNCATE语句清空数据表后，AUTO_INCREMENT计数器会被重置为初始值

7.数据查询操作

• 1.MySQL使用SELETE语句从数据库中检索数据并将结果集以表格的形式返回给应户

1.SELECT语句的基本语法

SELECT [ALL|DISTINCT] 查询内容
FROM 表名
[WHERE 查询条件]
[GROUP BY 字段名列表]
[HAVING 查询条件]
[ORDER BY 字段名 [ASC|DESC]]
[LIMT [OFFSET,] n];

• 1.查询内容：
  • 1.查询内容可以是一个字段，多个字段，全部字段，还可以是表达式或函数
  • 2.若要查询部分字段，需要将各字段名用逗号隔开，各字段名在SELECT子句中的顺序决定结果显示的顺序
  • 3.可以用* 表示返回所有字段
• 2.ALL|DISTINCT： 标识查询结果集中对相同行的处理方式，默认值为ALL
  • 1.ALL：表示返回查询结果集中的所有行，包括重复行
  • 2.DISTINCT：表示对数据表中一个或多个字段重复的数据进行过滤，如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，即只有多个字段组合起来完全是一样的情况下才会被去重
• 3.FROM 表名： 指定查询的数据表
• 4.WHERE 查询条件： 限定查询数据必须满足的条件
  • 1.带比较运算符和逻辑运算符的查询条件
    • 1.AND：记录满足所有查询条件时，才会被查询出来
    • 2.OR：记录满足任意一个查询条件时，才会被查询出来
    • 3.XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来
  • 2.带 BETWEEN AND 关键字的查询条件
    • 1.[NOT] BETWEEN 取值1 AND 取值2
    • 2.MySQL中BETWEEN AND 能匹配指定范围内的所有值，包括起始值和终止值
  • 3.带 IS NULL 关键字的查询条件
    • 1.IS [NOT] NULL
  • 4.带 IN 关键字的查询条件
  • 5.带 LIKE 关键字的查询条件
    • 1.[NOT] LIKE '字符串'
    • 2.NOT 是可选参数，字段中的内容与指定的字符串不匹配时满足条件
    • 3.%通配符，代表任何长度的字符串，字符串的长度可以为 0
    • 4._通配符，代表单个字符，字符的长度不能为 0
    • 5.匹配的字符串必须加单引号或双引号
    • 6.默认情况下LIKE关键字匹配字符的时候是不区分大小写的，如果需要区分大小写，可以加入BINARY关键字
    • 7.如果查询内容中包含通配符，可以使用\转义符
• 5.GROUP BY 字段名列表： 根据一个或多个字段对查询结果进行分组
  • 1.GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用，GROUP_CONCAT() 函数会把每个分组的字段值都显示出来
  • 2.多个字段分组查询时，会先按照第一个字段进行分组，如果第一个字段中有相同的值，MySQL才会按照第二个字段进行分组，如果第一个字段中的数据都是唯一的，那么MySQL将不再对第二个字段进行分组
  • 3.GROUP BY 关键字经常和聚合函数一起使用
  • 4.WITH POLLUP关键字用来在所有记录的最后加上一条记录，这条记录是上面所有记录的总和，即统计记录数量
• 6.HAVING 查询条件： 对分组后的数据进行过滤
  • 1.WHERE 和 HAVING 关键字的区别
    • 1.一般WHERE 用于过滤数据行，而 HAVING 用于过滤分组
    • 2.WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数
    • 3.WHERE在数据分组前进行过滤，而HAVING在数据分组后进行过滤
    • 4.WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤，即WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤，如果查询出的字段中没有该字段则会报错，即having过滤的字段必须包含在select中
    • 5.WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名
• 7.ORDER BY 字段名：
  • 1.将查询结果进行的排序，升序ASC和降序DESC，默认情况下查询数据按字母升序进行排序（A～Z）
  • 2.当排序的字段中存在空值时ORDER BY 会将该空值作为最小值来对待
  • 3.ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序
  • 4.对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序
• 8.LIMT [OFFSET,] n：
  • 1.限定查询结果的显示条数，OFFSET表示初始位置，n表示记录数
  • 2.可不指定初始位置，则记录从第一条记录开始显示，下标从0开始
  • 3.LIMIT 后的两个参数必须都是正整数

2.内连接

SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]

• 1.内连接使用 INNER JOIN 关键字连接两张表，并使用 ON 子句来设置连接条件
• 2.<字段名>：需要查询的字段名称
• 3.<表1><表2>：需要内连接的表名
• 4.INNER JOIN ：内连接中可以省略 INNER 关键字，只用关键字 JOIN
• 5.[ON子句]：用来设置内连接的连接条件
• 6.多个表内连接时在 FROM 后连续使用 INNER JOIN 或 JOIN 即可
• 7.组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集部分
  

3.外连接

• 1.内连接的查询结果都是符合连接条件的记录，而外连接会先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录
• 2.外连接可以分为左外连接和右外连接

1.左外连接

SELECT <字段名> FROM <表1> LEFT [OUTER] JOIN <表2> <ON子句>

• 1.左外连接使用 LEFT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件
• 2.<字段名>：需要查询的字段名称
• 3.<表1><表2>：需要左连接的表名
• 4.LEFT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 LEFT JOIN
• 5.ON 子句：用来设置左连接的连接条件，不能省略
• 6.上述语法中<表1>为基表，<表2>为参考表，左连接查询时可以查询出<表1>中的所有记录和<表2>中匹配连接条件的记录，如果<表1>的某行在<表2>中没有匹配行，那么在返回结果中，<表2>的字段值均为空值NULL
• 7.即左外连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为NULL
  

2.右外连接

SELECT <字段名> FROM <表1> RIGHT [OUTER] JOIN <表2> <ON子句>

• 1.右外连接使用 RIGHT OUTER JOIN 关键字连接两个表，并使用 ON 子句来设置连接条件
• 2.<字段名>：需要查询的字段名称
• 3.<表1><表2>：需要左连接的表名
• 4.RIGHT OUTER JOIN：左连接中可以省略 OUTER 关键字，只使用关键字 RIGHT JOIN
• 5.ON 子句：用来设置左连接的连接条件，不能省略
• 6.与左外连接相反，右外连接以<表2>为基表，<表1>为参考表，右连接查询时，可以查询出“<表2>中的所有记录和<表1>中匹配连接条件的记录，如果<表2>的某行在<表1>中没有匹配行，那么在返回结果中<表1>的字段值均为空值NULL
• 7.即右外连接，左表(只会显示符合搜索条件的记录，而右表的记录将会全部表示出来，左表记录不足的地方均为NULL
  

4.并集操作

• 1.Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序，默认是根据主键升序排序
• 2.Union进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果
• 2.Union All：对两个结果集进行并集操作，包括重复行，Union All不会对结果自动进行排序

   select *  
   from test  
   where id<4  
      union all
   select *  
   from student  
   where id>2 and id<6
  

5.子查询

WHERE <表达式> <操作符> (子查询)

• 1.子查询指将一个查询语句嵌套在另一个查询语句中，而且可以进行多层嵌套，子查询经常出现在 WHERE 子句中
• 2.<操作符>：可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字
  • 1.IN | NOT IN：当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE，若使用关键字 NOT，则返回值正好相反
  • 2.EXISTS | NOT EXISTS：用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE，若使用关键字 NOT，则返回的值正好相反
• 3.外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询，注意子查询必须放在圆括号内
• 4.MySQL 执行流程为：先执行子查询，再执行父查询
• 5.一般来说表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换
• 6.子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置，即 SELECT 子句，FROM 子句、WHERE 子句、GROUP BY 子句和 HAVING 子句
• 7.嵌套在 SELECT 语句的 FROM 子句中的子查询必须为表指定别名，可以当作一张临时表
• 8.多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的 SELECT 子句中出现的字段，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配

8.视图

• 1.MySQL 视图View是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中
• 2.数据库中只存放了视图的定义并没有存放视图中的数据，这些数据都存放在定义视图查询所引用的真实表中
• 3.使用视图查询数据时数据库会从真实表中取出对应的数据，因此视图中的数据依赖于真实表中的数据，一旦真实表中的数据发生改变显示在视图中的数据也会发生改变
• 4.如果经常需要从多个表查询指定字段的数据，可以在这些表上建立一个视图，通过这个视图显示这些字段的数据
• 5.视图与表在本质上虽然不相同，但视图经过定义以后结构形式和表一样，可以进行查询、修改、更新和删除等操作

1.视图和数据表的区别

• 1.视图不是数据库中真实的表，而是一张虚拟表，其结构和数据是建立在对数据中真实表的查询基础上的
• 2.存储在数据库中的查询SQL 语句定义了视图的内容，列数据和行数据来自于视图查询所引用的实际表，引用视图时动态生成这些数据
• 3.视图的建立和删除只影响视图本身，不影响对应的基本表

2.视图的优点

• 1.可以定制用户数据，聚焦特定的数据
• 2.可以创建视图来简化操作
• 3.提高数据的安全性，视图是虚拟的，物理上是不存在的
• 4.共享所需数据，通过使用视图，每个用户不必都定义和存储自己所需的数据，可以共享数据库中的数据，同样的数据只需要存储一次
• 5.重用 SQL 语句，视图提供的是对查询操作的封装，本身不包含数据，所呈现的数据是根据视图定义从基础表中检索出来的，如果基础表的数据新增或删除，视图呈现的也是更新后的数据
• 6.注意：
  • 1.创建视图需要访问权限
  • 2.创建视图的数目没有限制
  • 3.视图可以嵌套，即从其他视图中检索数据的查询来创建视图
  • 4.视图不能索引，也不能有关联的触发器、默认值或规则
  • 5.视图可以和表一起使用

3.创建视图

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

• 1.创建视图是指在已经存在的 MySQL 数据库表上建立视图，视图可以建立在一张表中，也可以建立在多张表中
• 1.<视图名>：指定视图的名称，该名称在数据库中必须是唯一的，不能与其他表或视图同名
• 2.<SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图

  //创建基于单表的视图
  CREATE VIEW view_student_info AS
  SELECT * FROM tb_student;
  SELECT * FROM view_student_info;
  //创建基于多表的视图
  CREATE VIEW v_students_info
    (s_id,s_name,d_id,s_age,s_sex,s_height,s_date)
   AS SELECT id,name,dept_id,age,sex,height,login_date
   FROM tb_students_info;
  SELECT * FROM v_students_info;
  

4.查看视图

DESCRIBE 视图名;
//或简写
DESC 视图名;
//查看视图的详细信息
SHOW CREATE VIEW 视图名; # \G结尾，显示结果格式化
例：SHOW CREATE VIEW v_studentinfo \G

5.修改视图

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

• 1.视图是一个虚拟表，实际的数据来自于基本表，所以通过插入、修改和删除操作更新视图中的数据，实质上是在更新视图所引用的基本表的数据
• 2.注意：对视图的修改就是对基本表的修改，因此在修改时要满足基本表的数据定义

  mysql> ALTER VIEW view_students_info
      -> AS SELECT id,name,age
      -> FROM tb_students_info;
  Query OK, 0 rows affected (0.07 sec)
  mysql> DESC view_students_info;
  

6.删除视图

DROP VIEW <视图名1> [ , <视图名2> …]

• 1.DROP VIEW 语句可以一次删除多个视图，但是必须在每个视图上拥有 DROP 权限

  mysql> DROP VIEW IF EXISTS v_students_info;
  Query OK, 0 rows affected (0.00 sec)
  mysql> SHOW CREATE VIEW v_students_info;
  ERROR 1146 (42S02): Table 'test_db.v_students_info' doesn't exist
  

9.索引

1.概念

• 1.索引是一种特殊的数据库结构由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录，所有MySQL列类型都可以被索引
• 2.索引是一个单独的，存储在磁盘上的数据结构，包含着对数据表里所有记录的引用指针
• 2.通过索引查询数据时不用读完记录的所有信息而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配
• 3.索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表
• 4.MySQL 中有以下两种方式访问数据库表的行数据
  • 1.顺序访问：本质是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据，效率非常低下
  • 2.索引访问
    • 1.索引访问是通过遍历索引来直接访问表中记录行的方式
    • 2.使用索引访问前提是对表某一列或多列建立索引，创建了索引后查找数据时可以直接根据索引找到对应记录行的位置，从而快捷地查找到数据
    • 3.扫描索引的速度远远大于扫描实际数据行的速度，所以采用索引的方式可以提高效率
• 5.索引是在存储引擎中实现的，MySQL索引使用的数据结构：B+Tree

2.优缺点

1.优点

• 1.通过创建唯一索引可以保证数据库表中每一行数据的唯一性
• 2.可以给所有的 MySQL 列类型设置索引
• 3.可以大大加快数据的查询速度
• 4.使用分组和排序子句进行数据查询时可以显著减少查询中分组和排序的时间

2.缺点

• 1.创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加
• 2.索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间，如果有大量的索引索引文件可能比数据文件更快达到最大文件尺寸
• 3.索引可以提高查询速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显

3.索引类型

• 1.MySQL索引可以从以下角度来进行分类
  

1.物理存储区分

• 1.MySQL索引按叶子节点存储的是否为完整表数据分为：
  • 1.聚簇索引
  • 2.非聚簇索引
• 2.MyISAM引擎和InnoDB引擎使用的索引类型不同，MyISAM对应的是非聚簇索引，而InnoDB对应的是聚簇索引

1.聚簇索引

• 1.不是一种单独的索引类型，而是一种数据存储方式
• 2.针对不同的引擎，具体实现细节有所不同
• 3.将数据与索引都存放在一起，存储在叶子节点上，找到索引也就找到了数据
• 4.InnoDB的存储文件.ibd同时存储了数据和索引，间接说明InnoDB采用的是聚簇索引
  
• 5.聚簇索引默认由主键实现，用主键作为B+树的key，并且把数据行绑定在叶子节点
• 6.如果表中没有定义主键，InnoDB会选择一个唯一且非空的列代替，如果没有这样的列，InnoDB则会隐式定义一个主键来作为聚簇索引
• 7.注意：一个表只能有一个聚簇索引，因为聚簇索引中包含了数据行，如果有多个聚簇索引，说明存储了多份相同的数据行浪费空间
  
• 8.主键通常建议使用自增id的原因
  • 1.聚簇索引的数据的物理存放顺序与索引顺序是一致的，即只要索引是相邻的那么对应的数据一定也是相邻地存放在磁盘上的
  • 2.如果主键不是自增的，则会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免
  • 3.如果主键是自增的，每次插入都是插入到最后，只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高，总之就是减少分裂和移动的频率

2.非聚集索引

• 1.二级索引又称作辅助索引，属于非聚簇索引
• 2.将数据与索引分开存储，叶子节点没有存储数据行，而只存储索引，索引指向了对应的数据行
• 3.MyISAM引擎的索引文件.MYI和数据文件.MYD是独立分开的，间接MyISAM采用的是非聚簇索引
  
• 4.MyISAM非聚簇索引和InnoDB的聚簇索引的B+树节点的结构完全一致，只是存储的内容不同，InnoDB主键聚簇索引B+树的叶子节点存储了主键和数据行，MyISAM非聚簇索引B+树的叶子节点存储了主键和指向data的指针
  

3.二级索引

• 1.二级索引又称为辅助索引，存在于InnoDB引擎中，属于非聚聚簇索引，二级索引的叶子节点并不存储一行完整的表数据，而是存储了聚簇索引所在列的值
• 2.因为InnoDB中的二级索引存放的是主键值而不是数据行，如果需要查询对应的数据行则需要回表查询，即在聚簇索引中进一步查找对应的数据行，这样可以避免在行移动或插入新数据时出现的页分裂问题
• 3.回表查询：由于二级索引的叶子节点不存储完整的表数据，索引当通过二级索引查询到聚簇索引列值后，还需要回到聚簇索引也就是表数据本身进一步获取数据
  
• 4.InnoDB使用的是聚簇索引，将主键形成一棵B+树中，而行数据就储存在叶子节点上
• 5.若使用主键作为条件查找则按照B+树的检索算法即可查找到对应的叶子节点获得行数据
• 6.若不使用主键作为条件查找则需要两个步骤（回表查询）
  • 1.第一步在辅助索引B+树中检索非主键字段，到达其叶子节点获取对应的主键
  • 2.第二步使用主键在主键索引B+树中再执行一次B+树检索操作，最终到达叶子节点即可获取行数据，重点在于通过其他键需要建立辅助索引
• 7.MyISAM和InnoDB不同，无论是主键索引还是二级索引，索引的叶子节点存放的都是指向数据行的指针，保证可以通过索引进而查找到对应的数据行，只需要对索引进行一遍查找，这样会存在页分裂问题
• 8.MyISM使用的是非聚簇索引，聚簇索引和非聚簇索引的B+树的节点的结构完全一致，只是存储的内容不同，而主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键
  
• 9.辅助索引使用主键作为指针而不是使用地址值作为指针的好处
  • 1.减少当出现行移动或数据页分裂时辅助索引的维护工作
  • 2.使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个指针
  • 3.即行的位置会随着数据库里数据的修改而发生变化，使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响
• 10.注意
  • 1.通过二级索引查询时回表不是必须的过程，当SELECT的所有字段在单个二级索引中都能够找到时就不需要回表，MySQL称此时的二级索引为覆盖索引或触发了索引覆盖
  • 2.可以用Explain命令查看SQL语句的执行计划，执行计划的Extra字段中若出现Using index表示查询触发了索引覆盖

2.数据结构区分

• 1.MySQL索引按数据结构可以分为：
  • 1.B+树索引
  • 2.B-树索引
  • 3.Hash索引

1.B+树索引

• 1.B+树是在B树的基础上做了增强
• 2.B+树中的数据只存储在叶子节点上，并且通过双向链表的方式将所有叶子节点全部关联起来
• 3.B+树的子树数量等于其关键字的数量
• 4.因为AVL树的高度比B树和B+树的高度要高，高度越高就意味着磁盘的IO的次数也就越多
• 5.选择B树或者B+树作为索引结构，主要是为了减少磁盘IO的次数
• 6.B+树由于数据都存储在叶子节点上，并且通过双向链表关联在一起，很容易进行区间遍历或全部遍历

2.B树索引

• 1.B树是一种多路平衡查找树，既可以满足平衡二叉树的规则又可以拥有多个子树
• 2.B树的数据存储在每一个节点上
• 3.B树的子树数量等于关键字数量+1

3.Hash索引

• 1.Hash索引是根据索引列对应的哈希值获取表的数据行
• 2.优点
  • 1.访问速度快
• 3.缺点
  • 1.需要读取表中索引列的值来参与哈希计算，建立哈希索引会耗费更多的时间
  • 2.不能使用Hash索引排序，因为Hash值随机生成无序

4.Full-text索引

3.索引字段特性区分

• 1.MySQL索引按索引的字段特性分为：
  • 1.普通索引
  • 2.唯一索引
  • 3.主键索引
  • 4.前缀索引

1.普通索引

• 1.普通索引是MySQL中最基本的索引类型，建立在普通字段上，其没有任何限制，唯一任务就是加快系统对数据的访问速度
• 2.普通索引允许在定义索引的列中插入重复值和空值

2.唯一索引

• 1.唯一索引建立在 Unique字段上，创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复
• 2.一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值，如果是组合索引，则列值的组合必须唯一

3.主键索引

• 1.主键索引建立主键字段上，一张表最多只有一个主键索引
• 2.主键索引是一种特殊的唯一索引，不允许值重复或值为空

4.前缀索引

• 1.前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引
• 2.前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上
• 3.使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率

4.索引字段个数区分

• 1.MySQL索引按索引字段个数分为：
  • 1.单列索引
  • 2.复合索引

1.单列索引

• 1.单列索引就指索引只包含数据表的一个列，在表中的单个字段上创建索引，单列索引只根据该字段进行索引
• 2.单列索引可以是普通索引，也可以是唯一索引，只要保证该索引只对应一个字段即可

2.复合索引

• 1.组合索引又称为复合索引，组合索引是将数据表的多个列共同组成一个索引
• 2.复合索引是在数据表的多个字段上创建一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询
• 3.复合索引只有查询条件中使用了多个字段中第一个字段并且按照创建时的顺序时，索引才会被使用
• 4.一个组合索引实质上为表的查询提供了多个索引以此来加快查询速度
• 5.复合索引使用时存在最左匹配原则，即按照最左优先的方式进行索引的匹配，如果不遵循最左匹配原则，复合索引会失效
• 6.例：表中创建了一个组合索引(c1，c2，c3)，实际查询中用来实际加速的索引有三个
  • 1.单个索引(c1)
  • 2.双列索引(c1，c2)
  • 3.多列索引(c1，c2，c3)

4.索引操作

1.创建索引

• 1.创建索引是指在某个表的一列或多列上建立一个索引，可以提高对表的访问速度
• 2.MySQL 提供了三种创建索引的方法

1.创建普通索引

CREATE INDEX <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
例：
CREATE INDEX idx_id ON tb_student(id);
// 前缀索引
CREATE INDEX idx_name ON tb_student(name(5));
create index idx_names on t_product(name(7) DESC) 
或
CREATE INDEX idx_name_address ON tb_student(name,address);

• 1.索引名：指定索引名，一般格式为idx_列名，一个表可以创建多个索引，但每个索引在该表中的名称是唯一的
• 2.表名：指定要创建索引的表名
• 3.列名：指定要创建索引的列名，一般将查询语句的 JOIN 子句和 WHERE 子句里经常出现的列作为索引列
• 4.长度
  • 1.指定使用列前的 length 个字符来创建索引，使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间
  • 2.某些情况下只能对列的前缀进行索引，单个索引列的长度有一个最大上限 255 个字节，所有索引最大上限为 1000 个字节，如果索引列的长度超过了这个上限，就只能用列的前缀进行索引
  • 3.另外BLOB 或 TEXT 类型的列也必须使用前缀索引
• 5.ASC|DESC：ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC
• 6.创建表的同时创建普通索引，此时索引名为列名

  CREATE TABLE tb_stu_info
  (
  id INT NOT NULL,
  name CHAR(45) DEFAULT NULL,
  dept_id INT DEFAULT NULL,
  age INT DEFAULT NULL,
  height INT DEFAULT NULL,
  INDEX(height)
  );
  

  

2.创建唯一索引

CREATE UNIQUE INDEX <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
例：
CREATE UNIQUE INDEX idx_id ON tb_student(id);
或
CREATE UNIQUE INDEX idx_name_address ON tb_student(name,address);

• 1.创建唯一索引使用 UNIQUE 关键字，注意其中id字段可以有唯一性约束也可以没有，而复合索引的组合需要唯一
• 2.创建表的同时创建唯一索引，此时索引名为列名

  CREATE TABLE tb_stu_info2
  (
  id INT NOT NULL,
  name CHAR(45) DEFAULT NULL,
  dept_id INT DEFAULT NULL,
  age INT DEFAULT NULL,
  height INT DEFAULT NULL,
  UNIQUE INDEX(height)
  );
  

3.创建主键索引

• 1.创建主键索引通常使用PRIMARY KEY关键字在创建表时创建
• 2.可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键和主键索引
• 3.当主键是由多个列组成的复合索引时，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现主键索引

  CREATE TABLE tb_stu(
  id INT PRIMARY KEY,
  name CHAR(45) DEFAULT NULL,
  dept_id INT DEFAULT NULL,
  age INT DEFAULT NULL,
  height INT DEFAULT NULL
  );
  CREATE TABLE tb_stu_test
  (
  id INT,
  name CHAR(45) DEFAULT NULL,
  dept_id INT DEFAULT NULL,
  age INT,
  height INT,
  PRIMARY KEY(age,height)
  );
  

  
  

2.查看索引

SHOW INDEX FROM <表名>

• 1.MySQL中可以使用SHOW INDEX语句查看表中创建的索引
• 2.表名：指定需要查看索引的数据表名
  

参数	说明
Table	表示创建索引的数据表名
Non_unique	表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0
Key_name	表示索引的名称
Seq_in_index	表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序
Column_name	表示定义索引的列字段
Collation	表示列以何种顺序存储在索引中，MySQL 中升序显示值A（升序），若显示为 NULL则表示无排序
Cardinality	索引中唯一值数目的估计值，基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的，基数越大当进行联合时，MySQL 使用该索引的机会就越大
Sub_part	表示列中被编入索引的字符的数量，若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL
Packed	指示关键字如何被压缩，若没有被压缩，值为 NULL
Null	用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES，若没有，则该列的值为 NO
Index_type	显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
Comment	显示评注

3.修改索引

• 1.MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作

4.删除索引

DROP INDEX <索引名> ON <表名>;
或
ALTER TABLE <表名> DROP INDEX <索引名>;
或
ALTER TABLE tb_stu_test DROP PRIMARY KEY; //删除主键索引

• 1.索引名：要删除的索引名
• 2.表名：指定该索引所在的表名
  
  
• 3.注意：删除的列是索引的组成部分，则删除该列时也会将该列从索引中删除，如果组成索引的所有列都被删除那么整个索引将被删除

5.索引失效的情况

• 1.索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用
• 2.使用索引有几种特殊情况，这些情况下使用带有索引的字段查询时索引会失效
• 3.使用EXPLAIN可以分析查询语句的查询情况

  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '%四'\G
  

1.查询语句中使用LIKE关键字

• 1.查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为通配符%，索引会失效，如果通配符%不在第一个位置，索引就不会失效

2.复合索引没有遵循最左匹配原则

• 1.复合索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段且遵循最左匹配原则，索引才会生效

3.查询语句中使用OR关键字

• 1.查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引，如果 OR 前后有一个条件的列不是索引，那么查询将不使用索引

4.查询语句中存在索引列的数据类型隐式转换

• 1.查询语句中存在索引列的数据类型隐式转换则索引失效
• 2.例：字段类型是字符串，那么一定要在条件中将数据使用引号引用起来，否则会隐式的将数值类型的数据转换为字符串，此时索引失效
  

5.where子句中对索引列进行数学运算

• 1.where子句对索引列进行数学运算时，索引会失效
  

6.where子句中对索引列使用函数

• 1.where子句中对索引列使用函数，索引会失效
  

7.数据量极少的表

• 1.数据量极少的表，MySQL估计使用全表扫描要比使用索引快则不使用索引

6.提升索引的使用效率

• 1.索引的设计可以遵循一些已有的原则，创建索引的时候应尽量考虑符合这些原则，便于提升索引的使用效率

1.选择唯一索引

• 1.唯一索引的值是唯一的，可以更快速的通过该索引来确定某条记录

2.排序、分组和联合操作的字段建立索引

• 1.经常需要 ORDER BY、GROUP BY、DISTINCT 和 JOIN 等操作的字段，排序操作会浪费很多时间，为其建立索引，可以有效地避免排序操作

3.查询条件的字段建立索引

• 1.如果某个字段经常用来做查询条件，则该字段的查询速度会影响整个表的查询速度
• 2.为这样的字段建立索引，可以提高整个表的查询速度

4.限制索引的数目

• 1.索引的数目不是越多越好，每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大
• 2.修改表的内容时，索引必须进行更新有时还可能需要重构，因此索引越多更新表的时间就越长
• 3.如果有一个索引很少利用或从不使用，则会减缓表的修改和插入速度

5.尽量使用数据量少的索引

• 1.如果索引的值很长，那么查询的速度会受到影响
• 2.例：对一个 CHAR(100) 类型的字段进行全文检索需要的时间要比对 CHAR(10) 类型的字段需要的时间要多

6.数据量小的表最好不要使用索引

• 1.由于数据较小全表查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果

7.尽量使用前缀来索引

• 1.如果索引字段的值很长，最好使用值的前缀来索引
• 2.例如TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间，如果只检索字段的前面的若干个字符这样可以提高检索速度

8.删除不再使用或者很少使用的索引

• 1.表中的数据被大量更新或数据的使用方式被改变后，原有的一些索引可能不再需要
• 2.应该定期找出这些索引将其删除，从而减少索引对更新操作的影响

10.事务（Transaction）

• 1.当多个用户访问同一数据时，一个用户在更改数据的过程中可能有其它用户同时发起更改请求，为保证数据的一致性状态MySQL 引入了事务
• 2.数据库的事务是一种机制、一个操作序列，包含了一组数据库操作命令
• 3.事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元
• 4.数据库系统上执行并发操作时，事务是最小的控制单元，适用于多用户同时操作的数据库系统

1.事务四大特性

• 1.事务的四大特性ACID保证了一个事务要么成功提交要么失败回滚，不存在中间状态
  • 1.原子性（Atomicity）
  • 2.一致性（Consistency）
  • 3.隔离性（Isolation）
  • 4.持久性（Durability）
• 2.因此事务的操作具有可恢复性，即当事务失败时数据的修改都会恢复到该事务执行前的状态

1.原子性

• 1.事务是一个完整的操作，事务中的各个操作是不可分的（原子的）
• 2.事务中的所有操作必须作为一个整体提交或回滚，如果事务中的任何元素失败则整个事务将失败，事务不能部分提交
• 3.例：银行转账事务中，如果该事务提交了则这两个账户的数据将会更新，如果由于某种原因事务在成功更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对任何账户余额的修改

2.一致性

• 1.当事务完成时数据必须处于一致状态，即事务开始之前数据库中存储的数据处于一致状态，正在进行的事务中数据可能处于不一致的状态，如数据可能有部分被修改，然而当事务成功完成时，数据必须再次回到已知的一致状态
• 2.例：银行转账事务中，事务开始之前所有账户余额的总额处于一致状态，事务进行的过程中一个账户余额减少了而另一个账户余额尚未修改，因此所有账户余额的总额处于不一致状态，事务完成以后账户余额的总额再次恢复到一致状态

3.隔离性

• 1.对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的而不应以任何方式依赖于或影响其他事务
• 2.修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据或在另一个使用相同数据的事务结束之后访问这些数据
• 3.另外当事务修改数据时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交之后对数据的修改才能生效

4.持久性

• 1.事务的持久性指不管系统是否发生了故障事务处理的结果都是永久的
• 2.一个事务成功完成之后对数据库所作的改变是永久性的，即一旦事务被提交并成功执行，则事务对数据所做的任何变动都会被永久地保留在数据库中

2.执行事务的语法和流程

• 1.MySQL提供了多种存储引擎来支持事务。支持事务的存储引擎有InnoDB，其中InnoDB存储引擎事务主要通过Undo 日志和Redo日志实现，而MyISAM 存储引擎不支持事务
• 2.任何一种数据库都会拥有各种各样的日志，用来记录数据库的运行情况、日常操作、错误信息等
• 3.为了维护 MySQL 服务器，MySQL数据库中进行了日志记录
  • 1.UNDO日志：复制事务执行前的数据，用于在事务发生异常时回滚数据
  • 1.REDO日志：记录事务执行中每条对数据进行更新的操作，当事务提交时，该内容将被刷新到磁盘
• 4.默认设置下每条SQL语句就是一个事务，即执行SQL语句后自动提交
• 5.而为了达到将多个操作做为一个整体的目的需要使用BEGIN或START TRANSACTION开启一个事务或禁止当前会话的自动提交

1.开始事务

BEGIN;
//或
START TRANSACTION;

• 1.BEGIN：显式地标记一个事务的起始点

2. 提交事务

COMMIT;

• 1.COMMIT：表示提交事务，即提交事务中的所有操作，将事务中所有对数据库的更新都写到磁盘上的物理数据库中
• 2.提交事务意味着将事务开始以来所执行的所有数据都修改成为数据库的永久部分，因此也标志着一个事务的结束，一旦执行了该命令将不能回滚事务，只有在所有修改都准备好提交给数据库时才执行这一操作

3.回滚（撤销）事务

ROLLBACK;

• 1.ROLLBACK：表示撤销事务，即事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态，这里的操作指对数据库的更新操作
• 2.当事务执行过程中遇到错误时，使用 ROLLBACK 语句使事务回滚到起点或指定的保持点处，同时系统将清除自事务起点或到某个保存点所做的所有的数据修改，并且释放由事务控制的资源因此这条语句也标志着事务的结束

4.事务总结

• 1.BEGIN或START TRANSACTION语句后面的 SQL 语句对数据库数据的更新操作都将记录在事务日志中，直至遇到ROLLBACK语句或COMMIT语句
  • 1.如果事务中某一操作失败且执行了ROLLBACK语句，那么在开启事务语句之后所有更新的数据都能回滚到事务开始前的状态
  • 2.如果事务中的所有操作都全部正确完成且使用了COMMIT语句向数据库提交更新数据，则此时的数据处在新的一致状态
• 2.MySQL事务是一项非常消耗资源的功能，使用过程中要注意以下几点
  • 1.事务尽可能简短
    • 1.事务的开启到结束会在数据库管理系统中保留大量资源，以保证事务的原子性、一致性、隔离性和持久性
    • 2.如果在多用户系统中，较大的事务将会占用系统的大量资源，使得系统不堪重负，会影响软件的运行性能，甚至导致系统崩溃
  • 2.事务中访问的数据量尽量最少
    • 1.当并发执行事务处理时，事务操作的数据量越少，事务之间对相同数据的操作就越少
  • 3.查询数据时不使用事务
    • 1.对数据进行查询操作并不会更新数据库的数据，因此不使用事务查询数据避免占用过量的系统资源，只有出现更新数据库的情况才会使用事务
  • 4.事务处理过程中不要出现等待用户输入的操作
    • 1.处理事务的过程中，如果需要等待用户输入数据那么事务会长时间地占用资源，有可能造成系统阻塞

3.设置事务自动提交（开启和关闭）

• 1.MySQL默认开启事务自动提交模式，即除非显式的开启事务（BEGIN 或 START TRANSACTION），否则每条SOL语句都会被当做一个单独的事务自动执行，但某些特定情况下需要关闭事务自动提交来保证数据的一致性
• 2.MySQL中可以通过 SHOW VARIABLES语句查看当前事务自动提交模式状态
  
• 3.上图结果显示autocommit 的值是 ON表示系统开启自动提交模式
• 4.MySQL中可以使用 SET autocommit 语句设置事务的自动提交模式

  SET autocommit = 0|1|ON|OFF;
  

  • 1.0|OFF：关闭事务自动提交，如果关闭自动提交用户将会一直处于某个事务中只有提交或回滚后才会结束当前事务重新开始一个新事务
  • 2.1|ON：开启事务自动提交，如果开启自动提交则每执行一条SQL语句事务都会提交一次，除非显示开启事务
• 5.注意：
  • 1.关闭自动提交功能后，只有当执行 COMMIT 命令后MySQL 才将数据表中的修改提交到数据库中
  • 2.如果执行ROLLBACK命令数据将会被回滚，如果不提交事务而终止 MySQL会话数据库将会自动执行回滚操作
  • 3.使用BEGIN或START TRANSACTION开启一个事务之后，自动提交将保持禁用状态，直到使用COMMIT或ROLLBACK结束事务之后，自动提交模式会恢复到之前的状态

4.事务的问题

• 1.由于并发访问会导致的数据读取事务出现一系列问题

1.脏读

• 1.脏读：指读取其他事务未提交数据
• 2.A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据

2.幻读

• 1.幻读：指当前事务需前后多次读取数据，由于其他事务的操作导致当前事务前后读取的数据总量不一致
• 2.A事务执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时B事务执行新增数据的操作并提交，然后A事务再次读取的数据总量和之前统计的不一样，平白无故的多了几条数据就像产生了幻觉，称为幻读

3.不可重复读

• 1.不可重复读：当前事务前后多次读取同一个数据，由于其他事务操作导致前后读取的数据内容不一致
• 2.A事务执行读取操作时由于整个A事务比较大，前后读取同一条数据需要经历很长的时间 ，而A事务第一次读取数据后，B事务执行更改操作，此时A事务第二次读取时，发现和之前的数据不一样，系统不可以读取到重复的数据，称为不可重复读

4.事务的事务隔离级别

• 1.数据库操作中为了有效保证并发读取数据的正确性，提出了事务的隔离级别，MySQL中有四种隔离级别，隔离级别由低到高分别
  • 1.读未提交（Read uncommitted）
  • 2.读已提交（Read committed）
  • 3.可重复读（Repeatable read）
  • 4.可串行化（Serializable）

隔离级别	脏读	不可重复读	幻读
Read uncommitte	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

• 2.MySQL中事务的默认隔离级别是可重复读(Repeatable read) 隔离级别，即事务未结束时(未执行Commit或 Rollback)，其它会话只能读取到未提交数据

1.读未提交（Read uncommitted）

• 1.该隔离级别下，所有事务能够读取其他事务未提交的数据
• 2.读取其他事务未提交的数据会造成脏读，因此在该隔离级别下不能解决脏读、不可重复读和幻读

2.读已提交（Read committed）

• 1.该隔离级别下，所有事务只能读取其他事务已经提交的内容
• 2.该隔离级别能够彻底解决脏读的现象，但在该隔离级别下不能解决不可重复读和幻读
• 3.这是大多数数据库系统的默认事务隔离级别（Oracle、SQL Server），但不是MySQL默认的隔离级别

3.可重复读（Repeatable read）

• 1.该隔离级别下，所有事务前后多次的读取到的数据内容是不变的
• 1.该隔离级别中某个事务在执行的过程中，不允许其他事务进行update操作，但允许其他事务进行add操作，造成某个事务前后多次读取到的数据总量不一致的现象从而产生幻读，因此在该隔离级别下不能解决幻读
• 3.可重复读是MySQL的默认事务隔离级，注意MySQL的可重复读的隔离级别上使用间隙锁的方式已经解决了幻读的问题

4.可串行化（Serializable）

• 1.该隔离级别下，所有的事务顺序执行
• 2.所以事务之间不存在冲突，从而能有效地解决脏读、不可重复读和幻读的现象
• 3.但是安全和效率不能兼得，该事务隔离级别会导致大量的操作超时和锁竞争从而大大降低数据库的性能，一般不使用该事务隔离级别

11.MySQL锁机制

1.定义

• 1.计算机协调多个进程或线程并发访问某一资源的机制
• 2.数据库中除传统计算资源（CPU、RAM、I/O等）的争抢，数据也是一种供多用户共享的资源，而锁机制正是用来保证共享数据并发访问的一致性，有效性

2.优缺点

1.优点

• 1.锁机制可以解决共享数据并发访问的一致性，有效性问题

2.缺点

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

3.类型

• 1.数据库中锁的类型分为
  • 1.表锁
  • 2.行锁
  • 3.页锁

1.表锁

• 1.MyISAM存储引擎默认锁是表锁
• 2.MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎的原因，因为写锁以后其它线程不能做任何操作，大量的更新使查询很难得到锁从而造成永远阻塞

1.种类

1.读锁（read lock）

• 1.读锁也叫共享锁（shared lock）
• 2.针对同一份数据，多个读操作(select)可以同时进行而不会互相影响
• 3.读锁会阻塞写操作，不会阻塞读操作

2.写锁（write lock）

• 1.写锁也叫排他锁（exclusive lock）
• 2.当前操作没完成之前，会阻塞其它读和写操作（update、insert、delete）
• 3.写锁会阻塞读和写操作

2.特点

• 1.对整张表加锁
• 2.开销小
• 3.加锁快
• 4.无死锁
• 5.锁粒度大，发生锁冲突概率大，并发性低

2.行锁

• 1.InnoDB存储引擎默认锁是行锁
• 2.只有通过索引条件检索数据时，InnoDB才会使用行级锁，否则会使用表级锁(索引失效，行锁变表锁)
• 3.即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
• 4.如果数据表建有多个索引时，可以通过不同的索引锁定不同的行

1.种类

1.读锁（read lock）

• 1.读锁也叫共享锁（shared lock）
• 2.允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁

2.写锁（write lock）

• 1.写锁也叫排他锁（exclusive lock）
• 2.允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁

3.意向共享锁（IS）

• 1.一个事务给一个数据行加共享锁时，必须先获得表的IS锁

4.意向排它锁（IX）

• 1.一个事务给一个数据行加排他锁时，必须先获得该表的IX锁

2.特点

• 1.对一行数据加锁
• 2.开销大
• 3.加锁慢
• 4.会出现死锁
• 5.锁粒度小，发生锁冲突概率最低，并发性高

3.页锁

• 1.开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般

4.锁操作

1.加锁

1.表锁

• 1.隐式上锁（默认自动加锁自动释放）

  select //上读锁
  insert、update、delete //上写锁
  

• 2.显式上锁（手动）

  lock table tableName read;//读锁
  lock table tableName write;//写锁
  

2.行锁

• 1.隐式上锁（默认自动加锁自动释放）

  select //不会上锁
  insert、update、delete //上写锁
  

• 2.显式上锁（手动）

  select * from tableName lock in share mode;//读锁
  select * from tableName for update;//写锁
  

2.解锁

1.表锁

• 1.解锁（手动）

  unlock tables;//所有锁表
  

  lock table teacher read;// 上读锁
  select * from teacher; // 可以正常读取	select * from teacher;// 可以正常读取
  update teacher set name = 3 where id =2;// 报错因被上读锁不能写操作	>	update teacher set name = 3 where id =2;// 被阻塞
  unlock tables;// 解锁
  update teacher set name = 3 where id =2;// 更新操作成功
  
  lock table teacher write;// 上写锁
  select * from teacher; // 可以正常读取	select * from teacher;// 被阻塞
  update teacher set name = 3 where id =2;// 可以正常更新操作	update teacher set name = 4 where id =2;// 被阻塞
  unlock tables;// 解锁
  select * from teacher;// 读取成功
  update teacher set name = 4 where id =2;// 更新操作成功
  

2.行锁

• 1.解锁（手动）
  • 1.提交事务（commit）
  • 2.回滚事务（rollback）
  • 3.kill 阻塞进程

3.排查锁

1.表锁

• 1.查看表锁情况

  show open tables;
  

• 2.表锁分析

  show status like 'table%';
  

• 3.table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况

• 4.table_locks_immediate：产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1

2.行锁

• 1.行锁分析

  show status like 'innodb_row_lock%';
  

• 1.innodb_row_lock_current_waits：当前正在等待锁定的数量
• 2.innodb_row_lock_time：从系统启动到现在锁定总时间长度
• 3.innodb_row_lock_time_avg：每次等待所花平均时间
• 4.innodb_row_lock_time_max ：从系统启动到现在等待最长的一次所花时间
• 5.innodb_row_lock_waits：系统启动后到现在总共等待的次数

5.MVCC机制

• 1.上了写锁别的事务还可以读操作的原因：因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读而不会被阻塞

6.死锁

• 1.指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

1.产生条件

• 1.互斥条件：一个资源每次只能被一个进程使用
• 2.请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放
• 3.不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺
• 4.循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系

2.如何避免

• 1.加锁顺序一致，尽可能一次性锁定所需的数据行
• 2.尽量基于primary（主键）或unique key更新数据
• 3.单次操作数据量不宜过多，涉及表尽量少
• 4.减少表上索引，减少锁定资源
• 5.尽量使用较低的隔离级别
• 6.尽量使用相同条件访问数据，这样可以避免间隙锁对并发的插入影响
• 7.精心设计索引，尽量使用索引访问数据

3.解决

• 1.查看死锁：show engine innodb status \G
• 2.自动检测机制，超时自动回滚代价较小的事务（innodb_lock_wait_timeout 默认50s）
• 3.手动kill阻塞进程（show processlist）

7.行锁的实现算法

1.Record Lock 锁

• 1.单个行记录上的锁
• 2.Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表建立的时候没有设置任何一个索引，这时InnoDB存储引擎会使用隐式的主键来进行锁定

2.Gap Lock 锁

• 1.当用范围条件而不是相等条件检索数据并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引加锁
• 2.间隙锁解决了事务并发的幻读问题，但因为查询执行过程中通过范围查找会锁定争个范围内所有的索引键值，即使这个键值并不存在
• 3.间隙锁有一个致命的弱点就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成锁定的时候无法插入锁定键值范围内任何数据

3.Next-key Lock 锁

• 1.同时锁住数据+间隙锁
• 2.Repeatable Read隔离级别下，Next-key Lock 算法是默认的行记录锁定算法

8.锁优化

• 1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 2.合理设计索引，尽量缩小锁的范围
• 3.尽可能较少检索条件，避免间隙锁
• 4.尽量控制事务大小，减少锁定资源量和时间长度
• 5.尽可能低级别事务隔离

9.乐观锁与悲观锁

1.悲观锁

• 1.假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
• 2.实现机制：表锁、行锁等
• 3.实现层面：数据库本身
• 4.适用场景：并发量大

2.乐观锁

• 1.假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性
• 2.实现机制：提交更新时检查版本号或者时间戳是否符合
• 3.实现层面：业务代码
• 4.适用场景：并发量小

12.主从复制

1.简介

• 1.定义：创建多台和主数据库完全一样的数据库环境(从数据库)，对主数据库的写操作(增，删，改)会自动同步到从数据库中
  

2.作用

• 1.从数据库可作为冷备机，进行日常备份，确保数据安全
• 2.从数据库可作为热备机，一旦主机宕机，可切换到从数据库提供服务，提高系统的并发性
• 3.主从复制是读写分离的基础，通过主从复制可以实现数据库的读写分离，提高系统的并发性

3.原理

• 1.基本原理
  • slave会从master读取binlog来进行数据同步
• 2.具体步骤
  • 1.master执行写操作(增删改)后，会将数据改变记录写入到二进制日志中(binlog.00000x)
  • 2.当slave上执行 start slave 命令之后，slave会创建一个 IO 线程用来连接master，请求master中的binlog文件
  • 3.当slave连接master时，master会创建一个 log dump 线程，用于发送 binlog 的内容（该线程读取 binlog 内容时，会对mster上的 binlog 加锁，当读取完成并发送给slave后解锁）
  • 4.IO 线程接收master的 log dump 进程发送的更新记录之后将其保存到中继日志（relay log） 中
  • 5.slave的SQL线程，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致

4.搭建步骤

1.克隆虚拟机

• 1.克隆3台(至少两台)安装过MySQL的虚拟机
• 2.克隆过程可参考Linux操作命令中的克隆
• 3.安装过程可参考Linux软件安装中的Linux安装MySQL

2.配置主机

• 1.选择一台服务器上的MySQL作为主机

1.主机的配置文件

• 1.MySQL配置文件
  • 1.Windows：my.ini/my-default.ini，位于MySQL的安装根目录下
    
  • 2.Linux：my.cnf，位于/etc/my.cnf
    
    
• 2.主机中配置server-id，其他配置可不做修改，使用默认值；也可根据需求改变
• 3.改变后需要重新启动生效：systemctl restart mysqld
• 4.二进制日志文件可以通过show master status中File查看，一般携带数字后缀
• 5.错误日志可以通过上述log-error指定的文件查看

1.日志格式

• 1.指配置文件中的binlog_format设置，其有三种日志格式
  • 1.binlog_format=STATEMENT
    • 1.设置日志记录主机的写指令，此时binlog日志文件显示的具体的增删改sql语句
    • 2.指令性能高，但是now()之类的函数以及获取系统变量的操作会出现主从数据不同步的问题
  • 2.binlog_format=ROW
    • 1.默认设置，设置日志记录主机写后的具体数据
    • 2.批量操作时性能差，可以解决now()之类的函数以及获取系统变量的操作会出现主从数据不同步的问题
  • 3.binlog_format=MIXED：
    • 1.设置为上述两种的混合使用，有函数用ROW，没函数用STATEMENT
    • 2.无法识别系统变量
• 2.目前使用最多的是第二种格式，其日志文件无法显示完整的sql语句，显示的是具体的数据，第一种格式日志可以查看完整sql语句

2.日志记录优先级

• 1.指配置文件中的binlog-do-db和binlog-ignore-db的优先级
  
• 2.日志写入步骤
  • 1.主机系统运行时，首先会判断以上两个参数是否设置
  • 2.如果都没有设置，所有数据库(包括自定义和系统定义)的写操作都会被记录到日志中
  • 3.如果有设置，会判断当前系统中是否有数据库，如果没有数据库则不写日志
  • 4.如果当前系统有数据库，会优先判断是否有binlog-do-db设置
  • 5.如果有则查询系统中的数据库是否匹配，如果匹配的则写日志，如果不匹配的则不写日志
  • 6.如果没有binlog-do-db设置则判断是否存在binlog-ingore-db设置
  • 7.如果存在该设置则查看当前系统是否匹配，如果匹配则不写日志，如果不匹配则写入日志

2.主机中创建slave用户

# 1.创建slave1用户并设置密码
create user 'slave1' @'%' identified by 'root';
# 3.赋予从机复制权限
grant replication slave on *.* to 'slave1' @'%';
# 4.刷新权限 
flush privileges;

# 1.创建slave2用户并设置密码
create user 'slave2' @'%' identified by 'root';
# 2.赋予slave复制权限
grant replication slave on *.* to 'slave2' @'%';
# 4.刷新权限 
flush privileges;

• 1.从机用户也可不创建，直接使用root用户
• 2.创建成功后可以查看mysql数据库中的user表查看具体信息
  

3.查询主机master状态

show master status;

• 1.执行完此步骤后，不再操作主服务器MySQL，防止主服务器状态值变化
• 2.记录File和Position的值，其中File记录的是日志文件的具体名称，Position记录的是当前日志记录位置
• 3.启动主从复制时，从机向主机中具体读取biglog日志，此时会从当前指定的文件以及指定的位置读取，后续会对从机进行配置

3.配置从机slave

• 1.选择指定服务器上的MySQL作为从机

1.从机的配置文件

• 1.添加从机唯一server-id
• 2.默认中继日志一般为xxx-relay-bin.00000x，可不配置

2.从机中配置主从关系

• 1.设置主机master的ip地址，主机上创建的允许从机复制的账号和密码，端口号，主机biglog文件名称和当前记录位置

  CHANGE MASTER TO MASTER_HOST='169.254.73.100',MASTER_USER='slave1',MASTER_PASSWORD='root', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=1332; 
  

• 2.如果主机中没有设置其他账号，可以直接使用root账号连接

  # 配置主机信息（change master to master_host='主机ip地址',master_user='主机用户名',master_password='主机密码',master_log_file='主机日志文件名',master_log_pos=日志文件的位置;）
  change master to master_host='192.168.73.100',master_user='root',master_password='root',master_log_file='binlog.000001',master_log_pos=1332;    
  

3.启动主从复制

• 1.从机执行以下命令

  start slave;
  

4.查看启动状态

• 1.从机执行以下命令

  show slave status\G  # \G是自动换行
  

  
  
  
• 1.Slave_IO_Running和Slave_SQL_Running都是yes说明io线程和sql线程都是正常启动的
• 2.通过Last_IO_Error和Last_SQL_Error查看错误情况

5.停止和重置

• 1.从机上执行，停止I/O 线程和SQL线程的操作

  stop slave; 
  

• 2.从机上执行，删除从机的relaylog(xxx-relay-bin.00000x)日志文件，并重新启用新的relaylog文件

  reset slave;
  

• 3.主机上执行，删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件，用于第一次进行搭建主从库时，进行主库binlog初始化工作；

  reset master;
  

4.测试

• 1.主机中手动添加一条信息，从机中生成同样一条
  

5.常见问题

1.不符合密码策略

• 1.问题原因
  • MySQL初始化时，使用临时密码，修改自定义密码时，由于自定义密码比较简单，出现不符合密码策略的问题
  • 2.注意创建用户时需要同时设置密码，否则也会报上述异常
• 2.解决方式
  • 1.进入MySQL：mysql -u账号 -p密码，注意中间无空格
    
  • 2.查看MySQL初始的密码策略：show variables like ‘validate_password%’;
    
  • 3.首先设置密码验证强度等级，设置 validate_password_policy 的全局参数为 LOW：set global validate_password_policy=LOW;
    
  • 4.然后设置全局密码长度：set global validate_password_length=4;
    
  • 5.创建账户并设置密码：create user 'slave1' @'%'identified by 'root';

2.数据不一致

• 1.问题原因
  • 主机中的t_user表添加了一条记录后从机才开始主从复制，然后主机删除该记录，由于从机中不含该条记录导致SQL执行异常
• 2.解决方式
  • 1.主机中查询binlog文件的位置
    
  • 2.主机中查询mysqlbinlog程序的位置
    
  • 3.通过mysqlbinlog查看binlog，否则会出现乱码
    
  • 4.根据上述图片所示找到出错的语句，然后解决问题，具体问题具体分析
    
    • 1.如果是删除，修改出错，则在从库加入该条记录
    • 2.如果是添加出错，则在从库中删除重复的记录
  • 5.恢复同步
    • 1.出现问题的从机停止主从复制
    • 2.跳过该条出问题的SQL语句，数字可根据实际情况改变
    • 3.重新启动从机的主从复制
      

5.主从不同步

1.产生原因

• 1.网络延迟
  • 主从复制是基于binlog的异步复制，通过网络传送时可能会出现延迟从而导致主从不同步
• 2.负载过高
  • 当某一台机器上的负载过高，导致io或sql线程资源不足，可能会导致主从不同步
• 3.自增键不一致
  • 自增键的开始键值和自增步长设置不一致导致主从不同步
• 4.版本不一致
  • 高版本的主库支持功能但是低版本的从库不支持该功能导致主从不同步
• 5.设置不一致
  • 主从库的设置不一致，导致一些sql在主库能执行但是在从库无法执行导致的主从不一致

2.解决方式

• 1.保证主从库的设置，自增键，版本一致
• 2.忽略错误，继续同步
  • 1.适用于主从数据相差不大，或者要求数据可以不完全统一的情况
  • 2.步骤等同于上述恢复同步
• 3.重新主从，完全同步
  • 1.适用于主从数据相差较大，或者要求数据完全统一的情况
  • 2.具体步骤
    • 1.先进入主库，进行锁表，防止数据写入

      # 锁定为只读状态,关闭会话或者执行unlock tables;释放锁
      flush tables with read lock;
      

      
      
    • 2.进行数据备份，把主库数据备份到mysql.bak.sql文件

      mysqldump -x -uroot -proot energy > mysql.bak.sql # -x 提交请求锁定所有数据库中的所有表，以保证数据的一致性
      

      
      
      
    • 3.查看master 状态，记录当前数据

      show master status;
      

      
    • 4.将主机备份文件传到从机，进行数据恢复

      scp mysql.bak.sql root@169.254.73.102:/tmp/
      

      
      
    • 5.停止从机的主从复制

      stop slave;
      

      
    • 6.从库执行mysql命令，导入数据备份（注意：首先需要创建数据库，然后选定，再导入）

      create database energy;
      use energy;
      source /tmp/mysql.bak.sql;
      

      
    • 7.设置从库同步，注意该处的同步点

      CHANGE MASTER TO MASTER_HOST='169.254.73.100',MASTER_USER='slave1',MASTER_PASSWORD='root', MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000001',MASTER_LOG_POS=3649; 
      

      
    • 8.重新开启从机同步

      start slave;
      

      
    • 5.查看同步状态

      show slave status\G
      

      

11.读写分离

• 1.对数据库的读和写都在同一个数据库服务器中，无法满足安全性，高可用性和高并发性等实际需求
• 2.因此通过主从复制来同步数据，再通过读写分离提升数据库的并发负载能力
• 3.操作：主数据库(master)处理写操作(增删改)，从数据库(slave)处理读操作(查询)
• 4.作用：提高数据的安全性，分摊读写压力，提高性能
• 5.工具：ShardingSphere，MyCat
• 6.具体操作步骤可参考ShardingSphere和MyCat文章