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
数据如下:CREATE TABLE `classes` ( `class_id` int(11) NOT NULL AUTO_INCREMENT, `class_name` varchar(100) NOT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`class_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `courses` ( `course_id` int(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(100) NOT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`course_id`), KEY `teacher_id` (`teacher_id`), CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `grades` ( `grade_id` int(11) NOT NULL AUTO_INCREMENT, `student_id` int(11) DEFAULT NULL, `subject` varchar(100) NOT NULL, `score` decimal(5,2) DEFAULT NULL, PRIMARY KEY (`grade_id`), KEY `student_id` (`student_id`), CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `students` ( `student_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `gender` enum('Male','Female','Other') NOT NULL, `date_of_birth` date NOT NULL, `class_id` int(11) DEFAULT NULL, PRIMARY KEY (`student_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE `teachers` ( `teacher_id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `subject` varchar(100) NOT NULL, PRIMARY KEY (`teacher_id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
03-15
-- 用户表(整合学生/教师/管理员) CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(50) UNIQUE NOT NULL COMMENT '登录用户名', pwd VARCHAR(50) NOT NULL COMMENT '明文密码', role ENUM('student','teacher','admin') NOT NULL COMMENT '用户角色', realname VARCHAR(50) NOT NULL COMMENT '真实姓名', userno VARCHAR(20) UNIQUE COMMENT '学号/工号(角色为student时是学号,teacher时为工号)', age TINYINT UNSIGNED, sex ENUM('M','F','O') COMMENT 'M-男, F-女, O-其他', email VARCHAR(100), tel VARCHAR(20), qq VARCHAR(20), photo VARCHAR(255) COMMENT '头像存储路径', majorid INT COMMENT '所属专业(仅学生有效)', gradeid INT COMMENT '所属班级(仅学生有效)', INDEX idx_role (role), INDEX idx_userno (userno) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 专业表(保留原有设计) CREATE TABLE major ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE COMMENT '专业全称', shortname VARCHAR(10) NOT NULL COMMENT '专业简称', comment TEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 班级表(保留原有设计) CREATE TABLE grade ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL UNIQUE COMMENT '班级名称(格式:年份+专业简称+班级)', majorid INT NOT NULL, comment TEXT, FOREIGN KEY (majorid) REFERENCES major(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 课程表(新增教师姓名冗余字段) CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, courseno VARCHAR(20) NOT NULL UNIQUE COMMENT '课程代码', name VARCHAR(100) NOT NULL, score TINYINT UNSIGNED COMMENT '学分', teacherid INT NOT NULL COMMENT '授课教师ID', teachername VARCHAR(50) COMMENT '教师姓名(冗余存储)', FOREIGN KEY (teacherid) REFERENCES user(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 考勤表(状态改为中文枚举) CREATE TABLE attendance ( id INT PRIMARY KEY AUTO_INCREMENT, stuid INT NOT NULL COMMENT '学生ID', courseid INT NOT NULL COMMENT '课程ID', status ENUM('出勤','请假','旷课','迟到','早退') NOT NULL, time DATETIME NOT NULL COMMENT '考勤时间', FOREIGN KEY (stuid) REFERENCES user(id), FOREIGN KEY (courseid) REFERENCES course(id), INDEX idx_course_time (courseid, time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 作业表(保留原有设计) CREATE TABLE task ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, content TEXT NOT NULL, starttime DATETIME NOT NULL, endtime DATETIME NOT NULL, courseid INT NOT NULL, FOREIGN KEY (courseid) REFERENCES course(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 评分表(增加教师评分人字段) CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, taskid INT NOT NULL, stuid INT NOT NULL, courseid INT NOT NULL, score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100), teacherid INT NOT NULL COMMENT '批改教师', comment TEXT, FOREIGN KEY (taskid) REFERENCES task(id), FOREIGN KEY (stuid) REFERENCES user(id), FOREIGN KEY (courseid) REFERENCES course(id), FOREIGN KEY (teacherid) REFERENCES user(id), UNIQUE KEY uni_task_stu (taskid, stuid) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
03-14
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值