数据库MySQL面经——一篇掌握MySQL（MySQL详细知识点）

一、 数据库基础

为什么要使用数据库

数据保存在内存中——存取速度快，但是不能永久保存
数据保存在文件中——可以永久保存，但是存取速度慢

数据保存在数据库——可以永久保存（数据持久化），并且效率高、管理方便（例如：快速的检索等）

什么是数据库

DB：数据库（Database） 即存储数据的“仓库”。它保存了一系列有组织的数据。

DBMS：数据库管理系统（Database Management System）：是一种操纵和管理数据库的大型软件，例如建立、使用和维护数据库。

目前互联网上常见的数据库管理软件有Sybase、DB2、Oracle、MySQL、Access、Visual Foxpro（面向对象型）、MS SQL Server、Informix、PostgreSQL（最符合SQL标准，但是性能差）这几种。

MySQL（关系型数据库：ER模型与三范式）

MySQL是一种开放源代码的关系型数据库管理系统。

关系型数据库， 采用关系模型来组织数据，简单来说，关系模型指的就是二维表格模型。类似于Excel工作表。

非关系型数据库，可看成传统关系型数据库的功能阉割版本，基于键值对存储数据，通过减少很少用的功能，来提高性能。

关系型数据库设计规则： 遵循ER模型和三范式
E-R模型：
E entity 代表实体的意思 对应到数据库当中的一张表
R relationship 代表关系的意思

三范式：
第一范式：每个列都不可以再拆分 （列不能拆分）
eg：(人是一个完整的个体，无法拆分)

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不是依赖于主键的一部分。（允许传递依赖，不允许部分依赖）
eg：（父母(主键)两个人是一个整体，只有父亲或只有母亲是无法生出某个孩子(非主键)的）

第三范式：在第二范式的基础上，非主键只依赖于主键，不依赖于其他非主键（不允许传递依赖）
eg：（只满足两代血缘关系，不允许三代人，即表中只允许存在父母（主键）和孩子，不允许存在孙子）

什么是SQL
结构化查询语言（Structured Query Language）

MySQL的安装与使用：
https://blog.csdn.net/weixin_46369022/article/details/120973020?spm=1001.2014.3001.5501.

二、Mysql的逻辑架构与存储引擎

逻辑架构：

存储引擎

MySQL最重要、最与众不同的特性是它的存储引擎架构，这种架构的设计将查询处理（Query Processing）及其他系统任务（Server Task）和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性，以及其他需求来选择数据存储的方式。

MySQL中同一个数据库，不同的表格可以选择不同的存储引擎。

不同存储引擎的区别
存储引擎Storage engine： MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

要素：是否提供事务支持、行级锁、外键

MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用。每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
总结：
不提供事务支持，也不支持行级锁和外键——速度快，安全性低。
存储空间小，可以被压缩。
每个MyISAM磁盘上存储成三个文件。
适合查询、插入为主的应用。

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保存数据和索引。
InnoDB：所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小限制。Myisam只缓存索引，不缓存真实数据；Innodb不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响。
总结：
提供了对数据库ACID事务的支持，提供行级锁、外键的约束。处理大容量数据库有优势——速度低、安全性高。
存储空间大，因为它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
每个InnoDB在磁盘上存储成两个文件。
适合频繁修改以及涉及到安全性较高的应用

MEMORY存储引擎使用存在于内存中的内容来创建表。MEMORY类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失。主要用于那些内容变化不频繁的代码表或者作为统计操作的中间结果表。

MyISAM和InnoDB

关于 MyISAM 和 InnoDB 的选择问题

三、MySQL的数据类型

整数（xxxint）

整数列的可选属性有三个：
1、M: 宽度(在0填充的时候才有意义，否则不需要指定)
2、unsigned: 无符号类型(非负)
3、zerofill: 0填充,(如果某列是zerofill，那么默认就是无符号)，如果指定了zerofill只是表示不够M位时，用0在左边填充，如果超过M位，只要不超过数据存储范围即可。
在 int(M) 中，M 的值跟 int(M) 所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 bytes 的存储空间。

浮点型

对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节

1、MySQL允许使用非标准语法（其他数据库未必支持，因此如果设计到数据迁移，则最好不要这么用）：
FLOAT(M,D)或DOUBLE(M,D)。这里，(M,D)表示该值一共显示M位，其中D表示小数点后几位，M和D又称为精度和标度。例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。M取值范围为0~ 255。D取值范围为0~30，同时必须<=M。

2、如果存储时，整数部分超出了范围（如上面的例子中，添加数值为1000.01），MySql就会报错，不允许存这样的值。如果存储时，小数点部分若超出范围，就分以下情况：若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存，例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。若四舍五入后，整数部分超出范围，则MySql报错，并拒绝处理。如999.995和-999.995都会报错。

3、说明：小数类型，也可以加unsigned，但是不会改变数据范围，例如：float(3,2) unsigned仍然只能表示0-9.99的范围。

4、float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示

5、REAL就是DOUBLE ，如果SQL服务器模式包括REAL_AS_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

注意：在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用“=”来判断两个数是否相等。如果希望保证值比较准确，推荐使用定点数数据类型。

日期时间类型

1、对于year类型，可以输入4位数，例如2018，也可以输入两位数，例如18，如果输入的是两位，“00-68”表示2000-2069年，“70-99”表示1970-1999年。

2、‘YYYY-MM-DD HH:MM:SS’或’YY-MM-DD HH:MM:SS’，'YYYY-MM-DD’或’YY-MM-DD’格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，‘98-12-31 11:30:45’、‘98.12.31 11+30+45’、'98/12/31 113045’和’98@12@31 11^ 30^45’是等价的。

3、'YYYYMMDD’或’YYMMDD’格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，‘19970523’和’970523’被解释为 ‘1997-05-23’，但’971332’是不合法的(它有一个没有意义的月和日部分)，将变为’0000-00-00’。

4、对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数。'1979-6-9’与’1979-06-09’是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。'1979-10-30 1:2:3’与’1979-10-30 01:02:03’相同。

5、数字值应为6、8、12或者14位长。如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。如果数字 是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

6、一般存注册时间、商品发布时间等，不建议使用datetime存储，而是使用时间戳，因为datetime虽然直观，但不便于计算。而且timestamp还有一个重要特点，就是和时区有关。还有如果插入NULL，会自动设置为当前系统时间。

字符串型

char,varchar,text区别

char是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
1、char如果不指定(M)则表示长度默认是1个字符。varchar必须指定(M)。

2、char(M) 类型的数据列里，无论存入的数据长度是多少，存入的都是M个字符所占用的长度字节。定义一个char[4]，写入字符“ab”，那么这个char所占的字符长度依然是4，会在不足长度的后面自动用空格补齐。（时间换空间）

