【数据库复习】第三章 SCHEMATABLE的增删改

关系数据库标准语言SQL

SQL概述，挑点课本上有用的东西讲

SQL能够实现数据库生命周期中的全部活动，比如：

定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。
对数据库中的数据进行查询和更新。
数据库重构和维护。
数据库安全性、完整性控制，以及事务控制
嵌入式 SOL 和动态 SQL 定义。
SQL的动词

SQL功能	动词
数据查询	SELECT
数据定义	CREATE, DROP, ALTER
数据操纵	INSERT, UPDATE, DELETE
数据控制	GRANT, REVOKE

在 SQL 数据库系统中，外模式、模式和内模式的部件如下：

外模式（External Schema）：
- 视图（Views）
- 用户定义的报表
模式（Schema）：
- 表（Tables）
- 索引（Indexes）
- 约束（Constraints）
- 触发器（Triggers）
- 存储过程和函数（Stored Procedures and Functions）
内模式（Internal Schema）：
- 存储结构（Storage Structures）
- 页（Pages）和区（Extents）
- 文件组织（File Organization）
- 缓冲区（Buffer）和高速缓存（Cache）.
  图示

┌──────────────────────────────┐
│          外模式              │
│  ┌─────────────┐  ┌───────┐ │
│  │    视图     │  │ 报表   │ │
│  └─────────────┘  └───────┘ │
└──────────────────────────────┘▲│
┌──────────────────────────────┐
│          模式                │
│  ┌─────┐  ┌──────┐  ┌─────┐  │
│  │ 表  │  │ 索引 │  │ 约束 │  │
│  └─────┘  └──────┘  └─────┘  │
│  ┌───────────┐  ┌──────────┐ │
│  │ 触发器     │  │ 存储过程 │ │
│  │           │  │ 和函数   │  │
│  └───────────┘  └──────────┘ │
└──────────────────────────────┘▲│
┌──────────────────────────────┐
│          内模式              │
│  ┌────────────┐  ┌─────────┐ │
│  │ 存储结构    │  │ 页和区  │ │
│  └────────────┘  └─────────┘ │
│  ┌────────────┐  ┌─────────┐ │
│  │ 文件组织    │  │ 缓冲区  │ │
│  │            │  │ 和缓存  │ │
│  └────────────┘  └─────────┘ │
└──────────────────────────────┘

数据定义的SQL语句

关于SCHEMA的操作

`CREATE SCHEMA` 和 `DROP SCHEMA` 相关操作

表格总结

操作	语法	描述
创建 schema	`CREATE SCHEMA schema_name;`	创建一个新的 schema
创建带授权的 schema	`CREATE SCHEMA schema_name AUTHORIZATION user_name;`	创建一个新的 schema 并授权给指定用户
创建包含对象的 schema	`CREATE SCHEMA schema_name [AUTHORIZATION user_name] [CREATE TABLE ...] [CREATE VIEW ...] ...;`	创建一个新的 schema，并同时创建表、视图等对象
删除 schema	`DROP SCHEMA schema_name;`	删除一个现有的 schema
使用 IF EXISTS 删除 schema	`DROP SCHEMA IF EXISTS schema_name;`	如果 schema 存在则删除它，避免因 schema 不存在导致的错误
删除 schema 及其所有对象	`DROP SCHEMA schema_name CASCADE;`	删除 schema 及其所有包含的对象

实例演示

创建一个简单的 schema
```
CREATE SCHEMA my_schema;
```

创建一个带有授权的 schema

CREATE SCHEMA my_schema AUTHORIZATION user1;

创建包含表和视图的 schema

CREATE SCHEMA my_schemaCREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50))CREATE VIEW employee_view ASSELECT first_name, last_name FROM employees;

删除一个 schema
```
DROP SCHEMA my_schema;
```
使用 IF EXISTS 删除 schema
```
DROP SCHEMA IF EXISTS my_schema;
```
删除 schema 及其所有对象
```
DROP SCHEMA my_schema CASCADE;
```

基本表的定义、删除与修改

