讀《MYSQL開發人員SQL權威指南》筆記

USING使用

FROM     TEAMS INNER JOIN PLAYERS ON TEAMS.PLAYERNO = PLAYERS.PLAYERNO

FROM     TEAMS INNER JOIN PLAYERS USING(PLAYERNO)

彙總函式與分組資料

彙總函式

資料分組

前面提到的SQL函數，其實都是行函數。所謂行函數就是作用在每條記錄上的函數。本章將介紹列函數，它們都是用於統計摘要資料。除列函數以外，本章還將介紹資料分組的方法，即Group By 子句的用法。在實際應用中，統計匯總和資料分組會經常被放在一起使用。

使用 count 函數求記錄個數

count 文法

count ( 列名 )

例：統計student表中的學生人數

select count ( * ) as 總人數 from student

例：統計沒有郵箱的人數

select count ( * ) as 沒有郵箱的人數 from student where mail is null

使用 sum 函數求和

文法:

sum ( 列名 )

例：計算wj2內 jihe 的總成績

select sum ( jihe ) as 幾何成績 from wj2

使用 avg 函數求某欄位的平均值

文法：

avg ( 列名 )

例：求 wj2 表中 jihe 的平均值

select    avg ( jihe ) as 幾何成績平均值 from wj2

使用 max 、min 函數求最大、最小值

文法：

max ( 列名 )

min ( 列名 )

例：求wj2表中幾何的最高成績和最低成績

select max ( jihe ) as 幾何最高成績 , min ( jihe ) as 幾何最低成績 from wj2

資料分組（group by）

group by 子句用來分組資料，首先必須瞭解分組是根據指定欄位的不同值劃分的。例如，性別欄位只只有2種值，因此，如果按性別欄位分組資料就會產生兩個數組；又例如，假設所屬院系欄位值中有5種不同的值，則如果按所屬院系分組就會產生5個組等。

例：student表，按所屬院系分組

select 所屬院系

from student

group by 所屬院系

彙總函式與分組配合使用

count ( * ) 與 group by 的配合使用

例：student 表，按所屬院系分組，並顯示人數

select 所屬院系,count(*) as 各院系人數

from student

group by 所屬院系

查詢資料的長條圖

長條圖是表示不同實體之間資料相對分布的條狀圖，在一個查詢語句中使用group by 子句，不僅可以查詢資料，還可以格式化資料組建圖表。

例：從student表中，查詢一個表示每個院系學生人數的長條圖

select 所有院系 ，

           replicate ( ' = ' , count ( * ) * 3 ) as 人數對比圖

from student

group by 所屬院系

排序分組結果

例：在student表中，統計每個院系的學生人數，並按學生人數降序排序

select 所屬院系,count(*) as 人數
from student
group by 所屬院系
order by count(*) desc

反轉查詢結果

有時，執行查詢語句後得到的資料雖然正確元誤，但是當人們查看時會很不方便

例：從student表中，查詢每個院系的男生人數和女生人數

select 所屬院系,性別,count(*) as 人數
from student
group by 所屬院系,性別
order by count(*) desc

上面的查詢結果，雖然將統計資料查詢出來了，但並不是人們習慣的統計樣式。那麼人們習慣的統計樣式又是什麼樣的呢？

例：上一個例子中的習慣樣式

select 所屬院系,

          count ( case

                            when 性別 = '男' then 1

                            else null

                      end ) as 男生人數 ,

          count ( case

                             when 性別 = '女' then 1

                             else null

                       end) as 女生人數

from student

group by 所屬院系

使用 having 子句設定分組查詢條件

有時，人們只希望查看想要的分組的統計資訊，而不是所有分組的統計資訊。例如只想查看軟體工程和電腦硬體的總人數。這時就需要把其它的系過濾掉

例：在student 表中，統計軟體工程系和電腦硬體系的人數，並按學生人數排序

select 所屬院系 , count ( * ) as 人數

from student

group by 所屬院系

having 所屬院系 in ( ' 軟體工程 ' , ' 電腦硬體 ' )

order by count ( * )

例：統計Score表中，考試總成線大於450分的學生的資訊

select 學號 , sum(考試成績) as 考試總成績

from score

group by 學號

having sum(考試成績)>=450

order by 考試總成績 desc

第11章

多表串連查詢

將資料存放區在多個不同表的原因

範式

串連查詢

進階串連查詢組合查詢

