【MySQL】表的增删查改（基础版）(指定列、全列、时间日期插入 全列、指定列查询 查询字段是表达式 查询时指定别名 去重DISTINCT 按照查询结果排序 条件查询:WHERE 基本查询 ...)

---

CRUD

增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写.
我们作为一个后端程序员以后可能工作主要就是CRUD.

一、Create

新增插入数据使用insert into values(值,值...);
insert into student values(1,'张三');

指定列插入

我们想只插入name和gender两列数据:insert into student(name,gender) values('张三','男');

此时values后面的内容和values前面()的内容匹配.
想要看到结果,需要使用查询语句,最简单的查询:select * from student;

全列插入

insert into student values(2,'李四','女'),(3,'王五','男');

一次插入N个记录,比一次插入一个记录,分N次插入,效率更高一点.交互多次的成本要比交互一次高很多.

时间日期插入

插入时间是通过特定格式的字符串来表示时间日期的.
形如:2023-02-17-21:25:00

往表中插入信息:
insert into homework values(1,'2023-02-17-21:25:00');

假设我们想把这个时间日期设置成当前时刻,sql提供了一个函数now()
insert into homework values(2,now());

二、Retrieve

查找操作

全列查找

查找整个表,所有行所有列.
select * from 表名;
* 表示所有列,这种特殊含义的符号,计算机中叫做"通配符".

mysql 是一个"客户端服务器"结构的程序

结论:执行select * 操作可能会非常危险.
如果数据量非常大,几亿几十亿,进行select * 操作会出问题.
请求本身不大,请求发到服务器上,服务器要读取硬盘,由于数据量非常大,就会立刻把硬盘访问的带宽给占满.此时其他程序也想使用硬盘就会出问题.进一步部署的服务器可能就死机了.除此以外还有网络.
这个操作会瞬间吃满硬盘带宽和网络带宽…就可能导致其他程序无法使用硬盘或者使用网络.

指定列查询

select 列名,列名.....from 表名;

select name,gender from student;

查询结构就只是我们想查的那两列.

查询字段是表达式

在查询的过程中可以做一些简单的运算(可以是列和列之间的运算)

我们先创建一个表:
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 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);

此时数据就全部插入进去了,现在我看看查询为表达式的操作:

1. 我们可以在查询的时候针对分数进行变换,比如让查询的数学成绩都在原来的基础上+10分.
   select name,math + 10 from exam_result;
   
   上述这样的查询,数据库服务器硬盘的数据是没有改变的.原因就是mysql是一个"客户端-服务器"结构的程序!!用户在客户端输入的sql,通过请求发送给服务器,服务器解析并执行sql把查询的结构从硬盘中读取出来,通过网络响应还给客户端,客户端把这些数据以"临时表"(只是在客户端显示一下的临时表,和服务器那边的硬盘上的表没啥关系)的形式展示出来.

2. 查询每个同学的总成绩
   select name,math + chinese + english from exam_result;
   
   表达式查询,是让列和列之间进行运算而不是行和行.按照表达式查询,临时表的列名就和表达式是一样的,很多时候,表达式的含义是不直观的.

查询的时候指定别名

相当起了一个小名,可以让我们更方便的理解含义.
select name,math + chinese + english as total from exam_result;

as可以省略,但是不建议!

去重DISTINCT

使用distinct针对指定列进行去重(把重复的行只保留一个)

数学这一列有重复的,我们先查看math这一列:
select math from exam_result;

然后:select distinct math from exam_result;此时回车我们就看到去重了.

distinct指定多个列的时候,要求这些列的值都相同,才视为重复.

按照查询结果排序

使用order by子句,指定某些列进行排序,排序可能升序也可能降序.
比如我们想按照数学成绩升序排序:
select name,math from exam_result order by math;

对于mysql来说,如果一个sql没有指定order by此时查询结果集的数据顺序是不可预期的. 所以在代码逻辑中不能依赖这里的查询顺序.

order by相当于针对很大数据量进行排序,有可能内存存不下,所以此时可能用的是归并排序,归并排序既可以对内存进行排序,又可以对硬盘进行高效的排序.

降序:
select name,math from exam_result order by math desc;

desc是describe的缩写.

还可以指定多个列来排序.多个列之间使用","分割.列越靠前就是关键的排序依据.先按照第一列排序,如果第一列的值相同了,再按照第二列排序…
select * from exam_result order by math desc,chinese desc;
咱们在排序的时候最关键的就是明确排序的列的主次关系.

条件查询:WHERE

在查询的时候制定筛选条件,符合条件的数据留下,不符合的直接pass,想查询需要先描述条件,sql就通过一系列的运算符来表示条件.

