高级SQL运用

死siua11

一:什么是数据库设计?
数据库设计就是将数据库中的数据实体以及这些数据实体之间的关系,进行规范和结构化的过程.
二:为什么要实施数据库设计?
1:良好的数据库设计可以有效的解决数据冗余的问题
2:效率高
3:便于进一步扩展
4:使得应用程序开发变得容易
三:设计数据库的步骤
第一步    需求分析:  分析客户的业务和数据处理需求(收集信息,标识实体,标识每个实体需要存储的详细信息,标识实体间的关系)
第二步    概要设计:  绘制E-R图,用于与客户或团队成员的交流
第三步    详细设计:  将E-R图转换成多张表,进行逻辑设计,应用数据库设计的三大范式进行审核,选择具体的数据库然后建库建表建约束,创建完成开始编写代码,开发前端应用程序.
四:绘制E-R图
　　(1) 什么是实体?
　　　　　　实体是指现实世界中具有区分其他事物的特征或属性并与其他实体有联系的实体(实体一般是名词)
　　　　　　严格来说,实体是指表中一行特定的数据,也常常把表称为一个实体
　　(2)什么是属性?
　　　　　　属性可以理解为实体的特征,属性对应表中的列
　　(3)什么是联系?
　　　　　　联系是两个或多个实体之间的关联关系(一般是动词)
　　(4)什么是映射基数?
　　　　　　映射基数是表示通过联系与该实体关联的其他实体的个数,具体有 一对一 ,  一对多,  多对一,  多对多.
　　(5)什么是关系实体图?
　　　　　　E-R图以图形的方式将数据库的整个逻辑结构表示出来.     矩形表示实体集;椭圆表示属性;菱形表示联系集;
  　　　　　直线用来连接属性和实体集,也用来联系实体集和联系集.

例如,酒店管理系统中E-R图:


五:绘制数据库模型图
例如:酒店管理系统数据库模型图



六:数据规范化
规范设计
第一范式:
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小单元(也称为最小的原子单元),则满足第一范式
第二范式:
如果一个关系满足第一范式,并且除主键以外的其他列,都依赖于该主建,则满足第二范式,第二范式要求一张表只描述一件事情
第三范式:
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式

注:第二范式是其他列都依赖于主键列但是没有说明是直接依赖还是间接依赖,也就是直接依赖和间接依赖均可,但第三范式明确指出只能是直接依赖,如果出现间接依赖的情况,要单独创建一张表出来

七:规范化和性能的关系
为了满足某种商业目标,数据库性能比规范数据库更重要
具体策略和方法:
(1):通过在给定的表中添加额外字段,以大量减少需要从中搜索信息所需要的时间
(2):通过在给定的表中插入计算列(比如成绩总分),以方便查询
在数据库规范时,要综合考虑数据库性能.

一 :创建数据库
创建一个数据文件和一个日志文件(MySchool)
create database MySchool
on primary      --默认属于primary主文件组,可省略
(
--数据文件的具体描述
name = 'MySchool_data'    --主数据库文件的逻辑名称
filename = 'D:\project\MySchool_data.mdf', --主数据库文件的物理名称
size = 5MB,     --主数据库文件的初始大小
maxsize = 100MB,     --主数据库文件增长的最大值
filegrowth = 15%     --主数据文件的增长率
)
log on
(
--日志文件的具体描述,各参数含义同上
name = 'MySchool_log',    --主数据库文件的逻辑名称
filename = 'D:\project\MySchool_data.ldf', --主数据库文件的物理名称
size=2MB,     --主数据库文件的初始大小
filegrowth = 1MB    --主数据文件的增长速度
)
go

创建多个数据文件和多个日志文件(employees)
create database employees
on primary
(
--主数据库文件的具体描述
name='employee1',
filename='D:\project\employee1.mdf',
size=10,
filegrowth=10%
),
(
--次要数据库文件的具体描述
name='employee2',
filename='D:\project\employee2.mdf',
size=20,
maxsize=100,
filegrowth=1
)           
log on
(
--日志文件1的具体描述
name='employeelog1',
filename='D:\project\employee1_log.ldf',
size=10,
maxsize=50,
filegrowth=1
),
(
--日志文件2的描述
name='employeelog2',
filename='D:\project\empolyee2_log.ldf',
size=10,
maxsize=50,
filegrowth=1
)
go


