mysql基础知识

基本命令

服务和登录

1.cmd中打开服务窗口的命令
net start mysql

2.cmd中停止mysql服务的命令

net stop mysql
3. 使用root账号root密码登录的命令
mysql -u root -p

4. 退出登录的命令
quit
EXIT
\q

SQL注释

单行注释: -- 你好
多行注释: /* 巴拉巴拉 */
MySQL 独有的单行注释: # 哈哈哈哈

SQL大小写规范

• MySQL 在 Windows 环境下是大小写不敏感的
• MySQL 在 Linux 环境下是大小写敏感的
  • 数据库名、表名、表的别名、变量名是严格区分大小写的
  • 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
• 推荐采用统一的书写规范：
  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL 关键字、函数名、绑定变量等都大写

SQL分类

• DDL（Data Definition Language）数据定义语言
  • 这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。关键字：CREATE、DROP、ALTER 等
• DML（Data Manipulation Language）数据操作语言
  • 用来对数据库中表的数据进行增删改。关键字：INSERT、DELETE、UPDATE 等
• DQL（Data Query Language）数据查询语言
  • 用来查询数据库中表的记录（数据）。关键字：SELECT等
• DCL（Data Control Language）数据控制语言（了解）
  • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT 等

1.	数据查询语言DQL（Data Query Language）：select、where、order by、group by、having 。

2.	数据定义语言DDL（Data Definition Language）：create、alter、drop。

3.	数据操作语言DML（Data Manipulation Language）：insert、update、delete 。

4.	事务处理语言TPL（Transaction Process Language）：commit、rollback 。

5.	数据控制语言DCL（Data Control Language）：grant、revoke。

DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

查询所有数据库

#查看MYSQL所有的数据库
SHOW DATABASES ; #显示当前MYSQL中包含的所有数据库

数据库名称	描述
information_schema	信息数据库，其中保存着关于所有数据库的信息（元数据）。 元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。
mysql	核心数据库，主要负责存储数据库的用户、权限设置、关键字等， 以及需要使用的控制和管理信息，不可以删除。
performance_schema	性能优化的数据库，MySQL 5.5版本中新增的一个性能优化的引擎。
sys	系统数据库，MySQL5.7版本中新增的可以快速的了解元数据信息的系统库 便于发现数据库的多样信息，解决性能瓶颈问题。

查看当前使用的数据库

SELECT DATABASE(); #查看当前使用的数据库

创建自定义数据库

CREATE DATABASE mydb1; #创建名为mydb1的数据库

#创建名为db2的数据库并设置编码格式为gbk
CREATE DATABASE mydb2 CHARACTER SET gbk ;

CREATE DATABASE mydb2  CHARSET gbk;
#如果db3不存在,则创建,如果存在,则不创建
CREATE DATABASE IF NOT EXISTS mydb3 ;
CREATE DATABASE IF NOT EXISTS mydb3  CHARACTER SET gbk

查看数据库创建信息

SHOW CREATE DATABASE db3; #查看创建数据库时的基本信息

修改数据库

注意mysql中utf-8 是utf8

ALTER DATABASE db3 CHARACTER SET gbk #修改创建数据库时的基本信息

删除数据库

DROP DATABASE if exists db3 #删除数据库

使用/切换数据库

USE db2; #使用db2数据库

查看表的结构

desc `表名`;
describe `表名`;

DESC t_employees #查看表的信息

查看表的字符集

show CREATE TABLE t_employees

查看所有表信息

选择数据库后，才能查看表

show tables;

数据类型

MYSQL支持多种类型,大致可以划分为三类:数值，日期/时间和字符串(字符)类型

