SQL-Server基础总结

1.1文件和文件组

1. 数据库文件：主数据库文件（.Mdf）、次数据库文件（.ndf）
2. 事务日志文件：事务日志文件是用来记录数据更新情况的文件。（.ldf）
3. 文件组：文件组是将多个数据库文件集合起来形成的一个整体，每个文件组有一个组名。

事务日志文件不属于任何文件组。

1. 数据的完成整形

1. 实体完整性：实体完整性是指行的完整性。要求表中所有行都有唯一标识符，称为主关键字。
2. 域完整性：与完整性能够保证表中的数据是合法的数据。
3. 引用完整性（参照完整性）：引用完整性是指某列的值必须与其他列匹配。

1. 创建约束：

1. 主键：
2. 检查约束：
3. 外键:
4. 默认约束：
5. 标识符：
6. 列是否允许为空：
7. 唯一约束：

1. 会使用数据导入导出
2. 创建数据库：

例：--创建数据库

CREATE DATABASE Home

ON PRIMARY--默认就属于primary 主文件组，可省略。

(

--主数据文件的具体描述

NAME='Home_data',

FILENAME='G:\Home_data.mdf',--主数据文件的物理名

SIZE=3MB,--主数据文件的初始大小

MAXSIZE=50MB,--主数据文件增长的最大值

FILEGROWTH=10%--主数据文件增长率

),

(

--次数据文件的具体描述

NAME='Home2_data',--次数据文件的逻辑名

FILENAME='G:\Home2_data.ndf',--次数据文件的物理名

SIZE=1MB,--次数据文件的初始大小

MAXSIZE=50MB,--次数据文件增长的最大值

FILEGROWTH=10%--次数据文件增长率

)

LOG ON

(

--日志文件的具体描述

NAME='Home_data',--日志文件的逻辑名

FILENAME='G:\Home_data.ldf',--日志文件的物理名

SIZE=1MB,--日志文件的初始大小

MAXSIZE=1MB--日志文件增长的最大值

),

(

--日志文件的具体描述

NAME='Home2_data',--日志文件的逻辑名

FILENAME='G:\Home2_data.ldf',--日志文件的物理名

SIZE=1MB,--日志文件的初始大小

MAXSIZE=1MB--日志文件增长的最大值

)

GO

1. 删除数据库：

--删除数据库

DROP DATABASE Home

GO 

1. 创建表

--创建表

USE Home

GO 

CREATE TABLE Student--创建学生表

(

StuID INT NOT NULL ,--创建不为空的ID列

StuName VARCHAR(64)NOT NULL,--创建不为空的名称列

StuSex CHAR(2)--性别列

)

GO 

CREATE TABLE StuMarks--创建学生成绩表

(

ExamNO INT NOT NULL,--考号

StuID INT NOT NULL ,--学号

Score INT NOT NULL --成绩

)

1. 删除表

--删除表

USE Home

GO

DROP TABLE dbo.Student

GO 

1. 约束

主键约束：primary key

唯一约束：unique

检查约束：check

默认约束：defult

外键约束：foreign key

1. 添加约束：

 

--为学生表添加主键

ALTER TABLE dbo.Student

ADD CONSTRAINT pk_StuID PRIMARY KEY(StuID)

GO 

--为学生表添加唯一键

ALTER TABLE dbo.Student

ADD CONSTRAINT UQ_StuName UNIQUE (StuName)

GO 

--为学生表添加默认约束

ALTER TABLE dbo.Student

ADD CONSTRAINT DF_StuSex DEFAULT (1) FOR StuSex

GO 

--为学生成绩表添加主键

ALTER TABLE dbo.StuMarks

ADD CONSTRAINT PK_ExamNO PRIMARY KEY (ExamNO)

GO 

--为学生成绩表添加检查约束

ALTER TABLE dbo.StuMarks

ADD CONSTRAINT CK_Score CHECK(Score>=0 AND Score <=100)

GO 

--为学生成绩表添加外键约束

ALTER TABLE dbo.StuMarks

ADD CONSTRAINT FK_StuID REFERENCES dbo.Student (StuID)

GO 

 

1. 删除约束：

--删除约束

ALTER TABLE dbo.StuMarks

DROP CONSTRAINT CK_Score

GO

1. T-SQL语言组成部分：

1. 数据定义语言（Data Definition Language，DDL)用来建立数据库、数据库表和定义列，大部分是以create开头的命令，例如：create table、create database等。
2. 数据操纵语言（Data Manipulation Language，DML）：用来操纵数据库中数据的命令，例如：select、insert、update、delete等。
3. 数据控制语言（Data Control Language，DCL）：用来控制数据库组件的存取许可、存取权限等命令，例如：GRANT、REVOKE等。
4. 流程控制语言（Flow Control Language,FCL）：用于设计应用程序的语句，例如：IF、WHILE、CASE等

