筆記 - SQL 指令練習

Posted on In

本篇紀錄在 SQL ZOO 練習 SQL 指令。

SELECT basics

IN

  • The IN operator allows you to specify multiple values in a WHERE clause.

  • The IN operator is a shorthand for multiple OR conditions.

  • Use NOT IN to exclude.

1
2
SELECT `name`, `population` FROM `world`
WHERE `name` IN ('Sweden', 'Norway', 'Denmark');

BETWEEN

  • The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

  • The BETWEEN operator is inclusive: begin and end values are included.

1
2
SELECT `name`, `area` FROM `world`
WHERE `area` BETWEEN 200000 AND 250000;

如果不包含的話，就用運算子寫吧

1
2
SELECT `name`, `area` FROM `world`
WHERE `area` > 200000 AND `area` < 250000;

LIKE

  • The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

  • There are two wildcards often used in conjunction with the LIKE operator:

    • The percent sign (%) represents zero, one, or multiple characters
    • The underscore sign (_) represents one, single character
1
2
SELECT `name` FROM `world`
WHERE `name` LIKE '%a' OR `name` LIKE '%l';

XOR (Exclusive OR)

  • One or the other (but not both)

Show the countries that are big by area or big by population but not both.

1
2
SELECT `name`, `population`, `area` FROM `world`
WHERE `area` > 3000000 XOR `population` > 250000000

ROUND (Rounding)

  • ROUND(f,p) returns f rounded to p decimal places
    1
    2
    3
    ROUND(7253.86, 0)    ->  7254
    ROUND(7253.86, 1)    ->  7253.9
    ROUND(7253.86,-3)    ->  7000

LENGTH

  • LENGTH(s) returns the number of characters in string s.
    1
    LENGTH('Hello') -> 5

LEFT

  • LEFT(s,n) allows you to extract n characters from the start of the string s.
    1
    LENGTH('Hello') -> 5

Non-ASCII and Escaping

Non-ASCII characters Keyboarding

Escaping single quotes: You can’t put a single quote in a quote string directly. You can use two single quotes within a quoted string.

ORDER BY

  • The ORDER BY keyword is used to sort the result-set in ascending or descending order.

  • The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

1
2
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;

技巧：讓某些欄位排在最前或最後

Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.

1
2
3
SELECT `winner`, `subject` FROM `nobel`
WHERE `yr` = 1984
ORDER BY (`subject` IN ('chemistry', 'physics')),  `subject`, `winner`;

KEY: The expression subject IN (‘chemistry’,’physics’) can be used as a value - it will be 0 or 1. 不包含的為 0 會排在前面 (ASC)
SQL ZOO 影片

SELECT DISTINCT

  • The SELECT DISTINCT statement is used to return only distinct (different) values.

  • Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

Shows the amount of years where no Medicine awards were given.

1
2
SELECT COUNT(DISTINCT `yr`) FROM `nobel`
WHERE `yr` NOT IN (SELECT DISTINCT `yr` FROM `nobel` WHERE `subject` = 'Medicine')

技巧：如果有欄位有空值

記得在條件中再 AND population > 0

技巧：Nested SELECT

  • We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.

Find the largest country (by area) in each continent, show the continent, the name and the area:

1
2
3
4
5
SELECT `continent`, `name`, `area` FROM `world` x
WHERE `area` >= ALL
    (SELECT `area` FROM `world` y
        WHERE y.`continent` = x.`continent`
          AND `area` > 0)

ANY and ALL

ANY

  • The ANY operator:
    • returns a boolean value as a result
    • returns TRUE if ANY of the sub-query values meet the condition
  • ANY means that the condition will be true if the operation is true for any of the values in the range.

只要一個符合就回傳 true

1
2
3
4
5
SELECT column_name(s) FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);

ALL

  • The ALL operator:
    • returns a boolean value as a result
    • returns TRUE if ALL of the sub-query values meet the condition
    • is used with SELECT, WHERE and HAVING statements
  • ALL means that the condition will be true only if the operation is true for all values in the range.

全部符合才會回傳 true

1
2
3
4
5
SELECT column_name(s) FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

Aggregate functions

An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.

  • SUM, COUNT, AVG,
  • MAX, DISTINCT, ORDER BY

GROUP BY

  • The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.

  • The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

1
2
3
4
SELECT column_name(s) FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

HAVING

  • The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions

WHERE 只看得懂資料表原本的欄位。所以經過 AS 重新命名的欄位、或是經過函式計算出來的欄位，要使用 HAVING 來放條件。（例如接在 GROUP BY 後面）

1
2
3
4
5
SELECT column_name(s) FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

JOIN & UNION

Related: 1965. Employees With Missing Information

這題要取聯集扣掉交集

才發現 MySQL 沒有 OUTER JOIN…

所以要用 LEFT JOIN, RIGHT JOIN 加上 UNION 來實作

1
2
3
4
5
6
7
8
SELECT T.employee_id
FROM  
  (SELECT * FROM Employees LEFT JOIN Salaries USING(employee_id)
   UNION 
   SELECT * FROM Employees RIGHT JOIN Salaries USING(employee_id))
AS T
WHERE T.salary IS NULL OR T.name IS NULL
ORDER BY employee_id;

補充：兩個 table 的 column name 相同時，可以用 USING 取代 ON