数据库基础知识（二）

建表

create table student(
    sno numeric(9) primary key,
    sex char(2) check(sex in('男','女')),
    varchar(100) not null,
    sage smallint unique ,
    check (sex=“女” or sname not like 'Ms.%')
);


create table sc(
    sno char(9) not null,
    cno char(4) not null,
    grade smallint check(grade>=0 and grde<=100),
    primary key(sno,cno)m
    foreign kery(sno) references student(sno)
);

完整性约束命名句子
create table Student(
    Sno numeric(6)
    constraint c1 check(sno betweent 1000 and 9999),
    sname char（20）
    constraint c2 not null,
    constraint StudnetKey primary key(sno)
);

修改表中的完整性限制
alter table student drop constraint c1;
alter table student add constraint c1 check(sno between 100 and 300);

域中的完整性限制
    建立一个性别域GenderDomain，并对其中的限制命名
    create DOMAIN GenderDomian CHAR(2)
        contraint GD check(value in ('男','女')
    );

修改数据

将学生2002的年龄改为22岁
update student set sage=22 where sno='2002';
将所有学生的年龄加1
update student set sage=sage+1;
带子查询的修改语句
update sc set grade=0 where 'cs'=(
    select sdept from student where student.sno=sc.sno);

修改表

向student表中增加“入学时间”列，其数据类型为日期型
alter table student add S_entrance date;

将年龄的数据类型由字符类型由字符型改为整型。
alter table student 
    alter column sage int;
增加课程名称必须取唯一值的约束条件
    alter table course add unique(cname);

插入
insert into student(sno,sname,ssex,sdept,sage)values('2001','blue'.'male','IS',18);

insert into sc(sno,cno) values('2002','1');

insert into scvalues('2002','1',null);
插入子查询结果
    对每一个系，求学生的平均年龄，并把结果存入数据库
    insert into Dept_age(Sdept,Av_age)  selcet Sdept,AVG(Sage) from student group by student;

删除

删除学号为111的学生记录
delete from student where sno="111";
删除所有的学生选课记录
delete from sc;
删除计算机系所有学生的选课记录
delete from sc where 'sc'=(
    select sdept from student where student.sno=sc.sno);    

单表查询

查询全体学生的学号和姓名
select sno,sname from student;
查询所有学生的详细信息
select * from student;
查询经过计算的值
    查询全体学生的姓名及其出生年份
    selecet sname,2004-sage from student;
查询全体学生的姓名、出生年份、和所在的郧西，并要求用小写字母标识所有系名。
select sname,'Year of Birth':,2004-sage,lower(sdept) from student;
用户可以通过制定别名来改变查询结果(Birth不存在，会自动创建)
select sname Name,'Year of Birth:' Birth,2004-sage BIRTHDAY,lower(sdept) DEPARTMENT from student;

查询不重复的数据
select distinct sname from student;
查询重复数据
select All sname from student;//all是默认的格式，可以不加

查询计算机系学生的名单
select sname from student where sdept="CS";
查询所有年龄在20岁以下的学生
select * from student where sage<20;
查询年龄在20-30之间的学生
select * from student where sage between 20 and 30;
查询年龄不在20-30之间的学生
select * from tudent where sage not betweent 20 and 30;
确定集合
    查询计算机系（CS）和数学系（MA）学生。
    select * from student where sdept in('CS','MA');
    与in相对应的是not in
    查询计不是算机系（CS）和数学系（MA）学生。
    select * from student where sdept not in('cs','ma');
字符匹配
    查询学号为200215121的学生的详细信息
    select * from student where sno like '200215121';
    查询以列开头的字符
        查询所有姓刘的学生
        select * from student where sname like '刘%';
    查询一个汉字两个字符
        查询姓‘欧阳’且全名为3个汉字的学生的姓名
        select sname from student where sname like '欧阳__';
    查询名字中第2个字为“阳”字的学生的姓名和学号。
    select sname.sno from student where sname like'__阳%';
    查询所有不姓刘的学生
    select * from student where sname not like'刘%';
字符转义
    查询DB_Dsign课程的课程号和学分
    select cno ,ccredit from course where cname like 'DB\_Design' escape '\';
    查询以"DB_"开头，且倒数第 3个字符为i的课程的详细情况
    select * from course where cname like 'DB\_%i__' escape '\';
查询缺少成绩的学生
select * from student where grade is null;
查询有成绩的学生
select * from student where grade is not null;
查询计算机系年龄在20岁以下的学生
select sname from student where sdept="cs" and sage<20;
order by字句
    查询选修了3号课的学生的成绩并降序显示
        select * from sc where cno=3 order by grade desc;
    查询选修了3号课的学生的成绩并按系升序，同系降序显示
        select * from sc where cno=3 order by sdept,grade desc;
聚集函数
    查询学生总数
    select count(*)  from sc;
    查询选修了课程的人数
    select count(distinct sno) from sc;
    计算1号课程的学生的平均成绩
    select avg(grade) from sc where cno='1';
    查询学生200215012选修课程的总学分数
    select sum(ccredit) from sc,course where sno='200215012' and sc.cno=course.cno;
