MySQL查询语句

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

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

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);

2. 找出至少有两门课程成绩及格的学生：

select count(score),id,name from info1
where score >= 60
group by id,name having count(score) >= 2;

3. 查找每个系的学生人数：

select major,COUNT(DISTINCT id) as stu_num from info1
group by major;

4. 计算每个系的学生平均分数

select major,lesson,FORMAT(AVG(score),2) as avg_score from info1
group by major,lesson;

5. 获取至少选修了有一门课程与 ‘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;

6. 找出具有重复名字的学生

select distinct id,name from info1 where name in
(select name from info1
group by name having COUNT(DISTINCT id) > 1)
ORDER BY name;

7. 查找在所有课程中都取得了及格分数的学生

select distinct id,name from info1 where id not in
(select id from info1 where score < 60);

8. 找出每门课程的平均分数，并按照平均分数降序排列

select lesson,FORMAT(AVG(score),2) as avg_score from info1
group by lesson
order by avg_score desc;

9. 查找学生选课数量超过平均选课数量的学生信息

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);

10. 左连接查询 查学号

select * from info1 as i1 left join info2 as i2
on i1.id = i2.id;

11. 右连接查询 查学号

select * from info1 as i1 right join info2 as i2
on i1.id = i2.id;

12. 内连接查询 查学号

select * from info1 as i1 inner join info2 as i2
on i1.id = i2.id;