MySQL初阶【2】 —— 数据表的增删改查

一、操作表

上一节中，我们已经讲了一些基础的内容，包括：数据库的一些历史趣事，数据库的CURD，数据库中字段的数据类型。那么，接下来要聊的就顺承上次谈的内容，从数据库表的创建、查看 与 删除 说起，再给大家介绍如果插入、查询、修改 和 删除一条数据。看完本篇内容，你就已经可以胜任工作中80%的内容啦！

1.表的创建、查看 与 删除

（1）表的创建

（1）创建一个表
create table 表名 (
	列名 列类型，
	......
	列名 列类型
);

创建如下图所示。有几个地方需要注意的：

第一，肯定是需要先创建数据库 并 选定某个数据库，才能在数据库中创建一个表；
第二，数据库表数据的定义跟Java语法不同，这里是先写列名，再写列类型，这跟Python是类似的；
第三，写完列类型之后是加,而不是;最后一条定义不需要任何标点

（2）表的查看

在看完表的创建之后，我们会发现，这跟数据库的创建方式是一样的呀，那我们查看表是不是也用show关键字呢？

笔者为大家的验证如下，我们可以看到，用show关键字看到的是表在数据库中的情况，并不是这个表结构的情况。

那么，我们应该用哪一个关键字才能看到表自身的情况呢？其实也蛮鸡贼的~因为用的即不是show，而且也不用写成desc table 表名;这也的形式，又增加了咱们的记忆负担…

（2）查看表结构
desc 表名;

上方截图中的的前两列大家都明白是啥，分别是我们要的字段（对应的数据名） 和 对应的数据类型。那么这里简单解释一下后面几点的内容吧。

①Null是指这个字段能否为空（也就是设置成YES），相当于我们在插入具体数据时，可以选择写people_name 和 people_number 中的一个，甚至都可以不写。
②Key是指约束，这里暂且不提，在进阶教程中我们再谈，这也是一个重要内容。
③Default是设置默认值，如果没有这是默认值，插入空数据之后就会是Null，当给了其他内容，插入空数据是就会显示Default设置的内容。
④Extra是负责写一些额外的描述，是给表设计者以及其他工作伙伴看的。

（3）表的删除

（3）表删除的语法
drop table 表名；

这个没啥好演示的，就套上面的格式就成了。跟删除数据库一样的是，删除表的风险一样很高，而且影响更加恶劣，因外在一个项目中有成百上千张表都是很正常的，只是删除一张表跑路，很可能很难发现删除了哪一张。总之，这种很刑的事情，大家千万不要起故意干，当然在属于业务范围内也还是可以删除的，但最好叫多一个人在旁边看你操作，多一个人监督自然就会减小出错概率。我们要对删除数据保有仪式感，无知不会毁灭我们，傲慢才是！

2.insert：插入数据

上面谈完表的创建，那么我们有如何给表插入一条数据呢？这就要用到insert这个关键字了。

插入一条数据时
insert into 表名 (列名, ...... ,列名) values (列对应值, ...... ,列对应值);

插入多条数据时
insert into 表名 (列名, ...... ,列名) values 
	(列对应值, ...... , 列对应值),
	......
	(列对应值, ...... , 列对应值);

部分读者可能会疑惑，为什么数据库有选定的操作，但是数据库表却没有这种操作呢？我们可以从上方 或者 下方还未看到的语法格式中发现：每个针对表的操作，都需要选定表。

下表中第26行的语句大家先忽略，咱们待会马上就会介绍到，笔者是为了给大家一个更加直观的感受才写了那条语句。我们能看到，在输入插入语句后，表的数据就增加了。因为咱们在创建表的时候已经将列名都设置成 Null，因此我们只加入一个字段的数据时，没加入的字段数据处写的就是创表时设置的Default值。

值得注意的是：在插入数据时，如果会把数据全部都写上 或者 都不写，才能够不写要添加的列名。

