mysql中索引知识点补充以及一些其他知识点的补充

直接po代码和截图了

#mysql中索引知识点补充以及一些其他知识点的补充

/*
参考网页https://blog.csdn.net/czh500/article/details/101036960

为什么我们添加完索引后查询速度为变快？
传统的查询方法，是按照表的顺序遍历的，不论查询几条数据，mysql需要将表的数
据从头到尾遍历一遍在我们添加完索引之后，mysql一般通过BTREE算法生成一个索引文件，在查
询数据库时，找到索引文件进行遍历(折半查找大幅查询效率)，找到相应的键从而获取数据

索引的代价
创建索引是为产生索引文件的，占用磁盘空间
索引文件是一个二叉树类型的文件，可想而知我们的dml操作同样也会对索引文件进行修改，所以性能会下降

在哪些列上使用索引？
1.较频繁的作为查询条件字段应该创建索引
2.唯一性太差的字段不适合创建索引，尽管频繁作为查询条件，例如gender性别字段
3.更新非常频繁的字段不适合作为索引
4.不会出现在where子句中的字段不该创建索引

总结
满足以下条件的字段，才应该创建索引.
1.肯定在where条件经常使用
2.该字段的内容不是唯一的几个值
3.字段内容不是频繁变化

*/

#----------------
#查询一张表中的所有索引
SHOW INDEX FROM book;
SHOW KEYS FROM book;
SHOW CREATE TABLE book;

#----------------
#删除一张表中的所有索引

/*
删除一张表中的所有索引
参考网页https://zhidao.baidu.com/question/165099713.html
参考网页https://blog.csdn.net/zj7321/article/details/82423066

MYSQL索引信息存储在INFORMATION_SCHEMA.STATISTICS表里，如
果要批量删除索引（主键除外）则可通过其构建批量删除sql

#拼接删除索引的语法
SELECT CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = '库名' AND i.INDEX_NAME <> 'PRIMARY';
*/

#拼接删除索引的语法
SELECT i.TABLE_NAME, i.COLUMN_NAME, i.INDEX_NAME, 
CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
WHERE TABLE_SCHEMA = 'myemployees' AND i.INDEX_NAME <> 'PRIMARY';

#-------------

#拼接删除索引的语法
SELECT i.TABLE_NAME, i.COLUMN_NAME, i.INDEX_NAME,
CONCAT('ALTER TABLE ',i.TABLE_NAME,' DROP INDEX ',i.INDEX_NAME,' ;') 
FROM INFORMATION_SCHEMA.STATISTICS i
#过滤主键索引
#过滤库和表
WHERE TABLE_SCHEMA = 'myemployees' AND TABLE_NAME = 'book' AND i.INDEX_NAME <> 'PRIMARY';

#----------------
#重建一张表中的所有索引;
#可以使用CREATE INDEX或ALTER TABLE来为表增加索引
/*
(1.)ALTER TABLE table_name ADD INDEX index_name (column_list)
(2.)ALTER TABLE table_name ADD UNIQUE (column_list)
(3.)ALTER TABLE table_name ADD PRIMARY KEY (column_list)

*/

#执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引
CREATE TABLE testMyIndex(
xing VARCHAR(30),
ming VARCHAR(20),
email VARCHAR(30),
UNIQUE KEY index_email(email), #唯一索引
KEY xm(xing,ming)
)
#查看表结构
DESC testMyIndex;
#查看表中的索引
SHOW INDEX FROM testMyIndex;

#ALTER TABLE可以添加多列，可以修改多列，可以删除多列，因此ALTER TABLE单个语句中可以同时创建多个索引
#添加多列
alter table testMyIndex add (address VARCHAR(40),salary FLOAT);   #正确，add支持多列




#参考网页https://blog.csdn.net/czh500/article/details/101166131
#参考网页https://blog.csdn.net/czh500/article/details/101118088
# -----------------------------------------------------------
alter table testMyIndex add (hometown VARCHAR(45) KEY,money FLOAT UNIQUE); #正确
#报错了，Multiple primary key defined 提示已经有primary key(主键)了
#alter table testMyIndex add (remarks VARCHAR(50) KEY,bankNumber VARCHAR(16) UNIQUE);
# -----------------------------------------------------------




#查看表结构
DESC testMyIndex;
SHOW CREATE TABLE testMyIndex;
#查看索引
SHOW INDEX FROM testMyIndex;
alter table testMyIndex add column (hobby VARCHAR(150),tel VARCHAR(11));    #正确
alter table testMyIndex add workCity VARCHAR(30),add schoolName VARCHAR(40);     #正确
#查看表结构
DESC testMyIndex;

