MySQL查询语句

发布于:2024-08-02 ⋅ 阅读:(79) ⋅ 点赞:(0)

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;

也可以联合wherehaving等语句进行筛选

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’ 相同课程的学生。
找出具有重复名字的学生。
查找在所有课程中都取得了及格分数的学生。
找出每门课程的平均分数,并按照平均分数降序排列。
查找学生选课数量超过平均选课数量的学生信息。
左连接查询 查学号
右连接查询 查学号
内连接查询 查学号

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

create table info1 (
	id int(4),
    major varchar(10),
    lesson varchar(10),
    name varchar(10),
    score decimal(5,2) default null,
    sex char(2)
);
create table info2 (
	id int(4),
    phone int(11),
    address varcha(30),
    hobby varchar(10) default null,
    sex char(2)
); 

insert into info1 values
(1,'电气工程','高数','张三',90,'男'),
(1,'电气工程','英语','张三',55,'男'),
(1,'电气工程','计算机','张三',80,'男'),
(2,'通信工程','高数','李四',60,'男'),
(2,'通信工程','英语','李四',55,'男'),
(2,'通信工程','计算机','李四',50,'男'),
(3,'制药工程','高数','韩梅梅',70,'女'),
(3,'制药工程','英语','韩梅梅',90,'女'),
(3,'制药工程','计算机','韩梅梅',80,'女');
(4,'通信工程','高数','李雷',80,'男'),
(4,'通信工程','英语','李雷',80,'男'),
(4,'通信工程','计算机','李雷',90,'男'),

insert into info1 values
(5,'计算机','计算机','张三',88,'男'),
(6,'传媒','英语','李四',88,'女');

insert into info2 values
(1,'18369961111','南京','爬山','男'),
(2,'18369962222','北京','遛弯','男'),
(3,'18369963333','天津','说相声','女'),
(4,'18369964444','东京','下海','男');

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

  1. 查找具有最高分数的学生:
select * from info1 
where score = (select max(score) from info1);
  1. 找出至少有两门课程成绩及格的学生:
select count(score),id,name from info1
where score >= 60
group by id,name having count(score) >= 2;
  1. 查找每个系的学生人数:
select major,COUNT(DISTINCT id) as stu_num from info1
group by major;
  1. 计算每个系的学生平均分数
select major,lesson,FORMAT(AVG(score),2) as avg_score from info1
group by major,lesson;
  1. 获取至少选修了有一门课程与 ‘id=5的张三’ 的课程相同的学生
select distinct id,name from info1 where id<>5 and lesson in
(select lesson from info1 where id = 5);

#或者用表info1自连接的方式
select distinct i1.id,i1.name from info1 i1
join info1 i2 on i1.lesson = i2.lesson and i2.id = 5 
where i1.id <> 5;
  1. 找出具有重复名字的学生
select distinct id,name from info1 where name in
(select name from info1
group by name having COUNT(DISTINCT id) > 1)
ORDER BY name;
  1. 查找在所有课程中都取得了及格分数的学生
select distinct id,name from info1 where id not in
(select id from info1 where score < 60);
  1. 找出每门课程的平均分数,并按照平均分数降序排列
select lesson,FORMAT(AVG(score),2) as avg_score from info1
group by lesson
order by avg_score desc;
  1. 查找学生选课数量超过平均选课数量的学生信息
select id,name,num_lesson from
(select id,name,count(*) as num_lesson from info1
group by id,name) as stu_lesson
where num_lesson > 
(select avg(cnt) as avg_lesson from
(select count(*) as cnt from info1
group by id,name) as avg_count);
  1. 左连接查询 查学号
select * from info1 as i1 left join info2 as i2
on i1.id = i2.id;
  1. 右连接查询 查学号
select * from info1 as i1 right join info2 as i2
on i1.id = i2.id;
  1. 内连接查询 查学号
select * from info1 as i1 inner join info2 as i2
on i1.id = i2.id;

网站公告

今日签到

点亮在社区的每一天
去签到