MySQL基本知识点和操作合集

一、安装MySQL

1.下载安装包

• 网址：https://dev.mysql.com/downloads/

• 选择 MySQL Community Server——Microsoft Window——Windows(x86,64-bit),ZIP Archive——No thanks,just start my download.

2.安装配置

• 解压安装包

• 在解压目录下新建my.ini文件

• 将下面文本拷贝进my.ini文件中

  [mysqld]
  # 设置3306端口
  port=3306
  # 设置mysql的安装目录   ----------是你的文件路径-------------
  basedir=D:\mysql-8.0.26-winx64\mysql-8.0.26-winx64
  # 设置mysql数据库的数据的存放目录  ---------是你的文件路径data文件夹自行创建
  #datadir=E:\mysql\mysql\data
  # 允许最大连接数
  max_connections=200
  # 允许连接失败的次数。
  max_connect_errors=10
  # 服务端使用的字符集默认为utf8mb4
  character-set-server=utf8mb4
  # 创建新表时将使用的默认存储引擎
  default-storage-engine=INNODB
  # 默认使用“mysql_native_password”插件认证
  #mysql_native_password
  default_authentication_plugin=mysql_native_password
  [mysql]
  # 设置mysql客户端默认字符集
  default-character-set=utf8mb4
  [client]
  # 设置mysql客户端连接服务端时默认使用的端口
  port=3306
  default-character-set=utf8mb4
  

3.初始化MySQL数据库

• 以管理员身份打开命令提示符，切换到bin目录下（cd 路径）

• 执行命令

  mysqld --initialize --console
  

  得到的结果最后一行为 用户名@地址:密码

  2022-05-06T05:24:29.591457Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
  2022-05-06T05:24:29.591476Z 0 [System] [MY-013169] [Server] D:\mysql-8.0.28-winx64\bin\mysqld.exe (mysqld 8.0.28) initializing of server in progress as process 12820
  2022-05-06T05:24:29.715224Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  2022-05-06T05:24:30.286496Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  2022-05-06T05:24:33.757389Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 2:Z8qy>BIp5>
  

• 安装MySQL服务

  mysqld --install mysql
  

• 启动/停止MySQL服务

  net start mysql
  net stop mysql
  

• 连接（登录）MySQL服务

  mysql -uroot -p密码
  #mysql -h埃坯地址 -P端口 -u用户名 -p密码
  

• 修改密码

  ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
  

  输入quit或者exit都可以退出

4.配置环境变量

• 依次打开：此电脑->属性->高级系统设置->环境变量，在系统变量中新建

  变量名：MYSQL_HOME
  变量值：MySQL的路径

• 然后在系统变量里面找到path变量，添加%MYSQL_HOME%\bin

• 最后右键此电脑->管理->服务与应用程序->服务，找到MySQL将启动类型改为手动

二、数据库、数据表基本操作

1.数据库

1.1.创建数据库

create database if not exists 数据库名;		

1.2.查看该数据库基本信息

show create database 数据库名;

1.3.删除数据

drop database if exists 数据库名;

1.4.查询连接中所有数据库

show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.02 sec)

1.5.修改数据库字符集为GKB

alter database 数据库名 character set gbk;

1.6.切换数据库

use 数据库名;

1.7.查看当前使用的数据库

select database();

1.8.数据库存储引擎

INNODB：默认使用，安全性高，支持事务的处理，多表多用户操作

MYISAM：早些年使用，节约空间，速度较快

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

2.数据表

数据表的列类型

• 数值

  数据类型	描述	大小（字节）
  tinyint	十分小的数据	1
  smallint	较小的数据	2
  mediumint	中等大小的数据	3
  int	标准的整数	4
  bigint	较大的数据	8
  float	浮点数	4
  double	浮点数	8
  decimal	字符串形式的浮点数，一般用于金融计算

• 字符串

  数据类型	描述	大小
  char	字符串固定大小	0~255
  varchar	可变字符串	0~65535
  tinytext	微型文本	2^8-1
  text	文本串	2^16-1

• 时间

  数据类型	描述	格式
  date	日期	YYYY-MM-DD
  time	时间	HH:mm:ss
  datetime	最常用的时间格式	YYYY-MM-DD HH：mm：ss
  timestamp	时间戳，1970.1.1到现在的毫秒数
  year	年份

2.1.创建数据表

表名和字段尽量使用``括起来

 create table if not exists 表名(
         字段1 字段类型,
         字段2 字段类型,
         …
         字段n 字段类型
);

mysql>  create table student(
    ->  id int,
    ->  name varchar(20),
    ->  gender varchar(10),
    ->  birthday date
    ->  );
Query OK, 0 rows affected (0.03 sec)	

2.2.查看当前数据库中的表

show tables;

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| class          |
| stu            |
| student        |
| student01      |
| student02      |
| student03      |
| student04      |
| student05      |
+----------------+
8 rows in set (0.00 sec)

2.3.查看表的基本信息（如外键）

show create table 表名;

mysql> show create table stu;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| stu   | CREATE TABLE `stu` (                                                                                                 |
|       | `id` varchar(4) DEFAULT NULL,                                                                                        |
|       | `sanme` varchar(10) DEFAULT NULL,                                                                                    |
|       | `gender` varchar(10) DEFAULT NULL,                                                                                   |
|       | `birthday` date DEFAULT NULL                                                                                         |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.4.查看表内字段属性

desc 数据表名;

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| gender   | varchar(10) | YES  |     | NULL    |       |
| birthday | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

2.5.修改表

*修改表名

alter table 表名 rename to 新表名;

mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.02 sec)

*修改字段名

alter table 表名 change 字段名 新字段名 varchar(10);

mysql> alter table stu change name sanme varchar(10);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

*修改字段数据类型

alter table 表名 modify 字段名 int;

mysql> alter table stu modify id varchar(4);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

*增加字段

alter table 表名 add 字段名 字段数据类型;

mysql> alter table stu add address varchar(20);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

*删除字段

alter table 表名 drop 字段名;

2.6.删除表

drop table if exists 表名;

三、数据库的约束

1.主键约束

主键约束即primary key用于唯一的标识表中的每一行，被标识为主键的数据在表中是唯一的且其值不能为空。这点类似于我们每个人都有一个身份证号，并且这个身份证号是唯一的。

字段名 数据类型 primary key;

#法一
create table student(
id int primary key,
name varchar(20)
);

#法二
create table student01(
id int,
name varchar(20),
primary key(id)
);

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> desc student01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.非空约束

非空约束即 NOT NULL指的是字段的值不能为空。

字段名 数据类型 NOT NULL;

create table student02(
id int,
name varchar(20) not null
);

ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>);

mysql> desc student02;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.默认值约束

默认值约束即DEFAULT用于给数据表中的字段指定默认值，即当在表中插入一条新记录时若未给该字段赋值，那么，数据库系统会自动为这个字段插人默认值。

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

create table student03(
id int,
name varchar(20),
gender varchar(10) default 'male'
);

mysql> desc student03;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(20) | YES  |     | NULL    |       |
| gender | varchar(10) | YES  |     | male    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4.唯一性约束

唯一性约束即UNIQUE用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

字段名 数据类型 UNIQUE;

create table student04(
id int,
name varchar(20) unique
);

mysql> desc student04;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

5.外键约束

5.1.创建外键约束

外键约束即FOREIGN KEY常用于多张表之间的约束。

#在创建数据表时语法如下：
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段)

