MySQL基本查询

发布时间:2024年01月14日


前言


一、表的增删改查

1、Create

语法:

insert into 表名 (要插入数据的列) values(数据);

下面我们先创建一个students表。
在这里插入图片描述

然后我们向表中插入数据。如果我们不指定要插入数据的列,那么就是全列插入,即我们需要为每一列都输入数据。如果我们指定了要插入数据的列,那么只需输入指定列数据即可。我们还可以进行多行插入,每一行数据之间使用逗号分割开。
在这里插入图片描述

插入更新

当我们插入数据时,可能会由于 主键 或者 唯一键 对应的值已经存在而导致插入失败。
在这里插入图片描述
此时我们可以选择性的进行同步更新操作。
语法:

insert into students (列名称) values(每一列值) on duplicate key update 如果发生冲突,需要更新的新数据。

下面的操作中,我们看到刚开始表中已经有id为10的数据了,然后我们再次插入id为10的貂蝉的数据,按理来说会失败,但是我们使用了插入更新,所以如果当前面貂蝉的数据中遇到了主键id冲突时,那么就会将id为10的数据更新为我们update后新给定的数据。我们看到这样的操作有两行数据受到了影响。
在这里插入图片描述
当表中已经有一个和我们update更新的数据一样的数据时,我们看到此时虽然发生了主键id冲突,但是并没有数据行受到影响,这是因为表中虽然有冲突,但是冲突数据的值和update更新的值相等,所以不会进行数据更新。
在这里插入图片描述
我们看到当没有冲突产生时,只有一行数据受到了影响。这就是表中没有冲突数据,所以on duplicate后面的update也不会执行。而是直接将前面没有产生冲突的数据插入到表中即可。
在这里插入图片描述
当主键或唯一键其中一个或两个都产生冲突时,才会执行后面update。而且新的数据可以和前面插入的数据不一样。
在这里插入图片描述

替换

替换是当主键或者唯一键有冲突时,会先将以前的数据删除,然后再将新的数据插入。
语法:

replace into students (列名称) values(列数据);

我们看到下面当主键和唯一键产生冲突时,有两行数据被影响。这就是因为发生了冲突,所以会先将表中原来的冲突数据删除,然后将新的数据插入到表中。
在这里插入图片描述
我们看到当插入的数据没有产生冲突时,那么只有一行数据收到了影响,这就是因为如果没有冲突,那么会直接将数据插入到表中。
在这里插入图片描述

2、Retrieve

select为检索语句。
语法:

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]  //条件
[ORDER BY column [ASC | DESC], ...]  //分组或排序
LIMIT ...  //限制行数

下面我们先创建一个表。
在这里插入图片描述
下面我们向表中插入数据。
在这里插入图片描述

SELECT 列

下面我们来属性select的列查询。
通常情况下,我们不建议使用 * 来进行全列查询,因为查询的列越多,意味着需要传输的数据量越大。如果表中数据很多时,那么这个select * 查询就会占用很多资源。
在这里插入图片描述
我们看到select是可以查询并计算表达式的。
在这里插入图片描述
所以我们也可以在查询表中数据时,计算表达式。
在这里插入图片描述
我们还可以通过下面的语法来为查询结果指定别名。其中as是可以省略的。

select id,name,chinese+math+english as 总分 from exam_result;

在这里插入图片描述

我们还可以通过下面的语法来对查询结果进行去重。

select distinct math from exam_result;

在这里插入图片描述

WHERE 条件

比较运算符:
在这里插入图片描述
逻辑运算符:

运算符说明
AND多个条件必须都为TRUE(1),结果才是TRUE(1)
OR任意一个条件为TRUE(1),结果为TRUE(1)
NOT条件为TRUE(1),结果为FALSE(0)

在这里我们需要特别注意的是 = 和 < = >。当比较NULL和NULL是否相等时,需要使用< = >。
在这里插入图片描述
在这里插入图片描述

下面我们来进行一些练习。
英语不及格的同学及英语成绩 ( < 60 )
我们可以先查看所有同学的英语成绩,然后再使用where条件筛选出来英语成绩小于60的同学的数据进行显示。select后面决定要显示哪些列,而where可以筛选出来满足条件的行数据。

select name,english from exam_result where english<60;

在这里插入图片描述

语文成绩在 [80, 90] 分的同学及语文成绩

