03/18: MySQL > Create Views
CREATE [OR REPLACE] [ALGORITHM = ...] VIEW view_name [(column_list)] AS select statement [WITH [CASCADED | LOCAL] CHECK OPTION]* ALGORITHM = { UNDEFINED | MERGE | TEMPTABLE }
Example:
CREATE OR REPLACE ALGORITHM = TEMPTABLE VIEW v (coutnryName, cityName) AS SELECT Country.Name, City.Name FROM Country, City WHERE Country.Code = City.CountryCode;You might want to specify TEMPTABLE to influence how MySQL uses locking while it process the view.
WITH CHECK OPTION is allowed only for updatable views, and an error occurs if you use it for a non-updatable view.
Restrictions on Views
Can't associate a trigger with a view.
Can't use subqueries in the FROM clause.
Can't use TEMPORARY tables.
Can't use any user variables.
The following statements are for checking views:
CHECK TABLE view_name; SHOW CREATE VIEW view_name; SHOW FULL TABLES FROM database_name;To use the OR REPLACE, ALTER VIEW or DROP VIEW, the DROP privilege is required.
Privileges for a view apply to the view, not to the underlying tables.
SELECT column_a FROM table_a WHERE id IN (SELECT table_a_id FROM table_b);
SELECT DISTINCT column_a FROM table_a, table_b WHERE table_a.id = table_b.table_a_id;
SELECT column_a FROM table_a WHERE id NOT IN (SELECT table_a_id FROM table_b);
SELECT column_a FROM table_a LEFT JOIN table_b ON table_a.id = table_b.table_a_id WHERE table_b.table_a_id IS NULL;
MySQL does not allow to use a table in a subquery if that table is going to be updated.
SELECT DISTINCT column_a FROM table_a, table_b WHERE table_a.id = table_b.table_a_id;
SELECT column_a FROM table_a WHERE id NOT IN (SELECT table_a_id FROM table_b);
SELECT column_a FROM table_a LEFT JOIN table_b ON table_a.id = table_b.table_a_id WHERE table_b.table_a_id IS NULL;
MySQL does not allow to use a table in a subquery if that table is going to be updated.
01/14: MySQL > Joins and Subqueries
Types of Subqueries
Scalar subqueires return a single value.
Row subqueries return a single row.
Colulmn subqueires return a single column.
Table subqueires return a result set.
Multiple table UPDATE and DELETE statements
UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.id;
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
The ODER BY and LIMIT clauses are not allowed for multiple table UPDATE and DELETE statements.
Using ALL, ANY, and SOME
SELECT Name, Population FROM Country WHERE Population < ALL (SELECT AVG(Population) FROM Country GROUP BY Continent) ORDER BY Name;
ANY and SOME may be equivalent to IN except IN can't be combined with any comparison operators such as '=' or '><'.
NOT IN is an alias of '>< ALL'.
Comparison Using Row Subqueries
The row subquery must return a single row.
SELECT City.Name FROM City WHERE [ROW](City.ID, City.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland');
* ROW is optional.
Row constructors can be used only for equality comparison using the = operator.
Scalar subqueires return a single value.
Row subqueries return a single row.
Colulmn subqueires return a single column.
Table subqueires return a result set.
Multiple table UPDATE and DELETE statements
UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.id;
DELETE t1 FROM t1, t2 WHERE t1.id = t2.id;
DELETE FROM t1 USING t1, t2 WHERE t1.id = t2.id;
The ODER BY and LIMIT clauses are not allowed for multiple table UPDATE and DELETE statements.
Using ALL, ANY, and SOME
SELECT Name, Population FROM Country WHERE Population < ALL (SELECT AVG(Population) FROM Country GROUP BY Continent) ORDER BY Name;
ANY and SOME may be equivalent to IN except IN can't be combined with any comparison operators such as '=' or '><'.
NOT IN is an alias of '>< ALL'.
Comparison Using Row Subqueries
The row subquery must return a single row.
SELECT City.Name FROM City WHERE [ROW](City.ID, City.CountryCode) = (SELECT Capital, Code FROM Country WHERE Name='Finland');
* ROW is optional.
Row constructors can be used only for equality comparison using the = operator.
DELETE returns a true row count, but TRUNCATE TABLE returns 0.
TRUNCATE TABLE and DELETE without WHERE clause may reset the sequence number of AUTO_INCREMENT.
You can use ORDER BY and LIMIT clauses in DELETE statements.
TRUNCATE TABLE requires the DELETE privilege.
TRUNCATE TABLE and DELETE without WHERE clause may reset the sequence number of AUTO_INCREMENT.
You can use ORDER BY and LIMIT clauses in DELETE statements.
TRUNCATE TABLE requires the DELETE privilege.
The UPDATE statement does not have any effect if the statement does not actually change any column values, even there is a TIMESTAMP column with ON UPDATE CURRENT_TIMESTAMP.
You can use ORDER BY and LIMIT in the UPDATE statement.
The --safe-updates option is for preventing dangerous UPDATE statement, such as no WHERE clause.
You can use ORDER BY and LIMIT in the UPDATE statement.
The --safe-updates option is for preventing dangerous UPDATE statement, such as no WHERE clause.
If both the column list and the VALUES list are empty, MySQL creates a new record with each column set to its default:
INSERT INTO table_name () VALUES();
When you have many records to add, multiple-row statements provide better performance (but may reach the max_allowed_packet size.)
In error condition for multiple record INSERT statements, MyISAM inserts the records until the error record, but for InnoDB, the entire statement fails and no records are inserted.
INSERT ... ON DUPLICATE KEY UPDATE clause allows you to do in one statement what otherwise requires two (INSERT and UPDATE).
INSERT INTO log_table (id, date_time, counter)
VALUES (1, NOW(), 1)
ON DUPLICATE KEY UPDATE counter=counter+1;
Note: If an already existing record is updated, the "rows affected" value is 2.
If a table contains multiple unique-valued indexes, REPLACE replaces each of those records (deletes the matching multiple rows, then inserts one row.)
REPLACE requires the INSERT and DELETE privilege.
INSERT INTO table_name () VALUES();
When you have many records to add, multiple-row statements provide better performance (but may reach the max_allowed_packet size.)
In error condition for multiple record INSERT statements, MyISAM inserts the records until the error record, but for InnoDB, the entire statement fails and no records are inserted.
INSERT ... ON DUPLICATE KEY UPDATE clause allows you to do in one statement what otherwise requires two (INSERT and UPDATE).
INSERT INTO log_table (id, date_time, counter)
VALUES (1, NOW(), 1)
ON DUPLICATE KEY UPDATE counter=counter+1;
Note: If an already existing record is updated, the "rows affected" value is 2.
If a table contains multiple unique-valued indexes, REPLACE replaces each of those records (deletes the matching multiple rows, then inserts one row.)
REPLACE requires the INSERT and DELETE privilege.
IN() always returns NULL when used to test NULL, even if NULL is included in the list.
IF() and CASE as used in expressions have somewhat different syntax than the IF and CASE statements such can be used within compound statements.
For approximate values, ROUND() uses the rounding method provided in the C library.
SELECT ROUND(11.5), ROUND(-11.5); <--- returns 12 and -12
SELECT ROUND(1.15E1), ROUND(-1.15E1); <--- returns 11 and -11
FLOOR() returns the largest integer not greater than its argument.
SELECT FLOOR(14.7), FLLOR(-14.7); <--- returns 14 and -15
The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively.
The CONCAT() returns NULL if any of its arguments are NULL, however CONCAT_WS() ignores NULL values.
The STRCMP() function compares two strings and returns -1, 0, or 1.
MySQL encription functions:
PASSWORD() (one-way)
ENCODE() and DECODE()
DES_ENCRYPTY() and DES_DECRYPT()
AES_ENCRYPT() and AES_DECRYPT()
MAKEDATE() takes two arguments year and day of the year.
MAKETIME() produces a time from hour, minute, and secound arguments.
To determine the current date or time, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, or NOW().
MySQL disallows a double dash (--) without a space as comment.
/*! */ is not a comment in MySQL.
CREATE TABLE t (i INT) /*! ENGINE = MEMORY */
SHOW /*!50002 FULL */ TABLES; <-- If MySQL is 5.0.2 or newer.
IF() and CASE as used in expressions have somewhat different syntax than the IF and CASE statements such can be used within compound statements.
For approximate values, ROUND() uses the rounding method provided in the C library.
SELECT ROUND(11.5), ROUND(-11.5); <--- returns 12 and -12
SELECT ROUND(1.15E1), ROUND(-1.15E1); <--- returns 11 and -11
FLOOR() returns the largest integer not greater than its argument.
SELECT FLOOR(14.7), FLLOR(-14.7); <--- returns 14 and -15
The LENGTH() and CHAR_LENGTH() functions determine string lengths in byte and character units, respectively.
The CONCAT() returns NULL if any of its arguments are NULL, however CONCAT_WS() ignores NULL values.
The STRCMP() function compares two strings and returns -1, 0, or 1.
MySQL encription functions:
PASSWORD() (one-way)
ENCODE() and DECODE()
DES_ENCRYPTY() and DES_DECRYPT()
AES_ENCRYPT() and AES_DECRYPT()
MAKEDATE() takes two arguments year and day of the year.
MAKETIME() produces a time from hour, minute, and secound arguments.
To determine the current date or time, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, or NOW().
MySQL disallows a double dash (--) without a space as comment.
/*! */ is not a comment in MySQL.
CREATE TABLE t (i INT) /*! ENGINE = MEMORY */
SHOW /*!50002 FULL */ TABLES; <-- If MySQL is 5.0.2 or newer.
11/07: MySQL > SQL Expressions 2
Temporal Expressions
To perform interval arithmetic, use the INTERVAL keyword and a unit value:
SELECT '2007-12-31' + INTERVAL 10 DAY; (or INTERVAL 10 DAY + '2007-12-31')
SELECT '2007-12-31' - INTERVAL 10 DAY;
* Not DAYS.
The INTERVAL can be specified in units such as SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
NULL Values
Use of NULL values in arithmetic or comparison operations normally produces NULL results. Even comparing NULL to itself results in NULL.
The following statements return NULL:
SELECT NULL + 1, NULL < 1;
SELECT NULL = 1, NULL != NULL;
SELECT NULL LIKE '%', NULL LIKE NULL;
In MySQL, <=> operator works like = and this works with NULL operand.
NULL values sort together (ORDER BY), group together (GROUP BY), and are not distinct.
Expressions that cannot be evaluated (such as 1/0) produce NULL as a result, but the result NULL value cannot be inserted. This is controlled by ERROR_FOR_DIVISION_BY_ZERO mode.
To perform interval arithmetic, use the INTERVAL keyword and a unit value:
SELECT '2007-12-31' + INTERVAL 10 DAY; (or INTERVAL 10 DAY + '2007-12-31')
SELECT '2007-12-31' - INTERVAL 10 DAY;
* Not DAYS.
The INTERVAL can be specified in units such as SECOND, MINUTE, HOUR, DAY, MONTH or YEAR.
NULL Values
Use of NULL values in arithmetic or comparison operations normally produces NULL results. Even comparing NULL to itself results in NULL.
The following statements return NULL:
SELECT NULL + 1, NULL < 1;
SELECT NULL = 1, NULL != NULL;
SELECT NULL LIKE '%', NULL LIKE NULL;
In MySQL, <=> operator works like = and this works with NULL operand.
NULL values sort together (ORDER BY), group together (GROUP BY), and are not distinct.
Expressions that cannot be evaluated (such as 1/0) produce NULL as a result, but the result NULL value cannot be inserted. This is controlled by ERROR_FOR_DIVISION_BY_ZERO mode.
10/29: MySQL > SQL Expressions
Numeric Expressions
When numbers are used in an arithmetic or comparison operation, the result may depend on whether it involves exact or approximate values. For example:
SELECT 1.1+2.2=3.3 as 'exact', 1.1E0+2.2E0=3.3E0 as 'approx.';
The column 'exact' returns true (1), but 'approx' returns false (0).
String Expressions
Double quotes can't be used if the ANSI_QUOTES SQL mode is enabled.
The || operator can be used for string concatenation if the PIPES_AS_CONCAT SQL mode is enabled.
The default character set and collation for literal strings depend on the values of the character_set_connection (the default is latin1) and collation_connection (the default is latin1_swedish_ci) system variables.
A non-binary string can be treated as a binary string by preceding it with the BINARY keyword. For example:
SELECT BINARY 'Hello' = 'hello'; (returns FALSE(0))
If ether string in a comparison is binary, both strings are treated as binary.
String comparison rules also apply to ORDER BY, GROUP BY and DISTINCT operations.
LENGTH() returns the length of a string in bytes, whereas CHAR_LENGTH() returns the length in characters.
The UPPER() and LOWER() functions perform case conversion only if the argument is a non-binary string. To make the two functions perform case conversion for a binary string, convert it to a non-binary string like below:
SELECT UPPER((CONVERT(BINARY 'AbCd' USING latin1));
MySQL allows use of LIKE with non-string values by converting non-string values to strings before performing the pattern match.
When numbers are used in an arithmetic or comparison operation, the result may depend on whether it involves exact or approximate values. For example:
SELECT 1.1+2.2=3.3 as 'exact', 1.1E0+2.2E0=3.3E0 as 'approx.';
The column 'exact' returns true (1), but 'approx' returns false (0).
String Expressions
Double quotes can't be used if the ANSI_QUOTES SQL mode is enabled.
The || operator can be used for string concatenation if the PIPES_AS_CONCAT SQL mode is enabled.
The default character set and collation for literal strings depend on the values of the character_set_connection (the default is latin1) and collation_connection (the default is latin1_swedish_ci) system variables.
A non-binary string can be treated as a binary string by preceding it with the BINARY keyword. For example:
SELECT BINARY 'Hello' = 'hello'; (returns FALSE(0))
If ether string in a comparison is binary, both strings are treated as binary.
String comparison rules also apply to ORDER BY, GROUP BY and DISTINCT operations.
LENGTH() returns the length of a string in bytes, whereas CHAR_LENGTH() returns the length in characters.
The UPPER() and LOWER() functions perform case conversion only if the argument is a non-binary string. To make the two functions perform case conversion for a binary string, convert it to a non-binary string like below:
SELECT UPPER((CONVERT(BINARY 'AbCd' USING latin1));
MySQL allows use of LIKE with non-string values by converting non-string values to strings before performing the pattern match.
10/24: MySQL > Using UNION
The unioned column type may be the smallest type that is guaranteed to be large enough to hold values from all tables.
To use ORDER BY and LIMIT clauses to the unioned table, surround each SELECT with parentheses and then add ORDER BY or LIMIT after the last parenthesis. Collumns named in such an ORDER BY should refer to columns in the first SELECT of the statement.
(SELECT colA, colB FROM tableA)
UNION
(SELECT colC, colD FROM tableB)
ORDER BY colA LIMIT 10;
To use ORDER BY and LIMIT clauses to the unioned table, surround each SELECT with parentheses and then add ORDER BY or LIMIT after the last parenthesis. Collumns named in such an ORDER BY should refer to columns in the first SELECT of the statement.
(SELECT colA, colB FROM tableA)
UNION
(SELECT colC, colD FROM tableB)
ORDER BY colA LIMIT 10;