多表串連查詢是SQL語言最強大的功能之一。它可以在執行查詢時動態地先將表串連起來，然後從中查詢資料。本章將介紹多表串連查詢的相關內容，同進介紹組合查詢的用法。

範式

第一範式：

第一範式是關聯式資料庫的底線，要想成為關聯式資料庫則必須滿足第一範式，第一範式的內容為，記錄的第一個分量都是不可分割的基本資料項目。

第二範式：

要滿足第二範式，首先必須滿足第一範式，即滿足第一範式是滿足第二範式的前提條件，其次，第二範式增加的要求是每一個非主屬性要完全函數依賴於碼。

第三範式：

第三範式是在滿足第二範式的基礎上，增加了每一個非主屬性都不傳遞依賴於碼的要求。

串連查詢

無串連規則串連

無串連規則串連後得到的結果是兩個表中的每一行都互相串連，即結果為笛卡兒積。

文法：

select    *(或欄位列表)

from 表名1，表名2

有串連規則串連

有串連規則串連，其實就是在無串連規則的基礎上，加上Where子句指定串連規則的串連方法。

文法：

select *(或欄位列表)

from    表名1，表名2

Where 串連規則

例：將T1和T2表正確串連的語句如下

select *

from T1,T2

where T1.職工號=T2.職工號

使用兩表串連查詢資料

例：查詢名為“張三”的學生的所有課程的平時成績和才試成績

select student.學號,student. 姓名,score.課號,score.平時成績,score.考試成績

from student,score

where student.姓名='張三'

and student.學號=score.學號

order by score.才試成績 desc,score.平時成績 desc

使用表別名簡化語句

例：

select st.學號,st.姓名,c.課名,s.平時成現,s.考試成績

from student as a

         score as b

         sourse as c

where st.姓名='張三'

and st.學號=s.學號

and s.課號=c.課號

例：查詢“電腦基礎”課程，考試成績大於等於90分的學生的學號、姓名、系別和考試成績，並按考試成績降序排序

select st.學號,st.姓名,st.所屬院系,s.考試成績

from score as s ,

course as c,

student as st

where c.課名='電腦基礎'

and s.考試成績>=90

and s.課號=c.課號

and st.學號=s.學號

order by s.考試成績 desc

使用 inner join 串連查詢

文法：

select * (或欄位列表)

from 表名1

        inner join 表名2

       on 串連規則

       inner join 表名3

       on 串連規則

其中，關鍵字“ON”之後是串連表的規則

進階查詢

自串連查詢

例：

select st1.*

from student as st1,student as st2

where st1.所屬院系=st2.所屬院系

and st2.姓名='張三'

或：

select *

from student as st1,student as st2

where st2.姓名='張三'

and st1.所屬院系=st2.所屬院系

外串連查詢

左外串連：

例：

select *

from student left outer join student2 on student.姓名=student.姓名

右外串連：

例：

select *

from student right outer join student2 on student.姓名=student.姓名

全外串連：

例：

select *

from student full outer join student2 on student.姓名=student.姓名

左外串連

select * from student left outer join student2 on student.姓名=student2.姓名

右外串連

select * from student right outer join student2 on student.姓名=student2.姓名

全外串連

select * from student full outer join student2 on student.姓名=student2.姓名

組合查詢(注意：因為組合查詢結果集的欄位名列表是根據第一個select子句的欄位名列表而定的，所以使用者在使用order by 時應當注意這一點)

組合查詢使用union 關鍵字

文法：

select 語句1

union

select 語句2

union

select 語句3

例：從student表中，查謁來源地為“北京市”或“江蘇省”或“內蒙古自治區”的學生的所屬院系資訊。

select 所屬院系

from student

where 來源地='廣東省'

union

select 所屬院系

from student

where 來源地='江蘇省'

union

select 所屬院系

from student

where 來源地='內蒙古自治區'

使用union 得到複雜的統計江總樣式

例：

select 學號,課號,考試成績

from student

union

select 學號,'總分',sum(考試成績)

from student

group by 學號

select 學號,'總分',avg(考試成績)

from student

group by 學號

第12章

子查詢

返回單值的子查詢

返回一列值的子查詢

相互關聯的子查詢

