当前位置：首页 > article >正文

JavaWeb全链路学习：8、数据库-sql语句

article 2025/3/17 11:14:08

一篇文章搞定sql语句！

数据库(Database) DB

存储数据的仓库，本质是一个文件系统。按照特定格式将数据存储起来。

数据库管理系统(Database Management System) DBMS

是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库

关系型数据库与非关系型数据库

关系型数据库.

关系型数据库:指采用了关系模型来组织数据的数据库。

关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

非关系型数据库

一、分类

根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

DDL Data Defintion language 数据库定义语言

- 用于完成对数据库对象(数据表,数据库,视图,索引)的创建，删除，修改

DML Data Manipulation language 数据操作语言

- 用于完成对数据表中的数据添加，删除，修改

DQL Data Query language 数据查询语言

- 用于将数据表中的数据查询出来

DCL Data Control Laguage 数据控制语言

- 用于完成事务管理等控制型操作

二、基本语法

SQL指令不区分大小写

每条SQL表达式结束之后都以;结束

SQL关键字之间都以空格进行分割

SQL之间可以不限制换行(可以有空格的地方就可以有换行)

三、数据类型

数值类型

类型	大小(Bytes)	范围
tinyint	１	有符号 (-128，127)无符号 (0，255)
smallint	２	有符号 (-32 768，32 767)无符号 (0，65 535)
mediumint	３	有符号 (-8 388 608，8 388 607)无符号 (0，16 777 215)
int/integer	４	有符号(-231,231- 1)无符号(0,2^32 - 1)
bigint	８	有符号(-263,263- 1)无符号(0,2^64 - 1)
float	４	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)有符号0，(1.175 494 351 E-38，3.402 823 466 E+38)
double	８	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)有符号 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
decimal	DECIMAL(M<D)为 max(M+2,D+2)	依赖于M和D的值 decimal(m,n)表示数值一共有10位小数有2位

字符类型

类型	字符序列的长度范围	说明
char	0~255字节	定长字符串,最多可以存储255个字节；当我们指定数据表字段char(n) 此列中的数据最多长为n个字符，如果添加的字符串少于n则补’\u0000’至长n长度
varchar	0~655535字节	可变长度字符串,此类型的类最大长度65535
tinyblob	0~255字节	存储二进制字符串
blob	0~65535字节	存储二进制字符串
mediumblob	0~1677215	存储二进制字符串
longblob	0~2^64 -1字节	存储二进制字符串
tinytext	0~255字节	文本数据(字符串)
text	0~65535字节	文本数据(字符串)
mediumtext	0~2^24 -1字节	文本数据(字符串)
longtext	0~2^64 -1字节	文本数据(字符串)

日期类型

类型	格式	说明
date	2021-09-13	日期,只存储年月日
time	11:20:31	时间,只存储时分秒
year	2021	年份
datetime	2021-09-13 11:20:31	日期加时间,存储年月日时分秒
timestamp	20210913 112031	日期+时间(时间戳)

四、CREATE TABLE – 创建表

CREATE TABLE 表名称
(
  列名称1 数据类型,
  列名称2 数据类型,
  列名称3 数据类型,
  ....
);

4.1字段约束

字段常见的约束？

非空约束(not null):限制此列的值必须提供,不能null
唯一约束(unique):在表中的多条数据,此列的值不能重复
主键约束(primary key):非空+唯一,能够唯一标示数据表中的一条数据.
外键约束(foreign key):建立不同表之间的关联关系.

主键约束

主键–就是数据表中的记录中的唯一标识，在一张表中只能有一个主键(主键可以是一个字段,也可以是多个列组合)

当一个字段声明为主键之后，添加数据时;

此字段数据不能为null;
此字段数据不能重复;

添加单列主键

创建表时添加主键约束：

create table books(
    book_isbn char(4),
    book_name varchar(20) primary key
);

或者

create table books(
    book_isbn char(4);
    book_name varchar(20),
    primary key(book_name)
);

追加主键：

## 创建表时没有添加主键约束
create table books(
    book_isbn char(4),
    book_name varchar(10) 
);

## 创建表之后添加主键约束
alter table books modify book_isbn char(4) primary key;

删除主键：

alter table books drop primary key;

添加多列主键

ALTER TABLE Enrollments
ADD CONSTRAINT PK_Enrollments PRIMARY KEY (StudentID, CourseID);

主键自增长：

在我们创建一张数据表时，如果数据表中有可以作为主键(列如:学生表的学号,图书表的isbn)我们可以直接设为这个字段为主键,

当有些数据没有合适的字段作为主键的时候，我们可以定义一个与记录无关的列(ID)作为主键,此数据无具体含义主要作为标识唯一,在mysql中我们可以将此定义为int ，同时设置为自动增长

当我们想数据表中新曾一条数据时，无需提供ID列的值，它会自动增长.

定义主键自动增长：

create table types(
    type_id double primary key auto_increment,
    type_name varchar(20) not null,
    type_reamrk varchar(20)
);

联合主键

