2.T-SQL，SqlServer 创建数据库、表脚本

T-SQL作为SQL的扩展语言，是SQL程序设计语言的增强版，它是用来让应用程序与SQL Server沟通的主要语言。

1.创建数据库

--首先指向要操作的数据库
use master 
go
if exists(select * from sysdatabases where name='CourseTest')
drop database CourseTest
go
--创建数据库
create database CourseTest
on primary
(
name='CourseTest_data1',--数据库得到逻辑文件名
filename='E:\11常用知识笔记\01SQL\01T-SQL\CourseTest_data1.mdf',--数据库的物理文件名（绝对路径）
size=10MB,--数据库初始文件大小
filegrowth=1MB--数据文件增量
),
(
name='CourseTest_data2',
filename='E:\11常用知识笔记\01SQL\01T-SQL\CourseTest_data2.ndf',--次要数据文件名
size=5MB,
filegrowth=1MB
)
log on
(
--创建日志
name='CourseTest_log',
filename='E:\11常用知识笔记\01SQL\01T-SQL\CourseTest_log.ldf',
size=2MB,
filegrowth=1MB
)
go

2.创建数据表

--创建数据表
--指向要操作的数据库
use CourseTest
go
--创建教师表
if  exists(select * from sysobjects where name='Teacher')
drop table Teacher
go
create table Teacher
(
    TeacherId int identity(10000,1) primary key, --教师编号，主键
    LoginAccound varchar(20) not null,--登录账号
    LoginPwd varchar(18) check(len(LoginPwd)>=6 and len(loginPwd)<=18) not null,--检查约束：密码长度
    TeacherName varchar(20) not null,
    Phone char(11) not null,
    NowAddress nvarchar(100) default('地址不详')--住址
)
go
--创建课程分类表
if exists(select * from sysobjects where name='CourseCategory')
drop table CourseCategory
go
create table CourseCategory
(
    CategoryId int identity(10,1) primary key,
    CategoryName varchar(20) not null
)
go

--创建课程表
if exists(select * from sysobjects where name='Course')
drop table Course
go
create table Course
(
    CourseId int identity(10000,1) primary key,
    CourseName varchar(50) not null,
    CourseContent nvarchar(500) not null,
    CourseHour int not null,
    Credit int check(Credit>=1 and Credit<=30) not null,
    CategoryId int references CourseCategory(CategoryId) not null,--外键约束
    TeacherId int references Teacher(TeacherId)
)

3. 添加相关测试数据

--添加教师信息
insert into Teacher(LoginAccount,LoginPwd,TeacherName,Phone,NowAddress)
values('1001','123456','耿老师','13011112222','合肥市包河区'),
('1002','123456','刘老师','13022223333','合肥市蜀山区'),
('1003','123456','张老师','13055556666','合肥市瑶海区')
insert into Teacher(LoginAccount,LoginPwd,TeacherName,Phone)
values('1004','123456','李老师','13040005000')


--添加课程分类
insert into CourseCategory(CategoryName)values('.NET开发'),('前端开发'),('Java开发')

--添加课程信息
insert into Course (CourseName, CourseContent, CourseHour, Credit,CategoryId,TeacherId)
values('.Net/C#上位机开发VIP课程09','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',500,10,10,10000)

nsert into Course(CourseName, CourseContent, CourseHour, Credit,CategoryId, TeacherId)values
('.Net/C#上位机开发VIP课程09','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',500,10,10,10000),
('.Net/C#上位机开发VIP课程06','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',500,10,10,10002),
('.Net/C#上位机开发VIP课程01','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',500,10,10,10000),
('.Net/C#上位机开发VIP课程11','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',566,10,10,10002),
('.Net/C#上位机开发VIP课程14','C#基础/OOP/SQL/WinForm/ASP.NET/WPF/WCF',380,10,10,10000)