数据库中级

数据库设计：

需求分析.概念结构设计(E-R模型).逻辑结构设计.数据库物理设计.数据库实施.数据库运行和维护

 

三范式：

第一范式：①一个表一个主键 ②每列均为原子列

第二范式：在第一范式的基础上，所有的非主键列要完全依赖与主键

第三范式：所有非主键列不能依赖与非主键列

 

创建数据库：

数据库文件：主文件（.mdf），次文件(.ndf)，日志文件(.ldf)

基本语法：

create database database_name

on primary

(

    name = '主文件名',（ 必填）filename = '主文件路径'（必填）

    [,size 文件初始大小][, filegrowth 文件增长增量][,maxsize 文件最大长度]

)

log on

(

    name = '日志文件名', （ 必填）filename = '日志文件名'（ 必填）

    [,size 文件初始大小][ ,filegrowth 文件增长增量][,maxsize 文件最大长度]

)

创建文件夹： exec xp_cmdshell 'mkdir 文件夹路径'

删除数据库：drop database 数据库名

 

创建表：

create table table_name

(

    列名 数据类型 约束（check约束.default.identity标识列.primary主键.FK外键.null为空或否.unipue唯一性）

)

约束命名规则：

  PK_列名→ 主键

  FK_列名→外键

  CK_列名→ check约束

  DF_列名→ 默认值

 

修改表的结构：

㈠添加.删除列

添加列：alter table table_name add  colum_name data_type

删除列：drop colum colum_name

㈡修改列的数据类型

  alter table table_name alter colum_name data_type

㈢添加.删除约束

  alter table table_name add constraint 约束名 约束（colum_name）

  alter table table_name drop constraint 约束名

 

sys.databases→找数据库

sys.objects→找表

创建登录用户: exec sp_addlogin '用户名', '密码'

关联数据库：exec sp_grantdbaccess '登录名','数据库用户名'

            grant select,insert,update,delete [all]

on ' table_name ' to' 数据库用户名'

删除权限：revoke delete '表名' to '数据库用户名'

 

T-SQL编程

使用变量：

㈠局部变量:（只能在一个批中使用 go）

  声明：declare @名称1 数据类型,@名称2 数据类型，……

  赋值：（为初始化则均为空）

        ①单赋值:set @变量=值（不允许同时给多个变量赋值）

        ②多赋值：select @变量1=值1,@变量2=值3，……

㈡全局变量

 有效范围：SQL Server服务器

由系统自己定义，不能自定义，不能修改，只读

@@error 上一条语句最后一个错误编号，无错为0；

@@identity 最后一个插入的标识列

 

流程控制语句:

IF-ELSE条件语句：

if (条件)

  begin

         ……

  end

else （条件）

 begin

……

end

WHILE循环语句：

while（条件）

 begin

……

end

CASE多分支语句：

 Case

      when 条件1 then 结果1

when 条件2 then 结果2

       ……

else 结果N

 

高级查询

SELECT语句查询一般格式：

 SELECT [ALL | DISTINCT]<目标列表达式>[,<目标列表达式>] ……

 FROM<表名或视图名>[,<表名或视图名>]……

  [WHERE<条件表达式>]

[GROUP BY <列名1>[HAVING<条件表达式>]]

[ORDER BY<列名2>[ASC|DESC]]

[COMPUTE<统计表达式>[BY<列名3>]]

[WITH{CUBE|ROLLUP}]

 含义：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，在按SELECT子句的目标表达式，选出元组中的属性值形成结果集。如果有GROUP子句，则结果按<列名1>的值进行分组，该属性列值相等的元组为一个组，每个组产生结果表中的一条记录。通常会在每个组中作用集合函数，如果GROUP子句带HAVING短语，则只有满足指定条件的组才能输出。如果有ORDER子句，则结果还要按<列名2>的值的升序或降序排序如果有COMPUTE子句，则结果既有汇总的详细信息又有摘要信息。

 

