MySQL's operations on tables

1. Basic concepts of the table

In a database, a table is a very important database object, which is the basic object that makes up a database and consists of several fields that are used primarily to store data records.

The database objects in the table contain columns, indexes, and triggers.

Column: Also called an attribute column, you must specify the name and data type of the column when you create the table specifically.

Index: is based on the order of the specified database table columns, provides a way to quickly access the data and supervises the table's data so that the data in the column to which the index points is not duplicated.

Triggers: A set of objects defined by a set of commands that are automatically executed when the data in a table is inserted, updated, or deleted, and can be used to ensure the integrity and security of the data.

2. Create a table

CREATE TABLE  INT varchar (-varchar);

3. View table structure

3.1 DESCRIBE (DESC is shorthand) Statement view table definition

DESCRIBE t_dept;

3.2 Show CREATE Table statement view table Detail definition

CREATE TABLE  t_dept;

When displaying detailed definition information, you can use ";", "\g" and "\g" symbols to end, in order to make the results beautiful, easy to see, it is best to use the "\g" symbol to end, as follows:

4. Delete a table

DROP TABLE t_dept;

5. Modify the table

5.1 Modifying table names

ALTER TABLE [to] NEW _table_name

5.2 Adding fields

Add a field at the last position of the table:

ALTER TABLE ADD Property Name property type

Add a field to the first position in the table:

ALTER TABLE ADD Property Name property type First

Add a field after the specified field in the table:

ALTER TABLE ADD Property Name property type after property name

5.3 Delete a field

ALTER TABLE DROP Property name

5.4 Modifying fields

To modify the data type of a field:

ALTER TABLE table_name Modify Property Name property type

Modify the name of the field:

ALTER TABLE table_name Change old property name new property name  old data type

Also modify the name and properties of the field:

ALTER TABLE table_name Change old property name new property name  new data type

To modify the order of fields:

ALTER TABLE table_name MODIFY property name 1 data type First| After property name 2

6. Constraints on the Operation table

For a table that has already been created, although the data type of the field determines the type of data that can be stored, the data stored in the table is not checked for legality. If you want to do some integrity checking on the data in the table, you can do so through the constraints in the table.

6.1 MySQL-Supported integrity constraints

The so-called completeness of the table refers to the accuracy and consistency of the data, while the integrity check refers to checking the accuracy and consistency of the data.

Integrity constraints

Integrity constraint Keywords	Meaning
Not NULL	The value of the constraint field cannot be empty
DEFAULT	Set the default value for a field
UNIQUE KEY (UK)	The value of the constraint field is unique
PRIMARY KEY (PK)	The constraint field is the primary key for the table and can be used as a unique identifier for the table record
Auto_increment	The value of the constraint field is automatically incremented
FOREIGN KEY (FK)	Constraint field is a foreign key of a table

The MySQL database management system does not support check constraints, that is, you can use check constraints without any effect.

Constraints can be divided into single-column constraints (each constraint constrains only one list of data) and multi-column constraints (each constraint can constrain multiple columns of data), depending on the constraint data column constraints.

6.2 Setting a non-null constraint (not null,nk)

Create Table table_name (property name   not NULL,...);

6.3 Setting the default value for a field

Create Table table_name (property name     default defaults,...);

6.4 Setting UNIQUE constraints (UNIQUE,UK)

Create Table table_name (attribute name   UNIQUE,...);

If you want to give a name to the constraint of a field that sets a unique constraint, you can execute the SQL statement constraint:

Create Table table_name (dbname property type,... CONSTRAINT UNIQUE (dbname));

6.5 Setting the PRIMARY KEY constraint (PRIMARY key,pk)

When you set a PRIMARY KEY constraint, the value that must satisfy the primary key field is unique and non-empty.

Single Field primary key:

Create Table table_name (attribute name   PRIMARYKEY,...);

If you want to give a name to the constraint of a field that sets a single primary key, you can execute the SQL statement constraint:

Create Table table_name (dbname property type,... CONSTRAINT PRIMARY KEY (dbname));

Multi-field Primary key:

When a primary key is composed of multiple fields, it needs to be implemented by SQL statement CONSTRAINT :

Create Table table_name (property name 1 data type, property name 2 data type,... CONSTRAINT PRIMARY KEY (attribute name 1, attribute name 2));

6.6 Setting field values autogrow (auto_increment)

Auto_increment is MySQL's only extended integrity constraint, and when a new record is inserted into a database table, the value on the field automatically generates a unique ID. Only one field in a database table can use the constraint, and the data type of the field must be an integer type. Because the field after setting the auto_increment constraint generates a unique ID, the field is often set to the PK primary key.

Create Table table_name (property name  data type auto_increment,...);

6.7 Setting foreign KEY constraints (FOREIGN KEY,FK)

The preceding integrity constraints are set in a single table, while the outer key constraint guarantees referential integrity between multiple tables (typically two tables), which is the reference relationship that is built between the two fields in two tables.

Create Table table_name (property name data type, property name data type,... CONSTRAINT FOREIGN KEY REFERENCES table Name (attribute name 2));

MySQL's operations on tables