而对于varchar(M) 类型的数据列里，varchar的字符长度就是“ab”两个字符长度。最终存储的字节长度是字符所占用的字节长度加上一到两个记录其长度的字节（即总长度为L字符所占字节数+1/2字字节）。（空间换时间）

（4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；5.0版本以上，varchar(20)，指的是20字符）
总结：（M）限制的是存入的字符个数，而不是字节数

3、由于某种原因char 固定长度，所以在处理速度上要比varchar快速很多，但相对费存储空间，所以对存储不大，但在速度上有要求的可以使用char类型，反之可以用varchar类型来实例。

4、text文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用char，varchar来代替。还有text类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含text类型字段，建议单独分出去，单独用一个表。

补：char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

哪些情况使用char或varchar更好

1、存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。

2、固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。

3、十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

4、InnoDB存储引擎，建议使用varchar类型，因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。

枚举（ENUM）

MySql中的ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值：
把不重复的数据存储为一个预定义的集合

四、MySQL的运算符

（1）算术运算符：+ - * /（除也可以写成div，div取整) ； %（取模可以写成mod）
div也表示除，但是只保留整数部分。

关于+，在Java中，+的左右两边如果有字符串，那么表示字符串的拼接,但是在MySQL中+只表示数值相加，如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。

（2）比较运算符：= > >= < <= !=（不等于还可以写成<>）； <=>（安全等于）

（3）逻辑运算符：&&（逻辑与也可以写成and）； ||（逻辑或也可以写成or）； not（逻辑非） ；xor（逻辑异或）

（4）范围： 表达式 between … and … （也可以写成 表达式>=… and 表达式 <=…）
表达式 not between … and …（也可以写成 表达式<… || 表达式 >…）

（5）集合：in (值，值，值…)； not in(值，值，值…)

（6）模糊查询： LIKE NOT LIKE，通配符：%表示0-n个字符，_下划线代表一个字符

like 和 通配符 一起使用
like _ 匹配单个字符
like % 匹配任意个字符

（7）位运算符：&（按位与）； |（按位或）；^（按位异或）；~（按位取反）；>>（右移）；<<（左移）

（8）NULL值判断，is null 或 is not null，如果使用null=null，null<>null,null=0,null<>0,null=false等都不对
不过xxx is null 可以使用xxx <=> null ，xxx is not null 可以写成 not xxx <=> null

函数：IFNULL(表达式,用什么值代替)

结论：所有的运算符遇到NULL结果都是NULL，除了<=>

五、SQL

SQL：Structure Query Language结构化查询语言，它是使用关系模型的数据库应用语言.

SQL的语言规范
1、mysql对于SQL语句不区分大小写，SQL语句关键字尽量大写
2、值，除了数值型，字符串型和日期时间类型使用单引号（’’）
3、别名，尽量使用双引号（“”），而且不建议省略as
4、所有标点符号使用英文状态下的半角输入方式
5、必须保证所有(),单引号，双引号是成对结束的
6、可以使用（1）#单行注释 （2）- -空格单行注释 （3）/* 多行注释 */

命名规则：
1、数据库、表名不得超过30个字符，变量名限制为29个
2、必须只能包含 A–Z, a–z, 0–9, _共63个字符
3、不能在对象名的字符间留空格
4、必须不能和用户定义的其他对象重名
5、必须保证你的字段没有和保留字、数据库系统或常用方法冲突
6、保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数,那在另一个表里可就别变成字符型了

在命令行中：一个语句可以分开多行编写，以;或\g结束

SQL分类

SQL的分类：
1、DDL（Data Definition Languages)：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。
主要的语句关键字包括create、drop、alter等。

2、DML（Data Manipulation Language）：数据操作语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
主要的语句关键字包括insert、delete、update、select等。

3、DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括grant、revoke等。

DDL

操作Database

1、创建数据库

create database 数据库名 [charset 字符集];   (关键字大写效果：CREATE DATABASE 数据库名;)
如果不指定字符集，则按照安装mysql服务时选择的默认字符集。

2、查看有哪些数据库

show databases;
提示：当前用户有权限查看的

3、删除数据库

drop database 数据库名;

4、选择数据库

use 数据库名;

5、查看当前正在使用哪个数据库

select database();

表结构的操作TABLE

注意：要操作表格和数据之前必须先说明是对哪个数据库进行操作，否则就要对所有对象加上“数据库名.”。
1、查看当前数据库的所有表格

show tables;   
//前面必须有use 数据库名语句，否则报错ERROR 1046 (3D000): No database selected
show tables from 数据库名;

2、创建表结构
CREATE TABLE 表名称(
字段名1 数据类型1 主键 自增长,
字段名2 数据类型2 非空 默认值,
字段名3 数据类型3
)ENGINE=当前表格的引擎 AUTO_INCREMENT=自增长的起始值 DEFAULT CHARSET=表数据的默认字符集;

CREATE TABLE t_stu(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL DEFAULT '男'
)ENGINE=INNODB 
AUTO_INCREMENT=1 
DEFAULT CHARSET=utf8;

3、查看表结构

desc 表名称;
查看表的定义：SHOW CREATE TABLE 表名;

4、删除表结构

drop table 表名称;

注意：
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE 语句不能回滚

truncate和drop是DDL语句不能回滚，delete是DML语句，可以回滚。

5、修改表结构
（1）重命名表

alter table 表名 rename 新表名;
rename table 表名 to 新表名; 

（2）增加一列

alter table 表名 add 【column】 列名 数据类型 【default 默认值】【not null】;  #默认在最后
alter table 表名 add 【column】 列名 数据类型 【default 默认值】【not null】 after 某一列;
alter table 表名 add 【column】 列名 数据类型 【default 默认值】【not null】 first;

（3）删除列

alter table 表名 drop 【column】 列名;

（4）修改列类型

alter table 表名 modify 【column】 列名 数据类型【default 默认值】【not null】;
alter table 表名 modify 【column】 列名 数据类型【default 默认值】【not null】 after 某一列;
alter table 表名 modify 【column】 列名 数据类型【default 默认值】【not null】 first;

（5）修改列名等

alter table 表名 change 【column】 列名 新列名 数据类型【default 默认值】【not null】;

约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：
1、实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
2、域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
3、引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
4、用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

主键约束PRIMARY KEY

主键：Primary key，简称PK，数据库主键作用保证实体的完整性，可以是一个列或多列的组合。

1、主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

2、每个表有且最多只允许一个主键约束。

3、当创建主键约束时，MySQL默认在对应的列上建立主键索引。删除主键时，也会直接删除主键索引。

建立主键约束：

