1 约束

1.1 概述

  • 为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
  • 约束是表级的强制规定。

  • 可以在创建表的时候添加约束，或者在表创建之后数据添加之前添加约束。

    1.2 约束类型

    有如下的六大约束：

  • NOT NULL，非空约束，规定某个字段不能为空。

  • UNIQUE，唯一约束，规定某个字段在整个表中是唯一的。
  • PRIMARY KEY，主键，非空且唯一。
  • CHECK，检查约束。
  • DEFAULT，默认值。
  • FOREIGN KEY，外键。

需要注意的是，MySQL不支持check约束，但是可以使用check约束，没有任何效果。

1.3 分类

  • 根据约束数据列的限制，约束可以分为：
    • 单列约束：每个约束只能约束一列。
    • 多列约束：每个约束可以约束多列。

CREATE TABLE 表名(
列名 字段类型 单列约束,
列名 字段类型 单列约束,
多列约束(列名1,列名2)
);

  • 根据约束的作用范围，约束可以分为：
    • 列级约束：只能作用在一个列上，并且是根据列的定义后面。
    • 表级约束：可以作用在多个列上，不和列在一起，而是单独定义。

CREATE TABLE 表名(
列名 字段类型 列级约束,
列名 字段类型 列级约束,
表级约束(列名1,列名2)
);
列级约束，六大约束都支持，但是外键约束没有效果。
表级约束，除了非空、默认约束，其他约束都支持。

1.4 创建表的时候添加列级约束

  • 创建学生表：

CREATE TABLE stu_info (
id INT PRIMARY KEY,# 主键
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) CHECK (
gender IN ( ‘男’, ‘女’ )),# 检查
seat INT UNIQUE,# 唯一
age INT DEFAULT 18 # 默认约束
);

1.5 创建表的时候添加表级约束

  • 创建课程表和学生表：

CREATE TABLE major (
id INT PRIMARY KEY,
name VARCHAR ( 255 )
);

CREATE TABLE stu_info (
id INT ,
stu_name VARCHAR ( 255 ) not null ,
gender CHAR ( 1 ) ,
seat INT ,
age INT DEFAULT 18,
major_id int,
CONSTRAINT pk_id PRIMARY KEY (id),
CONSTRAINT uk_seat UNIQUE (seat) ,
CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);

1.6 主键和唯一的区别

保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 最多有1个 √，但是不推荐
唯一 √ √ 可以有多个 √，但是不推荐

1.8 修改表时添加约束

  • 语法：

添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 字段类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 (字段名) [外键的引用];

  • 示例：

CREATE TABLE major (
id INT PRIMARY KEY,
name VARCHAR ( 255 )
);
CREATE TABLE stu_info (
id INT ,
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) ,
seat INT UNIQUE,# 唯一
age INT ,
major_id int
);
# 修改表时添加约束
ALTER TABLE stu_info MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE stu_info MODIFY COLUMN age INT DEFAULT 18;
ALTER TABLE stu_info ADD CONSTRAINT fk_major_id FOREIGN KEY (major_id) REFERENCES major(id);

1.9 修改表时删除约束

CREATE TABLE major (
id INT PRIMARY KEY,
name VARCHAR ( 255 )
);
CREATE TABLE stu_info (
id INT PRIMARY KEY,
stu_name VARCHAR ( 255 ) NOT NULL,# 非空
gender CHAR ( 1 ) ,
seat INT UNIQUE,# 唯一
age INT DEFAULT 18,
major_id int,
CONSTRAINT fk_marjor_id FOREIGN KEY (major_id) REFERENCES major(id)
);
# 修改表时删除非空约束
ALTER TABLE stu_info MODIFY COLUMN stu_name VARCHAR(255) ;
# 修改表时删除默认约束
ALTER TABLE stu_info MODIFY COLUMN age INT;
# 修改表时删除主键
ALTER TABLE stu_info DROP PRIMARY KEY;
# 修改表时删除主键
# 查询唯一键 show index from stu_info
ALTER TABLE stu_info DROP INDEX seat;
# 修改表时删除外键
ALTER TABLE stu_info DROP FOREIGN KEY fk_marjor_id;

2 自增长(auto_increment)

主键(primary key) && 自增长(auto_increment)

2.1 概念

  • 又称为自增长列，可以不用手动插入值，系统提供默认的序列值。

    2.2 应用示例

  • 创建表的时候设置标识列：

