Miscellaneous How-Tos
How to insert a row only if it does not exist?
To insert a row only if it does not exists (or no duplicate primary key or unique key), you could:
- Use 'INSERT INTO ... ON DUPLICATE KEY UPDATE ...'. For example,
INSERT INTO Table1 (column1, column2) VALUES (?, ?) ON DUPLICATE KEY UPDATE column1 = column1
The command check for duplicate primary key as well as unique key. It performs the INSERT if no duplicate key; otherwise, it does the UPDATE. It returns affected_rows of 1 for INSERT, 2 for UPDATE, and 0 for no UPDATE.
This command is available in MySQL. Not sure about other RDBMS. - Use a subquery 'SELECT ... WHERE NOT EXISTS' to check if the row already exists. For example,
INSERT INTO Table1 (column1, column2) SELECT ?, ? FROM dual WHERE NOT EXISTS (SELECT * FROM Table1 WHERE column1 = ? AND column2 = ?)You can interpret as'INSERT INTO Table1 (column1, column2) VALUES (?, ?)'only if the values does not exist.
In MySQL, "dual" is a special table that can be used in queries that do not need any real database table. In the above example, we merely select values, rather than table columns.
This probably works for all the RDBMS.
Count with GROUP BY and Sort with ORDER BY
You can use COUNT with GROUP BY to group record and obtain the count. However, to get the COUNT and say the latest record, you can't use ORDER BY as ORDER BY is evaluated after GROUP BY. Instead, you need to use a subquery. For example,
CREATE TABLE Cafe (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
category ENUM ('Coffee', 'Tea') NOT NULL,
name VARCHAR(50) NOT NULL,
price DECIMAL(5,2) NOT NULL,
PRIMARY KEY(id)
);
INSERT INTO Cafe VALUES
(NULL, 'Coffee', 'Espresso', 3.19),
(NULL, 'Coffee', 'Cappuccino', 3.29),
(NULL, 'Coffee', 'Cafe Latte', 3.39),
(NULL, 'Coffee', 'Cafe Mocha', 3.49),
(NULL, 'Coffee', 'Brewed Coffee', 3.59),
(NULL, 'Tea', 'Green Tea', 2.99),
(NULL, 'Tea', 'Wulong Tea', 2.89);
To count the 'Coffee' and 'Tea', you could:
SELECT category, count(*) AS count FROM Cafe GROUP BY category;
However, the get the count as well as the most expensive 'Coffee' and 'Tea':
// Won't work, as there is no ordering SELECT category, count(*) AS count, name, price FROM Cafe GROUP BY category; // Syntax error, ORDER BY shall be after GROUP BY SELECT category, count(*) AS count, name, price FROM Cafe ORDER BY price DESC GROUP BY category; // Not the expected outcome SELECT category, count(*) AS count, name, price FROM Cafe GROUP BY category ORDER BY price DESC; // Use a subquery to do the ordering SELECT category, count(*) AS count, name, price FROM (SELECT category, name, price FROM Cafe ORDER BY price DESC) AS tmpTable GROUP BY category;
NULL vs Empty String or Zero
NULL denotes no value, where empty string denote a special value. A classical example is for the 'middleName' column, NULL means no value assigned or value unknown, whereas empty string is a known value of empty string. Another example is in the 'markAwarded' column, NULL means not marked yet, but 0 means zero mark awarded.
You can use operators IS NULL or IS NOT NULL or function ISNULL() to test for NULL value (instead of comparing with empty string). For example,
SELECT * FROM table1 WHERE column1 IS NOT NULL;
Rule of Thumb:
- Use NULL (as default) for optional columns, with NULL indicating no value.
- Use NOT NULL for required columns, where empty string could be a possible value.
[TODO] more
COUNT vs SUM(IF(test, 1, 0))
COUNT does not count the NULL value.
You can use SUM(IF(test, 1, 0)) for selected count. For example,
SELECT COUNT(*) AS numAttempts, SUM(IF(status="YES", 1, 0)) AS numYes FROM Submissions WHERE ...... GROUP BY ......
[TODO]
INNER JOIN vs LEFT JOIN
[TODO]
privileges
Revoking all privileges
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'localhost'; FLUSH PRIVILEGES;
GRANT USAGE?
Let's create a new user, show his privilege (via SHOW GRANTS), grant some privileges:
-- Create a new user mysql> CREATE USER 'testuser'@'localhost' identified by 'xxxx'; -- Show the privileges mysql> SHOW GRANTS FOR 'testuser'@'localhost'; GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD *****-- Grant privilege mysql> GRANT SELECT ON test.* to 'testuser'@'localhost'; -- Show the privileges again mysql> SHOW GRANTS FOR 'testuser'@'localhost'; GRANT USAGE ON *.* TO 'testuser'@'localhost' IDENTIFIED BY PASSWORD ***** GRANT SELECT ON `test`.* TO 'testuser'@'localhost'
The "GRANT USAGE ON *.*" means "No privilege". In other word, the user has been created (with an entry in mysql.users table) with no privilege.