sql-fundamentals

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQL Fundamentals

SQL基础教程

Quick Start

快速入门

Your First SELECT Query

你的第一个SELECT查询

sql

-- Select all employees
SELECT * FROM employees;

-- Select specific columns with WHERE clause
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

-- Order results by salary
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

sql

-- 查询所有员工
SELECT * FROM employees;

-- 使用WHERE子句查询指定列
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000;

-- 按薪资排序结果
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

Core Concepts

核心概念

Data Types

数据类型

sql

-- Numeric types
BIGINT, INT, SMALLINT, TINYINT  -- Integer types
DECIMAL(10,2), FLOAT, DOUBLE     -- Decimal types

-- String types
VARCHAR(255), CHAR(10), TEXT     -- Text types

-- Date/Time types
DATE, TIME, TIMESTAMP, DATETIME  -- Temporal types

-- Other types
BOOLEAN, BLOB, JSON, UUID

sql

-- 数值类型
BIGINT, INT, SMALLINT, TINYINT  -- 整数类型
DECIMAL(10,2), FLOAT, DOUBLE     -- 小数类型

-- 字符串类型
VARCHAR(255), CHAR(10), TEXT     -- 文本类型

-- 日期/时间类型
DATE, TIME, TIMESTAMP, DATETIME  -- 时间类型

-- 其他类型
BOOLEAN, BLOB, JSON, UUID

DDL Operations (Data Definition Language)

DDL操作（数据定义语言）

sql

-- Create a table
CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  salary DECIMAL(10,2),
  hire_date DATE,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- Modify a table
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN phone;

-- Drop a table
DROP TABLE employees;

sql

-- 创建表
CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  salary DECIMAL(10,2),
  hire_date DATE,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

-- 修改表结构
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
ALTER TABLE employees DROP COLUMN phone;

-- 删除表
DROP TABLE employees;

DML Operations (Data Manipulation Language)

DML操作（数据操作语言）

sql

-- Insert single row
INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 75000);

-- Insert multiple rows
INSERT INTO employees (first_name, last_name, salary) VALUES
('Jane', 'Smith', 80000),
('Bob', 'Johnson', 70000);

-- Update records
UPDATE employees
SET salary = 85000
WHERE first_name = 'John';

-- Delete records
DELETE FROM employees WHERE id = 1;

sql

-- 插入单行数据
INSERT INTO employees (first_name, last_name, salary)
VALUES ('John', 'Doe', 75000);

-- 插入多行数据
INSERT INTO employees (first_name, last_name, salary) VALUES
('Jane', 'Smith', 80000),
('Bob', 'Johnson', 70000);

-- 更新记录
UPDATE employees
SET salary = 85000
WHERE first_name = 'John';

-- 删除记录
DELETE FROM employees WHERE id = 1;

Query Filtering

查询过滤

sql

-- WHERE with various operators
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
SELECT * FROM employees WHERE first_name IN ('John', 'Jane', 'Bob');
SELECT * FROM employees WHERE email IS NOT NULL;
SELECT * FROM employees WHERE first_name LIKE 'J%';  -- Starts with J

sql

-- 使用多种运算符的WHERE子句
SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 80000;
SELECT * FROM employees WHERE first_name IN ('John', 'Jane', 'Bob');
SELECT * FROM employees WHERE email IS NOT NULL;
SELECT * FROM employees WHERE first_name LIKE 'J%';  -- 以J开头

Sorting Results

结果排序

sql

-- Single column sorting
SELECT * FROM employees ORDER BY salary DESC;

-- Multiple column sorting
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

-- LIMIT results
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;  -- Top 10 highest paid

sql

-- 单列排序
SELECT * FROM employees ORDER BY salary DESC;

-- 多列排序
SELECT * FROM employees
ORDER BY department_id ASC, salary DESC;

-- 限制结果数量
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 10;  -- 薪资最高的10位员工

Aggregate Functions

聚合函数

sql

-- Count, Sum, Average
SELECT COUNT(*) as employee_count FROM employees;
SELECT SUM(salary) as total_salary FROM employees;
SELECT AVG(salary) as avg_salary FROM employees;
SELECT MIN(salary) as min_salary, MAX(salary) as max_salary FROM employees;

