SQL基础——mssql2005

SQL中可用的函数

字符函数：

CHARINDEX

LEN                     返回字符串的长度

LOWER         将字符串转换为小写

UPPER          将字符串转换为大写

LTRIM           清除字符串左边的空格

RTRIM           清除字符串右边的空格

RIGHT           从字符串右边返回

REPLACE

STUFF

数值函数：

 

日期函数：

GETDATE             取得当前日期

DATEADD           

DATEDIFF    

DATENAME

DATEPART

转换函数：

 

通用函数：

-------------------------------------1数据设计--------------------------

1。什么是数据库设计

是将数据库中的数据对象及这些数据对象的之间的关系，进行规划和机构化的过程。

2.数据设计非常重要

良好的数据库设计应表现为一下几点

1）效率高                          节省空间

2）便于更近一步的扩展                   数据完整性（重）

3）使得应用程序开发变得更容易            方便数据应用系统开发。

 

1、需求分析阶段

包括：

 1收集信息                              

 2标识对象

 3标识每个对象需要存储的详细信息。

 4标识对象之间的关系。

 

2。概要设计阶段

3，详细设计阶段

 

实体：是指现实世界中有区分其他事务的特征或属性，并与其他实体有联系的对象。

实体关系：

一对一：x中的一个实体最多与y中的一个实体关联，y中的一个实体最多只可与x中的一个实体关联。

一对多：x中的一个实体可以与y中的任意数量的实体关联，y中的实体最多与x中的一个实体关联。

多对一：x中的一个实体最多与y中的一个实体关联，y中的一个实体可以与x中的任意数量的实体关联。

多对多:x中的一个实体可以和y中的任意数量的实体关联，反之亦然。

 

实体关系图

矩形表示实体集

椭圆形表示属性

菱形表示关系集

直线用来连接属性和实体集，也用来连接实体集和关系集。

 

规范设计

1.第一范式：每列的原子性：不可再拆分。

2.第二范式：确保表中的每列都和主键相关。

3.第三范式：每列都和主键列直接相关，而不是间接相关。

 

 

----------------------------------2数据库实现------------------------

主数据文件：db.mdf。       (主要数据文件存放)

次要数据文件：db.ndf。     (可以分批将数据文件保存)

日志文件：db.ldf。      (记录什么时间，谁登陆，有些什么样的操作内容。可同日志文件恢复*.mdf)

 

(如果能算出数据文件有多大，就直接设为多大)

(日志文件会比数据文件大50%)

(文件增长个率。是相对文件初始大小增长的)

 

if Exists用来查询结果集有没有，如果有返回真 (if)语句只作用与下一行。

 

创建登陆帐户

/*---添加windows登陆帐户---*/

EXEC sp_grantlogin 'windows域名\域帐户'

/*---添加sql登陆帐户---*/

EXEC sp_grantlogin '帐户名','密码'

创建数据库用户

/*----在stuDB数据库中添加两个帐户----*/

EXEC sp_grantDBaccess '登陆帐户名','数据库用户'

 

--------------------------------------3 T-SQL编程

声明局部变量

DECLARE @name varchar(8)

DECLARE @age int

局部变量赋值

set @name=value

select @name=value

全局变量

@@ERROR	最后一个T-sql错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用语言的名称
@@MAX_CONNECTIONS	可以创建的同时连接的最大数目
@@ROWCOUNT	受上一个sql语句影响的行数
@@SERVERNAME	本地服务器的名称
@@SERVICENAME	该计算机的sql服务的名称
@@TIMETICKS	当前计算机没刻度的微妙数
@@TRANSCOUNT	当请连接打开的事务数
@@VERSION	sql server的版本信息

 

WHILE循环语句

while(条件)

       begin

              [BREAK]

       end

CASE多分支语句

case

       when 条件1 Then 结果1                  (bettween 60 and 69)between介于以知数中的未知数

       when 条件2 Then 结果2

       when 条件3 Then 结果3

end

 

-----------------------------4 高级查询----------------------------

 

-----------------------------5 事务，索引，视图--------------------

什么是事务：

事务是一种机制，一个操作序列，他包含了一组数据库操作命令

1.原子性：事务是完整的操作，事务中的各个元素是不可再分的。

2.一致性：当事务完成时，数据必须处于一致状态。

3.隔离性：事务是独立的，它不应该以任何方式依赖或影响其他事务。

4.持久性：对系统的影响是永久性的，即使修改是系统出现故障，也一直保存。

 

开始事务：BEGIN TRANSACTION

提交事务：COMMIT TRANSACTION

回滚事务：ROLLBACK TANSACTION

 

什么是索引：

索引好比字典中的目录页给出的限定的查询范围。

索引可提高数据库的查询性能(过G以上的数据量才有感觉)

1.该列用于频繁搜索

2.该列用与对数据库的排序

3.数据量大

4.数据没有规则

主键索引：系统创建的

聚集索引：按一定的序列排列有规律的索引。非聚集索引：反之亦然。（自定义的）

填充因子：0-100值，表示每页4k中索引所占的半分比。(一般不写好)

                            唯一              聚集            非聚集                                              索引名

CREATE       [UNIQUE]     [CLUSTERED|NONCLISTRED]       INDEX        index_name

表名             列名

ON  table_name(writtenExam)

              填充因子

       with fillfactor=30

索引缺点：

1.占有磁盘空间。

视图

是一种虚拟表(一组封装的sql语句)

视图通常用来进行一下3种操作(一般我们针对5张表以上我们采用视图)

筛选表中的行。

防止未经许可的用户访问敏感数据。

将多个物理表抽象为一个逻辑数据表

优点：

       针对用户：对结果更容易理解，获得数据更容易。

       针对开发人员：限制数据检索更容易，维护应用程序更方便。

 

创建视图有两种方式

1.使用Microsoft SQL Server Management Studio创建视图

2.使用T-SQL语句创建

 

user sutDB

go

/*检查是否存在*/

if exists(select * from sysobjects where name='view_stuInfo_stumarks')

       drop view vies_stuinfo_stuMarks

go

/*创建视图*/

create view view_stuInfo_stuMarks

as

select 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,=labExam,=(writtenExam+labExam)/2

from stuIfo left join stuMarks on StuInfo.stuNo=stuMarks.stuNo

go

/*--查看视图--*/

select * form view_sutInfo_stuMarks

 

-----------------------------6.存储过程--------------------

 

什么是存储过程

它是SQL语句和控制流语句的预编译集合，

存储过程包含逻辑控制语句和数据操作语句，包括接受参数，输出参数，返回单个或多个结果集及返回值。

存储过程在服务器上预编译，所以它比sql语句执行要快。

存储过程有以下优点：

1.       允许模块化设计。

2.       允许更快的执行。

3.       减少网络流量。

4.       可作为安全机制使用。

 

存储过程分为

系统存储过程：

用户自定义的存储过程：

 

 

 

 

 

 

 

 

常用的系统存储过程

Sp_databases	列出服务器上的所有数据库
Sp_helpdb	报告有关指令所有数据库信息
Sp_renamedb	更改数据库名称
Sp_tables	当前数据库可查询对象的列表
Sp_columns	查看某个表列的信息
Sp_help	查看摸个表的所有信息
Sp_helpconstraint	查看摸个表的约束
Sp_helpindex	查看某个表的索引
Sp_stored_procedures	列出当前环境下的所有存储过程
Sp_password	添加或修改登录账户的密码
Sp_helptext	查看指定对象的实际文本

 

Xp_cmdshell:  sqlserver2005中完成DOS命令下的一些操作。