【数据库】相关代码集

 1 Create Table student
 2 (
 3     Sno char (9) Primary key , 
 4     Sname char (20) Unique ,
 5     Ssex char (2) ,
 6     Sage smallint , 
 7     Sdept char(20)
 8 
 9 );
10 
11 Create Table Course
12 (
13     Cno char (4) Primary key ,
14     Cname char (40) not Null , 
15     Cpno char (4) ,
16     Ccredit smallint 
17     foreign key (Cpno) References Course(Cno)
18 );
19 
20 Create Table SC
21 (
22     Sno char (9) ,
23     Cno char (4) , 
24     Grade smallint 
25     
26     Primary key ( Sno , Cno ) ,
27     Foreign key ( Sno ) References Student (Sno),
28     Foreign key ( Cno ) References Course (Cno)     
29     
30 );
31 
32 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
33 values('201215121','李勇','男',20,'CS');
34 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
35 values('201215122','刘晨','女',19,'CS');
36 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
37 values('201215123','王敏','女',18,'MA');
38 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
39 values('201215125','张立','男',19,'IS');
40 
41 
42 Insert Into Course(Cno,Cname,Cpno,Ccredit)
43 values('2','数学',NULL,2)
44 Insert Into Course(Cno,Cname,Cpno,Ccredit)
45 values('6','数据处理',NULL,2)
46 Insert Into Course(Cno,Cname,Cpno,Ccredit)
47 values('4','操作系统','6',3)
48 Insert Into Course(Cno,Cname,Cpno,Ccredit)
49 values('7','PASCAL语言','6',4)
50 Insert Into Course(Cno,Cname,Cpno,Ccredit)
51 values('5','数据结构','7',4)
52 Insert Into Course(Cno,Cname,Cpno,Ccredit)
53 values('1','数据库','5',4)
54 Insert Into Course(Cno,Cname,Cpno,Ccredit)
55 values('3','信息系统','1',4)
56 
57 /*
58 drop table SC;
59 drop table Course;
60 drop table student;
61 */
62 
63 
64 Insert Into SC(Sno,Cno,Grade)
65 values ( '201215121' , '1' , 92 )
66 Insert Into SC(Sno,Cno,Grade)
67 values ( '201215121' , '2' , 85 )
68 Insert Into SC(Sno,Cno,Grade)
69 values ( '201215121' , '3' , 88 )
70 Insert Into SC(Sno,Cno,Grade)
71 values ( '201215122' , '2' , 90 )
72 Insert Into SC(Sno,Cno,Grade)
73 values ( '201215122' , '3' , 80 )

实验1

  1 Create Table student
  2 (
  3     Sno char (9) Primary key , 
  4     Sname char (20) Unique ,
  5     Ssex char (2) ,
  6     Sage smallint , 
  7     Sdept char(20)
  8 
  9 );
 10 
 11 Create Table Course
 12 (
 13     Cno char (4) Primary key ,
 14     Cname char (40) not Null , 
 15     Cpno char (4) ,
 16     Ccredit smallint 
 17     foreign key (Cpno) References Course(Cno)
 18 );
 19 
 20 Create Table SC
 21 (
 22     Sno char (9) ,
 23     Cno char (4) , 
 24     Grade smallint 
 25     
 26     Primary key ( Sno , Cno ) ,
 27     Foreign key ( Sno ) References Student (Sno),
 28     Foreign key ( Cno ) References Course (Cno)     
 29     
 30 );
 31 
 32 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
 33 values('201215121','李勇','男',20,'CS');
 34 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
 35 values('201215122','刘晨','女',19,'CS');
 36 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
 37 values('201215123','王敏','女',18,'MA');
 38 Insert Into student(Sno,Sname,Ssex,Sage,Sdept)
 39 values('201215125','张立','男',19,'IS');
 40 
 41 
 42 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 43 values('2','数学',NULL,2)
 44 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 45 values('6','数据处理',NULL,2)
 46 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 47 values('4','操作系统','6',3)
 48 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 49 values('7','PASCAL语言','6',4)
 50 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 51 values('5','数据结构','7',4)
 52 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 53 values('1','数据库','5',4)
 54 Insert Into Course(Cno,Cname,Cpno,Ccredit)
 55 values('3','信息系统','1',4)
 56 
 57 /*
 58 drop table SC;
 59 drop table Course;
 60 drop table student;
 61 */
 62 
 63 
 64 Insert Into SC(Sno,Cno,Grade)
 65 values ( '201215121' , '1' , 92 )
 66 Insert Into SC(Sno,Cno,Grade)
 67 values ( '201215121' , '2' , 85 )
 68 Insert Into SC(Sno,Cno,Grade)
 69 values ( '201215121' , '3' , 88 )
 70 Insert Into SC(Sno,Cno,Grade)
 71 values ( '201215122' , '2' , 90 )
 72 Insert Into SC(Sno,Cno,Grade)
 73 values ( '201215122' , '3' , 80 )
 74 
 75 
 76 /*1、查询女生中年龄大于19岁的学生姓名*/
 77 Select Sname
 78 From student
 79 where Sage>=19 and Ssex = '女';
 80 
 81 /*2、查询计算机系的学生学号、姓名、出生年份，
 82 按出生年份降序显示（给出生年份起个别名）；*/
 83 
 84 Select Sno 学生学号 , Sname 姓名 , 2019 - Sage 出生年份
 85 From student 
 86 Order by 2019 - Sage Desc;
 87 
 88 /*3、查询没有先行课的课程号、课程名；*/
 89 
 90 Select Cno 课程号 ,Cname 课程名 
 91 From Course
 92 Where Cpno IS NULL;
 93 
 94 /*4、查询选修了课程的学生学号；*/
 95 
 96 Select distinct Sno  学生学号
 97 From SC;
 98 
 99 /*5、查询年龄在18到20岁之间的学生学号、姓名、性别；*/
