数据库学习记录（下）

前言：某牛同学看了要考满分(＾－＾)V

索引

1.创建索引
create unique index 索引名 on 表名
可以选择ASC（升序），DESC（降序）

2.修改索引
alter index 旧索引名 rename to 新索引

3.删除索引
drop index 索引名

-- 为Student表的sname列创建唯一索引（索引名：idx_student_sname）
create unique index idx_student_sname on student（sname);

-- 为sc表的grade列创建降序索引（索引名：idx_sc_grade）
create index idx_sc_grade on sc(grade desc);

-- 为course表的ccredit列创建升序索引（索引名：idx_course_cname）
create index idx_course_cname on course(ccredit asc);

约束

完整性约束子句

constraint 约束名 约束条件

-- 增加约束
sno varchar(20) constraint c1 check(sno between 900 and 999)

-- 删除约束条件
alter table student drop constraint c1;

修改表中的完整性限制

-- 为Student表的Sage列添加约束，使其取值小于30岁(约束名：stu_chk_sage)
alter table student add constraint stu_chk_sage check(sage<30);

-- 为student表的Ssex列添加约束，使其只能取值‘m’或‘f’(约束名：stu_chk_ssex)
alter table student add constraint stu_chk_ssex check(ssex in('m','f'));

-- 为Student表的ssex列添加缺省约束，缺省值为‘m’(约束名：stu_def_ssex)
alter table Student add constraint stu_def_ssex default 'm' for ssex;

-- 为SC表的sno列添加外码（约束名：stu_ref_sno）
alter table sc add constraint stu_ref_sno foreign key(sno) references student(sno);

-- 为SC表的cno列添加外码（约束名：stu_ref_cno）
alter table sc add constraint stu_ref_cno foreign key(cno) references course(cno);

-- 为SC表的grade列添加检查约束（1到100分）（约束名：stu_chk_grade）
alter table sc add constraint stu_chk_grade check(grade between 1 and 100);

-- 为Course表的cname列添加唯一约束
alter table course add constraint Course_un_cname unique(cname);

---

视图

子查询视图

create view 视图名
as
select 列名
from 表名
where 判断条件

-- 建立信息系选修了1号课程的学生的视图
create view is_si(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 bt_s(sno,sname,sbirth)
as
select sno,sname,2014-sage
from student;

分组视图

create view sg(sno,gavg)
as
select sno,avg(grade)
from sc
group by sno;

删除视图

drop view 视图名;
drop view sg;

查询视图

create view is_student
as
select *
from student
where sdept='SC';

-- 查询信息系学生视图中年龄小于20的学生
select sno,sage
from is_student
where sage<20;

-- 查询选修了1号课程的信息系学生
select is_student.sno,sname
from is_student,sc
where is_student.sno=sc.sno and sc.cno='1';

-- sg视图的子查询
select *
from sg
where gavg>=90;
相当于：
select sno,avg(grade)
from sc
group by sno
having age(grade)>=90;

视图的增删改查

-- 将信息系学生视图is_student中学号为"201215122"的学生姓名改为"l刘辰".
update is_student
set sname='刘辰'
where sno='201215122';

-- 增加数据
insert into is_student
values('2020110265','小天才',20);

-- 删除数据
delete
from is_student
where sno='2020110265';

-- 创建vsumc视图，使其包含每个学生的获得的学分（成绩及格才能得学分）。列出学号和总学分（列名：ssumc）
create view vsumc(sno,ssumc)
as
select sno,sum(ccredit)
from sc,course
where grade>=60 and sc.cno=course.cno
group by sno;

-- 使用vsumc视图，查询男同学总学分高于12分的学生的学号，姓名，性别”的SQL语句
select sno,sname,ssex from student
where ssex='m' and sno in(
    select sno 
    from vsumc
    where ssumc>12
)

-- 、使用vsumc视图，查询总学分最高的学生，列出学号，姓名，性别，年龄，所在系”的SQL语句
select *
from student
where sno in(
    select sno
    from vsumc
    where ssumc>=all(select ssumc from vsumc)
)

-- 使用vsumc视图，查询平均分大于等于65分的学生的学号，平均分(列名：savg)，总学分（列名：ssumc）”的SQL语句
select sc.sno,avg(grade) as savg,ssumc
from sc,vsumc v
where sc.sno=v.sno
group by sc.sno,ssumc
having avg(grade)>=65;

用户权限

grant select/update/insert
on 表名
to 用户

-- 为utest用户赋予student表的查询权限”的SQL语句
grant select
on student
to utest

-- 收回utest对student表的修改权限”的SQL语句
revoke update
on student
to utest