组合查询

一.使用子查询

1,.子查询：嵌套在其他查询中的子查询

2.子查询也称为内部查询，而包含子查询的语句也称为外部查询

3.子查询分为两个类别

               相关子查询：执行依赖于外部查询的数据，外部查询返回一行，子查询就执行一次 select

               非相关子查询：独立于外部查询的 子查询（from后面的子查询），子查询总共执行一次，执行完毕后将值传递给外部查询            效率低

相关子查询通常要消耗更长的执行时间，当数据量增加时，执行时间会急剧增加。

4.select 中嵌套子查询

SELECT 学号,姓名,科目,

(SELECT 班级名称 FROM t_class c WHERE c.班级编号=s.班级编号)  名称

FROM t_student s

嵌套在SELECT语句中SQL语句要求查询的值只能是单行和单列

5.from中的子查询

SELECT * FROM (SELECT 学号,姓名 FROM t_student) s

FROM中嵌套的SQL不受任何限制，也就是说子查询结果可以为多行多列

6.在where后面嵌套子查询

                            在WHERE中嵌套的子查询根据不同的运算符有不同的分类：

                           比较运算符（>、<、=、>=、<=、!=）

                           in 和not in运算符

                         子查询运算符（ALL、ANY、 EXISTS）

SELECT * FROM t_student
WHERE 分数>(SELECT 分数 FROM t_student WHERE 姓名='张三' AND 科目='C语言')
AND 姓名!='张三'

7.  在WHERE中嵌套

ALL运算符

和子查询的结果逐一比较，必须全部满足时表达式的值才为真。

SELECT * FROM t_student
WHERE 分数 >ALL (SELECT 分数 FROM t_student WHERE 姓名='张三' )

ANY运算符

和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真。

SELECT * FROM t_student
WHERE 分数 >ANY (SELECT 分数 FROM t_student WHERE 姓名=‘张三’ ) AND 姓名!=‘张三’

EXISTS/NOT EXISTS运算符

EXISTS判断子查询是否存在数据，如果存在则表达式为真，反之为假。NOT EXISTS相反。

SELECT * FROM t_student s1
WHERE EXISTS(SELECT * FROM t_student s2 WHERE 姓名='张三' AND s1.分数=s2.分数)
AND 姓名!='张三'

二.组合查询

1.使用UNION运算符

UNION是一种联合两条或以上查询的运算符，类似多条查询结果相组合的效果

SELECT 列1 , 列2 FROM 表1
UNION
SELECT 列3 , 列4 FROM 表2;

使用UNION ALL运算符

UNION ALL会保留重复行

SELECT 列1 , 列2 FROM 表1
UNION ALL
SELECT 列3 , 列4 FROM 表2;

练习题

CREATE TABLE bbc(
   NAME VARCHAR(50) NOT NULL,          国家名称
   region VARCHAR(60),                                国家所处 地区
   AREA DECIMAL(10,0),                        面积
   population DECIMAL(11,0),                              人口
   gdp DECIMAL(14),                           国民生产总值
   PRIMARY KEY (NAME)                   主键
   );

CREATE INDEX bbc_region ON bbc(region);  创建索引

/*1.获得和Brazil 巴西 同属一个地区的所有国家*/

SELECT NAME FROM bbc WHERE region =(SELECT region FROM bbc WHERE NAME='Brazil');

/*2.给出人口多余Russia（俄罗斯）的国家名称*/

SELECT NAME FROM bbc WHERE population>(SELECT population FROM bbc WHERE NAME='Russia');

/*3.给出India（印度），Iran（伊朗）所在地区的所有国家的所有信息*/

SELECT * FROM bbc WHERE region IN(SELECT region FROM bbc WHERE NAME='India' OR NAME='Iran');

/*4.给出人均GDP超过'United Kingdom'(英国)的欧洲国家*/

SELECT NAME FROM bbc WHERE gdp/population>(SELECT gdp/papulation FROM bbc WHERE NAME='United Kingdom')AND region='Europe';

/*5.给出人口比Canada（加拿大）多但少于Algeria（阿尔及利亚）的国家信息*/

SELECT* FROM bbc WHERE population>(SELECT population FROM bbc WHERE NAME='Canada')AND 

population<(SELECT population FROM bbc WHERE NAME='Algeria') ;

/*6.给出GDP比任何欧洲国家都多的国家（值显示国家名称）*/

SELECT NAME FROM bbc WHERE gdp>(SELECT MAX(gdp) FROM bbc WHERE region='Europe');

老师：select*from bbc where gdp>all(select gdp from bbc where region='europe');

/*7.给出每个地区人口最大的国家*/

SELECT NAME, region FROM bbc  GROUP BY region HAVING MAX(population);

老师：select*from bbc where population in（select max（population）from bbc group by region）;

/*8.给出地区中所有国家的人口总数为0的地区*/

SELECT region FROM bbc WHERE NAME= (SELECT NAME FROM bbc GROUP BY NAME HAVING SUM(population)=0);

老师：select region,sum(population) as a from bbc group bby region having a=0;

/*9.有些国家的人口数比他的周边国家（周边国家是在同一地区的国家）要多三倍，列出这些国家和地区*/

SELECT NAME,region FROM bbc b1 WHERE population >all (SELECT 3*population FROM bbc b2 

WHERE b1.region=b2.region and b1.name!=b2.name);