-- Group By
SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

-- Having clause (filter groups)
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

sql

-- 计数、求和、平均值
SELECT COUNT(*) as employee_count FROM employees;
SELECT SUM(salary) as total_salary FROM employees;
SELECT AVG(salary) as avg_salary FROM employees;
SELECT MIN(salary) as min_salary, MAX(salary) as max_salary FROM employees;

-- GROUP BY分组
SELECT department_id, COUNT(*) as emp_count, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;

-- Having子句（过滤分组结果）
SELECT department_id, COUNT(*) as emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Basic JOINs

基础JOIN关联查询

sql

-- INNER JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Multiple joins
SELECT e.first_name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON e.id = p.employee_id;

sql

-- INNER JOIN内连接
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT JOIN左连接
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- 多表连接
SELECT e.first_name, d.department_name, p.project_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
INNER JOIN projects p ON e.id = p.employee_id;

Common String Functions

常用字符串函数

sql

-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;

-- Length
SELECT first_name, LENGTH(first_name) as name_length FROM employees;

-- Substring
SELECT SUBSTRING(email, 1, POSITION('@' IN email)-1) as username FROM employees;

-- Case functions
SELECT UPPER(first_name), LOWER(last_name) FROM employees;
SELECT TRIM(first_name) FROM employees;

sql

-- 字符串拼接
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;

-- 字符串长度
SELECT first_name, LENGTH(first_name) as name_length FROM employees;

-- 子字符串提取
SELECT SUBSTRING(email, 1, POSITION('@' IN email)-1) as username FROM employees;

-- 大小写与修剪
SELECT UPPER(first_name), LOWER(last_name) FROM employees;
SELECT TRIM(first_name) FROM employees;

Date Functions

日期函数

sql

-- Current date/time
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

-- Extract parts
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date)
FROM employees;

-- Date arithmetic
SELECT first_name, hire_date,
       DATEDIFF(CURRENT_DATE, hire_date) as days_employed
FROM employees;

SELECT first_name, hire_date,
       DATE_ADD(hire_date, INTERVAL 1 YEAR) as one_year_anniversary
FROM employees;

sql

-- 当前日期/时间
SELECT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP;

-- 提取日期部分
SELECT YEAR(hire_date), MONTH(hire_date), DAY(hire_date)
FROM employees;

-- 日期运算
SELECT first_name, hire_date,
       DATEDIFF(CURRENT_DATE, hire_date) as days_employed
FROM employees;

SELECT first_name, hire_date,
       DATE_ADD(hire_date, INTERVAL 1 YEAR) as one_year_anniversary
FROM employees;

Subqueries & Nested Queries

子查询与嵌套查询

sql

-- Subquery in WHERE clause
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM clause
SELECT dept, avg_salary
FROM (
  SELECT department_id as dept, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
) dept_averages
WHERE avg_salary > 70000;

-- Subquery with IN
SELECT first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT id FROM departments
  WHERE location = 'New York'
);

-- EXISTS clause
SELECT d.department_name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.department_id = d.id
  AND e.salary > 100000
);

sql

-- WHERE子句中的子查询
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- FROM子句中的子查询
SELECT dept, avg_salary
FROM (
  SELECT department_id as dept, AVG(salary) as avg_salary
  FROM employees
  GROUP BY department_id
) dept_averages
WHERE avg_salary > 70000;

-- 使用IN的子查询
SELECT first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT id FROM departments
  WHERE location = 'New York'
);

-- EXISTS子句
SELECT d.department_name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e
  WHERE e.department_id = d.id
  AND e.salary > 100000
);

CASE Statements

CASE语句

sql

-- Simple CASE
SELECT first_name, salary,
  CASE
    WHEN salary < 50000 THEN 'Junior'
    WHEN salary < 80000 THEN 'Mid-Level'
    WHEN salary < 120000 THEN 'Senior'
    ELSE 'Executive'
  END as level
FROM employees;

