当前位置:   article > 正文

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

删除表sql

一、操作表

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

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

(1)表的创建

(1)创建一个表
create table 表名 (
	列名 列类型,
	......
	列名 列类型
);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

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

在这里插入图片描述

(2)表的查看

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

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

在这里插入图片描述

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

(2)查看表结构
desc 表名;
  • 1
  • 2

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

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

(3)表的删除

(3)表删除的语法
drop table 表名;
  • 1
  • 2

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

2.insert:插入数据

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

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

插入多条数据时
insert into 表名 (列名, ...... ,列名) values 
	(列对应值, ...... , 列对应值),
	......
	(列对应值, ...... , 列对应值);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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

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

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

在这里插入图片描述

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

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

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

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

3.select:查询数据

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

查询数据操作
select (列名) from 表名;
或
select * from 表名; (慎用)
  • 1
  • 2
  • 3
  • 4

具体的代码已经代码结果如下所示。
在这里插入图片描述

(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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

运行效果图
接下来我们执行第一个计算和的操作,具体执行代码如下:

select name,chinese+math+english from score;
  • 1

在这里插入图片描述

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

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

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

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

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

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

select distince chinese from score;
  • 1

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

(3)用 order by 排序

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

升序
select * from score order by chinese asc;  # ascend 上升
降序
select * from score order by chinese desc;  # descend 下降
  • 1
  • 2
  • 3
  • 4

在这里插入图片描述
在这里插入图片描述
我们有没有办法把这里的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;
  • 1

在这里插入图片描述

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

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

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

在这里插入图片描述

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;
  • 1
  • 2
  • 3
  • 4
  • 5

看到这个结果部分读者可能就觉得有点奇怪,是不是哪里出错了呢?其实不然,只是根本没有符合条件的同学。这里我们可以用到前面的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 '张_';
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

在这里插入图片描述
这里额外对% _这两个运算符做一点简单的说明吧。X % 是:搜索以X为开头的数据;% Y是:搜获以Y结尾的所有数据,当然我们也可以将两者结合起来:% X % Y %表示中间有XY的数据。我们仔细品味这几个例子就会发现%的共同特点:查所有。那么相对应的,能不能查有限位呢?有的,就是_这个运算符。相信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;
  • 1

在这里插入图片描述
这样的结果就非常清晰了,那offset是干什么用的呢?这是用来定位开始位置的,比如说:从第三高分的同学(不包括第三)开始找三位语文成绩较高的同学

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

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

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

4.update:修改数据

标准的格式如下:

update 表名 set 列名 = 值 where xxx ;
  • 1

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

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

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

在这里插入图片描述
简单再写有关例子吧~

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

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

5.delete:删除数据

标准格式如下

delete from 表名 where xxx ;
  • 1

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

在这里插入图片描述

二、SQL语句汇总

1.表 SQL

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


(2)查看某数据库表中的内容
show tables;


(3)查看表的字段
desc 表名


(4)删除表
drop table 表名;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

2.表数据 SQL

(1)插入
插入多条数据时
insert into 表名 (列名, ...... ,列名) values 
	(列对应值, ...... , 列对应值),
	......
	(列对应值, ...... , 列对应值);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
(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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
(3)修改
update 表名 set 列名 = 值 where xxx ;
  • 1
  • 2
(4)删除
delete from 表名 where xxx ;
  • 1
  • 2

结语

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

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Guff_9hys/article/detail/775651
推荐阅读
相关标签
  

闽ICP备14008679号