MySQL基础

单进程多线程：    用户连接：连接线程       MySQL数据文件类型：        数据文件、索引文件        重做日志、撤消日志、二进制日志、错误日志、查询日志、慢查询日志、(中继日志)DDL & DML:    索引管理：            按特定数据结构存储的数据；        索引类型：            聚集索引、非聚集索引：数据是否与索引存储在一起；            主键索引、辅助索引            稠密索引、稀疏索引：是否索引了每一个数据项；            B+ TREE、HASH、R TREE            简单索引、组合索引            左前缀索引            覆盖索引        管理索引的途径：            创建索引：创建表时指定；CREATE INDEX            创建或删除索引：修改表的命令            删除索引：DROP INDEX            查看表上的索引：                SHOW {INDEX | INDEXES | KEYS}                    {FROM | IN} tbl_name                    [{FROM | IN} db_name]                    [WHERE expr]        索引的创建可以使用ALTER、CREATE命令创建：        mysql> CREATE INDEX name ON student (name);        Query OK, 0 rows affected (0.12 sec)        Records: 0  Duplicates: 0  Warnings: 0        mysql> SHOW INDEXES FROM student;        +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+        | Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |        +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+        | student |          0 | PRIMARY  |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |        | student |          1 | name     |            1 | name        | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |        +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+        2 rows in set (0.00 sec)        mysql>            EXPLAIN   #确认是否有使用到索引进行查询。            mysql> EXPLAIN SELECT * FROM students WHERE id=3;            mysql> select * from student;            +----+----------+---------------+------+            | id | name     | register_date | sex  |            +----+----------+---------------+------+            |  1 | ZhanYang | 2018-06-20    | NULL |            |  4 | LiuJia   | 2018-05-30    | NULL |            |  8 | JiaLiu   | 2018-06-20    | NULL |            |  9 | JiaLiu   | 2018-04-20    | NULL |            | 10 | gaoyf    | 2018-04-20    | NULL |            | 11 | zhujh    | 2018-04-20    | NULL |            | 12 | zhouha   | 2018-04-20    | NULL |            | 13 | hanzb    | 2018-04-21    | M    |            | 16 | ZhanXing | 2018-05-21    | M    |            | 17 | XingYan  | 2018-05-21    | M    |            +----+----------+---------------+------+            10 rows in set (0.01 sec)            mysql> EXPLAIN SELECT * FROM student  WHERE id=4;            +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+            | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra |            +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+            |  1 | SIMPLE      | student | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |            +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+            1 row in set (0.01 sec)            mysql> EXPLAIN SELECT * FROM student  WHERE id=4\G            *************************** 1. row ***************************                       id: 1              select_type: SIMPLE                    table: student                     type: const            possible_keys: PRIMARY                      key: PRIMARY                  key_len: 4                      ref: const                     rows: 1                    Extra: NULL            1 row in set (0.00 sec)        mysql> EXPLAIN SELECT * FROM student WHERE name LIKE '%X%';        +----+-------------+---------+------+---------------+------+---------+------+------+-------------+        | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |        +----+-------------+---------+------+---------------+------+---------+------+------+-------------+        |  1 | SIMPLE      | student | ALL  | NULL          | NULL | NULL    | NULL |   10 | Using where |        +----+-------------+---------+------+---------------+------+---------+------+------+-------------+        1 row in set (0.00 sec)        mysql> EXPLAIN SELECT * FROM student WHERE name LIKE '%X%'\G        *************************** 1. row ***************************                   id: 1          select_type: SIMPLE                table: student                 type: ALL        possible_keys: NULL                  key: NULL              key_len: NULL                  ref: NULL                 rows: 10                Extra: Using where        1 row in set (0.01 sec)    DESC命令用于获取数据表结构