文章目录
- CRUD
- 新增
- 全列插入
- 指定列插入
- 插入查询结果
- 查询
- 全列查询
- 指定列查询
- 查询字段为表达式
- 表达式不包含字段
- 表达式包含一个字段
- 表达式包含多个字段
- 补充:别名
- 去重查询
- 排序
- 条件查询
- 补充:运算符
- 区间查询
- 模糊查询
- NULL的查询
- 分页查询
- 聚合查询
- 聚合函数
- 分组查询
- 修改
- 删除
CRUD
CRUD是指创建(Create)、读取(Retrieve)、更新(Update)和删除(Delete)四个操作的首字母缩写(即增删查改),它是数据库或持久层在软件系统中执行的基本功能。这四个操作构成了大多数应用程序和服务中数据管理的基础。
接下来我们就介绍一数据库基本的增删改查。
新增
语法:
INSERT [INTO] tbl_name[(column [, column] ...)] VALUES (value_list) [, (value_list)] ...;value_list: value, [, value] ...
INSERT
、VALUES
以及(value_list)
是必须的,其余都被[]
括起来,属于可选项- 选择不同的可选项,可以达到全列插入与指定列插入、单行数据插入与多行数据插入以及这两组的组合。
我们先新建一个二维表,方便观察现象:
CREATE TABLE IF NOT EXISTS demo1 (id BIGINT,name VARCHAR(20),gender TINYINT
);
全列插入
语法:
INSERT [INTO] tbl_name VALUES (value_list) [, (value_list)] ...;
-
VALUES
后可以跟多个小括号,每个小括号就是一行数据,多行数据的小括号之间用,(逗号)
连接;只有一个小括号就是单行数据插入;多个小括号就是多行数据插入
-
全列插入的
VALUES
后的每个小括号中的值要与列一一对应(数量、数据类型),否则会报错!
例如:
SELECT * FROM demo1;
是全列查询语句。
指定列插入
语法:
INSERT [INTO] tbl_name[(column [, column] ...)] VALUES (value_list) [, (value_list)] ...;
- 在表名后、
()
里使用属性(字段)名指定列,如果要指定多个列,用,(逗号)
分隔。注意:即使只插入一列,()
也不能省略! - 一行中没有被指定插入的列,会用默认值填充,即
NULL
,表示缺失值 - 显式地指定所有属性名,就是全列插入
- 同样也可以多行数据插入
例如:
插入查询结果
建议先学习查询后再来。
语法:
INSERT INTO tbl_name [(column1)[,column2...]] SELECT ...
- 查询出的列与要插入的列的数量和类型要一致,一一对应
例如,将旧表中的数据导入新表:
查询
为了方便举例,我们后续可能会用到下表:
语法:
SELECT[DISTINCT] {* | {column1 [, column2] ...} [FROM tbl_name][WHERE ...][ORDER BY column [ASC | DESC], ...]LIMIT ...;
查询涉及到的关键字很多,分别有着不同的功能,但各个部分不是独立的。下面介绍时只是按照功能进行的分类,实际查询时一般不会只用到某个分类,比如,同时要求去重、条件查询、排序,这要求我们要依据需求编写合理的SQL查询语句
查询是不影响原表(永久表)的,每次返回的是一个临时表
全列查询
语法:
SELECT * FROM tbl_name;
*
:通配符,代表我要查询的结果中包含所有的字段(列)。- 当一张表中数据量很大时,建议不要使用全列查询,会有很大的磁盘开销和网络开销
例如:
指定列查询
语法:
SELECT column1 [, column2]... FROM tbl_name;
SELECT
后面紧跟要查询的列,之间用,(逗号)
分隔
例如:
查询字段为表达式
表达式不包含字段
语法:
SELECT column1, 常量 ... FROM tbl_name;
例如:
- 查询结果中返回的临时表,会在指定位置生成一列,如上图
表达式包含一个字段
例如,将表中的某列的值全部加10
SELECT column1 + 10, column2... FROM tbl_name;
表达式包含多个字段
例如,使用考试表,查询总分:
补充:别名
在MySQL中,别名的使用包括列别名和表别名两种类型。
使用别名可以带来多个重要的好处,主要包括提高查询的可读性、简化复杂查询、解决字段名冲突和提升性能等方面。
列别名的引入旨在提高查询结果的可读性,并允许在查询中使用更符合情境的标签来代表列。(也可以给表达式起别名)
表别名则主要用于简化查询中的表引用,特别是在多表查询的情况下。通过为每个表定义一个简短的别名,可以大大增强SQL语句的可读性和编写效率。
语法:
...列名 [AS] 别名...
...表名 [AS] 别名...
# 别名大多数情况下都是字符串,可以选择使用''引起来;不过,当别名中包含空格,必须使用''引起来
例如,优化查询考试总分:
去重查询
语法:
SELECT DISTINCT {* | {column1 [, column2] ...} FROM tbl_name;
- 判断为重复的条件:所有查询列的数值相等,例如全列去重查询,当两行数据的所有列的值相等,才判断为重复并去重
排序
语法:
SELECT[DISTINCT] {* | {column1 [, column2] ...} FROM tbl_name[ORDER BY column1 [ASC | DESC], ...];
-
ASC
为升序,DESC
为降序,用于指定排序方式。可以不指定,此时按照MySQL默认的升序排序 -
使用到
ORDER BY
,后面跟字段名(或查询列表的表达式)和排序方式,表示按照该字段的值进行升序/降序排序;ORDER BY
后面可以跟多个字段。在SQL中,可以使用逗号分隔多个字段来指定排序顺序。当使用多个字段进行排序时,首先按照第一个字段进行排序,如果第一个字段的值相同,则按照第二个字段进行排序,以此类推。每个字段都可以指定升序(
ASC
)或降序(DESC
)排序。(排序优先级取决于书写顺序) -
与查看表结构的关键字重名,但查看表结构的
DESC
是describe的缩写,排序的DESC
是descend的缩写,这是SQL设计时的反例。
例如,根据总分对exam表排名:
- 不能将
ORDER BY
后的别名用单引号引起来,否则排序就会失效
【注意】
NULL
数据排序,视为比任何值都小,升序排在最上面,降序排在最下面- 没有
ORDER BY
子句的查询,返回的顺序是未定义的
条件查询
条件查询基于比较运算符和逻辑运算符,可以构成较为复杂的条件对数据进行过滤,从而达到需求。下面给出的区间查询、模糊查询和NULL的查询都是条件查询,只是将这些场景单独拿出来,具体的场景要具体分析采用什么条件进行过滤
语法:
SELECT{* | {column1 [, column2] ...} FROM tbl_name[WHERE ...];
补充:运算符
【比较运算符】
运算符 | 说明 |
---|---|
>,>=,<,<= | 大于,大于等于,小于,小于等于 |
= | 等于。NULL 不安全,NULL = NULL 的结果为 NULL |
<=> | 等于。NULL 安全, NULL <=> NULL 的结果是1(TRUE ) |
!=,<> | 不等于 |
BETWEEN a AND b | 范围匹配。[a, b],如果 a <= values <= b,返回1(TRUE ) |
IN(option, …) | 如果是option中的任意一个,返回1(TRUE ) |
IS NULL | 是 NULL 。是,返回1(TRUE ),不是,返回0(FALSE ) |
IS NOT NULL | 不是NULL 。是,返回0(FALSE ),不是,返回1(TRUE ) |
LIKE | 模糊匹配。% 表示任意多个(包括0个)任意字符;_ 表示任意一个字符 |
【逻辑运算符】
运算符 | 说明 |
---|---|
AND | AND连接的多个条件必须都为TRUE ,结果才是1(TRUE ) |
OR | OR连接的任意一个条件为TRUE ,结果为1(TRUE ) |
NOT | 条件为TRUE ,返回0(FALSE ) |
-
WHERE
条件可以使用表达式,但不能使用别名,这与SQL语句的执行顺序有关:- 先确定表,即执行
FROM
- 根据条件过滤出合适的数据行,即执行
WHERE
- 显示要查询的列,即执行
SELECT
我们的列别名一般会在
SELECT
声明,这使得执行WHERE
时识别不出别名 - 先确定表,即执行
-
运算符是有优先级的,比如逻辑运算符
NOT >AND > OR
,不过不建议死记,建议使用小括号()
来手动设置优先级,确保万无一失 -
MySQL不支持链式比较,如 a < column < b,必须改写为
column > a AND column < b
-
NULL值不参与除了判断
NULL
之外的任何条件查询,它会被过滤掉。比如,找语文成绩高于60分的成绩,其中一位同学语文成绩一列为NULL
,那么就会直接过滤掉,不参与。 -
如果想知道某个表达式的值,写法:
SELECT 表达式;
。例如,SELECT NULL <=> NULL;
,将返回1(TRUE
) -
标准的比较运算符(如=, <>, <, >)在用于
NULL
值时会返回NULL
,比如SELECT NULL > 10;
结果为NULL
;对NULL
使用算术运算符将返回NULL
。例如,任何数字与NULL
进行加法运算的结果都将是NULL
。
区间查询
语法:
SELECT{* | {column1 [, column2] ...} FROM tbl_nameWHERE {column1 BETWEEN a AND b | column1 >= a AND column1 <= b};
例如,查询总分在200~300的学生:
模糊查询
语法:
SELECT{* | {column1 [, column2] ...} FROM tbl_nameWHERE column1 LIKE '[%][_...]值[_...][%]';
举例说明,查询姓刘的同学的成绩:
刘%
就代表刘的后面包含若干个(包括0个)字符,满足该条件的都会被选中。
查询姓名只有两个字的同学的成绩:
- 上图两个
_
号连在了一起,表示两个字符,必须是两个字符的才会被选中。
比如,我要查询姓刘且名字只有两个字的同学,就得:LIKE '刘_'
;查询名字中包含玄的同学,就得:LIKE '%玄%'
。
NULL的查询
语法:
SELECT {* | {column1 [, column2] ...} FROM tbl_name WHERE {column1 IS NULL | column1 IS NOT NULL};
- 区分
NULL
值 与 字符串'NULL'
,采用LIKE 'NULL'
实际上查询的时字符串NULL
,而不是NULL
。
小结一下目前遇到的有关NULL
的知识点:
- NULL的基本概念
- 定义:
NULL
在MySQL中代表一个未知的值。它是一种“没有类型”的特殊值,用于表示无值、未知值、缺失值等情况。 - 默认值:在创建表结构时,如果某字段未指定默认值,则默认为
NULL
值。这意味着如果在插入数据时没有提供该字段的值,它将自动被设置为NULL
。
- 定义:
- NULL值的比较与操作
- 比较运算符:标准的比较运算符(如=, <>, <, >)在用于
NULL
值时会返回NULL
,这表明结果既不是TRUE
也不是FALSE
,而是不确定的。因此,要检查一个值是否为NULL
,应使用IS NULL
或IS NOT NULL
运算符。 - 算术运算符:对
NULL
使用算术运算符将返回NULL
。例如,任何数字与NULL
进行加法运算的结果都将是NULL。 - 排序行为:在
ORDER BY
子句中,NULL
值在升序排序时默认位于最后,但在降序排序时则位于首位。
- 比较运算符:标准的比较运算符(如=, <>, <, >)在用于
分页查询
语法:
-- 从 0 开始,筛选 n 条结果
SELECT ... FROM tbl_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM tbl_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM tbl_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;
- 分页查询可以有效控制每次查询的的记录条数,可以减小数据库服务器的压力,可以有效提升数据处理的效率和用户体验。
- 记录的序号从 0 开始,第一条记录是0,以此类推
- 如果起始位置已经超出了整个结果集的范围,可以执行,但返回的是空结果集
例如,查询id编号前5的同学的成绩:
例如,查询总分排名前3的同学的成绩:
聚合查询
聚合函数
聚合函数(Aggregate Functions)在SQL中用于对一组值执行计算并返回单个值。
常见聚合函数如下:
函数 | 说明 |
---|---|
COUNT() | 返回查询到的数据数量 |
SUM() | 返回查询到的数据的总和,非数值没有意义 |
AVG() | 返回查询到的数据的平均值,非数值没有意义 |
MAX() | 返回查询到的数据的最大值 |
MIN() | 返回查询到的数据的最小值 |
语法:
SELECT 聚合函数([DISTINCT expr]) [,聚合函数(DISTINCT expr)]... FROM tbl_name [WHERE...];
-
聚合函数主要接受列名、列上的表达式以及(通过
CASE
语句)条件表达式作为参数。其他类型的参数(如直接的常量或复杂的子查询)要么在技术上可行但通常不推荐,要么需要特别考虑查询的上下文和目的。 -
聚合函数会忽略
NULL
(除了COUNT(*)
),例如SUM()
计算某列总和时,会忽略NULL
值,不会出现多个数值相加,其中包含NULL
值导致结果为NULL
的情况。所以,如果某列的值全为NULL
值,最终返回0 -
聚合函数的高级用法:
-
嵌套聚合函数:SQL允许在聚合函数内部使用其他聚合函数,进行更复杂的计算,例如计算每个部门的最高工资与最低工资之差。
-
使用DISTINCT关键字:有时需要对唯一值进行聚合计算,而不是考虑所有行。这时可以使用DISTINCT关键字来确保只考虑唯一的值。
-
计算百分比:聚合函数还可用于计算百分比或比例,例如计算每个部门的工资在总工资中的占比。
-
例如,查询班级人数(记录条数):
例如,查询全班人总分的平均数(有缺考科目的不计):
可以使用ROUND()
函数来处理结果:
ROUND()
函数用于对数值进行四舍五入到指定的小数位数。 它接受一到两个参数:
- 第一个参数:要四舍五入的数值。这可以是一个列名、一个表达式的结果,或者是一个直接的数值。
- 第二个参数(可选):指定要四舍五入到的小数位数。如果省略此参数,则默认四舍五入到最接近的整数。如果第二个参数是负数,则表示在小数点左侧进行四舍五入,即四舍五入到十位、百位等。
例如,查询全班数学成绩的最大值和最小值:
分组查询
语法:
SELECT 查询目标 FROM tbl_name [WHERE...] GROUP BY 分组字段名 [HAVING...];
WHERE
和HAVING
的区别:- 执行时机不同:
WHERE
是分组之前的过滤,不满足WHERE
条件,不参与分组;HAVING
是分组之后对结果进行过滤 - 判断条件不同:
WHERE
不能对聚合函数进行判断过滤,而HAVING
可以
- 执行时机不同:
- 分组查询常和聚合查询一起使用,使用聚合函数,对分组后各组数据进行指定求值
- 分组查询的查询目标一般为聚合函数和分组字段,查询其他字段无任何意义
- 执行顺序:
WHERE
> 聚合函数 >HAVING
例如,有这样一张表:
查询各个职务的平均薪资:
查询平均工资低于10000的职务(除游戏角色外)的平均工资和最高工资:
修改
语法:
UPDATE tbl_name SET column1 = expr [, column2 = expr ...][WHERE ...] [ORDER BY ...] [LIMIT ...];
SET
后指定列并修改值,WHERE
条件用于过滤筛选出要修改指定列的数据行,ORDER BY
用于排序,LIMIT
用于控制修改的数据行数- 在标准的 SQL 中,
UPDATE
语句本身并不直接支持ORDER BY
子句(可以使用,不报错)。ORDER BY
通常用于SELECT
语句中,以指定结果集的排序方式。并且,在UPDATE
语句中,排序通常不是必要的。对于需要排序的场景,需要结合使用子查询或临时表,并在子查询中指定排序。 - 在
SELECT
语句中,LIMIT
可以接受两个参数,第一个参数是偏移量,第二个参数是要返回的记录数。但在UPDATE
语句中,这种用法是不被支持的,仅支持一个参数的LIMIT
子句。 UPDATE
操作如果不加WHERE
子句,将会修改表中的所有记录,十分危险!
例如,将孙悟空同学的英语成绩和语文成绩分别改为95,90分:
例如,将总分小于200分的同学的英语成绩减去5分:
删除
语法:
DELETE FROM tbl_name [WHERE ...] [ORDER BY ...] [LIMIT ...];
-
在标准的 SQL 中,
DELETE
语句本身并不直接支持ORDER BY
子句(可以使用,不报错)。ORDER BY
通常用于SELECT
语句中,以指定结果集的排序方式。并且,在DELETE
语句中,排序通常不是必要的。对于需要排序的场景,需要结合使用子查询或临时表,并在子查询中指定排序。 -
在
SELECT
语句中,LIMIT
可以接受两个参数,第一个参数是偏移量,第二个参数是要返回的记录数。但在DELETE
语句中,这种用法是不被支持的,仅支持一个参数的LIMIT
子句,与UPDATE
语句一样。 -
DELETE
操作如果不加WHERE
子句,将会删除表中的所有记录,十分危险!
例如,删除孙悟空同学的成绩:
完