-- Multiple conditions
SELECT first_name, salary, years_employed,
  CASE
    WHEN years_employed >= 10 AND salary > 100000 THEN 'Senior Executive'
    WHEN years_employed >= 5 AND salary > 75000 THEN 'Senior Staff'
    WHEN salary > 60000 THEN 'Mid-Level'
    ELSE 'Junior'
  END as category
FROM employees;

-- CASE with aggregation
SELECT department_id,
  COUNT(CASE WHEN salary > 80000 THEN 1 END) as high_earners,
  COUNT(CASE WHEN salary <= 80000 THEN 1 END) as low_earners
FROM employees
GROUP BY department_id;

sql

-- 简单CASE语句
SELECT first_name, salary,
  CASE
    WHEN salary < 50000 THEN '初级'
    WHEN salary < 80000 THEN '中级'
    WHEN salary < 120000 THEN '高级'
    ELSE '管理层'
  END as level
FROM employees;

-- 多条件CASE语句
SELECT first_name, salary, years_employed,
  CASE
    WHEN years_employed >= 10 AND salary > 100000 THEN '高级管理层'
    WHEN years_employed >= 5 AND salary > 75000 THEN '高级员工'
    WHEN salary > 60000 THEN '中级'
    ELSE '初级'
  END as category
FROM employees;

-- 结合聚合的CASE语句
SELECT department_id,
  COUNT(CASE WHEN salary > 80000 THEN 1 END) as high_earners,
  COUNT(CASE WHEN salary <= 80000 THEN 1 END) as low_earners
FROM employees
GROUP BY department_id;

NULL Handling

NULL值处理

sql

-- COALESCE - return first non-null value
SELECT first_name,
  COALESCE(phone, 'No Phone', 'Unknown') as contact
FROM employees;

-- NULLIF - return NULL if equal
SELECT first_name,
  NULLIF(salary, 0) as salary
FROM employees;

-- IFNULL / ISNULL
SELECT first_name,
  IFNULL(bonus, 0) as bonus_amount
FROM employees;

-- ISNULL in WHERE clause
SELECT first_name FROM employees
WHERE phone IS NULL;

sql

-- COALESCE - 返回第一个非NULL值
SELECT first_name,
  COALESCE(phone, '无电话', '未知') as contact
FROM employees;

-- NULLIF - 若相等则返回NULL
SELECT first_name,
  NULLIF(salary, 0) as salary
FROM employees;

-- IFNULL / ISNULL
SELECT first_name,
  IFNULL(bonus, 0) as bonus_amount
FROM employees;

-- WHERE子句中的ISNULL
SELECT first_name FROM employees
WHERE phone IS NULL;

Distinct & Duplicates

去重与重复数据

sql

-- DISTINCT
SELECT DISTINCT department_id FROM employees;

-- COUNT DISTINCT
SELECT COUNT(DISTINCT department_id) as unique_departments
FROM employees;

-- Find duplicates
SELECT email, COUNT(*) as count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

sql

-- DISTINCT去重
SELECT DISTINCT department_id FROM employees;

-- 计数去重
SELECT COUNT(DISTINCT department_id) as unique_departments
FROM employees;

-- 查找重复数据
SELECT email, COUNT(*) as count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

Union & Set Operations

联合与集合操作

sql

-- UNION (removes duplicates)
SELECT first_name FROM employees WHERE salary > 100000
UNION
SELECT first_name FROM contractors WHERE hourly_rate > 100;

-- UNION ALL (keeps duplicates)
SELECT first_name FROM employees
UNION ALL
SELECT first_name FROM contractors;

-- INTERSECT (common records)
SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM projects;

-- EXCEPT (in first but not second)
SELECT employee_id FROM employees
EXCEPT
SELECT employee_id FROM time_off;

sql

-- UNION（去除重复项）
SELECT first_name FROM employees WHERE salary > 100000
UNION
SELECT first_name FROM contractors WHERE hourly_rate > 100;

-- UNION ALL（保留重复项）
SELECT first_name FROM employees
UNION ALL
SELECT first_name FROM contractors;

-- INTERSECT（交集）
SELECT department_id FROM employees
INTERSECT
SELECT department_id FROM projects;