#将创建数据表创号后语法如下：
ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段);

#级联删除
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段) ON DELETE CASCADE;

#级联更新
CONSTRAINT 外键名 FOREIGN KEY (从表外键字段) REFERENCES 主表 (主键字段) ON UPDATE CASCADE;

#法一
create table student05(
id int primary key,
name varchar(20)
);

create table class(
classid int primary key,
studentid int
);

alter table class add constraint fk_class_studentid foreign key(studentid) references student05(id);

#法二
create table student05(
id int primary key,
name varchar(20)
);

create table class(
classid int primary key,
studentid int,
constraint fk_class_studentid foreign key (studentid) references student05(id)
);

mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (                                                                                               |
|       | `classid` int NOT NULL,                                                                                              |
|       | `studentid` int DEFAULT NULL,                                                                                        |
|       | PRIMARY KEY (`classid`),                                                                                             |
|       | KEY `fk_class_studentid` (`studentid`),                                                                              |
|       | CONSTRAINT `fk_class_studentid` FOREIGN KEY (`studentid`) REFERENCES `student05` (`id`)                              |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.2.删除外键约束

alter table 从表名 drop foreign key 外键名；

alter table class drop foreign key fk_class_studentid;

mysql> show create table class;
+-------+----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------+
| class | CREATE TABLE `class` (                                                                                               |
|       | `classid` int NOT NULL,                                                                                              |
|       | `studentid` int DEFAULT NULL,                                                                                        |
|       | PRIMARY KEY (`classid`),                                                                                             |
|       | KEY `fk_class_studentid` (`studentid`)                                                                               |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                                                   |
+-------+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.3.关于外键约束需要注意的细节

• 从表里的外键通常为主表的主键
• 从表里外键的数据类型必须与主表中主键的数据类型一致
• 主表发生变化时应注意主表与从表的数据一致性问题

6.零填充约束

不足位数的用0来填充 , 如int(3),5则为005

create table student(
	id int zerofill, -- 零填充约束
	name varchar(20)
);

7.自增约束

通常用来设计唯一的主键，必须是整数类型

#法一
create table student(
	id int primary key auto_increment,
	name varchar(20)
)auto_increment = 100; #设置步长

#法二
create table student(
	id int primary key auto_increment,
	name varchar(20)
);
alter table student auto_increment = 100;

delete和truncate在删除后自增列的变化

• delete 数据之后自动增长从断点开始

• truncate数据之后自动增长从默认起始值开始

四、数据表插入、更新、删除数据

1.插入

#指定字段
INSERT INTO 表名（字段名1,字段名2,...) VALUES (值 1,值 2,...);

#全部字段
INSERT INTO 表名 VALUES (值 1,值 2,...);

#插入多条
INSERT INTO 表名 [(字段名1,字段名2,...)]VALUES (值 1,值 2,…),(值 1,值 2,…),...;

2.更新

#指定字段
UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…] [WHERE 条件表达式];

#全部更新
UPDATE 表名 SET 字段名1=值1[,字段名2 =值2,…];

3.删除

TRUNCATE和DETELE都能实现删除表中的所有数据的功能，但两者也是有区别的：

• DELETE语句后可跟WHERE子句，可通过指定WHERE子句中的条件表达式只删除满足条件的部分记录；但是，TRUNCATE语句只能用于删除表中的所有记录。
• 使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1
• DELETE语句是DML语句，TRUNCATE语句通常被认为是DDL语句

#指定字段
DELETE FROM 表名 [WHERE 条件表达式];

#全部删除
DELETE FROM 表名;

五、数据表简单查询

简单查询即不含where的select语句。在此，我们讲解简单查询中最常用的两种查询：查询所有字段和查询指定字段。
在此，先准备测试数据，代码如下：

-- 创建student06表
CREATE TABLE student06 (
    sid CHAR(6),
    sname VARCHAR(50),
    age INT,
    gender VARCHAR(50) DEFAULT 'male'
);

-- 向student表插入数据
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1007', 'hjop', 16, 'male');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1008', 'tyop', 15, 'female');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1009', 'nhmk', 13, 'male');
INSERT INTO student06 (sid,sname,age,gender) VALUES ('S_1010', 'xdfv', 17, 'female');

1.查询所有字段

select * from student06;

mysql> select * from student06;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
10 rows in set (0.00 sec)

2.查询指定字段（sid、sname）

select sid,sname from student06;

mysql> select sid,sname from student06;
+--------+-------+
| sid    | sname |
+--------+-------+
| S_1001 | lili  |
| S_1002 | wang  |
| S_1003 | tywd  |
| S_1004 | hfgs  |
| S_1005 | qwer  |
| S_1006 | zxsd  |
| S_1007 | hjop  |
| S_1008 | tyop  |
| S_1009 | nhmk  |
| S_1010 | xdfv  |
+--------+-------+
10 rows in set (0.00 sec)

3.常数的查询

select sid,sname,'2021-03-02' from student06;

mysql> select sid,sname,'2021-03-02' from student06;
+--------+-------+------------+
| sid    | sname | 2021-03-02 |
+--------+-------+------------+
| S_1001 | lili  | 2021-03-02 |
| S_1002 | wang  | 2021-03-02 |
| S_1003 | tywd  | 2021-03-02 |
| S_1004 | hfgs  | 2021-03-02 |
| S_1005 | qwer  | 2021-03-02 |
| S_1006 | zxsd  | 2021-03-02 |
| S_1007 | hjop  | 2021-03-02 |
| S_1008 | tyop  | 2021-03-02 |
| S_1009 | nhmk  | 2021-03-02 |
| S_1010 | xdfv  | 2021-03-02 |
+--------+-------+------------+
10 rows in set (0.00 sec)

4.从查询结果中过滤重复数据

在使用DISTINCT 时需要注意，在SELECT查询语句中DISTINCT关键字只能用在第一个所查列名之前。

select distinct gender from student06;

mysql> select distinct gender from student06;
+--------+
| gender |
+--------+
| male   |
| female |
+--------+
2 rows in set (0.00 sec)

6.算术运算符

查询学生10年后的年龄 MySQL命令：

select sname,age+10 from student06;

mysql> select sname,age+10 from student06;
+-------+--------+
| sname | age+10 |
+-------+--------+
| lili  |     24 |
| wang  |     25 |
| tywd  |     26 |
| hfgs  |     27 |
| qwer  |     28 |
| zxsd  |     29 |
| hjop  |     26 |
| tyop  |     25 |
| nhmk  |     23 |
| xdfv  |     27 |
+-------+--------+
10 rows in set (0.00 sec)

八、函数

1.聚合函数

在开发中，我们常常有类似的需求：统计某个字段的最大值、最小值、 平均值等等。为此，MySQL中提供了聚合函数来实现这些功能。所谓聚合，就是将多行总成一行。

• 所有的聚合函数均如此——输入多行，输出一行。聚合函数具有自动滤空的功能，若某一个值为NULL，那么会自动将其过滤使其不参与运算。

• 聚合函数使用规则：只有SELECT子句和HAVING子句、ORDER BY子句中能够使用聚合函数。例如，在WHERE子句中使用聚合函数是错误的。

1.1.count()

查询该学生表中有多少人：

select count(*) from student06;

mysql> select count(*) from student06;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.01 sec)

1.2.max()、min()

查询该学生表中最大、小的年纪值：

select max(age) from student06;
select min(age) from student06;

