mysql基本操作

创建数据库、使用数据库、查看表格：

MariaDB [mydb]> create database mydb;                           <==创建数据库
MariaDB [mydb]> use mydb;                                       <==使用数据库
MariaDB [mydb]> show tables;                                    <==查看表格
+----------------+
| Tables_in_mydb |
+----------------+
| stu            |
| students       |
| tbl1           |
+----------------+
3 rows in set (0.00 sec)

创建表格：

MariaDB [mydb]> create table students (stuid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200),age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));
#创建表格students 

插入数据到表格：

MariaDB [mydb]> INSERT INTO students (name,age,gender,major) value('jia baoyu',17,'M','pixie jianfa'),('li chong',37,'M','xialong shibazhang'),('ximen qing',31,'M','kuihua baodian'),('li mochou',27,'F','wuxiang shengong');
#插入数据到表格students

[root@centos7b ~]#for i in {5..1000};do mysql -p123456  -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'M','major$i')";done
#批量插入数据到mydb数据库的students表格

查看数据：

MariaDB [mydb]> select name,age  from students where age >= 15;
#显示表格students内，age>=15的行

创建索引：

MariaDB [mydb]> create index  name ON students(name);
#根据字段‘name’，给表格students创建索引

MariaDB [mydb]> show indexes from students;
#查看表格 students的索引信息

+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | stuid       | A         |        2204 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name     |            1 | name        | A         |         220 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

使用索引的好处：

MariaDB [mydb]> explain  select * from students where name = 'stu999' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ref
possible_keys: name
          key: name
      key_len: 203
          ref: const
         rows: 1                                       <==使用索引“name”过滤，找出结果只扫描了一行
        Extra: Using index condition
1 row in set (0.00 sec)


MariaDB [mydb]> explain  select * from students where major = 'major999' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2204                                    <==不使用索引，找出结果扫描了2204行
        Extra: Using where
1 row in set (0.00 sec)

右侧通配，索引有效：

MariaDB [mydb]> explain  select * from students where name like 'stu100%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: name
          key: name
      key_len: 203
          ref: NULL
         rows: 11
        Extra: Using index condition
1 row in set (0.00 sec)

左侧通配，索引失效：

MariaDB [mydb]> explain  select * from students where name like '%tu100%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2204
        Extra: Using where
1 row in set (0.00 sec)

创建多列索引

MariaDB [mydb]>  create index name_and_age ON students(name,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mydb]> show index from students;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY      |            1 | stuid       | A         |        2054 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name         |            1 | name        | A         |        2054 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            1 | name        | A         |         186 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            2 | age         | A         |         186 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

MariaDB [mydb]> explain select * from students where name like 'stu100%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: name,name_and_age                 <==过滤name，可用的索引有两个，因此我们可以把‘name’这个索引删除了，只留下‘name_and_age ’
          key: name
      key_len: 203
          ref: NULL
         rows: 11
        Extra: Using index condition
1 row in set (0.00 sec)

MariaDB [mydb]> explain select * from students where  age >= 80  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: NULL                  <==只过滤age，没有可用索引。‘name_and_age ’不能跳过name，只索引age。
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1611
        Extra: Using where
1 row in set (0.00 sec)


MariaDB [mydb]> drop index name on students;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [mydb]> show index from students;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY      |            1 | stuid       | A         |        1611 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_and_age |            1 | name        | A         |        1611 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            2 | age         | A         |        1611 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

MariaDB [mydb]> explain select * from students where name like 'stu100%' and age >= 80  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: range
possible_keys: name_and_age                         <==同时过滤name和age，可以用索引
          key: name_and_age                                <==使用索引name_and_age   
      key_len: 205
          ref: NULL
         rows: 11
        Extra: Using index condition
1 row in set (0.00 sec)


MariaDB [mydb]> explain select * from students where name like 'stu%' and age >= 80  \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
         type: ALL
possible_keys: name_and_age                          <==可以用索引
          key: NULL                                                  <==不用索引（此处因为stu%匹配的行太多，即使有索引‘name_and_age ’ ，系统也并没有用到索引它）
      key_len: NULL
          ref: NULL
         rows: 1611
        Extra: Using where
1 row in set (0.00 sec)