Hi SANNAHA

A Link is a Promise.

0%

MySQL与SQL语句

发表于 更新于

MySQL头图

目录 概述
数据库概述 数据库、数据库管理系统与常见数据库
安装MySQL 在 Linux 和 Docker 上安装 MySQL
登录与重设密码 登录或用 SQLyog 连接 MySQL 数据库
数据库备份与恢复 备份与恢复 MySQL 数据库
SQL概述 SQL 类型与常用数据类型
数据操作语言DML 操作数据库对象与表
数据操作语言DML 插入、更新与删除表记录
数据查询语言DQL 简单查询、条件查询、排序查询、聚合查询与排序查询
SQL执行顺序 FROM, ON, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT
SQL约束 主键约束、非空约束、唯一约束、默认约束与外键约束
多表操作 表与表之间的关系，一对多、多对多
多表查询 连接、子查询

数据库概述

数据库（DataBase）就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

数据库管理系统可以创建并管理多个数据库。数据库以表为组织单位存储数据，每张表有多个字段，每个字段都有对应的数据类型。根据表字段所规定的数据类型，我们可以向其中填入一条条的数据，而表中的每条数据类似类的实例对象。表中的一行一行的信息我们称之为记录。

数据库和数据库管理系统

常见的数据库管理系统：

MYSQL：开源免费的数据库，已经被 Oracle 收购。
Oracle：收费的大型数据库，Oracle 公司的产品。
DB2：IBM 公司的数据库产品。
SQLServer：MicroSoft 公司的数据库，常在 C#、.net 以及 Azure 等微软系产品中使用。
SQLite：嵌入式的小型数据库，应用在手机端，比如 Android APP。

安装MySQL

Yum

对于本地源中包含 MySQL 的包或者服务器有外网连接的情况下，使用 yum 安装可以自动解决依赖问题。

  1. 使用 yum 在线安装 MySQL：
1
$ yum -y install mysql mysql-server mysql-devel
  1. 运行 mysql 安装自带脚本进行配置：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
$ /usr/bin/mysql_secure_installation
...
# 输入root用户的密码（初次使用直接回车）
Enter current password for root (enter for none):

# 设置root用户的密码，如：123456
Set root password? [Y/n] y
New password:123456

# 移除匿名用户
Remove anonymous users? [Y/n] y

# 不禁止（允许）远程访问
Disallow root login remotely? [Y/n] n

# 移除测试数据库
Remove test database and access to it? [Y/n] y

# 重新加载权限表
Reload privilege tables now? [Y/n] y
  1. 进入 MySQL 客户端，进行授权：
1
2
3
4
5
$ mysql -uroot -proot
# 授权mysql允许远程连接
mysql> grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
# 刷新权限表
mysql> FLUSH PRIVILEGES;
  1. 设置 MySQL 开机启动：
1
2
$ chkconfig --add mysqld
$ chkconfig mysqld on

曾经在 cdh 集群安装过 MySQL 5.7（可以使用，但后来可能是由于版本问题无法与CM连接）：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# 添加MySQL5.7仓库
$ rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

# 安装rpm包
yum -y localinstall mysql57-community-release-el7-11.noarch.rpm

# 显示已配置的源，确认MySQL源添加成功
$ yum repolist enabled | grep "mysql.*-community.*"
mysql-connectors-community/x86_64       MySQL Connectors Community           230
mysql-tools-community/x86_64            MySQL Tools Community                138
mysql57-community/x86_64                MySQL 5.7 Community Server           564

# 安装MySQL
$ yum -y install mysql-community-server

# 启动MySQL
$ systemctl start mysqld
# 查看启动情况
$ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 日 2022-03-13 23:27:50 CST; 19s ago

