### 数据库操作基础知识点
#### 创建数据库
- **语法**:
```sql
CREATE DATABASE 数据库名;
```
- 示例: `CREATE DATABASE BookDB ON PRIMARY (name='BookDB', filename='D:\数据库文件\BookDB.mdf', size=6MB) LOG ON (name='Book_log', filename='D:\数据库文件\BookDB_log.ldf', size=2MB);`
- **解释**: 上述语句用于创建名为`BookDB`的数据库，并指定其主要数据文件的位置、名称和初始大小为6MB，日志文件的位置、名称及初始大小为2MB。
#### 删除数据库
- **语法**:
```sql
DROP DATABASE 数据库名;
```
- 示例: `DROP DATABASE BookDB;`
- **解释**: 上述语句用于删除名为`BookDB`的数据库。
#### 创建表
- **语法**:
```sql
CREATE TABLE 表名 (
字段名 数据类型 [约束条件] [默认值],
...
);
```
- 示例: `CREATE TABLE Students (ID int IDENTITY(1,1) PRIMARY KEY NOT NULL, Name nvarchar(50) NOT NULL DEFAULT('未知'));`
- **解释**:
- `IDENTITY(1,1)`: 设置该字段为自动增长，初始值为1，增量为1。
- `PRIMARY KEY`: 定义字段为主键。
- `NOT NULL`: 指定字段不能包含空值。
- `DEFAULT('未知')`: 为字段设置默认值。
#### 修改表结构
- **新增字段**:
```sql
ALTER TABLE 表名 ADD 字段名 数据类型;
```
- 示例: `ALTER TABLE Students ADD Age int;`
- **解释**: 向`Students`表中添加一个名为`Age`的整型字段。
- **删除字段**:
```sql
ALTER TABLE 表名 DROP COLUMN 字段名;
```
- 示例: `ALTER TABLE Students DROP COLUMN Age;`
- **解释**: 从`Students`表中删除名为`Age`的字段。
- **修改字段不可为空**:
```sql
ALTER TABLE 表名 ALTER COLUMN 字段名 数据类型 NOT NULL;
```
- 示例: `ALTER TABLE Students ALTER COLUMN Name nvarchar(50) NOT NULL;`
- **解释**: 修改`Students`表中的`Name`字段为不可为空。
- **重命名表**:
```sql
EXEC sp_rename '原表名', '新表名';
```
- 示例: `EXEC sp_rename 'Students', 'Pupils';`
- **解释**: 将表`Students`重命名为`Pupils`。
- **重命名列**:
```sql
EXEC sp_rename '表名.字段名', '表名.新字段名';
```
- 示例: `EXEC sp_rename 'Pupils.Age', 'Pupils.AgeYears';`
- **解释**: 将表`Pupils`中的字段`Age`重命名为`AgeYears`。
- **修改字段类型**:
```sql
ALTER TABLE 表名 ALTER COLUMN 字段名 新数据类型;
```
- 示例: `ALTER TABLE Pupils ALTER COLUMN AgeYears decimal(5,2);`
- **解释**: 修改`Pupils`表中的`AgeYears`字段的数据类型为decimal(5,2)。
#### 添加约束
- **添加主键**:
```sql
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
```
- 示例: `ALTER TABLE Pupils ADD CONSTRAINT PK_Pupils PRIMARY KEY (ID);`
- **解释**: 为`Pupils`表的`ID`字段添加名为`PK_Pupils`的主键约束。
- **添加唯一约束**:
```sql
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
```
- 示例: `ALTER TABLE Pupils ADD CONSTRAINT UQ_Pupils_Name UNIQUE (Name);`
- **解释**: 为`Pupils`表的`Name`字段添加名为`UQ_Pupils_Name`的唯一约束。
- **添加默认值**:
```sql
ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFAULT (默认值) FOR 字段名;
```
- 示例: `ALTER TABLE Pupils ADD CONSTRAINT DF_Pupils_Age DEFAULT (18) FOR AgeYears;`
- **解释**: 为`Pupils`表的`AgeYears`字段添加名为`DF_Pupils_Age`的默认值约束，设默认年龄为18岁。
- **添加检查约束**:
```sql
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK (内容);
```
- 示例: `ALTER TABLE Pupils ADD CONSTRAINT CK_Pupils_Age CHECK (AgeYears >= 18 AND AgeYears <= 100);`
- **解释**: 为`Pupils`表的`AgeYears`字段添加名为`CK_Pupils_Age`的检查约束，确保年龄在18到100之间。
- **添加外键约束**:
```sql
ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (外键字段名) REFERENCES 主表 (关联主键);
```
- 示例: `ALTER TABLE Enrollments ADD CONSTRAINT FK_Enrollments_Pupils FOREIGN KEY (PupilID) REFERENCES Pupils (ID);`
- **解释**: 为`Enrollments`表的`PupilID`字段添加名为`FK_Enrollments_Pupils`的外键约束，指向`Pupils`表的`ID`字段。
#### 删除约束
- **语法**:
```sql
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
```
- 示例: `ALTER TABLE Pupils DROP CONSTRAINT PK_Pupils;`
- **解释**: 从`Pupils`表中删除名为`PK_Pupils`的主键约束。
#### 数据处理
- **插入记录**:
```sql
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);
```
- 示例: `INSERT INTO Pupils (Name, AgeYears) VALUES ('张三', 20);`
- **解释**: 向`Pupils`表中插入一条记录，其中`Name`字段的值为“张三”，`AgeYears`字段的值为20。
- **更新记录**:
```sql
UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2 WHERE 条件表达式;
```
- 示例: `UPDATE Pupils SET Name = '李四', AgeYears = 21 WHERE ID = 1;`
- **解释**: 更新`Pupils`表中ID为1的记录，将`Name`字段设为“李四”，`AgeYears`字段设为21。
- **删除记录**:
```sql
DELETE FROM 表名 WHERE 条件表达式;
TRUNCATE TABLE 表名;
```
- 示例: `DELETE FROM Pupils WHERE ID = 1;`
- **解释**: 从`Pupils`表中删除ID为1的记录。
- 示例: `TRUNCATE TABLE Pupils;`
- **解释**: 清空`Pupils`表中的所有记录。
#### 查询记录
- **基本查询**:
```sql
SELECT 字段列表 / 函数 / * FROM 表名 [WHERE <条件表达式>] [GROUP BY 分组字段名 HAVING <分组后筛选条件>] [ORDER BY 排序字段名 DESC/ASC];
```
- 示例: `SELECT * FROM Pupils WHERE AgeYears > 20 ORDER BY Name ASC;`
- **解释**: 查询`Pupils`表中年龄大于20岁的所有记录，并按姓名升序排列。
- **去重查询**:
```sql
SELECT DISTINCT 字段名 FROM 表名;
```
- 示例: `SELECT DISTINCT Name FROM Pupils;`
- **解释**: 返回`Pupils`表中`Name`字段的所有不同值。
- **限制查询结果**:
```sql
SELECT TOP n * FROM 表名;
```
- 示例: `SELECT TOP 5 * FROM Pupils;`
- **解释**: 返回`Pupils`表中的前5条记录。
- **模糊查询**:
```sql
SELECT * FROM 表名 WHERE 字段 LIKE '模式';
```
- 示例: `SELECT * FROM Pupils WHERE Name LIKE '张%';`
- **解释**: 查询`Pupils`表中姓名以“张”开头的所有记录。
- **聚合函数**:
- **求和**: `SUM()`
- **最大值**: `MAX()`
- **最小值**: `MIN()`
- **平均值**: `AVG()`
- **计数**: `COUNT()`
- 示例: `SELECT COUNT(*) AS TotalCount, SUM(AgeYears) AS TotalAge FROM Pupils;`
- **解释**: 查询`Pupils`表中的记录总数和年龄总和。
- **连接查询**:
- **内连接**:
```sql
SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.关系字段 = 别名2.关系字段;
```
- 示例: `SELECT * FROM Pupils p INNER JOIN Enrollments e ON p.ID = e.PupilID;`
- **解释**: 查询`Pupils`表和`Enrollments`表中通过`ID`字段连接的所有记录。
- **简单多表查询**:
```sql
SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.关系字段 = 别名2.关系字段;
```
- 示例: `SELECT * FROM Pupils p, Enrollments e WHERE p.ID = e.PupilID;`
- **解释**: 同上。
- **自连接**:
```sql
SELECT * FROM 表1 别名1, 表1 别名2 WHERE 别名1.关系字段 = 别名2.关系字段;
```
- 示例: `SELECT * FROM Pupils p1, Pupils p2 WHERE p1.ID = p2.ID + 1;`
- **解释**: 查询`Pupils`表中ID相差1的记录对。
#### 子查询
- **单行子查询**:
```sql
SELECT * FROM 表名 WHERE 字段 = (子查询);
```
- 示例: `SELECT * FROM Pupils WHERE AgeYears = (SELECT MAX(AgeYears) FROM Pupils);`
- **解释**: 查询`Pupils`表中最年长学生的记录。
- **多行子查询**:
```sql
SELECT * FROM 表名 WHERE 字段 IN (子查询);
```
- 示例: `SELECT * FROM Pupils WHERE AgeYears IN (SELECT AgeYears FROM Pupils WHERE Name LIKE '张%');`
- **解释**: 查询`Pupils`表中年龄与所有姓“张”的学生相同的记录。
- **比较子查询**:
- **与所有值比较**:
```sql
SELECT * FROM 表名 WHERE 字段 > ALL (子查询);
SELECT * FROM 表名 WHERE 字段 < ALL (子查询);
```
- 示例: `SELECT * FROM Pupils WHERE AgeYears > ALL (SELECT AgeYears FROM Pupils WHERE Name LIKE '张%');`
- **解释**: 查询`Pupils`表中年龄大于所有姓“张”的学生年龄的记录。
- **与任一值比较**:
```sql
SELECT * FROM 表名 WHERE 字段 > ANY (子查询);
SELECT * FROM 表名 WHERE 字段 < ANY (子查询);
```
- 示例: `SELECT * FROM Pupils WHERE AgeYears > ANY (SELECT AgeYears FROM Pupils WHERE Name LIKE '张%');`
- **解释**: 查询`Pupils`表中年龄大于任一姓“张”的学生年龄的记录。
#### 其他常用功能
- **字符串拼接**:
```sql
CONCAT(字符串1, 字符串2, ...);
```
- 示例: `SELECT CONCAT(Name, '同学') AS FullTitle FROM Pupils;`
- **解释**: 返回`Pupils`表中每个学生姓名后面加上“同学”作为完整标题。
- **局部变量**:
- **声明**:
```sql
DECLARE @变量名 数据类型;
```
- 示例: `DECLARE @MaxAge int;`
- **解释**: 声明一个名为`MaxAge`的整型局部变量。
- **赋值**:
```sql
SET @变量名 = 值;
SELECT @变量名 = 值;
```
- 示例: `SET @MaxAge = (SELECT MAX(AgeYears) FROM Pupils);`
- **解释**: 将`Pupils`表中年龄的最大值赋给`MaxAge`变量。
- **输出**:
```sql
PRINT(内容);
PRINT '内容';
```
- 示例: `PRINT '最年长学生的年龄是: ' + CAST(@MaxAge AS nvarchar(50));`
- **解释**: 输出最年长学生的年龄。
- **数据类型转换**:
```sql
CONVERT(转换后的数据类型, 转换前的变量);
```
- 示例: `SELECT CONVERT(nvarchar(50), AgeYears) AS AgeString FROM Pupils;`
- **解释**: 将`Pupils`表中`AgeYears`字段的值转换为nvarchar(50)类型。
以上是对给定文档中提到的关键知识点进行详细解释的例子。这些操作涵盖了数据库管理的基本方面，包括创建、删除、修改数据库和表结构、添加约束、数据插入、更新和删除，以及查询数据等常见任务。掌握这些SQL命令对于数据库管理员和开发人员来说至关重要。
