SQL期末考试复习资料.docx

第一章数据库技术发展的三个阶段人工管理阶段、文件系统阶段、数据库系统阶段数据库的基本概念Data Base 数据库是为了实现一定的目的、按某种规则组织起来的“数据”的“集合”，简而言之，它是有结构的数据集合。数据库系统的三级模式结构 (考)外模式、模式、内模式外模式/模式映射 保证逻辑独立性模式/内模式映射 保证物理独立性数据模型的要素及其特点层次模型、网状模型、关系模型要素：数据结构： 数据操作：关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL）完整性约束：实体完整性、参照完整性和用户定义的完整性关系的特点：1、 每列数据项不可再分解2、 同一个关系中，不能出现相同的属性名3、 同一个关系中，不能有完全相同的元组4、 所有元组格式相同，长度相同；属性类型相同5、 行列顺序无关紧要关系的完整性（插入）：实体完整性、参照完整性关系的完整性（删除）：参照完整性规则关系的完整性（修改）=删除+插入实体之间的关系：（1）一对一关系（2）一对多关系（3）多对多关系常见数据库：foxpro 、access、sql server、mybase 、oracle第二章SQL Server 2008 新特性：可信、高效率、智能SQL（结构化查询语言）：数据定义语言（DDL）CREATE、DROP、ALTER数据操作语言（DML）SELECT、INSERT、UPDATE、DELETE数据控制语言（DCL）GRANT、REVOKE特点：一体化、高度非过程化、以同一种语法格式提供两种使用方式、语言简捷，易学易用SQL标识符1. 标识符的首字母必须是以下两种情况之一：所有在统一码(Unicode)2.0标准规定的字符，包括26个英文字母a-z和A-Z，以及其他一些语言字符，如汉字。下划线“_”、“”或“#”。2. 后续字符：除首字母约定外，还有数字字符及“$” 。3. 不允许是T-SQL的保留字4. 不允许嵌入空格或其他特殊字符(、#、#、)常规标识符：符合标识符的格式规则分隔标识符：使用了双引号（“”）或方括号（）等分隔符号，既可以遵守标识符命名规则，也可以不遵守标识符命名规则。第3章 创建和管理数据库数据库：是按照数据结构来组织、存储、和管理数据的仓库，是存储在一起的相关数据的集合。SQL Server2008的数据库对象主要包括表（table）、视图（view）、索引（index）、存储过程（stored procedure）、触发器（trigger）和约束（constraint）等。SQL Server 2008数据库主要由文件和文件组组成。文件主要分为3类：主要数据文件、次要数据文件和事务日志文件。文件组主要分为两种类型：主文件组和用户定义文件组。数据库对象的全名应该由服务器名、数据库名、拥有者名和对象名两种类型的实例：默认实例和命名实例（）数据库操作（使用SSMS和T-SQL创建、修改、删除数据库）新创建的数据库，系统对数据文件的默认值为：初始大小3MB，最大大小不限制，增量为1 MB。对日志文件的默认值为：初始大小1MB，最大大小不限制，增长方式为按10%比例增长。DROP DATABASE 数据表操作（使用SSMS和T-SQL创建、修改、删除数据表）create database userdb1on(name=userdb4_data,filename=E:sql_datauserdb4.mdf ,size=5,maxsize=10,filegrowth=1)log on( name=userdb4_log, filename=E:sql_datauserdb4.ldf , size=2 , maxsize=5 , filegrowth=1)创建、修改、删除约束（根据完整性规则应用SSMS和T-SQL设置表的主码、约束和外码等）添加日志文件：use booksys -使用数据库alter database booksys-修改数据库add log file-添加日志文件(name=booksys_ldf,filename=I:sql_datatest1booksys.ldf,size=2mb,maxsize=10mb,filegrowth=2mb)to filegroup 文件组名 -将文件添加到文件组中添加次要数据文件：use booksysalter database booksysadd file(name=booksys,filename=I:sql_datatest1booksys.ndf,size=2mb,maxsize=10mb,filegrowth=2mb)添加文件组：Alter database 数据库名Add filegroup 文件组名删除文件：Alter database 数据库名remove file 文件名删除数据库:Drop 数据库名创建表：use booksyscreate table book(bookid varchar(20) primary key,-主键bookname varchar(60) unique,-唯一约束editor char(8) not null,-非空约束price numeric(5,2) check(price20),-检查约束publish varchar(30) default(电子科大),-默认值publishdata datetime,qty int,foreign key(字段) references 参照表名(字段主键)）修改列：ALTER table 表名(ADD 列名类型约束，| ALTER COLUMN 列名数据类型约束，| DROP COLUMN列名）添加约束alter table borrowadd constraint pk_id primary key(bookid,cardid,bdate) 添加主键约束添加外键约束alter table borrowadd constraint fk_card foreign key(cardid)references reader(cardid)检查约束ALTER TABLE studentADD CONSTRAINT CHECK_sex CHECK (sex=男 or sex = 女)默认值ALTER TABLE studentADD CONSTRAINT df_sp DEFAULT 三院 FOR 列名 唯一约束ALTER TABLE studentADD CONSTRAINT uq_ID UNIQUE (ID)删除约束：ALTER TABLE 表名DROP 约束名常见的约束：1、NULL/NOT NULL：空值/非空值约束。2、 PRIMARY KEY：主键约束，等价非空、单值。3、CHECK (逻辑表达式)：检查约束。4、DEFAULT 常量表达式：默认值约束。5、UNIQUE：单值约束。6、foreign key REFERENCES 父表名 (主键)：外键约束删除表drop table 表名数据操作（使用SSMS和T-SQL添加、修改、删除数据）插入数据insert into book values(TP2001-001,数据结构,李国庆,22.00,清华大学出版社,2001-1-8,20)更新数据update student set classno=05 where spno=03删除数据delete student where spno=03删除列前必须先删除其上的约束第四章T-SQL编程与应用DECLARE (考)DECLARE变量名数据类型，变量名数据类型 ,n 通配符 (考)上课例子：SELECT * FROM 数据表WHERE 编号 LIKE 008%A-D%可能会查出的编号值为：A、006SABCB、007SFFGC、08ASXD、005B运算符优先级当一个复杂表达式中包含有多个运算符时，运算符的优先级决定了表达式计算和比较操作的先后顺序。运算符的优先级由高到低的顺序如下：（1）+（正）（负）（位反）（2）*（乘）/（除）%（取余）（3）+（加）+（字符串串联运算符）-（减）（4）=、=、=、!=、!、!（比较运算符）（5）（按位异或）&（按位与）| （按位或）（6）NOT（7）AND（8）ALL ANY BETWEEN IN LIKE OR SOME（逻辑运算符）（9）=（赋值）第五章 数据查询 (设计题一定考)查询语法格式 (考)SELECT 列名INTO 新表名FROM WHERE 逻辑表达式-列级条件GROUP BY 列名HAVING逻辑表达式-组级条件ORDER BY 列名asc升序，desc降序 常用的聚合函数 (考)sum()Avg()Max()Min()Count()Count(distinct)GROUP BY注意事项（1）在SELECT子句的字段列表中，除了聚集函数外，其他所出现的字段一定要在GROUP BY子句中有定义才行（2）SELECT子句的字段列表中不一定要有聚集函数，但至少要用到GROUP BY子句列表中的一个项目。（3）在SQL Server中text、ntext和image数据类型的字段不能作为GROUP BY子句的分组依据。（4）GROUP BY子句不能使用字段别名。交叉连接(笛卡尔积)SELECT fieldlist FROM table1,table2SELECT fieldlist FROM table1 cross JOIN table2连接SELECT fieldlistFROM table1 INNER | LEFT | RIGHT| FULL JOIN table2ON table1.column=table2.columnINNER JOIN: 如果表中有至少一个匹配，则返回行LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行FULL JOIN: 只要其中一个表中存在匹配，就返回行子查询(IN 比较运算符 EXISTS UNION) (考 选择、填空)UNION 操作符用于合并两个或多个 SELECT 语句的结果集。UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。Between：列名=开始值 and 列名2001-4、显示读者信息表中所有计算机系的男同学的信息select * from reader where dept=计算机系 and sex=男 -5、显示所有编者姓刘的图书情况select * from reader where name like 刘%-6、将book中的书籍信息按出版时间先后显示select * from book order by publishdate ASC-7、列出在book表中总共有多少种书select count(bookid) from book -8、列出水利水电出版社出版的书的种数SELECT bookname FROM bookwhere publish=水利水电出版社GROUP BY bookname-9、查找最贵和最便宜的书的价格SELECT max(price) FROM bookSELECT min(price) FROM book-10、各类书的总库存select bookname,count(qty) as 总库存 from book group by bookname-11、统计各出版社的图书库存总量select publish,count(qty) as 总库存 from book group by publish-12、将进行读者信息表reader和借书情况表borrow两张表进行各种连接Select * from reader JOIN borrow ON reader.cardid=borrow.cardidselect * from reader Left JOIN borrow ON reader.cardid=borrow.cardidselect * from reader Right JOIN borrow ON reader.cardid=borrow.cardid-创建查询某读者信息的存储过程proc_reader，（cardid在执行存储过程时给出）并调用。if exists (select * from sysobjects where name=proc_reader and type=P)begindrop procedure proc_readerprint 已删除！endelseprint不存在，可创建！create procedure proc_readercardid varchar(20) as select *from readerwhere cardid=cardidexec proc_reader cardid=s0111-创建DML（after）触发器tri_borrow，在图书借阅表borrow中添加借阅信息记录（bookid、cardid）后，-自动添加借阅日期为当前，并按天的借阅期限得到该书的应还日期；并且对应的书籍库存量减。create trigger tri_borrowon borrowAFTER INSERTasbegin print更新触发开始执行declare bookid varchar(