表联结是一种查询机制,用来在select语句中关联表进行查询,称为联结。
Where 和 Join
注意:如果没有where条件,那么第一个表中的每一行数据都会与第二个表中的每一行数据进行匹配,不管逻辑是否匹配。这种结果为第一个表中的数据乘以第二个表中的数据,称为笛卡尔积。
select subject_name,count(stu_id) as 考试人数
from subject as sub
where subject.subject_id = assessment.subject_id
group by subject_name;
除了使用where进行表的联结查询外,还可以使用另一种联结方式 join on
select subject_name,count(stu_id) as 考试人数
from subject as sub
join assessment as ass
on sub.subject_id = ass.subject_id
group by subject_name;
或inner join(首选语法方式):
select subject_name,count(stu_id) as 考试人数
from subject as sub
inner join assessment as ass
on sub.subject_id = ass.subject_id
group by subject_name;
联结多个表
select stu_id,subject_name,socre
from subject,assessment,student
where student.stu_id= assessment.stu_id
and assessment.id = subject.id
and score >60;
join 用法
select stu_id,subject_name,socre
from subject,assessment,student
join assessment on student.stu_id= assessment.stu_id
join subject on assessment.id = subject.id
where score >60;
inner join同理
select stu_id,subject_name,socre
from subject,assessment,student
inner join assessment on student.stu_id= assessment.stu_id
inner join subject on assessment.id = subject.id
where score >60;
自联结
使用子查询(嵌套查询)
select prod_id,prod_name
from products
where vend_id = (select vend_id from products where prod_id = 'AC21');
使用自联结方式查询
join on
select p1.prod_id,p1.prod_name from products as p1
join products as p2
on p1.vend_id = p2.vend_id
where p2.prod_id='AC21'
where
select p1.prod_id,p1.prod_name
from products as p1, products as p2
where p1.vend_id = p2.vend_id and p2.prod_id='AC21'
子查询(嵌套查询)是目前可明确知道的 sql中运行效率最低的一种查询方式,尽可能避免使用嵌套语句。
外联结
left join :以左侧的表为基准,去关联右侧的表进行联结,如果存在来自右侧表中未关联的数据,则显示为null。
right join:以右侧的表为基准,去关联左侧的变进行联结,如果存在来自左侧中未关联的数据,则显示为null。
例如:查询每个客户下的订单,包括那些没有下订单的客户。
select customers.cust_id,orders.order_num
from customers left join orders
on customers.cust_id = orders.cust_id;
Union
MySQL支持多个查询,并将结果作为单个查询结果集返回,这些组合查询通常称为 并(union)或符合查询(compound query)。
Unio原则
- Union 必须由两条或两条以上的SELECT语句组成,语句之间用关键字Union分割(因此,如果组合n个SELECT语句,将要使用(n-1)个Union关键字)
- Union中每个查询必须包含相同的列、表达式或聚集函数(不过个个列不需要以相同的次序列出)
- 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含的转化的类型(例如,不同的数值类型或不同的日期类型)。
注意事项:
- 当使用Union进行并合查询时,Union会默认地从查询结果集中自动去除重复行
如果想保留重复行,使用Union all 。 - select 语句的输出用Order by子句排序。在使用union组合查询时,只能使用一条order by子句,它必须出现在最后一条select语句之后 。
select vend_id,prod_id,prod_price from products where prod_price <= 5
union
select vend_id,prod_id,prod_price from products where vend_id in(1001,1002)
order by prod_price;