Mysql基础知识-常见查询语句-一

Mysql表结构大小写敏感

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set

常用查询语句

统计数据量

mysql> select count(*) from userInfo;
+----------+
| count(*) |
+----------+
|  1120293 |
+----------+
1 row in set

查看表结构

desc userInfo;
show columns from userInfo;
show create table userInfo;
show full columns from userInfo;

mysql> desc userInfo;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| userId   | bigint       | NO   |     | NULL    |       |
| userName | varchar(255) | YES  |     | NULL    |       |
| userAge  | int          | YES  |     | NULL    |       |
| label    | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set

mysql> show columns from userInfo;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| userId   | bigint       | NO   |     | NULL    |       |
| userName | varchar(255) | YES  |     | NULL    |       |
| userAge  | int          | YES  |     | NULL    |       |
| label    | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set

mysql> show create table userInfo;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| userInfo | CREATE TABLE `userInfo` (
  `userId` bigint NOT NULL,
  `userName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `userAge` int DEFAULT NULL,
  `label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql> show full 
columns from userInfo;
+----------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| Field    | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+----------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| userId   | bigint       | NULL               | NO   |     | NULL    |       | select,insert,update,references |         |
| userName | varchar(255) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| userAge  | int          | NULL               | YES  |     | NULL    |       | select,insert,update,references |         |
| label    | varchar(255) | utf8mb4_0900_ai_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+----------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
4 rows in set

desc table userT;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | userT | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set

查看索引

show index from userInfo;

查询和索引体验

mysql> select * from userInfo where userId=1;
+--------+----------+---------+-------+
| userId | userName | userAge | label |
+--------+----------+---------+-------+
|      1 | ??1      |      20 | ??1   |
+--------+----------+---------+-------+
1 row in set (0.83 sec)

mysql> select * from userInfo where userId=1;
+--------+----------+---------+-------+
| userId | userName | userAge | label |
+--------+----------+---------+-------+
|      1 | ??1      |      20 | ??1   |
+--------+----------+---------+-------+
1 row in set (0.88 sec)

mysql> select * from userInfo where userId=1;
+--------+----------+---------+-------+
| userId | userName | userAge | label |
+--------+----------+---------+-------+
|      1 | ??1      |      20 | ??1   |
+--------+----------+---------+-------+
1 row in set (0.91 sec)

mysql> select * from userInfo where userId=1;
+--------+----------+---------+-------+
| userId | userName | userAge | label |
+--------+----------+---------+-------+
|      1 | ??1      |      20 | ??1   |
+--------+----------+---------+-------+
1 row in set (0.88 sec)

mysql> select * from userInfo where userId=1;
+--------+----------+---------+-------+
| userId | userName | userAge | label |
+--------+----------+---------+-------+
|      1 | ??1      |      20 | ??1   |
+--------+----------+---------+-------+
1 row in set (0.84 sec)

修改为Navicat工具，xshell不支持中文输入，暂时不去解决，换个工具。

[SQL] select * from userInfo where userName='姓名1';
受影响的行: 0
时间: 0.870s
[SQL] select * from userInfo where userName='姓名2';
受影响的行: 0
时间: 0.972s

查看解释执行计划

 explain select * from userInfo where userName='姓名2';
 
mysql>  explain select * from userInfo where userId=2;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | userInfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1061937 |       10 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set

发现表没有建立索引，索引id查询也很慢，修改增加主键。

mysql>  explain select * from userInfo where userId=2;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | userInfo | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |      100 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set

现在输出有变化，走了主键，看看查询时间是多少。

[SQL] select * from userInfo where userId=2;
受影响的行: 0
时间: 0.001s

提升将近100倍。

建立索引

mysql> create index usi_idx on  userInfo(userName);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

[SQL]select * from userInfo where userName='姓名2';
受影响的行: 0
时间: 0.002s

创建索引后速度提升。
查看索引

mysql> show index from
 userInfo;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| userInfo |          0 | PRIMARY  |            1 | userId      | A         |     1112161 | NULL     | NULL   |      | BTREE      |         |               | YES     | NULL       |
| userInfo |          1 | usi_idx  |            1 | userName    | A         |     1109883 | NULL     | NULL   | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set
两个BTREE索引