Basic manipulation of data tables in MySQL

This article is based on the practice of the book "MySQL from the beginning to Master", which was compiled by the national 863 Central software incubator.

First, create a data table

The data table belongs to the database, and you should specify the operation in that database by using the statement use database name before creating the data table.

The statement creating the data table is create table with the following syntax rules:

1 CREATE TABLETable name2（//as parentheses3Field Name 1 data type[column-level constraint conditions] [Default Value],4Field Name 1 data type[column-level constraint conditions] [Default Value],5 ......6 [table-level constraint conditions]7）;//To have a semicolon

When creating tables using CREATE TABLE, consider the following:

(1) To create table names, not case-sensitive, cannot use keywords in the SQL language, such as drop, alter, etc.;

(2) The name and data type of each field in the datasheet, separated by commas if you want to create more than one.

1. PRIMARY KEY constraint

The primary key, also called the main code, is a combination of one or more columns in a table. The PRIMARY KEY constraint (Primary key Constraint) requires that the data for the primary key column be unique and is not allowed to be empty. A primary key uniquely identifies a record in a table, can be combined with a foreign key to define relationships between different data tables, and can speed up database queries. The relationship between the primary key and the record is like the relationship between the ID and the person, which corresponds to one by one. There are two types of primary keys: Single Chini and multi-field federated primary keys.

(1) Single field primary key

A) specify the primary key while defining the column, with the following syntax rules:

Field name data type PRIMARY KEY [default value]

b) Specify the primary key after all the columns have been defined, with the syntax in the following format:

PRIMARY KEY [Field name]

(2) Multi-field federated primary key

The syntax rules are as follows:

PRIMARY KEY [Field 1, Field 2 ...., field name N]

2, FOREIGN KEY constraints

Foreign keys are used to establish a connection between data in two tables, which can be one or more columns, and a table can have more than one foreign key. Foreign keys correspond to referential integrity, a table's foreign keys can be null, and if not NULL, each foreign key value must be equal to a value of the primary key in another table. The table where the primary key resides is the primary table, and the foreign key is the table from which the table is located.

The syntax for creating a foreign key is as follows:

[CONSTRAINT foreign Key name] FOREIGN KEY Field name 1 [Field Name 2 ...] REFERENCES primary table name primary key column 1 [, PRIMARY key column 2 ...]

Note: the foreign key of the child table must be associated with the primary key of the parent table, and the data type of the associated field must match, and if the type is different, the child table is created and an error message appears.

3, non-null constraints

A non-null constraint (NOT NULL Constraint) means that the value of a field cannot be empty. The syntax rules are as follows:

Field name data type not NULL

4, the uniqueness of the constraints

A uniqueness constraint requires that the column be unique, allowed to be empty, but only one null value can appear. A unique constraint ensures that no duplicate values occur for a column or a base class.

1) syntax

Field name data type UNIQUE

2) syntax

Specify a unique constraint after the definition goes to all columns, with the following syntax:

[CONSTRAINT < constraint name;] UNIQUE (< field name >)

5. Default Constraints

The default constraint specifies the default value for a column. The syntax is as follows:

Field name data type default defaults

Note: The difference between unique and primary key:

1) A table can have multiple fields declared as unique, but can only be declared by one primary key;

2) A class declared as primary KEY does not allow null values, but a field declared as unique allows the existence of a null value.

6, set the property value of the data table automatically increase

In a database application, you want the system to automatically generate a primary key value for a field each time a new record is inserted. You can do this by adding the Auto_increment keyword to the table primary key.

A table can use the auto_increment constraint only by one field, and the field must be part of the primary key. The syntax is as follows:

Field name data type Auto_increment

Then look at the data table after inserting the corresponding data:

Second, view the data table structure

1. View the basic structure of the table

The syntax is as follows:

DESCRIBE (DESC) table name;

2. View detailed structure of the table

The syntax format is as follows:

SHOW CREATE table < table name \g>;

Third, modify the data table

1. Modify the table name

The syntax is as follows:

ALTER table < old table name >rename [to]< new table name >;

Note: Modifying table names does not modify the structure of the table.

2. Modify field Data type

ALTER table< table name >MODIFY< field name >< data type >;

3. Modify field names

ALTER table< table name >CHANGE< old field name >< new field name >< new data type >;

4. Add fields

ALTER table< table name >ADD< new field name >< data type >[constraint][first/after field name already exists];

The default add location is the last column of the data table

1) Add a field without integrity constraints

2) Add a field with the completion constraint

3) Add a field to the first class of a table

4) Add a field after specifying a column in the table

5. Delete fields

ALTER table < table name >DROP< field name >;

6. Modify field Sorting

ALTER table< table name >MODIFY< field 1>< data type >first after< field 2>;

7. Modify the table's storage engine

ALTER table< table name >ENGINE=< changed storage engine name >;

8. Delete the foreign KEY constraint for the table

ALTER table< table name >drop FOREIGN key< foreign KEY constraint name >;

Iv. Deleting Data sheets

DROP table 1, table 2 ....

It is important to note that when you delete a data table, if you have a foreign key constraint, you should first delete the constraint and then delete the main table. You can also delete the child table first, and then delete the primary table.

Summarize:

1, modification of the data table with ALTER TABLE

2, delete the data table with drop

Basic manipulation of data tables in MySQL