第一部分：表格总结表的所有涉及到增删改查的语法

操作	语法	描述
定义基本表	`CREATE TABLE table_name (column1 datatype [constraint], column2 datatype [constraint], ..., columnN datatype [constraint]);`	创建一个新表
删除表	`DROP TABLE [IF EXISTS] table_name;`	删除一个表，如果存在可选用 `IF EXISTS` 避免错误
添加列	`ALTER TABLE table_name ADD column_name datatype [constraint];`	向表中添加新列
删除列	`ALTER TABLE table_name DROP COLUMN column_name;`	从表中删除列
修改列	`ALTER TABLE table_name MODIFY COLUMN column_name datatype [constraint];`	修改表中的列的类型或约束
添加约束	`ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);`	向表中添加新的约束
删除约束	`ALTER TABLE table_name DROP CONSTRAINT constraint_name;`	从表中删除约束

第二部分：实例展示

创建表

CREATE TABLE students (student_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),enrollment_date DATE,email VARCHAR(100) UNIQUE
);

向表中添加新列

ALTER TABLE students
ADD phone_number VARCHAR(20);

修改列数据类型

ALTER TABLE students
MODIFY COLUMN email VARCHAR(150);

删除表中的列

ALTER TABLE students
DROP COLUMN phone_number;

添加唯一约束

ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);

删除唯一约束

ALTER TABLE students
DROP CONSTRAINT unique_email;

删除表
```
DROP TABLE students;
```
使用 IF EXISTS 删除表
```
DROP TABLE IF EXISTS students;
```

SQL 数据类型及其应用实例

数据类型	描述	示例
数值类型
INT/INTEGER	存储整数	`123`, `-456`
DECIMAL/NUMERIC	存储定点数	`123.45`, `-678.90`
FLOAT/REAL	存储浮点数	`3.14`, `-0.001`
字符类型
CHAR(n)	固定长度字符	`'A'`, `'Hello '`
VARCHAR(n)	可变长度字符	`'Hello'`, `'OpenAI'`
TEXT	大型文本	`'This is a long text.'`
日期和时间类型
DATE	日期（年-月-日）	`2024-06-03`
TIME	时间（时:分:秒）	`14:30:00`
DATETIME	日期和时间	`2024-06-03 14:30:00`
TIMESTAMP	时间戳（日期和时间）	`2024-06-03 14:30:00`
布尔类型
BOOLEAN	布尔值（TRUE 或 FALSE）	`TRUE`, `FALSE`
二进制类型
BINARY(n)	固定长度二进制数据	`0x00FF`, `0xA1B2`
VARBINARY(n)	可变长度二进制数据	`0x00FF`, `0xA1B2C3D4`
JSON 类型
JSON	JSON 格式的数据	`{"name": "John", "age": 30}`

TIMESTAMP 和 DATE 的格式区别
- DATE:
  - 只包含日期部分。
  - 格式为：YYYY-MM-DD。
  - 示例：2024-06-03。
- TIMESTAMP:
  - 包含日期和时间部分。
  - 格式为：YYYY-MM-DD HH:MM:SS。
  - 示例：2024-06-03 14:30:00。

综合示例

以下是一个包含多种数据类型的综合表定义示例：

CREATE TABLE comprehensive_example (id INT PRIMARY KEY,username VARCHAR(50) NOT NULL,password CHAR(64) NOT NULL,email VARCHAR(100),balance DECIMAL(10, 2),birth_date DATE,signup_time TIMESTAMP,is_active BOOLEAN,profile_picture VARBINARY(2048),settings JSON
);

在这个示例中：

id 是整数类型，用作主键。
username 是可变长度字符类型，存储用户名。
password 是固定长度字符类型，存储哈希后的密码。
email 是可变长度字符类型，存储电子邮件地址。
balance 是定点数类型，存储用户余额。
birth_date 是日期类型，存储用户的出生日期。
signup_time 是时间戳类型，存储用户注册的时间。
is_active 是布尔类型，表示用户是否活跃。
profile_picture 是可变长度二进制类型，存储用户的头像图片。
settings 是 JSON 类型，存储用户的设置数据。

