1.使用一张表的数据填充另一张表
Populate one table using another table:
You can populate data into a table through select statement over another table provided another table has a set of fields, which are required to populate first table. Here is the syntax:
INSERT INTO first_table_name [(column1, column2, ... columnN)]
SELECT column1, column2, ...columnN
FROM second_table_name
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];
2.更改数据库某张表的列名
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| wifi_ap_tag | varchar(30) | YES | | NULL | |
| passenger_avecount | double | YES | | NULL | |
| time_stamp | varchar(19) | YES | | NULL | |
+--------------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
ALTER TABLE wifi_real_day24 CHANGE wifi_ap_tag WIFIAPTag VARCHAR(30);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+--------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| WIFIAPTag | varchar(30) | YES | | NULL | |
| passenger_avecount | double | YES | | NULL | |
| time_stamp | varchar(19) | YES | | NULL | |
+--------------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
ALTER TABLE wifi_real_day24 CHANGE passenger_avecount passengerCount double;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| WIFIAPTag | varchar(30) | YES | | NULL | |
| passengerCount | double | YES | | NULL | |
| time_stamp | varchar(19) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
mysql>
ALTER TABLE wifi_real_day24 CHANGE time_stamp slice10min VARCHAR(19);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| WIFIAPTag | varchar(30) | YES | | NULL | |
| passengerCount | double | YES | | NULL | |
| slice10min | varchar(19) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
3.更改数据库某张表的列属性的顺序
mysql>
ALTER TABLE wifi_real_day24 CHANGE COLUMN slice10min slice10min VARCHAR(19) AFTER WIFIAPTag;
Query OK, 0 rows affected (1.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SELECT * from wifi_real_day24 limit 10;
+------------------+---------------------+----------------+
| WIFIAPTag | slice10min | passengerCount |
+------------------+---------------------+----------------+
| E1-1A-1<E1-1-01> | 2016-09-24 15:00:00 | 0.4 |
| E1-1A-1<E1-1-01> | 2016-09-24 15:10:00 | 1.8 |
| E1-1A-1<E1-1-01> | 2016-09-24 15:20:00 | 2.3 |
| E1-1A-1<E1-1-01> | 2016-09-24 15:30:00 | 1.6 |
| E1-1A-1<E1-1-01> | 2016-09-24 15:40:00 | 4.6 |
| E1-1A-1<E1-1-01> | 2016-09-24 15:50:00 | 7.3 |
| E1-1A-1<E1-1-01> | 2016-09-24 16:00:00 | 8 |
| E1-1A-1<E1-1-01> | 2016-09-24 16:10:00 | 5.1 |
| E1-1A-1<E1-1-01> | 2016-09-24 16:20:00 | 11 |
| E1-1A-1<E1-1-01> | 2016-09-24 16:30:00 | 12.2 |
+------------------+---------------------+----------------+
10 rows in set (0.00 sec)
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| WIFIAPTag | varchar(30) | YES | | NULL | |
| slice10min | varchar(19) | YES | | NULL | |
| passengerCount | double | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec)
mysql>
ALTER TABLE wifi_real_day24 CHANGE WIFIAPTag WIFIAPTag VARCHAR(30) AFTER passengerCount;
Query OK, 0 rows affected (1.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
SHOW COLUMNS FROM wifi_real_day24;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| slice10min | varchar(19) | YES | | NULL | |
| passengerCount | double | YES | | NULL | |
| WIFIAPTag | varchar(30) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)