用两个主键包含的字段作为主键，这个组合在数据表中是唯一，且加了主键索引。这两个字段有一个字段不相同就可以区别两个数据，例如：你的订单表里有很多字段，一般情况只要有个订单号bill_no做主键就可以了，但是，现在要求可能会有补充订单，使用相同的订单号，
那么这时单独使用订单号就不可以了，因为会有重复。那么你可以再使用个订单序列号bill_seq来作为区别。把bill_no和bill_seq设成联合主键。即使bill_no相同，bill_seq不同也是可以的。

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)

);

外键约束

如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

外键的作用：
①为了一张表记录的数据不要太过冗余。

②保持数据的一致性、完整性。

not null约束

强制列不接受null值，强制字段始终包含值，意味着，如果不向字段添加值，就无法插入新记录或更新记录。

create table 表名称（ 
	列名称1 数据类型 not null，
	列名称2 数据类型 not null，
	列名称3 数据类型，。。。
）;

五、DELETE – 删除数据

5.1删除部分数据

删除某行：

DELETE FROM 表名称 WHERE 列名称 = 值;

删除所有行：

DELETE FROM table_name;

5.2删除表

drop table persons;

六、UPDATE – 更新数据

更新某一行中的一个列：

UPDATE Persons SET FirstName = 'Fred' WHERE LastName = 'Wilson';

更新某一行中的若干列：

UPDATE Persons SET ID_P = 6,city= 'London' WHERE LastName = 'Wilson';

七、INSERT – 插入数据

插入新的行

INSERT INTO Persons VALUES (1, 'Gates', 'Bill', 'Xuanwumen 10', 'Beijing');

在指定列插入数据

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees');

八、SELECT – 查询数据

查询指定列

SELECT 列名称 FROM 表名称;

查询所有列

SELECT * FROM Persons;

8.1 DISTINCT – 去除重复值

SELECT DISTINCT 列名称 FROM 表名称;

8.2 WHERE – 条件过滤

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值;

运算符：

操作符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式

## ＝　等于
select * from stus where sut_num = '20202';
## != <>不等于
select * from stus where sut_num != '20202';
select * from stus where sut_num <> '20202';
## >大于
select * from stus where sut_age>18;
## <小于
select * from stus where sut_age<18;
## >=大于等于
select * from stus where sut_age>=18;
## <=小于等于
select * from stus where stu_age<=18;
## between and 区间查询
select * from stus where stu_age between 18 and 20;

8.3 AND & OR – 运算符

AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter';

8.4 ORDER BY – 排序

ORDER BY 语句用于根据指定的列对结果集进行排序，默认按照升序对记录进行排序，如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

SELECT * FROM 表名称 ORDER BY 列1,列2 DESC;

默认排序为 ASC 升序，DESC 代表降序。

SELECT * FROM Persons ORDER BY ID_P,LASTNAME;

8.5 like-模糊查询

select * from tableName where columName like 'reg';

在like关键字后的reg表达式中

- %表示任意多字符［%o%表示含有o］
- _表示任意一个字符

## 查询学生姓名包含字符o的学生信息
select * from stus where stu_name like '%o%';
## 查询学生姓名第一个字为张的学生信息
select * from stus where stu_name like '张%';
## 查询学生姓名最后一个字母为o的学生信息
select * from stus where stu_name like '%o';
##　查询学生姓名中第二个字母为o的学生信息
select * from stus where stu_name like '_o％';

8.6 聚合函数

sql中提供了一些可以对查询记录进行列计算的的函数–聚合函数

count() 统计函数,统计满足条件的指定字段的个数(记录数)

- COUNT(*) ：返回表中的记录数。
- COUNT(DISTINCT 列名) ：返回指定列的不同值的数目。
- COUNT(列名) ：返回指定列的值的数目（NULL 不计入）。

## 统计学生个数
select count(id) from stu;
## 统计女生个数
select count(sex) from stu WHERE sex='男';

max()记录指定列中的最大值
min()记录中指定列的最小值
sum()记录中指定列的和
avg()记录中指定记录的平均值

8.7 GROUP BY – 分组

GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。

SELECT 列名A, 统计函数(列名B)
FROM 表名
WHERE 查询条件
GROUP BY 列名A;、

# 先对查询的学生信息按性别进行分组(分成男,女),然后分别统计每组学生的个数
select sex,count(sex)from stu group by sex;
# 先对查询的学生信息按性别进行分组　然后计算
select sex,avg(scroe) from stu group by sex;
# 先对学生按照班级分组　然后统计各组的学生数量，在排序
select age,count(stu) from stu group by age order by age asc;
# 查询所有学生,按年龄进行分组,然后分别统计每组>1的人数,再筛选年龄升序 (having　隐藏记录　有group by 才能有　having)
select age,count(num)from stugroup by agehaving count(num)>1 order by age asc;

8.8 HAVING – 句尾连接

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

SELECT 列名A, 统计函数(列名B)
FROM table_name
WHERE 查询条件
GROUP BY 列名A
HAVING 统计函数(列名B) 查询条件;

获取 Persons 表中住在北京的总人数大于1的 LASTNAME，根据 LASTNAME 分组

