MySQL Table modification

CREATE TABLE tab2 as (SELECT * from TAB1)
The storage engine for this practice table also uses the server's default storage engine instead of the source table's storage engine, which copies the contents of the table together.

CREATE talbe tab2 Engine=myisam, charset= ' UTF8 ' as (SELECT * from TAB1)
You can specify your own storage engine and character set to compensate for the lack of method one

CREATE TABLE tab2 like Tab1
Create a new table using the same structure as the TAB1 table, and the column names, data types, empty fingers, and indexes will also be copied, but the contents of the table will not be copied. Foreign keys and private permissions are not replicated.

MySQL copy table structure and data to a new table
CREATE TABLE tab_new SELECT * from Tab_old

Copy data from old table to new table (assuming two table structure)
INSERT into Tab1 SELECT * from TAB2

Copy data from old table to new table (assuming two table structure is different)
INSERT into Tab1 (Field 1, Field 2, ...) SELECT Field 1, Field 2, ... From TAB2

Change table name
ALTER TABLE employee RENAME to staff

Change Column type
ALTER TABLE employee MODIFY COLUMN truename VARCHAR (Ten) not NULL DEFAULT ' '

Change Column Name
ALTER TABLE Employee Change COLUMN truename employeename VARCHAR (Ten) not NULL DEFAULT '

Add a default value
Alter TABLE employee ALTER COLUMN truename SET DEFAULT '

Delete default values
Alter TABLE employee ALTER COLUMN truename deop DEFAULT


MySQL Modify table
The structure of the table is as follows:

Mysql> Show create table person;
| person | CREATE TABLE ' person ' (
' Number ' int (one) DEFAULT NULL,
' Name ' varchar (255) DEFAULT NULL,
' Birthday ' date DEFAULT NULL
) Engine=myisam DEFAULT Charset=utf8 |
To delete a column:

ALTER TABLE person DROP COLUMN birthday;
To add a column:

ALTER TABLE person ADD COLUMN birthday datetime;
Modify the column to change the number to bigint:

ALTER TABLE person MODIFY number BIGINT not NULL;
or change number to ID, type bigint:

ALTER TABLE person change number ID BIGINT;


To add a primary key:

ALTER TABLE person ADD PRIMARY KEY (ID);
To delete a primary key:

ALTER TABLE person DROP PRIMARY KEY;
To add a unique index:

ALTER TABLE person ADD UNIQUE name_unique_index (' name ');
A unique index was created for the Name column, and the name of the index is name_unique_index.



To add a normal index:

ALTER TABLE person ADD INDEX birthday_index (' birthday ');


To delete an index:

ALTER TABLE person DROP INDEX Birthday_index;
ALTER TABLE person DROP INDEX Name_unique_index;


Disabling non-unique indexes

ALTER TABLE person DISABLE KEYS;
ALTER TABLE ... DISABLE keys let MySQL stop updating non-unique indexes in the MyISAM table.

Activating non-Unique indexes

ALTER TABLE person ENABLE KEYS;
ALTER TABLE ... ENABLE keys to recreate the missing index.



Change the default character set of the table and all character columns (CHAR, VARCHAR, TEXT) to the new character set:

ALTER TABLE person CONVERT to CHARACTER SET UTF8;
Modify the encoding of a column in a table

ALTER TABLE Person Change name name varchar (255) CHARACTER SET UTF8;
Change the default character set for only one table

ALTER TABLE person DEFAULT CHARACTER SET UTF8;
Modify Table Name

RENAME TABLE person to Person_other;
Move a table to another database

RENAME TABLE current_db.tbl_name to Other_db.tbl_name;





In MySQL we modify the Data table field command as long as the use of alter on it, let me give you a detailed description of MySQL in the Modified table field names/Fields length/field type and so on some methods introduced, there is need to know the friend can refer to.

Let's take a look at the usual methods.
MySQL's simple syntax, often used, is not easy to remember. Of course, these SQL syntaxes are basically common in each database. Listed below:
1. Add a field
ALTER TABLE user add COLUMN new1 VARCHAR () DEFAULT NULL; Add a field, default is empty
ALTER TABLE user add COLUMN new2 VARCHAR (a) not NULL; Add a field that cannot be empty by default
2. Delete a field
ALTER TABLE user DROP COLUMN new2; Delete a field
3. Modify a Field
ALTER TABLE user MODIFY New1 VARCHAR (10); Modify the type of a field
ALTER TABLE user change new1 new4 int; To modify the name of a field, be sure to re-

Primary key
ALTER TABLE tabelname add new_field_id int (5) unsigned default 0 NOT NULL auto_increment, add primary key (NEW_FIELD_ID);
Add a new column
ALTER TABLE T2 add d timestamp;
ALTER TABLE infos add ex tinyint NOT null default ' 0′;
Delete Column
ALTER TABLE T2 drop column C;
renaming columns
ALTER TABLE T1 change a b integer;
Changing the type of a column
ALTER TABLE T1 change b b bigint not null;
ALTER TABLE infos change list list tinyint not null default ' 0′;
Renaming a table
ALTER TABLE t1 rename T2;
Add index
Mysql> ALTER TABLE tablename change depno depno int (5) is not null;
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);
Index of the plus primary keyword
Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);
Index with unique restriction criteria
Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);
Delete an index
Mysql>alter table tablename DROP index emp_name;
Add Field:
mysql> ALTER TABLE table_name ADD field_name Field_type;
Modify the original field name and type:
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;
To delete a field:
mysql> ALTER TABLE table_name DROP field_name;
MySQL Modify field length
ALTER TABLE name modify column field name type;
For example
The User table name field in the database is varchar (30)
can be used
ALTER TABLE user modify column name varchar (50);


