文章目录

一、数据库基本概念1.1 数据1.2 数据库1.3 数据库管理系统1.4 数据库系统二、数据库模型2.1 数据库系统的三种模式2.2 三个级别三、SQL语言基本规则3.1 SQL 语言基本定义3.2 SQL 语言分类3.3 数据类型四、SQL 通用语言4.1 启动和连接 SQL 服务4.2 数据定义语言（ DDL ）4.3 数据操作语言（DML）4.4 数据查询语言（DQL）4.5 数据控制语言（DCL）五、函数5.1 字符串函数5.2 数值函数5.3 日期函数5.4 流程控制函数六、约束6.1 概述6.2 约束演示七、多表查询7.1 概述7.2 连接查询7.3 子查询7.3.1 基本知识7.3.2 标量子查询7.3.3 列子查询7.3.4 行子查询7.3.5 表子查询八、事务8.1 简介8.2 事务操作

一、数据库基本概念

1.1 数据

数据（data）是数据库存储的基本对象，是描述事物的符号记录；

1.2 数据库

数据库（ dataBase，DB）是长期储存在计算机内、有组织的、可共享的大量数据的集合；

数据库的基本特征：

数据按一定的数据模型组织、描述和储存；可为各种用户共享；冗余度小；数据独立性搞；易扩展；

1.3 数据库管理系统

数据库管理系统（DBMS）是位于用户与操作系统之间的一层数据管理软件，是一个大型复杂的软件系统；

​DBMS 的用途：

科学地组织和存储数据；高效地获取和维护数据；

1.4 数据库系统

数据库系统（ Database System ，DBS）是计算机系统中引入数据库后的系统构成；

数据库系统的构成：

数据库；硬件平台；软件（ 应用程序 ）；数据库管理员；

数据库系统的设计目标是允许用户逻辑地处理数据，而不必涉及这些数据在计算机中是如何存放的，在数据组织和用户应用之间提供某种程度的独立性；

二、数据库模型

2.1 数据库系统的三种模式

​ 数据库系统可分为：

外模式（子模式、用户模式）：

（1）数据库用户（ 包括应用程序员和最终用户 ）使用的局部数据的逻辑结构和特征的描述；

（2）数据库用户的数据视图，是与某一应用有关的数据的逻辑表示；

（3）外模式的地位：介于模式与应用之间；

（4）模式与外模式的关系：一对多；

（5）外模式通常是模式的子集；

（6）一个数据库可以有多个外模式，反映了不同的用户的应用需求、看待数据的方式、对数据保密的要求；

（7）外模式的用途：

保证数据库安全性的一个有力措施；每个用户只能看见和访问所对应的外模式中的数据；

模式（概念模式、逻辑模式）：

数据库中全体数据的逻辑结构和特征的描述；

所有用户的公共数据视图，综合了所有用户的需求

一个数据库只有一个模式；

内模式（存储模式）：

是数据物理结构和存储方式的描述；是数据在数据库内部的表示方式；一个数据库只有一个内模式；

2.2 三个级别

与三级模式相对应，数据库系统可以划分为三个抽象级：

用户级数据库：对应于外模式，是用户看到和使用的数据库，又称用户视图，一个数据库可以有多个不同的用户视图；概念级数据库：对应于概念模式，是所有的用户视图的最小并集，一个数据库应用系统只有一个 DBA 视图；物理级数据库：对应于内模式，是数据库的低层表示，它描述数据的实际存储组织，是最接近于物理存储的，又称内部视图；

三、SQL语言基本规则

3.1 SQL 语言基本定义

SQL 是结构化查询语言（ Structured Query Language ）的缩写，其功能包括数据查询、数据操控、数据定义和数据控制四个部分；

SQL 语言简洁、方便实用、功能齐全，已成为目前应用最广的关系数据库语言；SQL 既是自含式语言（ 联机交互 ），又是嵌入式语言（ 宿主语言 ）；

两个基本概念

基本表：

是独立存在的表，不是由其它的表导出的表，一个关系对应一个基本表，一个或多个基本表对应一个存储文件；

视图：

视图是一个虚拟的表，是一个或几个基本表导出的表，数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变；

3.2 SQL 语言分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象（ 数据库，表，字段 ）；DML（ Data Manipulation Language）：数据操作语言，用来对数据表中的数据进行增删改；DQL（ Data Query Language）：数据查询语言，用来查询数据库中表的记录；DCL（ Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限；

3.3 数据类型

MySql 中的数据类型有很多,主要分为三类:数值类型,字符串类型,日期时间类型;