select lastname,count(city) from persons 
where city='Beijing' 
group by lastname
having count(city) > 1;

8.9 连接查询

通过DQL的学习,我们可以很轻松的从一张数据表中查询出需要的数据:在企业的应用开发,我们经常需要从多张表中查询数据(列如:我们查询学生信息的时候),可以通过连接查询从多张数据表提取数据:

在MySQL中可以使用join实现多表的联合查询–链接查询,

inner join 内连接
left join 左连接
right join 右连接

select 列名
from 表A
INNER|LEFT|RIGHT|FULL JOIN 表B
ON 表A主键列 = 表B外键列;

内连接

select ... from tableName inner join tableName2;

笛卡尔积

笛卡尔积(A集合&B集合):使用A中的每个记录依次关联B中每个记录,笛卡尔集的总数=A总数*B总数
如果直接执行select … from tableNmae1 inner join tableName2; 会获取两张数据表中的数据集合的笛卡尔积(依次使用tableName1表中的每条数据去匹配tableName2的每条数据)

内连接条件

两张表同时用inner join连接查询之后产生笛卡尔积数据很多是无意义的,我们如何消除无意义的数据----添加两张进行连接的查询时的条件

使用on设置两张表连接查询的匹配条件

-- 使用where设置过滤条件:先生成笛卡尔积再从笛卡尔积中过滤数据(效率很低)
select * from students 
inner join classes 
where students.cid=classes.class_id;
-- 使用ON设置连接查询条件:先判断连接条件是否成立,如果成立两张表的数据进行组合在生成一条结果记录
select * from students 
inner join classes 
on students.cid=classes.class_id;

左连接 LEFT JOIN

需求:查询出所有的学生信息,如果学生有对应的班级信息,则将对应的班级信息也查询出来.

左连接:显示左表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果右表中不存在匹配数据,则显示为null

select * from leftTable join rightTable on 匹配条件;

右连接 RIGHT JOIN

右连接:示右表中的所有数据,如果在右表中满足条件的数据,则进行匹配;如果左表表中不存在匹配数据,则显示为null

如果我们希望列出所有人的定购，可以使用下面的 SELECT 语句：

SELECT p.LastName, p.FirstName, o.OrderNo
FROM Persons p
INNER JOIN Orders o
ON p.Id_P = o.Id_P
ORDER BY p.LastName DESC;

8.10 UNION – 合并结果集

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

SELECT 列名 FROM 表A
UNION
SELECT 列名 FROM 表B;

UNION 操作符默认为选取不同的值

UNION ALL 语法：

SELECT 列名 FROM 表A
UNION ALL
SELECT 列名 FROM 表B;

结果显示重复的值.

8.11 子查询/嵌套查询

子查询–先进行一次查询,第一次查询的结果作为第二次查询的/条件(源) (第二次查询是基于第一次查询的结果进行的)

案例:查询班级班级名为(mysql)的学生信息(只知道班级名称,而不知道班级ID)

传统方式

-- a.查询mysql的班级的编号
select class_id from classes where class_name='mysql';
-- b.查询此班级编号的学生信息
select * from students where cid=2

子查询单行单列

子查询:

select * from students 
where cid=(select class_id from classes where class_name='mysql');

案例2:查询所有Java班级中的学生信息

传统方式

-- a查询所有Java班的班级编号
select class_id from classes where class_name like 'Java%';
-- b查询这些班级编号中的学生信息(union 将多个查询语句的结果整合到一起)
select * from students 
where cid = 1
union select * from students 
where cid = 2
union select * from students where cid = 3

子查询多行单列

子查询

-- 如果查询返回的结果是多个值(单列多行),条件使用**in / not in**
select * from students 
where cid in (select class_id from classes where class_name like 'mysql%');

子查询返回多个值,多行多列

案例3:查询cid=1的班级中性别为男的学生信息

-- 传统的多条件查询
select * from students where cid=1 and stu_gender='男';
-- 子查询(先查询cid=1班级中的所有信息,将这些信息作为一个整体虚拟表 在基于这个虚拟表查询性别为男的学生信息)
select * from (select * from students where cid=1) t where t.stu_gender='男';

8.12 BETWEEN – 选取区间数据

操作符 BETWEEN … AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 BETWEEN 值1 AND 值2;

查询上述结果相反的结果，可以使用 NOT：

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter';

8.13 AS – 别名

通过使用 SQL，可以为列名称和表名称指定别名（Alias），别名使查询程序更易阅读和书写。

表别名

SELECT 列名称/(*) FROM 表名称 AS 别名;、

SELECT p.LastName, p.FirstName
FROM Persons p 
WHERE p.LastName='Adams' AND p.FirstName='John';

列别名

SELECT 列名称 as 别名 FROM 表名称;

SELECT LastName "Family", FirstName "Name" FROM Persons;

8.14 IN – 锁定多个值

IN 操作符允许我们在 WHERE 子句中规定多个值。

SELECT 列名/(*) FROM 表名称 WHERE 列名称 IN (值1,值2,值3);

