三、SQL

一、SQL概述

1、特点

信息

1. 一体化
2. 高度非过程化
3. 语言简洁
4. 多种使用方式

2、功能概述

SQL 功能	对应命令
数据定义	CRÉATE、DROP ALTER
数据查询	SELECT
数据操纵	INSERT、UPDATE、DELETE
数据控制	GRANT、REVOKE、DENY

二、数据库

提示

1. 主数据文件用于存储数据库的系统表,数据库对象启动信息和数据库数据。所有数据库只能有一个主数据文件,其文件扩展名为mdf。
2. 次数据文件用于存储主数据文件中未存储的数据和数据对象。一个数据可有一个或多个次数据文件,其文件扩展名为 ndf。
3. 事务日志文件用于记录对数据库的操作情况。对数据库执行的插入、删除、更新等操作都会记录在文件中。必要时可以根据日志文件恢复数据库。每个数据库至少有一个事务日志文件,其文件扩展名为 ldf。

1、定义

提示

1. 如果在新建数据库时，没有指定日志文件逻辑名，则数据文件逻辑名称后加-log作为日志文件的逻辑名和物理名；
2. 方括号[]中的内容表示可选，如果不选，则系统会使默认值建立数据库

语法

CREATE DATABASE 数据库名称
[ ON 
    [ PRIMARY ]
    (
        NAME = 逻辑文件名,               -- 数据文件的逻辑名称
        FILENAME = '物理文件路径',         -- 数据文件的物理存储路径
        SIZE = 初始大小,  -- KB, MB, GB, TB 等
        MAXSIZE = 最大大小, -- KB, MB, GB, TB 等 (UNLIMITED 表示无限制)
        FILEGROWTH = 增长大小, -- 单位 KB, MB, GB, TB 等
    )
    [ ,...n ] 
]
[ LOG ON 
    (
        NAME = 日志文件逻辑名,             -- 日志文件的逻辑名称
        FILENAME = '物理文件路径',         -- 日志文件的物理存储路径
        SIZE = 初始大小 单位, -- KB, MB, GB, TB 等
        MAXSIZE = 最大大小 单位, -- KB, MB, GB, TB 等
        FILEGROWTH = 增长大小 单位, -- KB, MB, GB, TB 等
    )
    [ ,...n ]
]
[ COLLATE 排序规则 ]  -- 数据库的排序规则
[ FOR ATTACH ]       -- 附加现有数据库
[ ENCRYPTION ON ]    -- 启用加密 (SQL Server 2016 及更高版本)

2、修改/新增 数据库文件

（1）修改大小

ALTER DATABASE 数据库名称
MODIFY FILE
(
    NAME = 逻辑文件名,
    SIZE = 新的大小 单位    -- 可以是 KB, MB, GB, TB 等
);

示例：增加数据文件大小

ALTER DATABASE MyDatabase
MODIFY FILE
(
    NAME = MyDatabase_Data,
    SIZE = 5GB;            -- 将数据文件的大小调整为 5GB
);

（2）修改自动增长

ALTER DATABASE 数据库名称
MODIFY FILE
(
    NAME = 逻辑文件名,
    FILEGROWTH = 增长大小 单位    -- 增长步长，单位可以是 KB, MB, GB, TB 等
);

示例：修改数据文件增长步长

ALTER DATABASE MyDatabase
MODIFY FILE
(
    NAME = MyDatabase_Data,
    FILEGROWTH = 256MB;    -- 每次文件增长 256MB
);

（3）修改最大文件大小

ALTER DATABASE 数据库名称
MODIFY FILE
(
    NAME = 逻辑文件名,
    MAXSIZE = 最大大小 单位   -- 可以是 KB, MB, GB, TB，或设置为 UNLIMITED
);

示例：限制数据文件最大大小

ALTER DATABASE MyDatabase
MODIFY FILE
(
    NAME = MyDatabase_Data,
    MAXSIZE = 10GB;        -- 将数据文件的最大大小限制为 10GB
);

（4）添加新的文件或文件组

ALTER DATABASE 数据库名称
ADD FILE
(
    NAME = 新的逻辑文件名,
    FILENAME = '文件路径',
    SIZE = 初始大小 单位,
    MAXSIZE = 最大大小 单位,
    FILEGROWTH = 增长大小 单位
);

示例：添加新的数据文件

ALTER DATABASE MyDatabase
ADD FILE
(
    NAME = MyDatabase_Data2,
    FILENAME = 'C:\SQLServerData\MyDatabase_Data2.ndf',
    SIZE = 1GB,
    MAXSIZE = 5GB,
    FILEGROWTH = 512MB
);