部分数值类型> > > > > > > >部分字符串类型> > > > > > > >部分日期类型> > > > > >

四、SQL 通用语言

4.1 启动和连接 SQL 服务

MySQL服务启动（以管理员运行）：

-- 启动服务net start mysql80-- 关闭服务net stop mysql80

客户端连接：

-- 写法一mysql [-h 127.0.0.1] [-p 3306] -u root -p-- 写法二MySQL -u root -p

4.2 数据定义语言（ DDL ）

查询：

-- 查询所有数据库SHOW DATABASES;-- 查询当前数据库SELECT DATABASE();-- 查询当前数据库所有表SHOW TABLES;-- 查询表结构DESC 表名；-- 查询指定表的建表语句SHOW CREATE TABLE 表名;

创建：

/*IF NOT EXISTS：如果不存在DEFAULT CHARSET：指定默认字符集COLLATE 指定排序规则*/-- 创建一个数据库CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ]-- 创建一个表CREATE TABLE 表名(字段1 字段1类型[ COMMENT 字段1注释 ],字段2 字段2类型[ COMMENT 字段2注释 ],字段3 字段3类型[ COMMENT 字段3注释 ],......字段n 字段n类型[ COMMENT 字段n注释 ],)[ COMMENT 表注释 ];

删除：

-- IF EXISTS：如果存在DROP DATABASE [ IF EXISTS ] 数据库名;-- 删除字段ALTER TABLE 表名 DROP 字段名;-- 删除表DROP TABLE [IF EXISTS] 表名;-- 删除指定表，并重新创建此表TRUNCATE TABLE 表名;

使用：

USE 数据库名;

修改：

-- 添加字段ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];-- 修改数据类型ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);-- 修改字段名和字段类型ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 注释][约束];-- 修改表名ALTER TABLE 表名 RENAME TO 新表名;

4.3 数据操作语言（DML）

添加数据（INSERT）：

插入数据时，指定的字段顺序需要与值的顺序是一一对应的；字符串和日期类型数据应该包含在引号中；插入的数据大小应该在规定的范围内；

-- 给指定字段添加数据INSERT INTO 表名(字段名1,字段名2,...) VALUE(值1,值2,...);-- 给全部字段添加数据INSERT INTO 表名 VALUE (值1,值2,...);-- 批量添加数据#写法一INSERT INTO 表名 (字段1,字段2,...) VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);#写法二INSERT INTO 表名 VALUE (值1,值2,...),(值1,值2,...),...,(值1,值2,...);

修改数据（UPDATE）：

如果不设置修改表的条件，则表示修改整张表的数据；

-- 修改一个值UPDATE 表名 SET 字段名=值 [WHERE 条件];-- 修改多个值UPDATE 表名 SET 字段1=值1,字段2=值2,...,字段n=值n [WHERE 条件];

删除数据（DELETE）：

删除语句不能只删除某一个字段的值（可以利用UPDATE将值设置为 null 相当于删除了一个字段的值）；删除语句如果没有条件会删除整张表的数据；

-- 删除一条数据DELETE FROM 表名 [WHERE 条件];

4.4 数据查询语言（DQL）

DQL语法：

SELECT字段列表FROM表名列表WHERE条件列表GROUP BY分组字段查询HAVING分组后条件查询ORDER BY排序字段列表LIMIT分页参数

基础查询

-- 查询多个字段#查询指定字段SELECT 字段1,字段2,字段3... FROM 表名;#查询所有字段SELECT * FROM 表名;-- 查询后设置别名 ASSELECT 字段1[AS 别名1],字段2[AS 别名2],...,字段n[AS 别名n] FROM 表名;-- 去除重复记录 DISTINCTSELECT DISTINCT 字段列表 FROM 表名;

条件查询

WHERE 条件支持的有比较运算符和逻辑运算符；

SELECT 字段列表 FROM 表名 WHERE 条件列表;

以下是常用的条件运算符：

> > > > > > > > > >> > > >

聚合函数

聚合函数：聚合函数又叫组函数,通常是对表中的数据进行统计和计算,一般结合分组(group by)来使用,用于统计和计算分组数据；

SELECT 聚合函数(字段列表) FROM 表名；

常见的聚合函数：

> > > > > >

分组查询

分组查询就是将查询结果按照指定字段进行分组,字段中数据相等的分为一组；GRPUP BY 分组；HAVING 过滤；

SELECT 字段列表 [分组别名] FROM 表名 [WHERE 条件] GROUP BY 分组字段名[HAVING 分组后过滤条件];

WHERE和HAVING区别：