//在主键列后面直接加主键约束，复合主键不能使用这种方式
CREATE TABLE t_stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(100),
	gender CHAR
);
//单独声明主键约束
CREATE TABLE t_course(
	cid INT ,
	cname VARCHAR(100),
	decription VARCHAR(200),
	PRIMARY KEY(cid)
);
//声明复合主键，复合主键只能使用这种方式
CREATE TABLE t_stu_course(
	sid INT,
	cid INT,
	score DOUBLE(4,1),
	PRIMARY KEY(sid,cid)
);

建表后添加主键约束:

alter table 表名称 add 【constraint 约束名】 primary key (字段名);
alter table 表名称 add 【constraint 约束名】 primary key (字段名1，字段名2);

删除主键和对应的索引

//删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在
alter table 表名称 drop primary key;

唯一键约束Unique key

1、同一个表可以有多个唯一约束。

2、唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。

3、MySQL会给唯一约束的列上默认创建一个唯一索引。

4、删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

建立唯一性约束

//在某个列后面直接加唯一性约束
CREATE TABLE t_course(
	cid INT PRIMARY KEY,
	cname VARCHAR(100) UNIQUE,
	description VARCHAR(200)
);
//单独指定表的唯一性约束
CREATE TABLE t_stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(100),
	card_id CHAR(18),
	CONSTRAINT uk_card_id UNIQUE KEY(card_id)
);
//其中CONSTRAINT uk_cname和KEY可以省略

//组合列唯一性约束
CREATE TABLE t_stu_course(
	id INT PRIMARY KEY,
	sid INT,
	cid INT,
	score DOUBLE(4,1),
	CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid)
);
//其中CONSTRAINT uk_sid_cid和KEY可以省略

建表后增加唯一性约束

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名);
alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名1，字段名2);

删除唯一性约束和索引

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
//注意：如果忘记名称，可以通过“show index from 表名称;”查看

主键和唯一键的区别：
（1）都限定了某一列或某几列是的值是唯一的。
（2）主键是非空，唯一键允许空
（3）主键一个表只能一个，唯一键可以有多个

外键约束Foreign key

1、外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

2、在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

3、当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。

4、删除外键时，关于外键列上的普通索引需要单独删除。

注意：
1、在从表上建立外键，而且主表要先存在。

2、从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。

3、从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

4、一个表可以建立多个外键约束

5、从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。（主表约束从表添加——“孩子不能凭空出来，得看是否有父母”）

6、当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。（从表约束主表删除更新——“父母不能抛弃孩子”）

默认情况下，主表和从表是严格依赖关系RESTRICT。
当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据（在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的。拒绝对父表的删除或更新操作。)

但是有一种是级联“修改、删除”：
ON DELETE SET NULL(级联置空)： 当外键设置了SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行。
ON DELETE CASCADE(级联删除)： 当外键设置了CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。
所以，对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

7、如果要删除表，需要先删除从表，才能删除主表

建立外键约束

CREATE TABLE t_department(
	did INT PRIMARY KEY,
	dname VARCHAR(100) NOT NULL UNIQUE,
	description VARCHAR(200) NOT NULL
);
CREATE TABLE t_employee(
	eid INT PRIMARY KEY,
	ename VARCHAR(100) NOT NULL,
	dept_id INT,
	CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT
);
//其中CONSTRAINT fk_emp_dept_did可以省略
//ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

一个表可以有多个外键，而且主表和从表可以是一张表

CREATE TABLE t_emp(
	eid INT PRIMARY KEY,
	ename VARCHAR(100) NOT NULL,
	manager_id INT,
	dept_id INT,
	CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did)  ON UPDATE CASCADE ON DELETE RESTRICT,
	CONSTRAINT fk_emp_mid_eid FOREIGN KEY(manager_id) REFERENCES t_emp(eid)  ON UPDATE CASCADE ON DELETE RESTRICT
);
//其中CONSTRAINT fk_emp_dept_did可以省略
//ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

建表后创建外键

alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);
ALTER TABLE t_emp ADD CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did)  ON UPDATE CASCADE ON DELETE RESTRICT;
//其中CONSTRAINT fk_emp_dept_did可以省略
//ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

删除外键约束

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;
ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;

查看约束名

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

删除外键列上的索引——需要单独删除

ALTER TABLE 表名称 DROP INDEX 外键列索引名; 
ALTER TABLE t_emp DROP INDEX dept_id;

查看索引名

show index from 表名称;

为什么不推荐使用外键约束

总结：某个表的外键是另一个表的主键

在高并发的情况下，级联更新会造成大量的更改操作，无疑会造成数据的压力过大，性能下降。

非空约束

NOT NULL 非空约束，规定某个字段不能为空

CREATE TABLE t_stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(100) NOT NULL
);

如果某列已经创建好，那么可以修改列语句修改：
例如：原来非空，修改为允许空

ALTER TABLE ceshi_table MODIFY des VARCHAR(20);

例如：原来允许空，修改为非空

ALTER TABLE ceshi_table MODIFY des VARCHAR(20) NOT NULL;

检查约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；
例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

CREATE TABLE t_stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL CHECK(gender IN('男','女'))
);

Default缺省约束

default：默认值，在插入数据时某列如果没指定其他的值,那么会将默认值添加到新记录。

CREATE TABLE t_stu(
	sid INT PRIMARY KEY,
	sname VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL CHECK(gender IN('男','女'))
);

如果某列已经创建好，那么可以修改列语句修改：
例如：原来有默认值，去除默认值

ALTER TABLE ceshi_table MODIFY des VARCHAR(20);

例如：原来没有默认值，增加默认值

ALTER TABLE ceshi_table MODIFY des VARCHAR(20) DEFAULT '略';

索引：INDEX

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念。

例如：一本字典，如何快速找到某个字，可以给字典加目录，对数据库来说,索引的作用即是给"数据"加目录。
设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢。如果是btree(二叉树)索引，，如果是hash(哈希)索引，时间复杂度是1。

索引好处： 加快了查询速度(select )
索引坏处： 虽然降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)

MySQL提供多种索引类型供选择：
1、普通索引：基本索引类型，没有唯一性限制，允许为NULL

2、唯一性索引：数据列不允许重复，允许为NULL、一个表允许多个列创建唯一索引

3、主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键

4、全文索引：MySQL5.X版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。

MySQL的索引方法：
1、HASH
2、BTREE
MySQL中多数索引都以BTREE的形式保存。

索引的使用原则：
（1）不过度索引
（2）索引条件列(where后面最频繁的条件比较适宜索引)
（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

自增列：AUTO_INCREMENT

CREATE TABLE t_stu(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL DEFAULT '男',
	birthday DATE,
	address VARCHAR(200)
);