# 查看root默认密码，启动MySQL后才能在日志文件中看到初始密码
$ grep 'temporary password' /var/log/mysqld.log
2022-03-13T15:27:48.217568Z 1 [Note] A temporary password is generated for root@localhost: (qrhRp;W/7&/

(qrhRp;W/7&/

# 运行安全设置脚本，可以完成设置root密码，移除匿名用户，禁止root用户远程连接等
$ mysql_secure_installation




# 最近在安装 MySQL 时报错：
$ yum -y install mysql-community-server
...
mysql-community-libs-compat-5.7.37-1.el7.x86_64.rpm 的公钥尚未安装


 失败的软件包是：mysql-community-libs-compat-5.7.37-1.el7.x86_64
 GPG  密钥配置为：file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# 设置不检测 GPG，然后重新执行安装即可：
# vim /etc/yum.repos.d/mysql-community.repo
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
- gpgcheck=1
+ gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

RPM

对于无外网连接的情况，可以提前手动下载 RPM 包，再上传到服务器安装。下载 MySQL 5.7（需注册甲骨文账号）：https://dev.mysql.com/downloads/mysql/5.7.html

下载时可以选择整合包 mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar，解包后可以得到 10 个 .rpm 文件。也可以根据需要选择下载。使用 Oracle 的 RPM 软件包在 Linux 上安装 MySQL https://dev.mysql.com/doc/refman/5.7/en/linux-installation-rpm.html

各包的说明如下：

包名字 概括
mysql-community-server 数据库服务器及相关工具
mysql-community-client MySQL 客户端应用程序和工具
mysql-community-common 服务器和客户端库的通用文件
mysql-community-devel MySQL数据库客户端应用程序的开发头文件和库
mysql-community-libs MySQL 数据库客户端应用程序的共享库
mysql-community-libs-compat 先前 MySQL 安装的共享兼容性库
mysql-community-embedded MySQL 嵌入式库
mysql-community-embedded-devel 将 MySQL 作为嵌入式库开发头文件和库
mysql-community-test MySQL 服务器的测试套件 
1
2
3
4
5
6
7
8
9
10
mysql-community-client-5.7.36-1.el7.x86_64.rpm：MySQL客户端应用程序和工具
mysql-community-common-5.7.36-1.el7.x86_64.rpm：服务器和客户端库的通用文件
mysql-community-devel-5.7.36-1.el7.x86_64.rpm：用于MySQL数据库客户端应用程序的开发头文件和库
mysql-community-embedded-5.7.36-1.el7.x86_64.rpm：MySQL嵌入式库 
mysql-community-embedded-compat-5.7.36-1.el7.x86_64.rpm：MySQL服务器作为嵌入式库，与使用库版本18的应用程序兼容
mysql-community-embedded-devel-5.7.36-1.el7.x86_64.rpm：MySQL的开发标头文件和库作为可嵌入库
mysql-community-libs-5.7.36-1.el7.x86_64.rpm：MySQL数据库客户端应用程序的共享库 
mysql-community-libs-compat-5.7.36-1.el7.x86_64.rpm：以前的MySQL安装的共享兼容性库 
mysql-community-server-5.7.36-1.el7.x86_64.rpm：数据库服务器和相关工具
mysql-community-test-5.7.36-1.el7.x86_64.rpm：MySQL服务器的测试套件

安装时，需要根据依赖顺序 common -> libs -> client -> server -> libs-compat -> devel 进行安装：

在大多数情况下，您需要安装 mysql-community-server、 mysql-community-client、 mysql-community-libs、 mysql-community-common和 mysql-community-libs-compat软件包才能获得功能性的标准 MySQL 安装。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# common/libs/client/server 这四个包为必需
$ rpm -ivh mysql-community-common-5.7.36-1.el7.x86_64.rpm --force --nodeps
$ rpm -ivh mysql-community-libs-5.7.36-1.el7.x86_64.rpm --force --nodeps
$ rpm -ivh mysql-community-client-5.7.36-1.el7.x86_64.rpm --force --nodeps
$ rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm --force --nodeps

$ rpm -ivh --force --nodeps mysql-community-common-5.7.44-1.el7.x86_64.rpm mysql-community-libs-5.7.44-1.el7.x86_64.rpm mysql-community-client-5.7.44-1.el7.x86_64.rpm mysql-community-server-5.7.44-1.el7.x86_64.rpm

# mysql5.1
rpm -ivh --force --nodeps MySQL-server-community-5.1.73-1.rhel5.x86_64.rpm 

rpm -ivh：
-i：安装软件包。
-v：在安装过程中显示详细的输出信息。
-h：以哈希标记的方式显示进度。
mysql-community-common-5.7.36-1.el7.x86_64.rpm：这是要安装的 MySQL Community Edition 的 RPM 软件包的文件名。包含了软件包的名称、版本（5.7.36）、发行版本（1.el7）、架构（x86_64）等信息。
--force：强制安装。这个选项告诉 rpm 强制安装软件包，即使已经安装了同一版本的软件包或者存在其他冲突。
--nodeps：忽略依赖关系。这个选项告诉 rpm 在安装过程中忽略软件包的依赖关系，即使系统中缺少了一些必需的依赖项。

对于 CDH6.1.1：

1
2
3
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
yum install -y mysql-community-server

启动：

1
2
3
4
5
$ systemctl start mysqld

# 查看版本号
$ mysqld --version
mysqld  Ver 5.7.44 for Linux on x86_64 (MySQL Community Server (GPL))

查看安装时生成的 root 密码：

1
2
3
4
$ grep 'temporary password' /var/log/mysqld.log 
2021-11-11T07:41:17.285626Z 1 [Note] A temporary password is generated for root@localhost: !sannahadrFuVtr8!q


Docker

从仓库拉取 MySQL 镜像并创建容器：

1
2
3
4
5
6
7
8
9
10
$ docker pull mysql:5.7

$ docker run -p 3306:3306 --name mysql \
--network bigdata \
--network-alias mysql \
-v conf:/etc/mysql \
-v logs:/var/log/mysql \
-v data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:5.7

登录与重设密码

登录

MySQL 数据库需要账户名和密码进行登录，它提供了一个默认的 root 账号，使用安装时设置的密码即可登录：

1
2
3
# 通过命令行登录
$ mysql –u用户名 –p密码
$ mysql --host=ip --user=用户名 --password=密码

修改密码

登录 MySQL 后，可以使用下面两种方式修改密码：

1
2
3
4
5
6
-- ALTER USER 'username'@'hostname' IDENTIFIED BY 'new_password';
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';

-- 设置用户的密码，PASSWORD函数已经弃用，更推荐使用上面的语句
mysql> SET PASSWORD = PASSWORD('new_password');

重设密码

  1. 停止运行 mysqld 服务：
1
$ service mysqld stop
  1. 跳过访问授权表：
1
$ mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
  1. 登录 MySQL：
1
$ mysql -u root mysql
  1. 修改密码
1
2
3
4
5
6
7
8
9
-- mysql5.7 版本以下，newpassword 填入新的密码
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost';

-- mysql5.7 版本，newpassword 填入新的密码
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost';

-- 刷新权限表
mysql> FLUSH PRIVILEGES;
mysql> quit
  1. 重启 mysqld 服务并使用新密码登录：
1
2
3
4
5
6
# 重启mysqld服务
$ service mysqld restart

# 使用新密码登录
$ mysql -uroot -p
Enter password: <newpassword>

添加用户

1
2
3
4
-- 本地登录
CREATE USER "tyUser"@"localhost" IDENTIFIED BY "tyS.Ae2NqN7lAlbHdcA";
-- 远程登录
CREATE USER "tyUser"@"%" IDENTIFIED BY "tyS.Ae2NqN7lAlbHdcA";

删除用户

1
2
3
Delete FROM mysql.user Where User="“tyUser”" and Host="”localhost”";
flush privileges;
drop database testDB;

查看用户

1
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

数据库备份与恢复

备份

将 MySQL 数据库转换成对应的 .sql 文件：

1
2
3
4
5
# 通过命令行备份，通过命令行备份的.sql文件不包含创建数据库的sql语句
mysqldump  -u用户名 -p密码 数据库名 > dbdump.sql

# 通过SQLyog备份，通过SQLyog备份的.sql文件包含创建数据库的sql语句
对要备份数据库点击右键，选择 Backup/Export --> Backup Database As SQL dump...， 指定导出路径，保存成.sql文件

恢复

使用备份产生的 .sql 文件恢复数据库：

1
2
3
4
5
6
7
8
9
# 通过命令行恢复
# 已登录数据库
mysql  -uroot  -p密码 数据库名 < dbdump.sql

# 未登录数据库
source dbdump.sql

# 通过SQLyog恢复
对任意一个数据库点击右键，选择 Import --> Restore From SQL dump...， 指定.sql文件，执行即可

SQL概述

结构化查询语言简称 SQL(Structured Query Language)，是关系型数据库管理系统都需要遵循的规范。不同的数据库生产厂商都支持 SQL 语句，但都有特有内容。

SQL类型

  • 数据定义语言 DDL(Data Definition Language)：用来定义数据库对象，比如数据库，表，列等。关键字包括 CREATE，ALTER，DROP 等
  • 数据操作语言 DML(Data Manipulation Language)：用来对数据库中表的记录进行更新。关键字包括 INSERT，DELETE，UPDATE 等
  • 数据控制语言 DCL(Data Control Language)：用来定义数据库的访问权限和安全级别，及创建用户。关键字包括 GRANT，REVOKE 等
  • 数据查询语言 DQL(Data Query Language)：用来查询数据库中表的记录。关键字包括 SELECT，FROM，WHERE 等

常用数据类型

类型名称 说明
int(integer) 整数类型
double 小数类型
decimal(m,d) 指定整数位与小数位长度的小数类型
date 日期类型，格式为 yyyy-MM-dd，包含年月日，不包含时分秒
datetime 日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒
timestamp 日期类型，时间戳
varchar(M) 文本类型， M 为 0~65535 之间的整数

数据定义语言DDL

操作数据库对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 创建数据库，使用默认编码utf8
create database 数据库名;

-- 创建数据库，指定数据的编码
create database 数据库名 character set 字符集;

-- 查看MySQL服务器中的所有的数据库
show databases;

-- 查看某个数据库的定义的信息
show create database 数据库名;

-- 删除数据库
drop database 数据库名称;

-- 查看正在使用的数据库
select database();

-- 切换数据库
use 数据库名;

操作表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 创建表
create table 表名(
   字段名 类型(长度) [约束],
   字段名 类型(长度) [约束],
   ...
);

-- 查看数据库中的所有表
show tables;

-- 查看表结构
desc 表名;

-- 删除表
drop table 表名;

-- 修改表名
rename table 表名 to 新表名; 
-- 例子：将分类表category改名为category2
RENAME TABLE category TO category2;

-- 修改表的字符集
alter table 表名 character set 字符集;
-- 例子：修改分类表的编码表为gbk
ALTER TABLE category CHARACTER SET gbk;

-- 修改表结构
-- 1.添加新字段（列）
alter table 表名 add 列名 类型(长度) [约束];
#例子：为分类表添加“描述”字段，类型为varchar(20)
ALTER TABLE category ADD `describe` VARCHAR(20);

-- 2.修改字段的类型(长度)及约束
alter table 表名 modify 字段名 类型(长度)约束;
-- 例子：修改分类表的描述字段，类型(长度)改为varchar(50)，添加非空约束
ALTER TABLE category MODIFY `describe` VARCHAR(50) NOT NULL;

-- 3.修改字段名
alter table 表名 change 旧字段名 新字段名 类型(长度) 约束; 
-- 例子：为分类表的分类名称字段进行更换 更换为 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);

-- 4.删除字段
alter table 表名 drop 字段名; 
-- 例子：删除分类表中的description字段
ALTER TABLE category DROP description;

数据操作语言DML

插入表记录

1
2
3
4
5
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values  (值1,值2,值3..); 

-- 向表中插入所有字段,字段的顺序为创建表时的顺序
insert into 表 values (值1,值2,值3..);

注意：

  • 值与字段必须对应，个数相同，类型相同
  • 值的数据大小必须在字段的长度范围内
  • 除了数值类型外，其它的字段类型的值必须使用引号引起（建议单引号）
  • 如果要插入空值，可以不写字段，或者插入 null

更新表记录

1
2
3
4
5
-- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值,...;

-- 更新符合条件记录的指定字段
update 表名 set 字段名=值,字段名=值,... where 条件;

注意：

  • 列名的类型与修改的值要一致
  • 修改值得时候不能超过最大长度
  • 除了数值类型外，其它的字段类型的值必须使用引号引起

删除表记录

1
delete from 表名 [where 条件];

数据查询语言DQL

基本语法：

1
select [distinct] * | 列名,列名 from 表 where 条件;

简单查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查询所有的商品
select * from product;

-- 查询商品名和商品价格
select pname,price from product;

-- 别名查询，使用关键字as（可省略）给表起别名
select p.pname from product as p;

-- 别名查询，使用关键字as（可省略）给列起别名
select pname as pn from product;

-- 去掉重复值
select distinct price from product;

-- 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
select pname,price+10 from product;

条件查询

运算符与关键字 作用
< <= > >= != <> 小于、小于等于、大于、大于等于、不等于、不等于
BETWEEN …AND… 显示在某一区间的值(含头含尾)
IN(set) 显示在in列表中的值，例：in(100,200)
LIKE 模糊查询，Like语句中，%代表零个或多个任意字符，_代表一个字符，例如：first_name like '_a%';
IS NULL 判断是否为空
AND 多个条件同时成立
OR 多个条件任一成立
NOT 不成立

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname = '花花公子'

-- 查询价格为800商品
SELECT * FROM product WHERE price = 800

-- 查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800
SELECT * FROM product WHERE price <> 800
SELECT * FROM product WHERE NOT(price = 800)

-- 查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

-- 查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000;
SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

-- 查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);

