数据库常用操作语句（学生表、选课表、成绩表）

例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．查询年龄在20～23岁之间的学生的姓名、所在系和年龄。

SELECT Sname, Sdept, Sage  FROM Student

WHERE Sage BETWEEN 20 AND 23

 

SELECT Sname, Sdept, Sage  FROM Student

WHERE Sage >= 20 AND Sage<= 23

 

例11．查询年龄不在20～23之间的学生姓名、所在系和年龄。

SELECT Sname, Sdept, Sage  FROM Student

WHERE Sage NOT BETWEEN 20 AND 23

 

SELECT 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 SC

WHERE Cno='c02' ORDER BY Grade DESC

例28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。

SELECT * FROM Student

ORDER BY Sdept, Sage DESC  

例29．统计学生总人数。

SELECT COUNT(*) FROM Student

数（学生表里的）记录，有多少记录就有多少个学生。

例30．统计选修了课程的学生的人数。

SELECT COUNT (DISTINCT Sno)

      FROM SC

由于一个学生可以选多门课程，所以加DISTINCT

例31．统计9512101号学生的考试总成绩之和。

SELECT COUNT(*)  AS 选课门数，SUM(Grade) AS  总成绩 FROM SC

 WHERE Sno = ‘9512101’

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．查询“9512101”学生的选课门数、已考试课程门数以及考试最高分、最低分和平均分。

SELECT COUNT(*) AS 选课门数,

   COUNT(Grade) AS 考试门数,

   MAX(Grade) AS 最高分,

   MIN(Grade) AS 最低分,

   AVG(Grade) AS 平均分

  FROM SC WHERE Sno = '9512101‘

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. Credit

WHERE C2.Cname = '操作系统'

例53. 查询学生的修课情况，包括修了课程的学生和没有修课的学生。

SELECT Student.Sno, Sname, Cno, Grade

  FROM Student LEFT OUTER JOIN SC

ON 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 Student

WHERE Sdept IN

   ( SELECT Sdept FROM Student

  WHERE Sname = '刘晨' )

         AND Sname != '刘晨' 

例61. 查询成绩为大于90分的学生的学号、姓名。

SELECT Sno, Sname FROM Student

WHERE Sno IN

( SELECT Sno FROM SC

WHERE 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．将新生记录（9521104，陈冬，男，18岁，信息系）插入到Student表中。

  INSERT INTO Student VALUES

    ('9521104', '陈冬', '男', 18, '信息系')

例73．在SC表中插入一新记录，学号为“9521104”，选的课程号为“c01”，成绩暂缺。

INSERT INTO SC(Sno, Cno)

      VALUES('9521104', 'c01’)

  实际插入的值为：('9521105', 'c01'，NULL)

字符要用单引号  ' '      数字不用

例74. 将所有学生的年龄加1。

UPDATE Student

     SET Sage = Sage + 1

例75. 将‘9512101’学生的年龄改为21岁。

UPDATE Student SET Sage = 21

WHERE Sno = '9512101

例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

转自：数据库操作语句