:mysql> select max(age) from student06;
+----------+
| max(age) |
+----------+
|       19 |:
+----------+
1 row in set (0.00 sec)

mysql> select min(age) from student06;
+----------+
| min(age) |
+----------+
|       13 |
+----------+
1 row in set (0.00 sec)

1.3.sum()、avg()

查询该学生表中年纪的总和、平均数：

select sum(age) from student06;
select avg(age) from student06;

mysql> select sum(age) from student06;
+----------+
| sum(age) |
+----------+
|      160 |
+----------+
1 row in set (0.00 sec)

mysql> select avg(age) from student06;
+----------+
| avg(age) |
+----------+
|  16.0000 |
+----------+
1 row in set (0.00 sec)

2.其他函数

2.1.时间函数

SELECT NOW();
SELECT DAY (NOW());
SELECT DATE (NOW());
SELECT TIME (NOW());
SELECT YEAR (NOW());
SELECT MONTH (NOW());
SELECT CURRENT_DATE();
SELECT CURRENT_TIME();
SELECT CURRENT_TIMESTAMP();
SELECT ADDTIME('14:23:12','01:02:01');
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY);
SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH);
SELECT DATE_SUB(NOW(),INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(),INTERVAL 1 MONTH);
SELECT DATEDIFF('2019-07-22','2019-05-05');

2.2.字符串函数

SELECT CHAR_LENGTH('我喜欢你'); -- 字符串长度
SELECT CONCAT('我','喜欢','你'); -- 拼接字符串
SELECT INSERT('我喜欢',1,1,'超级') -- INSERT(str,pos,len,newstr) 从str的pos位置开始替换为长度为len的newstr
SELECT UPPER('zsr'); -- 转大写
SELECT LOWER('ZSR'); -- 转小写
SELECT INSTR('zsrs','s'); -- 返回第一次出现字串索引的位置
SELECT REPLACE('加油就能胜利','加油','坚持'); -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',3,6); -- 返回指定的字符串(源字符串,截取位置,截取长度) 就是胜利
SELECT REVERSE('rsz'); -- 反转字符串

2.3.数学函数

SELECT ABS(-8); -- 绝对值
SELECT CEIL(5.1); -- 向上取整
SELECT CEILING(5.1); -- 向上取整
SELECT RAND(); -- 返回0~1之间的一个随机数
SELECT SIGN(-10); -- 返回一个数的符号;0返回0;正数返回1;负数返回-1

2.4.MD5信息摘要算法

select md5("1234");

九、数据表高级查询

1.条件查询

1.1.使用关系运算符查询

查询年龄等于或大于17的学生的信息：

select * from student06 where age>=17;

mysql> select * from student06 where age>=17;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
4 rows in set (0.01 sec)

1.2.使用IN、BETWEEN AND关键字查询

*IN

查询sid为S_1002和S_1003的学生信息、查询sid为S_1001以外的学生的信息：

select * from student06 where sid in ('S_1002','S_1003');

select * from student06 where sid not in ('S_1001');

mysql> select * from student06 where sid in ('S_1002','S_1003');
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from student06 where sid not in ('S_1001');
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
9 rows in set (0.01 sec)

*BETWEEN AND

查询15到18岁的学生信息、查询不是15到18岁的学生信息：

select * from student06 where age between 15 and 18;

select * from student06 where age not between 15 and 18;

mysql> select * from student06 where age between 15 and 18;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
7 rows in set (0.01 sec)

mysql> select * from student06 where age not between 15 and 18;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1009 | nhmk  |   13 | male   |
+--------+-------+------+--------+
3 rows in set (0.00 sec)

1.3.使用空值查询

查询sname不为空值的学生信息：

select * from student06 where sname is not null;

mysql> select * from student06 where sname is not null;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
10 rows in set (0.00 sec)

1.4.使用AND、OR关键字查询

查询年纪大于15且性别为male的学生信息、查询年纪大于15或性别为male的学生信息：

select * from student06 where age>15 and gender='male';

select * from student06 where age>15 or gender='male';

mysql> select * from student06 where age>15 and gender='male';
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1003 | tywd  |   16 | male   |
| S_1005 | qwer  |   18 | male   |
| S_1007 | hjop  |   16 | male   |
+--------+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from student06 where age>15 or gender='male';
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1003 | tywd  |   16 | male   |
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
8 rows in set (0.00 sec)

1.5.使用LIKE关键字查询

MySQL中可使用LIKE关键字可以判断两个字符串是否相匹配。

*普通字符串

查询sname中与wang匹配的学生信息：

select * from student06 where sname like 'wang';

mysql> select * from student06 where sname like 'wang';
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
+--------+-------+------+--------+
1 row in set (0.01 sec)

*含有%通配的字符串

%用于匹配任意长度的字符串。例如，字符串“a%”匹配以字符a开始任意长度的字符串。

查询学生姓名包含s的记录：

select * from student06 where sname like '%s%';

mysql> select * from student06 where sname like '%s%';
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1004 | hfgs  |   17 | female |
| S_1006 | zxsd  |   19 | female |
+--------+-------+------+--------+
2 rows in set (0.00 sec)

*含有_通配的字符串

下划线通配符只匹配单个字符，如果要匹配多个字符，需要连续使用多个下划线通配符。例如，字符串“ab_”匹配以字符串“ab”开始长度为3的字符串，如abc、abp等等；字符串“a__d”匹配在字符“a”和“d”之间包含两个字符的字符串，如"abcd"、"atud"等等。

查询学生姓名以g结尾且长度为4的记录：

select * from student06 where sname like '___g';

mysql> select * from student06 where sname like '___g';
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1002 | wang  |   15 | female |
+--------+-------+------+--------+
1 row in set (0.00 sec)

1.6.使用LIMIT限制查询结果的数量

select 查询列表
from 表
limit offset,pagesize;

#取前三
select * from student06 limit 3;

#取中间部分
select * from student06 limit 3,7;

mysql> select * from student06 limit 3;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1001 | lili  |   14 | male   |
| S_1002 | wang  |   15 | female |
| S_1003 | tywd  |   16 | male   |
+--------+-------+------+--------+
3 rows in set (0.00 sec)

mysql> select * from student06 limit 3,7;
+--------+-------+------+--------+
| sid    | sname | age  | gender |
+--------+-------+------+--------+
| S_1004 | hfgs  |   17 | female |
| S_1005 | qwer  |   18 | male   |
| S_1006 | zxsd  |   19 | female |
| S_1007 | hjop  |   16 | male   |
| S_1008 | tyop  |   15 | female |
| S_1009 | nhmk  |   13 | male   |
| S_1010 | xdfv  |   17 | female |
+--------+-------+------+--------+
7 rows in set (0.00 sec)

1.7.使用GROUP BY进行分组查询

GROUP BY 子句可像切蛋糕一样将表中的数据进行分组，再进行查询等操作。换言之，可通俗地理解为：通过GROUP BY将原来的表拆分成了几张小表。
接下来，我们通过一个例子开始学习GROUP BY，代码如下：

#创建员工表
CREATE TABLE employee (
    id int,
    name varchar(50),
    salary int,
    departmentnumber int
);

#向员工表中插入数据
INSERT INTO employee values(1,'tome',2000,1001); 
INSERT INTO employee values(2,'lucy',9000,1002); 
INSERT INTO employee values(3,'joke',5000,1003); 
INSERT INTO employee values(4,'wang',3000,1004); 
INSERT INTO employee values(5,'chen',3000,1001); 
INSERT INTO employee values(6,'yukt',7000,1002); 
INSERT INTO employee values(7,'rett',6000,1003); 
INSERT INTO employee values(8,'mujk',4000,1004); 
INSERT INTO employee values(9,'poik',3000,1001);

