MySQL笔记

简介

概念

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。

关系型数据库： 采用了关系模型来组织数据的数据库，从语言功能层面来讲,一般是指用 SQL 语言来进行操作管理的关系型数据库。

优缺点

• 优点：
  • 运行速度快
  • 使用成本低
  • 使用容易
  • 可移植性强
  • 适用更多用户
• 缺点
  • 安全系统复杂而非标准
  • 没有存储过程语言
  • 不支持热备份
  • 缺乏标准的 RI（Referential Integrity-RI）机制
  • 价格随平台和安装方式变化

安装MySQL

• Window平台

  • 1.点击进入**官网**下载压缩包

    

  • 2.选择不用登陆，直接下载

    

  • 3.解压下载好的 .zip 文件到需要的安装目录下，如"G:\WWW\mysql"，并新建空文件 my.ini 文件如下图：

    

  • 4.配置初始化文件 my.ini 使用文本编辑器即可，写入如下内容:

    [mysqld]
    # 设置3306端口
    port=3306
    # 设置mysql的安装目录
    basedir=G:\WWW\mysql
    # 设置mysql数据库的数据的存放目录
    datadir=G:\WWW\mysql\data  
    # 允许最大连接数
    max_connections=200
    #允许连接失败的次数。防止有人从该主机试图攻击数据库系统
    max_connect_errors=10
    # 服务端使用的字符集默认为UTF8
    character-set-server=utf8
    # 创建新表时将使用的默认存储引擎
    default-storage-engine=INNODB
    # 默认使用“mysql_native_password”插件认证
    default_authentication_plugin=mysql_native_password
    [mysql]
    # 设置mysql客户端默认字符集
    default-character-set=utf8
    [client]
    # 设置mysql客户端连接服务端时默认使用的端口
    port=3306
    default-character-set=utf8
    

  • 5.初始化 MySQL，点击桌面开始搜索 cmd，右键以管理员身份运行 cmd ,进入 bin 目录，进入 bin 目录命令:

    cd /d G:\WWW\mysql\bin
    

    打开的 cmd 命令行窗口如下图，请注意当前目录在 bin 目录下：

    执行以下命令:

    mysqld --initialize --console #初始化MySQL环境,需耐心等待几分钟
    

    跟在 root@localhost: 后面的为初始化密码，需要记录一下，如图所示：

    

    安装 MySQL 服务:

    mysqld install
    

    出现如下图所示表示安装成功：

    

  • 6.关闭和启动 MySQL，在 bin 目录下执行如下命令：

    net start mysql # 启动命令
    
    net stop mysql # 关闭命令
    

    出现如图所示输出，说明 MySQL 服务已经启动成功：

    

  • 7.卸载MySQL

    cd /d G:\WWW\mysql\bin
    mysqld remove
    

    如下图所示:

    

• Linux平台

  见**另一篇帖子**

与MySQL 建立连接

命令行与MySQL建立连接

在 Windows 下打开 CMD 窗口，使用如下命令登录 MySQL：

mysql -uroot -p -P3306 -hlocalhost

命令中的 “-u” 后面表示登录用户名，“-p” 表示密码单独输入，“-P” 表示端口号，“-h” 表示主机地址，出现如下图所示表示需要输入密码，可将之前记录的初始密码右键粘贴回车即可，出现如下图所示表示登录成功：

第一次用初始密码登录成功后须修改初始密码，如果不修改初始密码，则会提示错误信息：

修改初始密码命令如下:

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

Java与MySQL建立连接

// 注册JDBC驱动
Class.forName(JDBC_DRIVER);

// 打开链接
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

// 执行查询
Statement stmt = conn.createStatement();
String sql = "SELECT sno, sname FROM student";
ResultSet rs = stmt.executeQuery(sql);

// 展开结果集数据库

