linux mysql 查询数据库字符集_linux系统mysql字符集

一、select高级用法

1.传统连接

世界上小于100人的城市在哪个国家？是用什么语言？

#1.分析要哪些内容？

城市的人口数量 城市名字 国家名字 国家语言

#2.分析数据所在库

city.population city.name country.name countrylanguage.language

#3.找出三个表相关联内容

city.countrycode country.code countrylanguage.countrycode

#3.编写语句

select country.name,city.name,city.population,countrylanguage.language from city,country,countrylanguage where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population < 100;

2.自连接

#自己寻找两个表相关联的字段和数据

1.两个表字段必须完全相同

2.两个表字段下的数据必须完全相同

SELECT city.name,city.countrycode,countrylanguage.language,city.population

FROM city NATURAL JOIN countrylanguage

WHERE population < 100

ORDER BY population;

3.内连接

1)语法格式

select * from 表1 join 表2 on 相关联的条件 where 条件;

#注意：命中率(驱动的概念)

表1 小表

表2 大表

select * from 表1 inner join 表2 on 相关联的条件 where 条件;

2)例子1：两表联查

#小于100人的城市在哪个国家，国家代码是什么？

select city.name,city.population,city.countrycode,country.name

from city join country on city.countrycode=country.code

where city.population < 100;

3)例子2：三表联查

#世界上小于100人的城市在哪个国家？是用什么语言？

select country.name,city.name,city.population,countrylanguage.language

from city join country on city.countrycode=country.code

join countrylanguage on country.code=countrylanguage.countrycode

where city.population < 100;

4.外连接(有问题)

1)左外连接

select city.name,city.countrycode,country.name,city.population

from city left join country

on city.countrycode=country.code

and city.population < 100;

2)右外连接

select city.name,city.countrycode,country.name,city.population

from city right join country

on city.countrycode=country.code

and city.population < 100;

二、字符集

1.什么是字符集

#字符集：是一个系统支持的所有抽象字符的集合。字符是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

最早的字符集：ASCII码

中国的字符集：gbk，utf8，gbk2312

日本字符集：shift-JIS

韩国字符集：Euc-kr

万国编码：Unicode字符集

#常用的字符集：

gbk:一个汉字占用2个字节

utf8:一个汉字占用3个字节

utf8mb4:一个汉字占用4个字节

#字符集修改要求：

包含关系才可以修改

#查看字符集：

mysql> show charset;

2.校验规则

#查看校验规则

mysql> show collation;

| latin7_general_ci | latin7 |

| latin7_general_cs | latin7 |

| latin7_bin | latin7 |

#校验规则区别

1.以ci结尾：不区分大小写

2.以cs或者bin结尾：区分大小写

#大小写不同，相同字段也不能添加

mysql> alter table city add nAME varchar(10);

ERROR 1060 (42S21): Duplicate column name 'nAME'

3.统一字符集

#1.xshell字符集

#2.linux系统字符集

#临时修改

[root@db03 ~]# LANG=zh_CN.UTF-8

#永久修改

[root@db03 ~]# vim /etc/sysconfig/i18n#Centos6

[root@db03 ~]# vim /etc/locale.conf #Centos7

LANG="zh_CN.UTF-8"

#3.数据库字符集

1)cmake 字符集指定

cmake .

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

2)配置文件

[root@db03 ~]# vim /etc/my.cnf

[mysqld]

character-set-server=utf8

#4.创建数据库时指定字符集

mysql> create database db7 charset utf8;

Query OK, 1 row affected (0.00 sec)

mysql> show create database db7;

+----------+--------------------------------------------------------------+

| Database | Create Database |

+----------+--------------------------------------------------------------+

| db7 | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+--------------------------------------------------------------+

1 row in set (0.00 sec)

#5.建表时根据库的字符集来建表

mysql> create table tb1(id int);

#6.指定字符集建表

mysql> create table tb3(id int) charset utf8mb4;

Query OK, 0 rows affected (0.01 sec)

mysql> show create table tb3;

+-------+------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------+

| tb3 | CREATE TABLE `tb3` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

+-------+------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

#7.修改库的字符集

mysql> show create database db7;

+----------+--------------------------------------------------------------+

| Database | Create Database |

+----------+--------------------------------------------------------------+

| db7 | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+--------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter database db7 charset gbk;

Query OK, 1 row affected (0.00 sec)

mysql> show create database db7;

+----------+-------------------------------------------------------------+

| Database | Create Database |

+----------+-------------------------------------------------------------+

| db7 | CREATE DATABASE `db7` /*!40100 DEFAULT CHARACTER SET gbk */ |

+----------+-------------------------------------------------------------+

1 row in set (0.00 sec)

#8.修改表的字符集

mysql> show create table tb2;

+-------+---------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------+

| tb2 | CREATE TABLE `tb2` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

mysql> alter table tb2 charset gbk;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table tb2;

+-------+--------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------+

