2 第二章 SQL

SQL（Structured Query Language，结构化查询语言）：操作关系型数据库的编程语言，定义了 一套操作关系型数据库统一标准 。

2.1 SQL的通用语法和分类

2.1.1 SQL的通用语法

在学习具体的SQL语句之前，先来了解一下SQL语言的同于语法。

  1. SQL语句可以单行或多行书写，以分号结尾。

  2. SQL语句可以使用空格/缩进来增强语句的可读性。

  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  4. 注释：

    • 单行注释：– 注释内容 或 # 注释内容
    • 多行注释：/* 注释内容 */

2.2 SQL语句的分类

SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL

表1.1: SQL语句的分类
分类 全称 说明
DDL Data Definition Language 数据定义语言，用来定义/创建数据库对象(数据库，表，字段)
DML Data Manipulation Language 数据操作语言，用来对数据库表中的数据进行增删改
DQL Data Query Language 数据查询语言，用来查询数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

2.3 数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

2.3.1 数值型

表2.1: 数值类型
类型 大小 有符号SIGNED范围 无符号范围
TINYINT 1byte (-128，127) (0，255)
SMALLINT 2bytes (-32768，32767) (0，65535)
MEDIUMINT 3bytes (-8388608，8388607) (0，16777215)
INT/INTEGER 4bytes (-2147483648，2147483647) (0，4294967295)
BIGINT 8bytes (-2^63，2^63-1) (0，2^64-1)
FLOAT 4bytes (-3.402823466 E+38，3.402823466351 E+38) 0和(1.175494351 E-38，3.402823466 E+38)
DOUBLE 8bytes (-1.7976931348623157E+308，1.7976931348623157E+308) 0和(2.2250738585072014E-308，1.7976931348623157E+308)
DECIMAL 不定 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 
# 例子:

# 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned

# 分数 -- 总分100分,最长4位（100.0） 最多出现一位小数
score double(4,1)

2.3.2 字符串型

char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和 字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性 能会更高些。

表2.2: 字符类型
类型 大小 描述
CHAR 0-255 bytes 定长字符串(需要指定长度)
VARCHAR 0-65535 bytes 变长字符串(需要指定长度)
TINYBLOB 0-255 bytes 不超过255个字符的二进制数据
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据 
# 例子:

# 用户名 username ------> 长度不定, 最长不会超过50
username varchar(50)

# 性别 gender ---------> 存储值, 不是男,就是女
gender char(1)

# 手机号 phone --------> 固定长度为11
phone char(11)

2.3.3 日期时间类型

表2.3: 日期时间类型
类型 大小 范围 格式
DATE 3 1000-01-01 至 9999-12-31 YYYY-MM-DD
TIME 3 -838:59:59 至 838:59:59 HH:MM:SS
YEAR 1 1901 至 2155 YYYY
DATETIME 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS
TIMESTAMP 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 
# 例子
# 生日字段 birthday
birthday date

# 创建时间 createtime
createtime datetime

2.4 数据定义语言 DDL

DDL（Data Definition Language，数据定义语言）：用来定义数据库对象(数据库，表，字段) 。

2.4.1 数据库操作

#查询所有数据库
show databases;

#查询当前数据库
select database();

#创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则] ;
create database itcast; #创建一个名为itcast的数据库
create database if not exists itcast;  #如果itcast不存在，创建数据库，否则不创建
create database itheima default charset utf8mb4; #使用utf8mb4字符集，一个字符占四个字节

#删除数据库
drop database [if exists] 数据库名;
drop database test;
drop database if exists test;

#使用数据库
use 数据库名;
use itcast;

2.4.2 表操作

2.4.2.1 表的创建

creat table 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];   #[...] 内为可选参数

create table tb_user(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender varchar(1) comment '性别'
) comment '用户表';

案例

设计一张员工信息表，要求如下： 1. 编号（纯数字）2. 员工工号 (字符串类型，长度不超过10位) 3. 员工姓名（字符串类型，长度不超过10位） 4. 性别（男/女，存储一个汉字） 5. 年龄（正常人年龄，不可能存储负数） 6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符） 7. 入职时间（取值年月日即可）

