SQL索引、AUTO INCREMENT、SQL约束(not null、unique、primary key、foreign key、check、defalut)

文章详细介绍了SQL中的索引类型如B树、哈希等,以及如何创建、删除和使用索引以优化查询性能。此外,还阐述了SQL约束的概念,包括非空约束、默认值、唯一性、主键和外键约束的创建、修改和撤销方法,强调了约束在保证数据完整性和一致性方面的重要性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

目录

SQL索引

创建索引 

删除索引

AUTO INCREMENT

MySQL

SQL Server 

MS Access

Oracle

SQL约束

Not null(非null)

创建表时添加约束

已有表添加约束

DEFAULT(默认值)

创建表时添加约束

已有表时添加约束

撤销约束

UNIQUE/CHECK/PRIMARY KEY /FOREIGN KEY约束

新建表添加约束(UNIQUE/PRIMARY KEY)

单列设置

多列设置(命名约束)

已有表添加约束(UNIQUE/PRIMARY KEY/CHECK)

撤销命名约束

UNIQUE/FOREIGN KEY/CHECK

UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK 

Unique(去重)

Check

创建表时添加约束

Primary KEY(主键)

撤销约束/命名约束

FOREIGN KEY(外键)

创建表时添加外键

已有表添加约束

命名 FOREIGN KEY 约束

撤销命名约束(撤销多列约束)


SQL索引

在数据库表的一列或多列上创建索引,并根据这些列的值进行排序。当执行查询时,数据库引擎可以使用索引来快速定位符合查询条件的数据行,而不必扫描整个表。这样可以大大减少查询所需的时间和资源消耗(用于加快对列筛选WHERE 语句的效率)。

常见的索引有B树索引、哈希索引、全文索引、空间索引。

CREATE INDEX idx_users_email ON users (email);

例如上面在users表中创建了名为idx_users_email的索引是关于email列的,此时下面对email的筛选效率会提升很多。过 

SELECT * FROM users WHERE email = 'example@example.com';

注意:一般选择经常被查询的列作为索引列索引的创建会增加存储空间和写操作的成本,需要权衡索引的数量和复杂度

创建索引 

普通索引允许使用列中有重复的值

CREATE INDEX PIndex
ON Persons (LastName, FirstName)

 唯一索引不允许列中有重复值

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

删除索引

MS Access

DROP INDEX index_name ON table_name

MS SQL Server

DROP INDEX table_name.index_name

DB2/Oracle

DROP INDEX index_name

MySQL

ALTER TABLE table_name DROP INDEX index_name

AUTO INCREMENT

在新记录插入表中时在设置列中生成一个递增的数字。

MySQL

id默认开始值为1,每次插入一条数据递增1。

CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
)

设置起始值为100

ALTER TABLE Persons AUTO_INCREMENT=100

SQL Server 

MS SQL Server 使用 IDENTITY 关键字来执行 AUTO_INCREMENT,下面表示id默认值为1,每次插入一条数据递增2。

CREATE TABLE Persons
(
ID int IDENTITY(1,2) PRIMARY KEY,
LastName varchar(255) NOT NULL,
)

MS Access

MS Access 使用 AUTOINCREMENT 关键字来执行 auto-increment  

CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
)

设置起始值1和递增值2。

CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT(1,2),
LastName varchar(255) NOT NULL,
)

Oracle

需要通过 sequence 对象(该对象生成数字序列)创建 auto-increment 字段。再通过nextval 函数在插入数据时调用获取递增的值。

下面创建一个名为 seq_person 的 sequence 对象,它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

 使用时借助nextval 函数(该函数从 seq_person 序列中取回下一个值)

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')

SQL约束

规定表中的数据规则,有not null、unique、primary key、foreign key、check、defalut约束。

Not null(非null)

不能命名约束直接指定多列,但可以通过check约束中is not null来实现撤销时通过alter修改列数据类型

创建表时添加约束

CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
Age int
);

已有表添加约束

ALTER TABLE Persons MODIFY Age int NOT NULL;

DEFAULT(默认值)

向列中插入默认值,如果没有规定其他的值,那么会将默认值添加到所有的新记录。

创建表时添加约束

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
)

已有表时添加约束

