MSSQL系列之二.二数据库与表管理

use MyDB

go

--DDL:CREATE,Alter,Drop:针对所有的数据库对象，基本都是都有效.

Create database Soft--建立数据库，路径在SQL Server安装路径/data文件夹下

--格式：

/*create database 库名

[on[Primary]

[<文件定义>[ ,...n ]]

[ , <文件组定义> [ ,...n ] ]

[log on[<文件定义>]]

文件定义：

(name='逻辑文件名',

filename='物理文件名',

size=初始大小,

maxsize=最大大小，

filegrowth=文件增长方式)

*/

if exists(select * from sys.databases where name='MyNewDb')

drop database MyNewDb--删除数据库

create database MyNewDb

on primary--主数据文件有一个,默认文件组

(

name='MyNewDb',

filename='e:/MyNewDb.mdf',

size=3Mb,

maxsize=10Mb,

filegrowth=1MB

)

,filegroup FileGroupS

( name='MyNewDb2',

filename='e:/MyNewDb2.ndf',

size=3Mb,

maxsize=UNLIMITED ,--最大值不受限制，无限增长，

filegrowth=1MB)

,(

name='MyNewDb3',

filename='e:/MyNewDb3.ndf',

size=3Mb,

maxsize=UNLIMITED ,

filegrowth=1MB)

log on

(

name='MyNewDb_Log',

filename='e:/MyNewDb_Log.ldf',

size=1Mb,

maxsize=10Mb,

filegrowth=1%

)

--查看数据库的帮助信息

sp_helpdb MyNewDb

--修改数据库的名称:sp_renamedb 旧数据库名,新数据库名

sp_renamedb MyNewDb,MyDBDB

--修改数据库alter database 库名..相关定义

--向文件组FileGoups添加一个次要数据文件

alter database MyDBDB

add file

(

name='MyNewDb4',

filename='e:/MyNewDb4.ndf',

size=1Mb,

maxsize=10Mb,

filegrowth=1%

)

to FileGroup FileGroups

alter database MyDBDB

remove file MyNewDb2

--通过alter database 修改库名

alter database MyDBDB

modify name=MYNewDB

--表管理:创建、修改、删除;DDL:Create Alter Drop

use myDB--1.使用自己的数据库.

go

Create table Customer--数据库名.架构名.表名;myDB.dbo.Customer

( --字段名 数据类型[长度]

顾客编号 char(10),

顾客姓名 varchar(12),

顾客性别 char(2),--bit

顾客卡号 char(10),

顾客地址 varchar(20),

顾客联系方式 varchar(20)

)

sp_help Customer--查询表的信息

sp_rename 顾客表,Customer

use myDB

go

--将顾客性别修改为bit类型，修改列命令：alter table 表名 alter Column 列名[类型]

alter table Customer

alter Column 顾客性别 char(2)

--修改表，向表中增加一列，比如说增加备注列

alter table Customer

add 备注 varchar(200)

alter table Customer

add 身高 decimal(3,2),

体重 tinyint

--删除列，：drop Column 列名

alter table Customer

drop column remark

--做作业4.40第一题.

--修改City列，将city列改为varchar(12)

--增加MobilePhone列为char(11)

--删除FirstName,LastName列，增加一个姓名列.

--删除表drop table 表名

drop table 转换后的表

--数据的完整性:实体完整性约束:主键约束、唯一约束、标识列,针对行，来保证每行实体的有意义.

--建立带主键的约束

Create table Customer--数据库名.架构名.表名;myDB.dbo.Customer

( --字段名 数据类型[长度]

顾客编号 char(10) Constraint PK_Customer_顾客编号 Primary Key,--建立主键约束

顾客姓名 varchar(12)not null,--非空约束

顾客身份证 char(18)Constraint UQ_Customer_顾客身份证 unique,--唯一约束

顾客性别 char(2),--bit

顾客卡号 char(10),

顾客地址 varchar(20),

顾客联系方式 varchar(20)

)

--主键约束，唯一且不为空；唯一约束，可以有一个为空(null)

select * from Customer

Create table WangBaPc

