任务1

  1. 创建一个学生关系表，年龄要求15岁以上、55岁以下(用户定义完 整性)
  2. 创建一个社团人员关系表，要求主码是学号+社团名字，学号的属性参照学生关系表(实体完整性、参照系完整性)
  3. 插入数据的指令
  4. 条件删除的指令
  5. 更新数据的指令
  6. 查询高数不及格的学生数量
  7. 查询同时有三门课不及格的同学学号
  8. 创建一个视图，记录每位同学学号、姓名、平均成绩
  9. 将某个表的查询权限授权给某个用户，允许他/她继续授权
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
/*创建数据库*/
create database mission1;
use mission1;

/*创建数据表*/
create table student(
    Sno char(3) primary key,
    Sname char(20) unique,
    Ssex char(1),
    Sage smallint check(Sage>=15 and Sage<=55)
);

create table club(
    Sno char(3),
    Cname char(20),
    primary key (Sno,Cname),
    foreign key (Sno) references student(Sno)
);

create table course(
    Cno char(2),
    Cname char(20),
    Ccredit smallint,
    primary key (Cno,Cname)
);

create table sc(
    Sno char(3),
    Cno char(2),
    Grade smallint,
    primary key (Sno,Cno),
    foreign key (Sno) references student(Sno),
    foreign key (Cno) references course(Cno)
);

/*插入数据*/
insert into student values('001','张三','男',15);
insert into student values('002','李四','男',20);
insert into student values('009','Foo','男',24);
insert into student values('911','球','女',20);
/*修改数据*/
update student set sname='Qiu' where sno='911';

insert into club values('002','数据库上机爱好者协会');
insert into club values('001','xdsec');
insert into club values('009','xdsec');
insert into club values('911','篮球俱乐部');

select sname from student,club where cname='数据库上机爱好者协会' and student.sno=club.sno;
/*外连接*/
select * from student left outer join club using (sno);

insert into course values('01','高数',4);
insert into course values('02','数据库',2);
insert into course values('03','信号',3);

insert into sc values('001','01',59);
insert into sc values('002','01',61);
insert into sc values('009','01',90);
insert into sc values('911','01',98);

/*高数选课自然连接*/
select student.sno,sname,ssex,sage,course.cno,cname,ccredit,grade
from student,course,sc
where sc.cno='01' and sc.sno=student.sno and sc.cno=course.cno;

insert into sc values('001','02',59);
insert into sc values('002','02',58);
insert into sc values('009','02',90);
insert into sc values('911','02',98);
insert into sc values('001','03',59);
insert into sc values('002','03',100);
insert into sc values('009','03',90);
insert into sc values('911','03',98);

/*自然连接*/
select student.sno,sname,ssex,sage,course.cno,cname,ccredit,grade
from student,course,sc
where sc.sno=student.sno and sc.cno=course.cno;

/*条件删除*/
insert into student(sno,sname) values('000','delete');
delete from student where sno='000';

/*高数挂科人数*/
select count(*)
from course,sc
where cname='高数' and course.cno=sc.cno and grade<60;

/*挂科三门的学生学号*/
select sno
from sc
where grade<60
group by sno
having count(*)=3;

/*平均成绩视图*/
create view avg_grade(Sno,Sname,Gavg)
as
select student.sno,sname,avg(grade)
from student,sc
where student.sno=sc.sno
group by sno;
drop view avg_grade;

/*授权用户查询且允许继续授权*/
create user test identified by 'test';
grant select on mission1.sc to test with grant option;
flush privileges;