数据库——MySQL

数据库——MySQL

数据库(Database)就是按照数据结构来组织，存储和管理数据的仓库。专业的数据库是专门对数据进行创建，访问，管理，搜索等操作的软件，比起我们自己用文件读写的方式对象数据进行管理更加的方便，快速，安全。

数据库分两大类，一类是 关系型数据库。另一类叫做 非关系型数据库。

• 关系型数据库： MySQL，Oracle，PostgreSQL，SQLserver。。。。
• 非关系型数据库：Redis内存数据库，MongoDB文档数据库。。。

1.MySQL的基本命令

SQL语句中的快捷键

• \G 格式化输出（文本式，竖立显示）
• \s 查看服务器端信息
• \c 结束命令输入操作
• \q 退出当前sql命令行模式
• \h 查看帮助

登录mysql,在终端输入以下命令，进行登录：mysql -u root -p

数据库操作

• 查看数据库 show databases;
• 创建数据库 create database 库名 default charset=utf8;
• 删除数据库 drop database 库名；
• 打开数据库 use 库名；

数据表操作

数据库管理系统中,可以有很多库,每个数据库中可以包括多张数据表。

• 查看表: show tables;
• 创建表: create table 表名(字段名1 类型,字段名2 类型)engine=innodb default charset=utf8;
• 创建表: 如果表不存在,则创建, 如果存在就不执行这条命令：create table if not exists 表名(字段1 类型,字段2 类型);
• 删除表: drop table 表名;
• 表结构: desc 表名;
• 查看建标语句：show create table users;

修改表结构：语法格式：alter table 表名 action (更改的选项) ；

添加字段：alter table 表名 add 添加的字段信息 （after 字段，first）；

删除字段：alter table 表名 drop 被删除的字段名；

修改字段：alter table 表名 change|modify 被修改的字段信息；。change: 可以修改字段名，modify: 不能修改字段名。

注意：一般情况下，无特殊要求，不要轻易修改表结构

修改表名：alter table 原表名 rename as 新表名；

更改表中的自增的值：alter table 表名 自增对象 = 数值;

修改表引擎：

推荐在定义表时，表引擎为 innodb。通过查看建表语句获取当前的表引擎：show create table 表名\G;

直接查看当前表状态信息：show table status from 库名 where name = ‘表名’\G;

修改表引擎语句：alter table 旧表名 engine = ‘新表名’;

数据操作-增删改查

插入

• insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
• insert into 表名(字段1,字段2,字段3) values(a值1,a值2,a值3),(b值1,b值2,b值3);

查询

• select * from 表名;
• select 字段1,字段2,字段3 from 表名;
• select * from 表名 where 字段=某个值;

修改

• update 表名 set 字段=某个值 where 条件;
• update 表名 set 字段1=值1,字段2=值2 where 条件;
• update 表名 set 字段=字段+值 where 条件;

删除

• delete from 表名 where 字段=某个值;

2.MySQL的数据类型

1、字符串数据类型

最常用的数据类型是串数据类型。它们存储串，如名字、地址、电 话号码、邮政编码等。不管使用何种形式的串数据类型，串值都必须括在引号内。有两种基本的串类型，分别为定长串和变长串。

• 定长串：char

1.接受长度固定的字符串，其长度是在创建表时指定的。定长列不允许存储多于指定长度字符的数据。

2.指定长度后，就会分配固定的存储空间用于存放数据

• 变长串 varchar

存储可变长度的字符串 varchar(7) 如果实际插入4个字符, 那么它只占4个字符位置,当然插入的数据长度不能超过7

个字符。

注意：MySQL处理定长列远比处理变长列快得多。

2.数值类型

数值数据类型存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。支持的取值范围越大，所需存储空间越多与字符串不一样，数值不应该括在引号内。

3.日期和时间类型

MySQL使用专门的数据类型来存储日期和时间值

4.二进制数据类型

二进制数据类型可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等

5.表的字段约束

• unsigned 无符号(给数值类型使用，表示为正数，不写可以表示正负数都可以)

• 字段类型后面加括号限制宽度

  char(5). varchar(7) 在字符类型后面加限制 表示 字符串的长度

  int(4) 没有意义，默认无符号的int为int(11)，有符号的int(10)

  int(4) unsigned zerofifill只有当给int类型设置有前导零时，设置int的宽度才有意义。

• not null 不能为空,在操作数据库时如果输入该字段的数据为NULL ，就会报错

• default 设置默认值

• primary key 主键不能为空,且唯一，一般和自动递增一起配合使用。

• auto_increment 定义列为自增属性，一般用于主键，数值会自动加1

