oracle--多表查询
十一、多表查询 (有关联)
1. select * from 表名1 别名1, 表名2 别名2 where 连接与查询条件
n个表,条件有n-1个
select * from stu, course; //两两的相积
select * from stu , course where stu.id = course.id ;
select s.last_name, s.first_name, c.cname from stu s , course c where s.id = c.id ;
2、内联接inner join
select * from 表名1 inner join 表名2 on 联接的条件 where 查询查条件
select * from stu inner join course on stu.id=course.id;
select * from stu , course where stu.id=course.id;
select * from stu inner join course on stu.id=course.id where stu.id>1;
3、外联接
select * from 表名1 left join 表名2 on 联接的条件 where 查询查条件
1)左联接 left join
select * from stu left join course on stu.id = course.id;
左表全部显示,右表只显示满足条件的
2)右联接 right join
select * from stu right join course on stu.id = course.id;
右表全部显示,左表只显示满足条件的
3)全联接 full join
select * from stu full join course on stu.id = course.id;
右表全部显示,左表全部显示
4、子查询可以转换为多表查询 (子查询的返回结果只有一个)
在stu表中查找出比小红年龄大的人
select * from stu where age > (select age from stu where last_name='小红');
select s1.* from stu s1,stu s2 where s1.age>s2.age and s2.last_name='小红';
select s1.last_name, s1.age , s1.id from stu s1, stu s2 where s1.age>s2.age and s2.last_name='海' ;
习题9:从表S_CUSTOMER,S_REGION中查找出顾客表的NAME、REGION_ID列和区域表的name列,REGION_ID为5号区域
select s1.name,s1.region_id,s2.name from s_customer s1 full join s_region s2 on s1.region_id = s2.id where s2.id = 5;select c.name, c.region_id, r.name from s_customer c inner join s_region r on c.region_id=r.id where c.region_id=5
select c.name, c.region_id, r.name from s_customer c left join s_region r on c.region_id=r.id where c.region_id=5
select c.name, c.region_id, r.name from s_customer c right join s_region r on c.region_id=r.id where c.region_id=5
select c.name, c.region_id, r.name from s_customer c full join s_region r on c.region_id=r.id where c.region_id=5
select c.name, c.region_id, r.name from s_customer c , s_region r where c.region_id=r.id and c.region_id=5
习题11:请统计出表S_ORD中,11号销售人员(SALES_REP_ID=11)的订单数量,以及他的TOTAL平均值
select sales_rep_id,count(*) "订单数量",avg(total) "total平均值" from s_ord group by sales_rep_id having sales_rep_id = 11 ;
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
SQL UNION ALL 语法
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
两个select语句的字段类型匹配,而且字段个数要相同,字段类型名
在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果