数据库学习笔记authorWANG.docx

1、数据库学习笔记authorWANG一、基本概念 2二、数据库基本操作 2 常用的查询条件运算符： 2 创建表： 2 修改基本表： 3 删除基本表或视图 3 创建索引 3三、表内数据的操作 3 select语句 3 聚集函数 3 group by 子句 4 where 与 having的区别： 4 连接查询 4 嵌套查询： 5四、数据更新操作 7 插入数据 7 修改数据（更新数据） 7 删除数据 8五、视图 8 建立视图 8 删除视图 8 视图的作用 8六、授权 9七、触发器 9八、范式 10一、基本概念1 数据：是数据库中存储的基本对象、数据库：（DataBase简称DB）是长期存储在计算机内

2、、有组织的、可共享的大量数据的集合数据库管理系统：（DBMS）位于用户与操作系统之间的一层数据管理软件数据库系统：（DBS）在计算机中引入数据库后的系统构成数据模型：在数据库中用数据模型这个工具来抽象、表示和处理现实世界中的数据信息。通俗的讲，数据模型就是现实世界的模拟数据模型的组成要素：数据结构、数据操作、完整性约束条件2 实体：客观存在并可相互区别的事物称为实体属性：实体所具有的某一特性码：唯一标识实体的属性域：属性取值范围称为该属性的域实体型：用实体名及其属性名集合来抽象和刻画同类实体实体集：同一类型实体的集合 实体内部的联系（1对多）联系： 实体间的联系（1对1）E-R图：实体型用矩形

3、表示、属性用椭圆表示、联系用菱形表示3 关系的完整性：实体完整性（主码不能为空）、参照完整性、用户定义完整性一、 数据库基本操作1 常用的查询条件运算符：比较：、！、！、！、NOT加上比较运算符确定范围：between and，not between and确定集合：in、not in字符匹配：notlike 匹配串escape 。通配符：%、_。（%可心代表0个或0个以上的所有字符，它代的是一个字符串；_只能代表一个字符。一个汉字占两个字符，故匹配一个汉字需使用两个_，即_ _）空值：is null、is not null（不能用=null代替is null）多重条件：and、or、not例