执行时机：WHERE 是分组之前进行过滤，不满足 WHERE 条件不参与分组，而 HAVING 是分组之后对结果进行过滤；

判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以；

排序查询

排序查询就是将查询的结果集按照指定的字段升序或降序显示；如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；ORDER BY 排序；ASC：升序（默认值）；DESC：降序；

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

分页查询

当要显示的数据,一页显示不全,需要分页提交 SQL 请求；起始索引从 0 开始,起始索引 = (查询页码-1) * 每页显示记录数;分页查询在不同的数据库有不同的实现，MySql 中是 LIMIT 关键字；如果查询的是第一页数据可以省略起始索引；

SELECT 字段列表 FROM 表名 LIMT 起始索引,查询记录数;

DQL 执行顺序

FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT;

4.5 数据控制语言（DCL）

管理用户

-- 用户信息是存储在mysql数据库中的，管理用户之前要先使用mysql数据库USE mysql;-- 查询user（mysql 数据库中的 user 存储的是当前数据库的所有用户）表的所有信息SELECT * FROM user;

通过查询可以看到数据库中共有如下四个用户：

四个用户的区别：

mysql.infoschema 用户：是 MySQL 数据库的系统用户，用来管理和访问系统自带实例 information_schema （信息数据库，保存着关于 MySQL 服务器所维护的所有其他数据库的信息）示例的；mysql.session 用户：插件内部使用来访问服务器，该用户已被锁定，客户端无法连接；mysql.sys 用户：用于 sys schema 中对象的定义，使用 mysql.sys 用户可避免 DBA 重命名或者删除 root 用户时发生的问题，该用户已被锁定，客户端无法连接；root 用户：用户拥有所有权限，可执行任何操作；

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

示例：

create user 'caterpillar'@'localhost' identified by '123456';

localhost表示的是当前本机，如果想让其在任意主机都能访问该数据库则将主机名写为%；

修改用户密码

-- mysql_native_password 本地密码ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

DROP USER'用户名'@'主机名';

权限控制

多个权限之间，使用逗号分隔；授权时数据库和表可以使用通配符 * 表示所有；

-- 查询权限SHOW GRANTS FOR '用户名'@'主机名';-- 授予权限GRANTS 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';-- 撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

MySql 中的常见权限> > > >

五、函数

5.1 字符串函数

常见的字符串函数> > > > > > > > > > >

示例：