*GROUP BY和聚合函数一起使用

统计各部门员工个数、统计部门编号大于1001的各部门员工个数：

select count(*), departmentnumber from employee group by departmentnumber;

select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;

mysql> select count(*), departmentnumber from employee group by departmentnumber;
+----------+------------------+
| count(*) | departmentnumber |
+----------+------------------+
|        3 |             1001 |
|        2 |             1002 |
|        2 |             1003 |
|        2 |             1004 |
+----------+------------------+
4 rows in set (0.00 sec)

mysql> select count(*), departmentnumber from employee where departmentnumber>1001 group by departmentnumber;
+----------+------------------+
| count(*) | departmentnumber |
+----------+------------------+
|        2 |             1002 |
|        2 |             1003 |
|        2 |             1004 |
+----------+------------------+
3 rows in set (0.00 sec)

*HAVING

• where和having都可

having可以用的前提是已经筛选出了goods_price字段，在这种情况下和where的效果是等效的，但是如果没有select goods_price 就会报错，因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

#where
select goods_price,goods_name from sw_goods where goods_price > 100;

#having
select goods_price,goods_name from sw_goods having goods_price > 100;

• 只可where，不可having

#where
select goods_name,goods_number from sw_goods where goods_price > 100;

#having
select goods_name,goods_number from sw_goods having goods_price > 100;
#报错！！！因为前面并没有筛选出goods_price 字段

• 只可having，不可where

where 后面要跟的是数据表里的字段，如果把ag换成avg(goods_price)也是错误的，因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

select goods_category_id , avg(goods_price) as ag from sw_goods group by goods_category having ag > 1000;

select goods_category_id , avg(goods_price) as ag from sw_goods where ag>1000 group by goods_category;
#报错！！因为from sw_goods 这张数据表里面没有ag这个字段

*GROUP BY和聚合函数以及HAVING一起使用

统计工资总和大于8000的部门：

select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;

mysql> select sum(salary),departmentnumber from employee group by departmentnumber having sum(salary)>8000;
+-------------+------------------+
| sum(salary) | departmentnumber |
+-------------+------------------+
|       16000 |             1002 |
|       11000 |             1003 |
+-------------+------------------+
2 rows in set (0.01 sec)

1.8.使用ORDER BY对查询结果排序

#asc升序，desc降序
SELECT 字段名1,字段名2,…
FROM 表名
ORDER BY 字段名 ASC | DESC;

2.别名设置

2.1.为表取别名

SELECT * FROM 表名 AS 表的别名 WHERE .... ;

2.2.为字段取别名

SELECT 字段名1 AS 别名1 , 字段名2 AS 别名2 , ... FROM 表名 WHERE ... ;

3.多表连接查询

3.1.交叉连接查询

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡儿积；比如：集合A={a,b}，集合B={0,1,2}，则集合A和B的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。所以，交叉连接也被称为笛卡尔连接，其语法格式如下：

SELECT * FROM 表1 CROSS JOIN 表2;

在该语法中：CROSS JOIN用于连接两个要查询的表，通过该语句可以查询两个表中所有的数据组合。
由于这个交叉连接查询在实际运用中没有任何意义，所以只做为了解即可

3.2.内连接查询

内连接：从左表中取出每一条记录，去右表中与所有的记录进行匹配，匹配必须是某个条件在左表中与右表中相同最终才会保留结果，否则不保留。

SELECT 查询字段1,查询字段2, ... FROM 表1 INNER JOIN 表2 ON 表1.关系字段=表2.关系字段;

# SELECT ... FROM 表1 JOIN 表2 ON 连接条件;
# SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件;
# SELECT ... FROM 表1, 表2 WHERE 连接条件;

准备数据，代码如下：

DROP TABLE IF EXISTS department;
DROP TABLE IF EXISTS employee;

-- 创建部门表
CREATE TABLE department(
  did int (4) NOT NULL PRIMARY KEY, 
  dname varchar(20)
);

-- 创建员工表
CREATE TABLE employee (
  eid int (4) NOT NULL PRIMARY KEY, 
  ename varchar (20), 
  eage int (2), 
  departmentid int (4) NOT NULL
);

-- 向部门表插入数据
INSERT INTO department VALUES(1001,'财务部');
INSERT INTO department VALUES(1002,'技术部');
INSERT INTO department VALUES(1003,'行政部');
INSERT INTO department VALUES(1004,'生活部');
-- 向员工表插入数据
INSERT INTO employee VALUES(1,'张三',19,1003);
INSERT INTO employee VALUES(2,'李四',18,1002);
INSERT INTO employee VALUES(3,'王五',20,1001);
INSERT INTO employee VALUES(4,'赵六',20,1004);

查询员工姓名及其所属部门名称：

select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;

mysql> select employee.ename,department.dname from department inner join employee on department.did=employee.departmentid;
+--------+-----------+
| ename  | dname     |
+--------+-----------+
| 张三   | 行政部      |
| 李四   | 技术部      |
| 王五   | 财务部      |
| 赵六   | 生活部      |
+--------+-----------+
4 rows in set (0.00 sec)

3.3.外连接查询

外连接分为两种：左(外)连接和右(外)连接。

左外连接就是保留左表所有记录，与右表做连接。如果右表有符合条件的记录就与左表连接。如果右表没有符合条件的记录，就用NULL与左表连接。右连接也是如此。

SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT | RIGHT JOIN 表2 ON 表1.关系字段=表2.关系字段 WHERE 条件;

先准备数据，代码如下：

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1002);
INSERT INTO student VALUES(4,'赵六',23,1003);
INSERT INTO student VALUES(5,'Jack',22,1009);

左连接：查询每个班的班级ID、班级名称及该班的所有学生的名字：

select class.cid,class.cname,student.sname from class left join student on class.cid=student.classid;

mysql> select class.cid,class.cname,student.sname from class left  join student on class.cid=student.classid;
+------+--------+--------+
| cid  | cname  | sname  |
+------+--------+--------+
| 1001 | Java   | 张三   |
| 1002 | C++    | 王五   |
| 1002 | C++    | 李四   |
| 1003 | Python | 赵六   |
| 1004 | PHP    | NULL   |
+------+--------+--------+
5 rows in set (0.00 sec)

右连接：查询每个班的班级ID、班级名称及该班的所有学生的名字：

select class.cid,class.cname,student.sname from class right join student on class.cid=student.classid;

mysql> select class.cid,class.cname,student.sname from class right join student on class.cid=student.classid;
+------+--------+--------+
| cid  | cname  | sname  |
+------+--------+--------+
| 1001 | Java   | 张三   |
| 1002 | C++    | 李四   |
| 1002 | C++    | 王五   |
| 1003 | Python | 赵六   |
| NULL | NULL   | Jack   |
+------+--------+--------+
5 rows in set (0.00 sec)

4.子查询

准备数据，代码如下：

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS class;

-- 创建班级表
CREATE TABLE class(
  cid int (4) NOT NULL PRIMARY KEY, 
  cname varchar(20)
);

-- 创建学生表
CREATE TABLE student (
  sid int (4) NOT NULL PRIMARY KEY, 
  sname varchar (20), 
  sage int (2), 
  classid int (4) NOT NULL
);

