一、表的创建
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表的数据
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/e5024a54f22a58ec8cdd572bdac9bd0c.png)
2、课程表的数据course
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/f82f24b7b52f9c6b8cf86b57b2839c60.png)
3、学生成绩表的数据
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/7a4f8cd1f72ee61d13d91d09468c6c29.png)
二、操作序列
1、查询计算机系cs的全体学生学号、姓名和性别
select * from student where sdept='cs';
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/887500f950154a0b878d57e3e0cc6f19.png)
2、检索选修了课程号为2的学生号和姓名
select student.sno,sname,cno from student,sc where student.sno=sc.sno and cno=2;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c0be3f45e3121062180ab16b173b220b.png)
3、检索至少选修了三门课以上的学生号
select sno from sc group by Sno having count(*)>3;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/c9c5db511f5562b27596153c4983ae27.png)
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');