sql sever avg保留小数_SQL复杂查询（NO.4)

sql复杂查询分为（1）视图（2)子查询（3）标量子查询（4)关联子查询

一：视图

1.如何创建视图？

/*
create view 视图名称(<视图列名1>,<视图列名2>,...)
as
<select 查询语句>；
*/
create view 按性别汇总(性别,人数)
as
select 性别,count(*)
from student
group by 性别;

2.如何使用视图？

-- 

3.关于视图？

频繁使用sql语句就可以创建视图，但注意不能往视图里面插入数据

二：子查询

1.什么是子查询？

-- 括号内即子查询,多行
select 性别,人数
from (
      select 性别,count(*) as 人数
      from student
      group by 性别
      ) as 按性别汇总；

2.如何使用子查询？

(1) ...in(子查询)

-- 第一步：查找每门课程的最低成绩有哪些值
select 课程号,min(成绩)
from score
group by 课程号
-- 第二步：在成绩表查找哪些值对应的学号
select 学号,成绩
from score
where 成绩 in(80,60,80);
-- 最终sql
select 学号,成绩
from score
where 成绩 in (select 课程号,min(成绩)
from score
group by 课程号
);

(2)...any(子查询)

select 列名1
from 表名1
where 列名1 > any(子查询);

案例：哪些学生的成绩比课程0002的全部成绩的任意一个高呢？

-- 第一步：课程0002全部成绩
select 成绩
from score
where 课程='0002';
-- 第二步：学生成绩大于任意第一步的成绩
select 成绩
from score
where 成绩>any(子查询)
-- 最终sql
select 成绩
from score
where 成绩>any(
select 成绩
from score
where 课程='0002'
)；

(3)...all(子查询)

案例：哪些学生的成绩比课程0002的全部成绩都高？

select 成绩
from score
where 成绩>all(
slect 成绩
from score
where 课程号='0002'
);

三：标量子查询

1.什么是标量子查询？

-- 标量子查询为子查询里引用函数，且得出的是单一值（in,any,all,between)
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);

2.如何使用标量子查询？

select 学号,成绩,（select avg(成绩) 
                  from score) as 平均成绩
from score;

3.标量子查询注意事项

-- 以下sql语句不可，标量子查询为单一值，group by是分组语句为多行
select 学号,成绩,(select avg(成绩) 
                  from score 
                  group by 课程号
                  ) as 平均成绩
from score;

四：关联子查询

1.关联子查询：在每个组里比较

-- 关联条件为where s1.课程号=s2.课程号
select 学号,课程号,成绩
from score as s1
where 成绩>(select avg(成绩)
from score as s2
where s1.课程号=s2.课程号
group by 课程号
);

五：应用在子查询的各种函数

函数分为：(1)汇总函数（2)算术函数（3）字符串函数（4）日期函数

1.汇总函数

• count(列名):求某列的行数，count(*)
• sum(列名):对某列数据求和，只能对数值类型的列计算
• avg(列名)：求某列数据求和的平均值，只能对数值 类型的列计算
• max(列名)：最大值
• min(列名)：最小值

2.算术函数

• round(数值，保留小数的位数)
• abs(数值)
• mod(被除数，除数)

3.字符串函数

• length(字符串)：字符串长度
• lower(字符串)：大写转换成小写
• upper(字符串)：小写转换成大写
• concat(字符串1,字符串2)：字符串拼接
• replace(字符串,被替换的字符串，用什么字符串替换)：字符串替换
• substring(字符串,截取的起始位置,截取长度)：字符串截取

4.日期函数

• current_date：当前日期
• current_time：当前时间
• current_timestamp：当前日期和时间
• year(日期)：获取时间的年份
• month(日期)：获取日期的月份
• day(日期)：获取时间的日期
• dayname(日期)：日期对应星期几

六：sqlzoo的select in select案例

1.List each country name where the population is larger than that of 'Russia'.

SELECT name
from world
WHERE population >(SELECT population 
                   FROM world
                   WHERE name='Russia');

2.Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

Per Capita GDP ，The per capita GDP is the gdp/population

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

3.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

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

4.Which country has a population that is more than Canada but less than Poland? Show the name and the population.

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

5.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

Decimal places ，Percent symbol %

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

6.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

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

7.Find the largest country (by area) in each continent, show the continent, the name and the area:

select continent,name,area 
from world as x
where area >= all(select area from world as y where y.continent=x.continent);

8.List each continent and the name of the country that comes first alphabetically.

select continent,name
from world as x
where name<= all(select name from world as y where y.continent=x.continent);

9.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

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

10.Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

select name,continent
from world as x
where population/3> all(select population from world as y where y.continent=x.continent and y.name<>x.name);