30个必备SQL查询技巧大全

基础查询语句

SELECT * FROM employees;
检索employees表中的所有列和行。

SELECT first_name, last_name FROM employees;
仅选择employees表中的first_name和last_name列。

SELECT DISTINCT department FROM employees;
返回employees表中不重复的department值。

条件查询

SELECT * FROM employees WHERE salary > 50000;
筛选salary大于50000的员工记录。

SELECT * FROM employees WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31';
查询在2020年至2022年间入职的员工。

SELECT * FROM products WHERE category IN ('Electronics', 'Clothing');
查找属于Electronics或Clothing类别的产品。

排序与限制

SELECT * FROM employees ORDER BY salary DESC;
按salary降序排列员工记录。

SELECT * FROM products LIMIT 10 OFFSET 20;
跳过前20条记录后返回10条产品数据。

聚合函数

SELECT COUNT(*) FROM orders;
计算orders表中的总记录数。

SELECT AVG(salary) FROM employees WHERE department = 'Engineering';
计算Engineering部门的平均薪资。

SELECT department, MAX(salary) FROM employees GROUP BY department;
按部门分组显示最高薪资。

表连接

SELECT e.name, d.department_name
FROM employees e JOIN departments d ON e.department_id = d.id;
通过department_id关联员工和部门表。

SELECT o.order_id, c.customer_name
FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;
使用左连接保留所有订单记录。

子查询

SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
查找薪资高于平均值的员工姓名。

数据修改

INSERT INTO employees (name, salary) VALUES ('John Doe', 75000);
向employees表插入新记录。

UPDATE employees SET salary = salary * 1.1 WHERE performance_rating = 'A';
给绩效为A的员工加薪10%。

DELETE FROM products WHERE discontinued = 1;
删除已停产的产品记录。

表操作

CREATE TABLE customers ( id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE );
创建包含约束的新表。

ALTER TABLE employees ADD COLUMN bonus DECIMAL(10,2);
向现有表添加新列。

DROP TABLE temp_data;
永久删除表及其数据。

索引优化

CREATE INDEX idx_customer_name ON customers(name);
为customer表的name列创建索引。

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
分析查询执行计划。

事务控制

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
执行转账的原子操作。

窗口函数

SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;
计算员工薪资排名。

日期处理

SELECT CURRENT_DATE;
获取当前日期。

SELECT EXTRACT(YEAR FROM hire_date) FROM employees;
提取员工入职年份。

字符串操作

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
合并姓和名。

SELECT UPPER(product_name) FROM products;
将产品名称转为大写。

正则表达式

SELECT * FROM users WHERE email REGEXP '@gmail.com$';
查找Gmail邮箱用户。

视图创建

CREATE VIEW high_paid_employees AS
SELECT * FROM employees WHERE salary > 100000;
创建高薪员工视图。

存储过程

CREATE PROCEDURE raise_salary(IN emp_id INT, IN amount DECIMAL(10,2))
BEGIN
UPDATE employees SET salary = salary + amount WHERE id = emp_id;
END;
定义加薪存储过程。

触发器

CREATE TRIGGER log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_logs(emp_id, old_salary, new_salary)
VALUES (NEW.id, OLD.salary, NEW.salary);
END;
自动记录薪资变更。

权限管理

GRANT SELECT ON employees TO hr_manager;
授予hr_manager查询员工表的权限。

REVOKE DELETE ON customers FROM sales_team;
撤销销售团队删除客户记录的权限。

备份与恢复

BACKUP DATABASE company TO DISK = '/backups/company.bak';
执行数据库备份。

RESTORE DATABASE company FROM DISK = '/backups/company.bak';
从备份恢复数据库。

