SQL语句
内容
.
1,模式的定义与删除
(1)定义: create schema <模式名> authorization <用户名>;
在创建模式的同时在这个模式定义中进一步创建基本表、视图,定义授权。
create schema <模式名> authorization <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>];
例子: create schema test authorization zhang
create table tab1(col1 smallint,
col2 int,
col3 char(20),
col4 numeric(10,3),
col5 decimal(5,2)
);
(2)删除:drop schema <模式名> <cascade|restrict>;
cascade:级联,删除模式并且删除模式下的表和视图;restrict:限制,如果模式下有表或视图,则不能进行删除操作。
2.基本表的定义、删除与修改
(1)定义基本表:create table <表名> (<列名> <数据类型> [列级完整性约束条件],
<列名> <数据类型> [列级完整性约束条件],
.......
[<表级完整性约束条件>]);
例子:create table 模式名.Student
(Sno char(9) primary key,
Sname char(20) unique,
Ssex smallint,
Sdept char(20));
-------------------------------------------------------------
例子:create table 模式名.Course
(Cno char(4) primary key,
Cname char(40) not null,
Cpno char(4),
Ccredit smallint,
foreign key (Cpno) references Course(Cno)
);
-------------------------------------------------------------
例子:create table 模式名.SC
(Sno char(9),
Cno char(4),
Cpno char(4),
Grade smallint,
primary key(Sno,Cno),
foreign key (Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);
(2)修改基本表:alter table <表名>
[add [column] <新列名><数据类型> [完整性约束]]
[add <表级完整性约束名>]
[drop [column] <列名>[cascade|restrict]]
[drop constraint<完整性约束名>[cascade|restrict]]
[alter column <列名> 数据类型];
例子:向Student表增加“入学时间”列,其数据类型为日期型。
alter table Student add S_entrance date;
例子:将年龄的数据类型由字符型改为整数。
alter table Student alter column Sage int;
例子:增加课程名称必须取唯一值得约束条件。
alter table Course add unique(Cname);
(3)删除基本表:drop table <表名> [cascade|restrict];
例子:删除Student表,默认是restrict(限制)。
drop table Student cascade;
3.数据查询
一般格式:select [all|distinct]<目标列表达式>,[<目标列表达式>]....
from <表名或视图名>,[<表名或视图名>....]|(<select 语句>)[as]<别名>
[where <条件表达式>]
[group by <列名1>[having<条件表达式>]]
[order by <列名2>[asc|desc]];
解释:根据where子句的条件表达式从from子句指定的基本表、视图或派生表中找出满足条件的元组,再按select子句的目标列表表达式选出元组中的属性值形成结果表。
如果有group by子句,则将结果按<列名 1>的值进行分组,该属性列值相等的元组为一组。如果有having短语,则分的组要满足短语条件才能输出。
如果有order by子句,则结果表要按<列名2>的值得升序或降序排序。
(1)单表查询
1)选择表中的若干列
例子1:查询指定列
查询全体学生的姓名、学号、所在系:select Sname, Sno, Sdept from Student;
例子2:查询全部列
查询全体学生的详细记录:select * from Student;等价于 select Sno,Sname,Ssex,Sage,Sdept from Student;
例子3:查询经过计算的值
查询全体学生的姓名及其出生年份:select Sname,2019-Sage from Student;
查询全体学生的姓名、出生年份、所在的院系,要用小写字母表表示系名,指定别名来改变查询结果的标题,在结果中加入一个新列为Year of Birth:
select Sname neam,'Year of Birth:' birth ,2019-Sage birthday,lower(Sdept) department from Student;
2)选择表中的若干元组
例子1:消除取值重复的行
查询选修了课程的学生的学号:select distinct Sno from SC;
例子2:查询满足条件的元组
比较大小:查询计算机科学系全体学生名单:select Sname from Student where Sdept='CS';
比较大小:查询所以年龄在20岁及以下的学生姓名及年龄:select Sname,Sage from Student where Sage<=20;
确定范围:查询年龄在20-23岁之间的学生姓名、系别和年龄:select Sname,Sdept,Sage from Student where Sage between 20 and 23;
确定集合:查询计算机系CS、数学系MA、信息系IS学生的姓名和性别: select Sname,Ssex from Student where Sdept in('CS','MA','IS');
字符匹配:[not] like '<匹配串>' [escape'<换码字符>']
查询所以姓刘的学生的姓名、学号和性别:select Sname,Sno,Ssex from Student where Sname like '刘%'';
查询所以姓欧阳并且名字长度为三个字的的学生的姓名、学号:select Sname,Sno from Student where Sname like '欧阳_';
涉及空值查询:查询所有有成绩的学生的学号和课程号:select Sno,Cno from SC where Grade is not null;
多重条件查询:where and or
3)order by 子句----asc升序(默认)、desc降序
例子:查询全体学生情况,查询结果按所在系号升序排列,同一系中学生按照年龄降序排列:select * from Student order by Sdept,Sage desc;
4)聚类函数----处count( )外,只处理非空值,空值元组跳过
例子1;查询学生总人数:select count(*) from Student;
例子2:查询选修了课程的学生的人数:select count(distinct Sno) from SC;
例子3:计算选修1号课程的学生平均成绩:select avg(Grade) from SC where Cno='1';
例子4:查询选修1号课程的学生最高分数:select max(Grade) from SC where Cno='1';
例子5:查询学生201215012选修课程的总分数:select sum(Ccredit) from SC,Course where Sno='201215012' and SC.Sno=Course.Cno;
5)group by子句
例子1:求各个课程号及相应的选课人数:先按照课程Cno分组,在用聚类函数分别作用于每个分组:
select Cno,count(Sno) from SC group by Cno;
例子2:查询选修了三门以上课程的学生学号:select Sno from SC group by Sno having count(*)>3;
(2)连接查询---等值连接、自然连接、自身连接、外部连接、复合条件连接查询等,连接查询的where子句
1)等值连接与非等值连接查询
例子1:查询每一个学生及其选修课程的情况:select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno;
例子2:若在等值连接中把目标列中重复的属性列取表则为自认连接,对例子1进行自然连接
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student,SC where Student.Sno=SC.Sno;
例子3:复合条件连接-----查询选修2号课程且成绩在90分以上的所以学生的学号和姓名:
select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Sno='2'and SC.Grade>90;
2)自身连接-需要给一张表起别名
例子1:查询每一门课的间接先行课(即先行课的先行课)
select C1.Cno,C2.Cpno from Course C1,Course C2 where C1.Cpno=C2.Cno;
3)外连接----将悬浮元组保留在结果集中,用null表示
例子1;改写1)的例子1:
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student left outer join SC on (Student.Sno=SC.Sno);
4)多表连接
例子1: select Student.Sno,Sname,Cname,Grade from Student,Course,SC where Student.Sno=SC.Sno and SC.Cno=Course.Cno;
(3)嵌套查询---在SQL语言中,一个select-from-where语句成为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having断句的条件中的查询称为嵌套查询
例子: select Sname from Student where Son in (select Sno from SC where Cno='2');
1)带in谓词的子查询
例子1:查询与“刘晨”在同一个系学习的学生
select Sno,Sname,Sdept from Student where Sdept in (select Sdept from Student where Sname='刘晨');
2)带有比较运算符的子查询
例子1:找出每个 学生超过他自己选修课程平均成绩的课程号
select Sno,Cno from SC x where Grade >=(select avg(Grade) from SC y where y.Sno=x.Sno);
3)带any(some)或all谓词的子查询
例子1:查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生的姓名和年龄
select Sname,Sage from Student where Sage<any(select Sage from Student where Sdept='CS') and Sdept<>'CS';
4)带有exists谓词的子查询---返回的是逻辑值,是否满足集合的条件,为where提供真假
例子1:查询所有选修了1课程的学生的名字:
select Sname from Student where exists(select * from SC where Sno=Student.Sno and Cno='1');
(4)集合查询---对select的结果集进行集合操作并操作union、交操作intersect、差操作except
例子:查询计算机科学系的学生与年龄不大于19岁的学生的差集:
select *from Student where Sdept='CS'except select* from Student where Sage<=19;
等价于 select *from Student where Sdept='CS'and Sage<=19;
(5)基于派生表查询---子查询不仅可以出现在where子句中,还可以出现在from子句中,这时子查询生成的临时派生表成为主查询的查询对象
例子:例如(3)4)例子1中的:查询所有选修了1课程的学生的名字:
select Sname from Student,(select sno from SC where Cno='1') as SC1(Sno) where Student.Sno=SC1.Sno;
4.数据更新
(1)插入数据
1)插入元组
例子1:将一个新的元组(学号:201215128,姓名:陈东,性别:男,所在系:IS,年龄:18岁)插入Student表中
insert into Student(Sno.Sname,Ssex,Sdept,Sage) values('201215128','陈东','男','IS',18);
例子2:插入一条选课记录('201215128','1')
insert into SC(Sno,Cno) values('201215128','1')
2)插入子查询结果
例子1:对每一个系,求学生的平均年龄,并把结果存入数据库
首先在数据库中建立一个新表,其中一列存放系名,另一列存放相应的学生平均年龄
create table Dept_age (Sdept char(15) Avg_age smallint);
然后对Student表按系分组求平均年龄,再把系名和平均年龄存入新表中
insert into Dept_age(Sdept,Avg_age) select Sdept ,avg(Sage) from Student group by Sdept;
(2)修改数据
1)修改某一个元组的值
例子1:将学生201215121的年龄改为22岁
update Student set Sage=22 where Sno='201215121';
2)修改多个元组的值
例子1:将所有学生的年龄增减1岁
update Student set Sage=Sage+1;
3)带子查询的修改语句
例子1:将计算机科学系全体学生的成绩设置为零
update set Grade=0 where Sno in (select Sno from Student where Sdept='CS');
(3)删除数据
1)删除某一个元组的值
例子1:删除学号为201215128的学生记录
delete from Student where Sno='201215128';
2)删除多个元组的值
例子1:删除所有的学生选课记录
delete from SC;
3)带子查询的删除语句
例子1:删除计算机科学系所有学生的选课记录
delete from SC where Son in(select Son from Student where Sdept='CS');
5.空值判断
(1)空值的产生
插入元组的时候有些属性值没有给出,或者用户自己主动设置的。
(2)空值的判断
在where中用 is null 、is not null来表示
6.视图---只是存储视图定义,而不是产生新表,只是逻辑上的虚表
(1)定义视图
1)建立视图
一般格式:create view <视图名> [(<列名>,<列名>...)] as <子查询> [with check option];
例子1:建立信息系学生的视图
create view IS_Student as select Sno,Sname,Sage from Student where Sdept='IS';
例子2:建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的同学
create view IS_Student as select Sno,Sname,Sage from Student where Sdept='IS' with check option;
例子3:建立信息系选修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';
例子4:建立信息系选修了1号课程且成绩在90分以上的学生的视图
create view IS_S2 as select Sno,Sname,Grade from IS_S1 where Grade>=90;
例子5:定义一个反应学生出生年份的视图
create view BT_S(Sno,Sname,Sbirth) as select Sno,Sname,2014-Sage from Student;
例子6:将学生的学号及平均成绩定义为一个视图
create view S_G(Sno,Gavg)as select Sno,avg(Grade) from SC group by sno;
2)删除视图
一般格式:drop view <视图名> [cascade];
例子1:删除视图BT_S和视图IS_SI:
drop view BT_S;
drop view IS_SI;
drop view IS_SI cascade;
(2)查询视图--视图消解(最终转化为对基本表的处理)
对视图的查询和在基本表上的处理一样,但是对于非行列子集视图,有些数据库不允许对视图的查询操作
(3)更新视图
对视图的更新和对基本表的更新一样,但是对于一些视图(可能是(不绝对):有聚类函数的,有表达式的,非行列子集视图等),有些数据库不允许对视图的查询操作
(4)视图的作用
多角度对待同一个数据
对机密数据提供安全保护
可以更加清晰地表达查询
7.数据库完整性
(1)实体完整性
设置表的主码:在列级定义:Sno char(9) primary key, ;在表级定义:primary key(Sno) ,最后定义。联合主码只能在表级定义 primary key(Sno,Cno)
(2)参照完整性
用foreign key短语来定义哪些列为外码,用references短语指明这些外码参照哪些表的主码。
例子1:定义表SC的实体完整性和参照完整性
create table SC
(Sno char(9)not null,
Cno char(4) not null,
Grade smallint,
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno)
on delete cascade
on update cascade,
foreign key(Cno) references Course(Cno)
on delete no action
on update cascade,
);
(3)用户定义完整性
在创建表的定义中定义属性的同时,可以根据应用要求定义属性上的约束条件,即属性值限制:列值非空(not null)、列值唯一(unique)、检查列值是否满足一个表达式(check短语)
例子1:Student表的Ssex只允许取“男”或者“女”
create table Student(Sno char(9)primary key,
Sname char(4) not null,
Ssex char(2) check(Ssex in('男','女')),
Sage smallint,
Sdept char(20))
例子2:SC表Grade的值应该在0—100之间
create table SC(Sno char(9)not null,
Cno char(4) not null,
Grade smallint,check(Grade>=0 and Grade<=100),
primary key(Sno,Cno),
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno));
例子3:当学生的性别是男时,其名字不能以Ms.打头
create table Student(Sno char(9)primary key,
Sname char(4) not null,
Ssex char(2) check(Ssex in('男','女')),
Sage smallint,
Sdept char(20),
check(Ssex ='女' or Sname not like 'Ms.%'))
当完整性约束不能满足时拒绝执行SQL语句
(4)完整性约束命名子句
constraint <定义完整性约束条件名> <完整性约束条件> 完整性约束条件有: not null、unique、primary key、foreign key、check
例子1:建立学生登记表Student,要求学号在90000-99999之间,姓名不能取空值,年龄小于30,性别只能是“男”或“女”。
create table Student
(Sno numeric(6)
constraint C1 check(Sno between 90000 and 99999),
Sname char(20)
constraint C2 not null,
Ssex char(2)
constraint C3 check(Ssex in('男','女')),
Sage numeric(6)
constraint C4 check(Sage<30),
constraint SKey primary key(Sno)
);
修改表中的完整性限制
例子1:修改Student表中的对性别的限制
首先删除Student表中的对性别的限制
alter table Student drop constraint C4;
再修改性别的限制
alter table Student add constraint C4 check(Ssex in('男','女',null));
(5)断言
断言建立后,任何对断言中所涉及关系的操作都会触发关系数据库管理系统对断言的检查,任何使用断言不为真值得操作都会被拒绝执行
1)创建断言的语句格式:create assertion <断言名> <check 子句> check中的约条件和where子句的条件表达式类似
例子1:限制数据库课程最多60名学生选修
create assertion ASSE_DB_NUM check(60>=(select count(*) from Course,SC where SC.Cno=Course.Cno and Course.Cname='数据库'));
当向SC表插入元组时,检查断言,当选课人数超过60,将拒绝插入
例子2:限制每个学期每门课最多60名学生选修
首先修改SC表的模式,增加一个“学期(TERM)”的属性
alter table SC add TERM date;
然后定义断言:
create assertion ASSE_SC_CNUM2 check(60>=all(select count(*) from SC group by Cno,TERM ));
2)删除断言的语句格式
drop assertion <断言名>;
(6)触发器(事件-条件-动作规则) 用户定义在关系表上的一类由事件驱动的特殊过程,对关系表的操作数据库服务器会自动的激活相应的触发器
1)定义触发器
SQL使用create trigger命令建立触发器,其一般格式为;
create trigger <触发器名>
{before|after} <触发事件> on <表名>
referencing new|old row as <变量>
for each{row|statement}
[when <触发条件>]<触发动作体>
例子1:当对表SC的Grade属性进行修改时,若分数增加了10%,则将此次操作记录到另一个表SC_U(Sno,Cno,Oldgrade,Newgrade)其中Oldgrade是修改前的分数,Newgrade是修改后的分数
create trigger SC_T
after update of Grade on SC
referencing
old row as OldTuple
new row as NewTuple
for each row
when (NewTuple.Grade>=1.1*OldTuple.Grade)
insert into SC_U(Sno,Cno,Oldgrade,Newgrade)
values(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade );
例子2:将每次对表Student的插入操作所增加的学生个数记录到表Student-InsertLong中
create trigger Student_Count
after insert on Student
referencing
new table as DELTA
for each statement
insert into Student-InsertLong(Numbers)
select count(*) from DELTA;
例子3:定义一个before行级触发器,为教师表Teacher顶一波完整性规则“教授的工资不得低于4000元,如果低于4000元,自动改为4000元”
create trigger Insert_Or_Update_Sal
before insert or update on Teacher
referencing
new row as newTuple
for each row
begin
if(newTuple.Job='教授')and(newTuole.Sal<4000)
then newTuple.Sal:=4000;
end if;
end;
2)删除触发器
drop trigger <触发器名> on <表名>
8.授权:授予与收回
(1)授予
例子1:把查询Student表和Course表和修改学生学号的权限给用户U4和U3
gavnt update(Sno),select
on table Student,Course
to U4,U3
with grant option
(2)收回
例子1:把用户U4修改学生学号的权限收回
revoke update(Sno)
on table Student
from U4;
例子2:收回所以用户对表SC的查询权限
revoke select
on table SC
from public;
例子3:把用户U5对SC表的insert权限收回
revoke insert
on table SC
from U5 cascade;
9.过程化SQL
(1)变量定义
declare 变量名 数据类型 [[not null]:=初值表达式]或
declare 变量名 数据类型 [[not null] 初值表达式]
(2)常量的定义
declare 常量名 数据类型 constant:=常量表达式
(3)赋值语句
变量名:=表达式
(4)流程控制
1)条件控制语句
<1>if-then语句
if condition then
Sequence_of_statements;
end if;
<2>if-then-else语句
if condition then
Sequence_of_statements1;
else
Sequence_of_statements2;
end if;
<3>嵌套的if语句
在then和else子句中还可以再包含if语句,即if语句可以嵌套
if condition1 then
Sequence_of_statements1;
else
if condition2 then
Sequence_of_statements2;
else
Sequence_of_statements3;
end if;
end if;
2)循环控制语句
<1>最简单的循环 loop
loop
Sequence_of_statements;
end loop;
<2>while-loop循环语句
while condition loop
Sequence_of_statements;
end loop;
<3>for-loop循环语句
for count in [reverse] bound1..bound2 loop
Sequence_of_statements;
end loop;
(5)存储过程的定义
create or replace procedure 过程名 ([参数名1 数据类型,参数名2 数据类型,参数名3 数据类型...]) as <过程化SQL块>;
例子1:create or replace procedure TRANSFER (inaccount int ,outaccount int,amount float) as <过程化SQL块>
(6)执行存储过程
call/perform procedure 过程名([参数名1 ,参数名2 ,参数名3 ...]);
例子1:call procedure TRANSFER (0111222,0111222,1000);
(7)修改存储过程
alter procedure 原过程名 rename to 新过程名;
再重新编译:alter procedure 过程名 compile;
(8)删除存储过程
drop procedure 过程名();
(9)执行部分(PL/SQL过程块)
begin
SQL语句,过程化SQL的流程控制语句
exception
异常处理部分
end;
10 .关系数据库系统的查询优化
优化就是使查询元组的个数最少化过程,因为每一个查询功能有不止一种实现方式,需要选择最优的一种