07.30周末作业

 

 

 

 

 

 

 

-- 第一题创建三张表
use lianxi;
create table Student(
Sno varchar(7) primary key,
Sname varchar(10) not null,
Ssex varchar(4) check(Ssex='男' or Ssex='女'),
Sage int check(Sage>=15 and Sage<=45),
Sdept varchar(20) default'计算机系'
);

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

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

desc Student;
-- 二,修改表结构
alter table SC add XKLB char(4);

alter table SC modify column XKLB char(6);

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     Sno,Sname from Student ;

select     Sno,Sname,Sdept from Student ;

select * from student;

select Sno from SC where XKLB="选修";

select Sname from Student where Sdept="计算机系";

select Sage,Sname from Student where Sage<20;

select Sno from SC where Grade<60;

select Sname,Sage,Sdept from Student where Sage between 20 and 23;

select Sname,Sage,Sdept from Student where Sage not between 20 and 23;

select Sname,Ssex from Student where Sdept not in("信息系","数学系","计算机系");

select Sname,Ssex from Student where Sdept in("信息系","数学系","计算机系");

select * from Student where Sname like "张%";

select Sname,Sno from Student where Sname like "%大%" or Sname like "%小%";

select Sname from Student where Sname like "刘%" or Sname like "张%"or Sname like "张%";

select Sname from Student where Sname not like "刘%";

select * from Student where Sno not like "%2" and Sno not like "%3" and Sno not like "%5";

select Sno,Cno from SC where Grade is null;

select Sno,Cno from SC where not Grade is null;

select Sname from Student where Sage<20;

select * from Student order by Sage;

select Sno,Grade from SC  where Cno="c02" order by Grade desc;

select * from Student order by Sdept,Sage desc;

select count(*) from Student;

select count(*) from SC group by XKLB having XKLB="选修";

select sum(Grade) from SC where Sno=9512101;

select avg(Grade) from SC where Cno="c01";

select max(Grade),min(Grade) from SC where Cno="c01";

select count(Cno),Cno from SC group by Cno;

select count(Cno),avg(Grade) from SC GROUP BY Cno;

select Sno from SC group by Sno having count(Sno)>3;

select count(Cno),avg(Grade) from SC group by Sno having count(Cno)>=4;

-- 四 多表连接查询

select * from Student inner join SC on Student.Sno=SC.Sno;

select distinct count(Cno),Sno from SC group by Sno;

select Student.Sname,Course.Cno,SC.Grade from SC inner join Student on SC.Sno=Student.Sno inner join Course on Course.Cno=SC.Cno where Student.Sdept="计算机系";

select Student.Sname,Course.Cname,SC.Grade from SC inner join Student on SC.Sno=Student.Sno inner join Course on Course.Cno=SC.Cno where Course.Cname="VB";

select Student.Sname,Student.Sdept from SC inner join Student on SC.Sno=Student.Sno inner join Course on Course.Cno=SC.Cno where Course.Cname="VB";

select Student.Sname,Student.Sdept from SC inner join Student on SC.Sno=Student.Sno inner join Course on Course.Cno=SC.Cno where Course.Cname="VB";

select distinct Student.Sname,Student.Sdept from SC right join Student on SC.Sno=Student.Sno where Student.Sdept="计算机系" and not Sname="刘晨";

select distinct Student.Sname,SC.XKLB,Course.Cname from SC right join Student on SC.Sno=Student.Sno left join Course on Course.Cno=SC.Cno group by Sname;

-- 子查询
select Sname from Student where Sdept="计算机系" and not Sname="刘晨";

select Sname,Sno from Student where Sno in (select Sno from SC where Grade>90);

select Sno,Sname from Student where Sno in (select Sno from SC WHERE Cno in (select Cno from Course where Cname="数据库基础"));

select Sno,Grade from SC WHERE Cno="c02" and Grade>(select avg(Grade) from SC where Cno="c02");

select Sname from Student where Sno in(select Sno from SC where Cno="c01");

select Sname,Sdept from Student where Sno in (select Sno from SC where not Cno="c01");

select Sname,Sdept from Student where Sno in (select Sno from SC where Cno="c01");

select Sno,Sname from Student where Sdept="数学系" and Sno in(select Sno from SC where Grade>80);

select Sname from Student where Sdept="计算机系" and Sno in(select Sno from SC order by Grade desc ) limit 0,1

-- 插入数据

insert into Student value(9521105,"陈冬","男",18,"信息系")

insert into SC value (9521105,"c01",null,null)

-- 更新数据
update Student set Sage=Sage+1;

update Student set Sage=21 where Sno=9512101;

SELECT Sno from Student where Sdept="计算机系";

update SC set Grade=Grade+5 where Sno in (SELECT Sno from Student where Sdept="计算机系");

-- 删除数据
delete from SC;

delete from SC where Grade<60

delete from SC where Sno in (SELECT Sno from Student where Sdept="计算机系")and Grade<60;