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
表示分,S
S表示秒- 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
的大小为2000M
B,如果用完了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.通过二级索引查询时
回表
不是必须的过程,当SELEC
T的所有字段在单个二级索引
中都能够找到时就不需要回表,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 Serve
r),但不是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 loc
k)- 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.R
ecord 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
文章