數(shù)據(jù)庫sql語句和重要知識點總結(jié)

1、SQL Server 2000簡單查詢查詢products表中p_price（商品價格）在800以上的商品詳細信息，SQL代碼如下所示：USE eshopSELECT *FROM productsWHERE m_price 800查詢products表中p_quantity（商品數(shù)量）在20和50之間的商品編號、商品名稱和商品數(shù)量，SQL代碼如下所示：USE eshopSELECT p_no, p_name, p_quantityFROM productsWHERE p_quantity = 20 AND p_quantity 30 AND m_sex = 男查詢orders表各商品銷售總量前

2、3名的商品編號和銷售總量，SQL代碼如下所示：USE eshopSELECT TOP 3 p_no, o_quantityFROM ordersORDER BY o_quantity DESC查詢orders表中購買過商品的會員帳號，要求去掉重復行，SQL代碼如下所示：USE eshopSELECT DISTINCT m_accountFROM orders查詢orders表已確認、已支付和已配送的訂單詳細信息，SQL代碼如下所示：USE eshopSELECT *FROM ordersWHERE o_confirm_state = 1 AND o_pay_state = 1 AND o_se

3、nd_state = 1SQL Server 2000高級查詢查詢性別為“男”的會員詳細信息，查詢結(jié)果按月薪降序排列，SQL代碼如下所示：USE eshopSELECT *FROM membersWHERE m_sex = 男ORDER BY m_salary DESC查詢?nèi)w會員的會員帳號，姓名和年齡并按家庭地址升序排列，同一地址中的會員按年齡降序排列，SQL代碼如下所示：USE eshopSELECT m_account, m_name, YEAR(GETDATE()-YEAR(m_birth) 年齡FROM membersORDER BY m_address, m_birth或USE e

4、shopSELECT m_account, m_name, DATEDIFF(YY, m_birth, GETDATE() 年齡FROM membersORDER BY m_address, m_birth查詢會員帳號為liuzc所購買的商品號和訂購日期，并按訂購日期升序排列，SQL代碼如下所示：USE eshopSELECT p_no, o_dateFROM ordersWHERE m_account = liuzcORDER BY o_date查詢購買商品號為0910810004總?cè)藬?shù)，SQL代碼如下所示：USE eshopSELECT COUNT(*)FROM ordersWHERE p

5、_no = 0910810004查詢20XX年6月6日前，所有商品的訂購總量，要求輸出商品號和訂購總量，SQL代碼如下所示：USE eshopSELECT p_no, SUM(o_quantity)FROM ordersWHERE o_date 10ORDER BY SUM(o_quantity) DESCSQL Server 2000聯(lián)接查詢查詢購買了商品號為“0910810004”的會員號和姓名，并以漢字標題顯示，SQL代碼如下所示：USE eshopSELECT DISTINCT members.m_account 會員號, m_name 姓名FROM membersJOIN order

6、sON members.m_account = orders.m_accountWHERE p_no = 0910810004查詢購買了商品名稱為“愛國者MP3”的會員號、姓名和商品價格，SQL代碼如下所示：USE eshopSELECT members.m_account, m_name,p_priceFROM membersJOIN ordersON members.m_account = orders.m_accountJOIN productsON orders.p_no = products.p_noAND p_name = 愛國者mp3查詢比“張自梁”月薪高的而和他不是同一地址的會

