上篇文章,我们介绍了数据的基本 DDL 语句,你应当具备基本的创建数据库、数据表的 SQL 语句,以及表字段的基本数据类型的熟知。
那么本篇就来总结总结大家日常最频繁接触到的 DDM 语句,也就是基本的增删改查 SQL。
数据的修改
众所周知的是,我们的项目中,有百分之八十的操作都是在查询,而仅有百分之二十的操作是做的数据修改。
所以,关系型数据库中对于数据的修改这块并没有什么很复杂的门道,我们优先介绍这一块内容,而对于数据的查询而言,它会复杂的多,各种排序、分组、子查询以及多表连接查询等等等等,就是旨在满足我们多样化的查询需求以及提升查询效率,这个我们稍后会介绍。
数据的修改包括,数据的插入、数据的修改以及数据的删除。
1、插入数据
向表中插入一条数据的 SQL 语法如下:
INSERT INTO [TABLE_NAME] (column1, column2, column3,...columnN) VALUES (value1, value2, value3,...valueN); 复制代码
那好,我们具体来看一个例子吧。
先创建这么一张 person 表,使用如下 SQL:
create table person(
id int primary key,
name varchar(16) not null,
age int,
phone varchar(11),
address varchar(256)
);
复制代码
接着,我们插入一条数据:
insert into person(id,name,age,phone,address)
values (1,'yang',22,'123232323','中国上海');
复制代码
于是你查询 person 表,会看到
+----+------+------+-----------+--------------+
| id | name | age | phone | address |
+----+------+------+-----------+--------------+
| 1 | yang | 22 | 123232323 | 中国上海 |
+----+------+------+-----------+--------------+
复制代码
当然,如果你在插入数据时有些字段的值暂时不想传入,或是该字段有默认值,insert 语句是允许你部分数据插入的,前提是不能违反一些非空、唯一、类型不匹配约束。
例如我只想插入一条数据,而我只知道这个人的名字,于是我也可以插入一条记录,但只赋值 name 字段。
insert into person(id,name)
values (2,'cao');
复制代码
再次查询 person 表:
+----+------+------+-----------+--------------+
| id | name | age | phone | address |
+----+------+------+-----------+--------------+
| 1 | yang | 22 | 123232323 | 中国上海 |
| 2 | cao | NULL | NULL | NULL |
+----+------+------+-----------+--------------+
复制代码
关系型数据库中,所有未赋值的字段都默认为 NULL,当然这个默认值是可以修改的,你可以修改为空字符串或空格等等。
再说一个细节,当你想要插入一条数据时,并且希望为该表的每一个字段都赋值,那么你可以不用在表名后列举所有字段名,例如以下两条 insert 语句是等效的。
insert into person(id,name,age,phone,address)
values (1,'yang',22,'123232323','中国上海');
复制代码
insert into person
values (1,'yang',22,'123232323','中国上海');
复制代码
关于 insert,我们暂时先说到这,后面介绍子查询的时候还会提到它,接着我们来看修改数据 update。
2、修改数据
SQL UPDATE 语句用于修改表中现有的记录。基本格式如下:
UPDATE [table_name]
SET column1 = value1, column2 = value2...., columnN = valueN
复制代码
举个例子,这是 person 表现在的数据情况:
+----+------+------+-----------+--------------+
| id | name | age | phone | address |
+----+------+------+-----------+--------------+
| 1 | yang | 22 | 123232323 | 中国上海 |
| 2 | cao | NULL | NULL | NULL |
+----+------+------+-----------+--------------+
复制代码
我们执行:
update person set address='浙江杭州';
复制代码
再来看 person 表:
+----+------+------+-----------+--------------+
| id | name | age | phone | address |
+----+------+------+-----------+--------------+
| 1 | yang | 22 | 123232323 | 浙江杭州 |
| 2 | cao | NULL | NULL | 浙江杭州 |
+----+------+------+-----------+--------------+
复制代码
你会发现 person 表的所有记录的 address 字段全都修改为「浙江杭州」。
所以,一般来说,我们的 update 语句都会结合 where 子句做一个数据筛选,只修改符合条件的记录的 address 字段值。
例如:
update person set address='浙江杭州' where id = 1;
复制代码
3、删除数据
我们使用 DELETE 语句对标数据进行删除,基本格式语法如下:
DELETE FROM [table_name]
WHERE [condition];
复制代码
同样,不追加 where 子句做条件筛选会导致整张表的数据丢失。例如我们删除 id 为 1 的那条数据记录。
delete from person where id = 1;
复制代码
数据的查询
SQL SELECT 语句用于从数据库的表中取回所需的数据,并以表的形式返回。返回的表被称作结果集。
基本的查询语法如下:
SELECT column1, column2, columnN FROM table_name;
复制代码
如果需要查询一条记录中的所有的字段,可以用符号「*」替代全体,例如:
SELECT * FROM person;
复制代码
可查询出 person 表所有的记录:
+----+-------+------+-----------+--------------+
| id | name | age | phone | address |
+----+-------+------+-----------+--------------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 2 | cao | NULL | NULL | 浙江杭州 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
+----+-------+------+-----------+--------------+
复制代码
这是最基本的查询,没有之一,接下来我们将一点点的增加复杂度,更熟练的掌握查询语句。
1、where 子句
where 子句又被称为条件子句,用于筛选查询出来的数据集,指定的条件语句中可以使用基本的算术、关系和逻辑运算,例如:>,<,=,!=,&&,||。
举个例子吧,person 表现在有如下数据:
+----+-------+------+------------+--------------+
| id | name | age | phone | address |
+----+-------+------+------------+--------------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 2 | cao | NULL | NULL | 浙江杭州 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
| 6 | yang | 24 | 2343435353 | 山东青岛 |
+----+-------+------+------------+--------------+
复制代码
我们现需要查询出,名字叫「yang」,年龄为「22」的记录,该怎么写呢?
select * from person
where name='yang'&& age=22;
复制代码
还是很简单的,虽然 where 子句很简单,但它却是我们 SQL 查询中最重要的一个关键字,基本上每一条 SQL 语句都离不开它。
在指定条件中,除了我们以上说的可以使用基本的逻辑算术运算符,子查询也是需要依赖 where 的,我们后面继续说。
2、LIKE 子句
LIKE 子句,我们一般用来做一些简单的搜索查询,或者说模糊匹配,表达式主要涉及到两个符号:
- 百分号 %:匹配任意多个字符
- 下划线 _:匹配固定一个字符
举几个例子吧,同样以我们的 person 表数据为例。
查询所有的数据,找到其中 name 字段以字符「ang」结尾的数据记录集合:
select * from person
where name like '%ang';
复制代码
执行 SQL,返回结果:
+----+-------+------+------------+--------------+
| id | name | age | phone | address |
+----+-------+------+------------+--------------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
| 6 | yang | 24 | 2343435353 | 山东青岛 |
+----+-------+------+------------+--------------+
复制代码
查询所有的数据,找到其中 name 字段以字符「ang」结尾,并且前面还有一个任意字符的数据记录集合:
select * from person
where name like '_ang';
复制代码
执行 SQL,返回结果:
+----+------+------+------------+--------------+
| id | name | age | phone | address |
+----+------+------+------------+--------------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 6 | yang | 24 | 2343435353 | 山东青岛 |
+----+------+------+------------+--------------+
复制代码
3、in 子句
in 关键字也是使用在 where 子句的条件表达式中,它限制的是一个集合,只要字段的值在集合中即符合条件,例如:
select * from person
where age in (22,30,23);
复制代码
这个 SQL 语句可以查询出来所有年龄是 22,30,23 的人数据记录。
你也可以使用 not in 反向限制,例如:
select * from person
where age not in (22,30,23);
复制代码
这个 SQL 则可以查出所有年龄不是这三个值的数据记录信息。
4、ORDER BY 子句
ORDER BY 子句根据一列或者多列的值,按照升序或者降序排列数据。某些数据库就默认以升序排列查询结果。
基本的 SQL 语法为:
SELECT column
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];
复制代码
ASC 表示数据结果集按升序排序,DESC 表示数据结果集按降序排序。
一般来说,我们按某一列进行排序即可,当然,有时候一列排序并不能完全解决问题,如果按多列排序,那么当遇到某一列值相同的时候,就会参照第二个列参数将这些重复列值得数据记录再一次排序。
举个例子:
我们将 person 表中的数据参照 id 列,倒序排序:
select * from person
order by id desc;
复制代码
执行 SQL,查看结果:
+----+-------+------+------------+--------------+
| id | name | age | phone | address |
+----+-------+------+------------+--------------+
| 6 | yang | 24 | 2343435353 | 山东青岛 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 2 | cao | NULL | NULL | 浙江杭州 |
| 1 | yang | 22 | 231232132 | 中国上海 |
+----+-------+------+------------+--------------+
复制代码
需要注意的是,对于数字类型的字段排序而言还相对容易理解些,对于非数字类型的排序,可能你不一定能看懂它为什么这样排序。
其实每个数据库都预定义了很多的排序规则,很多数据的实现都默认使用 utf8_general_ci 排序规则,当然,如果你很熟悉各种排序规则,你也可以在创建数据表的时候去主动指定使用哪种排序规则,一般使用默认排序规则就行。
5、GROUP BY 子句
GROUP BY 子句用于将查询返回的结果集进行一个分组,并展示各个分组中排在第一个的记录,将分组中其余成员隐藏。
我们为 person 表添加几条数据,用于演示:
+----+-------+------+------------+----------+
| id | name | age | phone | address |
+----+-------+------+------------+----------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 2 | cao | 30 | 456789 | 浙江杭州 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
| 6 | yang | 24 | 2343435353 | 山东青岛 |
| 7 | cao | 44 | 12312312 | 河南郑州 |
| 8 | huang | 45 | 5677675 | 安徽合肥 |
| 9 | yang | 80 | 3343738 | 江苏南通 |
+----+-------+------+------------+----------+
复制代码
注意观察姓名列,有几组重复的姓名。
我们按照姓名对结果集进行分组,SQL 如下:
select * from person
group by name;
复制代码
执行 SQL,得到结果:
+----+-------+------+-----------+----------+
| id | name | age | phone | address |
+----+-------+------+-----------+----------+
| 2 | cao | 30 | 456789 | 浙江杭州 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 1 | yang | 22 | 231232132 | 中国上海 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
+----+-------+------+-----------+----------+
复制代码
你看,分组之后,只展示每个分组下排序第一的记录,其余成员隐藏。
细心的同学可能发现了,分组后的数据记录排序怎么乱了,怎么不是默认的 id 升序排列了?
对,如果你没有显式执行排序方式的话,将默认以你用于分组参照的那个字段进行排序。
当然,我们是可以执行排序方式的,使用 order by 子句:
select * from person
group by name
order by id;
复制代码
效果是这样:
+----+-------+------+-----------+----------+
| id | name | age | phone | address |
+----+-------+------+-----------+----------+
| 1 | yang | 22 | 231232132 | 中国上海 |
| 2 | cao | 30 | 456789 | 浙江杭州 |
| 3 | li | 23 | 34567894 | 江苏南京 |
| 4 | huang | 33 | 34567894 | 湖北武汉 |
| 5 | zhang | 30 | 4567890 | 中国北京 |
+----+-------+------+-----------+----------+
复制代码
这就是分组,可能会有同学疑问,这样的分组有什么意义,分组是分完了,给我返回每个分组的第一行记录有什么用?
其实是这样的,我们之所以进行分组,就是为了统计和估量每个分组下的指标情况,比如这组数据的平均年龄、最高薪水等等等等。
而当我们只是 「select *」的时候,数据库根本不知道你要干什么,换句话说就是你并没有对每一个分组中的数据进行任何的分析统计,于是给你返回该分组的第一行数据。
你要记住的是,每个分组只能出来一个数据行,究竟让什么样的数据出来取决于你。
比如我们计算每个分组下的平均年龄:
select avg(age) as '平均年龄' from person
group by name;
复制代码
查询结果:
+----------+
| 平均年龄 |
+----------+
| 37.0000 |
| 39.0000 |
| 23.0000 |
| 42.0000 |
| 30.0000 |
+----------+
复制代码
这里涉及了一个求平均数的函数 avg,我们后续会介绍这些常用函数,这里你体会下其意思就行。
6、HAVING 子句
HAVING 子句在我看来就是一个高配版的 where 子句,无论是我们的分组或是排序,都是基于以返回的结果集,也就是说 where 子句的筛选已经结束。
那么如果我们对排序、分组后的数据集依然有筛选需求,就用到我们的 HAVING 子句了。
例如:
select avg(age) as vage from person
group by name
having vage>23;
复制代码
分组之后,我们得到每个分组中数据的平均年龄,再者我们通过 having 语句筛选出平均年龄大于 23 的数据记录。
以上我们介绍了六个子句的应用场景及其使用语法,但是如果需要同时用到这些子句,语法格式是什么样的?作用优先级是什么样的?
SELECT column1, column2
FROM table
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2
复制代码
大家一定要记住这个模板,各个子句在 SQL 语句中的位置,可以不出现,但不得越位,否则就会报语法错误。
首先是 from 语句,查出表的所有数据,接着是 select 取指定字段的数据列,然后是 where 进行条件筛选,得到一个结果集。
接着 group by 分组该结果集并得到分组后的数据集,having 再一次条件筛选,最后才轮到 order by 排序。
篇幅已经很长了,不再继续了,有关子查询、连接查询以及一些细节我们放在下一篇,本篇的重点是理解上述模板中的各个子句,并记住他们之间的作用优先级。