MySQL Tutorial

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

   [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)