100 Select Sno 学生学号, Sname 姓名 , Ssex 性别 
101 From student
102 Where Sage between 18 and 23;
103 
104 /*6、查询是计算机系或数学系的学生姓名，年龄；*/
105 Select Sname 学生姓名 , Sage 年龄
106 From student
107 Where Sdept in ('CS','MA');
108 
109 /*7、查询课程名含有‘系统’的全部信息；*/
110 Select Cno 课程号 , Cname 课程名称 , Cpno 先行课号 , Ccredit 学分
111 From Course
112 Where Cname Like '%系统%';
113 
114 /*8、查询学号倒数第二位是‘2’的学生姓名、院系；*/
115 Select Sno 学号 , Sname 学生姓名 , Sdept 院系
116 From student 
117 Where Sno Like '%2_' ;
118 
119 /*9、查询被选修了的课程号；*/
120 Select distinct Cno 已选修的课程号
121 From SC ;
122 
123 /*10、查询学分大于3的课程号和课程名；*/
124 Select Cno  课程号 , Cname 课程名 , Ccredit 学分
125 From Course 
126 Where Ccredit > 3 ;

实验2

 1 //按照数据库 （1号课）从高到低 排序
 2 
 3 select grade *
 4 from SC
 5 where Cno = '1'
 6 order by grade Desc ;
 7 
 8 //把所有课的成绩从高到低排序
 9 select grade *
10 from SC
11 where grade is not NULL
12 order by grade Desc ;
13 
14 //统计每门课的可好，及其最高成绩
15 select Cno , max( grade )
16 from SC
17 group by Cno ;
18 
19 //按照每个人的总成绩降序
20 select Sno , sum(grade)
21 from SC
22 where grade is not NULL
23 group by Sno
24 order by sum(grade) Desc ;

课堂讲解事例

 1 1、统计每个同学超过80分的课程的数目超过5门，输出对应的学号及课程号。
 2 
 3 方法1:
 4 
 5 Select Sno , Cno 
 6 from SC
 7 Group by Sno having count( grade > 80 ) > 5 ;
 8 
 9 方法2:
10 Select Sno , Cno
11 from SC
12 where grade > 80
13 group by Sno having Count(Cno) > 5;
14 
15 
16 2、间接先行课
17 Select A.Cno 课程号, B.Cpno 间接先行课
18 from course A  , course B
19 where A.cpno = B.Cno;
20 
21 3、查询与“刘晨”同学是同一个学院的学生 ,输出对应的学号，姓名，性别
22 Select B.Sno , B.Sname , B.Ssex
23 from student A , student B
24 where A.Sname = '刘晨' and A.Sdept = B.Sdept