`SHOW` 语句的应用

SQL 语句中 `SHOW` 的用途

SHOW 语句在 SQL 中用于显示数据库的不同信息和属性。以下是一些常用的 SHOW 语句及其用途：

`SHOW` 语句	用途
`SHOW DATABASES;`	显示当前服务器上的所有数据库。
`SHOW TABLES;`	显示当前数据库中的所有表。
`SHOW COLUMNS FROM table_name;`	显示指定表的所有列及其属性。
`SHOW INDEX FROM table_name;`	显示指定表的所有索引。
`SHOW TABLE STATUS;`	显示当前数据库中所有表的状态信息。
`SHOW CREATE TABLE table_name;`	显示创建指定表的 `CREATE TABLE` 语句。
`SHOW CREATE DATABASE database_name;`	显示创建指定数据库的 `CREATE DATABASE` 语句。
`SHOW VARIABLES;`	显示系统变量及其当前值。
`SHOW STATUS;`	显示服务器状态信息。
`SHOW PROCESSLIST;`	显示当前正在执行的所有线程。
`SHOW GRANTS FOR user;`	显示指定用户的权限。
`SHOW ENGINE INNODB STATUS;`	显示 InnoDB 引擎的状态信息。
`SHOW PRIVILEGES;`	显示服务器支持的所有权限类型。

示例展示

显示所有数据库
```
SHOW DATABASES;
```
显示当前数据库中的所有表
```
SHOW TABLES;
```
显示指定表的列信息
```
SHOW COLUMNS FROM employees;
```
显示指定表的索引信息
```
SHOW INDEX FROM employees;
```
显示当前数据库中所有表的状态信息
```
SHOW TABLE STATUS;
```
显示创建指定表的 CREATE TABLE 语句
```
SHOW CREATE TABLE employees;
```
显示系统变量及其当前值
```
SHOW VARIABLES;
```
显示服务器状态信息
```
SHOW STATUS;
```
显示当前正在执行的所有线程
```
SHOW PROCESSLIST;
```

详细说明

显示数据库和表的信息：通过 SHOW DATABASES; 和 SHOW TABLES; 可以快速了解当前服务器和数据库中的数据结构。
显示表的详细结构：SHOW COLUMNS FROM table_name; 和 SHOW CREATE TABLE table_name; 帮助用户理解表的列定义和创建表的 SQL 语句。
显示系统信息和状态：SHOW VARIABLES; 和 SHOW STATUS; 提供了关于服务器配置和运行状态的重要信息，有助于性能调优和故障排查。
显示权限信息：SHOW GRANTS FOR user; 用于查看用户的权限配置，确保安全性和正确的访问控制。

索引建立

索引的运用场景：当表的数据量比较大的时候，查询操作会比较耗时。建立索引是加快查询速度的有效手段。数据库索引类似于图书后面的索引，能够快速定位到需要查询的内容。
索引可以显著提高 SELECT 查询的速度，特别是在表中数据量较大时。

表格总结

操作	语法	描述
创建索引	`CREATE INDEX index_name ON table_name (column1, column2, ...);`	为表创建一个新的索引
创建唯一索引	`CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);`	为表创建一个新的唯一索引，确保索引列中的值是唯一的
删除索引	`DROP INDEX index_name;`	删除一个现有的索引
查看索引	`SHOW INDEX FROM table_name;`	显示表的索引信息
创建带有表达式的索引	`CREATE INDEX index_name ON table_name (expression);`	创建带有表达式的索引
创建复合索引	`CREATE INDEX index_name ON table_name (column1, column2, ...);`	为多个列创建一个复合索引
创建聚簇索引 (CLUSTER)	`CREATE CLUSTERED INDEX index_name ON table_name (column1, column2, ...);`	为表创建一个聚簇索引（聚簇索引的语法和支持情况取决于具体的数据库管理系统）
创建非聚簇索引 (NONCLUSTERED)	`CREATE NONCLUSTERED INDEX index_name ON table_name (column1, column2, ...);`	为表创建一个非聚簇索引（非聚簇索引的语法和支持情况取决于具体的数据库管理系统）

