Mysql Getting Started Tutorial (v) creation, modification, and deletion of the table _mysql

MySQL for relational databases (relational database Management System), this so-called "relational" can be understood as the concept of "table", a relational database consists of one or several tables.

A table is the base unit of the DB storage data, and a table contains several fields or records. The actions of a table include creation, modification, and deletion.

　　1. Create a table

Creating a table is the creation of a new table in an existing database. After you select a specific database using the USE statement, you can use it to create a table with SQL statements

Where the attribute name refers to the name of the field in the table, the data type is the data type of the corresponding field, and the integrity constraint refers to some special constraints for the corresponding field.

The basic integrity constraints in MySQL are

　　1.1 Setting the primary key of the table

A primary key is a special field in a table that uniquely represents each piece of information in the table, that is, the primary key is the identity card that is recorded in the table. Primary keys are designed to help MySQL find one piece of information in a table as quickly as you could. Primary key must be unique, the value of the primary key field of any two records in the table cannot be the same, and the value of the primary key is Non-null. A primary key can be a single field or a combination of multiple fields.

For a single field primary key, you can use subordinate statements whenever you create a table

Property name Data type PRIMARY KEY

For a multiple-field primary key, you want to set the primary key uniformly after the property is defined, as

That is, stu_id and course_id Two field combinations are used to uniquely determine a single record.

　　1.2 Set the foreign key of the table

If field A is a property of table A and depends on the primary key of table B, then the table B is called the parent table, table A is the child table, and a is the foreign key of table A. The relationship between parent table B and child table A is established by field A, that is, the role of the foreign key is to establish an association relationship between the table and its parent table. When a message is deleted from the parent table, the corresponding information in the child table must be changed to ensure the integrity of the information.

The basic syntax for setting a foreign key when creating a table is

The foreign key alias refers to the foreign Key's code name, the attribute list refers to the foreign key set in the child table, and the attribute two list refers to the primary key of the parent table, and the table name refers to the parent table. Such as

Note that the foreign key of the child table must be associated with the primary key of the parent table, and the data type must be consistent.

　　1.3 Set table non-empty constraints

Non-nullability means that the value of a field cannot be null. The basic syntax is

Property name data type not NULL

When a record inserted on a field that has a non-empty constraint is blank for the value of the field, the system saves the insertion and does not accept it.

　　1.4 Set the uniqueness constraint of a table

Uniqueness means that the value of the field in all records cannot recur. The basic syntax is

Property name Data type UNIQUE

　　1.5 Setting the table's property value automatically increases

Auto_increment is a special constraint in the MySQL database that is used to automatically generate a unique ID for the new record inserted in the table. The basic syntax is

Property name Data Type Auto_increment

A table can have only one field to use the constraint, and the field must be part of the primary key, which may be any integer type. By default, the value of this field is increased from 1 onwards. If the first record sets the initial value of the field, subsequent newly added records will start growing from the initial value.

　　1.6 Set the default value of a table's properties

For fields that have a default value set, the system automatically assigns this default value if the field is not assigned a value when the new record is inserted. The basic syntax is

Property name Data Type default value

　　2. View table structure

The view table structure refers to the definition of a table that already exists in DB, including the describe statement and the show CREATE table statement.

　　2.1 Describe statement

You can view the basic definition of a table, including the field name, field data type, whether it is a primary foreign key, default value, and additional information.

The basic syntax is

DESCRIBE table name;

　　2.2 Show CREATE Table statement

You can view detailed definitions of a table, including the field name, field data type, integrity constraints, and so on, and the default storage engine and character encoding. The same can be replaced with \g endings, making the display more aesthetically pleasing.

　　3, modify the table

Modifying a table refers to modifying the definition of a table that already exists in the database.

Modify the table through the ALTER TABLE statement, including modifying the table name, modifying the field data type, modifying the field name, adding fields, deleting fields, modifying the placement of the fields, changing the default storage engine, and deleting the foreign key constraints for the table.

　　3.1 Modifying table names

Syntax is

ALTER table Old table name RENAME [to] new table name;

Where to is an optional parameter and whether it appears in the statement does not affect the execution of the statement.

　3.2 Modifying field names

The basic syntax is

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

If you modify only the field name without modifying the field data type, keep the new data type consistent with the original.

　　3.3 Modifying the data type of a field

The basic syntax is

ALTER Table name MODIFY property name new data type;

　　3.4 Add Field

The basic syntax is

ALTER Table name ADD property name 1 data type [integrity constraint] [I | After property name 2];

Where the one is the optional parameter, used to set the new field to the first field in the table, after property name 2 is also optional, the user places the new field after the property name 2, and if both optional arguments are not selected, the new field will default to the last field in the table.

In fact, for a datasheet, the order of the fields has no effect on the table, but for the person who created it, a field that has some direct or indirect connection is placed together to make it easier to understand the structure of the table.

　　3.5 Add Field

The basic syntax is

ALTER Table name DROP property name;

　　3.6 Modifying the placement of fields

The basic syntax is

ALTER Table name MODIFY property name 1 data type | After property name 2;

　　3.7 Changing the table's storage engine

The basic syntax is

ALTER table name engine= new storage engine name;

If you already have a lot of data in a table, it's best not to change its storage engine easily.

　3.8 Delete a table's foreign KEY constraint

That is, to disconnect the relationship between the table and its parent table, the basic syntax is

ALTER table name DROP FOREIGN key foreign keys alias;

　　4, delete the table

Deleting a table deletes all the data in the table, and it is more complex to delete the parent tables because there may be a foreign key constraint when the table is created, and some tables become parent tables of the tables associated with them.

　　4.1 Delete plain tables that are not associated

The basic syntax is

The DROP table table name;

　　4.2 Delete the associated parent table

Because a foreign key depends on the table at this point, the 4.1 deletion syntax will cause an error.

There are generally two ways to delete such a parent table: directly delete the child table associated with it, remove the parent table, or delete the child table's foreign KEY constraint and then delete the parent table, we usually take the second method.