MySQL table operation

First, CREATE table 1, create a new table

#语法: Create TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); #注意: 1. In the same table, the field names cannot be the same as 2. Width and constraints are optional 3. Field names and types are required

Mysql> CREATE TABLE auth (    ID int () primary key auto_increment,    name varchar) NOT NULL,    -&G T Age int (3),    birthday datetime    ); Query OK, 0 rows affected (0.36 sec)

　　

2. Copy the table

Mysql> CREATE TABLE auth2 select * FROM Auth; Query OK, 0 rows affected (0.29 sec) records:0  duplicates:0  warnings:0

　　

Second, view table 1, view the table structure

Mysql> desc auth;+----------+-------------+------+-----+---------+----------------+| Field    | Type        | Null | Key | Default | Extra          |+----------+-------------+------+-----+---------+----------------+| ID       | int     | NO   | PRI | NULL    | auto_increment | | name     | varchar (10) | NO   |     | NULL    |                | | age      | INT (3)      | YES  |     | NULL    |                | | birthday | datetime    | YES  |     | NULL    |                | +----------+-------------+------+-----+---------+----------------+4 rows in Set (0.02 sec)

2. View the creation information of the table

Mysql> Show CREATE TABLE auth;+-------+------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------------------+| Table |                                                                                                                                                                                                            Create Table |+-------+------------------ --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------+| Auth | CREATE TABLE ' auth ' (' id ' int () NOT null auto_increment, ' name ' varchar (ten) NOT null, ' age ' int (3) DEFAULT NULL, ' B Irthday ' datetime default NULL, PRIMARY KEY (' id ')) engine=innodb default Charset=utf8 |+-------+----------------------- ------------------------------------------------------------------------------------------------------------------------------------------------ --------------------------------------------------+1 Row in Set (0.00 sec)

　　

Third, modify table 1, modify the table name

mysql> ALTER TABLE auth2 rename auth666; Query OK, 0 rows affected (0.10 sec)

2. Add Table field

Mysql> ALTER TABLE auth add addr char (6) not null; Query OK, 0 rows affected (0.53 sec) records:0  duplicates:0  warnings:0

3. Modify table field

Modify table field Information

Mysql> ALTER TABLE auth Modify addr varchar (6) NULL; Query OK, 0 rows affected (0.60 sec) records:0  duplicates:0  warnings:0

Modify table field names and fields information

Mysql> ALTER TABLE auth change addr address varchar (6); Query OK, 0 rows affected (0.09 sec) records:0  duplicates:0  warnings:0

4. Delete a table field

mysql> ALTER TABLE auth drop birthday; Query OK, 0 rows affected (0.49 sec) records:0  duplicates:0  warnings:0

Iv. deletion of tables

mysql> drop table auth666; Query OK, 0 rows affected (0.10 sec)

V. Data types of tables

Http://www.cnblogs.com/fu-yong/p/8495001.html

VI. constraints of the table

Http://www.cnblogs.com/fu-yong/p/8495003.html

MySQL table operation