Sqlserver数据库高级查询和设计

第1章 数据库 的 设计 Netstopmssqlserver ( 开启 SQL) Netstartmssqlserver( 关闭 SQL) 1.1 设计 数据库 的步骤： 需求分析阶段 1)收集信息 2)标识实体 3)标识每个实体需要存储的详细信息 4)标识实体之间的关系 概要 设计 阶段 5)绘制 E-R 图 6)将 E-R 图转

第1章 数据库的设计

Net stop mssqlserver (开启SQL)

Net start mssqlserver (关闭SQL)

1.1设计数据库的步骤：

需求分析阶段

1) 收集信息

2) 标识实体

3) 标识每个实体需要存储的详细信息

4) 标识实体之间的关系

概要设计阶段

5) 绘制E-R图

6) 将E-R图转换为数据库模型图

7) 遵循三大范式

详细设计阶段

三个范式的含义

1) 确保每列的原子性

2) 确保表中的每列都和主键相关（只描述一件事）

3) 确保每列都和主键列直接相关，而不是间接相关（两列不能描述同一件事）

三个范式的优点

4) 有助于规范化数据库的设计

5) 有助于减少数据沉（冗）余

1.2基数都有哪几种基数

1. 一对一： 1: 1 eg: 夫妻

2. 一对多: 1: N eg: 一个老师可以有多个学生

3. 多对一: N: 1 eg: 多个学生可以有一个老师

4. 多对多： M : N eg: 群殴

1.3画E-R图 要用哪几个图形

l 矩形——实体集

l 椭圆——属性

l 菱形——联系集

l 直线——连接属性和实体集，也用来联系实体集和联系集

1.4实体规范化和性能的关系

规范化——从关系型数据库表中除去沉（冗）余数据的过程

用于获得高效的关系型数据库表的逻辑结构的最好和最容易的方法

设计数据库时要遵守三大范式，满足的范式级别越高，系统性能就越低，因此允许适当的数据沉余列

一. SQL Server 数据库的基础

1 .数据库

含义：由表，关系，以及操作对象组成。

作用：存储数据、检索数据、生成新的数据

要求：统一、安全、性能等

按用途可分为：系统数据库 用户数据库

行：实际对应一个实体 （一个实体一条记录）（实体）

列：（字段）（属性）

表：实体的集合，用来存储具体的数据的。

2数据库系统和数据库管理系统

数据库管理系统：是一种系统软件，由一个相互关联的数据集合和一组访问数据的程序构成。

这个数据集合称为数据库

作用：维护数据库

数据库系统：是一个实际可运行的软件系统，可以对系统提供数据进行存储、维护、应用。

通常有 ：软件、数据库、数据库管理员组成。

3.Miscrosoft SQL Server 提供了4个系统数据库

（1）：Master 数据库： 所有的登录账号和系统配置设置

所有其他的数据库及数据库文件的位置

Sql server 的初始化信息

（2）：Tempdb 数据库：保存所有的临时表和临时存储过程，以及临时生成的工作表 （启动时都重新创建）

(3): Model 数据库：创建的所有数据库的模板。

（4）： Msdb 数据库：代理调度警报、作业、以及记录操作时使用。

4.创建数据库

一个数据库至少包含一个数据库文件和一个事物日志文件。

mdf是 primary data file 的缩写

ndf是Secondary data files的缩写

（1）：数据库文件：主数据库文件的扩展名为 .mdf ,用来存储数据库的启动信息数据。 一个数据库只能那个由一个主数据库，其他数据库文件被称为次数据库文件。

（2）：事物日志文件：扩展名为：.ldf, 事物日志文件名后需要加一个“_Log”。

一个数据可以有一个或多个事物日志文件。

（3）：次文件：扩展名为：ndf ; 次文件可有可无，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个 Windows 文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。

第2章 数据库的实现

2.1建库删库

【解决方案：】

use master --设置当前数据库为master 便于访问sysdatabases

--exists(查询语句)检测某个查询是否存在

if exists(select * from sysdatabases where name='MySchool')

Drop dataBase MySchool --删除后再创建