(

Id int identity(1,1),--标识列.适用于整型。

--identity(标识种子，增长量n),从标识种子，每次增加n

Pc varchar(8),

class varchar(8)

)

--域完整性约束：针对表中的列来进行约束。有如下几种：

--Not null、默认值、检查约束、外键约束、数据类型.

--建立顾客银行卡表.

Create table CustBank

(

顾客卡号 char(10)Constraint PK_CustBank_顾客卡号 Primary Key,

金额 money,

消费次数 int,

积分 int

)

select * from CustBank

--建立图书信息表

Create table BookInfo

(

BId char(20) Constraint PK_BookInfo_BkId Primary key,

BName varchar(30)not null,--非空约束

BPulisher varchar(20) not null,

BPrice money,

BType char(4),

BBuyPerson varchar(12)Constraint DF_BookInfo_BBuyPerson default '张三丰'--默认值约束

)

--专门来看检查约束

--建立销售人员表

Create table SalesPerson

(

SID int identity(1,1)Constraint PK_SalesPerson_SID Primary key,

SName varchar(12)not null,--非空约束

SGender char(2)Constraint CK_SalesPerson_SGender Check(SGender='男' or SGender='女')

)

--作业：

--建立一个游戏人员表

/*Create table Player

(

--id 为标识列的主键，

--姓名 非空

--身份证 唯一约束

--性别 只能为男、女

--身高 在0.30米与3.00米之间

--体重 在30KG到250KG之间

--属相 中国十二生肖

--城市 默认来自郑州

)

*/

Create Database HomeWork

go

if exists(select * from Sys.Objects)

drop table Player

Create table Player

(

id int identity(1,1)Constraint PK_Player_Id primary key,

姓名 varchar(8)not null,

身份证 char(18) Constraint UQ_Player_身份证 Unique,

性别 char(2) Constraint CK_Player_性别 Check(性别='男' or 性别='女'),

身高 decimal(3,2)Constraint CK_Player_身高 Check(身高>=0.3 and 身高<=3.0),

体重 tinyint Constraint CK_Player_体重 Check(体重>30 and 体重<250),

属相 char(2) Constraint CK_Player_属相 Check(属相 in ('鼠','牛','虎','兔','龙','蛇','马','羊','猴','鸡','狗','猪')),

城市 varchar(12)Constraint DF_Player_城市 Default '郑州'

)

sp_help Player

--建立超市物品表

--建立一个订购表

use myDB

go

Create table Goods--物品表

(

Id int Identity(1,1)Constraint PK_Goods_Id Primary key,

TiaoXingMa char(13)Constraint UQ_Goods_TXM Unique,--条形码

GName varchar(20)not null,--物品名称

GPrice Money,

GWeight decimal(5,2)

)

Create table OrderGoods--订购表

(

订购编号 char(10)Constraint PK_OrderGoods_订购编号 primary key,

GId int,--订购物品的编号

订购时间 smalldatetime Constraint DF_OrderGoods_订购时间 Default getdate(),

订购人 varchar(8)not null

)

select * from Goods

select * from OrderGoods

--在订购表的GID列上建立外键约束,和主表Goods的ID列建立关系(即指向ID列)

alter table OrderGoods

Add Constraint FK_Goods_OrderGoods_Id Foreign Key(GID)

References Goods(ID)

--格式：修改表添加外键;外键表：子表；主表：父表

alter table 子表

Add Constraint FK_主表名_子表名 Foreign Key(外键列)

References 主表名(主表被依赖列,一般为主表主键)

--建立主人表和仆人表

Create table 主人

(--主人：Master

MId char(8)Constraint PK_主人_MId Primary key,

MName varchar(8)not null

)

Create table 仆人

(--仆人：servant

SId char(8) Constraint 仆人_SId Primary Key,--仆人的编号

MId char(8) Constraint FK_主人_仆人_MID Foreign Key

References 主人(MID),--指定仆人的主人的编号

SName varchar(8)not null

)

--创建规则

Create Rule HeNanCity

as @City in('郑州','洛阳','安阳','南阳','濮阳','信阳','鹤壁','焦作','新乡','开封','商丘','周口','驻马店','许昌','漯河','济源')