从 Persons 表中选取姓氏为 Adams 和 Carter 的人：

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter');

8.15 分页查询

当数据表中的记录比较多的时候,如果一次性全部查询出来的显示给用户,用户的可读性/体验性就不太好,因为此我们可以将这些数据分页进行展示.

select ... from ... where ... limit param1,param2;

param1 int 获取查询语句的结果的第一条数据的索引(索引从0开始)
param2 int 获取查询语句的结果的条数(如果剩下的数据条数<pram2,则返回剩下的记录)

对数据表中的学生信息进行分页显示,总共59条数据,我们每页显示20条

总记录数: count 59

每页显示 page 20

总页数 pageCount = cpimt%page ?count/page:count/page +1;

# 查询第一页:
select * from stu limit 0,20;
# 查询第二页
select * from stu limit 20,20;
# 查询第三页
select * from stu limit 40,20;
# 如果一张表中,pageNum表示查询的页码,pageSize表示查询每页的条数:                                                                                                                                                       select * from stu limit (pageNum-1)*pageSize,pageSize;

九、VIEW – 视图

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

CREATE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;

视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

将 Persons 表中住在 Beijing 的人筛选出来创建视图：

create view persons_beijing as
select * from persons where city='Beijing';

如果需要更新视图中的列或者其他信息，无需删除，使用 CREATE OR REPLACE VIEW 选项：

CREATE OR REPLACE VIEW 视图名 AS
SELECT 列名
FROM 表名
WHERE 查询条件;

十、常见函数

10.1 自定义函数

基本语法

CREATE FUNCTION function_name (parameter_list)
RETURNS data_type--返回值类型
BEGIN
    -- Function body
    RETURN expression;
END;

示例

#改变权限
set @@global.log_bin_trust_function_creators=1
#创建函数，将tel后四位改为*
create function telnum(tel varchar(50)) returns VARCHAR(50)
BEGIN
	DECLARE result VARCHAR(50);
	set result=(select REPLACE(tel,right(tel,4),'****'));
	return result;
END;

#调用函数
select name,telnum(tel) from t_staff
select telnum('12345555555')

10.2 delimiter

在 SQL 中，DELIMITER 命令用于更改语句结束符（通常是分号;），以便能够在数据库管理系统中编写包含多个语句的复杂 SQL 脚本。这个功能在编写存储过程、触发器、函数等时特别有用，因为这些结构内可能包含多个 SQL 语句。

为什么需要 `DELIMITER`

默认情况下，SQL 语句以分号（;）结尾。但是，当我们编写包含多个 SQL 语句的复杂结构（如存储过程、触发器等）时，我们需要一种方式来区分单个 SQL 语句的结尾和整个结构的结尾。此时，DELIMITER 命令就派上用场了。

示例

假设我们要创建一个存储过程，其中包含多个 SQL 语句。以下是如何使用 DELIMITER 命令的示例。

1. 更改语句结束符

首先，告诉 SQL 解释器我们要使用不同的结束符（例如 $$）：

DELIMITER $$

2. 编写存储过程

在更改结束符之后，我们可以编写存储过程：

CREATE PROCEDURE SampleProcedure()
BEGIN
    DECLARE v INT;
    SET v = 1;
    SELECT v;
END$$

3. 恢复默认结束符

最后，恢复默认的语句结束符（分号 ;）：

DELIMITER ;

完整示例

下面是一个完整的示例，用于创建和调用一个简单的存储过程：

-- 更改语句结束符为 $$
DELIMITER $$

-- 创建存储过程
CREATE PROCEDURE SampleProcedure()
BEGIN
    DECLARE v INT;
    SET v = 1;
    SELECT v;
END$$

-- 恢复默认语句结束符为 ;
DELIMITER ;

-- 调用存储过程
CALL SampleProcedure();

使用场景

存储过程：编写和创建包含多个 SQL 语句的存储过程。
触发器：定义包含多个 SQL 语句的触发器。
函数：编写和创建包含多个 SQL 语句的用户定义函数。

十二、触发器

触发器（Trigger）是数据库中的一种特殊的存储过程，它在指定的表上执行特定的事件（INSERT、UPDATE 或 DELETE）时自动触发。触发器可以用于执行复杂的业务逻辑、维护数据一致性和完整性。

12.1 创建触发器的语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器的逻辑
END;

trigger_name：触发器的名称。
{BEFORE | AFTER}：指定触发器是在事件之前（BEFORE）还是之后（AFTER）触发。
{INSERT | UPDATE | DELETE}：指定触发器的事件类型。
table_name：触发器所作用的表名。
FOR EACH ROW：指定触发器的操作是针对每一行数据。
BEGIN ... END：触发器的主体部分，包含具体的业务逻辑。

示例

假设我们有一个表 employees，并希望在插入新记录时自动记录日志到 employee_log 表中。

创建触发器

创建一个在插入新员工记录之前自动记录日志的触发器：

DELIMITER $$

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (employee_id, action)
    VALUES (NEW.employee_id, 'INSERT');
END$$

DELIMITER ;

查看日志

