SQL语言

目录

• 操作数据库的神器，SQL语言
  • 1、SQL简介
    • 1、SQL是什么
      • 1）数据定义语言（Data Definition Language，DDL）
      • 2）数据操作语言（Data Manipulation Language，DML）
      • 3）数据查询语言（Data Query Language，DQL）
      • 4）数据控制语言（Data Control Language，DCL）
    • 2、SQL的基本规则
      • 1）SQL 语句要以分号;结尾或\G结尾
      • 2）SQL 语句不区分大小写
  • 2、MySQL数据库
    • 1、查看数据库
    • 2、创建数据库
    • 3、修改数据库
    • 4、指定数据库
    • 5、删除数据库
    • 6、数据库注释
  • 3、Mysql数据中的数据类型
    • 1、MySQL整数类型
    • 2、MySQL小数类型
    • 3、MySQL字符串类型
    • 4、MySQL日期和时间类型
    • 5、MySQL二进制类型
    • 6、MySQL系统变量
      • 1、查看系统变量
  • 4、MySQL数据表
    • 1、创建数据表
      • 1、基本语法
      • 2、创建数据表
    • 2、修改数据表
      • 1、修改表名
      • 2、修改表字符集
      • 3、修改表字段
      • 4、修改字段数据类型
    • 3、删除数据表
      • 1、基础语法
      • 2、删除数据表
      • 3、删除字段
    • 4、表数据的增删改查
      • 1、增加表数据
      • 2、查询数据
        • 1）条件语句（where）
          • 判断条件
        • 2）排序（order by）
        • 3）去重（DISTINCT）
        • 4）别名
        • 5）常用的函数
          • 函数总结
        • 6）having语句
        • 7）分组(group by)
      • 3、修改表数据
      • 4、删除表数据
    • 5、数据表约束（索引约束和数据类型约束）
      • 1、主键索引约束
      • 2、唯一索引约束
      • 3、检查索引
      • 4、外键索引（不推荐使用）
    • 6、增加字段
      • 1、在开头位置添加字段
      • 2、在中间位置添加字段
      • 3、在末尾位置添加字段
      • 4、字段的琐碎内容
    • 7、连表查询
      • 1、内连接（INNER JOIN ）
      • 2、左连接（LEFT JOIN）
      • 3、右链接（Right JOIN）
      • 4、合并数据
    • 8、子查询

操作数据库的神器，SQL语言

1、SQL简介

对数据库进行查询和修改操作的语言叫做 SQL（Structured Query Language，结构化查询语言）。SQL 语言是目前广泛使用的关系数据库标准语言，是各种数据库交互方式的基础。著名的大型商用数据库 Oracle、DB2、Sybase、SQL Server，开源的数据库 PostgreSQL、MySQL，甚至一些小型的数据库 Access 等都支持 SQL。近些年蓬勃发展的 NoSQL 系统最初是宣称不再需要 SQL 的，后来也不得不修正为 Not Only SQL，来拥抱 SQL。

1、SQL是什么

SQL 是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。与其他程序设计语言（如 C语言、Java 等）不同的是，SQL 由很少的关键字组成，每个 SQL 语句通过一个或多个关键字构成。

• SQL 具有如下优点：

1. 1. 一体化：SQL 集数据定义、数据操作和数据控制于一体，可以完成数据库中的全部工作。
   2. 使用方式灵活：SQL 具有两种使用方式，可以直接以命令方式交互使用；也可以嵌入使用，嵌入C、C++、Fortran、COBOL、Java 等语言中使用。
2. 1. 非过程化：只提操作要求，不必描述操作步骤，也不需要导航。使用时只需要告诉计算机“做什么”，而不需要告诉它“怎么做”，存储路径的选择和操作的执行由数据库管理系统自动完成。
   2. 语言简洁、语法简单：该语言的语句都是由描述性很强的英语单词组成，而且这些单词的数目不多。

1）数据定义语言（Data Definition Language，DDL）

用来创建或删除数据库以及表等对象，主要包含以下几种命令：

• DROP：删除数据库和表等对象

• CREATE：创建数据库和表等对象

• ALTER：修改数据库和表等对象的结构

2）数据操作语言（Data Manipulation Language，DML）

用来变更表中的记录，主要包含以下几种命令：

• SELECT：查询表中的数据

• INSERT：向表中插入新数据

• UPDATE：更新表中的数据

• DELETE：删除表中的数据

3）数据查询语言（Data Query Language，DQL）

用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

4）数据控制语言（Data Control Language，DCL）

用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

• GRANT：赋予用户操作权限

• REVOKE：取消用户的操作权限

• COMMIT：确认对数据库中的数据进行的变更

• ROLLBACK：取消对数据库中的数据进行的变更

标准 SQL 是指符合国际标准的 SQL，而非某个数据库厂商的 SQL 语法（如：Microsoft SQL Server 的 T-SQL，Oracle 的 PL/SQL，MySQL）。
标准 SQL 可以在任何数据库中使用，而数据库厂商的 SQL 只适合它们对应的数据库，如 T-SQL 只适合 Microsoft SQL Server。

2、SQL的基本规则

对于 SQL 初学者，在写 SQL 语句时，只要遵守下面几个书写规则，就可以避免很多错误。这些规则都非常简单，下面我们来逐一介绍。

1）SQL 语句要以分号;结尾或\G结尾

在 RDBMS （关系型数据库）当中，SQL 语句是逐条执行的，一条 SQL 语句代表着数据库的一个操作。我们通常在句子的句尾加注标点表示这句话结束，中文句子以句号。结尾，英文以点号.结尾，而 SQL 语句则使用英文分号;结尾。

2）SQL 语句不区分大小写

SQL 不区分关键字的大小写。例如，不管写成 SELECT 还是 select，解释都是一样的。表名和列名也是如此。

提示：关键字是数据库事先定义的，有特别意义的单词。

虽然可以根据个人喜好选择大写还是小写（或大小写混杂），但为了理解起来更加容易。

• 关键字大写
• 数据库名、表名和列名等小写

需要注意的是，插入到表中的数据是区分大小写的。例如，向数据库中插入单词 Computer、COMPUTER 或 computer，这三个是不一样的数据。

2、MySQL数据库

在SQL语言中，数据库相当于文件夹。

1、查看数据库

• 查看所有数据库

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

mysql> 

• 查看数据库定义

mysql> show create database test01;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

• 查看正在使用的数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

2、创建数据库

• 释义

• • <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
    • IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
