单表查询
- 查询表中所有数据
SELECT * FROM employees;
- 查询特定列
SELECT first_name, last_name FROM employees;
- 查询并重命名列
SELECT first_name AS '名', last_name AS '姓' FROM employees;
- 使用 DISTINCT 去重
SELECT DISTINCT department_id FROM employees;
- 使用 LIMIT 限制返回行数
SELECT * FROM employees LIMIT 10;
数据过滤
- 使用 WHERE 简单条件过滤
SELECT * FROM employees WHERE salary > 5000;
- 多条件组合 AND
SELECT * FROM employees WHERE salary > 5000 AND department_id = 10;
- 多条件组合 OR
SELECT * FROM employees WHERE salary > 5000 OR department_id = 10;
- 使用 IN 条件
SELECT * FROM employees WHERE department_id IN (10, 20, 30);
- 使用 BETWEEN 范围条件
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 6000;
- 使用 LIKE 模糊查询
SELECT * FROM employees WHERE last_name LIKE 'S%';
- 使用 IS NULL 判断空值
SELECT * FROM employees WHERE manager_id IS NULL;
- 使用 NOT 否定条件
SELECT * FROM employees WHERE NOT salary > 5000;
聚合函数
- 使用 COUNT 计数
SELECT COUNT(*) FROM employees;
- 分组计数
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;
- 使用 SUM 求和
SELECT SUM(salary) FROM employees;
- 使用 AVG 求平均值
SELECT AVG(salary) FROM employees;
- 使用 MAX 求最大值
SELECT MAX(salary) FROM employees;
- 使用 MIN 求最小值
SELECT MIN(salary) FROM employees;
- 使用 HAVING 过滤分组
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
排序
- 单列排序
SELECT * FROM employees ORDER BY salary;
- 降序排序
SELECT * FROM employees ORDER BY salary DESC;
- 多列排序
SELECT * FROM employees ORDER BY department_id, salary DESC;
多表查询
- 简单多表查询
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
表连接操作
- 内连接
SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
- 左外连接
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
- 右外连接
SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;
- 全外连接(MySQL 不支持，使用 UNION 模拟)
SELECT e.first_name, d.department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id
UNION
SELECT e.first_name, d.department_name
FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;
- 自连接
SELECT e1.first_name, e2.first_name AS manager_name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
- 交叉连接
SELECT * FROM employees CROSS JOIN departments;
子查询
- WHERE 子句中的子查询
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
- IN 子查询
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);
- EXISTS 子查询
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);
- FROM 子句中的子查询
SELECT dept_avg.dept_id, dept_avg.avg_salary
FROM (SELECT department_id AS dept_id, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id) dept_avg
WHERE dept_avg.avg_salary > 5000;
- SELECT 子句中的子查询
SELECT e.first_name,
(SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS dept_name
FROM employees e;
联合查询
- UNION 合并查询结果(去重)
SELECT employee_id FROM current_employees
UNION
SELECT employee_id FROM former_employees;
- UNION ALL 合并查询结果(不去重)
SELECT employee_id FROM current_employees
UNION ALL
SELECT employee_id FROM former_employees;
字符串处理
- 字符串连接
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
- 字符串长度
SELECT first_name, LENGTH(first_name) AS name_length FROM employees;
- 子字符串
SELECT first_name, SUBSTRING(first_name, 1, 3) AS short_name FROM employees;
- 字符串替换
SELECT REPLACE(phone_number, '.', '-') AS formatted_phone FROM employees;
- 大小写转换
SELECT UPPER(first_name) AS upper_name, LOWER(last_name) AS lower_name FROM employees;
- 去除空格
SELECT TRIM(' Hello ') AS trimmed;
时间日期函数
- 获取当前日期时间
SELECT NOW(), CURDATE(), CURTIME();
- 日期格式化
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') AS formatted_date FROM employees;
- 日期加减
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) AS one_year_later FROM employees;
- 日期差
SELECT DATEDIFF(CURDATE(), hire_date) AS days_employed FROM employees;
- 提取日期部分
SELECT YEAR(hire_date) AS hire_year, MONTH(hire_date) AS hire_month FROM employees;
- 时间戳转换
SELECT FROM_UNIXTIME(1617184800) AS datetime;
数据插入
- 插入单行数据
INSERT INTO employees (first_name, last_name, email, hire_date, job_id)
VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-15', 'IT_PROG');
- 插入多行数据
INSERT INTO employees (first_name, last_name, email, hire_date, job_id)
VALUES
('John', 'Doe', 'john.doe@example.com', '2023-01-15', 'IT_PROG'),
('Jane', 'Smith', 'jane.smith@example.com', '2023-01-16', 'FI_ACCOUNT');
- 插入查询结果
INSERT INTO employee_archive
SELECT * FROM employees WHERE hire_date < '2000-01-01';
数据更新
- 更新单列
UPDATE employees SET salary = 6000 WHERE employee_id = 100;
- 更新多列
UPDATE employees SET salary = salary * 1.1, job_id = 'IT_MGR' WHERE employee_id = 100;
- 使用子查询更新
UPDATE employees SET salary = (SELECT AVG(salary) FROM employees)
WHERE department_id = 10;
数据删除
- 删除特定行
DELETE FROM employees WHERE employee_id = 100;
- 删除所有行
DELETE FROM employees;
- 使用子查询删除
DELETE FROM employees WHERE department_id IN
(SELECT department_id FROM departments WHERE location_id = 1700);
表创建
- 创建简单表
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
- 创建带约束的表
CREATE TABLE employees (
employee_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE DEFAULT (CURRENT_DATE),
salary DECIMAL(10,2) CHECK (salary > 0),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
- 创建临时表
CREATE TEMPORARY TABLE temp_employees AS SELECT * FROM employees WHERE salary > 5000;
- 创建表从查询结果
CREATE TABLE high_paid_employees AS SELECT * FROM employees WHERE salary > 8000;
表删除
- 删除表
DROP TABLE employees;
- 删除表(如果存在)
DROP TABLE IF EXISTS employees;
表更新
- 添加列
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(50);
- 删除列
ALTER TABLE employees DROP COLUMN middle_name;
- 修改列类型
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
- 重命名列
ALTER TABLE employees CHANGE COLUMN salary monthly_salary DECIMAL(10,2);
- 重命名表
ALTER TABLE employees RENAME TO staff;
- 添加约束
ALTER TABLE employees ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(department_id);
库创建
- 创建数据库
CREATE DATABASE company;
- 创建数据库(如果不存在)
CREATE DATABASE IF NOT EXISTS company;
- 创建数据库指定字符集
CREATE DATABASE company CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
库删除
- 删除数据库
DROP DATABASE company;
- 删除数据库(如果存在)
DROP DATABASE IF EXISTS company;
约束
- 添加主键约束
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email);
- 添加检查约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
- 添加外键约束
ALTER TABLE employees ADD CONSTRAINT fk_dept
FOREIGN KEY (department_id) REFERENCES departments(department_id);
- 删除约束
ALTER TABLE employees DROP CONSTRAINT uk_email;
索引
- 创建索引
CREATE INDEX idx_last_name ON employees(last_name);
- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);
- 创建复合索引
CREATE INDEX idx_name_department ON employees(last_name, department_id);
- 删除索引
DROP INDEX idx_last_name ON employees;
- 查看索引
SHOW INDEX FROM employees;
视图操作
- 创建视图
CREATE VIEW emp_dept_view AS
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;
- 查询视图
SELECT * FROM emp_dept_view WHERE department_name = 'IT';
- 修改视图
ALTER VIEW emp_dept_view AS
SELECT e.employee_id, CONCAT(e.first_name, ' ', e.last_name) AS full_name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.department_id;
- 删除视图
DROP VIEW emp_dept_view;
事务控制
- 开始事务
START TRANSACTION;
- 提交事务
COMMIT;
- 回滚事务
ROLLBACK;
- 设置保存点
SAVEPOINT savepoint_name;
- 回滚到保存点
ROLLBACK TO savepoint_name;
- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
权限管理
- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
- 授予权限
GRANT SELECT, INSERT ON company.* TO 'newuser'@'localhost';
- 撤销权限
REVOKE INSERT ON company.* FROM 'newuser'@'localhost';
- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';
- 删除用户
DROP USER 'newuser'@'localhost';
本文使用 markdown.com.cn 排版