log_id  employee_id  action  log_date
------  ------------ ------- -------------------
1       1            INSERT  2023-05-21 12:34:56

12.2 触发器的事件类型

触发器可以根据不同的事件类型进行定义：

INSERT 触发器：在表上执行插入操作时触发。
UPDATE 触发器：在表上执行更新操作时触发。
DELETE 触发器：在表上执行删除操作时触发。

BEFORE 和 AFTER

BEFORE 触发器：在事件发生之前触发，常用于验证或修改即将插入或更新的数据。
AFTER 触发器：在事件发生之后触发，常用于日志记录或级联操作。

12.3 删除触发器

如果需要删除已创建的触发器，可以使用 DROP TRIGGER 语句：

DROP TRIGGER IF EXISTS trigger_name;

例如，删除 before_employee_insert 触发器：

DROP TRIGGER IF EXISTS before_employee_insert;

通过上述步骤，您可以在MySQL中创建、使用和管理触发器，以便更好地执行复杂的业务逻辑和维护数据的一致性和完整性。

十三、流程控制语句

在 SQL 中，CASE WHEN THEN 是一个用于条件表达式的语句，可以根据不同的条件返回不同的值。它在查询中非常有用，可以用于创建有条件的计算或显示。

13.1 语法

CASE 语句有两种主要形式：简单 CASE 表达式和搜索 CASE 表达式。

简单 CASE 表达式

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

搜索 CASE 表达式

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

13.2 示例

简单 CASE 表达式

假设我们有一个 students 表，包含学生的 ID 和他们的成绩。我们想要创建一个查询，根据成绩显示相应的等级（A, B, C, D, F）。

CREATE TABLE students (
    student_id INT,
    score INT
);

INSERT INTO students (student_id, score) VALUES
(1, 95),
(2, 85),
(3, 75),
(4, 65),
(5, 55);

使用简单 CASE 表达式：

SELECT student_id, score,
CASE score
    WHEN 90 THEN 'A'
    WHEN 80 THEN 'B'
    WHEN 70 THEN 'C'
    WHEN 60 THEN 'D'
    ELSE 'F'
END AS grade
FROM students;

搜索 CASE 表达式

使用搜索 CASE 表达式，我们可以处理更复杂的条件。例如，我们可以根据不同的分数范围来计算等级：

SELECT student_id, score,
CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
END AS grade
FROM students;

示例解释

简单 CASE 表达式：

- 通过 CASE score，检查 score 的值是否匹配 WHEN 子句中的某个值。
- 如果匹配，则返回对应的 THEN 结果。
- 如果没有匹配任何 WHEN 子句，返回 ELSE 结果。

搜索 CASE 表达式：

- 通过 CASE 关键字，依次检查 WHEN 子句中的条件。
- 如果某个条件为 TRUE，则返回对应的 THEN 结果。
- 如果没有任何条件为 TRUE，返回 ELSE 结果。

13.3 综合示例

假设我们有一个员工表 employees，包含员工的 ID、名字、职位和工资。我们想要根据工资水平分类员工的等级。

CREATE TABLE employees (
    employee_id INT,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (employee_id, name, position, salary) VALUES
(1, 'John Doe', 'Manager', 90000),
(2, 'Jane Smith', 'Developer', 80000),
(3, 'Sam Johnson', 'Intern', 30000),
(4, 'Chris Lee', 'Developer', 75000),
(5, 'Pat Taylor', 'Designer', 60000);

使用 CASE 表达式分类员工等级：

SELECT employee_id, name, position, salary,
CASE
    WHEN salary >= 85000 THEN 'High'
    WHEN salary >= 70000 THEN 'Medium'
    ELSE 'Low'
END AS salary_level
FROM employees;

查询结果：

employee_id  name         position   salary   salary_level
------------ ------------ ---------- -------- ------------
1            John Doe     Manager    90000.00 High
2            Jane Smith   Developer  80000.00 Medium
3            Sam Johnson  Intern     30000.00 Low
4            Chris Lee    Developer  75000.00 Medium
5            Pat Taylor   Designer   60000.00 Low

总结

CASE WHEN THEN 语句用于在查询中实现条件逻辑，可以根据不同条件返回不同的结果。
简单 CASE 表达式用于检查某个表达式的值。
搜索 CASE 表达式用于检查复杂的条件。
通过使用 CASE 语句，可以使查询结果更加灵活和动态，根据不同条件显示不同的值。

流程控制语句在数据库存储过程中非常重要，用于控制SQL语句的执行顺序和条件。常见的流程控制语句包括条件语句（IF...THEN）、循环语句（LOOP, WHILE, REPEAT）和跳转语句（LEAVE, ITERATE）。这些语句使得存储过程可以实现更复杂的逻辑。

条件语句

IF...THEN...ELSE

用于根据条件执行不同的代码块。

IF condition THEN
    -- statements
ELSE
    -- statements
END IF;

示例

DELIMITER $$

CREATE PROCEDURE check_salary (IN employee_id INT, OUT result VARCHAR(50))
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);

    -- 获取员工的工资
    SELECT salary INTO emp_salary
    FROM employees
    WHERE employee_id = employee_id;

    -- 检查工资
    IF emp_salary > 100000 THEN
        SET result = 'High salary';
    ELSE
        SET result = 'Normal salary';
    END IF;