MySQL

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access

ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

Oracle

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

撤销约束

MySQL

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT

UNIQUE/CHECK/PRIMARY KEY /FOREIGN KEY约束

新建表添加约束(UNIQUE/PRIMARY KEY)

单列设置

MySQL

CREATE TABLE Persons
(
P_Id int NOT NULL,
UNIQUE/PRIMARY KEY (P_Id)
)

SQL Server / Oracle / MS Access

CREATE TABLE Persons
(P_Id int NOT NULL UNIQUE/PRIMARY KEY)

多列设置(命名约束)

只需删除该命名就去除了多列的约束设置。

MySQL/SQL Server / Oracle / MS Access

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT uc_PersonID UNIQUE/PRIMARY KEY (P_Id,LastName)
)

已有表添加约束(UNIQUE/PRIMARY KEY/CHECK)

ALTER TABLE Persons
ADD UNIQUE/PRIMARY KEY/CHECK (P_Id)

多列设置(多列都取不同命名约束(只需删除该命名就去除了多列的约束设置)

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE/PRIMARY KEY/CHECK (P_Id,LastName)

撤销命名约束

UNIQUE/FOREIGN KEY/CHECK

MySQL

ALTER TABLE Persons
DROP UNIQUE/FOREIGN KEY/CHECK chk_Person

UNIQUE/PRIMARY KEY/FOREIGN KEY/CHECK 

SQL Server / Oracle / MS Access

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

Unique(去重)

保证改字段下的值无重复内容(null也只能有一个),注意命名约束多列相当于对每一列都设置了unique。

使用见上面。

Check

规定某一列或多列值的范围。

其它场景使用见上面。

创建表时添加约束

MySQL

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
)

Primary KEY(主键)

主键,相当于id对应唯一的数据,非null且不能重复值一个表中只能设置一个主键

使用方法和Unique相同(用Primary KEY替换unique即可),除了撤销约束不同

其它场景使用见上面。

撤销约束/命名约束

MySQL

ALTER TABLE Persons
DROP PRIMARY KEY

FOREIGN KEY(外键)

建立设置外键表和主键表的联系,设置外键的字段对应另一个表中的主键。

外键可以为空值,若不为空值,则每一个外键的值必须等于另一个表中主键的某个值。

外键中列的数目必须和父表的主键中列的数目相同

一个表中可以设置多个外键

MySQL的外键约束(FOREIGN KEY),有点小复杂 - 掘金

创建表时添加外键

MySQL

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

命名 FOREIGN KEY 约束

定义多个列的 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

已有表添加约束

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
命名 FOREIGN KEY 约束

并定义多个列的 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

撤销命名约束(撤销多列约束)

MySQL

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

