常見SQL語句和SQL基礎知識

標籤：var   values   資料集   開始   否則   釋放   屬性   XML   val   

引自：http://blog.csdn.net/u012467492/article/details/46790205

SQL語句考察（一）

1.查詢出每門課都大於80 分的學生姓名

name   kecheng   fenshu
張三    語文       81
張三     數學       75
李四     語文       76
李四     數學       90
王五     語文       81
王五     數學       100
王五     英語       90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)
    select name from table group by name having min(fenshu)>80

2. 學生表 如下:
自動編號    學號     姓名  課程編號 課程名稱 分數
1             2005001 張三     0001      數學        69
2             2005002 李四     0001      數學        89
3             2005001 張三     0001      數學        69
刪除除了自動編號不同, 其他都相同的學生冗餘資訊

A: delete tablename where 自動編號 not in

　　　　　　(select min( 自動編號) from tablename group by學號, 姓名, 課程編號, 課程名稱, 分數)，，group by表示對後面的屬性集進行分組

3.一個叫 team 的表，裡面只有一個欄位name, 一共有4 條紀錄，分別是a,b,c,d, 對應四個球隊，現在四個球隊進行比賽，用一條sql語句顯示所有可能的比賽組合.
你先按你自己的想法做一下，看結果有我的這個簡單嗎？

答：select a.name, b.name
from team a, team b 
where a.name < b.name  組合排列

4.請用SQL 語句實現：從TestDB 資料表中查詢出所有月份的發生額都比101 科目相應月份的發生額高的科目。請注意：TestDB 中有很多科目，都有1 －12 月份的發生額。
AccID ：科目代碼，Occmonth ：發生額月份，DebitOccur ：發生額。
資料庫名：JcyAudit ，資料集：Select * from TestDB

答：select a.*
from TestDB a ,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID=‘101‘ group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

5.面試題：怎麼把這樣一個表
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成這樣一個結果
year     m1    m2    m3   m4
1991   1.1    1.2    1.3    1.4
1992   2.1    2.2    2.3    2.4 

select year, 
(select amount  from  aaa m where month=1   and m.year=aaa.year) as m1,
(select amount  from  aaa m where month=2   and m.year=aaa.year) as m2,
(select amount  from  aaa m where month=3   and m.year=aaa.year) as m3,
(select amount  from  aaa m where month=4   and m.year=aaa.year) as m4
from aaa   group by year

6. 說明：複製表( 只複製結構, 源表名：a    新表名：b) 

--select * into b from a    //拷貝表的資料以及表的結構（前提：表b不存在）

--insert into b select * from a  //只拷貝表的資料，不拷貝表的結構（前提：表b存在）
--select * into b from a  where 1<>1// 表結構拷貝，不拷貝資料（前提：表b不存在） 

8. 說明：顯示文章、提交人和最後回複時間
SQL: select a.title,a.username,b.adddate

　　　　　　from table a,(select max(adddate) adddate from table where table.title=a.title) b

9. 說明：外串連查詢( 表名1 ：a 表名2 ：b)

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUTER JOIN b ON a.a = b.c

 

10. 說明：排程提前五分鐘提醒
SQL: select * from 排程 where datediff(‘minute‘,f 開始時間,getdate())>5

11. 說明：兩張關聯表，刪除主表（info）中已經在副表（infobz）中沒有的資訊
Delete from info where not exists (select * from infobz where info.infid=infobz.infid )

12.有兩個表A 和B ，均有key 和value 兩個欄位，如果B 的key 在A 中也有，就把B 的value 換為A 中對應的value
這道題的SQL 語句怎麼寫？

update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key)

 

13.原表:

courseid coursename score
-------------------------------------
     1 　　　java 　　　70
　 2           oracle          90
    3             xml            40
    4             jsp             30
   5          servlet           80