9-25课堂讲解事例

 1 嵌套查询
 2 1、查询和“刘晨”同学同一个专业的学生，输出Sno,Sname
 3 Select Sno, Sname
 4 from student
 5 where Sdept in(
 6 
 7 select Sdept
 8 from student
 9 where Sname = '***'
10 );
11 
12 2、查询有间接先行课的课程号
13 Select Cno
14 from Course
15 where Cpno in(
16 
17 select Cno
18 from Course
19 where Cpno is Not Null
20 );
21 
22 3、查询"数据库" 高于平均分的 学生->学号
23 
24 Select Sno
25 from SC
26 where grade >
27 (
28      select AVG(grade)
29      from SC
30      where Cno in (
31           select Cno 
32           from Course
33           where Cname = '数据库' 
34      )      
35 ) and Cno in (
36      select Cno
37      from Course
38      where Cname = '数据库'
39 );
40 
41 方法2:
42 select Sno
43 from SC,course
44 where Sc.cno = course.Cno and Cname = '数据库' 
45 and grade > 
46 (
47      select AVG(grade)
48      from Sc , Course
49      where Sc.cno = course.Cno and Cname = '数据库'
50 )

9-29 课堂讲解事例

 1 实验名称：实验三：数据库连接查询和聚集函数
 2 实验类型：设计型
 3 
 4 实验要求和目的：
 5 1、熟练掌握聚集函数
 6 2、掌握连接查询的使用
 7 
 8 
 9 实验步骤：
10 一、初始化数据库，还原三个表及数据；
11 二、实现以下查询：
12 1、查询每个学生的平均成绩；（输出学号、平均成绩）
13 2、查询每个学生的平均成绩；（输出学号、姓名、平均成绩）
14 3、查询数据库课程的最高分；
15 4、查询选修了1号课或3号课的学生姓名；
16 5、查询每个学院的学生人数；
17 6、查询计算机系的女生所有信息；
18 7、查询跟刘晨一个院系的学生学号、姓名；
19 8、查询选修人数超过2个人的课程号、课程名；
20 9、查询男女生人数；
21 10、按总成绩降序显示学生的学号、姓名、总成绩；
22 
23 /*1、查询每个学生的平均成绩；（输出学号、平均成绩）*/
24 Select Sno 学号 , AVG(grade)平时成绩
25 From SC 
26 group by Sno ;
27 
28 /*2、查询每个学生的平均成绩；（输出学号、姓名、平均成绩）*/
29 Select student.Sno 学号 , Sname 姓名 , AVG(grade) 平时成绩
30 From student , SC 
31 where student.Sno = SC.Sno
32 group by student.Sno , Sname ;
33 
34 /*3、查询'数据库课程'的最高分；*/
35 Select Course.Cname , MAX(Grade)最高分
36 From SC,Course
37 Where Course.Cno = Course.Cno And Course.Cname = '数据库'
38 Group by Cname ;
39 
40 /*4、查询选修了1号课或3号课的学生姓名；*/
41 Select distinct Sname 姓名
42 from student , SC 
43 where SC.Sno = student.Sno and (SC.Cno = 1 or SC.Cno = 3) ;
44 
45 /*5、查询每个学院的学生人数；*/
46 Select Sdept 学院,COUNT(*) 学生人数
47 from student
48 group by Sdept ;
49 
50 /*6、查询计算机系的女生所有信息；*/
51 Select Sno 学号 , Sname 姓名 , Ssex 性别 , Sage 年龄 , Sdept 学院
52 from student 
53 where Ssex = '女' and Sdept = 'CS';
54 
55 /*7、查询跟刘晨一个院系的学生学号、姓名；*/
56 Select A.Sno 学生学号 , A.Sname 姓名
57 from student A , student B 
58 where B.Sdept  = A.Sdept and B.Sname = '刘晨' ;
59 
60 /*8、查询选修人数超过2个人的课程号、课程名；*/
61 Select SC.Cno 课程号 , Cname 课程名
62 from SC , Course 
63 where SC.Cno = Course.Cno 
64 group by SC.Cno , Cname having COUNT(Sno) >= 2 ;
65 
66 
67 /*9、查询男女生人数；*/
68 Select Ssex 性别, COUNT(Sno) 人数
69 from student 
70 group by Ssex ;
71 
72 /*10、按总成绩降序显示学生的学号、姓名、总成绩；*/
73 Select stu.Sno 学号 , Sname 姓名 , SUM(Grade) 总成绩
74 from student stu , SC
75 where stu.Sno = SC.Sno 
76 group by stu.Sno , Sname 
77 order by SUM(Grade) DESC ;