数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
DOUBLE	8 字节	（-1.797E+308,-2.22E-308）	(0,2.22E-308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8个字节，M表示长度，D表示小数位数	同上，受M和D的约束 DUBLE(5,2) -999.99-999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值

日期类型

类型	大小	范围	格式	用途
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:00/2038 结束时间是第 2147483647 秒北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串 char(10) 10个字符
VARCHAR	0-65535 字节	变长字符串 varchar(10) 10个字符
BLOB（binary large object）	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据

• CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
• BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
• BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。
• 有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

数据表的创建（create）

最后一个字段后面没有逗号

#数据表的创建（CREATE）

CREATE TABLE 表名(

    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ...
    列名 数据类型 [约束] #最后一列的末尾不加逗号
    
 )[CHARSET=utf8] #可以根据需要指定的表的字符编码集

字符集如果不指定, 默认继承库的字符集

CREATE TABLE SUBJECT(
   subjectid INT,
   subjectname VARCHAR(20),
   subjecthours INT

)CHARSET=utf8;

数据表的修改(ALTER)

#alter TABLE 表名 操作;

• 注意：修改表中的某列时，也要写全列的名字，数据类型，约束

向现有表中添加列

#在课程表中添加 score列
ALTER TABLE SUBJECT ADD score INT

修改表中的列

#修改课程表中课程的字符长度为10个字符
ALTER TABLE SUBJECT MODIFY subjectname VARCHAR(10)

注意 修改列表中的某列时,也要写全列的名字,数据类型，约束

删除表中的列

#删除表中的score 列 
ALTER TABLE SUBJECT DROP score

注意 删除列的时，每次只能删除一列

修改列名

#修改课程表中 subjecthours 为 classhours
ALTER TABLE SUBJECT CHANGE subjecthours classhours INT

注意修改列名时，在给定新列名称时，要指定列的类型和约束

修改表名

#修改表名为sub
ALTER TABLE `subject` RENAME sub

数据表的删除

删除课程表

DROP TABLE 表名
DROP TABLE sub;

TRUNCATE TABLE 表名;

约束

实体完整性约束
表中的一行数据代表一个实体(entity) ,实体完整性的作用是标识每一行的数据不重复，实体唯一。

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

#实体完整性约束
#主键约束
PRIMARY KEY 唯一,标识表中的一行数据，此列不可重复,且不能为null

#唯一约束
UNIQUE 唯一,标识表中的一行数据,不可重复，可以为NULL

AUTO_INCREMENT 自动增长,给主键数值列添加自动增长,从1开始,每次加1，不能单独使用，和主键使用
#域完整性约束： 限制列的单元格的数据正确性
NOT NULL 非空,此列必须有值

DEFAULT 值  为此列赋予默认值,当新增数据不指定值时,书写DEFAULT以指定的默认值进行填充

引用完整性的约束


FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值

CREATE TABLE tb_user(
id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识',
name varchar(10) NOT NULL UNIQUE COMMENT '姓名' ,
age int check (age > 0 && age <= 120) COMMENT '年龄' ,
status char(1) default '1' COMMENT '状态',
gender char(1) COMMENT '性别'
);

CREATE TABLE SUBJECT(
  subjectid INT PRIMARY KEY AUTO_INCREMENT,#课程编号标识的编号唯一,且不能为NULL,自动增长会从1开始自动增长，每次增加1
  subjectname VARCHAR(10) UNIQUE NOT NULL,#课程名称唯一,课程名不能为空
  subjecthours INT DEFAULT 20 #默认是学时20个小时
  
)CHARSET=utf8

CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名,列名

CREATE TABLE sutdent(
   stuid INT AUTO_INCREMENT PRIMARY KEY,
   stuname VARCHAR(10),
   subjectid INT NOT NULL,
   CONSTRAINT fk_student_stuid FOREIGN KEY(subjectid) REFERENCES `subject`(subjectid)
   #和课程表中的subjectid相关联
   )

注意：建立关系时一定要先创建主表，在创建重表
删除关系时，先删除重表，再删除主表

外键约束

外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

CREATE TABLE 表名(
字段名 数据类型,
...
[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名)
);

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行
为有以下几种:

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES
主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

DML

新增(insert)

INSERT INTO 表名（列1,列2,列3.....）values(值1,值2,值3......)

-- 按照指定字段, 一次插入多行
insert into `表名` (字段1, 字段2 ...) values (值1, 值2, ...), (值1, 值2, ...);

-- 指定全部字段, 一次插入多行
insert into `表名` values (null, 值1, 值2, ...), (null, 值1, 值2, ...);

INSERT INTO SUBJECT(subjectname,subjecthours) VALUES('语文',30);

INSERT INTO SUBJECT(subjectname,subjecthours) VALUES('数学',DEFAULT);#默认课时是20个小时

注意:表名后的列名和values里的值要一一对应(个数，顺序，类型)
如果设置了自动增长则不能手动添加对应的列
字符串和日期型数据应该包含在引号中。
插入的数据大小，应该在字段的规定范围内。

修改(update)

UPDATE	表名 SET 列1=新增1,列2=新增2....where 条件

UPDATE SUBJECT SET subjectname='英语' WHERE subjectid=1  #将课程编号为1的课程名改为英语

UPDATE SUBJECT SET subjectname='物理',subjecthours=36 WHERE subjectid=2 #可以一次性改多个值

UPDATE `subject` SET  subjecthours=40 #如果不加条件,在没有违反约束的前提下会修改整张表

注意: set后有多个列名=值 绝大多数的情况下都要加where条件制定修改，否则为整表更新

删除(DELETE)

DELETE FROM 表名 WHERE 条件

删除一条信息

DELETE FROM SUBJECT WHERE subjectid=2

注意:删除时，如果不加where 删除的是整张表的数据

清空整张表数据(TRUNCATE)

TRUNCATE TABLE 表名

注意:与delete不加where删除数据不同，TRUNCATE是把表销毁，再按原表格式创建一个新表

DQL

执行查询语句返回的结果是一张虚拟表

基本查询语句

select 列名 from 表名

关键字	描述
SELECT	指定要查询的列
FROM	指定要查询的表

查询部分列

#从员工表中查询所有员工的id，姓和名
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME FROM t_employees;

查询所有列

#查询员工表中所有的信息(所有列)
SELECT * FROM t_employees
*是通配符代表着所有，效率低,可读性差

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用SELECT *进行查询。

对列中数据进行运算

每次查询相当于是一张虚拟的表，对原本表里面的内容没有影响

#查询员工表中的id和年薪
SELECT EMPLOYEE_ID,SALARY*12 FROM t_employees

算数运算符	描述
+	两列做加法运算
-	两列做减法运算
*	两列做乘法运算
/	两列做除法运算

注意:%是站位运算符，而不是取余运算符

列的别名

列 as '列名'

SELECT EMPLOYEE_ID '员工id',SALARY*12 AS '年薪' FROM t_employees

as可以省略

查询结果去重

#DISTINCT 去重
SELECT DISTINCT department_id,salary 
FROM employees;

DISTINCT 需要放到所有列名的前面，如果写成SELECT salary, DISTINCT department_id FROM employees会报错。

排序查询

#select 列名 from 表名 order by 排序列[排序方式]
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC

排序规则	描述
asc	对前面排序列做升序排序
desc	对前面排序列做降序排序

注意:排序方式可以省略那么默认是升序排列

依据单列排序

#按员工月薪降序排列
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC

依据多列排序

#按员工月薪降序排列当员工月薪相同时按员工id升序排列（薪资相同时，按照编号进行升序排序)
SELECT EMPLOYEE_ID,SALARY FROM t_employees ORDER BY SALARY DESC,EMPLOYEE_ID ASC	

当有多个列排序时先依据前面的先排，相同时在根据后面的排

注意：
1.排序方式有2种：升序 asc  降序 desc ，默认升序
2.如 字段1 和 字段2 排序有冲突，字段1 优先满足。
3.如字段1数据相同，再按照字段2的排序方式排序。

-- 注意：字符串也是可以排序的，排序依据为字符编码的二进制值
select name from student3  order by name;

条件查询

SELECT 列名 from 表名 where 条件

关键字	描述
WHERE 条件	在查询结果中，筛选符合条件的查询结果，条件为布尔表达式

等值判断

#查询员工月薪为400的信息（员工id，月薪）

SELECT EMPLOYEE_ID ,SALARY FROM t_employees WHERE SALARY = 4000

注意：与java中不同 java中是==，mysql中等值判断使用=

逻辑判断（and,or,not）

#查询员工月薪为11000并且员工号为114 的信息
SELECT * FROM t_employees WHERE SALARY = 11000 AND EMPLOYEE_ID=114

不等值判断（>,<,>=,<=,!=.<>）

区间判断（between and）

#查询薪资在4000到11000之间的员工信息
SELECT * FROM t_employees WHERE SALARY BETWEEN 4000 AND 11000
SELECT * FROM t_employees WHERE SALARY>=4000 AND SALARY<=11000

注意：在区间判断语法中，小值在前，大值在后，反之得不到正确结果

NULL值判断（IS NULL , IS NOT NULL）

IS NULL
列名 is NULL

IS NOT NULL
列名 is NOT	NULL

#查询没有提成的员工信息
SELECT * FROM t_employees WHERE COMMISSION_PCT IS NULL

枚举查询（IN （值1，值2，值3））

#查询部门编号为70,80,90的员工信息
SELECT * FROM t_employees WHERE DEPARTMENT_ID IN(70,80,90)

SELECT * FROM t_employees WHERE DEPARTMENT_ID=70 OR DEPARTMENT_ID=80 OR DEPARTMENT_ID=90 

in 表示包含这些条件的
not in 表示除去这些条件以外的，也就是不包含这些条件的
注意 in的查询效率比较低，可以通过多条件拼接

模糊查询

LIKE _(单个任意字符)
列名 LIKE '张_'
这样就只能匹配到例如张三两个字的名字，就不能匹配3个或4个字的名字

LIKE %(任意长度的任意字符串)
列名 LIKE '张%'
这样可匹配姓张的所有姓名，无论是几个字的姓名

注意: 模糊查询只能与LIKE关键字结合使用

#查询姓中以l开头所有员工信息
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'L%'

#查询姓中以l开头并且长度为4的所有员工信息
SELECT * FROM t_employees WHERE FIRST_NAME LIKE 'l___'

分支结构查询

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END as '列名'

注意：通过使用CASE END进行条件判断，每条数据对应生成一个值，与java中的switch类似
后面最好在取一个别名否则列名是对应的表达式

#根据薪水划分薪水等级
SELECT * ,
CASE 
     WHEN SALARY>=1000 AND SALARY <2000 THEN 'A'
     WHEN SALARY>=2000 AND SALARY <5000 THEN 'B'
     WHEN SALARY>=5000 AND SALARY <9000 THEN 'C'
     ELSE 'D'

END AS '月薪等级'
FROM t_employees 

流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

函数	功能
IF(value , t , f)	如果value为true，则返回t，否则返回f
IFNULL(value1 , value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] …ELSE [ default ] END	如果val1为true，返回res1，… 否则返回default默认值
CASE [ expr ] WHEN [ val1 ] THEN[res1] … ELSE [ default ] END	如果expr的值等于val1，返回res1，… 否则返回default默认值

select if(false, 'Ok', 'Error');  #Error

select ifnull('Ok','Default');  #Ok

select ifnull('','Default');

select ifnull(null,'Default'); #Default

时间查询

时间函数	描述
SYSDATE()	当前系统时间（日、月、年、时、分、秒）
SYSTIMESTAMP()	当前系统时间（日、月、年、时、分、秒）
CURDATE()	获取当前日期
CURTIME()	获取当前时间
WEEK()	获取指定日期为一年中的第几周
YEAR(DATE)	获取指定日期的年份
HOUR(TIME)	获取指定时间的小时值
MINUTE(TIME)	获取时间的分钟值
DATEDIFF(DATE1,DATE2)	获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N)	计算DATE 加上 N 天后的日期

