MySQL笔记

约束

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据的完整性要从以下四个方面考虑：

• 实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录

• 域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”

• 引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门

• 用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

根据约束的特点，分为几种：

• 键约束：主键约束、外键约束、唯一键约束

• Not NULL约束：非空约束

• Check约束：检查约束

• Default约束：缺省约束.

（1）查看某个表的约束和索引

• SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
• SHOW INDEX FROM 表名称;
• SHOW CREATE TABLE 表名;

（2）主键约束PRIMARY KEY

主键：Primary key，简称PK，数据库主键作用保证实体的完整性，可以是一个列或多列的组合。

• 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值，如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。

• 每个表有且最多只允许一个主键约束。

• MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。

• 当创建主键约束时，MySQL默认在对应的列上建立主键索引。删除主键时，也会直接删除主键索引。

在主键列后面直接加主键约束，复合主键不能使用这种方式	单独声明主键约束	声明复合主键，复合主键只能使用这种方式
CREATE TABLE t_stu( sid INT PRIMARY KEY, sname VARCHAR(100), gender CHAR);	CREATE TABLE t_course( cid INT , cname VARCHAR(100), decription VARCHAR(200), PRIMARY KEY(cid));	CREATE TABLE t_stu_course( sid INT, cid INT, score DOUBLE(4,1), PRIMARY KEY(sid,cid));
建表后添加主键约束	alter table 表名称 add 【constraint 约束名】 primary key (字段名);
alter table 表名称 add 【constraint 约束名】 primary key (字段名1，字段名2);
ALTER TABLE t_stu ADD PRIMARY KEY(sid);	ALTER TABLE t_course ADD PRIMARY KEY(cid);	ALTER TABLE t_stu_course ADD PRIMARY KEY(sid,cid);

如何删除主键和对应的索引？

删除主键约束，不需要指定主键名，因为一个表只有一个主键，删除主键约束后，非空还存在alter table表名称drop primary key;

（3）唯一键Unique key，简称UK

• 同一个表可以有多个唯一约束。

• 唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。

• MySQL会给唯一约束的列上默认创建一个唯一索引。

• 删除唯一键只能通过删除唯一索引的方式删除，删除时需要指定唯一索引名，唯一索引名就是唯一约束名一样。如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和()中排在第一个的列名相同。也可以自定义唯一性约束名。

如何建立唯一性约束？

在某个列后面直接加唯一性约束	单独指定表的唯一性约束	组合列唯一性约束
CREATE TABLE t_course( cid INT PRIMARY KEY, cname VARCHAR(100) UNIQUE, description VARCHAR(200));	CREATE TABLE t_stu( sid INT PRIMARY KEY, sname VARCHAR(100), card_id CHAR(18), CONSTRAINT uk_card_id UNIQUE KEY(card_id));#其中CONSTRAINT uk_cname和KEY可以省略	CREATE TABLE t_stu_course( id INT PRIMARY KEY, sid INT, cid INT, score DOUBLE(4,1), CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid));#其中CONSTRAINT uk_sid_cid和KEY可以省略
建表后增加唯一性约束	alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名);alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名1，字段名2);
ALTER TABLE t_course ADD CONSTRAINT uk_cname UNIQUE KEY(cname);#其中CONSTRAINT uk_cname和KEY可以省略	ALTER TABLE t_stu ADD CONSTRAINT uk_card_id UNIQUE KEY(card_id);#其中CONSTRAINT uk_cname和KEY可以省略	ALTER TABLE t_stu_course ADD CONSTRAINT uk_sid_cid UNIQUE KEY(sid,cid);#其中CONSTRAINT uk_cname和KEY可以省略

如何删除唯一性约束和索引？

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;#注意：如果忘记名称，可以通过“show index from 表名称;”查看

主键和唯一键的区别：

（1）主键是非空，唯一键允许空

（2）主键一个表只能一个，唯一键可以有多个

