Mysql创建普通索引的方法

最新推荐文章于 2024-07-21 00:00:44 发布

啊啊啊啊tttttt

最新推荐文章于 2024-07-21 00:00:44 发布

阅读量333

点赞数 11

文章标签： mysql adb android

本文链接：https://blog.csdn.net/2301_79638183/article/details/139365851

版权

Mysql创建普通索引的方法

文章目录

Mysql创建普通索引的方法

1.desc 与 asc(大小写不区分)#第一种方法

mysql> create database school;
Query OK, 1 row affected (0.01 sec)

mysql> use school;
Database changed
mysql> create table class(id int,name varchar(128),teacher varchar(128));
Query OK, 0 rows affected (0.03 sec)

mysql> alter table class add index index_id(id desc);#降序（默认增序）；
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into class values(1,'二班','猪猪侠'); #名字仅供娱乐，请勿当真。
Query OK, 1 row affected (0.00 sec)

mysql>  insert into class values(2,'三班','菲菲');
Query OK, 1 row affected (0.01 sec)

mysql> select *from class where id>0;
+------+------+---------+
| id   | name | teacher |
+------+------+---------+
|    2 | 三班 | 菲菲    |
|    1 | 二班 | 猪猪侠  |
+------+------+---------+
2 rows in set (0.00 sec)

mysql> explain  select *from class where id>0;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | class | NULL       | range | index_id      | index_id | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

2.第二种方法（增序）

mysql> drop table class;
Query OK, 0 rows affected (0.02 sec)
mysql> create table class(id int ,name varchar(128) unique,teacher varchar(128),index index_id(id));
Query OK, 0 rows affected (0.04 sec)

mysql>  insert into class values(1,'二班','猪猪侠');
Query OK, 1 row affected (0.01 sec)

mysql>  insert into class values(2,'三班','菲菲');
Query OK, 1 row affected (0.01 sec)

mysql> select*from class where id>0;
+------+------+---------+
| id   | name | teacher |
+------+------+---------+
|    1 | 二班 | 猪猪侠  |
|    2 | 三班 | 菲菲    |
+------+------+---------+
2 rows in set (0.00 sec)

3.副页//第三种方法（降序）

mysql> drop table class;
Query OK, 0 rows affected (0.02 sec)

mysql> create table class(id int,name varchar(128),teacher varchar(128));
Query OK, 0 rows affected (0.04 sec)

mysql>  create index index_id1 on class(id desc);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into class values(1,'二班','猪猪侠');
Query OK, 1 row affected (0.01 sec)

mysql> insert into class values(2,'三班','菲菲');
Query OK, 1 row affected (0.01 sec)
mysql> select*from class where id>0;
+------+------+---------+
| id   | name | teacher |
+------+------+---------+
|    2 | 三班 | 菲菲    |
|    1 | 二班 | 猪猪侠  |
+------+------+---------+
2 rows in set (0.00 sec)