数据库技术与应用复习资料

使用SQL语句创建数据表&数据操作Insert、Update、Delete 1.利用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 booksalesWHERE 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_birthdayCHECK(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’课程，并且课程期末成绩在90~100分之间的学号和期末成绩。

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 scoreON 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_classWHERE 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’, ‘李诚’,’机械制造’,’副教授’,’机械学院’)，并查看插入数据情况。