數(shù)據(jù)庫(kù)技術(shù)和應(yīng)用復(fù)習(xí)資料

1、 使用SQL語(yǔ)句創(chuàng)建數(shù)據(jù)表&數(shù)據(jù)操作Insert、Update、Delete1. 利用Transact-SQL語(yǔ)句創(chuàng)建表booksales的代碼。USE test01GOCREATE TABLE booksales(book_id nchar(6) NOT NULL,sellnum int NOT NULL,selldate datetime NOT NULL) ON PRIMARY2. 利用insert語(yǔ)句為表booksales添加數(shù)據(jù)：INSERT INTO booksales VALUES ('m00011',7,20/12/2008)3. 利用update語(yǔ)句為

2、表booksales更新數(shù)據(jù)：UPDATE booksales SET sellnum =11WHERE book_id ='m00011'4. 利用delete語(yǔ)句刪除表booksales的數(shù)據(jù)：DELETE FROM booksales WHERE book_id ='m00011'Transact-SQL語(yǔ)句基礎(chǔ)1 數(shù)據(jù)完整性1. 將teaching數(shù)據(jù)庫(kù)中score表的studentno列設(shè)置為引用表student的外鍵。ALTER TABLE ScoreADD CONSTRAINT FK_score_student FOREIGN KEY (stude

3、ntno)REFERENCES student(studentno)2. 將teaching數(shù)據(jù)庫(kù)中class表的classname創(chuàng)建UNIQUE約束。ALTER TABLE classADD CONSTRAINT UQ_class UNIQUE(classname)執(zhí)行如下插入語(yǔ)句，查看提示信息INSERT INTO class VALUES(090602, 計(jì)算機(jī)0902, 計(jì)算機(jī)學(xué)院, 馬文斐)3. 為teaching數(shù)據(jù)庫(kù)中student表的birthday列創(chuàng)建CHECK約束，規(guī)定學(xué)生的年齡在17-25歲之間。ALTER TABLE studentADD CONSTRAINT CK

