MySql基础学习

1 数据库简介

1.1 简介

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种
用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，
用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户
通过数据库管理系统访问数据库中的数据。
数据库软件应该为数据库管理系统，数据库是通过数据库管理系统创建和操作的。
数据库：存储、维护和管理数据的集合。

1.2 常见数据库

1.2.1 排名

目前互联网上常见的数据库管理软件有Oracle、MySQL、MS SQL Server、DB2、PostgreSQL、Access、Sybase、Informix这几种。以下是2021年DB-Engines Ranking 对各数据库受欢迎程度进行调查后的统计结果：（查看数据库最新排名: https://db-engines.com/en/ranking）

1.2.2 常见数据库系统介绍

Oracle

• 1979 年，Oracle 2 诞生，它是第一个商用的 RDBMS（关系型数据库管理系统）。随着 Oracle 软件的名气越来越大，公司也改名叫 Oracle 公司
• 2007年，总计85亿美金收购BEA Systems。
• 2009年，总计74亿美金收购SUN。此前的2008年，SUN以10亿美金收购MySQL。意味着Oracle同时拥有了MySQL 的管理权，至此 Oracle 在数据库领域中成为绝对的领导者。
• 2013年，甲骨文超越IBM，成为继Microsoft后全球第二大软件公司。
• 如今 Oracle 的年收入达到了 400 亿美金，足以证明商用（收费）数据库软件的价值。

SQL Server

• SQL Server 是微软开发的大型商业数据库，诞生于 1989 年。C#、.net等语言常使用，与WinNT完全集成，也可以很好地与Microsoft BackOffice产品集成。

DB2

• IBM公司的数据库产品,收费的。常应用在银行系统中。

PostgreSQL

• PostgreSQL 的稳定性极强，最符合SQL标准，开放源码，具备商业级DBMS质量。PG对数据量大的文本以及SQL处理较快

SQLite

• 嵌入式的小型数据库，应用在手机端。 零配置，SQlite3不用安装，不用配置，不用启动，关闭或者配置数据库实例。当系统崩溃后不用做任何恢复操作，再下次使用数据库的时候自动恢复。

informix

• IBM公司出品，取自Information 和Unix的结合，它是第一个被移植到Linux上的商业数据库产品。
  仅运行于unix/linux平台，命令行操作。 性能较高，支持集群，适应于安全性要求极高的系统，尤其是银行，证券系统的应用

1.3 MySQL介绍

1.3.1 概述

• MySQL是一个 开放源代码的关系型数据库管理系统 ，由瑞典MySQL
  AB（创始人MichaelWidenius）公司1995年开发，迅速成为开源数据库的 No.1。
• 2008被 Sun 收购（10亿美金），2009年Sun被 Oracle 收购。 MariaDB 应运而生。（MySQL 的创造者担心
  MySQL 有闭源的风险，因此创建了 MySQL 的分支项目 MariaDB）
• MySQL6.x 版本之后分为 社区版 和 商业版 。
• MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
• MySQL是开源的，所以你不需要支付额外的费用。
• MySQL是可以定制的，采用了 GPL（GNU General Public License）
  协议，你可以修改源码来开发自己的MySQL系统。
• MySQL支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持 4GB ，64位系统支持最大的表文件为 8TB 。
• MySQL使用 标准的SQL数据语言 形式。
• MySQL可以允许运行于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP和Ruby等。

1.3.2 MySQL的版本

• MySQL Community Server 社区版本，开源免费，自由下载，但不提供官方技术支持，适用于大多数普通用户。
• MySQL Enterprise Edition
  企业版本，需付费，不能在线下载，可以试用30天。提供了更多的功能和更完备的技术支持，更适合于对数据库的功能和可靠性要求较高的企业客户。
• MySQL Cluster 集群版，开源免费。用于架设集群服务器，可将几个MySQL Server封装成一个Server。需要在社区版或企业版的基础上使用。
• MySQL Cluster CGE 高级集群版，需付费。

1.3.2 关于MySQL8.0

MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。

• 开放源代码，使用成本低。
• 性能卓越，服务稳定。
• 软件体积小，使用简单，并且易于维护。
• 历史悠久，社区用户非常活跃，遇到问题可以寻求帮助。
• 许多互联网公司在用，经过了时间的验证。

1.3.3 Oracle vs MySQL

• Oracle 更适合大型跨国企业的使用，因为他们对费用不敏感，但是对性能要求以及安全性有更高的要求。
• MySQL由于其体积小、速度快、总体拥有成本低，可处理上千万条记录的大型数据库，尤其是开放源码这一特点，使得很多互联网公司、中小型网站选择了MySQL作为网站数据库（Facebook，Twitter，YouTube，阿里巴巴/蚂蚁金服，去哪儿，美团外卖，腾讯）。

1.4 关系型数据库基本概念

一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性。表具有一些特性，这些特性定义了数据在表中如何存储，类似Java和Python中 “类”的设计

1.4.1 表、记录、字段

• E-R（entity-relationship，实体-联系）模型中有三个主要概念是： 实体集 、 属性 、 联系集 。
• 一个实体集（class）对应于数据库中的一个表（table），一个实体（instance）则对应于数据库表中的一行（row），也称为一条记录（record）。一个属性（attribute）对应于数据库表中的一列（column），也称为一个字段（field）。
  

1.4.2 表的关联关系

• 表与表之间的数据记录有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。
• 四种：一对一关联、一对多关联、多对多关联

1.4.2.1 一对一关联（one-to-one）

1 举例：设计 学生表 ：学号、姓名、手机号码、班级、系别、身份证号码、家庭住址、籍贯、紧急联系人、…

• 拆为两个表：两个表的记录是一一对应关系。
• 基础信息表 （常用信息）：学号、姓名、手机号码、班级、系别
• 档案信息表 （不常用信息）：学号、身份证号码、家庭住址、籍贯、紧急联系人、…

2 两种建表原则：

• 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一。
• 外键是主键：主表的主键和从表的主键，形成主外键关系。
  

1.4.2.2 一对多关系（one-to-many）

• 常见实例场景： 客户表和订单表 ， 分类表和商品表 ， 部门表和员工表 。

• 举例：

  • 员工表：编号、姓名、…、所属部门

  • 部门表：编号、名称、简介

• 一对多建表原则：在从表(多方)创建一个字段，字段作为外键指向主表(一方)的主键
  

1.4.2.3 多对多（many-to-many）

2 MySQL8 环境搭建

2.1 安装