END$$

DELIMITER ;

CASE

用于根据多个条件之一执行代码块。

CASE
    WHEN condition1 THEN
        -- statements
    WHEN condition2 THEN
        -- statements
    ELSE
        -- statements
END CASE;

示例

DELIMITER $$

CREATE PROCEDURE check_position (IN employee_id INT, OUT position_desc VARCHAR(100))
BEGIN
    DECLARE emp_position VARCHAR(100);

    -- 获取员工的职位
    SELECT position INTO emp_position
    FROM employees
    WHERE employee_id = employee_id;

    -- 检查职位
    CASE emp_position
        WHEN 'Manager' THEN
            SET position_desc = 'Manages the team';
        WHEN 'Developer' THEN
            SET position_desc = 'Writes code';
        ELSE
            SET position_desc = 'Other position';
    END CASE;
END$$

DELIMITER ;

循环语句

LOOP

无限循环，需要使用 LEAVE 语句跳出循环。

[loop_label:] LOOP
    -- statements
    LEAVE loop_label;
END LOOP loop_label;

示例

DELIMITER $$

CREATE PROCEDURE loop_example ()
BEGIN
    DECLARE i INT DEFAULT 0;
    loop_label: LOOP
        SET i = i + 1;
        IF i >= 10 THEN
            LEAVE loop_label;
        END IF;
    END LOOP loop_label;
END$$

DELIMITER ;

WHILE

基于条件的循环，在每次迭代前检查条件。

WHILE condition DO
    -- statements
END WHILE;

示例

DELIMITER $$

CREATE PROCEDURE while_example ()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 10 DO
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;

REPEAT

基于条件的循环，在每次迭代后检查条件。

[repeat_label:] REPEAT
    -- statements
UNTIL condition
END REPEAT repeat_label;

示例

DELIMITER $$

CREATE PROCEDURE repeat_example ()
BEGIN
    DECLARE i INT DEFAULT 0;
    repeat_label: REPEAT
        SET i = i + 1;
    UNTIL i >= 10
    END REPEAT repeat_label;
END$$

DELIMITER ;

跳转语句

LEAVE

用于跳出循环。

LEAVE label;

ITERATE

用于跳过当前迭代并开始下一次迭代。

ITERATE label;

示例

DELIMITER $$

CREATE PROCEDURE iterate_example ()
BEGIN
    DECLARE i INT DEFAULT 0;
    loop_label: LOOP
        SET i = i + 1;
        IF i < 5 THEN
            ITERATE loop_label;
        END IF;
        IF i >= 10 THEN
            LEAVE loop_label;
        END IF;
    END LOOP loop_label;
END$$

DELIMITER ;

综合示例

结合条件语句和循环语句的综合示例：

DELIMITER $$