• unique 唯一索引(数据不能重复:用户名)可以增加查询速度,但是会降低插入和更新速度

6.MySQL的运算符

• 算术运算符： +、 -、 *、 /、 %
• 比较运算符： =、 >、 <、 >=、 <=、!=
• 数据库特有的比较： in、not in、is null、is not null、like、between、and
• 逻辑运算符： and、or、not
• like: 支持特殊符号%和_ ;

7.主键

1、表中每一行都应该有可以唯一标识自己的一列，用于记录两条记录不能重复，任意两行都不具有相同的主键值。

2、应该总是定义主键 虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。

要求：

• 记录一旦插入到表中，主键最好不要再修改。
• 不允许NULL 。
• 不在主键列中使用可能会更改的值。 （例如，如果使用一个名字作为主键以标识某个供应商，当该供应商合并和更改其名字时，必须更改这个主键。）
• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键。
• 可以使用多个列作为联合主键，但联合主键并不常用。使用多列作为主键时，所有列值的组合必须是唯一的。

3.MySQL数据库表引擎与字符集

1.服务器处理客户端请求

不论客户端进程和服务器进程是采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本（MySQL语句），服务器进程处理后再向客户端进程发送一段文本（处理结果）。

2.存储引擎

MySQL 服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能， MySQL 提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的 MySQLserver 层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、“读取索引下一条内容”、"插入记录"等等。

所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

MySQL 支持非常多种存储引擎：

3.MyISAM和InnoDB表引擎的区别

1. 事务支持：MyISAM不支持事务，而InnoDB支持。

2. 存储结构：MyISAM：每个MyISAM在磁盘上存储成三个文件。 InnoDB：主要分为两种文件进行存储

3. 表锁差异：

   MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

   InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

4. 表主键：

   MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

   InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。InnoDB的主键范围更大，最大是MyISAM的2倍。

5. 表的具体行数：

   MyISAM：保存有表的总行数，如果select count() from table;会直接取出出该值。

   InnoDB：没有保存表的总行数(只能遍历)，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

6. CURD操作：

   MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

7. 外键：MyISAM：不支持；InnoDB：支持

8. 查询效率：

   MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。

   推荐考虑使用InnoDB来替代MyISAM引擎，原因是InnoDB自身很多良好的特点，比如事务支持、存储 过程、视图、行级锁定等等，在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多。

   另外，任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。如果不是很复杂的Web应用，非关键应用，还是可以继续考虑MyISAM的，这个具体情况可以自己斟酌。

9. MyISAM和InnoDB两者的应用场景：

   MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。 InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。现在默认使用InnoDB。

4.MySQL中的常用字符集

• ASCII 字符集

  共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码。

• ISO 8859-1 字符集

  共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。

• GB2312 字符集

  收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容 ASCII 字符集，所以在编码方式上显得有些奇怪：

  如果该字符在 ASCII 字符集中，则采用1字节编码，否则采用2字节编码。

  这种表示一个字符需要的字节数可能不同的编码方式称为 变长编码方式 。比方说字符串 ‘爱u’ ，其中 ‘爱’ 需要用2个字节进行编码，编码后的十六进制表示为 0xCED2 ， ‘u’ 需要用1个字节进行编码，编码后的十六进制表示为 0x75 ，所以拼合起来就是 0xCED275 。

• GBK 字符集

  GBK 字符集只是在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。

• utf8 字符集

  收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节

  utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。

  utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。

4.MySQL数据查询

语法格式： select 字段列表|* from 表名 [where 搜索条件] [group by 分组字段 [having 分组条件]] [order by 排序字段 排序规则] [limit 分页参数]

1.Where条件查询

• 可以在where子句中指定任何条件
• 可以使用 and 或者 or 指定一个或多个条件
• where条件也可以运用在update和delete语句的后面
• where子句类似程序语言中if条件，根据mysql表中的字段值来进行数据的过滤

示例：

-- 查询users表中 age > 22的数据 
select * from users where age > 22; 
-- 查询 users 表中 name=某个条件值 的数据 
select * from users where name = '王五'; 
-- 查询 users 表中 年龄在22到25之间的数据 
select * from users where age >= 22 and age <= 25; 
select * from users where age between 22 and 25; 
-- 查询 users 表中 年龄不在22到25之间的数据 
select * from users where age < 22 or age > 25; 
select * from users where age not between 22 and 25; 
-- 查询 users 表中 年龄在22到25之间的女生信息 
select * from users where age >= 22 and age <= 25 and sex = '女';

注意 and和or使用

