SQL语句

1.基础的SQL语句

1.1库的操作

1.查看数据库

1.查看所有数据库

语法：show databases

注意：这里的databases是复数，因为是查看所有的数据库

实例：在这里我们使用navicat作为客户端使用，例子都用navicat来展示

2.查看创建数据库

语法：show create database db_name

作用:如图示：

会得到语句：CREATE DATABASE /*!32312 IF NOT EXISTS*/ `student` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

我们通过这个SQL语句可以得到已经创建的数据库的详细信息：

1./*!40100 default.... */这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话

2.字符集是utf8mb4，检验规则是utf8mb4_0900_ai_ci

3.ENCRYPTION='N' */表示我们创建的数据库没有加密

2.创建数据库

语法：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ...
create_option: [DEFAULT] {
 CHARACTER SET [=] charset_name
 | COLLATE [=] collation_name
 | ENCRYPTION [=] {'Y' | 'N'}
}

在这里为大家解释一下常见的几个问题character set,collate,encryption到底是什么？

字符集（一般写作charset）：举例解释就是：某一个汉字对应到哪一个数字来表示，这个规则有多种多样的方案，每一种方案就称为一个“字符集”。

类似于这个ASCII码表对照的一样，不同的字符集，每一个数字所对应的汉字就是不一样的。

那么问问大家，我们一个汉字存储占用多少内存呢？

可能多数人会说是2个字节，在学校的编程语言课程中，多数都是以2字节来当作一个汉字存储的占用内存大小，但是根据不同的字符集会有不同的情况，这里我们来介绍常用的情况。

1.GBK，使用两个字节来表示汉字（2个字节即范围为：0-65535可以存储这个数量的汉字）

泛用性：Windows简体中文版默认字符集就是GBK；学习C语言时，我们在VS上打印一个汉字的strlen长度是2。

2.UTF8（变长编码），根据具体要表示的内容，1~4个字节的范围（表示范围非常大，用于表示中文时通常使用3个字节表示）

泛用性：不仅可以表示中文，是当前业界最流行的编码方式。

注意：一般默认使用的是utf8mb4，这个方案补全了utf8没有emoji表情存储的缺失

3.UTF16，使用2个字节来表示汉字

泛用性：一般是Java编程语言使用来存储汉字的方案

一般创建数据库时，我们可以选定字符集，这样后续数据库存储汉字时，就会根据你选择的字符集规则来开辟空间存储。

检验规则（collate）：就是对存储数据进行大小比较/排序的规则。

进行加密（encryption）：即字面意义，是否对这个数据库进行加密。（一般依数据库用途来决定）

这是最基本的语法，当然也不用这样一大串写上去徒增麻烦，接下来我会挑选几种常用的情况来为大家说明。

1.直接创建数据库

语法：create database db_name(要创建的数据库的名称)

作用：创建一个名为db_name(可任取名)的数据库

举例：如这里我们执行create databsase student这一SQL语句可以得到以下结果

注意：这种创建方法如果先前我们已经创建了student数据库，再执行一次这个创建语句，就会出现下面的情况，报错之后，执行就会中断，后面的语句不会继续执行。

2.创建数据库，如果不存在

语法：create database if not exists db_name

作用：创建一个名为db_name的数据库，如果不存在则创建，如果存在，则不会执行创建操作，继续执行后面的语句

举例：可以看到在student库已经存在的基础上，这次执行并没有报错，后续语句依旧可以正常进行

3.根据条件完整创建数据库

语法：create database if not exists db_name charset utf8mbs collate utf8mb4_general_ci

作用：选定字符集和检验规则创建一个名为db_name的数据库

举例：可以看到这样我们也成功创建了一个数据库

4.特殊情况（创建一个以关键词为名称的数据库）

语法：create database if not exists `create`;(使用反引号即可)

作用：创造一个名称与关键词冲突的数据库，应对使用时的特殊情况

举例：这样我们就创造了一个以关键词单词一样的名称的数据库（一般应用于业务需求，日常不要乱使用）

3.选中数据库

语法：use db_name

作用：我们使用数据库时，由于不止有一个数据库，因此，我们要选定一个要操作的数据库，就可以使用这个语句，选中要操作的数据库。从而后续的SQL语句都将在这个具体的数据库上进行。

4.更改数据库

语法：


ALTER {DATABASE | SCHEMA} [db_name]
 alter_option ...
