MySQL数据库基础操作总结（适合小白快速入门）

---

一 常见操作指令

1 数据库对象操作

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS]db_name
[DEFAULT CHARACTER SET ='GBK'];

• 创建数据库
• —— 类型、名称
• —— 可选参数，表示如果不存在时创建
• —— 默认编码方式

查看数据库

SHOW WARNINGS; 显示警告

SHOW DATABASES;查看数据存在的

SHOW CREATE DATABASE;查看数据库的定义信息

修改指定数据库的编码方式

ALTER {DATABASE\|SCHMA}db\_name 
[DEFAULT CHARACTER SET = 'GBK'];

打开指定数据库

USE db_name; 打开的数据库名称

SELECT DATABASE\|SCHEMA();查看当前打开的而数据库名称

删除指定的数据库

DROP {DATABASE\|SCHEMA}[IF NOT EXISTS] db_name

2 数据表对象操作

定义

• 数据表用来存储数据的结构

• 由行和列构成的二维网络

创建数据表

CREATE TABLE [IF NOT EXISTS] tbl_name
(字段名称 字段类型[完整约束条件])
ENGINE =引擎名称 
CHARSET = 编码方式;

数据类型补充

字节数分别是1-2-3-4-8-1

- TINYINT
- SMALLINT
- MEDIUMAINT
- INT
- BIGINT
- BOOL
- BOOLEAN

浮点类型补充

FLOAT[(m,d)]

double[(m,d)]

decemal[m,d]

字符串类型

CHAR(M) M带变得存放的字符数，定长字符串

VARCHAR(M) 变长字符串

TEXT

SET("value","value")集合类型

ENUM("value","value")枚举类型

日期时间类型

TIME存储时间

DATE存储

DATETIME存储时间日期

TIMESTAMP存储时间戳

YEAR存储年份

3 存储引擎简介

存储引擎

指表的类型，表在计算机中怎样存储

SHOW ENGINES;

SHOW VARIABLES LIKE'storage_engin';

InnoDB 支持外接，存储.fim中，读写效率地，占用空间大

MyISAM 索引 结构 数据在三个文件中，占用空间小，快，但不支持事务

MEMORY 存储在内存中的内容创建表，对应磁盘文件，对磁盘快速处理。

二 排序和校验规则

1 基本概念

mysql数据库在做查询时候，有时候是英文字母大小写敏感的，有时候又不是的，主要是由mysql的字符校验规则的设置决定的，通常默认是不支持的大小写字母敏感的。

什么是字符集和校验规则？

字符集是一套符号和编码。校对规则是在字符集内用于比较字符的一套规则。任何一个给定的字符集至少有一个校对规则，它可能有几个校对规则。要想列出一个字符集的校对规则，使用SHOW COLLATION语句。

校对规则一般有这些特征：

1. 两个不同的字符集不能有相同的校对规则。
2. 每个字符集有一个默认校对规则。例如，utf8默认校对规则是utf8_general_ci。

命名规定

存在校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。不同级别的字符集和校验规则可控制大小写敏感

MySQL5.1在同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合定义字符串。字符集和校对规则有4个级别的默认设置：服务器级、数据库级、表级和连接级。

2 服务器级

1 修改配置文件

MySQL按照如下方法确定服务器字符集和服务器校对规则：

1. 修改配置文件/etc/my.cnf。
2. 在[mysqld]下添加：collation_server = utf8_bin。
3. 重启实例

更改服务器级的校验规则(collation_server )后，数据库校验规则(collation_collation)默认会继承服务器级的。

注意：
这个只适用于在重新启动之后, 新建的库,已存在的库不受影响.
同样的, 即使库的校验规则改了,已经存在的表不受修改影响;
同理与已经存在的列…

