SQL

命令

一、数据库操作

1. 创建数据库

基础创建语法

1
CREATE DATABASE [DatabaseName];

此简单形式会使用 ​​model 数据库​​作为模板创建新数据库，所有属性（包括文件大小、增长设置等）都继承自 model 数据库的配置

详细创建语法（生产环境推荐使用）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE DATABASE SM
ON PRIMARY -- 指定主文件组
(
    NAME = SM, -- 逻辑文件名，在数据库中唯一
    FILENAME = '/var/opt/mssql/data/SM.mdf', -- 物理文件路径
    SIZE = 10MB, -- 初始大小，最小512KB
    MAXSIZE = 50MB, -- 最大容量，可使用UNLIMITED
    FILEGROWTH = 2MB -- 增长增量，可使用百分比(如10%)
)
LOG ON -- 指定事务日志文件
(
    NAME = SM_log,
    FILENAME = '/var/opt/mssql/data/SM_log.ldf',
    SIZE = 5MB, -- 不指定则默认为数据文件总大小的25%
    MAXSIZE = 30MB,
    FILEGROWTH = 1MB
);

高级创建选项

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 创建包含多个数据文件和文件组的数据库
CREATE DATABASE Inventory
ON PRIMARY 
(
    NAME = Inventory_Primary,
    FILENAME = 'D:\Data\Inventory_Prm.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB
),
FILEGROUP FG_Archive -- 用户定义文件组
(
    NAME = Inventory_Archive,
    FILENAME = 'D:\Data\Inventory_Arc.ndf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB
)
LOG ON
(
    NAME = Inventory_Log1,
    FILENAME = 'D:\Data\Inventory_Log1.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB
);

-- 指定排序规则创建数据库
CREATE DATABASE InternationalDB 
COLLATE Latin1_General_CI_AS; -- 指定排序规则

安全检查后创建

1
2
3
4
5
6
7
8
9
10
-- 先检查数据库是否存在
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'SM')
BEGIN
    CREATE DATABASE SM;
    PRINT '数据库 SM 创建成功';
END
ELSE
BEGIN
    PRINT '数据库 SM 已存在';
END

2. 查看数据库信息

基本查看命令

1
2
3
4
5
6
7
8
9
10
11
### 3. 删除数据库
```sql
-- 安全删除（如果存在）
DROP DATABASE IF EXISTS [DatabaseName];

-- 强制删除（断开所有连接）
USE master;
GO
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE [DatabaseName];

二、表操作

1. 创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 基本创建
CREATE TABLE [TableName] (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

-- 示例：创建学生表
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    EnrollmentDate DATE
);

2. 修改表

1
2
3
4
5
6
7
8
9
10
11
-- 添加列
ALTER TABLE [TableName]
ADD [ColumnName] [DataType];

-- 修改列
ALTER TABLE [TableName]
ALTER COLUMN [ColumnName] [NewDataType];

-- 删除列
ALTER TABLE [TableName]
DROP COLUMN [ColumnName];

3. 删除表

1
2
3
4
5
-- 删除表
DROP TABLE [TableName];

-- 安全删除
DROP TABLE IF EXISTS [TableName];

三、数据操作

1. 插入数据

1
2
3
4
5
6
7
8
9
-- 插入单行数据
INSERT INTO Students (StudentID, FirstName, LastName, Age, EnrollmentDate)
VALUES (1, 'John', 'Doe', 20, '2023-09-01');

-- 插入多行数据
INSERT INTO Students (StudentID, FirstName, LastName, Age, EnrollmentDate)
VALUES 
(2, 'Jane', 'Smith', 22, '2023-09-01'),
(3, 'Tom', 'Brown', 21, '2023-09-01');

2. 查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询所有数据
SELECT * FROM Students;

-- 查询特定列
SELECT FirstName, LastName FROM Students;

-- 带条件的查询
SELECT * FROM Students WHERE Age > 21;

-- 排序查询
SELECT * FROM Students ORDER BY EnrollmentDate DESC;

-- 使用聚合函数
SELECT COUNT(*) AS TotalStudents FROM Students;

3. 更新数据

1
2
3
4
5
6
7
8
9
-- 更新数据
UPDATE Students
SET Age = 21
WHERE StudentID = 1;

-- 更新多个列
UPDATE Students
SET FirstName = 'Johnathan', LastName = 'Doe'
WHERE StudentID = 1;

4. 删除数据

1
2
3
4
5
-- 删除特定行
DELETE FROM Students WHERE StudentID = 1;

-- 删除所有数据
DELETE FROM Students; -- 注意：这将删除表中的所有数据，但保留表结构

四、索引操作

1. 创建索引

1
2
3
4
5
-- 创建单列索引
CREATE INDEX IDX_LastName ON Students(LastName);

-- 创建多列索引
CREATE INDEX IDX_Name_Age ON Students(FirstName, Age);

2. 删除索引

1
2
-- 删除索引
DROP INDEX IDX_LastName ON Students;

五、视图操作

1. 创建视图

1
2
3
4
5
-- 创建视图
CREATE VIEW StudentView AS
SELECT FirstName, LastName, Age
FROM Students
WHERE Age > 20;

2. 查询视图

1
2
-- 查询视图
SELECT * FROM StudentView;

3. 删除视图

1
2
-- 删除视图
DROP VIEW StudentView;

六、存储过程

1. 创建存储过程

1
2
3
4
5
6
CREATE PROCEDURE GetStudentById
    @StudentID INT
AS
BEGIN
    SELECT * FROM Students WHERE StudentID = @StudentID;
END;

2. 执行存储过程

1
EXEC GetStudentById @StudentID = 1;

3. 删除存储过程

1
DROP PROCEDURE IF EXISTS GetStudentById;

七、事务处理

1. 开始事务

1
2
3
4
5
6
7
8
9
10
11
BEGIN TRANSACTION;

-- 执行多个操作
INSERT INTO Students (StudentID, FirstName, LastName, Age, EnrollmentDate)
VALUES (4, 'Alice', 'Johnson', 23, '2023-09-01');

-- 提交事务
COMMIT;

-- 如果出现错误，回滚事务
ROLLBACK;

八、权限管理

1. 创建用户

1
CREATE USER [Username] FOR LOGIN [LoginName];

2. 授予权限

1
GRANT SELECT, INSERT, UPDATE ON Students TO [Username];

3. 撤销权限

1
REVOKE INSERT ON Students FROM [Username];

4. 删除用户

1
DROP USER [Username];

九、备份与恢复

1. 备份数据库

1
2
3
BACKUP DATABASE [DatabaseName]
TO DISK = 'D:\Backups\DatabaseName.bak'
WITH FORMAT;

2. 恢复数据库

1
2
3
RESTORE DATABASE [DatabaseName]
FROM DISK = 'D:\Backups\DatabaseName.bak'
WITH REPLACE;