| tb2 | CREATE TABLE `tb2` (

`id` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=gbk |

+-------+--------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

三、索引

1.什么是索引

1.索引就好比一本书的目录，它能让你更快的找到自己想要的内容。

2.让获取的数据更有目的性，从而提高数据库检索数据的性能。

2.索引的种类

1.BTREE: B+树索引(Btree，B+tree，B*tree)

2.HASH：HASH索引(memery存储引擎支持)

3.FULLTEXT：全文索引(myisam存储引擎支持)

4.RTREE：R树索引

1)Btree索引

2)B+tree索引

3)B*tree索引

3索引根据算法分类

索引是建立在数据库字段上面的

当where条件后面接的内容有索引的时候，会提高速度

1)主键索引(聚集索引)

#创建表的时候创建主键索引

mysql> create table test(id int not null auto_increment primary key comment '学号');

Query OK, 0 rows affected (0.04 sec)

mysql> create table test1(id int not null auto_increment,primary key(id));

Query OK, 0 rows affected (0.04 sec)

#查看索引命令

mysql> show index from test;

#已经有表时添加主键索引

mysql> alter table student add primary key pri_id(id);

2)唯一键索引

#创建表的时候创建唯一键索引

mysql> create table test2(id int not null auto_increment unique key comment '学号');

Query OK, 0 rows affected (0.04 sec)

#已经有表时添加唯一键索引

mysql> alter table student add unique key uni_key(name);

#注意：创建唯一建索引或主键索引的列不能有重复数据

判断一列能否做唯一建索引

1.查询数据总量

mysql> select count(name) from city;

2.去重查看该列数据总量

mysql> select count(distinct(name)) from city;

#以上两个值相等则可以设置唯一建索引

例：

#1.查看列的总数据量

mysql> select count(name) from country;

+-------------+

| count(name) |

+-------------+

| 239 |

+-------------+

1 row in set (0.00 sec)

#2.查看去重后数据量

mysql> select count(distinct(name)) from country;

+-----------------------+

| count(distinct(name)) |

+-----------------------+

| 239 |

+-----------------------+

1 row in set (0.00 sec)

#3.创建唯一建索引

mysql> alter table country add unique key uni_key(name);

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

3)普通索引(辅助索引)

mysql> alter table city add index inx_name(name);

Query OK, 0 rows affected (0.14 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> create index index_District on city(District);

Query OK, 0 rows affected (0.06 sec)

Records: 0 Duplicates: 0 Warnings: 0

4)全文索引

mysql> create table txt(id int,bookname varchar(12),wenzhang text,fulltext(wenzhang));

Query OK, 0 rows affected (0.20 sec)

mysql> select * from txt where match(wenzhang) against('查询的内容');

#实例

mysql> create table text(id int,bookname varchar(12) charset utf8,wenzhang text charset utf8,fulltext(wenzhang));

Query OK, 0 rows affected (0.21 sec)

mysql> insert into text values(1,'红楼梦','上回书说到张飞长坂坡三打白骨精救出宋江');

Query OK, 1 row affected (0.01 sec)

mysql> select * from text;

+------+-----------+-----------------------------------------------------------+

| id | bookname | wenzhang |

+------+-----------+-----------------------------------------------------------+

| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |

+------+-----------+-----------------------------------------------------------+

1 row in set (0.00 sec)

mysql> select * from text where match(wenzhang) against('上回书说到张飞长坂坡三打白骨精救出宋江');

+------+-----------+-----------------------------------------------------------+

| id | bookname | wenzhang |

+------+-----------+-----------------------------------------------------------+

| 1 | 红楼梦 | 上回书说到张飞长坂坡三打白骨精救出宋江 |

+------+-----------+-----------------------------------------------------------+

1 row in set (0.00 sec)

5)查看索引

#方式一：

mysql> show index from city;

#方式二：

mysql> desc city;

+-----+

| Key |

+-----+

| PRI |#主键索引

| MUL |#普通索引

| UNI |#唯一键索引

| MUL |

+-----+

6)删除索引

mysql> alter table city drop index index_District;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

1.索引作用

1)索引就好比一本书的目录，它能让你更快的找到自己想要的内容。

2)让获取的数据更有目的性，从而提高数据库检索数据的性能。

2. 索引的种类(算法)

​1)BTREE:B+树索引

2)HASH：HASH索引

3)FULLTEXT：全文索引

4)RTREE：R树索引

B-tree

B+Tree 在范围查询方面提供了更好的性能(> < >= <= like)

B*Tree

3. 索引管理

索引建立在表的列上(字段)的。

在where后面的列建立索引才会加快查询速度。

pages

3.1 索引分类

主键索引

普通索引

唯一索引

3.2 管理索引

#创建索引

alter table test add index index_name(name);

#创建索引

create index index_name on test(name);

#查看索引

desc table;

#查看索引

show index from table;

#删除索引

alter table test drop key index_name;

#添加唯一性索引

alter table student add unique key uni_xxx(xxx);

#查看表中数据行数

select count(*) from city;

#查看去重数据行数

select count(distinct name) from city;

3.3 唯一索引