4、：查询以“DB_开头，且倒数第三个字符为i的课程”Select * from Course where Cname like DB_%i_ _ escape 2 创建表：Create table Course(Cno char(4) primary key,Cname varchar(40) unique,/*Cname取值唯一*/Ssex char(2) check(Ssex in(男,女)，/*性别属性只能取男或女*/Cpno char(4),Ccridit smallint,Foreign key(Cpno) references Course(Cno);3 修改基本表：Alert ta

5、ble add 完整性约束drop alert column ;4 删除基本表或视图Drop Schema Drop table 5 创建索引Create uniquecluster index On （ ,）;例：create unique index SCno on SC(Sno asc,Cno desc);二、 表内数据的操作 select语句 Select all|distinct,From ,where group by having order by asc|desc注：distinct表示数据去重，默认的为all例：Select Sname,”year of birth:”,20

6、04-Sage BIRTHDAY,islower(Sdept) DEPARTMENT from student解析：这个查询语句查了student表中的3个字段，Sname、Sage、Sdept，而”year of birth:”是在查询的结果列中加入一列字符串。2004-age是一个表达式，它后面的BIRTHDAY是指将查询的结果列的列名用BIRTHDAY代替（如果不代替，则显示的列名将会是2004-age）。Islower(Sdept)是指将查询到的Sdept数据以小写的形式呈现 聚集函数 计数：count (distinct|all*) 统计元组个数 Count (distinct|al

7、l) 统计一列中值的个数计算总和：sum (distinct|all) 计算一列值的总和（此列必需是数值型）计算平均：avg(distinct|all)最大：max(distinct|all) 求一列值中的最大值最小：min(distinct|all) 求一列值中的最小值注：在聚集函数遇到空值时，除count(*)外，都能跳过空值而处理非空值。Count 也会跳过空值而处理非空值例：select count(distinct Sno) from SC;Select avg(Grade) from SC where Cno=1;Select max(Grade) from SC where Cn

8、o=1;Select sum(Ccredit) from SC,Course where Sno=200215012 and SC.no=Course.Cno; /*查询学生200215012选修课程的总学分数*/ group by 子句将查询结果按某一列或多列的值进行分组，值相等的为一组。结查询结果分组的目的是为了细化聚集函数的作用对象，如果未对查询结果分组，聚集函数将作用于整个查询结果，分组后聚集函数将作用于每个分组，即每一组都有一个函数值。Select后为聚集函数时，它一般只能带分组字段（带其它字段似乎意义不大，当然，带的话在语法上是没有错的）例：Select Sno from SC g

9、roup by Sno having count(*) where 与 having的区别：Where子句作用于基本表或视图。Having作用于组。 连接查询若一个查询同时涉及两个两个以上的表，则称之为连接查询。连接查询包括：等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接查询。1、等值连接与非等值连接：例：select Student.*,SC.* from Student,SC where Student.Sno=SC.Sno解析：sql的意思为将Student与SC中同一学生的信息的元组连接起来，并返回查询结果，返回的即为学生的信息。此查询涉及了两个表，且带有比较运算符。使

10、用的比较运算符为=时，就叫等值连接，使用的是其他比较运算符时就叫非等值连接。RDBMS执行该连接操作的一种可能过程是：首先在表Student中找到第一个元组，然后从头开始扫描SC表，逐一查找与Student第一个元组的Sno相等的SC无组，找到后将Student中的第一个元组与该元组拼接起来，形成结果表中的一个元组。SC表全部查找一遍后，再开始Student表中的第二个元组，依此反复执行，直到找完Student表为止。2、自然连接若在等值连接中把目标列中的重复的属性列去掉则为自然连接例：对上面的sql使用自然连接查询来完成的话即为：Select Student.Sno,Sname,Ssex,S

11、age,Sdept,Cno,GradeFrom Stuent,SCWhere Student.Sno=SC.Sno3、自身连接：一个表与其自己进行连接，称为表的自身接连。例：查询每门课程的间接先修课Select first.Cno,second.Cpno from Course first,Course secondWhere first.Cpno=second.Cno注：在做自身连接时需要将表取别名以示区别。4、外连接：如果在关系R和关系S作自然连接时，把该舍弃的元组也保留在新关系中，在新增加的属性上填空值（null），那么这种操作叫外连接操作。如果只把R中舍弃的元组放到新关系中，则称为“左

12、外连接”，相应的如果只把S中舍弃的元组放到新关系中，则称为“右外连接”。三种外连接：left、right、full例：select Student.Sno,Sname,Ssex,Sage,Ssdept,Cno,GradeFrom Student left out join SC on (Student.Sno=SC.Sno);/*也可使用using来去掉结果中的重复值：from Student left out join using(Sno);*/5、复合条件连接：Where子句中如果有多个连接条件，则称为复合条件连接。例1：查询选修2号课程且成绩在90分以上的所有学生Select Stude

13、nt.Sno,SnameFrom Student,SCWhere Student.Sno=SC.Sno and SC.Cno=2 and SC.Grade90;解析：该查询的一种优化的执行过程是先从SC中挑选出Cno=2并且Grade90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系。例2：查询每个学生的学号、姓名、选修课的课程名及成绩Select Student.Sno,Sname,Cname,GradeFrom Student,SC,CourseWhere Student.Sno=SC.Sno and SC.Cno=Course.Cno;6 嵌套查

14、询：在sql语言中，一个select-from-where语句称为一个查询块。将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。例：select Sname /*外层查询或父查询*/from Studentwhere Sno in (select Sno /*内层查询或子查询*/ From SC Where Cno=2);注：子查询的select语句中不能使用order by子句，order by 子句只能对最终查询结果排序。1、 带有in谓词的子查询：例：查询与“刘晨”在同一个系学习的学生Select Sno,Sname,SdeptForm Stud

15、entWhere Sdept in (select SdeptFrom StudentWhere Sname=刘晨);此例与可以通过自身连接为实现：Select S1.Sno,S1.Sname,S1.SdeptFrom Student S1,Student S2Where S1.Sdept=S2.Sdept and S2.name=刘晨;2、 带有比较运算符的子查询：上面的例子还可以写为：Select Sno,Sname,SdeptFrom StudentWhere Sdept= (Select SdeptFrom StudentWhere Sname=刘晨);例：找出第个学生超过他选修课平均

16、成绩的课程号Select Sno,CnoFrom SC xWhere Grade=(Select avg(Grade)From SC yWhere y.Sno=x.Sno);3、 带有any(some)或all谓词的子查询子查询返回单值时可以用比较运算符，但返回多值时要用any(有的系统用some)或all谓词修饰符。而使用any或all谓词时必须同时使用比较运算符。any 大于子查询结果中的某个值all 大于子查询结果中的所有值=any 大于等于子查询结果中的某个值=all 大于等于子查询结果中的所有值=any 小于等于子查询结果中的某个值=all 小于等于子查询结果中的所有值=any 等于

17、子查询结果中的某个值=all 等于子查询结果中的所有值（通常没有实际意义）!=(或)any 不等于子查询结果中的某个值!=(或)all 不等于子查询结果中的任何一个值例：查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄Select Sname,SageFrom StudentWhere Sageany(select SageFrom StudentWhere Sdept=CS)And Sdept CS;/*此为父查询中的条件*/4、 带有exists谓词的子查询：带有exists谓词的子查询不返回任何数据，只产生逻辑值”true”或”false”例：查询所有选修了1号课程的学生姓名Se

18、lect SnameFrom StudentWhere exists(select *From SCWhere Sno=Student.Sno and Cno=1);三、 数据更新操作 插入数据1、插入元组Insert into ,Values(,);注：into子句中没有出现的属性列，新元组在这些列上将取空值。但必须注意的是，在表定义时说明了not null的属性列不能取空值，否则会出错。如果into子句中没有指明任何属性列名，则新插入的元组必须在每个属性列上均有值。字符串常量要用单引号括起来。例：insert into SC(Sno,Cno) values(200215128,1);上述s

19、ql等价于：insert into SC values(200215128,1,null);2、插入子查询结果Insert into ,子查询;例：对每一个系，求学生的平均成绩，并把结果插入数据库Insert into Dept_age(Sdept,Avg_age)Select Sdept,AVG(Sage) from Student group by Sdept; 修改数据（更新数据） Update Set =,= Where ;1、 修改某一个元组的值例：将学生200215121的年龄改为22岁Update StudentSet Sage=22Where Sno=200215121;2、

20、修改多个元组的值例：将所有学生的年龄增加1岁Update StudentSet Sage=Sage+1;3、 带子查询的修改语句例：将计算机科学系全体学生的成绩置零Update SCSet Grade=0Where CS= (select SdeptFrom StudentWhere Student.Sno=SC.Sno); 删除数据DeleteFrom where ;1、 删除一个元组的值例：删除学号为200212128的学生记录Delete from Student where Sno=200212128;2、 删除多个元组的值例：删除所有的学生选课记录Delete from SC;3、

21、带子查询的删除语句例：删除计算机科学系所有学生的选课记录Delete from SCWhere CS=(select SdeptFrom StudentWhere Student.Sno=SC.Sno);四、 视图1 建立视图Create view (,)As with check option;注：子查询可以是任何复杂的select语句，但通常不允许含有order by子句和distinct短语。With check option表示对视图进行update，insert和delete操作时要保证更新、插入或删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。（不加也行）例：建立信息系

22、学生的视图Create view IS_Student(Sno,Sname,Sage)AsSelect Sno,Sname,SageFrom StudentWhere Sdept=IS;如果省略IS_Student后的列名Sno,Sname,Sage，则视图IS_Student的列名将由子查询的三个列名组成。2 删除视图Drop view cascade;3 视图的作用视图能够简化用户操作、使用户以多角度看待同一数据、对重构数据库提供了一定的逻辑独立性、能够对机密数据提供安全保护、适当的视图可以更清晰的表达查询。五、 授权Grant ,on To ,with grant option;/指定被

23、授权用户可以再授权，没有指定的话则权限不能传播Revoke ,on From ,可以在后面加上cascade表示级联收回例：Grant select on table Student to U1;Grant all priviliges on table Student,Course to U2,U3;/ all priviliges表示所有权限Grant select on table SC to public;/to public表示给所有用户六、 触发器Create trigger before|after on For each row|atatementWhen 注：触发事件指ins

24、ert、delete、update。Row是行级触发器，statement是语句级触发器Drop trigger on ;例：Create trigger Insert_OR_Update_SalBefore insert or update on TeacherFor each rowAs begin/*定义触发动作体是PL/SQL过程块*/If(new.job=教授) and (new.Sal4000) then new.Sal=4000;End if;End;Create trigger Insert_SalAfter insert on TeacherFor each rowAs begin Insert into Sal_log values(new.Eno,new.Sal,current_user,current_time);七、 范式1NF：如果一个关系模式R的所有属性都是不可分割的数据项，则R1NF2NF：若R1NF，且每一个非主属性完全函数依赖于码，则R2NF。（没有部分函数依赖）3NF：若R3NF，则每一个非主属性即不部分依赖于码也不传递依赖于码