-- 获取'hello'和'world'的拼接形式SELECT CONCAT('hello','world');/*输出：mysql> SELECT CONCAT('hello','world');+-------------------------+| CONCAT('hello','world') |+-------------------------+| helloworld |+-------------------------+1 row in set (0.00 sec)*/-- 将 'caterpillar' 从第2个字符开始数6个替换成 'hello'SELECT INSERT('caterpillar',2,6,'hello');/*输出：mysql> SELECT INSERT('caterpillar',2,6,'hello');+-----------------------------------+| INSERT('caterpillar',2,6,'hello') |+-----------------------------------+| chellollar |+-----------------------------------+1 row in set (0.00 sec)*/-- 获取 'HELLO'的小写形式SELECT LOWER('HELLO');/*输出：mysql> SELECT LOWER('HELLO');+----------------+| LOWER('HELLO') |+----------------+| hello|+----------------+1 row in set (0.00 sec)*/-- 获取'hello'的大写形式SELECT UPPER('hello');/*输出：mysql> SELECT UPPER('hello');+----------------+| UPPER('hello') |+----------------+| HELLO|+----------------+1 row in set (0.00 sec)*/-- 获取 'caterpillar' 的前5个字符SELECT LEFT('caterpillar',5);/*输出：mysql> SELECT LEFT('caterpillar',5);+-----------------------+| LEFT('caterpillar',5) |+-----------------------+| cater |+-----------------------+1 row in set (0.00 sec)*/-- 获取 'caterpillar' 的后6个字符SELECT RIGHT('caterpillar',6);/*输出：mysql> SELECT RIGHT('caterpillar',6);+------------------------+| RIGHT('caterpillar',6) |+------------------------+| pillar |+------------------------+1 row in set (0.00 sec)*/-- 在 'caterpillar' 前填充 'AA' 直到长度达到20SELECT LPAD('caterpillar',20,'AA');/*输出：mysql> SELECT LPAD('caterpillar',20,'AA');+-----------------------------+| LPAD('caterpillar',20,'AA') |+-----------------------------+| AAAAAAAAAcaterpillar |+-----------------------------+1 row in set (0.00 sec)*/-- 在 'caterpillar' 后填充 'ZZ' 直到长度达到20SELECT RPAD('caterpillar',20,'ZZ');/*输出：mysql> SELECT RPAD('caterpillar',20,'ZZ');+-----------------------------+| RPAD('caterpillar',20,'ZZ') |+-----------------------------+| caterpillarZZZZZZZZZ |+-----------------------------+1 row in set (0.00 sec)*/-- 去除 ' hello world ' 左边的空格SELECT LTRIM(' hello world ');/*输出：mysql> SELECT LTRIM(' hello world ');+--------------------------+| LTRIM(' hello world ') |+--------------------------+| hello world |+--------------------------+1 row in set (0.00 sec)*/-- 去除 ' hello world ' 右边的空格SELECT RTRIM(' hello world ');/*输出：mysql> SELECT RTRIM(' hello world ');+--------------------------+| RTRIM(' hello world ') |+--------------------------+| hello world |+--------------------------+1 row in set (0.00 sec)*/-- 将 'caterpillar ' 重复五次SELECT REPEAT('caterpillar ',5);/*输出：mysql> SELECT REPEAT('caterpillar ',5);+--------------------------------------------------------------+| REPEAT('caterpillar ',5) |+--------------------------------------------------------------+| caterpillar caterpillar caterpillar caterpillar caterpillar |+--------------------------------------------------------------+1 row in set (0.00 sec)*/-- 使用 'CATER' 替换 在 'caterpillar caterpillar' 出现的所有 'cater'SELECT REPLACE('caterpillar caterpillar','cater','CATER');/*输出：mysql> SELECT REPLACE('caterpillar caterpillar','cater','CATER');+----------------------------------------------------+| REPLACE('caterpillar caterpillar','cater','CATER') |+----------------------------------------------------+| CATERpillar CATERpillar |+----------------------------------------------------+1 row in set (0.00 sec)*/-- 比较 'abcc' 的 'accc' 大小SELECT STRCMP('abcc','accc');/*输出：mysql> SELECT STRCMP('abcc','accc');+-----------------------+| STRCMP('abcc','accc') |+-----------------------+|-1 |+-----------------------+1 row in set (0.00 sec)*/-- 去除 ' hello world ' 头尾的空格SELECT TRIM(' hello world ');/*输出：mysql> SELECT TRIM(' hello world ');+-------------------------+| TRIM(' hello world ') |+-------------------------+| hello world |+-------------------------+1 row in set (0.00 sec)*/-- 获取 'caterpillar' 从第6个字符开始长度为4的字符串SELECT SUBSTRING('caterpillar',6,4);/*输出：mysql> SELECT SUBSTRING('caterpillar',6,4);+------------------------------+| SUBSTRING('caterpillar',6,4) |+------------------------------+| pill|+------------------------------+1 row in set (0.00 sec)*/

5.2 数值函数

常见的数值函数> > > > > > > > >

示例：