-- 查询含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';

-- 查询以'香'开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';

-- 查询第二个字为'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';

-- 商品没有分类的商品
SELECT * FROM product WHERE category_id IS NULL

-- 查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL

排序查询

通过 order by 语句，可以将查询出的结果进行排序：

1
2
-- ASC 升序 (默认)，DESC 降序
SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

示例：

1
2
3
4
5
6
7
8
-- 根据价格降序排序
SELECT * FROM product ORDER BY price DESC;

-- 在价格降序排序的基础上，以分类降序排序
SELECT * FROM product ORDER BY price DESC,category_id DESC;

-- 显示商品的价格(去重复)，并降序排序
SELECT DISTINCT price FROM product ORDER BY price DESC;

聚合查询

之前的查询都是横向查询，它们都是根据条件一行一行地判断，而使用聚合函数的查询是纵向查询，它对一列的值进行计算，然后返回一个单一的值，另外聚合函数会忽略空值：

  • count：统计指定列不为NULL的记录行数；
  • sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  • max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  • min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  • avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询商品的总条数
SELECT COUNT(*) FROM product; 

-- 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;

-- 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';

-- 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';

-- 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

分组查询

使用 group by 关键字对查询信息进行分组：

1
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 HAVING 分组条件;

分组操作中的 having 子句，是用于在分组后对数据进行过滤的，作用类似于 where 条件，但二者又有区别：

  • having 是在分组后对数据进行过滤，where 是在分组前对数据进行过滤
  • having 后面可以使用分组函数（统计函数），where 后面不可以使用聚合函数