实验3

 1 1、利用嵌套查询：与刘晨同专业的姓名和学号
 2 
 3 select Sno,Sname 
 4 from Student
 5 where Sdept in(
 6      Select Sdept
 7      From Student
 8      where Sname = '刘晨'
 9 )
10 
11 2、查询选修了课程名为“信息系统”的学生学号，姓名
12 
13 Select Sno , Sname 
14 From Student 
15 Where Sno in (
16      Select Sno 
17      from Sc 
18      where Cno in (
19            Select Cno
20            From Course
21            where Cname = '信息工程'
22      )
23 )    
24 
25 
26 3、查询比自身平均分高的那门课
27 Select Cno
28 From Sc X
29 where Grade > (
30       select AVG(grade)
31       from Sc Y
32       where Y.Sno = X.Sno
33 )
34 
35 4、查询除CS外的专业的学生，比CS中任意一个年纪大的学号和姓名。
36 Select Sno , Sname
37 from Student
38 where age < ANY(
39      Select Sage 
40      from Student
41      where Sdept = 'CS'
42 )and Sdept <> 'CS'
43 
44 5、查询选修了全部课程的学生姓名
45 
46 Select Sname 
47 from Student 
48 Where NOT EXIST (
49     Select *          (Cno)
50     from Course 
51     where NOT EXIST(
52            Select *
53            From Sc
54            where Sc.Cno = Course.Cno and Sc.Sno = Student.Sno
55     )
56 )

10-9 课堂讲解事例

  1 /*1、查询选修了1号课的学生姓名（用2种方法实现）*/
  2 
  3 /* 方法1 - 嵌套查询*/
  4 Select Sname 学生姓名
  5 From student 
  6 where Sno in (
  7     Select Sno 
  8     From SC
  9     where Cno = '1' 
 10 );
 11 
 12 /* 方法2 - 外部连接*/
 13 Select Sname 学生姓名
 14 From SC , student 
 15 where SC.Sno = student.Sno and SC.Cno = '1' ;
 16 
 17 /* 方法3 - Exists*/
 18 Select Sname 学生姓名
 19 From student 
 20 where Exists(
 21     select *
 22     From SC
 23     where Cno = '1' and student.Sno = SC.Sno 
 24 );
 25 
 26 /* 2、查询没有选修1号课的学生学生姓名 */
 27 
 28 /* 利用NOT in 语句*/
 29 Select Sname 学生姓名
 30 From student 
 31 where Sno Not in(
 32     select Sno
 33     from SC
 34     where student.Sno = SC.Sno and SC.Cno = '1' 
 35 );
 36 
 37 /* 利用NOT EXISTS语句*/
 38 Select Sname 学生姓名
 39 From student
 40 where NOT EXISTS(
 41     select *
 42     From SC
 43     where student.Sno = SC.Sno and SC.Cno = '1' 
 44 );
 45 
 46 
 47 /*3、查询没有选修数据库的学生姓名*/
 48 Select Sname 学生姓名
 49 From student
 50 where EXISTS(
 51     Select *
 52     from Course
 53     where Cname = '数据库' and NOT EXISTS(
 54         Select *
 55         From SC
 56         where student.Sno = SC.Sno and Course.Cno = SC.Cno 
 57     )
 58 );
 59 
 60 /*4、查询计算机学院男生中比该学院所有女生年龄都大的学号、姓名、年龄；*/
 61 Select Sno 学号 , Sname 姓名 , Sage 年龄
 62 from student A
 63 where A.Ssex = '男'
 64 and A.Sage > ALL(
 65     Select Sage 
 66     From student B
 67     where B.Ssex = '女' and A.Sdept = B.Sdept 
 68 )
 69 and A.Sdept = 'CS'
 70 ;
 71 
 72 /*5、查询比自己平均成绩低的学生学号、课程号、课程名；*/
 73 Select Sno 学生学号 , X.Cno , Course.Cname 
 74 From SC X, course
 75 where X.Cno = Course.Cno  and Grade < (
 76     Select AVG(Grade)
 77     From SC Y
 78     where Y.Sno = X.Sno
 79 );
 80 
 81 /*6、查询每个学生的选课情况；（输出学号、姓名、课程号、成绩）在kingbase 里实现；*/
 82 Select stu.Sno 学号 , Sname 姓名 , Cno 课程号 , Grade 成绩
 83 From student stu, SC 
 84 where stu.Sno = SC.Sno ;
 85 
 86 /*7、查询选修了1号课和3号课的学生姓名；*/
 87 
 88 /*方法1 ：利用两次查询取交集*/
 89 Select Sname 学生姓名
 90 from student
 91 where Sno in (
 92     Select Sno
 93     From SC
 94     where SC.Sno = student.Sno and Cno = '1'
 95 ) and Sno in (
 96     Select Sno
 97     From SC
 98     where SC.Sno = student.Sno and Cno = '3'
 99 );