-- EXCEPT（差集，存在于第一个集合但不存在于第二个）
SELECT employee_id FROM employees
EXCEPT
SELECT employee_id FROM time_off;

Window Functions (Introduction)

窗口函数（入门）

sql

-- ROW_NUMBER
SELECT first_name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK with partitioning
SELECT first_name, department_id, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees;

-- Running total
SELECT first_name, salary,
  SUM(salary) OVER (ORDER BY id) as running_total
FROM employees;

-- LAG and LEAD
SELECT first_name, salary,
  LAG(salary) OVER (ORDER BY id) as prev_salary,
  LEAD(salary) OVER (ORDER BY id) as next_salary
FROM employees;

sql

-- ROW_NUMBER行号
SELECT first_name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- 分区排序RANK
SELECT first_name, department_id, salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as dept_rank
FROM employees;

-- 累计求和
SELECT first_name, salary,
  SUM(salary) OVER (ORDER BY id) as running_total
FROM employees;

-- LAG和LEAD函数
SELECT first_name, salary,
  LAG(salary) OVER (ORDER BY id) as prev_salary,
  LEAD(salary) OVER (ORDER BY id) as next_salary
FROM employees;

Common SQL Patterns

常见SQL场景

Employee Salaries Problem

员工薪资问题

sql

-- Find employees earning more than their manager
SELECT e.first_name, e.salary
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Top earner per department
SELECT department_id, first_name, salary
FROM (
  SELECT department_id, first_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
  FROM employees
) ranked
WHERE rn = 1;

sql

-- 查询薪资高于直属经理的员工
SELECT e.first_name, e.salary
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- 查询各部门薪资最高的员工
SELECT department_id, first_name, salary
FROM (
  SELECT department_id, first_name, salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rn
  FROM employees
) ranked
WHERE rn = 1;

Sales & Orders

销售与订单

sql

-- Monthly sales totals
SELECT DATE_TRUNC('month', order_date) as month,
  SUM(total_amount) as monthly_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Customer lifetime value
SELECT customer_id, COUNT(order_id) as num_orders,
  SUM(total_amount) as lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

-- Products never ordered
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id FROM order_items
);

sql

-- 月度销售总额
SELECT DATE_TRUNC('month', order_date) as month,
  SUM(total_amount) as monthly_total
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 客户终身价值
SELECT customer_id, COUNT(order_id) as num_orders,
  SUM(total_amount) as lifetime_value
FROM orders
GROUP BY customer_id
ORDER BY lifetime_value DESC;

-- 从未被下单的产品
SELECT product_id, product_name
FROM products
WHERE product_id NOT IN (
  SELECT DISTINCT product_id FROM order_items
);

Performance Tips

性能优化技巧

sql

-- Use indexes on frequently filtered columns
CREATE INDEX idx_employee_dept ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

-- Avoid SELECT * - specify columns
SELECT id, first_name, last_name FROM employees;  -- Better
SELECT * FROM employees;  -- Avoid

-- Filter early - put conditions before joins
SELECT *
FROM employees e
WHERE e.department_id = 1
INNER JOIN departments d ON e.department_id = d.id;

-- Use LIMIT when you only need a sample
SELECT * FROM large_table LIMIT 100;

sql

-- 在频繁过滤的列上创建索引
CREATE INDEX idx_employee_dept ON employees(department_id);
CREATE INDEX idx_order_date ON orders(order_date);

-- 避免使用SELECT * - 明确指定列
SELECT id, first_name, last_name FROM employees;  -- 更优
SELECT * FROM employees;  -- 避免使用

-- 提前过滤 - 在关联前添加条件
SELECT *
FROM employees e
WHERE e.department_id = 1
INNER JOIN departments d ON e.department_id = d.id;

-- 仅需样本数据时使用LIMIT
SELECT * FROM large_table LIMIT 100;

Next Steps

下一步

Learn Advanced SQL including CTEs, complex window functions, and query optimization in the

advanced-sql

skill.

学习进阶SQL内容，包括CTE、复杂窗口函数和查询优化，可查看

advanced-sql

教程。