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