MySQL
Any DATE must be in quotes (') in a MySQL insert statement, including if you use the CURDATE() function and assign it to a variable. Otherwise it inputs the default (0000-00-00 if you haven't messed with it). That's something I learned on my own.
All this information is stolen from a great book I kept out of the library long enough for the library to threaten legal action:
Paul DuBois, MySQL (Indianapolis, Indiana: New Riders, 2000).
Especially as my notes are in no order and leave out necessary information, you should get the book instead of looking below.
Create Table statement examples
(These are not intended to illustrate correct database design.)
CREATE TABLE president
(
last_name VARCHAR(15) NOT NULL,
first_name VARCHAR(15) NOT NULL,
suffix VARCHAR(5) NULL,
city VARCHAR(20) NOT NULL,
state CHAR(2) NOT NULL,
birth DATE NOT NULL,
death DATE NULL
)
CREATE TABLE student
(
name VARCHAR(20) NOT NULL,
sex ENUM('F','M') NOT NULL,
student_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)
CREATE TABLE score
(
student_id INT UNSIGNED NOT NULL,
event_id INT UNSIGNED NOT NULL,
score INT NOT NULL,
PRIMARY KEY (event_id, student_id)
)
Primary Key
PRIMARY KEY means that the column is indexed for fast lookups and that each value in the column must be unique. It can be a single column as in the student table above or multiple columns, declared as PRIMARY KEY (column1, column2) as in the score table immediately above.
Select statement examples
Select statements follow the formula SELECT columns FROM table WHERE conditions (if any). To select all the columns in a table use an asterisk. This statement would select every row of every column in the table “table”: SELECT * FROM table.
SELECT last_name, first_name FROM president WHERE last_name="roosevelt"
SELECT last_name, first_name, birth FROM president WHERE birth < "1750-1-1"
SELECT last_name, first_name, birth, state FROM president WHERE birth < "1750-1-1" AND (state="VA" OR state="MA")
Expressions in WHERE clauses can use the following operators:
| Operator | Meaning |
|---|---|
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
| Operator | Meaning |
|---|---|
| < | Less than |
| <= | Less than or equal to |
| = | Equal to |
| != (or <>) | Not equal to |
| >= | Greater than or equal to |
| > | Greater than |
| Operator | Meaning |
|---|---|
| AND | Logical AND |
| OR | Logical OR |
| NOT | Logical negation |
To perform searches for NULL values, you must use special syntax, because NULL cannot be compared against anything even itsel: the result of comparing too unknown values cannot be known. MySQL always returns null for any of these comparisons. The special language needed to search for NULL values is IS NULL or IS NOT NULL.
Pattern Matching
Pattern matching uses the special operators LIKE and NOT LIKE with a string containing wild card characters. The ‘_’ character matches any single character and ‘%’ matches any sequence of characters, including an empty sequence (no characters). Pattern matches using LIKE or NOT LIKE are not case sensitive.
This statement selects presidents with last names starting with W.
SELECT last_name, first_name FROM president WHERE last_name LIKE "W%"
Remember that you cannot use the equals sign or any other arithmatic operator; you must use the keyword LIKE for matches that contain your pattern or NOT LIKE to get matches that do not contain your pattern.
This pattern matches last names that contain exactly four characters:
SELECT last_name, first_name FROM president WHERE last_name LIKE "____"
Page 55.
Generating Summaries
MySQL can summarize lots of raw data in potentially useful ways.
One simple form of summarizing is to determine which unique values are present in a set of values. Use the DISTINCT keyword to remove dublicate rows from a result. This query returns all the different states in which presidents have been born.
SELECT DISTINCT state FROM president ORDER BY state
COUNT(): COUNT(*) tells you the number of rows selected by your query; without a WHERE clause it tells you the number of rows in the table. With a WHERE clause COUNT(*) counts the number of rows the clause selects. COUNT(col_name) counts only non-NULL values.
SELECT COUNT(*),COUNT(suffix),COUNT(death) FROM president
Result:
| COUNT(*) | COUNT(suffix) | COUNT(death) |
| 41 | 1 | 36 |
As of MySQL 3.23.2, COUNT() and DISTINCT can be combined to count the number of distinct walues in a result. This query returns the number of different states in which presidents have been born:
SELECT COUNT(DISTINCT state) FROM president
GROUP BY allows you to use COUNT() to count how many times each distinct value occurs in a column. For example,
SELECT sex, COUNT(*) FROM student GROUP BY sex
and
SELECT state, COUNT(*) FROM president GROUP BY state
list the two sexes in one column and the number of students belonging to each in the second column for the first query and each state followed by the number of presidents born in that state for the second.
When counting values this way, the GROUP BY clause is necessary to tell MySQL how to cluster values before counting them; you will get an error if you omit it.
ORDER BY could be added to this query to get the states listed from most president-producing to least. AS must be used to give COUNT(*), a column determined by calculation, an alias so that it can be referred to in the ORDER BY clause.
SELECT state, COUNT(*) AS count FROM president GROUP BY state ORDER BY count DESC
Other summary functions include MIN(), MAX(), SUM(), and AVG().
The following query returns a seven-column table with a row for each event that lists the event ID and the minimum score, maximum score, score range, additive total of scores, average score, and the number of scores that went into these calculations for the event with that ID.
SELECT event_id, MIN(score), MAX(score), MAX(score)-MIN(score)+1 AS range, SUM(score), AVG(score), COUNT(score) FROM score GROUP BY event_id
Retrieving Information from Multiple Tables
When you select information from multiple tables, you are performing an operation called a join. You are producing a result by joining the information from one table to the information in another. This is done by matching up common values in the tables.
In the following example, a three-way join results in a four-column table listing, for the given date, the name of each student who took a test or quiz, the date, the score, and whether it was a test or a quiz.
SELECT student.name, event.date, score.score, event.type FROM event, score, student WHERE event.date = "1999-09-23" AND event.event_id = score.event_id AND score.student_id = student.student_id
LEFT JOIN tells MySQL to produce a row of output for each row selected from the table named first (i.e. left of the keywords LEFT JOIN) in the join. (I think the use of ON should be noted.)
By naming the student table first in this query, output is given for every student, even those not represented in the absence field:
SELECT student.student_id, student.name, COUNT(absence.date) as absences FROM student LEFT JOIN absence ON student.student_id = absence.student_id GROUP BY student.student_id
MySQL Data Types
String Escape Sequences:
| Sequence | Meaning |
|---|---|
| \0 | NUL (ASCII 0) |
| \' | Single Quote |
| \" | Double Quote |
| \b | Backspace |
| \n | Newline |
| \r | Carriage Return |
| \t | Tab |
| \\ | Backslash |
Numeric Column Types [optional specifications in brackets] with Ranges and Storage Requirements:
| Type Specification | Range | Storage Required |
|---|---|---|
| TINYINT[(M)] |
Signed: -128 to 127 (-27 to 27-1) Unsigned: 0 to 255 (0 to 28-1) | 1 byte |
| SMALLINT[(M)] |
Signed: -32,768 to 32,767 (-215 to 215-1) Unsigned: 0 to 65,535 (0 to 216-1) | 2 bytes |
| MEDIUMINT[(M)] |
Signed: -8,388,608 to 8,388,607 (-223 to 223-1) Unsigned: 0 to 16,777,215 (0 to 224-1) | 3 bytes |
| INT[(M)] |
Signed: -2,147,683,648 to 2,147,683,647 (-231 to 231-1) Unsigned: 0 to 4,294,967,295 (0 to 232-1) | 4 bytes |
| BIGINT[(M)] |
Signed: -263 to 263-1 Unsigned: 0 to 264-1 | 8 bytes |
| FLOAT[(M,D)], FLOAT(4) |
Minimum non-zero values: ±1.175494351E-38 Maximum non-zero values: ±4.402823466E+38 | 4 bytes |
| DOUBLE[(M,D)], FLOAT(8) |
Minimum non-zero values: ±2.2250738585072014E-308 Maximum non-zero values: ±1.7976931348623157E+308 | 8 bytes |
| DECIMAL[(M,D)] | Range depends on M and D | M+2 bytes (MySQL 3.23 and later) |
String Column Types with Size and Storage Required:
| Type Specification | Maximum Size | Storage Required |
|---|---|---|
| CHAR(M) | M bytes | M bytes |
| VARCHAR(M) | M bytes | Length+1 bytes |
| TINYBLOB, TINYTEXT | 28-1 bytes | Length+1 bytes |
| BLOB, TEXT | 216-1 bytes | Length+2 bytes |
| MEDIUMBLOB, MEDIUMTEXT | 224-1 bytes | Length+3 bytes |
| LONGBLOB, LONGTEXT | 232-1 bytes | Length+4 bytes |
| ENUM("value1","value2",...) | 65535 members | 1 or 2 bytes |
| SET("value1","value2",...) | 64 members | 1, 2, 3, 4, or 8 bytes |
You cannot mix CHAR and VARCHAR in the same table (with a few limited exceptions). MySQL will change columns from one type to another on these principles: Tables with fixed-length rows are processed more easily than tables with variable-length rows; table rows are fixed-length only if all columns in the table are fixed-length types; because the performance advantages of fixed-length rows are lost when the row becomes variable-length, any fixed-length columns will be converted to variable-length equivalents when that will save space.
Pages 98, 99.
Expression Evaluation and Type Conversion
MySQL allows you to write expressions that include contstants, function calls, and references to table columns.
SELECT CONCAT(last_name, ", ", first_name), (TO_DAYS(death) - TO_DAYS(birth) / 365) FROM president WHERE birth > "1900-1-1" AND DEATH IS NOT NULL
Functions can contain no arguments or can contain arguments. Spaces are allowed around arguments; there cannot be a space after the function name before the parenthesis. These are all legal functions:
NOW()
STRCMP("abc","def")
STRCMP( "abc", "def")
MySQL Operators
Arithmetic Operators
| Operator | Syntax | Meaning |
|---|---|---|
| + | a + b | Addition; sum of operands |
| - | a - b | Subtraction; difference of operands |
| - | -a | Unary minus; negation of operand |
| * | a * b | Multiplication; product of operands |
| / | a / b | Division; quotient of operands |
| % | a % b | Modulo; remainder after division of operands |
Logical Operators
| Operator | Syntax | Meaning |
|---|---|---|
| AND, && | a AND b, a && b | Logical intersection; true if both operands are true |
| OR, || | a OR b, a || b | Logical union; true if either operand is true |
| NOT, ! | NOT a, !a | Logical negation; true if operand is false |
Bit Operators
| Operator | Syntax | Meaning |
|---|---|---|
| & | a & b | Bitwise AND (intersection); each bit of result is set if corresponding bits of both operators are set |
| | | a | b | Bitwise OR (union); each bit of result is set if corresponding bit of either operand is set |
| << | a << b | Left shift of a by b bit positions |
| >> | a >> b | Right shift of a by b bit positions |
Comparison Operators
| Operator | Syntax | Meaning |
|---|---|---|
| = | a = b | True if operands are equal |
| !=, <> | a != b, a <> b | True if operands are not equal |
| < | a < b | True if a is less than b |
| <= | a <= b | True if a is less than or equal to b |
| > | a > b | True if a is greater than b |
| >= | a >= b | True if a is greater than or equal to b |
| IN | a IN (b1, b2, ...) | True if a is equal to any of b1, b2, ... |
| BETWEEN | a BETWEEN b AND C | True if a is between the values of b and c, inclusive |
| LIKE | a LIKE b | SQL pattern match; true if a matches b |
| NOT LIKE | a NOT LIKE b | SQL pattern match; true if a does not matches b |
| REGEXP | a REGEXP b | Extended regular expression match; true if a matches b |
| NOT REGEXP | a NOT REGEXP b | Extended regular expression match; true if a does not matches b |
| <=> | a <=> b | True if operands are equal even if both NULL (unique to MySQL) |
| IS NULL | a IS NULL | True if operand is NULL |
| IS NOT NULL | a IS NOT NULL | True if operand is not NULL |
Creating Indexes
In addition to using the PRIMARY KEY keyword at the end of a column declaration to create a single-column primary key, the following statements can be made in a CREATE TABLE statement, as shown, or in an ALTER TABLE statement.
CREATE TABLE table_name
(
. . .
INDEX index_name (column_list),
UNIQUE index_name (column_list),
PRIMARY KEY (column_list),
. . .
All PRIMARY KEY columns must be not NULL; other indexes may include NULL values so long as you have MyISAM tables and not ISAM tables.
If desired, only the leftmost characters of column values need be indexed if they will be pretty unique. You cannot make a column be shorter than the length of its prefix in the index.
CREATE TABLE my_tbl
(
name CHAR(30),
address CHAR(60),
INDEX (name(10),address(20))
)
Left Join
An equi-join (normal join, which happens any time you use two tables) shows only rows where a match can be found in both tables. A LEFT JOIN forces the result set to contain a row for every row in the left side table (the table listed first, directly before the LEFT JOIN keyword). Each row is selected from the left table. If there is a matching row in the right table, that row is selected. If there is no match, a row filled with NULL values is created and selected. Matching is done according to the columns named in an ON or USING() clause.
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2
The USING() clause can be used only to join tables on columns that have the same name. The following query joins my_tbl1.b to my_tabl2.b.
SELECT my_tbl1.*, my_tbl2.* FROM my_tbl1 LEFT JOIN my_tbl2 USING(b)
If you know that the column you are selecting on in the right table does not contain NULL values (ie, if it is declared NOT NULL), you can find only those rows in the left table that do not appear in the right side table:
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 / t2.i2 WHERE t2.i2 IS NULL
Writing Comments
One-line comments can be made using the # character, one or multi-line comments can be placed between /* and */.
/* comments are not read by the database servers */
You can hide MySQL specific keywords and things in comments beginning with /*! and ending normally, so that MySQL will read it and other database servers won’t.
Rewriting Subselects as Joins
MySQL does not support subselects. Subselects that select matching values follow this pattern:
SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2a WHERE column2b = value)
Can be rewritten:
SELECT table1.* FROM table1, table2 WHERE table1.column1 / table2.column2a AND table2.column2b = value
Subselects that select non-matching values may be written as follows:
SELECT * FROM student WHERE student_id NOT IN (SELECT student_id FROM absence)
And rewritten like this:
SELECT student.* FROM student LEFT JOIN absence ON student.student_id = absence.student_id WHERE absence.student_id IS NULL
All this information is stolen from a great book I kept out of the library long enough for the library to threaten legal action:
Paul DuBois, MySQL (Indianapolis, Indiana: New Riders, 2000).