mysql(二)

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

mysql(二)

原文：http://youenstudy.blog.51cto.com/6722910/1748214