7、員姓名和年齡，SQL代碼如下所示：USE eshopSELECT A.m_name,YEAR(GETDATE()-YEAR(A.m_birth)FROM members AJOIN members BON A.m_account B.m_accountAND B.m_name = 張自梁AND A.m_salary B.m_salaryAND A.m_address B.m_address使用exists查詢購買了“0910810004”商品的會員號和姓名，SQL代碼如下所示：USE eshopSELECT m_account, m_nameFROM membersWHERE EXISTS (

8、 SELECT * FROM ordersWHERE members.m_account = orders.m_accountAND p_no = 0910810004)使用in查詢與“劉法治”購買至少同一種商品的會員號和商品號，SQL代碼如下所示：USE eshopSELECT DISTINCT A.m_account, A.p_noFROM orders AWHERE p_no IN ( SELECT p_no FROM orders BWHERE A.m_account B.m_accountAND B.m_account IN (SELECT m_accountFROM members

9、WHERE B.m_account = members.m_accountAND m_name=劉法治)使用簡單查詢家庭地址為“湖南株洲”的會員以及年齡在30歲以上的會員詳細信息，SQL代碼如下所示：USE eshopSELECT *FROM membersWHERE m_address = 湖南株洲SELECT *FROM membersWHERE (YEAR(GETDATE()-YEAR(m_birth) 30再按F5或點擊工具欄上的運行按鈕“”，查看運行結(jié)果，如圖7-1所示。使用集合查詢家庭地址為“湖南株洲”的會員以及年齡在30歲以上的會員詳細信息，并與步驟6進行比較，SQL代碼如下所示

10、：USE eshopSELECT *FROM membersWHERE m_address = 湖南株洲UNIONSELECT *FROM membersWHERE (YEAR(GETDATE()-YEAR(m_birth) 30再按F5或點擊工具欄上的運行按鈕“”，查看運行結(jié)果，如圖7-2所示。將members表和orders表之間的左向外聯(lián)接包括所有會員的信息，包括沒有購買商品的會員，SQL代碼如下所示：USE eshopSELECT members.*, orders.*FROM membersLEFT OUTER JOIN ordersON members.m_account = or

11、ders.m_accountSQL Server 2000的視圖操作在members表中創(chuàng)建地址為“湖南株洲”的會員的視圖V_addr，SQL代碼如下所示：CREATE VIEW V_addrASSELECT *FROM membersWHERE m_address = 湖南株洲在orders表中創(chuàng)建購買了商品號為“0910810004”商品的視圖V_buy，SQL代碼如下所示：CREATE VIEW V_buyASSELECT *FROM ordersWHERE p_no = 0910810004在members和orders表上創(chuàng)建“湖南株洲”的會員購買了商品號為“0910810004”商

12、品的視圖V_addr_buy，SQL代碼如下所示：CREATE VIEW V_addr_buyASSELECT members.*FROM membersJOIN ordersON members.m_account = orders.m_accountAND p_no = 0910810004AND m_address = 湖南株洲在視圖V_addr上查詢性別為“男”的會員信息，SQL代碼如下所示：USE eshopSELECT *FROM V_addrWHERE m_sex = 男在視圖V_addr中增加一條記錄（內(nèi)容如下），并查看members表中記錄的改變情況。記錄內(nèi)容如下：（T-SQ

13、L）fengxk，馮向克，男，1978-06-28，北京市，5000.0，fxk0628SQL代碼如下所示：USE eshopINSERT INTO V_addr VALUES(fengxk, 馮向克, 男, 1978-06-28, 北京市,5000.0, fxk0628)將視圖V_addr中會員號為“l(fā)iuzc518”的會員的密碼修改為“l(fā)iuzc0518”，并查看members中記錄的改變情況，SQL代碼如下所示：USE eshopUPDATE V_addrSET m_password = liuzc0518WHERE m_password = liuzc518在V_addr中刪除會員號為

14、“fengxk”的記錄，并查看members中記錄的改變情況，SQL代碼如下所示：USE eshopDELETE FROM V_addrWHERE m_account = fengxk刪除視圖V_addr_buy、V_buy和V_addr，SQL代碼如下所示：USE eshopDROP VIEW V_addr_buy, V_buy, V_addr學生表S，課程表C和學生選課表SC，它們的結(jié)構(gòu)如下，試用SQL完成下列操作。S（S#，SN，SEX，AGE，DEPT）C（C#，CN，TEACHER）SC（S#，C#，GRADE）學生表S，課程表C和學生選課表SC，它們的結(jié)構(gòu)如下，試用SQL完成下列操

15、作。S（S#，SN，SEX，AGE，DEPT）C（C#，CN，TEACHER）SC（S#，C#，GRADE）1. 建立數(shù)據(jù)庫：CREATE DATABASE studentON PRIMARY(NAME=student_dat,FILENAME=E:mydatastudent_dat.mdf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)LOG ON(NAME=student_log,FILENAME=E:mydatastudent_log.ldf,SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5MB)GO2. 創(chuàng)建表USE studentC

16、REATE TABLE S(S# VARCHAR(20),SN VARCHAR(20),SEX CHAR(2),AGE INT,DEPT VARCHAR(50)GOUSE studentCREATE TABLE C(C# VARCHAR(20),CN VARCHAR(20),TEACHER VARCHAR(50)GOUSE studentCREATE TABLE SC(S# VARCHAR(20),C# VARCHAR(20),GRADE INT)GOUSE studentCREATE TABLE SC_C(CNO VARCHAR(20),CNAME VARCHAR(20),AVG_GRADE

17、 INT)GO 用SQL找出選修了“程軍”老師教的所有課程的學生姓名。SELECT DISTINCT SN FROM SWHERE NOT EXISTS (SELECT * FROM C WHERE C.TEACHER=程軍 AND NOT EXISTS (SELECT * FROM SC WHERE S.S#=SC.S# AND SC.C#=C.C#)GO用SQL找出選修了“程軍”老師教的課程的學生姓名。SELECT DISTINCT SN FROM S,SC,CWHERE S.S#=SC.S# AND SC.C#=C.C# AND TEACHER=程軍GO 用SQL找出“程序設(shè)計”課程成績

18、在90分以上的學生姓名。SELECT DISTINCT SN FROM S,SC,CWHERE S.S#=SC.S# AND SC.C#=C.C# AND CN=程序設(shè)計 AND GRADE90GO 檢索所有比“王華”年齡大的學生姓名、年齡和性別。SELECT DISTINCT SN,AGE,SEX FROM SWHERE AGE(SELECT AGE FROM S WHERE SN=王華)GO 檢索選修課程“C2”的學生中成績最高的學生的學號。SELECT S# FROM SCWHERE C#=C2 AND GRADE=ALL(SELECT GRADE FROM SC WHERE C#=C2

19、)GO或SELECT S# FROM SCWHERE C#=C2 AND GRADE=(SELECT MAX(GRADE) FROM SC WHERE C#=C2)GO 檢索學生姓名及其所選課程的課程號和成績SELECT SN,C#,GRADE FROM S,SCWHERE S.S#=SC.S#GO 檢索選修四門以上課程的學生總成績（不統(tǒng)計不及格的課程），并要求按總成績的降序排列出來。SELECT SUM(GRADE) FROM SCWHERE GRADE=60GROUP BY S# HAVING COUNT(*)=4ORDER BY SUM(GRADE) DESCGO 檢索全是女同學選修的課

20、程的課程號。SELECT DISTINCT C# FROM SC,SWHERE SC.S#=S.S# AND SEX=女GO或SELECT DISTINCT C# FROM SCWHERE SC.S# IN (SELECT S.S# FROM S WHERE SEX=女)GO 檢索不學C6課程的男同學的學號和姓名（S#，SN）。SELECT S#,SN FROM SWHERE SEX=男 AND S# NOT IN(SELECT S# FROM SC WHERE C#=C6) GO 把SC表中每門課程的平均成績插入到另一個已存在的表SC_C(CNO,CNAME,AVG_GRADE)中。INSE

21、RT INTO SC_C(CNO,CNAME,AVG_GRADE)SELECT C.C#,CN,AVG(GRADE) FROM SC,CWHERE SC.C#=C.C#GROUP BY C.C#,CNGO 從SC表中把吳老師的女學生選課元組刪除。DELETE FROM SCWHERE S# IN (SELECT S# FROM S WHERE SEX=女) AND C# IN (SELECT C# FROM C WHERE TEACHER LIKE 吳%)GO 從SC表中把數(shù)學課程中低于數(shù)學平均成績的選課元組刪去。DELETE FROM SCWHERE GRADE(SELECT AVG(GRA

22、DE) FROM SC,C WHERE SC.C#=C.C# AND CN=數(shù)學 GROUP BY SC.C#)GO 把吳老師的女學生選課成績增加4%。UPDATE SCSET GRADE=GRADE+GRADE*0.04WHERE S# IN(SELECT S# FROM S WHERE SEX=女)AND C# IN(SELECT C# FROM C WHERE TEACHER LIKE 吳%)GO課本知識點總結(jié)P86 頁20題將下圖的教務(wù)管理數(shù)據(jù)庫的E-R圖，轉(zhuǎn)化為關(guān)系模型P87 頁23題P124 例57由學生、課程和選課3個表，定義一個計算機系的學生成績視圖，其屬性包括學號、姓名、課程名和成績CREATE VIEW 學生成績（學號，姓名，課程名，成績）AS SELECT 學生.學號，學生.姓名，課程.課程名，選課.成績 FORM 學生，課程，選課 WHERE學生.學號=選課. 學號AND課程.課程號=選課.課程號 AND學生.所在系=計算機系；P124 例58 將學生的學號、總成績、平均成績定義成一個視圖CREATE VIEW 學生成績統(tǒng)計（學號，總成績，平均成績）AS SELECT 學號，SUM（成績），AVG(成