基本查询

1. 查询英语不及格的同学
   select * from exam_result where english < 60;
   
   相当于针对数据库的表进行遍历,取出每一行数据,把数据代入到条件中,看条件是否符合,如果是真,这个记录就保留,作为结果集的一部分,如果是假,这个记录就pass.
2. 查询语文成绩好于英语成绩的同学

select * from exam_result where chinese > english;

3. 查询总分在200分以下的同学
   select * from exam_result where chinese + english + math < 200;

还可以：
select name,chinese+math+english from exam_result where chinese + english + math < 200;

select name,chinese+math+english as total from exam_result where chinese + english + math < 200;

AND与OR

1. 查询语文成绩大于80分，且英语成绩大于80分的同学
   select * from exam_result where chinese > 80 and english > 80;
   
2. 查询语文成绩大于80分或者英语成绩大于80分的同学
   select * from exam_result where chinese > 80 or english > 80;
   
   如果一个where中即存在and又存在or，先执行and后执行or.

范围查询

1. between…and… 约定的是一个前闭后闭区间.
   查询语文成绩在 [80, 90] 分的同学及语文成绩.
   select * from exam_result where chinese >= 80 and chinese <= 90;
select * from exam_result where chinese between 80 and 90;
   
2. in,查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
   select * from exam_result where math = 58 or math = 59 or math = 98 or math = 99;

select * from exam_result where math in(58,59,98,99);
   

模糊查询：LIKE

模糊匹配,不要求元素完全相同,只要求满足一定的规则,就可以了.
只支持两个用法:
1.使用%表示任意0个字符或N个字符.
2.使用_ 表示任意1个字符.

查询姓孙同学的成绩
select * from exam_result where name like '孙%';
查询孙开头的,like '%孙'是查询孙结尾的,like %孙%查询包含孙的.

insert into exam_result values(8,'孙行者悟空',null,null,null);

select * from exam_result where name like '孙_';

select * from exam_result where name like '孙_ _';

NULL 的查询：IS [NOT] NULL

null 和其他数值进行运算,结果还是null,null结果在条件中,就相当于false了.
使用<=>这个比较相等运算,就可以处理null的比较.

select * from exam_result where chinese <=> null;

select * from exam_result where chinese is null;

分页查询：LIMIT

针对查询结果进行限制,很多地方都可以见到.

只获取前三条数据:

select * from exam_result limit 3;

limit可以搭配offset,声明从哪一条开始查询(从0开始记数)

select * from exam_result limit 3 offset 0;
select * from exam_result limit 3 offset 3;
select * from exam_result limit 3 offset 6;

limit 3 offset 6; 等价于 limit 6,3;

limit也是可以和前面那些查询搭配使用的.

比如:查询总分前三名的同学的信息:

1. 计算每个同学总成绩.
2. 按照成绩排序(降序).
3. 取前三条记录.

select name, chinese + english + math as total from exam_result order by total desc limit 3;

三、Update

update 表明 set 列名 = 值, 列名 = 值…where 条件;

1. 将孙悟空同学的成绩改成80分.

update exam_result set math = 80 where name = '孙悟空';

2. 把曹孟德的数学成绩改为60,语文改为70.

update exam_result set math = 60,chinese = 70 where name = '曹孟德';

3. 把总成绩倒数前三二的 2 位同学的数学成绩加上30分.

先查询总成绩排名:
select name,chinese + math + english as total from exam_result order by total desc;

null和其他数据运算结果还是空,所以孙行者悟空三门成绩都为null他最终总成绩就是null,如果有一门为null,最总结果也还是null, null在排序的时候,视为最小的值. 所以我们主要看宋公明的数学成绩变化就行.

update exam_result set math = math + 30 order by chinese + math + english asc limit 2;

4. 把所有同学的语文成绩改为原来的0.5倍

update exam_result set chinese = chinese / 2;
修改我们发现了小问题:有7行发生了修改,有2个警告.

我们查看警告:
show warnings;

这里的警告信息是date截断,即小数点后位数不够就截断了,相当于把小数点后面的省略了.

本来孙悟空成绩应该是43.75,刘玄德成绩是27.75,由于约定这一列是3位有效数字,并且小数点后保留1位,此时就按照四舍五入的方式把数据截断了.

四、Delete

删除记录(行)

delect from 表名 where 条件;

delete from exam_result where name = '孙行者悟空';

删除孙字开头的:

delete from exam_result where name like '孙%';

这里就是把条件匹配出来的结果,都删掉了.

下面这个操作基本相当于删表:
delete from exam_result;

delete from 表还在,里面的数据没了.
drop table 表和数据都没了.