SQL2005基础知识

nullnullSQL－S2 复习总结目录目录第一章　数据库 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 第二章　数据库实现 第三章　T－SQL编程 第四章　高级查询 第五章　事务、索引和视图 第六章　存储过程 附录第1章　数据库设计第1章　数据库设计数据库设计的必要性 节省存储空间，效率高 能保证数据的完整性 方便数据库应用系统的开发，便于进一步扩展 数据库设计的步骤 需求分析阶段 收集信息 标识 采样口标识规范化 下载危险废物标识 下载医疗器械外包装标识图下载科目一标识图大全免费下载产品包装标识下载 对象 标识每个对象需要存储的详细信息 标识对象之间的关系第1章　数据库设计第1章　数据库设计概要设计阶段 绘制E－R图 确认需求信息的正确性和完整性 详细设计阶段 将E－R图转换为多张 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf ，进行逻辑设计 应用三大范式对表进行审核 E－R图（实体－关系图） 实体 现实世界中具有区分其他事物特征或属性并与其他实体有联系的对象，E－R图中用矩形表示。 属性 可以理解为实体的特征，E－R图中用椭圆表示。 关系 是实体间的联系，E－R图中用菱形表示。 E－R图中直线用来连接属性和实体集或实体与关系集第1章　数据库设计第1章　数据库设计实体关系图的映射关系 1：1－1对1 1：N－1对多和多对1 M：N－多对多 数据规范化 从关系型数据库中除去冗余数据的过程叫规范化，如果使用得当，是用于获得高效的关系型数据库表的逻辑结构的最好和最容易的方法。 第一范式（1NF） 确保每列的原子性，将数据库精简为最简单形式。 第二范式（2NF） 从表中删除冗余的列，确保每张表只描述一件事情。 第三范式（3NF） 标识所有依赖于其他数据的数据，确保每列与主键直接相关。第2章　数据库实现第2章　数据库实现数据库文件由3部分组成 主要数据文件：＊.mdf 次要数据文件：＊.ndf 日志文件：＊.ldf ［注：可以有多个数据库文件和日志文件，每个数据库文件必须有一个日志文件与之对应］ 创建数据库 Create database 数据库名 删除数据库 Drop database 数据库名第2章　数据库实现第2章　数据库实现语法： use master if exists ( select * from sysdatabases where name=‘ 数据库名 ’ ) drop database ‘ 数据库名’　－－有则删除数据库 GO create database ‘ 数据库名 ’ on ( /*主要数据库文件*/ name=‘数据库名_data’, filename=‘路径’, size=大小， filegrowth=增长率 ), ( /*次要数据库文件*/ … ) log on ( /*主要日志文件*/ name=‘数据库名_log’, filename=路径, size=大小， maxsize=最大容量， filegrowth=增长率 ), ( /*次要日志文件*/ … )第2章　数据库实现第2章　数据库实现SQL中的数据类型第2章　数据库实现第2章　数据库实现创建数据表 Create table 表名 ( 字段1　数据类型　列特征， 字段2　数据类型　列特征， … ) 删除数据表 Use 数据库名 If exists ( select * from sysobjects where name=‘表名’ ) drop table 表名 　－－删除数据表 Go第2章　数据库实现第2章　数据库实现常用约束类型 主键约束（PK＿）：即主键列，数据唯一且不能为空 唯一约束（UQ＿）：数据唯一，能出现一个空值 检查约束（CHK＿）：限制某列的取值范围和模式等 默认约束（DF＿）：设置某列的默认值 外键约束（FK＿）：用于在两表之间建立关系 添加和删除约束语法（创建好表后添加约束） Alter table 表名 Add［Drop］constraint 约束名　约束类型　具体的约束说明 例：添加默认约束 Alter table stuInfo add constrint DF_stuAge default (’18’) for stuAge第2章　数据库实现第2章　数据库实现创建数据表简易写法 Create table 表名 ( 列名　类型　是否为空　是否主键　是否有约束　默认值 … ) 例：create table stuMarks ( ID int identity(1,1) primary key not null, stuNO int foreign key references stuInfo(stuNo) not null, stuMark int check ( stuMark between 0 and 100 ) default (‘0’) )第2章　数据库实现第2章　数据库实现数据库有三层安全验证 登录到SQL　Server系统 成为要访问数据库的用户 需要足够的权限访问数据表 创建登录 创建windows登录 [ EXEC ]　sp_grantlogin　‘windows 域名/域账户’ 创建SQL登录 [ EXEC ]　sp_addlogin　‘账户’,‘密码’ 创建数据库用户 [ EXEC ]　sp_grantdbaccess　‘账户’,‘用户名’ 给数据库用户授权 Grant　权限　[on 表名]　TO　数据库用户 注意：每次只能授权一个表，省略表名时是授权数据库中的所有表。 例：grant select,insert,update on stuInfo to 用户名 授予建表权限 grant create table to 用户名第3章　T-SQL编程第3章　T-SQL编程全局变量与局部变量的区别 作用范围不同 局部变量仅限于定义它的程序内部，通常用来存储从表中查询的数据，或当做程序执行时的临时变量。 全局变量是在服务器级定义的，在任何程序中均可使用，常用来存储一些SQL配置设定值和效能统计数据，只能使用预先说明和定义的全局变量。 引用方式不同 局部变量必须先声明再使用。 全局变量可以直接引用。第3章　T-SQL编程第3章　T-SQL编程定义局部变量（以＠为前缀，可以包括特殊字符） 语法 Declare ＠varible_name DataType 赋值 Set @varible_name = value 或 select @varible_name = value(用于存储查询出的数据) 例：declare @a int,@@b int,@@@c int,@@@@d int 全局变量不能定义和修改，以＠＠为前缀，且我们定义的局部变量也不可以与全局变量相同。 如:declare @@error int　－－错误第3章　T-SQL编程第3章　T-SQL编程输出语句 print 局部变量 AS 自定义列名 select 局部变量 AS 自定义列名 例：select @@serverName AS ‘服务器名称’ IF-ELSE条件语句 语法 If (条件) Begin 语句或语句块 End Else Begin 语句或语句块 End 注：begin…end　相当于JAVA语言中的“｛｝”大括号，当只有一条语句时可以省略。第3章　T-SQL编程第3章　T-SQL编程WHILE 循环语句 语法 While (条件) begin 语句或语句块 [ break ] end CASE多分支语句 语法 Case when 条件1 then 结果1 when 条件2 then 结果2 End第4章　高级查询第4章　高级查询子查询 简单子查询 Select * from 表名 where 字段 [运算符] in (子查询) 注：IN适用于返回多条 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 的情况，NOT IN用于取反操作。 EXISTS和NOT EXISTS子查询 可以作为WHERE条件的子查询，但一般用于IF语句的存在检测 IF EXISTS（子查询） 语句或语句块 注：如果子查询结果非空则返回 true，否则返回 false。 例：select * from stuInfo where exists ( select * from stuMarks where stuNo = stuInfo.stuNo ) select * from stuInfo where stuNo in ( select stuNO from stuMarks ) select * from stuInfo inner join stuMarks on stuInfo.stuNo = stuMarks.stuNo第5章　事务、索引和视图第5章　事务、索引和视图事务是一种机制、一个操作序列，包含了一组数据命令，且所有命令都作为一个整体提交或回滚，要么成功，要么失败。是在数据库系统上执行并发操作时作为最小控制单元来使用的。 事务的ACID属性 原子性（Atomicity） 一致性（Consistency） 隔离性（Isolation） 持久性（Durability）第5章　事务、索引和视图第5章　事务、索引和视图如何创建事务 开始事务 Begin transaction (tran) 提交事务 Commit transaction (tran) 回滚（撤销）事务 Rollback transaction (tran) 事务分类 显式事务：用begin transaction明确指定事务的开始 隐式事务 自动提交事务 ［注：一旦事务回滚或提交，则该事务结束，常用的是显式事务］第5章　事务、索引和视图第5章　事务、索引和视图索引 索引是SQL编排数据的内部方法，存储在数据库的索引页中。 索引可以提高数据库检索速度，改善数据库性能 索引的分类 唯一索引（UNIQUE）：创建唯一约束时自动创建 主键索引：在创建主键时自动创建，是唯一索引的特殊类型 聚集索引（CLUSTERED）：聚集索引决定表中数据的物理存储顺序，一张表只能包含一个聚集索引，在创建主键时将自动创建一个聚集索引。 非聚集索引（NOCLUSTERED）：非聚集索引指定表中数据的逻辑顺序，一张表最多可有249个非聚集索引。第5章　事务、索引和视图第5章　事务、索引和视图创建索引 使用Microsoft SQL Server Management Studio（SQL企业管理器）创建 在表设计图中，单击鼠标右键，选择“索引/键”命令 使用T－SQL语句创建 Create [ unique ] [ clustered | noclustered] INDEX　索引名 ON　表名 ( 列名1［列名2…］) [ with fillfactor = x ] 删除索引 IF Exists ( select name from sysindexes where name = 索引名 ) drop index 表名.索引名　－－存在则删除 引用索引 Select * from 表名 with ( index = 索引名 ) where < 条件 >第5章　事务、索引和视图第5章　事务、索引和视图创建索引的标准 该列用于频繁搜索 该列用于对数据进行排序 该列中的值不同 不适于创建索引的情况 列中仅包含几个不同值 表中仅包含几行，这样在索引中搜索数据所花的时间会比在表中逐行搜索所花的时间更长第5章　事务、索引和视图第5章　事务、索引和视图视图 视图是一种虚拟表，是保存在数据库中的查询，充当着查询指定表的筛选器，可以基于一个或多个表，也可以基于其他视图、当前数据库或其他数据库。 视图的用途 筛选表中的行 防止未经许可的用户访问敏感数据 将多个物理数据表抽象成一个逻辑数据表 视图的优点 使结果更易理解，符合日常业务逻辑 获得数据更容易 限制数据检索以提高安全性 维护应用程序更容易第5章　事务、索引和视图第5章　事务、索引和视图创建视图 用SQL企业管理器创建 展开数据库，右击视图项选择“新建视图”即可 使用T－SQL语句创建 Create VIEW 视图名 AS < select 语句 > 例:IF exists ( select * from sysobjects where name=‘视图名’ ) Drop view 视图名 GO Create view 视图名 AS select 姓名 = stuName, 平均分 = ( writtenExam + labExam ) / 2 From stuInfo left join stuMarks on stuInfo.stuNo = stuMarks.stuNo GO /*--使用视图，同打开物理表一样--*/ Select * from 视图名第6章　存储过程第6章　存储过程存储过程是SQL语句和控制流语句预编译的集合，类似于JAVA中的方法，在创建时即在服务器上编译，执行起来比单个SQL语句快。 优点： 执行速度快，提高数据库访问速度 允许模块化程序设计 保持一致性和提高安全性 减少网络流量 分类 系统存储过程 用户自定义存储过程第6章　存储过程第6章　存储过程系统存储过程 所有系统存储过程都以 “ SP_” 或 “ XP_” 开头，系统管理员拥有这些存储过程的使用权限 常用系统存储过程如下： SP_database：列服务器上的所有数据库 SP_help：返回某个表的所有信息 SP_helpconstraint：查看某个表的约束 SP_helpindex：查看某个表的索引 SP_stored_procedures：列出当前环境下的所有存储过程 SP_password：添加或修改登录账户密码 XP_logininfo：查看账户信息（扩展存储过程） XP_cmdshell：完成DOS命令（扩展存储过程）第6章　存储过程第6章　存储过程用户定义存储过程 使用SQL企业管理器创建，步骤同视图创建 使用T－SQL语句创建CREATE PROCEDURE 存储过程名 存储过程要先创建，再调用，使用EXEC 存储过程存放于系统表 sysobjects 中 分类 不带参数的存储过程 带输入参数的存储过程 ( 同值传递 ) 带输出参数的存储过程 ( 相当于引用传递 ) 删除存储过程 DROP PROC[ EDURE ] 存储过程名第6章　存储过程第6章　存储过程创建存储过程通用语法 IF exists ( select * from sysobjects where name = ‘ 存储过程名 ’ drop proc 存储过程名 GO --注意：必须有，存储过程第一句必须是 Create Create proc 存储过程名 ［@参数1 类型 [ = 默认值 ] [ OUTPUT ] ］, … ［@参数n 类型 [ = 默认值 ] [ OUTPUT ] ］, AS SQL 语句 ］［ 注：（1）参数为可选，没有时则为不带参数存储过程 （2）OUTPUT 为输出标记，有则为带输出参数的存储过程，没有则为带输入参数的存储过程，需要注意的是调用带输出参数的存储过程时要定义接收变量，且在接收变量后必须添加　OUTPUT　关键字 （3）默认值为输入参数所独有，使用默认值可以使带输入参数的存储过程更灵活 ］第6章　存储过程第6章　存储过程SQL中的错误处理 PRINT 语句：只能临时显示错误信息，且只能显示给用户 RAISERROR 语句：返回给用户定义的错误信息时，可指定严重级别，设置系统变量记录所发生的错误。 语法 RaisError ( { msg_id | msg_str } { , severity , state } [ with option [ ,…n] ] Msg_id：在sysmessages系统表中指定用户定义的错误信息 Msg_str：用户定义的错误信息，最长255个字符 Severity：显示错误严重级别，可用范围0－18级，当其值大于10时，将自动设置全局变量＠＠ERROR为非零值 State：表示错误状态，是1－127的值 Option：指示是否将错误记录到服务器日志中附录附录关键代码： select 姓名=sname, 语文=sum(case subject when '语文' then score end), 数学=sum(case subject when '数学' then score end) from score group by sname附录附录查询只参加了N门考试的学员姓名和学号的存储过程 学员表为(stuInfo),分数表为(score)，关键代码如下： Create proc proc_find @n int AS select 姓名=(select sname from stuInfo where stuID=score.stuID), 学号=stuID from score group by stuID having count (*)=@n附录附录创建随机数 RAND ( 随机种子 ) 产生0-1之间的随机数 随机种子一般算法是:当前月份×100000＋当前秒数×1000＋当前毫秒数 即：随机种子＝( Datepart ( mm , getdate( ) ) * 100000 ) + ( Datepart ( ss , getdate()) * 1000 ) +Datepart ( ms , getdate() ) ——这样每次产生的随机数可以最大限度避免重复 Distinct 过滤掉重复数据 Select distinct 字段名 from 表名 where 条件 求本周、本月 DateDiff ( wk , 查询的字段名 , getdate() ) = 0 DateDiff ( mm, 查询的字段名 , getdate() ) = 0