谈到这里具体的插入操作我们已经了解完毕，但是对于我们给出的插入多条数据的语法形式，我们还要探究一下，为什么我们要写成那样呢 ？我们既然能够分开插入，为什么还会有多条一起插入的方式呢 ？答案其实在上一节 MySQL——初阶教程【1】的 请求和响应 一节中给出。

原因一：我们每插入一条数据，都是客户端向服务端发送一个请求，服务端完成操作之后才响应一个结果。如果分开插入，那么就说明有多次请求和响应，这无疑是要消耗更多的硬件资源的。如果一次全部插入，那么就只有一次请求和响应，节约了不少资源的开销。

原因二：数据库服务器是把数据存储到硬盘上的，每一次访问都是需要消耗时间的。

原因三：MySQL数据库在操作SQL语句的时候，每次都会开启一个事务。开启事务也是需要占用硬件资源的。（关于事务的内容，我们会在进阶教程中谈到）。

3.select：查询数据

接下来我们就来解释一下上方所写到的第26行。select这个关键字的作用，大家应该能感受到了，就是：查询表中的数据。但是我们看到一个不太理解的符号*，这个符号代表：查询表中所有的数据。正是因为这一个符号，我们才能看到插入的所有数据。

查询数据操作
select (列名) from 表名;
或
select * from 表名; （慎用）

具体的代码已经代码结果如下所示。

（1）危险操作

在简单了解了如何查看数据时，笔者要先告知大家刚刚涉及到的一个危险操作。部分读者可能会疑惑，刚刚的代码不都执行得好好的吗？哪里有问题呢？确实，我们刚刚的代码的所有语句都是没有问题的，但是我们在正式工作之后就要慎用select * from 表名;这一条语句。为什么呢？因为这条语句会消耗大量的硬件资源！

在正式工作中，我们面对的数据量都是TB级别的，如果我们查询所有数据，那么服务器就会去硬盘上读取我们的数据，然后通过网卡发到客户端。面对海量的数据，这个select *的操作将会占用掉几乎所有硬盘IO 和 网络带宽，那么其他用户想做其他操作可能也完成不了了，这跟宕机几分钟没啥区别。

（2）计算和、起别名、去重

对于查询来说，有上面的操作还算马马虎虎，因为我们日常的需求其实非常多样，比如：

A. 我们要在看成绩条的时候，我们希望看到总分是多少；
B. 在计算完总分之后，我们又需要给这条数据起个叫“总分”的名字；
C. 在看到成绩排名的时候，我们想着要去重，不要一列看下来全是满分的朋友。

那么上面的操作都是如何实现的呢？为了方便演示，笔者创建了一个关于分数的数据库表。由于我们此处没有具体的业务，而且数据量比较小，我们就占时用一用select *这个操作。

create table score (
	name varchar(20),
	chinese int,
	math int,
	english decimal(3,1)
);

insert into score values 
	("张三",84,26,78.5),
	("李四",56,75,51.8),
	("王五",84,75,15.2);
	
select * from score;

接下来我们执行第一个计算和的操作，具体执行代码如下：

select name,chinese+math+english from score;

通过上图我们可以看到：张三同学的成绩最高，李四其次，王五最后。这里就会有两个问题：
第一，MySQL给的结果是按照最高到最低来排名的吗 ？ 答案为：不是！MySQL中没有规定任何的承诺，也就说：数据库没有说明是默认按照降序排列的，那么这个排序是不可信的，因为很可能在其他情况下，指不定排序中间会出现一个最小的值。所有的升序、降序的排列都需要我们自己去指定！

第二，这个总成绩会对原来的成绩有影响吗 ？这是什么意思呢，的确这种算总分的操作必然没什么影响，但是语文老师发现一个题的答案给错分了呢？这就需要给chinese+10，这会影响原来的chinese的分数吗？输入：select name,chinese,chinese+10 from score;后运行，我们发现根本没有影响。我们下方看到的这个表其实是一个临时表。

