您的位置：首页 > 汽车 > 新车 > 【数据库复习】第三章 SCHEMATABLE的增删改

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

2024/7/8 0:52:48 来源：https://blog.csdn.net/AClerk/article/details/139424491  浏览:  次   关键词：【数据库复习】第三章 SCHEMATABLE的增删改

关系数据库标准语言SQL

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

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

  • 定义和修改、删除关系模式，定义和删除视图，插入数据，建立数据库。
  • 对数据库中的数据进行查询和更新。
  • 数据库重构和维护。
  • 数据库安全性、完整性控制，以及事务控制
  • 嵌入式 SOL 和动态 SQL 定义。
    SQL的动词
SQL功能动词
数据查询SELECT
数据定义CREATE, DROP, ALTER
数据操纵INSERT, UPDATE, DELETE
数据控制GRANT, REVOKE

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

  1. 外模式（External Schema）：

    • 视图（Views）
    • 用户定义的报表

  2. 模式（Schema）：

    • 表（Tables）
    • 索引（Indexes）
    • 约束（Constraints）
    • 触发器（Triggers）
    • 存储过程和函数（Stored Procedures and Functions）

  3. 内模式（Internal Schema）：

    • 存储结构（Storage Structures）
    • 页（Pages）和区（Extents）
    • 文件组织（File Organization）
    • 缓冲区（Buffer）和高速缓存（Cache）.
      图示
┌──────────────────────────────┐
│          外模式              │
│  ┌─────────────┐  ┌───────┐ │
│  │    视图     │  │ 报表   │ │
│  └─────────────┘  └───────┘ │
└──────────────────────────────┘▲│
┌──────────────────────────────┐
│          模式                │
│  ┌─────┐  ┌──────┐  ┌─────┐  │
│  │ 表  │  │ 索引 │  │ 约束 │  │
│  └─────┘  └──────┘  └─────┘  │
│  ┌───────────┐  ┌──────────┐ │
│  │ 触发器     │  │ 存储过程 │ │
│  │           │  │ 和函数   │  │
│  └───────────┘  └──────────┘ │
└──────────────────────────────┘▲│
┌──────────────────────────────┐
│          内模式              │
│  ┌────────────┐  ┌─────────┐ │
│  │ 存储结构    │  │ 页和区  │ │
│  └────────────┘  └─────────┘ │
│  ┌────────────┐  ┌─────────┐ │
│  │ 文件组织    │  │ 缓冲区  │ │
│  │            │  │ 和缓存  │ │
│  └────────────┘  └─────────┘ │
└──────────────────────────────┘

数据定义的SQL语句

关于SCHEMA的操作

CREATE SCHEMA 和 DROP SCHEMA 相关操作

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

  1. 创建一个简单的 schema

    CREATE SCHEMA my_schema;

  2. 创建一个带有授权的 schema

    CREATE SCHEMA my_schema AUTHORIZATION user1;

  3. 创建包含表和视图的 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;

  4. 删除一个 schema

    DROP SCHEMA my_schema;

  5. 使用 IF EXISTS 删除 schema

    DROP SCHEMA IF EXISTS my_schema;

  6. 删除 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;从表中删除约束

第二部分：实例展示

  1. 创建表

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

  2. 向表中添加新列

    ALTER TABLE students
ADD phone_number VARCHAR(20);

  3. 修改列数据类型

    ALTER TABLE students
MODIFY COLUMN email VARCHAR(150);

  4. 删除表中的列

    ALTER TABLE students
DROP COLUMN phone_number;

  5. 添加唯一约束

    ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);

  6. 删除唯一约束

    ALTER TABLE students
DROP CONSTRAINT unique_email;

  7. 删除表

    DROP TABLE students;

  8. 使用 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 类型
JSONJSON 格式的数据{"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;显示服务器支持的所有权限类型。

示例展示

  1. 显示所有数据库

    SHOW DATABASES;

  2. 显示当前数据库中的所有表

    SHOW TABLES;

  3. 显示指定表的列信息

    SHOW COLUMNS FROM employees;

  4. 显示指定表的索引信息

    SHOW INDEX FROM employees;

  5. 显示当前数据库中所有表的状态信息

    SHOW TABLE STATUS;

  6. 显示创建指定表的 CREATE TABLE 语句

    SHOW CREATE TABLE employees;

  7. 显示系统变量及其当前值

    SHOW VARIABLES;

  8. 显示服务器状态信息

    SHOW STATUS;

  9. 显示当前正在执行的所有线程

    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;

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

版权声明:

本网仅为发布的内容提供存储空间，不对发表、转载的内容提供任何形式的保证。凡本网注明“来源：XXX网络”的作品，均转载自其它媒体，著作权归作者所有，商业转载请联系作者获得授权，非商业转载请注明出处。

我们尊重并感谢每一位作者，均已注明文章来源和作者。如因作品内容、版权或其它问题，请及时与我们联系，联系邮箱：809451989@qq.com，投稿邮箱：809451989@qq.com

最新新闻

热搜词

命令行修改MySQL5.7数据库密码 灰色预测代码的讲解 最新扣子(Coze)实战案例：使用图像流做超分，模糊图片秒变清晰，完全免费教程 算法入门（4） 7.6 第一周进度总结 用友财务软件数据库恢复

声明：本站所有新闻及新闻图片来源于其他网站，如有侵权，请及时联系我们！

客户服务 | 关于我们 | 版权声明

版权所有：

Copyright 2024 尧图网 All Rights Reserved.QQ:809451989