数据库学习笔记authorWANG.docx
《数据库学习笔记authorWANG.docx》由会员分享,可在线阅读,更多相关《数据库学习笔记authorWANG.docx(15页珍藏版)》请在冰豆网上搜索。
数据库学习笔记authorWANG
一、基本概念2
二、数据库基本操作2
①常用的查询条件运算符:
2
②创建表:
2
③修改基本表:
3
④删除基本表或视图3
⑤创建索引3
三、表内数据的操作3
①select语句3
②聚集函数3
③groupby子句4
④where与having的区别:
4
⑤连接查询4
⑥嵌套查询:
5
四、数据更新操作7
①插入数据7
②修改数据(更新数据)7
③删除数据8
五、视图8
①建立视图8
②删除视图8
③视图的作用8
六、授权9
七、触发器9
八、范式10
一、基本概念
1数据:
是数据库中存储的基本对象、
数据库:
(DataBase简称DB)是长期存储在计算机内、有组织的、可共享的大量数据的集合
数据库管理系统:
(DBMS)位于用户与操作系统之间的一层数据管理软件
数据库系统:
(DBS)在计算机中引入数据库后的系统构成
数据模型:
在数据库中用数据模型这个工具来抽象、表示和处理现实世界中的数据信息。
通俗的讲,数据模型就是现实世界的模拟
数据模型的组成要素:
数据结构、数据操作、完整性约束条件
2实体:
客观存在并可相互区别的事物称为实体
属性:
实体所具有的某一特性
码:
唯一标识实体的属性
域:
属性取值范围称为该属性的域
实体型:
用实体名及其属性名集合来抽象和刻画同类实体
实体集:
同一类型实体的集合
实体内部的联系(1对多)
联系:
实体间的联系(1对1)
E-R图:
实体型用矩形表示、属性用椭圆表示、联系用菱形表示
3关系的完整性:
实体完整性(主码不能为空)、参照完整性、用户定义完整性
一、数据库基本操作
1常用的查询条件运算符:
比较:
=、>、<、>=、<=、!
=、<>、!
>、!
<、NOT加上比较运算符
确定范围:
betweenand,notbetweenand
确定集合:
in<值表>、notin<值表>
字符匹配:
[not]like‘匹配串’[escape‘<转码字符>’]。
通配符:
%、_。
(%可心代表0个或0个以上的所有字符,它代的是一个字符串;_只能代表一个字符。
一个汉字占两个字符,故匹配一个汉字需使用两个_,即__)
空值:
isnull、isnotnull(不能用=null代替isnull)
多重条件:
and、or、not
例:
查询以“DB_开头,且倒数第三个字符为i的课程”
Select*fromCoursewhereCnamelike‘DB\_%i__’escape‘\’
2创建表:
CreatetableCourse
(Cnochar(4)primarykey,
Cnamevarchar(40)unique,/*Cname取值唯一*/
Ssexchar
(2)check(Ssexin(‘男’,’女’)),/*性别属性只能取’男’或’女’*/
Cpnochar(4),
Ccriditsmallint,
Foreignkey(Cpno)referencesCourse(Cno)
);
3修改基本表:
Alerttable<表名>
[add<新列名><数据类型>[完整性约束]]
[drop<完整性约束名>]
[alertcolumn<列名><数据类型>];
4删除基本表或视图
DropSchema<模式名>
Droptable<表名>
5创建索引
Create[unique][cluster]index<索引名>
On<表名>(<列名>[<次序>][,<列名>[<次序>]]……);
例:
createuniqueindexSCnoonSC(Snoasc,Cnodesc);
二、表内数据的操作
①select语句
Select[all|distinct]<目标列表达式>[,<目标列表达式>]……
From<表名或视图>[,<表名或视图>]……
[where<条件表达式>]
[groupby<列名>[having<条件表达式>]]
[orderby<列名>[asc|desc]]
注:
distinct表示数据去重,默认的为all
例:
SelectSname,”yearofbirth:
”,2004-SageBIRTHDAY,islower(Sdept)DEPARTMENTfromstudent
解析:
这个查询语句查了student表中的3个字段,Sname、Sage、Sdept,而”yearofbirth:
”是在查询的结果列中加入一列字符串。
2004-age是一个表达式,它后面的BIRTHDAY是指将查询的结果列的列名用BIRTHDAY代替(如果不代替,则显示的列名将会是2004-age)。
Islower(Sdept)是指将查询到的Sdept数据以小写的形式呈现
②聚集函数
计数:
count([distinct|all]*)统计元组个数
Count([distinct|all]<列名>)统计一列中值的个数
计算总和:
sum([distinct|all]<列名>)计算一列值的总和(此列必需是数值型)
计算平均:
avg([distinct|all]<列名>)
最大:
max([distinct|all]<列名>)求一列值中的最大值
最小:
min([distinct|all]<列名>)求一列值中的最小值
注:
在聚集函数遇到空值时,除count(*)外,都能跳过空值而处理非空值。
Count<列名>也会跳过空值而处理非空值
例:
selectcount(distinctSno)fromSC;
Selectavg(Grade)fromSCwhereCno=’1’;
Selectmax(Grade)fromSCwhereCno=’1’;
Selectsum(Ccredit)fromSC,CoursewhereSno=’200215012’andSC.no=Course.Cno;/*查询学生200215012选修课程的总学分数*/
③groupby子句
将查询结果按某一列或多列的值进行分组,值相等的为一组。
结查询结果分组的目的是为了细化聚集函数的作用对象,如果未对查询结果分组,聚集函数将作用于整个查询结果,分组后聚集函数将作用于每个分组,即每一组都有一个函数值。
Select后为聚集函数时,它一般只能带分组字段(带其它字段似乎意义不大,当然,带的话在语法上是没有错的)
例:
SelectSnofromSCgroupbySnohavingcount(*)
④where与having的区别:
Where子句作用于基本表或视图。
Having作用于组。
⑤连接查询
若一个查询同时涉及两个两个以上的表,则称之为连接查询。
连接查询包括:
等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。
1、等值连接与非等值连接:
例:
selectStudent.*,SC.*fromStudent,SCwhereStudent.Sno=SC.Sno
解析:
sql的意思为将Student与SC中同一学生的信息的元组连接起来,并返回查询结果,返回的即为学生的信息。
此查询涉及了两个表,且带有比较运算符。
使用的比较运算符为=时,就叫等值连接,使用的是其他比较运算符时就叫非等值连接。
RDBMS执行该连接操作的一种可能过程是:
首先在表Student中找到第一个元组,然后从头开始扫描SC表,逐一查找与Student第一个元组的Sno相等的SC无组,找到后将Student中的第一个元组与该元组拼接起来,形成结果表中的一个元组。
SC表全部查找一遍后,再开始Student表中的第二个元组,依此反复执行,直到找完Student表为止。
2、自然连接
若在等值连接中把目标列中的重复的属性列去掉则为自然连接
例:
对上面的sql使用自然连接查询来完成的话即为:
SelectStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FromStuent,SC
WhereStudent.Sno=SC.Sno
3、自身连接:
一个表与其自己进行连接,称为表的自身接连。
例:
查询每门课程的间接先修课
Selectfirst.Cno,second.CpnofromCoursefirst,Coursesecond
Wherefirst.Cpno=second.Cno
注:
在做自身连接时需要将表取别名以示区别。
4、外连接:
如果在关系R和关系S作自然连接时,把该舍弃的元组也保留在新关系中,在新增加的属性上填空值(null),那么这种操作叫外连接操作。
如果只把R中舍弃的元组放到新关系中,则称为“左外连接”,相应的如果只把S中舍弃的元组放到新关系中,则称为“右外连接”。
三种外连接:
left、right、full
例:
selectStudent.Sno,Sname,Ssex,Sage,Ssdept,Cno,Grade
FromStudentleftoutjoinSCon(Student.Sno=SC.Sno);
/*也可使用using来去掉结果中的重复值:
fromStudentleftoutjoinusing(Sno);*/
5、复合条件连接:
Where子句中如果有多个连接条件,则称为复合条件连接。
例1:
查询选修2号课程且成绩在90分以上的所有学生
SelectStudent.Sno,Sname
FromStudent,SC
WhereStudent.Sno=SC.SnoandSC.Cno=’2’andSC.Grade>90;
解析:
该查询的一种优化的执行过程是先从SC中挑选出Cno=’2’并且Grade>90的元组形成一个中间关系,再和Student中满足连接条件的元组进行连接得到最终的结果关系。
例2:
查询每个学生的学号、姓名、选修课的课程名及成绩
SelectStudent.Sno,Sname,Cname,Grade
FromStudent,SC,Course
WhereStudent.Sno=SC.SnoandSC.Cno=Course.Cno;
6嵌套查询:
在sql语言中,一个select-from-where语句称为一个查询块。
将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。
例:
selectSname/*外层查询或父查询*/
fromStudent
whereSnoin
(selectSno/*内层查询或子查询*/
FromSC
WhereCno=’2’);
注:
子查询的select语句中不能使用orderby子句,orderby子句只能对最终查询结果排序。
1、带有in谓词的子查询:
例:
查询与“刘晨”在同一个系学习的学生
SelectSno,Sname,Sdept
FormStudent
WhereSdeptin
(selectSdept
FromStudent
WhereSname=’刘晨’);
此例与可以通过自身连接为实现:
SelectS1.Sno,S1.Sname,S1.Sdept
FromStudentS1,StudentS2
WhereS1.Sdept=S2.SdeptandS2.name=’刘晨’;
2、带有比较运算符的子查询:
上面的例子还可以写为:
SelectSno,Sname,Sdept
FromStudent
WhereSdept=
(
SelectSdept
FromStudent
WhereSname=’刘晨’);
例:
找出第个学生超过他选修课平均成绩的课程号
SelectSno,Cno
FromSCx
WhereGrade>=(
Selectavg(Grade)
FromSCy
Wherey.Sno=x.Sno);
3、带有any(some)或all谓词的子查询
子查询返回单值时可以用比较运算符,但返回多值时要用any(有的系统用some)或all谓词修饰符。
而使用any或all谓词时必须同时使用比较运算符。
>any大于子查询结果中的某个值
>all大于子查询结果中的所有值
>=any大于等于子查询结果中的某个值
>=all大于等于子查询结果中的所有值
<=any小于等于子查询结果中的某个值
<=all小于等于子查询结果中的所有值
=any等于子查询结果中的某个值
=all等于子查询结果中的所有值(通常没有实际意义)
!
=(或<>)any不等于子查询结果中的某个值
!
=(或<>)all不等于子查询结果中的任何一个值
例:
查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄
SelectSname,Sage
FromStudent
WhereSageFromStudent
WhereSdept=’CS’)
AndSdept<>‘CS’;/*此为父查询中的条件*/
4、带有exists谓词的子查询:
带有exists谓词的子查询不返回任何数据,只产生逻辑值”true”或”false”
例:
查询所有选修了1号课程的学生姓名
SelectSname
FromStudent
Whereexists
(select*
FromSC
WhereSno=Student.SnoandCno=’1’);
三、数据更新操作
①插入数据
1、插入元组
Insertinto<表名>[<属性列1>[,<属性列2>]……]
Values(<常量1>[,<常量2>]……);
注:
into子句中没有出现的属性列,新元组在这些列上将取空值。
但必须注意的是,在表定义时说明了notnull的属性列不能取空值,否则会出错。
如果into子句中没有指明任何属性列名,则新插入的元组必须在每个属性列上均有值。
字符串常量要用单引号括起来。
例:
insertintoSC(Sno,Cno)values(‘200215128’,’1’);
上述sql等价于:
insertintoSCvalues(‘200215128’,’1’,null);
2、插入子查询结果
Insertinto<表名>[<属性列1>[,<属性列2>]……]
子查询;
例:
对每一个系,求学生的平均成绩,并把结果插入数据库
InsertintoDept_age(Sdept,Avg_age)
SelectSdept,AVG(Sage)fromStudentgroupbySdept;
②修改数据(更新数据)
Update<表名>
Set<列名>=<表达式>[,<列名>=<表达式>]……
[Where<条件>];
1、修改某一个元组的值
例:
将学生200215121的年龄改为22岁
UpdateStudent
SetSage=22
WhereSno=’200215121’;
2、修改多个元组的值
例:
将所有学生的年龄增加1岁
UpdateStudent
SetSage=Sage+1;
3、带子查询的修改语句
例:
将计算机科学系全体学生的成绩置零
UpdateSC
SetGrade=0
Where‘CS’=
(selectSdept
FromStudent
WhereStudent.Sno=SC.Sno);
③删除数据
Delete
From<表名>
[where<条件>];
1、删除一个元组的值
例:
删除学号为200212128的学生记录
DeletefromStudentwhereSno=’200212128’;
2、删除多个元组的值
例:
删除所有的学生选课记录
DeletefromSC;
3、带子查询的删除语句
例:
删除计算机科学系所有学生的选课记录
DeletefromSC
Where‘CS’=(selectSdept
FromStudent
WhereStudent.Sno=SC.Sno);
四、视图
1建立视图
Createview<视图名>[(<列名>[,<列名>]……)]
As<子查询>
[withcheckoption];
注:
子查询可以是任何复杂的select语句,但通常不允许含有orderby子句和distinct短语。
Withcheckoption表示对视图进行update,insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。
(不加也行)
例:
建立信息系学生的视图
CreateviewIS_Student(Sno,Sname,Sage)
As
SelectSno,Sname,Sage
FromStudent
WhereSdept=’IS’;
如果省略IS_Student后的列名Sno,Sname,Sage,则视图IS_Student的列名将由子查询的三个列名组成。
2删除视图
Dropview<视图名>[cascade];
3视图的作用
视图能够简化用户操作、使用户以多角度看待同一数据、对重构数据库提供了一定的逻辑独立性、能够对机密数据提供安全保护、适当的视图可以更清晰的表达查询。
五、授权
Grant<权限>[,<权限>]……
[on<对象类型><对象名>]
To<用户>[,<用户>]……
[withgrantoption];//指定被授权用户可以再授权,没有指定的话则权限不能传播
Revoke<权限>[,<权限>]……
[on<对象类型><对象名>]
From<用户>[,<用户>]……
可以在后面加上cascade表示级联收回
例:
GrantselectontableStudenttoU1;
GrantallpriviligesontableStudent,CoursetoU2,U3;
//allpriviliges表示所有权限
GrantselectontableSCtopublic;//topublic表示给所有用户
六、触发器
Createtrigger<触发器名>
{before|after}<触发事件>on<表名>
Foreach{row|atatement}
[When<触发条件>]
<触发动作体>
注:
触发事件指insert、delete、update。
Row是行级触发器,statement是语句级触发器
Droptrigger<触发器名>on<表名>;
例:
CreatetriggerInsert_OR_Update_Sal
BeforeinsertorupdateonTeacher
Foreachrow
Asbegin/*定义触发动作体是PL/SQL过程块*/
If(new.job=’教授’)and(new.Sal<4000)thennew.Sal=4000;
Endif;
End;
CreatetriggerInsert_Sal
AfterinsertonTeacher
Foreachrow
Asbegin
InsertintoSal_logvalues(new.Eno,new.Sal,current_user,current_time);
七、范式
1NF:
如果一个关系模式R的所有属性都是不可分割的数据项,则R∈1NF
2NF:
若R∈1NF,且每一个非主属性完全函数依赖于码,则R∈2NF。
(没有部分函数依赖)
3NF:
若R∈3NF,则每一个非主属性即不部分依赖于码也不传递依赖于码