当前位置：首页 > news >正文

MySQL查询语句

news 2025/7/5 13:28:01

1. 一般查询

select * from table；

创建表：并插入数据，为下面的查询做例

create table info (
id int primary key,
name varchar(10),
score decimal(5,2),
address varchar(20),
hobbid int(5));insert into info values(1,'liuyi',80,'beijing',2);
insert into info values(2,'wangwu',90,'shengzheng',2);
insert into info values(3,'lisi',60,'shanghai',4);
insert into info values(4,'tianqi',99,'hangzhou',5);
insert into info values(5,'jiaoshou',98,'laowo',3);
insert into info values(6,'hanmeimei',10,'nanjing',3);
insert into info values(7,'lilei',11,'nanjing',5);

2. 排序语法：关键字排序

升序和降序

默认的排序方式就是升序

ASC ：升序；DESC ：降序；配合order by 语法

按照name列降序排列：

select * from info order by name desc;
#order by ：指定要排序的列
#desc ：指定排序的方式为降序

按照hobbid列降序，id列升序排列（先排hobbid，再排id）

select * from info order by hobbid desc,id;
#id不指定排序方式，就默认是升序排序

注意：以多个列作为排序关键字，只有当第一个参数有相同的值，第二个字段才有意义。因为多个列排序，会先对第一个字段进行排序，当第一个字段存在重复时，对重复的字段，会根据第二个字段再进行排序

3. where条件的筛选功能（比较符）

3.1 区间判断：可与and or 搭配使用

查询分数大于70且小于等于90的行：

select * from info where score > 70 and score <= 90;

查询分数大于80或小于70的行

select * from info where score < 70 or score > 80;

3.2 嵌套多条件

条件中再嵌套条件，用（）

select * from info where score < 60 or (score > 75 and score < 90)

4. 分组查询

SQL查询的结果进行分组，使用 group by 语句配合聚合函数一起来实现。

4.1 常用聚合函数的类型

count ：统计个数
sum ：求和
avg ：求平均数
max ：最大值
min ：最小值

4.2 聚合函数用法示例

选择列用于聚合函数，如果使用group by，则还需要至少一个列用于分组

select count(name),hobbid from info 
group by hobbid;

解释：**针对 info 表中的数据，按照 hobbid 列中的每个唯一值，统计出该值对应的记录数量，并将结果按照 hobbid 的值进行分组展示。**直白点说就是统计每个hobbid对应的记录数量，并显示出来。

在聚合函数分组语句中，所有的非聚合函数列，都可以在group by 语句中。

select count(name),hobbid,name from info 
group by hobbid,name;

也可以联合where，having等语句进行筛选

select count(name),hobbid from info 
where score >= 60 group by hobbid;

在group中使用having语句

select count(name),hobbid,score from info 
group by hobbid,score 
having score > 80;

having是在group by中用于筛选的语句，写在group by之后

例：以hobbid这一列作为分组，计算成绩score的平均值，筛选出平均成绩大于等于60分的分组

select avg(score),hobbid from info 
group by hobbid 
having avg(score) >= 60;

例：统计姓名，以兴趣和分数作为分组，统计出成绩大于80的分组，然后对统计姓名的列按照降序排列

select count(name),hobbid,score from info 
group by hobbid,score 
having score > 80 
order by count(name) desc;

5. limit

limit 1,3 为例：

1是位置偏移量（可选参数），偏移1表示从第二行开始；

如果不设定位置偏移量，默认为0，从第一行开始。3是从起始行开始连续选取三行。

例：选取以id排序的最后三行：

select * from info order by id desc limit 3;

6. as ：表和列的别名

实际工作中，表的名字和列的名字可能会很长，书写起来不太方便，需要多次声明表和列时，完整展示太麻烦，可以设置别名，可以使书写简化，方便阅读。

设置别名使用 as

设置列名的别名

select name as 姓名,score as 成绩 from info;

也可以省略as：

select name 姓名,score 成绩 from info;

设置表名的别名：

select i.name as 姓名,i.score as 成绩 from info as i;
select i.name 姓名,i.score 成绩 from info i;

复制表

create table test as select * from info;

注意：这种方式只能复制表的数据类型和数据，但是复制不了结构（各种KEY，约束等）

7. 通配符

like ：模糊查询

% ：表示0个，1个或多个字符

_ ：表示单个字符

#查询地址以s开头
select * from info where address like 's%';
#查询地址包含s
select * from info where address like '%s%';
#查询地址以s开头，长度至少为2
select * from info where address like 's_%';
#查询地址以s开头，第三个字母是a
select * from info where address like 's_a%';

8. 子查询

子查询也叫内查询，嵌套查询。

是在select语句当中又嵌套了一个select。嵌套的select才是子查询，先执行子查询的语句，外部的select再根据自条件的结果进行过滤查找。

子查询可以使多个表，也可以是同一张表。

关联语句： in ; not in ，格式 select (select)

select name,score from info 
where id in
( select id from info where score >= 80);

解释：先执行子查询里，筛选出score>=80的id，再回到外层查询，根据查到的id，列出对应id的name，score

select name,score from info 
where id not in
(select id from info where score >= 80);

解释：执行顺序和上面一样，区别在于回到外层查询时，对查到的id取反，即除了查到id，列出其他的id对应的name，score

例：联合其他表进行查询

select name,score from info where id not in
(select id from test where score >= 80);

也可以和update连用