#当前系统时间 
SELECT NOW();

#当前系统时间 
SELECT SYSDATE(); #2020-09-23 22:42:25

#当前系统日期 
SELECT CURDATE(); #2020-09-23

#当前系统时间
SELECT CURTIME(); #22:44:55

#获取指定日期中的年份
SELECT YEAR('2020-09-23'); #2020

#获取小时值
SELECT HOUR(CURTIME()); #22

#获取分钟值
SELECT MINUTE(CURTIME()) #48

#指定日期之差的相隔天数
SELECT DATEDIFF('2020-10-30','2019-10-30'); #366
SELECT DATEDIFF('2019-10-30','2020-10-30'); #-366

#计算Date日期加上N天后的日期
SELECT ADDDATE('2020-10-30',40); #2020-12-09
SELECT ADDDATE('2020-10-30',-40); #2020-09-20

案例：

查询所有员工的入职天数，并根据入职天数倒序排序。
思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by
entrydays desc;

字符串应用

字符串函数	说明
CONCAT(str1,str2,str…)	将 多个字符串连接
INSERT(str,pos,len,newStr)	将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str)	将指定字符串转换为小写
UPPER(str)	将指定字符串转换为大写
SUBSTRING(str,num,len)	将str 字符串指定num位置开始截取 len 个内容
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格