alter_option: {
 [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name
 | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'}
 | READ ONLY [=] {DEFAULT | 0 | 1}
}

作用：可以更改选中的数据库的字符集，检验规则，是否加密以及是否仅阅读等等。（但是实际上我们使用一般创建之后就不会对此进行修改）。

举例：这里我们用了这一SQL语句：ALTER DATABASE teacher CHARSET gbk;

这样我们就把teacher数据库的字符集更改成了gbk。

5.删除数据库

语法：drop database if exists db_name\

作用：删除所指定的数据库

注意：1.删除数据库是⼀个危险操作，不要随意删除数据库

2.删除数据库之后，数据库对应的⽬录及⽬录中的所有⽂件也会被删除
3.删除数据库后，使用show databases是没有删除的数据库的
如图所示

我们把student库删除后，就无法查看了。

1.2表的操作

表格的操作这是我们数据库的核心之一，所有的数据都要依赖于表格而存储

在进行任意的表操作之前，需要我们先选中某个数据库才能进行操作

1.查看所有表

语法：show tables

作用：查看当前数据库下的表格

举例：目前还未在student库中添加表格，因此现在查询出来的为空白。

PS：一个常用的查看操作：查看表的结构

语法：desc db_name(表名)

作用：可以查看某个表的结构（也可以看作属性）例如：表中有哪些列？列中数据是什么类型的数据？表中是否有元素？

举例：

这是我们后面创造表时创造的表格，通过desc操作我们可以清楚看到：

1.共有五个列，学生学号，姓名，还有语文，数学。英语的成绩

2.每个对应的类型，譬如姓名是字符串，其他是整型

3.表中是否有数据，可以看到这个表还未插入元素都是null

2.创建表格

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
 field datatype [约束] [comment '注解内容']
 [, field datatype [约束] [comment '注解内容']] ...
) [engine 存储引擎] [character set 字符集] [collate 排序规则];

一般的创建语法是：create table db_name(列名 类型，列名 类型……)

作用：创建一个表格在数据库中，表格中的头列可以由自己定义

举例：

可以看到这样我们就创建了一个学生考试成绩表格，同时我们还可以对每个列做注释，方便别人阅读。

3.修改表格

语法：