100 
101 /*方法2 ：直接利用Union语句*/
102 Select Sname 学生姓名
103 from student
104 where Sno in(
105 
106     Select Sno
107     from SC 
108     where Cno = '1'
109     
110     intersect
111     
112     Select Sno
113     from SC 
114     where Cno = '3'
115 );
116 
117 
118 /*---------------------------------*/
119 
120 /*1、将数据库课程的学分改为5；*/
121 /*注释 ：未修改前为 ：4学分*/
122 Update Course
123 Set Ccredit = 5
124 where Cname = '数据库';
125 
126 /*2、删除刘晨的所有信息；*/
127 Delete 
128 From SC 
129 where Sno in(
130     Select Sno
131     From student
132     where SC.Sno = student.Sno and Sname = '刘晨'
133 );
134 
135 Delete
136 From student
137 where Sname = '刘晨'

实验4

 1 1、查询每个学生的选课数量 
 2 
 3 Select Sno , Count(Cno)
 4 from Sc
 5 Group by Sno
 6 having Count(*) = 5 ;
 7 
 8 2、查询选了所有的课程的 学生
 9 
10 Select Sname
11 from Student
12 Where Sno in(
13      Select Sno 
14      From Sc 
15      Group by Sno
16      Having count(*) = (
17              Select count(*)
18              from course 
19      )
20 )
21 
22 3、查询至少选修了学生2015122选修的全部课程的学生学号
23 方法1:
24 
25 Select distinct Sno
26 From Sc Scx
27 where Not exists(
28      Select *
29      from Sc Scy
30      where Scy.Sno = '2015122' and
31      not exists(
32            select *
33            from Sc Scz
34            where Scz.Sno = Scx.Sno and
35                      Scz.Cno = Scy.Sno)    
36      ) 
37 );
38 
39 方法2:
40 Select Distinct Sno
41 from Sc
42 where Cno in(
43         Select Cno
44         from Sc 
45         where Sno = '2015122'
46 )
47 Group by Sno
48 Having count(*) = (
49       Select count(*)
50       from Sc
51       where Sno = '2015122'   
52 );
53 
54 10-16 课堂讲解事例

