-- 不佳的命名約定
SELECT id, nm FROM tbl WHERE type = 'A';
-- 改進(jìn)后的命名約定
SELECT employee_id, employee_name FROM employees WHERE employee_type = 'Full-Time';

-- 不規(guī)范的格式
SELECT id, name FROM products WHERE price > 1000;
-- 改進(jìn)后的格式
SELECT
    id,
    name
FROM
    products
WHERE
    price > 1000;

-- 缺少注釋的查詢(xún)
SELECT * 
FROM Orders 
WHERE order_status = 'Shipped' AND customer_id = 123;
-- 帶有注釋的查詢(xún)
-- 獲取客戶(hù)ID為123的所有已發(fā)貨訂單
SELECT * 
FROM Orders 
WHERE order_status = 'Shipped' AND customer_id = 123;

-- 不一致的大小寫(xiě)
SELECT FirstName, Last_name FROM Employees;
-- 一致的大小寫(xiě)（例如全部大寫(xiě)）
SELECT FIRSTNAME, LAST_NAME FROM EMPLOYEES;

-- 避免使用SELECT *
SELECT first_name, last_name FROM employees;

-- 無(wú)別名
SELECT employees.first_name, departments.department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;
-- 使用別名
SELECT e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 復(fù)雜查詢(xún)
SELECT
    employee_id,
    (SELECT MAX(salary) FROM salaries WHERE employee_id = employees.employee_id) AS max_salary
FROM
    employees;
-- 使用CTE
WITH max_salaries AS (
    SELECT employee_id, MAX(salary) AS max_salary
    FROM salaries
    GROUP BY employee_id
)
SELECT employee_id, max_salary
FROM max_salaries;

-- 數(shù)據(jù)類(lèi)型不一致（字符串 vs 整數(shù)）
SELECT * FROM products WHERE product_id = '123';
-- 數(shù)據(jù)類(lèi)型一致（整數(shù)）
SELECT * FROM products WHERE product_id = 123;

-- 嵌套子查詢(xún)
SELECT employee_id, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name
FROM employees;
-- 使用JOIN
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 初始查詢(xún)
SELECT * FROM orders WHERE order_date >= '2023-01-01';
-- 使用DATE函數(shù)重構(gòu)的查詢(xún)
SELECT * FROM orders WHERE DATE(order_date) >= '2023-01-01';