MySQL Cookbook Study notes chapter Fourth

1, Clone table (create a table that exactly matches an existing table structure)

Create TABLE ... like clone table structure; clone some or all of the table data using the INSERT INTO ... SELECT statement

2. Save the query results to a table

A, use Insert Into...select to insert the result of the statement query into the table, if the table does not exist, you need to use the CREATE TABLE ... select statement to create a new table for the query results.

Insert into DST_TB1 (i,s) Select Val, name from SRC_TB1

INSERT INTO DST_TB1 select * from SRC_TB1

INSERT INTO DST_TB1 select * from SRC_TB1 where val>100 and name like ' A% '

CREATE TABLE DST_TB1 SELECT * FROM SRC_TB1

To rename a column:

CREATE TABLE DST_TB1 Select Inv_no, sum (unit_cost*quantity) as Total_cost from SRC_TB1 Group by Inv_no;

Using an index in the destination table, you can specify in the SQL statement:

CREATE TABLE DST_TB1 (primary key (ID), index (state,city)) SELECT * FROM SRC_TB1

Some column properties cannot be cloned from an island destination table, such as Auto_increment, after the destination table has been created and the data is copied, use ALTER TABLE

To set the destination table to the corresponding property.

CREATE TABLE DST_TB1 (primary key (ID)) SELECT * from SRC_TB1;

ALTER TABLE DST_TB1 Modify ID int unsigned NOT NULL auto_increment;

3, using temporary tables

The so-called temporary table is for temporary use only, after the end of the use of MySQL automatically deleted.

Common Build Statement:

Create temporary table tbl_name (column definition);

Clone table:

Create temporary table new_table like original_table;

To create a table based on query results:

Create Temporaray table Tbl_name Select ...;

Temporary tables use the table name of the original table, you can create a temporary backup of the normal table, make any changes to the temporary table does not affect the real data

4. Check or change the storage engine for a table

MySQL supports a variety of engines, each with different features, such as InnoDB and BDB support transactions and MyISAM does not support transactions

Use INFORMATION_SCHEMA or Show table status or Show create TABLE

For information on the mail table:

Use ALTER and an engine clause to change the engines used by a table.

5, generate a unique table name

A, the use of random numbers, B, the use of the process number (the same time PID must be different), but different times can not be guaranteed; C, the connection identifier is another source of unique values