#多个字符串拼接
SELECT CONCAT('hello','world','java');#helloworldjava

SELECT CONCAT(FIRST_NAME,LAST_NAME) AS '姓名' FROM t_employees;

#字符串大写转换
SELECT LOWER('HELLO'); #hello

#字符串小写转换
SELECT UPPER('hello'); #HELLO

#字符串替换
SELECT INSERT('dyk的数据库',1,3,'mysql'); #mysql的数据库

#指定内容的获取
SELECT SUBSTRING('dyk的mysql数据库',5,8);  #mysql数据库

#左填充
select lpad('01', 5, '-'); #---01

# rpad : 右填充
select rpad('01', 5, '-'); #01---

# trim : 去除空格
select trim(' Hello MySQL ');#Hello MySQL

mysql中字符串下标是从1开始的

字符串案例

由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0。比如： 1号员工的工号应该为00001。

update emp set workno = lpad(workno, 5, '0');

数值函数

常见的数值函数如下：

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

#  ceil：向上取整
select ceil(1.1); #2

#  floor：向下取整
select floor(1.9);#1

# mod：取模
select mod(7,4);#3

#  rand：获取随机数
select rand();

# round：四舍五入
select round(2.344,2); #2.34

案例：

通过数据库的函数，生成一个六位数的随机验证码。
思路： 获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

select lpad(round(rand()*1000000 , 0), 6, '0');

聚合函数

SELECT 聚合函数(列名) FROM 表名
对多条数据进行的单列进行统计，返回统计后一行的结果