CREATE PROCEDURE comprehensive_example (IN max_count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE result VARCHAR(100) DEFAULT '';
    
    WHILE i < max_count DO
        SET i = i + 1;
        CASE
            WHEN i MOD 2 = 0 THEN
                SET result = CONCAT(result, 'Even, ');
            ELSE
                SET result = CONCAT(result, 'Odd, ');
        END CASE;
    END WHILE;
    
    SELECT TRIM(TRAILING ', ' FROM result) AS result;
END$$

DELIMITER ;

结论

通过使用这些流程控制语句，您可以在 MySQL 中创建功能强大且灵活的存储过程。这些语句允许您在数据库层实现复杂的业务逻辑，从而提高应用程序的性能和安全性。

十四、存储过程

14.1 什么是存储过程

存储过程（Stored Procedure）是一组预编译的 SQL 语句，这些语句在数据库中以一个单元存储，并可以在需要时被调用执行。存储过程通常用于封装复杂的业务逻辑、执行重复的操作、提高性能、确保数据一致性和安全性。

14.2 存储过程的优点

提高性能：存储过程在数据库服务器端执行，减少了客户端和服务器之间的数据传输，提高了执行效率。
封装业务逻辑：将复杂的业务逻辑封装在存储过程中，使得应用程序更简洁、维护更方便。
代码复用：一次编写，可以多次调用，减少了代码的重复。
安全性：通过授予对存储过程的执行权限，可以控制对底层数据的访问，增强了数据的安全性。
减少网络流量：存储过程在服务器端执行，只返回最终结果，减少了网络传输的数据量。

14.3 存储过程的结构

存储过程的基本结构包括过程名、参数列表、过程体（包含SQL语句）和返回值。存储过程可以接受输入参数（IN）、输出参数（OUT）或输入输出参数（INOUT）。

14.4 存储过程的语法

CREATE PROCEDURE procedure_name (parameter_list)
BEGIN
    -- Procedure body
    -- SQL statements
END;

procedure_name：存储过程的名称。
parameter_list：存储过程的参数列表，格式为 IN、OUT 或 INOUT 参数。
BEGIN ... END：存储过程的主体部分，包含具体的 SQL 逻辑。

14.5 示例

表结构

假设我们有一个员工表 employees 和一个日志表 employee_log。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(50),
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建存储过程

创建一个名为 update_salary 的存储过程，用于更新员工的工资并记录日志。

DELIMITER $$

CREATE PROCEDURE update_salary (
    IN p_employee_id INT,
    IN p_new_salary DECIMAL(10, 2)
)
BEGIN
    -- 更新员工工资
    UPDATE employees
    SET salary = p_new_salary
    WHERE employee_id = p_employee_id;
    
    -- 记录日志
    INSERT INTO employee_log (employee_id, action)
    VALUES (p_employee_id, 'UPDATE SALARY');
END$$

DELIMITER ;

解释

DELIMITER $$：更改语句结束符为 $$，以便可以包含多个语句。
CREATE PROCEDURE update_salary：定义一个名为 update_salary 的存储过程，包含两个输入参数 p_employee_id 和 p_new_salary。
BEGIN ... END：存储过程的主体部分，首先执行 UPDATE 语句更新员工的工资，然后执行 INSERT 语句将更新操作记录到 employee_log 表中。
DELIMITER ;：恢复默认语句结束符 ;。

调用存储过程

使用 CALL 语句调用存储过程：

CALL update_salary(1, 95000.00);

14.6 使用 IN、OUT 和 INOUT 参数

以下示例展示了如何使用 IN、OUT 和 INOUT 参数：

DELIMITER $$

CREATE PROCEDURE example_procedure (
    IN in_param INT,
    OUT out_param INT,
    INOUT inout_param INT
)
BEGIN
    SET out_param = in_param * 2;
    SET inout_param = inout_param + in_param;
END$$

DELIMITER ;

调用存储过程并获取输出参数：

SET @out_value = 0;
SET @inout_value = 5;
CALL example_procedure(10, @out_value, @inout_value);
SELECT @out_value, @inout_value;

14.7 删除存储过程

如果需要删除已创建的存储过程，可以使用 DROP PROCEDURE 语句：

DROP PROCEDURE IF EXISTS update_salary;

14.8 注意事项

权限：创建和执行存储过程需要特定的权限，如果没有权限，可以联系数据库管理员。
调试：调试存储过程可能会比较困难，可以通过日志记录或使用调试工具来辅助调试。
性能：存储过程可以提高性能，因为它们在数据库服务器上编译并执行，减少了网络传输和客户端处理的开销。

通过上述步骤，您可以在 MySQL 中创建、使用和管理存储过程，以便更好地封装业务逻辑、提高性能和维护数据的一致性和完整性。

十五、索引

什么是索引

索引（Index）是数据库中的一种数据结构，用于快速查询和检索数据库表中的数据。索引通过对表中的一个或多个列进行排序和组织，使得查询操作的效率大大提高。索引类似于书的目录，可以帮助你快速找到所需的信息。

索引的类型

主键索引（Primary Key Index）：

- 自动创建在定义主键时。
- 唯一性和非空性。

唯一索引（Unique Index）：

- 确保索引列中的所有值都是唯一的。
- 允许列包含空值（NULL）。

普通索引（Non-Unique Index）：

- 提高查询效率，不要求唯一性。

全文索引（Full-Text Index）：

- 用于全文搜索，如在文本字段中查找特定单词或短语。
- 仅在支持的存储引擎（如 MyISAM）上使用。

组合索引（Composite Index）：

- 包含多个列的索引。
- 提高多列查询的效率。

创建索引

创建主键索引

主键索引在创建主键时自动创建：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

创建唯一索引

创建唯一索引确保列值唯一：

CREATE UNIQUE INDEX idx_unique_name ON employees (name);

或者在创建表时定义唯一约束：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) UNIQUE,
    position VARCHAR(100),
    salary DECIMAL(10, 2)
);

创建普通索引

创建普通索引提高查询效率：

CREATE INDEX idx_position ON employees (position);

创建全文索引

创建全文索引用于全文搜索：

CREATE FULLTEXT INDEX idx_fulltext_name ON employees (name);

创建组合索引

创建组合索引用于多列查询：

CREATE INDEX idx_name_position ON employees (name, position);

使用索引

查询使用索引

使用索引进行查询：

SELECT * FROM employees WHERE name = 'John Doe';

使用组合索引进行多列查询：

SELECT * FROM employees WHERE name = 'John Doe' AND position = 'Manager';

查看索引

查看表的索引：

SHOW INDEX FROM employees;

删除索引

删除索引：

DROP INDEX idx_position ON employees;

索引的注意事项

空间消耗：索引会占用额外的存储空间。
维护成本：插入、更新和删除操作可能需要更新索引，增加了维护成本。
合理使用：在需要频繁查询的列上创建索引，不要在更新频繁的列上过多使用索引。

示例：索引的创建与使用

示例表结构

创建一个简单的 students 表：

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100),
    major VARCHAR(100)
);

创建索引

为 email 列创建唯一索引：

CREATE UNIQUE INDEX idx_unique_email ON students (email);