-- 返回 -1 的绝对值SELECT ABS(-1);/*输出：mysql> SELECT ABS(-1);+---------+| ABS(-1) |+---------+| 1 |+---------+1 row in set (0.00 sec)*/-- 将1.5向上取整SELECT CEIL(1.5);/*输出：mysql> SELECT CEIL(1.5);+-----------+| CEIL(1.5) |+-----------+| 2 |+-----------+1 row in set (0.00 sec)*/-- 将1.5向下取整SELECT FLOOR(1.5);/*输出：mysql> SELECT FLOOR(1.5);+------------+| FLOOR(1.5) |+------------+|1 |+------------+1 row in set (0.00 sec)*/-- 求 32 除以 3 的余数SELECT MOD(32,3);/*输出：mysql> SELECT MOD(32,3);+-----------+| MOD(32,3) |+-----------+| 2 |+-----------+1 row in set (0.00 sec)*/-- 返回一个 0 - 1 之间的随机数SELECT RAND();/*输出：mysql> SELECT RAND();+--------------------+| RAND() |+--------------------+| 0.8252376120772563 |+--------------------+1 row in set (0.00 sec)*/-- 标记 1、2 两个 0 - 1 的随机数SELECT RAND(1),RAND(1),RAND(2),RAND(2);/*输出：mysql> SELECT RAND(1),RAND(1),RAND(2),RAND(2);+---------------------+---------------------+--------------------+--------------------+| RAND(1) | RAND(1) | RAND(2) | RAND(2) |+---------------------+---------------------+--------------------+--------------------+| 0.40540353712197724 | 0.40540353712197724 | 0.6555866465490187 | 0.6555866465490187 |+---------------------+---------------------+--------------------+--------------------+1 row in set (0.00 sec)*/-- 对 4.56 四舍五入SELECT ROUND(4.56);/*输出：mysql> SELECT ROUND(4.56);+-------------+| ROUND(4.56) |+-------------+| 5 |+-------------+1 row in set (0.00 sec)*/-- 对 4.51624 保留两位小数四舍五入SELECT ROUND(4.51624,2);/*输出：mysql> SELECT ROUND(4.51624,2);+------------------+| ROUND(4.51624,2) |+------------------+| 4.52 |+------------------+1 row in set (0.00 sec)*/-- 对 2.3456 保留3位小数SELECT TRUNCATE(2.3456,3);/*输出：mysql> SELECT TRUNCATE(2.3456,3);+--------------------+| TRUNCATE(2.3456,3) |+--------------------+| 2.345 |+--------------------+1 row in set (0.00 sec)*/-- 返回数据组 (4,2,5,1,4,2) 中的最小值SELECT LEAST(4,2,5,1,4,2);/*输出：mysql> SELECT LEAST(4,2,5,1,4,2);+--------------------+| LEAST(4,2,5,1,4,2) |+--------------------+| 1 |+--------------------+1 row in set (0.00 sec)*/-- 返回数据组 (4,2,5,1,4,2) 中的最大值SELECT GREATEST(4,2,5,1,4,2);/*输出：mysql> SELECT GREATEST(4,2,5,1,4,2);+-----------------------+| GREATEST(4,2,5,1,4,2) |+-----------------------+| 5 |+-----------------------+1 row in set (0.00 sec)*/-- 返回 -5 的正负值SELECT SIGN(-5);/*输出：mysql> SELECT SIGN(-5);+----------+| SIGN(-5) |+----------+| -1 |+----------+1 row in set (0.00 sec)*/-- 返回 9 的平方根SELECT SQRT(9);/*输出：mysql> SELECT SQRT(9);+---------+| SQRT(9) |+---------+| 3 |+---------+1 row in set (0.00 sec)*/

5.3 日期函数

常见的日期函数> > > > > > > >

示例：

-- 返回当前的日期和时间组合、当前日期、当前时间SELECT NOW(),CURDATE(),CURTIME();/*输出：mysql> SELECT NOW(),CURDATE(),CURTIME();+---------------------+------------+-----------+| NOW()| CURDATE() | CURTIME() |+---------------------+------------+-----------+| -11-28 14:21:10 | -11-28 | 14:21:10 |+---------------------+------------+-----------+1 row in set (0.00 sec)*/-- 返回当前日期时间表达式的日期部分SELECT DATE(NOW());/*输出：mysql> SELECT DATE(NOW());+-------------+| DATE(NOW()) |+-------------+| -11-28 |+-------------+1 row in set (0.00 sec)*/-- 返回当前时间的年、月、日、时、分、秒；SELECTEXTRACT(YEAR FROM NOW()) AS '年',EXTRACT(MONTH FROM NOW()) AS '月',EXTRACT(DAY FROM NOW()) AS '天',EXTRACT(HOUR FROM NOW()) AS '时',EXTRACT(MINUTE FROM NOW()) AS '分',EXTRACT(SECOND FROM NOW()) AS '秒';/*输出：mysql> SELECT->EXTRACT(YEAR FROM NOW()) AS '年',->EXTRACT(MONTH FROM NOW()) AS '月',->EXTRACT(DAY FROM NOW()) AS '天',->EXTRACT(HOUR FROM NOW()) AS '时',->EXTRACT(MINUTE FROM NOW()) AS '分',->EXTRACT(SECOND FROM NOW()) AS '秒';+------+------+------+------+------+------+| 年 | 月 | 天 | 时 | 分 | 秒 |+------+------+------+------+------+------+| | 11 | 28 | 14 | 29 | 10 |+------+------+------+------+------+------+1 row in set (0.00 sec)*/-- 一个月后的当前时间SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后';/*输出：SELECT DATE_ADD(NOW(),INTERVAL 1 MONTH ) AS '一月后';+---------------------+| 一月后 |+---------------------+| -12-28 14:30:57 |+---------------------+1 row in set (0.00 sec)*/-- 十年前的今天SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前';/*输出：mysql> SELECT DATE_SUB(NOW(),INTERVAL 10 YEAR ) AS '十年前';+---------------------+| 十年前 |+---------------------+| -11-28 14:33:32 |+---------------------+1 row in set (0.00 sec)*/-- '-10-20' 减去 '-01-05' 的天数SELECT DATEDIFF('-10-20','-01-05');/*输出：mysql> SELECT DATEDIFF('-10-20','-01-05');+-------------------------------------+| DATEDIFF('-10-20','-01-05') |+-------------------------------------+| -3364 |+-------------------------------------+1 row in set (0.00 sec)*/-- 根据 '%Y %c %a %D' 的格式输出当前时间SELECT DATE_FORMAT(NOW(),'%Y %c %a %D');/*输出：mysql> SELECT DATE_FORMAT(NOW(),'%Y %c %a %D');+----------------------------------+| DATE_FORMAT(NOW(),'%Y %c %a %D') |+----------------------------------+| 11 Mon 28th |+----------------------------------+1 row in set (0.00 sec)*/-- 获取当前时间的年月日SELECTYEAR(NOW()) AS '年',MONTH(NOW()) AS '月',DAY(NOW()) AS '日';/*输出：mysql> SELECT->YEAR(NOW()) AS '年',->MONTH(NOW()) AS '月',->DAY(NOW()) AS '日';+------+------+------+| 年 | 月 | 日 |+------+------+------+| | 11 | 28 |+------+------+------+1 row in set (0.00 sec)*/