示例：

1
2
3
4
5
-- 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

-- 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

SQL执行顺序

1
2
3
4
5
6
7
8
9
(8) SELECT<fields> (9)DISTINCT<fields>
(1) FROM <t1> (3) <join_type>JOIN<t2>
(2) ON<join_condition>
(4) WHERE<where_condition>
(5) GROUP BY<group_by_condition>
(6) WITH {ROLLUP|CUBE}
(7) HAVING<having_condtion>
(10) ORDER BY<order_by_condition>
(11) LIMIT<limit_number>

所有的查询语句都是从 from 开始执行的，在执行过程中，每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入。在执行多表连接时，MySQL 会根据连接算法（比如 Nested-Loop join）执行嵌套循环连接操作，而不是每次只连接两种表，在对最终结果集没影响的前提下，优先选择结果集最小的那张表作为驱动表。为了便于理解，通常使用“参与 join 的两张表生成笛卡尔积再过滤”来帮助理解 join 语义，实际上并不会这样执行（效率太差）。

  1. 首先对 from t1 join t2 中的前两个表执行一个笛卡尔乘积，此时生成虚拟表 vt1。
  2. 接下来便是应用 on 筛选器，on 中的逻辑表达式将应用到 vt1 中的各个行，筛选出满足 on 逻辑表达式的行，生成虚拟表 vt2。
  3. 如果是 outer join 那么这一步就将添加外部行，left outer jion 就把左表在第二步中过滤掉的行添加进来，如果是 right outer join 那么就将右表在第二步中过滤掉的行添加进来，这样生成虚拟表 vt3 。如果 from 子句中的表数目多余两个表，那么就将 vt3 和第三个表连接从而计算笛卡尔乘积，生成虚拟表，该过程就是一个重复 1~3 的步骤，最终得到一个新的虚拟表 vt3。 （这样说明只是为了便于理解 join 语义，实际执行时会进行优化）
  4. 应用 where 筛选器，对上一步生产的虚拟表引用 where 筛选器，生成虚拟表 vt4。对于包含 outer join 子句的查询，就有一个让人感到困惑的问题，到底在 on 筛选器还是用 where 筛选器指定逻辑表达式呢？on 和 where 的最大区别在于，如果在 on 应用逻辑表达式，那么在第三步 outer join 中还可以把移除的行再次添加回来，而 where 的移除是最终的。
  5. group by 子句将 vt4 按指定字段聚合得到多个分组，生成虚拟表 vt5。应用 group by 后，后面的所有步骤都只能使用 vt5 的列和聚合函数，每个分组保留一行数据。
  6. 应用 rollup 或 cube 选项，对 group by 分组结果进行汇总，为 vt5 生成超组，得到 vt6。 rollup 与 order by 为互斥关键字。
  7. 应用 having 筛选器，生成 vt7。having 是唯一一个应用在已分组数据的筛选器。
  8. 应用 select 子句，用 select 中出现的列对 vt7 进行筛选，生成 vt8。
  9. 应用 distinct 子句，移除 vt8 中相同的行，生成 vt9。如果应用过 group by 子句那么 distinct 就是多余的。
  10. 应用 order by 子句。按照 order_by_condition 排序 vt9，此时返回一个游标，而不是虚拟表。SQL 是基于集合理论的，集合中的行之间没有预先定义的顺序，它只是成员的一种逻辑组合，成员之间的顺序无关紧要。但是对于带有 order by 子句的查询，它返回的是一个对象，其中的行按照顺序组织在一起，这个对象就叫游标。order by 是唯一一个可以使用 select 列表中别名的步骤。
  11. 应用 limit 选项，返回结果给用户。

