-- 1. 数据库的创建与删除  
create database student; 
use student;
drop database student;

-- 2. 模式的创建与删除  
--create schema ty;
--drop schema ty;

-- 3. 表的创建和修改及删除  
create table Student
(
	Sno char(9) primary key,
	Sname varchar(20) unique,
	Ssex char(2),
	Sage smallint,
	Sdept char(20)
);

--create table TTY.Student
--(
--	Sno char(9) primary key,
--	Sname varchar(20) unique,
--	Ssex char(2),
--	Sage smallint,
--	Sdept char(20)
--);

--drop table TTY.Student;

--drop schema TTY ;

create table Course
(
	Cno char(4),
	Cname char(40) not null,
	Cpno char(4),
	Ccredit smallint,
	primary key(Cno),
	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)
);

alter table Student add Entrance date;
alter table Student add unique(Entrance);
alter table Student drop constraint UQ__Student__1CC0725B1273C1CD; 
alter table Student alter column Entrance varchar(20);
alter table Student drop column Entrance;

drop table SC;
drop table Student;

-- 4. 索引的创建与删除

create unique index Courno on Course(Cno);
create CLUSTERED index Courno on Course(Cno);
drop index Course.Courno;

-- 5. 基本表插入数据
	-- A. 满足完整性约束  
	-- B. 主码不能取重复值
	-- C. 注意输入次序
-- 6. 简单查询  
-- ① 查询两列
select sname, sno
from student ;

-- ② 整个基本表
select *
from student;  

-- ③ 运算的表达式
select sname 姓名, 2020 - sage 出生年份
from student;

-- ④ 函数  
select *
from sc;

select MAX(grade) 最大成绩
from sc; 

-- ⑤ 字符串  
select sname , '2020' 年份
from student;  

-- ⑥ 取消重复
select *
from sc;
  
select distinct sno
from sc;

select COUNT(distinct sno) 
from sc;

-- 7. where条件 not  
-- ① 比较大小
 select sname, sage 
 from student  
 where not sage < 20;  
 
-- ② 确定范围  
select sname , sage 
from student 
where sage not between 20 and 23;

-- ③ 确定集合  
select sname , sdept 
from student 
where sdept   in ('MA','IS');

-- ④ 字符串匹配  
select sname 
from student 
where not sname not like '刘%';  

select sname
from student 
where sname like '欧阳__';

-- ⑤ 指明通配符 
select sname
from student
where sname like '%\_%' escape '\';

-- 8. 空值查询  
select *
from course 
where cpno is NULL;  

select sno, cno, grade 
from sc
where grade is not null;

-- 9. 多重条件查询  
select sname, sage, sdept
from student
where sdept = 'CS' or sage < 20 ;

-- 10. 排序  
select sno, grade 
from sc
where sno = '200215121'
order by grade asc;

select *
from student 
order by sdept , sage desc;

-- 11. 聚集函数  
select *
from sc;

select COUNT(distinct sno)
from sc;

select *
from sc;

select AVG(grade)
from sc
where cno = '1';

select SUM(ccredit)
from sc, course
where sno = '200215121' and sc.cno = course.cno;


select *
from sc;

select MIN(grade)
from sc;

select MAX(grade)
from sc;

select * 
from sc
where  grade > AVG(grade);  -- error

-- error
SELECT sno, MIN(grade)
FROM sc

select sno, grade 
from sc
where grade in (select MIN(grade)
                 from sc);



-- 12. 分组查询  
select cno ,COUNT(sno)
from sc
group by cno;

select sno
from sc 
group by sno  
having COUNT(*) >= 2;  


SELECT MIN(grade)
FROM sc

select student.*,cno,grade 
from student, sc
where student.sno = sc.sno;


-- 13.连接查询  
-- a. 等值连接  
select student.*, sc.*
from student , sc
where student.sno = sc.sno;  

-- b. 自然连接  
select sc.sno, sname, ssex, sage, sdept,cno, grade
from student , sc
where student.sno = sc.sno;  

select student.*,cno, grade
from student , sc
where student.sno = sc.sno;  

-- c. 连接相关的复合操作
-- 2号课程成绩大于80分的学生学号、姓名  
select student.sno , sname  
from student , sc
where student.sno = sc.sno
      and  cno = '2'
      and  grade > 80; 
      
-- d. 自身连接  
-- 查询课程的先修课的先修课
      
select F.cno, S.cpno
from course F, course S 
where F.cpno = S.cno;

-- e. 外连接  

select course.* , sno, grade 
from course, sc 
where course.cno = sc.cno;

select course.* , sno, grade 
from course left outer join sc
on course.cno = sc.cno;

