MySQL杂记

1. 读取数据库中所有表名称

   mysql> show tables;
   +-------------------+
   | Tables_in_example |
   +-------------------+
   | AFSFSS            |
   | AOLIAL            |
   | QJXRXT            |
   +-------------------+
   3 rows in set (0.00 sec)
   

2. 查看指定表的DDL

   mysql> show create table AFSFSS;
   +--------+-------------------------------------------+
   | Table  | Create Table                              |
   +--------+-------------------------------------------+
   | AFSFSS | CREATE TABLE `AFSFSS` (                   |
   |        |     `MMDXUE` date DEFAULT NULL,           |
   |        |     `AWMFJL` time DEFAULT NULL,           |
   |        |     `GGUAFL` varchar(255) DEFAULT NULL,   |
   |        |     `CODE` varchar(255) NOT NULL,         |
   |        |     PRIMARY KEY (`CODE`)                  |
   |        | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4   |
   +--------+-------------------------------------------+
   1 row in set (0.01 sec)
   

3. 使用 insert ignore into 防止写入相同的数据，以主键和唯一键判断

   mysql> select * from AFSFSS;
   +------------+----------+-----------------+----------------------------------+
   | MMDXUE     | AWMFJL   | GGUAFL          | CODE                             |
   +------------+----------+-----------------+----------------------------------+
   | 2020-12-17 | 10:24:59 | 发生发射点       | 00ae333988e34f15ba50f932c2db3906 |
   | 2020-12-17 | 10:25:17 | 发生发撒法       | 02528cf8a9334f01b1463f93d8ebda67 |
   +------------+----------+-----------------+----------------------------------+
   2 rows in set (0.00 sec)
   
   mysql> INSERT IGNORE INTO AFSFSS VALUES ('2020-12-03', '11:00:00', '发撒法大使', '00ae333988e34f15ba50f932c2db3906');
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   
   mysql> select * from AFSFSS;
   +------------+----------+-----------------+----------------------------------+
   | MMDXUE     | AWMFJL   | GGUAFL          | CODE                             |
   +------------+----------+-----------------+----------------------------------+
   | 2020-12-17 | 10:24:59 | 发生发射点      | 00ae333988e34f15ba50f932c2db3906 |
   | 2020-12-17 | 10:25:17 | 发生发撒法      | 02528cf8a9334f01b1463f93d8ebda67 |
   +------------+----------+-----------------+----------------------------------+
   2 rows in set (0.00 sec)
   

4. 分页查询

   # limit 开始记录,每页记录数（开始记录序号从0开始，20表示第21条）
   mysql> select code from AOLIAL order by code limit 20,20;
   +----------------------------------+
   | code                             |
   +----------------------------------+
   | 494bf86a4fd54b50b60474eb35b94b35 |
   | 4a8f5cd3a157400fa7e2780f57048e30 |
   | 4c436df41c234e0f9af9847bfa1ccf49 |
   | 4c93496561ca428f988cf809bc38dfbc |
   | 4cef6e438e814d68b81a819c0166dd1c |
   | 4ebad1bc17164a37b75a13bf4d3bdfad |
   | 580bb3d524f34257b0be24bae13cd81f |
   | 634527ced7d4408885ae2aa3471b8cb0 |
   | 69bd9c85f19f4e2b97459a7e7e3c1cd8 |
   | 6c34f34d3d4a4dcc994b4281f9cb9f5f |
   | 6ed6dd50db42424a88f9b497a3b0a100 |
   | 7316adf9623a494fb446fb1cdfac96d0 |
   | 74ef39ecdbc642da80da5dffde23f16e |
   | 751f07a68d464ebc88346ef5944275b0 |
   | 7634407265924fd1a5b23d081fcfd4c7 |
   | 7994c820fdbb4323b647c61958c07418 |
   | 7ca232f656c8493f8a5fee84ab8520e8 |
   | 7deeae2f7ed947659c71298885a32668 |
   | 817d7a649116449689cb84df3272e23b |
   | 81d708617eed43afb1bfb6e071f547b7 |
   +----------------------------------+
   20 rows in set (0.00 sec)
   
   # limit 开始记录 offset 开始记录前记录数量（offset表示从第一条开始往后偏移（舍弃）多少条记录）
   mysql> select code from AOLIAL order by code limit 20 offset 20;
   +----------------------------------+
   | code                             |
   +----------------------------------+
   | 494bf86a4fd54b50b60474eb35b94b35 |
   | 4a8f5cd3a157400fa7e2780f57048e30 |
   | 4c436df41c234e0f9af9847bfa1ccf49 |
   | 4c93496561ca428f988cf809bc38dfbc |
   | 4cef6e438e814d68b81a819c0166dd1c |
   | 4ebad1bc17164a37b75a13bf4d3bdfad |
   | 580bb3d524f34257b0be24bae13cd81f |
   | 634527ced7d4408885ae2aa3471b8cb0 |
   | 69bd9c85f19f4e2b97459a7e7e3c1cd8 |
   | 6c34f34d3d4a4dcc994b4281f9cb9f5f |
   | 6ed6dd50db42424a88f9b497a3b0a100 |
   | 7316adf9623a494fb446fb1cdfac96d0 |
   | 74ef39ecdbc642da80da5dffde23f16e |
   | 751f07a68d464ebc88346ef5944275b0 |
   | 7634407265924fd1a5b23d081fcfd4c7 |
   | 7994c820fdbb4323b647c61958c07418 |
   | 7ca232f656c8493f8a5fee84ab8520e8 |
   | 7deeae2f7ed947659c71298885a32668 |
   | 817d7a649116449689cb84df3272e23b |
   | 81d708617eed43afb1bfb6e071f547b7 |
   +----------------------------------+
   20 rows in set (0.00 sec)