#ALTER TABLE允许在单个语句中更改多个列
#修改多列
alter table testMyIndex change address test_address VARCHAR(120),change salary test_salary FLOAT(7,2); #正确
alter table testMyIndex change column hobby test_hobby VARCHAR(130),change column tel test_tel VARCHAR(13); #正确

#查看表结构
DESC testMyIndex;

#删除多列
alter table testMyIndex drop test_address,drop test_salary;   #正确
alter table testMyIndex drop column test_hobby,drop column test_tel;     #正确
#查看表结构
DESC testMyIndex;

#查看索引
SHOW INDEX FROM testMyIndex;

#ALTER TABLE允许在单个语句中更改多个列，因此ALTER TABLE可以同时创建多个索引
#ALTER TABLE单个语句中可以同时创建多个索引
#MySQL修改表一次添加多个索引(普通索引)
ALTER TABLE testMyIndex 
ADD INDEX index_workCity(workCity), 
ADD INDEX index_schoolName_workCity(schoolName,workCity), 
ADD INDEX index_schoolName(schoolName); 
#查看索引
SHOW INDEX FROM testMyIndex;

#MySQL修改表一次添加多个索引(唯一索引)
ALTER TABLE testMyIndex ADD UNIQUE index_workCity2(workCity), 
ADD UNIQUE index_schoolName_workCity2(schoolName,workCity), 
ADD UNIQUE index_schoolName2(schoolName); 
#查看索引
SHOW INDEX FROM testMyIndex;

#
CREATE TABLE userInfo(
id int,
userName VARCHAR(40),
`password` VARCHAR(20),
email VARCHAR(50),
address VARCHAR(200),
tel VARCHAR(11),
salary FLOAT
)
#添加普通索引(索引名可选，我这里给索引取了名字)
ALTER TABLE userInfo ADD INDEX address_index (address, salary);
#查看索引
SHOW INDEX FROM userInfo;
#删除索引
DROP INDEX address_index ON userInfo;
#查看索引
SHOW INDEX FROM userInfo;
#添加普通索引(索引名可选，我这里不给索引取名字，MySQL将根据第一个索引列自动赋一个名称)
ALTER TABLE userInfo ADD INDEX (address, salary);
#查看索引
SHOW INDEX FROM userInfo;
#添加唯一索引(MySQL将根据第一个索引列自动赋一个索引名)
ALTER TABLE userInfo ADD UNIQUE (userName, email, tel);
#查看索引
SHOW INDEX FROM userInfo;
#删除索引(以下2种方式都可以删除索引，大家任选一种即可)
ALTER TABLE userInfo DROP INDEX userName;
DROP INDEX userName ON userInfo;
#查看索引
SHOW INDEX FROM userInfo;
#添加唯一索引(MySQL将根据第一个索引列自动赋一个索引名)
ALTER TABLE userInfo ADD UNIQUE (tel, userName, email);
#查看索引
SHOW INDEX FROM userInfo;
#添加主键索引
ALTER TABLE userInfo ADD PRIMARY KEY(id);
#查看索引
SHOW INDEX FROM userInfo;
#删除主键索引
ALTER TABLE userInfo DROP PRIMARY KEY;
#查看索引
SHOW INDEX FROM userInfo;

#
DESC userInfo;
#查看索引
SHOW INDEX FROM userInfo;
#
ALTER TABLE userInfo DROP INDEX tel;
DROP INDEX address ON userInfo;
#查看索引
SHOW INDEX FROM userInfo;
#CREATE INDEX可对表增加普通索引或UNIQUE索引
/*
表名、索引名和字段列表具有与ALTER TABLE语句中相同的含义，索引名不可选
还有一点要注意：不能用CREATE INDEX语句创建PRIMARY KEY索引
*/
CREATE INDEX index_salary_address ON userInfo (salary, address);
CREATE UNIQUE INDEX index_email_tel_userName ON userInfo (email, tel, userName);
#查看索引
SHOW INDEX FROM userInfo;
#添加一列
alter table userInfo add column hobby varchar(200);
#
DESC userInfo;
#
CREATE INDEX index_test ON userInfo (`password`);
#报错[Err] 1061 - Duplicate key name 'index_test' (提示索引名重复了)
#CREATE INDEX index_test ON userInfo (hobby);
CREATE INDEX index_test2 ON userInfo (hobby);
#查看索引
SHOW INDEX FROM userInfo;
#删除索引
ALTER TABLE userInfo DROP INDEX index_test;
#查看索引
SHOW INDEX FROM userInfo;
#添加普通索引(此时有冗余索引，就是在某个列上，可能存在多个索引)
CREATE INDEX index_test ON userInfo (`password`, hobby);#hobby字段上存在多个索引
#查看索引
SHOW INDEX FROM userInfo;

