About MYSQL, you need to know the data types and operation tables

Data Types and Operations Data Table

1.1 MySQL type: integer

1.2 MySQL data type: floating point type

1.3 Date and time type

DATE 1 Support period: January 1, 1000 ~ December 31, 9999
DATETIME 3 supports time: 00:00 on January 1, 1000 to 23:59:59 on December 31, 9999
TIMESTAMP 3 supports dates from January 1, 1970 to December 31, 2037
TIME 8 Support time: -8385959~8385959
YEAR 4 Support time: 1970-2069

1.4 Character Type

CHAR: fixed length, if the length is less than the specified length, add spaces at the end.
VARCHAR: variable length (variable)
The extra bytes in L+1 or L+2 are used to store the length of the data value. When processing variable-length data, MySQL saves both the data content and the data length.
1 refers to the maximum storage range of L, 1 byte is 8 1s.
For a signed number, the highest bit is the sign bit, but the character type is an unsigned number, so 2^7+2^6+2^5+2^4+2^3+2^2+2^1+1=255, that is, the first L<=255.

Four data types:

Integer: TINYINT SMALLINT MEDIUMINT BIGINT

Floating point type: FLOAT or DOUBLE[(M,D)] M is the total number of digits and D is the number of digits after the decimal point

Date and time type: less used, mostly stored in the form of timestamp

Character type char(5) 5-byte char type

1.5 Create a data table

The data table (or table) is one of the most important components of the database and the basis of other objects.
USE: Open the data table
USE database name; MySQL login steps:
1. Enter username -u
2. Enter password -p
3. Enter the port number-P (uppercase)

Show all databases:

show databases

Open the database to be executed

use database library name

Show an open database:

Set select database
CREATE TABLE tb1(

username VARCHAR(20),

age TINYINT UNSIGNED,

salary FLOAT(8,2) UNSIGNED

);

//Created a tb1 table containing user name, age, and salary.

1.6 View Data Table

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

1.7 View the data table structure

SHOW COLUMNS FROM tbl_name

1.8 Inserting and searching MySQL records

Insert Record

INSERT [INTO] tblname [(colname,...)] VALUES(val,..)
insert into tb1 values('jack',18,6000.00);

Record search

SELECT expr,... FROM tbl_name;
select * from tb1;

1.9 MySQL null and non-null values

NULL, the field value can be empty
NOT NULL, field values ​​cannot be empty. Add NULL and NOT NULL when inserting fields.

1.10 Automatic numbering

AUTO_INCREMENT

Automatic numbering, and must be used in combination with the primary key. By default, the starting value is 1, and the increment is 1 each time;

1.11 Introduction to primary key constraints:

PRIMARY KEY

Primary key constraint Each data table can only have one primary key. The primary key ensures the uniqueness of the record. The primary key is automatically NOT NULL.
AUTO_INCREMENT must be used together with PRIMARY KEY.
But PRIMAYR KEY does not have to be used with AUTO_INCREMENT.

1.12 Introduction to unique constraints

UNIQUE KEY

Unique Constraint

The unique constraint can ensure the uniqueness of the record

The unique constraint field can have a null value (NULL)

Each data table can have multiple unique constraints

create table tb5(

id smallint unsigned auto_increment primary key,

username VARCHAR(20) NOT NULL UNIQUE KEY,

age tinyint UNSIGNED

);

1.13 Default Constraints

DEFAULT

Default value When inserting a record, if no value is explicitly assigned to the field, a default value is automatically assigned.

create table tb6(

id smallint unsigned auto_increment primary key,

username varchar(20) not null unique key,

sex enum('1','2','3') default '3'

);

Summarize

This is the end of this article about the data types and operation data tables you need to know about MYSQL. For more relevant MYSQL data types and operation data table content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!