• • [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
    • [DEFAULT] COLLATE：指定字符集的默认校对规则。

• 格式

CREATE DATABASE [IF NOT EXISTS] <数据库名>
	[[DEFAULT] CHARACTER SET <字符集名>] 
	[[DEFAULT] COLLATE <校对规则名>];

• 案例

mysql> CREATE DATABASE IF NOT EXISTS test01 CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test01             |
+--------------------+
7 rows in set (0.00 sec)

3、修改数据库

在 MySQL 数据库中只能对数据库使用的字符集和校对规则进行修改，数据库的这些特性都储存在 db.opt 文件中。下面我们来介绍一下修改数据库的基本操作。

在 MySQL 中，可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为：

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

• 语法说明如下：

• • ALTER DATABASE 用于更改数据库的全局特性。
• • 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
• • 数据库名称可以忽略，此时语句对应于默认数据库。
• • CHARACTER SET 子句用于更改默认的数据库字符集。

• 案例

mysql> show create database test01;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER DATABASE test01 
    -> DEFAULT CHARACTER SET utf8 
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> show create database test01;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test01   | CREATE DATABASE `test01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

4、指定数据库

在 MySQL 中就有很多系统自带的数据库，那么在操作数据库之前就必须要确定是哪一个数据库。在 MySQL 中，USE 语句用来完成一个数据库到另一个数据库的跳转。

当用 CREATE DATABASE 语句创建数据库之后，该数据库不会自动成为当前数据库，需要用 USE 来指定当前数据库。其语法格式为：

USE [数据库名称];

注：该语句可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库。该数据库保持为默认数据库，直到语段的结尾，或者直到遇见一个不同的 USE 语句。 只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作。

• 案例

mysql> use test01;
Database changed
mysql> 

5、删除数据库

当数据库不再使用时应该将其删除，以确保数据库存储空间中存放的是有效数据。删除数据库是将已经存在的数据库从磁盘空间上清除，清除之后，数据库中的所有数据也将一同被删除。

在 MySQL 中，当需要删除已创建的数据库时，可以使用 DROP DATABASE 语句。其语法格式为：

DROP DATABASE [ IF EXISTS ] <数据库名>

• 语法说明如下：

• • <数据库名>：指定要删除的数据库名。
    • IF EXISTS：用于防止当数据库不存在时发生错误。
• • DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

• 案例

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test01             |
+--------------------+
7 rows in set (0.00 sec)

mysql> drop database test01;
Query OK, 0 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

注意：MySQL 安装后，系统会自动创建名为 information_schema 和 mysql 的两个系统数据库，系统数据库存放一些和数据库相关的信息，如果删除了这两个数据库，MySQL 将不能正常工作。

6、数据库注释

1. 单行注释可以使用#注释符，#注释符后直接加注释内容。格式如下：

#注释内容
单行注释使用注释符#的示例如下：
#从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;

2. 单行注释可以使用--注释符，--注释符后需要加一个空格，注释才能生效。格式如下：

-- 注释内容
单行注释使用注释符--的示例如下：
-- 从结果中删除重复行
SELECT DISTINCT product_id, purchase_price FROM Product;

#和--的区别就是：#后面直接加注释内容，而--的第 2 个破折号后需要跟一个空格符在加注释内容。

3. MySQL 多行注释

多行注释使用/* /注释符。/用于注释内容的开头，*/用于注释内容的结尾。多行注释格式如下：

/*
第一行注释内容
第二行注释内容
*/

注释内容写在/和/之间，可以跨多行。

3、Mysql数据中的数据类型

数据类型（data_type）是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

如果使用错误的数据类型可能会严重影响应用程序的功能和性能，所以在设计表时，应该特别重视数据列所用的数据类型。更改包含数据的列不是一件小事，这样做可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型有大概可以分为 5 种，分别是整数类型、浮点数类型和定点数类型、日期和时间类型、字符串类型、二进制类型等。

1、MySQL整数类型

整数类型又称数值型数据，数值型数据类型主要用来存储数字。

MySQL 提供了多种数值型数据类型，不同的数据类型提供不同的取值范围，可以存储的值范围越大，所需的存储空间也会越大。

MySQL 主要提供的整数类型有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，其属性字段可以添加 AUTO_INCREMENT 自增约束条件。下表中列出了 MySQL 中的数值类型。

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个宇节
MEDIUMINT	中等大小的整数	3个字节
INT (INTEGHR)	普通大小的整数	4个字节
BIGINT	大整数	8个字节

从上表中可以看到，不同类型的整数存储所需的字节数不相同，占用字节数最小的是 TINYINT 类型，占用字节最大的是 BIGINT 类型，占用的字节越多的类型所能表示的数值范围越大。

根据占用字节数可以求出每一种数据类型的取值范围。例如，TINYINT 需要 1 个字节（8bit）来存储，那么 TINYINT 无符号数的最大值为 28-1，即 255；TINYINT 有符号数的最大值为 27-1，即 127。其他类型的整数的取值范围计算方法相同，如下表所示。

类型名称	说明	存储需求
TINYINT	-128〜127	0 〜255
SMALLINT	-32768〜32767	0〜65535
MEDIUMINT	-8388608〜8388607	0〜16777215
INT (INTEGER)	-2147483648〜2147483647	0〜4294967295
BIGINT	-9223372036854775808〜9223372036854775807	0〜18446744073709551615

• 案例

用utf8mb4创建xiaowu库
mysql> create database xiaowu charset utf8mb4;
使用xiaowu库；
mysql> use xiaowu;
在xiaowu库下创建t1表，id列用int型，name列用varchar型，age用tinyint型
mysql> create table t1(id int ,name varchar(64) ,age tinyint);
 
说明：手机号是无法存储到int的。一般是使用char类型来存储手机

2、MySQL小数类型

MySQL 中使用浮点数和定点数来表示小数。

浮点类型有两种，分别是单精度浮点数（FLOAT）和双精度浮点数（DOUBLE）；定点类型只有一种，就是 DECIMAL。

浮点类型和定点类型都可以用(M, D)来表示，其中M称为精度，表示总共的位数；D称为标度，表示小数的位数。

浮点数类型的取值范围为 M（1～255）和 D（1～30，且不能大于 M-2），分别表示显示宽度和小数位数。M 和 D 在 FLOAT 和DOUBLE 中是可选的，FLOAT 和 DOUBLE 类型将被保存为硬件所支持的最大精度。DECIMAL 的默认 D 值为 0、M 值为 10。

下表中列出了 MySQL 中的小数类型和存储需求。

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)	压缩的“严格”定点数	M+2 个字节

DECIMAL 类型不同于 FLOAT 和 DOUBLE。DOUBLE 实际上是以字符串的形式存放的，DECIMAL 可能的最大取值范围与 DOUBLE 相同，但是有效的取值范围由 M 和 D 决定。如果改变 M 而固定 D，则取值范围将随 M 的变大而变大。

从上表中可以看到，DECIMAL 的存储空间并不是固定的，而由精度值 M 决定，占用 M+2 个字节。

FLOAT 类型的取值范围如下：

• 有符号的取值范围：-3.402823466E+38～-1.175494351E-38。
• 无符号的取值范围：0 和 -1.175494351E-38～-3.402823466E+38。

DOUBLE 类型的取值范围如下：

• 有符号的取值范围：-1.7976931348623157E+308～-2.2250738585072014E-308。
• 无符号的取值范围：0 和 -2.2250738585072014E-308～-1.7976931348623157E+308。

3、MySQL字符串类型

字符串类型用来存储字符串数据，还可以存储图片和声音的二进制数据。字符串可以区分或者不区分大小写的串比较，还可以进行正则表达式的匹配查找。

MySQL 中的字符串类型有 CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。

下表中列出了 MySQL 中的字符串数据类型，括号中的M表示可以为其指定长度。

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)
SET	一个设置，字符串对象可以有零个或 多个SET成员	1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员）

VARCHAR 和 TEXT 类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用 L 表示），而不是取决于类型的最大可能尺寸。

例如，一个 VARCHAR(10) 列能保存一个最大长度为 10 个字符的字符串，实际的存储需要字符串的长度 L 加上一个字节以记录字符串的长度。对于字符 “abcd”，L 是 4，而存储要求 5 个字节。

mysql> CREATE TABLE test05 (
    -> name VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test06( name ENUM("1","2","3") );
Query OK, 0 rows affected (0.01 sec)

4、MySQL日期和时间类型

MySQL 中有多处表示日期的数据类型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。当只记录年信息的时候，可以只使用 YEAR 类型。

每一个类型都有合法的取值范围，当指定确定不合法的值时，系统将“零”值插入数据库中。

下表中列出了 MySQL 中的日期与时间类型。

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

• 案例

mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.03 sec)

mysql> use db1;
Database changed

mysql> create table t1(id int,name char, date1 date, date2 time, date3 datetime, date4 timestamp, date5 year);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (1, '1', '2021-09-09','12:12:12','2021-09-09','2021-09-09','2021');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t1;
+----+------+------------+----------+---------------------+---------------------+-------+
| id | name | date1      | date2    | date3               | date4               | date5 |
+----+------+------------+----------+---------------------+---------------------+-------+
|  1 | 1    | 2021-09-09 | 12:12:12 | 2021-09-09 00:00:00 | 2021-09-09 00:00:00 |  2021 |
+----+------+------------+----------+---------------------+---------------------+-------+
1 row in set (0.07 sec)


datetime 和 timestamp 之间的区别？
	
  1、两者的存储方式不一样
    对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。
    对于DATETIME，不做任何改变，基本上是原样输入和输出。
    
  2、两者所能存储的时间范围不一样
    timestamp所能存储的时间范围为：‘1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’。
    datetime所能存储的时间范围为：‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
    
    mysql> insert into t1 values (1, '1', '2021-09-09','12:12:12','2221-09-09 12','2221-09-09','2021');
    1292 - Incorrect datetime value: '2221-09-09' for column 'date4' at row 1

5、MySQL二进制类型

MySQL 中的二进制字符串有 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

下表中列出了 MySQL 中的二进制数据类型，括号中的M表示可以为其指定长度。

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

• 案例

import pymysql

class BlobDataTestor:
    def __init__(self):
        self.conn = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='db1', port=3306)

    def __del__(self):
        try:
            self.conn.close()
        except:
            pass

    def closedb(self):
        self.conn.close()

    def setup(self):
        cursor = self.conn.cursor()
        cursor.execute("""  
             CREATE TABLE IF NOT EXISTS `Dem_Picture` (  
             `ID` int(11) NOT NULL auto_increment,  
             `PicData` mediumblob,  
             PRIMARY KEY (`ID`)  
             ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;  
             """)


    def testRWBlobData(self):
        # 读取源图片数据
        f = open("D:\\1.jpg", "rb")
        b = f.read()
        f.close()

        # 将图片数据写入表
        cursor = self.conn.cursor()
        cursor.execute("INSERT INTO Dem_Picture (PicData) VALUES (%s)", (pymysql.Binary(b)))
        # self.conn.commit()

        # 读取表内图片数据，并写入硬盘文件
        cursor.execute("SELECT PicData FROM Dem_Picture ORDER BY ID DESC limit 1")
        d = cursor.fetchone()[0]
        cursor.close()

        f = open("D:\\1.jpg", "wb")
        f.write(d)
        f.close()


# 下面一句的作用是：运行本程序文件时执行什么操作  
if __name__ == "__main__":

    test = BlobDataTestor()

    try:
        test.setup()
        test.testRWBlobData()
        # test.teardown()
    finally:
        test.closedb()

6、MySQL系统变量

在 MySQL 数据库，变量分为系统变量和用户自定义变量。系统变量以 @@ 开头，用户自定义变量以 @ 开头。

服务器维护着两种系统变量，即全局变量（GLOBAL VARIABLES）和会话变量（SESSION VARIABLES）。全局变量影响 MySQL 服务的整体运行方式，会话变量影响具体客户端连接的操作。

每一个客户端成功连接服务器后，都会产生与之对应的会话。会话期间，MySQL 服务实例会在服务器内存中生成与该会话对应的会话变量，这些会话变量的初始值是全局变量值的拷贝。

1、查看系统变量

可以使用以下命令查看 MySQL 中所有的全局变量信息。

#查看全局变量信息
SHOW GLOBAL VARIABLES; 

#过滤查找变量信息 (like)
SHOW GLOBAL VARIABLES like "%ssl%" \G

可以使用以下命令查看与当前会话相关的所有会话变量以及全局变量。

SHOW SESSION VARIABLES;

其中，SESSION 关键字可以省略。

4、MySQL数据表

数据表是数据库的重要组成部分，每一个数据库都是由若干个数据表组成的。换句话说，没有数据表就无法在数据库中存放数据。

1、创建数据表

在创建数据库之后，接下来就要在数据库中创建数据表。所谓创建数据表，指的是在已经创建的数据库中建立新表。

创建数据表的过程是规定数据列的属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）约束的过程。接下来我们介绍一下创建数据表的语法形式。

1、基本语法

在 MySQL 中，可以使用 CREATE TABLE 语句创建表。其语法格式为：

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];

-- 增加 IF NOT EXISTS 判断表存不存在。（表存在不会报错）

其中，[表定义选项]的格式为：

<列名1> <类型1> [,…] <列名n> <类型n>

CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的。

这里首先描述一个简单的新建表的例子，然后重点介绍 CREATE TABLE 命令中的一些主要的语法知识点。

CREATE TABLE 语句的主要语法及使用说明如下：

• CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。

• <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，'mydb'.'mytbl' 是合法的，但 'mydb.mytbl' 不合法。

• <表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

• 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

2、创建数据表

数据表属于数据库，在创建数据表之前，应使用语句“USE<数据库>”指定操作在哪个数据库中进行，如果没有选择数据库，就会抛出 No database selected 的错误。

#进入test01库
mysql> use test01
Database changed

#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| test01     |
+------------+
1 row in set (0.00 sec)

#建表
-- 增加 IF NOT EXISTS 判断表存不存在。（表存在不会报错）

mysql> CREATE TABLE test01(
    -> id int(11),
    -> name VARCHAR(25)
    -> );
Query OK, 0 rows affected (0.02 sec)

语句执行后，便创建了一个名称为 test01 的数据表，使用 SHOW TABLES；语句查看数据表是否创建成功，如下所示。

mysql> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| test01        |
+---------------+
2 rows in set (0.00 sec)

2、修改数据表

修改数据表的前提是数据库中已经存在该表。修改表指的是修改数据库中已经存在的数据表的结构。修改数据表的操作也是数据库管理中必不可少的，就像画素描一样，画多了可以用橡皮擦掉，画少了可以用笔加上。

不了解如何修改数据表，就相当于是我们只要画错了就要扔掉重画，这样就增加了不必要的成本。

1、修改表名

MySQL 通过 ALTER TABLE 语句来实现表名的修改。

ALTER TABLE <旧表名> RENAME [TO] <新表名>;

• 案例

mysql> ALTER TABLE test01 RENAME TO test_01;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------+
| Tables_in_db1 |
+---------------+
| Dem_Picture   |
| test_01       |
+---------------+
2 rows in set (0.00 sec)

2、修改表字符集

MySQL 通过 ALTER TABLE 语句来实现表字符集的修改。

ALTER TABLE 表名 [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则名>;

其中，DEFAULT 为可选参数，使用与否均不影响结果。

mysql> SHOW CREATE TABLE test_01\G
*************************** 1. row ***************************
       Table: test_01
Create Table: CREATE TABLE `test_01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

3、修改表字段

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

其语法格式如下：

ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；

• 案例

mysql> ALTER TABLE test_01 CHANGE name  name CHAR(11);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test_01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(11) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4、修改字段数据类型

修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。

ALTER TABLE <表名> MODIFY <字段名> <数据类型>

其中：

• 表名：指要修改数据类型的字段所在表的名称；

• 字段名：指需要修改的字段；

• 数据类型：指修改后字段的新数据类型。

案例

mysql> DESC test_01;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(11) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE test_01 MODIFY name VARCHAR(15);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC test_01;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

3、删除数据表

在 MySQL 数据库中，对于不再需要的数据表，我们可以将其从数据库中删除。

在删除表的同时，表的结构和表中所有的数据都会被删除，因此在删除数据表之前最好先备份，以免造成无法挽回的损失。

下面我们来了解一下 MySQL 数据库中数据表的删除方法。

1、基础语法

使用 DROP TABLE 语句可以删除一个或多个数据表，语法格式如下：

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]

