sql server数据库技术-sql server提供的数据完整性约束

SQL Server 数据库设计概述

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

1、主数据库

是SQL Server 2012的核心数据库，一旦损坏，数据库软件将无法运行。 主要包含以下主要信息：

1）所有用户登录名和用户ID都属于角色

2）数据库存放路径

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

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

2、模型数据库

创建数据库时，总是使用一组预定义的标准作为模板来创建它。 使用模型数据库作为模板创建其他数据库。 而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_DB
on primary
(
--主数据库文件--
name = 'Test_DB',--主数据文件逻辑名
fileName = 'E:\DB\Test_DB.mdf', --主数据文件物理逻辑名
size = 5MB, --初始值大小，可以使用KB，MB，GB，TB做后缀，不指定则使用model数据库中的主文件大小
maxsize = 100MB, --最大大小，一般不限制大小， 一直到磁盘满，maxsize = unlimited
filegrowth = 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 数据库迁移：

方法一：“分离/附加”数据库，即将数据库文件（.MDF）和对应的日志文件（.LDF）复制到其他磁盘备份sql server数据库技术，然后将这两个文件复制到任何需要该数据库的系统中之中。

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

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

“任务”-“分离”

然后分离数据库页面并选中“更新统计信息”复选框。 如果“消息”栏中没有显示活动连接，则“状态”栏显示“就绪”； 否则显示“Not Ready”，必须勾选“Delete Connection”栏的复选框。分离后数据库在资源管理器中消失

将其他数据库文件和日志文件复制到已创建的文件夹中。

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

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

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

数据定义语言（Data Definition Language，简称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 --添加一个字段Birthday
ALTER TABLE Person DROP COLUMN Birthday --删除字段Birthday
ALTER TABLE Persons ALTER COLUMN Birthday year --修改字段Birthday的属性

删除语句

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

DROP TABLE 表名（被删除表的结构、属性、索引也会被删除）

TRUNCATE TABLE 表名（删除表中的数据，但不删除表本身）

DROP DATABASE 数据库名（删除数据库）

DROP TABLE Persons
TRUNCATE TABLE Persons
DROP 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语句中，使用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 语句可以从表中删除一行或多行数据。

从 table_or_name WHERE search_condition 中删除

DELETE FROM Person WHERE LastName = 'Wilson’

选择语句

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

SELECT 列名 FROM 表名 WHERE 条件

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

选择语句

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

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

解释：

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

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

GROUP BY：与聚合函数结合使用sql server数据库技术，按一列或多列对结果集进行分组。

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 90
SELECT * 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 --默认升序，不加ASC
SELECT * FROM Result WHERE Result > 50 ORDER BY Result DESC
SELECT * 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

使用 GROUP BY 根据一列或多列对结果进行分类和汇总。 它通常与统计功能一起使用。 常用的统计函数有：

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

语法结构如下：

SELECT column_name,
aggregate_function(column_name)
FROM table_name
WHERE search_condition
GROUP 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_name
WHERE search_condition
GROUP BY group_by_expression
HAVING search_condition
ORDER BY order_ expression [ASC] | [DESC] ;

示例数据库表：

有实例：

1）按城市查询总销售额，以城市为“加拿大”组作为过滤条件；

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

2）按城市查询总销量，以累计销量大于100为过滤条件；

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

从上面可以看出，经过HAVING过滤后，查询结果和第一项的查询结果是不一样的。

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

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

那么我们来看看这两种说法的区别：

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

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