10-16 课堂讲解事例

  1 1、查询　每门课都及格　的学生的学号
  2 
  3 Select distinct Sno
  4 from Sc A
  5 where Not exists(
  6      Select *
  7      from Sc B
  8      where A.Sno = B.Sno and B.Grade < 60
  9 );
 10 
 11 2、查询　既有　“课程大于90分”　又有“课程不及格”　的学生的学号
 12 Select distinct A.Sno
 13 from Sc A
 14 where exists(
 15     Select *
 16     from Sc B
 17     where A.Sno = B.Sno and Grade > 90 
 18 ) and exists (
 19     Select *
 20     from Sc C
 21     where A.Sno = C.Sno and Grade < 60
 22 )
 23 Group by Sno
 24 
 25 
 26 3、查询 "平均分不及格的课程号"　和　"平均成绩"
 27 
 28 select Cno , AVG(Grade)
 29 from Sc
 30 Group by Cno
 31 having AVG(Grade) < 60
 32 
 33 
 34 
 35 4、找出至少选修了２号学生选修过的全部课程的学生号
 36 Select Sno
 37 from Sc
 38 where Cno in(
 39     Select Cno
 40     from Sc
 41     where Sno = '201215122'
 42 )
 43 Group by Sno
 44 Having count(*) = (
 45     Select count(*)
 46     from Sc
 47     where Sno = '201215122'
 48 );
 49 
 50 5、查询7号课程没有考试成绩的学生学号
 51 
 52 Select Sno
 53 from Sc
 54 where Cno = '7' and Grade is NULL
 55 
 56 
 57 6、查询7号课程成绩在90分以上或60分以下的学生学号
 58 
 59 Select Sno
 60 from Sc 
 61 where Cno = '7'  and ( Grade < 60 or Grade > 90 )
 62 
 63 
 64 
 65 7、查询课程名以"数据"两个字开头的所有课程的课程号和课程名
 66 
 67 Select Cno , Cname
 68 from Course
 69 where Cname like '数据%'
 70 
 71 
 72 8、查询每个学生所有课程的平均成绩,输出学生学号和平均成绩
 73 
 74 Select Sno , AVG(Grade)
 75 from Sc
 76 group by Sno
 77 
 78 
 79 9、查询选修7号课的学生的学号、姓名、性别
 80 Select A.Sno 学号 , A.Sname 姓名 , A.Ssex 性别
 81 from student A , Sc B
 82 where A.Sno = B.Sno and B.Cno = '2'
 83 
 84 
 85 
 86 10、查询有30名以上的学生选修的课程号
 87 Select Cno
 88 from Sc
 89 Group by Cno
 90 Having count(*) >= 30 ;
 91 
 92 11、查询至今没有考试考试不及格的学生学号
 93 
 94 Select Sno
 95 from Sc A
 96 where Not exists(
 97     Select *
 98     from Sc B
 99     where B.Sno = A.Sno and ( B.Grade is NULL or B.Grade < 60 )
100 )
101 group by Sno
102 
103 
104 
105 12、查询没有选修1号课的学生 输出其姓名(用3种方法实现)
106 
107 方法1:
108 Select distinct A.Sno
109 from Sc A
110 where ( Select Cno 
111      from Sc B 
112 where A.Sno = B.Sno and B.Cno = '1' ) is NULL
113 
114 方法2:
115 Select Sno
116 from Student
117 Except
118 Select Sno
119 from Sc
120 where Cno = '1'
121 
122 
123 方法3:
124 Select distinct Sno , Sname 
125 from Student 
126 where not exists (
127     select *
128     from Sc
129     where Cno = '1' and student.Sno = Sc.Sno
130 )
131 
132 13、查询至少选修了刘晨选的全部课程的学生学号
133 Select Sno
134 from Sc 
135 where Cno in ( 
136     Select Cno
137     from Sc , Student 
138     where student.Sno = Sc.sno and Sname ='刘晨'
139 )
140 Group by Sno
141 having count(*) = (
142     Select count(*)
143     from Sc , Student 
144     where student.Sno = Sc.sno and Sname ='刘晨'
145 )
146 
147 14、查询每个学院学生的平均年龄,并把结果插入到一个新表Sdep_Avg(Sdep,age);
148 
149 Create table Dept_age(
150     Sdept char(20) ,
151     Avg_age smallint
152 );
153 
154 Insert into
155 dept_age( Sdept , Avg_age )
156 Select Sdept , AVG( Sage )
157 from Student 
158 group by Sdept ;
159 
160 15、将"刘晨"的信息系统课程成绩改为90分
161 
162 Update SC
163 Set Grade = 90 
164 where Sno in (
165     Select Sc.Sno
166     from Student , Sc
167     where Student.Sno = Sc.Sno and Student.Sname = '刘晨'
168 )

实验5-数据库综合查询

 1 查询选修了1号课 "或" 2号课的学生学号
 2 
 3 Select Sno
 4 From Sc
 5 where Cno = '1'
 6 UNION
 7 Select Sno
 8 From Sc
 9 where Cno = '2'
10 
11 查询选修了1号课 "和" 2号课的学生学号
12 Select Sno
13 from Sc
14 where Cno = '1' 
15 Intersect
16 select Sno
17 from Sc
18 where Cno = '2'
19 
20 
21 查询没有选修1号课的学生学号
22 方法1:
23 Select distinct A.Sno
24 from Sc A
25 where(
26      select Cno
27      from Sc B
28      where A.Sno = B.Sno and B.Cno = '1'   
29 ) is NULL
30 
31 方法2:
32 Select Sno
33 from Sc 
34 Except
35 Select Sno
36 from Sc
37 where Cno = '1'
38 
39 方法3:
40 Select distinct Sno
41 from Sc
42 where Cno = '1' And
43 Group by Sno
44 Having count(*) = 0
45 
46 方法4:
47 Select distinct Sno
48 from Sc A
49 where Not exist(
50      Select *
51      from Sc B
52      where A.Sno = B.Sno and B.Cno = '1'
53 )

