0、常用的数据库操作方法（都要掌握）

（1）可视化工具

如：SQLyog、Navicat等。

（2）SQL语句

既可以在命令行窗口中运行，也可以在SQLyog的询问窗口中使用运行。

1、基本命令（初识MySQL）

1.1、连接数据库

命令格式：

1	mysql -h主机地址 -u用户名 -p密码 --连接数据库

注：

主机地址和用户名前既可以加空格也可以不加空格，但是密码前必须没有空格。
进入到MYSQL中后，命令行的提示符会变为： mysql>

1.2、修改用户密码

（1）**方法一：**使用mysqladmin命令，命令格式：

1	mysqladmin -u 用户名 -p 旧密码 password 新密码

（2）**方法二：**使用SQL语句-，命令格式：

1	update mysql.user set password=PASSWORD(‘新密码’) where User=’root’ and Host = 'localhost'; --SQL语句

注：

SQL语句可以换行；
SQL语句都以分号结尾。

1.3、刷新权限

命令格式：

1	flush privileges;

**注：**mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问。当然，我们也可以重启mysql服务器，来使新设置生效。

1.4、显示所有数据库

命令格式：

1	show databases; --注意：最后有个s

显示所有数据库

1.5、使用数据库（切换至某一数据库）

命令格式：

use 数据库名称

使用数据库

1.6、查看某一数据库中所有的表

命令格式：

1	show tables;

查看数据库中所有的表

1.7、查看某一表中的信息（字段）

命令格式：

1	describe 表名;

查看表中的信息

1.8、创建一个数据库

命令格式：

1	create database 数据库名称;

创建数据库

1.9、退出数据库

命令格式：

exit

退出数据库

2、DDL、DML、DQL、DCL

SQL语言共分为四大类：数据定义语言DDL（Data Definition Language）、数据操纵语言DML（Data Manipulation Language）、数据查询语言DQL（Data Query Language）、数据控制语言DCL（Data Control Language）。

**注：**我们学习数据库，本质上就是学习这四类SQL语言。

3、操作数据库

接下来，我们将通过在可视化工具SQLyog的询问窗口中执行SQL语句或使用可视化工具SQLyog直接操作的方法演示操作数据库的方法。其它数据库操作方法（如：在命令行窗口中执行SQL语句等）以此类推，这里不再赘述。

3.1、操作数据库

3.1.1、创建数据库

命令格式：

1	CREATE DATABASE [IF NOT EXISTS] 数据库名称;

注：

MySQL的关键字不区分大小写，只是SQLyog软件会自动将SQL关键字大写，并可通过Tab键自动补全。
[ ]表示其中的SQL语句可省略；{ }表示其中的SQL语句不可省略。
若数据库名称、表名或字段名为关键字，则需使用转义字符(``)进行转义。
若创建数据库的名称与已有数据库同名，则创建该数据库时会出错。

因此，为了防止数据库同名，建议不要省略[ ]中的SQL语句。

3.1.2、删除数据库

命令格式：

1	DROP DATABASE [IF EXISTS] 数据库名称;

3.1.3、使用数据库

命令格式：

1	USE 数据库名称;

3.1.4、查看全部数据库

命令：

1	SHOW DATABASES;

查询所有数据库

3.2、操作数据库中的表

3.2.1、创建表

（1）使用SQL语句创建表

