mysql的工作流程：
用户请求连接管理器，创建线程，通过线程管理器进行管理（初次链接），在用户模块检查权限进行验证，通过命令派发器，通过HASH键值查询缓存，如果有就返回用户，如果没有就分析器分析选择模块，转发到操作模块， 在访问控制中查询是否有操作权限，有的交由表管理器对存储引擎接口的调用；最终返回记录日志。
索引：通过算法将数据排序，并通过内存快速查询数据，一般使用在大量数据上。
注意：经常不变的数据建立索引，当服务正忙时不加索引，数量条目少不加索引。
索引不能加‘%X%’
语法：
MariaDB [hellodb]> help show index;
Name: 'SHOW INDEX'
Description:
Syntax:
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
查看索引
MariaDB [hellodb]> show indexes from students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
查询索引
MariaDB [hellodb]> explain select * from students where StuID=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where Age=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25
Extra: Using where
1 row in set (0.00 sec)
修改索引
MariaDB [hellodb]> alter table students add index(Age);
Query OK, 25 rows affected (0.05 sec)
Records: 25 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> explain select * from students where Age=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)
MariaDB [hellodb]> show indexes from students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: Age
Seq_in_index: 1
Column_name: Age
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
另一种创建索引：
MariaDB [hellodb]> create index name on students (Name);
Query OK, 25 rows affected (0.04 sec)
Records: 25 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> show indexes from students\G
*************************** 1. row ***************************
Table: students
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: StuID
Collation: A
Cardinality: 25
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: students
Non_unique: 1
Key_name: Age
Seq_in_index: 1
Column_name: Age
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: students
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where Name like 'X%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
+-------+-------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where Name like 'X%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: range
possible_keys: name
key: name
key_len: 152
ref: NULL
rows: 6
Extra: Using index condition
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where Name like '%X%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25
Extra: Using where
视图：相当于超链接。如果改数据取决基表的约束。
MariaDB [hellodb]> create view test AS select StuID,Name,Age from students;
Query OK, 0 rows affected (0.31 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| test |
| toc |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> explain select * from test;
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 25 | |
+------+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from test\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25
Extra:
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from test where Age=22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 2
Extra:
1 row in set (0.01 sec)
MariaDB [hellodb]> show table status like 'test'\G
*************************** 1. row ***************************
Name: test
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
DML: INSERT, DELETE, UPDATE, SELECT
INSERT：一次插入一行或多行数据；
批量修改后在做索引；表名区分大小写
语法：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] {VALUES | VALUE} ({expr | DEFAULT},...),(...),...[ ON DUPLICATE KEY UPDATE ]
ON DUPLICATE KEY UPDATE:数据重复更新不报错
简化：INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)
MariaDB [hellodb]> insert students (Name,Age,Gender) values ('jingjiao king',100,'F');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | jingjiao king | 100 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
MariaDB [hellodb]> select * from students where Age=100;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | jingjiao king | 100 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where Age=100;
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | students | ref | Age | Age | 1 | const | 2 | |
+------+-------------+----------+------+---------------+------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> explain select * from students where Age=100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 2
Extra:
1 row in set (0.00 sec)
DELETE:DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制条件，否则将清空表中的所有数据；
限制条件：
WHERE;LIMIT
UPDATE：UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
注意：一定要有限制条件，否则将修改所有行的指定字段；
限制条件：
WHERE;LIMIT