MySQL Change table structure: Add, delete, modify fields, adjust field order
Mysqltablenulluserlist
To add a field:

ALTER TABLE ' User_movement_log '
Add column Gatewayid int not null default 0 after ' RegionID ' (which field is added later)

To delete a field:

ALTER TABLE ' user_movement_log ' drop column Gatewayid

To adjust the order of fields:

ALTER TABLE ' user_movement_log ' Gatewayid ' gatewayid ' int not null default 0 after RegionID

Primary key

ALTER TABLE tabelname add new_field_id int (5) unsigned default 0 NOT NULL auto_increment, add primary key (NEW_FIELD_ID);

Add a new column

ALTER TABLE T2 add d timestamp;
ALTER TABLE infos add ex tinyint NOT null default ' 0 ';

Delete Column

ALTER TABLE T2 drop column C;

renaming columns

ALTER TABLE T1 change a b integer;

Changing the type of a column

ALTER TABLE T1 change b b bigint not null;
ALTER TABLE infos change list list tinyint not null default ' 0 ';

Renaming a table

ALTER TABLE t1 rename T2;

Add index

Mysql> ALTER TABLE tablename change depno depno int (5) is not null;
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);

Index of the plus primary keyword

Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);

Index with unique restriction criteria

Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);

Delete an index

Mysql>alter table tablename DROP index emp_name;

To modify a table:

Add Field:

mysql> ALTER TABLE table_name ADD field_name Field_type;

Modify the original field name and type:

mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

To delete a field:

mysql> ALTER TABLE table_name DROP field_name;


MySQL table structure modification detailed

Modify the syntax of a table
=========================
Add column [Add column name]
=========================
①alter table name Add column list Type column parameter "Add column to last face of table"
Example: ALTER TABLE test add username char (a) not null default ';
ALTER TABLE test add birth date not null default ' 0000-00-00 ';

②alter table name Add column list type column parameter after a column "Add a new column after a column"
Example: ALTER TABLE test add gender char (1) NOT null default "after username;

③alter table name Add column list type column parameter First "Add new column to the front"
Example: ALTER TABLE TEST add PID int NOT null default 0 first;

=========================
Delete column [drop column name]
=========================
①alter table Name drop column name
Example: ALTER TABLE test drop PID;

=========================
Modify column [modife column name]
=========================
①alter table name Modify column name new type new parameter "Modify column type"
Example: ALTER TABLE test modify gender char (4) NOT null default ';
②alter table name change old column name new column name new type new parameter "Modify column name and column type"
Example: ALTER TABLE test change PID UID int. unsigned NOT NULL default 0;

=========================
Query columns
=========================
①DESC table name "Querying all Columns"
Example: DESC test;
Mysql> DESC Department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Did | Int (11) | NO |         PRI |       | |
| Dname | varchar (32) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+

②show columns from table name "Effect as DESC"
Mysql> Show columns from department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Did | Int (11) | NO |         PRI |       | |
| Dname | varchar (32) |     YES | |       NULL | |
+-------+-------------+------+-----+---------+-------+

③show CREATE TABLE table name "View Creation code for table"
Mysql> Show CREATE TABLE department;
CREATE TABLE ' Department ' (
' Did ' int (one) is not NULL,
' dname ' varchar (+) DEFAULT NULL,
PRIMARY KEY (' did ')
) Engine=innodb DEFAULT Charset=utf8

mysql> ALTER TABLE table_name DROP field_name;_name new_field_name field_type;), default) primary key (NEW_FIELD_ID);




ORDER by _column1, _column2; /* _column1 Ascending, _column2 ascending */
ORDER by _column1, _column2 DESC; /* _column1 Ascending, _column2 descending */
ORDER by _column1 DESC, _column2; /* _column1 Descending, _column2 ascending */
ORDER by _column1 Desc, _column2 desc; /* _column1 Descending, _column2 descending */
Sort in reverse order by DESC (that is, sort from large to small)
Use ACS to sort by positive order (i.e., from small to large)



The order should be sorted by the ASCII code of the Chinese characters, the following is the alphabetical order according to Chinese characters
SELECT * from Corp_data where Chengshi as "Tumushuke DACLC" ORDER by convert (name using GBK);


#select Count (*) as Count from Corp_data where Chengshi like "Tumushuke DACLC";
SELECT * from Corp_data where Chengshi as "Tumushuke DACLC" ORDER by convert (name using GBK);
#delete from Corp_data where Chengshi like "Tumushuke DACLC";
#desc Corp_data;
#alter table corp_data Modify column Hangye varchar (100);
#alter table corp_data Modify column Jianjie varchar (10000);

MySQL Table modification