SQL SERVER複習筆記04

--五,compute by,根據compute by 和order by 的by後面的列來進行分組,select * from book1order by 書名 desccompute avg(定價) by 書名--六.group byselect 編號 , COUNT(書名) from book1group by 書名/*報錯訊息 8120，層級 16，狀態 1，第 1 行挑選清單中的列 'book1.編號' 無效，因為該列沒有包含在彙總函式或 GROUP BY 子句中。說明,SELECT 出的列必須是GROUP BY裡會包含的*/select 書名,COUNT(書名) as '本數' from book1group by 書名--七.having ，與WHERE意思相同,但是where更有效率,只是WHERE中不能使用彙總函式,而SELECT和HAVING可以--平價定價大於60的書,這裡就只能用havingselect 書名,avg(定價) as '平均價格' from book1group by 書名having avg(定價)>60--由此,我們可以看到WHERE是對原始記錄進行過慮,面HAVING是對查詢結果進行過慮--八.巢狀查詢,注意:子查詢中不能用ORDER BY,它只能用於最終查詢結果的排序;子查詢只返回一列資料select * from book1where 定價>(select avg(定價) from book1)/*九.UNION,兩個規則:1,所有查詢中列數和列的順序必須相同,表1第N列對應表2第N列;2,各列相對應資料類型必須相相容,即要麼資料類型要同,要麼SQL SERVER能從一種資料類型轉換為另一種資料類型.列名是以表1為準的,所以要變更列名要在表1更改*/select 編號 as 'BOOK1編號與BOOK2書名' from book1unionselect 書名 from book2order by 書名/*訊息 104，層級 16，狀態 1，第 4 行如果該語句包含 UNION、INTERSECT 或 EXCEPT 運算子，則 ORDER BY 項必須出現在挑選清單中。顯然這裡0RDER BY 後面用表2的列是不對的*/select 編號 as 'BOOK1編號與BOOK2書名' from book1unionselect 書名 from book2order by 編號--十,多表查詢與串連.1.內串連:等值串連和自然串連;2.外串連:左外串連、右外串連、全串連;3.交叉串連--1.笛卡爾積select * from book1,teacher--2.串連條件,一般來說,N個表串連要有N-1個條件,多表或視圖可以在同一資料庫,也可以不在--向teacher表插入資料exec sp_help teacherinsert into teacher values ('1011','張三','男',35,'233','10117')--完整表結構插入  insert into teacher values ('0172','李四','男',35,'119','10172')--完整表結構插入  insert into teacher values ('1012','小七','女',35,'112','1012')--完整表結構插入  insert into teacher values ('1017','小五','女',35,'110','1017')--完整表結構插入 insert into teacher values ('3424','何東','女',35,'3434','103417') select * from teacher  --(1)等值串連,串連列出現兩次,則會出現同名的列select book1.編號,book1.書名,book1.書名,book1.定價,teacher.職稱 from book1,teacherwhere book1.編號=teacher.編號 --當多表中有同名列時,一定要用[table_name].[column_name]的格式  select * from book1,teacher where book1.編號=teacher.編號--(2)自然串連,可去掉重複列,即在等值串連上,用SELECT子句指定結果表中包含的列名select book1.*,作者姓名,職稱,聯絡電話 from book1,teacherwhere book1.編號=teacher.編號--(3)不等值串連select book1.*,作者姓名,職稱,聯絡電話 from book1,teacherwhere book1.編號<>teacher.編號select book1.編號,book1.書名,book2.編號,book2.書名 from book1,book2where book1.編號<>book2.編號--(4)自串連,一個表自身串連,這樣能把一個表中的行與行之間聯絡起來select a.編號, a.出版社 ,a.書名, a.定價, b.編號 as b編號from book1 as a,book1 as bwhere a.出版社=b.出版社 and a.編號<>'0172' and b.編號='101172'--(5)左外串連,相當於先進行自然串連,然後將左表不匹配的行添加進自然串連結果集裡,用空值來填充右表select book1.*,作者姓名,職稱,聯絡電話 from book1,teacherwhere book1.編號*=teacher.編號/*報錯:訊息 4147，層級 15，狀態 1，第 2 行此查詢使用的不是 ANSI 外部聯結運算子("*=" 或 "=*")。若要不進行修改即運行此查詢，請使用 ALTER DATABASE 的 SET COMPATIBILITY_LEVEL 選項將當前資料庫的相容層級設定為 80。極力建議使用 ANSI 外部聯結運算子(LEFT OUTER JOIN、RIGHT OUTER JOIN)重寫此查詢。在將來的 SQL Server 版本中，即使在向後相容模式下，也不支援非 ANSI 聯結運算子。修改如下:*/select book1.*,作者姓名,職稱,聯絡電話 from book1 LEFT OUTER JOIN teacherON book1.編號=teacher.編號--(6)右外串連,相當於先進行自然串連,然後將右表不匹配的行添加進自然串連結果集裡,用空值來填充左表select book1.*,作者姓名,職稱,聯絡電話 from book1 RIGHT OUTER JOIN teacherON book1.編號=teacher.編號--(7)全外串連,左右表空值都填充進來select book1.*,作者姓名,職稱,聯絡電話 from book1 FULL  JOIN teacherON book1.編號=teacher.編號--(8)交叉串連,列數為兩表列數之和,行數為兩表行之積select * from book1,teacher--十.exists和inselect 編號,書名,定價 from book1where exists (select 編號 from teacher)select * from teacherselect  編號,書名,定價 from book1where 編號 in (select 編號 from teacher)