MySQL getting started tutorial (5): create, modify, and delete tables. mysql getting started tutorial

MySQL getting started tutorial (5): create, modify, and delete tables. mysql getting started tutorial

MySQL is a Relational Database Management System. This so-called "Relational" can be understood as "tables". A Relational Database consists of one or more tables.

A table is the basic unit for storing data in a database. A table contains several fields or records. Table operations include creating, modifying, and deleting tables.

　　1. Create a table

Create a new table in an existing database. After selecting a specific database using the USE statement, you can USE its SQL statement to create a table.

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 constraints refer to some special constraints of the corresponding field.

MySQL has basic integrity constraints

　　1.1 set the table's primary key

A primary key is a special field in a table. This field uniquely represents each piece of information in the table, that is, the primary key is like the ID card recorded in the table. The primary key aims to help MySQL find a piece of information in the table as quickly as possible. The primary key must satisfy the uniqueness. The values of the primary key fields of any two records in the table cannot be the same, and the value of the primary key is not null. A primary key can be a single field or a combination of multiple fields.

For single-field primary keys, you only need to use subordinate statements when creating the table.

Attribute name data type PRIMARY KEY

For a multi-field primary key, you must set the primary key after the attribute is defined, as shown in figure

Use the stu_id and course_id fields to uniquely identify a record.

　　1.2 set the foreign key of the table

If field a is the attribute of table A and depends on the primary key of Table B, table B is the parent table, table A is the sub table, and table a is the foreign key of Table. Field a establishes an association between parent table B and child table A, that is, the foreign key is used to establish the association between the table and its parent table. When deleting a piece of information in the parent table, the corresponding information in the child table must also be changed to ensure information integrity.

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

The foreign key alias refers to the foreign key code. Attribute 1 refers to the foreign key set in the child table, attribute 2 refers to the primary key of the parent table, and table name refers to the name of the parent table. For example

Note: The Foreign keys of the child table must be the primary keys of the parent table, and the data types must be consistent.

　　1.3 set non-empty table Constraints

Non-NULL indicates that the field value cannot be NULL. The basic syntax is

Attribute name data type NOT NULL

When the field value of the record pair inserted on a field with non-null constraints is null, the system will save and will not accept this insert.

　　1.4 set table uniqueness constraints

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

Attribute name data type UNIQUE

　　1.5 set the attribute value of the table to automatically increase

AUTO_INCREMENT is a special constraint in the MySQL database. It is mainly used to automatically generate a unique ID for the new records inserted in the table. The basic syntax is

Property Name Data Type AUTO_INCREMENT

Only one field in a table can use this constraint, and this field must be part of the primary key. This field can be any integer type. By default, the value of this field starts from 1. If the initial value of this field is set for the first record, the newly added record will start from the initial value.

　　1.6 set the default values of table Properties

If a field with a default value is not assigned a value when a new record is inserted, the system automatically assigns the default value to it. The basic syntax is

Property Name Data Type DEFAULT Value

　　2. view the table structure

Viewing the TABLE structure refers to viewing the definitions of existing tables in the DB, including the DESCRIBE statement and the show create table statement.

　　2.1 DESCRIBE clause

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

The basic syntax is

DESCRIBE table name;

　　2.2 show create table statement

You can view the detailed definitions of a table, including the field name, field data type, integrity constraints, and other definitions, as well as the default storage engine and character encoding. It can also be replaced by the end of \ G to make the display result more beautiful.

　　3. modify a table

Modifying a table is to modify the definition of an existing table in the database.

Use the Alter Table statement to modify a Table, this includes modifying the table name, modifying the field data type, modifying the field name, adding a field, deleting a field, modifying the field arrangement position, changing the default storage engine, and deleting the table's foreign key constraints.

　　3.1 Modify Table Name

Syntax:

Alter table old TABLE name RENAME [TO] new TABLE name;

Here, "To" is an optional parameter. Whether it appears in a statement does not affect statement execution.

　3.2 Modify Field name

The basic syntax is

Alter table name Change old property name new data type;

If you only modify the field name and do not modify the field data type, keep the new data type consistent with the original one.

　　3.3 modify the Data Type of a field

The basic syntax is

Alter table name MODIFY attribute name new data type;

　　3.4 add a field

The basic syntax is

Alter table name ADD attribute name 1 Data Type [integrity constraints] [FIRST | AFTER attribute name 2];

FIRST is an optional parameter, which is used to set the new field to the FIRST field of the table. AFTER attribute name 2 is also an optional parameter. You can put the new field AFTER attribute name 2, if neither of the two optional parameters is selected, the new field is placed in the last field of the table by default.

In fact, for a data table, the field sorting sequence does not affect the table, but for the creator, it puts directly or indirectly related fields together, it is easy to understand the structure of this table.

　　3.5 Add a field

The basic syntax is

Alter table Name DROP attribute name;

　　3.6 modify the field arrangement position

The basic syntax is

Alter table name MODIFY attribute name 1 Data Type FIRST | AFTER attribute name 2;

　　3.7 change the storage engine of a table

The basic syntax is

Alter table name ENGINE = Name of the new storage ENGINE;

If a table already has a lot of data, it is best not to change its storage engine easily.

　3.8 Delete the foreign key constraint of a table

The basic syntax for disconnecting a table from its parent table is

Alter table Name drop foreign key alias;

　　4. delete a table

Deleting a table deletes all the data in the table. Because a foreign key constraint may exist during table creation, some tables become the parent tables associated with the table. to delete these parent tables, the situation is complicated.

　　4.1 delete an unassociated common table

The basic syntax is

Drop table name;

　　4.2 Delete the associated parent table

In this case, because a foreign key is dependent on the table, an error is returned when you use the delete 4.1 syntax.

To delete a parent table, you can directly Delete the associated child table and then the parent table, or delete the foreign key constraint of the child table before deleting the parent table, generally, we adopt the second method.

Articles you may be interested in:

• ASP. NET and MySQL Databases
• Getting started with MySQL-Quick Reference
• Php entry-a class connecting to the mysql database
• Introduction to triggers in MySQL
• Java operation mysql getting started code instance (including insert, update and query)
• 21-minute MySQL getting started tutorial
• Getting started with MySQL in Python
• MySQL Getting Started Guide and basic installation tutorials in Linux