Mysql common data types and instance descriptions (study Note 1)

1. Mysql

Net start mysql [

Net stop mysql [

Quit [

\ C [

Select version (), current_date ()

Mysql

Now ()

User () User

Select sin (pi ()/6), 100/3;

 

Prompt	Description
Mysql>	Ready to accept new commands
->	Wait for the next line of the multi-line command
'>	Wait for the next line, and wait for the single quotation mark "'")
"	Wait for the next line, and wait for the double quotation mark "'")
`	Wait for the next line, and wait for the reverse oblique "'")
/*	Wait for the next line.

 

Note: When you enter

 

 

① String data type

Data Type	Description
Char	A fixed-length string of 1 to characters. The length must be specified at creation. Otherwise, Mysql is assumed to be char (1)
Varchar	Variable Length. the maximum length of the whole body is 65,532 bytes. If it is specified as varchar (n) at creation, the variable length string (n <= 65535) Character Set with 0 to n characters can be stored.
Tinytext	Same as text, up to 255 bytes
Mediumtext	Same as text, but up to 16 k
Text	Variable-length text with a maximum length of 64 KB
Longtext	Same as text, but the maximum length is 4 GB
Enum	A string of a predefined set consisting of up to 64 K strings.
Set	Accept 0 or multiple strings in a predefined set consisting of up to 64 K strings

Numeric data type

Data Type	Description
Tinyint	Integer, supporting-128-127 (if unsigned, 0-255) of 1 byte
Smallint	Integer, supporting-32768-32767 (unsigned, 0-65535) 2 bytes
Mediumint	-8388608-8388607 (undesigned 0-) 3 bytes 223 -223-223-1
Int	231 4 bytes
Bigint	8 bytes
Float	Single-precision floating point value
Double	Double-precision floating point value
Boolean	Boolean
Decimal	Variable-precision floating point value
Peal	4-byte floating point value
Bit	It is a field, 1-64 bits. (The function is tinyint before mysql5)

 

 

 

Date and Time Data Types

Data Type	Description
DATE	Represents the date from 1000-01-01 to 9999-12-31, in YYYY-MM-DD format)
DATETIME	Combination of DATE and TIME
TIMESTAMP	Functions are the same as DATETIME (but the range is small)
TIME	Format: HH: MM: SS
YEAR	It is represented by two digits in the range of 70 (1970)-69 (2069), and four digits in the range of 1901-2155.

 

Binary data

Data Type	Description
TINYBLOB	Blob can contain up to 255 bytes.
BLOB	64kb
MEDIUMBLOB	16 MB
LONGBLOB	4 GB

 

Optional attribute unsigned and zerofill in column Declaration

Tinyint

Only when

For example

Decimal type:

Floating Point Type

Float (M, D) M

For example

-9999.99-9999.99

688.896

1, 3, 7, 9

2, 4, 6, 8, 5

Fixed Point Type:

Decimal (M, D)

There is little difference between data inserted and stored data.

Character Type:

Char (M) M, not the byte size, a Chinese character byte, and it is still a character (when the inserted data is smaller than the specified length, remove the trailing space when it is filled with spaces, if the end of the inserted data is a space, the space at the end of the last part of the data will be lost, less

 

Varchar (M)

Proof of the above differences:

Create a table

Create table 't2 '(

'Id' int (10) unsigned not null auto_increment,

'Name' char (7) not null,

'Pass' varchar (8) not null,

Primary key ('id ')

) ENGINE = InnoDB default charset = utf8

Insert data with spaces at the end

Insert into t2 (name, pass)

Value ('hk ', 'hk ');

Retrieve Data

Select concat (name ,'! '), Concat (pass ,'! ') From t2;

Will find

 For date-type data, when the default value is auto-filled, such as CURRENT_TIMESTAMP, to ensure that the time in this item is modified with the modification of a related information, the factors for changing the length plus on update

For example, create a time field

Column declaration:

·stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Modify column data attributes

Method 1:

Alter table 'T1 'change 'id' INT (10) unsigned not null AUTO_INCREMENT

Alter table

Alter table name modify column field name attribute declaration...

Where and having