-- f.多表连接  
-- 查询学号、姓名、课程名、成绩   
select student.sno, sname, cname, grade 
from student, sc, course 
where student.sno = sc.sno
      and sc.cno = course.cno;
      
      
-- 14. 带有in谓词的子查询  
-- a. 查询与“刘晨”在同一个系学习的学生。  
-- a1. 嵌套查询 
select sno, sname, sdept
from student
where sdept in(select sdept
			   from student
			   where sname = '刘晨');
	  -- and sname <> '刘晨';
-- a2. 连接查询  
select s2.sname,s2.sno, s1.sdept
from student s1, student s2
where s1.sdept = s2.sdept  
      and s1.sname = '刘晨';
      
 
 -- b.  查询选修了课程名为“信息系统”的学生学号和姓名。 
 -- b1. 嵌套查询      
select Sno,Sname 
from Student
where Sno in (select Sno 
             from SC 
             where Cno in( select Cno 
						   from Course
						   where Cname='信息系统'));
-- b2. 连接查询 						   					   
select student.sno,sname
from student ,sc ,course
where student.sno=sc.sno
      and course.cno=sc.cno
      and course.cname ='信息系统'; 
      
-- 15. 带有比较运算符的子查询 
-- a. 查询与“刘晨”在同一个系学习的学生。  
select sno, sname, sdept
from student
where sdept = (select sdept
			   from student
			   where sname = '刘晨');
			   
			   
-- b. 找出每个学生超过他选修课程平均成绩的课程号。 
select sno, cno 
from sc x
where grade >= (
	select AVG(grade)
	from sc y
	where x.sno = y.sno);
	
	
-- 16. 带有any或all谓词的子查询  	
-- a.查询其他系中比信息系(IS)任意一个(其中某一个)学生年龄小的学生姓名和年龄。
-- a1. 使用any或all谓词 
select sname, sage, sdept
from student
where sage < any(
		select sage 
		from student
		where sdept = 'IS')
	  and sdept <> 'IS';
	  
-- a2. 使用聚集函数 
select sname, sage, sdept
from student
where sage < (
		select max(sage)
		from student
		where sdept = 'IS')
	  and sdept <> 'IS';
	  
-- b. 查询其他系中比信息系(IS)所有学生年龄小的学生姓名和年龄。
-- b1. 谓词all
select sname, sage, sdept
from student
where sage < all (
		select  sage 
		from student
		where sdept = 'IS')
	  and sdept <> 'IS';  
-- b2. 聚集函数
select sname, sage, sdept
from student
where sage < (
		select  MIN(sage)
		from student
		where sdept = 'IS')
	  and sdept <> 'IS';  
	  
-- 17. 带有exists谓词的子查询 
-- a. 查询所有选修了1号课程的学生姓名。   
select sname
from student 
where exists( select *
              from sc
              where sno = student.sno
              and cno = '1')
              
-- b. 查询没有选修1号课程的学生姓名。  
select sname
from student 
where not exists( select *
              from sc
              where sno = student.sno
              and cno = '1')
-- 18. 不同形式查询的替换  
-- a. 查询与“刘晨”在同一个系学习的学生。  
-- a1. 其他查询
select sno, sname, sdept
from student
where sdept = (select sdept
			   from student
			   where sname = '刘晨');
-- a2. exists查询 
select sno, sname, sdept
from student s1
where exists (select *
              from student s2
              where s2.sdept = s1.sdept
                    and s2.sname = '刘晨')
      and sname != '刘晨';
-- b. 查询选修了全部课程的学生姓名  
-- (没有一门课程是他不选的学生姓名)  student course sc 
select sname
from student
where not exists( select *
                  from course
                  where not exists( select *
                                    from sc 
                                    where sno = student.sno
                                      and cno = course.cno
                                     ));
-- c. 查询至少选修了学生201215122选修的全部课程的学生号码。  
-- ( 不存在这样的课程y，学生201215122选修了y，而学生x没有选。)
select distinct sno
from sc scx
where not exists( select *
                  from sc  scy
                  where sno = '201215122'
                        and not exists( select *
                                        from sc scz
										where sno = scx.sno
                                      and scy.cno = scz.cno
                                     )); 
                                     
-- 19.集合查询 
-- a. 集合并  
--  查询计算机科学系的学生及(or)年龄不大于19岁的学生   
select *
from student 
where sdept = 'CS'
union all
select *
from student
where sage <= 19;

-- b. 集合交   
-- 查询计算机科学系的学生与年龄不大于19岁的学生 交集  
select *
from student 
where sdept = 'CS'
intersect
select *
from student
where sage <= 19;

-- c. 集合差
                                                 
-- 查询计算机科学系的学生与年龄不大于19岁的学生 集合差  
select *
from student 
where sdept = 'CS'
except
select *
from student
where sage <= 19;