SQL约束

约束用于限制加入表的数据的类型。可以在创建表时通过 CREATE TABLE 语句规定约束，或者在表创建之后通过 ALTER TABLE 语句添加约束。SQL 约束分为以下几类：

  • PRIMARY KEY：主键约束
  • NOT NULL：非空约束
  • UNIQUE：唯一约束
  • DEFAULT：默认约束
  • FOREIGN KEY：外键约束

主键约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录，相当于唯一约束 + 非空约束：

  • 主键必须包含唯一的值
  • 主键列不能包含 NULL 值
  • 每个表都应该有一个主键，并且每个表只能有一个主键

添加主键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 方式一：创建表时，在字段描述处声明指定字段为主键
CREATE TABLE persons(
  id_p int PRIMARY KEY,
  lastname varchar(255),
  firstname varchar(255),
  address varchar(255),
  city varchar(255)
);

-- 方式二：创建表时，在constraint约束区域声明指定一个/多个字段为主键/联合主键
CREATE TABLE persons(
    firstname varchar(255),
    lastname varchar(255),
    address varchar(255),
    city varchar(255),
    [CONSTRAINT 名称] PRIMARY KEY (firstname,lastname)
);

-- 方式三：创建表之后，通过修改表结构，声明指定字段为主键
ALTER TABLE persons MODIFY id_p int PRIMARY KEY;

