Data tables in MySQL

Overview:

A data table (or table) is one of the most important components of a database and is the basis for other objects.

A relational database is a two-dimensional table

The line is called a record

Columns are called fields

Open database: use+ database name

View selected Databases select database ();

Create a data table

CREATE TABLE [if NOT EXISTS] table_name (column_name data_type,.. ）

See if the data table exists, show TABLES [from db_name] [like ' pattern ' | WHERE Expr]

View all data tables in the MySQL database show TABLES from MySQL;

View data table structure: Show COLUMNS from Db_name;

INSERT

Inserting records

INSERT [into] tbl_name [(Col_name,..)] VALUES (Val,...);

Record lookup: SELECT expr,... From Tbl_name

NULL and NOT NULL NULL and non-empty

Guarantee the uniqueness of records: Auto_increment

Automatically numbered and must be combined with the primary key

By default, the starting value is 1, and each increment is 1

About PRIMARY key: PRIMARY key

PRIMARY KEY constraint

Only one primary key can exist per data table

The primary key guarantees the uniqueness of the record

Primary key is not NULL automatically

The primary key allows assignment, but the same value cannot be assigned.

Note: The auto_increment must be used with the primary key primary key, but the primary key primary key can not be used with auto_increment.

Unique constraint

UNIQUE KEY

Unique constraint

Unique constraints guarantee the uniqueness of records

A field of a unique constraint can be controlled (NULL)

Multiple unique constraints can exist for each data table

Primary KEY constraints a single data table can have more than one unique constraint, but there are some differences when creating an index.

Default constraint

Default value

Automatically assigns a default value when you insert a record without explicitly assigning a value to the field

Data type: Character, integer, float, date-time

Data table operations: Inserting records, finding records

Record actions: Creating a data table, using constraints

Data tables in MySQL