聚合函数	说明
SUM()	求所有行中单列结果的总和
AVG()	平均值
MAX()	最大值
MIN()	最小值
COUNT()	求总行数

#聚合函数
SELECT 聚合函数(列名) FROM 表名

#求单列所有数据的和
#SUM()函数返回一组值的总和，SUM()函数忽略`NULL`值。**如果找不到匹配行，则SUM()函数返回`NULL`值。
SELECT SUM(SALARY) FROM t_employees

#求单列所有数据的平均值
#AVG()函数计算一组值的平均值**。 它计算过程中是忽略`NULL`值的
SELECT AVG(SALARY) FROM t_employees

#求单列的最大值
#MAX()函数返回一组值中的最大值
SELECT MAX(SALARY) FROM t_employees 

#求单列的最小值
#MIN()函数返回一组值中的最小值
SELECT MIN(SALARY) FROM t_employees 

#总行数 员工的数量
#COUNT()函数返回结果集中的行数
SELECT COUNT(EMPLOYEE_ID) FROM t_employees

#统计有提成的人数
SELECT COUNT(COMMISSION_PCT) FROM t_employees

注意：聚合函数会自动忽略null值,不进行统计

分组查询

GROUP BY 语句根据一个或多个列对结果集进行分组

select 列名 from 表名 where 条件 group by 分组依据(列)

关键字	说明
GROUP BY	分组依据，必须在 WHERE 之后生效

或者在分组之后用having来进行筛选

#查询各部门的总人数
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再针对各部门的人数进行统计

SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID) FROM t_employees GROUP BY DEPARTMENT_ID

#查询各部门的平均工资
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再对每个部门的工资进行统计

SELECT DEPARTMENT_ID,AVG(salary) FROM t_employees GROUP BY DEPARTMENT_ID

#查询各个部门,各个岗位的人数
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再按照岗位进行分组（分组的依据是JOB_ID）
#3.最后按照各部门各个岗位的人数进行统计

SELECT DEPARTMENT_ID,JOB_ID,COUNT(EMPLOYEE_ID) FROM t_employees GROUP BY DEPARTMENT_ID,JOB_ID

分组查询中,select显示的列只能是分组的依据列或者聚合函数列不能出现其他列
换句话说就是select查询的列中如果不是聚合函数就一定也要在GROUP BY后面写上相应的列

-- 查询address
select  address from student3;

-- 使用distinct关键字
select distinct address from student3;

-- group by 分组查询
--这其实跟distinct返回的结果一致
select address from student3 group by address;



-- 当查询多个列的数据时

-- 查询name和address
select  name,address from student3;


-- 用distinct试试
-- 这里只是去重了name
select distinct  name,address from student3;


--  用group by 试试
-- 结果集name,address一组都相同才会去重
select  name,address from student3 group by name,address;

分组过滤查询

SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

关键字	说明
HAVING 过滤规则	过滤规则定义对分组后的数据进行过滤

#统计10,80,90部门的最高工资
#1.按照部门编号进行分组（分组的依据是DEPARTMENT_ID）
#2.再根据过滤条件过滤出部门编号为70,80,90的信息
#3.再求出最大值

SELECT DEPARTMENT_ID,MAX(SALARY) FROM t_employees GROUP BY DEPARTMENT_ID HAVING DEPARTMENT_ID IN(70,80,90)

分组前条件 和 分组后条件 过滤结果集

• where 分组前条件
• having 分组后条件

注意：

1.where 在分组前进行条件过滤，不满足条件的记录不参与分组，不能跟 聚合函数
2.having 在分组后进行条件限定，不满足限定的记录不会被查询出来， 可以跟 聚合函数

where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
• 判断条件不同：where不能对聚合函数进行判断，而having可以。

限定查询

SELECT 列名 FROM 表名 LIMIT 起始行,查询行数

关键字	说明
LIMIT offset_start，row_count	限定查询结果的起始行和总行数

#查询员工表中前5名员工的信息
SELECT * FROM t_employees LIMIT 0,5

mysql中没有 top和sql server 不同

mysql中字符串是从1开始的，但是这个起始行还是从0开始的

注意：起始行是从0开始的，代表了第一行，第二个参数代表的是从指定行开始查询几行

-- offset 起始行数，从 0  开始计数，如果省略，默认就是 0
-- size 每页展示的记录数 

limit offset,size

公式（记住即可）

起始行数 start=(currPage-1)*size
- currPage 当前页数
- size 每页展示的记录数

注意：limit 语法是mysql的方言，其它数据库，对于分页有不同的实现方式。

找出当前页数和起始行数之间的规律

• 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

sql 语句的编写顺序

#sql 语句的编写顺序
SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排列顺序（asc|desc） LIMIT 起始行,查询行数

