首页 > 代码库 > 【练习】表维护

【练习】表维护

2024-08-18 23:19:08   222人阅读

1.在world数据库中新建一个名为city_temp的表，该表示city表的副本，将city表的内容插入city_temp表中。

mysql> create table city_temp like city;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into city_temp select * from city;
Query OK, 4080 rows affected (0.46 sec)
Records: 4080  Duplicates: 0  Warnings: 0

2.通过使用id列删除city_temp表数据中的多个行来创建‘洞’（删除或更新产生的行间隔）。

mysql> delete from city_temp where id between 1001 and 2000;
Query OK, 1000 rows affected (0.18 sec)
mysql> show table status like ‘city_temp‘\G
*************************** 1. row ***************************
           Name: city_temp
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3080
 Avg_row_length: 106
    Data_length: 327680
Max_data_length: 0
   Index_length: 98304
      Data_free: 0
 Auto_increment: 4081
    Create_time: 2016-11-05 04:18:28
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

3.通过对city_temp表运行analize table 来更新表的统计信息，确认已经分析。

mysql> analyze table city_temp;
+-----------------+---------+----------+----------+
| Table           | Op      | Msg_type | Msg_text |
+-----------------+---------+----------+----------+
| world.city_temp | analyze | status   | OK       |
+-----------------+---------+----------+----------+
1 row in set (0.07 sec)

 

【练习】表维护


联系
我们