【第11讲】数据存储--T-SQL脚本数据库设计与编程

一、数据库简介

• 课程管理系统数据（CourseManageDB）
• 讲师表(Teacher)保存讲师相关信息，编号、用户名、密码.......
• 课程表(Course):保存课程信息,课程Id、课程名、课程内容、课时、学分
• 课程分类表(CourseCategory):包括课程分类信息，课程Id和课程分类名

系统数据库：

• master数据主要用来管理其他数据库的信息。
• model数据库：模板数据...

二、创建数据库

数据文件的构成：

【1】主数据文件，一个数据库有，且只能有一个主数据文件。文件后缀名为mdf

【2】次要数据文件，可以根据需要，添加多个，并且分布到不同的磁盘路径。文件后缀名为ndf

【3】日志文件，有且至少有一个。文件后缀名为ldf

学习T-SQL脚本，最大的好处就是你能感觉学到了东西！并且，在数据库兼容性上非常强大！

特别注意：drop关键字，如果你在开发中使用，要特别小心，万一删错是无法挽回的。

use master
go
if exists(select * from sysdatabases where name='CourseManageDB')
drop database CourseManageDB
go

--创建数据库
create database CourseManageDB
on primary
(
	--数据库的逻辑文件名（系统使用必须唯一）
	name = 'CourseManageDB_data',
	--指定数据库物理文件名（绝对路径）
	filename = 'D:\DB\CourseManageDB_data.mdf',--主数据库文件名
	--数据库初始文件大小(一定要根据实际生产需求决定)
	size=20MB,
	--数据库文件的增量值（也要参考文件本身大小）
	filegrowth=1MB
)
,
--创建次要数据库文件
(
	name = 'CourseManageDB_data1',
	filename = 'D:\DB\CourseManageDB_data.ndf',--次要数据库文件名
	size=20MB,
	filegrowth=1MB
)
--创建日志文件
log on
(
	name = 'CourseManageDB_log',
	filename = 'D:\DB\CourseManageDB_log.ldf',--日志文件名
	size=20MB,
	filegrowth=1MB --最后一句不能有‘,’
)
go

三、创建数据表

--
use CourseManageDB
go
if exists(select * from sysobjects where name='Teacher')
drop table Teacher
go
--创建讲师表
create table Teacher
(
	TeacherId int identity(10000,1) primary key,--讲师编号 主键自带约束，不能重复
	LoginAccount varchar(50) not null,--登录用户名
	LoginPwd varchar(18) check(len(LoginPwd)>=6 and len(LoginPwd)<=18) not null,--登录密码
	TeacherName varchar(20)not null,--讲师名
	PhoneNumber char(11)not null,--讲师电话号码
	NowAddress nvarchar(100) --讲师住址
)
go


use CourseManageDB
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


use CourseManageDB
go
--创建课程表
if exists(select * from sysobjects where name = 'Course')--判断是否有这个表，如果有就删掉重新创建
drop table Course
go
create table Course
(
	CourseId int primary key,--课程ID
	CourseName varchar(20) not null,--课程名
	CourseContent nvarchar(500) not null,--课程内容
	ClassHour int not null,--课时
	Credit int not null--学分
)
go

四、添加约束

1.检查（check）约束：也就是添加数据的时候，会检查这个字段的值，是否在约束的条件范围之内。

LoginPwd varchar(18) check(len(LoginPwd)>=6 and len(LoginPwd)<=18) not null,--登录密码

2.主键（primary key）约束：要求不能重复。

TeacherId int identity(10000,1) primary key,--讲师编号 主键自带约束，不能重复

3.标识列（identity）：也就是这个列也必须是唯一的，通常由系统帮我们自动生成，我们不用显式的插入数据。

TeacherId int identity(10000,1) primary key,

格式：identity（标识种子，增值量）

identity（1000，1）

常见错误：标识列显式给值；插入的列的个数和对应的值个数不一致。

PS：标识列，在删除数据后，不会自动补充，你也没必要考虑这个。

学习要求：熟练掌握insert、update、delete、select 语句的基本语法格式,附上一个链接仅供参考,

SQL基础语法select|insert|update|delete(增删改查) 简单使用 - mmzz3322 - 博客园 (cnblogs.com)

4.默认约束：给字段添加默认值。

NowAddress nvarchar(100) default('地址不详') --讲师住址

有数据时使用传入的数据,没数据的话使用default中定义的默认值。

5.唯一约束：（自己学习）

6.外键约束：如果两个数据表之间有关系，但是我们没有约束，有时候在添加数据的时候，会造成数据不一致的情况。

CategoryId int references CourseCategory(CategoryId) not null--外键约束

--如果两个数据表之间有关系，但是我们没有约束，有时候在添加数据的时候会造成数据不一致的情况

--作用1：当我们在外键表中，添加数据的时候，如果和主表建立引用关系，则会自动的从主表中对应的字段中查询数据是否一致，不一致会拒绝。保证数据一致。

--作用2：后面我们讲解查询的时候，也是非常方便的管理数据。

--添加课程信息
insert into Course(CourseName,CourseContent,ClassHour,Credit,CategoryId)
values('.NET上位机开发VIP课程09','C#基础/OOP/SQL/WinFrom/ASP.NET/WPF/WCF',500,10,10)
--此处插入数据之前定义了CourseName varchar(20),在这种情况下之能输入10个汉字会报错：将截断字符串或二进制数据。
--CourseId最开始没写，修改为标识列之后重新建表


--未添加外键约束测试数据，能够插入但是无法和课程分类表对应，插入的数据是无效的
insert into Course(CourseName,CourseContent,ClassHour,Credit,CategoryId)
values('.NET上位机开发VIP课程10','基于PLC+C#开发空压机控制系统',500,10,20)

select * from Course
select * from CourseCategory

7.使用innser join 实现联合查询

select CourseName,CourseContent,ClassHour,Credit,Course.CategoryId,CategoryName from Course
inner join CourseCategory on Course.CategoryId=CourseCategory.CategoryId

--会出现的问题
--未使用inner join时应用另一张表中的列时提示列名CategoryName无效
--但两个表中都有CategoryId时需要显式地指出表名，否则提示列名"CategoryId"不明确

select CourseName,CourseContent,ClassHour,Credit,Course.TeacherId,TeacherName from Course

inner join Teacher on Course.TeacherId=Teacher.TeacherId
inner join Teacher on Teacher.TeacherId=Course.TeacherId

--主键和外键的顺序可以对调

8.数据库关系图的建立。