select * from users where age=22 or age = 25 and sex = '女'; 
+------+--------+------+-------+-------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+------+--------+------+-------+-------+------+------+ 
| 1 | 章三 | 22 | | NULL | 男 | 0 | 
| 1002 | cc | 25 | 123 | NULL | 女 | NULL | 
+------+--------+------+-------+-------+------+------+ 
2 rows in set (0.00 sec) 
-- 上面的查询结果并不符合 查询条件的要求。 
-- 问题出在 sql 计算的顺序上，sql会优先处理and条件，所以上面的sql语句就变成了 
-- 查询变成了为年龄22的不管性别，或者年龄为 25的女生 
-- 如何改造sql符合我们的查询条件呢？ 
-- 使用小括号来关联相同的条件 
select * from users where (age=22 or age = 25) and sex = '女'; 
+------+------+------+-------+-------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+------+------+------+-------+-------+------+------+ 
| 1002 | cc | 25 | 123 | NULL | 女 | NULL | 
+------+------+------+-------+-------+------+------+ 
1 row in set (0.00 sec) 

2.Like子句

我们可以在where条件中使用=,<,> 等符合进行条件的过滤，但是当想查询某个字段是否包含时如何过滤？可以使用like语句进行某个字段的模糊搜索，例如： 查询 name字段中包含五的数据 。

-- like 语句 like某个确定的值 和。where name = '王五' 是一样 
select * from users where name like '王五'; 
+----+--------+------+-------+-----------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+----+--------+------+-------+-----------+------+------+ 
| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 | 
+----+--------+------+-------+-----------+------+------+ 
1 row in set (0.00 sec) 
-- 使用 % 模糊搜索。%代表任意个任意字符 
-- 查询name字段中包含五的 
select * from users where name like '%五%';
-- 查询name字段中最后一个字符 为 五的 
select * from users where name like '%五'; 
-- 查询name字段中第一个字符 为 王 的 
select * from users where name like '王%'; 
-- 使用 _ 单个的下划线。表示一个任意字符，使用和%类似 
-- 查询表中 name 字段为两个字符的数据 
select * from users where name like '__'; 
-- 查询 name 字段最后为五，的两个字符的数据 
select * from users where name like '_五';

注意：where子句中的like在使用%或者_进行模糊搜索时，效率不高，使用时注意：

• 尽可能的不去使用%或者_
• 如果需要使用，也尽可能不要把通配符放在开头处

3.Mysql中的统计函数（聚合函数）

# 计算 users 表中 最大年龄，最小年龄，年龄和及平均年龄 
select max(age),min(age),sum(age),avg(age) from users; 
+----------+----------+----------+----------+ 
| max(age) | min(age) | sum(age) | avg(age) | 
+----------+----------+----------+----------+ 
| 28 | 20 | 202 | 22.4444 |
+----------+----------+----------+----------+ 
-- 上面数据中的列都是在查询时使用的函数名，不方便阅读和后期的调用，可以通过别名方式 美化 
select max(age) as max_age, 
min(age) min_age,sum(age) as sum_age, 
avg(age) as avg_age 
from users; 
+---------+---------+---------+---------+ 
| max_age | min_age | sum_age | avg_age | 
+---------+---------+---------+---------+ 
| 28 | 20 | 202 | 22.4444 | 
+---------+---------+---------+---------+ 
-- 统计 users 表中的数据量 
select count(*) from users; 
+----------+ 
| count(*) | 
+----------+ 
| 9 | 
+----------+ 
select count(id) from users; 
+-----------+ 
| count(id) | 
+-----------+
| 9 | 
+-----------+ 
-- 上面的两个统计，分别使用了 count(*) 和 count(id),结果目前都一样，有什么区别？ 
-- count(*) 是按照 users表中所有的列进行数据的统计，只要其中一列上有数据，就可以计算 
-- count(id) 是按照指定的 id 字段进行统计，也可以使用别的字段进行统计， 
-- 但是注意，如果指定的列上出现了NULL值，那么为NULL的这个数据不会被统计 
-- 假设有下面这样的一张表需要统计 
+------+-----------+------+--------+-----------+------+------+ 
| id | name | age | phone | email | sex | mm | 
+------+-----------+------+--------+-----------+------+------+ 
| 1 | 章三 | 22 | | NULL | 男 | 0 | 
| 2 | 李四 | 20 | | NULL | 女 | 0 | 
| 5 | 王五 | 24 | 10011 | ww@qq.com | 男 | 0 | 
| 1000 | aa | 20 | 123 | NULL | 女 | NULL | 
| 1001 | bb | 20 | 123456 | NULL | 女 | NULL | 
| 1002 | cc | 25 | 123 | NULL | 女 | NULL | 
| 1003 | dd | 20 | 456 | NULL | 女 | NULL | 
| 1004 | ff | 28 | 789 | NULL | 男 | NULL | 
| 1005 | 王五六 | 23 | 890 | NULL | NULL | NULL | 
+------+-----------+------+--------+-----------+------+------+ 
9 rows in set (0.00 sec) 
-- 如果按照sex这一列进行统计，结果就是8个而不是9个，因为sex这一列中有NULL值存在 
mysql> select count(sex) from users; 
+------------+ 
| count(sex) | 
+------------+ 
| 8 | 
+------------+ 