3、修改数据库的状态

（1）只读

ALTER DATABASE 数据库名称 SET READ_ONLY;

（2）读写

ALTER DATABASE 数据库名称 SET READ_WRITE;

（3）离线

ALTER DATABASE 数据库名称 SET OFFLINE;

（4）在线

ALTER DATABASE 数据库名称 SET ONLINE;

4、更改数据库的排序规则

ALTER DATABASE 数据库名称 COLLATE 新的排序规则;

5、删除数据库文件

ALTER DATABASE 数据库名称 REMOVE FILE 逻辑文件名;

6、重命名数据库

ALTER DATABASE 原数据库名称 MODIFY NAME = 新数据库名称;

7、检查和修复数据库

DBCC CHECKDB (数据库名称);

8、修复数据库错误（例如修复索引等）

DBCC CHECKDB (数据库名称, REPAIR_ALLOW_DATA_LOSS);

示例：修复数据库中的错误

DBCC CHECKDB (MyDatabase, REPAIR_ALLOW_DATA_LOSS);

9、删除数据库

DROP DATABASE 数据库名称;

三、表

1、定义

语法

CREATE TABLE 表名 (
    列名1 数据类型 [列级别约束],
    列名2 数据类型 [列级别约束],
    ...
    [表级别约束]
);

2、数据类型

数据类型	说明	字节大小	取值范围/长度
CHAR(n)	固定长度字符串，n 表示长度	n 字节	最多 n 字符
NCHAR(n)	固定长度 Unicode 字符串，n 表示长度	2n 字节	最多 n 字符
VARCHAR(n)	可变长度字符串，n 表示最大长度	实际长度（最多 n 字节）	最多 n 字符
NVARCHAR(n)	可变长度 Unicode 字符串，n 表示最大长度	实际长度（最多 2n 字节）	最多 2^31-1 字符
TEXT	可变长度字符串	2^31-1 字节	最多 2^31-1 字符
DATE	日期类型（年-月-日）	3 字节	0001-01-01 到 9999-12-31
TIME(n)	时间类型（时:分:秒.小数秒），n 为小数秒精度	3-5 字节	00:00:00.0000000 到 23:59:59.9999999
DATETIME	日期和时间，精确到 3.33 毫秒	8 字节	1753-01-01 到 9999-12-31
SMALLDATETIME	日期和时间，精确到分钟	4 字节	1900-01-01 到 2079-06-06
INT	整数类型	4 字节	-2^31 到 2^31-1
SMALLINT	小范围整数类型	2 字节	-2^15 到 2^15-1
TINYINT	非常小的整数类型	1 字节	0 到 255
BIGINT	大范围整数类型	8 字节	-2^63 到 2^63-1
FLOAT(n)	浮点数，n 为精度（1-53）	4 字节/8 字节	精度 7-15 位小数
DECIMAL(p, q)	精确数值，p 为总位数，q 为小数位数	最多 17 字节	-10^38+1 到 10^38-1
NUMERIC(p, q)	精确数值，p 为总位数，q 为小数位数	最多 17 字节	与 DECIMAL 相同
MONEY	大范围货币类型	8 字节	-922337203685477.5808 到 922337203685477.5807
SMALLMONEY	小范围货币类型	4 字节	-214748.3648 到 214748.3647

3、表维护

ALTER TABLE < table name >
	[ADD <column_name> < data type> [constaint]]				/*增加列*/
	[DROP COLUMN < column name >]						   /*删除列*/
	[ALTER COLUMN < column name > < data type > [constaint]]	 /*修改列*/

​

四、完整性

1、定义

alter table <table name> 
	[add [<constraint> <constraint_name>] <constraint>]   /* 增加约束 */

常见约束类型：

• 主键约束（Primary Key）：

  add constraint pk_<column_name> primary key (<column_name>);

• 外键约束（Foreign Key）：

  add constraint fk_<column_name> foreign key (<column_name>) 
  	references <referenced_table> (<referenced_column>);

• 唯一性约束（Unique）：

  add constraint uq_<column_name> unique (<column_name>);

• 检查约束（Check）：

  add constraint ck_<column_name> check (<condition>);

2、维护

alter table <table_name> 
	[drop <constraint_name>]   		/* 删除约束 */

五、索引

1、定义

定义

索引有聚集索引、非聚集索引、唯一索引、唯一索引

六、增删改查

参考MySQL语法