Mysql common data types in detail and examples (Note 1)
1.Mysql under Windows
Net start mysql[]
Net stop mysql[]
MySQL Quit[from the command line.
\C[to cancel the command input.
Select version(),current_date()
The Mysql version number, the date (date as 2013-7-15)
Now()Now the time (date when the minutes and seconds such as 2013-07-15 08:29:56)
User() The user
2 when a simple calculator
select sin( pi()/6),100/3;
3 not all in a row is given a command, a longer command can be input into multiple lines. Mysql by looking for terminating semicolon but not the end of the input rows where the end decision statement.
The Mysql state
Prompt |
Meaning |
Mysql> |
Ready to accept new orders |
-> |
Waiting for the command line below the line |
‘> |
Wait for the next line, waiting with a single quotation mark (“ ’ ”) at the end of the string |
“ |
Wait for the next line, waiting with a double quote (“ ’ ”) at the end of the string |
` |
Wait for the next line, waiting to anti skew point (“ `”) at the end of the string |
/* |
Wait for the next line, waiting for the end begins with / * comments |
Note: when the input ’ “,,, without input and the corresponding end cannot enter a new command
4.Mysql commonly used types of columns
The string data type
Data type |
Explain |
char |
1-255 characters of fixed length string. His length must be specified on creation or Mysql is assumed to be char(1) |
varchar |
Variable length, the maximum length of the whole body is 65532 bytes if you create a designated as varchar (n), you can store 0-n characters in a variable length string (n<=65535) character set and |
tinytext |
As with text, the maximum length of 255 bytes |
mediumtext |
The same as text, but the maximum 16K |
text |
The maximum length of variable length text 64K |
longtext |
The same as text, but the maximum length is 4GB |
enum |
Accept up to 64K a string consisting of a predefined set of a string |
set |
Accept up to 64K a string consisting of a predefined set of 0 or more strings |
Numeric data types
Data type |
Explain |
tinyint |
Integer values, -128-127 (if unsigned, 0-255 number of 1 bytes) |
smallint |
Integer values, support -32768-32767(unsigned,0-65535) 2 bytes |
mediumint |
-8388608-8388607(undesigned 0-) 3 byte 223 -223-223-1 |
int |
231 4 bytes |
bigint |
8 bytes |
float |
A single precision floating point value |
double |
Double precision floating point value |
boolean |
Boolean |
decimal |
Floating point precision variable value |
peal |
4 byte floating point value |
bit |
For the field, 1-64. (before the mysql5 function is equal to tinyint) |
Date and time data types
Data type |
Explain |
DATE |
Said 1000-01-01 to 9999-12-31 date, format(YYYY-MM-DD) |
DATETIME |
The combination of DATE and TIME |
TIMESTAMP |
Function with DATETIME (but to a lesser extent) |
TIME |
Format HH:MM:SS |
YEAR |
Represented by 2 digits, the range is 70 (1970) -69 (2069), 4 bits, 1901-2155 |
Binary data
Data type |
Explain |
TINYBLOB |
The maximum length of Blob is 255 bytes |
BLOB |
64kb |
MEDIUMBLOB |
16MB |
LONGBLOB |
4GB |
5 related data type specification supplement
Set out in the declaration of the optional attribute unsigned ,zerofill
The default tinyint Tinyint(4)
Only when zerofill brackets given length is only meaningful
For example, the definition of the zerofill tinyint (5) inserted into the 1 insertion is 00001
The decimal type:
Floating point (not standard), fixed type (4 or 8 bytes are complex)
Float(M,D) M does not count. The total number, D number of digits after the decimal point
For example, float (6,4) range
-9999.99-9999.99
688.896 insert is 688.90
1,3,7,The 9 round
2,4,6,8,5 digit
The fixed-point type:
Decimal(M,D)
Insert data into data difference with small
Character:
Char(M) M represents the character length can accommodate rather than byte size of a Chinese characters Z takes 3 bytes and it is still a character (according to coding and decide), The inserted data is less than the specified length with spaces filled out to remove trailing spaces, if the insertion of the tail of the data is with a space, then a deposit after trailing spaces will be lost, also for M character is less than M, utilization rate<=100%;
Varchar(M) Less than M, the maximum is 65535 bytes (ASCII), If the real storageN characters, n<=M, it must also use 1-2 bytes to record the actual save how many, how many taking, tail box will not be lost, the utilization of =n/ (n+1~2 bytes) <100%, less than 255 records with a byte, 2 bytes greater than with
That the above difference instance:
Create table t2
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 the tail with a space data
insert into t2(name,pass)
value('hk ','hk ');
Remove the data
select concat(name,'!'),concat(pass,'!') from t2;
Will find
The difference between Char and varchar
1.Char: 1-255 fixed length string Varchar:0-65535
2 real space are not the same and utilization efficiency
Treatment of trailing spaces of 3
Char (can not give the length of the default 1), the varchar must be given in the table is created when the length of a given length, not of type tinytext, text can not give to a given length, text data type is not set the default value
To date, the default for CURRENT_TIMESTAMP automatic filling value, a revision of the relevant information in order to ensure that in time with the modification, long and factors of on update changes
For example, create a time field
Columns are declared as:
·stamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMPModify column data attribute
Method a:
ALTER TABLE `t1` CHANGE `id` `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT
The alter table table name change field name field declaration (including the field name)
The unsigned must be placed after
Method two:
Alter table modify column field name attribute declarations...
Where and having
The data in the table, the table on the hard disk or in memory to file the form
Where for the table file function
Query results can also be seen as a table, the general temporary buffer file,
Having is for the query results (also can be the original table) play a role
Posted by Amber at December 15, 2013 - 12:26 AM