为 major 列创建普通索引：

CREATE INDEX idx_major ON students (major);

为 name 和 age 列创建组合索引：

CREATE INDEX idx_name_age ON students (name, age);

使用索引进行查询

查询使用 email 列上的唯一索引：

SELECT * FROM students WHERE email = 'john.doe@example.com';

查询使用 major 列上的普通索引：

SELECT * FROM students WHERE major = 'Computer Science';

查询使用组合索引：

SELECT * FROM students WHERE name = 'John Doe' AND age = 21;

总结

索引是数据库优化的关键工具，可以显著提高查询性能。然而，索引也有其成本，包括占用额外的存储空间和增加维护开销。因此，在设计数据库时，应根据实际需求合理创建和使用索引，以平衡查询性能和系统开销。

十五、索引失效的情况

索引是提高数据库查询性能的重要工具，但在某些情况下，索引可能会失效，导致查询性能下降。了解索引失效的情况有助于更好地设计和优化数据库。以下是一些常见的索引失效情况：

1. 使用函数或表达式

在查询中对索引列使用函数或表达式会导致索引失效，因为数据库无法直接使用索引进行查找。

示例

SELECT * FROM employees WHERE UPPER(name) = 'JOHN DOE';

这种情况可以通过使用函数索引来解决（如果数据库支持）或在应用程序层处理函数调用。

2. 隐式类型转换

如果索引列的数据类型与查询条件的数据类型不一致，数据库可能会进行隐式类型转换，导致索引失效。

示例

SELECT * FROM employees WHERE employee_id = '123';

这种情况可以通过确保查询条件的数据类型与索引列的数据类型一致来解决。

3. 使用不等于操作符（<>）

在查询中使用不等于操作符（<> 或 !=）会导致索引失效，因为数据库无法使用索引进行范围查找。

示例

SELECT * FROM employees WHERE salary <> 50000;

4. 使用 IS NULL 或 IS NOT NULL

在查询中使用 IS NULL 或 IS NOT NULL 可能导致索引失效，因为 NULL 值通常不会被索引。

示例

SELECT * FROM employees WHERE salary IS NULL;

5. 使用 LIKE 模式匹配

在查询中使用 LIKE 模式匹配时，如果模式以通配符（% 或 _）开头，索引将失效。

示例

SELECT * FROM employees WHERE name LIKE '%John';

这种情况可以通过避免在模式开头使用通配符来解决。

6. 使用 OR 条件

在查询中使用 OR 条件，如果 OR 条件中的某一列没有索引，则索引会失效。

示例

SELECT * FROM employees WHERE employee_id = 1 OR salary = 50000;

这种情况可以通过使用联合索引或将 OR 条件拆分为多个查询来解决。

7. 不满足最左前缀原则（对于复合索引）

对于复合索引，查询条件必须包含最左前缀，否则索引将失效。

示例

CREATE INDEX idx_name_position ON employees (name, position);

-- 仅使用position列，索引失效
SELECT * FROM employees WHERE position = 'Manager';

-- 使用name和position列，索引有效
SELECT * FROM employees WHERE name = 'John Doe' AND position = 'Manager';

8. 使用低选择性列

在选择性较低的列上创建索引可能导致索引失效，因为索引的效果有限。选择性是指列中不同值的数量与总行数的比率。

示例

SELECT * FROM employees WHERE gender = 'M';

9. 查询数据量过大

如果查询返回的数据量占表中总数据量的大部分，数据库可能会选择进行全表扫描而不是使用索引。

示例

SELECT * FROM employees WHERE salary > 1000;

10. 更新频繁的列

在频繁更新的列上创建索引可能会导致索引失效，因为索引维护成本高，影响查询性能。

示例汇总

以下是一个综合示例，展示了各种索引失效的情况：

示例表结构

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2),
    gender CHAR(1),
    hire_date DATE
);

CREATE INDEX idx_name_position ON employees (name, position);
CREATE INDEX idx_salary ON employees (salary);
CREATE INDEX idx_hire_date ON employees (hire_date);

示例查询

-- 使用函数，索引失效
SELECT * FROM employees WHERE UPPER(name) = 'JOHN DOE';

-- 隐式类型转换，索引失效
SELECT * FROM employees WHERE employee_id = '123';

-- 使用不等于操作符，索引失效
SELECT * FROM employees WHERE salary <> 50000;

-- 使用 IS NULL，索引失效
SELECT * FROM employees WHERE salary IS NULL;

-- LIKE 模式匹配，索引失效
SELECT * FROM employees WHERE name LIKE '%John';

-- OR 条件，索引失效
SELECT * FROM employees WHERE employee_id = 1 OR salary = 50000;

-- 不满足最左前缀原则，索引失效
SELECT * FROM employees WHERE position = 'Manager';

-- 低选择性列，索引失效
SELECT * FROM employees WHERE gender = 'M';

-- 查询数据量过大，索引失效
SELECT * FROM employees WHERE salary > 1000;

-- 更新频繁的列，索引失效（假设 salary 是更新频繁的列）
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 1;