BbS.okane204.info/PoSt/1121_204643.HtM
BbS.okane205.info/PoSt/1121_699136.HtM
BbS.okane206.info/PoSt/1121_810343.HtM
BbS.okane207.info/PoSt/1121_171395.HtM
BbS.okane208.info/PoSt/1121_474184.HtM
BbS.okane209.info/PoSt/1121_281696.HtM
BbS.okane210.info/PoSt/1121_650472.HtM
BbS.okane211.info/PoSt/1121_952455.HtM
BbS.okane212.info/PoSt/1121_517960.HtM
BbS.okane213.info/PoSt/1121_980098.HtM
BbS.okane204.info/PoSt/1121_513009.HtM
BbS.okane205.info/PoSt/1121_078003.HtM
BbS.okane206.info/PoSt/1121_583515.HtM
BbS.okane207.info/PoSt/1121_177258.HtM
BbS.okane208.info/PoSt/1121_638944.HtM
BbS.okane209.info/PoSt/1121_253543.HtM
BbS.okane210.info/PoSt/1121_830882.HtM
BbS.okane211.info/PoSt/1121_583996.HtM
BbS.okane212.info/PoSt/1121_191213.HtM
BbS.okane213.info/PoSt/1121_277412.HtM
BbS.okane214.info/PoSt/1121_233557.HtM
BbS.okane215.info/PoSt/1121_719523.HtM
BbS.okane216.info/PoSt/1121_426037.HtM
BbS.okane217.info/PoSt/1121_431678.HtM
BbS.okane218.info/PoSt/1121_320840.HtM
BbS.okane219.info/PoSt/1121_233352.HtM
BbS.okane220.info/PoSt/1121_397141.HtM
BbS.okane221.info/PoSt/1121_194443.HtM
BbS.okane222.info/PoSt/1121_713870.HtM
BbS.okane223.info/PoSt/1121_635780.HtM
BbS.okane214.info/PoSt/1121_752379.HtM
BbS.okane215.info/PoSt/1121_644733.HtM
BbS.okane216.info/PoSt/1121_585796.HtM
BbS.okane217.info/PoSt/1121_241166.HtM
BbS.okane218.info/PoSt/1121_412611.HtM
BbS.okane219.info/PoSt/1121_967238.HtM
BbS.okane220.info/PoSt/1121_197112.HtM
BbS.okane221.info/PoSt/1121_474455.HtM
BbS.okane222.info/PoSt/1121_576691.HtM
BbS.okane223.info/PoSt/1121_740228.HtM
BbS.okane214.info/PoSt/1121_360921.HtM
BbS.okane215.info/PoSt/1121_795836.HtM
BbS.okane216.info/PoSt/1121_081719.HtM
BbS.okane217.info/PoSt/1121_848770.HtM
BbS.okane218.info/PoSt/1121_207948.HtM
BbS.okane219.info/PoSt/1121_409843.HtM
BbS.okane220.info/PoSt/1121_945704.HtM
BbS.okane221.info/PoSt/1121_560146.HtM
BbS.okane222.info/PoSt/1121_067707.HtM
BbS.okane223.info/PoSt/1121_726125.HtM
BbS.okane214.info/PoSt/1121_635236.HtM
BbS.okane215.info/PoSt/1121_500866.HtM
BbS.okane216.info/PoSt/1121_016834.HtM
BbS.okane217.info/PoSt/1121_489958.HtM
BbS.okane218.info/PoSt/1121_158259.HtM
BbS.okane219.info/PoSt/1121_768923.HtM
BbS.okane220.info/PoSt/1121_980239.HtM
BbS.okane221.info/PoSt/1121_875066.HtM
BbS.okane222.info/PoSt/1121_305454.HtM
BbS.okane223.info/PoSt/1121_979512.HtM
BbS.okane214.info/PoSt/1121_919264.HtM
BbS.okane215.info/PoSt/1121_841171.HtM
BbS.okane216.info/PoSt/1121_565557.HtM
BbS.okane217.info/PoSt/1121_200183.HtM
BbS.okane218.info/PoSt/1121_404328.HtM
BbS.okane219.info/PoSt/1121_344411.HtM
BbS.okane220.info/PoSt/1121_068387.HtM
BbS.okane221.info/PoSt/1121_073380.HtM
BbS.okane222.info/PoSt/1121_616377.HtM
BbS.okane223.info/PoSt/1121_707575.HtM
BbS.okane214.info/PoSt/1121_205029.HtM
BbS.okane215.info/PoSt/1121_493465.HtM
BbS.okane216.info/PoSt/1121_660130.HtM
BbS.okane217.info/PoSt/1121_156010.HtM
BbS.okane218.info/PoSt/1121_429373.HtM
BbS.okane219.info/PoSt/1121_137744.HtM
BbS.okane220.info/PoSt/1121_713193.HtM
BbS.okane221.info/PoSt/1121_305566.HtM
BbS.okane222.info/PoSt/1121_229950.HtM
BbS.okane223.info/PoSt/1121_986658.HtM