实例演示

完整的SQL脚本，演示索引。

-- 保险措施
DROP DATABASE IF EXISTS company;
-- 创建数据库
CREATE DATABASE company;-- 使用数据库
USE company;-- 创建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100) UNIQUE,hire_date DATE
);-- 插入数据
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '2020-01-15'),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '2019-07-23'),
(3, 'Emily', 'Jones', 'emily.jones@example.com', '2021-05-11'),
(4, 'Michael', 'Brown', 'michael.brown@example.com', '2018-03-30');-- 创建简单索引
CREATE INDEX idx_last_name ON employees (last_name);-- 创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON employees (email);-- 创建复合索引
CREATE INDEX idx_name_hire_date ON employees (last_name, hire_date);-- 创建带有表达式的索引
CREATE INDEX idx_upper_last_name ON employees (UPPER(last_name));-- 查看索引
SHOW INDEX FROM employees;-- 删除简单索引
DROP INDEX idx_last_name ON employees;-- 删除唯一索引
DROP INDEX idx_unique_email ON employees;-- 删除复合索引
DROP INDEX idx_name_hire_date ON employees;-- 删除带有表达式的索引
DROP INDEX idx_upper_last_name ON employees;

完整的SQL脚本，演示复合索引：

-- 保险措施
DROP DATABASE IF EXISTS company;
-- 创建数据库
CREATE DATABASE company;-- 使用数据库
USE company;-- 创建表
CREATE TABLE employees (employee_id INT PRIMARY KEY,first_name VARCHAR(50),last_name VARCHAR(50),email VARCHAR(100) UNIQUE,hire_date DATE,department_id INT
);-- 插入数据
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, department_id) VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '2020-01-15', 101),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '2019-07-23', 102),
(3, 'Emily', 'Jones', 'emily.jones@example.com', '2021-05-11', 103),
(4, 'Michael', 'Brown', 'michael.brown@example.com', '2018-03-30', 101),
(5, 'Linda', 'White', 'linda.white@example.com', '2022-01-01', 104);-- 创建复合索引
CREATE INDEX idx_last_name_department_id ON employees (last_name, department_id);-- 使用复合索引查询
SELECT * FROM employees WHERE last_name = 'Doe' AND department_id = 101;-- 查看索引
SHOW INDEX FROM employees;-- 删除复合索引
DROP INDEX idx_last_name_department_id ON employees;

通过这个实例，你可以了解如何在一个数据库中创建复合索引，如何使用复合索引来优化查询，以及如何查看和删除索引。

【数据库复习】第三章 SCHEMATABLE的增删改

关系数据库标准语言SQL

SQL概述，挑点课本上有用的东西讲

数据定义的SQL语句

关于SCHEMA的操作

`CREATE SCHEMA` 和 `DROP SCHEMA` 相关操作

表格总结

实例演示

基本表的定义、删除与修改

第一部分：表格总结表的所有涉及到增删改查的语法

第二部分：实例展示

SQL 数据类型及其应用实例

综合示例

`SHOW` 语句的应用

SQL 语句中 `SHOW` 的用途

示例展示

详细说明

索引建立

表格总结

实例演示

最新新闻

热搜词

【数据库复习】第三章 SCHEMATABLE的增删改

关系数据库标准语言SQL

SQL概述，挑点课本上有用的东西讲

数据定义的SQL语句

关于SCHEMA的操作

CREATE SCHEMA 和 DROP SCHEMA 相关操作

表格总结

实例演示

基本表的定义、删除与修改

第一部分：表格总结表的所有涉及到增删改查的语法

第二部分：实例展示

SQL 数据类型及其应用实例

综合示例

SHOW 语句的应用

SQL 语句中 SHOW 的用途

示例展示

详细说明

索引建立

表格总结

实例演示

最新新闻

热搜词

`CREATE SCHEMA` 和 `DROP SCHEMA` 相关操作

`SHOW` 语句的应用

SQL 语句中 `SHOW` 的用途