• 注意：将子查询 ”一行一列“的结果作为外部查询的条件，做第二次查询
• 子查询得到一行一列的结果才能作为外部查询的等值判断条件

多表查询

在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。

多表查询分类

• 连接查询
  • 内连接：相当于查询A、B交集部分数据
  • 外连接：
    1. 左外连接：查询左表所有数据，以及两张表交集部分数据
    2. 右外连接：查询右表所有数据，以及两张表交集部分数据
    3. 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

隐式内连接

SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;

显式内连接

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;

内连接案例

查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ;
-- 为每一张表起别名,简化SQL编写
select e.name,d.name from emp e , dept d where e.dept_id = d.id;

查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id

select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
-- 为每一张表起别名,简化SQL编写
select e.name, d.name from emp e join dept d on e.dept_id = d.id;

注意: 一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。

外连接

外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：

左外连接

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;

查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

注意:左连接查询是以左表为主表，依次向右匹配，匹配到返回结果，匹配不到则返回NULL值填充

右外连接

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;

查询emp表的所有数据, 和对应的部门信息
由于需求中提到，要查询emp的所有数据，所以是不能内连接查询的，需要考虑使用外连接查询。
表结构: emp, dept
连接条件: emp.dept_id = dept.id

select e.*, d.name from emp e left join dept d on e.dept_id = d.id;

注意右连接查询是以右表为主表，依次向左匹配，匹配到返回结果，匹配不到则返回NULL值填充

自连接

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;

而对于自连接查询，可以是内连接查询，也可以是外连接查询。

案例：

查询员工 及其 所属领导的名字
表结构: emp a , emp b

select a.name , b.name from emp a , emp b where a.managerid = b.id;

select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid =b.id;

注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。如果允许重复的值，请使用 UNION ALL。

SELECT * FROM 表名1 UNION SELECT * FROM 表名2
SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2

#合并两张表的结果（去除重复记录）
SELECT * FROM t1 UNION SELECT * FROM t2

#合并两张表的结果（保留重复记录）
SELECT * FROM t1 UNION ALL SELECT * FROM t2

注意： 合并结果的两张表，列数必须相同，列的数据类型可以不同
使用union合并结果集，会去除两张表中重复的数据

注意：

​1、UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名
​2、UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。union只关注数据类型，数据业务含义是否相同不管。

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

子查询分类

根据子查询结果不同，分为：
A. 标量子查询（子查询结果为单个值）
B. 列子查询(子查询结果为一列)
C. 行子查询(子查询结果为一行)
D. 表子查询(子查询结果为多行多列)

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
常用的操作符：= <> > >= < <=

A. 查询 “销售部” 的所有员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 “销售部” 部门ID
②. 根据 “销售部” 部门ID, 查询员工信息

1. 查询 “销售部” 部门ID

 select id from dept where name = '销售部';

2. 根据 “销售部” 部门ID, 查询员工信息

select * from emp where dept_id = (select id from dept where name = '销售部');

查询在 “方东白” 入职之后的员工信息
完成这个需求时，我们可以将需求分解为两步：
①. 查询 方东白 的入职日期
②. 查询指定入职日期之后入职的员工信息

1. 查询 方东白 的入职日期

select entrydate from emp where name = '方东白';

2. 查询指定入职日期之后入职的员工信息

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL

查询 “销售部” 和 “市场部” 的所有员工信息
分解为以下两步:
①. 查询 “销售部” 和 “市场部” 的部门ID
②. 根据部门ID, 查询员工信息

1. 查询 “销售部” 和 “市场部” 的部门ID

select id from dept where name = '销售部' or name = '市场部';

2. 根据部门ID, 查询员工信息

select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
①. 查询所有 财务部 人员工资
②. 比 财务部 所有人工资都高的员工信息

1. 查询所有 财务部 人员工资

select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');

2. 比 财务部 所有人工资都高的员工信息

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );

查询比研发部其中任意一人工资高的员工信息
分解为以下两步:
①. 查询研发部所有人工资
②. 比研发部其中任意一人工资高的员工信息

1. 查询研发部所有人工资

select salary from emp where dept_id = (select id from dept where name = '研发部');

2. 比研发部其中任意一人工资高的员工信息

select * from emp where salary > any ( select salary from emp where dept_id =
(select id from dept where name = '研发部') );

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN

查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:
①. 查询 “张无忌” 的薪资及直属领导
②. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

1. 查询 “张无忌” 的薪资及直属领导

select salary, managerid from emp where name = '张无忌';

2. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;