//使用 AND 进行条件连接
select name,chinese from exam_result where chinese>=80 and chinese<=90;

//使用 BETWEEN ... AND ... 条件
select name,chinese from exam_result where chinese between 80 and 90;

在这里插入图片描述
数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

//使用 OR 进行条件连接
select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

//使用 IN 条件
select name,math from exam_result where math in (58,59,98,99);

在这里插入图片描述

姓孙的同学及孙某同学
这个题我们可以使用模糊匹配。需要注意的是在LIKE后面如果跟%,那么表示后面可以为任意多个字符并且也可以为0个字符。而LIKE后面如果跟_,表示任意一个字符。

//姓孙的同学。% 匹配任意多个(包括 0 个)任意字符
select name from exam_result where name like '孙%';

//孙某同学。 _ 匹配严格的一个任意字符
select name from exam_result where name like '孙_';

在这里插入图片描述
语文成绩好于英语成绩的同学

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

在这里插入图片描述

总分在 200 分以下的同学

//WHERE 条件中使用表达式。别名不能用在 WHERE 条件中
select name,chinese+english+math as 总分 from exam_result where chinese+english+math<200;

在这里插入图片描述
我们看到在where后面我们需要重新输入一遍chinese+english+math,那么在where后面我们是否可以使用前面的重命名来表示chinese+english+math这个表达式呢?我们尝试后发现并不可以,并且错误信息提示我们说total这一列找不到。
在这里插入图片描述
这其实就和select语句执行的顺序有关了。当MySQL执行下面的语句时,会先分析要查询的表,然后分析where后面的满足条件的数据,然后再分析select后面的按照什么样的样式显示查询到的数据。所以当执行到第二步total<200时,此时还没有进行chinese+english+math as total的重命名,所以会找不到total。而且我们需要注意的是select控制要显示的列,所以只有在select后面才能进行重命名。
在这里插入图片描述
语文成绩 > 80 并且不姓孙的同学

//AND 与 NOT 的使用
select name,chinese from exam_result where chinese>80 and name not like '孙%';

在这里插入图片描述

孙某同学,否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80
当where后面有多个条件时,我们可以通过加()的方式来使条件看起来更清晰。

