Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Introduction to SQL Basics

By: FYIcenter.com

Part:   1  2   3  4  5  6  7 

(Continued from previous part...)

What Are the Differences between CHAR and VARCHAR?

CHAR and VARCHAR are both ASCII character data types by default. But they have the following major differences:

• CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
• VARCHAR stores values in variable lengths. Values are not padded with any characters. But 1 or 2 extra bytes are added to store the length of the data.

The table below shows you a good comparison of CHAR and VARCHAR data types:

Value    CHAR(4)       Length 
''       '    '        4 bytes
'ab'     'ab  '        4 bytes
'abcd'   'abcd'        4 bytes 

Value    VARCHAR(4)    Length
''       ''            1 byte 
'ab'     'ab'          3 bytes
'abcd'   'abcd'        5 bytes 

What Are the Differences between BINARY and VARBINARY?

Both BINARY and VARBINARY are both binary byte data types. But they have the following major differences:

• BINARY stores values in fixed lengths. Values are padded with 0x00.
• VARBINARY stores values in variable lengths. Values are not padded with any bytes. But 1 or 2 extra bytes are added to store the length of the data.

What Are Numeric Data Types?

MySQL supports the following numeric data types:

• BIT(n) - An integer with n bits.
• BOOL same as BOOLEAN - Boolean values stored in 1 bit.
• TINYINT - A small integer stored in 1 byte.
• SMALLINT - A small integer stored in 2 bytes.
• MEDIUMINT - A medium integer stored in 3 bytes.
• INT same as INTEGER - An integer stored in 4 bytes.
• BIGINT - An integer stored in 8 bytes.
• FLOAT - A single-precision floating-point number stored in 4 bytes.
• DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.
• REAL - Same DOUBLE by default.
• DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).

What Are Date and Time Data Types?

MySQL supports the following date and time data types:

• DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
• DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
• TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
• TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
• TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
• YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".

How To Calculate Expressions with SQL Statements?

There is no special SQL statements to calculate expressions. But you can use the "SELECT expression FROM DUAL" statement return the calculated value of an expression. "DUAL" is a dummy table in the server. The tutorial exercise below shows you some good examples:

SELECT 'Hello world!' FROM DUAL; 
   Hello world!

SELECT (1+2)*3/4 FROM DUAL;
   2.2500

SELECT TRUE FROM DUAL;
   1

SELECT TRUE AND FALSE FROM DUAL;
   0

SELECT TIME(SYSDATE()) FROM DUAL;
   21:30:26

(Continued on next part...)

Part:   1  2   3  4  5  6  7