-- 用户表 CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL COMMENT 'bcrypt加密存储', email VARCHAR(100) NOT NULL UNIQUE, role ENUM('user', 'admin') DEFAULT 'user', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_users_username (username) ); -- 书籍表 CREATE TABLE books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, publisher VARCHAR(255), isbn VARCHAR(13) UNIQUE NOT NULL CHECK (REGEXP_LIKE(isbn, '^[0-9]{13}$')), genre VARCHAR(50), stock INT DEFAULT 1 CHECK (stock >= 0), INDEX idx_books_author (author), INDEX idx_books_title (title(191)) ) COMMENT='书籍基本信息表'; -- 推荐表 CREATE TABLE recommendations ( recommendation_id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, book_id INT, book_title VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, recommendation_date DATETIME DEFAULT CURRENT_TIMESTAMP, status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending', reason TEXT NOT NULL, FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (book_id) REFERENCES books(book_id), UNIQUE KEY unique_recommendation (user_id, book_title, author), INDEX idx_recommendations_status (status) ); -- 审核表 CREATE TABLE reviews ( review_id INT AUTO_INCREMENT PRIMARY KEY, recommendation_id INT NOT NULL, admin_id INT NOT NULL, review_date DATETIME DEFAULT CURRENT_TIMESTAMP, result ENUM('approved', 'rejected') NOT NULL, comments TEXT, FOREIGN KEY (recommendation_id) REFERENCES recommendations(recommendation_id), FOREIGN KEY (admin_id) REFERENCES users(user_id) );根据代码给我提供概念结构设计,逻辑结构设计,物理结构设计,概念结构设计要求画E-R图,请按规范画图;逻辑结构设计要求给出关系模式,关系模式要求标明主;物理结构设计需要给出数据类型、主约束和其它自定义约束
最新发布
03-20
<think>好的,我现在需要处理用户的请求,他们希望基于提供的SQL代码进行数据库设计分析,包括概念结构设计(E-R图)、逻辑结构设计(关系模式)和物理结构设计。首先,我需要明确用户的具体需求是什么。用户提到了三个主要部分:E-R图、关系模式以及物理设计的细节,比如数据类型和约束。 首先,用户提供的引用内容是关于CASE语句和数据库软件的介绍,可能与实际SQL代码无关。因此,我需要用户提供具体的SQL代码才能进行分析。但用户可能在提问时没有附上代码,这时候需要我提示他们补充相关信息。比如,用户可能在之前的对话中提供了SQL语句,但现在的问题中没有显示,所以我需要确认这一点。 假设用户已经提供了SQL代码,我的下一步是分析这些代码以提取实体、属性和关系。例如,如果代码中有创建表的语句,每个表对应一个实体,字段对应属性,对应关系。例如,如果有`CREATE TABLE Students (id INT PRIMARY KEY, name VARCHAR(50))`,那么Students是一个实体,id和name是属性。 接下来,生成概念结构设计,即E-R图。需要识别所有实体,它们的属性,以及实体之间的关系。比如,学生和课程之间可能存在多对多的选课关系,这就需要中间实体来连接。 然后是逻辑结构设计,将E-R图转换为关系模式。每个实体成为一张表,属性对应字段,确定主键。例如,学生表的主键是id,课程表的主键是course_id,选课表则包含学生id和课程id作为,联合主键。 物理结构设计涉及具体的数据类型、约束等。例如,字段的数据类型选择VARCHAR的长度,INT的大小,是否允许NULL,以及约束、检查约束等。比如,学生的姓名设置为NOT NULL,成绩字段可能有CHECK约束确保值在0-100之间。 同时,需要考虑用户可能的深层需求。他们可能希望了解设计的最佳实践,比如规范化程度、索引的使用,或者性能优化方面的建议。例如,是否需要进行反规范化处理,或者如何选择合适的数据类型以提高存储效率。 此,用户可能没有明确说明他们使用的数据库管理系统(如MySQL、Oracle等),不同系统在数据类型和约束支持上可能有差异,需要询问或假设通用标准。 最后,生成相关问题,帮助用户进一步思考设计中的关点,例如如何处理多对多关系,如何确保数据完整性,或者如何进行性能优化。 总结步骤:1. 确认SQL代码存在并分析结构;2. 提取实体、属性、关系;3. 绘制E-R图;4. 转换为关系模式;5. 设计物理结构,包括数据类型和约束;6. 考虑用户潜在需求,如优化和兼容性;7. 提出相关问题引导深入思考。</think>根据您的要求,数据库设计分析需要分三部分进行。由于您未提供具体SQL代码,以下将以教务管理系统为例进行说明(若需分析特定代码,请补充SQL语句): 一、**概念结构设计(E-R图)** ``` [学生] ---<选课>--- [课程] | | |1 1 | 姓名 课程名 | [成绩]------------学分 ``` 需根据实际SQL中的表关系确定实体间的基数约束[^1] 二、**逻辑结构设计(关系模式)** 1. 学生表:`Student(sid PK, sname, sgender, sbirth DATE)` 2. 课程表:`Course(cid PK, cname, credit DECIMAL(3,1))` 3. 选课表:`SC(sid FK, cid FK, grade INT CHECK(0<=grade<=100))`[^2] 三、**物理结构设计** ```sql CREATE TABLE Student ( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(20) NOT NULL, sgender ENUM('男','女') DEFAULT '男', sbirth DATE CHECK(sbirth > '1900-01-01') ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE SC ( sid INT REFERENCES Student(sid) ON DELETE CASCADE, cid INT REFERENCES Course(cid) ON UPDATE CASCADE, grade INT, PRIMARY KEY(sid,cid), INDEX grade_idx (grade DESC) ); ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值