命令格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名称` 字段类型 [其它字段属性] [索引] [注释],
    `字段名称` 字段类型 [其它字段属性] [索引] [注释],
    ……
    `字段名称` 字段类型 [其它字段属性] [索引] [注释],
	PRIMARY KEY(`字段名称`)  -- 设置主键
)[表的类型（引擎）][字符集设置][注释]

注：

所有的字段和字段属性都写在英文括号内。
表的名称和字段名称最好使用转义字符(``)进行转义，以防止与关键字重名。
除最后一条语句外，其它所有语句都以英文逗号(,)结尾。
同一条语句内部各关键词都使用空格隔开。
所有的字符串都使用单引号(‘’)括起来。
主键一般在最后设置。

如：在school数据库中创建一个学生表。

CREATE TABLE IF NOT EXISTS `student`(
  -- 字段：id，数据类型：int(4)，其它字段属性：不为空、自增、注释
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',		
  -- 字段：name，数据类型：varchar(30)，其它字段属性：不为空、默认值、注释
  `name` VARCHAR(30) NOT NULL DEFAULT '张三' COMMENT '姓名',
  -- 字段：password，数据类型：varchar(20)，其它字段属性：不为空、默认值、注释	
  `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  -- 字段：sex，数据类型：varchar(2)，其它字段属性：不为空、默认值、注释
  `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
  -- 字段：birthday，数据类型：datetime，其它字段属性：不为空、默认值、注释		
  `birthday` DATETIME DEFAULT NULL COMMENT '生日',	
  -- 字段：address，数据类型：varchar(100)，其它字段属性：不为空、默认值、注释	
  `address` VARCHAR(100) NOT NULL DEFAULT '无' COMMENT '地址',	
PRIMARY KEY(`id`)  -- 设置主键
)ENGINE = INNODB DEFAULT CHARSET = utf8  -- 设置引擎和字符集

（2）使用可视化工具（SQLyog）创建表

选中数据库的表，右键创建新表。
设置表名、引擎、字符集和核对方式。
确定各列数据（字段）的属性。

新建表

附：a、数据库的字段属性（列属性）（重点）

在数据库中创建表时需要确定各列数据（字段）的属性，又称“列属性”或“字段属性”。

列名

即该列的名称。
数据类型

即该字段的数据类型（详见b部分）。
长度

即该字段的长度，单位：字节。
- 字段的长度应设置为保证正常使⽤需求下的最⼩长度。
- 如果不指定字段的长度，那么数据库在存储的时候都必须给每个字段预留最⼤的存储空间，这样极⼤的浪费了空间，也增加了数据库的管理难度。
默认

即该字段的默认值。
主键？

主键（primary key）：数据库表中每行数据的唯一标识。
- 每一行数据都必须具有一个主键值，且主键值不允许为NULL。
- 任何两行数据的的主键值都不相同；
非空？

声明该字段是否为not null。
- 当某一字段设置为非空时，若该字段未被赋值，则会报错。
- 当某一字段为设置非空时，若该字段未被赋值，则默认值为null。
Usigned？

声明未占满的位是否为无符号的数，即：是否为非负数。
自增？

声明该字段是否自动在上一条记录的基础上一定数值（默认情况下自动+1）。
- 常用来设置唯一的主键；
- 可高级选项中自定义自增的起始值和步长，但一般很少使用。
Zerofill？

声明该字段是否使用零填充。
更新

插入时自动更新。
注释

用于添加该字段的注释。

b、数据库的字段类型（列类型）

在数据库中创建表时需要确定各列数据（字段）的数据类型，又称“列类型”或“字段类型”。

数据库的数据类型主要指的就是列类型。
选择适当的列类型可以提升SQL语句的性能、提升空间使用率、提升IO一次性读取的数量。

常见的MySQL的列类型主要包括：

数值
- tinyint：十分小的整数，占1个字节。
- smallint：较小的整数，占2个字节。
- mediumint：中等大小的整数，占3个字节。
- int：标准的整数，占4个字节（常用）。
- bigint：较大的整数，占8个字节。
- float：单精度浮点型，占4个字节。
- double：双精度浮点型，占8个字节。
- decimal：字符串形式的浮点数（一般用于金融领域的计算）。
字符串
- char：定长字符串，占0~255字节。
- **varchar：变长字符串，占0~65535字节（常用），**对应Java中的String类型，一般用于保存变量。
- tinytext：微型文本数据，占0~255字节。
- **text：文本数据，占0~65535字节（常用），**对应Java中的String类型，一般用于保存文本。
时间日期
- date：日期类型，显示格式为YYYY-MM-DD。
- time：时间类型，显示格式为HH:MM:SS。
- datetime：日期时间类型（最常用的时间格式），显示格式为YYYY-MM-DD HH:MM:SS。
- timestamp：时间戳（也较为常用），1970年1月1日到现在的秒数，显示格式为YYYY-MM-DD HH:MM:SS。
- year：年份类型，显示格式为YYYY。

注：

为了保证数据库的健壮性和安全性，阿里巴巴规范中要求每一个表都必须要有以下五个字段：

/******************
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
******************/

c.数据库引擎（表的类型）

数据库引擎是用于存储、处理和保护数据的核心服务。我们现在通用的大多数数据库的核心服务都是数据库引擎，比如MySQL，Access，MySQL，但是Oracle数据库没有引擎的概念。
数据库引擎的实质就是底层的软件组织，是用户操作数据的接口。

我们知道计算机上的应用程序与应用程序之间，应用程序与硬件之间有许多驱动程序，用以数据通信。其实数据库引擎也相当于数据库管理系统的驱动，同样遵循OPC通信标准。

数据库中的数据以各种文件的形式存放在电脑硬件磁盘上，当我们不管通过何种方式想访问数据时（可以是通过数据库管理系统访问数据，也可以是通过应用程序访问数据），都需要先经过数据库引擎，由数据库引擎先将sql语句转化为对数据库的操作，然后再将结果返回给用户。

在数据库管理系统中直接写sql语句即可调用数据库引擎获取数据访问及操作。在应用程序中会根据应用程序开发语言的不同而有所不同，比如Java语言开发的应用程序用JDBC就是在调用数据库引擎。

**MySQL 5.5之前的默认引擎是MyIASM，之后变成了InnoDB。**MyIASM和InnoDB的主要区别如下：

	MyIASM	InnoDB
事务支持	不支持	支持
锁的方式	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁定某一行数据，对其它行没有影响，适合高并发的操作
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为MyIASM的2倍
缓存	只缓存索引，不缓存真实数据	布局缓存索引，也缓存真实数据，对内存要求较高，且内存大小对性能有决定性影响
在磁盘上存储的物理文件	（1）`.frm`文件（2）上级目录下的`.ibd` 文件或 `*.ibdata` 文件	（1）`.frm`文件（2）`.MYD` 文件（3）`*.MYI` 文件
优点/适应场景	节约空间、速度较快	安全性高、支持事务处理、可多表多用户操作

**注：**数据库本质上还是文件存储，多有的数据文件都默认存储在MySQL安装目录的data文件夹下，一个数据库就对应一个文件夹。

InnoDB在磁盘上将我们建的数据库存储成两个文件。（1）*.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等；（2）上级目录下的*.ibd 文件或 *.ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。独享表空间存储方式使用.ibd文件，并且每个表对应一个.ibd文件；共享表空间存储方式使用.ibdata文件，所有表共同使用一个或多个.ibdata文件（可自己配置）。
MyIASM在磁盘上将我们建的数据库存储成三个文件。（1）*.frm文件：用来存储 表结构的定义信息；（2）*.MYD (MYData)文件：用来存储表的数据；（3）*.MYI (MYIndex)文件：用来存储 表的索引。

d.字符集

MySQL默认的字符集不支持中文。因此，为了保证数据安全，建议在创建表的时候设置字符集为utf8编码。

(3)常用命令

1
2
3

SHOW CREATE DATABASE 数据库名称   -- 查看创建数据库的语句
SHOW CREATE TABLE 表名     -- 查看创建数据库中表的语句
DESC 表名                  -- 查看表的结构（describe）

3.2.2、修改表

3.2.2.1、修改表名

命令格式：

1	ALTER TABLE 旧表名 RENAME AS 新表名;

3.2.2.2、增加表的字段

命令格式：

1	ALTER TABLE 表名 ADD 字段名字段类型 [其它字段属性];

3.2.2.3、字段重命名

命令格式：

1	ALTER TABLE 表名 CHANGE 旧字段名新字段名字段类型 [其它字段属性];

3.2.2.4、修改约束（字段属性）

命令格式：

1	ALTER TABLE 表名 MODIFY 字段名字段类型 [其它字段属性];

3.2.2.5、删除表的字段

命令格式：

1	ALTER TABLE 表名 DROP 字段名;

3.2.3、删除表

命令格式：

1	DROP TABLE [IF EXISTS] 表名;

注：

为了安全，避免重复，建议所以名称都用反引号(``)括起来。
sql语句对字母大小写不敏感，建议大家小写。

3.3、操作数据库表中的数据（MySQL数据管理）

3.3.1、外键

（1）什么是外键（foreign key）

已知两个表（表1和表2），如果字段a既是表1的一个字段（一般不是主键），又是表2的主键，那么我们就称字段a为表1的外键。此时，表1依赖于表2而存在，因此我们也称表1为从表，表2为主表。

例如：已知一个student表和一个grade表。student表的字段包括：序号、姓名、性别、年龄、年级；grade表专门用来存放年级，其字段包括：序号、年级、备注。若student表的年级信息需要去grade表中去查询，则我们可以将student表中的年级字段设置为student表的外键，指向grade表。

什么是外键

（2）外键的作用

外键的作用就是将两个表关联起来。

（3）代码实现

先创建一个student表。

CREATE TABLE `student` (
   `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
   `name` VARCHAR(10) NOT NULL DEFAULT '张三' COMMENT '姓名',
   `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
   `age` INT(4) NOT NULL DEFAULT '0' COMMENT '年龄',
   `grade` INT(4) NOT NULL DEFAULT '0' COMMENT '年级',
   PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

再创建一个grade表。

CREATE TABLE `grade`(
  `gradeId` INT(4) NOT NULL AUTO_INCREMENT COMMENT '年级序号',
  `gradeName` VARCHAR(10) NOT NULL COMMENT '年级名称',
  `comment` VARCHAR(20) COMMENT '备注',
  PRIMARY KEY(`gradeId`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

此时两张表为相互独立的两个表格，运行结果如下图所示：

接下来，我们便可以通过修改student表，采用为其**添加一个外键约束（CONSTRAINT）的方式，将student表中的grade字段与grade表关联起来。即：让student表的grade字段去引用（references）**grade表的gradeId字段。

命令格式：

1	ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(从表中指定为外键的字段名) REFERENCES 主表名(主表的主键名);

如：

1	ALTER TABLE `student` ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`grade`) REFERENCES `grade`(`gradeId`);

此时两张表为便通过外键FK_gradeId关联起来了，运行结果如下图所示：

注：

当一个表添加了外键时，若从表未删除，则主表不能删除；若要删除主表，则应先删除从表。
以上的操作都是物理外键，即数据库级别的外键。为了避免数据库内部各表产生错综复杂的引用（依赖）关系，实际项目中我们不建议使用。
实际项目中，数据库中存放的就是单纯的表，只用来保存数据，只有行（数据）和列（字段）。
实际项目中，若想使用多张表的数据，阿里巴巴编程规范要求必须使用应用层（Java）代码来实现外键的功能。
因此，外键我们只需了解即可。

3.3.2、DML（重点）

DML（Data Manipulation Language）是数据操纵语言，主要包括：添加数据（INSERT）、修改数据（UPDATE）和删除数据（DELETE）。

3.3.2.1、添加数据

命令格式：

1	INSERT INTO 表名[(字段名1,字段名2，……)] VALUES(字段1的值,字段2的值，……)[,(字段1的值,字段2的值，……),……];

注：

因为主键自增，所以我们可以省略主键。
字段和字段之间使用英文逗号隔开。
字段名可以省略，但是后面的值必须要与表中所有字段（包括主键）一一对应。
可以同时插入多条数据，即：values(),(),.....
若一条SQL语句太长，则可分多行写。

如：

INSERT INTO `grade`(`gradeName`,`comment`) VALUES('一年级','小学');
INSERT INTO `grade` (`gradeName`,`comment`) VALUES
('二年级','小学'),('三年级','小学'),('四年级','小学'),('五年级','小学'),('六年级','小学'),
('初一','初中'),('初二','初中'),('初三','初中'),
('高一','高中'),('高二','高中'),('高三','高中');

执行结果如下：

添加数据

**注：**可视化工具最后一行是创建表时的默认值，不是我们添加的数据。

3.3.2.2、修改数据

命令格式：

1	UPDATE 表名 SET 字段名1 = 字段1的新值[, 字段名2 = 字段2的新值,……] where 修改条件

注：

各字段的新值既可以是一个具体的值，也可以是一个变量。
修改条件既可以是单个条件，也可以是复合条件。
如果不指定修改条件，那么表中的每一行数据都将会被修改。因此，为了数据安全，实际项目中一般是不允许不指定修改条件的。

如：修改数据前student表的数据如下图所示。

数据修改前

使用修改数据命令将id=1的那一行数据的姓名改为阿汤。

1	UPDATE `student` SET `name` = '阿汤' WHERE id = 1;

执行结果为：

数据修改后

附：where条件子句

使用格式：

where 表达式

**作用：**检索数据中符合条件的值。

where条件子句中常用的操作符（逻辑运算符）主要有：

操作符	含义
=	等于
<> 或 !=	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!<	不小于
!>	不大于
between A and B	在闭区间[A,B]内
and 或 &&	与
or 或 \|\|	或
not 或！	非
is null	为空

3.3.2.3、删除数据

命令格式：

1	DELETE FROM 表名 WHERE 删除条件;

注：

删除条件既可以是单个条件，也可以是复合条件。
如果不指定修改条件，那么表中的每一行数据都将会被删除。因此，为了数据安全，实际项目中一般是不允许不指定删除条件的。

如：删除数据前student表的数据如下图所示。

数据删除前

使用删除数据命令将id=4的那一行数据。

1	DELETE FROM `student` WHERE id = 4

执行结果为：

数据删除后

附：truncate命令

我们可以使用truncate命令清空一个数据库中的表，表的结构和索引约束不产生任何变化。命令格式：

1	TRUNCATE 表名;

注：DELETE FROM 表名与TRUNCATE 表名的相同点是：都能将数据库中一个表的数据全部删除，且不会删除表的结构。

这两条语句的区别在于：

truncate命令会重新设置自增列，自动增量（计数器）会归零。
truncate命令不会影响事务。

因此，我们一般使用truncate命令清空一个数据库中的表。

4、DQL（重中之重）

DQL（Data Query Language）是数据查询语言。DQL之所以这么重要是因为：

它既是数据库中最核心的语言，又是实际项目中使用频率最高的语言。
所有的查询操作都用select关键字；它既能进行简单的查询，又能进行复杂的查询。

4.0、查询表达式

MySQL可以通过select语句查询相应的表达式。数据库中的表达式可以是：列名、文本、函数、计算表达式、系统变量、null等。

命令格式：

1	select 表达式 [from 表名];

注：

SQL中所有的查询语句都以select关键字开始。
随着学习的不断深入，我们会逐步扩充要查询的表达式的内容。

例1：查询文本

1	SELECT '阿汤笔迹';

例2：使用函数查询系统的版本

1	SELECT VERSION();

例3：查询计算表达式

1	SELECT 100*8+2/3;

例4：查询系统变量

1	SELECT @@auto_increment_increment; -- 自增步长

4.1、查询某一个或几个字段（列）

命令格式：

1	SELECT 列名1[,列名2,……] FROM 表名;

例：已知student表的数据如下图所示

查询_student表

执行查询语句：

1	SELECT `name` FROM `student`;

结果为：

查询某一列

执行查询语句：

1	SELECT `name`,`id` FROM `student`;

结果为：

查询多列

4.1.1、as别名

为了让查询结果的字段名称含义更一目了然，我们可以给要查询的字段起一个别名。
我们既可以给字段起别名，也可以给表起别名。

命令格式：

1	SELECT 列名1 AS 别名1[,列名2 AS 别名1,……] FROM 表名 [AS 表的别名];

**注：**关键字as可以省略。

例：针对上述student表，执行查询语句

1	SELECT `name` AS 姓名,`id` AS 编号 FROM `student`;

结果为：

查询结果别名

4.1.2、concat拼接

我们可以使用concat函数将查询结果与字符串拼接起来。

函数格式：

1	concat (str1,str2,……)

例：针对上述student表，执行查询语句

1	SELECT CONCAT('这位同学是：',`name`) AS 姓名 FROM `student`;

结果为：

查询结果拼接字符串

4.2、查询所有字段（列）

命令格式：

1	SELECT * FROM 表名;

**注：**星号*为通配符。

例：针对上述student表，执行查询语句：

1	SELECT * FROM `student`;

结果为：

查询所有列

4.3、distinct去重

命令格式：

1	SELECT DISTINCT 字段名 FROM 表名;

**注：**关键字distinct表示去掉查询结果中重复的行。

例：已知学生的考试成绩（test_result）表的数据如下图所示

查询考试成绩表

若想查询参加考试的学生名单，则可通过先查询test_result表的name字段，然后再去重来实现。即执行查询语句：

1	SELECT DISTINCT `name` FROM `test_result`;

结果为：

查询去重

4.4、where子句过滤

我们同样可以使用where条件子句对查询结果进行过滤。

命令格式：

1	select 表达式 from 表名 where 表达式;

例：针对上述test_result表，查询考试成绩在80~100之间的结果。执行查询语句：

1	SELECT `name`,`course`,`score` FROM `test_result` WHERE `score` BETWEEN 80 AND 100;

结果为：

查询结果where子句过滤

4.5、模糊查询

模糊查询的本质是一些比较运算符。

4.5.1、常用的比较运算符

常用的比较运算符主要有：

运算符	用法	含义
IS NULL	a is null	若a的值为NULL，则结果为true
IS NOT NULL	a is not null	若a的值不为NULL，则结果为true
BETWEEN……AND	a is between b and c	若在a的值在闭区间[B,C]内，则结果为true
LIKE	a like b	SQL匹配，若a能够匹配到b，则结果为true
IN	a in (a1,a2,a3,……)	若a是a1,a2,a3,……中的一个，则结果为true

4.5.2、LIKE运算符（重点）

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名 WHERE 字段名i LIKE '含%或_的表达式';

like操作符需要配合通配符%或_使用。其中：

通配符%表示：任意（0~+∞）个字符。
通配符_表示：一个字符。

例1：已知新的student表的数据如下图所示

模糊查询

若想查询所有姓李的同学的名单和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`address` FROM `student` WHERE `name` LIKE '李%'; -- 匹配“李”后面有任意个字符的同学

执行结果为：

模糊查询所有结果

例2：针对上述student表，若想查询所有姓李且姓名只有两个字的同学的的名单和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`address` FROM `student` WHERE `name` LIKE '李_'; -- 匹配“李”后面有1个字符的同学

执行结果为：

模糊查询结果_

例3：针对上述student表，若想查询所有姓李且姓名只有三个字的同学的的名单和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`address` FROM `student` WHERE `name` LIKE '李__'; -- 匹配“李”后面有2个字符的同学

执行结果为：

模糊查询__

例4：针对上述student表，若想查询所有姓名中含“三”的同学的的名单和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`address` FROM `student` WHERE `name` LIKE '%三%'; -- 匹配”三“前、后都有任意个字符的同学

执行结果为：

模糊查询前后都

4.5.3、IN运算符

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名 WHERE 字段名i IN (值1,值2,……);

注：括号中为具体的值。

例4：针对上述student表，若想查询"张三"、“李四”、“王二”这三位同学的的名单和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`address` FROM `student` WHERE `name` IN ('张三','李四','王二');

执行结果为：

模糊查询in

4.5.4、IS NULL运算符

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名 WHERE 字段名i IS NULL;

例：针对上述student表，若想查询出生年月为NULL的同学的名单、生日和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`birthday`,`address` FROM `student` WHERE `birthday` IS NULL;

执行结果为：

查询isNull

4.5.5、IS NOT NULL运算符

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名 WHERE 字段名i IS NOT NULL;

例：针对上述student表，若想查询出生年月不为NULL的同学的名单、生日和地址，则可通过下面的SQL语句来实现：

1	SELECT `name`,`birthday`,`address` FROM `student` WHERE `birthday` IS NOT NULL;

执行结果为：

查询isNotNull

4.6、两表联表查询（重难点）

实际项目中，对同一事物的不同描述经常存储在不同的表中。因此，我们要同时查询关于该事物不同表中的属性，就必须将这些表关联起来，并称之为联结。

两个表联结的本质就是：先将第一个表的每一行与第二个表的每一行配对，得到“笛卡尔积”；然后再根据级联条件去除不符合逻辑的数据。

附：笛卡尔积

由没有联结条件的表关系返回的结果为笛卡尔积。
笛卡尔积检索出的行数 = 第一个表的行数 × 第二个表的行数。

4.6.1、内部联结（等值联结）

联结的创建其实非常简单，只需规定要联结的所有表及它们如何关联即可。

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名1,表名2 WHERE 联结条件;

注：

不同表中同时出现的字段名，必须用表名.字段名的方式明确它是哪个表中的字段，否则会报字段含义不明确的错误。
联结条件一般为：表名1.字段名m = 表名2.字段名n。

例：已知student表和test_result表。

其中，student表的数据如下图所示：

模糊查询

test_result表的数据如下图所示：

testResult表

若想同时查询所有同学的的学号、姓名、课程、成绩、生日和地址，则必须将这些表关联起来，通过创建联结来实现：

1 2	SELECT `studentId`,student.name,`course`,`score`,`birthday`,`address` FROM `student`,`test_result` WHERE student.name = test_result.name; -- 等值联结

执行结果为：

创建联结

注：

这里用的联结是基于两个表之间的相等测试，称为等值联结（equijoin）,这种联结也称为内部联结（inner join）。
等值联结与内部联结的执行结果相同，只是语法不同，ANSI SQL规范建议首先内部联结（inner join）。

**内部联结（inner join）**的命令格式为：

1	SELECT 字段名1[,字段名2,……] FROM 表名1 INNER JOIN 表名2 ON 联结条件;

注：

同样地，不同表中同时出现的字段名，必须用表名.字段名的方式明确它是哪个表中的字段，否则会报字段含义不明确的错误。
联结条件一般为：表名1.字段名m = 表名2.字段名n。
inner join会先将表1的每一行与表2的每一行配对，得到“笛卡尔积”；然后再根据级联条件去除不符合逻辑的数据。即：返回两个表共有的数据。如下图所示：

innerjoin

例：针对上述student表和test_result表，若采用内部联结的方式将两个表联结起来，执行如下SQL语句：

1	SELECT `studentId`,student.name,`course`,`score`,`birthday`,`address` FROM `student` INNER JOIN `test_result` ON student.name = test_result.name; -- 内部联结

则执行结果为：

内部联结

由此可见：等值联结与内部联结的执行结果相同，只是语法不同。

4.6.2、左联结（left join）

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名1 LEFT JOIN 表名2 ON 联结条件;

注：

同样地，不同表中同时出现的字段名，必须用表名.字段名的方式明确它是哪个表中的字段，否则会报字段含义不明确的错误。
联结条件一般为：表名1.字段名m = 表名2.字段名n。
表1为左表，表2为右表。
left join会返回所有从左表中匹配到的值，即使右表中没有匹配到数据。如下图所示：

leftjoin

右表若未匹配到数据，则用null填充。

例：针对上述student表和test_result表，若采用左联结的方式将两个表联结起来，执行如下SQL语句：

1
2

SELECT `studentId`,student.name,`course`,`score`,`birthday`,`address`
FROM `student` LEFT JOIN `test_result` ON student.name = test_result.name   -- 左联结（表student为左表，表test_result为右表）

则执行结果为：

左联结

附：应用举例

针对上述student表和test_result表，如果我们想查询所有缺考同学的信息查出来，则可通过以下SQL语句来实现：

1
2
3

SELECT `studentId`,student.name,`course`,`score`,`birthday`,`address` 
FROM `student` LEFT JOIN `test_result` ON student.name = test_result.name 
WHERE score IS NULL   -- 左联结（表student为左表，表test_result为右表）

执行结果为：

左联结应用

4.6.3、右联结（right join）

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名1 RIGHT JOIN 表名2 ON 联结条件;

注：

同样地，不同表中同时出现的字段名，必须用表名.字段名的方式明确它是哪个表中的字段，否则会报字段含义不明确的错误。
联结条件一般为：表名1.字段名m = 表名2.字段名n。
表1为左表，表2为右表。
right join会返回所有从右表中匹配到的值，即使左表中没有匹配到数据。如下图所示：

rightjoin

左表若未匹配到数据，则用null填充。

例：针对上述student表和test_result表，若采用右联结的方式将两个表联结起来，执行如下SQL语句：

SELECT `studentId`,student.name,`course`,`score`,`birthday`,`address` FROM `student` RIGHT JOIN `test_result` ON student.name = test_result.name   -- 右联结（表student为左表，表test_result为右表）

则执行结果为：

右联表

附：总结

联结方式	查询结果
Inner join	当两个表中都匹配到数据时，才返回该行。
left join	会返回所有从左表中匹配到的值，即使右表中没有匹配到数据。（右表若未匹配到数据，则用null填充）
right jion	会返回所有从右表中匹配到的值，即使左表中没有匹配到数据。（左表若未匹配到数据，则用null填充）

4.7、多表联结查询

方式一：联表查询

命令格式：

SELECT 字段名1[,字段名2,……] FROM 表名1
INNER（或 LEFT 或 RIGHT） JOIN 表名2 ON 联结条件1 
INNER（或 LEFT 或 RIGHT） JOIN 表名3 ON 联结条件2
……
INNER（或 LEFT 或 RIGHT） JOIN 表名n ON 联结条件n-1

方式二：等值查询

命令格式：

1	SELECT 字段名1[,字段名2,……] FROM 表名1,表名3[,表名3,……] WHERE 联结条件1 AND 联结条件2 [AND 联结条件3 ……];

**注：**虽然SQL本身对每个联结约束中表的数量没有限制，但实际上许多DBMS都有限制，具体限制个数参见官方DBMS文档。

4.8、自联结

所谓“自联结”即：一个表自己与自己联结。

**注：**需要自联结的表常是用于描述树形结构的表。

命令格式：

1
2
3

SELECT 表别名1.字段1名称 AS 字段1别名, 表别名2.字段2名称 AS 字段2别名
FROM 表名 AS 表别名1, 表名 AS 表别名2
WHERE 自联结条件

例：已知已知category表，表中各字段分别表示：分类ID、父ID和分类名称。表中数据如下图所示：

自联结示例

若想查询所有的分类名称及其父类名称，则可通过下面的SQL语句来实现：

1
2
3

SELECT tab1.`categoryName` AS '父类名称', tab2.`categoryName` AS '子类名称'
FROM `category` AS tab1, `category` AS tab2
WHERE tab1.`categoryId` = tab2.`parentId`

执行结果为：

自联结示例查询结果

4.9、order by子句排序

命令格式：

1	select 表达式 from 表名 ORDER BY 字段名1 排序方式1[,字段名2 排序方式2,……] -- 在select语句中使用 order by子句

注：

排序方式只有：**升序（ASC）和降序（DESC）**两种。
若不指定排序方式，则MySQL默认按升序排序。
数据库查询结果既可以一个字段排序，也可以按多个字段排序。如果按多字段排序，那么每个字段都应指定相应的排序方式。若不指定，则该字段默认按升序排序。

例：已知学生的考试成绩test_result表，如下图所示：

排序示例

1）若要将所有同学的所有考试成绩按降序排列，则可通过如下SQL语句实现：

1	SELECT `studentId`,`name`,`course`,`score` FROM `test_result` ORDER BY `score` DESC;

执行结果为：

排序示例结果1

2）若要将所有同学的语文考试成绩按降序排列，则可通过如下SQL语句实现：

1	SELECT `studentId`,`name`,`course`,`score` FROM `test_result` WHERE `course` = '语文' ORDER BY `score` DESC;

排序示例结果2

注：ORDER BY必须在WHERE之后，因为要先筛选出符合要求的结果，然后再排序。

4.10、limit子句分页

数据分页可以缓解数据库压力，增强用户体验。

命令格式：

1	select 表达式 from 表名 limit 起始位置,页面大小;

注：

起始位置从0开始计数。
若页面大小为n，则第m页的起始位置为：(m-1)×n。

例：已知学生的考试成绩test_result表，如下图所示：

排序示例

若要从第3条数据开始显示，且显示5条数据，则可通过如下SQL语句实现：

1	SELECT `studentId`,`name`,`course`,`score` FROM `test_result` LIMIT 2,5;

执行结果为：

排序示例

4.11、子查询

所谓“子查询”，即：嵌套在其它查询语句的查询语句。子查询最常见的用法是在where子句的in操作符中使用。

注：

当有子查询时，SQL语句先执行子查询，再执行外部查询；
作为子查询的select语句只能查询单列，若查询多列系统会报错；
子查询的结果往往是一个集合，所以谓词in是嵌套查询中最经常使用的谓词。
子查询语句中不要以分号（;）结尾。

附：在where子句的in操作符中使用子查询

命令格式：

SELECT 字段1[,字段2,……] FROM 表名1 
WHERE 表1中字段名i IN (
      SELECT 表2中字段名m FROM 表名2 WHERE 过滤条件  -- 先执行子查询，再执行外部查询
);

例：已知学生student表和考试成绩test_result表，其中，student表的数据如下图所示：

子查询示例学生表

test_result表的数据如下图所示：

子查询示例成绩表

如果我们查询所有男生的全部考试成绩，则可通过以下SQL语句实现：

SELECT `name`,`course`,`score` FROM `test_result` 
WHERE `name` IN (
      SELECT `name` FROM `student` WHERE `sex` = '男'  -- 先执行子查询，再执行外部查询
);

执行结果为：

子查询示例1结果

4.12、嵌套查询

在SQL语言中，一个SELECT-FROM-WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的 WHERE子句或 HAVING短语的条件中的查询称为嵌套查询。外层的查询称为父查询（主查询），内层的查询称为子查询（从查询）。

注：

SQL语言允许多层嵌套查询，即一个子查询中还可以嵌套其他子查询。
嵌套查询的⼯作⽅式是由内向外的，即先进行内层查询，再进行外层查询。
外层查询则利⽤内层查询的结果集作为条件进⾏查询。
嵌套查询一般也可以通过联表查询的方式来实现，大家可根据个人习惯灵活选择查询方法。

例：已知学生student表、学校school表和考试成绩test_result表，其中，student表的数据如下图所示：

嵌套查询示例学生信息表

school表的数据如下图所示：

嵌套查询学校信息表

test_result表的数据如下图所示：

嵌套查询示例考试成绩表

如果我们查询所有在第一小学读书的安徽籍学生的全部考试成绩，则可通过以下SQL语句实现：

SELECT `name`,`course`,`score` FROM `test_result` WHERE `name` IN (			-- （3）最后，执行最外层查询
	SELECT `name` FROM `school` WHERE `schoolName` = '第一小学' AND `name` IN ( 	-- （2）然后，执行中间层查询
		SELECT `name` FROM `student` WHERE `address` LIKE '%安徽%' 		-- （1）首先，执行最内层查询
      )
);

执行结果为：

嵌套查询示例查询结果

4.13、分组

命令格式：

1	GROUP BY 字段名

例：已知test_result表的数据如下图所示：

聚合函数sum示例

若想查询所有考试科目的平均成绩，则可通过下面的SQL语句来实现：

1	SELECT `course`,AVG(`score`) FROM `test_result` GROUP BY `course`; -- 按考试科目分组

执行结果为：

分组示例结果

4.14、having分组后再过滤

having语句的作用是对分组后的数据进行再过滤。

命令格式：

1	select 表达式 from 表名 GROUP BY 字段名 HAVING 分组后过滤条件;

注：

having和where的作用都是对数据进行过滤，它们的语法是完全相同的。
having和where的的主要区别是：where在数据分组前进行过滤，having在数据分组后进行过滤。

例：针对上述test_result表，若想查询所有平均分大于70分的考试科目的平均成绩，则可通过下面的SQL语句来实现：

1	SELECT `course`,AVG(`score`) FROM `test_result` GROUP BY `course` HAVING AVG(`score`) > 70;

执行结果为：

分组过滤示例结果

4.15、Select小结（重点）

所有的查询操作都用select关键字，select查询语句实际项目中使用频率最高的SQL语句。

select的完整命令格式：

SELECT [ALL | DISTINCT]  	-- 全部或去重
{* | 表名.* | [表名.字段名1 [AS 字段1别名][,表名.字段名2 [AS 字段2别名],……]]}
FROM 表名1 [AS 表1别名]          -- 基本查询
       [INNER | LEFT | RIGHT JOIN 表名2 ON 联结条件1]
       [INNER | LEFT | RIGHT JOIN 表名3 ON 联结条件2 ……]  -- 联表查询
       [WHERE 过滤条件|子查询语句] -- 分组前过滤
       [GROUP BY 字段名]  -- 分组
       [HAVING 过滤条件]  -- 分组后再过滤
       [ORDER BY 字段名1 排序方式1[,字段名2 排序方式2,……]] -- 排序(升序:ASC,降序:DESC）
       [LIMIT 起始位置,页面大小] -- 分页（指定查询的记录从哪一条至哪一条）

注：

字符|表示“或”。
[ ]表示其中的SQL语句可省略；{ }表示其中的SQL语句不可省略。
上述select语句中各子句的先后顺序不可打乱。
实际项目中经常要在业务层实现跨表、跨数据库查询。

5、MySQL函数

这里仅仅介绍部分常用函数，其它函数具体参见MySQL官方文档。

5.1、常用函数

**注：**这里介绍的MySQL常用函数一般也并不常用，而聚合函数却经常使用。

5.1.1、数学运算函数

（1）绝对值

函数用法：

1	SELECT ABS(表达式)

（2）向上取整

函数用法：

1	SELECT CEILING (表达式)

（3）向下取整

函数用法：

1	SELECT FLOOR (表达式)

（4）随机数生成

函数用法：

1	SELECT RAND() -- 返回一个在[0,1)区间的随机数

（5）判断正负

函数用法：

1	SELECT SIGN(表达式) -- 整数返回1，负数返回-1,零值返回0

5.1.2、字符串处理函数

（1）字符串长度

函数用法：

1	SELECT CHAR_LENGTH(字符串)

**注：**字符串需要使用单引号''或双引号""引起来。

（2）拼接字符串

函数用法：

1	SELECT CONCAT(字符串1,字符串2,……)

（3）替换字符串

方式一：

1	SELECT REPLACE(原始字符串,将要被替换的子字符串,将要替换的新字符串)

方式二：

1	SELECT INSERT(原始字符串，替换的起始位置，替换长度，将要替换的新字符串)

**注：**此函数会将原始字符串从替换的起始位置开始，将后面替换长度个字符替换（覆盖）成将要替换的新字符串，其它字符保持不变。

（4）大写转小写

函数用法：

1	SELECT LOWER(字符串)

（5）小写转大写

函数用法：

1	SELECT UPPER(字符串)

（6）返回第一次出现子串的索引

函数用法：

1	SELECT INSTR(字符串,子字符串)

（7）返回指定的子字符串

函数用法：

1	SELECT SUBSTR(原始字符串,想要截取子字符串的起始位置,想要截取的长度)

（8）翻转字符串

函数用法：

1	SELECT REVERSE(字符串)

5.1.3、日期和时间处理函数（掌握）

常用函数：

SELECT CURRENT_DATE() 	-- 获取当前日期
SELECT NOW() 		-- 获取当前日期 + 时间
SELECT LOCALTIME()	-- 获取本地时间
SELECT SYSDATE() 	-- 获取系统时间

SELECT YEAR(NOW())	-- 获取当前年份
SELECT MONTH(NOW())	-- 获取当前月份
SELECT DAY(NOW())	-- 获取当前日份
SELECT HOUR(NOW())	-- 获取当前小时
SELECT MINUTE(NOW())	-- 获取当前分钟
SELECT SECOND(NOW())	-- 获取当前秒数

5.1.4、系统相关函数

（1）系统用户

函数用法：

1 2	SELECT SYSTEM_USER() SELECT USER() -- 简写

（2）数据库版本

函数用法：

1	SELECT VERSION()

5.2、聚合函数

聚合函数是指：运行在行组上，计算和返回单个值的函数。聚合函数是经过高效设计的，它的返回结果一般比你在客户机应用程序中计算要快很多。SQL提供了5个聚合函数。

聚合函数名称	功能描述
COUNT()	返回某列的行数
SUM()	返回某列值之和
AVG()	返回某列的平均值
MAX()	返回某列的最大值
MIN()	返回某列的最小值

5.2.1、COUNT函数（重点）

**用法一：**统计某一字段中具有值的行数，不统计该字段为NULL的行。

1	COUNT(字段名)

**用法二：**统计一个表的所有行数，不管行中各列数据为不为NULL都会统计。

COUNT(*)

例：已知student表的数据如下图所示：

聚合函数count示例

（1）查询生日字段不为NULL的同学个数：

1	SELECT COUNT(`birthday`) FROM `student`;

执行结果为：

聚合函数count示例结果1

（2）查询student表中共有多少条记录：

1	SELECT COUNT(*) FROM `student`;

执行结果为：

聚合函数count示例结果2

5.2.2、SUM函数

函数用法：返回某列值之和。

sum(表达式)

注：

这里的表达式可以是某一字段名，也可以是多个字段组成的数学表达式。
AVG()函数自动忽略该字段值为NULL的行。

例：已知test_result表的数据如下图所示：

聚合函数sum示例

可通过如下SQL语句，计算张三同学的总成绩：

1	SELECT SUM(`score`) FROM `test_result` WHERE `name` = '张三';

执行结果为：

聚合函数sum示例结果

5.2.3、AVG函数

函数用法：返回某列的平均值。

1	COUNT(字段名)

**注：**AVG()函数自动忽略该字段值为NULL的行。

例：针对上述test_result表，若想查询所有同学数学考试成绩的平均值，则可通过下面的SQL语句来实现：

1	SELECT AVG(`score`) FROM `test_result` WHERE `course` = '数学';

执行结果为：

聚合函数avg示例结果

5.2.4、MAX函数

函数用法：返回某列的最大值。

MAX(字段名)

**注：**MAX()函数自动忽略该字段值为NULL的行。

例：针对上述test_result表，若想查询所有同学数学考试的最高成绩，则可通过下面的SQL语句来实现：

1	SELECT MAX(`score`) FROM `test_result` WHERE `course` = '数学';

执行结果为：

聚合函数max示例结果

5.2.5、MIN函数

函数用法：返回某列的最小值。

MIN(字段名)

**注：**MIN()函数自动忽略该字段值为NULL的行。

例：针对上述test_result表，若想查询所有同学数学考试的最低成绩，则可通过下面的SQL语句来实现：

1	SELECT MIN(`score`) FROM `test_result` WHERE `course` = '数学';

执行结果为：

聚合函数min示例结果

5.3、数据库级别的MD5加密

MD5是一种目前广泛使用的数据加密算法。该加密算法具有复杂度高、加密过程不可逆、相同数据加密后的值也相同等特点。常应于明文密码的加密存储与校验、licence数据的生成与校验等。

函数用法：

1	MD5(待加密数据)

注：

明文密码的加密存储与校验的SQL语句一般在Java代码中使用。
密码校验的原理是：先将用户输入的数据进行MD5加密，然后再比对加密后的值是否相等。若相等，则密码校验成功；否则校验失败。

（本讲完，系列博文持续更新中…… ）

阿汤笔迹微信公众平台

关注**“阿汤笔迹”** 微信公众号，获取更多学习笔记。
原文地址：http://www.atangbiji.com/2022/06/22/SQLInDetail
博主最新文章在个人博客 http://www.atangbiji.com/ 发布。

0、常用的数据库操作方法（都要掌握）

（1）可视化工具

（2）SQL语句

1、基本命令（初识MySQL）

1.1、连接数据库

1.2、修改用户密码

1.3、刷新权限

1.4、显示所有数据库

1.5、使用数据库（切换至某一数据库）

1.6、查看某一数据库中所有的表

1.7、查看某一表中的信息（字段）

1.8、创建一个数据库

1.9、退出数据库

2、DDL、DML、DQL、DCL

3、操作数据库

3.1、操作数据库

3.1.1、创建数据库

3.1.2、删除数据库

3.1.3、使用数据库

3.1.4、查看全部数据库

3.2、操作数据库中的表

3.2.1、创建表

（1）使用SQL语句创建表

（2）使用可视化工具（SQLyog）创建表

附：a、数据库的字段属性（列属性）（重点）

b、数据库的字段类型（列类型）

数值

字符串

时间日期

c.数据库引擎（表的类型）

d.字符集

(3)常用命令

3.2.2、修改表

3.2.2.1、修改表名

3.2.2.2、增加表的字段

3.2.2.3、字段重命名

3.2.2.4、修改约束（字段属性）

3.2.2.5、删除表的字段

3.2.3、删除表

3.3、操作数据库表中的数据（MySQL数据管理）

3.3.1、外键

（1）什么是外键（foreign key）

（2）外键的作用

（3）代码实现

3.3.2、DML（重点）

3.3.2.1、添加数据

3.3.2.2、修改数据

附：where条件子句

3.3.2.3、删除数据

附：truncate命令

4、DQL（重中之重）

4.0、查询表达式

4.1、查询某一个或几个字段（列）

4.1.1、as别名

4.1.2、concat拼接

4.2、查询所有字段（列）

4.3、distinct去重

4.4、where子句过滤

4.5、模糊查询

4.5.1、常用的比较运算符

4.5.2、LIKE运算符（重点）

4.5.3、IN运算符

4.5.4、IS NULL运算符

4.5.5、IS NOT NULL运算符

4.6、两表联表查询（重难点）

4.6.1、内部联结（等值联结）

4.6.2、左联结（left join）

4.6.3、右联结（right join）

附：总结

4.7、多表联结查询

4.8、自联结

4.9、order by子句排序

4.10、limit子句分页

4.11、子查询

附：在where子句的in操作符中使用子查询

4.12、嵌套查询

4.13、分组

4.14、having分组后再过滤

4.15、Select小结（重点）

5、MySQL函数