2.1.1 下载压缩包

访问地址:https://dev.mysql.com/downloads/mysql/
下载压缩包
for window

for mac

2.1.2 配置环境变量

下载后解压，放在非C盘下，文件夹改名mysql
将解压文件夹下的bin路径添加到变量值中，前后以 ; 开头结尾

2.1.3 MySQL配置文件

在mysql文件夹下找到my.ini或my-default.ini，如果没有.ini结尾的文件,直接创建该文件。新增内容为如下，注意basedir和datadir是我自己的路径位置，自定义。记得新增一个文件Data文件夹

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=d:\java\mysql-8.0.22-winx64
# 设置mysql数据库的数据的存放目录
datadir=d:\java\mysql-8.0.22-winx64\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

2.1.4 . 安装mysql

在mysql的安装目录中，打开bin文件夹，运行cmd.执行初始化数据库的指令：

mysqld --initialize --console

root用户的初始化密码:

注意：要是你不小心关掉cmd，或者没记住，那也没事，删掉初始化的 datadir 目录，再执行一遍初始化命令，又会重新生成的。

2.1.5 安装服务

在MySQL安装目录的 bin 目录下执行命令：
mysqld --install [服务名] 这里的服务名默认是mysql，可以自定义

mysqld --install mysql8

如果提示上述错误，需要关闭cmd,重新打开，使用管理员身份执行

安装完成之后
通过命令net start mysql8启动MySQL的服务了。
通过命令net stop mysql8停止服务。

注意:安装时，卸载其他版本的mysql数据库

2.1.6 连接数据库：