1. 条件表达书

常量：常量可以是一个或者多个字符数字符合组成。字符和日期需要用单引号括起来，二进制字符串和数子常量则不需要。

列名：数据表中的名称。

运算符：比较运算符（=、>、>=、<、<=等）、逻辑运算符（AND、OR、NOT等）、

算数运算符（+、-、*、/等）

1. SELECT语句

SELECT *FROM dbo.Student

1. INSERT语句

--插入语句

INSERT INTO dbo.Student( StuID, StuName, StuSex )

VALUES  ( 0, -- StuID - int

          '', -- StuName - varchar(64)

          ''  -- StuSex - char(2)

          )

1. UPDATE语句

UPDATE dbo.Student SET StuID='1'WHERE StuName=''

1. DElETE 语句

DELETE FROM dbo.Student WHERE StuID=''

1. TRUNCATE Table

--删除表中所有数据行

TRUNCATE TABLE dbo.Student

1. 多行插入语句

--多行插入语句

INSERT INTO dbo.Student

        ( StuID, StuName, StuSex )

SELECT 0,'','' UNION

SELECT 1,'','' UNION

SELECT 2,'','' 

--将另一个表中查询出来的数据添加到表中

INSERT INTO dbo.Student

        ( StuID, StuName, StuSex )

SELECT *FROM dbo.StuMarks

1. 查询

--设置字段别名

SELECT StuName name FROM dbo.Student

SELECT name=StuName FROM dbo.Student

SELECT StuName AS name FROM dbo.Student

--合并字段

SELECT StuSex+'-'+StuName AS '性别-姓名' FROM dbo.Student

--返回全部行

SELECT ALL StuID,StuName FROM dbo.Student

--使用distinct关键字过滤重复记录

SELECT DISTINCT StuName FROM dbo.Student

--使用TOP关键字查询若干条记录

SELECT TOP 100 * FROM dbo.Student

1. 查询结果进行处理

1. 比较运算符（大小比较，包括>、>=、<、<=、<>、!>、!<）

--where字句--名称等于张三

SELECT * FROM dbo.Student WHERE StuName='张三'

--where字句--名称不等于张三

SELECT * FROM dbo.Student WHERE StuName<>'张三'

1. 范围运算符（表达式是否在指定范围）

--where字句--between...and

SELECT * FROM dbo.Student WHERE StuID BETWEEN 2 AND 4

--where字句--not between...and

SELECT * FROM dbo.Student WHERE StuID NOT BETWEEN 2 AND 4

1. 列表运算符（判断表达式是否为列表中指定的项）

--where字句--int

SELECT * FROM dbo.Student WHERE StuID IN(1,2)

--where字句--not int

SELECT * FROM dbo.Student WHERE StuID NOT IN(1,2)

1. 空值判断（判断表达式是否为空）

--判断表达式是否为空

SELECT * FROM dbo.Student WHERE StuName IS NULL

SELECT * FROM dbo.Student WHERE StuName IS NOT NULL

1. 逻辑运算符（用于多条件的逻辑连接）

--通配符

SELECT * FROM dbo.Student WHERE StuName LIKE '_san'

SELECT * FROM dbo.Student WHERE StuName LIKE '%san'

SELECT * FROM dbo.Student WHERE StuName LIKE '%san%'

SELECT * FROM dbo.Student WHERE StuName LIKE 'zhan[h-z]san'

SELECT * FROM dbo.Student WHERE StuName LIKE '^zhang'

SELECT * FROM dbo.Student WHERE StuName NOT LIKE '_san'

1. 模式匹配符（判断值是否与指定的字符统配格式相符）

--排序--order by(ASC增序、DESC倒序，默认增序)

SELECT * FROM dbo.Student ORDER BY StuID

SELECT * FROM dbo.Student ORDER BY StuID DESC

--排序--order 多列进行排序

SELECT * FROM dbo.Student ORDER BY StuID DESC,StuName DESC

--排序--order by排序+top

SELECT TOP 3* FROM dbo.Student ORDER BY StuID DESC

1. 对查询结果进行计算

--查询结果进行计算---SUM求和

SELECT SUM(Score) FROM dbo.StuMarks

--查询结果进行计算---AVG平均值

SELECT AVG(Score) FROM dbo.StuMarks

--查询结果进行计算---MAX最大值

SELECT MAX(Score) FROM dbo.StuMarks

--查询结果进行计算---MIN最小值

SELECT MIN(Score) FROM dbo.StuMarks

