[MySQL] 零基础学MySQL 08

本博客是【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门视频的学习笔记

自增长

一个问题：在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动地增长，该如何处理呢？

字段名 整形 primary key auto_increment

添加自增长字段的方式
如果字段1是自增长的，给字段1赋值要写null，或者不给字段1赋值。

insert into xxx (字段1,字段2......) values(null,'值'......);
insert into xxx (字段2,字段3......) values('值','值'......);
insert into xxx values(null,'值1','值2'......);

---

#自增长
CREATE TABLE t24
	(id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '' ,
	`name` VARCHAR(32) NOT NULL DEFAULT ''
	)
DESC t24;
#测试自增长的使用
INSERT INTO t24 (id,email,`name`) VALUES(NULL,'jack@qq.com','jack');
INSERT INTO t24 VALUES(NULL,'tom@qq.com','tom');
#如果不是自增长的话，下面这个语法是错误的
INSERT INTO t24 (email,`name`) VALUES('lhq@qq.com','lhq');
SELECT * FROM t24;

---

自增长使用细节：

1. 一般来说，自增长和主键配合使用
2. 自增长也可以单独使用，但是需要配合一个unique
3. 自增长修饰的字段为整数类型的（虽然小数也可以但非常少这样使用）
4. 自增长默认从1开始，你也可以通过如下命令修改

ALTER TABLE t25 AUTO_INCREMENT=100;

5.如果添加数据时，给自增长字段（列）指定的有值，则以指定的值为准。如果指定了自增长，一般来说，就按照自增长的规则来添加数据

INSERT INTO t25 VALUES(666,'lhq@qq.com','lhq');

6.如果设置了自增长，删除掉某条记录后，自增长不会自动填补，会在删除数据的id上加一

MySQL索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高百倍千倍。

这里我们举例说明索引的好处【构建海量表 有8000000条数】

我们已经提前准备好了一个数据库和海量表。

#在没有创建索引时，我们查询一条记录
SELECT * 
	FROM emp
	WHERE empno=1234567

在没有创建索引前，emp.ibd文件大小是524,288KB

#使用索引来优化一下，体验索引有多牛
#empno_index索引名称
#ON emp (empno) 表示在emp表的empno列创建索引
CREATE INDEX empno_index ON emp (empno)

创建索引后，emp.ibd文件大小是655,360KB

得出结论，创建的索引本身也会占用磁盘空间。

这里联想到“以空间换时间”的算法思想。

SELECT * 
	FROM emp
	WHERE empno=1234568

提升速度非常显著！

---

我们在ename上没有创建索引，那么查询ename时依然很慢

#创建索引后，只对创建了索引的列有效
SELECT * FROM emp WHERE ename='axJxCT';

索引机制

索引的原理

图片来自【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门

索引的代价
1.磁盘占用会变大
2.对update delete insert语句的效率会有影响
以删除为例，删除一条数据会导致整个索引数据结构的改变，需要重新更新调整数据结构，所以对速度会有影响。

在项目中，select操作比update、delete、insert多得多。

创建索引

索引的类型

1. 主键索引 如果某个列是主键，那它自然就是主索引 (primary key)

CREATE TABLE t1(
	id int primary key,#主键，同时也是索引，称为主键索引
	name varchar(32));

2. 唯一索引 (unique)

CREATE TABLE t2(
	id int unique,#id是唯一的，同时也是索引，称为unique索引
	name varchar(32));

3. 普通索引 (index)

4. 全文索引 (fulltext) [适用于MyISAM]
    一般开发不使用Mysql自带的全文索引，而是使用全文搜索 Solr 和 ElasticSearch （ES）

#演示MySQL索引的使用
#创建索引
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32));
#查询表是否有索引
SHOW INDEXES FROM t26;
#添加唯一索引
CREATE UNIQUE INDEX id_index ON t26 (id);
#添加普通索引
create index id_index on t26(id);
#如何选择唯一索引和普通索引
#1.如果某列的值是不会重复的，则优先考虑unique索引，否则使用普通索引
#添加普通索引的另一个方法
-- alter table t26 add index id_index (id)

#添加主键索引
#1.建表时指定primary key
#2.alter table t26 add primary key(id);
ALTER TABLE t26 ADD PRIMARY KEY(id);

删除索引、查询索引

#删除索引
DROP INDEX id_index ON t26;
SHOW INDEX FROM t26;
#删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;

#修改索引就是先删除，再添加新的索引

#查询索引
#1.
SHOW INDEX FROM t26;
#2.
SHOW INDEXES FROM t26;
#3.
SHOW KEYS FROM t26;
#4.
DESC t26;

---

练习：建立主键索引
要求：创建一张订单表order（id，商品名，订购人，数量）要求id号为主键，请使用两种方式来创建主键。

CREATE TABLE order1(
	id INT PRIMARY KEY,
	goods_name VARCHAR(32),
	order_people VARCHAR(32),
	nums INT);
SHOW INDEXES FROM order1;

CREATE TABLE order2(
	id INT,
	goods_name VARCHAR(32),
	order_people VARCHAR(32),
	nums INT);
ALTER TABLE order2 ADD PRIMARY KEY(id);
SHOW INDEXES FROM order1;

练习：建立唯一索引
要求：创建一张特价菜谱表menu（id，菜谱名，厨师，点餐人身份证，价格）要求id号为主键，点餐人身份证是unique，请使用两种方式来创建unique

CREATE TABLE menu1(
	id INT PRIMARY KEY,
	food_name VARCHAR(32),
	cook_name VARCHAR(32),
	customer_idcard VARCHAR(32) UNIQUE,
	price INT);
SHOW INDEXES FROM menu1;

CREATE TABLE menu2(
	id INT PRIMARY KEY,
	food_name VARCHAR(32),
	cook_name VARCHAR(32),
	customer_idcard VARCHAR(32),
	price INT);
CREATE UNIQUE INDEX customer_idcard_index ON menu2(customer_idcard);
SHOW INDEXES FROM menu2;

练习：创建普通索引
要求：创建一张运动员表sportman（id，名字，特长）要求id号为主键，名字为普通索引。请使用三种方式来创建索引。

CREATE TABLE sportman1(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
CREATE INDEX name_index ON sportman1(`name`);
SHOW INDEXES FROM sportman1;

CREATE TABLE sportman2(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
ALTER TABLE sportman2 ADD INDEX name_index(`name`); 
SHOW INDEXES FROM sportman2;

CREATE TABLE sportman3(
	id INT,
	`name` VARCHAR(32),
	special_skill VARCHAR(32));
ALTER TABLE sportman3 ADD PRIMARY KEY (id);
CREATE INDEX name_index ON sportman3(`name`);
SHOW INDEXES FROM sportman3;

创建索引的规则

小结：在哪些列上适合使用索引

1. 较频繁的作为查询条件的字段应该创建索引
   select * from emp where empno=1
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
   例如：性别
3. 更新非常频繁的字段不适合创建索引
   select * from emp where logincount=1
4. 不会出现在WHERE子句中的字段不该被创建索引