对应的建表语句详见 3.1.1 节。

2.4.2.2 表的查询

# 查询当前数据库所有表
show tables;
show tb_user;

# 查询表结构
desc 表名;
desc tb_user;

# 查询指定表的建表语句
show creat table 表名;

2.4.2.3 表的修改

# 添加字段
alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
alter table emp add nickname varchar(20) comment '昵称';

# 修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);

# 修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
alter table emp change nickname username varchar(30) comment '用户名';

# 删除字段
alter table 表名 drop 字段名;
alter table emp drop username;

# 修改表名
alter table 表名 rename to 新表名;
alter table emp rename to employee;

2.4.2.4 表的删除

# 删除表
drop table [if exists] 表名;

# 删除指定表数据，但保留表结构
truncate table 表名;
truncate table employee;

2.5 数据操作语言 DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进 行增、删、改操作。

2.5.1 添加数据

# 给指定字段添加数据
insert into 表名 (字段1, 字段2, ...) values(值1, 值2, ...);
INSERT INTO employee(id, workno, `name`, gender,age,idcard,entrydate) VALUES(1, '1', 'Itcast', '男', 10, '123456789012345678', '2020-01-01');

# 给全部字段添加数据
insert into 表名 values(值1, 值2, ...);
INSERT INTO employee VALUES(2,'2','张无忌','男',18,'123456789012345670','2005-01-01');

# 批量添加数据
insert into 表名 (字段名1, 字段名2, ...) values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
insert into 表名 values (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
INSERT INTO employee VALUES(3,'3','韦一笑','男',38,'123456789712345670','2005-01-01'),(4,'4','赵敏','女',18,'123456789712345670','2005-01-01');

注意事项:

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

2.5.2 修改数据

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... 1 [WHERE 条件];

# 例子
# 修改id为1的数据，将name修改为itheima
UPDATE employee SET name = 'ithema' WHERE id=1;
# 修改id为1的数据, 将name修改为小昭, gender修改为 女
UPDATE employee SET name = '小昭', gender = '女' WHERE id=1;
# 将所有的员工入职日期修改为 2008-01-01
UPDATE employee SET entrydate = '2008-01-01';

注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

2.5.3 删除数据

DELETE FROM 表名 [ WHERE 条件 ] ;

# 例子
# 删除gender为女的员工
DELETE FROM employee WHERE gender = '女';
# 删除所有员工
DELETE FROM employee;

2.6 数据查询语言 DQL

DQL（Data Query Language，数据查询语言）：用来查询数据库中表的记录。

我们先来完成如下数据准备工作，数据表的建表语句详见 3.1.2 节。

DQL 查询语句，语法结构如下：

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

我们在讲解这部分内容的时候，会将上面的完整语法进行拆分，分为以下几个部分：基本查询（不带任何条件）、条件查询（WHERE）、聚合函数（count、max、min、avg、sum）、分组查询（group by）、排序查询（order by）和分页查询（limit）。

2.6.1 基础查询

在基本查询的DQL语句中，不带任何的查询条件，查询的语法如下：

# 查询多个字段
SELECT 字段1, 字段2, 字1 段3 ... FROM 表名;
SELECT `name`,workno,age FROM emp; # 查询指定字段 name, workno, age并返回

# 查询返回所有字段
SELECT * FROM 表名;
SELECT * FROM emp; # 查询返回emp表中的所有字段

# 字段设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 1 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
SELECT workaddress AS '工作地址' FROM emp; # 查询所有员工的工作地址,起别名
SELECT workaddress '工作地址' FROM emp; # 查询所有员工的工作地址,起别名

# 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
SELECT DISTINCT workaddress AS '工作地址' FROM emp; # 查询公司员工的上班地址有哪些(不要重复)

2.6.2 条件查询

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

# 查询年龄等于 88 的员工
SELECT * FROM emp WHERE age=88;
# 查询年龄小于 20 的员工信息
SELECT * FROM emp WHERE age<20;
# 查询年龄小于等于 20 的员工信息
SELECT * FROM emp WHERE age<=20;
# 查询没有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NULL;
# 查询有身份证号的员工信息
SELECT * FROM emp WHERE idcard IS NOT NULL;
# 查询年龄不等于 88 的员工信息
SELECT * FROM emp WHERE age!=88;
SELECT * FROM emp WHERE age<>88;
# 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
SELECT * FROM emp WHERE age >= 15 && age <= 20;
SELECTt * FROM emp WHERE age >= 15 AND age <= 20;
SELECT * FROM emp WHERE age BETWEEN 15 AND 20;
# 查询性别为 女 且年龄小于 25岁的员工信息
SELECT * FROM emp WHERE gender = '女' AND age<=25;
# 查询年龄等于18 或 20 或 40 的员工信息
SELECT * FROM emp WHERE age IN (18,20,40);
SELECT * FROM emp WHERE age=18 OR AGE=20 OR AGE=40;
# 查询姓名为两个字的员工信息 _ %
SELECT * FROM emp WHERE `name` LIKE '__';
# 查询身份证号最后一位是X的员工信息
SELECT * FROM emp WHERE idcard LIKE '%X';

条件

表2.4: 比较运算符
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
!=或<> 不等于
BETWEEN … AND … 在某个范围之内（含最小最大值）
IN(…) 在in之后的列表中的值，多选一
LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符)
IS NULL 判断是否为NULL值
表2.5: 逻辑运算符
逻辑运算符 功能
AND 或 && 并且 (多个条件同时成立)
OR 或 || 或者 (多个条件任意一个成立)
NOT 或 ! 非 , 不是