格式：`mysql -h 主机名 -P 端口号 -u 用户名 -p密码
实战： mysql -u root -p

修改账户密码：

mysql -u root -p
alter user 'root'@'localhost' identified with mysql_native_password BY '新密
码';

例：

alter user 'root'@'localhost' identified with mysql_native_password BY
'123456';

修改密码，注意命令尾的分号一定要有，这是mysql的语法
退出数据库：

quit
或者
exit

2.1.7 使用演示

c:\> mysql -V
c:\> mysql --version

或

c:\> mysql -V
c:\> mysql --version

查看所有数据库

mysql> show databases;

2.2 卸载

2.2.1 使用管理员身份运行cmd,关闭mysql服务

net stop mysql8

2.2.2 删除mysql服务

mysqld remove mysql8
#或者
sc delete mysql8

2.2.3 刪除mysqlDB目录文件（ 安裝mysql时my.ini指定的目录）

2.3 MySQL图形化管理工具

2.3.1 Navicat

Navicat MySQL是一个强大的MySQL数据库服务器管理和开发工具。它可以与任何3.21或以上版本的MySQL一起工作，支持触发器、存储过程、函数、事件、视图、管理用户等，对于新手来说易学易用。其精心设计的图形用户界面（GUI）可以让用户用一种安全简便的方式来快速方便地创建、组织、访问和共享信息。Navicat支持中文，有免费版本提供。 下载地址：http://www.navicat.com/

https://www.cnblogs.com/kkdaj/p/16260681.html 也可以下载正版，然后破解

2.3.2 SQLyog

SQLyog 是业界著名的 Webyog 公司出品的一款简洁高效、功能强大的图形化 MySQL 数据库管理工具。这款工具是使用C++语言开发的。该工具可以方便地创建数据库、表、视图和索引等，还可以方便地进行插入、更新和删除等操作，同时可以方便地进行数据库、数据表的备份和还原。该工具不仅可以通过SQL文件进行大量文件的导入和导出，还可以导入和导出XML、HTML和CSV等多种格式的数据。 下载地址：http://www.webyog.com/，读者也可以搜索中文版的下载地址。
![在这里插入图片描述](https://img-blog.csdnimg.cn/6528daf2214c49bda3d25f2a534cc05c.png

3 SQL语言

3.1 概述

SQL：Structure Query Language（结构化查询语言），SQL被美国国家标准局（ANSI）确定为关系型
数据库语言的美国标准，后来被国际化标准组织（ISO）采纳为关系数据库语言的国际标准。
各数据库厂商都支持ISO的SQL标准，普通话
各数据库厂商在标准的基础上做了自己的扩展，方言

SQL 是一种标准化的语言，它允许你在数据库上执行操作，如创建项目，查询内容，更新内容，并删除条目等操作。
Create, Read, Update, and Delete 通常称为CRUD操作。

3.2 SQL语句分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等。
  • 主要的语句关键字包括 CREATE 、 DROP 、 ALTER 等。
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）增删改。
  • 主要的语句关键字包括 INSERT 、 DELETE 、 UPDATE 、 SELECT 等
  • SELECT是SQL语言的基础，最为重要
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。
  • 主要的语句关键字包括 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）查询。
  • 因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
• TCL （Transaction Control Language，事务控制语言）
  • 单独将 COMMIT 、 ROLLBACK 取出来称为TCL

3. 3 SQL语言的规则与规范

• SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
• 每条命令以 ; 或 \g 或 \G 结束
• 关键字不能被缩写也不能分行
• 关于标点符号
  • 必须保证所有的()、单引号、双引号是成对结束的
  • 必须使用英文状态下的半角输入方式
  • 字符串型和日期时间类型的数据可以使用单引号（’ '）表示
  • 列的别名，尽量使用双引号（" "），而且不建议省略as
• MySQL 在 Windows 环境下是大小写不敏感的
• MySQL 在 Linux 环境下是大小写敏感的
• 数据库名、表名、表的别名、变量名是严格区分大小写的
• 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
• 推荐采用统一的书写规范：
  • 数据库名、表名、表别名、字段名、字段别名等都小写
  • SQL 关键字、函数名、绑定变量等都大写

system cls #清屏
use mysql;
select * from user \g
select * from user;
select * from user \G

3.4 注释

单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

3.5 命名规则

数据命名规范，可以参考阿里开发规约(嵩山版)，基本规则如下：

• 数据库、表名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 数据库名、表名、字段名等对象名中间不要包含空格
• 同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重号）引起来
• 保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性。假如数据类型在一个表里是整数，那在另一个表里可就别变成字符型

#以下两句是一样的，不区分大小写
show databases;
SHOW DATABASES;
#创建表格
#create table student info(...); #表名错误，因为表名有空格
create table student_info(...);
#其中order使用``飘号，因为order和系统关键字或系统函数名等预定义标识符重名了
CREATE TABLE `order`();
select id as "编号", `name` as "姓名" from t_stu; #起别名时，as都可以省略
select id as 编号, `name` as 姓名 from t_stu; #如果字段别名中没有空格，那么可以省
略""
select id as 编 号, `name` as 姓 名 from t_stu; #错误，如果字段别名中有空格，那么不
能省略""

3.6 数据导入命令

mysql> source d:\mysqldb.sql

4 基本的select语句

4.1 select … from

语法：

SELECT 标识选择哪些列
FROM 标识从哪个表中选择

不带from子句：

select 1;
select 9/2;
select 1 from dual;
select null = null; # null，所以规定使用is null判断Null
SELECT * FROM employees WHERE manager_id IS NULL;

选择全部列：

select * from departments;

选择特定的列：

SELECT department_id, location_id FROM departments;

MySQL中的SQL语句是不区分大小写的，因此SELECT和select的作用是相同的，但是，许多开发人员习惯将关键字大写、数据列和表名小写，读者也应该养成一个良好的编程习惯，这样写出来的代码更容易阅读和维护。

4.2 列的别名

• 重命名一个列
• 便于计算
• 紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特
• 殊的字符并区分大小写。
• AS 可以省略
• 建议别名简短，见名知意

SELECT last_name AS name, commission_pct comm FROM employees;

SELECT last_name "name", salary*12 "annual_salary" FROM employees;

4.3 去除重复行

默认情况下，查询会返回全部行，包括重复行。

SELECT department_id FROM employees;

在SELECT语句中使用关键字DISTINCT去除重复行

SELECT DISTINCT department_id FROM employees;

多列去重

SELECT DISTINCT department_id,salary FROM employees;

**注意：

• DISTINCT 需要放到所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROM
  employees 会报错。
• DISTINCT 其实是对后面所有列名的组合进行去重，你能看到最后的结果是 74 条，因为这 74 个部门id不同，都有 salary
  这个属性值。如果你想要看都有哪些不同的部门（department_id），只需要写 DISTINCT department_id
  即可，后面不需要再加其他的列名了。**

4.4 空值参与运算

所有运算符或列值遇到null值，运算的结果都为null

select null + 1; # null
select null >=1; # null;
select null = nul; #null
SELECT commission_pct FROM employees WHERE commission_pct = NULL; #错误
SELECT commission_pct FROM employees WHERE commission_pct is NULL; #
SELECT employee_id,salary,commission_pct, 12 * salary * (1 + commission_pct)
"annual_sal" FROM employees;

这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。

4.5 着重号

mysql> SELECT * FROM `ORDER`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM `order`;
+----------+------------+
| order_id | order_name |
+----------+------------+
| 1 | shkstart |
| 2 | tomcat |
| 3 | dubbo |
+----------+------------+
3 rows in set (0.00 sec)

我们需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在SQL语句中使用一对"``"（着重号）引起来。

4.6 查询常数

SELECT 查询还可以对常数进行查询。对的，就是在 SELECT 查询结果中增加一列固定的常数列。这列的取值是我们指定的，而不是从数据表中动态取出的。
你可能会问为什么我们还要对常数进行查询呢？
SQL 中的 SELECT 语法的确提供了这个功能，一般来说我们只从一个表中查询数据，通常不需要增加一个固定的常数列，但如果我们想整合不同的数据源，用常数列作为这个表的标记，就需要查询常数。
比如说，我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段 corporation ，这个字段固定值为“日知录”，可以这样写：

SELECT '日知录' as corporation, last_name FROM employees;

4.7 过滤条件

语法：

SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件

例：

SELECT employee_id, last_name, job_id, department_id FROM employees WHERE
department_id = 90;

4.8 显示表结构

DESCRIBE employees;
或 DESC employees;

例：

mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

其中，各个字段的含义分别解释如下：

• Field：表示字段名称。
• Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
• Null：表示该列是否可以存储NULL值。
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的
• 一部分；MUL表示在列中某个给定值允许出现多次。
• Default：表示该列是否有默认值，如果有，那么值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。

5 运算符

5.1 算数运算符

例:

# 查询1+1
select 1+1;
# 查询1-1
select 1-1;
#查询工资乘以医保比率
SELECT salary * commission_pct FROM employees
#筛选出employee_id是偶数的员工
SELECT * FROM employees WHERE employee_id MOD 2 = 0;

5.2 比较运算符

比较运算符用来对表达式左边的操作数和右边的操作数进行比较，比较的结果为真则返回1，比较的结果为假则返回0，其他情况则返回NULL。
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。

#工资大于10000的员工
SELECT * FROM employees WHERE salary > 10000
#查询department_id是90和60的员工
SELECT * FROM employees WHERE department_id IN (90, 60);
#模糊查询
SELECT * FROM employees WHERE first_name LIKE '%ex%' #是否区分大小写，看操作系统或
者字符集
SELECT * FROM employees WHERE first_name like binary '%ex%' # 区分大小写

5.3 逻辑运算法

逻辑运算符主要用来判断表达式的真假，在MySQL中，逻辑运算符的返回结果为1、0或者NULL。MySQL中支持4种逻辑运算符如下：

SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary
>=10000 AND job_id LIKE %MAN%';
#查询基本薪资不在9000-12000之间的员工编号和基本薪资
SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND
salary <= 12000);
SELECT employee_id,salary FROM employees WHERE salary <9000 OR salary >
12000;
SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND
12000;
#正则运算符
SELECT * FROM employees WHERE first_name REGEXP '^A';

5.4 位运算

位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
MySQL支持的位运算符如下：

mysql> SELECT 1 & 10, 20 & 30;
+--------+---------+
| 1 & 10 | 20 & 30 |
+--------+---------+
| 0 | 20 |
+--------+---------+
1 row in set (0.00 sec)

6 排序与分页

6.1 排序数据

使用 ORDER BY 子句排序

• ASC（ascend）: 升序
• DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾。

SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY
hire_date ;
SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY
hire_date DESC ;
SELECT employee_id, last_name, salary*12 annsal FROM employees ORDER BY
annsal;
#多列排序
SELECT last_name, department_id, salary FROM employees ORDER BY
department_id, salary DESC;

6.2 分页

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？
MySQL中使用 LIMIT 实现分页

LIMIT [位置偏移量,] 行数

例：

--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;

分页显式公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

7 多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

7.1 笛卡尔积（交叉连接）

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

#查询员工姓名和所在部门名称
SELECT last_name,department_name FROM employees,departments;
SELECT last_name,department_name FROM employees CROSS JOIN departments;
SELECT last_name,department_name FROM employees INNER JOIN departments;
SELECT last_name,department_name FROM employees JOIN departments;

为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;

7.2 多表关联的分类

7.2.1 等值连接和非等值连接

1.等值链接

SELECT employees.employee_id, employees.last_name,
employees.department_id, departments.department_id,
departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

2.非等值链接

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

7.2.2 自连接和非自连接

1.自连接

SELECT CONCAT(worker.last_name ,' works for '
, manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id ;

2.非自连接

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e , departments d
WHERE e.department_id = d.department_id;

7.2.3 内连接和外连接

除了查询满足条件的记录以外，外连接还可以查询某一方不满足条件的记录。

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
• 如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表
  
  SQL92标准对于外连接的处理：
• 在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。
• Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接
• 而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

7.3 SQL99语法实现多表查询

SELECT table1.column, table2.column,table3.column
FROM table1
JOIN table2 ON table1 和 table2 的连接条件
JOIN table3 ON table2 和 table3 的连接条件

• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
• ON 子句使语句具有更高的易读性。
• 关键字 JOIN、INNER JOIN、CROSS JOIN 的含义是一样的，都表示内连接

7.3.1 内连接(INNER JOIN)

SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

例1：

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

例2：

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

7.3.2 左外连接(LEFT OUTER JOIN)

#实现查询结果是A
SELECT 字段列表
FROM A表 LEFT JOIN B表
ON 关联条件
WHERE 等其他子句;

例：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

7.3.3 右外连接(RIGHT OUTER JOIN)

SELECT 字段列表
FROM A表 RIGHT JOIN B表
ON 关联条件
WHERE 等其他子句;

例：

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

7.3.4 满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

7.4 UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

例：

#方式1
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#方式2
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;

7.5 7种SQL JOINS的实现

#中图：内连接 A∩B
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右上图：右外连接
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#左中图：A - A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
#右中图：B-A∩B
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL
#左下图：满外连接
# 左中图 + 右上图 A∪B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL #没有去重操作，效率高
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`;
#右下图
#左中图 + 右中图 A ∪B- A∩B 或者 (A - A∩B) ∪ （B - A∩B）
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL

