数据库基础知识（二）

建表

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;