关于自增长auto_increment：
（1）整数类型的字段才可以设置自增长。
（2）当需要产生唯一标识符或顺序值时，可设置自增长。
（3）一个表最多只能有一个自增长列
（4）自增长列必须非空
（5）自增长列必须是主键列或唯一键列。
InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

DML

数据操纵语言（DML）DML用于插入、修改、删除数据记录，包括如下SQL语句：
INSERT：添加数据到数据库中
UPDATE：修改数据库中的数据
DELETE：删除数据库中的数据

插入数据

语法

INSERT INTO 表名称  VALUES(值1，值2，......);
INSERT INTO 表名称  VALUES(值1，值2，......),(值1，值2，......),...;
INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......);
INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......),(值1，值2，......),.....;

说明
1、值列表(值1，值2，…)的顺序、个数与字段列表(字段1，字段2，…) 中字段的顺序、个数一致
（1）如果个数少了就报Column count doesn’t match value count
（2）如果VALUES前面的()中没有列出字段，那么默认就是为表中的所有字段赋值，那么个数与顺序与表结构中字段定义的一致。

2、关于自增长列、默认值列、允许为NULL列的赋值
（1）如果字段列表列出了字段名，那么值列表中就要为其赋值，哪怕它是自增长列，有默认值列，允许为NULL的列。

• InnoDB表的自动增长列可以手动插入合适的值，但是插入的值如果是NULL或者0，则实际插入的将是自动增长后的值；
• 如果列声明了“默认约束”那么对应的位置可以赋值具体的值，也可以使用“DEFAULT”，表示使用默认值；
• 如果列允许了NULL值，那么可以为对应的字段赋值为具体值或赋值为NULL

（2）对于没有列出的字段，自增列就自动赋值，默认值列就自动赋默认值，允许NULL的列就自动赋NULL值，非空列且没有提供默认值会自动赋值为对应数据类型的默认值，例如字符串赋值为空字符串，int赋值为0；

3、VALUES也可以写成VALUE，但是VALUES是标准写法
4、可以同时插入多行
5、如果插入从表的数据，要注意查看主表参照字段的值是否存在
（主表约束从表添加——“孩子不能凭空出来，得看是否有父母”）
插入主表的数据，不用考虑从表。
6、值的位置可以是常量值、表达式、函数

示例

CREATE TABLE t_department(
	did INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(100) NOT NULL,
	description VARCHAR(200),
	manager_id INT
);

INSERT INTO t_department(dname,description)
VALUES('教学部','技术培训'),('咨询部','课程咨询服务');

CREATE TABLE `t_job` (
  `job_id` INT(11) PRIMARY KEY AUTO_INCREMENT,
  `job_name` VARCHAR(100) DEFAULT NULL,
  `description` VARCHAR(200) DEFAULT NULL
);

INSERT INTO t_job VALUES
(NULL,'JavaSE讲师','Java基础'),(NULL,'Web讲师','Web基础'),
(NULL,'JavaEE框架','框架讲解'),(NULL,'课程顾问','课程咨询');

CREATE TABLE t_employee(
	eid INT PRIMARY KEY AUTO_INCREMENT,
	ename VARCHAR(100) NOT NULL,
	gender CHAR NOT NULL DEFAULT '男',
	card_id CHAR(18) UNIQUE,
	tel CHAR(11),
	job_id INT,
	`mid` INT,
	birthday DATE,
	hiredate DATE,	
	address VARCHAR(100),	
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES t_department(did),
	FOREIGN KEY (job_id) REFERENCES t_job(job_id)
);
INSERT  INTO `t_employee`(`eid`,`ename`,`gender`,`card_id`,`tel`,`job_id`,`mid`,`birthday`,`hiredate`,`address`,`dept_id`)
 VALUES (1,'孙红雷','男','123456789012345678','12345678901',1,NULL,'1990-01-01','2015-01-01','白庙',1),
 (2,'张亮','男','123456789012345677','12345678902',2,NULL,'1990-01-02','2015-01-02','天通苑北',1),
 (3,'鹿晗','男','123456789012345676','12345678903',3,NULL,'1990-01-03','2015-01-03','北苑',1),
 (4,'邓超','男','123456789012345675','12345678904',2,NULL,'1990-01-04','2015-01-04','和谐家园',1),
 (5,'孙俪','女','123456789012345674','12345678905',3,NULL,'1990-01-05','2015-01-05','霍营',1),
 (6,'Angelababy','女','123456789012345673','12345678906',4,NULL,'1990-01-06','2015-01-06','回龙观',2);

CREATE TABLE t_salary(
	eid INT PRIMARY KEY,
	basic_salary DECIMAL(10,2),
	performance_salary DECIMAL(10,2),
	commission_pct DECIMAL(10,2),
	deduct_wages DECIMAL(10,2),
	FOREIGN KEY (eid) REFERENCES t_employee(eid)
);
INSERT  INTO `t_salary`(`eid`,`basic_salary`,`performance_salary`,`commission_pct`,`deduct_wages`) 
VALUES (1,'12000.00','6000.00','0.40','0.00'),
(2,'9000.00','5000.00','0.20',NULL),(3,'11000.00','8000.00',NULL,NULL),
(4,'13000.00','5000.00',NULL,NULL),(5,'8000.00','8000.00','0.30',NULL),
(6,'15000.00','6000.00',NULL,NULL);

修改数据

1、语法

UPDATE 表名称 SET 字段名1 = 值1, 字段名2=值2,..【WHERE  条件】; 
UPDATE 表1，表2,.. SET 表1.字段名1 = 值1, 表1.字段名2=值2,表2.字段1 = 值1, 表2.字段2=值2...【WHERE  条件】; 

2、说明

1、如果不写where条件，会修改所有行
2、值可以是常量值、表达式、函数
3、可以同时更新多张表
如果两个表没有建立外键，但逻辑上有外键关系
4、如果修改从表外键字段的数据，要注意查看主表参照字段的值是否存在
5、如果修改主表的被参考的字段的值，要注意查看从表的外键是否有依赖该值，如果有：
（1）如果外键是on update RESTRICT或on update NO ACTION，那么要先处理从表的数据，才能修改
（2）如果外键是on update SET NULL 或 on update CASCADE，那么直接修改，从表的外键字段会自动处理

3、示例
修改"邓超"的入职日期为今天

UPDATE t_employee SET hiredate = CURDATE() WHERE ename ='邓超';

修改教学部的主管编号，以及该部门所有员工的领导编号为"邓超"的编号

//修改教学部的主管编号，以及该部门所有员工的领导编号为"邓超"的编号
UPDATE t_department,t_employee
SET t_department.manager_id =t_employee.eid
WHERE t_department.`dname` = '教学部' 
 AND t_department.`did` = t_employee.`dept_id` 
 AND t_employee.ename = '邓超';
 
