MySQL实验06

create database SQL06;
use SQL06;
CREATE TABLE student
(sno CHAR(9) PRIMARY KEY,
sname CHAR(20),
ssex CHAR(2),
sage SMALLINT,
sdept CHAR(20));

CREATE TABLE course
(cno CHAR(4) PRIMARY KEY,
cname CHAR(40),
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(cno)REFERENCES course(cno),
FOREIGN KEY(sno)REFERENCES student(sno));

INSERT INTO student VALUES
('2019001','陈武','男',19,'CS'),
('2019002','潘璋','女',19,'CS'),
('2019003','蒋钦','女',18,'CS'),
('2019004','甘宁','女',19,'IS'),
('201215126','周泰','女',18,'CS'),
('201215200','徐盛','男',17,'IS'),
('201215140','丁奉','男',18,'IS');

INSERT INTO course VALUES
('1','数据库',NULL,4),
('2','数学',NULL,2),
('3','信息系统',NULL,4),
('4','操作系统',NULL,3),
('5','数据结构',NULL,4),
('6','数据处理',NULL,2);

INSERT INTO sc VALUES
('2019001','1',92),
('2019001','2',90),
('2019002','3',95),
('2019002','2',90),
('2019003','3',80),
('2019002','1',90),
('2019003','1',59),
('201215126','2',59);

insert into SC(Sno,Cno,Grade)
VALUES('201215126','1',NULL);

update student
set sdept =null
where Sno='201215200';

select *from student
WHERE Sname IS NULL or ssex IS NULL or sage IS NULL or sdept is null;

select sno from sc
where grade <60 and cno='1';

select sno from sc
where grade <60 and cno='1'
union select sno from sc where grade is null;

create view IS_Student
AS
select sno,sname,sage
from student 
where sdept='IS';

create view IS_S1(Sno,Sname,Grade)
AS
select student.Sno,sname,grade
from student,sc
where sdept ='IS'
AND
student.sno=SC.sno and sc.cno=1;

create view IS_S2
AS
select sno,sname,grade
from IS_S1
where grade<90;

create view BT_S(Sno,Sname,Sbrith)
AS
select Sno,Sname,2023-sage
from student;

create view S_G(Sno,Gave)
as
select sno,avg(grade)
from sc
group by sno;

create view f_student(f_sno,name,sex,age,dept)
as
select * from student
where ssex='女';

drop view bt_s;
drop view IS_s1;

select sno,sage
from is_student
where sage<20;

update is_student
set sname='孙权'
where sno ='201215140';

insert into student values('201215129','孙策','男',20,'IS');

DELETE FROM IS_Student WHERE Sno = '201215129';