T-SQL语言系列：（1）DDL操作

DDL（数据定义语言） Create Add ALter Drop

DML（数据操作语言）Insert Delete Update Select

DCL（数据控制语言）Commit Rollback

 

---创建数据库

use master

if exists(select * from sysdatabases where name='TestDB')
drop database TestDB

create database TestDB
on primary
(
   name=TestDB,
   filename='f:\TestDB.mdf',
   size=10MB,
   maxsize=100MB,
   filegrowth=10%
)
log on
(
    name=TestDB_log,
    filename='f:\TestDB.ldf',
    size=1MB,
    filegrowth=10%
)

---删除数据库

use master

if exists(select * from sysdatabases where name='TestDB')
drop database TestDB

---创建数据表

use TestDB

create table Student
(
    Id int,
    Name varchar(10),
    Age int,
    Address varchar(50),
    Phone varchar(20)
)

---更改数据表

use TestDB

--增加字段
alter table Student 
add Gender varchar(2)
--修改字段
alter table Student
alter column Gender varchar(10)
--删除字段
alter table Student
drop column Gender

---删除数据表

use TestDB

drop table Student

---创建带约束的数据表

--主键表
create table Grade
(
    Id int constraint PK_GId primary key,
    Name varchar(50)
)
--外键表
create table Student
(
    --主键约束
    Id int constraint PK_SId primary key,
    --唯一约束，非空约束
    Name varchar(10) constraint UQ_Name unique  not null,
    --默认约束
    Age int constraint DF_Age default 18,
    Address varchar(50),
    Phone varchar(20),
    --检查约束
    Gender char(2) constraint CK_Gender Check(Gender in('男','女')),
    --外键约束
    GradeId int constraint FK_GradeId foreign key references Grade(Id)
)

---添加非空约束

alter table Student
alter column Name int not null

---删除非空约束

alter table Student
alter column Name int null

---添加主键约束

alter table Student
add constraint PK_SId primary key
( 
Id 
)

---删除主键约束

alter table Student
drop constraint PK_SId

 ---添加唯一约束

alter table Student
add constraint UQ_Name unique
(
    Name
)

---删除唯一约束

alter table Student
drop constraint UQ_Name 

---添加检查约束

alter table Student
add constraint CK_Gender Check(Gender in('男','女'))

---删除检查约束

alter table Student
drop constraint CK_Gender

 ---添加默认约束

alter table Student
add constraint DF_Age default 18 for Age

---删除默认约束

alter table Student
drop constraint DF_Age

---添加外键约束

alter table Student
add constraint FK_GradeId foreign key (GradeId)
references Grade(Id)

---删除外键约束

alter table Student
drop constraint FK_GradeId

---创建视图

create view Student_view
as
select Id,Name,Phone from Student

---更改视图名

exec sp_rename 'student_view','stu_view'

---更改视图

alter view student_view
as 
select Id,Name from Student

---删除视图

drop view Student_view

转载于:https://www.cnblogs.com/ForceBaker/p/8675463.html