2.2创建文件夹

//****//在D盘创建名为project的文件夹

exec sp_configure 'show advanced options',1 //显示高级配置信息

reconfigure

exec sp_configure 'xp_cmdshell',1 //需要执行sp_configure启用xp_cmdshell

reconfigure

exec xp_cmdshell 'mkdir D:\project',NO_output //DOS命令 创建文件夹

扩展存储过程（Extened Stored Procedures）允许使用其他编程语言创建外部存储过程，为用户提供SQL Sever 实例到外部程序的接口，便于维护。以“xp_”为前缀，以DLL形式单独存在

2.3建表删表

use MySchool //指明在哪个数据库中建表

//判断若存在先删除

if Exists(select * from sysobjects where name ='Student')

drop table Student

//创建表

create table Student

(

//identity (标识增量，标识种子)

// 标识列不能为varchar类型，只能是（bigint(8),int(4), smallint(2),tinyint(1(0-255之间)),decimal）

StudentNo int not null identity (1,1), //非空，是标识列

LoginPwd varchar(20) not null,

StudentName varchar(20) not null,

Sex bit not null,

GradeId int not null,

Phone nvarchar(50) null,

Address nvarchar(255) null,

Borndate datetime not null,

Email nvarchar(50) null,

IndentityCard varchar(18) not null

)

2.3创建和删除约束

--主键

alter table Student

add constraint PK_StudentNo primary key (StudentNo)

--唯一(可以为NULL值，但不能重复)

alter table Student

add constraint UQ_IdentityCard unique (IdentityCard)

--默认

alter table Student

add constraint DF_Address default('安徽') for Address

--检查

alter table Student

add constraint CK_BornDate Check(BornDate>=1990-01-01)

--外键(主表Student和从表Result通过关联列StudentNo建立关系)

alter table Result

add constraint FK_Student_Result foreign key (StudentNo) references(引用Student(StudentNo)

--删除约束

alter table Student

drop constraint DF_Address

二．数据库表的管理

1 数据完整性：

有四种类型的约束

（1）：实体完整性约束 ：要求表中的每一行数据反映不同的实体，不能存在相同的数据行

通过： 索引、唯一约束(Unique)、主键约束(primary key)或标识列(identity)属性。

（2）：域完整性约束： 给定 列的输入有效值

通过：限制数据类型、检查约束(check)、输入格式、外键约束（Foreign Key References）、

默认值(default)、空约束( null) 、非空约束(not null)

（3）：引用完整性约束: 输入或删除数据行时，此约束用来保持表之间已定义的关系。

通过：主键和外键之间的引用关系实现。

(4): 自定义完整约束

2：主键和外键

主键：一个表只能有一个主键

原则：唯一、最少性、非空性、稳定性

外键：一个表可以有多个外键

3．标识列：

identity (标识种子，标识增量)

标识列常被定义为主键，在插入数据时，不许为标识列指定值。

4．主表和子表的关系

（1）：子表的相关项目的数据，在主表中必须存在

（2）：主表中相关项的数据更改了，则子表对性的数据项也应当随之更改。

（3）：在删除子表之前，不能够删除主表。

5．数据类型

第3章 SQL编程

3.1 使用变量

//***//根据学号查找’ 李斯文’的信息，及与’ 李斯文’相邻的学生信息

use MySchool //使用MySchool数据库

//声明变量

declare @name varchar(8)

declare @no int

// 局部变量的赋值有两种方法：使用 set 和 select

set @name='李斯文'

select * from Student where StudentName=@name

select @no =StudentNo from Student where StudentName=@name

//两种方式实现查询和李斯文相邻学号的信息

select * from Student where (StudentNo=@no -1) or (StudentNo=@no +1)

select * from Student where (StudentNo in (@no+1,@no -1))

eg:

1.【set 不能同时为两个变量赋值】

2.【A ==set 只能赋一个值 B==select查询有多个值时，赋值查询的最后一个结果】

3．【查询无结果时 set == @addr被赋NULL值 select ==@addr 保持原值 】

【警告：】select @addr=(select Address from Student where 1<0)此写法将显示空值

3.2全局变量

【@@ERROR >0 表示上一条执行语句有误】

【注意】 常用的全局变量有：@@ERROR ——错误号

@@IDENTITY ——标识列

@@RowCount ——受影响的行数

3.3 输出语句

print 局部变量或字符串 //在消息框中显示

select 局部变量 as 自定义列名 //在结果框中显示

eg:

print '服务器的名称：'+@@ServerName

select @@ServerName as '服务器的名称'

3.4 数据类型转换

Cast （表达式 as 数据类型） Cast（date as varchar(10)）

Convert(varchar(10),@date,111)

Convernt （数据类型（长度） ， 表达式 ， 日期型（1-131）、浮点型指定转换的格式）

print '成绩：'+convert(varchar(20) ,@result)

print '成绩：'+cast(@result as varchar(20)))

