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

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