网站导航
> 技术文档 > SQL命令汇总

SQL命令汇总

网友: 技术文档 2025-07-30 20:11:35

一、数据查询命令

1. SELECT - 基本查询
SELECT column1, column2 FROM table_name;SELECT * FROM table_name; -- 查询所有列
  • 示例：SELECT name, age FROM users;
2. WHERE - 条件过滤
SELECT * FROM table_name WHERE condition;
  • 示例：SELECT * FROM users WHERE age > 18;
3. AND / OR - 组合条件
-- AND：多个条件需同时满足SELECT * FROM users WHERE age > 18 AND gender = \'F\';-- OR：满足任一条件即可SELECT * FROM users WHERE age < 18 OR country = \'China\';
4. IN / NOT IN - 范围匹配
-- IN：匹配列表中的值SELECT * FROM products WHERE category IN (\'Electronics\', \'Books\');-- NOT IN：排除列表中的值SELECT * FROM users WHERE country NOT IN (\'USA\', \'UK\');
5. BETWEEN - 区间查询
SELECT * FROM orders WHERE amount BETWEEN 100 AND 200;-- 等价于：amount >= 100 AND amount <= 200
6. LIKE - 模糊查询
-- %：匹配任意数量字符SELECT * FROM users WHERE name LIKE \'A%\'; -- 以A开头-- _：匹配单个字符SELECT * FROM products WHERE code LIKE \'P_001\'; -- 如P1001, P2001
7. IS NULL / IS NOT NULL - 空值判断
SELECT * FROM users WHERE email IS NULL;SELECT * FROM products WHERE price IS NOT NULL;
8. EXISTS - 子查询存在性判断
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);-- 仅返回在orders表中有订单的用户

二、结果排序与限制

1. ORDER BY - 结果排序

sql

SELECT * FROM users ORDER BY age ASC; -- 升序（默认）SELECT * FROM users ORDER BY age DESC; -- 降序SELECT * FROM users ORDER BY age DESC, name ASC; -- 多字段排序
2. LIMIT - 限制结果数量
SELECT * FROM users LIMIT 10; -- 返回前10条SELECT * FROM users LIMIT 5, 10; -- 从第6条开始，返回10条（OFFSET=5）
3. DISTINCT - 去重
SELECT DISTINCT country FROM users; -- 返回唯一的国家列表

三、多表关联与合并

1. JOIN - 表连接
-- 内连接（仅匹配两表都存在的记录）SELECT * FROM ordersINNER JOIN users ON orders.user_id = users.id;-- 左连接（返回左表所有记录，右表无匹配时补NULL）SELECT * FROM usersLEFT JOIN orders ON users.id = orders.user_id;-- 右连接（与左连接相反）SELECT * FROM ordersRIGHT JOIN users ON orders.user_id = users.id;-- 自连接（表与自身连接）SELECT e1.name AS employee, e2.name AS managerFROM employees e1LEFT JOIN employees e2 ON e1.manager_id = e2.id;
2. GROUP BY - 分组统计
-- 统计每个部门的员工数量SELECT department, COUNT(*) FROM employeesGROUP BY department;-- 结合聚合函数（SUM, AVG, MAX, MIN）SELECT category, SUM(price) FROM productsGROUP BY category;
3. UNION / UNION ALL - 合并查询结果
-- UNION：去重合并SELECT name FROM customersUNIONSELECT name FROM vendors;-- UNION ALL：保留重复项SELECT name FROM customersUNION ALLSELECT name FROM vendors;

四、高级查询技巧

1. 别名（ALIAS）
SELECT u.name AS username, o.amount AS order_amountFROM users uJOIN orders o ON u.id = o.user_id;
2. 子查询（Subquery）
-- 嵌套在WHERE子句中SELECT * FROM productsWHERE price > (SELECT AVG(price) FROM products);-- 作为临时表SELECT * FROM ( SELECT * FROM orders WHERE status = \'paid\') AS paid_orders;

五、数据修改命令

1. INSERT - 插入数据
-- 插入单条记录INSERT INTO users (name, age) VALUES (\'Alice\', 25);-- 批量插入INSERT INTO users (name, age)VALUES (\'Bob\', 30), (\'Charlie\', 35);
2. UPDATE - 更新数据
UPDATE users SET age = age + 1 WHERE country = \'China\';
3. DELETE - 删除数据
DELETE FROM users WHERE age < 18;
4. REPLACE - 替换数据（先删除再插入）
REPLACE INTO users (id, name) VALUES (1, \'Alice\');-- 若id=1存在，则更新；否则插入

六、实用示例

1. 复杂查询示例
-- 查询每个用户的订单总金额，并按金额降序排序，取前5名SELECT u.name, SUM(o.amount) AS total_amountFROM users uJOIN orders o ON u.id = o.user_idWHERE u.country = \'CN\'GROUP BY u.idHAVING total_amount > 1000ORDER BY total_amount DESCLIMIT 5;
2. 子查询与 JOIN 结合
-- 查询价格高于平均价格的产品SELECT p.*FROM products pJOIN (SELECT AVG(price) AS avg_price FROM products) AS subON p.price > sub.avg_price;
网络标签：

相关问题