数据库总结(共13页).docx

精选优质文档-倾情为你奉上1. 数据库特点：较小的冗余度。较高的数据独立性、易扩展性。可为各种用户共享数据库是数据集合2. DBMS功能：数据定义功能。数据组织、存储和管理（文件结构和存取方式） 。数据操纵功能（增删查改）3. 高级数据库系统: 分布式数据库系统, 面向对象数据库系统4. 数据模型的组成部分包括：数据结构，数据操作，数据约束条件。5. 数据更新：数据添加，数据修改，数据删除。6. 数据模型（网状、层次、关系模型）：按计算机系统的观点建模，主要用于DBMS的实现，不包括E-R关系模型中由三种完整性约束：实体完整性，参照完整性，用户自定义完整性。7. 关系：（Relation）一个关系通常是一张表。8. 分量：某一元组的一个属性值，为最小单位，不可分。9. 存储结构：在DB物理组织之中，表以文件形式储存10. 数据库系统结构：从DBMS角度：外模式(External Schema)，模式(Schema)，内模式(Internal Schema)。从最终用户角度：集中式结构。分布式结构 。客户/服务器结构。并行结构11.12. 模式/内模式映象是唯一的，同一个模式可以有任意多个外模式；三级模式与二级映像优点：（1）保证数据的独立性（2）简化用户接口（3）有利于数据共享（4）有利于数据保密13. 关系模型的组成：关系数据结构。关系操作集合。关系完整性约束 SQL Server基于关系模型的数据库系统。14. 主 码：若一个关系有多个候选码，则选定其中的一个为主码15：数据类型：decimal(不包含小数点多少位,小数点后几位)nvarchar采用Unicode编码，无论是中文英文占一个字节有n表示Unicode编码，每个字符占一个字节，没有n表示非Unicode编码，英文或者数字占一个字节，中文占两个字节。有中文用nvarchar,没有中文用varchar.16：按照功能分类：DDL：(Database design language)数据定义语言，用于进行各种数据库对象的创建主要操作包括create,alter,dropDML：(Database management language)数据管理语言，用于对标的数据添加,修改，删除，查询，主键操作包括，insert,update,delete，selectDCL：(Database control language)数据控制语言，用于进行权限分配等1：创建数据库create database TaoHaoon primary(name='TaoHao',filename='E:Microsoft SQL ServerDATATH_data.mdf',size=10mb,maxsize=200mb,filegrowth=10mb)log on(name='TaoHao_log',filename='E:Microsoft SQL ServerDATATH_log.ldf',size=5mb,filegrowth=10mb)相关子查询:not exists ;子查询需要父查询的结果才能执行不相关子查询:not in:执行顺序是子查询先执行,结果传给父查询子查询返回的值只有一个：利用（=,<,>,>=,<=,!=）子查询返回一组值：需要在比较运算符和子查询之间插入any或all2：修改，删除，查询，-alter用法alter table 表名alter column 列名 数据类型 null|not null-为所有行的指定列修改update UserInfo set UserPwd='admin'-为指定行的列修改update UserInfo set UserPwd='2250' where UserName='陶浩'update UserInfo set UserPwd='abcde' where UserId>1-清空数据truncate table UserInfo-删除 drop table UserInfo-查询前n部分数据：top n * from 表示查看前n行select top 2 * from StaffInfoselect top 2 percent * from StaffInfo-distinct消除重复行-模糊查询-%表示个或者多个字符-_下划线表示一个字符-表示你在某一范围字符-表示不在某一范围内的字符select * from StudentInfowhere sPhonr like '15-9%'3.内连接:inner join,两表中完全匹配的数据-left join以左表属性顺序（CID属性顺序）为标准-right join 以右表属性顺序（Sid顺序）为标准-full join,左表中特有的数据4.聚合函数：-对比自己的成绩和平均分select StaffInfo.*,AVG(score)over() as '平均分'from StaffInfowhere Name='bb'-统计班同学男生女生各人数select sGender,COUNT(*)from StudentInfowhere cId=2group by sGender-统计学生编号大于的号班级的性别的学生个数select sGender,COUNT(*)-指定了cId不能select cIdfrom StudentInfowhere sId>1 and cId=3group by sGender分组查询：group by字句可以将查询结果按照属性列或属性列组合在行的方向上进行分组，每组在属性列或属性列组合上具有相同的值。若在分组后还要按照一定的条件进行筛选需要使用having字句。重点：子查询的SELECT语句中不能使用 ORDER BY 子句，因为 ORDER BY 子句只能对最终查询结果排序。找出每个学生超过他自己选修课程平均成绩的课程号SELECT Sno,CnoFROM SC XWHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno);查询选修了全部课程的学生姓名由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。SELECT SnameFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=Course.Cno);查询至少选修了学生选修的全部课程的学生号码SELECT DISTINCT SnoFROM SC SCXWHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno='' AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno);5.快速备份select * into test01 from ClassInfo向未有表备份：select列名into备份表名from源表名说明：备份表可以不存在，会新建表，表的结构完全一致，但是不包含约。向已有表备份：insert into 备份表名 select 列名 from 源表名6.数据转换select CAST(89. as decimal(4,2)-decimal表示除了小数点一共位select CONVERT(decimal(4,1),89.999)-会四舍五入select CAST(1 as CHAR(1)+'1'-char类型-统计销售总价超过的商品名称和销售总价，并按照销售总价降序排序select * from MyOrdersselect pName,SUM(SaleNumber*SalePrice) 销售总价from MyOrdersgroup by pNamehaving SUM(SaleNumber*SalePrice)>300order by 销售总价 desc7.函数：select '哈哈'+LTRIM(' 长安的 美食')-去掉左空格select '哈哈'+RTRIM(' 长安的 ')-去掉右空格select '哈哈'+RTRIM(LTRIM(' 中国 ')-select LEFT('麦田吃饭',1)-从左边开始截取几个select RIGHT('可口可乐',3)-从右边开始截取几个select SUBSTRING('初审过了哈哈哈',2,4)-从第二个开始截取四个8.开窗函数：over()将统计出来的数据分不到原表的每一行中，结合聚合函数，排名函数的使用1：t-sql编程变量：l 声明：declare UserName nvarchar(50)l 赋值1：set UserName=N'杨'：修改l 赋值2：select UserName=N'牛'：修改l 输出：print UserNameselect UserNamel select UserName+N'中'，这时UserName仍然是杨，这句代码执行后显示杨中l 区别：赋值时，set比select更严谨些set一次只能为一个变量赋值，select一次可以为多个变量赋值。l 变量是有作用域的l 全局变量：*select变量declare name nvarchar(10)声明set name=陶浩赋值print name输出-选择语句declare id intset id=10if id>5beginprint 'ok'+convert(varchar(10),id)endelsebeginprint 'no'end2：casel 在查询语句的select后面，可以进行选择判断的逻辑l 语法1：判等case 列名或表达式：判断与值1，2，3是否相等when 值1 then .返回then后面表达式的值when 值2 then .when 值3 then .else .end as 列别名l 语法2：判不等case when 条件1 then .判断条件的真假when 条件2 then .when 条件3 then .else .end as 列别名3：overl 开窗函数：几个行作为一个区，就被称为一个窗，能够进行按行划区的函数就是开窗函数l 排名函数：rank() over(order by 列名 desc)，比row_number()函数更适合用于排名l over与聚合函数一起使用，但不要和group by 一组使用，否则会报错l 没有分组的时候，就认为是将整个查询结果分成了一组l select *,sum(销售数量) from myOrders会报错l 改为：select *,sum(销售数量) over from myOrders则不会报错，并且在最后一列显示求和结果4：视图Viewl 视图：就是一个select语句，写起来比较复杂，多次被使用，则可以将这个select语句存放到一个视图中l 创建语法： create view 名称 as 查询语句l 好处：使用方便，安全（看不到表的名称、结构等信息）l 使用：主要用于查询，与查询表的语法一样l 查看视图的代码：exec sp_helptext 视图名称l 可以基于视图再创建视图l 建议：不要在视图中使用order by子句 集合的结果集被当作一个集合使用，所以是不应该有顺序的 如果加入了top n则可以使用，这样返回的又是一个n项的无序集合，虽然看上去是有顺序的去掉左空格create view IS_Staras select *from Starwhere name like=”张%”with check option:约束条件是对视图里的所有的name首字符以下张开头的不管后续怎么修改。view:虚拟的表，具有普通表的结构，不实现数据存储，物理上不存在。5：事务事务：保证多个操作全部成功，否则全部失败，这处机制就是事务思考：下了个订单，但是在保存详细信息时出错了，这样可以成功吗？数据库中的事务：代码全都成功则提交，如果有某一条语句失败则回滚，整体失败事务操作：begin transaction-开始事务comit transaction-提交，没错后执行rollback transaction-回滚，出错后执行用法：声明一个变量，记录错误信息，最终根据变量值是否大于0，进行提交或回滚示例：模拟转账操作：一个账户扣钱，一个账户加钱=显式事务：sql server默认采用显式事务，即不需要明确调用就自动提交隐式事务：可以将事务设置为必须手动提交的Set implicit_transactions on示例：在一个窗口中修改，在另外一个窗口中查询6：存储过程：就是一个函数，用于存储一段处理代码好处：完成代码的封装，实现代码重用；安全方便应用程序与数据库间通信，不用传大量sql语句过程，而只用传一个存储过程名称过来，简单方便系统存储过程（在界面中打开看一看）l 自定义存储过程 create proc usp_test1 参数列表 as 自定义代码段l 调用执行：exec 存储过程名称create proc trimstr1 varchar(10)asbeginselect LTRIM(RTRIM(str1)end-'abc'exec trim ' abc '7：异常处理l begin tryl .l end tryl begin catchl .l end catchl 尽量少使用，会影响结果，可以通过error来查看上一条语句是否发生了错误8：索引：l 索引就像书的目录，找起来非常快捷方便l 分类：聚集索引：与实际存储位置一样，一个表中只能有一个聚集索引；非聚集索引，是逻辑上的一个划分，如学生在教室中的位置，可以按年龄进行查找，也可以按身高进行查找l 为什么快了呢？建了索引，就分了区，排了序，再找起来就快了l 是否要为列建索引呢？根据实际的sql语句进行分析，列出现在where中的次数较多，时间损耗较大，则为这个列建立索引l 注意：不是越多越好，因为每建立一个列索引，都要排序存储一次，会让数据库文件变大l 主键就是聚集索引，如果没有主键，才能自建聚集索引l 创建：界面操作，再查看代码l -建立索引l create index 索引名 on 表名l create unique index sidx on S9：触发器：l 作用：对表进行增、删、改操作时，自动进行一个操作l 根据触发机制不同，分为：after触发器，instead of替换触发器l 创建触发器： create trigger 名称 On 表名 After|instead of insert|delete|update As Begin endl 两个临时表：inserted、deletedl 示例：删除分公司时，将该分公司的所有部门都删除l 建议：对于性能影响太大，所以要慎重使用create trigger del_S on Safter deleteas delete from SCwhere SC.SNO in(select SNO from deleted)-after触发器不能在视图上定义，只能在表中定义。-instead of触发器可定义在表中和视图中，如果在使用了with check option 语句的视图上不能定义instead of触发器10：游标：l 可以逐条操作表中的数据l 缺点：性能低，慎重使用l 语法代码见备注l 示例：逐行输出语句，可以通过查看执行时间来对比select语句平时题目：1集函数包括那些？2如果关系D1有3个元组，关系D2有4个元组，select * from D1.D2有12个元组。3在用SQL创建基本表时，是通过foreign key实现参照完整性规则。4group by 字句总是出现在where之后，没有where也可以出现group by 5不依赖于外层查询的内层查询称为不相关子查询。6数据库系统包括数据库和数据库管理系统。7数据库系统的数据独立性体现在：不会因为系统数据存储结构和数据逻辑结构的变化而影响应用程序。 数据的物理独立性是指：用户的应用程序与存储在磁盘上的数据库中的数据是相互独立的。8常见数据模型：层次，网状，关系9用分组查询定义的视图不能直接进行数据更新10：一个关系数据库文件中的各条记录前后顺序可以颠倒，不影响库中数据关系。11：关系模型：关系数据结构，关系操作集合，完整性约束。关系模式每个属性是一个分量不可再分。13：某个属性值为null:说明属性值不确定14：数据库数据一般放在磁盘15：E-R图中每个实体型至少会与其它实体型或自身发生一个联系。16：delete truncate被外键引着不能被清空。1：where属性都是单个值，没有集合的概念2：Transact SQL中显示定义批的命令是：go3：视图是一种虚表，DBMS中存储其定义，不存储其数据，视图不能创建索引，因为索引要占内存空间视图是select语句的结果集，是放在内存中的数据4：返回一个值普通子查询：使用比较运算符5：返回一组值普通子查询：利用比较运算符和any,all6：where和in,exists,any,all,not in连用。7：使用sum要用group by,group by 要与聚合函数一起使用。-创建一个存储过程create proc QueryTeach(sno varchar(6),sn varchar(10) output,dept nvarchar(20) output)as select sn=SN,dept=dnofrom Swhere Sno=sno-查看存储过程源代码exec sp_helptext QueryTeach-重命名sp_rename QueryTeach,MyProcexec sp_helptext MyProc-执行不带参数的存储过程exec MyProc-执行带参数的存储过程exec MyProc 'S2','小刚','D1'-执行QueryTeach,执行完毕后结果保存到输出函数sn和deptdeclare sn nvarchar(10)declare dept nvarchar(20)exec MyProc 'S2',sn output,dept outputselect '姓名='+sn+',系别='+dept-查找学号为'S10'同学的信息，将查询到的同学的姓名，系别存到sn和dept中显示结果-修改存储过程alter proc MyProcasselect sn from S where sn like '孙%' and sex='女'-运行存储过程exec MyProc-结果就是一列一行'孙珊'触发器-update 操作包括删除原纪录，插入新纪录两步操作-对表执行了uodate操作在deleted表中存放原来的记录而在instead of 表中存放新的纪录例如:S表中删除一个学生该学生在SC表中记录全部删除创建触发器create trigger del_S on S after deleteas delete from SCwhere SC.snoin(select sno from deleted)激活触发器del_Sdelete from S where sno='S20'例如:SC表中添加新记录,学生必须已经存在S中创建触发器create trigger insert_SC on SC after insertasif exists( select * from inserted where sno in(select sno from S)print '添加成功!'else begin rollback transactionend-激活触发器insert SCvalues('S20','C1','99')权限授予grant select on SC to public exists查询所有选修了号课程的学生姓名select * from SC select * from Sselect snfrom Swhere exists(select * from SCwhere sno=S.sno and cno='C1')exists执行过程:现在外层查询取S的第一个元组,用该元祖的相关属性值处理内层查询,若外层的where字句返回true值则此元组送入结果的表中,然后再取下一个元组,重复上述直到外层表中的元组全部遍历一次.查询与刘晨在同一系的学生(同表查询)(一)select sn,sno,dnofrom Swhere dno in(select dnofrom Swhere sn='小刚')and sn<>'小刚'(二)select s1.sno,s1.sn,s1.dnofrom S s1,S s2where s1.dno=s2.dnoand s2.sn='小刚'(三)select S.sno,S.sn,S.dno from Swhere exists(select * from S s1where S.dno=s1.dnoand s1.sn='小刚' )等价关系:(=any)<=>(in) (>any)<=>(>MIN()(<>all)<=>(not in) (>all)<=>(>MAX()E-R图实体型:矩形框表示,框内标注实体名称属性:椭圆形框表示,框内注明属性名称,利用无向边与实体相连联系:练习用菱形框表示,框内标注联系的名称,用无向边与实体相连,同时边上标注联系类型,1:1,1:n,n:m每个联系有唯一的名字。（一）E-R图中实体转换为关系模型：实体的属性就是关系的属性，实体的码就是关系的码（二）E-R图中一个关系转换为关系模型1.一个-1联系:转换为一个独立的关系模式关系的候选码:每个实体的码均是该关系的候选码2.一个-n联系:关系的候选码:n端实体的码3.一个m-n联系转换为一个关系模式.关系的候选码:各实体码的组合数据库设计:（1）数据字典：数据项，数据结构，数据流，数据存储，处理过程。（2）设计E-R模型：自顶向下，自底向上，逐步扩张，混合策略。（E-R实体-联系方法）（3）数据抽象：分类和聚集。（4）E-R图中的冲突种类：属性种类，命名种类，结构冲突。（5）消除冗余：采用分析方法。数据字典和数据流图是分析冗余的联系的依据。（6）函数依赖：不完全函数依赖和传递函数依赖都会导致数据冗余，(sno,cn)->grade:是完全函数依赖(sno,cn)->dno:是不完全函数依赖关系模式的范式：（1） 一个属性构成的候选码作为主码，那么一定没有不完全函数依赖。专心-专注-专业