获取年龄

@age=floor(DATEDIFF(DY,Borndate,GETDATE())/365)

Floor 小于某个数的最大整数 eg: Floor(27.7) 27(周岁)

Ceiling 大于某个数的最小整数 eg: Ceiling (27.7) 28(虚岁)

3.5 逻辑控制语句

顺序 begin ~ end 相当于 大括号{}

分支 if ~ else 和 case ~ end

循环 while

set nocount on --不显示“n行受影响”，写在执行SQL语句的上面

3.6 批处理

GO——把SQL语句批量处理（以一条命令的方式来处理一组命令的过程称为批处理。）

它可以提高语句执行的效率。

【★☆】“不显示n行受影响”——set nocount on

三．数据管理

1． T-SQL的组成

DML(数据操作语言) ： 用来插入、删除、修改、查询数据库中的数据( insert delete update select)

DCL ( 控制 )： 用来控制数据库组件的存取许可、存取权限等。（Grant Revoke）

DDL ( 定义 ): 用来建立数据库、数据库对象和定义其列 。

大部分Greate开头的命令 ：（Greate Table Great view drop Tabel）

2.比较运算符：

> < = >= <= <>(不等于) ! (非)

3.通配符

通配符经常与 like 关键字使用。

4. 逻辑表达式

T-sql 支持的逻辑运算符有not、And、or。

eg: 采购订单表中—付款方式：信用卡

—约束要求：牡丹卡、金穗卡、龙卡、阳光卡

Not(付款方式=’信用卡’) or ( 信用卡 in ( ‘牡丹卡’ , ’ 金穗卡’ , ’ 龙卡’ , ’ 阳光卡’) )

在一个语句中使用了多个逻辑运算符时，首先求not 值，然后求and值，最后且or值

注：int 关键字用来限制范围。

列名 Between 低值 And 高值

Where 条件种类 And Or Not(与，或，非)

In(值列表) eg：Select * from stuInfo Where stuAge IN (21,25)

Like(模糊查询)

5.SQL语句

增

1. 一次插入一行

Insert [into] <表名> [列名列表] values <值列表>

列名列表顺序可自己指定，但值列表的顺序应该和列名列表相同

2. 一次插入多行

l Insert [into] <新表名>[此表必须预先创建]

select <列名> from <查询的表>

l Select <列名> into <新表名>[必须预先没有] from <查询的表>

l Insert <查询的表>[列名1, 列名2,列名3] ——得到的是虚拟表

Select ' ', ' ', ' ' union

Select ' ', ' ', ' ' union

Select ' ', ' ', ' '

删

Delete from <表名> [where <删除条件>]

只要删除就是删除整条记录，不会只删除单个字段，所以在delete 后不出现字段名。

注：delete...from...只要删除就是删除整条记录，不会删除单个字段，所有在delete后不能出现字段名，例如：

“delete SAddress from Students” 此语句将会报错

Truncate table <表名>

注意：1执行删除不能删除主外建关系的表数据

2 删除表中所有数据

3执行效率高（快）

改

Update <表名> set <列名=更新值> [where <更新条件>]

查

Select <列名>

From <表名>

Where [查询条件表达式]

Group by [分组]