MySql中的日期格式

5.4 流程控制函数

三种流程控制函数

示例：

-- 如果 4 > 5 成立返回 'A' 否则返回 'B'SELECT IF(4>5,'A','B');/*输出：mysql> SELECT IF(4>5,'A','B');+-----------------+| IF(4>5,'A','B') |+-----------------+| B|+-----------------+1 row in set (0.00 sec)*/-- 因为首项为空，所有返回 'caterpillar'SELECT IFNULL(NULL,'caterpillar');/*输出：mysql> SELECT IFNULL(NULL,'caterpillar');+----------------------------+| IFNULL(NULL,'caterpillar') |+----------------------------+| caterpillar|+----------------------------+1 row in set (0.00 sec)*/-- 表名设为 list ，如果 50>55 表数据为 'A'，否则如果 40>45 表数据为 'B'，否则表数据为 'C'SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list';/*输出：mysql> SELECT CASE WHEN 50>55 THEN 'A' WHEN 40>45 THEN 'B' ELSE 'C' END 'list';+------+| list |+------+| C |+------+1 row in set (0.00 sec)*/-- 表名设为 list ，如果有跟 'caterpillar' 相同的条件则表数据为第一个为 'caterpillar' 条件所跟随的值SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list';/*输出：mysql> SELECT CASE 'caterpillar' WHEN 'hello' THEN 'A' WHEN 'caterpillar' THEN 'B' END 'list';+------+| list |+------+| B |+------+1 row in set (0.00 sec)*/

六、约束

6.1 概述

概念：约束是作用于表中的规则，用于限制存储在表中的数据，约束是作用于表中字段上的，在创建和修改表的时候可以添加约束；

常见的约束

目的：保证数据库中的数据的正确性、有效性和完整性；

6.2 约束演示

基础约束

示例：

/*要求：1. 建立一个表 tb_personnel；2. 表内数据有(id, name, age, srarus, opender);3. id 要求为主键且作自增；4. name 要求不为空且不能重复；5. age 要求大于 0 小于 120；6. srarus 要求默认为 '1'；*/CREATE TABLE tb_personnel(id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',srarus CHAR(1) DEFAULT 1 COMMENT '状态',opender CHAR(1) COMMENT '性别') COMMENT '人员表';

外键约束

语法：

-- 建表时添加外键CREATE TABLE 表名(字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名));-- 建表后添加外键ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);-- 删除外键ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

示例：

-- 建立外键表CREATE TABLE tb_srarus(id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',srarus varchar(10) NOT NULL UNIQUE COMMENT '状态');-- 建表时添加外键CREATE TABLE tb_personnel(id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'ID',name VARCHAR(20) NOT NULL UNIQUE COMMENT '姓名',age INT CHECK ( 120 > age && age > 0 ) COMMENT '年龄',srarus INT DEFAULT 1 COMMENT '状态',opender CHAR(1) COMMENT '性别',CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id)) COMMENT '人员表';-- 建表后添加外键ALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id);-- 删除外键ALTER TABLE tb_personnel DROP FOREIGN KEY additional;

外键的删除和更新

删除更新行为

注：RESTRICT与 NO ACTION 是默认行为

语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE 约束名;

示例：

-- 指定约束为 SET NULLALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE SET NULL ;-- 指定约束为 CASCADEALTER TABLE tb_personnel ADD CONSTRAINT additional FOREIGN KEY (srarus) REFERENCES tb_srarus(id) ON UPDATE CASCADE ON DELETE CASCADE ;

七、多表查询

7.1 概述

项目开发中在进行数据库表与表之间的设计时有以下三种联系：

一对一联系

多用于多表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中；在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的；

一对多联系

在多的一方建表，指向一的一方的主键；

多对多联系

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键；

多表查询分类：

连接查询

内连接：相当于查询A、B的交集部分；外连接：查询左/右表所有数据，以及两张表交集部分数据；自连接：当前表与自身的连接查询，自连接必须使用表别名；

子查询

即嵌套查询；

笛卡尔积的概念：

笛卡尔积运算 设a,b为集合，用a中元素为第一元素，b中元素为第二元素构成有序对，所有这样的有序对组成的集合叫做a与b的笛卡尔积；

表信息：

)