UNION（联合）:

 ① 合并前，列数相等

 ②对应列的数据类型相似

 

子查询与连接：

①        子查询:用于结果在一个表中，条件在另一个表中（先执行内部在执行外部）

②        连接:结果再多个表中

 

相关子查询：EXISTS

  必须把外层表带入内层判断中

 

事务.索引.视图

事务：把一条或多条语句一起执行，要么都不执行

事务属性：原子性，一致性，隔离性，持久性，简称ACID特性

事务分类：

①        显示事务

②        隐式事务 [insert,update,delete]

③        自动提交事务 [成功则提交，失败则回滚]

 

开启事务：set implicit_transactions on

关闭事务：set implicit_transactions off

 

创建事务：

 Begin tran

      语句

  Rollback tran  回滚到初始状态（未改）

   Commit tran  提交结束（已修改）

 

索引：（提高查询效率，降低增删改效率）

为加速对表中数据行的检索而创建的

索引分类：

  聚集索引：一个表只能有一个，

  非聚集索引：一个表可以有多个，

唯一索引：设置字段必须无重复记录

复合索引： 视图索引： 全文索引：

索引使用：数据量大，常用作查找的列中，两个表的相关列（join）

不使用索引：数据量小，存在数据大量重复，image，text类型数据不能建索引

 

创建索引语法：

Create [UNIQUE][CLUSTERED][NONCLUSTERED] INDEX index_name

ON [DATABASE.]table_name|view_name(column[ASC|DESC])

 

当一个表中没有聚集索引，在表中主键建一个索引，则为主键索引

 

删除索引：DROP INDEX index_name

 

视图（虚拟表）

创建视图语法：

create view view_name

as

  select ……()                 只能是select语句，且只有一条

 

修改视图中的数据，即修改了基表中的数据

只能修改一个基表中的数据，不能同时影响多个表中的数据

 

                   存储过程

存储过程分类：

①        系统存储过程：（master数据库中）sp_为前缀

②        用户自定义存储过程：xp_为前缀

③        扩展存储过程：

 

常用系统存储过程：

 sp_database:显示所有数据库信息

sp_rename:在当前数据库中更改用户创建的对象名称

sp_renamedb:修改数据库名

sp_helptext:返回用户定义的规则.默认值.存储过程.触发器.函数.视图等。

sp_columns：返回指定数据库表或视图的列信息

用户自定义存储过程语法：

 create proc proc_name

@parameter data_type[=default][output]

[,@parameter1 data_type[=default][output]]

 

as

   SQL语句

go

 

带输出参数的存储过程：

①定义变量

②传变量到过程中去

③传变量是后须加OUTPUT

 

                     触发器

触发器是一种特殊的存储过程。

每个触发器都创建了两个专用表：inserted表和deleted表，用户只有读取的权利没有修改的权利，它们存放与数据库服务的内存中

Inserted表：存放由于insert或update语句的执行二导致的要加到该触发表中的所有新列

Deleted表:对于删除操作，deleted表中存放的是倍删除的旧行。

触发器种类：DML触发器和DDL触发器

触发器分类：insert触发器，delete触发器，update触发器

触发器是定义在特定表，与表关联，自动触发，不能直接调用，是一个事务（可回滚）

 

创建触发器语法：

create trigger trigger_name

on table|view

for delete|update|insert

as

  SQL语句

go

 

update触发器是先将原来记录删除放到delete表中，在讲新记录放到inserted表中

 

SET NOCOUNT ON 不显示几行受影响

 

Raiserror显示用户自定义的错误信息

Raiserror（msg_id|msg_str|severity,state with option [1-13]）

msg_id:在SYSMESSAGE系统中指定用户定义错误的信息

msg_str：用户定义的特定信息，最长255个字符

severity：定义严重性质级别，级别0-18级

state:错误的状态，1-127之间