10-18 课堂讲解事例

 1 Create table student
 2 (
 3     Sno char(9) ,
 4         Constraint StdentKey primary key(Sno) ,
 5     Sname char(20) 
 6         Constraint C1 Not Null ,
 7     Ssex char(2) 
 8         Constraint C2 Check( Ssex in('男','女') ) ,
 9     Sage Smallint 
10         Constraint C3 Check( 15 <= Sage AND Sage <= 35 ),
11     Sdept char(20) 
12         Constraint C4 Not Null 
13 );
14 
15 Create table Course(
16     Cno Char(9) ,
17 
18     Cname Char(9) 
19         Constraint C1 Unique ,
20 
21     Cpno Char(9) ,
22     
23         Constraint CourseForKey foreign key ( Cpno )
24         References Cno( Cpno ) ,
25 
26     Ccredit Smallint 
27         Constraint C2 Not Null ,
28 
29     Constraint CourseKey primary key ( Cno )
30 );
31 
32 Create table SC (
33     Sno Char(9) ,
34     Cno Char(9) ,
35     Grade Smallint
36         Constraint C1 Check( 0 <= Grade and Grade <= 100 ) ,
37     Constraint SCKey1 primary key ( Sno , Cno )
38 );
39 
40 Alter table Student 
41     Drop Constraint C3 ;
42 Alter table Student
43     Add Constraint C3 Check( 15 <= Sage and Sage <= 50 )
44 
45 
46 Create table s_count(
47     Ssex Char(9),
48     Scount Smallint 
49 );
50 
51 
52 Insert Into S_count 
53 values( ‘男’ , 0 )
54 
55 Insert Into S_count
56 values( ‘女’ , 0 )
57 
58 Create trigger count_student 
59 After Insert On Student 
60 for each Row
61 Begin
62     if (new.Ssex = ‘男’)
63         Update S_count
64         Set Scount = Scount + 1 
65         where Ssex = ‘男’
66     end if
67 
68     if (new.Ssex = ‘女’ )
69         Update S_count
70         Set Scount = Scount + 1
71         where Ssex = ‘女’     end if
72 End;
73 
74 Update S_count
75 set Scount = 0 ;
76 
77 Delete 
78 from Student ;
79 
80 
81 Insert Into
82 Student
83 values( '20172899' ,'hhz','男',18,'CS');
84 
85 Insert Into
86 Student
87 values( '20172900' ,'haz','女',18,'CS');
88 
89 Insert Into
90 Student
91 values( '20172901' ,'wb','男',22,'CS');

实验6

 1 1、新建用户u1、u2，u3 （connect类型）
 2 2、将对student、course、sc三个表的查询、增删改权限授予u1,同时u1具有继续授予其他用户的权利；
 3 
 4 grant select , update , delete 
 5 on table student , course , Sc
 6 to u1 
 7 with grant option
 8 
 9 3、以u1身份登录，将对三个表的查询权利授给u2；
10 
11 grant select
12 on table student , courst , Sc
13 to u2
14 
15 
16 4、以u1身份登录，将对三个表的查询，增加、修改的权利授给u3；
17 
18 grant select,update,delete
19 on table student , courst , Sc
20 to u3
21 
22 5、回收u1的对三个表的增删改权利
23 
24 revoke select, update , delete 
25 on table student , course , Sc
26 from u1

实验7

 1 P71页习题
 2 1、求供应工程J1零件的供应商号码Sno
 3 
 4 Select dinstinct Sno from SPJ
 5 where jno = 'J1';
 6 
 7 2、求供应工程J1零件P1的供应商号码Sno
 8 
 9 Select Sno from SPJ
10 where Jno = 'J1' and Pno = 'p1';
11 
12 3、求供应工程J1零件为红色的供应商号码Sno
13 Select Sno
14 from P , SPJ
15 where SPJ.Pno = P.pno AND jno = 'J1' AND color = '红'
16 
17 4、求没有使用天津供应商生产的红色零件的工程号Jno
18 
19 Select jno from J
20 Except
21 Select jno from SPJ , P , S
22 where 
23         SPJ.pno = P.pno 
24 AND SPJ.Sno = S.Sno 
25 AND city = '天津' 
26 AND color = '红'
27 
28 5、求至少用了供应商S1所供应的全部零件的工程号Jno
29 Select distinct jno 
30 from SPJ A
31 where not exists(
32        Select *
33        From SPJ B
34        where Sno = 'S1' AND
35                   not exists(
36                          Select *
37                          from SPJ C
38                          where C.Jno = A.Jno 
39                                     AND C.Sno = 'S1' AND C.pno = B.pno
40                   )   
41 )