mysql数据库基础上

创建数据库

create database PYtoDB;

使用数据库

use PYtoDB

建表

create table Student
(Sno int PRIMARY KEY,
Sname varchar(20) ,
Ssex nvarchar(2),
Sage int ,
Sdept varchar(2));

插入数据
insert into Student values('10001','jack','男',21,'CS'),
('10002','Rose','女',20,'SE'),
('10003','Michael','男',21,'IS'),
('10004','Hepburn','女',19,'CS'),
('10005','Lisa','女',20,'SE');

create table course
(Cno int PRIMARY KEY,
Cname varchar(20) ,
Credit int );
insert into Course values(00001,'DataBase',4),
(00002,'DataStructure',4),
(00003,'AIgorithms',3),
(00004,'OperatingSystems',5),
(00005,'ComputerNetwork',4);

create table SC
( Sno int,
Cno int ,
Grade int ,
Primary key(Cno,Sno));
insert into SC 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);

 

1.查学号10002学生成绩，结果包含学号，姓名，所在系，课程号，课程名，对应成绩（隐式连接查询）
select student.Sno,Sname,Sdept,course.Cno,Cname,Grade from student,course,sc where student.Sno=sc.Sno and course.Cno=sc.Cno and student.Sno=10002;
2.查每位学生成绩大于85的课程，结果中包含学号，姓名，所在系别，课程号，课程名，以及对应成绩。
select student.Sno,Sname,Sdept,course.Cno,Cname,sc.Grade from student,course,sc where student.Sno=sc.Sno and course.Cno=sc.Cno and sc.Grade>85;
3.由于培养计划修改，现要将课程号00001，课程名DataBase的学分改为5学分。（改操作）
update course set Credit=5 where Cno=00001;
4.将学号10005的学生，operatingSystem为73记录录入选课表中（插入）
insert into sc values (10005,4,73);
delete from sc where Sno=00004;
5.将学号10003的学生从三个表删除。
delete from student where Sno=10003;
insert into student values (10003,'michael','男',21,'IS');