关系数据库标准语言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;
通过这个实例，你可以了解如何在一个数据库中创建复合索引，如何使用复合索引来优化查询，以及如何查看和删除索引。