sql server学习笔记 -- 库、表操作
0、类型
（1）字符
-
char(n)定长字符, 无论存储数据是否真的到了n个字节, 都要占用n个字节 -
varchar(n)变长字符, 最多占用n个字节, 如果有m个字节, 且m<n, 就真实占用m个字节 -
text长文本, 比如有几千几万字, 效率不高 -
类型前面加n存储unicode字符, 对中文友好, 1中文2字节-
varchar(100)能存100英文50中文 -
nvarchar(100)能存100英文100中文
-
（2）时间
-
date存储年月日 -
datetime存储年月日时分秒 -
smalldatetime存储年月日时分秒, 但范围比datetime更小
（3）小数
-
float -
decimal(m, n)总长度为m, 小数位数为n -
money钱
1、创建数据库
-
ON数据文件 -
LOG ON日志文件
-- 创建前删库慎用！
IF EXISTS(select * from sys.databases where name = 'DBTST')
DROP DATABASE DBTEST
CREATE DATABASE [DBTEST]
CONTAINMENT = NONE
ON PRIMARY -- 数据文件
( -- 逻辑名称
NAME = N'DBTEST',
-- 物理路径和名称
FILENAME = N'D:\SQL Server\SQL Server19\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBTEST.mdf' ,
-- 文件初始大小, 最大大小
SIZE = 8192KB ,
MAXSIZE = UNLIMITED,
-- 增长方式可以写大小, 也可以写百分比
FILEGROWTH = 65536KB
)
LOG ON -- 日志文件
( -- 逻辑名称\物理名称:"习惯直接在数据文件名后加_log"
NAME = N'DBTEST_log',
FILENAME = N'D:\SQL Server\SQL Server19\MSSQL15.MSSQLSERVER\MSSQL\DATA\DBTEST_log.ldf' ,
SIZE = 8192KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 65536KB
)
WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO2、创建表
-
primary key主键 -
identity(n,m)自动增长, 初始值n, 增长值m -
default(值)默认为值 -
check(字段1 = 值1 or/and 字段2 = 值2)对某个字段进行规定, 如果对数据进行添加修改的时候, 必须遵守规定 -
unique唯一约束 -
references 关联表(外键)引用外键, 将关联表的键作为当前键
-- 切换数据库
use DBTEST;
-- 创建表
create table 表名
(
键 类型(长度)
primary key identity(初始值,增长值)
unique
default(值)
check(字段1 = 值1 or / and 字段2 = 值2)
not null,
)（1）例子 -- 建表(部门, 职级, 员工)
-
部门
-
部门编号
-
primary key主键 -
identity(1,1)自动增长, 初始值1, 增长值1
-
-
部门名称
-
部门描述
-
-- 判断表是否存在 type = U 代表用户自定义的表, 如果有就删了
if exists(select * from from sys.objects where name = 'Department' and type = 'U')
drop table Department
create table Department
(
-- 部门编号, primary key: 主键, `identity(n,m)`自动增长, 初始值n, 增长值m
DepartmentId int primary key identity(1,1),
-- 部门名称, 字符串(长度50) 非空
DepartmentName nvarchar(50) not null,
-- 部门描述, 内容比较多的字符
DepartmentRemark text
)-
职级
-
职级编号
-
职级名称
-
职级描述
-
if exists(select * from from sys.objects where name = 'Rank' and type = 'U')
drop table Rank
create table Rank
(
RankId int primary key identity(1,1),
RankName nvarchar(50) not null,
RankRemark text
)-
员工
-
员工编号
-
部门编号, 不能超过已有的编号
-
职级编号, 不能超过已有的编号
-
员工名
-
性别, 男女单选
-
出生日期
-
薪水, 1000 - 1000000
-
电话
-
地址
-
添加时间, 自动记录当前时间
-
if exists(select * from sys.objects where type = 'U' and name = 'People')
drop table People
create table People
(
PeopleId int primary key identity(1,1),
DepartmentId int references Department(DepartmentId) not null,
RankId int references Rank(RankId) not null,
PeopleName varchar(50) not null,
PeopleSex nvarchar(1) default('男') check(PeopleSex = '男' or PeopleSex = '女') not null,
PeopleBirth smalldatetime not null,
PeopleSalary decimal(12,2) check(PeopleSalary >= 1000 and <=1000000) not null,
PeoplePhone varchar(20) unique not null,
PeopleAddress varchar(300),
-- 自动记录当前时间
PeopleAddTime smalldatetime default(getdate())
)3、修改表结构
（1）添加列
alter table 表名 add 新列名 数据类型（2）修改列
-
如果该列已有数据, 修改长度, 修改数据类型都会报错
alter table 表名 alter column 列名 数据类型（3）删除列
alter table 表名 drop column 列名4、维护约束
（1）删除约束
-
不是专业运维, 尽量别操作, 约束名要去sys表里查询, 约束名称是系统自动生成的
alter table 表名 drop constraint 系统自动生成的约束名（2）添加约束（check）
alter table 表名 add constraint 系统自动生成的约束名 check(约束表达式)
-- 添加工资字段约束, 工资必须在1000-100000
alter table Prople add constraint 约束名 check(PeopleSalary >= 1000 and <=1000000)（3）添加约束（主键）
alter table 表名 add constraint 系统自动生成的约束名 primary key(列名)（3）添加约束（唯一）
alter table 表名 add constraint 系统自动生成的约束名 unique(列名)（4）添加约束（默认值）
alter table 表名 add constraint 系统自动生成的约束名 default 默认值 for 列名（5）添加外键约束
alter table 表名 add constraint 系统自动生成的约束名 foreign key(列名) references 关联表(列名:通常是主键)