MySQL-外键+索引

外键

描述：

外键是表的一个特殊字段；
被参照的表是主键，外键所在字段的表为子表；
设置外键的原则就是依赖于数据库中已存在的表的主键；

作用：

建立该表与其父表的关联关系，父表中对记录做操作时，子表中与之对应的信息也应有相应的该表；
保持数据的一致性和完整性；
实现一对一或一对多的关系；

注意项：

父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
数据表的存储引擎只能为InnoDB；
外键列和参照列必须具有相似的数据类型，其中数据的长度或是否有符号为必须相同，而字符的擦汗那个度则可以不同；
外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将自动创建索引；

外键约束的参照操作：

CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行；
SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL，如果使用该选项，必须保证子表列没有指定NOT NULL;
RESTRICT:拒绝对父表的删除或更新操作

• –创建部门表department(父表)

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
departName VARCHAR(20) NOT NULL NUIQUE
)ENGINE=INNODB CHARSET=UTF-8;

INSERT INTO department(departName) VALUES
('人力'),
('财务')，
('研发'),
('销售');

• –创建员工表(子表)

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
departId TINYINT UNSIGNED,
FOREIGN KEY(departId) REFERENCES department(id)		#添加外键
)ENGINE=INNODB;

INSERT INTO employee(username,departId) VALUES
('赵',1),
('钱',2),
('孙',3),
('王',3),
('李',4);

• –查询出员工id，用户名，所属部门

SELECT e.id,e.username,d.departName
FROM employee AS e
JOIN department AS d
ON e.departId=d,id;

• –删除主表中的记录

DELETE FROM department WHERE id=1;
#删不掉，因为员工表中有员工属于部门1

• –删除员工表中的属于部门1 的人

SELETE FROM employee WHERE departId=1;
#删除员工后，才可以删除部门

• –删除员工表

DROP TABLE employee;

• –创建外键，指定外键名称

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
departId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(departId) REFERENCES department(id)		#添加外键名称是emp_fk_dep
)ENGINE=INNODB;

• –删除外键

ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;

• –动态添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(departId) REFERENCES department(id);

• –CASCADE:级联操作，从父表删除或更新且自动删除或更新子表中匹配的行
• –测试CASCADE
• –创建部门表department(父表)

CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
departName VARCHAR(20) NOT NULL NUIQUE
)ENGINE=INNODB CHARSET=UTF-8;

• –创建员工表(子表)

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
departId TINYINT UNSIGNED,
FOREIGN KEY(departId) REFERENCES department(id)	ON DELETE ON UPDATE CASCADE 	#删除或更新父表中指定列，子表也会删除或更新
)ENGINE=INNODB;

DELETE FROM department WHERE id=1;
#删除父表中id=1的记录，此操作同时会删除子表中departId=1的记录

• –SET NULL:从父表删除或更新行，并设置子表中的外键列为NULL；如果使用此选项，必须保证子表列没有指定NOT NULL

• –RESTRICT:拒绝对父表的删除或更新操作

• –NO ACTION:标准SQL的关键字，在MySQL中与RESTRICT相同

索引

1. 什么是索引？

• 索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
• 索引的优点是可以提高检索数据的速度
• 索引的缺点是创建和维护索引需要时间
• 索引可以提高查询速度，会减慢写入速度

2.索引的分类

• 普通索引
• 唯一索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

3.如果创建索引？

3.1 创建表的时候创建索引

CREATE TABLE tbl_name(
字段名称 字段类型 [完整性约束条件],
...,
[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [索引名称](字段名称[(长度)] [ASC|DESC])
);

• –创建普通索引

CREATE TABLE test1(
id TINYINT UNSIGNED,
username VARCHAR(20),
INDEX in_id(id),
KEY in_username(username)
);

• –创建唯一索引

CREATE TABLE test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
card CHAR(18) NOT NULL,
UNIQUE KEY uni_card(card)
);

• –创建全文索引

CREATE TABLE test3(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
userDese VARCHAR(20) NOT NULL,
FULLTEXT INDEX full_userDese(userDesc)
);

• –创建单列索引

CREATE TABLE test4(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX in_test1(test1)
);

• –创建多列索引

CREATE TABLE test5(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
INDEX mul_t1_t2_t3(test1,test2,test3)
);

CREATE TABLE test6(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 VARCHAR(20) NOT NULL,
test2 VARCHAR(20) NOT NULL,
test3 VARCHAR(20) NOT NULL,
test4 VARCHAR(20) NOT NULL,
UNIQUE KEY mul_t1_t2_t3(test1,test2,test3)
);

• –创建空间索引

CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test)
)ENGINE=MyISAM;

3.2 在已经存在的表上创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称 ON 表名 {字段名称[(长度)] [ASC|DESC]};
ALTER TABLE tbl_name ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名称](字段名称[(长度)] [ASC|DESC]);

• –创建普通索引

CREATE INDEX in_id ON test1(id);
ALTER TABLE test1 ADD INDEX in_username(username); 

• –创建唯一索引

ALTER TABLE test2 DROP INDEX uni_card;
DROP INDEX username ON test2;
CREATE UNIQUE INDEX uni_username ON test2(username);
ALTER TABLE test2 ADD UNIQUE INDEX uni_card(card);

• –创建全文索引

CREATE FULLTEXT INDEX full_userDesc ON test3(userDesc);

• –创建多列索引

ALTER TABLE test5 DROP INDEX mul_t1_t2_t3;
ALTER TABLES test5 ADD INDEX mul_t1_t2_t3(test1,test2,test3);

• –创建空间索引

CREATE TABLE test7(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
test1 GEOMETRY NOT NULL,
SPATIAL INDEX spa_test(test1)
)ENGINE=MyISAM;

DROP INDEX spa_test ON test7;
CREATE SPATIAL INDEX spa_test ON test7(test1);

4.如何删除索引？

DROP INDEX 索引名称 ON tbl_name

• –删除索引

DROP INDEX in_id ON test1;	
DROP INDEX in_username ON test1;