group by 字句
    求各个课程号及相应的选课人数
        select cn,count(sno) from sc group by cno;
    查询选修了3门以上课程的学生学号
        select sno from sc group by sno having count(*)>3;

多表连接
    查询每个学生及其选修课程的情况
        select student.*,sc.* from student,sc where student.sno =sc.sno;
        若在等值连接中把目标列中重复的属性列去掉则为自然连接
自身连接
    查询每一门课的间接先修课
    select first.con,second.cpno from course first course second where fisrt.cpno=second.cno;
外连接
select student.sno,sname,ssex,sage,sdept,cno,grade
from student left join sc on (student.sco=sc.sno);
符合条件连接
    查询选修2号课程且成绩在90分以上的所有学生
    select student.sno,sname from student.sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90;
多表连接
    查询每个学生的学号、姓名、选修的课程名及成绩  
    select Student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno=course.sno;    
    嵌套查询
    select snane from student wher  sno in(
        select sno from sc where
        cno='2');
    查询与“刘晨”在同一个系学习的学生。
    select sno,sname,sdept from student where sdept in (
        select sdept from student where sname="刘晨");
    另一种写法：二
    select sno,sname,sdept from student where sdept in ("CS");
    另一种写法：三
    select s1.sno,s1.sname,s1.sdept from student s1.student s2 where s1.sdept=d2.sdept and s2.snmae="刘晨";

    查询讯修了课程名为信息系统“”的学生学号和姓名。
    select sno,sname from studnet where sno in(
        select sno where cno in(
相关子查询
    select sno,sname,sdept from student where sdept(
        select sdept from studnet where sname="刘晨");
    找出每个学生超过他选修课程平均成绩的课程号
    select sno,cno
        from sc x
             where grader>=(
                 select avg(grade)
                     from sc y
                         where y.sno=x.sno;
带有any或all谓词的子查询
    查询其他系中比计算机系某一学生年龄小的学生姓名和年龄
    select sname,sage from student where sage<ANY(
        select sage from student where sdept="cs");
    查询其他系中比计算机系所有学生年龄小的学生姓名和年龄
        select sname,sage from student where sage<ALL(
        select sage from student where sdept="cs");     
查询所有选修了1号课程的学生姓名
    select sname from student where exists(
        select * from sc where sno=student.sno and cno='1');
查询没有选修了1号课程的学生姓名
select sname from student where not exists(
        select * from sc where sno=student.sno and cno='1');

视图

定义视图
建立信息系学生的视图
create view IS_Student 
as
select sno,sname,sage from student where sdept="IS";

建立信息系学生的视图，并要求进行修改和插入操作时仍需要保证该视图只有信息系的学生
create view IS_Student 
as select sno,sname,sage from studnet where sdept="IS" 
WITH CHECK ON;

视图上建立视图
create view IS_S2
as
 select sno,sname,grade from IS_S1 where grade>=90;
删除视图
 drop view IS_S1;
查询视图
在信息里学生的视图中找出年龄小于20岁的学生。
select sno,sage from IS_Student where sage<20;
更新视图
update IS_Student
set Sname="刘辰" wher sno='2002';
增删改查的操作都与表相同。

授权与回收

授予
    把查询student表的权限授给用户U1
    grant select 
    on talbe student
    to u1;

    把对student表和course表的全部操作权限授予用户U2和U3
    grant all privileges
    on talbe studnet,course
    to U1,U3;

    把对表Sc的查询权限授予所有用户
    grant select on table sc to public;

    把对表SC的insert权限授予U5用户，并允许将此权限再授予其他用户。
    grant insert on table sc
    to U5
    with grant option;
回收
    把用户U4修改学生学号的权限收回
    revoke update(sno) on table Student from U4;
    收回所有用户对表SC的查询权限
    revoke select on tallbe sc xfrom public;
    把用户U5对SC表的insert权限回收
    revoke insert on table sc from U5 cascade;

审计

对修改Sc表结构或修改SC表数据的操作进行审计
audit alter,update on sc;
取消对SC表的一切审计
noaudit alter,update on sc;

建立触发器自动更新数据

create table sal_log（
    Eno numeric（4） references teacher(Eno),
    Sal numeric(7,2),
    Username char(10),
    Date timedstamp
）；
create TRIGGER inser_sal
        AFTER　INSERT ON teacher
            FOR EACH ROW
                AS BEGIN
                    insert into sal_log values(new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
                END;

create TRIGGER update_sal
            AFTER UPDATE ON teacher
                FOR EACH ROW
                    AS BEGIN
                        IF(new.Sal<>old.Sal)THEN
                             insert into sal_log values(
                                     new.Eno,new.Sal,CURRENT_USER,CURRENT_TIMESTAMP);
                        END IF;
                    END;

删除触发器
drop trigger inser_sal on teacher;