Having [过滤] ——不单独使用，只与group by 一起使用(相当于Where)

[order by <排序的列名> [ASC 或DESC（降序）]

1. 选择指定数量的记录,通常配合order By使用

Eg; Select Top 2 * From stuInfo Order By stuAge Desc

第4章 高级查询

4.1子查询

含义：嵌入的查询称为子查询或嵌套查询

实现方式：可通过关系运算符、in 、exists 实现的查询

注意：子查询语句必须放置在一对圆括号内。

与关系运算符使用时，必须保证子查询返回的值不能多于一个

举例：

--查询最近一次C# OOP考试成绩最高分和最低分

select 科目='C# OOP',MAX(StudentResult) as 最高分 , Min(StudentResult)as 最低分 from dbo.Result

where SubjectNo=(select SubjectNo from dbo.Subject where SubjectName='C# OOP')

and ExamDate=(select Max(ExamDate)from dbo.Result where SubjectNo=(select SubjectNo from dbo.Subject where SubjectName='C# OOP'))

go

4.2 表联接

内联接——返回两表的交集

select 列名，列名，列名，列名

from 表1 inner join 表2 on (表1. 列名=表2. 列名)

inner join 表3 on 主外键关系

第一个 on 主外键关系是表1 和表2的

第二个 on 主外键关系是表2和表3的或表1 和表3

(在内连接中，参与连接的表的地位是平等的，没有主从之分)

外联接—— A C B

1. 左外联接（left join 或 left outer join） A+C

2. 右外连接（Right join 或Right outer join） B+C

3. 完整外联接 （Full join 或Full outer join） A+B+C

select 列名，列名，列名，列名

from表1 left join 表2 on (表1. 列名=表2. 列名)

left join 表3 on 主外键关系

第一个 on 主外键关系是表1 和表2的

第二个 on 主外键关系是表2和表3的

(在外部的连接中参与连接的表有主从之分，left join 左边的表是主表，右边是从表 )

交叉联接——返回两表之积

4.3 in 和 not in

和where做查询使用

--查询S1学期开设得课程（in）（not in 查询的是非S1开始的课程）

select SubjectName from dbo.Subject

where GradeID in (select GradeID from dbo.Grade where GradeName='S1')

go

4.4 Exists 和not Exists

和if做判断使用

--如果有S1的学生，就将他的在读年级更新为S2

if exists( SELECT * FROM Student INNER JOIN Grade ON

Student.GradeId=Grade.GradeId WHERE GradeName='S1')

begin

update dbo.Student set GradeId=(select GradeID from dbo.Grade where GradeName='S2')

from dbo.Student inner join dbo.Grade on Student.GradeId=Grade.GradeID

where GradeName='S2'

end

go

4.5 表联接和子查询的区别

表联接都可以用子查询替换，反之不可以。

子查询——适合于作为查询的筛选条件

表联接——更适合于查看多表的数据

4.6 子查询的注意事项

1. 父查询中的子查询可包括

select 、 from 、 where 、 group by 、 having

但排序语句（order by）不能写在子查询中

1. 子查询的select子句中不能出现text、ntext、image数据类型的列

2. 子查询中包含而父查询中没有的列不能包含在输出列中

select * from (select * from Result) Info

第5章 ??☆☆ SQL函数

5.1 字符串函数

5.2 日期函数

5.3 数学函数

5.4 聚合函数

1. sum 放回数值的总和 （只能用于数字类型的列 ，不能汇总字符、日期等其他数据类型）

2. avg 返回数值的平均值 （只能用于数字类型的列）

3. max（返回最大值） 和min（返回最小值）

（可以用于数字型、字符型、以及时间/日期类型）

4．count （返回提供的表达式非空值的计数 ）（可用数字和字符串类型）

第6章 事务、视图和索引

6.1 事务

含义：

是单个逻辑工作单元执行的一系列操作。

如果某一事务成功，则该事物中进行的所有数据更改均会提交，成为数据库的永久组成部分。

如果事务遇到错误且必须取消或回滚，则所有数据更改均被清楚

特性：

一个逻辑单元必须有四个属性（ACID）。

原子性（Atomicity） 一致性（Consistency） 隔离性（Isolation） 持久性（Durability）

语法：

begin transaction --开始事务

commit transaction --提交事务

rollback transaction --回滚事务

分类：

显式事务——用begin transaction明确指定事务的开始

隐式事务——通过设置set implicit_transactions on 将隐式事务模式设置为打开。

无需描述每个事务的开始，只需要提交或回滚每个事务

自动提交事务——SQL Server 的默认模式，它将每条T-SQL语句视为一个事务，自动提交或回滚

编写事务的原则：

l 事务尽可能简短

l 事务中访问的数据量尽量最少

l 查询数据时尽量不要使用事务

l 在事务处理过程中尽量不要出现等待用户输入的操作

6.2 视图

含义：

保存在数据库中的select查询。是一种虚拟表，由一个或多个表中的列和行组合而成。

使用原因：

1. 数据的安全性。用户不必查看整个数据库结构，有所侧重

2. 符合用户日常业务逻辑，使他们对数据更容易理解

如何操作视图：

l 筛选表中的行

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

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

特点：

视图并不是数据库中存储的数据值的集合，它的行和列来自查询中引用的表。在执行时，直接显示表中的数据。

使用方法：

一：使用Microsoft SQL Server Management Studio

1. 选中数据库下的“视图”，右击选择“新建视图”

2. 在打开的对话框中选择需要连接的表后，单击“添加”按钮

3. 选择希望查看的列，可以取别名，然后单击 即可

二：使用T-SQL语句

use MySchool

go

--检测视图是否存在，视图记录存放在系统表sysobjects中

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

drop view vw_Stu_Result_Subject

go

--创建视图

create view vw_Stu_ReSub

as

SELECT Student.StudentName AS 学生姓名, Student.Phone AS 联系电话,

Result.StudentResult AS 成绩, Result.ExamDate AS 考试日期,

Subject.SubjectName AS 课程名称

FROM Student

INNER JOIN Result ON Student.StudentNo = Result.StudentNo

INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo

go

--查看视图结果

select * from vw_Stu_Result_Subject

注意事项：

l 每个视图中可使用多张表

l 一个视图可以嵌套另一个视图

l 视图定义中的select语句不能包括以下内容

Ø Order by子句，除非查询语句的选择列表中也有一个TOP子句

Ø Into 关键字

Ø 引用临时表或表变量

6.3 索引

作用：

提升数据库的检索速度，改善数据库性能

分类：

Ø 唯一索引——不允许两行具有相同的索引值（不建议使用）

Ø 主键索引——是唯一索引的特殊类型，它要求主键中的每个值都是唯一的

Ø 聚集索引——按字母（字母是逻辑顺序，页码是物理顺序）一个表只能由一个聚集索引

Ø 非聚集索引——按笔画排序

Ø 复合索引——多列组合而成的索引

Ø 全文索引——基于标记的功能性索引

使用方法：

一：使用Microsoft SQL Server Management Studio

在设计表中右击“索引/键”，点击“添加”——索引列的排序、是唯一的……

二：使用T-SQL语句

use MySchool

go

--检测是否存在该索引(索引存放在系统表sysindexes中)

if exists(select name from sysindexes where name ='IX_Student_StudentName')

drop index Student.IX_Student_StudentName --删除索引

--创建非聚集索引，填充因子30%

--unique(唯一索引) clustered (聚集索引) nonclustered (非聚集索引)

create nonclustered index IX_Student_StudentName

on Student(StudentName)

with fillfactor=30

go

/*--指定按索引：IX_Student_StudentName查询--*/

select * from Student

with(index=IX_Student_StudentName)

where StudentName like '李%'

建立索引的标准：

Ø 频繁搜索的列

Ø 经常用作查询选择的列

Ø 经常排序分组的列

Ø 经常用作连接的列（主键/外键）

第7章 存储过程

7.1 存储过程的概念

含义：

是SQL语句和控制语句的预编译集合。包括 声明变量、逻辑控制语句、编程

优点：

Ø 模块化程序设计——创建一次，调用多次

Ø 执行速度快，效率高——已创建的存储过程，可直接执行，无需编译优化

Ø 减少网络流量——使用一行执行语句即可

Ø 具有良好的安全性——不同权限用户使用不同存储过程，通过提交的存储过程名称执行

一个完整的存储过程包括以下三个部分：

Ø 输入参数和输出参数

Ø 在存储过程中执行的T-SQL语句

Ø 存储过程的返回值

分类：

一：系统存储过程

二：用户自定义的存储过程

7.2 系统存储过程

exec sp_databases --列出当前系统中的数据库

exec sp_renamedb 'MyBank','Bank' --改变数据库名称

use MySchool

go

exec sp_tables --当前数据库中可查询对象的列表

exec sp_columns Student --查询Student表中列的信息

exec sp_help Student --查询Student表的所有信息

exec sp_helpconstraint Student --查看Student表的约束

exec sp_helptext 'vw_Stu_Result_Subject' --查看视图的语句文本

exec sp_stored_procedures --返回当前数据库中的存储过程列表

exec sp_helpindex Student --查询Student表的索引

exec sp_stored_procedures --显示存储过程的列表

7.3 用户自定义的存储过程

set @errorSum=@errorSum+@@ERROR --显示错误数

set @subjectNo=@@IDENTITY --获得课程编号（获取主键标识列的编号）

l 创建不带参数的存储过程

use MySchool

go

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

drop procedure usp_StudentResult --删除存储过程-

go

/*--创建不带参数的存储过程--*/

create procedure usp_StudentResult

as

SELECT Student.StudentName AS 学生姓名, Student.Phone AS 联系电话,

Result.StudentResult AS 成绩, Result.ExamDate AS 考试日期,

Subject.SubjectName AS 课程名称

FROM Student

INNER JOIN Result ON Student.StudentNo = Result.StudentNo

INNER JOIN Subject ON Result.SubjectNo = Subject.SubjectNo

Go --go不能省，要省略需写 begin end

exec usp_StudentResult --调用执行存储过程

l 创建带输入参数的存储过程

create procedure usp_unpass

@score int --平均成绩 @score int=50

@subName varchar(50),--考试课程

As

--输入参数

exec usp_unpass 50, 'SQL Base'

exec usp_unpass @score=50, @subName='SQL Base'

--输入参数采用默认值

exec usp_unpass default, 'SQL Base'

exec usp_unpass @subName='SQL Base'

l 创建带输出参数的存储过程

create procedure usp_query_num

@UnPassNum int output, --未通过人数****输出参数

@TotalNum int output, --参加考试人数****输出参数

@subName varchar(50),--考试课程****输入参数

@score int=60 --平均成绩****输入参数

As

--声明变量用于接收输出的值

declare @UnPassNum int

declare @TotalNum int

exec usp_query_num @UnPassNum output ,@TotalNum output,'Java Logic',50

例如： 1. 输入参数 2.Exec执行

7.4 处理错误信息

作用：向用户报告错误

语法：

--创建存储过程

create procedure usp_存储过程名

as

if(@GradeName is null or @SubjectName is null)

begin

raiserror('学期名称和课程名称是否为空',16,1)

return --立即返回，退出存储过程

end

--SQL语句

7.5 注意

存在于sysobjects中的有—— 表 、 视图 、存储过程

string account=string.Format("{0:000000}",int.Parse(userItem[1]));

///C3 货币格式 eg：2000 ————￥2，000.000

///D3 十进制格式 eg：2000 ————2000

///F3 小数点后的位数固定 eg： 2000 ————2000，000

///N 用逗号隔开的数字 eg： 250000 ————250，000.00

///P3 百分比计算法 eg：0.3256 ————32.56

///X000 十六进制格式 eg：12 ———— C

string Money = string.Format("{0:C2}",int.Parse(userItem[2])); //货币格式 小数点后两位小数

其他：

CREATE PROCEDURE P_GetPagedOrders2005
@startIndex INT,
@pageSize INT
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,
O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @startIndex+@pageSize-1
end