信创：信息技术应用创新
硬件：
软件：
操作系统：Windows系列、麒麟、欧拉
手机操作系统：Android、鸿蒙
数据库：MySQL、Oracle、SQ L Server、高斯、达梦
数据库基础
一、数据库的相关概念
1）什么是数据库
数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。
关系型数据库–用二维表来表示数据
数据库文件：
关系型数据库：二维表存储数据
记录：代表的一行数据
列：字段
2）为什么使用数据库
将数据存储在文件中，但是在文件中读写数据速度相对较慢。
3）关系型数据库
所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS即关系数据库管理系统(Relational Database Management System)特征：
1.数据以表格的形式出现
2.每行为各种记录名称
3.每列为记录名称所对应的数据域
4.许多的行和列组成一张表单
5.若干的表单组成database
4）RDBMS术语
数据库： 数据库是一些关联表的集合。
数据表： 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列： 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性：参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
二、连接MySQL
（一）通过MySQL自带客户端窗口
找到MySQL Sercer文件夹中的 MySQL8.0 Command Line Client 打开 就有命令行窗口
（二）使用mysql 命令
MySQL安装目录中的bin目录需要配置到环境变量path中
-h可以省略，默认连接本机
-u 用户名
-p 密码
（三）使用图形化工具
三、数据库操作
1、创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name [CHARACTER SET charset_name] [COLLATE collation_name];
2、删除数据库
DROP DATABASE <database_name>; -- 直接删除数据库，不检查是否存在 或 DROP DATABASE [IF EXISTS] <database_name>;
3、选择数据库
USE database_name;
四、MySQL数据类型
MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
一）数值类型
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16777215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4294967295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18446744073 709551615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175494351E-38，3.402823466E+38) | 单精度 浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.2250738585072014E-308，1.7976931348623157E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | Decimal属于定点类型，是 NUMERIC 的实现。它是一种精确值，用于存储精确的数字数据值，例如货币数据。MySQL 以二进制格式存储 Decimal 值。 |
二）日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
| 类型 | 大小 bytes | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 |
| TIMESTAMP | 4 | ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |
DATE_FORMAT() 函数：用于以不同的格式显示日期/时间数据
语法：DATE_FORMAT(date,format)
date 参数是合法的日期。format 规定日期/时间的输出格式。
format中的特殊字符含义
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值（00-12） |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间, 24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与 %X 使用 |
| %v | 周（01-53）星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天（0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
三）字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
注意：
char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
枚举与集合类型（Enumeration and Set Types）
ENUM： 枚举类型，用于存储单一值，可以选择一个预定义的集合。
SET：集合类型，用于存储多个值，可以选择多个预定义的集合。
五、创建、删除表
一）创建表
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );
table_name创建的表的名称。
column1, column2, ... 是表中的列名。
datatype 是每个列的数据类型。
举例：
CREATE TABLE IF NOT EXISTS `book `(
`id` INT UNSIGNED AUTO_INCREMENT,
` title` VARCHAR(100) NOT NULL,
`author` VARCHAR(40) NOT NULL,
`submission_date` DATE,
PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
ENGINE 设置存储引擎，CHARSET 设置编码。
六、数据库函数
一）数值相关
取近似值
ceil (n) //向上取整
floor (n) //向下取整
round(n) //四舍五入
round( n，m ) //其值保留到小数点后面的m位，.若m为负，则保留n值到小数点左边m位，返回值是整型或浮点型
truncate( n,m ) //返回值是整型或浮点型
//返回被舍去至小数点后m位的数字n，
//若m的值为0，则结果为整数若m的值为负数，则截去n小数点左起第m位开始后面所有低位的值
案例
select round(24.4)//24
select floor(24.7)//24
select ceil(24.1)//25
select round(28.55,1)//28.6
select round(28.55,0)//29
select round(28.55,-1)//30
select truncate(28.55,1)//28.5
select truncate(28.55,0)//28
select truncate(28.55,-1)//20
————————————————
版权声明：本文为CSDN博主「这是一条海鱼」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_41071876/article/details/106508240
取模、绝对值、随机数
mod（n,m） 取模函数，同n%m
abs (n）n的绝对值
rand（） 0~1内的随机数
案例
select mod(22,10) //2
select abs(-1) //1
select rand() //0.17553753794686638
二）字符文本相关
拼接字符串
concat（str1,str2,...sn）//返回结果为连接参数产生的字符串，如果任何一个参数为NULL，则返回值为NULL
concat_ws （x , s1 , s2 ,....）//第一个参数 x 是其他参数的分隔符，分隔符可以是一个字符串，也可以是其他参数
repeat( str,x ) //返回str重复x次的结果
案例
select repeat('abc',3) //abcabcabc
截取字符串
left（str,x） //返回字符串str最左边的x个字符
right（str,x） //返回字符串str最右边的x个字符
substring（str，x,y） //返回从字符串str x位置起y个字符长度的字符
//如果n是负数。则字符串的截取起始位置从字符串结尾开始
案例
select right('12345',3)//345
select left('12345',3)//123
select substring('123456',2,3)//234
select substring('123456','-1',3);//6
字符串的比较
strcmp(s1,s2) //比较字符串s1和s2的ASCII，如果s1小返回-1，如果s1大返回1，如果两这相等，返回0
案例
select strcmp('1234','1234567')//-1
字符串的替换
insert(str ,x,y,instr) //将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
replace（str,a,b） 用字符串b替换字符串str中所有出现的字符串a
案例
select insert('1234567',4,1,'wo');//123wo567
select replace('oldstring','old','new');//newstring
字符串的填充
lpad( str,n,pad ) //用字符串pad对str最左边进行填充，直到长度为n个字符串长度
rpad( str,n,pad ) //用字符串pad对str最右边进行填充，直到长度为n个字符串长度
select lpad('temp',10,'ad')//adadadtemp
select rpad('hello',10,'wg');//hellowgwgw
空格去除
ltrim( str ) //去掉字符串str左侧的空格
rtrim( str ) //去掉字符串str右侧的空格
trim（str） //去掉字符串str两侧空格
字符串的长度
length( str ) //返回字符串str的字符长度，包括前后空格
案例
select length(' ab ')//5
大小写转换
lower（str） //将字符串str中所有字符变为小写
upper(str ) //将字符串str中所有字符变为大写
select lower('HELLO') //hello
select upper('world') //WORLD
LIKE() 模式匹配
expr LIKE pat [ESCAPE ‘escape-char’]
模式匹配，使用SQL简单正规表达式比较。返回1 (TRUE) 或 0 (FALSE)。 若 expr 或 pat 中任何一个为 NULL,则结果为 NULL。
| 字符 | 说明 |
|---|---|
| % | 匹配任何数目的字符，甚至包括零字符 |
| _ | 只能匹配一种字符 |
三）流程控制函数
IFNULL(expr1,expr2)：假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。
IF(expr,v1,v2)：如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
SELECT IF(1>0,'yes','no');
NULLIF(expr1,expr2)：如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。
CASE WHEN语句
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
四）加密函数
可逆转
encode()//加密，使用key作为密钥加密字符串str，结果是一个二进制字符串，它以BLOB类型存储
decode() //解密。使用key作为密钥解密加密字符串str
案例
SELECT ENCODE('123456','key') //�mL�l�
SELECT DECODE(ENCODE('123456','key'),'key') //123456
不可逆转加密
//加密字符串，加密后的值以32位16进制数字的二进制字符串形式返回，
//若参数为NULL，则返回NULL
MD5()
//创建一个经过加密的密码字符串，适合于插入到MySQL的安全系统。
//该加密过程不可逆，主要用于MySQL的认证系统。
//若参数为NULL，则返回NULL
PASSWORD（）
案例
SELECT MD5('123456')//e10adc3949ba59abbe56e057f20f883e
SELECT MD5('123456') = MD5('123456') //1
SELECT PASSWORD(1234) = PASSWORD(1234) //1
SELECT PASSWORD(1234) //*A4B6157319038724E3560894F7F932C8886EBFCF
五）聚合函数
聚合函数会把每组查询值 聚合成一行
MAX（）//返回某列的最大值
MIN（） //返回某列的最小值
AVG() //返回某列的平均值
SUM（） //返回某列值的和
COUNT() //返回某列的行数
六）日期时间函数
返回当前时间
NOW()/SYSDATE()：当前日期和时间
CURDATE()/CURRENT_DATE()：当前日期
//将当前日期按照 YYYY-MM-DD或 YYYYMMDD格式的值返回
//具体格式根据函数在字符串或者数字语境中而定
curdate()
current_date()
//将当前时间以 HH:MM:SS或者HHMMSS的格式返回
//具体格式根据函数在字符串或者数字语境中而定
curtime()
current_time()
now()//返回的是整条语句执行时的时间
sysdate()//返回的是sysdate()函数执行时的时间
//返回格式为YYYY-MM-DD HH:MM:SS
//或者YYYYMMDDHHMMSS具体格式根据函数在字符串或者数字语境中而定
案例1
select curdate() //2018-11-25
select current_date(); //2018-11-25
select curtime()//00:02:32
select current_time() //00:02:32
案例2
mysql> select now(), sleep(2),sysdate();
+---------------------+----------+---------------------+
| now() | sleep(2) | sysdate() |
+---------------------+----------+---------------------+
| 2018-11-24 23:51:05 | 0 | 2018-11-24 23:51:07 |
+---------------------+----------+---------------------+
mysql> select sysdate(),sleep(2),now();
+---------------------+----------+---------------------+
| sysdate() | sleep(2) | now() |
+---------------------+----------+---------------------+
| 2018-11-24 23:52:14 | 0 | 2018-11-24 23:52:14 |
+---------------------+----------+---------------------+
WEEK(DATE)：获取指定日期为一年中的第几周
YEAR(DATE)：获取年份
MONTH(DATE)：获取月份
DAY(DATE)：获取天
HOUR(TIME)：获取小时
MINUTE(TIME)：获取分钟
SECOND(TIME)：获取秒
DATE_ADD(date, INTERVAL expr unit)：用于在给定的日期上增加或减少特定的时间间隔
date是要进行计算的日期，expr是一个整数，表示要增加或减少的时间数量，unit是时间单位。
| 时间单位 | 描述 |
|---|---|
| MICROSECOND | 微秒 |
| SECOND | 秒 |
| MINUTE | 分钟 |
| HOUR | 小时 |
| DAY | 天 |
| WEEK | 周 |
| MONTH | 月 |
| QUARTER | 季度 |
| YEAR | 年 |
DATEDIFF(date1,date2）：返回两个日期之间的天数（date1-date2）
DATE_FORMAT(date,format)：日期格式化
date 参数是合法的日期。format 规定日期/时间的输出格式。
format中的特殊字符含义
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值（00-12） |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
日期格式化
date_fromat(date,format) //根据format指定的格式显示date值
SELECT DATE_FORMAT('2018-9-4','%Y-%m'); #2018-09
%b : 月份的缩写名称（ Jan...Dec）
SELECT DATE_FORMAT('2018-9-26','%b'); #Sep
%c : 月份的数字形式（1，2，..12）
SELECT DATE_FORMAT('2018-9-26','%c'); #9
%m : 月份的数字形式（01，02，..12）
SELECT DATE_FORMAT('2018-9-26','%m'); # 09
%M : 月份英语名称
SELECT DATE_FORMAT('2018-9-26','%M'); #September
%d : 该月日期，数字形式(01,02,03,04，.22... )
SELECT DATE_FORMAT('2018-9-4','%d'); #04
%e : 该月日期，数字形式(1,2,3,4，..22.. )
SELECT DATE_FORMAT('2018-9-4','%e'); #4
%Y : 4位数形式表示年份
SELECT DATE_FORMAT('2018-9-4','%Y'); #2018
%y : 2位数形式表示年份
SELECT DATE_FORMAT('2018-9-4','%y'); #18
日期格式的总结
| 时间单位 | 描述 |
|---|---|
| MICROSECOND | 微秒 |
| SECOND | 秒 |
| MINUTE | 分钟 |
| HOUR | 小时 |
| DAY | 天 |
| WEEK | 周 |
| MONTH | 月 |
| QUARTER | 季度 |
| YEAR | 年 |
| 格式 | 描述 |
|---|---|
| %a | 缩写星期名 |
| %b | 缩写月名 |
| %c | 月，数值（00-12） |
| %D | 带有英文前缀的月中的天 |
| %d | 月的天，数值（00-31） |
| %e | 月的天，数值（0-31） |
| %f | 微秒 |
| %H | 小时（00-23） |
| %h | 小时（01-12） |
| %I | 小时（01-12） |
| %i | 分钟，数值（00-59） |
| %j | 年的天（001-366） |
| %k | 小时（0-23） |
| %l | 小时（1-12） |
| %M | 月名 |
| %m | 月，数值（00-12） |
| %p | AM 或 PM |
| %r | 时间，12-小时（hh:mm:ss AM 或 PM） |
| %S | 秒（00-59） |
| %s | 秒（00-59） |
| %T | 时间, 24-小时（hh:mm:ss） |
| %U | 周（00-53）星期日是一周的第一天 |
| %u | 周（00-53）星期一是一周的第一天 |
| %V | 周（01-53）星期日是一周的第一天，与 %X 使用 |
| %v | 周（01-53）星期一是一周的第一天，与 %x 使用 |
| %W | 星期名 |
| %w | 周的天（0=星期日, 6=星期六） |
| %X | 年，其中的星期日是周的第一天，4 位，与 %V 使用 |
| %x | 年，其中的星期一是周的第一天，4 位，与 %v 使用 |
| %Y | 年，4 位 |
| %y | 年，2 位 |
日期时间之间的计算
date_add( date , INTERVAL expr type)
//date 是一个DATETIME或者DATE值，用来指定起始时间是一个字符串
//expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值
//type为关键字，他指示了表达式被解释的方式 如 YEAR,MONTH,DAY,WEEK,HOUR
SELECT DATE_ADD('2018-10-10',INTERVAL 1 YEAR ); #2019-10-10
SELECT DATE_ADD('2018-10-10',INTERVAL -1 YEAR ); #2017-10-10
datediff（date1 , date2）// 返回date1减date2的天数
mysql> select datediff('1993-09-26',now());
+------------------------------+
| datediff('1993-09-26',now()) |
+------------------------------+
| -9191 |
+------------------------------+
七）信息函数
DATABASE() 当前数据库
USER() 当前用户
VERSION() 版本信息
七、MySQL存储引擎
查看当前MySQL支持的存储引擎类型
show engines;
InnoDB在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点:
MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
事务安全（遵从 ACID）
行级锁
热备份
Crash Safe Recovery（自动故障恢复）
MySQL中常用的存储引擎分别是：MyISAM、InnoDB、MEMORY。
5.5版本以后出现共享表空间概念
表空间的管理模式的出现是为了数据库的存储更容易扩展
5.6版本中默认的是独立表空间：对于用户自主创建的表，会采用此种模式，每个表由一个独立的表空间进行管理
InnoDB存储引擎：是MySQL默认的事务型存储引擎，使用广泛，基于聚簇索引建立的。InnoDB内部做了很多优化，如能够自动在内存中创建自适应hash索引，以加速读操作。
优点：支持事务和崩溃修复能力;引入了行级锁和外键约束。
缺点：占用的数据空间相对较大。
适用场景：需要事务支持，并且有较高的并发读写频率。
MyISAM存储引擎：数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，可以使用MyISAM引擎。MyISAM会将表存储在两个文件中，数据文件.MYD和索引文件.MYI。
优点：访问速度快。
缺点：MyISAM不支持事务和行级锁，不支持崩溃后的安全恢复，也不支持外键。
适用场景：对事务完整性没有要求;表的数据都会只读的。
MEMORY存储引擎：
MEMORY引擎将数据全部放在内存中，访问速度较快，但是一旦系统奔溃的话，数据都会丢失。
MEMORY引擎默认使用哈希索引，将键的哈希值和指向数据行的指针保存在哈希索引中。
优点：访问速度较快。
缺点：哈希索引数据不是按照索引值顺序存储，无法用于排序。
不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的。
事务
（一）什么是事务？
事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。
事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。
（二）事务的四个特性（ACID）
原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
**一致性（Consistency）：**事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
**隔离性（Isolation）：**同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
**持久性（Durability）：**事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
（三）并发事务导致的问题
在许多事务处理同一个数据时，如果没有采取有效的隔离机制，那么并发处理数据时，会带来一些的问题。
**丢失更新1：**撤销一个事务时，把其他事务已提交的更新数据覆盖。
小明去银行柜台存钱，他的账户里原来的余额为100元，现在打算存入100元。在他存钱的过程中，银行年费扣了5元，余额只剩95元。突然他又想着这100元要用来请女朋友看电影吃饭，不打算存了。在他撤回存钱操作后，余额依然为他存钱之前的100元。所以那5块钱到底扣了谁的？
**丢失更新2：**是不可重复读的特殊情况。如果两个事物都读取同一行，然后两个都进行写操作，并提交，第一个事物所做的改变就会丢失。
小明和女朋友一起去逛街。女朋友看中了一支口红，（对，女朋友就是用来表现买买买的）小明大方的掏出了自己的银行卡，告诉女朋友：亲爱的，随便刷，随便买，我坐着等你。然后小明就坐在商城座椅上玩手机，等着女朋友。这个时候，程序员的聊天群里有人推荐了一本书，小明一看，哎呀，真是本好书，还是限量发行呢，我一定更要买到。于是小明赶紧找到购买渠道，进行付款操作。而同时，小明的女朋友也在不亦乐乎的买买买，他们同时进行了一笔交易操作，但是这个时候银行系统出了问题，当他们都付款成功后，却发现，银行只扣了小明的买书钱，却没有扣去女朋友此时交易的钱。哈哈哈，小明真是太开心了！
**丢失更新3（脏写）：**当两个或两个以上的事务选择数据库中的同一行数据，并基于最初选定的值更新该行数据时，因为多个事务之间都无法感知彼此的存在，所以会出现最后的更新操作覆盖之前由其它事务完成的更新操作的情况。也就是说，对于同一行数据，一个事务对该行数据的更新操作覆盖了其它事务对该行数据的更新操作。
例如张三的账户余额是 100 元，当前有事务 A 和事务 B 两个事务，事务 A 负责将张三的账户余额增加 100 元，事务 B 负责将张三的账户余额增加 200 元。 起初事务 A 和事务 B 同时读取到张三的账户余额为 100 元，然后事务 A 和事务 B 分别更新张三的银行账户余额。假设事务 A 先于事务 B 提交，但最后的结果是张三的账户余额为 300 元。本来应该有 400 元的，因为 A 增加 100、B 增加 200，加上原本的 100。因此这个现象就是脏写，因为后提交的事务 B 覆盖了事务 A 的更新操作，A 的更新操作无效了。
更新丢失（脏写）本质上是写操作的冲突，解决办法是让每个事务按照串行的方式执行，按照一定的顺序依次进行写操作。
**脏读：**脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。
一个事务正在对数据库中的一条记录进行修改操作，但在这个事务完成并提交之前，有另一个事务来读取正在修改的这条数据记录。如果没有对这两个事务进行控制，则第二个事务就会读取到没有被提交的脏数据，并根据这些脏数据做进一步的处理，此时就会产生未提交的数据依赖。我们通常把这种现象称为脏读，也就是一个事务读取了另一个事务未提交的数据。
（1）进入餐厅发现“梦中情人”旁边座位已经有“帅哥”坐那儿了，正郁闷，打完饭，发现那个位置是空着的，又欣喜若狂，其实刚刚那个“帅哥”只是临时过去打个招呼。 （2）比如银行取钱，事务A开启事务，此时切换到事务B，事务B开启事务–>取走100元，此时切换回事务A，事务A读取的肯定是数据库里面的原始数据，因为事务B取走了100块钱，并没有提交，数据库里面的账务余额肯定还是原始余额，这就是脏读。 （3）例如当前有事务 A 和事务 B 两个事务，事务 A 向张三的银行账户转账 100 元，事务 B 查询张三的账户余额。事务 A 执行完转账操作、但还没有提交时，事务 B 就查询到张三的银行账户多了 100 元。 后来事务 A 由于某些原因，例如服务超时、系统异常等因素进行回滚操作，张三的余额又变回去了。但事务 B 并不知道，此时我们就说事务 B 发生了脏读，因为事务 B 使用的还是在事务 A 回滚之前就读到的脏数据。
脏读本质上是读写操作的冲突，解决办法是先写后读，也就是写完之后再读。
**幻读也叫虚读：**一个事务执行两次查询，第二次结果集包含第一次中没有或某些行已经被删除的数据，造成两次结果不一致，只是另一个事务在这两次查询中间插入或删除了数据造成的。幻读是事务非独立执行时发生的一种现象。
比如学生信息，事务A开启事务–>修改所有学生当天签到状况为false，此时切换到事务B，事务B开启事务–>事务B插入了一条学生数据，此时切换回事务A，事务A提交的时候发现了一条自己没有修改过的数据，这就是幻读，就好像发生了幻觉一样。幻读出现的前提是并发的事务中有事务发生了插入、删除操作
**不可重复读：**是指在一个事务里面读取了两次某个数据，读出来的数据不一致。
（1）在图书馆门口，发现自己占的位置旁边有位“美女”，等刷完卡，兴冲冲的走到那儿，发现已经变成“如花”了。 （2）以银行取钱为例，事务A开启事务–>查出银行卡余额为1000元，此时切换到事务B事务B开启事务–>事务B取走100元–>提交，数据库里面余额变为900元，此时切换回事务A，事务A再查一次查出账户余额为900元，这样对事务A而言，在同一个事务内两次读取账户余额数据不一致，这就是不可重复读。
（四）数据库事务的隔离级别
事务隔离级别，就是为了解决上面几种问题而诞生的。为什么要有事务隔离级别，因为事务隔离级别越高，在并发下会产生的问题就越少，但同时付出的性能消耗也将越大，因此很多时候必须在并发性和性能之间做一个权衡。
Read uncommitted（最低级别，任何情况都无法保证）
读未提交，即能够读取到没有被提交的数据，所以很明显这个级别的隔离机制无法解决脏读、不可重复读、幻读中的任何一种，因此很少使用
Read committed（可避免脏读的发生）
读已提交，即能够读到那些已经提交的数据，自然能够防止脏读，但是无法限制不可重复读和幻读
Repeatable read（可避免脏读、不可重复读的发生）
重复读取，即在数据读出来之后加锁，类似"select * from XXX for update"，明确数据读取出来就是为了更新用的，所以要加一把锁，防止别人修改它。REPEATABLE_READ的意思也类似，读取了一条数据，这个事务不结束，别的事务就不可以改这条记录，这样就解决了脏读、不可重复读的问题，但是幻读的问题还是无法解决
Serializable（可避免脏读、不可重复读、幻读的发生。）
串行化，最高的事务隔离级别，不管多少事务，挨个运行完一个事务的所有子事务之后才可以执行另外一个事务里面的所有子事务，这样就解决了脏读、不可重复读和幻读的问题了。但是这种事务隔离级别效率低下，比较耗数据库性能。
（五）MySQL中的事务处理
默认情况下，MySQL是自动提交事务，每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。
如果需要在当前会话的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置set autocommit = false;或set autocommit = 0;
DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交，因此最好事务中不要涉及DDL语句。
#开启手动处理事务模式
set autocommit = false;
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
\#开始事务
start transaction;或者begin;
\#回滚
rollback
\#提交事务
commit;
\#保存还原点
savepoint 还原点名;
\#回滚到某个还原点
rollback to 还原点名;
翻页
Limit m,n：m代表跳过几条数据，n代表显示几条数据
前3条数据 limit 0,3;
分页问题：每页显示10条数据
页码 m n
1 0 10
2 10 10
3 20 10
4 30 10
M=（当前页码-1）*n
显示第三页 一页有10条数据
也就是前两页有20条数据
显示第三页
limit 20,10;
MySQL ALTER 命令
需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。
MySQL 的 ALTER 命令用于修改数据库、表和索引等对象的结构。
ALTER 命令允许添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。
注意：
在使用 ALTER 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。
在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作。
基本语法：
ALTER TABLE <表名> [修改选项]
常用修改选项的语法格式如下：
{ ADD COLUMN <列名> <类型> | CHANGE COLUMN <旧列名> <新列名> <新列类型> | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } | MODIFY COLUMN <列名> <类型> | DROP COLUMN <列名> | RENAME TO <新表名> }
1. 添加列
ALTER TABLE table_name
ADD COLUMN new_column_name datatype;
2. 修改列的数据类型
ALTER TABLE TABLE_NAME
MODIFY COLUMN column_name new_datatype;
3. 修改列名
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name datatype;
4. 删除列
ALTER TABLE table_name
DROP COLUMN column_name;
5. 添加 PRIMARY KEY
第一种
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
第二种：
ALTER TABLE table_name
ADD CONSTRAINT pk_name PRIMARY KEY (column_name);
6. 添加 FOREIGN KEY
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (column_name)
REFERENCES parent_table (column_name);
7. 删除主键
ALTER TABLE table_name
DROP PRIMARY KEY;
8. 删除外键
ALTER TABLE table_name
DROP FOREIGN KEY fk_name;
9. 删除索引|唯一约束
ALTER TABLE table_name
DROP INDEX index_name;
10. 修改表名
ALTER TABLE old_table_name
RENAME TO new_table_name;
视图
1.什么是视图
视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是 SQL 中的一个重要概念。
视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
**视图的创建和删除只影响视图本身，不影响对应的基表。**但是当对视图中的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然。视图，是向用户提供基表数据的另一种表现形式。
视图的作用：
Ø 方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
Ø 更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；
2.创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名称 [column_list] AS column_list [WITH [CASCADED|LOCAL] CHECK OPTION]
**OR REPLACE：**如果给定了此子句，表示该语句可以替换已有视图。
**ALGORITHM：**可选项，表示视图选择的算法。
Ø UNDEFINED：表示MySQL 将自动选择所要使用的算法。
Ø MERGE：表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
Ø TEMPTABLE：表示将视图的结果存入临时表，然后使用临时表执行语句。
column_list**：**可选项，表示字段名清单。指定了视图中各个字段名，默认情况
下，与SELECT语句中查询的字段名相同。
select_statement**：**一个完整的查询语句，表示从某个表或视图中查出某些满足条件的记录，将这些记录导入视图中。
WITH CHECK OPTION**：**可选项，表示创建视图时要保证在该视图的权限范围之内。
CASCADED**：**可选项，表示创建视图时，需要满足与该视图有关的所有相关视图和表的条件，该参数为默认值。
LOCAL**：**可选项，表示创建视图时，只要满足该视图本身定义的条件即可。该源句要求具有针对视图的CREATEVIEW权限，以及针对由SELECT语句选择的母列上的某些权限。对于在SELECT其地使用的列，必须具有SELECT限。如果还有 OR REPLACE子句，必须在视图上具有DROP权限。
简化版本：
CREATE VIEW 视图名称
AS 查询语句
创建视图的规则：
①与表一样，视图必须唯一命名(不能给视图取与别的视图或表相同的名承)。
②对于可以创建的视图数量没有限制。
③为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
④视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
⑤ORDER BY可以用在视图中，但如果在该视图检索数据SELECT中含有 ORDER BY，那么该视图中的 ORDER BY 将被覆盖。
⑥视图不能索引，也不能有关联的触发器或默认值。
⑦视图可以和表一起使用。例如，编写一条连接表和视图的SELECT语句。
（1）创建单表视图
CREATE VIEW v_student AS SELECT id,NAME,sex FROM student t WHERE id = 1;
查询视图：
SELECT * FROM v_student;
针对别名的处理，可以在创建视图的子查询中指定对应的别名
CREATE VIEW v_student1 AS SELECT id stu_id,name stu_name,sex gender FROM student ;
也可以在创建视图的视图名称后添加对应的别名字段
CREATE VIEW v_student2(stu_id,stu_name,gender) AS SELECT id ,name ,sex FROM student ;
说明：
实际上就是我们在 SQL 查询语句的基础上封装了视图 VIEW，这样就会基于 SQL 语句的结果集形成一张虚拟表。
在创建视图时，没有在视图名后面指定字段列表，则视图中字段列表默认和SELECT语句中的字段列表一致。如果SELECT语句中给字段取了别名，那么视图中的字段名和别名相同。
（2）创建多表视图
CREATE VIEW v_student_score AS SELECT t1.id,t1.name,t2.c_name,t2.grade FROM student t1 left join score t2 on t1.id = t2.stu_id
查询视图
SELECT * FROM v_student_score;
（3）基于视图创建视图
CREATE VIEW v_student_score1 AS SELECT * FROM v_student_score WHERE grade > 80;
3.查看视图
语法1：查看数据库的表对象、视图对象
SHOW TABLES;
语法2：查看视图的结构
DESC / DESCRIBE 视图名称;
语法3：查看视图的详细定义信息
SHOW CREATE VIEW 视图名称;
4. 更新视图数据
（1）一般情况
MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。
（2）不可更新的视图
要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作：
Ø 在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
Ø 视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
Ø 在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；
Ø 在定义视图的SELECT语句后的字段列表中使用了 数学表达式 或 子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
Ø 在定义视图的SELECT语句后的字段列表中使用 DISTINCT 、 聚合函数 、 GROUP BY 、 HAVING 、 UNION 等，视图将不支持INSERT、UPDATE、DELETE；
Ø 在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
Ø 视图定义基于一个不可更新视图 ；
注意：虽然可以更新视图数据，但总的来说，视图作为虚拟表 ，**主要用于方便查询 ，不建议更新视图的数据。**对视图数据的更改，都是通过对实际数据表里数据的操作来完成的。
5.修改视图
方式1：使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW v_student_score
AS
SELECT t1.id,t1.name,t2.c_name,t2.grade
FROM student t1 left join score t2
on t1.id = t2.stu_id
方式2：ALTER VIEW
修改视图的语法是：
ALTER VIEW 视图名称 AS查询语句
6.删除视图
删除视图只是删除视图的定义，并不会删除基表的数据。
删除视图的语法是：
DROP VIEW IF EXISTS 视图名称 ;
DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;
举例：
DROP VIEW v_student;
说明：基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。
索引
1、索引是什么？
索引是一种数据结构，它的出现就是为了提高数据查询的效率，就像一本书的目录。
索引本身也是占用磁盘空间的（一本书中的目录也是占用页数的），它主要以文件的形式存在于磁盘中。
**本质：**通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，可以总是用同一种查找方式来锁定数据。
优点：
Ø 提高查询语句的执行效率，减少 IO 操作的次数
Ø 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
Ø 加了索引的列会进行排序（一本书的章节顺序就是按照目录来排），在使用分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间
缺点：
Ø 索引需要占物理空间
Ø 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
Ø 当对表中的数据进行增删改查，索引也要动态的维护，这样就降低了数据的更新效率
2、索引的分类
**主键索引：**一种特殊的唯一索引，不允许有空值。(主键约束=唯一索引+非空值)
**唯一索引：**索引列中的值必须是唯一的，但是允许为空值。
**普通索引：**允许空值和重复值，纯粹为了提高查询效率而存在。
**单列索引：**索引的列数量只有一个，每个表可以有多个单列索引。
**组合索引：**多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。注意，使用它的时候需要遵守最左匹配原则。多个列作为查询条件时，组合索引在工作中很常用。
**全文索引：**只能在文本内容，也就是 TEXT、CHAR、VARCHAR 数据类型的列上建全文索引，当这列的内容很长时，用like查询就会很慢，这就适合建全文索引。
**注意：**在MySQL 5.5里，这些索引只能针对MylSAM类型的表创建。MySQL 5.6引入了对InnoDB的全文捜索支持。
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同,一个表只能有一个聚集索引。
聚集索引（clustered index），也叫作聚簇索引。其余都称呼为非聚簇索引也被称为二级索引（secondary index），也叫作辅助索引。
与非聚集索引相比，聚集索引有着更快的检索速度。
3、索引的内存模型
实现索引的方式有很多种，这里先介绍下最常见的三种：哈希表、有序数组、二叉树，其中二叉树又分为二叉查找树、平衡二叉树、B 树以及 B+ 树。
MySQL索引的数据结构主要有B+树和哈希表。
对应的索引分别为B+树索引和哈希索引。
InnoDB引擎默认的索引类型为B+树索引。
不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
Archive不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
（1）哈希表
哈希表就是一种以键值对存储数据的结构。在 MySQL 中 key 用于存储索引列，value 就是某行的数据或者是它的磁盘地址。
哈希表结构适用于只有等值查询的场景，不适合范围查询。
（2）有序数组
为了解决区间查询速度慢的问题，有序数组应运而生。它的等值和范围查询都很快。
有序数组只适用于存储一些不怎么变的数据，比如一些过去的年份数据。
（3）B+树与B树
性能上
Ø 不同于B树只适合随机检索，B+树同时支持随机检索和顺序检索；
Ø B+树的磁盘读写代价更低。
Ø B+树的查询效率更加稳定。
Ø B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
4、创建索引
**方法一：**创建表时
CREATE TABLE 表名 ( 字段名1 数据类型 [完整性约束条件…], 字段名2 数据类型 [完整性约束条件…], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC |DESC]) );
注：index和key为同义词，两者作用相同，⽤来指定创建索引
**方法二：**CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;
**方法三：**ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
5、删除索引
删除索引：DROP INDEX 索引名 ON 表名字;
如何在MySQL中优化查询
一、查询优化的基本原则
1、减少访问数据的次数
2、查询过程中，需要访问数据库中的数据，访问数据的次数越少，查询效率越高。因此，减少访问数据的次数是查询优化的关键。
3、减少数据传输的量：查询过程中，需要将数据传输到客户端，数据传输的量越少，查询效率越高。因此，减少数据传输的量也是查询优化的关键。
4、减少数据的处理量：查询过程中，需要对数据进行处理，处理的量越少，查询效率越高。
二、查询优化的方法
**1、使用索引：**索引是MySQL中提高查询效率的一种重要方式。
索引创建规则：
Ø 数据量超过300的表应该有索引；
Ø 经常与其他表进行连接的表，在连接字段上应该建立索引；
Ø 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
Ø 索引应该建在选择性高的字段上；
Ø 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
Ø 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
Ø 正确选择复合索引中的主列字段，一般是选择性较好的字段；
Ø 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
Ø 如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
Ø 如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
Ø 如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
Ø 频繁进行增删改操作的表，不要建立太多的索引；
Ø 删除无用的索引，避免对执行计划造成负面影响；
**2、优化查询语句：**优化查询语句是提高查询效率的另一种重要方式。
在优化查询语句时，可以采用以下方法：
（1）使用正确的查询语句
在查询数据时，应该使用正确的查询语句。例如，如果查询的是单个记录，应该使用SELECT … WHERE语句；如果查询的是多条记录，应该使用SELECT … FROM语句。
（2）避免使用SELECT *
使用SELECT *将返回所有列的数据，这样会增加数据传输的量，导致查询效率降低。因此，应该只查询需要的列数据，避免使用SELECT *。
（3）避免使用子查询
使用子查询会增加查询的复杂度，导致查询效率降低。
（4）使用JOIN查询
使用JOIN查询可以减少访问数据的次数，提高查询效率。
（5）使用LIMIT语句
在查询大量数据时，应该使用LIMIT语句来限制返回数据的数量。
**（6）count()优化：**count(*) 会算出包含null记录的数量， count(field_name)只包含不含 null的数量(这也是很多时候两种count方式结果不一致的原因)， count()的时候尽量用后一种。
（7）group by优化 如果包含子查询，在子查询里面使用where条件和group by过滤, 避免在复杂查询的最外层使用group by(如果最外层使用会用到临时表)
3、优化表结构
优化表结构是提高查询效率的另一种重要方式。在优化表结构时，可以采用以下方法：
（1）删除不必要的列
删除不必要的列可以减少数据传输的量，提高查询效率。
（2）合理设置数据类型和长度
合理设置数据类型和长度可以减少数据的处理量，提高查询效率。尽量使用合适长度的数据类型，如整型可以根据数值范围选择TINYINT、SMALLINT、MEDIUMINT、INT或BIGINT，字符串可以根据字符数选择CHAR、VARCHAR或TEXT。
（3）避免使用NULL值
使用NULL值会导致查询效率降低。
（4）使用分区表
使用分区表可以将大型表分成多个小表，减少访问数据的次数，提高查询效率。
（5）尽量使用简单的数据类型，如整型、浮点型、日期型等，避免使用复杂的数据类型，如文本型、二进制型、JSON型等。简单的数据类型占用空间少，比较操作快，索引效率高。
4、使用缓存
使用缓存可以减少访问数据库的次数，提高查询效率。
5、表级优化
（1）表的范式优化
（2）适当增减一些冗余，做反范式优化（以空间换取时间）
（3）表的列非常多的时候使用垂直拆分
原则：
（1）把不常用的单独字段放到一个表中
（2）把大字段独立存放到一个表中
（3）把经常一起用的字段放在一起
（4）表的数据量非常大的时候使用水平拆分
方法:
Ø 根据某个字段进行hash预算， 如果要拆分成5个表， 用取余的方式取到0-4，分表保到相应的表中
Ø 针对不同的hashID把数据存到不同的表中
三、查询优化的注意事项
在进行查询优化时，需要注意以下事项，以确保查询优化的效果和正确性：
1、在使用索引时，需要选择合适的索引类型和创建索引的列，避免因索引不匹配或创建索引的列不正确等问题导致查询效率降低。
2、在优化查询语句时，需要遵循查询优化的基本原则，避免因查询语句不正确或优化不充分等问题导致查询效率降低。
3、在优化表结构时，需要根据实际情况选择合适的数据类型、长度和表结构设计，避免因表结构不合理或数据类型不匹配等问题导致查询效率降低。
4、在使用缓存时，需要注意缓存的大小和缓存的更新方式，避免因缓存过大或缓存更新不及时等问题导致查询效率降低。
5、在使用MySQL优化工具时，需要了解工具的使用方法和原理，避免因使用不当或理解不清等问题导致查询效率降低。
数据库范式
设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
第一范式：列不可分
定义：设R是一个关系模式，R属于第一范式当且仅当R中每一个属性A的值域只包含原子项，即不可分割的数据项。
1NF不能排除数据冗余和更新异常等问题，因为其中可能包含部分函数依赖。
上表就是不满足第一范式的表，表中工资项还可以分为基本工资、加班工资和实发工资，还可以进行分割，不是原子项，所有不满足第一范式。
而这张表满足了属性全为原子项，不可分割，所以上面学生表满足数据库第一范式。但可以看出这张学生表还存在着数据冗余、更新异常和插入异常等问题，并且包含着部分函数依赖。
第二范式
定义：设R是一个关系模式，R属于第二范式当且仅当R是1NF，且每个非主属性都完全函数依赖于候选码。
属于2NF的关系模式R也可能存在数据冗余和更新异常等问题，因为其中可能存在传递函数依赖。但不属于2NF的关系模式R会产生插入异常、删除异常和修改复杂等问题。
相关概念：
**元组：**表中的一行即为一个元组，对应存储文件中的一个记录值。
**属性：**表中的列称为属性，每一列有一个属性名。属性名相当于记录中的数据项或字段值。
**候选码：**属性或属性组合，其值能够唯一标识一个元组。
**主码（主键）：**在一个关系中可能有多个候选码，从中选择一个作为主码。
**部分函数依赖：**如果X->Ｙ，但Ｙ不完全函数依赖于Ｘ，则称Y对Ｘ部分函数依赖。部分函数依赖就是一个组合里任意真子集能够决定依赖关系，例如（学号,课程号）->姓名这个组合关系的函数依赖中，单一个学号也能够决定姓名了，所以这就是部分函数依赖。
了解完这些概念之后，我们来分析这张表，其中，（学号，课程号）为表的候选码，唯一标识一个元组，从上图第二行可以看出姓名、学院、院长都部分函数依赖于（学号，课程号）中的学号，课程名部分函数依赖于（学号、课程号）中的课程名，非主属性不都完全函数依赖于候选码，所以不满足数据库第二范式。
而想要上表满足第二范式，我们就需要对表进行拆解。
把表拆解成以上三个表之后，可以看出，三个表中非主属性都完全函数依赖于候选码，且具有无损连接性和保持函数依赖性，满足了数据库第二范式。
第三范式
定义：设R是一个关系模式，R属于第三范式当且仅当R是2NF，且每个非主属性都非传递函数依赖于候选码。
一个不属于3NF的关系模式R会产生插入异常、删除异常和修改复杂等问题。属于3NF的关系模式R可能存在主属性对码的部分依赖和传递依赖。
第三范式要求在表满足第一范式的情况下，表中每个非主属性都非传递函数依赖于候选码。
在上面第二范式分解的表中，可以看出只有一张表不满足，出现了传递函数依赖。
上表中学号决定了学院，学院又决定了院长，可得出院长对学号传递依赖。要消除传递函数依赖，我们依旧需要进行拆分表。
可以看出分解后的表已经不存在传递函数依赖，满足了数据库第三范式。
总结
修改表结构使得表满足数据库三范式的过程就是层层递进的过程：
要满足数据库第一范式，就需要表中属性是原子不可分割的。
要满足数据数据库第二范式，就需要消除表中的部分函数依赖。
要满足数据库第三范式，就需要消除表中的传递函数依赖。
SQL语言分类
结构化查询语⾔(Structured Query Language，简称SQL)是⼀种数据库查询和程序设计语⾔。
SQL语句按其功能分为4类：
1.数据定义语言（DDL Data Definition Language） ：创建、修改或删除数据库中表、视图、索引等对象的操作，常⽤命令为create、alter和drop；
2.数据查询语言（DQL Data Query Language） ：按照指定的组合、条件表达式或排序检索已存在的数据库中数据，不改变数据库中数据，常⽤命令为select；
*3.数据操纵语言（DML Data Manipulation Language） ：向表中添加、删除、修改数据操作，常⽤命令有insert、update和delete；
4.数据控制语言（DCL Data Control Language） ：⽤来授予或收回访问数据库的某种特权、控制数据操纵事务的发⽣时间及效果、对数据库进⾏监视等操作，常⽤命令有GRANT、REVOKE、COMMIT、ROLLBACK；
触发器
一、什么是触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
触发器的这种特性可以协助应用在数据库端确保数据的完整性。
比如现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，需要编写程序语言逻辑才能实现，但是如果定义了一个触发器，触发器的作用就是在用户表中插入一条数据的之后在日志表中插入一条日志信息。
创建触发器
创建触发器的语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE
创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表 END
其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开，MySQL默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突，为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||，当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;
DELIMITER || CREATE TRIGGER demo BEFORE DELETE ON users FOR EACH ROW BEGIN INSERT INTO logs VALUES(NOW()); INSERT INTO logs VALUES(NOW()); END || DELIMITER ;
上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作
就会执行BEGIN和END中的语句，接着使用||结束
最后使用DELIMITER ; 将结束符号还原
tigger_event：
load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。
触发器可以是一条SQL语句，也可以是多条SQL代码块，那如何创建呢？
DELIMITER $ #将语句的分隔符改为$ BEGIN sql1; sql2; ... sqln END $ DELIMITER ; #将语句的分隔符改回原来的分号";"
在BEGIN…END语句中也可以定义变量，但是只能在BEGIN…END内部使用：
DECLARE var_name var_type [DEFAULT value] #定义变量，可指定默认值 SET var_name = value #给变量赋值
NEW和OLD的使用:
根据以上的表格，可以使用一下格式来使用相应的数据：
NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据
案例：使用触发器完成日志记录
现在有表如下：
用户users表
CREATE TABLE `users` ( `id` int AUTO_INCREMENT, `name` varchar(255), `add_time` datetime, PRIMARY KEY (`id`) );
日志logs表：
CREATE TABLE `logs` ( `Id` int AUTO_INCREMENT, `log` varchar(255), PRIMARY KEY (`Id`) );
**需求：**当在users中插入一条数据，就会在logs中生成一条日志信息。
创建触发器：
DELIMITER $ CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW BEGIN DECLARE s1 VARCHAR(40)character set utf8; DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集 SET s2 = " is created"; SET s1 = CONCAT(NEW.name,s2); #函数CONCAT可以将字符串连接 INSERT INTO logs(log) values(s1); END $ DELIMITER ;
需求：删除users数据表中的数据，在logs表中记录
CREATE TRIGGER user_log_delete AFTER DELETE on users FOR EACH ROW BEGIN DECLARE cc VARCHAR(255); DECLARE txt VARCHAR(255); SET txt= "被删除了"; SET cc=CONCAT(OLD.name,txt,now()); insert into logs(log) values(cc); END
删除触发器
drop trigger 触发器名;
总结
触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过多的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果。
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
存储过程
一、基本概念
1、什么是存储过程
**存储过程（**Stored Procedure）是一组为了完成特定功能的SQL 语句集合，经编译后保存在数据库中。可包含数据操纵语句、变量、逻辑控制语句等。
MySQL5.0版本开始支持存储过程，使数据库引擎更加灵活和强大
2、存储过程的优缺点
优点
Ø 减少网络流量
Ø 提升执行速度
Ø 减少数据库连接次数
Ø 安全性高
Ø 复用性高
**缺点：**可移植性差
在实际应用开发中，要根据业务需求决定是否使用存储过程，对于应用中特别复杂的数据处理，可以选用存储过程来进行实现
例如：复杂的报表统计，涉及多条件多表的联合查询等
3、创建存储过程
CREATE PROCEDURE 过程名 ([过程参数[,…]]) [特性] #可选项,用于设置存储过程的行为 存储过程体
常用特性：
| 特性 | 说明 |
|---|---|
| LANGUAGE SQL | 表示存储过程语言，默认SQL |
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | 表示存储过程要做的工作类别 默认值为CONTAINS SQL |
| SQL SECURITY { DEFINER | INVOKER } | 指定存储过程的执行权限 默认值是DEFINER DEFINDER：使用创建者的权限 INVOKER：用执行者的权限 |
| COMMENT ‘string’ | 存储过程的注释信息 |
案例1：
CREATE PROCEDURE p1() BEGIN SELECT NOW(); END CREATE PROCEDURE p2() BEGIN SELECT * FROM users; END
**4、调用存储过程：**CALL 存储过程名([参数]);
二、存储过程的参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])
Ø IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
Ø OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
Ø INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
1、in 输入参数
create procedure in_param(in p_in int) begin select p_in; set p_in=2; select p_in; end SET @p1=10; CALL in_param(@p1);
2、out输出参数
create procedure out_param(out p_out int) begin select p_out; set p_out=2; select p_out; end
out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
建议：
Ø 输入值使用in参数。
Ø 返回值使用out参数。
Ø inout参数就尽量的少用。
MySQL存储过程的删除：DROP PROCEDURE 存储过程名;
三、MySQL存储过程的控制语句
1、if-then-else 语句
CREATE PROCEDURE p_if(in age int) BEGIN IF age<18 THEN SELECT '未成年'; ELSE SELECT '成年'; END IF; END
2、case语句：
3、循环语句
（1）while ···· end while
（2）repeat···· end repeat
（3）loop ·····endloop
| 说明 |
| ------------------------------------------------------------ | ------------------------------------------------------------ |
| LANGUAGE SQL | 表示存储过程语言，默认SQL |
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | 表示存储过程要做的工作类别 默认值为CONTAINS SQL |
| SQL SECURITY { DEFINER | INVOKER } | 指定存储过程的执行权限 默认值是DEFINER DEFINDER：使用创建者的权限 INVOKER：用执行者的权限 |
| COMMENT ‘string’ | 存储过程的注释信息 |
案例1：
CREATE PROCEDURE p1() BEGIN SELECT NOW(); END CREATE PROCEDURE p2() BEGIN SELECT * FROM users; END
**4、调用存储过程：**CALL 存储过程名([参数]);
二、存储过程的参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形…])
Ø IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
Ø OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
Ø INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
1、in 输入参数
create procedure in_param(in p_in int) begin select p_in; set p_in=2; select p_in; end SET @p1=10; CALL in_param(@p1);
2、out输出参数
create procedure out_param(out p_out int) begin select p_out; set p_out=2; select p_out; end
[外链图片转存中…(img-dlfwulF6-1709306616841)]
[外链图片转存中…(img-PDIroKRI-1709306616841)]
out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
建议：
Ø 输入值使用in参数。
Ø 返回值使用out参数。
Ø inout参数就尽量的少用。
MySQL存储过程的删除：DROP PROCEDURE 存储过程名;
三、MySQL存储过程的控制语句
1、if-then-else 语句
CREATE PROCEDURE p_if(in age int) BEGIN IF age<18 THEN SELECT '未成年'; ELSE SELECT '成年'; END IF; END
2、case语句：
3、循环语句
（1）while ···· end while
（2）repeat···· end repeat
（3）loop ·····endloop
（4）ITERATE迭代
3380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
