Database--Lesson Two

Create a data table

Before we create a data table, let's look at the data types.

1. Integral type

TINYINT 1 bytes

SMALLINT 2 bytes

Mediumint 3 bytes

INT 4 bytes

BIGINT 8 bytes

2. Decimal (floating point type)

float[(M,d]

double[(M,D)]

where m represents the total number of digits, and D represents the number of digits after the decimal point

3. Date type

Year 1 bytes

Time 3 bytes

DATE 3 bytes

DATETIME 8 bytes

TIMESTAMP 4 bytes

4. Character type

CHAR (M)

VARCHAR (M)

TEXT

Tinytext

Mediumtext

Longtext

ENUM (' Values ', ' values2 ', ' values3 ' ...) Enumeration

SET (' Values ', ' values2 ', ' values3 ' ...) Collection

First, if you need to use a database before you need to

Use database name

Okay, now we're going to show you how to create a data table.

1. Create a data table

CREATE TABLE (IF not EXISTS) tb1 (column_name data_type,column_name1 data_type,.....);

Example:

CREATE TABLE tb1 (id varchar), username varchar (+), sex CHAR (Ten), Age TINYINT UNSIGNED);

Where unsigned represents the unsigned bit. is a positive number.

2. View Data Sheet

SHOW Tables[from db_name];

3. View Data Structures

SHOW COLUMNS from Tb1_name;

4. Inserting records (that is, assigning values to data tables)

INSERT [into] tb1_name (column_name1,column_name2,..) VALUES (Values1,values2);

INSERT tb1_name (username,age) VALUES ("TOM", 18);

Or if all the values are assigned to a value.

INSERT [into] tb1_name VALUES (Values1,values2,values3,...);

5,not null and NULL

CREATE TABLE Tb1_name (

Username VARCHAR () not NULL,

Age TINYINT UNSIGNED NULL

)

If the following is neither write null nor write not NULL, then the number can be null, which means null by default. If not NULL is set, the item cannot be empty when inserting data to the table.

6.auto_increment

Automatically numbered and must be used in conjunction with the primary key, with a default starting value of 1 and an increase of 1 each time

Primary key is primary key

Each table has only one primary key

Uniqueness of the primary key to save records

The data marked with the primary key cannot be empty, that is, not null;

Also be sure to pay attention. Auto_increment must be used in conjunction with primary key, but primary key does not have to be combined with auto_invrement.

Example:

CREATE TABLE tb1 (

ID samllint UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR (+) not NULL

)

Also note that because the ID here we set the primary key, and is self-increment, so if we do not assign the value when inserting data, the system will automatically give this value from 1 to 1 to each set of data

7.UNIQUE KEY (Unique constraint)

Unique constraints can also guarantee the uniqueness of the data

A unique constraint can be a null value

Each data table unique constraint can be used by multiple

Example:

CREATE TABLE Tb5 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username VARCHAR () not NULL UNIQUE KEY,

Age TINYINT UNSIGNED

)

INSERT tb3 (username,age) VALUES ("TOM", 26)

INSERT tb3 (username,age) VALUES ("TOM", 27)

This will cause an error, because username is the only constraint, so it cannot be repeated,

The second ID is self-growing, so you can not assign a value.

8.DEFAULT (default value)

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

CREATE TABLE Tb3 (

ID SMALLINT UNSIGNED auto_increment PRIMARY KEY,

Username Varcahr () not NULL UNIQUE KEY,

Sex ENUM ("male", "female") DEFAULT "male"

)

To summarize: This chapter mainly describes how to create a data table, in addition to introduce the data standards of several major constraints, and its characteristics.

Database--Lesson Two