4、_birthday CHECK(YEAR(GETDATE()-YEAR(birthday) BETWEEN 17 AND 25執(zhí)行如下插入語(yǔ)句，查看提示信息INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES (0922221328, 源, 男, 1983-04-05, 090501)提示：表達(dá)式Y(jié)EAR(GETDATE()-YEAR(birthday)數(shù)據(jù)檢索11）SELECT FROM 2）條件WHERE3）生成新表INTO4）比較運(yùn)算符（數(shù)值類(lèi)型、字符串、日期時(shí)間，YEAR()）5）模糊查詢(xún)LIKE6）邏輯

5、運(yùn)算符AND OR NOT7）檢索一定圍的值BETWEEN AND 8）利用列表值檢索 IN9）改變列名AS10）排序ORDER BY11）消除重復(fù)行 DISTINCT12）輸出前n行TOP n TOP n PERCENT13）分組GROUP BY 篩選 HAVING14）聚合函數(shù)15）附加行匯總值PUTE16）多表連接INNER JOIN17）使用子查詢(xún)1. 查詢(xún)course表中所有的記錄。SELECT * FROM course2. 查詢(xún)student表中女生的人數(shù)。SELECT * FROM studentWHERE sex='女'3. 查詢(xún)teacher表中每一位教授的

6、教師號(hào)、和專(zhuān)業(yè)名稱(chēng)。SELECT teacherno, tname, major FROM teacherWHERE prof='教授'4. 利用現(xiàn)有的表生成新表，新表中包括學(xué)號(hào)、課程號(hào)和總評(píng)成績(jī)。其中：總評(píng)成績(jī)=final*0.8+usually*0.2SELECT student.studentno, sname, courseno, final*0.8+usually*0.2 AS 總評(píng)成績(jī)INTO zongpingFROM student,scoreWHERE student.studentno = score.studentnoGOSELECT * FROM zong

7、ping5. 查詢(xún)student表中所有年齡大于20歲的男生的和年齡。SELECT sname, Year(GetDate()-Year(birthday) AS ageFROM studentWHERE Year(GetDate()-Year(birthday)>20GO6. 查詢(xún)計(jì)算機(jī)學(xué)院教師的專(zhuān)業(yè)名稱(chēng)。SELECT major AS 專(zhuān)業(yè)名稱(chēng)FROM teacherWHERE department='計(jì)算機(jī)學(xué)院'GO7. 查詢(xún)Email使用126的所有學(xué)生的學(xué)號(hào)、和電子地址。SELECT studentno,sname,EmailFROM studentWHERE

8、Email Like '%126.%'GO8. 查詢(xún)score表中選修c05109或c05103課程，并且課程期末成績(jī)?cè)?0100分之間的學(xué)號(hào)和期末成績(jī)。SELECT * FROM scoreWHERE (courseno in('c05109','c05103') and (final between 90 and 100)9. 查詢(xún)student表中所有學(xué)生的基本信息，查詢(xún)結(jié)果按班級(jí)號(hào)classno升序排列，同一班級(jí)中的學(xué)生按入學(xué)成績(jī)point降序排列。SELECT * FROM studentORDER BY classno ASC, po

9、int DESC10. 查詢(xún)選修c05109課程，并且期末成績(jī)?cè)谇?名的學(xué)生學(xué)號(hào)、課程號(hào)和期末成績(jī)。（提示：TOP 5）SELECT TOP 5 studentno,courseno,finalFROM scoreWHERE courseno='c05109'ORDER BY final DESC數(shù)據(jù)檢索2 Group by、Having、pute by1. 按性別分組，求出student表中每組學(xué)生的平均年齡。SELECT sex,AVG(YEAR(GETDATE()-YEAR(birthday) AS ageFROM studentGROUP BY sex2. 查詢(xún)各班學(xué)生

10、的人數(shù)。SELECT classno, COUNT(*) AS 人數(shù)FROM studentGROUP BY classnoORDER BY classno3. 查詢(xún)各課程期末成績(jī)的最高分和最低分。SELECT courseno, MAX(final) AS 最高成績(jī), MIN(final) AS 最低成績(jī)FROM scoreGROUP BY courseno4. 查詢(xún)教兩門(mén)及以上課程的教師編號(hào)、任課班級(jí)數(shù)。SELECT teacherno, COUNT(courseno) AS 班級(jí)數(shù)FROM teach_classGROUP BY teachernoHAVING COUNT(coursen

11、o) >=25. 查詢(xún)課程編號(hào)以c05開(kāi)頭、被3名及以上學(xué)生選修且期末成績(jī)的平均分高于75分的課程號(hào)、選修人數(shù)和期末成績(jī)平均分，并按平均分降序排序。SELECT courseno,COUNT(studentno) AS 選修人數(shù), AVG(final) AS 平均分FROM scoreWHERE courseno LIKE 'c05%' AND final IS NOT NULLGROUP BY coursenoHAVING COUNT(studentno)>=3 AND AVG(final)>=75ORDER BY AVG(final) DESCSQL語(yǔ)句的

12、高級(jí)應(yīng)用1 連接、外連接1. 查詢(xún)所有班級(jí)的期末成績(jī)平均分，并按照平均分降序排列。表：score,student 2joinSELECT classno,AVG(final) AS 班級(jí)平均分FROM student INNER JOIN score ON student.studentno=score.studentnoWHERE final IS NOT NULLGROUP BY classnoORDER BY AVG(final) DESC2. 查詢(xún)兩門(mén)及以上課程的期末成績(jī)超過(guò)80分的學(xué)生的及其平均成績(jī)。表：student,scoreSELECT student.studentno,sn

13、ame,AVG(final) AS 平均分FROM student JOIN score ON student.studentno=score.studentnoWHERE final IS NOT NULL and final >=80GROUP BY student.studentno,snameHAVING COUNT(*)>=2SQL語(yǔ)句的高級(jí)應(yīng)用2 使用子查詢(xún)3. 查詢(xún)?nèi)雽W(xué)考試成績(jī)最高的學(xué)生的學(xué)號(hào)、和入學(xué)成績(jī)。=SELECT studentno,sname,pointFROM studentWHERE point = (SELECT MAX(point) FROM stu

14、dent)4. 查詢(xún)所有教授c05127號(hào)課程的教師信息。InSELECT * FROM teacherWHERE teacherno IN (SELECT teacherno FROM teach_class WHERE courseno='c05127')視圖與索引使用SQL語(yǔ)言1. 創(chuàng)建一個(gè)視圖v_teacher，查詢(xún)所有“計(jì)算機(jī)學(xué)院”的教師信息,添加WITH CHECK OPTION選項(xiàng)。CREATEVIEW v_teacherASSELECT*FROM teacherWHERE department='計(jì)算機(jī)學(xué)院'WITHCHECKOPTIONGOSE

15、LECT*FROM v_teacher2. 創(chuàng)建一個(gè)視圖v_avgstu，查詢(xún)每個(gè)學(xué)生的學(xué)號(hào)、及平均分，并且按照平均分降序排列。CREATEVIEW v_avgstuASSELECTTOP 100 student.studentno,sname,avg(final)AS'平均分'FROM student JOIN scoreON student.studentno=score.studentnoGROUPBY student.studentno,snameORDERBYavg(final)GOSELECT*FROM v_avgstu3. 通過(guò)視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(05039, 馨月,計(jì)算機(jī)應(yīng)用,講師,計(jì)算機(jī)學(xué)院)和(06018, 誠(chéng),機(jī)械制造,副教授,機(jī)械學(xué)院)，并查看插入數(shù)據(jù)情況。INSERTINTO v_teacher VALUES('05039','馨月','計(jì)算機(jī)應(yīng)用','講師','計(jì)算機(jī)學(xué)院')GOSELECT*FROM te