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

Source: Internet
Author: User

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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.