while(rs.next()){
// 通过字段检索
String no = rs.getString("sno");
String name = rs.getString("sname");

// 输出数据
System.out.println("no: " + no + ", name: " + name);

// 完成后关闭
rs.close();
stmt.close();
conn.close();

MySQL 创建子账号

使用 root 账号登录 MySQL

使用 root 账号登录 MySQL，登录成功如图所示：

新建一个 MySQL 子账号，新建子账号命令如下：

CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

若出现如下图所示，则表示新建 MySQL 账号成功:

root 账号退出登录，退出 MySQL 登录状态命令如下，退出成功如图所示：

quit; #注意后面有分号

使用子账号登录 MySQL，命令如下,登录成功如下图：

mysql -utest -p -P3306 -hlocalhost

为什么要创建子账号?

在实际的业务开发中，需要多人合作同时对数据库进行操作，有开发人员也有运维人员。但是每个角色需要的数据库权限都是不一样的，例如开发王小明需要对数据库进行新增数据，但是不能删除和修改数据。运维小王只能对数据库进行读取，不能新增、修改和删除数据。这个时候只需要新建具有相应权限的 MySQL 子账户一一分配给对应人员就行了。

MySQL 创建数据库

• 进入MySQL

  mysql -utest -p;
  

• 查看数据库

  SHOW DATABASES; 
  

  

  如图所示当前已包含 MySQL系统数据库，这些数据库用来存储和管理 MySQL 服务相关的一些配置。

• 新建数据库

  CREATE DATABASE item_name;
  

  

• 验证是否创建成功

  SHOW DATABASES;
  

MySQL 删除数据库

• 登录MySQL

  mysql -utest -p;
  

• 查看当前已有数据库：

  SHOW DATABASES; 
  

  

• 删除指定数据库

  DROP DATABASE item_name;
  

• 查看指定的数据库是否删除成功

  SHOW DATABASES;
  

  

MySQL选中数据库

• 选中数据库

  use item_name;
  

  

• 创建数据库表

  如上图所示，表示已选中需要操作的数据库，选中之后就可以对数据库进行增删改查操作了。

  CREATE TABLE `table_name` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  

  如图所示，表示成功新建一个数据库表：

  

MySQL 的数据类型

整数类型

类型	大小	SIGNED(有符号)	UNSIGNED(无符号)	说明
tinyint	1字节	-128~127	0~255	小整型
smallint	2字节	-32768~32767	0~65535	小整型
mediumint	3字节	-8388608~8388607	0~16777215	中整型
int	4字节	-2147483648~2147483647	0~49294967295	整型
bigint	8字节	-9223372036854775808 ~ 9223372036854775807	0~18446744073709551615	大整型

浮点类型

类型	大小	SIGNED(有符号)	UNSIGNED(无符号)	说明
float	4字节	-3.402823466E+38 ~ -1.175494351E-38， 0， 1.175494351E-38 ~ 3.402823466351E+38	0，1.175494351E-38 ~ 3.402 823 466 E+38	单精度浮点型
double	8字节	-1.7976931348623157E+308 ~ -2.2250738585072014E-308， 0， 2.2250738585072014E-308 ~ 1.7976931348623157E+308	0， 2.2250738585072014E-308 ~ 1.797693134862315 7E+308	双精度浮点型
decimal	每4个字节存储9个数字，小数点占1字节	DECIMAL(M,D)，取决于M和D,M是整数部分，D是小数部分	DECIMAL(M,D)，取决于M和D，M是整数部分,D是小数部分	精确类型,常用来表示金额

日期和时间类型

类型	大小	格式	范围
date	3字节	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
time	3-6字节	HH::MM:SS[.微秒]	-838:59:59 ~ 838:59:59
year	1字节	YYYY	1901 ~ 2155
datetime	5-8字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 UTC
timestamp	4-7字节	YYYY-MM-DD HH:MM:SS[.微秒值]	1970-01-01 00:00：00 ~ 2038-01-19 03:14:07 UTC

字符串类型

类型	范围	说明
char	1~255个字节	固定长度
varchar	字段存储所占字节数不能超过65535字节	可变长度
tinytext	最大长度255字节	可变长度
text	最大长度65535个字节	可变长度
mediumtext	最大长度16777215个字节	可变长度
longtext	最大长度4294967295个字节	可变长度

枚举值类型

类型	范围	说明
enum	几何数最大65535	枚举值类型,只能插入列表中指定的值

二进制类型

类型	范围	说明
tinyblob	最大长度255字节	可变长度
blob	最大长度65535个字节	可变长度
mediumblob	最大长度16777215个字节	可变长度
longblob	最大长度4294967295个字节	可变长度

MySQL 设计数据表

数据表命名规范

数据表命名是小写字母和下划线 _ 组成，用来分割不同单词之间的含义，例如 “student_course” 表示学生选课关联表，实际命名需要根据具体功能而定，好的命名规范在实际工作中也是很重要的。

新建学生信息表 student

确定字段

字段名称	含义
id	自增主键
name	学生姓名
age	学生年龄
id_number	身份证号

学生信息表包含的字段可以有很多，本小节选择学生姓名、年龄、身份证号介绍字段的选择，字段的确定是根据实际业务需求来新增或减少的，例如想要存储学生籍贯信息可新增 “address” 字段。

选择字段的数据类型

字段名称	数据类型
id	无符号整型(UNSIGNED INT)
name	VARCHAR(50)
age	UNSIGNED INT
id_number	VARCHAR(18)

Tips：实际业务中一般要将年龄字段设置为可变，通常会使用时间戳（距离1970-01-01 00:00:00的秒数）来表示年龄相关的信息，在这里为了方便演示直接使用了 UNSIGNED INT。

一般来说姓名字段的长度为 2-4 个字符，但是考虑到少数民族的学生姓名比较长，所以将姓名字段的长度设置为 50，适当的给的大一点。身份证号同样使用字符串类型来存储，因为有的身份证号中有字母。身份证的长度统一都是 18 位不变，直接在这里写死就好。

新建数据表

命令 : 
CREATE TABLE `student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '无名',
  `age` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `id_number` varchar(18) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

Tips：其中 “student” 为表名称，“id”、“name”、“age”、“id_number” 为字段名称，跟在字段名称后面的是字段的数据类型，“UNSIGNED” 表示无符号，“AUTO_INCREMENT” 表示自增，"PRIMARY KEY (id)"表示设置 “id” 为业务主键,，"NOT NULL DEFAULT ‘无名’ " 表示默认不为空，且默认值为 “无名” 。

执行结果如下图：

查看当前数据库中所有的数据表：

show tables

可以看到 item_name 数据库中已经有了 student 这张数据表。

MySQL 数据表设计规范

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。

第二设计范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分，为实现区分通常需要我们设计一个主键来实现（这里的主键不包含业务逻辑）。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）。

第三设计范式

满足第三范式（3NF）必须先满足第二范式（2NF），简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放（能尽量外键 join 就用外键 join）。很多时候我们为了满足第三范式往往会把一张表分成多张表。

即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。 通俗解释就是一张表最多只存两层同类型信息。

反范式化设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于 DML 的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的。

ALTER 命令修改表

修改数据表名称

ALTER TABLE 旧的表名 RENAME TO 新的表名;

查看建表 sql 语句

SHOW CREATE table_name;

修改表字段数据类型

修改表字段数据类型命令，以上面 “new_student” 表的 “name” 字段为例，varchar 类型修改为 char类型 ：

ALTER TABLE `new_student` 
MODIFY COLUMN `name` char(50) CHARACTER 
SET utf8 COLLATE utf8_general_ci 
NOT NULL DEFAULT '无名' AFTER `id`;

其中 “CHARACTER SET utf8 COLLATE utf8_general_ci” 为新字段类型的字符集和编排方式，默认值为 “无名”，“AFTER id” 表示跟在 id 字段后面。

新增表字段

新增数据表的字段命令,以新增性别字段为例:

 ALTER TABLE `new_student` 
ADD COLUMN `sex` tinyint(2) UNSIGNED NOT NULL DEFAULT 1 COMMENT '性别 : 1:男 2:女' AFTER `id_number`;

其中COMMENT 性别 : 1:男 2:女’ 表示该字段的注释说明。

删除表字段

删除表字段命令如下，以删除上面性别字段为例 :

ALTER TABLE `new_student` 
DROP COLUMN `sex`;

修改表字段名称

修改表字段数据类型命令，以 name 字段改为 new_name 为例：

ALTER TABLE `new_student` 
CHANGE COLUMN `name` `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '无名' AFTER `id`;

修改表字段默认值

修改表字段默认值命令，以修改 new_name 字段默认值为例 :

ALTER TABLE `new_student` 
MODIFY COLUMN `new_name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '小花' AFTER `id`;

MySQL删除数据表

使用这条命令将 item_name 数据库中的 new_student数据表删除掉:

 DROP TABLE new_student;

INSERT 插入数据

向数据表插入一条数据

以teacher 表为例， teacher 表的字段为 name、age、id_number，向教师表新增一条数据命令如下:

 INSERT INTO teacher (name,age,id_number) VALUES ('秦小贤',18,'42011720200604088X');

查询表所有数据

 SELECT * FROM teacher;

向数据表插入多条数据

INSERT INTO teacher
(name,age,id_number)
VALUES
('王小花',19,'42011720200604077X'),
('张晓丽',18,'42011720200604099X'),
('刘美丽',20,'42011720200604020X'),
('吴帅',21,'42011720200604022X'),
('张平',22,'42011720200604033X')

DELETE 删除数据

删除一条数据

以 teacher 表为例，删除一条指定 id=8 的数据，命令如下:

DELETE FROM teacher WHERE id = 8;

删除全部数据

以 teacher 表为例，删除多条数据命令如下:

DELETE FROM teacher;

清空表数据

TRUNCATE TABLE new_student;

SELECT查询数据

查询表中所有数据

以 teacher 表为例，查询 teacher 表中的所有数据：

SELECT * FEOM teacher;

查询指定条数的结果集

有时查询出来的所有数据会很多，只需要前 10 条或者分批次查询，每次查询指定条数，例如查询前10 条，可以使用命令:

SELECT * FROM teacher LIMIT 10;

查询指定起始位置条数的结果集

有时想要查询指定起始位置指定条数的结果集，例如想要查询第 11 条开始的后面 10 条数据可以使用命令 :

SELECT * FROM teacher LIMIT 10,10;

指定位置时，起始值是从 0 开始的，所以例子中 “LIMIT 10,10” 前面的 10 表示第 11 条，后面的 10 表示需要查询的条数，若不指定其实位置,默认是 0，即 “LIMIT 10” 和 “LIMIT 0,10” 是同一个意思。

查询指定字段列的结果集

以 teacher 表为例，只需要查询 name、age 字段的列表值，命令如下:

SELECT name,age FROM teacher LIMIT 6,5;

也可以给指定的字段重命名：

SELECT name AS new_name,age FROM teacher LIMIT 6,5;

UPDATE 更新数据

更新某一列字段的值

以 teacher 表为例，需要把前 3 条数据的 age 更新为 33，命令如下:

 UPDATE teacher SET age = 33 LIMIT 3;

更新多列字段的值

以 teacher 为例，更新 id = 30 这条数据的 age=18，id_number=44444444440604099X，命令如下：

LIKE 模糊查询

模糊查询表达式

% 表示指代任意内容,例如 '%小%' 表示包含 小 的表达式,且 小 前后都有内容, '%小' 表示以 小 结尾的表达式,王 前面有内容,后面没有内容,'小%' 表示以 小 开头的表达式,小 前面没有内容,后面有内容。

使用LIKE模糊查询

以teacher 表为例，需要查询出姓 王 的教师信息结果集:

SELECT * FROM teacher WHERE name LIKE '王%';

WHERE 条件

WHERE 条件的操作符类型有如下:

符号	说明	举例
<	小于,< 左边的值如果小于右边的值,则结果为 TRUE,否则为 FALSE	如 : 满足年龄小于 18 的条件 age < 18
=	等于,= 左边的值如果等于右边的值,则结果为 TRUE,否则为 FALSE	如 : 姓名为 小明 的条件 name = '小明'
>	大于,> 左边的值如果大于右边的值,则结果为 TRUE,否则为 FALSE	如 : 时间戳大于 2020-03-30 00:00:00的条件 time > 1585497600
<>	不等于,<>还可写成 != ，左边的值如果不等于右边的值,则结果为 TRUE,否则为 FALSE	如 : 年份不等于2012的条件 year != 或 year <> 2012
<=	小于等于,<= 左边的值如果大于右边的值,则结果为 FALSE,否则为 TRUE	如 : 满足年龄小于等于 18 的条件 age <= 18
>=	大于等于,>= 左边的值如果小于右边的值,则结果为 FALSE,否则为 TRUE	如 : 满足年龄大于等于 18 的条件 age >= 18
LIKE	模糊条件,LIKE 右边的值如果包含左边的值,则结果返回TRUE,否则为 FALSE	如 : 满足身份证号为 420 开头的条件 id_number LIKE '420%',其中 % 表示任意值
NOT LIKE	不满足模糊条件,LIKE 右边的值如果不包含左边的值,则结果返回TRUE,否则为 FALSE	如 : 满足身份证号不是 X 结尾的条件 id_number NOT LIKE '%X',其中 % 表示任意值
BETWEEN AND	在两个值之间(包含两端值)	如 : 年龄满足 大于等于20 和 小于等于30 的条件 age BETWEEN 20 AND 30
NOT BETWEEN AND	不在在两个值之间(不包含两端值)	如 : 年龄满足 小于20 和 大于30 的条件 age NOT BETWEEN 20 AND 30
IS NULL	空,IS NULL 左边的值如果为空,则返回TRUE,否则为FALSE	如 : 年龄满足 邮箱为空 的条件 email IS NULL
IS NOT NULL	不是空,IS NOT NULL 左边的值如果不为空,则返回TRUE,否则为FALSE	如 : 年龄满足 邮箱不为空 的条件 email IS NOT NULL

单条件查询

以 teacher 表为例，查询年龄大于 18 的教师信息：

SELECT * FROM teacher WHERE age > 18;

再比如使用 LIKE 模糊查询身份证号以 020X 结尾的教师信息:

SELECT * FROM teacher WHERE id_number LIKE '%020X';

AND多条件查询

以 teacher 表为例，查询年龄大于 18 和 教师姓氏 王 的教师信息：

SELECT * FROM teacher WHERE age > 18 AND name LIKE  '王%';

OR 多条件查询

以 teacher 表为例,查询年龄大于 25 或 教师姓氏 王 的教师信息：

SELECT * FROM teacher WHERE age > 25 OR name LIKE  '王%';

BETWEEN AND 更新数据

以 teacher 表为例，将 age 在 20 和 30 之间的教师身份证设置为无:

UPDATE teacher SET id_number = '无' WHERE age BETWEEN 20 AND 30;

IS NULL 查询

查询 email 为 NULL 的教师信息结果集：

SELECT * FROM teacher WHERE email IS NULL;

UNION 联合查询

UNION ALL联合查询

使用 UNION ALL 把满足两种查询条件的结果集并到一起：

SELECT * FROM teacher WHERE age > 20
UNION ALL
SELECT * FROM teacher WHERE age > 25;

如上图所示,UNION ALL 将两种查询结果并到一起，仔细观察可以发现结果集中有重复的数据，所以使用 UNION ALL 联合查询的结果集没有去掉重复的数据。

UNION 联合查询

现在使用 UNION 把上面两种结果集并到一起：

SELECT * FROM teacher WHERE age > 20
UNION
SELECT * FROM teacher WHERE age > 25;

如上图所示,UNION 将两种查询结果并到一起，可以看到结果集中已经去掉重复的数据，需要根据具体业务选择使用 UNION 还是 UNION ALL。

ORDER BY 排序

ASC 从小到大排序

ASC 是对结果集按照字段从小到大排序（升序），以 teacher 表为例，将查询出来的所有结果集按照年龄 age 从小到大排序：

 SELECT * FROM teacher ORDER BY age ASC;

DESC 从大到小排序

DESC 是对结果集按照字段从大到小排序（降序），以 teacher 表为例，将查询出来的所有结果集按照年龄 id 从大到小排序：

 SELECT * FROM teacher ORDER BY id DESC;

使用多字段混合排序

以 teacher 表为例，将查询出来的结果集按照 age 从大到小排序之后，再按照 id 字段从小到大排序：

SELECT * FROM teacher ORDER BY age DESC,id ASC;

对字符串类型字段排序

对查询结果集按照 name 字段 ASC 升序排序：

SELECT * FROM teacher ORDER BY name ASC;

如上图所示，使用 ASC 对结果集按照 name 字段升序，其实是对 name 字段字符串编码的排序，英文字符排序在前，中文在后，其中排序规则是按照字符 ASCII码 对应值的大小排序的。

JOIN 表连接

LEFT JOIN 左连接

以课程表 course 和 teacher 左连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c LEFT JOIN teacher t ON c.teacher_id=t.id; 

这对上述 sql 语句说明如下：

• c.id AS course_id 表示将 course表 中 id 字段重命名为 course_id 展示，其目的是为了防止和 teacher表 中 id 字段混淆；
• c.* 表示 course 表所有字段数据；
• t.* 表示 teacher 表字段所有数据；
• ON 后面跟着的条件是连接表的条件；
• course c 表示将 course 简写为 c， teacher t 表示将 teacher 简写为 t；
• LEFT JOIN 为左连接，是以左边的表为’基准’，若右表没有对应的值，用 NULL 来填补。

INNER JOIN 内连接

同样以表 course 和 teacher 内连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c INNER JOIN teacher t ON c.teacher_id=t.id;

INNER JOIN 为内连接，展示的是左右两表都有对应的数据。

RIGHT JOIN 右连接

同样以表 course 和 teacher 右连接为例：

SELECT c.id AS course_id,c.*,t.* FROM course c RIGHT JOIN teacher t ON c.teacher_id=t.id;

RIGHT JOIN 为右连接，是以右边的表为’基准’，若左表没有对应的值，用 NULL 来填补。

多表混合连接

以本小节所有数据全部连接查询为例：

SELECT * FROM 
student a 
LEFT JOIN 
student_course b 
ON a.id=b.student_id 
RIGHT JOIN 
course c 
ON b.course_id=c.id 
INNER JOIN teacher d 
ON c.teacher_id=d.id;

多表混合连接查询时，后面可以把前面执行的结果集整体当成一个表，例如 SELECT * FROM student a LEFT JOIN student_course b ON a.id=b.student_id RIGHT JOIN course c ON b.course_id=c.id 可以理解为 student 和 student_course 左连接查询之后的结果集再对 course 右连接查询。

GROUP BY分组

以表 course 为例，需要查出所有课程教师相关信息，按照 teacher_id 字段分组可去除重复数据：

 SELECT teacher_id FROM course GROUP BY teacher_id;

DISTINCT 去重

以表 student_course 和 表 student 链接为例：

SELECT * FROM student_course a INNER JOIN student b ON a.student_id=b.id;

上图查询结果中，若只需要学生信息，则需要对结果进行去重：

SELECT DISTINCT student_id 
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id;

聚合函数

AVG函数求平均值

可以使用 AVG() 函数求出全部教师平均年龄：

SELECT AVG(age) FROM teacher;

COUNT函数统计总条数

可以使用 COUNT() 函数统计全部学生数量：

SELECT COUNT(*) FROM student;

也可以对某一列使用 COUNT() 函数：

SELECT COUNT(id) FROM teacher;

SUM函数统计总和

以 teacher 表为例，获取所有教师年龄总和：

SELECT SUM(age) FROM teacher;

MIN函数取最小值

可以使用 MIN() 函数对结果集取年龄最小值的数据：

SELECT MIN(age) FROM teacher;

MAX函数取最大值

可以使用 MAX() 函数对结果集取年龄最大值的数据：

SELECT MAX(age) FROM student;

GROUP BY HAVING

可以使用 HAVING 对结果筛选，例如选出选课学生平均年龄大于 20 的课程数据：

SELECT a.course_id,c.course_name,AVG(age) 
FROM 
student_course a 
INNER JOIN 
student b 
ON a.student_id=b.id 
INNER JOIN course c 
ON a.course_id=c.id
GROUP BY a.course_id,c.course_name
HAVING AVG(age) >= 20;

条件判断函数

IF函数

以 student 表为例，使用 IF() 函数对查询结果的字段判断：

SELECT name,IF(age > 17,'成年','未成年') AS age_group,id_number FROM student;

IFNULL函数

SELECT name,age,id_number,IFNULL(email,'default@qq.com') AS full_email FROM teacher;

CASE条件判断

以 teacher 表为例，将指定英文名对应的中文名展示出来：

SELECT 
*，
CASE name
WHEN 'Tom'  THEN '汤姆'
WHEN 'Jack' THEN '杰克'
WHEN 'Mary' THEN '玛丽'
WHEN 'Timo' THEN '提莫'
WHEN 'Bob'  THEN '鲍勃'
WHEN 'Judy' THEN '朱蒂'
ELSE '未定义' END AS 'chinese_name'
FROM teacher;

如上图所示， 对 name 字段进行条件判断，并将判断后的列重命名为 chinese_name，若指定的 name 字段的值满足 WHEN 则展示相应的 THEN 后面的值。

MySQL 的系统函数

MySQL 提供了很多的系统函数，这些系统函数可以对数据进行特殊的处理，下面表格列举了大部分的系统函数。

函数名	说明
CONCAT(str0,str1)	将两个字段的值拼接在一起
CONCAT_WS(“拼接符”,str0,str1)	将两个字段的值使用指定拼接符拼接在一起
GROUP_CONCAT(expr)	将指定分组的字段内容拼接在一起
UPPER(str)	将字符串包含的英文字符小写转化成大写
lOWER(str)	将字符串包含的英文字符大写转化成小写
INITCAP(str)	将字符串的首字母变成大写
LENGTH(str)	获取字符串的长度
SUBSTR(str FROM pos FOR len)	截取字符串，其中 pos 表示其实位置，len 表示结束位置
TRIM(str)	去除字符串两边空格
INSTR(str,substr)	查找指定字符在字符串的中的位置，其中 substr 表示需要查找的字符，例如 'Tom' 中 'T' 的位置为 1，'m' 的位置为 3
LPAD(str,len,padstr)	左填充，其中 len，表示字符串总长度，padstr 表示填充的字符
RPAD(str,len,padstr)	右填充，其中 len，表示字符串总长度，padstr 表示填充的字符
LEFT(str,len)	取一个字符串的前多少位
RIGHT(str,len)	取一个字串的后多少位
CEIL(X)	向上取整
FLOOR(X)	向下取整
MOD(N,M)	取余，例如 MOD(age,5)，将字段 age 除以 5,除不尽的取余数
POWER(X,Y)	幂运算，例如 MOD(age,2)，获取字段 age 值的 2 次方的值
NOW()	获取当前日期和时间
CURDATE()	获取当前日期，不包含时间
CURTIME()	获取当前时间，不包含日期
YEAR(now())	获取当前的年份
HOUR(NOW())	获取当前时间的小时数
MINUTE(now())	获取当前时间的分钟数
SECOND(NOW())	获取当前时间的秒数
MONTHNAME(now())	获取当前日期的英文月份
MONTH(NOW())	获取当前日期的数字月份
DATE_ADD(date,INTERVAL expr unit)	查询日期的变化，例如 DATE_ADD(‘2020-03-03’,INTERVAL 10 day) 表示 2020-03-03 十天之后的日期
DATEDIFF(expr1,expr2)	日期差，例如 DATEDIFF('2019-12-29'，'2019-12-01') 表示 2019-12-29 距离 2019-12-01 的天数
DATE_FORMAT(date,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT('2019-12-29'，'%m/%d/%y') ，其中 %m 表示前面对应的月份，%d 表示前面对应的日期的天数，%y 表示前面对应的年份
STR_TO_DATE(str,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT('12-29-2019'，'%m/%d/%y')
DATE_FORMAT(date,format)	将指定日期转化为自定义格式，例如 DATE_FORMAT('2019/12/29'，'%m/%d/%y')
MD5(str)	对字符串进行 MD5 转换
UNIX_TIMESTAMP()	将指定日期转化为时间戳
COUNT()	获取查询结果集条数
AVG(expr)	获取指定列平均值
SUM(expr)	获取指定字段值的总和
MIN(expr)	获取指定字段值的最小值
MAX(expr)	获取指定字段值的最大值

MySQL 中使用正则表达式

正则表达式中的元字符

元字符	描述
\	将下一个字符标记符、或一个向后引用、或一个八进制转义符。例如，“\n”匹配\n。“\n”匹配换行符。序列“\”匹配“\”而“(”则匹配“(”。即相当于多种编程语言中都有的“转义字符”的概念。
^	匹配输入字行首。如果设置了RegExp对象的Multiline属性，^也匹配“\n”或“\r”之后的位置。
$	匹配输入行尾。如果设置了RegExp对象的Multiline属性，$也匹配“\n”或“\r”之前的位置。
*	匹配前面的子表达式任意次。例如，zo*能匹配“z”，也能匹配“zo”以及“zoo”。*等价于{0,}。
+	匹配前面的子表达式一次或多次(大于等于1次）。例如，“zo+”能匹配“zo”以及“zoo”，但不能匹配“z”。+等价于{1,}。
?	匹配前面的子表达式零次或一次。例如，“do(es)?”可以匹配“do”或“does”。?等价于{0,1}。
{n}	n是一个非负整数。匹配确定的n次。例如，“o{2}”不能匹配“Bob”中的“o”，但是能匹配“food”中的两个o。
{n,}	n是一个非负整数。至少匹配n次。例如，“o{2,}”不能匹配“Bob”中的“o”，但能匹配“foooood”中的所有o。“o{1,}”等价于“o+”。“o{0,}”则等价于“o*”。
{n,m}	m和n均为非负整数，其中n<=m。最少匹配n次且最多匹配m次。例如，“o{1,3}”将匹配“fooooood”中的前三个o为一组，后三个o为一组。“o{0,1}”等价于“o?”。请注意在逗号和两个数之间不能有空格。
?	当该字符紧跟在任何一个其他限制符（*,+,?，{n}，{n,}，{n,m}）后面时，匹配模式是非贪婪的。非贪婪模式尽可能少地匹配所搜索的字符串，而默认的贪婪模式则尽可能多地匹配所搜索的字符串。例如，对于字符串“oooo”，“o+”将尽可能多地匹配“o”，得到结果[“oooo”]，而“o+?”将尽可能少地匹配“o”，得到结果 [‘o’, ‘o’, ‘o’, ‘o’]
.	匹配除“\n”和"\r"之外的任何单个字符。要匹配包括“\n”和"\r"在内的任何字符，请使用像“[\s\S]”的模式。
(pattern)	匹配pattern并获取这一匹配。所获取的匹配可以从产生的Matches集合得到，在VBScript中使用SubMatches集合，在JScript中则使用$0…$9属性。要匹配圆括号字符，请使用“(”或“)”。
(?:pattern)	非获取匹配，匹配pattern但不获取匹配结果，不进行存储供以后使用。这在使用或字符来组合一个模式的各个部分时很有用。
(?=pattern)	非获取匹配，正向肯定预查，在任何匹配pattern的字符串开始处匹配查找字符串，该匹配不需要获取供以后使用。预查不消耗字符，也就是说，在一个匹配发生后，在最后一次匹配之后立即开始下一次匹配的搜索，而不是从包含预查的字符之后开始。
(?!pattern)	非获取匹配，正向否定预查，在任何不匹配pattern的字符串开始处匹配查找字符串，该匹配不需要获取供以后使用。例如“Windows(?!95
(?<=pattern)	非获取匹配，反向肯定预查，与正向肯定预查类似，只是方向相反。*python的正则表达式没有完全按照正则表达式规范实现，所以一些高级特性建议使用其他语言如java、scala等
(?<!patte_n)	非获取匹配，反向否定预查，与正向否定预查类似，只是方向相反。*python的正则表达式没有完全按照正则表达式规范实现，所以一些高级特性建议使用其他语言如java、scala等
[xyz]	字符集合。匹配所包含的任意一个字符。例如，“[abc]”可以匹配“plain”中的“a”。
[^xyz]	负值字符集合。匹配未包含的任意字符。例如，“[^abc]”可以匹配“plain”中的“plin”任一字符。
[a-z]	字符范围。匹配指定范围内的任意字符。例如，“[a-z]”可以匹配“a”到“z”范围内的任意小写字母字符。注意:只有连字符在字符组内部时,并且出现在两个字符之间时,才能表示字符的范围; 如果出字符组的开头,则只能表示连字符本身.
[^a-z]	负值字符范围。匹配任何不在指定范围内的任意字符。例如，“[^a-z]”可以匹配任何不在“a”到“z”范围内的任意字符。
\b	匹配一个单词的边界，也就是指单词和空格间的位置（即正则表达式的“匹配”有两种概念，一种是匹配字符，一种是匹配位置，这里的\b就是匹配位置的）。例如，“er\b”可以匹配“never”中的“er”，但不能匹配“verb”中的“er”；“\b1_”可以匹配“1_23”中的“1_”，但不能匹配“21_3”中的“1_”。
\B	匹配非单词边界。“er\B”能匹配“verb”中的“er”，但不能匹配“never”中的“er”。
\cx	匹配由x指明的控制字符。例如，\cM匹配一个Control-M或回车符。x的值必须为A-Z或a-z之一。否则，将c视为一个原义的“c”字符。
\d	匹配一个数字字符。等价于[0-9]。grep 要加上-P，perl正则支持
\D	匹配一个非数字字符。等价于[^0-9]。grep要加上-P，perl正则支持
\f	匹配一个换页符。等价于\x0c和\cL。
\n	匹配一个换行符。等价于\x0a和\cJ。
\r	匹配一个回车符。等价于\x0d和\cM。
\s	匹配任何不可见字符，包括空格、制表符、换页符等等。等价于[ \f\n\r\t\v]。
\S	匹配任何可见字符。等价于[^ \f\n\r\t\v]。
\t	匹配一个制表符。等价于\x09和\cI。
\v	匹配一个垂直制表符。等价于\x0b和\cK。
\w	匹配包括下划线的任何单词字符。类似但不等价于“[A-Za-z0-9_]”，这里的"单词"字符使用Unicode字符集。
\W	匹配任何非单词字符。等价于“[^A-Za-z0-9_]”。
\xn	匹配n，其中n为十六进制转义值。十六进制转义值必须为确定的两个数字长。例如，“\x41”匹配“A”。“\x041”则等价于“\x04&1”。正则表达式中可以使用ASCII编码。
\num	匹配num，其中num是一个正整数。对所获取的匹配的引用。例如，“(.)\1”匹配两个连续的相同字符。
\n	标识一个八进制转义值或一个向后引用。如果\n之前至少n个获取的子表达式，则n为向后引用。否则，如果n为八进制数字（0-7），则n为一个八进制转义值。
\nm	标识一个八进制转义值或一个向后引用。如果\nm之前至少有nm个获得子表达式，则nm为向后引用。如果\nm之前至少有n个获取，则n为一个后跟文字m的向后引用。如果前面的条件都不满足，若n和m均为八进制数字（0-7），则\nm将匹配八进制转义值nm。
\nml	如果n为八进制数字（0-7），且m和l均为八进制数字（0-7），则匹配八进制转义值nml。
\un	匹配n，其中n是一个用四个十六进制数字表示的Unicode字符。例如，\u00A9匹配版权符号（©）。
\p{P}	小写 p 是 property 的意思，表示 Unicode 属性，用于 Unicode 正表达式的前缀。中括号内的“P”表示Unicode 字符集七个字符属性之一：标点字符。其他六个属性：L：字母；M：标记符号（一般不会单独出现）；Z：分隔符（比如空格、换行等）；S：符号（比如数学符号、货币符号等）；N：数字（比如阿拉伯数字、罗马数字等）；C：其他字符。*注：此语法部分语言不支持，例：javascript。
\< \>	匹配词（word）的开始（<）和结束（>）。例如正则表达式能够匹配字符串"for the wise"中的"the"，但是不能匹配字符串"otherwise"中的"the"。注意：这个元字符不是所有的软件都支持的。
( )	将( 和 ) 之间的表达式定义为“组”（group），并且将匹配这个表达式的字符保存到一个临时区域（一个正则表达式中最多可以保存9个），它们可以用 \1 到\9 的符号来引用。

使用REGEXP正则表达式查询

以 student 表为例，使用正则表达式查询出姓王的学生：

SELECT * FROM student WHERE name REGEXP '^王';

以 teacher 表为例，使用正则表达式匹配符合邮箱格式的数据：

SELECT * FROM teacher  WHERE email REGEXP '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$';