ALTER TABLE tbl_name [alter_option [, alter_option] ...];
alter_option: {
 table_options
 | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
 | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
 | DROP [COLUMN] col_name
 | RENAME COLUMN old_col_name TO new_col_name
 | RENAME [TO | AS] new_tbl_name

基本语法可以看作：alter table tbl_name(表名)+多种功能修改（增添列，修改列，删除列，重命名列，重命名表格），具体根据使用的需求而定。

增添列

语法：alter table tbl_name add +字段名 +字段类型 (注释)comment +after 在哪个列后侧（根据添加需求定）

作用：在表上添加我们新的的数据需求。

举例：

可以看到我们在英语成绩后面又加上了班级编号这一列数据。

修改表中现有的列

语法：alter table tbl_name+modify+修改的字段名+要修改成的类型+(注释)comment +"备注"

作用：将表某一列的数据类型改变

举例：

可以看到我们把原本的学生姓名的类型varchar（20）改成了varchar（200）

修改表格之重命名某列

语法：alter table tbl_name+rename+COLUMN (固定某一列)+列名+to+修改后列名

作用：对表格中某一列重新进行命名

举例：

可以看到我们成功把最后一列的名字改掉了。

修改表格之删除某一列

语法：alter table tbl_name drop 列名

作用：删除不需要的一列数据

举例：

可以看到我们把学生班级编号这一列给删除掉了。

修改表格之重命名表格

语法：

作用：

举例：

1.3表数据的插入

常见的插入形式

语法：

INSERT [INTO] table_name
 [(column [, column] ...)]
VALUES
 (value_list) [, (value_list)] ...
value_list: value, [, value] ...

基本的格式就是：insert into table_name values(数据)

作用：给我们建立的表中插入数据

下面我们会有三种插入表数据的方法，我们会一一进行了解。

1.单行数据全列插入

这里，我们以只有id和name两列的数据表为例子来举例

语法：一般格式为：insert into table_name values(id,name)

（值得注意这里的数据要对应我们表的列，要一一对应，不能多也不能少）

作用：

举例：

可以看到按照标准的格式，我们就插入了数据，但是如果我们多一项或者少一项呢？

可以看到进行单行数据全列插入的时候，按照要插入表格的格式，多一列或者少一列都是不可行的。

注意：

1.进行单行全列插入时，要根据表格的列来插入，多一列或者少一列数据是不可行的。

2.进行单行全列插入时，要根据表格的列来插入，就算列数正确，但是数据不符合表格要求的数据类型，也是不可行的。

2.单行数据指定列插入

语法：insert into table_name (选定要插入的列) values (要插入的列数据)

作用：选定插入需要或者掌握的数据即可，使用条件更宽松

举例：

可以看到，我们这里只选定了学生学号和学生姓名两列进行了数据插入，其他列都采取默认值填充（一般默认值为Null）。

注意：

1.指定哪些数据，就要插入哪些，同样不能多插或者少插，数据类型也要保证一致。

2.我们指定插入哪些列后，后面插入数据的顺序就要和前面指定的一一对应，譬如：

（id，name），后面我们插入就要按照这个数据插入数据。

3.多行数据指定列插入

语法：insert into table_name(选定要插入的列) values (要插入的指定列数据)，（要插入的指定列数据）……

作用：一次性可以插入多条数据（节省时间），并且还可指定列

举例：

可以看到我们这一次就一次性指定并且插入了三条数据。

注意：

当然我们可以仿照这个格式，不用指定列，而是把表全部列都插入，并且一次性插入多条数据。

插入查询结果

插入查询结果是insert语句和select语句结合的语句，本质就是将查询到的结果作为要插入表中的数据插入数据表。

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

举例：

可以看到我们创造了一个新的数据表student2，然后将exam表中查询到的数据，一模一样的插入到了新表中。

注意：像这种把一个表的数据查询出来插入到另一个表，我们需要注意：插入数据的表的列的个数和类型都必须和查询到的表一样！！！

1.4基本的表数据查询

基本查询语句

语法：

SELECT
 [DISTINCT]
 select_expr [, select_expr] ...
 [FROM table_references]
 [WHERE where_condition]
 [GROUP BY {col_name | expr}, ...]
 [HAVING where_condition]
 [ORDER BY {col_name | expr } [ASC | DESC], ... ]
 [LIMIT {[offset,] row_count | row_count OFFSET offset}]

对表中的数据查询，我们使用SELECT关键字，接下来我们介绍多种多样的查询形式。

1.全列查询

语法：select *from table_name

作用：查询我们表中的所有数据，总览数据

举例：

我们通过这个SQL语句就成功查询到了表中的所有数据，上面举例展现的表也是这样得到的。

注意：全列查询是一个很危险的操作，如果表过大，进行全列查询可能会把公司带宽占满，造成服务器卡顿，所以一般并不会这样使用。

2.指定列查询

语法：select 要查询的列名 from table_name

作用：可以只查询我们需要的列数据，节约空间

举例：

我们就得到了只查询学生id和学生名字的查询结果

3.带有表达式的查询

在开始介绍之前，我们会插入一部分数据来辅助我们讲解后面的内容，插入完后表如图：

语法：select 表达式 from table_name

作用：可以实现对表中数据初步统计后的呈现，便于观察

举例：

例子1：所有人的语文成绩+5

特别注意：任何一个select语句都不会修改服务器的原始数据！！！

因此这张图只是一个‘临时表’，select操作并没有更改数据库服务器上的原始数据，只是在返回客户端时，在一个临时表上对上述数据进行了加工，当客户端收到这个数据之后，临时表就无了。

例子2：所有人语文，数学，英语成绩之和

这样我们就直接得到了所有人的分数总和。但是可以看到分数总和这一列显得有些许冗长。我们同样可以对冗长的表达式取别名，从而使列表名字更顺眼一些。

这样我们就把三科分数总和命名成了总分，这样不仅更简洁，而且也更便于理解。

4.去重查询

语法：select （指定的列名）distinct from table_name

作用：对查询到的指定列里的重复数据去重

举例：

通过这些例子我们可以看到，指定数学成绩查询时，会出现98和null这些多次出现的数据。如果我们仅仅只是想知道数学成绩有哪些分数，就可以这样去重查询得到简洁的结果。

注意：

1.去重数据，是要指定列中重复的那两行数据完全相同才能去重

2.

通过这张图，我们就能验证注意中所写的内容，由于我们这次指定查询列是学生姓名和数学成绩一起，因此，对于去重操作，必须是指定列的行记录中姓名和数学成绩两者都重复才能去除记录。由此可以看出去重操作的限制。

where子句与数据查询的结合

可能有人会好奇这个where子句是何方神圣，可以在这里先透露一下，它们结合起来其实就是条件查询。在查询使用数据时，我们肯定是取出自己需要的数据即可，不必多查数据既浪费了时间，也增加工作难度。因此我们要指定需要的条件，从而查询需要的数据，这里的where子句其实就是限定我们查询到的数据的条件。

在开始介绍条件查询之前，我们先介绍一下SQL中的运算符，这样我们才能使用合理的运算符来表达出我们想要限制的条件。

1.比较运算符

比较运算符

运算符

 说明
>,>=,<,<= 大于，大于等于，小于，小于等于
= 等于，对于NULL的比较不安全，比如NULL=NULL结果还是NULL
<=> 等于，对于NULL的比较j是安全，比如NULL<=>NULL结果是TRUE（1）
!=,<> 不等于
value BETWEEN a0 AND a1 范围匹配，[a0,a1]，如果a0<=value<=a1，返回TRUE或1，NOT BETWEEN则取反
value IN (option,…) 如果value在option列表中，则返回TRUE（1），NOT IN则取反
IS NULL 是NULL
IS NOT NULL 不是NULL
LIKE 模糊匹配，%表示任意多个（包括0个）字符；_表示任意一个字符，NOT LIKE则取反
2.逻辑运算符
逻辑运算符
运算符 说明
AND 多个条件必须都为TRUE（1），结果才是TRUE(1)
OR 任意一个条件为TRUE(1)，结果为TRUE(1)
NOT 条件为TRUE（1），结果为FALSE(0)

注意：

1.NULL在数据表中表示这个格子里面，什么数据都没有

2.NULL在SQL语句中还可以参加到算术运算以及比较运算，但是运算的结果还是NULL，比如：1+NULL->NULL；1==NULL->NULL

同时，NULL又会被隐式转换为FALSE

例如：NULL==NULL->NULL->FALSE

3.<=>这个符号对NULL进行了特殊处理，例如NULL<=>NULL->TRUE

4.BETWEEN AND不同于一般的编程语言（一般是前闭后开），而是特殊的“前闭后闭”的区间。

对一些特殊的情况进行说明之后，我们就正式开始介绍条件查询

语法：

SELECT
 select_expr [, select_expr] ... [FROM table_references]
 WHERE where_condition

一般就是:select （指定的列）from table_name where+限制的条件

作用：可以查询到我们需要的数据

接下来，我们使用各种例子来帮助大家，在实践中更好理解运用上述的运算符。

1.查询英语成绩不合格的同学（<60）

直接采用 '<' 符号即可完成条件的书写，这里我们是将列与指定的数值进行比较，由此我们查询到了英语成绩不合格的同学

2.查询语文成绩高于英语成绩的同学

直接采用'<'即可完成条件的书写，这里我们是将列与列进行比较，查询到了语文成绩高于英语成绩的同学

3.总分在200分以下的同学

这里采用'<'即可完成条件的书写，这里我们是用表达式与数字进行比较，查询到了总分在200分以下的同学

4.逻辑运算符AND和OR的使用

查询语文成绩和英语成绩都大于80的同学

这里我们采用'AND'逻辑运算符查询两个条件同时成立的数据，查询到了语文成绩和英语成绩都大于80的同学

查询语文成绩或者英语成绩大于80的同学

这里我们采用'OR'逻辑运算符查询两个条件只要成立一个就符合的数据，查询到了语文成绩或者英语成绩其中一科满足大于80分的同学。

注意：接下来，我们通过一个例子来分析一下AND和OR的优先级

可以看到我们设定的条件是数学成绩和英语成绩用AND绑定起来，然后语文成绩和后面那一大块的结果用OR绑定起来。但是我们发现居然有语文成绩不符合的数据还有英语成绩不符合要求的数据也被查询到了。

对此，我们对限定从句来进行分析，分析过程如下：

当然，如果想要改变优先级，我们也可以用小括号来指定执行的先后顺序。

5.范围查询

语⽂成绩在 [80, 90] 分的同学及语⽂成绩

这里我们使用betwee ……and就能简单完成任务，值得注意的是，SQL语句里的范围是左闭右闭的

数学成绩是78或者79或者98或者99的同学

这里我们用到了'IN'表示在这个集合里面的数据是我们要查询的数据。可以看到查询到的数据，与集合中的数据相同。

6.模糊查询

这里我们要用到'LIKE'这一个运算符，在这里我们要介绍两个和LIKE搭配使用的两个通配符

，这两个通配符需要跟在LIKE后面才有用，可以把它们看作我们打扑克牌使用的赖子牌，去替代指代一些东西。

  1. %：可以指代0个或任意多个字符
  2. _：只能指代1个字符

查询所有姓孙的同学

可以看到%跟在我们要指定查询的后面，由于%可以代表任意多个字符，因此可以查询所有以孙字开头的名字

查询姓孙的并且只有两个字的同学

同理，_代表一个字符，因此可以查询所有以孙字开头但是姓名只有两个字的同学。

值得注意的是，我们可以用多个_跟在后面，比如__这里的两个下划线，代表查询姓孙，并且姓名是3个字的同学。

6.NULL查询

使用IS NULL和IS NOT NULL可以完成对是否有数据为空的对象的查询。

语法十分简单，就不再这里继续赘述。

值得一提的是我们使用表达式计算时，只要有一个数据是NULL就会导致整个结果变成NULL

可以看到新插入的数据只有语文成绩是NULL但是最后总的total计算出来就是NULL。

在条件查询的过程中，我们会遇到一类特殊的情况：

按理来说，我们这样取别名完全是可行的，但是为什么就报错了呢？我们通过下面的注意来对此解释

注意：条件查询执行是按照这种过程

  1. 先遍历整个表的每一行数据（一次一行的遍历）
  2. 把这次遍历的这一行数据，代入到where子句的条件中（此时还没有对表达式别名为total，因此根本不存在total这一列，因此报错）
  3. 如果条件成立（true）则将这一行加入到结果集合中；如果条件不成立（false）这一行直接跳过
  4. 完成对所有数据的遍历之后，得到结果集合（大概与我们查询出来的相似），根据select操作指定的列/表达式/别名/去重操作，从这些方面针对结果进行处理得到我们最后看到的（直到这最后一步，表达式才别名为total）
Order by对查询结果进行排序

语法：

ASC为升序（从⼩到⼤）
-- DESC 为降序（从⼤到⼩）
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...] ORDER BY {col_name | expr } [ASC | 
DESC], ... ;

