MySQL

Selecting the proper column type for your tables is key to a successful database. Table B.10 lists the different string, number, and other types you can use, along with how much space they will take up on the server's hard drive. When choosing a type for each column, you should use the most efficient (i.e., the most size-frugal) data type given what the largest value of the column could be.

Table B.10. The list of available types for column definitions in a MySQL table.
MySQL Data Types
TYPE	SIZE	DESCRIPTION
`CHAR[Length]`	Length bytes	A fixed-length field from 0 to 255 characters long
`VARCHAR[Length]`	String length + 1 bytes	A variable-length field from 0 to 255 characters long
`TINYTEXT`	String length + 1 bytes	A string with a maximum length of 255 characters
`TEXT`	String length + 2 bytes	A string with a maximum length of 65,535 characters
`MEDIUMTEXT`	String length + 3 bytes	A string with a maximum length of 16,777,215 characters
`LONGTEXT`	String length + 4 bytes	A string with a maximum length of 4,294,967,295 characters
`TINYINT[Length]`	1 byte	Range of 128 to 127 or 0 to 255 unsigned
`SMALLINT[Length]`	2 bytes	Range of 32,768 to 32,767 or 0 to 65,535 unsigned
`MEDIUMINT[Length]`	3 bytes	Range of 8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned
`INT[Length]`	4 bytes	Range of 2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned
`BIGINT[Length]`	8 bytes	Range of 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned
`FLOAT`	4 bytes	A small number with a floating decimal point
`DOUBLE [Length, Decimals]`	8 bytes	A large number with a floating decimal point
`DECIMAL [Length, Decimals]`	Length + 1 or Length + 2 bytes	A `DOUBLE` stored as a string, allowing for a fixed decimal point
`DATE`	3 bytes	In the format of YYYY-MM-DD
`DATETIME`	8 bytes	In the format of YYYY-MM-DD HH:MM:SS
`TIMESTAMP`	4 bytes	In the format of YYYYMMDDHHMMSS; acceptable range ends in the year 2037
`TIME`	3 bytes	In the format of HH:MM:SS
`YEAR`	1 byte	In the format of either YY or YYYY
`ENUM`	1 or 2 bytes	Short for enumeration, which means that each column can have one of several possible values
`SET`	1, 2, 3, 4, or 8 bytes	Like `ENUM` except that each column can have more than one of several possible values
TINYBLOB	String length + 1 byte	A binary file with a maximum length of 255 characters
BLOB	String length + 2 bytes	A binary file with a maximum length of 65,535 characters
MEDIUMBLOB	String length + 3 bytes	A binary file with a maximum length of 16,777,215 characters
LONGBLOB	String length + 4 bytes	A binary file with a maximum length of 4,294,967,295 characters

When it comes to defining columns, remember that any column type can be NULL or NOT NULL, integers can be UNSIGNED, and any number can be ZEROFILL. An integer column can also be designated as AUTO_INCREMENT if it is set as the primary key for that table.

Table B.11 shows most of the operators and comparators used in SQL queries on a MySQL database. Most of these are part of the SQL standard and will also work with any database application.

Table B.11. The symbols and terms to use for performing operations and comparisons on a MySQL table.
MySQL Operators and Comparators
OPERATOR	MEANING
`+`	addition
`-`	subtraction
`*`	multiplication
`/`	division
`%`	modulus
`=`	equals
`<`	less than
`>`	greater than
`<=`	less than or equal to
`>=`	greater than or equal to
`!=`	not equal to
`IS NOT NULL`	has a value
`IS NULL`	does not have a value
`BETWEEN`	within a range
`NOT BETWEEN`	outside of a range
`OR` (also `\|\|`)	where one of two conditionals is true
`AND` (also `&&`)	where both conditionals are true
`NOT` (also `!`)	where the condition is not true
`LIKE`	where the value matches a string
`NOT LIKE`	where the value does not match a string
`%`	multiple wildcard character (used with LIKE and NOT LIKE)
`_`	single wildcard character (used with LIKE and NOT LIKE)
`REGEXP`	where the value matches a pattern
`NOT REGEXP`	where the value does not match a pattern

Table B.12 lists the special characters used in BOOLEAN mode in FULL TEXT searches. For example:

 SELECT * FROM tablename WHERE MATCH(column) AGAINST('+database -mysql'  IN BOOLEAN MODE)

Table B.12. Use these operators to fine-tune your `FULLTEXT` searches.
Boolean Mode Operators
OPERATOR	MEANING
`+`	Must be present in every match
`-`	Must not be present in any match
`~`	Lowers a ranking if present
`*`	Wildcard
`<`	Decrease a word's importance
`>`	Increase a word's importance
`""`	Must match the exact phrase
`()`	Create subexpressions