db01 [world]>alter table city add unique index idx_uni1(name);

ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

统计city表中，以省的名字为分组，统计组的个数

select district,count(id) from city group by district;

需求: 找到world下,city表中 name列有重复值的行,最后删掉重复的行

db01 [world]>select name,count(id) as cid from city group by name having cid>1 order by cid desc;

db01 [world]>select * from city where name='suzhou';

3.4 前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));

注意：数字列不能用作前缀索引。

避免对大列建索引

如果有，就使用前缀索引

3.5 联合索引

多个字段建立一个索引

例：

where a.女生 and b.身高 and c.体重 and d.身材好

index(a,b,c)

特点：前缀生效特性

a,ab,ac,abc,abcd 可以走索引或部分走索引

b bc bcd cd c d ba ... 不走索引

原则：把最常用来做为条件查询的列放在最前面

#创建people表

create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));

#创建联合索引

alter table people add index idx_gam(gender,age,money);

4. 执行计划分析explain

4.1 介绍

(1) 获取到的是优化器选择完成的,他认为代价最小的执行计划. 作用: 语句执行前,先看执行计划信息,可以有效的防止性能较差的语句带来的性能问题. 如果业务中出现了慢语句，我们也需要借助此命令进行语句的评估，分析优化方案。

(2) select 获取数据的方法

全表扫描(应当尽量避免,因为性能低) 2. 索引扫描 3. 获取不到数据

4.2 全表扫描

1.全表扫描1)在explain语句结果中type为ALL

2)什么时候出现全表扫描?

2.1 业务确实要获取所有数据

2.2 不走索引导致的全表扫描

​ 2.2.1 没索引

​ 2.2.2 索引创建有问题

​ 2.2.3 语句有问题

生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

4.3索引扫描

1 常见的索引扫描类型:

1)index

2)range

3)ref

4)eq_ref

5)const

6)system

7)null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。

range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有查询。

mysql> alter table city add index idx_city(population);

mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;

mysql> explain select * from city where countrycode='chn';

mysql> explain select * from city where countrycode in ('CHN','USA');

mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

join B

on A.sid=B.sid

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

5. 建立索引的原则

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

那么索引设计原则又是怎样的?

5.1 索引设计原则

5.1.1 选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。

如果使用姓名的话，可能存在同名现象，从而降低查询速度。

优化方案:

(1) 如果非得使用重复值较多的列作为查询条件(例如:男女),可以将表逻辑拆分

(2) 可以将此列和其他的查询类,做联和索引

select count(*) from world.city;

select count(distinct countrycode) from world.city;

select count(distinct countrycode,population ) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

5.1.2 为经常需要排序、分组和联合操作的字段建立索引

例如:

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。

如果为其建立索引，可以有效地避免排序操作

5.1.3 为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。

因此，为这样的字段建立索引，可以提高整个表的查询速度。

3.1 经常查询

3.2 列值的重复值少

注：如果经常作为条件的列，重复值特别多，可以建立联合索引

5.1.4 尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。

例如，TEXT和BLOG类型的字段，进行全文检索

会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5.1.5 限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。

修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.1.6 删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理

员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

5.2 不走索引的情况

5.2.1没有查询条件，或者查询条件没有建立索引

#全表扫描

select * from table;

select * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。

1)对用户查看是非常痛苦的。

2)对服务器来讲毁灭性的。

3)SQL改写成以下语句：

#情况1

#全表扫描

select * from table;

#需要在price列上建立索引

selec * from tab order by price limit 10;

#情况2

#name列没有索引

select * from table where name='zhangsan';

1、换成有索引的列作为查询条件

2、将name列建立索引

5.2.2 查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;

1)如果业务允许，可以使用limit控制。

2)结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。

5.2.3 索引本身失效，统计数据不真实

索引有自我维护的能力。

对于表内容变化比较频繁的情况下，有可能会出现索引失效。

重建索引就可以解决

5.2.4 查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

#例子

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

5.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

mysql> create table test (id int ,name varchar(20),telnum varchar(10));

mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);

mysql> explain select * from test where telnum=120;

mysql> alter table test add index idx_tel(telnum);

mysql> explain select * from test where telnum=120;

mysql> explain select * from test where telnum=120;

mysql> explain select * from test where telnum='120';

5.2.6 <> ，not in 不走索引

mysql> select * from tab where telnum <> '1555555';

mysql> explain select * from tab where telnum <> '1555555';

单独的>,

or或in尽量改成union

EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');

#改写成

EXPLAIN SELECT * FROM teltab WHERE telnum='110'

UNION ALL

SELECT * FROM teltab WHERE telnum='119'

5.2.7 like "%_" 百分号在最前面不走

#走range索引扫描

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';

#不走索引

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';

%linux%类的搜索需求，可以使用Elasticsearch -------> ELK

5.2.8单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);

ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);

DESC t1

SHOW INDEX FROM t1

#走索引的情况测试

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30 AND sex='m';

#部分走索引

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND sex='m';

#不走索引

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=20

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';

EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';