UPDATE t_employee,t_department 
SET t_employee.mid = t_department.manager_id
WHERE t_employee.dept_id = t_department.did
 AND t_department.`dname` = '教学部';

删除数据

1、语法

delete from 表名 【where 条件】;
delete 表1，表2,... from 表1，表2，... 【where 条件】;

2、说明
1、如果不加where条件，表示删除整张表的数据，表结构保留。
delete from 表名;
删除整张表的数据还可以使用truncate 表名;
区别：
truncate相当于删除表再重建一张同名结构的表，操作后得到一张全新表，而delete是在原有表中删除数据。如果决定清空一张表的数据，truncate速度更快一些。
TRUNCATE语句不能回滚

2、如果删除主表的记录，要注意查看从表的外键是否有依赖该行的值，如果有
（1）如果外键是on delete RESTRICT或on delete NO ACTION，那么要先处理从表的数据，才能删除
（2）如果外键是on delete SET NULL 或 on delete CASCADE，那么删除时从表的对应记录也会被置空或跟着删除

3、可以一次删除多个表的数据
例如：两个表没有建立外键，但逻辑上有外键关系，也可以通过删除多个表的数据来实现级联删除

3、示例
删除学号为9的学生信息

DELETE FROM t_stu WHERE sid = 9;

删除所有“教学部”的员工信息和薪资信息和“教学部”部门信息

DELETE t_employee,t_department,t_salary
FROM t_employee,t_department,t_salary
WHERE t_department.`dname` ='教学部' 
 AND t_employee.`dept_id`=t_department.`did`
 AND t_employee.`eid` = t_salary.eid;

查询（重点）

1、语法

SELECT 查询列表
  FROM 表名或视图列表
  【WHERE 条件表达式】
  【GROUP BY 字段名 【HAVING 条件表达式】】
  【ORDER BY 字段 【ASC|DESC】】
  【LIMIT m,n】;

查询表中的所有行所有列
使用*表示，查询所有字段，即查询所有行

select * from t_stu;

查询部分字段，查询部分列

select sname,major from t_stu;

查询所有列，部分行

select * from t_stu where major = 'JavaEE';

查询部分行，部分列

select sname,major from t_stu where major = 'JavaEE';

说明：
（1）如果SELECT后面是*，那么表示查询所有字段
（2）SELECT后面的查询列表，可以是表中的字段，常量值，表达式，函数
（3）查询的结果是一个虚拟的表
（4）select语句，可以包含5种子句：依次是where、 group by、having、 order by、limit必须照这个顺序。

2、别名AS
说明：
（1）可以给字段取别名、可以给表名取别名
（2）AS 可以省略
（3）如果给字段取别名，如果别名中包含特殊符号，例如“空格”等，建议给别名加上双引号或单引号
（4）如果是给表名取别名，那么不能加双引号或单引号，也不能有特殊符号，例如“空格”等
（5）建议别名简短，见名知意

 UPDATE t_department AS d,t_employee AS e
SET d.manager_id =e.eid
WHERE d.dname = '教学部' 
 AND d.did = e.`dept_id` 
 AND e.ename = '孙红雷';
//查询员工姓名以及手机号码
SELECT ename AS '员工姓名',tel AS '手机号码'
FROM t_employee;

3、去重DISTINCT

//查询员工表的部门编号
SELECT DISTINCT dept_id FROM t_employee;

//统计员工表中员工有几个部门
SELECT COUNT(DISTINCT dept_id) FROM t_employee;  

联合查询

作用:从2张或多张表中,取出有关联的数据.

关联查询一共有几种情况：
1、内连接：INNER JOIN 、CROSS JOIN
2、外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）
3、自连接：当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义

1、关联条件
表连接的约束条件可以有三种方式：WHERE, ON, USING

WHERE：适用于所有关联查询

//把关联条件写在where后面
SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did;

ON：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。

//把关联条件写在on后面，只能和JOIN一起使用
SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;
SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;

USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等

//把关联字段写在using()中，只能和JOIN一起使用
//而且两个表中的关联字段必须名称相同，而且只能表示=
//查询员工姓名与基本工资
SELECT ename,basic_salary FROM t_employee INNER JOIN t_salary USING(eid);

2、笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数.
在MySQL中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确
注：外连接必须写关联条件，否则报语法错误

//笛卡尔积
//查询员工姓名和所在部门名称
SELECT ename,dname FROM t_employee,t_department;
SELECT ename,dname FROM t_employee INNER JOIN t_department;
SELECT ename,dname FROM t_employee CROSS JOIN t_department;
SELECT ename,dname FROM t_employee JOIN t_department;

3、说明：
（1）连接 n个表,至少需要 n-1个连接条件。 例如：连接三个表，至少需要两个连接条件。

（2）当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分

（3）当如果表名比较长时，可以给表取别名，简化SQL语句

内连接（INNER JOIN）

返回连接表中符合连接条件和查询条件的数据行

隐式：
SELECT [cols_list] from 表1,表2 where [condition]

//查询员工姓名，基本工资，部门名称
SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary 
WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

显式：
SELECT [cols_list] from 表1 INNER JOIN 表2 ON [关联条件] where [其他筛选条件]

//查询员工姓名，基本工资，部门名称
SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary 
ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

SELECT [cols_list] from 表1 CROSS JOIN 表2 ON [关联条件] where [其他筛选条件]

SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did;

SELECT [cols_list] from 表1 JOIN 表2 ON [关联条件] where [其他筛选条件]

SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did;

外连接（OUTER JOIN）

A LEFT/RIGHT JOIN B: 无论是左连接还是右连接，重点都是保留JOIN右边的表B的完整信息，左右连接的作用是相同的。

左连接（LEFT JOIN）

连接表在被连接的表的左边。
Case1、返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列返回空值。

//查询所有部门信息以及该部门员工信息
SELECT did,dname,eid,ename 
FROM t_department LEFT OUTER JOIN t_employee
ON t_department.did = t_employee.dept_id;

//查询所有员工信息，以及员工的部门信息
SELECT eid,ename,did,dname
FROM t_employee LEFT OUTER JOIN t_department
ON t_employee.dept_id = t_department.did ;

Case2、返回左边中行在右表中没有匹配行的记录

//查询部门信息，仅保留没有员工的部门信息
SELECT did,dname,eid,ename 
FROM t_department LEFT OUTER JOIN t_employee
ON t_department.did = t_employee.dept_id
WHERE t_employee.dept_id IS NULL;

//查询员工信息，仅保留没有分配部门的员工
SELECT eid,ename,did,dname
FROM t_employee LEFT OUTER JOIN t_department
ON t_employee.dept_id = t_department.did 
WHERE t_employee.dept_id IS NULL;

