Saturday, January 29, 2011

MySQL data type and some useful commands


NUMBER TYPES
TINYINT( )-128 to 127 normal
0 to 255 UNSIGNED.
SMALLINT( )-32768 to 32767 normal
0 to 65535 UNSIGNED.
MEDIUMINT( )-8388608 to 8388607 normal
0 to 16777215 UNSIGNED.
INT( )-2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED.
BIGINT( )-9223372036854775808 to 9223372036854775807 normal
0 to 18446744073709551615 UNSIGNED.
FLOATA small number with a floating decimal point.
DOUBLE( , )A large number with a floating decimal point.
DECIMAL( , )A DOUBLE stored as a string , allowing for a fixed decimal point.
TEXT TYPES
CHAR( )A fixed section from 0 to 255 characters long.
VARCHAR( )A variable section from 0 to 255 characters long.
TINYTEXTA string with a maximum length of 255 characters.
TEXTA string with a maximum length of 65535 characters.
BLOBA string with a maximum length of 65535 characters.
MEDIUMTEXTA string with a maximum length of 16777215 characters.
MEDIUMBLOBA string with a maximum length of 16777215 characters.
LONGTEXTA string with a maximum length of 4294967295 characters.
LONGBLOBA string with a maximum length of 4294967295 characters.



DATE TYPES
DATEYYYY-MM-DD.
DATETIMEYYYY-MM-DD HH:MM:SS.
TIMESTAMPYYYYMMDDHHMMSS.
TIMEHH:MM:SS.

MISC TYPES
ENUM ( )Short for ENUMERATION which means that each column may have one of a specified possible values.
SETSimilar to ENUM except each column may have more than one of the specified possible values.

Counting Rows

SELECT COUNT(*) FROM pet;
SELECT owner, COUNT(*) FROM pet GROUP BY owner;
SELECT COUNT(score) AS n,
    -> SUM(score) AS sum,
    -> MIN(score) AS minimum,
    -> MAX(score) AS maximum,
    -> AVG(score) AS mean,
    -> STD(score) AS 'std. dev.'
    -> FROM testscore;

No comments:

Post a Comment