聚合函数除了以上简单的使用意外，通常情况下都是配合着分组进行数据的统计和计算

4.Group BY 分组

group by 语句根据一个或多个列对结果集进行分组，一般情况下，是用与数据的统计或计算，配合聚合函数使用。

-- 统计 users 表中 男女生人数, 
-- 很明显按照上面的需要，可以写出两个语句进行分别统计 
select count(*) from users where sex = '女'; 
select count(*) from users where sex = '男'; 
-- 可以使用分组进行统计，更方便 
select sex,count(*) from users group by sex; 
+------+----------+ 
| sex | count(*) | 
+------+----------+ 
| 男 | 4 | 
| 女 | 5 | 
+------+----------+
-- 统计1班和2班的人数 
select classid,count(*) from users group by classid; 
+---------+----------+ 
| classid | count(*) | 
+---------+----------+ 
| 1 | 5 | 
| 2 | 4 | 
+---------+----------+ 
-- 分别统计每个班级的男女生人数 
select classid,sex,count(*) as num from users group by classid,sex; 
+---------+------+-----+ 
| classid | sex | num | 
+---------+------+-----+ 
| 1 | 男 | 2 | 
| 1 | 女 | 3 | 
| 2 | 男 | 2 | 
| 2 | 女 | 2 | 
+---------+------+-----+ 
\# 注意，在使用。group by分组时，一般除了聚合函数，其它在select后面出现的字段列都需要出现在grouop by后面

Having 子句

having时在分组聚合计算后，对结果再一次进行过滤，类似于where，where过滤的是行数据，having过滤的是分组数据。

-- 要统计班级人数 
select classid,count(*) from users group by classid; 
-- 统计班级人数，并且要人数达到5人及以上 
select classid,count(*) as num from users group by classid having num >=5;

5.Order by 排序

我们在mysql中使用select的语句查询的数据结果是根据数据在底层文件的结构来排序的，首先不要依赖默认的排序，另外在需要排序时要使用orderby对返回的结果进行排序。

Asc 升序，默认；desc降序 。

-- 按照年龄对结果进行排序，从大到小 
select * from users order by age desc; 
-- 从小到大排序 asc 默认就是。可以不写 
select * from users order by age; 
-- 也可以按照多个字段进行排序 
select * from users order by age,id; # 先按照age进行排序，age相同情况下，按照id进行排序 
select * from users order by age,id desc; 

6.Limit数据分页

• limit n 提取n条数据，
• limit m,n 跳过m跳数据，提取n条数据

-- 查询users表中的数据，只要3条 
select * from users limit 3; 
-- 跳过前4条数据，再取3条数据 
select * from users limit 4,3; 
-- limit一般应用在数据分页上面 
-- 例如每页显示10条数据，第三页的 limit应该怎么写？ 思考 
第一页 limit 0,10 
第二页 limit 10,10 
第三页 limit 20,10 
第四页 limit 30,10 
-- 提取 user表中 年龄最大的三个用户数据 怎么查询？ 
select * from users order by age desc limit 3; 

5. ⼦查询与表连接

1.⼦查询(嵌套sql)

利⽤⼦查询进⾏过滤

订单存储在两个表中。对于包含订单号、客户ID、订单⽇期的每个订单，orders表存储⼀⾏。 各订单的物品存储在相关的orderitems表中。orders表不存储客户信息。它只存储客户的ID。实际的客户信息存储在customers表中。现在，假如需要列出订购物品TNT2的所有客户，应该怎样检索？

--(1) 检索包含物品TNT2的所有订单的编号。
select order_num from orderitems where prod_id = 'TNT2';
+-----------+
| order_num |
+-----------+
| 20005 |
| 20007 |
+-----------+
--(2) 检索具有前⼀步骤列出的订单编号的所有客户的ID
select cust_id from orders where order_num IN (20005,20007);
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
--(3) 检索前⼀步骤返回的所有客户ID的客户信息。
select cust_name,cust_contact from customers where cust_id in (10001,10004);
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+

可以把其中的WHERE⼦句转换为⼦查询⽽不是硬编码这些SQL返回的数据：