2.6.3 聚合函数

将一列数据作为一个整体，进行纵向计算。

常见的聚合函数如下：

表2.6: 逻辑运算符
函数 功能
count 计数
max 最大值
min 最小值
avg 平均值
sum 求和

NULL值是不参与所有聚合函数运算的。

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

# 统计该企业员工数量
SELECT COUNT(*) FROM emp;
# 统计该企业员工的平均年龄
SELECT AVG(age) FROM emp;
# 统计该企业员工的最大年龄
SELECT MAX(age) FROM emp;
# 统计该企业员工的最小年龄
SELECT MIN(age) FROM emp;
# 统计西安地区员工的年龄之和
SELECT SUM(age) FROM emp WHERE workaddress = '西安';

2.6.4 分组查询

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

# 根据性别分组 , 统计男性员工 和 女性员工的数量
SELECT gender, COUNT(*) FROM emp GROUP BY gender;
# 根据性别分组 , 统计男性员工 和 女性员工的平均年龄
SELECT gender, AVG(age) FROM emp GROUP BY gender;
# 查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址
SELECT workaddress, COUNT(*) AS address_count FROM emp WHERE age<45 GROUP BY workaddress having address_count>=3;
# 统计各个工作地址上班的男性及女性员工的数量
SELECT workaddress, gender, COUNT(*) '数量' FROM emp GROUP BY gender, workaddress;

where与having区别

  • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
  • 执行顺序: where > 聚合函数 > having 。
  • 支持多字段分组, 具体语法为 : group by columnA,columnB

2.6.5 排序查询

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

# 根据年龄对公司的员工进行升序排序
SELECT * FROM emp ORDER BY age ASC;
# 根据入职时间, 对员工进行降序排序
SELECT * FROM emp ORDER BY entrydate DESC;
# 根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序
SELECT * FROM emp ORDER BY age ASC, entrydate DESC;

排序方式

  • ASC : 升序(默认值)。如果是升序, 可以不指定排序方式ASC ;
  • DESC: 降序

注意事项：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序；

2.6.6 分页查询

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

# 查询第1页员工数据, 每页展示10条记录
SELECT * FROM emp LIMIT 0,10; 
select * from emp limit 10;
# 查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
SELECT * FROM emp LIMIT 10,10;

注意事项

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

案例

详见 3.1.3 节。