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

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

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

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

4、(birthday) BETWEEN 17 AND 25執(zhí)行如下插入語句，查看提示信息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）比較運算符（數(shù)值類型、字符串、日期時間，YEAR()）5）模糊查詢LIKE6）邏輯運算符AND OR NOT7）檢索一定范圍的值BETWEEN AND

5、 8）利用列表值檢索 IN9）改變列名AS10）排序ORDER BY11）消除重復(fù)行 DISTINCT12）輸出前n行TOP n TOP n PERCENT13）分組GROUP BY 篩選 HAVING14）聚合函數(shù)15）附加行匯總值COMPUTE16）多表連接INNER JOIN17）使用子查詢1. 查詢course表中所有的記錄。SELECT * FROM course2. 查詢student表中女生的人數(shù)。SELECT * FROM studentWHERE sex=女3. 查詢teacher表中每一位教授的教師號、姓名和專業(yè)名稱。SELECT teacherno, tname, maj

6、or FROM teacherWHERE prof=教授4. 利用現(xiàn)有的表生成新表，新表中包括學(xué)號、課程號和總評成績。其中：總評成績=final*0.8+usually*0.2SELECT student.studentno, sname, courseno, final*0.8+usually*0.2 AS 總評成績INTO zongpingFROM student,scoreWHERE student.studentno = score.studentnoGOSELECT * FROM zongping5. 查詢student表中所有年齡大于20歲的男生的姓名和年齡。SELECT snam

7、e, Year(GetDate()-Year(birthday) AS ageFROM studentWHERE Year(GetDate()-Year(birthday)20GO6. 查詢計算機學(xué)院教師的專業(yè)名稱。SELECT major AS 專業(yè)名稱FROM teacherWHERE department=計算機學(xué)院GO7. 查詢Email使用126郵箱的所有學(xué)生的學(xué)號、姓名和電子郵箱地址。SELECT studentno,sname,EmailFROM studentWHERE Email Like %GO8. 查詢score表中選修c05109或c05103課程，并且課程期末成績在9

8、0100分之間的學(xué)號和期末成績。SELECT * FROM scoreWHERE (courseno in(c05109,c05103) and (final between 90 and 100)9. 查詢student表中所有學(xué)生的基本信息，查詢結(jié)果按班級號classno升序排列，同一班級中的學(xué)生按入學(xué)成績point降序排列。SELECT * FROM studentORDER BY classno ASC, point DESC10. 查詢選修c05109課程，并且期末成績在前5名的學(xué)生學(xué)號、課程號和期末成績。（提示：TOP 5）SELECT TOP 5 studentno,course

9、no,finalFROM scoreWHERE courseno=c05109ORDER BY final DESC數(shù)據(jù)檢索2 Group by、Having、Compute by1. 按性別分組，求出student表中每組學(xué)生的平均年齡。SELECT sex,AVG(YEAR(GETDATE()-YEAR(birthday) AS ageFROM studentGROUP BY sex2. 查詢各班學(xué)生的人數(shù)。SELECT classno, COUNT(*) AS 人數(shù)FROM studentGROUP BY classnoORDER BY classno3. 查詢各課程期末成績的最高分和最

10、低分。SELECT courseno, MAX(final) AS 最高成績, MIN(final) AS 最低成績FROM scoreGROUP BY courseno4. 查詢教兩門及以上課程的教師編號、任課班級數(shù)。SELECT teacherno, COUNT(courseno) AS 班級數(shù)FROM teach_classGROUP BY teachernoHAVING COUNT(courseno) =25. 查詢課程編號以c05開頭、被3名及以上學(xué)生選修且期末成績的平均分高于75分的課程號、選修人數(shù)和期末成績平均分，并按平均分降序排序。SELECT courseno,COUNT(s

11、tudentno) 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īng)用1 內(nèi)連接、外連接1. 查詢所有班級的期末成績平均分，并按照平均分降序排列。表：score,student 2joinSELECT classno,AVG(final) AS 班級平均分FROM student INNER JO

12、IN score ON student.studentno=score.studentnoWHERE final IS NOT NULLGROUP BY classnoORDER BY AVG(final) DESC2. 查詢兩門及以上課程的期末成績超過80分的學(xué)生的姓名及其平均成績。表：student,scoreSELECT student.studentno,sname,AVG(final) AS 平均分FROM student JOIN score ON student.studentno=score.studentnoWHERE final IS NOT NULL and final

13、=80GROUP BY student.studentno,snameHAVING COUNT(*)=2SQL語句的高級應(yīng)用2 使用子查詢3. 查詢?nèi)雽W(xué)考試成績最高的學(xué)生的學(xué)號、姓名和入學(xué)成績。=SELECT studentno,sname,pointFROM studentWHERE point = (SELECT MAX(point) FROM student)4. 查詢所有教授c05127號課程的教師信息。InSELECT * FROM teacherWHERE teacherno IN (SELECT teacherno FROM teach_class WHERE courseno=

14、c05127)視圖與索引使用SQL語言1. 創(chuàng)建一個視圖v_teacher，查詢所有“計算機學(xué)院”的教師信息, 添加WITH CHECK OPTION選項。CREATE VIEW v_teacherASSELECT * FROM teacherWHERE department=計算機學(xué)院WITH CHECK OPTIONGOSELECT * FROM v_teacher2. 創(chuàng)建一個視圖v_avgstu，查詢每個學(xué)生的學(xué)號、姓名及平均分，并且按照平均分降序排列。CREATE VIEW v_avgstuASSELECT TOP 100 student.studentno,sname,avg(fi

15、nal) AS 平均分FROM student JOIN scoreON student.studentno=score.studentnoGROUP BY student.studentno,snameORDER BY avg(final)GOSELECT * FROM v_avgstu3. 通過視圖v_teacher向基表teacher中分別插入數(shù)據(jù)(05039, 張馨月,計算機應(yīng)用,講師,計算機學(xué)院)和(06018, 李誠,機械制造,副教授,機械學(xué)院)，并查看插入數(shù)據(jù)情況。INSERT INTO v_teacher VALUES (05039, 張馨月,計算機應(yīng)用,講師,計算機學(xué)院)GOSEL