右连接（RIGHT JOIN）

连接表在被连接的表的右边。

Case1、返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列返回空值

//查询所有部门信息以及该部门员工信息
SELECT did,dname,eid,ename 
FROM t_employee RIGHT OUTER JOIN t_department
ON t_department.did = t_employee.dept_id;

//查询所有员工信息，以及员工的部门信息
SELECT eid,ename,did,dname
FROM t_department RIGHT OUTER JOIN t_employee
ON t_employee.dept_id = t_department.did ;

Case2、返回右表中在左表没有匹配行的记录

//查询部门信息，仅保留没有员工的部门信息
SELECT did,dname,eid,ename 
FROM t_employee RIGHT OUTER JOIN t_department
ON t_department.did = t_employee.dept_id
WHERE t_employee.dept_id IS NULL;

//查询员工信息，仅保留没有分配部门的员工
SELECT eid,ename,did,dname
FROM t_department RIGHT OUTER JOIN t_employee
ON t_employee.dept_id = t_department.did 
WHERE t_employee.dept_id IS NULL;

全连接（FULL JOIN）

mysql不支持FULL JOIN，但是可以用 left join union right join代替

Case1、A∪B

//查询所有部门信息和员工信息
SELECT did,dname,eid,ename 
FROM t_department LEFT OUTER JOIN t_employee
ON t_department.did = t_employee.dept_id
UNION 
SELECT did,dname,eid,ename 
FROM t_department RIGHT OUTER JOIN t_employee
ON t_department.did = t_employee.dept_id;

Case2、A∪B - A∩B

//查询所有没有员工的部门和没有分配部门的员工
SELECT did,dname,eid,ename 
FROM t_department LEFT OUTER JOIN t_employee
ON t_department.did = t_employee.dept_id
WHERE t_employee.dept_id IS NULL
UNION
SELECT did,dname,eid,ename 
FROM t_employee LEFT OUTER JOIN t_department
ON t_department.did = t_employee.dept_id
WHERE t_employee.dept_id IS NULL;

自连接

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询

//自连接
//查询员工姓名以及领导姓名，仅显示有领导的员工
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp, t_employee AS mgr
WHERE emp.mid = mgr.eid;

//查询员工姓名以及领导姓名，仅显示有领导的员工
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp INNER JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

//查询所有员工姓名及其领导姓名
SELECT emp.ename,mgr.ename 
FROM t_employee AS emp LEFT JOIN t_employee AS mgr
ON emp.mid = mgr.eid;

select的5个子句（group by;having;order by…）

1、where条件查询
从原表中的记录中进行筛选

2、group by 分组查询
很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每一个部门的人数等。
聚合函数
AVG(【DISTINCT】 expr) 返回expr的平均值
COUNT（【DISTINCT】 expr）返回expr的非NULL值的数目
MIN(【DISTINCT】 expr)返回expr的最小值
MAX(【DISTINCT】 expr)返回expr的最大值
SUM（【DISTINCT】 expr）返回expr的总和

group by + 聚合函数

//group by + 聚合函数
//统计每个部门的人数
SELECT dept_id,COUNT(*) FROM t_employee
GROUP BY dept_id;