8 单行函数

8.1 函数的理解

8.1.1 什么是函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率 。

从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。

8.1.2 不同DBMS函数的差异

我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。

8.1.3 MySQL的内置函数及分类

MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。

8.2 字符串函数

mysql> SELECT
FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM
DUAL;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
| 0 | 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL | mysql |
+-------------------------+---------------------+
1 row in set (0.00 sec)

8.3 日期和时间函数

8.3.1 获取日期，时间

SELECT
CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_
TIME()+0
FROM DUAL;

8.3.2 日期与时间戳的转换

mysql> SELECT UNIX_TIMESTAMP(now());
+-----------------------+
| UNIX_TIMESTAMP(now()) |
+-----------------------+
| 1576380910 |
+-----------------------+
1 row in set (0.01 sec)
mysql> SELECT UNIX_TIMESTAMP(CURDATE());
+---------------------------+
| UNIX_TIMESTAMP(CURDATE()) |
+---------------------------+
| 1576339200 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP(CURTIME());
+---------------------------+
| UNIX_TIMESTAMP(CURTIME()) |
+---------------------------+
| 1576380969 |
+---------------------------+
1 row in set (0.00 sec)
mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
+---------------------------------------+
| UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+---------------------------------------+
| 1320981071 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1576380910);
+---------------------------+
| FROM_UNIXTIME(1576380910) |
+---------------------------+
| 2019-12-15 11:35:10 |
+---------------------------+
1 row in set (0.00 sec)

8.3.3 获取月份、星期、星期数、天数等函数

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;
SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

8.3.4 日期的操作函数

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

8.3.5 时间和秒钟转换的函数

mysql> SELECT TIME_TO_SEC(NOW());
+--------------------+
| TIME_TO_SEC(NOW()) |
+--------------------+
| 78774 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774);
+--------------------+
| SEC_TO_TIME(78774) |
+--------------------+
| 21:52:54 |
+--------------------+
1 row in set (0.12 sec)

8.3.6 计算日期和时间的函数

第一组:

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21
23:32:12',INTERVAL
1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;
SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1,
SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2,
DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3
FROM DUAL;

第二组：

mysql> SELECT ADDTIME(NOW(), 50);
+---------------------+
| ADDTIME(NOW(), 50) |
+---------------------+
| 2019-12-15 22:17:47 |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT ADDTIME(NOW(), '1:1:1');
+-------------------------+
| ADDTIME(NOW(), '1:1:1') |
+-------------------------+
| 2019-12-15 23:18:46 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1);
+------------------+
| MAKEDATE(2020,1) |
+------------------+
| 2020-01-01 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1);
+-----------------+
| MAKETIME(1,1,1) |
+-----------------+
| 01:01:01 |
+-----------------+
1 row in set (0.00 sec)

8.3.7 日期的格式化与解析

mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
+--------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') |
+--------------------------------+
| 22:57:34 |
+--------------------------------+
1 row in set (0.00 sec)
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y') FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s') FROM DUAL;
mysql> SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-----------------------------------------------+
| STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-----------------------------------------------+
| 2020-01-01 |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

8.4 数值函数

8.4.1 基本函数

SELECT
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(
32.32),
FLOOR(-43.23),MOD(12,5)
FROM DUAL;
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
SELECT
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.
66,-1)
FROM DUAL;

8.4.2 三角函数

SELECT
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(A
TAN2(1,1)
) FROM DUAL;

8.4.3 角度与弧度互换函数

8.4.4 指数和对数

8.4.5 进制间的转换

8.5 流程控制函数

SELECT IF(1 > 0,'正确','错误')
->正确
SELECT IFNULL(null,'Hello Word')
->Hello Word
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;

8.6 加密与解密函数

mysql> SELECT PASSWORD('mysql'), PASSWORD(NULL);
+-------------------------------------------+----------------+
| PASSWORD('mysql') | PASSWORD(NULL) |
+-------------------------------------------+----------------+
| *E74858DB86EBA20BC33D0AECAE8A8108C56B17FA | |
+-------------------------------------------+----------------+
1 row in set, 1 warning (0.00 sec)
SELECT md5('123')
->202cb962ac59075b964b07152d234b70
SELECT SHA('Tom123')
->c7c506980abc31cc390a2438c90861d0f1216d50
mysql> SELECT ENCODE('mysql', 'mysql');
+--------------------------+
| ENCODE('mysql', 'mysql') |
+--------------------------+
| íg ¼ ìÉ |
+--------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
+-----------------------------------------+
| DECODE(ENCODE('mysql','mysql'),'mysql') |
+-----------------------------------------+
| mysql |
+-----------------------------------------+
1 row in set, 2 warnings (0.00 sec)