-- 向班级表插入数据
INSERT INTO class VALUES(1001,'Java');
INSERT INTO class VALUES(1002,'C++');
INSERT INTO class VALUES(1003,'Python');
INSERT INTO class VALUES(1004,'PHP');
INSERT INTO class VALUES(1005,'Android');

-- 向学生表插入数据
INSERT INTO student VALUES(1,'张三',20,1001);
INSERT INTO student VALUES(2,'李四',21,1002);
INSERT INTO student VALUES(3,'王五',24,1003);
INSERT INTO student VALUES(4,'赵六',23,1004);
INSERT INTO student VALUES(5,'小明',21,1001);
INSERT INTO student VALUES(6,'小红',26,1001);
INSERT INTO student VALUES(7,'小亮',27,1002);

4.1.带比较运算符的子查询

查询张三同学所在班级的信息：

select * from class where cid=(select classid from student where sname='张三');

mysql> select * from class where cid=(select classid from student where sname='张三');
+------+-------+
| cid  | cname |
+------+-------+
| 1001 | Java  |
+------+-------+
1 row in set (0.00 sec)

4.2.带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询， 它不产生任何数据只返回TRUE或FALSE。当返回值为TRUE时外层查询才会执行。

假如王五同学在学生表中则从班级表查询所有班级信息：

select * from class where exists (select * from student where sname='王五');

mysql> select * from class where exists (select * from student where sname='王五');
+------+---------+
| cid  | cname   |
+------+---------+
| 1001 | Java    |
| 1002 | C++     |
| 1003 | Python  |
| 1004 | PHP     |
| 1005 | Android |
+------+---------+
5 rows in set (0.01 sec)

4.3.带ANY、ALL关键字的子查询

ANY关键字表示满足其中任意一个条件就返回一个结果作为外层查询条件。

ALL关键字与ANY有点类似，只不过带ALL关键字的子査询返回的结果需同时满足所有内层査询条件。

#查询比任一学生所属班级号还大的班级编号
select * from class where cid > any (select classid from student);

#查询比所有学生所属班级号还大的班级编号
select * from class where cid > all (select classid from student);

mysql> select * from class where cid > any (select classid from student);
+------+---------+
| cid  | cname   |
+------+---------+
| 1002 | C++     |
| 1003 | Python  |
| 1004 | PHP     |
| 1005 | Android |
+------+---------+
4 rows in set (0.00 sec)

mysql> select * from class where cid > all (select classid from student);
+------+---------+
| cid  | cname   |
+------+---------+
| 1005 | Android |
+------+---------+
1 row in set (0.00 sec)

5.查询关键字顺序

• 查询语句的书写顺序和执行顺序

  select——from——where——group by——having——order by——limit

• 查询语句的执行顺序

  from——where——group by——having——select——order by——limit

十、索引

1.索引原理

1.1.索引介绍

查字典，不可能从第一页翻到最后一页去查找，一般先查找拼音或者偏旁部首，然后直接跳转到对应的页小范围的一条一条查找。

索引也是类似，建立索引就是按照某种规则建立一颗B+树，在B+树查询到符合要求的记录就类似于查询到拼音或者偏旁部首一样，最后回表就能很快的查找到完整具体的记录。

优势：

• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。

• 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

• 索引会占据磁盘空间

• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

应该创建索引的列：

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
• 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列：

• 对于那些在查询中很少使用或者参考的列不应该创建索引。

  若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

• 对于那些只有很少数据值或者重复值多的列也不应该增加索引。

  这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

• 对于那些定义为text, image和bit数据类型的列不应该增加索引。

  这些列的数据量要么相当大，要么取值很少。

• 当该列修改性能要求远远高于检索性能时，不应该创建索引。

假设数据表index_demo如下：

CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1)
)charset=utf8mb4;

将插入的每条记录的行格式简化为如下：

• record_type：记录头信息的一项属性，表示记录的类型：

  • 0表示普通记录（普通数据），
  • 1表示目录项记录（索引目录），
  • 2表示Infimum记录（最小记录的上一条记录，即开头），
  • 3表示Supremum记录（最大记录的下一条记录，即结尾）。

• next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量（指针）。

• 各个列的值：这里只记录在index_demo表中的三个列，分别是c1、c2和c3。

• 其他信息：除了上述3种信息以外的所有信息，包括其他隐藏列的值以及记录的额外信息。

插入记录后，结构如下：

1.2.插入记录时页的变化

假设每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放好多条记录）。

向index_demo插3条记录：

INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');

变化如下：

可以看到，这些记录组成了单链表，并且按照主键值从小到大的顺序将记录排序。innodb要求插入记录必须这么做。

接着我们在插入一条记录：

INSERT INTO index_demo VALUES(4, 4, 'a');

innodb还要求，下一个数据页用户记录的主键值大于上一个页中用户记录的主键值。

而页10最多存放3条数据记录，所以不得不再分配一个新页：

链表新增结点都是随机分配一块空间，在内存上并不是连续的，这里就假设为28，用来说明这个内存的不连续性。

不管对页中的记录是增加、删除还是修改，必须遵守下一个数据页用户记录的主键值大于上一个页中用户记录的主键值这个规则。为了维持这个规则，往往伴随记录的移动，甚至需要为记录分配新页，这个过程也叫页分裂。

假设此时往表中插入了很多条记录，如下：

查字典不可能从头开始一个个往后查，记录也是一样，不可能一条条顺序往后找，所以innodb给这些记录一个目录：

record_type属性各个取值代表的意思如下：

0：普通的用户记录
1：目录项记录
2：Infimum记录
3：Supremum记录

目录项记录的record_type值是1，而普通用户记录的record_type值是0。

1.3.索引查询原理

以查找主键为8的记录为例。

• 先到存储目录项记录的页（页30）中通过二分法快速定位到用户记录页，因为5< 8< 12，所以定位到对应的记录所在的页就是页28。

• 再到存储用户记录的页28中根据单链表遍历定位到主键值为8的用户记录。

目录项比较多一页放不下就和用户记录一样进行页分裂。

假设一个目录页最多存放4条目录项记录(真实情况是可以存放很多条)，如果此时再插入一条主键值为320的用户记录，那就需要再分配一个新的页去存放目录了。

从图中可以看出，我们插入了一条主键值为320的用户记录之后需要两个新的数据页：

• 为存储该用户记录而新生成了页31。

• 因为原先存储目录项记录的页30的容量已满（我们前边假设只能存储4条目录项记录），所以不得不需要一个新的页32来存放页31对应的目录项。

再次以查找主键为8的记录为例。

• 确定目录项在哪个页。页30的目录项记录的主键值的范围是[1, 320)，页32表示的目录项记录的主键值范围[320, +∞)，显然主键值为8的记录在页30中。
• 通过目录页确定用户记录页。因为5< 8< 12，所以用户记录在页28中。
• 在真正用户记录页中定位到具体记录。这个就是单链表遍历即可。

如果表中的数据确实是很多，会产生很多存储目录项的页，那怎么根据主键值快速定位一个存储目录项记录的页呢？那就是为这些存储目录的页再生成一个更高级的目录页，就像是一个多级目录一样。

我们生成了一个存储更高级目录项的页33，这个页中的两条记录分别代表页30和页32，如果用户记录的主键值在[1, 320)之间，则到页30中查找更详细的目录项记录，如果主键值范围是[320, +∞)，就到页32中查找更详细的目录项记录。随着表中记录的增加，这个目录的层级会继续增加。

