Operation of MySQL table

First, create a table

CREATE TABLE table_name (

Property name Data type,

Property name Data type,

.

.

Property name data type);

Second, view the table structure

1. View the table definition

DESCRIBE table_name;

2. View Table Detail Definition

SHOW CREATE TABLE table_name;

Third, delete the table

DROP TABLE table_name;

Iv. Modification of the table

1. Modify the table name

ALTER TABLE old_table_name RENAME [T0] new_table_name;

2. Add fields

① add fields at the last position of the table

ALTER TABLE table_name

The ADD property Name property type;

② adding fields to the first position in a table

ALTER TABLE table_name

ADD Property Name property type FRIST;

③ adding fields to a specified field in a table

ALTER TABLE table_name

ADD Property Name Property type

After property name;

3. Delete a field

ALTER TABLE table_name

DROP attribute name;

4. Modifying fields

① Modifying the data type of a field

ALTER TABLE table_name

MODIFY Property name new data type;

② change the name of a field

ALTER TABLE table_name

Change old property name new property name old data type;

③ changing the name and data type of a field at the same time

ALTER TABLE table_name

Change old property name new data type of new property name;

④ Modifying the Order of fields

ALTER TABLE table_name

MODIFY Property name 1 data type first| After property name 2;

5. Constraints on the Operation table

① set non-null constraint (not null,nk): Insert a new record is not empty, or it will be an error

CREATE TABLE table_name (

Property name data type not NULL);

② the default value of the Set field: If the user inserts a new record field that is empty, the default value is automatically inserted

CREATE TABLE table_name (

Property name data type default defaults);

③ set UNIQUE constraint (UNIQUE,UK): User inserted new record field cannot be duplicated or system error

CREATE TABLE table_name (

Attribute name data type UNIQUE);

To set the identifier for a unique constraint

CREATE TABLE table_name (

Property name Data type

CONSTRAINT Uk_ Property name UNIQUE (attribute name)); give the constraint a name so that you can delete it more easily in the future.

④ setting a PRIMARY KEY constraint (PRIMARY KEY,PK): A table often has a combination of one or more columns whose values uniquely identify each row in the table. Such a column or columns is called the table's primary key, which enforces the entity integrity of the table

Single Field primary key

CREATE TABLE table_name (

Property name Data type PRIMARY KEY);

Set Identifier

CREATE TABLE table_name (

Property name Data type

CONSTRAINT Pk_ Property name PRIMARY KEY (attribute name));

Multi-field Primary key

CREATE TABLE table_name (

Property name Data type

"CONSTRAINT constraint name" PRIMARY KEY (property name, property name ...));

⑤ setting field value auto-increment (auto_increment): Only one field in a database can use the constraint, and the data type of the field must be an integer type

CREATE TABLE table_name (

Property name data type auto_increment);

⑥ setting foreign KEY constraints (FOREIGN KEY,FK): Maintain data consistency, integrity, primarily to control the data stored in the External key table. Make two tables associative, and foreign keys can only refer to the values of columns in the outside!

CREATE TABLE table_name (

Property name Data type

CONSTRAINT FOREIGN KEY constraint name FOREIGN key (property name 1)

refrences table Name (attribute name 2));

Property Name 1 is the name of the field that sets the foreign key in the Word table

Property Name 2 is the name of the field in the parent table that sets the PRIMARY KEY constraint

Operation of MySQL table