8.7 MYSQL信息函数

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
+----------------+----------------+----------------+----------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() |
+----------------+----------------+----------------+----------------+
| root@localhost | root@localhost | root@localhost | root@localhost |
+----------------+----------------+----------------+----------------+
mysql> SELECT CHARSET('ABC');
+----------------+
| CHARSET('ABC') |
+----------------+
| utf8mb4 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT COLLATION('ABC');
+--------------------+
| COLLATION('ABC') |
+--------------------+
| utf8mb4_general_ci |
+--------------------+
1 row in set (0.00 sec)

9 聚合函数

9.1 聚合函数介绍

1.什么是聚合函数

2.聚合函数类型

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

3.聚合函数语法

4. AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。

SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

5. MIN和MAX函数
   可以对任意数据类型的数据使用 MIN 和 MAX 函数。

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

6. COUNT函数
   COUNT()返回表中记录总数，适用于任意数据类型。

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

COUNT(expr) 返回expr不为空的记录总数。

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 50;

9.2 GROUP BY

9.2.1 基本使用

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

注意：WHERE一定放在FROM后面
在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

9.2.2 使用多个列分组

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

9.2.3 GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

9.3 HAVING

9.3.1 基本使用

ELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。如下：

SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
#报错

9.3.2 WHERE和HAVING的对比

9.4 SELECT的执行过程

9.4.1 查询的结构

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分

9.4.2 SELECT执行顺序

1. 关键字的顺序是不能颠倒的：

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

2. SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -
> LIMIT

10 子查询

10.1 子查询简介

10.1.1 实际问题

#方式一：
SELECT salary
FROM employees
WHERE last_name = 'Abel';
SELECT last_name,salary
FROM employees
WHERE salary > 11000;
#方式二：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式三：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

10.1.2 子查询的基本使用

10.1.3 子查询的分类

10.2 单行子查询

10.2.1 单行比较操作符

题目：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141)
AND salary >
(SELECT salary
FROM employees
WHERE employee_id = 143);

题目：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);

10.2.2 HAVING中使用子查询

题目：查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);

10.3 多行子查询

题目：查询平均工资最低的部门id

SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)

10.4 思考问题

问题：谁的工资比Abel的高？

#方式1：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e1.last_name = 'Abel'
AND e1.`salary` < e2.`salary`
#方式2：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

问题：以上两种方式有好坏之分吗？
解答：自连接方式好！
题目中可以使用子查询，也可以使用自连接。一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。
可以这样理解：子查询实际上是通过未知表进行查询后的条件判断，而自连接是通过已知的自身数据表进行条件判断，因此在大部分 DBMS 中都对自连接处理进行了优化。

11 创建和管理表

11.1 基础知识

11.1.1 一条数据存储的过程

11.1.2 标识符命名规则

