SQL期末考试复习资料.docx

1、第一童数据库技术发展的三个阶段人工管理阶段、文件系统阶段、数据库系统阶段数据库的基本概念Data Base 数据库是为了实现一定的目的、按某种规则组织起来的数据的”集合 , 简而言之, 它是有结构的数据集合。数据库系统的三级模式结构( 考 )外模式、模式、内模式外模式 / 模式映射保证逻辑独立性模式 / 内模式映射保证物理独立性数据模型的要素及其特点层次模型、网状模型、关系模型要素：数据结构:数据操作: 关系代数、关系演算、具有关系代数和关系演算双重特点的语言( SQL )完整性约束：实体完整性、参照完整性和用户定义的完整性关系的特点：1 、 每列数据项不可再分解2、同一个关系中，不能出现相同

2、的属性名3 、 同一个关系中, 不能有完全相同的元组4 、 所有元组格式相同, 长度相同；属性类型相同5 、行列顺序无关紧要 关系的完整性（插入）：实体完整性、参照完整性关系的完整性（删除）：参照完整性规则关系的完整性（修改）二删除+插入实体之间的关系：（1 ）一对一关系（2） 一对多关系（3） 多多关系常见数据库：fox pro 、 access, sql server, mybase 、 oracle第章SQL Server 2008 新特性 : 可信、高效率、智能SQL （结构化查询语言）:数据定义语言（DDL ） CREATE. DROP 、 ALTER数据操作语言（DML） SELE

