MySQL 進階查詢

標籤：

進階查詢

1.串連查詢(對列的擴充)

第一種形式
select * from Info,Nation #會形成笛卡爾積

select * from Info,Nation where Info.Nation = Nation.Code #加入篩選條件

select Info.Code,Info.Name,Sex,Nation.Name from Info,Nation where Info.Nation = Nation.Code
#查詢指定列

select Info.Code as ‘代號‘,Info.Name as ‘姓名‘,Sex as ‘性別‘,Nation.Name as ‘民族‘,Birthday as ‘生日‘ from Info,Nation where Info.Nation = Nation.Code
#換表頭

第二種形式:
select * from Info join Nation #join串連,形成笛卡爾積 查詢很慢
select * from Info join Nation on Info.Nation = Nation.Code #join關鍵字

 

2.聯集查詢(對行的擴充)

select * from Info where Nation = ‘n002‘
union #關鍵字
select * from Info where Code = ‘p002‘

 

3.子查詢(無關子查詢) 如果子查詢語句可以單獨拿出來執行,就是無關查詢
在一個sql語句中有兩個子查詢,其中一個a查詢的結果作為另一個b的

select查詢條件,a成為裡層查詢,b為外層查詢或父查詢

查詢民族為‘漢族‘的人員資訊:
select * from Info where Nation = ( select Code from Nation where Name = ‘漢族‘)

查詢民族為 ‘漢族‘ 或者 ‘回族‘ 的人員資訊

select * from Info where Nation in (select Code from Nation where Name = ‘漢族‘ or Name = ‘回族‘)

select * from Info where Nation not in (select Code from Nation where Name = ‘漢族‘ or Name = ‘回族‘)
#in 關鍵字 not in不在表裡

 

4.子查詢(相互關聯的子查詢)

查詢同一系列的 油耗 要比平均油耗低的汽車資訊
子查詢
select avg(oil) from Car where Brand = ‘‘
父查詢
select * from Car where oil<平均油耗

select * from Car a where a.oil < (select avg(b.oil) from Car b where b.Brand = a.Brand)

#b.Brand 是 查詢條件,a.Brand 是 逐條的資訊

MySQL 進階查詢