嵌入另一個seelct語句中的select語句稱為子查詢。目前，子查詢能完成的工作通過表串連幾乎都可以完成，而在過去，因為內串連的運行效率
比較差，外串連又不能使用，所以子查詢被運用重非常廣。但是，由於開發人員在過去幾年對DBMS的最佳化，使得內串連的運行效率明顯優於子查詢，面外串連也
被開發出來。所以人們開始放棄那些難理解的子查詢語句，而改用相對容易理解的表串連查詢語句。

雖然多數情況下，使用表串連查詢要優於子查詢，但是，在特定環境下，子查詢啟動並執行效率可能優於表串連查詢。為了能夠閱讀、理解早年編寫的SQL語句，所以本書還是將子查詢的內容加入了進來。

使用返回單值的子查詢

例：查詢所有學生“心理學”的考試成績，並以考試成績降序進行排序

select 學號,考試成績

from score

where 課號=(select 課號 from course where 課名 ='心理學')

order by 考試成績 desc

子查詢與彙總函式的配合使用

例：查詢出生日期最小的學生的所有資訊

select *

from student

where 出生日期=(select min(出生日期) from student)

例：查詢“心理學”考試成績大於其考試成績大於其考試成績平均分的所有學生的學號、平時成績和考試成績。

select 學號,平時成績,考試成績

from score

where 課號=(select 課號 from course where 課名='心理學') and 考試成績>(select
avg(考試成績) from score where 課號=(select 課號 from course where 課名='心理學'))

使用in 的子查詢

例：使用in的例子

select 課號

from score

where 課號 in (select 課號 from course where 類型='必修')

相互關聯的子查詢（它是一種使用SQL的舊方法，查詢的效率非常低，不提倡使用。只要明白就可以）

第13章 視圖

視圖基礎

視圖的建立

視圖的刪除

本章將講解SQL中的另一個概念－－視圖。視圖在資料庫應用中經常會出現，它最主要的應用是簡化複雜的查詢語句。視圖是由英文單詞VIEW翻譯過來的名詞，其實VIEW還有“查看”的意思，筆者認為有時該單詞譯成“查看方式”可能更貼切一些。

視圖基礎

例：定義一個視圖vw1，將一個查詢語句放入視圖內

create view vw1

as

select * from student

使用視圖時：

select * from vw1

視圖的刪除：

drop view vw1

視圖建立文法：

create view 視圖名

as

資料庫動作陳述式

例：

create view view_student_all

as

select st1.*,st2.*

from student as st1 join student2 as st2 on st1.姓名=st2.姓名

利用視圖提高資料安全性

1.隱藏列資料

例：建立一個只能查看 學號 姓名 和 性別 3個列的視圖 vw_student1

create voiw vw_student1

as

select 學號,姓名,性別

from student

2.隱藏行資料

例：建立一個只能查看電腦系學生資訊析視圖vw_student2

create view vw_student2

as

select *

from student

where 所屬院系='電腦系'

利用視圖得到摘要資料

例：建立一個視圖vw_student3,顯示每個不同院系的學生人數。

create view vw_student3

as

select 所屬院系,count ( * ) as 人數

from student

group by 所屬院系

第14章

插入資料

直接向表插入資料

通過視圖插入資料

向資料表插入資料也是SQL語言最基本的功能之一。插入資料有多種方法，也需要遵循一定的規則。插入資料使用的SQL語句是INSERT。本章將詳細介紹INSERT的各種用法和使用時需要注意的規則。

直接向表插入資料

insert into 表名或視圖名

values (欄位1的值，欄位2的值。。。)

例：插入一條資料

insert into student(姓名,性別,總分,平時成績,address,所屬院系)

values ( '大彪','男',99,99,'中國長春','電腦軟體工程')

將查詢結果插入表

文法：

insert into 表名（欄位表）

select 語句

例：將student表中查詢到的資料存到student2中

insert into student2

select * from student where 姓名='大彪'

insert select 與 select into 的區別

insert select 將表中查詢到的內容插入到另一個表中

select into    將表中查詢到的內容重新儲存為一個新表

通過視圖插入資料

例：先建立視圖

create view vw_computer

as

select *

from student

where 所屬院系='電腦系'

在建立視圖後執行下面語句

select *

from vw_computer

查詢 所屬院系 為 電腦系 的資料。

再進行插入操作

insert into vw_computer

values ( '0017','蔣十九','女','1982-05-09','山東省',null,null,'電腦系' )

with check option

最後的一句 with check option 是為了保證插入的資料內，所屬院系=電腦系，這樣才可能使資料安全，因為視圖操作的只是 電腦系 的學員資訊。