mysql> create databaseyutest0;
Query OK,1 row affected (0.00sec)
mysql> useyutest0;Databasechanged
mysql> create table t1 (name varchar(10));
Query OK,0 rows affected (0.01sec)
mysql> insert into t1 values('AAA');
Query OK,1 row affected (0.00sec)
mysql> insert into t1 values('aaa');
Query OK,1 row affected (0.01sec)
mysql> select * fromt1;
+------+
| name |
+------+
| AAA |
| aaa |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='aaa';
+------+
| name |
+------+
| aaa |
+------+
1 row in set (0.00 sec)

可以看出，在服务器级进行相应的校对规则设置，查询大小写敏感。

当服务器启动时根据有效的选项设置

当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和校对规则。

shell> mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci

更改设定值的一个方法是通过重新编译。如果希望在从源程序构建时更改默认服务器字符集和校对规则，使用：–with-charset和–with-collation作为configure的参量。例如：

shell> ./configure --with-charset=latin1 --with-collation=latin1_german1_ci

mysqld和configure都验证字符集/校对规则组合是否有效。如果无效，每个程序都显示一个错误信息，然后终止。

3 数据库级

规则说明

MySQL这样选择数据库字符集和数据库校对规则：

1. 如果指定了character set X和collate Y，那么采用字符集X和校对规则Y。
2. 如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。
3. 否则，采用服务器字符集和服务器校对规则。

测试验证

1. 创建数据库时设置数据库校验规则

mysql> create database yutest default character setutf8 collate utf8_bin;
Query OK,1 row affected (0.00sec)
mysql> show variables like 'collation_%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_bin |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00sec)
mysql> select * fromt1;
+------+
| name |
+------+
| ABC |
| abc |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='abc';
+------+
| name |
+------+
| abc |
+------+
1 row in set (0.01 sec)

可以看出，在数据库级进行相应的校对规则设置，查询大小写敏感。

4 表级

规则说明

MySQL按照下面的方式选择表字符集和校对规则：

1. 如果指定了character set X和collate Y，那么采用character set X和collate Y。
2. 如果指定了character set X而没有指定collate Y，那么采用character set X和character set X的默认校对规则。
3. 否则，采用数据库字符集和服务器校对规则。

测试验证

在创建表时设置表级校验规则：

mysql> create databaseyutest2;
Query OK,1 row affected (0.01sec)
mysql> useyutest2;Databasechanged
mysql> create table t1(name varchar(10))-> default character setutf8 collate utf8_bin;
Query OK,0 rows affected (0.01sec)
mysql> insert into t1 values('ABC');
Query OK,1 row affected (0.00sec)
mysql> insert into t1 values('abc');
Query OK,1 row affected (0.00sec)
mysql> show variables like 'collation_%';+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00sec)
mysql> select * fromt1;+------+
| name |
+------+
| ABC |
| abc |
+------+
2 rows in set (0.00sec)
mysql> select * from t1 where name='abc';+------+
| name |
+------+
| abc |
+------+
1 row in set (0.00 sec)

可以看出，在表级进行相应的校对规则设置，查询大小写敏感。

5 连接级

连接字符集

考虑什么是一个“连接”：它是连接服务器时所作的事情。客户端发送SQL语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集。对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决：

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

1. 当查询离开客户端后，在查询中使用哪种字符集？
   1. 服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。
2. 服务器接收到查询后应该转换为哪种字符集？
   1. 转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection。
3. 服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？
   1. character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据(如列名)。

在SQL语句中使用collate

创建数据库表时大小写不敏感，仍然有方法在查询时区分大小写

使用collate子句，能够为一个比较覆盖任何默认校对规则。collate可以用于多种SQL语句中，比如where，having，group by，order by，as，聚合函数。

mysql> select * from t1 where name collate utf8_bin = 'ABC';+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00sec)
mysql> select * from t1 where name = 'ABC';+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00sec)
mysql> select * fromt1;+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)

binary操作符

binary操作符是collate子句的一个速记符。binary ‘x’等价与’x’ collate y，这里y是字符集’x’二元校对规则的名字。每一个字符集有一个二元校对规则。例如，latin1字符集的二元校对规则是latin1_bin，因此，如果列a是字符集latin1，以下两个语句有相同效果：

