sqltest1

mysql -u root -p123456;
quit;

create database testfj character set utf8mb4;
use testfj;

create table Student(
    Sno varchar(7) primary key,
    Sname varchar(10) not null,
    Ssex varchar(2) check(Ssex = "男" or Ssex = "女"),
    Sage int check(Sage >= 15 and Sage <= 45),
    Sdept varchar(20) default "计算机系"
);

create table Course(
    Cno varchar(10) primary key,
    Cname varchar(20) not null,
    Ccredit int check(Ccredit > 0),
    Semster int check(Semster > 0) ,
    Period int check(Period > 0) 
);

create table SC(
    Sno varchar(7),
    Cno varchar(10),
    Grade int check (Grade >= 0 and Grade <= 100),
    primary key(Sno,Cno),
    foreign key (Sno) references Student(Sno),
    foreign key (Cno) references Course(Cno)
);

alter table SC 
add XKLB varchar(4);

alter table Course 
drop Period;

insert into Student 
values
('9512101','李勇','男',19,'计算机系'),
('9512102','刘晨','男',20,'计算机系'),
('9512103','王敏','女',20,'计算机系'),
('9521101','张立','男',22,'信息系'),
('9521102','吴宾','女',21,'信息系'),
('9521103','张海','男',20,'信息系'),
('9531101','钱小平','女',18,'数学系'),
('9531102','王大力','男',19,'数学系');

insert into Course 
values
('C01','计算机文化学',3,1),
('C02','VB',2,3),
('C03','计算机网络',4,7),
('C04','数据库基础',6,6),
('C05','高等数学',8,2),
('C06','数据结构',5,4);

insert into SC 
values
('9512101','c01',90,'必修'),
('9512101','c02',86,'选修'),
('9512101','c06',null,'必修'),
('9512102','c02',78,'选修'),
('9512102','c04',66,'必修'),
('9521102','c01',82,'选修'),
('9521102','c02',75,'选修'),
('9521102','c04',92,'必修'),
('9521102','c05',50,'必修'),
('9521103','c02',68,'选修'),
('9521103','c06',null,'必修'),
('9531101','c01',80,'选修'),
('9531101','c05',95,'必修'),
('9531102','c05',85,'必修');


-- //查询既不属于信息系也不属于数学系的学生的姓名和性别
select Sname,Ssex 
from Student 
where Sdept != "信息系" and Sdept != "数学系";
-- where Sdept <> "信息系" and Sdept <> "数学系";
-- where Sdept not in("信息系","数学系");


-- //查询数学系成绩在80分以上的学生的学号,姓名
-- //多表联查,内连接
select t1.Sno,t1.Sname
from Student as t1,SC as t2
where t1.Sno = t2.Sno 
and t1.Sdept <=> "数学系" and t2.Grade > 80;
-- //多表联查,内连接2
select t1.Sno,t1.Sname
from Student as t1
inner join SC as t2
on t1.Sno = t2.Sno 
and t1.Sdept <=> "数学系" and t2.Grade > 80;
-- //子查询
select Sname,Sno 
from Student 
where Sdept <=> "数学系" 
and 
Sno in(
    select Sno from Sc where Grade > 80
);


-- //查询计算机系考试成绩最高的学生的姓名
-- //多表联查
select Sname 
from Student as t1,SC as t2
where t1.Sno = t2.Sno 
and t1.Sdept <=> "计算机系"
order by t2.Grade desc 
limit 1;


-- //删除计算系不及格学生的选课记录
-- 子查询
delete from SC 
where Grade < 60 
and Sno in(
    select Sno from Student where Sdept <=> "计算机系"
);