一、表的创建
create table student (
sno int primary key comment '学号',
sname varchar(25) comment '姓名',
age int comment '年龄',
sex char(20) comment '性别',
sdept varchar(36) comment '所在系'
);
create table course(
cno int primary key comment '学号',
cname varchar(36) comment '课程名',
cdept varchar(36) comment '课程所开系'
);
create table sc(
sno int comment '学生学号' ,
cno int comment '学生课程号' ,
grade int comment '课程成绩',
primary key (sno,cno),
foreign key(sno) references student(sno),
foreign key (cno) references course(cno)
);
1.student表的数据
2、课程表的数据course
3、学生成绩表的数据
二、操作序列
1、查询计算机系cs的全体学生学号、姓名和性别
select * from student where sdept='cs';
2、检索选修了课程号为2的学生号和姓名
select student.sno,sname,cno from student,sc where student.sno=sc.sno and cno=2;
3、检索至少选修了三门课以上的学生号
select sno from sc group by Sno having count(*)>3;
4、检索选修了全部课程的学生
select sname from student
where NOT exists
(select * from course
where NOT exists
(select * from sc where
sno = student.sno and cno = course.cno))
5、在原表的基础上创建一个视图名v_student(学号、姓名)
create view v_student (sno,sname)
as
select sno,sname from student
6、更新学号为145689701的学生的姓名ws->王三
update student set sname='王三' where sno=145689701;
7、在student表中删除学号为145689705的学生
delete from student where sno=145689705;
8、在student表中插入一条数据
insert into student(sno, sname, age, sex, sdept) VALUE (145689705,'张强',18,'男','sw');