对语法格式的说明如下：

• 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
• IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：

• 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
• 表被删除时，用户在该表上的权限不会自动删除。

2、删除数据表

删除数据表test_01。

mysql> DROP TABLE test_01;
Query OK, 0 rows affected (0.00 sec)

3、删除字段

删除字段是将数据表中的某个字段从表中移除。

ALTER TABLE <表名> DROP <字段名>;

• 案例

mysql> DESC db01;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE db01 DROP name;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC db01;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

4、表数据的增删改查

表相当于文件，表中保存的其实是真正的数据。如下我们介绍一下表数据的各种常见操作。

1、增加表数据

增加数据其实就是向表中插入数据，或者是向表中添加数据。

格式：

INSERT INTO <表名> (字段1,字段2,...字段n) VALUES (数据1,数据2...数据n);

案例：

#进入test01库
mysql> use test01
Database changed

#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| test01     |
+------------+
1 row in set (0.00 sec)

-- 创建一个班级表
-- 增加 IF NOT EXISTS 判断表存不存在。（表存在不会报错）

CREATE TABLE class(
  id int,
  name varchar(50),
  age tinyint(2),
  sex enum('1','2')
);

#查看数据表结构
mysql> desc class;
+-------+---------------+------+-----+---------+-------+
| Field | Type          | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id    | int(11)       | YES  |     | NULL    |       |
| name  | varchar(50)   | YES  |     | NULL    |       |
| age   | tinyint(2)    | YES  |     | NULL    |       |
| sex   | enum('1','2') | YES  |     | NULL    |       |
+-------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