-- 方法四：创建表之后，添加主键约束
ALTER TABLE persons ADD [CONSTRAINT 名称] PRIMARY KEY (firstname,lastname);

删除主键约束

1
2
-- 通过修改表结构，删除主键约束。如果有自增要先删除自增
ALTER TABLE persons DROP PRIMARY KEY;

添加自动增长列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建表时，对主键字段设置为自动增长列，默认的起始值为1
CREATE TABLE persons(
    p_id int PRIMARY KEY AUTO_INCREMENT,
    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    city varchar(255)
);

-- 添加数据时，不为p_id字段设置值或设置成null，数据库将自动维护主键值
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates');
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates');

-- 修改自增起始值
ALTER TABLE persons AUTO_INCREMENT=100;

对于 `auto_increment`，使用 `delete from 表名` 和使用 `truncate table 表名` 这两种删除表中所有记录有什么区别？

  • delete：删除表中记录，可以回滚，不会清空 auto_increment 记数
  • truncate：清空表中记录，无法回滚，会将 auto_increment 记数重置为零

非空约束

NOT NULL 约束强制列不接受 NULL 值。如果不向字段添加值，就无法插入新记录或者更新记录。

添加非空约束

1
2
3
4
5
6
7
8
9
10
11
-- 方式一：创建表时，设置非空字段
CREATE TABLE persons(
    id_p int NOT NULL,
    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    city varchar(255)
);

-- 创建表之后，通过修改表结构，声明指定字段非空
ALTER TABLE persons MODIFY lastname varchar(255) NOT NULL;

删除非空约束

1
2
-- 通过修改表结构，删除非空约束
ALTER TABLE persons MODIFY lastname varchar(255);

唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录，为列或列集合提供了唯一性的保证。PRIMARY KEY 拥有自动定义的 UNIQUE 约束，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

添加唯一约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 方式一：创建表时，在字段描述处声明唯一
CREATE TABLE persons(
    id_p int UNIQUE,
    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    city varchar(255)
);

-- 方式二：创建表时，在约束区域声明唯一
CREATE TABLE persons(
    id_p int,
    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    city varchar(255),
    [CONSTRAINT 名称] UNIQUE (id_p)
);

