数据库操作语句(共10页).doc
精选优质文档-倾情为你奉上 例1：查询全体学生的学号与姓名。SELECT Sno, Sname FROM Student例2.查询全体学生的姓名、学号、所在系 SELECT Sname, Sno, Sdept FROM Student 例3查询全体学生的记录 SELECT Sno,Sname,Ssex, Sage, Sdept FROM Student 例5查询全体学生的姓名和出生年份所在系，并在出生年份列前加入一个列，此列的每行数据均为“出生年份”常量值。SELECT Sname,'出生年份:', 2010-Sage FROM Student 例6查询选修了课程的学生的学号 SELECT Sno FROM SC 例7查询计算机系全体学生的姓名。SELECT Sname FROM Student WHERE Sdept = '计算机系 例8查询年龄在20岁以下的学生的姓名及年龄。SELECT Sname, Sage FROM Student WHERE Sage < 20或SELECT Sname, Sage FROM Student WHERE NOT Sage >= 20 例9查询考试成绩有不及格的学生的学号 SELECT DISTINCT Sno FROM SC WHERE Grade < 60 例10查询年龄在2023岁之间的学生的姓名、所在系和年龄。SELECT Sname, Sdept, Sage FROM Student WHERE Sage BETWEEN 20 AND 23SELECT Sname, Sdept, Sage FROM Student WHERE Sage >= 20 AND Sage<= 23 例11查询年龄不在2023之间的学生姓名、所在系和年龄。SELECT Sname, Sdept, Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23SELECT Sname, Sdept, Sage FROM Student WHERE Sage < 20 or Sage> 23 例12查询2009年6月份出版的全部图书的详细信息。SELECT * FROM 图书表 WHERE 出版日期 BETWEEN '2009/6/1' AND '2009/6/30' 例13查询信息系、数学系和计算机系学生的姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sdept IN ('信息系', '数学系','计算机系') SELECT Sname, Ssex FROM Student WHERE Sdept = '信息系 or Sdept = '数学系 or Sdept = '计算机系' 例14查询信息系和计算机系之外的其他系的学生姓名、性别和所在系。SELECT Sname, Ssex FROM Student WHERE Sdept NOT IN ('信息系', '计算机系') SELECT Sname, Ssex FROM Student WHERE Sdept != '信息系 or Sdept != '数学系 例15.查询姓张的学生的详细信息。SELECT * FROM Student WHERE Sname LIKE '张%' 例16查询学生表中姓张、李和刘的学生的情况。SELECT * FROM Student WHERE Sname LIKE '张李刘%' 例17查询名字中第2个字为小或大的学生的姓名和学号。 SELECT Sname, Sno FROM Student WHERE Sname LIKE '_小大%' 例18查询所有不姓“王”也不姓“张”的学生姓名SELECT Sname FROM Student WHERE Sname NOT LIKE '王张%' 例19查询姓“王”且名字是2个字的学生姓名。SELECT Sname FROM Student WHERE Sname LIKE '王_' 例20查询姓王且名字是3个字的学生姓名SELECT Sname FROM Student WHERE Sname LIKE '王_' 例21. 在Student表中查询学号的最后一位不是2、3、5的学生信息。 SELECT * FROM Student WHERE Sno LIKE '%235' 例22查询没有考试成绩的学生的学号和相应的课程号。SELECT Sno, Cno FROM SC WHERE Grade IS NULL 例23查询所有有考试成绩的学生的学号和课程号。SELECT Sno, Cno FROM SC WHERE Grade IS NOT NULL 例24查询计算机系年龄在20岁以下的学生姓名。SELECT Sname FROM Student WHERE Sdept = '计算机系' AND Sage < 20 例25查询计算机系和信息系年龄大于等于20岁的学生姓名、所在系和年龄。 SELECT Sname,Sdept, Sage FROM Student WHERE (Sdept = '计算机系' OR Sdept = '信息系') AND Sage >= 20 例26将学生按年龄的升序排序。SELECT * FROM Student ORDER BY Sage 例27查询选修了c02号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECT Sno, Grade FROM SCWHERE Cno='c02' ORDER BY Grade DESC 例28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT * FROM StudentORDER BY Sdept, Sage DESC 例29统计学生总人数。SELECT COUNT(*) FROM Student 数（学生表里的）记录，有多少记录就有多少个学生。 例30统计选修了课程的学生的人数。SELECT COUNT (DISTINCT Sno) FROM SC由于一个学生可以选多门课程，所以加DISTINCT 例31统计号学生的考试总成绩之和。SELECT COUNT(*) AS 选课门数，SUM(Grade) AS 总成绩 FROM SC WHERE Sno = COUNT(*) 数个数，数选课表每个同学的选课记录就是选课门数 例32计算C01号课程学生的考试平均成绩。SELECT AVG(Grade) AS 平均成绩 FROM SC WHERE Cno='C01' 例33查询C01号课程的考试最高分和最低分。SELECT MAX(Grade) AS 最高分, MIN(Grade) AS 最低分 FROM SC WHERE Cno='C01' 例34查询“”学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分。SELECT COUNT(*) AS 选课门数, COUNT(Grade) AS 考试门数, MAX(Grade) AS 最高分, MIN(Grade) AS 最低分, AVG(Grade) AS 平均分 FROM SC WHERE Sno = 'COUNT(*) 数个数，数选课表该同学的选课记录就是选课门数COUNT(Grade)数选课表该同学有几个考试成绩就是考试门数 例35统计每门课程的选课人数，列出课程号和人数。 SELECT Cno as 课程号, COUNT(Sno) as 选课人数 FROM SC GROUP BY Cno 例36查询每个学生的选课门数和平均成绩。 SELECT Sno as 学号, COUNT(*) as 选课门数, AVG(Grade) as 平均成绩 FROM SC GROUP BY Sno 例37统计每个系的学生人数和平均年龄。SELECT Sdept, COUNT(*) AS 学生人数, AVG(Sage) AS 平均年龄 FROM Student GROUP BY Sdept 例38. 带WHERE子句的分组。统计每个系的女生人数。SELECT Sdept, Count(*) 女生人数 FROM Student WHERE Ssex = '女' GROUP BY Sdept 例39. 按多列分组。统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。SELECT Sdept, Ssex, Count(*) 人数, Max(Sage) 最大年龄 FROM Student GROUP BY Sdept, Ssex ORDER BY Sdept先按系别分组，每个系再按性别分组 例40. 查询选修了3门以上课程的学生的学号和选课门数。SELECT Sno, Count(*) 选课门数 FROM SC GROUP BY Sno HAVING COUNT(*) > 3 例41查询修课门数等于或大于4的学生的平均成绩和选课门数。SELECT Sno, AVG(Grade) 平均成绩, COUNT(*) 修课门数 FROM SC GROUP BY Sno HAVING COUNT(*) >= 4 例42. 查询计算机系和信息管理系的学生人数。 方法1：SELECT Sdept, COUNT(*) FROM Student GROUP BY Sdept HAVING Sdept IN ( '计算机系', '信息管理系') 方法2：SELECT sdept, COUNT (*) FROM Student WHERE Sdept IN ('计算机系', '信息管理系') GROUP BY Sdept 例43. 查询每个系年龄小于等于20岁的学生人数。SELECT Sdept, COUNT (*) FROM Student WHERE Sage <= 20 GROUP BY Sdept 例44查询每个学生及其选课的详细信息。SELECT * FROM Student INNER JOIN SC把2个表联起来ON Student.Sno = SC.Sno-SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON Student.Sno = SC.Sno 例46.查询计算机系学生的修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECT Sname, Cno, Grade FROM Student JOIN SC ON Student.Sno = SC.Sno WHERE Sdept = '计算机系' 例47. 查询信息系修了“计算机文化学”课程的学生信息，要求列出学生姓名、课程名和成绩。SELECT Sname, Cname, Grade FROM Student s JOIN SC ON s.Sno = SC. Sno JOIN Course c ON c.Cno = SC.Cno WHERE Sdept = '信息系' AND Cname = '计算机文化学' 例48查询所有修了VB课程的学生的修课情况，要求列出学生姓名和所在系。 SELECT Sname, Sdept FROM Student S JOIN SC ON S.Sno = SC. Sno JOIN Course C ON C.Cno = SC.cno WHERE Cname = 'VB' 例49有分组的多表连接查询。统计每个系的学生考试平均成绩。SELECT Sdept, AVG(grade) as AverageGrade FROM student S JOIN SC ON S.Sno = SC.Sno GROUP BY Sdept 例50有分组和行选择条件的多表连接查询。统计计算机系每门课程的选课人数、平均成绩、最高成绩和最低成绩。 SELECT Cno, COUNT(*) AS Total, AVG(Grade) as AvgGrade, MAX(Grade) as MaxGrade, MIN(Grade) as MinGrade FROM Student S JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' GROUP BY Cno 例51. 查询与刘晨在同一个系学习的学生的姓名和所在的系。SELECT S2.Sname, S2.Sdept FROM Student S1 JOIN Student S2 ON S1.Sdept = S2.Sdept WHERE S1.Sname = '刘晨' AND S2.Sname != '刘晨' 例52查询与“操作系统”学分相同的课程的课程名和学分。SELECT C1.Cname, C1.Credit FROM Course C1 JOIN Course C2 ON C1.Credit = C2. CreditWHERE C2.Cname = '操作系统' 例53. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。SELECT Student.Sno, Sname, Cno, Grade FROM Student LEFT OUTER JOIN SCON Student.Sno = SC.Sno 例54. 查询哪些课程没有人选，列出其课程名。SELECT Cname FROM Course C LEFT JOIN SC ON C.Cno = SC.Cno WHERE SC.Cno IS NULL 例55. 查询计算机系没有选课的学生，列 出学生姓名和性别。 SELECT Sname,Sdept,Cno,grade FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' AND SC.Sno IS NULL 例56. 统计计算机系每个学生的选课门数，包括没有选课的学生，结果按选课门数递减排序。 SELECT S.Sno AS 学号, COUNT(SC.Cno) AS 选课门数 FROM Student S LEFT JOIN SC ON S.Sno = SC.Sno WHERE Sdept = '计算机系' GROUP BY S.Sno ORDER BY COUNT(SC.Cno) DESC 例57 查询年龄最大的三个学生的姓名、年龄及所在的系。SELECT TOP 3 Sname, Sage, Sdept FROM Student ORDER BY Sage DESC 例58 查询VB考试成绩最高的前三名的学生的姓名、所在系和VB考试成绩。SELECT TOP 3 WITH TIES Sname, Sdept, Grade FROM Student S JOIN SC on S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB' ORDER BY Grade DESC 例59 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数。 SELECT TOP 2 WITH TIES Cno, COUNT(*) 选课人数 FROM SC GROUP BY Cno ORDER BY COUNT(Cno) ASC 例60. 查询与刘晨在同一个系的学生。SELECT Sno, Sname, Sdept FROM StudentWHERE Sdept IN ( SELECT Sdept FROM Student WHERE Sname = '刘晨' ) AND Sname != '刘晨' 例61. 查询成绩为大于90分的学生的学号、姓名。SELECT Sno, Sname FROM StudentWHERE Sno IN( SELECT Sno FROM SCWHERE Grade > 90 ) 例62查询计算机系选了“c02”课程的学生，列出姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sno IN (SELECT Sno FROM SC WHERE Cno = 'c02') AND Sdept = '计算机系' 例63. 查询选修了“VB”课程的学生的学号和姓名。 （1）在Course表中，找出“VB”课程名对应的课程号； （2）根据得到的“VB”课程号，在SC表中找出选了该课程号的学生的学号； （3）根据得到的学号，在Student表中找出对应的学生的学号和姓名。SELECT Sno, Sname FROM Student WHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname = 'VB') ) 例64. 在选修了VB的这些学生中，统计他们的选课门数和平均成绩。SELECT Sno 学号, COUNT(*) 选课门数, AVG(Grade) 平均成绩 FROM SC WHERE Sno IN ( -选VB的学生 SELECT Sno FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB') GROUP BY Sno 例65. 查询选了“c04”号课程且成绩高于此课程的平均成绩的学生的学号和成绩。 首先计算“c04”号课程的平均成绩： SELECT AVG(Grade) from SC WHERE Cno = c04 -平均成绩79 然后，查找“c04”号课程所有的考试成绩中，高于79的学生： SELECT Sno , Grade FROM SC WHERE Cno = c04 AND Grade > 79 将两个查询语句合起来即为满足我们要求的查询语句： SELECT Sno , Grade FROM SC WHERE Cno = c04 AND Grade > ( SELECT AVG(Grade) FROM SC WHERE Cno = c04) 例66. 查询计算机系年龄最大的学生的姓名和年龄。SELECT Sname, Sage FROM Student WHERE Sdept = '计算机系' AND Sage = ( SELECT MAX(Sage) FROM Student WHERE Sdept = '计算机系') 例67. 查询考试平均成绩高于全体学生的总平均成绩的学生的学号和平均成绩。SELECT Sno, AVG(Grade) 平均成绩 FROM SC GROUP BY Sno HAVING AVG(Grade) > ( SELECT AVG(Grade) FROM SC ) 例68. 查询VB考试成绩高于VB平均成绩的学生的姓名、所在系和VB成绩。(子查询是多表查询)SELECT Sname, Sdept, Grade FROM Student S JOIN SC ON S.Sno = SC.Sno JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB' AND Grade > ( SELECT AVG(Grade) FROM SC JOIN Course C ON C.Cno = SC.Cno WHERE Cname = 'VB') 例69.查询选修了c01课程的学生姓名。 SELECT Sname FROM Student WHERE EXISTS(SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01') 例71.查询没有选修c01号课程的学生姓名和所在系。 SELECT Sname, Sdept FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno = 'c01') 或：SELECT Sname, Sdept FROM Student WHERE Sno NOT IN ( SELECT Sno FROM SC WHERE Cno = 'c01' ) 例71 查询计算机系没有选修“VB”课程的学生的姓名和性别。SELECT Sname, Ssex FROM Student WHERE Sno NOT IN ( -查询选了VB的学生 SELECT Sno FROM SC JOIN Course ON SC.Cno = Course.Cno WHERE Cname = 'VB') AND Sdept = '计算机系' 例72将新生记录（，陈冬，男，18岁，信息系）插入到Student表中。 INSERT INTO Student VALUES ('', '陈冬', '男', 18, '信息系') 例73在SC表中插入一新记录，学号为“”，选的课程号为“c01”，成绩暂缺。INSERT INTO SC(Sno, Cno) VALUES('', 'c01) 实际插入的值为：('', 'c01'，NULL) 字符要用单引号 ' ' 数字不用 例74. 将所有学生的年龄加1。UPDATE Student SET Sage = Sage + 1 例75. 将学生的年龄改为21岁。UPDATE Student SET Sage = 21WHERE Sno = ' 例76：将计算机系全体学生的成绩加5分。 （1）用子查询实现UPDATE SC SET Grade = Grade + 5 WHERE Sno IN(SELECT Sno FROM Student WHERE Sdept = '计算机系' ) （2）用多表连接实现UPDATE SC SET Grade = Grade + 5 FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = '计算机系' 例77. 将学分最低的课程的学分加2分。UPDATE Course SET Credit = Credit + 2 WHERE Credit = ( SELECT MIN(Credit) FROM Course ) 例78. 数学系学生的VB考试成绩增加10分。 用子查询实现。UPDATE SC SET Grade = Grade + 10 WHERE Cno IN ( SELECT Cno FROM Course WHERE Cname = 'VB') AND Sno IN ( SELECT Sno FROM Student WHERE Sdept ='数学系') 用多表连接实现。UPDATE SC SET Grade = Grade + 10 FROM SC JOIN Course C ON C.Cno = SC.Cno JOIN Student S ON S.Sno = SC.Sno WHERE Cname = 'VB' AND Sdept = '数学系' 例79. 删除所有学生的选课记录。DELETE FROM SC 例80删除所有不及格学生的修课记录。 DELETE FROM SC WHERE Grade < 60 例81删除计算机系不及格学生的修课记录。 （1）用子查询实现 DELETE FROM SC WHERE Grade < 60 AND Sno IN ( SELECT Sno FROM Student WHERE Sdept = '计算机系' ) （2）用多表连接实现DELETE FROM SC FROM SC JOIN Student ON SC.Sno = Student.Sno WHERE Sdept = '计算机系' AND Grade < 60 例81. 删除信息系考试成绩不及格学生的不及格课程的选课记录。 用多表连接形式实现。DELETE FROM SC FROM Student S JOIN SC ON S.Sno = SC.sno WHERE Sdept = '信息系' AND Grade < 60 用子查询形式实现。DELETE FROM SC WHERE Sno IN ( SELECT Sno FROM Student WHERE Sdept = '信息系') AND Grade < 60专心-专注-专业