4）外键：Foreign key，简称FK

• 外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。

• 当创建外键约束时，系统默认会在所在的列上建立对应的普通**索引**。但是索引名是列名，不是外键的约束名。

• 删除外键时，关于外键列上的普通索引需要单独删除。

注意：

• 在从表上建立外键，而且主表要先存在。

• 从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键）。

• 从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样

• 一个表可以建立多个外键约束

• 从表的外键值必须"在主表中能找到"或者为空，从而约束了从表的外键列的值的添加和修改。

• 当主表的记录被从表参照时，主表中被参考记录的删除和更新也会受到限制。

 （1）默认情况下，主表和从表是严格依赖关系RESTRICT。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。

 （2）但是有一种是级联“修改、删除”：

 ON DELETE SET NULL(级联置空)：当外键设置了SET NULL,当主表的相关记录删除时，从表对应的字段改为NULL。注意从表外键字段得允许为空才行

 ON DELETE CASCADE(级联删除)：当外键设置了CASCADE（级联），当主表的相关记录删除时，从表对应的行都删除了。

 对于外键约束,最好是采用: ON UPDATE CASCADE ON DELETE RESTRICT 的方式

 如果要删除表，需要先删除从表，才能删除主表

如何建立外键约束？

创建外键	CREATE TABLE t_department( did INT PRIMARY KEY, dname VARCHAR(100) NOT NULL UNIQUE, description VARCHAR(200) NOT NULL);CREATE TABLE t_employee( eid INT PRIMARY KEY, ename VARCHAR(100) NOT NULL, dept_id INT, CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT);#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT
一个表可以有多个外键，而且主表和从表可以是一张表	CREATE TABLE t_emp( eid INT PRIMARY KEY, ename VARCHAR(100) NOT NULL, manager_id INT, dept_id INT, CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT fk_emp_mid_eid FOREIGN KEY(manager_id) REFERENCES t_emp(eid) ON UPDATE CASCADE ON DELETE RESTRICT);#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT
建表后创建外键	alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);ALTER TABLE t_emp ADD CONSTRAINT fk_emp_dept_did FOREIGN KEY(dept_id) REFERENCES t_department(did) ON UPDATE CASCADE ON DELETE RESTRICT;#其中CONSTRAINT fk_emp_dept_did可以省略#ON UPDATE CASCADE ON DELETE RESTRICT如果省略表示都是RESTRICT

如何删除外键约束？

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;ALTER TABLE t_emp DROP FOREIGN KEY fk_emp_dept_did;

查看约束名SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

如何删除外键列上的索引？需要单独删除

ALTER TABLE 表名称 DROP INDEX 外键列索引名; ALTER TABLE t_emp DROP INDEX dept_id;

查看索引名show index from 表名称;

（5）非空约束

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

如果某列已经创建好，那么可以修改列语句修改：

例如：原来非空，修改为允许空

例如：原来允许空，修改为非空

（6）检查约束

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；

例如：age tinyint check(age >20) 或 sex char(2) check(sex in(‘男’,’女’))

（7）Default缺省约束

default：默认值，在插入数据时某列如果没指定其他的值,那么会将默认值添加到新记录。

如果某列已经创建好，那么可以修改列语句修改：

例如：原来有默认值，去除默认值

例如：原来没有默认值，增加默认值

8.7 索引：INDEX

索引：索引是对数据库表中一列或多列的值进行排序的一种结构。索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。而约束是一种逻辑概念。

例如：一本字典，如何快速找到某个字，可以给字典加目录，对数据库来说,索引的作用即是给"数据"加目录。

设有N条随机记录,不用索引,平均查找N/2次,那么用了索引之后呢。如果是btree(二叉树)索引，，如果是hash(哈希)索引，时间复杂度是1。

索引好处：加快了查询速度(select )

索引坏处:降低了增,删,改的速度(update/delete/insert)，增大了表的文件大小(索引文件甚至可能比数据文件还大)

