MySQL database table structure and table constraints

Table structure Operations:

Add a single column: ALTER TABLE TB_NAME add [column] Col_name

Add a column: Alter TABLE ' sudty ' add ' aaa ' int;

　　　　

Add multiple columns:

　　　　

Specify where to add: The default is added in the last column, which can be added after a column with After+col_name

You can add the first column by using it.

ALTER TABLE ' sudty ' add ' bbb ' int after ' name '; Add a column of BBB after sudty the name of the table.

　　　　

ALTER TABLE ' sudty ' add ' account ' int first;

　　　　

　　Delete column: ALTER TABLE sudty drop BBB;

Delete multiple columns: ALTER TABLE sudty drop Aaa,drop scx;

To modify a table:

　　　　

To modify the data type of age:

ALTER TABLE sudty modify ' age ' varchar (20);

Modify the Age column name to:

ALTER TABLE sudty change ' ages ' ' age ' varchar (20);

Modify the table name Sudty to school:

ALTER TABLE Sudty Rename to school;

Non-empty constraint: NOT NULL

Constraint when building a table:

　　　　

Post-build constraints:

　　　　

　　

At this point, the null value of ID and name becomes No. Columns with non-null constraints must be passed when the value is passed in.

Unique constraint:

　　Make sure that the values in the field are unique:unique key

As with not NULL, it can be built at the time of the table:

　　

To delete a unique constraint:

　　

Add a UNIQUE constraint: Now add a column ' name ' to the TB, let's add a union unique after:

　　

At this point, the federated unique key name is ID, so delete just need to write: ALTER TABLE TB drop key ID;

INSERT into TB (id,name) VALUES (1, ' Harry '), (2, ' Harry '); Note that the two Unite is the only one,

That is, as long as the ID and name are not two at the same time.

　　　　

PRIMARY KEY constraint:

　　

The primary key guarantees the uniqueness of the record, uniquely identifying each data primary key to automatically NOT NULL； exist for each data table only

a primary key not Null+unique key , A unique key is also a NOT null time, then it is treated as primary

Key PRIMARY Key when there is no primary key in a table, the first non-null and unique column that appears is considered to have a primary key.

Naturally you can add a PRIMARY KEY constraint while building a table, and the following discusses adding a PRIMARY KEY constraint to the ID of the table TB:

　　

To delete a primary key:

　　

To add a federated primary key: There is still only one primary key (although there are two PRI)

　　

Deleting the Federated primary key is the same as removing the primary key normally.

　　

MySQL database table structure and table constraints