作用：排序完后的数据更加易于观察

举例：按照数学成绩从低到高排序（升序）

可以看到NULL一般默认是最小的，然后下面的数据按照升序排序并且被查询出来

表达式取为别名后，使用别名排序可以看到我们将总分取名为total后，直接按照total进行排序是可行的

where语句和order by的联合使用

可以看到这里我们先使用where子句限定条件，然后使用了order by对查询的数据进行了排序。

总结：

  1. 当查询语句中没有order by时，查询结果的顺序是按照插入的顺序排序的，其数据并没有可信度
  2. order by可以按照列的别名进行排序
  3. NULL在数据里面视作比任何数据都小，如果order by按照升序排序则NULL在最上面，按照降序则NULL在最下面
  4. order by是针对查询出来的临时表进行的排序，而非在数据库里面进行

    特别注意：不同于where的条件子句，order by的排序执行过程是最后才进行的

  • 遍历表，一次一行的遍历
  • 把当前遍历的行代入到条件中，判断这一行是否要保留
  • 根据select的列名，把指定列筛选出来/计算表达式的值/定义别名
  • order by对我们需求的数据进行排序
  • 因此order by是可以用别名排序的，就像这样：
分页查询

分页查询，我们在日常也经常跟它打交道

比如我们搜索欧冠会跳出很多的消息，于是它便分出了很多页面，这样我们就把原本要在一页展现出来的大量数据，分散成了多页少量的形式。

