文章目录
- CRUD
- 一、Create
- 指定列插入
- 全列插入
- 时间日期插入
- 二、Retrieve
- 全列查找
- 指定列查询
- 查询字段是表达式
- 查询的时候指定别名
- 去重DISTINCT
- 按照查询结果排序
- 条件查询:WHERE
- 基本查询
- AND与OR
- 范围查询
- 模糊查询:LIKE
- NULL 的查询:IS [NOT] NULL
- 分页查询:LIMIT
- 三、Update
- 四、Delete
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);
此时数据就全部插入进去了,现在我看看查询为表达式的操作:
- 我们可以在查询的时候针对分数进行变换,比如让查询的数学成绩都在原来的基础上+10分.
select name,math + 10 from exam_result;
- 上述这样的查询,数据库服务器硬盘的数据是没有改变的.原因就是mysql是一个"客户端-服务器"结构的程序!!用户在客户端输入的sql,通过请求发送给服务器,服务器解析并执行sql把查询的结构从硬盘中读取出来,通过网络响应还给客户端,客户端把这些数据以"临时表"(只是在客户端显示一下的临时表,和服务器那边的硬盘上的表没啥关系)的形式展示出来.
- 查询每个同学的总成绩
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就通过一系列的运算符来表示条件.
基本查询
- 查询英语不及格的同学
select * from exam_result where english < 60;
- 相当于针对数据库的表进行遍历,取出每一行数据,把数据代入到条件中,看条件是否符合,如果是真,这个记录就保留,作为结果集的一部分,如果是假,这个记录就pass.
- 查询语文成绩好于英语成绩的同学
select * from exam_result where chinese > english;
- 查询总分在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
- 查询语文成绩大于80分,且英语成绩大于80分的同学
select * from exam_result where chinese > 80 and english > 80;
- 查询语文成绩大于80分或者英语成绩大于80分的同学
select * from exam_result where chinese > 80 or english > 80;
- 如果一个where中即存在and又存在or,先执行and后执行or.
范围查询
- between…and… 约定的是一个前闭后闭区间.
查询语文成绩在 [80, 90] 分的同学及语文成绩.select * from exam_result where chinese >= 80 and chinese <= 90;
select * from exam_result where chinese between 80 and 90;
- 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也是可以和前面那些查询搭配使用的.
比如:查询总分前三名的同学的信息:
- 计算每个同学总成绩.
- 按照成绩排序(降序).
- 取前三条记录.
select name, chinese + english + math as total from exam_result order by total desc limit 3;
三、Update
update 表明 set 列名 = 值, 列名 = 值…where 条件;
- 将孙悟空同学的成绩改成80分.
update exam_result set math = 80 where name = '孙悟空';
- 把曹孟德的数学成绩改为60,语文改为70.
update exam_result set math = 60,chinese = 70 where name = '曹孟德';
- 把总成绩倒数前三二的 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;
- 把所有同学的语文成绩改为原来的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
表和数据都没了.