MySQL study note _ 2_ SQL data model and data type

Create a data table (on) in MySQL)

I. Create an SQL statement model for a data table [weak type]

Create table [if not exists] TABLE Name (

Type of field name 1 column [attribute] [Index],

Type of field name 2 column [attribute] [Index],

...

Type of field name n column [attribute] [Index],

) [Table attributes] [Table character set];

[Table name. You must name the field name]

[SQL statements are case-insensitive, but a file name is not case-sensitive in Windows, but case-sensitive in Linux/UNIX]

[Naming rules: 1. Meaningful (English, English combination or abbreviations)

2. The name must be in lower case.

3. It is recommended that SQL statements be capitalized .]

1. create table users (

Id INT,

Name CHAR (32)

);

2. show tables;

3. DESC users;

4. SELECT * FROM users;

Ii. Data Value and column type

They are all subdivided by space size and can be saved!

1. numeric type

Integer ):

Very small integer type 1 byte-128 ~ 127 0 ~ 255 (unsigned) TINYINIT

Small integer type 2-32768 ~ 32767 smallineit

Medium-Size integer 3-byte 0 ~ 16777215 MEDIUMIINT

Standard integer 4-2147483648 ~ 2147483647 INT

8-byte BIGINT type

[Attributes can be added: UNSIGNED]

Float (decimal ):

FLOAT (M, N) 4-byte [length M, retain N digits after the decimal point]

DOUBLE (M, N) 8 bytes

DECIMAL (M, N) M + 2 bytes [number of points, data is stored in the form of strings, more accurate data storage, but the efficiency will be discounted .]

[Floating point numbers may have errors. Therefore, when you are sensitive to numbers, you need to use a fixed number of points for storage !]

2. Balanced type

MySQL supports the string type using single quotes and double quotes.

For example, "MySQL" and "MySQL" are the same.

Char (M) up to 255 fixed-length strings

Varchar (M) up to 255 variable-length strings

Char (4) varchar (4) // always one byte more than the character

''4'' 1 byte

'A' 4 'A' 2 bytes

'Abcd' 4 'abcd' 5 bytes

'Abcdefg' 4 'abcdefgh' 5 bytes

Char truncates trailing spaces;

Example: create table if not exists tab (v varchar (4), c char (4 ));

Insert into tab (v, c) VALUES ("AB", "AB ");

Select concat (v, "#"), CONCAT (c, "#") FROM tab;

Best practices:

Fixed Length. char type is recommended (May waste some space ).

Variable Length. varchar type is recommended (may cause some performance loss)

Text Data: article, log length: 2 ^ 16-1

MEDIUMTEXT

LONGTEXT

Blob binary data: photo, movie length: 2 ^ 16-1

MEDIUMBLOB

LONGBLOB

[Although we cannot insert binary files such as photos, movies, and compressed packages into the database using SQL statements, we can use C ++/Java and other programming languages to convert binary files into binary data streams, then save it to the database]

ENUM enumeration 1 ~ 2 bytes

ENUM ("one", "two", "three", "four ")~ 65535

Only one value can be entered at a time.

SET 1, 2, 3, 4, 8 bytes

SET ("one", "two", "three", "four ")~ 64

There can be multiple values at a time, separated by commas.

3. date type

DATE YYYY-MM-DD // if the number of digits is sufficient, you do not need to add '-'

TIME hh: mm: ss // The number of digits is sufficient. Ignore ':' and the number of digits is insufficient.

DATETIME YYYY-MM-DD hh: mm: ss.

When the number of TIMESTAMP YYYYMMDDhhmmss is insufficient, the leading 0 cannot be ignored.

YEAR YYYY

[When creating a data table, it is best not to use these time values. It is best to use integers in C ++/Java and other programming languages to save the time, which is more convenient for calculation, such as int in C ++, in PHP, the timestamp is]