Mysql common data types in detail and examples (Note 1)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

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_TIMESTAMP

Modify 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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Amber at December 15, 2013 - 12:26 AM