I talked about FULL TEXT searches and BOOLEAN mode in Chapter 5, "Advanced SQL and MySQL."

Table B.13 has the most basic SQL terms listed, most of which were discussed in Chapter 4, "Introduction to SQL and MySQL," and Chapter 5.

Table B.13. SQL has surprisingly few terms but can still do very complex procedures.
SQL Terminology
TERM	USAGE
`ALTER`	Change the structure of a table.
`CREATE`	Create a table or database.
`DELETE`	Delete rows from a table.
`DESCRIBE`	Reveal the structure of a table.
`DROP`	Delete entire tables or databases.
`INSERT`	Add a row to a table.
`SELECT`	Retrieve information from a database.
`SHOW`	Retrieve information about the structure of a database or table.
`trUNCATE`	Delete and redefine a table.
`UPDATE`	Modify a database entry.

MySQL Functions

Preformatting the results returned by a query makes your data more usable and can cut down on the amount of programming interface required (i.e., how much work you have to do in PHP). To format query results, you make use of MySQL's built-in functions, first introduced in Chapter 4. Table B.14 shows those used on strings. Table B.15 has most, but not all, of the number-based functions.

Table B.14. These MySQL functions can be used to manipulate string values.
Text Functions
FUNCTION	USAGE	PURPOSE
`CONCAT()`	`CONCAT(x, y, ...)`	Creates a new string of the form xy.
`LENGTH()`	`LENGTH(column)`	Returns the length of the value stored in the column.
`LEFT()`	`LEFT(column, x)`	Returns the leftmost x characters from a column's value.
`RIGHT()`	`RIGHT(column, x)`	Returns the rightmost x characters from a column's value.
`TRIM()`	`TRIM(column)`	Trims excess spaces from the beginning and end of the stored value.
`UPPER()`	`UPPER(column)`	Capitalizes the entire stored string.
`LOWER()`	`LOWER(column)`	Turns the stored string into an all-lowercase format.
`SUBSTRING()`	`SUBSTRING (column, start, length)`	Returns length characters from column beginning with start (indexed from 0).

Table B.15. These MySQL functions perform calculations, formatting, and other treatments on numbers.
Numeric Functions
FUNCTION	USAGE	PURPOSE
`ABS()`	`ABS(x)`	Returns the absolute value of x.
`CEILING()`	`CEILING(x)`	Returns the next-highest integer based upon the value of x.
`FLOOR()`	`FLOOR(x)`	Returns the integer value of x.
`FORMAT()`	`FORMAT(x, y)`	Returns x formatted as a number with y decimal places and commas inserted every three spaces.
`MOD()`	`MOD(x, y)`	Returns the remainder of dividing x by y (either or both can be a column).
`RAND()`	`RAND()`	Returns a random number between 0 and 1.0.
`ROUND()`	`ROUND(x, y)`	Returns the number x rounded to y decimal places.
`SIGN()`	`SIGN(x)`	Returns a value indicating whether a number is negative (1), zero (0), or positive (+1).
`SQRT()`	`SQRT(x)`	Calculates the square root of x.

Table B.16 lists the aggregate or grouping functions. Table B.17 is the catchall for miscellaneous functions. Most every function can be applied either to the value retrieved from a column or to a manually entered one:

 SELECT ROUND(column, 2) FROM tablename SELECT ROUND(3.142857, 2)

Table B.16. The grouping functions are frequently, but not always, used with an SQL `GROUP BY` clause.
Grouping Functions
FUNCTION	PURPOSE
`AVG()`	Returns the average value of the column.
`COUNT()`	Counts the number of rows.
`COUNT(DISTINCT)`	Counts the number of distinct column values.
`MIN()`	Returns the smallest value from the column.
`MAX()`	Returns the largest value from the column.
`SUM()`	Returns the sum of all the values in the column.

Table B.17. This table shows functions that handle encryption and other miscellaneous tasks.
Other Functions
FUNCTION	USAGE	PURPOSE
`CONCAT_WS()`	`CONCAT_WS ('-',` `column1, column2)`	Combines the elements with the one common separator.
`DATABASE()`	`DATABASE()`	Returns the name of the database currently being used.
`ENCODE()`	`ENCODE('string', 'salt')`	Returns an encrypted version of string, which can be decrypted.
`ENCRYPT()`	`ENCRYPT('string', 'salt')`	Returns an encrypted version of string using salt (requires the Unix crypt library).
`DECODE()`	`DECODE('string', 'salt')`	Returns a decrypted version of string.
`LAST_INSERT_ID()`	`LAST_INSERT_ID()`	Returns the previous auto-incremented value.
`SHA()`	`SHA('string')`	Returns an encrypted version of string.
`USER()`	`USER()`	Returns the name of the user of the current session.

Date and time

