MySQL study note _ 2_MySQL create a data table (on)

MySQL study note _ 2_MySQL create a data table (on)
Create a data table (on) in MySQL)

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

 

CREATETABLE [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. CREATETABLE users (

IdINT,

NameCHAR (32)

);

2. SHOWTABLES;

3. DESCusers;

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 [five homes and six entries]

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 .] [Rounding]

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

 

3. Balanced

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: createtable if not exists tab (v varchar (4), c char (4 ));

INSERTINTO tab (v, c) VALUES ("AB", "AB ");

SELECTCONCAT (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.

4. 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-DDhh: 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: 0: 0]