-- 向班级中插入数据
INSERT INTO class (id,name,age,sex) VALUES (1,'班长',18,1);
INSERT INTO class (id,name,age,sex) VALUES (2,'小仙女',16,2);

测试结果：

mysql> -- 创建一个班级表
mysql> CREATE TABLE class(
    -> id int,
    ->   name varchar(50),
    ->   age tinyint(2),
    ->   sex enum('1','2')
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> -- 向班级中插入数据
mysql> INSERT INTO class (id,name,age,sex) VALUES (1,'班长',18,1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO class (id,name,age,sex) VALUES (2,'小仙女',16,2);
Query OK, 1 row affected (0.00 sec)

mysql> -- 当所有的字段都涉及到了，那么括号中的字段可以省略；当插入的字段没有全部涉及到，那么则必须指定字段。
mysql> INSERT INTO class VALUES (3, '小帅哥', 17, 1);
Query OK, 1 row affected (0.00 sec)

2、查询数据

常见符号

符号	说明
%	匹配0个或多个任意字符
_ （下划线）	匹配单个字符
like	模糊匹配
=	等于，精确匹配
>	大于
<	小于
>=	大于等于
<=	小于等于
!=和<>	不等于
!和not	逻辑非
||和or	逻辑或
&&和and	逻辑与
between...and...	两者之间
in（....）	在
not in (....)	不在

表中保存了很多数据，其目的就是为了使用的时候可以立即查询出来，所以数据库的查询语句的使用率是其他语句的数倍。下面我们介绍查询语法：

格式：

SELECT [查询字段] FROM [表名] [条件语句] [显示规则] [规则条件]

案例：

-- * 默认代表所有的字段
mysql> SELECT * FROM class;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | 班长      |   18 | 1    |
|    2 | 小仙女    |   16 | 2    |
|    3 | 小帅哥    |   17 | 1    |
+------+-----------+------+------+
3 rows in set (0.00 sec)

-- 查询指定字段
mysql> SELECT name FROM class;
+-----------+
| name      |
+-----------+
| 班长      |
| 小仙女    |
| 小帅哥    |
+-----------+
3 rows in set (0.00 sec)

1）条件语句（where）

条件语句是用来筛选数据的，主要用于查询某些数据。下面我们介绍条件语句

判断条件

#1.条件查询where可以接的符号
where接条件: > < = >= <= (!= 和 <> ：不等于) (like:模糊查询) (and:并且） （or：或者）
=:精确查询
> < = >= <= != :范围查询
like:模糊查询
in  : 硬查询

#2.查询中国城市的人口
mysql> select name,population from city where countrycode='CHN';

#3.查询中国黑龙江省的人口
mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';

#4.查询人口数量大于多少的城市
mysql> select * from city where population>=1410000;

#5.模糊查询，跟正则不同，查询带H的使用 %H%，查询H开头的使用 H%，查询H结尾的，使用 %H
mysql> select * from world.city where countrycode like '%H%';

#6.查询中国和美国的城市 or 和 in
mysql> select * from world.city where countrycode='CHN' or countrycode='USA';
mysql> select * from world.city where countrycode in ('CHN','USA');
#排除
mysql> select * from world.city where countrycode not in ('CHN','USA');

#union all (联合查询) 讲索引的时候再说
mysql> select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

案例1：要求查询出小仙女和小帅哥的信息。

mysql> SELECT * FROM class where id > 1;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    2 | 小仙女    |   16 | 2    |
|    3 | 小帅哥    |   17 | 1    |
+------+-----------+------+------+
2 rows in set (0.01 sec)

案例2：要求查询出班长的信息

mysql> SELECT * FROM class where id = 1;
+------+--------+------+------+
| id   | name   | age  | sex  |
+------+--------+------+------+
|    1 | 班长   |   18 | 1    |
+------+--------+------+------+
1 row in set (0.00 sec)

案例3：要求查询出女生有哪些

mysql> SELECT * FROM class WHERE sex = 2;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    2 | 小仙女    |   16 | 2    |
+------+-----------+------+------+
1 row in set (0.00 sec)

案例4：要求查询出名字中包含小的数据

-- 模糊查询中的%类似于正则表达式中的*，代表匹配所有的内容。
-- 前置% 代表的是以什么结尾 和 后置% 代表以什么开头，如果两者皆有，则表示包含。 
mysql> SELECT * FROM class WHERE name like '%小%';
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    2 | 小仙女    |   16 | 2    |
|    3 | 小帅哥    |   17 | 1    |
+------+-----------+------+------+
2 rows in set (0.00 sec)

案例5：要求查询出id既大于1，又小于3

mysql> SELECT * FROM class WHERE id > 1 AND id < 3;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    2 | 小仙女    |   16 | 2    |
+------+-----------+------+------+
1 row in set (0.00 sec)

案例6：要求查询出下于等于1或者大于等于3

mysql> SELECT * FROM class WHERE id <= 1 OR id >= 3;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | 班长      |   18 | 1    |
|    3 | 小帅哥    |   17 | 1    |
+------+-----------+------+------+
2 rows in set (0.00 sec)

2）排序（order by）

排序，顾名思义就是按照某种规则查询出数据，默认情况下是按照从前到后查询数据，但是也可以通过排序语法查询出相关的数据。

格式：

SELECT [查询字段] FROM [表名] [显示规则]

-- 排序的规则

ASC   ：默认，正向排序
DESC  ：反向排序

案例1：按照年龄的从大到小的顺序查询所有的数据

mysql> SELECT * FROM class ORDER BY age DESC;
+------+-----------+------+------+
| id   | name      | age  | sex  |
+------+-----------+------+------+
|    1 | 班长      |   18 | 1    |
|    3 | 小帅哥    |   17 | 1    |
|    2 | 小仙女    |   16 | 2    |
+------+-----------+------+------+
3 rows in set (0.00 sec)

3）去重（DISTINCT）

去重，顾名思义就是在查询的数据中过滤掉重复数据，默认会显示所有的数据，可以使用出重语法实现去掉重复数据。

格式：

# 去重是在指定需要被出重的语句之前添加去重关键字

案例：要求按照性别去出重

mysql> SELECT DISTINCT sex FROM class;
+------+
| sex  |
+------+
| 1    |
| 2    |
+------+
2 rows in set (0.00 sec)

4）别名

别名，顾名思义就是将字段设置一个新的名字。

案例：要求计算出当前表中所有的行数

-- 计算行数的函数是count()
mysql> SELECT count(id) FROM class;
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

-- 设置别名
mysql> SELECT count(id) '行数' FROM class;
+--------+
| 行数   |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

5）常用的函数

函数，就是具备某种功能的工具。那么在数据库中使用函数就是通过函数实现某种具体的功能。

函数总结

count()			#统计
password()		#加密
distinct()		#去重
now()			#当前时间
database()		#当前数据库

max()			#最大值
mysql> select max(population) from country;

min()			#最小值
mysql> select min(population) from country;

sum()			#求和
mysql> select sum(population) from country;

avg()			#求平均值
mysql> select avg(population) from country;

●计算行数的函数

案例：计算出当前数据有多少行

-- 计算行数的函数是count()
mysql> SELECT count(id) FROM class;
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

●计算某一个字段的和

案例：计算表中所有的年龄的总和

-- 计算和的函数是sum()
mysql> SELECT SUM(age) FROM class;
+----------+
| SUM(age) |
+----------+
|       51 |
+----------+
1 row in set (0.00 sec)

●计算平均值

案例：计算表中所有的年龄的平均值

-- 计算平均值的函数是avg()
mysql> SELECT AVG(age) FROM class;
+----------+
| AVG(age) |
+----------+
|  17.0000 |
+----------+
1 row in set (0.00 sec)

6）having语句

having也是一个条件判断语句，类似于前面所讲的where语句是用于做条件判断的，但是有所不同的是where的条件作用于查询之前的字段，having是作用于查询之后的语句。

案例1：所有的男生中，大于17岁的有哪些

mysql> SELECT * FROM class WHERE sex = 1 HAVING age > 17;
+------+--------+------+------+
| id   | name   | age  | sex  |
+------+--------+------+------+
|    1 | 班长   |   18 | 1    |
+------+--------+------+------+
1 row in set (0.00 sec)

案例2：查询出一个月消费在2000元以上的同学

mysql> -- 创建一个消费表
mysql> CREATE TABLE consumption(
    -> id int,
    ->   name varchar(20),
    ->   money DECIMAL (8,2)
    -> );
ption VALUES (1,'小仙女',500.01);
INSERT INTO consumption VALUES (1,'小仙女',500.01);

-- 查询出每个人的消费超过2000元的
SELECT SUM(money) moneys FROM consumption HAVING moneys > 2000;Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> -- 插入消费数据
mysql> INSERT INTO consumption VALUES (1,'小仙女',500.01);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO consumption VALUES (1,'小仙女',500.01);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO consumption VALUES (1,'小仙女',500.01);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO consumption VALUES (1,'小仙女',500.01);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> -- 查询出每个人的消费超过2000元的
mysql> SELECT SUM(money) moneys FROM consumption GROUP BY name HAVING moneys > 2000;
+---------+
| moneys  |
+---------+
| 2000.04 |
+---------+
1 row in set (0.00 sec)

7）分组(group by)

分组，顾名思义就是按照某种要求进行分组查询，例如：查询出所有人的消费总额。

案例：要求查询出每一个人消费总额

mysql> SELECT SUM(money) FROM consumption GROUP BY name;
+------------+
| SUM(money) |
+------------+
|    3500.07 |
|    2000.04 |
+------------+
2 rows in set (0.00 sec)

3、修改表数据

在数据表中存储的数据时常都会有所更改，例如：是否单身，是今天是否国庆。所以，怎么会随着一些事务的推移从而需要修改表数据，这个时候我们就需要用到MySQL UPDATE语句。

格式：

UPDATE <表名>  SET [修改的内容] [条件];

案例：修改单条数据（将数据表中的小仙女的年纪改成16岁）

mysql> -- 将数据表中的年纪改成17岁
mysql> UPDATE class SET age = 17;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 5  Changed: 0  Warnings: 0
mysql> SELECT * FROM class;
+------+--------------+------+------+
| id   | name         | age  | sex  |
+------+--------------+------+------+
|    1 | 班长         |   17 | 1    |
|    2 | 小仙女       |   17 | 2    |
|    3 | 小帅哥       |   17 | 1    |
|    1 | 学习委员     |   17 | 1    |
|    2 | 组长         |   17 | 2    |
+------+--------------+------+------+
5 rows in set (0.00 sec)


mysql> -- 将数据表中的小仙女的年纪改成16岁
mysql> UPDATE class SET age = 16 WHERE name = "小仙女";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM class;
+------+--------------+------+------+
| id   | name         | age  | sex  |
+------+--------------+------+------+
|    1 | 班长         |   17 | 1    |
|    2 | 小仙女       |   16 | 2    |
|    3 | 小帅哥       |   17 | 1    |
|    1 | 学习委员     |   17 | 1    |
|    2 | 组长         |   17 | 2    |
+------+--------------+------+------+
5 rows in set (0.01 sec)

案例2：修改多条数据（将学习委员的年纪修改成16，同时将其性别修改成女）

mysql> -- 将学习委员的年纪修改成16，同时将其性别修改成女
mysql> UPDATE class SET age = 16, sex=2 WHERE name = "学习委员";
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM class;
+------+--------------+------+------+
| id   | name         | age  | sex  |
+------+--------------+------+------+
|    1 | 班长         |   17 | 1    |
|    2 | 小仙女       |   16 | 2    |
|    3 | 小帅哥       |   17 | 1    |
|    1 | 学习委员     |   16 | 2    |
|    2 | 组长         |   17 | 2    |
+------+--------------+------+------+
5 rows in set (0.00 sec)

4、删除表数据

删除表数据，就是当数据表中有错误或者没有任何价值的数据时，通过SQL语句去将这部分数据删除。

格式：

DELETE FROM <表名> [条件];

案例：删除所有数据（千万要谨慎使用）

mysql> DELETE FROM t1;
Query OK, 2 rows affected (0.00 sec)

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

案例2：删除某一部分数据（常用）

mysql> DELETE FROM test01 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test01;
+------+------+
| id   | age  |
+------+------+
|    3 |   18 |
+------+------+
1 row in set (0.00 sec)

案例3：清空数据表

mysql> -- 清空test01数据表中的所有的数据
mysql> DELETE FROM test01;
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE TABLE test01;
Query OK, 0 rows affected (0.00 sec)



-- DELETE 和 TRUNCATE 之间的区别？

  DELETE删除的是数据，不删除索引，TRUNCATE不仅删除数据而且删除索引。

5、数据表约束（索引约束和数据类型约束）

数据表的约束，从字段名字上可以知道，它是为了控制数据而生的.。

1、主键索引约束

所谓的主键约束就是在数据表中(一般是id字段)，选择一个字段充当索引角色。强烈建议一个表中至少要有一个主键索引约束。下面我们介绍主键索引：

主键是一个字段的类型，不能够单独的存在。

创建一个具有主键索引的数据表：

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm1(
    ->   id int PRIMARY KEY,
    ->   name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE TABLE pm1\G
*************************** 1. row ***************************
       Table: pm1
Create Table: CREATE TABLE `pm1` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

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

我们尝试插入数据，看看有什么区别？

mysql> -- 插入数据
mysql> INSERT INTO pm1 (id, name) VALUES (1, '小仙女'),(2,"小帅哥");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pm1;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 小仙女    |
|  2 | 小帅哥    |
+----+-----------+
2 rows in set (0.00 sec)

-- 我们可以看到插入数据和查询数据没有什么区别，不急，将上述插入数据的SQL语句重新执行一遍
mysql> INSERT INTO pm1 (id, name) VALUES (1, '小仙女'),(2,"小帅哥");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

一旦字段被标记成立主键，则其值是无法重复的。除此之外，主键算是MySQL中最快的索引之一。具体的底层算法，我们将在后面的存储引擎原理中详细介绍。

1、自增长

在日常使用数据库的时候常常不知道当天数据的主键索引的编号属于哪一个，这个时候我们就很需要一个自动为我们填充主键编号的功能即为：自增长。

案例：自动填充主键

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm2(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE pm2\G
*************************** 1. row ***************************
       Table: pm2
Create Table: CREATE TABLE `pm2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

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

创建完成数据表之后，填充数据时不增加id，看结果。

mysql> INSERT INTO pm2 (name) VALUES ( '小仙女'),("小帅哥");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pm2;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 小仙女    |
|  2 | 小帅哥    |
+----+-----------+
2 rows in set (0.00 sec)

除此之外，我们还可以设置自动增长的起始值。

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm3(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20)
    -> ) ENGINE=INNODB AUTO_INCREMENT=10000;
Query OK, 0 rows affected (0.03 sec)

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

mysql> INSERT INTO pm3 (name) VALUES ( '小仙女'),("小帅哥");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pm3;
+-------+-----------+
| id    | name      |
+-------+-----------+
| 10000 | 小仙女    |
| 10001 | 小帅哥    |
+-------+-----------+
2 rows in set (0.00 sec)

2、添加主键

当数据表已经创建完毕了，我们需要为该表添加主键，如何添加呢？

格式：

ALTER TABLE <数据表> ADD PRIMARY KEY(字段名称);

案例：

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm4(
    ->   id int ,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

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

mysql> ALTER TABLE pm4 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

3、删除主键

当数据表不需要主键时，我们可以尝试将其删除，下面我们来演示：

格式：

ALTER TABLE <数据表名> DROP PRIMARY KEY;

案例：

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm5(
    ->   id int PRIMARY KEY,
    -> name VARCHAR(20)
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

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

mysql> ALTER TABLE pm5 DROP PRIMARY KEY ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

2、唯一索引约束

唯一索引约束跟主键索引类似，也是要求不允许重复，但是主键索引一般作用于id, 唯一索引可以作用于所有的字段。同理唯一索引也是依赖于字段，能够单独存在。

格式：

mysql> -- 创建一个具有主键索引的数据表
mysql> CREATE TABLE IF NOT EXISTS pm6(
    ->   id int PRIMARY KEY,
    -> name VARCHAR(20) UNIQUE KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)

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

创建之后，可以尝试向其中name字段插入相同的数据，看结果：

mysql> CREATE TABLE IF NOT EXISTS pm6(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) UNIQUE KEY
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO pm6 (name) VALUES ("小仙女");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pm6 (name) VALUES ("小仙女");
ERROR 1062 (23000): Duplicate entry '小仙女' for key 'name'

-- 通过上文可以得出，字段一旦加上了唯一索引，则不能够重复。

3、检查索引

检查索引，顾名思义就是通过设置范围，来管控数据。

案例：

mysql> CREATE TABLE IF NOT EXISTS pm7(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) ,
    -> code  tinyint(2) CHECK( code > 100 AND code < 200 )
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE pm7\G
*************************** 1. row ***************************
       Table: pm7
Create Table: CREATE TABLE `pm7` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `code` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> INSERT INTO pm7 (name, code) VALUES ("小仙女", 20);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM pm7;
+----+-----------+------+
| id | name      | code |
+----+-----------+------+
|  1 | 小仙女    |   20 |
+----+-----------+------+
1 row in set (0.00 sec)

4、外键索引（不推荐使用）

注： 只有InnoDB存储引擎支持外键
作用：
控制数据必须在关联外键的表中存在。

外键索引顾名思义就是依赖别的表的数据的一种索引，例如：我们寄快递的时候，需要选择地址：上海市青浦区华徐公路999号。

案例：

mysql> CREATE TABLE city(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE city2(
    -> id int PRIMARY KEY AUTO_INCREMENT,
    ->   name VARCHAR(20),
    -> 	 fid int ,
    ->   FOREIGN KEY(fid) REFERENCES city(id)
    -> );
Query OK, 0 rows affected (0.01 sec)

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

mysql> DESC city2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
| fid   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO city2 (name,fid) VALUES ("青浦区", 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`linux13`.`city2`, CONSTRAINT `city2_ibfk_1` FOREIGN KEY (`fid`) REFERENCES `city` (`id`))

mysql> INSERT INTO city (name) VALUES ("上海市");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO city2 (name,fid) VALUES ("青浦区", 1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM city;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 上海市    |
+----+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM city2;
+----+-----------+------+
| id | name      | fid  |
+----+-----------+------+
|  2 | 青浦区    |    1 |
+----+-----------+------+
1 row in set (0.00 sec)

-- city2变依赖于city变数据，当city表中没有相关数据时，则不能够添加数据到city2。

6、增加字段

MySQL 数据表是由行和列构成的，通常把表的“列”称为字段（Field），把表的“行”称为记录（Record）。随着业务的变化，可能需要在已有的表中添加新的字段。

1、在开头位置添加字段

MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字。

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

• 案例

mysql> DESC db01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE db01 ADD name VARCHAR(20) FIRST;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC db01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(20) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2、在中间位置添加字段

MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字。

ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;

AFTER 的作用是将新字段添加到某个已有字段后面。

mysql> DESC db01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(20) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> ALTER TABLE db01 ADD sex VARCHAR(20) AFTER id;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC db01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| name  | varchar(20) | YES  |     | NULL    |                |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| sex   | varchar(20) | YES  |     | NULL    |                |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3、在末尾位置添加字段

一个完整的字段包括字段名、数据类型和约束条件。

ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];

对语法格式的说明如下：

• <表名> 为数据表的名字
• <新字段名> 为所要添加的字段的名字
• <数据类型> 为所要添加的字段能存储数据的数据类型
• [约束条件] 是可选的，用来对添加的字段进行约束

使用 ALTER TABLE 语句添加一个 INT 类型的字段 age，SQL 语句和运行结果如下：

mysql> DESC db01;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE db01 ADD addr VARCHAR(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC db01;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| addr  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

4、字段的琐碎内容

将字段中常用的约束一一列举出来。

1、是否允许为空

是否为空顾名思义就是设置是否允许字段为空。其格式是：NOT NULL

案例

mysql> CREATE TABLE IF NOT EXISTS pm8(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS pm9(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) 
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO pm9 (id) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO pm8 (id) VALUES (10);
ERROR 1364 (HY000): Field 'name' doesn't have a default value    '

mysql> INSERT INTO pm8 (name) VALUES ( '小仙女');
Query OK, 1 row affected (0.01 sec)

2、默认值

默认值，顾名思义就是给字段设置一个默认值，当字段没有添加任何值的时候，使用默认值进行填充。

案例：

mysql> CREATE TABLE IF NOT EXISTS pm10(
    ->   id int PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) DEFAULT "小仙女"
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> DESC pm10;
+-------+-------------+------+-----+-----------+----------------+
| Field | Type        | Null | Key | Default   | Extra          |
+-------+-------------+------+-----+-----------+----------------+
| id    | int(11)     | NO   | PRI | NULL      | auto_increment |
| name  | varchar(20) | YES  |     | 小仙女    |                |
+-------+-------------+------+-----+-----------+----------------+
2 rows in set (0.00 sec)

mysql> INSERT INTO pm10 (id) VALUES (10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM pm10;
+----+-----------+
| id | name      |
+----+-----------+
| 10 | 小仙女    |
+----+-----------+
1 row in set (0.00 sec)

3、字段注释

字段注释，顾名思义就是给字段一个注释，有利于后期维护的时候快速理解字段含义。

案例：

-- 字段注释
mysql> CREATE TABLE IF NOT EXISTS pm11(
    ->   id int PRIMARY KEY AUTO_INCREMENT COMMENT "主键字段",
    -> name VARCHAR(20) DEFAULT "小仙女" COMMENT "名字"
    -> ) ENGINE=INNODB;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE pm11\G
*************************** 1. row ***************************
       Table: pm11
Create Table: CREATE TABLE `pm11` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键字段',
  `name` varchar(20) DEFAULT '小仙女' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


-- 表注释
mysql> CREATE TABLE IF NOT EXISTS pm12(
    ->   id int PRIMARY KEY AUTO_INCREMENT COMMENT "主键字段",
    -> name VARCHAR(20) DEFAULT "小仙女" COMMENT "名字"
    -> ) ENGINE=INNODB COMMENT "仙女表";
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE pm12\G
*************************** 1. row ***************************
       Table: pm12
Create Table: CREATE TABLE `pm12` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键字段',
  `name` varchar(20) DEFAULT '小仙女' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='仙女表'
1 row in set (0.00 sec)

7、连表查询

两个或多个表至之间通过某种关系，按照某种规则合并起来查询出来的数据即为连表查询，连表查询是企业中常用一种查询数据方式，在关系型数据库中连表查询是很常见的。但是连表查询仅仅限于同一个数据库内多张数据表相互链接，不同数据库中的数据便无法使用连表查询。

1、内连接（INNER JOIN ）

把两个数据表中的所有的数据一次性按照某种条件一次性查询出来。

案例

mysql> use linux13;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> CREATE TABLE IF NOT EXISTS student(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL COMMENT "学生名称",
    -> age TINYINT(2) NOT NULL DEFAULT 18 COMMENT "年龄"
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS major (
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL COMMENT "专业名称"
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE IF NOT EXISTS student_major(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> stu_id INT NOT NULL COMMENT "学生表ID",
    -> m_id INT NOT NULL COMMENT "专业表ID"
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS teacher(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) NOT NULL COMMENT "老师名称"
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS tearch_major(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> tea_id INT NOT NULL COMMENT "老师表ID",
    -> m_id INT NOT NULL COMMENT "专业表ID"
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO student (name,age) VALUES ("小明", 18),("小红", 17),("小花", 16);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO major (name) VALUES ("数学"),("英语"),("毛概");
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO teacher (name) VALUES ("李铁锤"),("石林"),("为李飞");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * FROM student;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 小明   |  18 |
|  2 | 小红   |  17 |
|  3 | 小花   |  16 |
+----+--------+-----+
3 rows in set (0.00 sec)

mysql> select * FROM teacher;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | 李铁锤    |
|  2 | 石林      |
|  3 | 为李飞    |
+----+-----------+
3 rows in set (0.00 sec)

mysql> select * FROM major;
+----+--------+
| id | name   |
+----+--------+
|  1 | 数学   |
|  2 | 英语   |
|  3 | 毛概   |
+----+--------+
3 rows in set (0.00 sec)


INSERT INTO student_major (stu_id, m_id) VALUES (1, 1),(1,2);
INSERT INTO student_major (stu_id, m_id) VALUES (2, 1),(2,3);
INSERT INTO student_major (stu_id, m_id) VALUES (3, 1),(3,2),(3,3);

mysql> select * from student_major;
+----+--------+------+
| id | stu_id | m_id |
+----+--------+------+
|  1 |      1 |    1 |
|  2 |      1 |    2 |
|  3 |      2 |    1 |
|  4 |      2 |    3 |
|  5 |      3 |    1 |
|  6 |      3 |    2 |
|  7 |      3 |    3 |
+----+--------+------+
7 rows in set (0.00 sec)


mysql> INSERT INTO tearch_major (tea_id, m_id) VALUES (1,3),(2,1),(3,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * FROM tearch_major;
+----+--------+------+
| id | tea_id | m_id |
+----+--------+------+
|  1 |      1 |    3 |
|  2 |      2 |    1 |
|  3 |      3 |    2 |
+----+--------+------+
3 rows in set (0.00 sec)

列：数据建立完毕之后，我们有如下几个需求：

1、查询出小明选修哪几门课？

-- 按照既学的内容，我们可以得出如下：

mysql> SELECT * FROM student WHERE id = 1;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 小明   |  18 |
+----+--------+-----+
1 row in set (0.00 sec)

mysql> SELECT * FROM student_major WHERE stu_id = 1;
+----+--------+------+
| id | stu_id | m_id |
+----+--------+------+
|  1 |      1 |    1 |
|  2 |      1 |    2 |
+----+--------+------+
2 rows in set (0.01 sec)

mysql> SELECT * FROM major WHERE id = 1 or id = 2;
+----+--------+
| id | name   |
+----+--------+
|  1 | 数学   |
|  2 | 英语   |
+----+--------+
2 rows in set (0.00 sec)

-- 通过上面可知，如果需要查询出选修的课程需要三步，非常的繁琐，那么我们介绍一下连表查询。
mysql> SELECT * FROM student INNER JOIN  student_major ON student.id = student_major.stu_id WHERE student.id = 1;
+----+--------+-----+----+--------+------+
| id | name   | age | id | stu_id | m_id |
+----+--------+-----+----+--------+------+
|  1 | 小明   |  18 |  1 |      1 |    1 |
|  1 | 小明   |  18 |  2 |      1 |    2 |
+----+--------+-----+----+--------+------+
2 rows in set (0.00 sec)
-- 由上可知，数据库连表查询非常方便快捷。

mysql> SELECT * FROM student INNER JOIN  student_major ON student.id = student_major.stu_id  INNER JOIN major ON student_major.m_id = major.id WHERE student.id = 1;
+----+--------+-----+----+--------+------+----+--------+
| id | name   | age | id | stu_id | m_id | id | name   |
+----+--------+-----+----+--------+------+----+--------+
|  1 | 小明   |  18 |  1 |      1 |    1 |  1 | 数学   |
|  1 | 小明   |  18 |  2 |      1 |    2 |  2 | 英语   |
+----+--------+-----+----+--------+------+----+--------+
2 rows in set (0.00 sec)

-- 由上简化得来
mysql> SELECT student.id,student.name,student.age, major.name FROM student INNER JOIN  student_major ON student.id = student_major.stu_id  INNER JOIN major ON student_major.m_id = major.id WHERE student.id = 1;
+----+--------+-----+--------+
| id | name   | age | name   |
+----+--------+-----+--------+
|  1 | 小明   |  18 | 数学   |
|  1 | 小明   |  18 | 英语   |
+----+--------+-----+--------+
2 rows in set (0.00 sec)

2、查询出小花选修的课的老师有哪些？

mysql> SELECT  student.id,student.name,student.age, major.name,teacher.name  FROM  student  INNER JOIN  student_major ON student.id = student_major.stu_id   INNER JOIN major ON student_major.m_id = major.id  INNER JOIN tearch_major ON tearch_major.m_id = major.id INNER JOIN teacher ON teacher.id =  tearch_major.tea_id WHERE student.id = 3;
+----+--------+-----+--------+-----------+
| id | name   | age | name   | name      |
+----+--------+-----+--------+-----------+
|  3 | 小花   |  16 | 毛概   | 李铁锤    |
|  3 | 小花   |  16 | 数学   | 石林      |
|  3 | 小花   |  16 | 英语   | 为李飞    |
+----+--------+-----+--------+-----------+
3 rows in set (0.00 sec)

2、左连接（LEFT JOIN）

左连接顾名思义就是以左边的表为主表，其他的表为副表；也就是说会把左边表中所有的符合条件的数据全部查询出来，至于后面的表有没有内容不管，没有内容则用空来代替。

案例：

-- 插入一个学生
INSERT INTO student (name, age) VALUES ("铁锤", 18);

SELECT * FROM student LEFT JOIN student_major ON student.id = student_major.stu_id ;
SELECT * FROM student INNER JOIN student_major ON student.id = student_major.stu_id;

mysql> SELECT * FROM student LEFT JOIN student_major ON student.id = student_major.stu_id ;
+----+--------+-----+------+--------+------+
| id | name   | age | id   | stu_id | m_id |
+----+--------+-----+------+--------+------+
|  1 | 小明   |  18 |    1 |      1 |    1 |
|  1 | 小明   |  18 |    2 |      1 |    2 |
|  2 | 小红   |  17 |    3 |      2 |    1 |
|  2 | 小红   |  17 |    4 |      2 |    3 |
|  3 | 小花   |  16 |    5 |      3 |    1 |
|  3 | 小花   |  16 |    6 |      3 |    2 |
|  3 | 小花   |  16 |    7 |      3 |    3 |
|  4 | 铁锤   |  18 | NULL |   NULL | NULL |
+----+--------+-----+------+--------+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM student INNER JOIN student_major ON student.id = student_major.stu_id;
+----+--------+-----+----+--------+------+
| id | name   | age | id | stu_id | m_id |
+----+--------+-----+----+--------+------+
|  1 | 小明   |  18 |  1 |      1 |    1 |
|  1 | 小明   |  18 |  2 |      1 |    2 |
|  2 | 小红   |  17 |  3 |      2 |    1 |
|  2 | 小红   |  17 |  4 |      2 |    3 |
|  3 | 小花   |  16 |  5 |      3 |    1 |
|  3 | 小花   |  16 |  6 |      3 |    2 |
|  3 | 小花   |  16 |  7 |      3 |    3 |
+----+--------+-----+----+--------+------+
7 rows in set (0.00 sec)

3、右链接（Right JOIN）

右链接顾名思义就是用右边表作为主表，其他表作为副表。也就是说，右链接是会把右边的表中的所有的数据全部都查询出来，至于左边的表如果没有数据既用空代替。

案例：

INSERT INTO student_major (stu_id,m_id) VALUES (4, 5);

mysql> SELECT * FROM  major LEFT JOIN student_major ON major.id = student_major.m_id;
+----+--------+------+--------+------+
| id | name   | id   | stu_id | m_id |
+----+--------+------+--------+------+
|  1 | 数学   |    1 |      1 |    1 |
|  2 | 英语   |    2 |      1 |    2 |
|  1 | 数学   |    3 |      2 |    1 |
|  3 | 毛概   |    4 |      2 |    3 |
|  1 | 数学   |    5 |      3 |    1 |
|  2 | 英语   |    6 |      3 |    2 |
|  3 | 毛概   |    7 |      3 |    3 |
|  4 | Linux  | NULL |   NULL | NULL |
+----+--------+------+--------+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM  major RIGHT JOIN student_major ON major.id = student_major.m_id;
+------+--------+----+--------+------+
| id   | name   | id | stu_id | m_id |
+------+--------+----+--------+------+
|    1 | 数学   |  1 |      1 |    1 |
|    1 | 数学   |  3 |      2 |    1 |
|    1 | 数学   |  5 |      3 |    1 |
|    2 | 英语   |  2 |      1 |    2 |
|    2 | 英语   |  6 |      3 |    2 |
|    3 | 毛概   |  4 |      2 |    3 |
|    3 | 毛概   |  7 |      3 |    3 |
| NULL | NULL   |  8 |      4 |    5 |
+------+--------+----+--------+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM  major INNER JOIN student_major ON major.id = student_major.m_id;
+----+--------+----+--------+------+
| id | name   | id | stu_id | m_id |
+----+--------+----+--------+------+
|  1 | 数学   |  1 |      1 |    1 |
|  2 | 英语   |  2 |      1 |    2 |
|  1 | 数学   |  3 |      2 |    1 |
|  3 | 毛概   |  4 |      2 |    3 |
|  1 | 数学   |  5 |      3 |    1 |
|  2 | 英语   |  6 |      3 |    2 |
|  3 | 毛概   |  7 |      3 |    3 |
+----+--------+----+--------+------+
7 rows in set (0.00 sec)

4、合并数据

顾名思义就是将多个SQL语句查询出来的数据合并一次性查询出来。需要注意的是，两边的字段必须一致。

案例：

mysql> SELECT name,age FROM student UNION SELECT * FROM teacher;
+--------+-----------+
| name   | age       |
+--------+-----------+
| 小明   | 18        |
| 小红   | 17        |
| 小花   | 16        |
| 铁锤   | 18        |
| 1      | 李铁锤    |
| 2      | 石林      |
| 3      | 为李飞    |
+--------+-----------+
7 rows in set (0.00 sec)

8、子查询

子查询顾名思义就是在SQL中依赖于另一个SQL语句的结果来共同查询一个结果。每一个子查询语句只能够返回一条数据。在工作用极其不建议使用子查询，因为子查询的性能非常低。

案例：

mysql> SELECT * FROM student WHERE id = (SELECT DISTINCT stu_id FROM student_major LIMIT 1);
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 小明   |  18 |
+----+--------+-----+
1 row in set (0.00 sec)