数据库技术与应用复习资料(7页).doc

-使用SQL语句创建数据表&数据操作Insert、Update、Delete1. 利用Transact-SQL语句创建表booksales的代码。USE test01GOCREATE TABLE booksales(book_id nchar(6) NOT NULL,sellnum int NOT NULL,selldate datetime NOT NULL) ON PRIMARY2. 利用insert语句为表booksales添加数据：INSERT INTO booksales VALUES ('m00011',7,20/12/2008)3. 利用update语句为表booksales更新数据：UPDATE booksales SET sellnum =11WHERE book_id ='m00011'4. 利用delete语句删除表booksales的数据：DELETE FROM booksales WHERE book_id ='m00011'Transact-SQL语句基础1 数据完整性1. 将teaching数据库中score表的studentno列设置为引用表student的外键。ALTER TABLE ScoreADD CONSTRAINT FK_score_student FOREIGN KEY (studentno)REFERENCES student(studentno)2. 将teaching数据库中class表的classname创建UNIQUE约束。ALTER TABLE classADD CONSTRAINT UQ_class UNIQUE(classname)执行如下插入语句，查看提示信息INSERT INTO class VALUES(090602, 计算机0902, 计算机学院, 马文斐)3. 为teaching数据库中student表的birthday列创建CHECK约束，规定学生的年龄在17-25岁之间。ALTER TABLE studentADD CONSTRAINT CK_birthday CHECK(YEAR(GETDATE()-YEAR(birthday) BETWEEN 17 AND 25执行如下插入语句，查看提示信息INSERT INTO student(studentno, sname, sex, birthday, classno)VALUES (0922221328, 张源, 男, 1983-04-05, 090501)提示：表达式YEAR(GETDATE()-YEAR(birthday)数据检索11）SELECT FROM 2）条件WHERE3）生成新表INTO4）比较运算符（数值类型、字符串、日期时间，YEAR()）5）模糊查询LIKE6）逻辑运算符AND OR NOT7）检索一定范围的值BETWEEN AND 8）利用列表值检索 IN9）改变列名AS10）排序ORDER BY11）消除重复行 DISTINCT12）输出前n行TOP n TOP n PERCENT13）分组GROUP BY 筛选 HAVING14）聚合函数15）附加行汇总值COMPUTE16）多表连接INNER JOIN17）使用子查询1. 查询course表中所有的记录。SELECT * FROM course2. 查询student表中女生的人数。SELECT * FROM studentWHERE sex='女'3. 查询teacher表中每一位教授的教师号、姓名和专业名称。SELECT teacherno, tname, major FROM teacherWHERE prof='教授'4. 利用现有的表生成新表，新表中包括学号、课程号和总评成绩。其中：总评成绩=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 sname, Year(GetDate()-Year(birthday) AS ageFROM studentWHERE Year(GetDate()-Year(birthday)>20GO6. 查询计算机学院教师的专业名称。SELECT major AS 专业名称FROM teacherWHERE department='计算机学院'GO7. 查询Email使用126邮箱的所有学生的学号、姓名和电子邮箱地址。SELECT studentno,sname,EmailFROM studentWHERE Email Like '%'GO8. 查询score表中选修c05109或c05103课程，并且课程期末成绩在90100分之间的学号和期末成绩。SELECT * FROM scoreWHERE (courseno in('c05109','c05103') and (final between 90 and 100)9. 查询student表中所有学生的基本信息，查询结果按班级号classno升序排列，同一班级中的学生按入学成绩point降序排列。SELECT * FROM studentORDER BY classno ASC, point DESC10. 查询选修c05109课程，并且期末成绩在前5名的学生学号、课程号和期末成绩。（提示：TOP 5）SELECT TOP 5 studentno,courseno,finalFROM scoreWHERE courseno='c05109'ORDER BY final DESC数据检索2 Group by、Having、Compute by1. 按性别分组，求出student表中每组学生的平均年龄。SELECT sex,AVG(YEAR(GETDATE()-YEAR(birthday) AS ageFROM studentGROUP BY sex2. 查询各班学生的人数。SELECT classno, COUNT(*) AS 人数FROM studentGROUP BY classnoORDER BY classno3. 查询各课程期末成绩的最高分和最低分。SELECT courseno, MAX(final) AS 最高成绩, MIN(final) AS 最低成绩FROM scoreGROUP BY courseno4. 查询教两门及以上课程的教师编号、任课班级数。SELECT teacherno, COUNT(courseno) AS 班级数FROM teach_classGROUP BY teachernoHAVING COUNT(courseno) >=25. 查询课程编号以c05开头、被3名及以上学生选修且期末成绩的平均分高于75分的课程号、选修人数和期末成绩平均分，并按平均分降序排序。SELECT courseno,COUNT(studentno) AS 选修人数, 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语句的高级应用1 内连接、外连接1. 查询所有班级的期末成绩平均分，并按照平均分降序排列。表：score,student 2joinSELECT classno,AVG(final) AS 班级平均分FROM student INNER JOIN score ON student.studentno=score.studentnoWHERE final IS NOT NULLGROUP BY classnoORDER BY AVG(final) DESC2. 查询两门及以上课程的期末成绩超过80分的学生的姓名及其平均成绩。表：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语句的高级应用2 使用子查询3. 查询入学考试成绩最高的学生的学号、姓名和入学成绩。=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='c05127')视图与索引使用SQL语言1. 创建一个视图v_teacher，查询所有“计算机学院”的教师信息, 添加WITH CHECK OPTION选项。CREATE VIEW v_teacherASSELECT * FROM teacherWHERE department='计算机学院'WITH CHECK OPTIONGOSELECT * FROM v_teacher2. 创建一个视图v_avgstu，查询每个学生的学号、姓名及平均分，并且按照平均分降序排列。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中分别插入数据(05039, 张馨月,计算机应用,讲师,计算机学院)和(06018, 李诚,机械制造,副教授,机械学院)，并查看插入数据情况。INSERT INTO v_teacher VALUES ('05039', '张馨月','计算机应用','讲师','计算机学院')GOSELECT * FROM teacher试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。语句已终止。4. 通过视图v_teacher将基表teacher中教师编号为05039的教师职称修改为”副教授”。UPDATE v_teacherSET prof='副教授'WHERE teacherno='05039'GOSELECT * FROM teacher-第 7 页-