select cust_name,cust_contact
from customers
where cust_id in (select cust_id
 from orders
 where order_num IN (select order_num
 from orderitems
 where prod_id = 'TNT2'));
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+
--为了执⾏上述SELECT语句，MySQL实际上必须执⾏3条SELECT语句。
--最⾥边的⼦查询返回订单号列表，此列表⽤于其外⾯的⼦查询的WHERE⼦句。
--外⾯的⼦查询返回客户ID列表，此客户ID列表⽤于最外层查询的WHERE⼦句。
--最外层查询确实返回所需的数据。

作为计算字段使⽤⼦查询

-- 假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。
-- (1) 从customers表中检索客户列表。
select cust_id,cust_name from customers ;
+---------+----------------+
| cust_id | cust_name |
+---------+----------------+
| 10001 | Coyote Inc. |
| 10002 | Mouse House |
| 10003 | Wascals |
| 10004 | Yosemite Place |
| 10005 | E Fudd |
+---------+----------------+
-- (2) 对于检索出的每个客户，统计其在orders表中的订单数⽬。
select count(*) as orders from orders where cust_id = 10001;
+--------+
| orders |
+--------+
| 2 |
+--------+

为了对每个客户执⾏COUNT()计算，应该将COUNT()作为⼀个⼦查询。

select cust_id,cust_name,
 (select count(*)
 from orders
 where orders.cust_id = customers.cust_id) as orders
 )
from customers
order by cust_name;
+---------+----------------+--------+
| cust_id | cust_name | orders |
+---------+----------------+--------+
| 10001 | Coyote Inc. | 2 |
| 10005 | E Fudd | 1 |
| 10002 | Mouse House | 0 |
| 10003 | Wascals | 6 |
| 10004 | Yosemite Place | 1 |
+---------+----------------+--------+

orders是⼀个计算字段，它是由圆括号中的⼦查询建⽴的。该⼦查询对检索出的每个客户执⾏⼀次。在此例⼦中，该⼦查询执⾏了5次，因为检索出了5个客户。

注意:⼦查询中的WHERE⼦句与前⾯使⽤的WHERE⼦句稍有不同，因为它使⽤了完全限定列名这种类型的⼦查询称为相关⼦查询。任何时候只要列名可能有多义性，就必须使⽤这种语法（表名和列名由⼀个句点分隔）。因为有两个cust_id列，⼀个在customers中，另⼀个在orders中，需要⽐较这两个列以正确地把订单与它们相应的顾客匹配。如果不完全限定列名，MySQL将假定你是对orders表中的cust_id进⾏⾃身⽐较。

2.关系表

SQL最强⼤的功能之⼀就是能在数据检索查询的执⾏中联结（join）表。在能够有效地使⽤联结前，必须了解关系表以及关系数据库设计的⼀些基础知识。

关系表的设计就是要保证把信息分解成多个表，⼀类数据⼀个表。各表通过某些常⽤的值（即关系设计中的关系（relational））互相关联。例如，可建⽴两个表，⼀个存储供应商信息，另⼀个存储产品信息。

-- vendors表包含所有供应商信息
|vend_id | vend_name | vend_address| vend_city ....
-- products表只存储产品信息，它除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。
prod_id | vend_id | prod_name | prod_price | prod_desc

vendors表的主键⼜叫作products的外键，它将vendors表与products表关联，利⽤供应商ID能从vendors表中找出相应供应商的详细信息。 这样做的好处如下：

• 供应商信息不重复，从⽽不浪费时间和空间；
• 如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不⽤改动；
• 由于数据⽆重复，显然数据是⼀致的，这使得处理数据更简单

关系数据可以有效地存储和⽅便地处理。因此，关系数据库的可伸缩性远⽐⾮关系数据库要好。

3.表联结

如果数据存储在多个表中，怎样⽤单条SELECT语句检索出数据？

答案是使⽤联结。简单地说，联结是⼀种机制，⽤来在⼀条SELECT语句中关联表，因此称之为联结。使⽤特殊的语法，可以联结多个表返回⼀组输出，联结在运⾏时关联表中正确的⾏。

例如：我们需要查询出所有的商品及对应的供应商信息怎么办？

-- 联结的创建⾮常简单，规定要联结的所有表以及它们如何关联即可。
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id = products.vend_id
order by vend_name,prod_name;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Detonator | 13.00 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)
--这两个表⽤WHERE⼦句正确联结，WHERE⼦句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。
--可以看到要匹配的两个列以 vendors.vend_id 和 products. vend_id指定。这⾥需要这种完全限定列名，因为如果只给出vend_id，则MySQL不知道指的是哪⼀个（它们有两个，每个表中⼀个）。
--在引⽤的列可能出现⼆义性时，必须使⽤完全限定列名（⽤⼀个点分隔的表名和列名）。