--查询结果进行计算---count记录行数

SELECT COUNT(*) FROM dbo.StuMarks

--对查询结果进行分组--GROUP BY

SELECT Subject AS 科目,COUNT(*) AS 人数 FROM dbo.StuMarks GROUP BY Subject

注意：使用GROUP BY时GROUP BY字句必须与查询的数目相同

--查询结果进行计算--HAVING字句(对聚合后函数进行筛选)

SELECT Subject AS 科目,AVG(Score) AS 平均分数 FROM dbo.StuMarks GROUP BY Subject

HAVING AVG(Score)>75

1. 链接查询

--链接查询--内部链接

SELECT * FROM dbo.Student a WITH(NOLOCK)

INNER JOIN dbo.StuMarks b WITH(NOLOCK) ON b.StuID = a.StuID

--链接查询--左连接

SELECT * FROM dbo.Student a WITH(NOLOCK)

LEFT JOIN dbo.StuMarks b WITH(NOLOCK) ON b.StuID = a.StuID

--链接查询--右连接

SELECT * FROM dbo.Student a WITH(NOLOCK)

RIGHT JOIN dbo.StuMarks b WITH(NOLOCK) ON b.StuID = a.StuID

--后面可以跟查询条件

--链接查询--交叉连接

SELECT * FROM dbo.Student a WITH(NOLOCK)

CROSS JOIN dbo.StuMarks b WITH(NOLOCK) 

--UNION(查询时列必须一致，数据类型要么相同，要么可以进行转换)

SELECT StuID FROM dbo.Student

UNION 

SELECT StuID FROM dbo.StuMarks

--UNION ALL(查询时列必须一致，数据类型要么相同，要么可以进行转换)

--UNION 和UNION ALL 的区别在于UNION允许有重复的列，UNION ALL可以有

SELECT StuID FROM dbo.Student

UNION 

SELECT StuID FROM dbo.StuMarks

--INTESECT：交际运算，交集将两个结果集相同的记录下来形成一个新的集合

SELECT * FROM dbo.StuMarks

WHERE Score>90

UNION 

SELECT * FROM dbo.StuMarks

WHERE StuID=3

--EXCEPT:减集运算。减集是指比较两个结果集，将EXCEPT关键字的结果集去除交集部分而形成的新的集合

--注意两个表顺序的不同决定不同的结果

SELECT * FROM dbo.StuMarks

WHERE Score>90

EXCEPT 

SELECT * FROM dbo.StuMarks

WHERE StuID=3

1. E-R图

ERB表示：矩形代表实体集

1. 椭圆表示属性
2. 菱形表示关系
3. 直线用来连接实体集与属性，同时也用来链接实体集与关系
4. 直线上的箭头用来表示实体集之间的映射基数。

1. 一个好的数据库设计应满足的条件

尽可能少的数据冗余

没有插入、删除、更新异常

1. 数据库三范式

第一范式要求每列必须是最小的原子单元

第二范式要求每列必须与主键相关

第三范式要求表中割裂必须和主键列直接关联，不允许间接相关。

1. 变量。T-SQL中可以使用两种变量：一种局部变量，一种全局变量

1. 、局部变量：使用局部变量必须以@开头，而且必须使用DECLARE命令后才能使用。

DECLARE @NAME varchar(128)--声明一个变量长度为128的字符变量

局部变量赋值：select @NAME=’zhangsan’或set @NAME=’zhangsan’

注意：select语句变量赋值确保筛选的变量不多于一条，如果多余一条，将把最后一条记录变量赋值给变量。

1. 、全局变量：全局变量是sql系统内部使用的变量，起作用范围并不局限于某一程序二十任何程序均可调用。引用全局变量必须以@@开头，全局变量名成不能与局部变量名相同，否则会在应用程序中出错。

1. 输出语句：用于输出处理的数据结果。

PRINT @NAME

或select @NAME

1. 逻辑控制语句

1. IF....ELSE
2. WHILE...CONTINUE....BREAK

DECLARE @score FLOAT

SELECT @score=AVG((chinese+english+math)/3)FROM dbo.StuMarks

WHILE(@score<80)

BEGIN

........

END

 

1. Case

SELECT StuID,语文=

CASE WHEN chinses<=19 THEN '1' 

WHEN chinses<=39 THEN '2' 

WHEN chinses<=49 THEN '3' 

WHEN chinses<=59 THEN '4' 

WHEN chinses<=69 THEN '5' 

FROM dbo.StuMarks

1. go 批处理：将批处理语句内编译为一个可执行单元，一起执行。
2. 嵌套子查询

1. 嵌套查询
2. 使用IN和NOT IN完成子查

