TABLE OF CONTENTS (HIDE)

SQL Miscellaneous

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:

  1. 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.
  2. 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 says 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', 'Caffe Latte', 3.39),
  (NULL, 'Coffee', 'Caffe 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.

Link to MySQL References & Resources