删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
name VARCHAR ( 255 )
);
# 新增数据
INSERT INTO student(name) VALUES (‘xxx’);

  • 修改表时设置标识列：

删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY ,
name VARCHAR ( 255 )
);
# 修改表的时候设置标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY auto_increment;
# 新增数据
INSERT INTO student(name) VALUES (‘xxx’);

  • 修改表时删除标识列：

删除表
DROP TABLE IF EXISTS student;
# 创建表的时候设置标识列
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
name VARCHAR ( 255 )
);
# 修改表时删除标识列
ALTER TABLE student MODIFY COLUMN id PRIMARY KEY;

2.3 特点

  • 标识列必须不一定和主键搭配，但是要求是一个key。
  • 一个表中至多有一个标识列。
  • 标识列的类型只能是数值型。
  • 标识列可以通过set auto_increment_increment =3;设置步长，也可以通过手动插入值的方式设置标识列的起始值INSERT INTO student(id,name) VALUES (5,’xxx’)。

    3 索引

    1、定义

    对数据库表的一列或者多列的值进行排序的一种结构(BTree方式)

    2、优点

    加快数据的检索速度

    3、缺点

    1、占用物理存储空间
    2、当对表中数据更新时,索引需要动态维护,占用系统资源,降低数据维护速度

    4、索引示例

    1、开启运行时间检测
    set profiling=1;
    2、执行查询语句(没有索引)
    select name from t1 where name=”lucy88888”;
    3、在name字段创建索引
    create index name on t1(name);
    4、再执行查询语句(有索引)
    select name from t1 where name=”lucy99999”;
    5、对比执行时间
    show profiles;

    5 示例

    多列索引
    CREATE INDEX indexName ON mytable(username1(length),username2(length));
    CREATE INDEX index_age_aex ON t_student(STU_AGE,STU_SEX);

4、索引分类

1 普通索引(index) && 唯一索引(unique)


1、使用规则
1、可设置多个字段
2、约束
普通索引：无约束 key标志 ：MUL
唯一索引：字段值不允许重复,可为NULL UNI
3、把经常用来查询的字段设置为索引字段

  1. - CREATE TABLE article (  
  2.  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,   
  3.  title VARCHAR(200),  
  4.  content TEXT,  
  5.  FULLTEXT (title, content)  
  6.  )ENGINE=MyISAM DEFAULT CHARSET=utf8;


2、创建表创建
create table 表名(
… …
index(name),
index(age),
unique(phnumber),
unique(cardnumber)
);
3、已有表创建
create [unique] index 索引名 on 表名(字段名);
- 修改表结构(添加索引)

  1. ALTER table tableName ADD INDEX indexName(columnName)
  2. ALTER table mytable ADD UNIQUE [indexName] (username(length))
  3. ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  4. ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  5. ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
  6. ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
  1. 4、查看索引


1、desc 表名; —> key标志
2、show index from 表名\G;
5、删除索引
drop index 索引名 on 表名;

2、主键(primary key) && 自增长(auto_increment)

3、外键(foreign key)

外键的特点
  • 要求在从表设置外键。
  • 要求从表的外键列的类型要和主表的关联列的类型要求一致或兼容，但是名称不要求一致。
  • 主表的关联列必须是一个key（一般是主键或唯一键）。

1、定义

  1.   让当前表字段的值在另一个表的范围内选择

2、语法

  1.   foreign key(参考字段名)


references 主表(被参考字段名)
on delete 级联动作
on update 级联动作

3、使用规则

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

4、示例

  1.   1、缴费信息表(财务)


id 姓名 班级 缴费金额
1 唐伯虎 AID08 300
2 点秋香 AID08 200
create table jftab(
id int primary key,
name varchar(20) not null,
class char(5) default “AID”,
money smallint
)charset=utf8;

insert into jftab values
(1,”唐伯虎”,”AID08”,300),
(2,”点秋香”,”AID08”,200);

2、学生信息表(班主任)
stu_id 姓名 缴费金额
create table bjtab(
stu_id int,
name varchar(15),
money smallint,
foreign key(stu_id) references jftab(id)
on delete cascade
on update cascade
);

4、删除

  1.   1、查看外键名


show create table bjtab;
2、删除外键
alter table 表名 drop foreign key 外键名;