在联结两个表时，你实际上做的是将第⼀个表中的每⼀⾏与第⼆个表中的每⼀⾏配对。WHERE⼦句作为过滤条件，它只包含那些匹配给定条件（这⾥是联结条件）的⾏。

如果上⾯的sql没有where条件时会怎样？select vend_name,prod_name,prod_price from vendors,products

如果没有where条件，第⼀个表中的每个⾏将与第⼆个表中的每个⾏配对，⽽不管它们逻辑上是否可以配在⼀起。由没有联结条件的表关系返回的结果为笛卡⼉积。检索出的⾏的数⽬将是第⼀个表中的⾏数乘以第⼆个表中的⾏数。

其实，对于这种联结可以使⽤稍微不同的语法来明确指定联结的类型。

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id;

两个表之间的关系是FROM⼦句的组成部分，以INNER JOIN指定。在使⽤这种语法时，联结条件⽤特定的ON⼦句⽽不是WHERE⼦句给出。传递给ON的实际条件与传递给WHERE的相同。SQL规范⾸选INNER JOIN语法。

4.联结多个表

SQL对⼀条SELECT语句中可以联结的表的数⽬没有限制。创建联结的基本规则也相同。⾸先列出所有表，然后定义表之间的关系。

select prod_name,vend_name,prod_price,quantity
from orderitems,products,vendors
 where products.vend_id = vendors.vend_id
 and orderitems.prod_id = products.prod_id
 and order_num = 20005;

注意：MySQL在运⾏时关联指定的每个表以处理联结。 这种处理可能是⾮常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

表别名 AS：

别名除了⽤于列名和计算字段外，SQL还允许给表名起别名。这样做有两个主要理由：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使⽤相同的表

注意：表别名只在查询执⾏中使⽤。与列别名不⼀样，表别名不返回到客户机。

5.⾃联结

-- 使⽤⼦查询(嵌套查询)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 使⽤联结的相同查询
select p1.prod_id,p1.prod_name
from products as p1,products as p2
where p1.vend_id = p2.vend_id and p2.prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
-- 此查询中需要的两个表实际上是相同的表，因此products表在FROM⼦句中出现了两次。虽然这是完全合法的，但对products的引⽤具有⼆义性，因为MySQL不知道你引⽤的是products表中的哪个实例。
-- 为解决此问题，使⽤了表别名。products的第⼀次出现为别名p1，第⼆次出现为别名p2。现在可以将这些别名⽤作表名。
--例如，SELECT语句使⽤p1前缀明确地给出所需列的全名。如果不这样，MySQL将返回错误，因为分别存在两个名为prod_id、prod_name的列。MySQL不知道想要的是哪⼀个列（即使它们事实上是同⼀个列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）⾸先联结两个表，然后按第⼆个表中的prod_id过滤数据，返回所需的数据。

注意：⽤⾃联结⽽不⽤⼦查询 ⾃联结通常作为外部语句⽤来替代从相同表中检索数据时使⽤的⼦查询语句。虽然最终的结果是相同的，但有时候处理联结远⽐处理⼦查询快得多。

6.外部链接

许多联结将⼀个表中的⾏与另⼀个表中的⾏相关联。但有时候会需要包含没有关联⾏的那些⾏。例如，可能需要使⽤联结来完成以下⼯作：

• 对每个客户下了多少订单进⾏计数，包括那些⾄今尚未下订单的客户；
• 列出所有产品以及订购数量，包括没有⼈订购的产品；

在上述例⼦中，联结包含了那些在相关表中没有关联⾏的⾏。这种类型的联结称为外部联结。

-- 内部联结。它检索所有客户及其订单：
select customers.cust_id,orders.order_num from customers inner join orders on
customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)
--外部联结语法类似。检索所有客户，包括那些没有订单的客户
select customers.cust_id,orders.order_num from customers left join orders on
customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10002 | NULL |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
6 rows in set (0.00 sec)

聚集函数也可以⽅便地与其他联结⼀起使⽤。如果要检索所有客户及每个客户所下的订单数，下⾯使⽤了COUNT()函数的代码可完成此⼯作，包含那些没有任何下订单的客户。

select customers.cust_name,customers.cust_id,count(orders.order_num) as
num_ord from customers left join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+

• 保证使⽤正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡⼉积。
• 在⼀个联结中可以包含多个表，甚⾄对于每个联结可以采⽤不同的联结类型。虽然这样做是合法的，⼀般也很有⽤，但应该在⼀起测试它们前，分别测试每个联结。这将使故障排除更为简单。

7.组合查询 UNION

