练习——实训6.1
create table bbc(
Name varchar(50) primary key not null,
Region varchar(60),
Area decimal(10),
Population decimal(11),
Gdp decimal(14),
);
insert into bbc values ('中国','亚洲',10,10,10);
insert into bbc values ('美国','北美洲',9,6,3);
insert into bbc values ('巴西','南美洲',13,4,5);
insert into bbc values ('阿根廷','南美洲',6,3,2);
insert into bbc values ('英国','欧洲',7,4,2);
insert into bbc values ('俄罗斯','欧洲',15,5,3);
insert into bbc values ('苏丹','非洲',3,2,1);
insert into bbc values ('日本','亚洲',6,6,2);
insert into bbc values ('南非','非洲',5,3,3);
select * from bbc;
--获得和巴西在同一个地区的所有国家
select * from bbc where region=(
? select region from bbc where name='巴西'
)
and name!='巴西'
--给出人口多于俄国(俄罗斯)的国家名称
select * from bbc where population>(
? select population from bbc where name='俄罗斯'
)
--给出印度、伊朗所在地区的所有国家的所有信息
select * from bbc where region in
(
?select region from bbc where name in('印度','伊朗')
)
select * from bbc where region in
(
?select region from bbc where name='印度' or name='伊朗'
)
--给出人均GDP比加拿大多，但少于阿尔及利亚的国家信息
select * from bbc
where gdp/population
between () and ()
--给出GDP比任何欧洲国家都多(只要显示国家名称)
select name from bbc
where population>all(select gdp from bbc where region='欧洲')
或者
select name from bbc
where population>(select max(gdp) from bbc where region='欧洲')
区别：给出GDP比任何一个欧洲国家都多(只要显示国家名称)
--给出每个地区人口最大的国家
select * from bbc a
where a.population=(
? select max(b.population) from bbc b
? where b.region=a.region
)
所以在实际工作中要用表连接要替换。
思考。
给出地区中所有国家的人口总数大于?的地区
select region
from bbc
group by region
having sum(population)>?
说明：sum(population)不一定要写在select中，
写在sum(population)中只是表示要显示出来。
9思考。
题意：
地区? 人口
a???? 10
a???? 7
a???? 2
b???? 8
b???? 3
满足条件的只有：
地区? 人口
a???? 10
a???? 7
都超过2的3倍。
?
?
where xxx>(select .... where a.id=b.id)
?
select * from t_class;
select * from t_class a,t_class b;
select * from t_class;
select * from t_subject;
select * from t_stu;
select * from t_score;
--统计各班有多少人：班级名称，人数
--相关子查询
--通过? 表连接 来优化
select c.c_name,
? (select count(*) from t_stu s where s.c_id=c.c_id) 人数
from t_class c
t_stu会被查询3次，所以效率低。
一班?4
二班?2
三班?0
体会场景：全校有20个班，你就会被问20次，你是不是 1班/2班/……
的？
通过表连接来优化，优化的原理是两个表都只查询一次。
思考：用内连接对不对？
select c_id,count(*) as 人数
from t_class c join t_stu s on c.c_id=s.c_id
group by c_id
error:列名 'c_id' 不明确。
因为c_id在两个有中都有，所以必须指出是哪个表的c_id。
select c.c_id,count(*) as 人数
from t_class c join t_stu s on c.c_id=s.c_id
group by c.c_id
需要班级名称，怎么办？
select c.c_id,c.c_name,count(*) as 人数
from t_class c join t_stu s on c.c_id=s.c_id
group by c.c_id
必须将c_name放到group by后面，变成分组字段。
select c.c_name,count(*) as 人数
from t_class c join t_stu s on c.c_id=s.c_id
group by c.c_id,c.c_name
如果让3班也出现？
使用左连接。
select c.c_name,count(*) as 人数
from t_class c left join t_stu s on c.c_id=s.c_id
group by c.c_id,c.c_name
错在哪里呢？
因为用left join以后，三班一定会有一条记录，但是不表示有一个学生。
所以必须对学生的信息进行count。
select c.c_name,count(s.s_id) as 人数
from t_class c left join t_stu s on c.c_id=s.c_id
group by c.c_id,c.c_name
在工作中，经常会涉及到多个表连接。
如：查询：班级名称，姓名，科目名称，分数。
方法：
首先在草稿上画出表间关系图，这是最重要的。
然后确定书写的方向：班级表→学生表→成绩表→科目表
2种写法：传统和JOIN
select c_name,s_name,sub_name,score
from t_class c join t_stu s on c.c_id=s.c_id
?????????????? join t_score sc on s.s_id=sc.s_id
?????????????? join t_subject sub on sc.sub_id=sub.sub_id
;
select c_name,s_name,sub_name,score
from t_class c, t_stu s ,t_score sc,t_subject sub
where? c.c_id=s.c_id
and s.s_id=sc.s_id
and sc.sub_id=sub.sub_id
;
在开发中，由于连接时计算机消耗的资源多，所以要尽量少连接表。
全连接(full join)的效率非常低，所以尽量不要使用。
要掌握多个表(3个表以上)的连接写法。
作业：银行转账
1、简单版
帐户表：帐户ID，帐户名称，余额，密码
2、完整版
帐户表：帐户ID，帐户名称，余额，密码
业务表：业务ID，业务类型，发生金额，发生时间
业务明细表：流水号，业务ID，发生帐户，发生金额，
帐户表：帐户ID，帐户名称，余额，密码
????????? 1?????? a??????? 1000
????????? 2?????? b??????? 100
a向b转账300
业务表：业务ID，业务类型，发生金额，发生时间
??????? 10001??? 转账?????? 300????? 2012-1-2
业务明细表：流水号，业务ID，发生帐户，发生金额，
???????????? 2001?? 10001??? 1?????? -300
???????????? 2002?? 10001??? 2?????? 300
同时还要更新帐户表
帐户表：帐户ID，帐户名称，余额，密码
????????? 1?????? a??????? 1000-300
????????? 2?????? b??????? 100+300
转账时一共涉及(3个Insert+2个Update)
?
?
?
?
?
?
?
