SQL基础-约束&索引-白红宇

SQL基础-约束&索引

阅读量：5220 次

发布时间：2019-06-14

本文共 5354 字，大约阅读时间需要 17 分钟。

一、约束

1、约束简介

约束（constraint）：    表中数据的限制条件。 完整性   有效性约束的种类：    主键约束    外键约束    唯一约束    检查约束    非空约束

2、主键约束

主键约束：    唯一标识表中每一行的一列或多个列，可以定义一列或多列为主键；    主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）；    主健可作外健，唯一索引不可；    不能或者很难更新；定义表的主键：方法一：建表时定义CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',PRIMARY KEY(student_id))COMMENT='学生';方法二：建表后创建REATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号')COMMENT='学生’;ALTER TABLE student ADD PRIMARY KEY(student_id);

3、外键约束

外键约束：    约定某一列的值必须出现在另一个表的主键中；    表的外键是另一表的主键, 外键可以有重复的, 可以是NULL；    用来和其他表建立联系用的；    一个表可以有多个外键；方法一：建表时创建CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id))COMMENT='学生';方法二：建表后创建CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号')COMMENT='学生’;ALTER TABLE student ADD FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id);

4、唯一约束

唯一约束：    约定某一列或某几列的值在表中不重复。唯一约束和主键约束的区别：    唯一性约束允许在该列上存在NULL值，而主键约束的限制更为严格，不但不允许有重复，而且也不允许有空值。 约束和索引：    前者是用来检查数据的正确性，后者用来实现数据查询的优化，目的不同；#创建唯一约束方法一：UNIQUE关键字，这种直接定义在列名后，适合单字段CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名' UNIQUE,gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号')COMMENT='学生';方法二：定义在最后，可以指定多个字段唯一CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号',UNIQUE (calss_id,student_name))COMMENT='学生';

5、检查约束

检查约束：    约定某一列的值必须满足指定的条件。与其他约束不同，MySQL虽然支持CHECK约束，但并没有强制限制。也就是说，违反约束的数据也会被接受。CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄' CHECK ( age >= 10 AND age <= 20 ),class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号')COMMENT='学生';

6、非空约束

非空约束：    约定某一列的值不可为NULL。CREATE TABLE student (student_id varchar(50) NOT NULL COMMENT '学生编号',student_name varchar(100) NOT NULL DEFAULT '' COMMENT '学生姓名',gender varchar(10) NOT NULL DEFAULT '' COMMENT '性别',birth_day date NOT NULL COMMENT '生日',age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',class_id varchar(50) NOT NULL DEFAULT '' COMMENT '班级编号',score decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '数学成绩',teacher_id varchar(20) DEFAULT NULL COMMENT '老师编号')COMMENT='学生';

二、索引

1、索引

索引定义：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，

数据库索引是用于提高数据库表的数据访问速度的。

数据库索引的特点：

a）避免进行数据库全表的扫描，大多数情况，只需要扫描较少的索引页和数据页，而不是查询所有数据页。而且对于非聚集索引，有时不需要访问数据页即可得到数据。

b）聚集索引可以避免数据插入操作，集中于表的最后一个数据页面。

c）在某些情况下，索引可以避免排序操作。

索引背后的数据结构基础是b tree或者 b+ tree，后续有机会再详细看；

2、索引的创建及使用

比如：    查询2006年6月12日出生的学生有哪些？    SELECT * FROM student WHERE birth_day = '2006-06-12';如果birth_day这一列查询频率很高，可以创建索引：    CREATE INDEX idx_birth_day ON student(birth_day);#为多个列创建索引    CREATE UNIQUE INDEX idx_2 ON student(class_id,student_name);    SELECT * FROM student WHERE class_id = 'G0102' AND student_name = '牛思静';

3、删除索引

比如：删除student表上创建的名为idx_2的索引：        DROP INDEX idx_2 ON student;

4、索引的优缺点

优点： 提高查询（SELECT、GROUP BY、ORDER BY）速度； 唯一索引可保证数据唯一性；缺点： 创建的索引需要占用物理存储空间； 建立并维护索引需要花费时间，且随着表中数据量的增加，花费的时间也会增加；

转载于:https://www.cnblogs.com/weiyiming007/p/11446005.html

你可能感兴趣的文章