Using Values in Queries
Before you begin actually using SQL to interact with MySQL, you should have an understanding of how values must be used in your queries. I want to be explicit as to how you should treat the different data types in your SQL commands. Always abide by these rules:
Numeric values shouldn't be quoted.
Numeric values must not contain commas.
String values (for CHAR, VARCHAR, and TEXT column types) must always be quoted.
Date and time values must always be quoted.
Functions, introduced in Chapter 6, "MySQL Functions," cannot be quoted.
The world NULL must not be quoted.
Along with those rules, you have to watch out for certain characters in strings that could break your queries. For example, if you quote some text using single quotes, a single quote (an apostrophe) within that text will ruin the query. To avoid problems in these instances, you can escape the apostrophe in the string by preceding it with a backslash: \'. Table 5.1 lists the characters that must be escaped in your strings.
Table 5.1. These characters all have special meanings when used in queries. The letters are all case-sensitive! The percentage character and the underscore are necessary because these characters, when not escaped, can be used as wildcards in searches.
Single quotation mark
Double quotation mark
With the date formats, not only do you have to quote the value used, but it also has to be in the right format. The standard format is YYYY-MM-DD HH:MM:SS. You can also use a two-digit year in this format, but I'd advise against ever doing so (remember that whole "Y2K" thing?). In fact, you can also get away with using a single digit for the day or month, when applicable, but I think it's best to always use two digits there (so January is 01, not 1).
Alternatively, you could use other punctuation as your delimiters: YYYY/MM/DD HH:MM:SS or YYYY.MM.DD HH.MM.SS. You can even forgo delimiters entirely, but it's important to use four digits for the year and two for everything else in that case: YYYYMMDDHHMMSS.
Finally, I'll point out that SQL keywordsINSERT, SELECT, FROM, WHERE, and so onare case-insensitive. I tend to capitalize these words so that they stand out, but this is not required. If you'd rather type your SQL in all lowercase, that's fine. As for the database, table, and column names, these are case-sensitive on Unix and case-insensitive on Windows and Mac OS X.
If you use an invalid date in a query, it will be treated as a "zero" value for that type: 0000-00-00 00:00:00 (DATETIME) or 0000-00-00 (DATE).
If you use double quotation marks to encapsulate a string, a single quotation mark within that string is not a problem, and vice versa. You only need to escape a quotation mark within a string if that string is encapsulated by the same type of quotation mark.
The Boolean values of true and false can be written using any caseTRUE, True, truebut are not quoted. MySQL will evaluate the Boolean TRue as 1 and the false as 0.
If you escape a character that's not listed in Table 5.1, the escape will be ignored. Thus \m is the same as just m.
SQL is broken down into two broad categories: Data Definition Language (DDL) and Data Manipulation Language (DML). The first group of SQL commands are used to create, alter, and delete databases, tables, and indexes. All of these are discussed in Chapter 4. The second group of SQL commands relate to the data stored in a table. That's what's covered in this chapter.
Knowing about these two labels isn't mandatory, nor will it impact how you use a database. Still, you'll see these terms elsewhere, so you might want to be familiar with them.