按建表顺序来执行
-- 创建学生表 create table Student ( Sno int primary key not null , Sname varchar(20), Ssex char(2), Sage int, Sdept varchar(10) ) -- 给学生表添加数据 insert into student(Sno,Sname, Ssex, Sage, Sdept) VALUES (10001,'Jack', '男',21,'CS'), (10002,'Rose','女',20,'SE'), (10003,'Michael','男',21,'IS'), (10004,'Hepburn','女',19,'CS'), (10005,'Lisa','女',20,'SE'); Sex 输入报错 然后 我们让Ssex以utf-8编码存在 alter table student change Ssex Ssex varchar(10) character set utf8; -- 创建课程表 create table Course ( Cno VARCHAR(20) primary key not null , Cname varchar(20), Credit int ) -- 给课程表添加数据 insert into Course(Cno, Cname, Credit) VALUES ('00001','DataBase',4), ('00002','DataStructure',4), ('00003','Algorithms',3), ('00004','OperatingSystems',5), ('00005','ComputerNetwork',4); SELECT *FROM Course; -- 创建选课表 create table SC ( Sno int, Cno int , Grade int ); ALTER TABLE SC ADD CONSTRAINT scc FOREIGN KEY(Sno ) REFERENCES student(Sno); --ALTER TABLE SC ADD CONSTRAINT sccs --FOREIGN KEY(Cno ) REFERENCES Course(Cno); -- 给 选课表添加数据 insert into SC(Sno, Cno, Grade) VALUES ('10002','00003',86), ('10001', '00002', 90), ('10002' ,'00004', 70), ('10003' ,'00001', 85), ('10004', '00002', 77), ('10005', '00003' ,88), ('10001', '00005' ,91), ('10002','00002', 79), ('10003', '00002' ,83), ('10004', '00003' ,67); select *from SC; -- 查询学号为10002学生的所有成绩,结果中需包含学号、姓名、所在系别、课程号、课程名以及对应成绩 select s.Sno,s.Sname,s.Sdept,c.Cno,SC.Grade from student s,SC,course c where s.Sno=SC.Sno and c.Cno = SC.Cno and s.Sno=10002; -- 查询每位学生成绩大于85的课程,结果中需包含学号、姓名、所在系别、课程号、课程名以及对应成绩。 select s.Sno,s.Sname,s.Sdept,c.Cno,sc.Grade from student s,sc,course c where s.Sno=sc.Sno and c.Cno = sc.Cno and sc.Grade>85; -- (4)将学号为10005的学生, OperatingSystems(00004)成绩为73分这一记录写入选课表中。 insert into sc(sno, cno, grade) values (10005,00004,73); SELECT *FROM sc; -- 从三个表中删除学号为10003 delete from sc where Sno=10003; delete from student where Sno=10003;