--将Player的城市列绑定到规则HeNanCity

sp_bindrule 'HeNanCity','Player.城市'

Create Type 身高

from Decimal(3,2)

Create Table Person

(

id int identity(1,1),

Name varchar(10)not null,

P身高 身高

)

sp_help Person

insert into Person values('无用',2.2)

select * from Person

--use myDB

go

drop table go

drop table test

--建立一个表，字段ID,Name,Sex

Create table Test

(

Id int identity(1,1),--自动增长，不用插入数据

Name varchar(8) not null,

Sex char(2)

)

sp_help Test

--插入数据的格式：

insert into 表名(字段1，字段2,...) values(值1，值2,...)

insert into Test(Name,Sex) values ('张名','女')

insert into Test(Name,Sex) values('小鹏','女')

insert into Test(Name,Sex) values('涛妹','go')-- 不正确的数据

--查询

select * from Test

--向表中添加约束，检查约束，针对性别(男，女)

alter table Test

with noCheck--对表中老的数据，不检查

Add Constraint CK_Test_Sex Check(Sex in ('男','女'))

--自定义完整性:规则和自定义类型

--规则的格式：Create Rule 规则名 as @变量 表达式

Create Rule CityRule

As @HeNanCity in('郑州','洛阳','安阳','开封','商丘','南阳','周口','信阳','驻马店','焦作','新乡','许昌')

--建立规则后的使用，必须将规则绑定到表中的列上

Create table Sut

(

XueHao char(8)Constraint PK_Stu_XH Primary key,

XM varchar(12)not null,

City char(16)

)

--修改表名

sp_rename 'Sut','STU'

select * from Stu

--向表中插入数据

insert into Stu(XueHao,XM,City) values('0401','渔利为','郑州')

insert into Stu(XueHao,XM,City) values('0402','张阳','南阳')

insert into Stu(XueHao,XM,City) values('0403','萧成龙','信阳')

--将规则绑定到Stu的City列

sp_bindrule 'CityRule','Stu.City'--成功后会显示：已将规则绑定到表的列。

--再测试输入，不为河南的城市的学生

insert into Stu(XueHao,XM,City) values('0406','康非龙','信阳')

insert into Stu(XueHao,XM,City) values('0404','邓小萍','南京')

-----------------------------------------------------------------------

--自定义类型

--格式：Create Type 自定义类型名 from 数据类型[(长度)]

--创建自定义类型:相扑体重

Create type 相扑体重

from tinyint

--创建一个表,相扑运动员

Create table 相扑运动员

(

Id int identity(1,1)Constraint PK_Id Primary key,

Name varchar(20)not null,

Weight 相扑体重--相扑体重：自定义类型，范围为0-255

)

insert into 相扑运动员(Name,Weight) values('山本五十8',250)

insert into 相扑运动员(Name,Weight) values('纯口火鸟',220)

insert into 相扑运动员(Name,Weight) values('井大门',300)

--作业：将前面我们建立的属相设定为规则，绑定到属相列

--自定义类型身高，建立一个表的列类型为身高

--*******************************************************************

--第一创建分区函数,跟着建立分区的文件组和文件，并建立对应关系.

--第二创建分区方案(架构)

--第三对表实施分区

--

create partition function Fun_RateChngDate(datetime) --创建分区函数 RateChngDate

as range right for values('1996-01-01','2000-01-01','2004-01-01','2008-01-01')

------------------------------------------------------------------------------------

create partition scheme Sch_RateChangDate --创建创建分区方案 RateChangDate

as partition RateChngDate--指明分区方案基于的分区函数

to(Old,First,Second,Third,Fourth)--将分区方案建立在对应的文件组上

------------------------------------------------------------------------------------

create table EmpPayHistPart--雇员付薪记录表

(

EmployeeId int,

RateChangeDate datetime,--分区列

Rate money,

RayFreQuency tinyint,

ModifileDate datetime

)on RateChngDate(RateChangeDate)--on 分区方案(分区列)

------------------------------------------------------------------------------------