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

使用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ù)：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. 將teaching數(shù)據(jù)庫中class表的classname創(chuàng)建UNIQUE約束。ALTER TABLE classADD CONSTRAINT UQ_class UNIQUE(classname)執(zhí)行如下插入語句，查看提示信息INSERT INTO class VALUES(090602, 計(jì)算機(jī)0902, 計(jì)算機(jī)學(xué)院, 馬文斐)3. 為teaching數(shù)據(jù)庫中student表的birthday列創(chuàng)建CHECK約束，規(guī)定學(xué)生的年齡在17-25歲之間。ALTER TABLE studentADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE()-YEAR(birthday) BETWEEN 17 AND 25執(zhí)行如下插入語句，查看提示信息INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES (0922221328, 張?jiān)? 男, 1983-04-05, 090501)提示：表達(dá)式Y(jié)EAR(GETDATE()-YEAR(birthday)數(shù)據(jù)檢索11）SELECT FROM 2）條件WHERE3）生成新表INTO4）比較運(yùn)算符（數(shù)值類型、字符串、日期時(shí)間，YEAR()）5）模糊查詢LIKE6）邏輯運(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）附加行匯總值COMPUTE16）多表連接INNER JOIN17）使用子查詢1. 查詢course表中所有的記錄。SELECT * FROM course2. 查詢student表中女生的人數(shù)。SELECT * FROM studentWHERE sex=女3. 查詢teacher表中每一位教授的教師號(hào)、姓名和專業(yè)名稱。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 zongping5. 查詢student表中所有年齡大于20歲的男生的姓名和年齡。SELECT sname, Year(GetDate()-Year(birthday) AS ageFROM studentWHERE Year(GetDate()-Year(birthday)20GO6. 查詢計(jì)算機(jī)學(xué)院教師的專業(yè)名稱。SELECT major AS 專業(yè)名稱FROM teacherWHERE department=計(jì)算機(jī)學(xué)院GO7. 查詢Email使用126郵箱的所有學(xué)生的學(xué)號(hào)、姓名和電子郵箱地址。SELECT studentno,sname,EmailFROM studentWHERE Email Like %126.com%GO8. 查詢score表中選修c05109或c05103課程，并且課程期末成績(jī)?cè)?0100分之間的學(xué)號(hào)和期末成績(jī)。SELECT * FROM scoreWHERE (courseno in(c05109,c05103) and (final between 90 and 100)9. 查詢student表中所有學(xué)生的基本信息，查詢結(jié)果按班級(jí)號(hào)classno升序排列，同一班級(jí)中的學(xué)生按入學(xué)成績(jī)point降序排列。SELECT * FROM studentORDER BY classno ASC, point DESC10. 查詢選修c05109課程，并且期末成績(jī)?cè)谇?名的學(xué)生學(xué)號(hào)、課程號(hào)和期末成績(jī)。（提示：TOP 5）SELECT TOP 5 studentno,courseno,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. 查詢各課程期末成績(jī)的最高分和最低分。SELECT courseno, MAX(final) AS 最高成績(jī), MIN(final) AS 最低成績(jī)FROM scoreGROUP BY courseno4. 查詢教兩門及以上課程的教師編號(hào)、任課班級(jí)數(shù)。SELECT teacherno, COUNT(courseno) AS 班級(jí)數(shù)FROM teach_classGROUP BY teachernoHAVING COUNT(courseno) =25. 查詢課程編號(hào)以c05開頭、被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語句的高級(jí)應(yīng)用1 內(nèi)連接、外連接1. 查詢所有班級(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. 查詢兩門及以上課程的期末成績(jī)超過80分的學(xué)生的姓名及其平均成績(jī)。表：student,scoreSELECT student.studentno,sname,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語句的高級(jí)應(yīng)用2 使用子查詢3. 查詢?nèi)雽W(xué)考試成績(jī)最高的學(xué)生的學(xué)號(hào)、姓名和入學(xué)成績(jī)。=SELECT studentno,sname,pointFROM studentWHERE point = (SELECT MAX(point) FROM student)4. 查詢所有教授c05127號(hào)課程的教師信息。InSELECT * FROM teacherWHERE teacherno IN (SELECT teacherno FROM teach_class WHERE courseno=c05127)視圖與索引使用SQL語言1. 創(chuàng)建一個(gè)視圖v_teacher，查詢所有“計(jì)算機(jī)學(xué)院”的教師信息, 添加WITH CHECK OPTION選項(xiàng)。CREATE VIEW v_teacherASSELECT * FROM teacherWHERE department=計(jì)算機(jī)學(xué)院WITH CHECK OPTIONGOSELECT * FROM v_teacher2. 創(chuàng)建一個(gè)視圖v_avgstu，查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名及平均分，并且按照平均分降序排列。CREATE VIEW v_avgstuASSELECT TOP 100 student.studentno,sname,avg(final) 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, 張馨月,計(jì)算機(jī)應(yīng)用,講師,計(jì)算機(jī)學(xué)院)和(06018, 李誠,機(jī)械制造,副教授,機(jī)械學(xué)院)，并查看插入數(shù)據(jù)情況。INSERT INTO v_teacher VALUES (05039, 張馨月,計(jì)算機(jī)應(yīng)用,講師,計(jì)算機(jī)學(xué)院)GOSE