MySQL 3. Creating a data table

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

Relational database is actually a two-dimensional table, more rigorous refers to the data table, two-dimensional table row called records, columns called fields.

If you open the database to confirm whether it is the database you want, enter select Database ();

To create a data table step:

• 1.USE database name;
• 2.CREATE table datasheet name (column name data type, ... ）;

Demo:

1 CREATE TABLE Test (2VARCHAR),3TINYINT  UNSIGNED, 4 FLOAT (8,2) insigned 5 );

View Code

Unsigned in demo indicates unsigned

View Data sheet:

• Displays the data table of the current database show TABLES;
• Displays the data table in the specified name database show TABLES from database name;

View data table structure: Show COLUMNS from data table name

add a record to the table:

• Add records to all tables (each column requires a value) INSERT data table name values (val,...);
• Selectively add data to a few columns INSERT data table name (column name 1,...) VALUES (Val1,...);

Demo:

INSERT VALUES ('gzc',+,999999.99); INSERT VALUES ('gzc',1);

View Codenull and NOT NULL:

When you create a table, you can set whether a column's properties are allowed to be empty

Demo

1 CREATE TABLE test3 (2VARCHAR(notNULL,3  TINYINT4 );

View Code

Data columns are allowed to be null by default

Auto Number (auto_increment):

• AutoNumber (Auto_increment) and must be used in conjunction with the primary key
• By default, the starting value is 1, and each increment is 1
• Fields that are automatically grown must be numeric (integer or floating point), and decimal digits must be 0 when floating-point

PRIMARY Key:

• Set Primary key is available PRIMARY key can also be written directly as key
• Only one primary key per data table
• The primary key guarantees the uniqueness of the data
• Primary key is not NULL automatically
• Primary keys are not necessarily used in conjunction with Auto_increment
• Primary keys can be assigned values but cannot be the same value

Demo:

CREATE TABLE  TINYINTPRIMARYKEYCHAR(());

View CodeUnique constraint (unique KEY):

• Unique constraints guarantee the uniqueness of records
• The field of a unique constraint can be null (only one null value is saved)
• Multiple unique constraints can exist for each table

CREATE TABLE  SMALLINTPRIMARYKEY VARCHAR (not NULL UNIQUE KEY  TINYINT  UNSIGNED);

View CodeDefault constraints:

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

Demo:

1 CREATE TABLETest5 (2IdSMALLINTUNSIGNED auto_incrementPRIMARY KEY,3UsernameVARCHAR( -) not NULL UNIQUE KEY4Sex ENUM ('1','2','3')DEFAULT '3'5);

View Code

MySQL 3. Creating a data table