二:删除数据库
usr master
if exists(select * from sysdatabases where name='....')
drop database ......


三:创建和删除表
use MySchool    --在Myschool中创建表
go
create table Student
(
StudentNo int not null.   --学号,int 类型,不允许为空
LoginPwd nvarchar(50) not null,  --密码 nvarchar类型,不允许为空
StudentName nvarchar(50) not null, --名字,nvarchar类型,步允许为空
Sex bit not null,   --性别,取值0或1
Email nvarchar(20)   --邮箱,可为空
)
go
删除表
use MySchool
go
if exists(select * from sysobjects where naem='Student')
drop table Student
  
                                                                                                                                                                                                                                                                                                                                                                                                   
四:创建和删除约束
主键约束(Primary Key Constraint)
非空约束(Not Null)
唯一约束(Unique Constaraint)
检查约束(Check Constaraint)
默认约束(Default Constaraint)
外建约束(Foreign Key Constarint):用于在两表之间建立关系,需要指定引用主表的哪一列
alter table 表名
add constraint 约束名  约束类型  具体的约束说明
例:
--添加主键约束
alter table Student
add constraint PK_StudentNo Primary Key(StudentNo)
--添加唯一约束
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 checke(BornDate>='1980-01-01')
--添加外键约束(Result是从表,Student是主表)
alter table Result
add constraint FK_StudentNo
foreign key(StudentNo) references Student(StudentNo)
go

删除约束
alter table 表名
drop constraint 约束名
例:删除学生表中的默认约束
alter tablte Student
drop constraint DF_Address

怎样向已存在数据的表中添加约束
alter table Employee with nocheck ( whit nocheck不向已存在的数据约束)
add constraint
向已存在的数据表中插入一列
alter table 表名
add 列名 数据类型  null


在数据库中用SQL语句创建文件夹(例:在E盘创建一个project文件夹)
exec sp_configure 'show advanced option',1
go
reconfigure
go
exec sp_configure 'xp_cmdshell',1
go
reconfigure
go
exec xp_cmdshell 'mkdir D:\project'
使用变量 数据类型转换 逻辑控制语句(begin ...end; case...end; if...else; while)
一:变量
变量分为局部变量和全局变量  (全局变量是系统自定的,是不可手动给值的,若想自己定义全局变量可考虑创建全局临时表!)
局部变量的定义:  declare @变量名  数据类型　　　　(局部变量只能用于同一批处理当中!!!!!!!)
全局变量: @@
@@error    　　　　最后一个T-SQL语句错误的错误号
@@identity 　　　　最后一次插入的标识值
@@rowcount　　　受上一个SQL语句影响的行数
@@servicename  　该计算机上SQl服务器的名称
@@version　　　　SQL Server的版本信息
@@transcount 　　当前连接打开的事务数
@@timeticks　　　　当前计算机上每刻度的微秒数
@@max_connections 可以创建的,同时连接的最大数目
@@language 　　　　当前使用的语言的名称
二:给变量赋值  (set  和seklect)
set和select的区别
1:set不支持多个变量赋值,select支持
2:表达式返回多个值时,set出错,select返回最后一条数据
3:表达式未返回任何值时,set赋值为null(没有) select 保持不变

三:输出语句
print @变量名 或
select @变量名
一般从数据库中查找数据后赋值使用select赋值方法
四:数据类型转换
隐式转换: 类型相同或相兼容,自动转换
显式转换: 类型不同,可用convert 函数 或 cast 函数
相同点: 都用于某种数据类型的表达式转换为另一种数据类型
不同点: 在转换日期的时候,convert可以转化为指定的格式
cast (变量名 as 数据类型)
convert(数据类型, 变量名)
五:逻辑控制语句
流程控制语句  begin....end  
通常使用在if判断和while循环中,相当于C#中的 {}
分支结构   if..else  或
　　　　　case ...end(多重选择)
eg:
　　select 成绩=case
　　　　　　　　when 条件一  then 结果一
　　　　　　　　when 条件二  then 结果二
　　　　　　　　else 其他结果(可省略)