MySQL Programming

If you want to enter numeric values as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a numeric value, if the expression context is a numeric value. Here are some good examples:

SELECT X'10' + 16 FROM DUAL;

32

SELECT 0x1000 + 0 FROM DUAL;

4096

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

If you want to include character strings in your SQL statements, you need to quote them in one of the following formats:

► Using single quotes. For example 'GlobalGuideLine.com'.

► Using double quotes. For example "ggl Center".

► Using single quotes prefixed with N for NATIONAL characters (same as UTF8 characters). For example N'Allo, Francois.'.

► Using single quotes prefixed with _utf8 for UTF8 characters. For example _utf8'Allo, Francois.'.

If you want to enter characters as HEX numbers, you can quote HEX numbers with single quotes and a prefix of (X), or just prefix HEX numbers with (0x). A HEX number string will be automatically converted into a character string, if the expression context is a string. Here are some good examples:

SELECT X'313233' FROM DUAL;

123

SELECT 0x414243 FROM DUAL;

ABC

SELECT 0x46594963656E7465722E636F6D FROM DUAL;

GlobalGuideLine.com

If you want concatenate multiple character strings into one, you need to use the CONCAT() function. Here are some good examples:

SELECT CONCAT('Welcome',' to') FROM DUAL;

Welcome to

SELECT CONCAT('ggl','center','.com') FROM DUAL;

GlobalGuideLine.com

If you want to enter character strings or numeric values as binary numbers, you can quote binary numbers with single quotes and a prefix of (B), or just prefix binary numbers with (0b). Binary numbers will be automatically converted into character strings or numeric values based on the expression contexts. Here are some good examples:

SELECT B'010000010100001001000011' FROM DUAL;

ABC

SELECT 0b1000 + 0 FROM DUAL;

8

You can convert numeric values to character strings by using the CAST(value AS CHAR) function as shown in the following examples:

SELECT CAST(4123.45700 AS CHAR) FROM DUAL;

4123.45700

-- How to get rid of the last 2 '0's?

SELECT CAST(4.12345700E+3 AS CHAR) FROM DUAL;

4123.457

SELECT CAST(1/3 AS CHAR);

0.3333

-- Very poor conversion

If you want to enter Boolean values in SQL statements, you use (TRUE), (FALSE), (true), or (false). Here are some good examples:

SELECT TRUE, true, FALSE, false FROM DUAL;

+------+------+-------+-------+

| TRUE | TRUE | FALSE | FALSE |

+------+------+-------+-------+

| 1 | 1 |  0 |  0 |

+------+------+-------+-------+

You can convert character strings to numeric values by using the CAST(string AS DECIMAL) or CAST(string AS SIGNED INTEGER) function as shown in the following examples:

SELECT CAST('4123.45700' AS DECIMAL) FROM DUAL;

4123.46

-- Very poor conversion

SELECT CAST('4.12345700e+3' AS DECIMAL) FROM DUAL;

4123.46

-- Very poor conversion

SELECT CAST('4123.45700' AS SIGNED INTEGER) FROM DUAL;

4123

SELECT CAST('4.12345700e+3' AS SIGNED INTEGER) FROM DUAL;

4

-- Very poor conversion

A LIKE condition is also called pattern patch. There are 3 main rules on using LIKE condition:

* '_' is used in the pattern to match any one character.

* '%' is used in the pattern to match any zero or more characters.

* ESCAPE clause is used to provide the escape character in the pattern.

The following tutorial exercise provides you some good pattern matching examples:

SELECT 'GlobalGuideLine.com' LIKE '%center%' FROM DUAL;

1

SELECT 'GlobalGuideLine.com' LIKE '%CENTER%' FROM DUAL;

1

-- Case insensitive by default

SELECT 'GlobalGuideLine.com' LIKE '%CENTER_com' FROM DUAL;

1

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.

If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern condition: REGEXP. The following tutorial exercise provides you some good examples:

SELECT 'GlobalGuideLine.com' REGEXP '.*ggl.*' FROM DUAL;

1

SELECT 'GlobalGuideLine.com' REGEXP '.*com$' FROM DUAL;

1

SELECT 'GlobalGuideLine.com' REGEXP '^F.*' FROM DUAL;

1

An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.

Some examples are given in the tutorial exercise below:

SELECT 3 IN (1,2,3,4,5) FROM DUAL;

1

SELECT 3 NOT IN (1,2,3,4,5) FROM DUAL;

0

SELECT 'Y' IN ('F','Y','I') FROM DUAL;

1

If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:

SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;

09:26:50.123000

SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;

09:26:50.000123