接下来我们聊一聊怎么起别名，毕竟我们看到chinese+math+english这么一大长串名字的时候就觉得很不直观。具体操作如下：

select name,chinese+math+english as `total` from score;

笔者其实还知道另外一种写法select name,chinese+math+english total from score;但笔者个人是很鄙夷这种写法的，主要原因就是可读性不高，次要原因是太丑了！我们从一个维护代码的角度来看，这行代码是否需要引起我们哪怕0.01秒的思考？既然有更加直观，更加不用消耗脑力，且同样不会消耗更多资源 的写法，为什么要恶心别人呢？大家应该向推荐的写法靠拢，维护好代码编译风格的环境。

最后我们来聊聊怎么去重，最重要的就是用上关键字distince

select distince chinese from score;

去重的效果如上图所示，原本两个84变成只有一个84了。这里再次强调：MySQL没有给任何关于排序的承诺，所以我们能看到去重后降序排列的结果是运气！

（3）用 order by 排序

上方说过，我们需要自己指定排序的顺序，那么我们应该怎么去指定呢？这就需要用上此处介绍的order by 子句

升序
select * from score order by chinese asc;  # ascend 上升
降序
select * from score order by chinese desc;  # descend 下降

我们有没有办法把这里的order by子句 与 上面 计算和、起别名 结合起来呢？代码如下：select name,chinese+math+english as `total` from score order by total desc;

（4）用 where 条件查询

在我们日常的工作中，可能也会遇到这种场景：

A. 查询语文比英语高分的同学
B. 查询语文与数学都及格的同学
C. 查询英语成绩在40~50之间的同学
D. 查询英语成绩是某个值的同学
E. 查询名字中以张开头的同学
F. 查询名字只有两个字的姓张的同学

我们可以从上方的场景中感受到，这其实是对查询出来的结果进行一些去除，也就是按照给出的条件查询结果。那么我们先来简单看一下需要用到的运算符吧！相对来说比较难的就是模糊查询这一边的运算符了，这也是我们重点要谈的内容。

A. 查询语文比英语高分的同学

我们用到的第一种筛选方式，按照条件筛选

select name,chinese,english from score where chinese > english;

B. 查询语文与数学都及格的同学

我们用到的第二种筛选方式，按照逻辑表达式筛选

select name,chinese,english from score where chinese > english;

C. 查询英语成绩在40~50之间的同学

这个查询目标，我们即可以用到的第二种筛选方式，也可以用 第三种筛选方式。

筛选方法1
select name,english from score where english >= 40 and english <= 50;

筛选方法2
select name,english from score where english between 40 and 50;

看到这个结果部分读者可能就觉得有点奇怪，是不是哪里出错了呢？其实不然，只是根本没有符合条件的同学。这里我们可以用到前面的insert关键字，强行造出一个符合条件的结果，结果如下所示。但是我们能看到这里笔者并不推荐第一种写法，这是为什么呢？这里就要涉及到MySQL中的最左边前缀匹配原则，对于这种用逻辑运算符筛选的语句。索引只会给前面的加，不给后面的加，具体来说就是：只给english >= 40有索引，而english <= 50没有。但是，用between...and...则可以避免这也的问题。

D. 查询英语成绩是某个值的同学
E. 查询名字中以张开头的同学
F. 查询名字只有两个字的姓张的同学

接下来的两个就要用到模糊查询的运算符了。代码如下：

D. 查询英语成绩是某个值的同学
select name,english from score where english in (78.5,100,51.8);

E. 查询名字中以张开头的同学
# insert into score (name) values ("张三丰");   为了方便演示，故意插入这条数据
select name from score where name like '张%';

F. 查询名字只有两个字的姓张的同学
select name from score where name like '张_';

