復雜查詢

原先建立的數據集情況：

student（學號，姓名，出生日期，性別）

score（學號，課程號，成績）

course（課程號，課程名稱，教師號）

teacher（教師號，教師姓名）

• 視圖

1.在數據庫中有很多表，表中存放了實際數據，視圖中存放的是SQL查詢語句，當通過客戶端連接到數據庫開始使用視圖時，會運行視圖中的查詢語句，從表中查找數據導出到臨時表中（臨時表表示當數據庫與客戶端斷開連接時，臨時表消失）。

2.創建視圖

create view 視圖名稱（視圖列名1，視圖列名2，...）

as

select 查詢語句

create view 按性別匯總(性別,人數) as select 性別,count(*) from student group by 性別

注意：視圖的列名與查詢語句中選擇的表的列名需一一對應。

3.界面操作：運行查詢語句后，右鍵視圖點擊刷新就能看到出現新的視圖

4.使用及作用

select 性別,人數 from 按性別匯總;

刪除視圖，選中視圖右鍵刪除。

作用：頻繁使用某語句，可定義好視圖直接使用即可。表中數據更新，視圖數據同步更新。不需多余的存儲位置，節省數據的儲存空間

5.注意事項

避免在視圖中創建視圖，多重視圖會降低SQL的運行性能和效率。不能往視圖中插入數據，會報錯。

運行結果展示：

• 子查詢

1.子查詢：在from查詢字句中直接寫定義視圖的SQL查詢語句。

字句as后為子查詢的名稱，是一次性的，不保存，語句運行完便消失。運行順序：子查詢→外部查詢語句。

2.使用：

select

子查詢除了像上面一樣放到from字句中，還可以放到where字句中，與in、any、all一起使用。

IN: 找出每個課程中成績最低的學號。（下例為關聯子查詢）

select * from score A where 成績 in (select min(成績) from score Bwhere B.課程號=A.課程號group by 課程號) order by 課程號,學號;

ANY: 哪些學生的成績比課程0002的全部成績里的任意一個高呢？(SOME相同)

select * from score where 成績 > any(select 成績 from score where 課程號='0002');

ALL: 哪些學生的成績比課程0002的全部成績里的都高呢？

select * from score where 成績 > all(select 成績 from score where 課程號='0002');

注意，這里的大于any和all換種說法可理解為，大于某組別中最小值和大于某組別中最大值。

但all、any返回結果不是一個數值而是一組集合。

3.作用：偶爾使用的語句，不像視圖那樣經常用。

4.注意事項：

a>3*all(b) ×

a/3>all(b) √

避免使用多層嵌套子查詢，因為子查詢層數有限制，層數越多性能越差。

as 子查詢名稱 可以省略，但最好標注方便查看。

運行結果展示：

• 標量子查詢

1.標量子查詢：只返回一行一列查詢結果（單一值）的子查詢。

大于平均成績的學生學號和成績

select 學號,成績 from score where 成績 > (select avg(成績)from score);

查找成績介于差生（成績不及格)的平均值和優等生（成績大于80)的平均值之間學生的學號和成績

select 學號,成績 from score where 成績 between (select avg(成績) from score where 成績<=60) and (select avg(成績) from score where 成績>80);

標量子查詢可放在select子句中

select 學號,成績,(select avg(成績) from score) as 平均成績 from score;

2.作用：用于偶爾使用的語句中，因為返回的是單一的語句，所以可以和運算符連用實現復雜查詢條件。

3.注意事項：不能返回多行數據（下例錯誤）

select 學號,成績,(select avg(成績) from score group by 課程號) as 平均成績 from score;

運行結果展示：

• 關聯子查詢

1.關聯子查詢：

查找出每個課程中大于對應課程平均成績的學生

select * from score A where 成績 >(select avg(成績) from score B where B.課程號 = A.課程號 group by 課程號)

注意：外表別名可在子查詢中識別出，但子查詢中別名僅在子查詢中有效。

運行結果展示：

• 如何用SQL解決業務問題

哪些學生的成績比課程0002的全部成績里的任意一個高

全部0002的成績（子查詢）→大于任意

select * from score where 成績 > any (select 成績 from score where 課程號='0002');

查找報錯原因，可逐一排查

可先選中子查詢語句，點擊運行已選中的，看是否報錯。

運行結果展示：

• 各種函數

1.匯總函數

count（列名）求某列的行數

sum（列名）求某列的數據和

avg（列名）求某列數據的平均值

min（列名）最小值

max（列名）最大值

2.算數函數

round（數值，保留小數的位數（可為負））對數據四舍五入

abs（數值）求絕對值

mod（數值）求余數

3.字符串函數

length（字符串）求字符串長度

lower（字符串）全部轉換為小寫

upper（字符串）全部轉換為大寫

concat（字符串1，字符串2）字符串拼接

replace（字符串，被替換的字符串，用什么字符串替換）替換

substring（字符串，截取起始位置，截取長度）截取

4.日期函數

current_date 當前日期 2019-09-30

current_time 當前時間 22:19:12

current_timestamp 當前日期和時間 2019-09-30 22:19:12

year（日期）、month（日期）、day（日期） 獲取日期的年份、月份、天份 2019、09、30

dayname（日期）日期對應星期幾 星期一

select * from student where year(出生日期)='1990';

運行結果展示：

SQLZOO練習題 鏈接：

寓言：Exercise of SQLZOO?zhuanlan.zhihu.com

總結

以上是生活随笔為你收集整理的mysql练习_MySQL练习——4的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。