【关系型数据库】单表增删改查：详解ddl,dml,基础dql语言

admin管理员组

文章数量:1530835

📢本篇使用数据库软件mysql8.0
📢学习遵旨：再简单的代码都要至少手敲一遍！

【关系型数据库从0到1】单表增删改查：详解ddl,dml,基础dql语言

• 🚁数据库软件
• 🚁关系型数据库和非关系型数据库
• 🚁sql语言（sql语言由DDL语言，DML语言(其中包含DQL语言)，DCL语言组成）
• 🚁DDL语言（创建库/表，删除库/表，更改库(更改表的结构)
• • 1. 针对数据库的ddl和基础dml操作
  • • 🧑‍💻在mysql上创建数据库
    • 🧑‍💻查看mysql上有哪些数据库
    • 🧑‍💻选中要进行操作的某个数据库
    • 🧑‍💻删除某个数据库
  • 2. 针对表的ddl和基础dml操作
  • • 🧑‍💻创建二维表
    • 🧑‍💻查看当前选中数据库中的所有表
    • 🧑‍💻查看指定表的结构
    • 🧑‍💻删除指定表
    • 🧑‍💻更改表的结构
• 🚁sql中如何注释：--空格+注释内容或者#空格+注释内容
• 🚁基础DML语言(单表增删改查)
• • 🧑在表中新增记录insert
  • • 🧑‍💻单行数据 + 全列插入
    • 🧑‍💻单行数据 + 指定列插入
    • 🧑‍💻多行数据 + 全列插入
    • 🧑‍💻多行数据 + 指定列插入
    • • • • 一次插入多行数据的效率高于一次插入一行数据的效率
          • 如何插入datetime类型的数据
  • 🧑在一张表中查询数据select
  • • 🧑‍💻全列查询：mysql服务器把表中的所有行和列的数据都返回客户端(select *)
    • 🧑‍💻指定列查询：mysql服务器把表中的指定列的数据返回给客户端(select 列名)
    • 🧑‍💻列为表达式的查询(select 列名构成的表达式)
    • 🧑‍💻给列或者列构成的表达式或者表起别名
    • 🧑‍💻去重操作：用distinct修饰某个列/多个列
    • 🧑‍💻查询+排序：mysql服务器把查询到的数据排序后返回给客户端(order by子句)
    • 🧑‍💻条件查询：mysql服务器把表中符合条件的数据返回给客户端(where子句）
    • 🧑‍💻分页查询：提高mysql服务器的查询效率(limit子句)
    • 🧑‍💻聚合查询：通过聚合函数对数据进行一些简单的统计操作
    • 🧑‍💻group by子句：先对数据进行分组，针对每个分组，再分别进行聚合查询
    • 🧑‍💻having子句：先对数据进行分组，针对若干组，根据having子中的条件筛选出记录
  • 🧑修改表中的数据update
  • 🧑删除表中的数据delete
• 🚁ddl，dml，基础dql语法总结
• 🚁进阶DQL语言(多表增删改查)

🚁数据库软件

在过去，都是通过文件在计算机上保存和管理数据。但是用文件保存和管理数据存在以下弊端：

所以为了避开这些缺点，更好的在计算机上保存和管理数据，专家们研究出了专门保存和管理数据的软件——数据库。

这类软件可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。

🚁关系型数据库和非关系型数据库

不同数据库内部组织数据的方式不一样，基于这一点，数据库软件又分为关系型数据库和非关系型数据库。

• 关系型数据库：

关系型数据库就是：在数据库中使用 “二维表” 这样的结构来组织数据的。而一个关系型数据库就是由很多个二维表及其之间的联系所组成的一个数据组织。

二维表：有很多行很多列，每一行被称为一个记录，每一列被称为一个字段。所有行的列数相等，每个元素要与所在列的要求匹配。

常见的四大关系型数据库（Oracle，mysql，SqlServer，sqlite）介绍：

关系型数据库之间的差异很小，一般学会一种，其他也能容易上手。

• 非关系型数据库

非关系型数据库通常使用键值对，文档，列组，图型等结构来组织数据。

文档组织数据就是，一条数据就是一个文档，文档与文档之间可以差异很大。
键值对组织数据就是很直接的，什么等于什么的。

常见的非关系型数据库

• 关系型数据库与非关系型数据库的区别：
  
  OLTP（On-Line Transaction Processing）是指联机事务处理。
  OLAP（On-Line AnalyticalProcessing）是指联机分析处理。

从表格可以看出，关系型数据库和非关系型数据库各有优缺。所以通常都是关系型数据库和非关系数据库搭配使用。例如mysql和redis一般会搭配使用。

🚁sql语言（sql语言由DDL语言，DML语言(其中包含DQL语言)，DCL语言组成）

sql语言就是：操作关系型数据库命令的一个集合，sql语言中的每一条语句都是一个命令。

sql语言就是一个编程语言，掌握了sql语言，我们就能使用关系型数据库，在关系型数据库软件中进行存储和管理数据。本篇以关系型数据库mysql为例。

sql英文全称Structured Query Langeage。即结构化查询语言，它是一个通用的，功能性极强的关系数据库语言。
而它的功能性强主要体现在，sql语言包含DDL语言，DML语言，DQL语言，DCL语言，其中dql语言属于dml语言^1。

DDL全称data definition language，即数据定义语言。数据定义语言ddl的作用是维护存储数据的结构，包括创建数据的结构，删除数据的结构，更改数据的结构。体现在语法上就是create语句，drop语句和alter语句。

DML全称data manipulation language，即数据操纵语言，它的功能是操作数据，包括插入数据，删除数据，更新数据。体现在语法上就是， insert语句，delete语句，update语句。

DML中又单独分了一个DQL，（dql属于dml语言）
DQL全称data query language。即数据查询语言，它的功能就是查询数据。体现在语法上就是 select语句。

DCL全称Data Control Language，即数据控制语言。它主要负责权限管理和事务。体现在语法上就是 grant语句，revoke语句，commit语句。

sql语言的基本常识：

• sql语言对大小写不敏感，编写代码时英文大小写都可以。
• create，drop，database等都是sql语言中的关键字^{2，关键字一般不能作为数据库名，表名，列名等一系列我们在写代码时的自定义名字。如果自定义名字确实非要和关键字相同，用 反引号`3把自定义名字引起来。}

• 数据库的命名规则：

• 每个单词之间空格隔开，一条语句写完后用；结尾。

🚁DDL语言（创建库/表，删除库/表，更改库(更改表的结构)

ddl:数据定义语言

通过上面，相信大家已经对数据库软件，关系型数据库mysql，ddl数据定义语言有所了解。接下来总结ddl语言的用法。

一个mysql服务器程序^{4，可以在硬盘上组织保存很多数据库（此处的数据库指的是一个逻辑上的数据集合，也就是mysql里面存的一张表或者有关联关系的很多张表，ddl语言针对的对象就是该含义下的数据库）。}

1. 针对数据库的ddl和基础dml操作

🧑‍💻在mysql上创建数据库

1. 语法

create database 要创建的数据库名;

2. 数据库创建的时候不能重复。如果创建了一个已经存在的数据库，mysql就会报错。
   在创建时在database后面加一个if not exists，此时就不会因为数据库同名而报错^{5。（mysql发现同名数据库已经存在，不会执行该创建数据库的命令）}

create database if not exists 要创建的数据库名;

3. 创建数据库的时候，可以选择手动指定一下数据库采用的字符集（charset，全称character set）和数据库字符集的校验规则（collate）。如果没有指定字符集和校验规则，mysql8系统默认字符集是utf8（mysql5.7默认字符集是拉丁文），校验规则是是utf8_ general_ ci。

create database if not exists 要创建的数据库名 charset 字符集;

• 这里为什么建议手动指定字符集？因为MySQL8的utf8编码不是真正的utf8（残本），没有包含某些复杂的中文字符并且少了一些emoji表情。（mysql5.7的拉丁文编码不支持中文）。MySQL真正的utf8是使用utf8mb4，所以建议大家手动指定字符集为utf8mb4。
  

🧑‍💻查看mysql上有哪些数据库

列出当前的mysql服务器上一共有哪些数据库。

1. 语法

show databases;

2. 一般修改电脑配置都可以通过mysql数据库来操作，此操作有大大滴风险，需慎重！！！

🧑‍💻选中要进行操作的某个数据库

这个操作可以说是最关键的操作了。
很好理解，mysql服务器上有很多数据库，表从属于数据库，（各个数据库之间不可能同名，同一个数据库中的表名也不可能相同，但是不同数据库中表名有可能重名）此时你要对某张表进行操作（增删查改），此时要先选中该表所在的数据库，在进行表操作。

use 要进行操作的数据库名;

🧑‍💻删除某个数据库

1. 语法

drop database 要删除的数据库名;

2. 不能删除不存在的表。如果要删除一个不存在的数据库，mysql就会报错。
   在删除时在database后面加一个if exists，此时就不会因为要删除不存在的数据库而报错。（mysql发现数据库不存在，不会执行此命令）

drop database if exists 要删除的数据库名;

3. 删除某个数据库的这个操作，删掉的不仅仅是数据库，这个数据库中所有的表，和表里的数据都被删掉了！！！所以在实际生活中，删除数据库是一个非常危险^{6的操作。}

2. 针对表的ddl和基础dml操作

mysql服务器上有很多数据库，表从属于数据库。一个数据库由一张表或有关联关系的多张表组成。所以在进行针对表的任何操作前，都需要先选中该表对应的数据库。

表操作的前提：use 要进行操作的表从属的数据库名;

🧑‍💻创建二维表

1. 语法

create table 要创建表的表名
(表的第一个列名 该列中元素的数据类型,
表的第二个列名 该列中元素的数据类型);

2. mysql中支持的数据类型
   二维表中的每一列都有一个具体的数据类型，在mysql中支持的数据类型主要分为三大类：数值类型9个，字符串类型4个，和日期类型2个。
   在这其中，我们只需要掌握最重要的六个（int，bigint，double，decimal(m,d)，varchar(size)，datetime）即可。

• int，bigint不必多说，用法和java/c语言/c++一样。
• float，double用法同样和java/c语言/c++一样，都是IEEE 754标准下的浮点数。存在误差，且会发生精度丢失^7。
• decimal（m，d）是精度更高的浮点数（它是非IEEE 754标准下的浮点数，是用其他的方式来存储小数的）。但是它占用的空间更多，运算速度也相对较慢。
  • 休闲娱乐谈论一下：如果此表中保存的数据和钱有关，此时的数据类型应该选择什么呢^8？？？

• sql中没有字符这个类型，所以使用'单引号'或"双引号"来表示字符串都是可以的！

• varchar（size）是可变长度字符串类型。里面的size表示的是该类型空间里最多存储几个字符(不是字节^{9)。例如，varchar（20）的意思是该空间最多放20个字符串。在使用时，它会先根据实际情况分配一个较小空间，如果不够，自动扩展，最大不会超过20个字符串。而不是最初立马分配20个字符串大的空间供你使用。}

  • 休闲娱乐讨论一下：这里的size怎么确定呢¹⁰

• text和mediumtext是文本数据类型，文本数据类型里面存储字符，这些字符都可以在对应码表上查到。在码表上查不到的字符，例如音乐，图片，视频等，就是二进制数据，存储在blob类型^11中。

🧑‍💻查看当前选中数据库中的所有表

show tables;

🧑‍💻查看指定表的结构

1. 语法

desc 表名;

desc英文全称describe，描述的意思。

2. desc表中的相关词的含义：

• field：字段，即列名
• int（11）：此处的11，同varchar（20）里面的20一样，都是该类型能存储的最大字符的数量。11指的是显示这个int类型的时候，最多占据11个字符的宽度。与int的自身大小4byte无关。（同类型字符在不同字符集下占的字节大小不同，不同类型占的字节大小更不同了）
• desc表中的NULL列，key列，default列都是对对应表student某列的约束。
  • 例如NULL列约束：指定表的某列不能为空值。当某列的NULL约束对应YES时，代表，表中对应的该列的元素取值可以为空值（该列可以不填）。当某列的NULL约束对应NO时，代表，表中对应的该列的元素取值必须填值，不能为空。例如上图中，student表的id列和name列的NULL约束都为yes，代表在student表中插入某名学生信息时，该学生的id和name都可以不填。实际情况中，在一张student表中，我们一般约束学生id这一列不能为空（id列的null约束为no）。
  • 为了保证数据库中能够被避免插入或修改一些非法数据，数据库设置了能自动对数据的合法性进行校验检查的一系列机制，以上提到的三个关键字NULL，key，default都属于该机制下的内容。详情见专栏sql语言：关系型数据库约束。
• extra：对表某列进行备注或解释说明，有点像代码中的注释。

🧑‍💻删除指定表

1. 语法

drop table 要删除的表名;

删除表的时候，也会把表里的数据一起删除掉。所以删除表和删库一样都是很危险的操作，一定要慎重！！！

2. 悠闲娱乐来谈论一下：删表和删库哪个更危险？？？^{12见注脚10。}

🧑‍💻更改表的结构

🚁sql中如何注释：–空格+注释内容或者#空格+注释内容

在SQL中可以使用--空格+注释内容来表示注释说明。

🚁基础DML语言(单表增删改查)

dml：数据操纵语言
dql：数据查询语言

CRUD 即增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

🧑在表中新增记录insert

向表中增加内容，是以行为单位进行的，可以一次增加一行数据，也可以一次添加多行数据。
先创建一张有两列(第一列id，第二列name)的学生表（此时表为空）：
以此表为例进行插入元素：

🧑‍💻单行数据 + 全列插入

insert into 表名 values
（第一个要插入的元素，第二个要插入的元素，... ，最后一个要插入的元素）;
-- 插入的元素和列的个数，类型都要相匹配

在student表中插入一条记录：id为1，name是张三的学生。

🧑‍💻单行数据 + 指定列插入

insert into 表名(要插入的列名1,要插入的列名2,...) values
(要插入的元素1，要插入的元素2，... );
-- 要插入的元素和列的个数，类型都要相匹配

在student表中插入一条记录：name是李四的学生。此时该学生的id为默认值，默认值默认情况下为null（想修改默认值，详情见专栏关系型数据库的三大约束——default约束）

---

经过上面两次插入操作，此时student表如下：

---

🧑‍💻多行数据 + 全列插入

insert into 表名 values
(第一条记录要插入的元素1，要插入的元素2 ... ),
(第二条记录要插入的元素1，要插入的元素2 ... ),
...;
-- 每条记录中插入的元素和列的个数，类型都要相匹配

在student表中插入三条记录：
id为1，name是小明的学生。
id为2，name是小红的学生。
id为3，name是小张的学生。

🧑‍💻多行数据 + 指定列插入

insert into 表名(要插入的列名1，要插入的列名2...) values
(第一条记录要插入的列名1对应的元素1，要插入的列名2对应的元素2 ... ),
(第二条记录要插入的列名1对应的元素1，要插入的列名2对应的元素2 ... ),
...;
-- 每条记录中插入的元素和列的个数，类型都要相匹配

在student表中插入两条记录：
一位id为12的学生和一位id为13的学生

---

经过上面四次插入操作，此时student表如下：

---

一次插入多行数据的效率高于一次插入一行数据的效率

一次插入多行数据，相比于一次插入一行数据，分多次插入，时间上要快很多。这是因为，mysql作为一个客户端-服务器结构的程序，我们每对表格进行一次操作（比如一次插一条记录或一次插多条记录），此时客户端和mysql服务器就要进行一次网络交互。客户端向mysql服务器发送一次请求，服务器接收请求，处理数据后再反馈给客户端结果。
虽然一次插多条记录比一次插一条记录的体积搭上不少，但这就相当于扔垃圾，假如寝室有三包垃圾，楼下只有一个垃圾处理站。下楼一次，一次性把三包垃圾都扔掉的效率明显高于下楼三次，分三次把垃圾都扔掉的效率。

如何插入datetime类型的数据

1. 可以用一个固定格式的字符串"年-月-日 时 时:分:秒"，来表示时间日期。
2. 如果想填写的时间日期是当前时刻，sql中提供了一个现成的函数now()

先给student表新增一个列time，数据类型datetime。(student表此时已清空)。


此时的学生表：

🧑在一张表中查询数据select

mysql是一个客户端-服务器结构的程序！
进行查询操作时，客户端通过网络向服务器发送查询请求，mysql服务器接受请求，查询数据后生成临时表，以临时表为依据根据查询条件处理数据，，再生成新的临时表。然后通过网络，把新的临时表返回给客户端。最后在客户端显示出来。

先在mysql上创建下面这张表，以此表为例进行查询操作。

-- 创建此表源码：
-- 创建考试成绩表
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- 插入测试数据
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,'唐三藏', 67, 98, 56),
(2,'孙悟空', 87.5, 78, 77),
(3,'猪悟能', 88, 98.5, 90),
(4,'曹孟德', 82, 84, 67),
(5,'刘玄德', 55.5, 85, 45),
(6,'孙权', 70, 73, 78.5),
(7,'宋公明', 75, 65, 30);
(null,'小明',null,null,null);
(null,'小红%',null,null,null);

🧑‍💻全列查询：mysql服务器把表中的所有行和列的数据都返回客户端(select *)

1. 语法

select * from 列所在的表名; 

例如：查询exam_result表中所有数据：

2. 此时mysql服务器和客户端的行为：
   mysql客户端把全列查询的请求发给服务器之后，mysql服务器把表的所有数据组织成临时表返回给客户端。客户端显示出来。

3. 休闲娱乐讨论一下：全列查询这个操作，在实际开发中是一个危险操作^13！

🧑‍💻指定列查询：mysql服务器把表中的指定列的数据返回给客户端(select 列名)

一个表的列数，可能是非常多的。针对具体场景，指定要查询的列，mysql服务器查询指定列的所有元素并返回给客户端。

1. 语法

select 要查询的列名1,要查询的列名2,... from 列所在的表名;

例如：查询exam_result表中所有学生名字和他们的语文成绩：

2. 此时mysql服务器和客户端的行为：
   mysql客户端把指定列查询的请求发给服务器之后，mysql服务器把要查询的列的所有数据组织成临时表返回给客户端。客户端显示出来。

🧑‍💻列为表达式的查询(select 列名构成的表达式)

1. 语法：

select 列名构成的表达式1，列名2,... from 要查询的表名;

2. mysql数据库的行为：
   当要查询的列名，变成列名构成的表达式时，传递给mysql服务器进行查询操作时，服务器根据列名查数据，生成临时表，把临时表中每一列中所有行的数据依次代入该列名构成的表达式中，进行计算，最后组织成新临时表返回给客户端，客户端显示出来。

表达式查询是单个列，或者列 和列之间的运算，是把每一行的列元素都代入到表达式的运算中。千万不要认为是行和行之间的运算！！！

可见，查询字段为表达式的查询操作，服务器虽然进行了运算，但并不没有修改数据库服务器上的原始数据。

2. 话不多说，实操加深理解。

• 表达式中不包含字段：
  
• 表达式中包含一个字段：
  例如：查询所有同学，英语成绩都-10的效果：
• 表达式中包含多个字段
  例如：查询所有同学的总成绩：
  表达式中包含多个字段时，该表达式应该尽量简单，过于复杂的表达式不容易被人理解。

🧑‍💻给列或者列构成的表达式或者表起别名

当列或者列构成的表达式或者表，他们的名字很长，或执行一些特殊操作（大部分多表查询操作）时，一条sql语句中可能会涉及到相同的列或列构成的表达式或者表，此时我们就可以在给他们起别名，在该条sql语句中，用这个别名替代表原来的名称。
在为表取别名时，要保证不能与数据库中其他表的名称冲突。

1. 起别名的语法：

select 列名/列名构成的表达式 as 别名 from 表名 as 别名;

例如：查询每个人的姓名和总分

2. mysql数据库的行为：
   当MySQL客户端把要查询的列名（已定义别名），或列名构成的表达式（已定义别名），或表（已定义别名）传递给mysql服务器进行查询操作时，服务器根据列名/表达式查数据，生成临时表，此时临时表的列名是别名，最后将临时表返回给客户端，客户端显示出来。而整个过程中，表别名只有mysql服务器进行查询操作的时候起到作用，mysql服务器并不会把表别名返回给客户端。

表别名只在执行查询的时候使用，并不在返回结果中显示，而列别名定/列构成的表达式别名定义之后，将返回 给客户端显示，显示的结果字段为字段列的别名。

🧑‍💻去重操作：用distinct修饰某个列/多个列

用distinct修饰某个列，以该列元素为标准，在行与行之间，对应的列值相同的行，只保留一个。
用distinct修饰多个列，以多个列元素为标准，在行与行之间，多个列元素值对应都相同的行，只保留一个。

1. 语法:

select distinct 列名 from 表名;
select distinct 列名1,列名2,... from 表名;
select distinct * from 表名;

2. 此时mysql服务器和客户端的行为：
   mysql服务器根据具体的查询请求查询数据，生成临时表，对临时表进行去重操作，生成新的临时表，把新临时表返回给客户端，客户端显示出来。

🧑‍💻查询+排序：mysql服务器把查询到的数据排序后返回给客户端(order by子句)

mysql服务器对数据排序的时候是以行为单位的。以某一列或某几列作为比较规则，按照升序或者降序对数据进行排序。

1. 语法

select 要查询列的列名 from 表名 order by 以该列进行排序规则的列名 asc/desc;
-- asc是升序，desc是降序，如果省略，默认按照升序排。

2. 此时mysql服务器和客户端的行为：
   mysql客户端把查询数据+给数据排序的请求发给服务器之后，服务器查询数据，并且把查询到的结果进行排序组织成二维表，再返回给客户端，客户端显示出来。
   此时排序虽然是服务器完成的，但仍然是针对临时数据展开的，不影响原有数据再mysql服务器上的存储内容和顺序
3. 屁话不多说，上实操：

• 查询所有学生的姓名和数学成绩，并按照数学成绩升序排序

  • 排序是null默认为最小值。

• 查询所有学生的姓名和数学成绩，并按照语文成绩降序排序

  • 作为排序规则的那一列，即使没有在select时候要求查询，这并不影响选择该列作为排序规则依据。（因为是服务器处理排序，而不是客户端！）
• • order by可以指定多个列作为排序规则。例如查询所有学生的姓名和数学成绩，并按照数学成绩升序，语文成绩降序排序。（即先按照数学成绩程序来排序，如果数学成绩相同，再按照语文降序来排序）

• order by可以用列构成的表达式作为排序规则：

  • 例如查询所有学生的姓名和语文成绩，并且按照总分来排序。
  • 查询所有学生的姓名和总分，并且按照总分来排序。
    • 直接先查再排序
    • 别名+字段是表达式查询，的排序：
      
    • null参与各种运算，结果还是null。

5. 休闲娱乐谈论一下：如果一条sql查询语句没有加order by子句，此时显示出的查询结果的顺序是怎样的呢^14？

🧑‍💻条件查询：mysql服务器把表中符合条件的数据返回给客户端(where子句）

明确要查哪几列数据（select），明确要查询的数据，它符合哪些具体条件（where）。服务器根据这些条件对所有数据进行筛选，最终只返回给客户端符合条件的数据。

1. 语法：

select 明确列的列名1,列名2,... from 列所在的表名 where 数据符合的条件;

2. 此时mysql服务器和客户端的行为：
   mysql客户端把条件查询的请求发给服务器之后，mysql服务器会遍历该表的每一行记录，把每一行指定列对应的数据分别代入对应的条件中。如果条件成立，这个数据就会被放入结果集合中，如果条件不成立，这个数据就pass。遍历完整张表后把结果集合组织成表返回给客户端，客户端收到后显示出来。
3. sql中条件如何表达，where子句怎么写：列名/列名构成的表达式+运算符+限制范围where条件中不能使用别名！！！具体解释见下面基本查询中最后一个例子：查询总成绩小于200的学生姓名和他们的总成绩

• sql中没有==，sql中直接用=或<=>表示比较运算符相等。且要注意的是，在sql中null=null的结果是null（即false），null<=>null的结果才是true。
• 现在sql中通常用！=表示比较运算符不等于。
• AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分。

5. 总结的再多不如实际操作，话不多说，看以下例子：

• 基本查询（where子句中的运算符是>, <, =, <=> ,!=, <= ,>= ）：
  • 查询英语不及格的同学名字及英语成绩
    
  • 查询语文成绩好于英语成绩的同学名字，并显示他们的语文英语成绩
    
  • 查询语文成绩为null的同学姓名和语文成绩（写法1）
    
• 字段是表达式的查询（可有别名）+条件查询（不能有别名）
  • 例如：查询总分小于200的学生姓名和他们的总成绩
    
    
    
    
  • where子句中为什么不能使用别名：一条sql查询语句中如果既有as关键字又有where关键字，客户端把请求发给mysql服务器后，mysql服务器先根据where子句筛选数据组织成临时表，再根据别名生成新临时表。除非where子句能嵌套as关键字，否则MySQL是不可能在处理where子句时未卜先知出现的别名代表的意思的。

• 逻辑查询（where子句中用到的运算符是and，or，not）：
  • 查询语文成绩大于80分，且英语成绩大于80分的同学名字以及他们的语文英语成绩
    
  • 查询语文成绩大于80分，或者英语成绩大于80分的同学名字以及他们的语文英语成绩
    
  • sql中AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分。
    

• 范围查询（where子句中用到的运算符是between a and b或in(option1,option2,...)）：
  列名 between a and b表示条件：该列中数据是否处在a到b这段连续的范围中（范围包括a和b）。

  • 例如查询语文成绩在 [80, 90] 分的同学及语文成绩(写法1)
    
    用between a and b限定范围的子句还可以用基本查询运算符和逻辑查询中的and运算符共同表示，例如再次查询语文成绩在 [80, 90] 分的同学及语文成绩(写法2)：
    

  列名 in(option1,option2,...表示条件：该列中的数据是否与in中的某个option值相等。 in(option1,option2,...表示一个离散的集合。

  • 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩(写法1)。
    
    用in(option1,option2,...限定范围的子句还可以用基本查询运算符和 逻辑查询中的or运算符 共同表示。再次查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩(写法2)：
    

模糊查询(where子句中用到的运算符是like)：

• 列名 like 模糊条件表示的条件：该列中的数据是否符合模糊条件，把符合的数据放入结果集合中。
• 模糊条件主要由 通配符（必须有）+确定字符（可无) 构成，如果模糊条件中不含通配符，则模糊查询的效果和基本查询中=的效果一致。
  通配符：就是一些特殊的字符，能表示特定的含义。
  • 例如%，当它作为通配符时，它表示任意个（包括0个），任意的字符。
  • 例如_,当它作为通配符时，它表示一个，任意的字符。
• 例1：查询姓孙的同学的姓名。
  
• 例2：查询姓孙，而且名字是两个字的同学的姓名。
  
• 例3：查询名字里有%的同学的姓名。
  
• 休闲娱乐扩展一下：在sql语言中，如果一个符号既是通配符，也是普通符号，在用的时候如何区分它们呢？
  • sql语言中如何区分一个符号是通配符还是普通符号：借助escape关键字进行两个含义之间的转义。一个有两义的符号默认情况下是特殊含义，此时如果想用它的普通含义，就在用特殊含义语句后面加上escape关键字，escape后面再跟着一个字符(该字符自定义)，该字符是什么，MySQL就会把那个符号当做转义符。把转义符放在需要转义的符号前面，即可完成转义。
  • 
    更多详情见参考文章：mysql模糊查询大全

null的查询（where子句中用到的运算符是is null和is not null）

• 列名 is null表示条件：该列中的每个数据是否为空。把值为null的数据放入结果集合中。
• 列中的数据是非空值是，很明显非空值 is null的结果是false。这里要注意的是，假如列中的数据是一个null值，此时null is null的结果是true，它等价于null<=>null。
  • 查询语文成绩为null的同学姓名和语文成绩（写法2）
    
• 列名 is not null表示条件：该列中的每个数据是否为空。把值不为null的数据放入结果集合中。

🧑‍💻分页查询：提高mysql服务器的查询效率(limit子句)

在上面提到的所有查询操作中，全列查询，指定列查询，列为表达式的查询，给列/列组成的表达式起别名，条件查询，它们的主要查询操作都是以列为单位进行的。在查询+排序操作中，其中的查询操作也是以列为依据，在排序时候才是以行为单位进行。去重操作是以行为单位进行的。而接下来我们要学习的分页查询，也是以行为单位进行的。

查询+处理数据完成后，mysql服务器会把筛选出来符合条件的所有结果集合一次性全部返回给客户端。当筛选出来的数据非常多时，一次性全部显示出来，既影响效率，同时不方便查看。而通过分页查询，我们可以让mysql只返回结果集合中符合条件的一部分数据（以行为单位）。

1. 语法

-- []中的内容可有可无
-- 起始记录为第 0条记录
-- 从 第0条记录 开始，从上到下顺序筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 第s条记录 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 第s条记录 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

• 休闲娱乐讨论一下：offset偏移量¹⁵

2. mysql服务器和客户端的行为（limite子句是如何提高mysql服务器的查询效率的）：
   客户端把查询请求传给服务器，如果查询请求中包括分页查询，服务器筛选数据时候就会有所注意。只要筛选到符合条件的记录，且记录数量少于limit子句要求的数量，就继续筛选，反之，则停止筛选。处理数据后将数据返回给客户端。在这个过程中，mysql服务器不必遍历表的所有数据，且返回结果的时候也不必返回所有符合条件的数据，很明显提高了查询效率。
3. 实践出真知：针对exam_result表，按 id 进行分页，每页 3 条记录，分别显示 第 1、2、3 页

-- 第 1 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;
-- 第 2 页
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;
-- 第 3 页，如果结果不足 3 个，不会有影响
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;

🧑‍💻聚合查询：通过聚合函数对数据进行一些简单的统计操作

在上面的查询操作中，我们已经知道排序，去重，分页都是以行为单位进行的查询(相关)操作。全列查询，指定列查询，列为表达式的查询，给列/列组成的表达式起别名，条件查询，它们都是以列为单位进行的查询(相关)操作，其中，列为表达式的查询和条件查询还涉及到列与列之间进行运算的操作。

而聚合函数，相当于数据以行为单位(表经过行，列筛选后，此时聚合函数针对的是一张单列的临时表)，且在行与行之间进行对应计算的查询操作。

sql语言中提供了一些“聚合函数”，通过调用聚合函数完成行与行之间对应数据的运算。

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

聚合函数中，函数名，（），expr三者为一体，之间不能有空格。在给列起别名操作中，其中关键字as可以省略。聚合函数这里要求不能有空格，就是为了避免和给列起别名（省略as）之间引发歧义。

1. 语法：

select 聚合函数 from 表名 where 筛选行;
-- 没有where条件时代表所有行符合条件

2. 此时mysql服务器和客户端的行为：mysql客户端把条件查询的请求发给服务器之后，1. mysql服务器会遍历该表的每一行记录，筛选出符合条件的记录组成临时表。2. 在临时表中，根据指定列（或列构成的算术表达式）继续筛选(计算)数据，生成新的临时表。3. 对临时表中数据，以行为单位进行相应的聚合运算，最后把运算结果返回给客户端，客户端收到后显示出来。
3. 实践出真知（依然以下列exam_result表为例）：
   

• 聚合函数COUNT([DISTINCT] expr)：返回查询到的数据的数量。expr可以是一个列名、或者是*或者0。expr不能是任何表达式。
  • 例如统计班级里共有多少名同学：
    写法1：expr为*或0，返回查询到的数据的数量，此时若某行数据为null，数据存在意义。
    
    写法2：expr分别为第二个列名name和第一个列名id。返回查询到的数据的数量，此时若某行数据为null时，数据无意义，即不计入数量。
    
  • 条件查询+聚合查询，先条件查询筛选出符合条件的行，再聚合函数count计算指定列中数据的数量。例如统计班里共有几名同学语文成绩大于85。
    
  • 去重+聚合查询：当expr是一个列名时，该列名可以被distinct修饰。对数据进行去重操作后在计算此时数据的数量。
  • 在执行其他查询操作后，例如全列查询exam_result表后，mysql客户端会在查询结果底部显示以行为单位的数据的数量。所以为什么还要用聚合函数count计算数据的数量呢？？？
    首先：查询结果底部显示数据的行数这是mysql客户端内置的功能。此时我们刚好是在mysql客户端写代码，发送请求给mysql服务器处理请求，再响应结果回客户端显示出来。但是如果我们通过代码操作服务器，直接在服务器运行代码显示结果，可就看不到数据的行数了。
    其次：通过聚合函数count()得到的结果，还能参与各种算术运算，搭配其他sql语句使用。
    

• 聚合函数sum([DISTINCT] expr)：返回查询到的数据的总和（把最终临时表列的若干行，进行求和算术运算），数据必须时数字16。expr可以是一个列名、一个列名构成的算术表达式，或者多个列名构成的算术表达式
  • 若某数据不是数字或某数据为null时，数据无意义/null进行任何运算，结果仍然是null，即数据（非数字/null）不参与求和运算。例如求和所有同学的名字。
    
    
  • expr为算术表达式时，mysql服务器先根据where条件筛选记录，生成临时表。在根据列所在 的算术表达式计算列，生成新的临时表。最后对新的临时表进行sum运算。例如统计所有同学的所有成绩和。
    
  • 剩下三个聚合函数同sum一样。
  • 条件查询筛选行。去重筛选重复的列。

🧑‍💻group by子句：先对数据进行分组，针对每个分组，再分别进行聚合查询

在select中，可以使用group by子句对指定列进行分组查询。即先通过group by子句，把指定列中，值相同的行分为一组。这样就会得到若干组。然后再通过聚合函数，分别对每一组依次进行聚合查询。

1. 语法

select 指定列(按该列进行分组) 聚合函数 from 表名 group by 指定列(按该列进行分组);

2. 实践出真知：

以下面这张表为例进行查询操作：

-- 建表：
create table emp
(id int,name varchar(20),
role varchar(20),salary int);
-- 插入数据
insert into emp values
(1,'张三','程序猿',10000),
(2,'李四','程序猿',11000),
(3,'王五','程序猿',12000),
(4,'赵六','产品经理',9000),
(5,'田七','产品经理',9500),
(6,'周八','老板',100000);

• 查询六个人的平均工资（只进行聚合查询）
  
• 查询每个角色的平均工资（先分组再聚合查询）
  
• 如果只分组，而不进行聚合查询的话，此时的查询结果就是每一组中的某个代表数据。但是，通常group by子句和聚合函数都是搭配使用，否则group by子句无意义！通过更改mysql的配置项，可以强制规定group by子句和聚合函数必须一起使用，否则就报错。为什么不是第一个数据而是某个代表数据，文末注脚14已解释。例如：查询每个角色的平均工资（只分组）
  
  • 休闲娱乐拓展一下：配置项
    有些程序，有非常多的功能。有些时候只想使用其中的一部分功能，其中有的功能有很多选项，此时如果我们想使用其中一个选项。就可以在配置文件中，通过设置配置项，使程序按照我们的要求去运行。比如空调，空调有调节温度，净化空气等功能，调节温度这个功能里面又有制冷，制热等选项。这时候我们只要在遥控器上按一下制冷或者制热，就可以让空调按我们要求去运行。其中，制冷，制热都是配置项。而空调是给老百姓用的，所以专门有遥控器让使用者能简单快速的设置配置项。但是程序员用到的很多较专业的软件，功能更多，可设置空间更大，一般会有一个“图形化”界面（类似于空调遥控器）简化配置项的设置。比如idea。但是程序员用到的更多的专业软件，一般直接给你一个文件，通过编写文件的内容起到设置配置项的效果。在配置文件中，可以开启，关闭，设置某个功能，每个功能有一或多个配置项（功能和配置项相当于键值对），编辑文件即生效。

🧑‍💻having子句：先对数据进行分组，针对若干组，根据having子中的条件筛选出记录

区分where子句和having子句：having子句是，通过group by子句已经把数据分为若干组后，对若干组再进行条件筛选，此时的条件是having子句中的条件。而where子句里的条件是在group by子句之前进行的。
在一条sql语句中，如果三个子句都有，按照where子句，group by 子句，having子句顺序，服务器依次执行。

1. 语法

select 指定列(按该列进行分组) 聚合函数 from 表名 [where 条件] group by 指定列(按该列进行分组) having 条件;
-- 三个子句的书写顺序没有硬性规定
-- having后的条件一般是，聚合函数

2. 实践出真知：以group by 中的表为例：

• 例如：查询每个岗位的平均工资，但是排除张三
  
• 例如：查询每个岗位的平均工资，但是排除平均薪资超过2w的结果。
  
• 例如：查询每个岗位的平均工资，不算张三，并且保留平均值小于2w的结果
  

🧑修改表中的数据update

确定表，确定符合条件的行，修改符合条件的行中，一列，或者一些列中的数据。

1. 语法

update 表名 set 列名=值,列名=值,... 查询条件(where子句/order by子句/limit子句);

• 查询条件:限制这次操作，具体要修改哪些行的数据。如果不写查询条件句，相当于对所有行都进行修改。
• 这里的=相当于赋值的意思
• 修改后的新值也要在列对应的数据类型范围内
• 休闲娱乐谈论一下：set在计算机中都有哪些含义？¹⁷

2. 实践出真知：
   以此表为例进行修改表中的数据：
   

• 将孙悟空同学的数学成绩变更为 80 分
  
• 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
  
• 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分(null参与各种运算，结果还是null。)
  
  • 总成绩倒数前三的 3 位同学都找到了Rows matched: 3，都给他们成功进行了math=math+30的操作Warnings: 0，但实际上只有一位同学的数学成绩改变了 Changed: 1。这是因为，排序的时候null值被认为是最小值，且null参与各种运算，结果还是null。
• 将所有同学的语文成绩更新为原来的 2 倍
  
• 将所有同学的语文成绩更新为原来的 1/2 倍
  

🧑删除表中的数据delete

确定表，确定符合条件的行，删除符合条件的行。

1. 语法

delete from 表名 查询条件(where子句/order by子句/limit子句);

• 查询条件：限制这次操作，具体要删除哪些行。如果要删除行中的某个数据，只需要更新数据值为null值即可。
• 如果不写查询条件句，相当于删除整张表的数据。

2. 实践出真知：
   以此表为例进行修改表中的数据：
   

• 删除孙悟空同学的考试成绩
  
• 删除整张表的数据
  

3. 比较delete from table_name;和drop table table_name;前者只是删除表中的所有记录。后者不但把表中的所有记录都删了，还把表删了（即此时表在对应数据库中不存在了）。
   

🚁ddl，dml，基础dql语法总结

详情见专栏。

🚁进阶DQL语言(多表增删改查)

多表增删改查（重点是查）需要掌握关系型数据库的数据约束后才能更好的学习。
欲知多表增删改查的更多详情，
请先移步

• sql语言：关系型数据库约束（若已知，可跳过此步）。

再移步：

• 进阶DML语言(多表增删改查)

---

完结~
相信读完本篇，你已经对sql语言有了一个初步掌握。关于关系型数据库的三大约束，以及用sql语言进行多表增删改查，都是数据库的核心知识！！！详情见专栏。我也会继续总结关系型数据库的其他内容。最后，你的认可是我更新的动力！欢迎一起讨论学习进步~

---

1. 你可以认为sql语言是由DDL语言，DML语言，DQL语言，DCL语言四个语言组成的，你也可以认为sql语言是由DDL语言，DML语言，DCL语言三个语言组成的。因为实际上DML语言中包含DQL语言，dml语言是数据操纵语言，即对数据进行增删查改，dql数据查询语言，即对应dml语言中的查。因为dql太关键所以被单独拎了出来。 ↩︎

2. 关键字：sql语言中有特殊含义的单词 ↩︎

3. 反引号`在键盘哪里：tab键上方，esc键下方，英文模式下直接按。实际上，此时，表和库的名字是反应号create反应号，而关键字是create。
   ↩︎

4. mysql数据库通过硬盘存储数据，redis数据库通过内存存储数据。 ↩︎

5. 防止报错的重要性：实际工作中，是把一系列sql语句写到一个文件中，批量执行的。在批量执行的情况下，如果一条sql语句报错，后面的sql语句都无法执行。此时要修改的语句就是报错的sql语句和该语句后面的所有语句，这样的改错成本太大！！！ ↩︎

6. • 删错数据库有多危险：公司中的数据库里都会保存一些重要商业数据，价值难以估量。举个例子：学校的教务系统是一个数据库，假如这个数据库被删了，后果的严重程度可想而知。
   • 如何避免删错库带来的损失：为了避免这种情况，通常会有以下措施：
   ↩︎

7. 假设有三个float或double类型的数：0.1和0.2和0.3。
   此时在计算机看来，0.1 + 0.2 != 0.3。
   而在数据库中，对精度要求一般很高。相比于float和double类型，通常都是使用decimal类型。 ↩︎

8. 肯定不能选择float和double，这时候有些伙伴可能会说decimal更精确，选择decimal。但其实更好的选择是int，此时钱数以分为单位即可。 ↩︎

9. 一个汉字算作一个字符，但是一个汉字一般占多个字节。 ↩︎

10. 我们平时练习的时候，就根据题目拍拍脑门确定出来。而在实际开发设计数据库的时候，一般是由专门的人（产品经理pm）负责确定的。 ↩︎

11. 在实际开发中，并不会用blob类型存放音乐，图片，视频等，一般都是把图片等单独放在专门的目录中，然后让数据库保存图片的路径。 ↩︎

12. 误删表有时候可能比误删数据库，导致的后果更严重！在实际开发中，数据库一般会分为线上数据库和线下数据库。线上数据库又叫做生产环境数据库，它是被用户访问的数据库 ，里面存储的数据都是真实用户的信息。线下数据库一般是开发测试的节点，是工作人员自己构造的一些“假的”数据库。线上数据库，通常都会搭配报警。mysql服务器，必须保持7*24小时运行，一旦挂了就会产生很大影响，所以它就会搭配报警程序。这个报警程序会自动的监控mysql上数据库的状态，一旦发现数据库存在问题（比如误删数据库），立马会通过短信/邮件/wx/电话等方式提醒上面的人。所以误删数据库一般会第一时间发现，赶紧进行处理（把之前的数据恢复回去）。但是如果是删表操作，通常很难第一时间发现，此时程序就会以错误的状态（数据）在生产环境又运行很长时间，在此期间不断产生损失影响直到发现解决。 ↩︎

13. • 全列查询怎么个危险法1：我们知道mysql是一个客户端-服务器结构的数据库程序。客户端中的操作，都会通过请求发送给服务器，服务器查询的结构也会通过响应返回给客户端。请求和响应都是通过网络进行的。mysql是通过硬盘保存数据的。如果一张表中的数据非常多，当客户端请求全列查询的时候，可能就会产生如下两个问题：
      
      
      在平时学习的时候我们无所谓全列查询。但在实际的开发中，针对数据量比较大的线上数据库（用户能直接访问到的服务器），不能随便进行全列查询！！！
    • 全列查询怎么个危险法2：全列查询可能还会影响到索引的使用。详情见专栏【mysql——索引】。
    ↩︎

14. 如果一条sql查询语句没有加order by子句，此时查询结果顺序是不确定，也就是理论上是无序的 。我们通过观察上面已经进行的过的无排序查询操作，好像顺序看起来还是原来表的顺序，没变。但是，这只是我们自己在自己的机器上进行简单操作！如果进行一些更复杂的操作，就不一定了！即使是在条件查询里面，我们说过服务器会遍历每一行记录，但是可没说过服务器是挨个遍历！究竟是按照怎样的顺序遍历，详情见专栏——索引。 ↩︎

15. offset是计算机中广泛使用的概念，通常是寻址的意思。即找指定的内存。 ↩︎

16. 字符串之间可以相加，但这不是算术运算。 ↩︎

17. set在计算机中有两种典型含义：一是设置的意思，比如面向对象语言中的封装操作getter和setter，比如文中提到的update语句中的set。二是集合的意思，比如数据结构与算法中的TreeSet和HashSet。 ↩︎

本文标签： 详解语言关系数据库基础