MySQL提供多种索引类型供选择：

l 普通索引：

l 唯一性索引：

l 主键索引：只有一个主键索引

l 全文索引：MySQL5.X版本只有MyISAM存储引擎支持FULLTEXT，并且只限于CHAR、VARCHAR和TEXT类型的列上创建。

MySQL的索引方法：

l HASH

l BTREE

MySQL中多数索引都以BTREE的形式保存。

 

索引的使用原则：

（1）不过度索引

（2）索引条件列(where后面最频繁的条件比较适宜索引)

（3）索引散列值,过于集中的值不要索引，例如:给性别"男","女"加索引,意义不大

CREATE INDEX 索引名 ON 表名称 (column_name,[column_name...]); 最左边的列最关键

alter table 表名称 drop index 索引名;

8.8 自增列：AUTO_INCREMENT

例如：

关于自增长auto_increment：

（1）整数类型的字段才可以设置自增长。

（2）当需要产生唯一标识符或顺序值时，可设置自增长。

（3）一个表最多只能有一个自增长列

（4）自增长列必须非空

（5）自增长列必须是主键列或唯一键列。

（6）InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

一、DML操作

数据操作语言（DML）DML用于插入、修改、删除数据记录，包括如下SQL语句：

• INSERT（insert）：添加数据到数据库中
• UPDATA（updata）：修改数据库中的数据
• DELETE（delete）：删除数据库中的数据

1.1插入数据

1.1.1语法

• INSERT  INTO 表名称 VALUES（值1，值2...）；=

　　　　{ insert into 表名称 values（值1，值2.....）；}

• INSERT INTO 表名称 VALUES（值1，值2，......），（值1，值2......）；

　　　　{insert into 表名称 values（值1，值2，......），（值1，值2，......）；}

• INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......);

　　　　{insert into 表名称（字段1，字段2,....） values（值1，值2.....）；}

• INSERT INTO 表名称 (字段1，字段2，......) VALUES(值1，值2，......),(值1，值2，......),.....; 

　　　　　{insert into 表名称（字段1.字段2，....）values（值1，值2，......),(值1，值2，......),.....; }

1.1.2说明

1、值列表内（值1，值2，....）的顺序、个数与字段列表内的（字段1，字段2，.....）中的字段顺序、个数一致

（1）如果个数少了就报Column count doesn’t match value count

（2）如果VALUES前面的()中没有列出字段，那么默认就是为表中的所有字段赋值，那么个数与顺序与表结构中字段定义的一致

2、关于自增长列、默认值列、允许为NULL列的赋值

（1）如果字段列表列出了字段名，那么值列表中就要为其赋值，哪怕它是自增长列，有默认值列，可以为NULL值的列。

• InnoDB表的自动增长列可以手动插入合适的值，但是插入的值如果是NULL或者0，则实际插入的将是自动增长后的值；

• 如果列声明了“默认约束”那么对应的位置可以赋值具体的值，也可以使用“DEFAULT”，表示使用默认值；

• 如果列允许了NULL值，那么可以为对应的字段可以赋值为具体值也可以赋值为NULL

（2）对于没有列出的字段，像自增列就自动赋值，像默认值列就自动赋默认值，像允许NULL的列就自动赋NULL值，但是非空列又没有提供默认值会自动赋值为对应数据类型的默认值，例如字符串赋值为空字符串，int赋值为0；

3、VALUES也可以写成VALUE，但是VALUES是标准写法

4、可以同时插入多行

5、如果插入从表的数据，要注意查看主表参照字段的值是否存在

6、值的位置可以是常量值、表达式、函数

1.1.3示例

CRETATE TABLE s_stu(
              sid int primary key  auto_incrment,
              sname varchar(100) not null,
              gender char not null default '男',
              card_id dhar(18)not null unique,
              birthdat date,
              address varchar(200)
);