MySQL也允许执⾏多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

• UNION必须由两条或两条以上的SELECT语句组成，语句之间⽤关键字UNION分隔（因此，如果组合4条SELECT语句，将要使⽤3个UNION关键字）。
• UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的⽇期类型）。

select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
order by prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1003 | TNT1 | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1001 | ANV01 | 5.99 |
| 1002 | OL1 | 8.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
+---------+---------+------------+
8 rows in set (0.00 sec)
--这条UNION在最后⼀条SELECT语句后使⽤了ORDER BY⼦句。
--虽然ORDER BY⼦句似乎只是最后⼀条SELECT语句的组成部分，但实际上MySQL将⽤它来排序所有SELECT语句返回的所有结果。

6.MySQL 事务

事务（Transaction）是由⼀系列对系统中数据进⾏访问与更新的操作所组成的⼀个程序执⾏逻辑单元。

1.事务的语法

1. start transaction或begin;
2. commit; 使得当前的修改确认
3. rollback; 使得当前的修改被放弃

2.事务的ACID特性

1.原⼦性（Atomicity）

事务的原⼦性是指事务必须是⼀个原⼦的操作序列单元。事务中包含的各项操作在⼀次执⾏过程中，只允许出现两种状态之⼀。全部执⾏成功或全部执⾏失败。

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执⾏过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发⽣⼀样。也就是说事务是⼀个不可分割的整体，就像化学中学过的原⼦，是物质构成的基本单位。

2.⼀致性（Consistency）

事务的⼀致性是指事务的执⾏不能破坏数据库数据的完整性和⼀致性，⼀个事务在执⾏之前和执⾏之后，数据库都必须处以⼀致性状态。

⽐如：如果从A账户转账到B账户，不可能因为A账户扣了钱，⽽B账户没有加钱。

3.隔离性（Isolation）

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各⾃完整的数据空间。

⼀个事务内部的操作及使⽤的数据对其它并发事务是隔离的，并发执⾏的各个事务是不能互相⼲扰的。隔离性分4个级别，下⾯会介绍。

4. 持久性（Duration）

事务的持久性是指事务⼀旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么⼀定能够将其恢复到事务成功结束后的状态。

3.事务的并发问题

• 脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
• 不可重复读：同⼀条命令返回不同的结果集（更新）.事务 A 多次读取同⼀数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同⼀数据时，结果 不⼀致。
• 幻读：重复查询的过程中，数据就发⽣了量的变化（insert， delete）。

4.事务隔离级别

事务隔离级别	脏 读	不可重复读	幻 读
读未提交（READ_UNCOMMITTED）	允许	允许	允许
读已提交（READ_COMMITTED）	禁⽌	允许	允许
可重复读（REPEATABLE_READ）	禁⽌	禁⽌	可能会
顺序读（SERIALIZABLE）	禁⽌	禁⽌	禁⽌

4种事务隔离级别从上往下，级别越⾼，并发性越差，安全性就越来越⾼。 ⼀般数据默认级别是读以提交或可重复读。

查看当前会话中事务的隔离级别：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

5.不同的隔离级别的锁的情况(了解)

1. 读未提交（RU）: 有⾏级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
2. 读已提交（RC）：有⾏级的锁，没有间隙锁，读不到没有提交的数据。
3. 可重复读（RR）：有⾏级的锁，也有间隙锁，每次读取的数据都是⼀样的，并且没有幻读的情况。
4. 序列化（S）：有⾏级锁，也有间隙锁，读表的时候，就已经上锁了

7.MySQL中的特性

1.MySQL存储过程

⽬前使⽤的⼤多数SQL语句都是针对⼀个或多个表的单条语句。并⾮所有操作都这么简单，经常会有⼀个完整的操作需要多条语句才能完成。例如：

• 为了处理订单，需要核对以保证库存中有相应的物品。
• 如果库存有物品，需要预定以便不将它们再卖给别的⼈， 并减少可⽤的物品数量以反映正确的库存量。
• 库存中没有的物品需要订购，这需要与供应商进⾏某种交互。

执⾏这个处理需要针对许多表的多条MySQL语句。可能需要执⾏的具体语句及其次序也不是固定的。

所以，可以创建存储过程。

• 存储过程简单来说，就是为以后的使⽤⽽保存 的⼀条或多条MySQL语句的集合。
• 储存过程是⼀组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调⽤。
• 存储过程就像脚本语⾔中函数定义⼀样。

为什么要使⽤存储过程?

优点:

• 可以把⼀些复杂的sql进⾏封装,简化复杂操作
• 保证了数据的完整性,防⽌错误
• 简单的变动只需要更改存储过程的代码即可
• 提⾼性能。因为使⽤存储过程⽐使⽤单独的SQL语句要快。(预先编译)