Depending upon where a date or time value originates, either PHP or MySQL may be used to format the returned value. I earlier listed the parameters for formatting dates in PHP with the date() function and mentioned PHP's getdate() function. Table B.18 lists some of MySQL's date- and time-related functions. Table B.19 has the formatting to use with DATE_FORMAT() and TIME_FORMAT().

Table B.18. MySQL's functions for working with date and time values.
Date and Time Functions
FUNCTION	USAGE	PURPOSE
`HOUR()`	`HOUR(column)`	Returns just the hour value of a stored date.
`MINUTE()`	`MINUTE(column)`	Returns just the minute value of a stored date.
`SECOND()`	`SECOND(column)`	Returns just the second value of a stored date.
`DAYNAME()`	`DAYNAME(column)`	Returns the name of the day for a date value.
`DAYOFMONTH()`	`DAYOFMONTH(column)`	Returns just the numerical day value of a stored date.
`MONTHNAME()`	`MONTHNAME(column)`	Returns the name of the month in a date value.
`MONTH()`	`MONTH(column)`	Returns just the numerical month value of a stored date.
`YEAR()`	`YEAR(column)`	Returns just the year value of a stored date.
`ADDDATE()`	`ADDDATE(column, INTERVAL x type)`	Returns the value of x units added to column.
`SUBDATE()`	`SUBDATE(column, INTERVAL x type)`	Returns the value of x units subtracted from column.
`CURDATE()`	`CURDATE()`	Returns the current date.
`CURTIME()`	`CURTIME()`	Returns the current time.
`NOW()`	`NOW()`	Returns the current date and time.
`UNIX_TIMESTAMP()`	`UNIX_TIMESTAMP(date)`	Returns the number of seconds since the epoch until the current moment or until the date specified.

Table B.19. The parameters used by the `DATE_FORMAT()` and `TIME_FORMAT()` functions.
DATE_FORMAT() and TIME_FORMAT() Parameters
TERM	USAGE	EXAMPLE
`%e`	Day of the month	1-31
`%d`	Day of the month, two-digit	01-31
`%D`	Day with suffix	1st-31st
`%W`	Weekday name	Sunday-Saturday
`%a`	Abbreviated weekday name	Sun-Sat
`%c`	Month number	1-12
`%m`	Month number, two-digit	01-12
`%M`	Month name	January-December
`%b`	Month name, abbreviated	Jan-Dec
`%Y`	Year	2002
`%y`	Year	02
`%l` (lowercase L)	Hour	1-12
`%h`	Hour, two digit	01-12
`%k`	Hour, 24-hour clock	0-23
`%H`	Hour, 24-hour clock, two-digit	00-23
`%i`	Minutes	00-59
`%S`	Seconds	00-59
`%r`	Time	8:17:02 PM
`%T`	Time, 24-hour clock	20:17:02
`%p`	AM or PM	AM or PM

Table B.10. The list of available types for column definitions in a MySQL table.

Table B.11. The symbols and terms to use for performing operations and comparisons on a MySQL table.

Table B.12. Use these operators to fine-tune your `FULLTEXT` searches.

Table B.13. SQL has surprisingly few terms but can still do very complex procedures.

MySQL Functions

Table B.14. These MySQL functions can be used to manipulate string values.

Table B.15. These MySQL functions perform calculations, formatting, and other treatments on numbers.

Table B.16. The grouping functions are frequently, but not always, used with an SQL `GROUP BY` clause.

Table B.17. This table shows functions that handle encryption and other miscellaneous tasks.

Date and time

Table B.18. MySQL's functions for working with date and time values.

Table B.19. The parameters used by the `DATE_FORMAT()` and `TIME_FORMAT()` functions.

MySQL

Table B.10. The list of available types for column definitions in a MySQL table.

Table B.11. The symbols and terms to use for performing operations and comparisons on a MySQL table.

Table B.12. Use these operators to fine-tune your FULLTEXT searches.

Table B.13. SQL has surprisingly few terms but can still do very complex procedures.

MySQL Functions

Table B.14. These MySQL functions can be used to manipulate string values.

Table B.15. These MySQL functions perform calculations, formatting, and other treatments on numbers.

Table B.16. The grouping functions are frequently, but not always, used with an SQL GROUP BY clause.

Table B.17. This table shows functions that handle encryption and other miscellaneous tasks.

Date and time

Table B.18. MySQL's functions for working with date and time values.

Table B.19. The parameters used by the DATE_FORMAT() and TIME_FORMAT() functions.

Table B.12. Use these operators to fine-tune your `FULLTEXT` searches.

Table B.16. The grouping functions are frequently, but not always, used with an SQL `GROUP BY` clause.

Table B.19. The parameters used by the `DATE_FORMAT()` and `TIME_FORMAT()` functions.