数据库名、表名不得超过30个字符，变量名限制为29个
必须只能包含 A–Z, a–z, 0–9, _共63个字符
数据库名、表名、字段名等对象名中间不要包含空格
同一个MySQL软件中，数据库不能同名；同一个库中，表不能重名；同一个表中，字段不能重名必须保证你的字段没有和保留字、数据库系统或常用方法冲突。如果坚持使用，请在SQL语句中使用`（着重
号）引起来
保持字段名和类型的一致性：在命名字段并为其指定数据类型的时候一定要保证一致性，假如数据类型
在一个表里是整数，那在另一个表里可就别变成字符型了

11.1.3 MySQL中的数据类型

11.2 创建和管理数据库

11.2.1 创建数据库

方式一：创建数据库

CREATE DATABASE 数据库名;

方式二：创建数据库并指定字符集

CREATE DATABASE 数据库名 CHARACTER SET 字符集;

方式三：判断数据库是否已经存在，不存在则创建数据库（ 推荐 ）

CREATE DATABASE IF NOT EXISTS 数据库名;

如果MySQL中已经存在相关的数据库，则忽略创建语句，不再创建数据库。
注意：DATABASE 不能改名。一些可视化工具可以改名，它是建新库，把所有表复制到新库，再删旧库完成的。

11.2.2 使用数据库

查看当前所有数据库

SHOW DATABASES; #有一个S，代表多个数据库

查看当前正在使用的数据库

SELECT DATABASE(); #使用的一个 mysql 中的全局函数

查看指定库下的表

SHOW TABLES FROM 数据库名;

查看数据库的创建信息

SHOW CREATE DATABASE 数据库名;
或者：
SHOW CREATE DATABASE 数据库名\G

使用/切换数据库

USE 数据库名;

11.2.3 修改数据库

ALTER DATABASE 数据库名 CHARACTER SET 字符集; #比如：gbk、utf8等

11.2.4 删除数据库

删除指定数据库

DROP DATABASE 数据库名;
DROP DATABASE IF EXISTS 数据库名;（推荐）

11.3 创建表

11.3.1 创建方式1

CREATE TABLE [IF NOT EXISTS] 表名(
字段1, 数据类型 [约束条件] [默认值],
字段2, 数据类型 [约束条件] [默认值],
字段3, 数据类型 [约束条件] [默认值],
……
[表约束条件]
);

必须指定：

• 表名
• 列名(或字段名)，数据类型，长度

可选指定：

• 约束条件
• 默认值

例：

-- 创建表
CREATE TABLE emp (
-- int类型
emp_id INT,
-- 最多保存20个中英文字符
emp_name VARCHAR(20),
-- 总位数不超过15位
salary DOUBLE,
-- 日期类型
birthday DATE
);

CREATE TABLE dept(
-- int类型，自增
deptno INT(2) AUTO_INCREMENT,
dname VARCHAR(14),
loc VARCHAR(13),
-- 主键
PRIMARY KEY (deptno)
);

查看表的结构和列信息

DESC emp;或
DESCRIBE dept;

11.3.2 创建方式2

CREATE TABLE emp1 AS SELECT * FROM employees;
CREATE TABLE emp2 AS SELECT * FROM employees WHERE 1=2; -- 创建的emp2是空表

CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;
DESCRIBE dept80;

11.3.3 查看数据表结构

SHOW CREATE TABLE 表名\G

11.4 修改表

追加一个列：

ALTER TABLE 表名 ADD 【COLUMN】 字段名 字段类型 【FIRST|AFTER 字段名】;

ALTER TABLE dept80
ADD job_id varchar(15);

修改一个列：

ALTER TABLE 表名 MODIFY 【COLUMN】 字段名1 字段类型 【DEFAULT 默认值】【FIRST|AFTER
字段名2】;

ALTER TABLE dept80 MODIFY last_name VARCHAR(30);
ALTER TABLE dept80 MODIFY salary double(9,2) default 1000;

重命名一个列：

ALTER TABLE 表名 CHANGE 【column】 列名 新列名 新数据类型;

ALTER TABLE dept80 CHANGE department_name dept_name varchar(15);

删除一个列：

ALTER TABLE 表名 DROP 【COLUMN】字段名

ALTER TABLE dept80 DROP COLUMN job_id;

11.5 重命名表

RENAME TABLE emp TO myemp;或
ALTER table dept RENAME [TO] detail_dept; -- [TO]可以省略

11.6 删除表

DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, …, 数据表n];

DROP TABLE dept80;

11.7 清空表

TRUNCATE TABLE detail_dept;

12 数据处理之增删改

12.1 插入数据

12.1.1 VALUES的方式添加

为表中所有字段按默认顺序插入数据：

INSERT INTO 表名
VALUES (value1,value2,....);

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);
INSERT INTO departments
VALUES (100, 'Finance', NULL, NULL);

为表的指定字段插入数据：

INSERT INTO 表名(column1 [, column2, …, columnn])
VALUES (value1 [,value2, …, valuen]);

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

同时插入多条数据：

INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

或

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

例：

mysql> INSERT INTO emp(emp_id,emp_name)
-> VALUES (1001,'shkstart'),
-> (1002,'atguigu'),
-> (1003,'Tom');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

12.1.2 将查询结果插入到表中

INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

INSERT INTO emp2
SELECT *
FROM employees
WHERE department_id = 90;

INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';

12.2 更新数据

UPDATE table_name
SET column1=value1, column2=value2, … , column=valuen
[WHERE condition]

UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
或
UPDATE copy_emp
SET department_id = 110;

12.3 删除数据

DELETE FROM departments
WHERE department_name = 'Finance';

DELETE FROM departments
WHERE department_id = 60;

13 约束

13.1 约束概述

约束（Constraints）在数据库中起到了关键的作用，它们用于确保数据的完整性、一致性和准确性。以下是一些需要约束的原因：

• 数据完整性：约束可以确保数据表中的数据满足特定的规则和要求。例如，主键约束要求每个记录都有唯一标识符，外键约束要求引用其他表的数据必须存在关联关系等。这样可以避免数据中出现缺失、重复或不一致的情况，保证数据的完整性。
• 数据一致性：约束可以确保数据表中不同列之间的数据保持一致。例如，唯一约束要求某列的值在表中是唯一的，检查约束可以确保某列的值满足特定的条件等。这有助于避免数据之间的冲突和矛盾，保持数据的一致性。
• 数据安全性：约束可以加强对数据的安全性和保护机制。例如，检查约束可以限制某列的值必须在指定的范围内，防止恶意输入或错误数据导致的安全风险。此外，约束还可以限制对表的操作，如防止删除关键数据、修改重要字段等。
• 查询性能优化：约束可以提供额外的信息，帮助数据库优化查询操作。例如，主键约束可以帮助数据库快速定位和访问数据，外键约束可以自动处理关联的查询和更新操作等。

总的来说，约束是数据库设计和管理中至关重要的一部分，它们可以确保数据的完整性、一致性和安全性，并提高数据库的性能和效率。

约束的分类：
根据约束数据列的限制，约束可分为：

• 单列约束：每个约束只约束一列
• 多列约束：每个约束可约束多列数据

根据约束的作用范围，约束可分为：

• 列级约束：只能作用在一个列上，跟在列的定义后面
• 表级约束：可以作用在多个列上，不与列一起，而是单独定义

根据约束起的作用，约束可分为：

• NOT NULL 非空约束，规定某个字段不能为空
• UNIQUE 唯一约束，规定某个字段在整个表中是唯一的
• PRIMARY KEY 主键(非空且唯一)约束
• FOREIGN KEY 外键约束
• CHECK 检查约束
• DEFAULT 默认值约束

13.2 非空约束

13.2.1 建表时

CREATE TABLE 表名称(
字段名 数据类型,
字段名 数据类型 NOT NULL,
字段名 数据类型 NOT NULL
);

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL,
sex CHAR NULL
);

CREATE TABLE student(
sid int,
sname varchar(20) not null,
tel char(11) ,
cardid char(18) not null
);

insert into student values(1,'张三','13710011002','110222198912032545'); #成
功
insert into student values(2,'李四','13710011002',null);#身份证号为空
ERROR 1048 (23000): Column 'cardid' cannot be null
insert into student values(2,'李四',null,'110222198912032546');#成功，tel允许为
空
insert into student values(3,null,null,'110222198912032547');#失败
ERROR 1048 (23000): Column 'sname' cannot be null

13.2.2 建表后

 alter table 表名称 modify 字段名 数据类型 not null;

ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;

alter table student modify sname varchar(20) not null;

13.2.3 删除非空约束

ALTER TABLE emp MODIFY sex VARCHAR(30) NULL;

ALTER TABLE emp MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

13.3 唯一性约束

13.3.1 建表时

create table 表名称(
字段名 数据类型,
字段名 数据类型 unique,
字段名 数据类型 unique key,
字段名 数据类型
);

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] unique key(字段名)
);

create table student(
sid int,
sname varchar(20),
tel char(11) unique,
cardid char(18) unique key
);

CREATE TABLE t_course(
cid INT UNIQUE,
cname VARCHAR(100) UNIQUE,
description VARCHAR(200)
);

CREATE TABLE USER(
id INT NOT NULL,
NAME VARCHAR(25),
PASSWORD VARCHAR(16),
-- 使用表级约束语法
CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);

insert into student values(1,'张三','13710011002','101223199012015623');
insert into student values(2,'李四','13710011003','101223199012015624');
insert into student values(3,'王五','13710011004','101223199012015624'); #身
份证号重复
ERROR 1062 (23000): Duplicate entry '101223199012015624' for key 'cardid'
insert into student values(3,'王五','13710011003','101223199012015625');
ERROR 1062 (23000): Duplicate entry '13710011003' for key 'tel

13.3.2 建表后指定唯一约束

#字段列表中如果是一个字段，表示该列的值唯一。如果是两个或更多个字段，那么复合唯一，即多个字段
的组合是唯一的
#方式1：
alter table 表名称 add unique key(字段列表);
#方式2：
alter table 表名称 modify 字段名 字段类型 unique;

create table student(
sid int primary key,
sname varchar(20),
tel char(11) ,
cardid char(18)
);

alter table student add unique key(tel);
alter table student add unique key(cardid);

13.3.3 关于复合唯一约束

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
unique key(字段列表) #字段列表中写的是多个字段名，多个字段名用逗号分隔，表示那么是复
合唯一，即多个字段的组合是唯一的
);

#学生表
create table student(
sid int, #学号
sname varchar(20), #姓名
tel char(11) unique key, #电话
cardid char(18) unique key #身份证号
);

#课程表
create table course(
cid int, #课程编号
cname varchar(20) #课程名称
);

#选课表
create table student_course(
id int,
sid int,
cid int,
score int,
unique key(sid,cid) #复合唯一
);

insert into student values(1,'张三','13710011002','101223199012015623');#成功
insert into student values(2,'李四','13710011003','101223199012015624');#成功
insert into course values(1001,'Java'),(1002,'MySQL');#成功
insert into student_course values
(1, 1, 1001, 89),
(2, 1, 1002, 90),
(3, 2, 1001, 88),
(4, 2, 1002, 56);#成功
insert into student_course values (5, 1, 1001, 88);#失败

13.3.4 删除唯一性约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名';
#查看都有哪些约束
#删除唯一索引
ALTER TABLE USER DROP INDEX uk_name_pwd;

13.4 PRIMARY KEY 约束

PRIMARY KEY（主键）约束是一种用于定义数据表中唯一标识记录的约束。主键约束要求某列（或多个列的组合）的值在整个表中是唯一的，并且不能为空（即不为NULL）。

主键约束的作用如下：

• 唯一性：主键约束确保了某列（或列组合）的值在表中是唯一的。这意味着每条记录都有一个独特的标识符，没有两条记录可以具有相同的主键值。这样可以避免数据重复和冲突，保证数据的唯一性。
• 数据完整性：主键约束要求主键列的值不能为空（即不为NULL）。这确保了每条记录都具有有效的主键值，避免了数据缺失的情况。
• 快速访问：主键约束为数据库提供了快速访问和定位数据的能力。由于主键的值是唯一的，数据库可以使用主键作为索引来加速查询操作，提高查询的性能和效率。

13.4.1 添加主键约束

建表时指定主键约束：

create table 表名称(
字段名 数据类型 primary key, #列级模式
字段名 数据类型,
字段名 数据类型
);

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
[constraint 约束名] primary key(字段名) #表级模式
);

create table temp(
id int primary key,
name varchar(20)
)
mysql> desc temp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
insert into temp values(1,'张三');#成功
insert into temp values(2,'李四');#成功
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
+----+------+
2 rows in set (0.00 sec)
insert into temp values(1,'张三');#失败
ERROR 1062 (23000): Duplicate（重复） entry（键入，输入） '1' for key 'PRIMARY'
insert into temp values(1,'王五');#失败
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into temp values(3,'张三');#成功
mysql> select * from temp;
+----+------+
| id | name |
+----+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 张三 |
+----+------+
3 rows in set (0.00 sec)
insert into temp values(4,null);#成功
insert into temp values(null,'李琦');#失败
ERROR 1048 (23000): Column 'id' cannot be null

列级约束：

CREATE TABLE emp4(
id INT PRIMARY KEY AUTO_INCREMENT ,
NAME VARCHAR(20)
)

表级约束：

CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

建表后增加主键约束：

ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表); #字段列表可以是一个字段，也可以是多个字
段，如果是多个字段的话，是复合主键

ALTER TABLE student ADD PRIMARY KEY (sid);
ALTER TABLE emp5 ADD PRIMARY KEY(NAME,pwd);

13.4.2 复合主键

create table 表名称(
字段名 数据类型,
字段名 数据类型,
字段名 数据类型,
primary key(字段名1,字段名2) #表示字段1和字段2的组合是唯一的，也可以有更多个字段
);

#学生表
create table student(
sid int primary key, #学号
sname varchar(20) #学生姓名
);
#课程表
create table course(
cid int primary key, #课程编号
cname varchar(20) #课程名称
);
#选课表
create table student_course(
sid int,
cid int,
score int,
primary key(sid,cid) #复合主键
);
insert into student values(1,'张三'),(2,'李四');
insert into course values(1001,'Java'),(1002,'MySQL');
mysql> select * from student;
+-----+-------+
| sid | sname |
+-----+-------+
| 1 | 张三 |
| 2 | 李四 |
+-----+-------+
2 rows in set (0.00 sec)
mysql> select * from course;
+------+-------+
| cid | cname |
+------+-------+
| 1001 | Java |
| 1002 | MySQL |
+------+-------+
2 rows in set (0.00 sec)
insert into student_course values(1, 1001, 89),(1,1002,90),(2,1001,88),
(2,1002,56);
mysql> select * from student_course;
+-----+------+-------+
| sid | cid | score |
+-----+------+-------+
| 1 | 1001 | 89 |
| 1 | 1002 | 90 |
| 2 | 1001 | 88 |
| 2 | 1002 | 56 |
+-----+------+-------+
4 rows in set (0.00 sec)
insert into student_course values(1, 1001, 100);
ERROR 1062 (23000): Duplicate entry '1-1001' for key 'PRIMARY'

13.4.3 删除主键约束

alter table 表名称 drop primary key;

ALTER TABLE student DROP PRIMARY KEY;
ALTER TABLE emp5 DROP PRIMARY KEY;

13.5 自增列：AUTO_INCREMENT

• 一个表最多只能有一个自增长列
• 当需要产生唯一标识符或顺序值时，可设置自增长
• 自增长列约束的列必须是键列（主键列，唯一键列）
• 自增约束的列的数据类型必须是整数类型
• 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值

建表时：

create table 表名称(
字段名 数据类型 primary key auto_increment,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);
create table 表名称(
字段名 数据类型 default 默认值 ,
字段名 数据类型 unique key auto_increment,
字段名 数据类型 not null default 默认值,,
primary key(字段名)
);

create table employee(
eid int primary key auto_increment,
ename varchar(20)
);
mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

建表后：

alter table 表名称 modify 字段名 数据类型 auto_increment;

create table employee(
eid int primary key ,
ename varchar(20)
)
alter table employee modify eid int auto_increment;
mysql> desc employee;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

删除自增约束：

alter table 表名称 modify 字段名 数据类型; #去掉auto_increment相当于删除
alter table employee modify eid int;
mysql> desc employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid | int(11) | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

重置auto_increment：

#方式一
delete from tb1;
ALTER TABLE tbl AUTO_INCREMENT = 100;
#方式二
truncate tb1; #（好处, 简单, AUTO_INCREMENT 值重新开始计数.）

13.6 FOREIGN KEY 约束

FOREIGN KEY（外键）约束是一种用于建立数据表间关联关系的约束。外键约束定义了一个列或列组合，它引用另一个表中的主键或唯一键，从而创建了两个表之间的关联。

外键约束的作用如下：

• 关联数据：外键约束允许在一个表中引用另一个表的数据。通过在一个表中定义外键列，并将其与另一个表的主键或唯一键进行关联，我们可以建立起两个表之间的联系和关联。
• 数据完整性：外键约束确保引用的数据在相关表中存在。它要求外键的值必须匹配关联表中的已有主键或唯一键的值。这样可以防止在关联表中引用不存在的数据，保证数据的完整性和一致性。
• 数据一致性：外键约束可以确保关联表之间的数据一致性。当主表中的数据发生变化时，外键约束可以自动更新或限制关联表中的数据。例如，如果删除主表中的一条记录，外键约束可以限制或自动删除关联表中与该记录相关的数据，避免了数据之间的冲突和不一致。

13.6.1 主表和从表

• 主表（父表）：被引用的表，被参考的表
• 从表（子表）：引用别人的表，参考别人的表
• 例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。
• 例如：学生表、课程表、选课表：选课表的学生和课程要分别参考学生表和课程表，学生表和课程表是主表，选课表是从表。

13.6.2 特点

• （1）从表的外键列，必须引用/参考主表的主键或唯一约束的列
• （2）在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名（例如student_ibfk_1;），也可以指定外键约束名。
• （3）创建(CREATE)表时就指定外键约束的话，先创建主表，再创建从表
• （4）删表时，先删从表（或先删除外键约束），再删除主表
• （5）当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
• （6）在“从表”中指定 外键约束，并且一个表可以建立多个外键约束
• （7）从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意义一致。如果类型不一样，创建子表时，就会出现错误“ERROR
  1005 (HY000): Can’t createtable’database.tablename’(errno: 150)”。
• （8）当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是外键的约束名。（根据外键查询效率很高）
• （9）删除外键约束后，必须 手动 删除对应的索引

13.6.3 建表时：

create table 主表名称(
字段1 数据类型 primary key,
字段2 数据类型
);
create table 从表名称(
字段1 数据类型 primary key,
字段2 数据类型,
[CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段) references 主表名(被
参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
-- FOREIGN KEY: 在表级指定子表中的列
-- REFERENCES: 标示在父表中的列

create table dept( #主表
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(#从表
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int, #员工所在的部门
foreign key (deptid) references dept(did) #在从表中指定外键约束
#emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
);
说明：
（1）主表dept必须先创建成功，然后才能创建emp表，指定外键成功。
（2）删除表时，先删除从表emp，再删除主表dept

13.6.4 建表后

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的字段) REFERENCES 主
表名(被引用字段) [on update xx][on delete xx];

ALTER TABLE emp1 ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id)
REFERENCES dept(dept_id);

create table dept(
did int primary key, #部门编号
dname varchar(50) #部门名称
);
create table emp(
eid int primary key, #员工编号
ename varchar(5), #员工姓名
deptid int #员工所在的部门
);
#这两个表创建时，没有指定外键的话，那么创建顺序是随意
alter table emp add foreign key (deptid) references dept(did);

13.6.5 约束等级

• Cascade方式 ：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
• Set null方式 ：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
• No action方式 ：如果子表中有匹配的记录，则不允许对父表对应候选键进行update/delete操作
• Restrict方式 ：同no action， 都是立即检查外键约束
• Set default方式
  （在可视化工具SQLyog中可能显示空白）：父表有变更时，子表将外键列设置成一个默认的值，但Innodb不能识别
• 如果没有指定等级，就相当于Restrict方式。

13.7 DEFAULT约束

DEFAULT约束是一种用于设置列的默认值的约束。当插入新行时，如果没有显式地为该列指定值，那么将使用默认值作为该列的值。

DEFAULT约束的作用如下：

• 默认值：DEFAULT约束允许在创建表时为列设置默认值。当插入新行时，如果没有提供该列的值，将使用默认值填充该列。这样可以确保数据表中的每个记录都具有一个默认值，避免了数据缺失的情况。
• 灵活性：DEFAULT约束可以根据需求设置不同的默认值。默认值可以是一个常数、一个表达式或一个函数返回的值。这样可以根据业务逻辑和需求来定义默认值。
• 数据完整性：DEFAULT约束确保在插入新行时，所有未提供值的列都能被正确填充。这避免了因未提供值而导致的数据完整性问题。默认值可以是有效的、合理的值，使数据保持一致和准确。

13.7.1 建表时

create table 表名称(
字段名 数据类型 primary key,
字段名 数据类型 unique key not null,
字段名 数据类型 unique key,
字段名 数据类型 not null default 默认值,
);

create table employee(
eid int primary key,
ename varchar(20) not null,
gender char default '男',
tel char(11) not null default '' #默认是空字符串
);
insert into employee(eid,ename) values(2,'天琪'); #成功
mysql> select * from employee;
+-----+-------+--------+-------------+
| eid | ename | gender | tel |
+-----+-------+--------+-------------+
| 1 | 汪飞 | 男 | 13700102535 |
| 2 | 天琪 | 男 | |
+-----+-------+--------+-------------+
2 rows in set (0.00 sec)

13.7.2 建表后

alter table 表名称 modify 字段名 数据类型 default 默认值;
#如果这个字段原来有非空约束，你还保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非
空约束就被删除了
#同理，在给某个字段加非空约束也一样，如果这个字段原来有默认值约束，你想保留，也要在modify语
句中保留默认值约束，否则就删除了
alter table 表名称 modify 字段名 数据类型 default 默认值 not null;

create table employee(
eid int primary key,
ename varchar(20),
gender char,
tel char(11) not null
);
alter table employee modify gender char default '男'; #给gender字段增加默认值约束
alter table employee modify tel char(11) default ''; #给tel字段增加默认值约束