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．数据类型

分类	备注和说明	数据类型	说明
二进制数据类型	用来存储非字符和文本的数据	binary	固定长度的二进制数据
		varbinary	可变长度的二进制数据
		image	可用来存储图像
文本数据类型	字符数据包括任意字母、符号、或数字字符的组合	char	固定长度的非Unicode字符数据，最大长度为8000个字符
		varchar	可变长度的Unicode (可存放新词)
		nvarchar	可变长度的Unicode数据(全球统一标识符)
		ncahr	固定长度的Unicode
		text	存储长文本信息
		ntext	存储可变长度的长文本
日期和时间	日期和时间在单引号内分别输入	DateTime	日期和时间
数字数据	数字(正数、负数、分数)	Int	占用4个字节的整数
		smallint	占用2个字节的整数
		tinyint	占用1个字节的整数
货币数据类型	十进制货币值，且精确到小数点4位数字	Money smallMoney	固定四位小数
Bit 数据类型	1和0或空值	Bit	布尔类型（表示是/否）
浮点型	近似数值类型	real	供浮点数使用
		float
	存储精度比较高的（如货币金额）	Decimal(18,2) numeric	固定精度和范围的数值型数据

第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))

编号	区分方面	set	select
1	同时对多个变量赋值	不支持	支持
2	表达式返回多个值时	出错	将返回的最后一个值赋给变量
3	表达式未返回值时	变量被赋空值	变量保持原值

eg:

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

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

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

【警告：】select @addr=(select Address from Student where 1

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.通配符

通配符	解释	示例
‘_’	一个字符	A like ‘C_’
%	任意长度的字符串	B like ‘CO_%’
[]	括号里所有指定范围内的一个字符	C like ‘9wo[1-2]’
[^]	不再括号中指定范围内的任意一个字符	D like like’9wo[^1-2]’

通配符经常与 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] 表名