这在数据库查询中其实也有很重要的意义，把一次查询大量数据改为多次查询少量数据（分页），这样可以大大减少我们数据库的工作量

语法： 
-- 起始下标为 0
-- 从 0 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num;
-- 从 start 开始，筛选 num 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT start, num;
-- 从 start 开始，筛选 num 条结果，⽐第⼆种⽤法更明确，建议使⽤
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT num OFFSET start;

这里有三种形式：

对于第一句，LIMIT是限制一次返回多少条数据

对于第二句，LIMIT后start是规定从下标几的数据开始查询，num同样是限制一次返回多少数据

对于第三句同第二句的功能相同，只是先后顺序改变，用OFFSET来表示开始下标更清晰

举例：

可以看到我们限制每次查询返回三条数据，查询到最后数据只有一行不够满足返回三条的条件，因此直接查询返回了剩下的所有数据。

1.5对表数据修改

对表中数据进行修改，可以结合先前我们学习到的where子句和order by的排序规则，来对我们的表中数据修改，并且不同先前的查询对临时表改变数据，SQL的update修改语句，是可以真实在数据库中修改存储的数据

语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
 SET assignment [, assignment] ...
 [WHERE where_condition]
 [ORDER BY ...]
 [LIMIT row_count]

基本语法：update+表名+set+列名和数据（需要更改成什么样）+条件限制（帮助查询到要更改的部分）

特别注意：修改这一操作，需要先“查询”，查询到后才会对查询结果进行修改。

举例：

将孙悟空同学的数学成绩改为80分

可以看到现在孙悟空的数学成绩是78分，接下来我们对其成绩进行修改

可以看到我们修改后，孙悟空同学的数学成绩就变更为了80分

对总分最高的三位同学的数学成绩减去20分

通过这一SQL语句我们查询到了总分最高的三个学生，接下来对其总分减20

由于我们已经修改过了，之前三个总分最高的学生不一定还是总分最高，因此我们要直接指定它们查询，根据查询结果可以看到它们的分数确实由于数学成绩而下降了20分

将所有同学的语文成绩都翻倍

这先是所有同学原始的语文成绩，接下来我们就要进行翻倍操作

可以从查询到的结果看到，所有同学的语文成绩都得到了翻倍处理（后续已对其做回复处理，以便后续继续使用）

注意事项：

  1. update每次修改数据都是在数据库上修改，因此math+=20这种操作是不成立的，必须使用math=math+20
  2. update使用时，一定要加上where子句增添限制条件，否则会对整个数据表的所有数据都进行修改

1.6对表数据删除

语法：

DELETE FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]

一般语法形式：delete from table_name +限制条件

举例：

删除成绩为空的所有同学数据

可以看到这样我们就把只要有一科为NULL的学生数据全部删除掉了

1.7聚合函数

聚合函数

聚合函数
函数 说明
COUNT([DISTINCT]expr) 返回查询到的数据的数量
SUM（[DISTINCT]expr） 返回查询到的数据的总和，不是数字没有意义
AVG（[DISTINCT]expr） 返回查询到的数据的平均值，不是数字没有意义
MAX（[DISTINCT]expr） 返回查询到的数据的最大值
MIN([DISTINCT]expr) 返回查询到的数据的最小值

聚合函数是SQL自带的函数（库函数），针对查询数据进行数理统计之后返回，我们接下来会通过多个例子来向大家说明如何使用这些函数。

在这里解释一下“不是数字没有意义”这一句的含义：这是指这些聚合函数只对数据类型是‘int,decimal,double,bigint’这些数字类型的数据才能进行这些功能。当然如果字符串中含有数字例如“100李四”中的“100”这个数字字符串可以隐式转化成数字从而进行这些聚合功能的计算