--使用IN和NOT IN完成子查询

SELECT * FROM dbo.Student NOT IN (1,2,3)

SELECT * FROM dbo.Student IN (1,2,3)

1. 使用EXISTS和NOT EXISTS完成子查询

--使用EXISTS和NOT EXISTS完成子查询

SELECT * FROM dbo.Student WHERE EXISTS(SELECT * FROM dbo.StuMarks WHERE Score>80)

1. 使用some、any、all进行子查询：some、any、all后必须跟子查询
2.  使用COMPUTE和COMPUTE BY进行汇总查询

--COMPUTE汇总查询

SELECT * FROM dbo.StuMarks WHERE Subject='' COMPUTE AVG(Score)

--COMPUTE BY汇总查询

SELECT * FROM dbo.StuMarks WHERE Subject='' COMPUTE AVG(Score) BY (Subject)

1. 排序函数

1. ROW_NUMBER()函数

特点：函数排序特点是没有并列编号 ，不跳空不编号。

SELECT ROW_NUMBER() OVER (ORDER BY a.Score DESC) AS 排名,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

1. RANK()函数(RANK函数生成的排序根据排序字句给出递增序号，但是存在并且跳空。)

特点：有并列序号，有跳空编号。

SELECT RANK() OVER (ORDER BY a.Score DESC) AS 排名,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

1. DENSE_RANK函数

特点：有并列编号，没有跳空编号

SELECT DENSE_RANK() OVER (ORDER BY a.Score DESC) AS 排名,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

1. PARTITION BY函数

SELECT DENSE_RANK() OVER (PARTITION BY a.Score DESC) AS 排名,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

1. 公用表达式

WITH stu(StuID,StuName)

AS(

SELECT a.StuID,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

)

SELECT * FROM stu

1. 索引

1. 聚集索引：聚集索引是指表中数据行的物理存储顺序与索引顺序完全相同。
2. 非聚集索引：非聚集索引不改变表中数据行物理存储位置，数据与索引分开存储，通过索引指向的地址与表中的数据发生关系。
3. 其他索引：

1. 唯一索引：如果希望索引键都不相同，可以创建唯一索引。聚集索引和非聚集索引都可以是唯一索引。
2. 包含性列索引：在 Microsoft SQL Server 2008 系统中，索引列的最大数量是16个,索引列字节总数的最高值是 900。如果当多个列的字节总数大于 900,且又想将这些列都包含在索引中时，可以使用包含性列索引。
3. 视图索引：如果希望提高视图的查询效率，可以将视图的索引物理化，也就是说将结果集永久储存在索引中，可以创建视图索引。
4. XML 索引：是与 XML 数据关联的索引形式，是 XML 二进制 BLOB 的已拆分持久表示形式。
5. 全文索引：一种特殊类型的基于标记的功能性索引，由 SQL Server 全文引擎(MSFTESQL)服务创建和维护，用于帮助在字符串中搜索复杂的词。

1. 常用索引

unique：创建唯一索引，可选项。

CLUSTERED：创建聚集索引，可选项

NONCLUSTERED：创建非聚集索

1. 视图：视图是一个虚拟的表。该数据表中的数据记录是由一条查询语句的查询结果得到的。

1. 视图创建准则

1. 视图名称必须遵循标识符的规则,该名称不得与该架构包含的任何表的名称相同。
2. 用户可以对其他视图创建视图。Microsoft SQL Server 2008 允许嵌套视图。但嵌套不得超过 32 层。根据视图的复杂性及可用内存，视图嵌套的实际限制可能低于该值。视图最多可包含 1024 个字段。
3. 不能将规则或 DEFAULT 定义与视图相关联。
4. 定义视图的查询不能包含 COMPUTE 子句、COMPUTE BY 子句或 INTO 关键字。
5. 定义视图的查询不能包含 ORDER BY 子句，除非在 SELECT 语句的选择列表中还有一个 TOP 子句。

1. 创建视图

USE Home

GO 

IF EXISTS(SELECT * FROM sys.system_views WHERE name='stu1')

BEGIN

--删除视图

DROP VIEW stu1

END

--创建视图

CREATE VIEW stu1

AS

SELECT a.StuID,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

GO 

--查看视图

SELECT * FROM stu1

 

 

 

1. 加密视图

--查看所有视图信息

SELECT * FROM INFORMATION_SCHEMA.VIEWS

--创建加密视图

CREATE VIEW stu2

WITH encryption 

AS

SELECT a.StuID,b.StuName FROM dbo.StuMarks a

INNER JOIN dbo.Student b ON b.StuID = a.StuID

GO 

--查看视图

SELECT * FROM stu2