数据库设计案例（SQL）

SQL Server 数据库设计概述

SQL Server 系统数据库在安装软件时自动创建，用于协助系统共同完成对数据库的操作；也是数据库运行的基础；

1，master数据库

是SQL Server 2012的核心数据库，如果损坏则数据库软件无法运行，主要包含如下主要信息：

1）所有用户登陆名和用户ID所属角色

2）数据库存储路径

3）服务器中数据库的名称和相关信息

4）系统配置设置， SQL Server 初始化信息

2，model数据库

在创建数据库时，总是以一套预定义的标准为模板进行创建的。以model数据库为模板来创建其他数据库。且model数据库是tempdb数据库的基础。

3，tempdb数据库

它是一个临时数据库，用来存储用户建立的临时表和临时存储过程，存储用户定义的全局变量值。它存在于SQL Server会话期间，会话结束，则关闭tempdb数据库，且数据库丢失。

4，msdb数据库

用于代理计划警报和作业

SQL Server 数据库存储文件

数据库文件是由数据文件和事务日志文件组成。

1，数据库文件指数据库中用来存储数据库数据和数据库对象的文件，一个数据库只能由一个主数据库文件，扩展名为 .mdf

2, 次数据库文件包含除主数据库文件外的所有数据文件，一个数据库可以没有次数据库文件，也可以由多个，扩展名为 .ndf

3, 日志文件由一系列日志记录组成，它记录了存储数据库的更新情况等事务日志信息，用户对数据库进行的插入，删除，更新都会记录在日志文件中。数据库发生损坏时可根据日志文件分析出错原因，或者数据丢失时，使用事务日志恢复数据库。每个数据库至少要有一个日志文件。

SQL Server 数据库创建，使用T-SQL语言创建：

if exists(select * from sysdatabases where name = 'Test_DB')--exists返回‘true'则执行删除数据库操作--drop database Test_DB--exists返回‘false'则表明数据库不存在，直接创建 create database Test_DBon primary(--主数据库文件--name = 'Test_DB',--主数据文件逻辑名fileName = 'E:\DB\Test_DB.mdf', --主数据文件物理逻辑名size = 5MB, --初始值大小，可以使用KB，MB，GB，TB做后缀，不指定则使用model数据库中的主文件大小maxsize = 100MB, --最大大小，一般不限制大小， 一直到磁盘满，maxsize = unlimitedfilegrowth = 15% --数据文件增长量，该值可以是KB，MB，GB，TB或者百分比(%),%增量大小为发生增长时文件大小的指定百分比)log on(--日志文件--name = 'Test_DB_log',filename = 'E:\DB\Test_DB.ldf',size = 2MB,filegrowth = 1MB)go

使用T-SQL语言删除数据库：

DROP DATABASE database_name ；Eg: DROP DATABASE Test_DB；

SQL Server 数据库迁移：

方法1：“分离/附加”数据库，即把数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘上作备份，然后把这两个文件再拷贝到任何需要这个数据库的系统之中。

分离数据库就是将某个数据库从SQL Server数据库列表中删除，使其不再被SQL Server管理和使用，但该数据库的文件（.MDF）和对应的日志文件（.LDF）完好无损。分离成功后，就可以把该数据库文件（.MDF）和对应的日志文件（.LDF）拷贝到其它磁盘中作为备份保存。

分离之前，设置数据库为单个用户，并记住该数据库所在路径。

“任务”—“分离”

然后分离数据库页面选中“更新统计信息”复选框。若“消息”列中没有显示存在活动连接，则“状态”列显示为“就绪”；否则显示“未就绪”，此时必须勾选“删除连接”列的复选框。分离后资源管理器中数据库消失

将需要附加的数据库文件和日志文件拷贝到某个已经创建好的文件夹中。

右击数据库对象，并在快捷菜单中选择“附加”命令，打开“附加数据库”窗口。

添加—选择需要附件的数据库的.MDF文件。“附件为”数据库名称可修改。

数据定义语言/数据操纵语言

数据定义语言（DDL）是指用来定义和管理数据库以及数据库中各种对象的语句，这些语句包括CREATE、ALTER和DROP等。在SQL Server中，数据库对象包括表、视图、触发器、存储过程、规则、默认、用户自定义的数据类型等。这些对象的创建、修改和删除等都可以通过使用CREATE，ALTER，DROP等语句来完成。

创建如图所示表，使用DDL语言如何创建？？？

 CREATE TABLE Persons ( Id int not null, LastName varchar(255) not null, FirstName varchar(255), Address varchar(255), City varchar(255) PRIMARY KEY (Id))

如何再增加一个字段？？？

ALTER TABLE Persons ADD Birthday date --添加一个字段BirthdayALTER TABLE Person DROP COLUMN Birthday --删除字段BirthdayALTER TABLE Persons ALTER COLUMN Birthday year --修改字段Birthday的属性

DROP 语句

DROP 可以删除索引、表和数据库。

DROP TABLE 表名称 (删除表的结构、属性以及索引也会被删除)

TRUNCATE TABLE 表名称 (去表内的数据，但并不删除表本身)

