SQL server （二）表的操作
表的基本操作
在数据库中，表是由数据按一定的顺序和格式构成的数据集合，是数据库的主要对象。每一行代表一条记录，每一列代表记录的一个字段。
在关系数据库中每一个关系都体现为一张表。
1.设计表
-
表包含的数据类型
-
表中的列数以及列的数据类型
-
哪些列允许空值
-
是否使用以及何处使用约束
-
哪些是主键，哪些是外键
2.数据类型
整型
- BIGINT 大整型 8字节
- INT 常用的整型 4字节
- SMALLINT 小整型 2字节
- TINYINT 微整型 0~255 1字节
- BIT 位类型 0和1 1字节
小数
- DECIMAL (p,s) 和 NUMERIC (p,s) 小数 p确定总位数（默认18），s确定小数位（默认0）
货币
- MONEY 货币值 8字节
- SMALLMONEY 比Money小 4字节
近似数字
- FLOAT 8字节
- REAL 4字节
日期和时间
- DATETIME 8字节 范围 1753.1.1-9999.12.31
- SMALLDATETIME 4字节 1900.1.1-2079.12.31
字符串
- CHAR(n) n长度 范围 1-8000 固定长度 小于n补空格
- VARCHAR(n) n长度 可变
Unicode字符串
-
NCHAR(n) 与char类型，两个字节为一个存储单位
-
NVARCHAR(n)
二进制字符串
-
BINARY(n) 长度固定 为n+4字节 小于n补0 常用于存储图像等数据
-
VARBINARY (n) 长度变化
-
IMAGE 存储照片或图像等
3.创建表
使用 CREATE TABLE 语句
CREATE TABLE
[database_name.[schema_name].|schema_name.]table_name
{(column_name column_property column_constraint)}[,...]
--切换数据库的语法 use 数据库名
use DB2
-- 创建学生表（学号，姓名，年龄，性别）
CREATE TABLE 学生表
(
学号 VARCHAR(15) NOT NULL,
姓名 CHAR(10) NOT NULL,
年龄 INT NULL,
性别 CHAR(5)
)
4.修改表
- 添加（列）属性
ALTER TABLE 表名
ADD 属性 数据类型[(长度)] [NULL|NOT NULL]
--为student表添加sno列
ALTER TABLE student
ADD sno VARCHAR(15) NOT NULL
ALTER TABLE student
add age int
- 修改数据类型
ALTER TABLE 表名
ALTER COLUMN 属性 数据类型
--修改name数据类型为 VARCHAR(20)
ALTER TABLE student
ALTER COLUMN name VARCHAR(20)
- 删除（列）属性
ALTER TABLE 表名
DROP COLUMN 属性
--删除age
ALTER TABLE student
DROP COLUMN age
- 修改列名
EXEC sp_rename '表名.列名', '新列名'
- 修改表名
EXEC sp_rename '原表名', '新表名'
--修改列名 name-->sname
EXEC sp_rename 'student.name', 'sname'
--修改表名
EXEC sp_rename 'student', 'Student'
5.约束
1.主键约束 PRIMARY KEY
唯一确定表中每一条记录的标识符
（一个表中只能有一个主键约束，为空的列不能创建主键约束）
2.外键约束 FOREIGN KEY
用于建立和加强两个表数据之间的连接
（1.两列的数据类型必须一致，2.引用的列必须是主键约束或唯一约束 3.两列列名尽量保持一致）
3.唯一约束 UNIQUE
指定一个列或多个列的值具有唯一性（可以为空）
4.检查约束 CHECK
设置检查条件，以限制输入值
5.默认约束 DEFAULT
插入操作中没有提供输入值时系统会自动加上指定值
创建约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型 （具体的约束说明)
--给学号添加主键约束
ALTER TABLE 学生表
ADD CONSTRAINT PK_学号 PRIMARY KEY (学号)
--给姓名添加唯一约束
ALTER TABLE 学生表
ADD CONSTRAINT UN_姓名 UNIQUE (姓名)
--给年龄添加检查约束，年龄大于18
ALTER TABLE 学生表
ADD CONSTRAINT CK_年龄 CHECK (年龄>18)
--给性别添加默认约束 为男
ALTER TABLE 学生表
ADD CONSTRAINT DF_性别 DEFAULT '男' FOR 性别
--年龄 大于18 小于25
ALTER TABLE 员工信息表
ADD CONSTRAINT CK_年龄 CHECK (年龄>18 AND 年龄<25)
ALTER TABLE 员工信息表
ADD CONSTRAINT CK_部门 CHECK (单位部门='软件测试' OR 单位部门='产品研发')
--创建一个成绩表
CREATE TABLE 成绩表
(
学号 VARCHAR(15) NOT NULL,
成绩 INT
)
--给成绩表的学号建立一个外键约束，引用学生表中的学号（加强两表的联系）
ALTER TABLE 成绩表
ADD CONSTRAINT FK_学号 FOREIGN KEY (学号)
REFERENCES 学生表(学号)
删除约束：
ALTER TABLE 表名
DROP CONSTRAINT 约束名
--删除默认约束
ALTER TABLE 学生表
DROP CONSTRAINT DF_性别
--删除成绩表的外键约束
ALTER TABLE 成绩表
DROP CONSTRAINT FK_学号
--删除主键约束 (删之前不能被引用)
ALTER TABLE 学生表
DROP CONSTRAINT PK_学号
创建表同时创建约束
在tsgl数据库中，分别使用命令创建图书表ts、读者表dz、借阅表jy
/*
1)图书表ts（书号（主键，字符型），书名（字符型），出版社（字符型），
类别（字符型），作者（字符型），出版时间（日期型），
价格（数值型，为正））
*/
USE tsgl
CREATE TABLE ts
(
书号 CHAR(20) PRIMARY KEY,
书名 CHAR(20),
出版社 CHAR(20),
类别 CHAR(20),
作者 CHAR(10),
出版时间 DATETIME,
价格 INT CHECK (价格>0)
)
/*
读者表dz（编号（主键），姓名，单位（缺省值为千锋教育），
性别（限制 男、女），电话（唯一约束））
所有属性都是字符型
*/
CREATE TABLE dz
(
编号 CHAR(20) PRIMARY KEY,
姓名 CHAR(20),
单位 CHAR(25) DEFAULT '千锋教育',
性别 CHAR(5) CHECK (性别='男' OR 性别='女'),
电话 CHAR(20) UNIQUE
)
/*
借阅表jy（书号（主键，ts的外键），编号（主键，dz的外键），
借阅日期（日期型），还书日期（日期型），还书标识（长度为2的字符型））
*/
CREATE TABLE jy
(
书号 CHAR(20) FOREIGN KEY REFERENCES ts(书号),
编号 CHAR(20) FOREIGN KEY REFERENCES dz(编号),
借阅日期 DATETIME,
还书日期 DATETIME,
还书标识 CHAR(2),
PRIMARY KEY(书号,编号) --联合主键，一般放最后
)
6.删除表
使用 DROP TABLE 语句
DROP TABLE 表名
--删除Student表
USE DB2
DROP TABLE Student