//统计每个部门的平均基本工资
SELECT emp.dept_id,AVG(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

//统计每个部门的年龄最大者
SELECT dept_id,MIN(birthday) FROM t_employee GROUP BY dept_id;

//统计每个部门基本工资最高者
SELECT emp.dept_id,MAX(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

//统计每个部门基本工资之和
SELECT emp.dept_id,SUM(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

注意：
count( * ),count(1)作用都是是统计记录数,谁好呢?
对于myisam引擎的表, 是没有区别的.这种引擎内部有一计数器在维护着行数.
Innodb的表,用count ( * )直接读行数,效率很低,因为innodb要去遍历一遍.

关于mysql的group by的特殊：
注意：在SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT列表中最好不要出现GROUP BY子句中没有的列。

例如下面这个查询案例，通过查询结果很容易让人产生误解，统计的数量是种类的数量，而第一列name却只显示了第一种。

正确使用如下：

3、having 筛选

having与where类似,可筛选数据
having与where不同点：
（1）where针对表中的列发挥作用,查询数据；having针对查询结果中的列发挥作用,筛选数据
（2）where后面不能写分组函数，而having后面可以使用分组函数
（3）having只用于group by分组统计语句

//按照部门统计员工人数，仅显示部门人数少于3人的
SELECT dept_id,COUNT(*) AS c 
FROM t_employee 
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING c < 3;
//查询每个部门的平均工资，并且仅显示平均工资高于10000
SELECT emp.dept_id,AVG(s.basic_salary ) AS avg_salary
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid AND dept_id IS NOT NULL
GROUP BY emp.dept_id
HAVING avg_salary >10000;

4、order by 排序
（1）按一个或多个字段对查询结果进行排序
用法：order by col1,col2,col3…
说明：先按col1排序如果col1相同就按照col2排序，依次类推
col1，col2，col3可以是select后面的字段也可以不是
（2）默认是升序，也可以在字段后面加asc显示说明是升序，desc为降序
例如：order by click_count desc;
如果两个字段排序不一样，例如：
order by 字段1 asc ,字段2 desc;
（3）order by 后面除了跟1个或多个字段，还可以写表达式，函数，别名等

//查询员工基本工资，按照基本工资升序排列，如果工资相同，按照eid升序排列
SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary
ON t_employee.eid = t_salary.eid
ORDER BY basic_salary,eid;

//查询员工基本工资，按照基本工资降序排列，如果工资相同，按照eid排列
SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary
ON t_employee.eid = t_salary.eid
ORDER BY basic_salary DESC,eid;

//统计每个部门的平均基本工资，并按照平均工资降序排列
SELECT emp.dept_id,AVG(s.basic_salary)
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id
ORDER BY AVG(s.basic_salary) DESC;

5、limit 分页
limit m,n
m表示从下标为m的记录开始查询，第一条记录下标为0，n表示取出n条出来，如果从m开始不够n条了，就有几条取几条。m=(page-1)*n，（page页码，n表示每页显示的条数）

如果第一页limit 0,n
如果第二页limit n,n
依次类推，得出公式limit (page-1)*n , n

//查询员工信息，每页显示5条，第二页
SELECT * FROM t_employee LIMIT 5,5;

//统计每个部门的平均基本工资，并显示前三名
SELECT emp.dept_id,AVG(s.basic_salary)
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id
ORDER BY AVG(s.basic_salary) DESC
LIMIT 0,3;

子查询（where;from;exists）

某些情况下，当进行一个查询时，需要的条件或数据要用另外一个 select 语句的结果，这个时候，就要用到子查询。

为了给主查询（外部查询）提供数据而首先执行的查询（内部查询）被叫做子查询。
一般根据子查询的嵌入位置分为，where型子查询,from型子查询，exists型子查询。

1、where型子查询

where型子查询即把内层sql语句查询的结果作为外层sql查询的条件.
(1)子查询要包含在括号内。
(2)建议将子查询放在比较条件的右侧。
(3)单行操作符对应单行子查询，多行操作符对应多行子查询。

• 单行操作符 右边子查询必须返回的是单个值，单行比较运算符（=,>,>=,<,<=,<>）
• 多行操作符 右边子查询可以返回多行，但必须是单列，ALL, ANY,IN, 其中ALL和ANY运算符必须与单行比较运算符（=,>,>=,<,<=,<>）结合使用

IN：等于任何一个
ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于sal>1 && sal>2 && sal>3，即大于所有。

ANY：和子查询返回的任意一个值比较。 例如：sal>ANY(1,2,3)等价于sal>1 or sal>2 or sal>3，即大于任意一个就可以。

EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为TRUE，否则为FALSE。

#where型子查询
//查询比“孙红雷”的工资高的员工编号
SELECT * FROM t_salary
WHERE basic_salary > (SELECT basic_salary FROM t_employee INNER JOIN t_salary ON t_employee.eid=t_salary.eid WHERE t_employee.ename='孙红雷');

//查询和孙红雷，李晨在同一个部门的员工
SELECT * FROM t_employee
WHERE dept_id IN(SELECT dept_id FROM t_employee WHERE ename='孙红雷' OR ename = '李晨');

SELECT * FROM t_employee
WHERE dept_id = ANY(SELECT dept_id FROM t_employee WHERE ename='孙红雷' OR ename = '李晨');

//查询全公司工资最高的员工编号，基本工资
SELECT eid,basic_salary FROM t_salary
WHERE basic_salary = (SELECT MAX(basic_salary) FROM t_salary);

SELECT eid,basic_salary FROM t_salary
WHERE basic_salary >= ALL(SELECT basic_salary FROM t_salary);

2、from型子查询

from型子查询即把内层sql语句查询的结果作为临时表供外层sql语句再次查询.

//找出比部门平均工资高的员工编号，基本工资
SELECT t_employee.eid,basic_salary 

FROM t_salary INNER JOIN t_employee INNER JOIN (
SELECT emp.dept_id AS did,AVG(s.basic_salary) AS avg_salary
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id) AS temp

ON t_salary.eid = t_employee.eid AND t_employee.dept_id = temp.did
WHERE t_salary.basic_salary > temp.avg_salary;

3、exists型子查询

//查询部门信息，该部门必须有员工
SELECT * FROM t_department 
WHERE EXISTS (SELECT * FROM t_employee WHERE t_employee.dept_id = t_department.did);

六、 事务

事务是一个不可分割的数据库操作序列，是数据库并发控制的基本单位，要么都执行，要么都不执行。

事务的ACID特性

1、原子性（Atomicity）： 事务是最小的执行单位，不允许分割，要么全部执行，要么完全不执行
2、一致性（Consistency）： 指一个事务执行前和执行后数据库必须处于一致性状态
3、隔离性（Isolation）： 并发的事务是相互隔离的，简单点说，某个事务的操作对其他事务不可见的.
4、持久性（Durability）： 事务对数据库中数据的改变是持久的，即当事务提交完成后，其影响应该保留下来，不能撤消

事务的用法（了解）

1、开启事务(start transaction)
2、执行sql操作(普通sql操作)
3、提交/回滚(commit/rollback)

注意:
1、建表的时候,选择 Innodb引擎才支持事务
2、默认情况下，MySQL是自动提交事务，每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。如果某一组操作需要在一个事务中，那么需要使用start transaction，一旦rollback或commit就结束当次事务，之后的操作又自动提交。
3、如果需要在当前会话（连接）的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置set autocommit = false;或set autocommit = 0;那样的话每一句SQL都需要手动commit提交才会真正生效。rollback或commit之前的所有操作都视为一个事务，之后的操作视为另一个事务，还需要手动提交或回滚。
4、和Oracle一样，DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交，因此最好事务中不要涉及DDL语句。

#开启手动处理事务模式
#set autocommit = false;
#开始事务（推荐）
start transaction;

#查看当前表的数据
select * from t_stu_detail;
#删除整张表的数据
delete from t_stu_detail;
#查询该表数据，发现显示删除后的结果
select * from t_stu_detail;
#回滚
rollback
#查看当前表的数据，发现又回来了
select * from t_stu_detail;

#删除整张表的数据
delete from t_stu_detail;
#提交事务
commit;
#查看当前表的数据，发现真删除了
select * from t_stu_detail;

#插入一条记录
INSERT INTO t_stu_detail VALUES 
(1, '123456789012345678', '1990-01-21', '12345678901', ' a@ 163.com', '北七家');
#保存还原点1
savepoint point1;

#插入一条记录
INSERT INTO t_stu_detail VALUES 
(2, '123456789012345677', '1990-02-21', '12345678902', 'b@ 163.com', '北七家');
#保存还原点2
savepoint point2;

#查看当前效果
select * from t_stu_detail;
#回滚到某个还原点
rollback to point1;
#提交事务
commit;

#清空表
truncate t_stu_detail;
#回滚，对于truncate无法回滚
rollback;

#修改表结构
alter table t_stu_detail add description varchar(50);
#回滚，对于修改表结构的语句无法回滚
rollback;

数据库的隔离级别

对于同时运行的多个事务（多线程并发）, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题: （问题的本质就是线程安全问题，共享数据的问题）

1、脏读: 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的.
（读的数据是无效的）

2、不可重复读: 对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新并提交了该字段. 之后, T1再次读取同一个字段, 值就不同了.
（两次读的数据不一致）

3、幻读: 对于两个事务 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入、删除了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出、少了几行.

数据库事务的隔离性:
数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

Mysql 支持 4 中事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE-READ

1、READ-UNCOMMITTED：允许事务读取其他事务未提交的数据，脏读、不可重复读、幻读的问题都会出现。

2、READ-COMMITTED：只允许事务读取其他事务已经提交的数据，可以避免脏读，但是不可重复读、幻读的问题仍然会出现。

3、REPEATABLE-READ：确保事务可以多次从一个字段中读取相同的值， 好比在事务开启时对现有的数据进行了拍照，其他事务对数据的修改，不管事务是否提交，我这里读取的是拍照下来的数据，可以避免脏读和不可重复读，但幻读的问题仍然存在。

注意：INNODB使用了MVCC (Multiversion Concurrency Control)，即多版本并发控制技术防止幻读。真正的像拍照一样，其他事务新插入或删除的记录也看不出来。

4、SERIALIZABLE：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作，所有并发问题都可以避免，但是性能十分低下。

七、 锁

数据库是一个共享资源，可以提供多个用户使用。上文已经讲到，事务是并发控制的基本单位，保证事务的ACID特性是事务处理的重要任务，而事务的ACID特性可能遭到破坏的原因之一就是多个事务对数据库的并发操作造成的。

封锁是实现并发控制的一个重要技术
基本的封锁类型有两种：排它锁和共享锁

排他锁（X锁）： 又称写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

共享锁（S锁）： 若事务T对数据对象A加上锁，则事务T可以读A但不能修改A，其他事务只都能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。

锁机制与 加锁原理

MySQL本身的锁机制（行锁、表锁、共享锁、排它锁）

按照锁的粒度划分：行锁、表锁、页锁
按照锁的使用方式划分：共享锁(读锁)、排它锁(写锁)（悲观锁的一种实现）

1、表级锁： MySQL 中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM 和 InnoDB 引擎都支持表级锁。

2、行级锁： MySQL 中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。

3、排他锁（X锁）： 又称写锁。若事务T对数据对象A加上X锁，则只允许T读取和修改A，其他任何事务都不能再对A加任何类型的锁，直到T释放A上的锁为止。

4、共享锁（S锁）： 若事务T对数据对象A加上锁，则事务T可以读A但不能修改A，其他事务只都能再对A加S锁，而不能加X锁，直到T释放A上的S锁为止。

MyISAM 只采用采用表级锁(table-level locking)。

所以，MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁(共享)，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁(独占)，这个过程并不需要用户干预，因此用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

所以可以总结出MyISAM存储引擎的特点：
读锁会阻塞写，写锁会阻塞读和写
1、对MyISAM表的读操作，不会阻塞其它进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。

2、对MyISAM表的写操作，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。

所以MyISAM不适合做写为主表的引擎，因为写锁后，其它线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

InnoDB的表锁操作：要显式加锁

InnoDB 支持行级锁(row-level locking)和表级锁，默认为行级锁

下面都以默认的行级锁进行讲解，即每个sql语句只锁住某一行。

1、在InnoDB引擎中，对于select语句，如果不显示表示，是不会加锁的。除非我们显示的表示，如下所示：
加共享锁：
再重复一遍特点：若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

select ... lock in share mode;

加排它锁（重点）
若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

select ... for update

2、insert，delete，update在事务中都会自动默认加上排它锁。
演示操作：
实例1： 使用主键id为条件去查询，然后开启另一个事务去更新数据，更新被阻塞，加锁了，锁定要查询的id为1的行数据。

第一个事务（还未提交）

第二个事务去更新数据，被阻塞

第二个事务，长时间拿不到锁报错。

解释：事务一对于select操作加的是排它锁，其他事务对于此行不能加任何锁。

但是，其他事务可以对其他未加锁的行进行加锁并执行相应操作。如下：

InnoDB锁的特性

如果查询条件用了索引/主键，那么 select … for update 就会进行行锁。如果是普通字段(没有索引/主键)，那么 select … for update 就会进行锁表。

并发控制（乐观锁、悲观锁）

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair、synchronize等都有类似的概念。

针对于不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为DBMS中的概念，更不要把他们和数据中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁：

悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。（排他性，一山不容二虎）

在mysql/InnoDB中使用悲观锁：
首先我们得关闭mysql中的autocommit属性，因为mysql默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作。

通过下面的例子来说明：
（1）当你手动加上排它锁，但是并没有关闭mysql中的autocommit。

SESSION1:
mysql> select * from user for update;
+----+------+--------+
| id | name | psword |
+----+------+--------+
|  1 | a    | 1      |
|  2 | b    | 2      |
|  3 | c    | 3      |
+----+------+--------+
3 rows in set

这里他会一直提示Unknown
mysql> update user set name=aa where id=1;
1054 - Unknown column 'aa' in 'field list'
mysql> insert into user values(4,d,4);
1054 - Unknown column 'd' in 'field list'

（2）正常流程

窗口1：
mysql> set autocommit=0;
Query OK, 0 rows affected
我这里锁的是表
mysql> select * from user for update;
+----+-------+
| id | price |
+----+-------+
|  1 |   500 |
|  2 |   800 |
+----+-------+
2 rows in set

窗口2：
mysql> update user set price=price-100 where id=1;
执行上面操作的时候，会显示等待状态，一直到窗口1执行commit提交事务才会出现下面的显示结果
Database changed
Rows matched: 1  Changed: 1  Warnings: 0

窗口1：
mysql> commit;
Query OK, 0 rows affected
mysql> select * from user;
+----+-------+
| id | price |
+----+-------+
|  1 |   400 |
|  2 |   800 |
+----+-------+
2 rows in set

悲观锁的优点和不足：
悲观锁实际上是采取了“先取锁再访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事务锁一行数据的时候，其他事务必须等待该事务提交之后，才能操作这行数据。

乐观锁：

乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

数据版本,为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，同时对版本标识进行更新。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的版本标识进行比对，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

死锁的原理及分析

八、视图

特点：
1、视图是从一个或几个基本表（或视图）导出的表。它与基本表不同，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍然存放在基本表中。所以，一旦基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。同时，视图的建立和删除也不会影响基本表。

//建立信息系学生视图
CREATE VIEW IS_Student
AS
SELECT Sno,Sname,Sage
FROM Student
WHERE Sdept="IS";
//查询视图
SELECT Sno,Sage    消解      SELECT Sno,Sage  
FROM IS_Student   ====>      FROM Student
WHERE Sage<20                WHERE Sage<20 AND Sdept="IS";

2、由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新——与查询视图的消解一样

3、不是所有的视图都可以更新的，因为有些视图的更新不能唯一地有意义地转换成对相应基本表的更新

DB2部分规定：
（1）当视图来自多个基本表时，不允许添加和删除数据。
（2）若视图定义中含有GROUP BY子句，此视图不允许更新。
（3）若视图中含有DISTINCT短语，则此视图不允许更新。

视图作用、优缺点参考文章：https://zhanglong.blog.csdn.net/article/details/118882245.
视图的作用：
根本作用：简化SQL、提高开发效率

重用SQL语句
1、简化复杂的SQL操作。（编写查询后，可以很方便的重用它而不必知道其基本查询细节）
2、使用表的组成部分而不是整个表
3、保护数据，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
4、更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图优缺点

1、优点
查询简单化，简化用户操作
数据安全性，对机密数据提供安全保护
逻辑数据独立性：对重构数据库提供了一定程度的逻辑独立性。

2、缺点
性能低：对视图的查询必须转化为对基本表的查询
修改限制：对于多个基本表抽象的视图，是不可修改的