查询test表中name为tianqi的id，将info表中对应id的score改为80

update info set score = 80 where id in 
(select id from test where name = 'tianqi');

注意：使用update，子查询不能再查自己的表，会报错

子查询当中多表查询和别名

例：info表和test表，查询两张表id相同的部分。然后根据id相同的部分，查询info表的对应id的所在行

select * from info as a 
where a.id in 
(select b.id from test as b);

解释：a.id in (select b.id from test as b);

例：info表和test表，查询两张表id相同的部分。然后根据id相同的部分，查询info表的对应id中score大于80的所在行

select * from info as a 
where a.score > 80 and a.id in 
(select b.id from test as b);

例：info表和test表，查询两张表id相同的部分。然后根据id相同的部分，查询info表的平均成绩

select avg(a.score) from info as a
where a.id in
(select b.id from test as b);

9. exists

exists判断子查询的结果是否为空。不为空返回true；为空返回false

select count(*) from info where exists
(select id from test where score > 80);

解释：看起来好像是统计score大于80的数量，实际是先执行子查询，查询结果存在，则exists返回值为true，执行外层查询，统计info的总行数

例：查询分数，如果分数小于50，则统计info的字段数

select count(*) from info where exists
(select id from info where score < 50);

10. mysql的视图(view)

视图是一个虚拟表，表的数据基于查询的结果生成。

视图可以简化复杂的查询，隐藏查询的细节，访问数据更安全。

视图表是多表数据的一个集合表。

视图和表之间的区别

存储方式：表是实际的数据行，视图不存储数据行，仅仅是查询结果的虚拟表。
数据更新：更新表可以直接更新数据表的数据（较老的版本可能不支持）。
占用空间：表实际占用空间，视图表不占用空间，只是一个动态结果的展示。
视图表的数据可以是一张表的部分查询数据，也可以是多个表的一部分查询数据。

查询当前数据库当中的视图表：

show full tables in basename where table_type like 'VIEW';

创建视图表：实例：

create view test03 as select * from info where score >= 80;
#创建视图表test03，展示info表中score>=80的行
#此时执行下行
select * from test02;
#相当于执行下行
select * from info where score >= 80;

注意：MySQL5.5版本之前，视图表是只读的，不能修改；MySQL5.5版本开始，视图表可以修改，且修改视图表和修改源表是双向的，即修改视图表也能更新源表。

删除视图表：

drop view tablename;

创建一张视图表，视图表中包含 id,name,address ,从info 和test当中的name值相同的部分创建

create view v_info as 
select a.id,a.name,a.address from info as a
where a.name in 
(select b.name from test as b); select * from v_info;

可以发现，视图表其实就相当于为查询语句创建了一个别名，为查询做了简化。

平时使用中表的权限是不一样的，因为库的权限是有控制的。而查询视图表的权限相对低。所以使用视图表既可以保证原表的数据安全，也简化了查询的过程。

11. 连接查询

两张表或者多个表的记录结合起来，基于这些表共同的字段，进行数据的拼接。

首先，要确定一个主表作为结果集，然后将其他表的行有选择性的选定到主表的结果上(即做一个拼接)。

11.1 连接的类型

内连接：两张表或者多张表之间符合条件的数据记录的集合。

INNER JOIN，INNER一般可以省略

创建两张表：

create table test1 (
a_id int(11) default null,
a_name varchar(32) default null);create table test2 (
b_id int(11) default null,
b_level int(11) default null);insert into test1 values (1,'aaaa');
insert into test1 values (2,'bbbb');
insert into test1 values (3,'cccc');
insert into test1 values (4,'dddd');insert into test2 values (2,20);
insert into test2 values (3,30);
insert into test2 values (5,50);
insert into test2 values (6,60);

把两张表按照a_id,a_name,b_level拼接起来，并以on 后接筛选条件

select a.a_id,a.a_name,b.b_level from 
test1 as a INNER JOIN test2 as b 
on a.a_id = b.b_id;

外连接：取两个表或多个表之间的交集。

outer join，outer一般可以省略

左连接：左外连接，left join ，left outer join

左连接以左表为基础，接收左表的所有行，以左表的记录和右表的记录进行匹配。匹配左表的所有，以及右表中符合条件的行。不符合的显示null。

select * from test1 as a left join test2 as b
on a.a_id = b.b_id;

注意：写在left join 左边的就是左表。

右连接：右外连接，right join , right outer join

基本就是跟左连接反过来。右连接以右表为基础，接收右表的所有行，以左表的记录和右表的记录进行匹配。匹配右表的所有，以及左表中符合条件的行。不符合的显示null。

select * from test1 as a right join test2 as b
on a.a_id = b.b_id;

注意：写在right join 右边的就是右表。

12. 练习

需求：两张表

第一张表：记录学生的学号，所属专业，课程，姓名成绩性别
第二张表：记录学生的学号，手机家庭地址，兴趣爱好，性别

编写一个查询来查找具有最高分数的学生。
找出至少有两门课程成绩及格的学生。
查找每个系的学生人数。
计算每个系的学生平均分数。
获取至少同时选修了一门与 ‘xxx’ 相同课程的学生。
找出具有重复名字的学生。
查找在所有课程中都取得了及格分数的学生。
找出每门课程的平均分数，并按照平均分数降序排列。
查找学生选课数量超过平均选课数量的学生信息。
左连接查询查学号
右连接查询查学号
内连接查询查学号