编辑代码

create database stu;
use stu ;

create table student (
	Sno char(9)  primary key, 
	Sname char (20) unique,
	Ssex char (2),
	Sage smallint,
	Sdept char (20)  );

create table course (
	Cno char (4) primary key, 
	Cname char (40) not null, 
	Cpno char(4),  
	Ccredit smallint , 
	foreign key (Cpno) references course (Cno)  ); 

create table sc (    
	Sno char (9) ,
	Cno char (4) ,
	Grade smallint ,
	primary key (Sno , Cno) ,
	foreign key (Sno) references student (Sno) ,
	foreign key (Cno) references course (Cno)  );

insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215121' , '李勇' , '男' , 20 , 'CS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215122' , '刘晨' , '女' , 19 , 'IS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215123' , '王敏' , '女' , 18 , 'MA'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215124' , '张立' , '男' , 19 , 'IS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215125' , '流云' , '女' , 18 , 'CS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215126' , '王楠' , '女' , 19 , 'CS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215127' , '王晓之' , '男' , 17 , 'IS'  );
insert into student ( Sno , Sname , Ssex , Sage, Sdept ) values ( '20215128' , '王皇明' , '男' , 16 , 'IS'  );


insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '2' , '数学' , null , 6 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '6' , '数据处理' , null , 3 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '4' , '操作系统' , '6' , 4 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '7' , 'pascal语言' ,'6' , 4 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '5' , '数据结构' , '7' , 4 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '1' , '数据库' , '5' , 4 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '3' , '信息系统' , '1' , 4 ) ;
insert into course ( Cno , Cname , Cpno , Ccredit  ) values ( '8' , '数据库原理' , '5' , 4 ) ;

insert into sc ( Sno , Cno , Grade ) values ( '20215121' , '1' , '92' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215121' , '2' , '85' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215121' , '3' , '88' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215122' , '2' , '90' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215122' , '3' , '80' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215123' , '2' , '85' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215124' , '1' , '58' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215124' , '2' , '85' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215128' , '2' , '89' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215128' , '3' , '77' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215128' , '1' , '56' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215127' , '2' , '100' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215127' , '3' , '97' ) ;
insert into sc ( Sno , Cno , Grade ) values ( '20215127' , '1' , '93' ) ;




select Sno , Sname 
from student ;

select Sname , 2019-Sage 出生年份 , lower(Sdept) 系
from student ;

select Sname , Sage 
from student
where Sage < 20 ;

select distinct Sno   
from sc
where Grade < 60 ;

select Sname , Sage , Sdept
from student
where Sage between 20 and 30 ;

select Sname , Ssex
from student
where Sdept in ('CS' , 'MA' , 'IS' ) ;

select Sname , Sno , Ssex 
from student
where Sname like ' 李% ' ;

select Sno , Grade
from sc
where Cno = '3' ;

select student . * , sc . * 
from student , sc
where student . Sno = sc . Sno ;

update course set Ccredit = 4
where Cno = '4' ;

update course set Cpno = null
where Cno = '7' ;

select Sno , Sname , Sdept
from student
where Sdept in (
	select Sdept
	from student
	where Sname = '刘晨'  ) ;