笛卡尔积示例：

select * from tb_staff,tb_branch;

结果图：

)

7.2 连接查询

内连接：

隐式内连接

SELECT 字段列表 FROM 表1,表2 WHERE 条件...;

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

示例：

-- 建立一张员工表、一张部门表create table tb_staff(> sid int auto_increment comment '员工id' primary key,sname varchar(20) comment '员工姓名',> branch int comment '部门号')comment '员工表';create table tb_branch(bid int auto_increment comment '部门号' primary key ,> bname varchar(10) unique comment '部门名称',manager varchar(10) comment '部门经理'>)comment '部门表';-- 添加外键约束alter table tb_staff add constraint branch_key foreign key (branch)references tb_branch(bid);-- 在两张表中输入测试样例insert into tb_branch (bname, manager) VALUE ('人事部','毛毛虫'),('安保部','张三丰'),('技术部','金庸'),('李世民',null);insert into tb_staff (sname, branch) VALUE ('caterpillar',1),('张无忌',3),('小龙女',2),('杨过',2),(null,null);-- 隐式内连接方式selecttb_staff.sname as '员工姓名',tb_branch.bname as '部门',tb_branch.manager as '部门经理'fromtb_staff,tb_branchwheretb_staff.branch = tb_branch.bid;-- 显式内连接方式selecttb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'fromtb_staff inner join tb_branchontb_staff.branch = tb_branch.bid;

查询结果：

)

外连接：

左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON条件...;

右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

示例：

-- 左外连接selecttb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'fromtb_staff left outer join tb_branchontb_staff.branch = tb_branch.bid;-- 右外连接selecttb_staff.sname as '员工姓名',tb_branch.bid as '部门id',tb_branch.bname as '部门',tb_branch.manager as '部门经理'fromtb_staff right outer join tb_branchontb_staff.branch = tb_branch.bid;

结果：

)

自连接：

自连接可以是内连接也可以是外连接；

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

示例：

-- 建立员工丛属表create table tb_firm(fid int auto_increment comment '员工id' primary key,fname varchar(20) not null comment '姓名',b_mid int comment '直系领导')comment '员工从属表';-- 插入测试数据insert intotb_firm (fname, b_mid)values('张三丰',null),('张无忌',1),('小龙女',2),('韦一笑',2),('杨过',3),('老顽童',3),('天山童姥',3),('赵无极',4),('韦小宝',4);-- 使用自连接投影出每个员工的直系上司select f1.fname as '员工名称',f2.fname as '直系上司' from tb_firm as f1 left outer join tb_firm as f2 on f1.b_mid = f2.fid;

结果：

联合查询：

对于联合查询（union），就是把多次查询的结果合并，形成一个新的结果集；联合查询的数张表必须保持一致，字段类型也需要保持一致；union all 会将所有数据合并在一起，union 会对合并之后的数据去重；

语法：

SELECT 字段列表 FROM 表A ...UNION [ALL]SELECT 字段列表 FROM 表B ...;

示例：

-- 建立一张学生表create table tb_stduent(sid int auto_increment comment '学生id' primary key,sname varchar(20) comment '姓名',class varchar(10) comment '学生班级',sage int check ( 0 < sage and sage < 120 ) comment '年龄')comment '学生表';-- 插入测试数据insert intotb_stduent (sname, class, sage)values('张三丰','3班',18),('张无忌','3班',16),('杨过','1班',17),('韦小宝','2班',18),('金台','3班',18),('李存孝','1班',18),('李元霸','2班',18),('高宠','3班',18);-- 通过联合查询将姓张的学生和在1班的学生投影出来select * from tb_stduent where sname like '张%'union allselect * from tb_stduent where class = '1班';

结果：

7.3 子查询

7.3.1 基本知识

概念：

