Some common data table Operation statement notes in Mysql _mysql

0. Create a table

CREATE Table Table name (property name data type [integrity constraint],
property name data type [integrity constraint],
property name data type [integrity constraint])

Integrity constraints are certain special constraints that specify certain fields.
You first select a database using the USE statement when creating a table using the CREATE table. For example, there is a example database, using use example Select this database.
Table names cannot be in SQL keywords, such as create,update, and letters are case-insensitive.
The following is an example of creating a table:

CREATE table example (ID int, name varchar (), sex Boolean);

The example above creates a example table with three fields, an ID integral type, a name string type, and a sex Boolean type.
Integrity constraints are restrictions on a field. If the integrity constraints are not met, the database system will not perform the user's operation and protect the integrity of the data in the database.
The following are the integrity constraints:

• PRIMARY key identifies the property as the primary key for the table, and can uniquely identify the corresponding tuple
• The FOREIGN key identifies the property as the foreign key for the table, and is the primary key of the table associated with it.
• Not NULL to identify this property cannot be null
• Unique identifies the value of this property as unique
• Auto_increment identifies the value of this property automatically increases
• Default set defaults for this property

1. View table structure

Describe table name

Describe can be abbreviated to DESC.
The describe statement can view the basic definition of a table. Includes the field Name field, the field data type type, whether null NULL, the primary foreign key key, default value defaults, and additional information extra.

2. View table Detail Structure

Show create table name show

CREATE TABLE table name \g

\g Enhanced Display visibility

Show CREATE TABLE

Statements can view table fields, field data types, integrity constraints, the underlying storage engine, character encoding, and so on.

3. Modify the table name

ALTER TABLE old table name rename [to] new table name;

4. Modify the data type of the field

ALTER TABLE name modify property name data type;

5. Modify Field Name

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

The old attribute name parameter refers to the field name before the modification, and the new property name parameter refers to the modified field name, and the data type does not change if the data type is not specified.

6. Add Field

ALTER TABLE name Add property name 1 data type [integrity constraint] [i] after property name 2;

The first option means that this field is the primary field of the table, after the property name 2 is placed after the specified field, and the last one is the default.

7. Delete Fields

ALTER TABLE name drop property name;

8. Delete association table
(1) Delete the foreign KEY constraint of the table
a foreign key is a special field that associates a table with its parent table. The FOREIGN KEY constraint is set when the table is created. The following statement is needed to remove the association between them.

ALTER TABLE name drop FOREIGN key foreign keys alias;

The foreign key alias parameter refers to the foreign key code set when the table is created.

(2) Delete plain tables that are not associated

The drop table table name;

When you delete a table, all of the data in the table is also deleted. It is best to back up the data in the table when you delete the table.

(3) Delete the parent table that is associated with the other table
When you delete a table related to a relationship, the drop table example1 the error, because a foreign key depends on the table

For example, creating a example4 table relies on the example1 table, and the foreign key of the Example4 table stu_id dependent on the primary key of the example1 table. Example1 table example4 The parent table of the table.

If you want to delete the Example4 table, you must first remove the dependency. The easiest way to do this is to delete the child table Example4 First, and then delete the parent table example1. However, this may affect other data in the child table.

Another method is to delete the foreign key constraint of the child table and then delete the parent table. This method does not affect other data of the child table, and can guarantee the security of the database.

For example, the foreign key alias for the Example4 table is D_FK, removing the example4 foreign KEY constraint

。

You can see whether you have deleted it by show create TABLE Example4 \g.
And then execute it again.

.
Successful execution indicates that the operation was successful.