select * from t1 order by binarya;select * from t1 order by a collate latin1_bin;
mysql> select * from t1 where binary name = 'ABC';+------+
| name |
+------+
| ABC |
+------+
1 row in set (0.00sec)
mysql>mysql> select * from t1 where name = 'ABC';+------+
| name |
+------+
| ABC |
| Abc |
| abc |
+------+
3 rows in set (0.00 sec)

析## 1 简介

在MySQL中，EXPLAIN命令的返回值是一个表格，包含了查询语句的执行计划。表格中的每一行代表了一个访问方式，每一列代表了一个属性，例如：

+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const|    1 |             |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

下面是表格中各个属性的含义：

• id: 查询的标识符，每个查询都有一个唯一的标识符。如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行

  • 
  • 

• select_type: 查询的类型，共有以下几种类型：

  • SIMPLE：简单select查询,查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子查询,最外层查询则被标记为primary
  • SUBQUERY：在select或where中包含了子查询
  • DERIVED：在from列表中包含的子查询被标记为derived(衍生)把结果放在临时表当
  • UNION：若第二个select出现的union之后,则被标记为union。若union包含在from子句的子查询中,外层select将被标记为deriver
  • UNION RESULT：从union表获取结果select,两个UNION合并的结果集在最后

• table: 此行正在访问的表。

• type: 表示访问类型，包括以下几种类型：

  • ALL: 全表扫描，将访问表的每一行数据。将全表进行扫描,从硬盘当中读取数据,如果出现了All 切数据量非常大, 一定要去做优化
  • index: 全索引扫描，将访问索引的每一行数据。index与All区别为index类型只遍历索引树,通常比All要快,
    因为索引文件通常比数据文件要小all和index都是读全表,
    但index是从索引中读取,all是从硬盘当中读取
  • range: 只检索给定范围的行,使用一个索引来选择行 一般就是在你的where语句中出现between<>\ in等查询,这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点.而结束语另一点,不用扫描全部索引。
  • ref: 非唯一性索引扫描。出现在一对多关系中，例如部门和员工。ref 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
  • eq_ref: 唯一索引引用，类似于ref，但使用的是唯一索引。eq_ref 唯一性索引扫描 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或唯一索引扫描
  • const: 表示通过索引一次就找到了。常量复杂度
  • system: 表中有一行记录(系统表) 这是const类型的特例,平时不会出现

• partitions：如果查询是基于分区表的话, 会显示查询访问的分区

• possible_keys: 可能使用的索引列表。

• key: 实际使用的索引。

  • 实际使用的索引,如果为NULL,则没有使用索引,查询中若使用了覆盖索引 ,则该索引仅出现在key列表possible_keys与key关系,理论应该用到哪些索引实际用到了哪些索引覆盖索引 查询的字段和建立的字段刚好吻合,
    这种我们称为覆盖索引

• key_len: 索引键使用的字节数。

• ref: 列与索引之间的匹配。索引是否被引入到, 到底引用到了哪几个索引

• rows: 表示MySQL估计需要扫描的行数。语句中出现了Using Filesort 和 Using Temporary说明没有使用到索引。出现 impossible where说明条件永远不成立

  • Extra: 额外的信息，包括以下几种类型：
  • Using filesort: MySQL需要额外排序。
  • Using temporary: MySQL需要创建临时表。
  • Using index: 查询使用了覆盖索引。
  • Using where: MySQL需要过滤部分数据。
  • Using join buffer: MySQL正在使用连接缓冲区。

• filtered满足查询的记录数量的比例，注意是百分比，不是具体记录数 . 值越大越好，filtered列的值依赖统计信息，并不十分准确

通过分析执行计划，可以发现哪些地方需要优化，例如是否可以添加索引、是否可以缩小查询范围等等。

2 实例

id值与执行顺序

• id值相同
  

• id值不同
  

• id值