sql 显示百分比_SQL复杂查询

SQL复杂查询

一、知识框图

二、视图

-- 视图中的列和SQL语句中的列一一对应
create view 按性别汇总 (性别,人数) as
select 性别,count(*)
from student
group by 性别;

-- 视图的使用
select 性别,人数
from 按性别汇总;

-- 练习
create view 按课程号进行汇总 (课程号,选课人数) as
select 课程号,count(*)
from score
group by 课程号;

select 课程号,选课人数
from 按课程号进行汇总;

三、子查询

-- 子查询完整sql
select 学号,成绩
from score 
where 成绩 in ( select min(成绩)
from score
group by 课程号);

-- 哪些学生的成绩比课程0002的全部成绩里的 任意一个高呢
select 学号,成绩
from score
where 成绩 > any(
select 成绩
from score
where 课程号 = '0002');

-- 哪些学生的成绩比课程0002的全部成绩里的 都高呢
select 学号,成绩
from score
where 成绩 > all(
select 成绩
from score
where 课程号 = '0002');

四、标量子查询

-- 大于平均成绩学生的学号和成绩
select 学号,成绩
from score
where 成绩 > (
select avg(成绩)
from score);

-- 查询介于差生（成绩小于等于60）和优等生（成绩大于80）
-- 学号和成绩
select 学号,成绩
from score
where 成绩 between 
(select avg(成绩)
 from score
 where 成绩 <= 60) and
(select avg(成绩)
 from score
 where 成绩 >= 80);

-- 练习
select 学号,成绩,(select avg(成绩) from score) as 平均成绩
from score;

五、关联子查询

-- 查找出每门课程中 大于对应课程平均成绩的学生
select 学号,课程号,成绩
from score as s1
where 成绩 > (select avg(成绩)
from score as s2
where s1.课程号 = s2.课程号
group by 课程号)
order by 课程号 asc;

-- 查找1990年出生的学生名单
select 学号,姓名
from student
where year(出生日期) = 1990;

六、SQLZOO练习

1.列出每个国家的名字，其中人口比“俄罗斯的大

select name
from world
where population > (select population from world
where name = 'Russia');

2.显示人均GDP高于“英国”的欧洲国家

select name
from world
where continent = 'Europe' and (gdp/population) > (select (gdp/population) 
from world
where name = 'United Kingdom');

3.列出包含阿根廷或澳大利亚的各大洲的国家名称和大洲。按国家名称排序

select name,continent
from world
where continent in (select continent from world
where name = 'Argentina' or name = 'Australia')
order by name;

4.哪个国家的人口超过加拿大，但少于波兰？显示姓名和人口

select name,population
from world
where population > (select population from world
where name = 'Canada') and population <(select population from world
where name = 'Poland');

5.显示欧洲每个国家的名称和人口。显示人口占德国人口的百分比

select name,concat(round(population/(select population from world
where name = 'Germany')*100,2),'%')
from world
where continent = 'Europe';

6.哪些国家的GDP高于欧洲每个国家？[仅提供名称。]（某些国家/地区的gdp值可能为NULL）

select name
from world
where gdp > all(select gdp
from world
where continent = 'Europe' and gdp > 0);

7.查找每个大陆中最大的国家（按区域），显示该大陆，名称和区域

select continent,name,area 
from world as a
where area = (select max(area) from world as b
where a.continent = b.continent
group by continent);

*8.列出每个洲和按字母顺序排在首位的国家/地区的名称

-- 思路：通过continent关联子查询；字母顺序排在首位，即最先出现的国家名称
-- 转化成SQL即是name要小于等于所有子查询里的查询的name
select continent,name
from world as x
where name <= all(select name
from world as y
where x.continent = y.continent);

*9.查找所有国家/地区人口均≤25000000的大洲。然后查找与这些大洲相关的国家/地区的名称。显示姓名，大陆和人口

select name,continent,population
from world as x
where 25000000 >= all(select population from world as y
where x.continent = y.continent)
order by x.continent asc;

*10.一些国家的人口是其邻国（在同一大陆上）的三倍以上。给国家和大洲

-- 思路：在同一大陆上，寻找人口数大于领国3倍的国家
-- 子查询中查询3*population
-- 使用continent作为关联
-- 注意：在子查询中要排除本身
select name,continent
from world as x
where population > all(select 3*population from world as y
where x.continent = y.continent and x.name<>y.name);

注意事项：

• all可以和比较运算符结合起来使用
• MySQL里all得到的是一个集合，即n行数据，不能写a > 3all(b)，语法里只能是a/3> all(b)
• 关联子查询，要根据题意找到关联的变量，有一定难度，多看案例，总结成语法模板