这里额外对% _这两个运算符做一点简单的说明吧。X % 是：搜索以X为开头的数据；% Y是：搜获以Y结尾的所有数据，当然我们也可以将两者结合起来：% X % Y %表示中间有X和Y的数据。我们仔细品味这几个例子就会发现%的共同特点：查所有。那么相对应的，能不能查有限位呢？有的，就是_这个运算符。相信F示例已经很清楚了，大家自己品味吧。

特殊的NULL

笔者在这里需要特殊解释一下NULL，主要有两点：第一，这个关键字不能直接用等号判断；第二，这个关键字是最小的（哪怕有负数也是最小的）。

我们可以简单证明一下，结果如下所示。但是为什么是这样的呢？因为：任何数据和NULL相比都是False，结果自然也是返回NULL，那么正确的方式判断方式是什么呢？我们应该用<=>或者is NULL来判断。

第二个咱们就不测了，大家可以自己写一个升序的order by子句，看看值为NULL的结果排在哪里。

（5）用 limit…offset 限制查询结果数量

我们同样用问题来引出回答。假设我们需要查找语文成绩排名前三的同学，我们应该怎么查找呢？如果是：“用升序去遍历整个表，再看到所有结果后确定前三”，这样无疑是很浪费资源的，那么正确的做法就是用limit...offset关键字

select name,chinese from score order by chinese desc limit 3;

这样的结果就非常清晰了，那offset是干什么用的呢？这是用来定位开始位置的，比如说：从第三高分的同学（不包括第三）开始找三位语文成绩较高的同学。

select name,chinese from score order by chinese desc limit 3 offset 3;

因为我们的数据只有五条，那么只能查出两位也正常。limit...offset一般是用在分页查询功能上的，此处我们还不能看到这个关键字大显神威。

这里最后补充一句，在where子句中是不能用别名比较的，这跟MySQL的内部实现有关。因为先把不符合条件的筛选掉，在给符合条件的数据起别名，这样的计算次数更少。

4.update：修改数据

标准的格式如下：

update 表名 set 列名 = 值 where xxx ;

我们简单结合一些示例来用一用就好啦。

A. 把张三丰的名字改成张三不丰

update score set name = "张三不丰" where name = "张三丰";

简单再写有关例子吧~

B. 优秀的张三不丰同学，科科都是满分

不知道各位读者有没有跳进笔者挖的坑中，为什么这么说呢？因为我们如果给english = 100是不能通过编译的，在最开始给english定类型的时候就定了decimnal(3,1),在三个数都是小数点前的数的情况下，无法让小数点后还能有一位小数！

5.delete：删除数据

标准格式如下

delete from 表名 where xxx ;

删除名字为 张三不丰 的同学

二、SQL语句汇总

1.表 SQL

（1）创建一个表
create table 表名 (
	列名 列类型，
	......
	列名 列类型
);


（2）查看某数据库表中的内容
show tables;


（3）查看表的字段
desc 表名


（4）删除表
drop table 表名；

---

2.表数据 SQL

（1）插入
插入多条数据时
insert into 表名 (列名, ...... ,列名) values 
	(列对应值, ...... , 列对应值),
	......
	(列对应值, ...... , 列对应值);

（2）查询
select (列名) from 表名;
或
select * from 表名; （慎用）


1）查询和
select name,chinese+math+english from score;


2）起别名
select name,chinese+math+english as `total` from score;


3）去重
select distince chinese from score;


4）排序
select * from score order by chinese asc;  # ascend 上升
select * from score order by chinese desc;  # descend 下降


5）关于NULL
select * from score where chinese <=> NULL;
select * from score where chinese is NULL;


6）分页偏移
select name,chinese from score order by chinese desc limit 3 offset 3;

（3）修改
update 表名 set 列名 = 值 where xxx ;

（4）删除
delete from 表名 where xxx ;

---

结语

恭喜读到这里的读者，你们现在已经掌握工作中80%的数据库操作内容啦！如果你觉得博主写得不错的话，请给一个赞支持一下吧！博主能力有限，欢迎大家给我纠错！