MySQL Table related Operations

1. Create a table

Reference: Create-table

CREATE [Temporary] TABLE [IF not EXISTS]tbl_name (field name data type[Not NULL | NULL] [DEFAULT default_value]  [auto_increment] [UNIQUE [KEY] | [PRIMARY] KEY][COMMENT ' string '][REFERENCES tbl_name (index_col_name,...)],...) ENGINE=Engine name[DEFAULT]CHARSET=coding method;

Create a table with the results of a query and insert data

CREATE [temporary] TABLE [IF not EXISTS] tbl_name  [as] query_expression;

Creating empty tables with existing tables

CREATE [temporary] TABLE [IF not EXISTS]  like old_tbl_name;

2. Display all table names in the current database

SHOW TABLES;

3. Query the type and constraints of the specified table structure or specified fields

{DESC| | [col_name] |  from Tbl_name  [fromdb_name];

For example:

DESC user;  -- show the structure of the user table DESC User -- displays only the type and constraints of the User table username field  from User  from   from TestDB. user;

4. Display the creation process of the table

CREATE TABLE tbl_name;

5. Delete the table definition and the corresponding data

DROP [temporary] TABLE [IF EXISTS]  [, Tbl_name] ...

Note: If any tables named in the argument list does not exist, the statement returns a error indicating by name which Nonex isting tables It is unable to drop and also drops all tables in the list so do exist. Drop table deletes only the data tables that exist in the list, and returns an error for non-existent.

6. Clear the table data, but the table still exists

TRUNCATE [TABLE] tbl_name

The difference between delete from Tbl_name is that TRUNCATE table deletes the tables and then re-creates the data in the table instead of row by line, so the TRUNCATE table is faster when you want to delete all the data in a table with a large amount of data

Table structure Related:

Reference: Alter-table

ALTER TABLE can Add, delete, modify field types, modify field names, and more.

ALTER TABLE [alter_specification [, Alter_specification] ...]

1. Modify the table name

ALTER table tbl_name  [to|as]tableto[   , tbl_name2 to New_tbl_name2]-- can modify multiple table names at the same time

2. Add a field

ALTER TABLE ADD [COLUMN] col_name [First| After Col_name]--  first indicates the Insert field as the top column of the table

3. Delete a field

ALTER TABLE DROP [COLUMN] col_name;

4. Modify only the type of the field and the position in the table

ALTER TABLE [COLUMN] col_name [First| After Col_name];

Also modify the field type, the position in the table, and the field name

ALTER TABLE [COLUMN] [first| After Col_name];

5. Set and cancel default values

ALTER TABLE ALTER [COLUMN] col_name {SETDEFAULT| DROP DEFAULT};

6. Adding and removing primary keys

ALTER TABLE ADD [CONSTRAINT [symbol]PRIMARYKEY  [index_type] -- Add primary Key ALTER TABLE DROP PRIMARY KEY -- Delete primary Key

Note: When the primary key is set to self-increment, an error occurs when attempting to delete the primary key. You need to first remove the Auto_increment property with alter TABLE modify.

Mysql> CREATE TABLE IF  not EXISTSTest ( -IdINTUNSIGNED auto_incrementPRIMARY KEY     -); MySQL> ALTER TABLETestDROP PRIMARY KEY; ERROR1075(42000): IncorrectTableDefinition There can onlyOne autocolumn  andIt must be defined asAKeyMySQL> ALTER TABLETest MODIFY IDINTUNSIGNED;--Remove the Auto_increment property of the primary keyMysql> ALTER TABLETestDROP PRIMARY KEY;

7. Add and remove unique indexes

ALTER TABLETbl_nameADD [CONSTRAINT [Symbol]]UNIQUE [index| KEY] [index_name] [Index_type](Index_col_name,...);--Add a unique indexALTER TABLETbl_nameDROP{INDEX|KEY} index_name;--Delete Index

The added index is named by the corresponding field name by default

8. Modifying the storage Engine

ALTER TABLE [=] engine_name;

9. Modify self-growth values

ALTER TABLE tbl_name  [=] value;

10. Modifying the character Set

ALTER TABLE [DEFAULT] CHARACTER SET [=] charset_name;

MySQL Table related Operations