COUNT

统计exam表中有多少条数据

我们查询整个exam表中的数据，最终可以显然看到共有7条数据。那么使用count的结果呢？

同样也是7条数据，其中*是查询表中所有数据时才会使用的，那么针对某个列呢？

统计语文成绩低于80分的同学数量

通过查询所有人的语文成绩，我们看到低于80分的共有5人

使用count针对语文成绩这一列的统计明显也是可行的

SUM

统计所有学生的数学成绩总和

根据查询的结果我们可以得到数学成绩总和是509分

使用SUM统计得到的结果明显也是正确的。

不能统计非数值的列

注意：SUM聚合函数使用时会自动过滤掉数据中的NULL值，因此才不会出现基本所有数据都有意义，因为一个NULL这个老鼠屎导致结果为NULL的情况

AVG（取平均）

由于取平均使用起来没有什么特殊之处我们这里只用一个例子来进行说明即可：

统计总成绩的平均分：

可以看到在使用聚合函数的时候，我们还能继续使用别名去方便读懂表中数据的涵义。

MAX,MIN

查询总成绩最高分和英语成绩的最低分

MAX和MIN可以一起使用，实际上聚合函数都可以互相搭配起来使用从而满足我们的需求。

注意：MAX和MIN本质是比较大小，因此对于字符串也是可以使用的，毕竟字符串也有自己的检验规则（区分谁大谁小）

通过这个例子我们就能看到MAX和MIN是可以参与到字符串的比较时，具体怎么使用以及最终的结果，根据使用场景下的检验规则决定。

假设一个语句中有order by,limit,where同时调用了聚合函数，那么执行顺序该如何呢？

  1. 遍历表，按照一次一行
  2. 执行where子句，把遍历的每一行数据代入条件
  3. 获取到满足条件的列，表达式求值，定义别名
  4. 执行order by对结果进行排序
  5. 执行limit进行输出限制长度
  6. 再进行聚合

2.多种查询方法

2.1分组查询

分组查询，重点就在于“分组”二字，一般重点是group by后跟的列。（对于指定的列，把这一列数值相同的值，分到一组）

语法：

SELECT {col_name | expr} ,... ,aggregate_function (aggregate_expr)
 FROM table_references
 GROUP BY {col_name | expr}, ... 
 [HAVING where_condition]

作用：将指定查询的列，按照数值相同的分到一组这一规则，结合聚合函数进行数据特殊处理

这是我们一会要用到的数据，后面例子查询出的表都是基于这部分的数据

统计每个角色的人数

可以看到分组查询其实和之前的select正常查询一样，只是进行了分组而已，这样我们就结合聚合函数，成功查询出了每个职业到底有几个人

统计每个角色的平均工资，最高工资和最低工资

我们还能使用多种聚合函数来对我们的数据进行处理

在这里我们需要总结一句：group by分组是首先执行的，数据是先被分好组之后才进行后面的操作的。

Having子句

对于group by分组之后的数据，对于分组的结果过滤，我们不能使用where子句，而是要使用having子句

统计平均工资低于15000的职位和它的平均工资

可以看到的是我们是按照职位进行分组的，工资低于15000是我们对于分组条件的补充，因此我们要使用having子句

where和having子句的区别

  • where子句用于对我们查询的真实数据进行限制，过滤
  • having子句是对我们分组结果进行过滤，补充

2.2联合查询

实际使用时，由于我们要遵循范式设计数据表，因此需要多个表的数据结合起来，才是一个完整的需要查询的数据，在这里的我们介绍联合查询，也就是多表查询。

多表查询是建立在笛卡尔积这一数学定义上的

笛卡尔积

简单来说，笛卡尔积可以看作是排列组合，只不过排列组合的先后顺寻是按照查询时写的SQL语句的顺序来决定，并且一定不能乱了先后顺序！！！

通过笛卡尔积对两个表进行运算之后，我们可以得到结果如下：

两个表的查询结果合并起来一起查询了

多表查询的标准步骤以及案例

一般情况来说，多表查询的步骤大体来说有五步

  1. 根据查询的条件，挑选出需要的表
  2. 把挑选出的多个表进行笛卡尔积操作
  3. 添加“连接条件”，去掉笛卡尔积中的无用数据（譬如，student.class_id=class.class_id）
  4. 根据需求，补充查询条件（一般是where子句等等）
  5. 对查询的最终结果进行精简

接下来，我们就按照这些步骤，稳扎稳打完成案例的代码书写

查询孙悟空的基本信息，包括学生信息和班级信息

这就是我们总体的代码，可以看到我们的查询语句不是一簇而成，而是经过不断的一步步优化而成的，只要按照上面的基本流程对查询语句优化，即可得到最终完善且满足需求的语句。

  1. 我们挑选出学生表和班级表作为查询的表
  2. 对学生表和班级表进行初步的笛卡尔积操作
  3. 添加连接条件：s.class_id=c.class_id（值得注意的是，这里我们对两张表进行了定义别名，这样方便我们限制条件的书写）
  4. 补充查询条件
  5. 对查询结果进行精简