DROP DATABASE 数据库名称 (删除数据库)

DROP TABLE PersonsTRUNCATE TABLE PersonsDROP DATABASE Test_DB

数据操纵语言(DML)

数据操纵语言(DML)是指用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。

INSERT语句用于向数据库表或者视图中加入一行数据。INSERT语句的语法形式如下：

INSERT [INTO] table_or_view [(column_list)] VALUES(data_values)

其中，table_or_view是指要插入新记录的表或视图；column_list是可选项，指定待添加数据的列； VALUES子句指定待添加数据的具体值。列名的排列顺序不一定要和表定义时的顺序一致。但当指定列名表时VALUES子句值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。

INSERT INTO Persons VALUES (1,'Gates', 'Bill', 'Xuanwumen 10', 'Beijing')--在指定的列中插入数据:INSERT INTO Persons (Id,LastName, Address) VALUES (2,'Wilson', 'Champs-Elysees') SELECT * FROM Persons --查询表中所有数据

UPDATE 语句

UPDATE用于更新表中已经存在的数据 。

UPDATE语句既可以一次更新一行数据，也可以一次更新许多行，甚至可以一次更新表中的全部数据行。

在UPDATE语句中，使用WHERE子句指定要更新的数据行满足的基本条件，使用SET子句给出新的数据。新数据既可以是常量，也可以是指定的表达式 。

UPDATE table_or_view_name SET column_name = expression, … WHERE search_condition --Eg1:更新某一行中的一个列UPDATE Persons SET FirstName = 'Fred' WHERE LastName = 'Wilson’ --Eg2:更新某一行中的若干列UPDATE Persons SET Address = 'Zhongshan 23', City = 'Nanjing’ WHERE LastName = 'Wilson'

DELETE 语句

当表中的数据不再需要时，可以删除。一般情况下，使用DELETE语句删除数据。DELETE语句可以从一个表中删除一行或多行数据。

DELETE FROM table_or_name WHERE search_condition

DELETE FROM Person WHERE LastName = 'Wilson’

SELECT 语句

用于检索表中数据，结果被存储在一个结果表中（称为结果集）。

SELECT 列名称 FROM 表名称 WHERE 条件

SELECT LastName,FirstName FROM Persons --查询表中LastName,FirstName所有记录SELECT * FROM Persons WHERE City='Beijing' --查询City='Beijing' 的所有记录

SELECT语句

在数据库中，使用最多的就是查询语句：SELECT 语句用于检索表中的数据。常用的查询语句格式如下：

SELECT [DISTINCT] [TOP (n)] { * | select_list }FROM table_name | view_nameWHERE search_condition[GROUP BY group_by_expression ] [HAVING search_condition][ORDER BY order_ expression [ASC] | [DESC] ]

解释：

DISTINCT：指定在结果中只能包含唯一行；

TOP(n)：子句用于规定要返回的记录的数目。

GROUP BY： 用于结合合计函数，根据一个或多个列对结果集进行分组。

HAVING：指定组或聚合的搜索条件， WHERE 关键字无法与合计函数一起使用时使用。

ORDER BY：指定查询结果的排序方式，ASC:升序，DESC：降序

1.查询基本语句

SELECT * FROM Student --查询Student表中所有数据

2.查询指定字段

SELECT StudentName, phone, Address, IDENTITYcard FROM Student 

--查询Student表中指定字段的数据

3.查询结果中使用表达式1

SELECT StudentNo, SubjectNo, StudentResult -10 AS Result FROM Result WHERE StudentResult > 80 

--字段的数据,并将StudentResult减去10后作为新的查询结果

4.查询结果中使用表达式2

SELECT ‘学号:’+ StudentNo + ‘-课程编号:’ + SubjectNo, StudentResult -10 AS ‘成绩’ FROM Result WHERE StudentResult > 80 

5.带限定条件的查询

SELECT TOP 3 * FROM Result WHERE StudentResult > 80 

--查询结果中返回前 3 行分数大于80分的人

6.显示部分查询结果

SELECT TOP 3 * FROM Result --查询结果中返回前 3 行SELECT TOP 3 PERCENT StudentNo, SubjectNo, StudentResult FROM Result --查询结果中返回3%的记录

7.带AND的多条件查询

SELECT * FROM Result WHERE StudentResult > 50 AND SubjectNo = 1 --查询结果中分数大于50且课程号位1的人

8.带OR的多条件查询

SELECT * FROM Result WHERE StudentResult > 50 OR SubjectNo = 1--查询结果中分数大于50或者课程号位1的人

9.结合AND和OR的多条件查询

SELECT StudentName, phone, Address, IDENTITYcard FROM Student WHERE ( StudentName LIKE ‘李%’ OR StudentName LIKE ‘王%’ ) AND Sex =‘女’

第一，查询结果中使用LIKE和通配符

上图中介绍了4中通配符的使用方法。下面以实际例子介绍