假如叶子结点代表的数据页可以存放100条用户记录，所有目录页可以存放1000条目录项记录，那么：

• 如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
• 如果B+树有2层，最多能存放1000×100=100000条记录。
• 如果B+树有3层，最多能存放1000×1000×100=100000000条记录。
• 如果B+树有4层，最多能存放1000×1000×1000×100=100000000000条记录。（一千亿条记录）

B+树一般不超过4层，可以通过二分法快速定位记录，然后小范围遍历即可。

1.4.聚集索引

刚刚上面几节所画的B+树全部都是按照聚集索引(主键值)排序的，可以说B+树本身就是一个索引。聚集索引有以下2个特点：

• 使用主键值大小进行记录和页的排序，都是按照主键值升序排列，记录之间是单链表结构，页与页之间是双链表结构。
• B+树的叶子结点是完整的用户记录，也就是叶子结点存储了所有列的值(包括隐藏列)。

具有这2个特点的B+树称为聚集索引，聚集索引不需要显式使用INDEX语句创建，如下：

CREATE INDEX 索引名称 ON 表名(字段);
ALTER TABLE 表名 ADD INDEX 索引名称(字段); 

innodb自动为我们创建聚集索引。

注意：
  有一个点很容易混淆，走PRIMARY索引和全表扫描有什么区别呢？他们其实都是在聚集索引上操作的（聚集索引B+树的叶子结点是根据主键排好序的完整的用户记录，包含表里的所有字段），区别就在于：
  全表扫描将聚集索引B+树的叶子结点依次顺序扫描并判断条件，在以下几种情况会走全表扫描：
  
    1.select * from demo_info这种无条件的查询语句
    2.select * from demo_info where common_field != 'a'这种条件字段common_field没有建索引的情况
    3.select * from demo_info order by key1 limit 10000, 1条件字段key1建了索引但是MySQL认为走二级索引的成本比全表扫描成本高的情况。
  
  PRIMARY索引是利用二分思想将聚集索引B+树到指定范围区间进行扫描，比如select * from demo_info where id in (1, 2)这种条件字段是主键id，可以很好的利用到索引进行二分的快速查询。

聚集索引一张表只能有一个，创建的主键索引其实就是聚集索引，如果未定义主键，MYSQL会默认选择非空的唯一索引当作主键，否则会默认生成一个主键（不可见）。

1.5.非聚集索引(二级索引)

若查询的时候，where条件并不是按照主键id去筛选条件。如果除了主键使用默认聚集索引以外，并没有手动添加其他的索引。那么根据条件去筛选的时候就只能沿着链表依次遍历了。

这时可以通过建不同的B+树（可以采用不同的排序规则）来优化。比如刚刚的index_demo 表中的c2列的值的大小作为记录和页的排序规则，再建这样一个规则的B+树：

• B+树的叶子结点存储的不是完整的用户记录，没有全部列的信息，只有c2列和主键列这两个列的值。
• 目录项记录不再是主键+页号，而是c2列+页号。
• 使用记录c2列的大小进行记录和页的排序。不管是用户记录页还是目录页，都是按照c2列的大小升序排列的单链表结构。

创建了非聚集索引后，现在想要查询where c2 = 4的记录的步骤如下：

• 在一级目录页（第三格是c1，第四格是页号）确定第一条符合c2 = 4的目录项所在的页。
• 在二级目录页（第三格是缩小范围的c1，第四格是缩小范围的页号）页确定第一条符合c2 = 4的用户记录所在的页。
• 在用户记录页（第三格是c1，第四格是主键）找到符合c2 = 4的第一条用户记录。找到该记录后，**由于只存储了c2列和主键c1，所以需要根据该记录的主键信息到聚集索引中查找完整的用户记录（回表）。**然后返回非聚集索引的叶子节点处，找到刚才定位到的符合条件的记录，继续往后扫描，因为c2列没有唯一键unique约束，可能满足c2 = 4的有多条记录，搜索完本页就跳到下一页第一条记录继续往后扫描，每找到一条满足的记录就进行一次回表操作，重复这个过程，直到下一条记录不满足c2 = 4为止。

1.6.联合索引

可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比如想让B+树按照c2和c3列的大小进行排序，那么

• 先把各个记录和页按照c2列进行排序。

• 在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3列建立的索引，如下：

每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。可以看图中的页50和页55，当c2 = 4的时候，c3按照从小到大的顺序一次是"a"，“o”，“u”，

每条用户记录由c2、c3和主键c1列组成。

• 一级目录页：第三格是c1，第四格是c3，第五格是页号
• 二级目录页：第三格是缩小范围的c1，第四格是跟随c2具体的c3，第五格是缩小范围的页号
• 用户记录页：第5格是主键

1.7.避免回表

比如有一个联合索引idx_c2_c3(c2, c3)，select c3 from 表名 where c2 = 4;就只需要查询一次辅助索引就可以了，因为需要查询的值正好是索引之一，一棵索引树上就能获取SQL所需的列数据（索引覆盖），无需回表，速度更快。

2.索引操作

准备表：

create table indextest(
primaryC varchar(20),
indexC varchar(20),
combineC1 varchar(20),
combineC2 varchar(20),
uniqueC varchar(20),
fulltextC varchar(20),
c1 int,
c2 int,
c4 int,
c5 int
);

insert into indextest values ('1','1','1','1','1','1',1,1,1,1,1);
insert into indextest values ('2','2','2','2','2','2',2,2,2,2,2);

2.1.索引删除

-- 删除索引
	DROP INDEX 索引名 ON 表名;
-- 删除主键索引
	ALTER TABLE 表名 DROP PRIMARY KEY;

2.2.显示索引信息

SHOW INDEX FROM 表名;

Table：表名

Non_unique：若为0则是唯一索引，为1不是唯一索引

Key_name：索引名称，若名称相同则是联合索引

Seq_in_index：索引序列号，若是1表示从1开始

Column_name：索引的列名

Collation：字符序，'A’是升序排列

Cardinality：基数，索引不同值得个数估计

Sub_part：前置索引。如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL

Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案。

Null：如果表中的列含有NULL，则含有YES

Index_type：索引类型。共分为四种： FULLTEXT、HASH、BTREE 、RTREE

• FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

• HASH由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。 HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

• BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

• RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

mysql> show index from indextest;
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| indextest |          0 | PRIMARY   |            1 | primaryC    | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| indextest |          0 | uniqueC   |            1 | uniqueC     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| indextest |          1 | indexC    |            1 | indexC      | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| indextest |          1 | combineC1 |            1 | combineC1   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| indextest |          1 | combineC1 |            2 | combineC2   | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| indextest |          1 | fulltextC |            1 | fulltextC   | NULL      |           0 |     NULL |   NULL | YES  | FULLTEXT   |         |               | YES     | NULL       |
+-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
6 rows in set (0.01 sec)

2.3.explain分析sql执行的情况

-- 增加一个全文索引
ALTER TABLE `student` ADD FULLTEXT INDEX `StudentName`(`StudentName`);

-- EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(StudentName) AGAINST('d'); -- 全文索引

3.索引分类

3.1.主键索引

（PRIMARY KEY）

• 最常见的索引类型，不允许为空值
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

-- 创建表的时候指定主键索引
CREATE TABLE tableName(
  ......
  PRIMARY key (columeName)
)

-- 修改表结构添加主键索引
ALTER TABLE tableName ADD PRIMARY key (columnName)