#
CREATE TABLE testMyIndex2(
id int,
address VARCHAR(130),
email VARCHAR(40),
hobby VARCHAR(160),
userName VARCHAR(50)
);
#
DESC testMyIndex2;
#添加索引
ALTER TABLE testMyIndex2 ADD INDEX index_address (address);#添加普通索引
ALTER TABLE testMyIndex2 ADD UNIQUE (email);#添加唯一索引
ALTER TABLE testMyIndex2 ADD PRIMARY KEY (id);#添加主键索引
CREATE INDEX index_hobby ON testMyIndex2 (hobby);#添加普通索引
CREATE UNIQUE INDEX index_userName ON testMyIndex2 (userName);#添加唯一索引
#
DESC testMyIndex2;
#查看索引
SHOW INDEX FROM testMyIndex2;
SHOW KEYS FROM testMyIndex2;

/*
插入完了以后能够通过运行【SELECT @@IDENTITY】获取上一条插入语
句中生成的自增长字段的值。
*/
SELECT @@IDENTITY;

/*
基于单个连接客户端之间所执行的insert语句最近一条，而且客户端之
间是不会影响的，它是连接级别的函数，只对当前用户的连接有效
*/
SELECT LAST_INSERT_ID();

#使用表分区有个前提就是你的数据库必须支持。执行下面命令查看是否支持
#显示 partition |active|storage engine | null |gpl | #表示支持
#水平分区(将表的数据按行分割成不同的数据文件)
#垂直分区(将表的数据按列分割成不同的数据文件)
SHOW PLUGINS;

#修改表时添加默认约束
#
ALTER TABLE testMyIndex2 MODIFY address VARCHAR(115) DEFAULT '江西省赣州市于都县';
SELECT * FROM testMyIndex2;
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (1, 'ling@qq.com', '喝酒', '令狐冲');
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (4, 'yang@qq.com', DEFAULT, '打架', '杨过');
SELECT * FROM testMyIndex2;
#
ALTER TABLE testMyIndex2 ALTER COLUMN address SET DEFAULT '中国江西省于都县';
SELECT * FROM testMyIndex2;
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (2, 'wei@qq.com', '喜欢瞎掰', '韦小宝');
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (3, 'zhang@qq.com', DEFAULT, '练武功', '张无忌');
SELECT * FROM testMyIndex2;

#删除默认约束(有2种方式)
#删除默认约束(方式1)
ALTER TABLE testMyIndex2 MODIFY address VARCHAR(90);
#
DESC testMyIndex2;
SELECT * FROM testMyIndex2;
#插入数据时，不会报错，address列为null
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (5, 'guo@qq.com', DEFAULT, '看书', '郭靖');
SELECT * FROM testMyIndex2;

#删除默认约束(方式2)
ALTER TABLE testMyIndex2 ALTER COLUMN address DROP DEFAULT;
#
DESC testMyIndex2;
/*如果是通过第2种方式删除默认约束，那么如下这样插入数
据时会报错1364 - Field 'address' doesn't have a default value
*/
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (6, 'huang@qq.com', DEFAULT, '聪明伶俐', '黄蓉');
SELECT * FROM testMyIndex2;

#
DESC testMyIndex2;
#正确插入数据
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (7, 'jian@qq.com', '紫禁城', '嚣张跋扈', '建宁公主');

/*如果是通过第2种方式删除默认约束，那么如下这样插入数
据时会报错1364 - Field 'address' doesn't have a default value
*/
INSERT INTO testMyIndex2(id, email, hobby, userName) VALUES (8, 'shuang@qq.com', '武功', '双儿');
#
DESC testMyIndex2;
#可以查一下建表的信息，分析一下为什么上面的那条INSERT语句为什么无法插入数据，为什么报错?
SHOW CREATE TABLE testMyIndex2;
#
SELECT * FROM testMyIndex2;
#
INSERT INTO testMyIndex2(id, email, address, hobby, userName) VALUES (9, 'mu@qq.com', '沐王府', '小郡主', '沐剑屏');
#
SELECT * FROM testMyIndex2;

#查看表结构
DESC testMyIndex2;
#查看生成表的SQL
SHOW CREATE TABLE testMyIndex2;