-- 方式三：创建表后，修改表结构，声明字段唯一
ALTER TABLE persons MODIFY id_p int UNIQUE;

-- 方式四：创建表后，添加唯一约束。如果没有设置约束名称，默认为当前字段名
ALTER TABLE persons ADD [CONSTRAINT 名称] UNIQUE (Id_P);

删除唯一约束

1
2
-- 通过修改表结构，删除唯一约束
ALTER TABLE persons DROP INDEX 唯一约束名称;

默认约束

default 约束用于指定字段默认值。当向表中插入记录时，如果没有明确的为字段赋值，则自动赋予默认值。

添加默认约束

1
2
3
4
5
6
7
-- 方式一：创建表时候，在字段描述处添加默认约束
CREATE TABLE t_user(
    user_name varchar(10) DEFAULT '阿卡林'
);

-- 方式二：创建表后，修改表结构，添加默认约束
ALTER TABLE t_user ALTER name SET DEFAULT '阿卡林';

删除默认约束

1
2
-- 通过修改表结构，删除默认约束
ALTER TABLE t_user ALTER user_id DROP DEFAULT;

外键约束

现有分类表 category 和商品表 product，为了表明商品属于哪个分类，我们将在商品表 product 上添加一列 category_id，用于存放分类表中的 cid 信息，此列称为外键。此时分类表 category 称为主表，cid 为主键。商品表 product 称为从表，category_id 称为外键。通过主表的主键和从表的外键来描述主外键关系，呈现一对多关系。

FOREIGN KEY 约束用于保证数据完整性。外键的特点：

  • 从表外键的值是对主表主键的引用
  • 从表外键类型，必须与主表主键类型一致

添加外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建主表
CREATE TABLE category(
    cid int,
    cname varchar(255)
);
-- 方式一：创建表时候，在约束区域声明外键约束
CREATE TABLE product(
    pid int,
    pname varchar(255),
    category_id int,
    CONSTRAINT FK_CATEGORY_ID FOREIGN KEY(category_id) REFERENCES category(cid)
);

-- 方式二：创建表后，添加外键约束
ALTER TABLE product ADD [constraint 名称] FOREIGN KEY (从表外键字段名) REFERENCES 主表 (主表的主键);

删除外键约束

1
2
-- 通过修改表结构，删除外键约束
ALTER TABLE 从表 DROP FOREIGN KEY 外键约束名称;

多表操作

实际开发中，一个项目通常需要很多张表，而且这些表的数据之间存在一定的关系。

表与表之间的关系

一对多关系

常见实例：客户和订单，分类和商品，部门和员工。
一对多建表原则：在从表（多方）创建一个字段，字段作为外键指向主表（一方）的主键。

多对多关系

常见实例：学生和课程、用户和角色。
多对多关系建表原则：需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

一对一关系

在实际的开发中应用不多，因为一对一可以创建成一张表。

一对多

分类表 category 为一方，也就是主表，提供主键 cid。
商品表 products 为多方，也就是从表，提供外键 category_id。

建立一对多关系：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建分类表
create table category(
  cid varchar(32) PRIMARY KEY ,
  cname varchar(100) -- 分类名称
);

-- 创建商品表
CREATE TABLE products (
  pid varchar(32) PRIMARY KEY  ,
  name VARCHAR(40) ,
  price DOUBLE 
);

-- 添加外键字段
alter table products add column category_id varchar(32);
-- 添加外键约束
alter table products add constraint 'product_fk' foreign key (category_id) references category (cid);

操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 向分类表中添加数据
INSERT INTO category (cid ,cname) VALUES('c001','服装');

-- 向商品表添加普通数据，没有外键信息，默认为null
INSERT INTO products (pid,pname) VALUES('p001','商品名称');

-- 向商品表添加普通数据，含有外键信息(category表中存在这条数据)
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名称2','c001');

-- 向商品表添加普通数据，含有外键信息(category表中不存在这条数据) -- 失败，出现异常
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名称2','c999');

-- 删除指定分类(分类被商品使用) -- 执行异常
DELETE FROM category WHERE cid = 'c001';

多对多

商品和订单为多对多关系，拆分成两个一对多：

  • 商品表 products，为其中一个一对多的主表，需要提供主键 pid
  • 订单表 orders，为另一个一对多的主表，需要提供主键 oid
  • 中间表 orderproducts，为专门建立的第三张表，需要提供两个外键 oid 和 pid

建立多对多关系：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 商品表已存在（略去）