2.3表的内连接和外连接

2.3.1内连接

上面的多表查询，实际上就是一种内连接查询，内连接查询的语法如下

语法： 
1 select 字段 from 表1 别名1, 表2 别名2 where 连接条件 and 其他条件;
2 select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 where 其他条件;

后面我们举例时，都会给出两种内连接书写形式的代码

查询唐三藏同学的成绩

第一种：

正常的查询过程跟多表查询类似，这里就不再赘述。

第二种：

可以看到两种语法查询到的结果是相同的，这里看大家更喜欢哪种自行挑选即可。

查询所有同学的总成绩，以及学生信息

第一种：

第二种：

2.3.2外连接

MySQL同样也提供了外连接，外连接同样可以进行多表查询，只是查询到的结果相较于内连接会有些许不同。

MySQL提供了左外连接，右外连接，全外连接，但是遗憾的是MySQL并不支持全外连接，因此这里不再赘述

语法：

-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;

查询没有参加考试的同学信息（左外连接）

可以看到，即使“不想毕业”同学没有考试成绩，但是还是被查询出来了，这是因为左外连接会以左侧表为主，全部展示出来，即使它并没有满足我们右边表的条件

查询没有同学的班级

这是右外连接，以右侧表为主，展示出来

关于左外连接和右外连接我们可以用大概这个图片来理解：

2.4自连接

自连接就是一张表，自己对自己取笛卡尔积，主要是为了把“行关系转换为列关系”，一般来说SQL的限制条件是对列有作用效果，因此需要自连接来解决问题。->主要是为了让行与行的数据进行比较

显示所有“MySQL”成绩比“JAVA”成绩高的学生信息

一般情况下我们查询成绩，这样内连接查询出来的成绩是分隔在两行，因此只能人力比较，有了自连接之后，我们就能实现行与行的比较，具体主要是对同一个表取两个别名进行比较

表连接实例

显⽰所有"MySQL"成绩⽐"JAVA"成绩⾼的学⽣信息和班级以及成绩信息

2.5子连接

子连接简单来说就是用一个select语句查询的结果作为另一个select语句查询的条件使用。

一般情况下我们很少使用子连接，因为我们的计算机语言追求的是可读性，如果嵌套太多语句不便于阅读，那么写的代码很难理解和修改，因此我们在这里介绍，但是希望大家以后可以尽量避开使用。

语法：

select * from table1 where col_name1 {= | IN} (
 select col_name1 from table2 where col_name2 {= | IN} [(
 select ...)
 ] ...
)

单行子查询

查询与“不想毕业”同学同班的其他同学

可以看到，我们拆分成两步进行，清晰地查询到了class_id=2的所有同学信息。至于为什么叫单行子查询，是因为嵌套查询条件返回的是一行数据，例如我们例子中嵌套语句返回的是class_id=2这一行数据。

多行子查询

查询“MySQL”或“Java”的成绩信息

可以看到这一次我们嵌套条件的select语句返回了两个课程的id信息，算是返回了两行数据，因此，取名为多行子查询的原因就是：嵌套条件返回的是多行数据，一般用IN关键字来连接

多列子查询

查询重复的数据

可以看到我们嵌套返回了三列数据（学生id，课程id，成绩），因此命名为多列子查询的原因就是嵌套条件可以返回多条列数据，而非行数据。

在From子句中使用子查询

查询所有比“java001”班平均分高的成绩信息

当⼀个查询产⽣结果时，MySQL⾃动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回 用户，在from⼦句中也可以使⽤临时表进⾏⼦查询或表连接操作。

这里我们就是把“java001”班的平均分看成了一个临时表，从而跟在from后面，作为一个表进行查询，将两个表中的数据进行比较。

2.6合并查询

实际运用中，有时我们会想把多个select语句查询出来的结果打包一起输出，因此我们可以使用UNION和UNION ALL集合操作符来处理。

在正式开始介绍之前，我们先列举出先前使用的数据表和新创建的数据表，以便大家观察这两个集合操作符的特性：

先前创建的student表：

新创建的student001表：

UNION

这个操作符会对两个结果集取并集，然后进行去重操作

实例：

查询student表中<3的同学和student001的所有同学

第一张表按照条件查询出来是唐和孙两个同学，第二张表查询出来四个同学，但是由于有重复的唐同学存在，因此将两个查询结果取并集的时候去重了，只有一个唐同学。

UNION ALL

这个操作符同样会对两个结果集取并集，但是它并不会对数据表进行去重操作

实例：

可以看到这一次查询出来的结果就有两个唐同学，其余都与UNION的查询相同，可以明显看到UNION ALL不会去重的特性。

内置函数

日期函数