-------------------------------------
為了便於閱讀, 查詢此表後的結果顯式如下( 及格分數為60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
寫出此查詢語句                                                                                                     

SQL> select courseid, coursename ,score ,decode(sign(score-60),-1,‘fail‘,‘pass‘) as mark from course_v;

14，create table testtable1

(
id int IDENTITY,
department varchar(12) 
)

select * from testtable1
insert into testtable1 values(‘設計‘)
insert into testtable1 values(‘市場‘)
insert into testtable1 values(‘售後‘)
/*
結果
id department
1   設計
2   市場
3   售後 
*/
create table testtable2
(
id int IDENTITY,
dptID int,
name varchar(12)
)
insert into testtable2 values(1,‘張三‘)
insert into testtable2 values(1,‘李四‘)
insert into testtable2 values(2,‘王五‘)
insert into testtable2 values(3,‘彭六‘)
insert into testtable2 values(4,‘陳七‘)
/*
用一條SQL語句，怎麼顯示如下結果
id dptID department name
1   1      設計        張三
2   1      設計        李四
3   2      市場        王五
4   3      售後        彭六
5   4      黑人        陳七
*/

答案：

SELECT testtable2.* , ISNULL(department,‘黑人‘)
FROM testtable1 right join testtable2 on testtable2.dptID = testtable1.ID

15，表A，結構如下： 

p_ID  p_Num   s_id 
1 　　10 　　01 
1 　　12 　　02 
2 　　8 　　01 
3 　  11　　 01 
3 　　8 　　03 
其中：p_ID為產品ID，p_Num為產品庫存量，s_id為倉庫ID。請用SQL語句實現將上表中的資料合併，合并後的資料為： 
p_ID s1_id s2_id s3_id 
1   　　10 　12     0 
2 　　  8 　　0 　 0 
3 　　11 　　0 　8 
其中：s1_id為倉庫1的庫存量，s2_id為倉庫2的庫存量，s3_id為倉庫3的庫存量。如果該產品在某倉庫中無庫存量，那麼就是0代替。

結果：

select p_id ,
sum(case when s_id=1 then p_num else 0 end) as s1_id
,sum(case when s_id=2 then p_num else 0 end) as s2_id
,sum(case when s_id=3 then p_num else 0 end) as s3_id
from myPro group by p_id

16，為管理業務培訓資訊，建立3個表：

     S(S#,SN,SD,SA)S#,SN,SD,SA分別代表學號，學員姓名，所屬單位，學員年齡

     C(C#,CN)C#,CN分別代表課程編號，課程名稱

      SC(S#,C#,G) S#,C#,G分別代表學號，所選的課程編號，學習成績

    （1）使用標準SQL嵌套語句查詢選修課程名稱為’稅收基礎’的學員學號和姓名?

          答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’稅收基礎’)

      (2) 使用標準SQL嵌套語句查詢選修課程編號為’C2’的學員姓名和所屬單位?

答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’

      (3) 使用標準SQL嵌套語句查詢不選修課程編號為’C5’的學員姓名和所屬單位?

答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)

       (4)查詢選修了課程的學員人數

答：select 學員人數=count(distinct s#) from sc

       (5) 查詢選修課程超過5門的學員學號和所屬單位?

答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)

17，查詢A(ID,Name)表中第31至40條記錄，ID作為主鍵可能是不是連續增長的列，完整的查詢語句如下：

select top 10 * from A where ID >(select max(ID) from (select top 30 ID from A order by A ) T) order by A

2.查詢表A中存在ID重複三次以上的記錄,完整的查詢語句如下：
select * from(select count(ID) as count from table group by ID)T where T.count>3

SQL基礎知識（一）

1．觸發器的作用？

  答：觸發器是一種特殊的預存程序，主要是通過事件來觸發而被執行的。它可以強化約束，來維護資料的完整性和一致性，可以追蹤資料庫內的操作從而不允許未經許可的更新和變化。可以聯級運算。如，某表上的觸發器上包含對另一個表的資料操作，而該操作又會導致該表觸發器被觸發。

2。什麼是預存程序？用什麼來調用？

答：預存程序是一個先行編譯的SQL 語句，優點是允許模組化的設計，就是說只需建立一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次SQL ，使用預存程序比單純SQL 語句執行要快。可以用一個命令對象來調用預存程序。

3。索引的作用？和它的優點缺點是什嗎？

答：索引就一種特殊的查詢表，資料庫的搜尋引擎可以利用它加速對資料的檢索。它很類似與現實生活中書的目錄，不需要查詢整本書內容就可以找到想要的資料。索引可以是唯一的，建立索引允許指定單個列或者是多個列。缺點是它減慢了資料錄入的速度，同時也增加了資料庫的尺寸大小。

3。什麼是記憶體流失？

答：一般我們所說的記憶體流失指的是堆記憶體的泄漏。堆記憶體是程式從堆中為其分配的，大小任意的，使用完後要顯示釋放記憶體。當應用程式用關鍵字new 等建立對象時，就從堆中為它分配一塊記憶體，使用完後程式調用free 或者delete 釋放該記憶體，否則就說該記憶體就不能被使用，我們就說該記憶體被泄漏了。

4。維護資料庫的完整性和一致性，你喜歡用觸發器還是自寫商務邏輯？為什嗎？

答：我是這樣做的，儘可能使用約束，如check, 主鍵，外鍵，非空欄位等來約束，這樣做效率最高，也最方便。其次是使用觸發器，這種方法可以保證，無論什麼業務系統訪問資料庫都可以保證資料的完整新和一致性。最後考慮的是自寫商務邏輯，但這樣做麻煩，編程複雜，效率低下。

5。什麼是事務？什麼是鎖？

答：事務就是被綁定在一起作為一個邏輯工作單元的SQL 語句分組，如果任何一個語句操作失敗那麼整個操作就被失敗，以後操作就會復原到操作前狀態，或者是上有個節點。為了確保要麼執行，要麼不執行，就可以使用事務。要將有組語句作為事務考慮，就需要通過ACID 測試，即原子性，一致性，隔離性和持久性。

  鎖：在所有的 DBMS中，鎖是實現事務的關鍵，鎖可以保證事務的完整性和並發性。與現實生活中鎖一樣，它可以使某些資料的擁有者，在某段時間內不能使用某些資料或資料結構。當然鎖還分層級的。

6。什麼叫視圖？遊標是什嗎？

答：視圖是一種虛擬表，具有和物理表相同的功能。可以對視圖進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改不影響基本表。它使得我們擷取資料更容易，相比多表查詢。

  遊標：是對查詢出來的結果集作為一個單元來有效處理。遊標可以定在該單元中的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行做修改。一般不使用遊標，但是需要逐條處理資料的時候，遊標顯得十分重要。

你能向我簡要敘述一下SQL Server 2000中使用的一些資料庫物件嗎?

你希望聽到的答案包括這樣一些對象:表格、視圖、使用者定義的函數，以及預存程序;如果他們還能夠提到像觸發器這樣的對象就更好了。如果應聘者不能回答這個基本的問題，那麼這不是一個好兆頭。

NULL是什麼意思?

NULL(空)這個值是資料庫世界裡一個非常難纏的東西，所以有不少應聘者會在這個問題上跌跟頭您也不要覺得意外。

NULL這個值表示UNKNOWN(未知):它不表示“”(Null 字元串)。假設您的SQL Server資料庫裡有ANSI_NULLS，當然在預設情況下會有，對NULL這個值的任何比較都會生產一個NULL值。您不能把任何值與一個 UNKNOWN值進行比較，並在邏輯上希望獲得一個答案。您必須使用IS NULL操作符。

什麼是索引?SQL Server 2000裡有什麼類型的索引?

任何有經驗的資料庫開發人員都應該能夠很輕易地回答這個問題。一些經驗不太多的開發人員能夠回答這個問題，但是有些地方會說不清楚。

簡單地說，索引是一個資料結構，用來快速存取資料庫表格或者視圖裡的資料。在SQL Server裡，它們有兩種形式:叢集索引和非叢集索引。叢集索引在索引的葉級儲存資料。這意味著不論叢集索引裡有表格的哪個(或哪些)欄位，這些欄位都會按順序被儲存在表格。由於存在這種排序，所以每個表格只會有一個叢集索引。非叢集索引在索引的葉級有一個行標識符。這個行標識符是一個指向磁碟上資料的指標。它允許每個表格有多個非叢集索引。

什麼是主鍵?什麼是外鍵?

主鍵是表格裡的(一個或多個)欄位，只用來定義表格裡的行;主鍵裡的值總是唯一的。外鍵是一個用來建立兩個表格之間關係的約束。這種關係一般都涉及一個表格裡的主鍵欄位與另外一個表格(儘管可能是同一個表格)裡的一系列相連的欄位。那麼這些相連的欄位就是外鍵。

什麼是觸發器?SQL Server 2000有什麼不同類型的觸發器?

讓未來的資料庫開發人員知道可用的觸發器類型以及如何?它們是非常有益的。

觸發器是一種專用類型的預存程序，它被捆綁到SQL Server 2000的表格或者視圖上。在SQL Server 2000裡，有INSTEAD-OF和AFTER兩種觸發器。INSTEAD-OF觸發器是替代資料操控語言(Data Manipulation Language，DML)語句對錶格執行語句的預存程序。例如，如果我有一個用於TableA的INSTEAD-OF-UPDATE觸發器，同時對這個表格執行一個更新語句，那麼INSTEAD-OF-UPDATE觸發器裡的代碼會執行，而不是我執行的更新語句則不會執行操作。

AFTER觸發器要在DML語句在資料庫裡使用之後才執行。這些類型的觸發器對於監視發生在資料庫表格裡的資料變化十分好用。

您如何確一個帶有名為Fld1欄位的TableB表格裡只具有Fld1欄位裡的那些值，而這些值同時在名為TableA的表格的Fld1欄位裡?

這個與關係相關的問題有兩個可能的答案。第一個答案(而且是您希望聽到的答案)是使用外鍵限制。外鍵限制用來維護引用的完整性。它被用來確保表格裡的欄位只儲存有已經在不同的(或者相同的)表格裡的另一個欄位裡定義了的值。這個欄位就是候選索引鍵(通常是另外一個表格的主鍵)。

另外一種答案是觸發器。觸發器可以被用來保證以另外一種方式實現與限制相同的作用，但是它非常難設定與維護，而且效能一般都很糟糕。由於這個原因，微軟建議開發人員使用外鍵限制而不是觸發器來維護引用的完整性。

對一個投入使用的線上交易處理表格有過多索引需要有什麼樣的效能考慮?

你正在尋找進行與資料操控有關的應聘人員。對一個表格的索引越多，資料庫引擎用來更新、插入或者刪除資料所需要的時間就越多，因為在資料操控發生的時候索引也必須要維護。

你可以用什麼來確保表格裡的欄位只接受特定範圍裡的值?

這個問題可以用多種方式來回答，但是只有一個答案是“好”答案。您希望聽到的回答是Check限制，它在資料庫表格裡被定義，用來限制輸入該列的值。

觸發器也可以被用來限制資料庫表格裡的欄位能夠接受的值，但是這種辦法要求觸發器在表格裡被定義，這可能會在某些情況下影響到效能。因此，微軟建議使用Check限制而不是其他的方式來限制域的完整性。

如果應聘者能夠正確地回答這個問題，那麼他的機會就非常大了，因為這表明他們具有使用預存程序的經驗。

返回參數總是由預存程序返回，它用來表示預存程序是成功還是失敗。返回參數總是INT資料類型。

OUTPUT參數明確要求由開發人員來指定，它可以返回其他類型的資料，例如字元型和數值型的值。(可以用作輸出參數的資料類型是有一些限制的。)您可以在一個預存程序裡使用多個OUTPUT參數，而您只能夠使用一個返回參數。

什麼是相互關聯的子查詢?如何使用這些查詢?

經驗更加豐富的開發人員將能夠準確地描述這種類型的查詢。

相互關聯的子查詢是一種包含子查詢的特殊類型的查詢。查詢裡包含的子查詢會真正請求外部查詢的值，從而形成一個類似於迴圈的狀況。

常見SQL語句和SQL基礎知識