缺点:

• 存储过程的编写⽐SQL语句复杂
• ⼀般可能还没有创建存储过程的权限,只能调⽤

个⼈观点:

• 业务逻辑不要封装在数据库⾥⾯,应该由应⽤程序(JAVA、Python、PHP)处理。
• 让数据库只做它擅⻓和必须做的，减少数据库资源和性能的消耗。
• 维护困难，⼤量业务逻辑封装在存储过程中，造成业务逻辑很难剥离出来。动A影响B。
• ⼈员也难招聘，因为既懂存储过程，⼜懂业务的⼈少，使⽤困难。

在电信、银⾏业、⾦融⽅⾯以及国企都普遍使⽤存储过程来熟悉业务逻辑，但在互联⽹中相对较少。

创建存储过程

\d // 修改MySQL默认的语句结尾符 ; ，改为 // 。create procedure 创建语句；BEGIN和END语句⽤来限定存储过程体。

-- 定义存储过程
\d //
create procedure p1()
begin
set @i=10;
while @i<90 do
insert into users values(null,concat('user:',@i),@i,0);
set @i=@i+1;
end while;
end;
//

执⾏储存：call p1()

查看存储过程：show create procedure p1\G

删除存储过程：drop procedure p1

2.MySQL的触发器

触发器的定义：

• 触发器是MySQL响应写操作(增、删、改)⽽⾃动执⾏的⼀条或⼀组定义在BEGIN和END之间的MySQL语句
• 或可理解为：提前定义好⼀个或⼀组操作,在指定的SQL操作前或后来触发指定的SQL⾃动执⾏触发器就像是JavaScript中的事件⼀样

举例: 定义⼀个update语句,在向某个表中执⾏insert添加语句时来触发执⾏,就可以使⽤触发器。

触发器语法：

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tbl_name FOR EACH ROW trigger_stmt
说明：
\# trigger_name：触发器名称
\# trigger_time:触发时间，可取值：BEFORE或AFTER
\# trigger_event：触发事件，可取值：INSERT、UPDATE或DELETE。
\# tb1_name：指定在哪个表上
\# trigger_stmt：触发处理SQL语句。
-- 查看所有的 触发器
show triggers\G;
-- 删除触发器
drop trigger trigger_name;

触发器Demo

注意:如果触发器中SQL有语法错误,那么整个操作都会报错

-- 创建⼀个删除的触发器,在users表中删除数据之前,往del_users表中添加⼀个数据
-- 1,复制当前的⼀个表结构
create table del_users like users;
-- 2,创建 删除触发器 注意在创建删除触发器时,只能在删除之前才能获取到old(之前的)数据
\d //
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.name,old.age,old.account);
end;
//
\d ;
-- 3 删除users表中的数据去实验

tips：

• 在INSERT触发器代码内，可引⽤⼀个名为NEW的虚拟表，访问被 插⼊的⾏;

• 在DELETE触发器代码内，可以引⽤⼀个名为OLD的虚拟表，访问被删除的⾏;

  OLD中的值全都是只读的，不能更新。

  在AFTER DELETE的触发器中⽆法获取OLD虚拟表

• 在UPDATE触发器代码中

  可以引⽤⼀个名为OLD的虚拟表访问更新以前的值

  可以引⽤⼀个名为NEW的虚拟表访问新 更新的值;

3.MySQL中的视图

视图的含义：

视图是虚拟的表。与包含数据的表不⼀样，视图只包含使⽤时动态检索数据的查询。视图仅仅是⽤来查看存储在别处的数据的⼀种设施或⽅法。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。在添加或更改这些表中的数据时，视图将返回改变过的数据。因为视图不包含数据，所以每次使⽤视图时，都必须处理查询执⾏时所需的任⼀个检索。如果你⽤多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图的作⽤：

1. 重⽤SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以⽅便地重⽤它⽽不必知道它的基本查询细节。
3. 使⽤表的组成部分⽽不是整个表。
4. 保护数据。可以给⽤户授予表的特定部分的访问权限⽽不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
6. 注意:视图不能索引，也不能有关联的触发器或默认值。

视图的基础语法：

创建视图:
create view v_users as select id,name,age from users where age >= 25 and age<= 35;
-- Query OK, 0 rows affected (0.00 sec)
view视图的帮助信息:
mysql> ? view
ALTER VIEW
CREATE VIEW
DROP VIEW
查看当前库中所有的视图
show tables; --可以查看到所有的表和视图
show table status where comment='view'; --只查看当前库中的所有视图
删除视图v_t1:
mysql> drop view v_t1;