3、CT、 INSERT、 UPDATE. DELETE数据控制语言（DCL ） GRANT. REVOKEr Create Table/Alter Table/Drop Table-定义基本表(DDL 彳 Create View/Drop View :视图操作Create Index/Drop Index :索引操作r InsertDeleteSelect< UpdateGrant匚 Revoke嵌入式SQL的使用规定DMLDCL记录操作权限管理易学易用特点：一体化、高度非过程化、以同一种语法格式提供两种使用方式、语言简捷SQL标识符1.标识符的首字母必须是以下两种情况之一所有在统一码（U

4、nicode）2.0 标准 规定的字符，包括26个英文字母a-z和A-Z,以及其他一些语言字符,如汉字。下划线 _、/或 # # 。2.后续字符：除首字母约定外,还有数字字符及$"。3 .不允许是SQL的保留字4 .不允许嵌入空格或其他特殊字符 （ #、#、） 常规标识符：符合标识符的格式规则 分隔标识符：使用了双引号（ “）或方括号（口）等分隔符号，既可以遵守标识 符命名规则, 也可以不遵守标识符命名规则第 3 章创建和管理数据库数据库：是按照数据结构来组织、存储、和管理数据的仓库，是存储在一起的相关数据的集合。SQL Server2008 的数据库对象主要包括表( 视索引 ( i

5、ndex 存储过程 ( stored procedure )、触发器 ( trigger) 和约束 ( constraint) 等。SQL Server 2008 数据库主要由文件和文件组组成。文件主要分为3 类：主要数据文件、次要数据文件和事务日志文件。文件组主要分为两种类型：主文件组和用户定义文件组。数据库对象的全名应该由服务器名、数据库名、拥有者名和对象名两种类型的实例：默认实例和命名实例(计算机名称实例名称)数据库操作( 使用 SSMS 和 T-SQL 创建、修改、删除数据库)新创建的数据库，系统对数据文件的默认值为：初始大小3MB, 最大大小不限制，增量为1 MB 。对日志文件的默认

6、值为：初始大小1MB , 最大大小不限制，增长方式为按10% 比例增长。DROP DATABASE数据表操作（ 使用 SSMS 和 SQL 创建、修改、删除数据表）createdatabase userdblon（name=userdb4_data ffilename= 1 E:sql_datauserdb4.mdf 1 ,size=5,maxsize=10,filegrowth=l logonname=userdb4_log ffilename= 1 E:sql_datauserdb4.ldf! ,size=2 ,maxsize=5 , filegrowth=l)创建、修改、删除约束( 根据

7、完整性规则应用SSMS 和 T-SQL 设置表的主码、约束和外码等)添加日志文件：use booksys- 使用数据库alterdatabase booksys- 修改数据库addlogfile - 添加 I 志文件 (name-booksys_ldf,filename= 1 I:sql_datatestlbooksys.ldf1 ,size=2mb, maxsize=10mb a filegrowth=2mb ) tofilegroup 文件组名一将文件添加到文件组中添加次要数据文件：use booksys alterdatabase booksys addfile(name=booksys

8、,filename= 1 I:sql_datatest1booksys.ndf1zsize-2mb,maxsize=10mb,filegrowth=2mb)添加文件组：Al. terdatabase 数据库乞Addfilegroup 文件组名删除文件：Al t erdat abaremove file文件名删除数据库：Drop 数据库名 创建表：use booksyscreatetable bookbookid varchar (20 ) primarykey, - 唯 - 哟束 editor char (8)notnull,- price numeric (5,2) check (price

9、>20)主键 bookname varchar (60) unique,非空约束, - 检查约束publish varchar (30)default ( ' 电子无斗人'), 一一默认彳肖publishdata d&tetime,qty int,foreignkey ( 字段 ) references 参照表名( 字段主键)修改列：ALTER teble 表名(ADD列名类型约束，I ALTERCOLUMN 列名数据类型约束，I DROPCOLUMN 歹 M添加约束altertable borrow- 添加主键约束reader(cardid)addconstra

10、int pk_id primarykey(bookid,cardid,bdate)添加外键约束altertable borrowaddconstraint fk_card foreignkey(cardid)references检查约束ALTERTABLE studentADDCONSTRAINT CHECK_sex CHECK(sex= 11 or sex =' 女)默认值ALTERTABLE studentADDCONSTRAINT df_sp DEFAULT f HK 1 FOR 歹 U 名唯一约束ALTERTABLE studentADDCONSTRAINT uq_ID UNI

11、QUE (ID)删除约束：ALTERTABLE 衣名DROP约束名常见的约束：1、 、 NULL/NOT NULL : 空甸非空值约束。2、 PRIMARY KEY : 主键约束 ， 等价非空、单值。3、 CHECK （逻辑表达式）：检查约束。4、 DEFAULT 常量表达式: 默认值约束。5、 UNIQUE: 单值约束。6、 foreign key REFERENCES 父表名（主键）：外键约束删除表droptable 表名数据操作（使用SSMS 和 SQL 添加、修改、删除数据）* TP2001-001', )* 数据结构J ，李国庆J *22.00*, ，清插入数据inserti

12、nto book values华大 学出版社- *2001-1-8 * , 20更新数据update student set classno=105 1 where spno= 103 1103 1删除数据delete student where spno=删除列前必须先删除其上的约束第四章T-SQL编程与应用DECLARE （考）DECLARE磔量多数据类型， 磔量名数据类型，n 通配符（考）通配符（下划线）描述示例包含零个或更多字符的任意 字符串。WHERE title LIKE ' Wcoinputer%'将查找处于书名任意位置的包 含单词computer 的所有书名c任

13、何单个字符指定范围（a-f） 或集台（abcdef）中的任 何单个字符。WHERE au fname LIKE ' ean J 将 查找以ean结尾的所有4个 字母的名孑（Dean、Sean等）v = WHERE au_lname LIKEC-P arsen* 将查找以 arsen 结 尾且以介于C与P之间的 任何单个字 符开始的作者姓氏，例如.Carsen、Larsen、Karsen 等。上课例子：不属于指定范（a-f）或集合（Labe def j ）的任何单个子付。WHERE auNname LIKE '将查找以de开始且其W的字母不为1的所有作者的姓氏。SELECT *

14、FROM 数据表 WHERE 编号 LIKE' 00 A8%A-D%#可能会查出的编号值为:A、006SABCB、 007SFFGC、 08ASXD、 005B运算符优先级当一个复杂表达式中包含有多个运算符时，运算符的优先级决定了表达式计算和比较操作的先后顺序。运算符的优先级由高到低的顺序如下：1 ）+ （正）- （ 负） （位反）2 2） * （乘）/ （除）% （取余）（3 ） + （加）+ （字符串串联运算符）（减）（4）二、二、二、！二、！ 、！ v （比较运算符）5 ） A （按位异或）& （按位与）| （按位或）6 ） NOT7 ) AND8 ) ALL ANY B

15、ETWEEN IN LIKE OR SOME (逻辑运算符) （ 9 ）二（赋值）第五章数据查询（设计题一定考）查询语法格式（考）SELECT 列名INTO 新农名FROMC表或视图WHEREI辑表达式一列级条件GROUPBY 列多HAVING 逻辑表达式一组级条件ORDERBY歹U名asc升序，desc降序常用的聚合函数（考）sum （）Avg （）Max （）Min （）Count （）Count （ distinct ）GROUP BY 注意事项（ 1 ）在 SELECT 子句的字段列表中，除了聚集函数外，其他所出现的字段一定要在 GROUP BY 子句中有定义才行（ 2 ） SELEC

16、T 子句的字段列表中不一定要有聚集函数旦至少要用到GROUP BY子句列表中的一个项目(3 )在5、1 Server中text、ntext和image数据类型的字段不能作为 GROUPBY子句的分组依据。(4 ) GROUP BY 子句不能使用字段别名。交叉连接(笛卡尔积)SELECT fieldlist FROM tablel,table2SELECT fieldlist FROM tablel crossJOIN table2连接SELECT fieldlistFROM tablel INNER | LEFT | RIGHT| FULL JOIN table2ON tablel.colum

17、n=table2.columnINNER JOIN:如果表中有至少一个匹配，则返回行LEFT JOIN:即使右表中没有匹配，也从左表返回所有的行RIGHT JOIN:即使左表中没有匹配，也从右表返回所有的行FULL JOIN:只要其中一个表中存在匹配，就返回行子查询(IN比较运算符EXISTSUNION)(考 选择、填空)UNION操作符用于合并两个或多个 SELECT语句的结果集。UNION内部的SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条SELECT语句中的列的顺序必须相同。Between :列名 > 二开始值and列名 < 二结束值(考)(1)

18、 NOT:不满足条件(2) AND :满足所有条件(3) OR :满足其中任意一个条件DISTINCT 消除重复记录（考 ）弟八早二级存储映象三级模式SQLSERVER 数据库对象Transact-SQL DDL外模式视图（ VIEW）逻辑独立性模式基本表（ TABLE ）物理独立性内模式数据库（ DATABASE）CREATE VIEWCREATE TABLECREATE DATABASE（考）视图 （ View ） 是一个虚拟表。视图作用（考多选题）视图能简化用户的操作、提高安全性、逻辑数据独立性修改数据注意事项（不能通过视图修改数据）:? Select 列表中含有Distinct? Se

19、lect 列表中含有表达式，如计算列?在from 子句中引用多个表?引用不可更新的视图? GROUP BY 或 Having 子句?视图中不含非空列函数等创建视图：（考）CREATEVIEW view_nameAS select_statement修改视图：ALTERVIEW view_name AS select_statement删除视图：DROPVIEW视图名视图添加数据Insert into视图名values （列值，列值，列值 n）视图更新数据update 视图名 set 列 =列值，列=列值Where 条件表达式视图删除数据DELETEFROM视图名WHEREI辑表达式第七章索引与

20、数据完整性索引优点（考）提高数据库的查找速度、保证列的惟一性、加快表与表之间的连接速度、减少查询中分组和排序的时间、检索数据的过程中使用优化隐藏器，提高系统性能索引类型（考）存储结构：聚集索引（物理顺序与索引顺序相同、_个表一个）和非聚集索引（物理顺序与索引顺序不相同、一个表可有多个）数据唯一性: 唯一索引 （ UNIQUEIndex ） 和非唯一索引 （ Nonunique Index ）唯一索引是指索引值必须是唯一的, 不允许数据表中具有两行相同的索引值.键列的个数：单列索引和多列索引索引分类（考）聚集索引、非聚集索引、唯一索引、索引视图、全文索引 关键字- 唯 -S 聚集，非聚集CREA

21、TEUNIQUE CLUSTERED | NONCLUSTEREDINDEX 索弓 | 名ON 表（视图）名（列名ASCIDESC）删除索引dropindex 表名 . 索弓 I 名数据完整性（ 1 ） 数据类型准确无误。（ 2 ） 数据取值符合规定的范围。（3 ） 多个数据表之间的数据不存在冲突。数据完整性机制（ 考 ）( 行 1 域完整性( 列 1 引用完整性( 表与表 1 用户定义完整性以下为了解即可, 不知道考不考(1) 唯一索引 (Unique Index) 。(2) 主键 (Primary Key) 。(3) 唯一码(Unique Key) 。(4) 标识列(Identity Co

22、lumn)。域完整性常见的实现机制包括以下几点(1) 默认值(Default) o(2) 检查(Check) 。(3) 外键(Foreign Key)。(4) 数据类型 (Data Type) 。(5) 规则(Rule) o(1) 当主表中没有关联的记录时，将记录添加到相关表中。(2) 更改主表中的值并导致相关表中的记录孤立。(3) 从主表中删除记录，但仍存在与该记录匹配的相关记录。(1) 规则 (Rule) o(2) 触发器 (Trigger) 。(3) 存储过程 (Stored Procedure) 。(4) 创建数据表时的所有约束(Constraint) 存储过程优点（ 考选择题）允许标

23、准组件式编程, 可以嵌套使用, 增强重用性和共享性可以接受并使用参数动态执行其中的SQL 语句比一般的SQL 语句执行速度快具有安全特性允许模块俯辩设计减少网络通信流量可以强制应用程序的安全性存储过程类别（ 考 ）系统提供的存储过程、用户自走义存储过程和扩展存储过程。创建语法CREATEPROCEDURE proc procedure_name 参数 with recompile （ 重新编译） |encryption （ 加密 ）（ 考 ） As sql 语句调用方法Exec 存储过程名参数变量传值的调用方法：declare变量名数据类型set 变量名Exec procedure_name变

24、量名参数修改存储过程:alterPROCEDURE proc procedure_nameAs sq _L语句删除存储过程dropproc 存储过程名获得存储过程信息：sp_helptext查看存储过程的相关性信息：sp.depends查看存储过程的一般信息：sp_help例子：createproc p_stu_avgstu_id char (10),avg_cj intoutputasselect avg_cj=avg(chengji)from xuankewhere xkid=stu_iddeclare cj intBS 平均成绩exec p_stu_avg stu_id= 109002

25、1 r avg_cj=cj output select c j触发器 ： trigger触发器分为DML 触发器和DDL 触发器。DML虫发器可以分为如下5种类型。UPDATED2) INSERT蛾器DELETE虫发器INSTEAD OF虫发器根据触发的方式AFTER虫发器创建 DM L 触发器语法：CREATETRIGGER schema_name ? trigger_nameONtable|viewFOR| AFTER |INSTEADOF INSERT , UPDATE , DELETE ASsql_statementDDL 触发器只能在触发事件发生后才会调用执行，即它只能是AFTER

26、触发器。创建 DDL 触发器语句（无设计题）CREATETRIGGER trigger_nameONALLSERVER I DATABASEWith encryptionFORI AFTER 触发的事件类型AS sql_statement删除触发器DROPTRIGGER触发器名管理触发器修改触发器、删除触发器、禁用（启用）触发器第九章事务系统函数：标量函数，聚合函数，行集函数。（考）三种用户自定义函数：标量函数：返回单个数据值。（考设计题）内连表值函数：返回值是一个记录集合表。在此函数中 z return 语句包含条单独的select 语句。多语句表值函数：返回值是由选择的结果构成的记录集。函

27、数不能有输出参数，存储过程可以有输出参数。创建函数语法：（考设计题）createfunction 函数名（形参名数据类型,形参名数据类型n ） returns 返回值数据类型as begin命令行或程序块（必须有Return 子句）End调用函数：Print dbo. 函数（实参）或select dbo. 函数（实参）删除自定义函数dropfunction 函数名Pivot（ 必考）select sex, 信息学院计算机学院机械学院from （ select a.studentID,a.sex a.speciality fromstudent a ） as tpivot （ count （ s

28、tudentID ） for speciality in （信息学院，计算机学院机械学院）as b事务属性：原子性（Atomicity ）、一致性（ Consistency ） 、隔离线 （ Isolation ） .持久性 （ Durability ）（ 考）事务管理主要使用四条语句（考）1. BEGIN TRANSACTION （事务的起点）2. COMMIT TRANSACTION （ 事务的终点）3. ROLLBACK TRANSACTION （ 事务回滚 ）4. SAVE TRANSACTION （ 事务保存点）类型：系统提供事务、用户定义事务（考）模式 : 自动提交事务、显式事务、

29、隐性事务（考）并发问题：丢失更新、未确认的相关性（脏读）、不一致的分析（不可重复读X 幻象读死锁条件：互斥条件、请求与保持条件、不剥夺条件、循环等待条件锁：共享锁S、独占锁X、更新锁U （考）第十章SQL Server 的安全性管理是建立在验证( authentication ) 和访问许可(permission ) 这两种机制上的。SQL Server 2008 的验证方式包括Windows 验证模式与混合验证模式.SQL Server 的安全模型中包括以下几部分：? SQL Server 身份验证；?登录账户；?数据库用户；?角色；?权限在 SQL Server 中 , 账号有两种：一种是

30、登录服务器的登录账号, 另外一种就是使用数据库的用户账号.创建登陆名：createlogin登录名 with password 密码，修改登录名：alterloginA:录名 with password= z 密码，删除登录名：droplogin 登录名课后作业- 作业二- -IN显 zjxbook 表中所有书号、书名和出版社select bookid,bookname,publish from book- 2、 显示所有的书目(DISTINCT)selectdistinet bo oknmme from book orderby bookname一 3、显示年以后出版的书select*fro

31、m book where(year)publishdate)>2001- 4 、显示读者信息表中所有计算机系的男同学的信息1select*f rom reader where dept=1 计算机系1 and sex= ' 男- -5 、 显示所有编者姓刘的图书情况select* from reader wherename1ike' 夫 U% '- -6、将b o o k 中的I!;籍信息按出版时间先后显示select*from book orderby publishdate ASC- 7、 列出在 book 表中总共有多少种书selectcount(booki

32、d)from book- 8. 列出水利水电出版社出版的书的种数SELECT bookname FROM bookwhere publish= ，水利水电出版社，GROUPBY bookname- -9 、 查找最贵和最便宜的书的价格SELECTmax(price)FROM bookSELECTmin(price)FROM book- 10 、各类书的总库存select booknmme, count (qty) ms总库存 from book groupby bookname- IK 统计各出版社的图书库存总量select publish, count (qty)BS 总库存 from book groupb