1, the preface

Today when the interview byte, very helpless, the interviewer does not test query statement, test is to build a table sentence, which I will? The types inside are basically forgotten, today do an overall record

2. Integer type

The integer types are TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. The following table describes the storage space and value range

3. Real number types

FLOAT

Single-precision floating-point type, using 8 bits —-B

DOUBLE

A double-precision floating-point type, using 16-bit storage —-2B

DECIMAL

When float and double are calculated, precision loss will occur. The reasons for precision loss can be found in the following article: Boss, why would YOU deduct my salary for storing money in float you can use DECIMAL for precision calculations, using DECIMAL requires extra space and overhead, so use it if and only if precision calculations are required

4. String type

1. The VARCHAR and CHAR

Varchar and char are very, very common string types

VARCHAR

VARCHAR is used to store variable length strings, which require an extra 1 or 2 bytes to record the length of the string:

• If the maximum column length is less than or equal to 255 => use 1 byte
• If the column length is greater than 255 => use 2 bytes

This applies to the scenario where VARCHAR is used as the storage type:

• Rarely updated columns => Frequently updated columns are prone to page splitting
• If the column length is not fixed => VARCHAR stores only necessary space, saving space

CHAR

CHAR is used to store fixed-length strings. When storing the CHAR type, all trailing Spaces are removed

Scenarios where CHAR is used as the storage type

• Columns are almost constant in length
• Short column length => VARCHAR requires additional byte storage length
• Columns are updated frequently

BLOB and TEXT types

Both BLOB and TEXT types are used to store large amounts of data, such as article content

BLOB

In binary storage, BLOB subdivisions can be divided into TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, and LONGBLOB

TEXT

Using character storage, TEXT subdivision can be divided into TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

When BLOB and TEXT values become too large, InnoDB storage uses an external storage area to store the values and then stores a 1 – to 4-byte pointer to the external storage

5. Date and time types

Common date types are DATETIME and TIMESTAMP

DATETIME

Using 8-byte storage, you can store a wide range of values from 1001 to 9999 years

TIMESTAMP

Use 4-byte storage, save range is smaller than DATETIME, from 1970 to 2038

You can use DOUBLE or BIGINT for dates and times that need to be stored at a smaller granularity, but you can also use BIGINT

6. Construct the method of expression

CREATE TABLE table_name (column_name column_type);
Copy the code

• Remember, write the table name first, then the type of the table

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE.PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

7, interview

• In the interview, the examiner asked, create a list of about 10,000 students, including student id, student name, student gender, student address, you need to choose a reasonable type

CREATE TABLE IF NOT EXISTS 'students'{
    'student_id' SMALLINT AUTO_INCREMENT NOT NULL.'name' varchar(20) not null.'age' TINYINT NOT NULL.'sex' bit default 1.'birth' datetime not null.'address' varchar(255) not null.PRIMARY KEY('student_id')
}ENGINE=InnoDB default CHARSET = UTF- 8 -;
Copy the code

• Id: 10000 – The id can be smallint
• Name: A maximum of 20 names
• Age: indicates that the age must be younger than 100, which can be identified by TINYINT
• Sex: Can be identified with a single bit
• Birth requires an adoption date
• Address: generally varchar (255)
• Set increment and primary key

reference

• Juejin. Cn/post / 684490…