//综合性查询
select name,chinese,english,math,chinese+english+math as total from exam_result where (name like '孙%' or (chinese<math and english>80);

在这里插入图片描述

NULL 的查询

我们先创建一个表。然后插入数据。
在这里插入图片描述
查找name为NULL的人

//使用 IS NULL
select id,name from test where name is null;

//使用<=>
select id,name from test where name<=>null;

在这里插入图片描述
查找名字为空串的人
我们需要注意空串不等于NULL,NULL表示什么都没有,并且不参与运算。而空串表示有,但是内容为空串。

select id,name from exam_result where name='';

在这里插入图片描述

ORDER BY 结果排序

结果排序可以将select显示的数据进行排序后显示。
语法:

-- ASC(ascending) 为升序(从小到大)
-- DESC(descending) 为降序(从大到小)
-- 默认为 ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

我们需要注意没有 ORDER BY 子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序。

同学姓名及数学成绩,按数学成绩升序显示
order by默认的顺序为升序排序。

//指明排序顺序
select name,math from exam_result order by math asc;

//使用order by的默认升序排序
select name,math from exam_result order by math;

在这里插入图片描述

当有NULL时,NULL 视为比任何值都小,降序出现在最下面

select id,name from test order by id desc;

在这里插入图片描述

查询同学各门成绩,依次按 数学降序,英语升序,语文升序的方式显示
会先按照数学成绩降序排序,当数学成绩相同时才会按照英语升序排序,当英语成绩也相同时才会按照语文升序的方式排序。

select name,math,english,chinese from exam_result order by math desc,english asc,chinese asc;

在这里插入图片描述

查询同学及总分,由高到低

//不使用别名
select name,chinese+math+english as total from exam_result order by chinese+math+english desc;

//ORDER BY 子句中可以使用列别名
select name,chinese+math+english as total from exam_result order by total desc;

在这里插入图片描述
我们看到上面的语句中order by子句中可以使用列别名了。这是因为order by的执行顺序在select子句的后面。即先执行from exam_result子句确定要查询的表,然后再执行where子句进行数据筛选,然后执行select子句设置要显示的列,然后再执行order by子句将要显示的数据进行排序显示。
在这里插入图片描述

查询姓孙的同学或者姓曹的同学数学成绩,结果按数学成绩由高到低显示

//结合 WHERE 子句 和 ORDER BY 子句
select name,math from exam_result where name like '孙%' like '曹%' order by math desc;

在这里插入图片描述

LIMIT 筛选分页结果

语法:

-- 起始下标为 0
--0 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- 从 s 开始,筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- 从 s 开始,筛选 n 条结果,比第二种用法更明确,建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

当我们对未知表进行查询时,最好加一条 LIMIT 1,避免因为表中数据过大,查询全表数据导致数据库卡死。

显示指定行的数据
在这里插入图片描述

显示指定区间的数据
我们看到当我们输入limit 2,4时,显示的数据id为3,6。这是因为limit 2,4的写法中2表示开始位置(下标从0开始,即0才代表id为1的数据),4表示步长,即从指定位置开始,向后连续读取多少条记录。所以我们可以看到当输入limit 0,3时和输入limit 3显示的数据相同。因为limit 3是默认从0位置开始向后读取数据。
在这里插入图片描述
在这里插入图片描述
limit的offset写法
limit的这个写法中可以看着更明确。即limit后面的4表示读取4条数据,offset后面的2表示从2位置开始。
在这里插入图片描述
在这里插入图片描述

按 id 进行分页,每页 3 条记录,分别显示 第 1、2、3 页
limit可以很好的实现分页显示。
在这里插入图片描述
在这里插入图片描述
得到总分大于200的前三的同学的成绩

select name,chinese+math+english as total from exam_result where chinese+math+english>200 order by total desc limit 3 offset 0;

在这里插入图片描述
下面我们来分析limit子句的执行顺序。我们需要知道limit的功能是分页显示,即只有当数据都准备好了并且排序好了之后,才进行分页显示,所以limit子句的执行顺序在最后。
在这里插入图片描述

3、Update

对查询到的结果进行列值更新。
语法:

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

将孙悟空同学的数学成绩变更为 80 分

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

在这里插入图片描述

将曹孟德同学的数学成绩变更为 60 分,语文成绩变更为 70 分

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

在这里插入图片描述

将总成绩倒数前三的 3 位同学的数学成绩加上 30 分

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

在这里插入图片描述

将所有同学的语文成绩更新为原来的 2 倍

update exam_result set chinese=chinese*2;

在这里插入图片描述

4、Delete

删除数据

语法:

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

删除孙悟空同学的考试成绩

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

在这里插入图片描述

删除总分为倒数第一名的人。

delete from exam_result order by chinese+math+english asc limit 1;

在这里插入图片描述

删除整张表数据
我们先创建一个表。并且设置表的主键id为自增长。然后我们向表中插入数据。
在这里插入图片描述
我们可以通过下面的语句来将表中数据全部删除。我们看到表中数据被删除完了。

delete from for_delete;

在这里插入图片描述
然后我们再次向表中插入数据,我们看到主键id是从4开始的,而不是从1重新开始。
在这里插入图片描述

截断表

语法:

TRUNCATE [TABLE] table_name

注意:这个操作慎用,TRUNCATE只能对整表操作,不能像 DELETE 一样针对部分数据操作;实际上TRUNCATE不对数据操作,所以比 DELETE 更快,但是TRUNCATE在删除数据的时候,并不经过真正的事
物,所以无法回滚。并且TRUNCATE会重置 AUTO_INCREMENT 项。

下面我们创建一个表,并且向表中插入数据。
在这里插入图片描述
然后我们使用下面的语句来截断表。

truncate for_truncate;

在这里插入图片描述
然后我们再次向表中插入数据,我们看到主键id是从1重新开始的,即TRUNCAST会重置 AUTO_INCREMENT 项。
在这里插入图片描述

5、插入查询结果

语法:

INSERT INTO table_name [(column [, column ...])] SELECT ...

删除表中的的重复记录,重复的数据只能有一份
我们先创建一个表,然后向表中插入数据。
在这里插入图片描述
我们通过select distinct可以进行去重显示表中数据。
在这里插入图片描述
那么我们就可以先创建一个新表,然后将select distinct去重后的数据插入到新表中,这样新表中的数据就是去重后的数据了。然后我们再将新表进行重命名。
我们可以通过下面的语句来创建一个新表,并且使这个新表的表结构和旧表的表结构一样。

create table no_duplicate_table like duplicate_table;

在这里插入图片描述

然后我们通过下面的语句将查询结果插入到新表中。

insert into no_duplicate_table select distinct * from duplicate_table;

在这里插入图片描述
最后我们再对新表进行重命名,这样我们就完成了删除表中的重复记录。
在这里插入图片描述

6、聚合函数

这些聚合函数都可以添加distinct去重操作。

函数名称函数描述
COUNT([DISTINCT])返回查询到的数据的数量
SUM([DISTINCT])返回查询到的数据的总和,不是数字没有意义
AVG([DISTINCT])返回查询到的数据的平均值,不是数字没有意义
MAX([DISTINCT])返回查询到的数据的最大值,不是数字没有意义
MIN([DISTINCT])返回查询到的数据的最小值,不是数字没有意义

统计班级共有多少同学

select count(*) 总数 from exam_result;

在这里插入图片描述

统计数学成绩数量

select count(math) from exam_result;

在这里插入图片描述

统计去重后的有效数学成绩

select count(distinct math) as res from exam_result;

在这里插入图片描述

统计数学成绩总分

select sum(math) from exam_result;

在这里插入图片描述

计算英语不及格的同学的英语平均分
我们可以通过sum和count聚合函数运算求得平均分。

select sum(english)/count(english) from exam_result where english<60;

在这里插入图片描述
我们也可以直接使用AVG聚合函数来求出平均分。

select avg(english) from exam_result where english<60;

在这里插入图片描述

统计平均总分

select avg(chinese+math+english) from exam_result;

在这里插入图片描述

返回英语最高分

select max(english) from exam_result;

在这里插入图片描述

返回数学成绩 > 70 分以上的数学最低分

select min(math) from exam_result where math>70;

在这里插入图片描述

7、group by子句的使用

在select中使用group by 子句可以对指定列进行分组查询。
语法:

select column1, column2, .. from table group by column;

我们先创建一个雇员信息表(来自oracle 9i的经典测试表)

  • EMP员工表
  • DEPT部门表
  • SALGRADE工资等级表

在这里插入图片描述
我们查看创建的3个表。
在这里插入图片描述

显示每个部门的平均工资和最高工资
我们可以通过下面的语句显示员工中的最高工资和平均工资。

select max(sal) 最高, avg(sal) 平均 from emp;

在这里插入图片描述
那么我们想要显示每个部门的平均工资和最高工资时,只需要根据部门来分组即可,即根据员工的deptno部门编号来进行分组即可。然后显示的结果中就会使deptno相同的员工中的最高工作和平均工资显示出来。

select max(sal) 最高, avg(sal) 平均 from emp group by deptno;

在这里插入图片描述

显示每个部门的每种岗位的平均工资和最低工资
在分组时可以用逗号隔开不同的分组条件。

select min(sql) 最低, avg(sal) 平均 from emp group by deptno,jog;

在这里插入图片描述
在group by语句中,select后面只能跟分组的列,或者聚合函数。剩下的列都不能跟。
在这里插入图片描述

显示平均工资低于2000的部门和它的平均工资

select deptno, avg(sal) 平均 from emp group by deptno having avg(sal)<2000;

在这里插入图片描述
下面我们来了解where和having条件筛选的区别。wherw是对具体的任意列进行条件筛选,而having是对分组聚合之后的结果进行条件筛选。所以where和having条件筛选的阶段是不同的。

显示每个部门的每种岗位的平均工资低于2000的岗位,但是SMITH员工不参与统计
此时我们就可以使用where和having组合的条件筛选。即我们先使用where条件筛选将SMITH员工除去,形成一个没有SMITH员工信息的中间表。然后我们再通过having条件筛选选出平均工资低于2000的岗位,然后将这个表显示出来。

select deptno,job, avg(sal) as myavg from emp where ename!='SMITH' group by deptno,job having myavg<2000;

在这里插入图片描述
下面为语句的执行顺序。我们不应该单纯的认为MySQL中只有真实存在的表才叫做表。我们在执行语句中间通过where和having筛选出来的每一个表,包括最终结果,其实都是一个表。只不过这些表没有真实的表名,只是逻辑上的表。所以我们就可以认为MySQL中一切皆为表。
在这里插入图片描述

文章来源:https://blog.csdn.net/dong132697/article/details/135580791
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。