'当在查询中使用中文时，有时会查询不到数据，实际记录中是有对应的记录的，一般是'由于编码问题，可以在中文字符前面加N，如: N‘李%’'使用通配符%，查询StudentName中第一个字是李，后面任意多个或者一个字SELECT StudentName, phone, Address, IDENTITYcard FROM Student WHERE StudentName LIKE ‘李%’'使用通配符_，查询StudentName中第一个字是李，后面任意一个字SELECT StudentName, phone, Address, IDENTITYcard FROM Student WHERE StudentName LIKE ‘李_’'使用通配符[]，查询StudentName中第一个字是张王李任意一个字符，后面任意多个字SELECT StudentName, phone, Address, IDENTITYcard FROM Student WHERE StudentName LIKE ‘[张王李]%’'使用通配符[^]，查询StudentName中第一个字不在张王李任意一个字符SELECT StudentName, phone, Address, IDENTITYcard FROM Student WHERE StudentName LIKE ‘[^张李]%’

第二，查询中使用操作符

在上图中介绍了常用的操作符的含义。下面以实际例子介绍

（1）操作符 BETWEEN ... AND 选取介于两个值之间的数据范围。值可以是数值、文本或者日期。

SELECT * FROM Result WHERE Result BETWEEN 50 AND 90SELECT * FROM Result WHERE Result NOT BETWEEN 50 AND 90

（2）比较的使用，不等于有两种：<>, !=

SELECT * FROM Result WHERE (Result > 50) AND (Result < 80)

（3）操作符 IN 允许在WHERE子句中规定多个值。也可以使用 NOT IN

SELECT StudentName,phone, Address, IDENTITYcard FROM Student WHERE StudentName IN (N'李雷',N'李莫愁')

（4）对查询结果排序（ASC:升序，DESC:降序）

SELECT * FROM Result ORDER BY Result ASC --默认升序，不加ASCSELECT * FROM Result WHERE Result > 50 ORDER BY Result DESCSELECT * FROM Student ORDER BY [ID] ASC, [IDENTITYcard] DESC --多列排序

（5）使用IS NULL 查询空值，不能使用” =NULL ”

SELECT * FROM Student WHERE phone IS NULL --查询phone为空的记录SELECT * FROM Student WHERE phone IS NOT NULL --查询phone不为空的记录

SELECT高级查询语句

首先要介绍的就是分组查询。比如我们有一个销售订单，这个订单里包含了地域，人员等多个不同的字段信息，我们需要按照地域进行分组查询每个地域的总销售额。或者要按照人员查询每个人的销售总额等信息，那么就需要用到这个分组查询的功能。

使用 GROUP BY 根据一个或者多个列对结果进行分类汇总，通常和统计函数一起使用，常用的统计类函数有：

COUNT(统计组中项数) / COUNT (*)，SUM，AVG，MAX，MIN等。

语法结构如下：

SELECT column_name,aggregate_function(column_name)FROM table_nameWHERE search_conditionGROUP BY group_by_expression;

示范数据库表：

GROUP BY 实例：查询各个城市总的销售额是多少；

SELECT Country, SUM(sales) AS TotalSales FROM Sales GROUP BY Country;

查询结果如下：

从上面我们可以看出，GROUP BY 将城市名称相同的销售额聚集在一起，然后通过 SUM() 聚合函数计算出他们的总和。

重点：GROUP BY 主要作用是用来进行分组聚合查询，有时候会用来进行排重，与 DISTINCT 关键字作用类似。常与 HAVING 关键字一起使用，用来对分组结果进行筛选。

注意： GROUP BY 子句中必须保证 SELECT 语句后列值是可计算的或者在 GROUP BY 列表中。

下面语句会报错：

SELECT Country, Region, SUM(sales) AS TotalSales FROM Sales GROUP BY Country;

2. 统计查询 HAVING

筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用 having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

语法结构如下：

SELECT column_name,aggregate_function(column_name)FROM table_nameWHERE search_conditionGROUP BY group_by_expressionHAVING search_conditionORDER BY order_ expression [ASC] | [DESC] ;

示范数据库表：

HAVING 实例：

1）以城市进行分组查询总的销售额是多少并以城市为“Canada”组为过滤条件；

SELECT Country, SUM(sales) AS TotalSales FROM Sales GROUP BY CountryHAVING Country = 'Canada'

2）以城市进行分组查询总的销售额是多少并以累计销售额大于100为过滤条件；

SELECT Country, SUM(sales) AS TotalSales FROM Sales GROUP BY CountryHAVING SUM(sales) > 300

从上面可以看出通过 HAVING 进行过滤后，查询到的结果和第一项中查询到的结果不同了。

那么 WHERE 也是过滤条件，它和 HAVING 有什么区别呢？

区别：where 子句的作用是在对查询结果进行分组前，将不符合 where 条件的行去掉，即在分组之前过滤数据，where 条件中不能包含聚组函数，使用 where 条件过滤出特定的行。

那么我们来看看一下两条语句的区别：

SELECT Country, Region, SUM(sales) AS TotalSales FROM Sales GROUP BY Country, RegionHAVING SUM(sales) >= 100;

SELECT Country, Region, SUM(sales) AS TotalSales FROM Sales WHERE Country ='Canada'GROUP BY Country, RegionHAVING SUM(sales) >= 100;