-- 创建订单表
create table orders(
  `oid` varchar(32) PRIMARY KEY ,
  `totalprice` double 	#总计
);

-- 创建中间表
create table orderproducts(
  oid varchar(50),-- 订单id
  pid varchar(50)-- 商品id
);

-- 订单表和中间表的主外键关系
alter table orderproducts add constraint 'order_products_fk' foreign key (oid) references orders(oid);

-- 商品表和中间表的主外键关系
alter table orderproducts add constraint order_products_fk foreign key (pid) references products(pid);

-- 创建联合主键（可省略）
alter table orderproducts add primary key (oid,pid);

操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 向商品表中添加数据
INSERT INTO products (pid,pname) VALUES('p003','商品名称');

-- 向订单表中添加数据
INSERT INTO orders (oid ,totalprice) VALUES('x001','998');
INSERT INTO orders (oid ,totalprice) VALUES('x002','100');

-- 向中间表添加数据(数据存在)
INSERT INTO orderproducts(pid,oid) VALUES('p001','x001');
INSERT INTO orderproducts(pid,oid) VALUES('p001','x002');
INSERT INTO orderproducts(pid,oid) VALUES('p002','x002');

-- 删除中间表的数据
DELETE FROM orderproducts WHERE pid='p002' AND oid = 'x002';

-- 向中间表添加数据(数据不存在) -- 执行异常
INSERT INTO orderproducts(pid,oid) VALUES('p002','x003');

-- 删除商品表的数据(商品被中间表使用) -- 执行异常
DELETE FROM products WHERE pid = 'p001';

多表查询

准备数据

创建表并插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 分类表
CREATE TABLE category (
  cid VARCHAR(32) PRIMARY KEY ,
  cname VARCHAR(50)
);

-- 商品表
CREATE TABLE products(
  pid VARCHAR(32) PRIMARY KEY ,
  pname VARCHAR(50),
  price INT,
  flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
  category_id VARCHAR(32),
  CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);

-- 插入数据到分类表
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');

-- 插入数据到商品表
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');

多表查询

  1. 交叉连接查询（得到的是两个表的笛卡尔积，存在脏数据）
  • 语法：select * from A,B;
  1. 内连接查询（避免笛卡尔积）
  • 隐式内连接：select * from A,B where 条件;
  • 显式内连接：select * from A [inner] join B on 条件;，inner 可省略
1
2
3
4
5
6
7
8
9
10
11
-- 查询哪些分类的商品已经上架
-- 隐式内连接
SELECT DISTINCT c.cname 
  FROM category c , products p 
  WHERE c.cid = p.category_id AND p.flag = '1';

-- 显式内连接
SELECT DISTINCT c.cname 
  FROM category c INNER JOIN products p 
  ON c.cid = p.category_id 
  WHERE p.flag = '1';
  1. 外连接查询（以一侧为基底，返回表中所有行，如果另一侧没有匹配的行，其提供的字段返回 NULL）
  • 左外连接：select * from A left [outer] join B on 条件;，outer 可以省略
  • 右外连接：select * from A right [outer] join B on 条件;，outer 可以省略
1
2
3
4
5
6
-- 查询所有分类商品的个数
SELECT cname,COUNT(category_id) 
  FROM category c 
  LEFT OUTER JOIN products p 
  ON c.cid = p.category_id 
  GROUP BY cname;

内外连接有什么区别？

连接查询

子查询

一条 select 语句的结果作为另一条 select 语句的一部分（查询条件、查询结果、表等）。

语法：

1
select ....查询字段 ... from ... 表.. where ... 查询条件

示例：

1
2
3
4
5
6
7
8
9
10
-- 查询“化妆品”分类上架商品详情
-- 隐式内连接
SELECT p.* FROM products p , category c 
WHERE p.category_id=c.cid AND c.cname = '化妆品';
-- 子查询作为查询条件
SELECT * FROM products p WHERE p.category_id = 
(SELECT c.cid FROM category c WHERE c.cname='化妆品');
-- 子查询作为另一张表
SELECT * FROM products p , (SELECT cid FROM category WHERE cname='化妆品') c 
WHERE p.category_id = c.cid;

函数

//TODO

参考资料

w3schools - SQL Tutorial
关于SQL和MySQL的语句执行顺序
MySQL 查询语句执行顺序疑问：多表关联时会先生成笛卡尔乘积？
嵌套循环连接算法

  • 本文作者： SANNAHA
  • 本文链接： https://sannaha.moe/MySQL/
  • 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！