MySQL Basics: Create, view, modify, and delete tables

Mysql Create, view, modify, and delete tables

1. Create a table

Create a syntax form for a table:

CREATE TABLE Table Name (property name  data type  constraint,                                 property name  data type  constraint,                                 ...                               ) ENGINE=DEFAULT CHARSET= character encoding name;  

Table of Constraint conditions:

　　

Attention:

1) Multiple primary keys can be added. Form: added in parentheses after the table name: PRIMARY KEY (field name 1, field Name 2, ...) ；

2) storage engine and character encoding can be omitted and not written;

3) When setting the table field name to the default value, you need to add "default" after the "defult" keyword;

4) foreign key settings:

The syntax rules are:

CONSTRAINT  Foreign key aliases  FOREIGNkey (attribute 1.  1, attribute 1. 2　　　　　　　　　, ...)   REFERENCES  Table Name (Property 2.  1, attribute 2. 2, ...)

　　

2. View the table

Three viewing tables in the form of:

DESCRIBE   table name; DESC    table name; SHOW  CREATE table   table name;

3. Modify the table

1) Modify the table name:

ALTER  TABLE  Old table name   RENAME   

2) Modify the data type of the field:

ALTER  TABLE  Table name   MODIFY   property name   data type;

3) Modify the field name:

ALTER  TABLE  Table name   change   old property name new   data type of new property name   ;

4) add field:

ALTER  TABLE  Table name   ADD   Property name 1   data type   constraint   [First| After property name 2];

　　Note: The first, after property name 2 is the place to add the added field to the beginning or after the property name 2. Where [] does not need to be added.

5) Delete the field:

ALTER  TABLE  Table name   DROP   attribute name;

6) Change the storage engine for the table:

ALTER  TABLE  Table name   engine= name of the storage engines;

7) Delete the foreign KEY constraint for the table:

ALTER  TABLE  Table name  DROP  FOREIGNkey    foreign key alias;

4. Delete a table

DROP  TABLE   Table name;

Attention:

When you delete a table, you need to be aware of whether it is associated with another table. If there is an association with another table, you need to delete the associated table or delete the association relationship before you can delete the table.

MySQL Basics: Create, view, modify, and delete tables