sql 语句当中嵌套 select 语句，称为嵌套查询，又称子查询；

语法：

SELECT * t1 WHERE column1 = (SELECT column1 FROM t2);

根据子查询的结果可分为：

标量子查询列子子查询行子子查询表子查询

子查询的位置可以是：

WHERE 之后；FROM 之后；SELECT 之后；

7.3.2 标量子查询

概念：

标量子查询返回的结果是单个值（数字、字符串、日期等）；常用的操作符：=、<>、>、>=、<、<=；

有如下一张表：

示例：

-- 生成一张3班的学生表，查询表中姓名为高宠的学生的年龄select sage from (select * from tb_stduent where class = '3班') as list where sname = '高宠';

结果：

7.3.3 列子查询

概念：

列子查询返回的结果是一列（可以是多行）；常用的操作符：IN、NOT IN、ANY、SOME、ALL

有如下一张表：

示例：

-- 查询不和高宠在一个班的学生姓名select sname from tb_stduent where class not in (select class from tb_stduent where sname = '高宠');-- 查询年龄小于1班所有学生的学生姓名select sname from tb_stduent where sage < all (select sage from tb_stduent where class = '1班');-- 查询年龄小于1班任意一名学生的学生姓名select sname from tb_stduent where sage < some (select sage from tb_stduent where class = '1班');

结果：

7.3.4 行子查询

概念：

行子查询返回的结果是一行（可以是多列）；常用的操作符：=、<>、IN、NOT IN；

有如下一张表：

示例：

-- 查询和张三丰同属一个班级且年龄相同的学生信息select * from tb_stduent where (class,sage) = (select class,sage from tb_stduent where sname = '张三丰');

结果：

7.3.5 表子查询

概念：

表子查询返回的结果是多行多列；常用操作符：IN

有如下一张表：

示例：

-- 生成一张班级为3班的学生表，再从生成的表中查询姓张的学生信息select * from (select * from tb_stduent where class = '3班') as list where sname like '张%';

结果：

八、事务

8.1 简介

概念

事物是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，也就是说要么同时成功，要么同时失败；

事务的四大特性

原子性：事务是不可再分割的最小操作单元，要么全部成功，要么全部失败；一致性：事务完成时，必须使所有的数据都保持一致状态；隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境；持久性：事务一旦提交或回滚对数据的改变是永久的；

（注：默认 MySql 的事务是自动提交的，也就是说，当执行一条 DML 语句，MySql 会立即隐式的提交事务）

8.2 事务操作

方法一：通过设置事务的提交方式

语法：

-- 查看事务SELECT @@autocommit;-- @@autocommit为1时是自动提交，为0是手动提交，默认是1SET @@autocommit = 0;-- 提交事务COMMIT;-- 回滚事务ROLLBACK;

现有下表：

-- 建立一张银行用户表create table tb_bank_user(id int auto_increment comment '用户id' primary key,name varchar(20) comment '姓名',money double check ( money >= 0 ) comment '余额')comment '银行用户表';-- 插入测试数据insert into tb_bank_user (name, money) values ( '赵无极',2000.00),('高宠',1000.00),('杨坚',0.00),('李世民',500.00);-- 将事务提交方式更改为手动set @@autocommit = 0;

示例1：

-- 执行完转账操作之后使用commit提交update tb_bank_user set money = money - 1000 where name = '赵无极';update tb_bank_user set money = money + 1000 where name = '高宠';commit;

示例2：

-- 执行转帐的过程中出错使用rollback回滚update tb_bank_user set money = money - 1000 where name = '杨坚';update tb_bank_user set money = money + 1000 where name = '高宠';rollback;

方法二：

语法：

-- 开启事务START TRANSACTION 或 BEGIN;-- 提交事务COMMIT;-- 回滚事务ROLLBACK;

这种方法跟方法一操作一致，区别在于一旦使用了提交或回滚就表示事务已经结束不需要再手动更改事务提交规则；

并发事务

并发事务问题：

脏读：一个事务读到另外一个事务还未提交的数据；不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读；幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了；

事务隔离级别（安全性从低到高）：

未提交读（Read uncommitted）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而 且能看到其他事务没有提交的对已有记录的更新；已提交读（Read committed）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新；可重复读/默认（Repeatable read）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新；可串行化（Serializable）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新。（事务执行的时候不允许别的事务并发执行。事务串行化执行，事务只能一个接着一个地执行，而不能并发执行）；

（注：事务隔离级别越高，运行越慢）

语法：

-- 查看事务隔离级别SELECT @@TRANSACTION_ISOLATION;-- 设置事务隔离级别SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}