数据库知识点总结资料.doc

第一章 数据库基础1数据库系统：是由数据库及其管理软件组成的系统，常常把数据库有关的硬件和软件系统成为数据库系统, DBMS的英文全称DataBase Management System概念模型是指人对现实世界的认识，抽象成信息数据模型是指将现实世界转换成计算机能认识的信息SQL是英文全称是Structured Query Language2.数据库：数据库就是数据的仓库，由表、关系以及操作对象组成3.数据:是描述事物的符号记录（数字、文字、图形、图像、声音等）4.数据库的作用 存储大量数据，方便检索和访问 保持数据信息的一致、完整 共享和安全 通过组合分析，产生新的有用信息5.数据库经历的三个阶段及特点1） 人工管理阶段: 数据不保存；使用应用程序管理数据；数据不共享；数据不具有独立性。2） 文件系统阶段：数据可以长期保存；由文件系统管理数据；共享性差，数据冗余大；数据独立性差。3） 数据库系统阶段：数据结构化；数据共享性高；数据独立性强；数据粒度小；独立的数据操作界面；统一管理和控制6.数据模型的分类 层次模型 网络模型 关系模型 用二维表结构表达实体集的模型7. E-R图三个主要部分1)1.实体集：在E-R图中用长方形来表示实体集，实体是实体集的成员。 2) 联系： 在E-R图中用菱形来表示联系，联系与其涉及的实体集之间以直线连接， 并在直线端部标上联系的种类 , (1:1，1:N，M:N)。 3) 属性： 在E-R图中用椭圆形来表示实体集和联系的属性，对于主键码的属性， 在属性名下划一横线。8. 绘制E-R图所需的图形 1) 长方形框-实体集（考虑问题的对象） 2) 菱形框-联系(实体集间联系) 3) 椭圆形框-实体集和联系的属性 4) 直线-连接相关的联系和实体，并可标上联系的种类9. E-R图设计原则：真实性；避免冗余；简单性10.三大范式 第一范式：在关系模型中的每一个具体关系R中，如果每个属性都是不可再分的，则称关系（R）属于第一范式（1NF） 第二范式：如果关系模式R属于第一范式，且每一个非主属性都完全依赖于主码，则称关系R是属于第二范式的 第三范式：如果关系模式R为2NF,并且R中的每个非主属性不传递依赖于R的主码，则称关系R是属于第三范式的若要求分解保持函数依赖，那么模式分解一定能够达到BCNF第二章 数据库的安装1.常见的数据库类型：Access、SQL server2000、2005、2008，Oracle数据库等2. 数据库管理员的工作是：配置数据库服务器环境 ；管理数据库的逻辑对象结构； 配置数据库的对象权限 ；制定数据库的性能优化策略 ；数据库的备份还原策略 ；数据库的异构协同结构3. SQL Server 2008 的版本 Express 适用于无连接的客户端或独立应用程序Workgroup 适用于工作组或分支机构操作的数据库Standard 部门级应用程序的数据库服务器Enterprise 高度可伸缩和高度可用的企业级数据库Developer Enterprise 版，但是只授予开发和测试用许可Web 供托管公司提供低成本、高伸缩的托管服务，只收取低廉的每月许可费Mobile 用于智能手持式设备的精简数据库12.掌握SQL Server 2008数据库的安装与卸载不同的数据库之间若想互相使用数据可以采取导入导出的方式进行希望完全安装SQL Server 2008，则应选择典型安装第三章 数据库的管理1.T-SQL语言分类 DDL（数据定义语言）-create（创建）-alter （修改）-drop (删除)DQL（数据查询语言） -select（查询） DML（数据操作语言）-insert（插入） -update（更新） -delete(删除) DCL（数据控制语言） -revoke(撤销) -deny(拒绝) -grant(同意、授权)master数据库，记录所有系统级信息，记录其他所有数据库的信息的系统数据库model数据库创建其他所有数据库的模板的系统数据库msdb数据库与sql server 代理，与计划任务和作业等相关的系统数据库tempdb数据库存储数据库运行时的临时的信息的系统数据库2.数据库文件 主数据文件（.mdf）：一个数据库有且只有一个 辅助数据文件(.ndf)：根据需要自由选择，当数据库很大时，可以选择多个 日志文件(.ldf)：用于存储恢复数据库所需的事务日志信息数据库数据文件结构中，最小的存储单位是页数据库数据文件结构中，管理空间的基本单位是区SQL SERVER数据库中，每个表最多有1024个列标识表中唯一的实体是主键为数据表创建索引的目的是提高查询的检索性能存储过程是一组预先定义并（编译 ）的Transact-SQL语句3.掌握数据库的创建及修改（图形化及代码） 修改数据库包括:扩展、收缩、分离附加、删除4.语法 1）修改数据库名 Alter database 原数据库名 Modify name =新数据库名 例子：【例】将数据库book的名字改为books alter database book modify name=books 2）修改文件属性 Alter database 数据库名 Modify file (name=逻辑名, size=修改后的大小, maxsize=修改后的大小， filegrowth=修改后的大小) Go 例子：把初始大小由原来5mb增大为12mbalter database books modify file ( name=book_data, size=12mb ) go 3)添加日志文件 Alter database 数据库名 Add log file ( name= 逻辑名, filename = 文件的存放路径, size=日志文件的初始大小, maxsize=日志文件的最大大小, filegrowth=日志文件的增长方式 ) Go 例子：【例】向shop数据库中添加一个日志文件alter database shop add log file ( name=shop_log2, filename =c:shop_log2.ldf, size=10mb, maxsize=20mb, filegrowth=10% ) go 4）删除空文件 Alter database 数据库名 Remove file 文件的逻辑名 例子: 删除文件shop_data2 alter database shop remove file shop_data24） 添加辅助数据文件 alter database 数据库名 add file ( name=逻辑名, filename=文件存放的路径, size=初始大小， maxsixe=最大大小, filegrowth=增长方式 ) Go 例子：向数据库shop中添加一个辅助数据文件alter database shop add file ( name=shop_data3, filename=c:shop_data3.ndf, size=5mb, maxsize=10mb, filegrowth=10% ) go 5）创建/删除数据库 Create database 数据库名 on primary ( -数据文件 name=逻辑名， filename=文件的存放路径， size=数据文件的初始大小， maxsize=数据文件的最大大小， filegrowth=文件的增长方式 ) log on ( -日志文件 name=逻辑名， filename=文件的存放路径， size=数据文件的初始大小， maxsize=数据文件的最大大小， filegrowth=文件的增长方式 ) go 例子：创建一个名为book的数据库，其初始值大小为5MB，最大大小为 50MB，允许数据库自动增长，增长方式是按10%比例增长；日志文件初始 为2MB，最大可增长到5MB，按1MB增长。 create database book on primary ( name=book_data, filename=c:book_data.mdf, size=5mb, maxsize=50mb, filegrowth=10% ) log on ( name=book_Log, filename=c:book_log.ldf, size=2mb, maxsize=5mb, filegrowth=1mb) 6) 删除数据库 Drop database 数据库名 例子：删除数据库shop drop database shop打开或切换数据库的命令是use 数据库名收缩数据库northwind，保留10%的可用空间的命令是DBCC ShrinkDatabase(northwind,10)第四章 数据表的操作1. 在SQL server中创建表的极限： 每个数据库可以有20亿个表 ,每个表有1024个列 ,每个列可以有8064字节2. 数据类型种类数据类型数字整数int, bigint, smallint, tinyint精确数值decimal, numeric近似数值float, real货币money, smallmoney日期和时间datetime, smalldatetime字符Non-Unicodechar,varchar,varchar(max), textUnicodenchar,nvarchar,nvarchar(max), ntext二进制binary,varbinary,varbinary(max)图像image全局标识符uniqueidentifierXmlxml特殊bit, cursor, timestamp, sysname, table, sql_variant3.字符类型字符、数字、特殊符号 char( ) : 固定长度,以空格填补多余长度空间 varchar( ): 可变长长度,不以空格填补多余长度空间 nchar( ): unicode固定长度 nvarchar( ) : unicode可变长长度 unicode国际标准码： 双字节模式(固定每个字符16bit)一个汉字占两个字节(16bit) 一个字母占两个字节(16bit)电话号码应当采用字符格式的数据类型来存储int数据类型存储占几个字节 4smallint数据类型存储占几个字节 2tinyint数据类型存储占几个字节 1bigint数据类型存储占几个字节 8DECIMAL p s 的形式，p 和s 代表什么p代表总位数，s代表小数位数数据类型char每个字符占几个字节 1数据类型nchar每个字符占几个字节 2数据类型varchar每个字符占几个字节 1数据类型nvarchar每个字符占几个字节 2datetime数据类型的范围是表示的是1753年1月1日到9999年12月31日4.语法： 1）创建表create table 表名 2）修改表alter table 表名3）删除表drop/delete table 表名 4)将查出的信息放入新表Select * into 新表名 from 旧表名5） 添加的信息必须满足现有表结构Insert into 现有表 select * from 旧表名5.drop和delete、truncate的区别：drop是删除表，而delete和truncate是删除表中记录5. 数据操作语言DML select 从表或视图中检索数据 insert 将数据插入到表或视图中 update 修改表或视图中的数据 Update grade set score=score+5 where score=55 and score=30go5.外连接语法：select 字段名1，字段名2字段n From 表1 left outer join 表2 from 表1 right outer join 表2 from 表1 full outer join 表2 Go【例】查找所有学生信息及其考试成绩，若该生未参加任何考试，也列出其信息 use student_score select M.*,S.CID,S.score from Score S right join Member M on M.MID=S.MIDgo6.交叉连接语法：select字段1，字段2 From 表1 cross join 表2 Go【例】返回Member表和Score表所有可能记录的组合 use student_score select M.MID,M.Mname,CID,Score from Member M cross join Score S go在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询块称为子查询SQL中消除重复的记录命令是distinct第七章 函数1. 函数：是一段特殊的程序代码，它能对查询结果进行一定的操作 函数的作用：就是使用户不必书写太多的程序代码即可完成复杂的操作2. SQL Server函数类型包括系统函数、字符串函数、 日期时间函数、聚合函数等 3. 系统函数对SQL Server服务器和数据库对象进行操作，返回服务器配置和数据库对象数值等信息CURRENT_TIMESTAMP( )可返回系统当前日期和时间，类型为datetime HOST_NAME ()返回主机名称 SYSTEM_USER 返回当前系统用户 USER_NAME( ) 返回id 标识指定的用户名称 db_name()返回当前会话的数据库名称4. 聚合函数 功能 1)Sum（） 计算表达式所有值之和 必须是数值，数字类型【例】求选修01课程的学生的总成绩。 use student_score select sum(score) as 课程01总成绩 from Score where CID=01go2)Avg（） 计算表达式的平均值【例】求选修10课程的学生的平均成绩。 use student_score select avg(score) as 课程10平均成绩 from Score where CID=10go3)Count(列名) 计算表达式中非空值的数量【例】求学生的总成绩 use student_score select count() as 学生总成绩 from Membergo4)count(*) 计算表达式中所有值的数量【例】求学生的总人数 use student_score select count(*) as 学生总人数 from Membergo5）Min（） 计算表达式的最小值 【例】求选修00课程的学生的最低分 use student_score select min(score) as 课程00的最低分 from Score where CID=00 go6）Max（） 计算表达式的最大值【例】求选修11课程的学生的最高分 use student_score select max(score) as 课程11的最高分 from Score where CID=11go逻辑运算符：not or and第八章 数据的完整性1. 数据库完整性： 是指存储在数据库中数据的一致性和标准性2. 数据完整性的类型1）域（列）完整性：指定对列的一组有效值并决定是否允许有空值。也可以通 过在一列中限定数据类型，格式和可能值的范围来强制数据完整性。2） 实体（行）完整性：要求表中所有的行具有唯一的标识符，例如主关键字值。3） 参照完整性：确保维持主关键字和外部关键字的关系3. 约束的概念： 确保在列中输入有效的值并维护表之间的关系。4.约束的类型完整性类型约束类型说明域DEFAULT指定列的默认值CHECK指定列的允许值NULL指定是否允许NULL实体PRIMARY KEY唯一标识每一行UNIQUE防止非主键重复引用FOREIGN KEY定义值必须与此表的主键匹配的列5.default功能：执行insert语句时将默认值自动插入约束所在的列 语法： alter table 表名 add constraint 约束名 default(默认值) for 字段名 go【例】修改数据库student_score中的Menber表,将性别的默认值设置为男 use student_score alter table Member add constraint df_sex default(男) for Msex go6. check功能： check(检查约束),每次执行Insert/update语句时，该约束要校验数据的合法性语法：alter table 表名 add constraint 约束名 check(条件) go【例】 修改数据库student_score中的Score表,使成绩限制在0100之间 use student_score alter table Score add constraint ck_cj check(score=0 and score=100)go7. primary key功能： primary key(主键约束)，一个表中只有能一个pk，不能有空值，不能有 重复值；pk所在列的值能够唯一标识此行 ；能与fk关联， pk所在表为主表； 同时创建一个与PK约束名的簇索引。 语法： alter table 表名 add constraint 约束名 primary key(字段名) go【例】修改数据库student_score中的kc表,将课程号设置为主键use student_score alter table Course add constraint pk_kch primary key(CID) go8. Unique功能： unique(唯一约束), 指定在同一列中的两行不能有相同的值，只能有一行为 空值，该约束自动创建一个与约束同名的非簇索引,在插入/修改时自动执行。 语法： alter table 表名 add constraint 约束名 unique (字段名)go 【例】修改数据库student_score中的Course表,将课程名设置为unique约束 use student_score alter table Course add constraint un_kcm unique(Cname) go 9. FOREIGN KEY功能： 参照完整性确保维持主关键字（在主表中）和外部关键字（在辅表中）的关系。 语法： alter table 表名1 add constraint 约束名 foreign key(字段名) references 表名2(字段名) go【例】修改数据库student_score中的Score表,将课程号设置为外键use student_score alter table Score add constraint fk_kch foreign key(CID) references Course(CID)go10. 删除约束 语法： alter table 表名 drop constraint 约束名 【例】 删除 Menber表的fk_xh约束。 use student_score alter table Score drop constraint fk_xh go第九章 视图1.视图; 是从一个或多个表（或视图）导出的虚表。对于数据库用户来说，视图 似乎是一个真实的表，它具有一组命名的数据列和行。但是，与真实的表不 同，在视图中没有存储任何数据，仅仅是一种较简单的访问数据库里其他表 中数据的方式，因此称它为“虚表”。视图只能建立在当前正在使用的数据库中2.视图的优点聚焦数据，创建可以控制的环境，将需要的、常规的、适当的数据存储在视图中。 隐藏数据库的复杂性 简化对用户的许可管理 数据库拥有者只允许用户通过视图查询数据,保护了底层基本表的设计结构 提高性能 存储复杂查询、分割数据 输出数据给其他应用程序3.视图创建是应注意的情况 只能在当前数据库中创建视图。 如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新 的基表或者视图。 如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同， 则必须为列定义名称。 当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库 对象存在，而且数据修改语句不能违反数据完整性规则。 视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的2.语法; 1) create view 视图名 创建视图【例】创建v1视图，包括作者为马德保的书籍ISBN,名称，及单价 use book go create view v1 as select isbn,bookname,unitprice from bookinfo where author=马德保go2) drop view 视图名 删除视图 【例】删除名为v2的视图drop view v23) alter view 视图名 修改视图 【例】修改v1视图，包括出版社为工业出版社的书籍ISBN,作者及单价 use book go alter view v1 as select isbn,author,unitprice from bookinfo where publisher=工业出版社 go4) exec sp_helptext 视图名 查看视图定义 【例】查看视图v1: exec sp_helptext v15) update 视图名 更新视图 【例】将v1视图中书名为“Windows Server 2008活动目录”的单价增加10。use book go update v1 set unitprice=unitprice+10 where bookname=Windows Server 2008活动目录go第十章 索引1. 建立索引目的:是希望提高SQL Server数据检索的速度2.索引的分类 聚集索引 对表进行物理排序的索引是聚集索引 非聚集索引 唯一索引 3. 建立索引的原则1)定义主键数据列一定要建立索引 2)定义外键数据列上一定建立索引 3)对于经常查询的数据列最好建立索引 4)对于需要在指定范围内快速或频繁查询的数据列 5)经常用在where子句中的数据列 6)对于那些查询中很少涉及的列,重复值较多的列不要建立索引 7)对于定义为text、image和bit数据类型的列不要建立索引4.语法; 创建索引 Create unique 索引名 修改索引 alter unique 索引名 删除索引 drop unique 索引名例如：在xscj数据库中的xs表上创建名为ix_name的非聚集索引、唯一索引，该索引基于“姓名”列创建语法是 Create unique nonclustered Index ix_name on xs(姓名)更新索引统计信息的命令是update statistics对表进行数据操作可能会导致表碎片的产生，而碎片会导致读取额外页，从而造成数据查询性能的降低，可以使用(dbcc showcontig )语句扫描表 当表或视图上的聚集索引和非聚集索引页上存在碎片时，可以使用( dbcc indexdefrag )进行碎片整理第十一章 存储过程1. 存储过程的类型 系统存储过程 (名字以“sp_”为前缀,存储在master里) 本地存储过程 (存储在用户定义的数据库中) 扩展存储过程 (名字都以“xp_”为前缀,存储在master里) 临时存储过程 (名字以#开头)2.语法：1)Creat procedure 存储过程名 创建存储过程【例】创建名为p_book_info的存储过程,该存储过程查询所有书籍的信息use book go create procedure p_book_info as select * from bookinfo order by ISBNgo2)Exec procedure存储过程名 执行存储过程 【例】 p_order_detail可以通过以下方式执行 exec p_order_detail3)Alter procedure 存储过程名 修改存储过程【例】修改p_order_detail,返回订单id,用户姓名,用户地址,书籍单价, 书籍名称及 订单数量 alter procedure p_order_detail as select o.orderdetailid,u.username,u.addr,b.unitprice,b.bookname,o.quantity from orderdetail as o join userinfo as u on o.userid=u.userid join bookinfo as