SQL Serever学习10——T-SQL语句
在sqlserver2018中使用的是Transact-SQL语言，简称T-SQL。
数据库的创建和管理
数据定义语言DDL
DDL功能包括数据库，表，索引，视图，存储过程
数据库：CREATE DATABASE , DROP DATABASE
表：CREATE TABLE , DROP TABLE , ALTER TABLE
索引：CREATE INDEX , DROP INDEX
视图：CREATE VIEW , DROP VIEW
数据库创建
创建数据库包括：数据库名，文件位置，大小，事务日志文件位置和大小
数据库定义需要注意：
定义数据库名，sqlserver中，数据库名称最多为128字符，每个系统最多可以管理用户数据库32767个
定义数据文件，数据库文件最小为3MB，文件增长10%，可以定义多个数据文件，默认第一个为主文件
定义日志文件，在LOG ON子句中，日志文件的长度最小值为1MB
创建数据库
数据库名是销售管理，文件位置保存到D盘根目录下的销售数据库文件夹下，文件名为销售管理_data，初始值为16MB，每次增长为6MB，上限为100MB。
日志文件名是销售管理_log，初始值1MB，最大为5MB，每次增长10%
/*创建数据库*/ CREATE DATABASE 销售管理 ON (NAME=销售管理_data, FILENAME='d:\销售管理数据库\销售管理_data.mdf', SIZE=16, MAXSIZE=100, FILEGROWTH=6) LOG ON (NAME=销售管理_log, FILENAME='d:\销售管理数据库\销售管理_log.ldf', SIZE=1, MAXSIZE=5, FILEGROWTH=10%) GO
删除数据库
/*删除数据库*/ DROP DATABASE 销售管理 GO
数据表的创建和管理
数据表的创建
每一个数据表有一个名称，叫做表名，或者关系名，表名必须以字母开头，最大长度为30个字符，一张表包括若干字段，字段名唯一。
在销售管理数据库中创建5个表：商品表，买家表，买家级别表，商品类型表，销售表。
/*切换到数据库*/ USE 销售管理 GO /*创建 商品表*/ CREATE TABLE 商品表 (商品编号 CHAR(3), 商品名称 VARCHAR(50), 品牌 VARCHAR(20), 型号 VARCHAR(20),
类型 CHAR(3), 进价 MONEY, 销售价 MONEY, 库存 INT) GO /*创建 买家表*/ CREATE TABLE 买家表 (买家编号 CHAR(3), 买家名称 VARCHAR(50), 电话 VARCHAR(20), 级别 CHAR(3)) GO /*创建 买家级别表*/ CREATE TABLE 买家级别表 (级别编号 CHAR(3), 级别名称 VARCHAR(50), 享受折扣 FLOAT, 特权 VARCHAR(50)) GO /*创建 商品类型表*/ CREATE TABLE 商品类型表 (类型编号 CHAR(3), 类型名称 VARCHAR(50), 级别 VARCHAR(20)) GO /*创建 销售表*/ CREATE TABLE 销售表 (ID INT, 商品编号 CHAR(3), 买家编号 CHAR(3), 实际销售价格 MONEY, 销售日期 DATETIME, 销售数量 INT) GO
添加约束
在创建表的时候，可以在字段后添加约束，但是一般不这样混用，推荐奖添加约束和创建表的语句分开，首先创建数据表，然后通过修改数据表的结构来添加约束。
常用的约束有5个：
- 主键约束，就是添加主键，要求主键字段唯一并且不为空
- 唯一约束，要求字段唯一
- 检查约束，就是CHECK约束，限制字段格式
- 默认约束，就是DEFAULT约束，给字段默认值
- 外键约束，就是FOREIGN约束，2个表之间的关系
刚才创建了数据表，现在来给数据表添加约束条件
添加主键约束
买家表的买家编号色设置为主键，商品表的商品编号设置为主键
注意：设置为主键的字段需要NOT NULL
/*切换到数据库*/ USE 销售管理 GO /* 商品表*/ ALTER TABLE 商品表 ADD CONSTRAINT PK_商品编号 PRIMARY KEY(商品编号) /* 买家表*/ ALTER TABLE 买家表 ADD CONSTRAINT PK_买家编号 PRIMARY KEY(买家编号)
添加默认约束
设置商品表的品牌字段默认为A牌
/*切换到数据库*/
USE 销售管理
GO
/* 商品表*/
ALTER TABLE 商品表
ADD CONSTRAINT DF_品牌 DEFAULT('A牌') FOR 品牌
添加条件约束
要求商品表的进价>0，买家表的买家编号为M**格式
/*切换到数据库*/ USE 销售管理 GO /* 商品表*/ ALTER TABLE 商品表 ADD CONSTRAINT CK_进价 CHECK(进价>0) /* 买家表*/ ALTER TABLE 买家表 ADD CONSTRAINT CK_编号 CHECK(买家编号 like 'M__')
添加外键约束
建立买家表和买家类型表之间的关联，建立商品表和商品类型表的关联
注意：外键表不能有数据，否则报错的！
/*切换到数据库*/ USE 销售管理 GO /* 商品表*/ ALTER TABLE 商品表 ADD CONSTRAINT FK_sp FOREIGN KEY (类型) REFERENCES 商品类型表(类型编号) GO /* 买家表*/ ALTER TABLE 买家表 ADD CONSTRAINT FK_mj FOREIGN KEY (级别) REFERENCES 买家级别表(级别编号) GO
数据表的删除
删除买家表
/*切换到数据库*/ USE 销售管理 GO /* 买家表*/ DROP TABLE 买家表 GO
视图的创建和管理
因为公司的很多日常工作是经常性的，如果引入一些视图机制，会大大提高工作效率。
创建视图
创建高价商品视图，显示所有进价在4000元以上的商品名称，品牌和进价
/*切换到数据库*/ USE 销售管理 GO /* 创建视图*/ CREATE VIEW 高价商品_view AS SELECT 商品名称,品牌,进价 FROM 商品表 WHERE 进价>4000 GO
使用视图
查看高价商品的名称
/*切换到数据库*/ USE 销售管理 GO /* 使用视图*/ SELECT 商品名称 FROM 高价商品_view GO
删除视图
删除高价商品视图
/* 删除视图*/ DROP VIEW 高价商品_view
规则和默认的创建和管理
在某一个字段建立规则，属于列级约束，而表级约束单独存在数据库中，是一个约束文件，将这个约束文件绑定到相应的字段，即可对该字段约束。
创建规则
要求字段的值大于0
/*切换到数据库*/ USE 销售管理 GO /*创建规则*/ CREATE RULE 数值_rule AS @SZ>0 GO
绑定规则
使用存储过程sp_bindrule
将数值_rule绑定到商品表的进价和销售价字段上
/*切换到数据库*/ USE 销售管理 GO /*绑定规则*/ sp_bindrule 数值_rule,'商品表.进价' GO sp_bindrule 数值_rule,'商品表.销售价' GO
执行后的提示
查看字段属性，确认绑定
解除绑定
使用存储过程sp_unbindrule
解除商品表的销售价字段上的绑定
/*切换到数据库*/ USE 销售管理 GO /*解除绑定规则*/ sp_unbindrule '商品表.销售价' GO
执行成功会有解除绑定信息提示
删除规则
当需要删除一个规则时，首先需要将规则从绑定的字段上全部解除后方可删除
/*切换到数据库*/ USE 销售管理 GO /*删除规则*/ DROP RULE 数值_rule GO
如果没有删除报错
默认对象的管理
默认对象的使用和管理方式和规则相似
默认规则可以在表定义的时候申明，也可以使用命令创建
创建默认对象
/*切换到数据库*/ USE 销售管理 GO /*默认规则*/ CREATE DEFAULT 男_DF AS '男' GO
绑定默认对象
/*切换到数据库*/ USE 销售管理 GO /*默认规则*/ sp_bindefault 男_DF,'用户表.性别' GO
解除绑定
/*切换到数据库*/ USE 销售管理 GO /*默认规则*/ sp_unbindefault '用户表.性别' GO
删除默认对象
删除前也应该先解除所有的绑定，否则报错
/*切换到数据库*/ USE 销售管理 GO /*默认规则*/ DROP DEFAULT 男_DF GO