5、在已有表中添加外键

  1.   alter table bjtab add foreign key(stu_id)


references jftab(id)
on delete set null
on update set null;

6、级联动作

当删除或更新主键表数据时，由于有外键约束，所以必须先删除外键表数据才能删除主键表数据
使用数据库的级联操作可以避免这类问题

  1.   1、cascade


数据级联删除、更新(参考字段)
cascade:当删除或修改主键表user的数据时，会自动删除或修改外键表tip所关联的数据，使其与主键表中的字段值保持一致
2、set null
从表有相关联记录,字段值设置为NULL
set null:当删除或修改主键表user时，会自动设置外键表中的外键字段为null
3、restrict(默认)
从表有相关联记录,不让主表删除、更新



  1. create table user(
  2. id int primary key auto_increment, //主键id
  3. username varchar(30), //用户名
  4. password varchar(50) //密码
  5. );
  6. create table tip(
  7. id int primary key auto_increment.
  8. userid int, //外键列
  9. foreign key (userid) references user(id) on delete cascade/set null on update cascase/set null //设置级联
  10. );

外键示例

简单演示一下使用，做dage和xiaodi两个表，大哥表是主键，小弟表是外键

  2. # 建表：
  5.  1CREATE TABLE `dage` (
  6.  2  `id` int(11) NOT NULL auto_increment,
  7.  3  `name` varchar(32) default '',
  8.  4  PRIMARY KEY  (`id`))
  9.  5  ENGINE=InnoDB DEFAULT CHARSET=latin1；
  10.  6
  11.  7CREATE TABLE `xiaodi` (
  12.  8  `id` int(11) NOT NULL auto_increment,
  13.  9  `dage_id` int(11) default NULL,
  14. 10  `name` varchar(32) default '',
  15. 11  PRIMARY KEY  (`id`),
  16. 12  KEY `dage_id` (`dage_id`),
  17. 13  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
  18. 14) ENGINE=InnoDB DEFAULT CHARSET=latin1；
  21. # 插入个大哥：
  24. 1mysql> insert into dage(name) values('铜锣湾');
  25. 2Query OK, 1 row affected (0.01 sec)
  26. 3mysql> select * from dage;
  27. 4+----+--------+
  28. 5| id | name   |
  29. 6+----+--------+
  30. 7|  1 | 铜锣湾 |
  31. 8+----+--------+
  32. 91 row in set (0.00 sec)
  36. # 插入个小弟：
  38. 1mysql> insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');
  39. 2Query OK, 1 row affected (0.02 sec)
  40. 3
  41. 4mysql> select * from xiaodi;
  42. 5+----+---------+--------------+
  43. 6| id | dage_id | name         |
  44. 7+----+---------+--------------+
  45. 8|  1 |       1 | 铜锣湾_小弟A |
  46. 9+----+---------+--------------+
  50. # 把大哥删除：
  52. 1mysql> delete from dage where id=1;
  53. 2ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))
  55. 提示：不行呀，有约束的，大哥下面还有小弟，可不能扔下我们不管呀！
  57. 插入一个新的小弟：
  59. 1mysql> insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');              
  60. 2ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bstar/xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))
  61. 3
  63. 提示：小子，想造反呀！你还没大哥呢！
  65. # 把外键约束增加事件触发限制：
  66. 复制代码
  68. 1mysql> show create table xiaodi;
  69.  2
  70.  3  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
  71.  4
  72.  5mysql> alter table xiaodi drop foreign key xiaodi_ibfk_1; 
  73.  6Query OK, 1 row affected (0.04 sec)
  74.  7Records: 1  Duplicates: 0  Warnings: 
  75.  8mysql> alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
  76.  9Query OK, 1 row affected (0.04 sec)
  77. 10Records: 1  Duplicates: 0  Warnings: 0
  79. 复制代码
  81. 再次试着把大哥删了：
  82. 复制代码
  84. 1mysql> delete from dage where id=1;
  85. 2Query OK, 1 row affected (0.01 sec)
  86. 3
  87. 4mysql> select * from dage;
  88. 5Empty set (0.01 sec)
  89. 6
  90. 7mysql> select * from xiaodi;
  91. 8Empty set (0.00 sec)
  93. 复制代码
  95. 哎呦，这回对应的小弟也没了，没办法，谁让你跟我on delete cascade(级联限制)了呢！