日期函数
函数 说明
CURDATE() 返回当前日期，同义词CURRENT_DATE,CURRENT_DATE()
CURTIME() 返回当前时间，同义词CURRENT_TIME,CURRENT_TIME([fsp])
NOW() 返回当前日期和时间，同义词CURRENT_TIMESTAMP，也就是我们熟知的时间戳
ADDDATE(data,INTERVAL expr unit) 向日期值添加时间值（间隔），同义词DATE_ADD()
SUBDATE(data,INTERVAL expr unit) 向日期值减去时间值（间隔），同义词DATE_SUB()
DATEDIFF(expr1,expr2) 两个日期的差，以天为单位，expr1-expr2
·DATE（data） 提取data或datatime表达式的日期部分
  • 语法：select CURRENT_DATE()
  • 语法：select CURRENT_TIME()
  • 语法：select NOW（）和selec DATE(data)->提取给定日期+时间中的日期部分
  • 语法：select ADDDATE(data,INTERVAL ……)
  • 语法：select SUBDATE(data,INTERVAL……)
  • 语法：select DATEDIFF(expr1,expr2)

字符串处理函数

字符串处理函数
函数 说明
CHAR_LENGTH(str) 返回给定字符串的长度
LENGTH(str) 返回给定字符串的字节数，与当前使用的字符集有关
CONCAT(str1，str2，……) 返回拼接后的字符串
CONCAT_WS(separator，str1，str2，……) 返回拼接后带分隔符的字符串
LCASE(str) 将给定字符串换为小写，同义词LOWER()
UCASE(str) 将给定字符串换为大写，同义词UPPER()
HEX(str)，HEX(N) 对于字符串参数str，HEX（）返回str的十六进制字符串表示形式，对于数字参数N，HEX（）返回一个十六进制字符串表示形式
INSTR(str，substr) 返回substring第一次出现的索引
INSERT(str，pos，len，newstr) 在指定位置插入子字符串，最多不超过指定的字符数

SUBSTR(str，pos) OR

SUBSTR（str，FROM pos FOR len)

 返回指定位置的子字符串1
REPLACE（str，from_str，to _str） 把字符串str中所有的from_str替换为to_str，区分大小写
STRCMP(expr1，expr2) 逐个字符比较两个字符串，返回-1，0，1

LEFT（str，len），

RIGHT（str，len）

 返回字符串str中最左/最右边的len个字符
LTRIM(str)，RTRIM(str)，TRIM（str） 删除给定字符串的前导,末尾,前导和末尾的空格
TRIM（[]） 删除给定字符串的前导，末尾或前导和末尾的指定字符串

在这里，我们用一些常用的字符串处理函数举例说明即可，一般都是用Java来操作使用

  • 统计给定的字符串长度
  • 统计给定字符串占的字节数
  • 返回拼接后的字符串
  • 将数据列拼接起来，并且用指定符号隔开
  • 字符串的大小写转换
  • 从字符串中返回子字符串
  • 替代字符串中的子字符串
  • 比较两个字符串
  • 直接从左或者从右返回子字符串

数学函数

数学函数
函数 说明
ABS(X) 返回X的绝对值
CEIL(X) 返回不小于X的最小整数值，同义词是CEILING(X)
FLOOR(X) 返回不大于X的最大整数值
CONV(N,from_base,to_base) 不同进制之间的转换
FORMAT(X,D) 将数字X格式化为 ”#，###，###“的格式。四舍五入到小数点后D位，并以字符串形式返回
RAMD([N]) 返回一个随机浮点值，取值范围[0.0,1.0)
ROUND(X),ROUND(X,D) 将参数X舍入到小数点后D位
CRC32(expr) 计算指定字符串的循环冗余校验值并返回一个32位无符号整数
  • 返回-3.14的绝对值
  • 返回不小于20.36的最小整数值
  • 返回不大于11.32的最大整数值
  • 10进制转16进制
  • 返回一个随机浮点值
  • 舍弃到小数点后4位
  • 格式化1234567.654321

其他函数

》

其他常用的函数
函数 说明
version() 显示当前数据库版本
database() 显示当前正在使用的数据库
user() 显示当前用户
md5(str) 对一个字符串进行md5摘要，摘要后得到一个32位字符串
ifull(val1,val2) 如果val1是NULL，则返回val2，否则返回val1
  • 语法：select version()
  • 语法：select database()
  • 语法：select user()
  • 语法：select ifull(val1,val2)

那么基本的SQL语句操作就到这，我们介绍了最常用的“增删查改”以及多种操作，还有SQL自带的内置函数。后续我们会介绍一些MySQL的特性内容。本人才疏学浅，望诸位读者不吝赐教

# mysql # 数据库
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

当 AI 修图师装上“万能插头“：MCP 如何改变修图工作流

avatar AI编程社区
cover

2026年8月开发者实战指南：ChatGPT Plus / Pro + Codex 如何协同完成需求分析、编码、测试、调试与代码审查

avatar AI编程社区
cover

Claude API + Python实战：结构化输出后，如何拦住“看起来正确”的JSON

avatar AI编程社区