3.2.普通索引

（KEY / INDEX）

默认的，快速定位特定数据

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

-- 直接创建普通索引
CREATE INDEX indexName ON tableName (columnName)

-- 创建表的时候指定普通索引
CREATE TABLE tableName(
  ......
  INDEX indexName (columeName)
)

-- 修改表结构添加普通索引
ALTER TABLE tableName ADD INDEX indexName(columnName)

#联合索引：括号多加个字段，逗号分隔

3.3.唯一索引

（UNIQUE KEY）

• 它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值

• 与主键索引的区别：主键索引只能有一个、唯一索引可以有多个

• 创建唯一约束会自动创建索引

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)

-- 创建表的时候指定唯一索引
CREATE TABLE tableName(  
	......
	UNIQUE INDEX [indexName] (columeName)  
);  

-- 修改表结构添加唯一索引
ALTER TABLE tableName ADD UNIQUE INDEX [indexName] (columnName)

3.4.全文索引

（FULLText）

快速定位特定数据（百度搜索就是全文索引）

• 在特定的数据库引擎下才有：MyISAM
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

-- 增加一个全文索引
ALTER TABLE tableName ADD FULLTEXT INDEX indexName(columnName);

十一、事务

要么都成功，要么都失败。

事务操作原理: 事务开启之后, 所有的操作都会临时保存到事务日志, 事务日志只有在得到commit命令才会同步到数据表,其他任何情况都会清空(rollback, 断电, 断开连接)。

1.事务原则：ACID

1.1.原子性

原子性（Atomicity）是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

针对同一个事务

这个过程包含两个步骤

A：800 - 200 = 600

B：200 + 200 = 400

原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作。

1.2.一致性

（Consistency）针对一个事务操作前与操作后的状态一致。

操作前A：800，B：200

操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算。

1.3.持久性

（Durability）表示事务结束后的数据不随着外界原因导致数据丢失。

操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400

1.4.隔离性

（Durability）针对多个用户同时操作，主要是排除其他事务对本次事务的影响。

事务一：A向B转账200

事务二：C向B转账100

两个事务同时进行，其中一个事务读取到另外一个事务还没有提交的数据，执行步骤如图所示，按照数字顺序执行。

隔离性用于解决以上问题。

*脏读

一个事务读取了第二个事务未提交的数据，当第二个事务回滚了数据之后，第一个事务就读取到了无效的数据。

如下图，事务1查询course_id=59的平均分score为9.2，而事务2此时将其平均分修改为9.6，当事务1再次读取的时候，平均分就变成了9.6，此时事务2回滚，事务1就是读取的无效数据，简称脏读。

1先查数据库

2把数据库改了

1再查数据库

2把操作回滚

导致1第二次读了无效数据

*不可重复读

一个事务前后两次读取的同一数据不一致。

如下图，事务1查询course_id=59的平均分score为9.6，而事务2此时将其平均分修改为9.7，并将修改提交，当事务1再次读取的时候，平均分就变成了9.7，事务1就是读取的错误数据，注意，不可重复读和脏读的区别就是，脏读的数据会回滚，不可重复读会把数据提交，脏读的数据是无效的，而不可重复读因为事务2的提交，数据是有效的。

1先查数据库

2把数据库改了然后提交

1再查数据库然后提交

导致1两次读的数据有效但不一致

*幻读

指一个事务两次查询的结果集记录数不一致。

如下图，事务1查询到平均分在9.5到9.8之间的记录数是2条，经过事务2对course_id=43的平均分修改，导致事务1第二次查询的记录数为3条，这种情况就叫幻读，幻读的数据最终也是有效的数据。

2.事务操作

2.1.手动操作

事务操作分为两种: 自动事务(默认的), 手动事务

默认情况下，MySQL执行每条SQL语句都会自动开启和提交事务。为了让多条SQL语句纳入到一个事物之下，可以手动管理事务。

start transaction;

sql语句;
sql语句;
...;

[COMMIT | ROLLBACK];

准备数据：

create table transactiontest(
c1 int,
c2 int
);

insert into transactiontest values (1,1);
insert into transactiontest values (2,2);
insert into transactiontest values (3,3);

开启事务：

START TRANSACTION;

DELETE FROM transactiontest;
SELECT * FROM transactiontest;

得到：

mysql> SELECT * FROM transactiontest;
Empty set (0.00 sec)

但如果：

show tables;

发现transactiontest表还在：

mysql> show tables;
+-----------------+
| Tables_in_test  |
+-----------------+
| class           |
| department      |
| employee        |
| indextest       |
| stu             |
| student         |
| student01       |
| student02       |
| student03       |
| student04       |
| student05       |
| student06       |
| transactiontest |
+-----------------+
13 rows in set (0.00 sec)

因为开启了事务，现在的操作还在redo日志里面，并没有同步到数据库文件里面，只有使用COMMIT之后才会同步，也可以执行ROLLBACK回滚。这样redo日志被清空，下次操作的时候重新往redo日志里面进行操作，就不会受到上一次操作的影响。

2.2.自动事务处理

在mysql中默认的都是自动事务处理, 用户操作完会立即同步到数据表中。系统通过autocommit变量控制自动事务。

#查看自动提交
show variables like 'autocommit';

#关闭自动提交
set autocommit = 0;

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.02 sec)

自动提交关闭之后,需要每次执行sql语句后手动来选择处理: commit提交, rollback回滚。

注意: 通常都会使用自动事务。

2.3.回滚点

回滚点：在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功。

可以在当前成功的位置，设置一个点——可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

#设置回滚点
savepoint 回滚点名字;

#回到回滚点
rollback to 回滚点名字;

3.事务隔离级别

设置事务隔离级别的语法如下：

SET PERSIST | GLOBAL | SESSION
    TRANSACTION ISOLATION LEVEL
    READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE;
 
-- PERSIST:所有连接到mysql服务的新的连接都有效，并且mysql服务器重启后也不会丢失修改
-- GLOCAL: 所有连接到mysql服务的新的连接都有效，但是mysql服务器重启后会丢失这个修改
-- SESSION：开发最常用，只会影响到当前连接，当前连接断开，这个隔离级别的修改就会丢失

-- 开发中也可以用show variables like '%iso%'查看当前session的隔离级别

3.1.read uncommitted

假如A和B都在买同一辆车的车票，此时还剩最后一张票，A点击购买，但是还没付款提交，因为查看不到事务之间的临时数据，所以B查看时，也还剩一张票，于是B点击购买，立即付款提交，结果A就会购买失败。所以理想的情况应该是，当A点击购买去付款时，B应该看得到这个临时数据，显示没有票才对。这种场景会出现脏读、幻读、不可重复读情况，隔离性最低，并发性最高。

已知transactiontest表：

mysql> select * from transactiontest;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)

开启事务1，不提交：

START TRANSACTION;
UPDATE transactiontest SET c2=666;

开启事务2（如在windows另一个cmd窗口），提交：

START TRANSACTION;
select * from transactiontest;
COMMIT;

注意：这里没有修改数据，仅仅只是select查询数据，redo日志没有改变，所以不会做同步到文件的操作，commit之后会清空对应的undo日志数据。

结果如下，前者在事务1中修改c1为1，事务2中却看不到：

mysql> select * from transactiontest;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    3 |    3 |
|    4 |    4 |
+------+------+
4 rows in set (0.00 sec)

如果修改事务2隔离级别，如下：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; /*代表可以读取其他事务未提交的数据*/

