Home >> FAQs/Tutorials >> MySQL Tutorials

MySQL FAQs - Introduction to SQL Basics

By: FYIcenter.com

Part:   1   2  3  4  5  6  7 

A collection of 26 FAQs on MySQL SQL basics. Clear answers are provided with tutorial exercises on character and numeric data types; quoting character strings; ASCII and UTF8 character sets; NULL values and NULL conditions; IN and LIKE conditions. Topics included in this collection are:

1. What Is SQL?
2. How Many Groups of Data Types?
3. What Are String Data Types?
4. What Are the Differences between CHAR and NCHAR?
5. What Are the Differences between CHAR and VARCHAR?
6. What Are the Differences between BINARY and VARBINARY?
7. What Are Numeric Data Types?
8. What Are Date and Time Data Types?
9. How To Calculate Expressions with SQL Statements?
10. How To Include Comments in SQL Statements?
11. How To Include Character Strings in SQL statements?
12. How To Escape Special Characters in SQL statements?
13. How To Concatenate Two Character Strings?
14. How To Include Numeric Values in SQL statements?
15. How To Enter Characters as HEX Numbers?
16. How To Enter Numeric Values as HEX Numbers?
17. How To Enter Binary Numbers in SQL Statements?
18. How To Enter Boolean Values in SQL Statements?
19. What Are NULL Values?
20. What Happens If NULL Values Are Involved in Expressions?
21. How To Convert Numeric Values to Character Strings?
22. How To Convert Character Strings to Numeric Values?
23. How To Use IN Conditions?
24. How To Use LIKE Conditions?
25. How To Use Regular Expression in Pattern Match Conditions?
26. How To Use CASE Expression?

Please note that all answers and tutorials are based on MySQL 5.0. Tutorial exercises should be executed with "mysql" or other MySQL client programs.

What Is SQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

How Many Groups of Data Types?

MySQL support 3 groups of data types as listed below:

• String Data Types - CHAR, NCHAR, VARCHAR, NVARCHAR, BINARY, VARBINARY, TINYBLOB, TINYTEXT, BLOB, TEXT, MEDIUMBLOB, MEDIUMTEXT, LONGBLOB, LONGTEXT, ENUM, SET
• Numeric Data Types - BIT, TINYINT, BOOLEAN, SMALLINT, MEDIUMINT, INTEGER, BIGINT, FLOAT, DOUBLE, REAL, DECIMAL.
• Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

What Are String Data Types?

MySQL supports the following string data types:

• CHAR(n) same as CHARACTER(n) - Fixed width and " " padded characters strings. Default character set is ASCII.
• NCHAR(n) same as NATIONAL CHARACTER(n) - Fixed width and " " padded character strings with UTF8 character set.
• VARCHAR(n) same as CHARACTER VARYING(n) - Variable width character strings. Default character set is ASCII.
• NVARCHAR(n) same as NATIONAL CHARACTER VARYING(n) - Variable width character strings with UTF8 character set.
• BINARY(n) - Fixed width and 0x00 padded byte strings.
• VARBINARY(n) same as BINARY VARYING(n) - Variable width byte string.
• TINYBLOB - BLOB (Binary Large Object) upto 255 bytes.
• BLOB - BLOB (Binary Large Object) upto 64K bytes.
• MEDIUMBLOB - BLOB (Binary Large Object) upto 16M bytes.
• LONGBLOB - BLOB (Binary Large Object) upto 4G bytes.
• TINYTEXT - CLOB (Binary Large Object) upto 255 characters.
• TEXT - CLOB (Binary Large Object) upto 64K characters.
• MEDIUMTEXT - CLOB (Binary Large Object) upto 16M characters.
• LONGTEXT - CLOB (Binary Large Object) upto 4G characters.
• ENUM - An enumeration to hold one entry of some pre-defined strings.
• SET - A set to hold zero or more entries of some pre-defined strings.

What Are the Differences between CHAR and NCHAR?

Both CHAR and NCHAR are fixed length string data types. But they have the following differences:

• CHAR's full name is CHARACTER.
• NCHAR's full name is NATIONAL CHARACTER.
• By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.
• By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.
• Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.

The following column definitions are the same:

CREATE TABLE faq (Title NCHAR(80));
CREATE TABLE faq (Title NATIONAL CHAR(80));
CREATE TABLE faq (Title NATIONAL CHARACTER(80));
CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);
CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);

(Continued on next part...)

Part:   1   2  3  4  5  6  7