-- 20. 数据的插入   
-- a. 插入元组  
insert into student(sno, sname, ssex, sdept, sage)
values('201215128', '陈冬', '男', 'IS', 18);

insert into student(sno, sname, ssex, sdept )
values('201215130', '陈冬1', '男', 'IS' );


insert into student 
values ('201215136', '张冬', 18, '男', 'IS')
      ,('201215135', '李冬2', 18, '男', 'IS') ;
      
insert into sc 
values ('201215121', '1', 92)
      ,('201215121', '2', 85)
      ,('201215121', '3', 88) 
      ,('201215122', '2', 90) 
      ,('201215122', '3', 80)
      ,('201215125', '1', 96);
      
      
insert into student 
select '201215137', '王冬', 18, '男', 'IS'
union all select '201215138', '王冬1', 18, '男', 'IS'





select *
from student;
-- b. 插入子查询的结果  

-- b1. 创建一个表  
create table depage  
(
  sdept char(15),
  avgage smallint
);
-- b2. 插入子查询结果  
insert into depage(sdept, avgage)
    select sdept, AVG(sage)
    from student
    group by sdept;
    
select *
from depage;

-- 21. 数据修改  
-- a. 修改某个元组的值  
update student
set sage = 22
where sno = '201215121'; 

-- b. 修改多个元组  
update student
set sage = sage + 1;


update student
set sage += 1;

update student
set sage++; -- error 

-- c. 带子查询的修改语句  
update sc
set grade = 0
where sno in (select sno 
              from student
              where sdept = 'CS');
              
select *
from sc;

-- 22.删除数据  
-- a. 删除一个元组  
select *
from student;

delete 
from student
where sno = '201215128';

--b. 删除多个元组  
delete 
from depage;  

drop table depage;

-- c. 带有子查询的删除语句  

delete 
from sc
where sno in (select sno
              from student
              where sdept = 'CS');
              
              
              
 
              
-- 23.视图的创建与删除  
-- a. 创建 
-- a1.  建立信息系学生的视图包括学号 姓名 年龄 院系。

create view is_student
as
select sno, sname, sage, sdept
from student
where sdept = 'IS';

select *
from is_student;

create view is_student9
as
select sno, sname, sage, sdept
from student
where sdept = 'IS'
with check option;  

insert into is_student9
values ('201215142', '李四', 18, 'IS');


--a2.建立信息系选修了1号课程的学生视图。
create view is_s1(sno, sname, grade)
as
select student.sno, sname, grade
from student, SC
where student.sno = SC.sno
      and sdept = 'IS'
      and cno = '1';
      
select *
from is_s1;

insert into sc 
values ('201215125', '1', 96);

-- a3. 建立信息系选修了1号课程且成绩在90分以上的学生视图。
create view is_s2 
as
select *
from is_s1
where grade >= 90;

select *
from is_s2;



-- a4,将学生的学号及他的平均成绩定义为一个视图。

create view s_g(sno, gavg)
as
select sno, AVG(grade)
from SC
group by sno;

select *
from s_g;

--b 删除视图  
--b1 直接删除  
drop view is_s1;

--b2 删除基本表对视图的影响  
drop table sc; 


--24. 视图查询  
--a. 在信息系学生的视图中找出年龄小于20岁的学生。
select sno, sage
from is_student
where sage < 20;

-- 视图消解法
select sno, sage
from student
where sdept = 'IS'
      and sage < 20;
      
      
-- b. 在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。
select *
from s_g
where gavg >= 90;

-- 视图消解法  
select sno, AVG(grade) 
from SC
group by sno
having AVG(grade) >= 90;

-- 25.更新视图  
-- a 将信息系学生视图IS_Student中学号201215122的学生姓名改为“刘辰” 。
update is_student
set sname = '刘辰'
where sno = '201215122';

-- b 视图S_G为不可更新视图
update s_g
set gavg = 90
where sno = '201215121'; --error

-- 26. 创建登录  
-- a. 创建登录 
create login U1
with password = '123456';

create login U2
with password = '123456';

create login U3
with password = '123456';

-- 27. 创建用户   
create user use1
for login U1;

create user use2
for login U2;

create user use3
for login U3;

--28. 授权  

grant select 
on student 
to use1;


grant all privileges
on course 
to use2, use3;

grant update 
on student 
to use1;

grant select 
on student 
to use1
with grant option;

grant select 
on sc
to public;

--29. 权限回收  
revoke select 
on sc
from public;


revoke select
on course 
from use2;

revoke select
on student
from use1 CASCADE;

--30. 删除用户和登录  
-- a. 手动删除(保证该用户或登录没占用)  
-- b. SQL语句 

drop login U3;  
drop user use3;