Mysql 查询练习

#创建数据库
create database ST CHARACTER set utf8;
#创建用户
create user ST identified by '19980510';
#授权用户操作该数据库
grant all on ST.* to ST;

----------------
#创建学生表
create table Student
(
Sno char(9) primary key ,
Sname char(20) not null,
Ssex char(2),
Sage smallint,
Sdept char(20)
);
#插入信息
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values(201215121,'李勇','男',20,'CS');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values(201215122,'刘晨','女',19,'CS');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values(201215123,'王敏','女',18,'MA');
insert into Student(Sno,Sname,Ssex,Sage,Sdept) values(201215125,'张立','男',19,'IS');
--------------
#创建课程表
create table Course
(
Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key(Cpno) references Course(Cno)
);
#插入信息
insert into Course(Cno,Cname,Cpno,Ccredit) values(1,'数据库',5,4);
insert into Course(Cno,Cname,Cpno,Ccredit) values(2,'数学',,2);
insert into Course(Cno,Cname,Cpno,Ccredit) values(3,'信息系统',1,4);
insert into Course(Cno,Cname,Cpno,Ccredit) values(4,'操作系统',6,3);
insert into Course(Cno,Cname,Cpno,Ccredit) values(5,'数据结构',7,4);
insert into Course(Cno,Cname,Cpno,Ccredit) values(6,'数据处理',,2);
insert into Course(Cno,Cname,Cpno,Ccredit) values(7,'PASCAL语言',6,4);
-------------------
/*
[SQL]insert into Course(Cno,Cname,Cpno,Ccredit) values(1,'数据库',5,4);
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`st`.`course`, CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`))
*/
-- 解决方法 先不录入先修课（Cpno）的值
insert into Course(Cno,Cname,Ccredit) values(1,'数据库',4);
insert into Course(Cno,Cname,Ccredit) values(2,'数学',2);
insert into Course(Cno,Cname,Ccredit) values(3,'信息系统',4);
insert into Course(Cno,Cname,Ccredit) values(4,'操作系统',3);
insert into Course