START TRANSACTION;
select * from transactiontest;
COMMIT;

就可得到事务1中未提交的数据（此时事务1仍未提交）：

mysql> select * from transactiontest;
+------+------+
| c1   | c2   |
+------+------+
|    1 |  666 |
|    2 |  666 |
|    3 |  666 |
|    4 |  666 |
+------+------+
4 rows in set (0.00 sec)

另一个事务修改不用提交，本事务都能查询到修改内容

3.2.read commited

银行转账的场景，账户有5000，事务1从账户存1000，事务2从账户取100（未提交），如果事务1能读取到事务3未提交的数据，那么事务1查询账户就会查到5900，若此时事务2回滚，此时账户就只有5900块了，凭空消失100块，所以只有A事务读取到B事务提交后的数据才能保证转账的正确性。这种场景就和买票的场景完全不同。这种场景是会出现幻读和不可重复读的。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;/*只能读取其他事务提交的数据*/

另一个事务修改必须提交，本事务才能查询到

3.3.repeatable read

在淘宝购买东西，支付时，此时显示的价格是undo日志的价格，如果此时卖家涨价，你购买的还是涨价之前的价格，这种场景就是可重复读。可重复读不会出现脏读、不可重复读的情况，因为事务1读取不到事务2对数据的修改。对于幻读，这里只有靠临键锁才能保证不出现幻读的问题。

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;/*事务在执行中反复读取数据，得到的结果是一致的*/

已知transactiontest表：

mysql> select * from transactiontest;
+------+-------+
| c1   | c2    |
+------+-------+
|    1 | 11111 |
|    2 | 11111 |
|    3 | 11111 |
|    4 | 11111 |
+------+-------+
4 rows in set (0.00 sec)

开启事务1：

START TRANSACTION;
select * from transactiontest;

这里一定要先执行一次select语句，保证undo日志拷贝过一次数据。

开启事务2：

START TRANSACTION;
UPDATE transactiontest SET c2=00000;
commit;

在事务1再次执行查询：

select * from transactiontest;

mysql> select * from transactiontest;
+------+-------+
| c1   | c2    |
+------+-------+
|    1 | 11111 |
|    2 | 11111 |
|    3 | 11111 |
|    4 | 11111 |
+------+-------+
4 rows in set (0.00 sec)

虽然数据库表文件的数据已经修改了，但是事务1处的事务隔离级别是可以反复读，每次都从undo日志里面读取，所以这里还是修改前的数据，直到事务1提交commit，commit之后清空对应的undo日志记录，下次会重新从数据库文件里面拷贝数据，才能得到事务2中修改并提交的数据数据。MySQL默认事务隔离级别就是REPEATABLE READ。

本事务先查询，另一个事务修改并提交，本事务再查询，仍是修改前的数据

3.4.serializable

由于事务并发执行所带来的各种问题，前三种隔离级别只适用于在特定业务场景中，凡事序列化的隔离性，让事务逐一执行，就不会产生上述问题了。但是序列化的隔离级别使用的特别少，它让事务的并发性大大降低。serializable不会出现幻读、脏读、不可重复读的情况，因为事务1读取不到事务2对数据的修改。隔离性最高，并发性最低，其实就是没有并发，所有事务按照顺序执行。

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;/*事务序列化*/

已知transactiontest表：

mysql> select * from transactiontest;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    0 |
|    2 |    0 |
|    3 |    0 |
|    4 |    0 |
+------+------+
4 rows in set (0.00 sec)

开始事务1：

START TRANSACTION;
UPDATE transactiontest SET c2=1234;

开启事务2：

START TRANSACTION;
select * from transactiontest;

但是这行sql之后并没有出结果：

mysql> select * from transactiontest;

因为事务1还没有提交，事务1执行commit之后，事务2就会立即执行查询结果：

mysql> select * from transactiontest;
+------+------+
| c1   | c2   |
+------+------+
|    1 | 1234 |
|    2 | 1234 |
|    3 | 1234 |
|    4 | 1234 |
+------+------+
4 rows in set (6.55 sec)

十二、视图

视图: view, 是一种有结构（有行有列）但是没结果（结构中不真实存放数据）的虚拟表, 虚拟表的结构来源不是自己定义, 而是从对应的基表中产生（视图的数据来源）。

• 视图可以节省SQL语句: 将一条复杂的查询语句使用视图进行保存: 以后可以直接对视图进行操作
• 数据安全: 视图操作是主要针对查询的, 如果对视图结构进行处理(删除), 不会影响基表数据(相对安全).
• 视图往往是在大项目中使用, 而且是多系统使用: 可以对外提供有用的数据, 但是隐藏关键(无用)的数据: 数据安全
• 视图可以对外提供友好型: 不同的视图提供不同的数据, 对外好像专门设计
• 视图可以更好(容易)的进行权限控制

1.创建视图

Create view 视图名字 as select语句; -- select语句可以是普通查询;可以是连接查询; 可以是联合查询; 可以是子查询.

• 创建单表视图：基表只有一个

• 创建多表视图：基表来源至少两个

2.查看视图

视图是一张虚拟表：表，表的所有查看方式都适用于视图：

show tables [like];
desc 视图名字;
show create table 视图名;

视图一旦创建，系统会在视图对应的数据库文件夹下创建一个对应的结构文件：frm文件。

3.使用视图

使用视图主要是为了查询，将视图当做表一样查询即可。

视图的执行其实本质就是执行封装的select语句。

4.修改视图

视图本身不可修改，但是视图的来源是可以修改的。修改视图就是修改视图本身的来源语句(select语句)：

Alter view 视图名字 as 新的select语句;

5.删除视图

Drop view 视图名字;

6.视图数据操作

太蠢了，算了。

十三、触发器

触发器不推荐使用，触发操作能在业务层解决就在业务层解决，否则很难维护，而且容易产生死锁。

create trigger triggerName  
after/before insert/update/delete on 表名  
for each row   #这句话在mysql是固定的  
begin  
    #可以加个 if 判断条件 then
    sql语句;  
end;  

不管触发器是否触发了，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后新的状态给分别保留下来，供触发器使用。其中, 要操作的当前状态保存到old中，操作之后的可能形态保存给new。

• 删除的时候是没有new的，插入的时候是没有old

• 使用方式：old.字段名 / new.字段名（new代表的是假设发生之后的结果）

十四、范式

1.1NF

1NF——原子性

在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前还需要额外的处理(拆分)，那么说表的设计不满足第一范式。

第一范式是数据库的基本要求，不满足第一范式就不是关系型数据库。

数据表的每一列都是不可分割的基本数据项，同一列中不能有多个值，也不能存在重复的属性。

2.2NF

2NF——唯一性

在数据表设计的过程中，如果有复合主键（如双字段主键），且表中有字段并不是由整个主键来确定，而是依赖复合主键中的其中一个字段（主键的部分）：存在字段依赖主键的部分的问题，称之为部分依赖。第二范式就是要解决表设计不允许出现部分依赖。

数据表中的每条记录必须是唯一的。为了实现区分，通常要为表加上一个列来存储唯一标识，这个唯一属性列被称作主键列。

3.3NF

3NF——关联性

理论上讲，应该一张表中的所有字段都应该直接依赖主键，如果表设计中存在一个字段，并不直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键。把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。第三范式就是要解决传递依赖的问题。

依照第三范式，数据可以拆分到不同的数据表，彼此保持关联：