第七章 数据库的字符集和相关索引
一、字符集
1.什么是字符集
#常用的字符集
gbk: 两字节
utf8：三个字节
utf8mb4：四个字节
#字符集转换
只有包含关系的字符集能够互相转换
#查看支持的字符集
mysql> show charset;
2.校验规则
#查看校验规则
mysql> show collation;
#检验规则种类
| latin1_bin
| latin1_general_ci
| latin1_general_cs
#区别
bin和cs区分大小写
ci不区分大小写
3.统一字符集
#1.xshell字符集
#2.linux系统的字符集
#永久修改：
1）centOS7：
[root@db01 ~]# vim /etc/locale.conf
LANG="zh_CN.UTF-8"
2）centOS6：
[root@db01 ~]# vim /etc/sysconfig/i18n
LANG="zh_CN.UTF-8"
#临时修改：
[root@db01 ~]# LANG="zh_CN.UTF-8
#3.cmake阶段指定字符集
#使用utf8字符集
-DDEFAULT_CHARSET=utf8
#校验规则
-DDEFAULT_COLLATION=utf8_general_ci
#4.配置文件可以修改默认的字符集
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
character-set-server=utf8
#5.创建数据库时指定字符集
mysql> create database zifuji3 charset gbk;
#6.在库下建表
在库下面建表，表的字符集随着库走，不走默认与cmake的字符集
#7.建表时指定字符集
mysql> create table test2(id int) charset utf8mb4;
#8.修改已有表的字符集
mysql> alter table test charset utf8mb4;
二、索引概述
1.什么是索引
1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2.让获取的数据更有目的性，从而提高数据库检索数据的性能。
2.索引的种类
1.BTREE: BTREE B+TREE B*TREE
2.HASH：HASH索引（只有memery存储引擎支持）
3.FULLTEXT：全文索引
4.RTREE：R树索引
1）BTREE树索引
2）B+TREE树索引
3）B*TREE树索引
三、根据算法索引的分类
索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages <---- 索引（属性）<---- 查数据。
1.主键索引（聚集索引）
#创建主键索引
1.建表时创建主键索引
1）方法1：
mysql> create table student(id int unsigned primary key auto_increment comment '学生id');
2）方法2：
mysql> create table student1(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', PRIMARY KEY (id));
2.已经有的表，添加主键索引
#建表
mysql> create table student2(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名');
#添加主键索引
mysql> alter table student2 add primary key pri_key(id);
2.唯一建索引
1）创建表时添加唯一键索引
1.方法1：
mysql> create table student3(id int unsigned NOT NULL unique key COMMENT '学生id');
2.方法2：
mysql> create table student4(id int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生id', unique key (id));
2）已经有的表，添加唯一键索引
#建表
mysql> create table student5(id int unsigned NOT NULL COMMENT '学生id',name varchar(10) NOT NULL COMMENT '学生姓名');
#添加主键索引
mysql> alter table student2 add unique key uni_key(id);
3）如何确定哪一列可以创建唯一键
#1.查看表中某一字段的总数据
mysql> select count(name) from country;
+-------------+
| count(name) |
+-------------+
| 239 |
+-------------+
1 row in set (0.00 sec)
#2.去重查看表中某一列数据
mysql> select distinct(name) from country;
mysql> select distinct(count(name)) from country;
+---------------+
| (count(name)) |
+---------------+
| 239 |
+---------------+
1 row in set (0.00 sec)
#3.查看以上两个值是否完全相同
#4.如果相等则可以创建唯一建索引
mysql> alter table country add unique key uni_key(name);
4）函数
now() #当前时间的函数
database() #查看当前库的函数
count() #查看数据总数的函数，count(主键)
distinct() #去重数据的函数
PASSWORD() #数据库密码加密函数
max() #取某一列最大值
min() #取某一列最小值
avg() #取某一列平均值
sum() #取某一列总数
3.普通索引（辅助索引）
1）创建索引
1.方法1：
mysql> alter table city add index index_key(name);
2.方法2：
mysql> create index idx_key on city(District);
4.全文索引
1）建表时创建全文索引
mysql> create table xiaoshuo(id int,bookname varchar(20),author varchar(10),content text,FULLTEXT(content));
2）插入数据
mysql> insert into xiaoshuo values('1','西游演义','lhd','上回书说到张飞长坂坡三打白骨精救宋江');
Query OK, 1 row affected (0.00 sec)
mysql> select * from xiaoshuo;
+------+--------------+--------+--------------------------------------------------------+
| id | bookname | author | content |
+------+--------------+--------+--------------------------------------------------------+
| 1 | 西游演义 | lhd | 上回书说到张飞长坂坡三打白骨精救宋江 |
+------+--------------+--------+--------------------------------------------------------+
1 row in set (0.00 sec)
3）使用索引查询数据
mysql> select * from xiaoshuo where match(content) against('上回书说到张飞长坂坡三打白骨精救宋江');
+------+--------------+--------+--------------------------------------------------------+
| id | bookname | author | content |
+------+--------------+--------+--------------------------------------------------------+
| 1 | 西游演义 | lhd | 上回书说到张飞长坂坡三打白骨精救宋江 |
+------+--------------+--------+--------------------------------------------------------+
1 row in set (0.00 sec)
5.查看索引
1.方式1：
mysql> desc city;
+-------------+----------+------+-----+
| Field | Type | Null | Key |
+-------------+----------+------+-----+
| ID | int(11) | NO | PRI | #主键索引
| Name | char(35) | NO | MUL | #普通索引
| CountryCode | char(3) | NO | UNI | #唯一键索引
| District | char(20) | NO | MUL |
| Population | int(11) | NO | |
+-------------+----------+------+-----+
2.方式2：
mysql> show index from city;
6.删除索引
mysql> alter table city drop index idx_key;
四、根据设置方法分类
1.创建索引时会对数据进行重新排序
2.建立索引会占用磁盘空间，所以索引不是越多越好
3.在同一列避免创建多个索引
4.避免在大数据的列创建索引，如果非要创建就创建前缀索引
1.前缀索引
#创建根据District列前三个字符进行排序的索引
mysql> alter table city add index District_key(District(3));
#查看表的索引
mysql> show index from city;
2.联合索引
1）案例
#1.创建一个库
mysql> create database xiangqin;
#2.创建一个表
mysql> use xiangqin;
mysql> create table user(id int,name varchar(10),sex enum('male','fmale'),age tinyint,weight int,height int,money int,look varchar(10));
#3.插入数据
mysql> insert into user values('1','qiudao','male','38','180','120','-100000','very ugly'),('2','dawei','male','18','130','182','10000','Handsome'),('3','bingbing','fmale','30','110','170','100000000','beautiful');
#4.查看数据
mysql> select * from user;
+------+----------+-------+------+--------+--------+-----------+-----------+
| id | name | sex | age | weight | height | money | look |
+------+----------+-------+------+--------+--------+-----------+-----------+
| 1 | qiudao | male | 38 | 180 | 120 | -100000 | very ugly |
| 2 | dawei | male | 18 | 130 | 182 | 10000 | Handsome |
| 3 | bingbing | fmale | 30 | 110 | 170 | 100000000 | beautiful |
+------+----------+-------+------+--------+--------+-----------+-----------+
3 rows in set (0.00 sec)
#5.建立联合索引
mysql> alter table user add index index_all(sex,age,money,look);
#6.查看索引
mysql> show index from user;
2）注意
where sex='' and age='' and money='' and look='';
index(sex,age,money,look)
特点：前缀生效特性
#可以走索引
sex
sex,age
sex,age,money
sex,age,money,look
#部分走索引：
只要包含联合索引的第一个条件的查询语句都部分走索引
#不走索引的情况
age,money,look
age,look
money,look
money look
age,sex ...
#原则：把最常用来做为条件查询的列放在最前面