select * from emp where (salary,managerid) = (select salary, managerid from emp
where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN

查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
分解为两步执行:
①. 查询 “鹿杖客” , “宋远桥” 的职位和薪资
②. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

1. 查询 “鹿杖客” , “宋远桥” 的职位和薪资

select job, salary from emp where name = '鹿杖客' or name = '宋远桥';

2. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息

select * from emp where (job,salary) in ( select job, salary from emp where name =
'鹿杖客' or name = '宋远桥' );

查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息
分解为两步执行:
①. 入职日期是 “2006-01-01” 之后的员工信息
②. 查询这部分员工, 对应的部门信息;

1. 入职日期是 “2006-01-01” 之后的员工信息

select * from emp where entrydate > '2006-01-01';

2. 查询这部分员工, 对应的部门信息;

select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left
join dept d on e.dept_id = d.id ;

将子查询多行多列的结果作为外部查询的一张表，做第二次查询，

注意：子查询作为临时表，为其赋予一个临时表名，否则会报错

蠕虫复制

什么是蠕虫复制：在已有的数据基础之上，将原来的数据进行复制，插入到对应的表中
语法格式：INSERT INTO 表名1 SELECT * FROM 表名2;
作用:将表名2中的数据复制到表名1中
具体操作:

• 创建student2表，student2结构和student表结构一样

CREATE TABLE student2 LIKE student;

将student表中的数据添加到student2表中

INSERT INTO student2 SELECT * FROM student;

事务的概念

事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系
统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐
式的提交事务。

drop table if exists account;
create table account(
id int primary key AUTO_INCREMENT comment 'ID',
name varchar(10) comment '姓名',
money double(10,2) comment '余额'
) comment '账户表';
insert into account(name, money) VALUES ('张三',2000), ('李四',2000);

未控制事务

测试正常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。

测试异常情况

-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
出错了....
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';

检查最终的数据情况, 发现数据在操作前后不一致了

控制事务

查看/设置事务提交方式

SELECT @@autocommit ;
SET @@autocommit = 0 ;

开启事务

START TRANSACTION 或 BEGIN ;

提交事务

COMMIT;

回滚事务

ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提
交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

-- 开启事务
start transaction
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务
-- rollback;

注意:开启事务后，执行的语句均属于当前的事务，成功再执行commit,失败要进行rollback

事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立
  环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
  上述就是事务的四大特性，简称ACID。

并发事务问题

赃读

一个事务读到另外一个事务还没有提交的数据。

不可重复读

一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

幻读

一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离级别

为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意：事务隔离级别越高，数据越安全，但是性能越低。

DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

管理用户

查询用户

select * from mysql.user;

创建用户

CREATE USER 用户名 IDENTIFIED BY 密码

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

#创建一个zhangsan用户
CREATE USER `zhangshan` IDENTIFIED BY '123456'

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

删除用户

DROP USER 用户名
DROP USER `zhangshan`
DROP USER '用户名'@'主机名' ;

注意事项
在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
主机名可以使用 % 通配。

案例

1. 创建用户dyk, 只能够在当前主机localhost访问, 密码123456;

create user 'dyk'@'localhost' identified by '123456';

2. 创建用户dyk, 可以在任意主机访问该数据库, 密码123456;

create user 'dyk'@'%' identified by '123456';

3. 修改用户dyk的访问密码为1234;

alter user 'dyk'@'%' identified with mysql_native_password by '1234';

4. 删除 dyk@localhost 用户

drop user 'dyk'@'localhost';

权限控制

查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

GRANT ALL ON 数据库.表 TO 用户名
#将db3 下的所有表的权限都赋值给zhangshan

GRANT ALL ON db3.* TO `zhangshan` 

撤销用户权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

#将zhangshan的db3权限取消

REVOKE ALL ON db3.* FROM `zhangshan`;

注意:撤销权限后，账户要更新连接客户端才会生效

• 多个权限之间，使用逗号分隔
• 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。

案例

1. 查询 ‘heima’@’%’ 用户的权限

show grants for 'heima'@'%';

2. 授予 ‘heima’@’%’ 用户itcast数据库所有表的所有操作权限

grant all on itcast.* to 'heima'@'%';

3. 撤销 ‘heima’@’%’ 用户的itcast数据库的所有权限

revoke all on itcast.* from 'heima'@'%';

视图

视图，虚拟表，从一个表或多个表查询出来的表，作用和真实表一样包含一系列带有行和列的数据，视图中，用户可以使用select 语句查询数据，也可以使用INSERT ,UPDATE,DELETE修改记录,视图可以使用用户操作方便，并保障数据库系统安全

视图特点

优点
简单化，数据所见所得
安全性，用户只能查询或修改他们所能见到得到的数据
逻辑独立性，可以屏蔽真实表结构变化带来的影响
缺点
性能相对较差,简单的查询也变得稍微复杂
修改不方便，特别是复杂的聚合视图基本无法修改

视图的创建

  CREATE VIEW 视图名 AS 查询数据源表语句

创建视图

#创建视图
CREATE VIEW t_empinfo AS  SELECT * FROM t_employees;
#使用视图
SELECT * FROM t_empinfo

视图的修改

方式一 ： create or replace view
方式二 ：alter view 视图名 as 查询语句

修改视图

#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;

#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

删除视图

注意：删除视图不会影响原表

#删除t_empInfo视图
DROP VIEW t_empInfo;

在这里插入代码片

视图的注意事项

注意：

- 视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
- 如果视图包含以下结构中的一种，则视图不可更新
  - 聚合函数的结果
  - DISTINCT 去重后的结果
  - GROUP BY 分组后的结果
  - HAVING 筛选过滤后的结果
  - UNION、UNION ALL 联合后的结果

存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式 。存储引擎是基于表的，而不是
基于库的，所以存储引擎也可被称为表类型。我们可以在创建表的时候，来指定选择的存储引擎，如果
没有指定将自动选择默认的存储引擎

建表时指定存储引擎

CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE = INNODB [ COMMENT 表注释 ] ;

查询当前数据库支持的存储引擎

show engines;

存储引擎特点

InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。

InnoDB特点

• DML操作遵循ACID模型，支持事务；
• 行级锁，提高并发访问性能；
• 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

InnoDB逻辑存储结构

• 表空间 : InnoDB存储引擎逻辑结构的最高层，ibd文件其实就是表空间文件，在表空间中可以
  包含多个Segment段。
• 段 : 表空间是由各个段组成的， 常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管
  理，都是引擎自身完成，不需要人为对其控制，一个段中包含多个区。
• 区 : 区是表空间的单元结构，每个区的大小为1M。 默认情况下， InnoDB存储引擎页大小为
  16K， 即一个区中一共有64个连续的页。
• 页 : 页是组成区的最小单元，页也是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默
  认为 16KB。为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。
• 行 : InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的，在每一行中除了定义表时
  所指定的字段以外，还包含两个隐藏字段

MyISAM

MyISAM是MySQL早期的默认存储引擎

MyISAM特点

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

Memory

Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为
临时表或缓存使用。

Memory特点

• 内存存放
• hash索引（默认）

存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据
实际情况选择多种存储引擎进行组合。

• InnoDB: 是Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完
  整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是
  对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

面试题 InnoDB引擎与MyISAM引擎的区别 ?

①. InnoDB引擎, 支持事务, 而MyISAM不支持。
②. InnoDB引擎, 支持行锁和表锁, 而MyISAM仅支持表锁, 不支持行锁。
③. InnoDB引擎, 支持外键, 而MyISAM是不支持的。

索引

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

假如我们要执行的SQL语句为 ： select * from user where age = 45;

在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建立一个二叉树的索引结构。此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率

索引特点

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

二叉树

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

所以，如果选择二叉树作为索引结构，会存在以下缺点：

• 顺序插入时，会形成一个链表，查询性能大大降低。
• 大数据量情况下，层级较深，检索速度慢。

B-Tree

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。
以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5个指针：

特点：

• 5阶的B树，每一个节点最多存储4个key，对应5个指针。
• 一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。
• 在B树中，非叶子节点和叶子节点都会存放数据。

B+Tree

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一下其结构示意图：

• 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
• 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

B+Tree 与 B-Tree区别

最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

mysql中的B+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序

为什么InnoDB存储引擎选择使用B+tree索引结构?

A. 相对于二叉树，层级更少，搜索效率高；
B. 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
C. 相对Hash索引，B+tree支持范围匹配及排序操作；

索引分类

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

聚集索引&二级索引

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(ClusteredIndex)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(SecondaryIndex)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
②. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
③. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题

以下两条SQL语句，那个执行效率高? 为什么?
A. select * from user where id = 10 ;
B. select * from user where name = ‘Arm’ ;
备注: id为主键，name字段创建的有索引；

A 语句的执行性能要高于B 语句。
因为A语句直接走聚集索引，直接返回数据。 而B语句需要先查询name字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

InnoDB主键索引的B+tree高度为多高呢?

假设:
一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint，占用字节数为8。

高度为2：

n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170
1171* 16 = 18736

也就是说，如果树的高度为2，则可以存储 18000 多条记录。
高度为3：

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

索引语法

创建索引

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

CREATE INDEX idx_user_name ON tb_user(name);

phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

为profession、age、status创建联合索引

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

查看索引

SHOW INDEX FROM table_name ;

删除索引

 DROP INDEX index_name ON table_name ;