常用SQL语句大全/SQL常用命令

---

一、基础操作（CRUD）

1. 数据查询（SELECT）

-- 基础查询（指定字段）
SELECT column1, column2 FROM table_name;

-- 查询所有字段
SELECT * FROM table_name;

-- 带条件查询（WHERE）
SELECT * FROM users WHERE age > 18 AND status = 'active';

-- 排序（ORDER BY，默认升序ASC，降序DESC）
SELECT * FROM products ORDER BY price DESC;

-- 分页查询（LIMIT/OFFSET，MySQL/PostgreSQL）
SELECT * FROM orders LIMIT 10 OFFSET 20;  -- 跳过20条，取10条（即第21-30条）
-- 或简写（MySQL）
SELECT * FROM orders LIMIT 20, 10;       -- 前20条跳过，取后10条

-- 去重（DISTINCT）
SELECT DISTINCT department FROM employees;

-- 聚合函数（COUNT/SUM/AVG/MAX/MIN）
SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users;
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- 分组后筛选（HAVING，用于聚合结果的条件）
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 10000;

2. 数据插入（INSERT）

-- 插入单条记录（指定字段）
INSERT INTO users (name, email, age) VALUES ('张三', 'zhangsan@example.com', 25);

-- 插入多条记录（一次插入多行）
INSERT INTO products (name, price, stock) 
VALUES 
  ('手机', 2999, 100),
  ('笔记本', 5999, 50);

3. 数据更新（UPDATE）

-- 更新单条/多条记录（需指定条件避免全表更新！）
UPDATE users SET age = 26 WHERE id = 1;
UPDATE products SET price = price * 0.9 WHERE stock > 100;  -- 打折（库存>100的商品降价10%）

4. 数据删除（DELETE）

-- 删除指定记录（必须加WHERE条件！）
DELETE FROM users WHERE id = 10;
-- 清空表（慎用！会删除所有数据，但保留表结构）
DELETE FROM orders;  
-- 或使用TRUNCATE（效率更高，不可回滚）
TRUNCATE TABLE temp_data;

---

二、表结构操作

1. 创建表（CREATE TABLE）

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,  -- 主键且自增（MySQL）
  -- id INT PRIMARY KEY IDENTITY(1,1), -- SQL Server自增写法
  -- id SERIAL PRIMARY KEY,            -- PostgreSQL自增写法
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,          -- 唯一约束
  age INT CHECK (age >= 18 AND age <= 65),  -- 检查约束（MySQL需用触发器或ENUM替代）
  department_id INT,
  hire_date DATE DEFAULT CURRENT_DATE,      -- 默认当前日期
  salary DECIMAL(10, 2),
  FOREIGN KEY (department_id) REFERENCES departments(id)  -- 外键关联
);

2. 修改表结构（ALTER TABLE）

-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 修改字段类型/约束
ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 2);  -- MySQL
-- ALTER TABLE products ALTER COLUMN price TYPE DECIMAL(12, 2);  -- PostgreSQL

-- 删除字段
ALTER TABLE users DROP COLUMN phone;

-- 添加主键/唯一约束
ALTER TABLE employees ADD CONSTRAINT pk_employees PRIMARY KEY (id);
ALTER TABLE emails ADD CONSTRAINT uk_email UNIQUE (email);

-- 添加外键
ALTER TABLE orders ADD CONSTRAINT fk_customer 
  FOREIGN KEY (customer_id) REFERENCES customers(id);

3. 删除表（DROP TABLE）

DROP TABLE temp_table;  -- 直接删除表（含数据与结构）
DROP TABLE IF EXISTS old_table;  -- 安全写法（表不存在时不报错）

---

三、高级查询

1. 多表连接（JOIN）

-- 内连接（INNER JOIN，只返回匹配的记录）
SELECT o.order_id, c.name, o.amount 
FROM orders o 
INNER JOIN customers c ON o.customer_id = c.id;

-- 左连接（LEFT JOIN，返回左表所有记录+右表匹配的记录）
SELECT e.name, d.department_name 
FROM employees e 
LEFT JOIN departments d ON e.department_id = d.id;

-- 右连接（RIGHT JOIN，返回右表所有记录+左表匹配的记录）
SELECT p.product_name, o.order_id 
FROM products p 
RIGHT JOIN order_items o ON p.id = o.product_id;

-- 全连接（FULL JOIN，MySQL不支持，需用UNION模拟）
-- 交叉连接（CROSS JOIN，生成笛卡尔积）
SELECT a.name, b.category FROM table_a a CROSS JOIN table_b b;

2. 子查询

-- 嵌套子查询（作为条件）
SELECT name FROM products 
WHERE price > (SELECT AVG(price) FROM products);

-- IN子查询（匹配子查询结果集）
SELECT name FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = '北京');

-- EXISTS子查询（判断子查询是否有结果）
SELECT name FROM customers c 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

3. 联合查询（UNION/UNION ALL）

-- 合并多个查询结果（自动去重，要求字段数/类型一致）
SELECT name FROM employees WHERE department = '技术部'
UNION
SELECT name FROM contractors WHERE role = '开发';

-- 保留所有记录（包括重复）
SELECT product FROM list1 
UNION ALL 
SELECT product FROM list2;

4. 分组与过滤（GROUP BY + HAVING）

-- 按部门统计人数与平均工资
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees 
GROUP BY department
HAVING AVG(salary) > 15000;  -- 过滤分组后的结果

---

四、数据控制与事务

1. 事务管理（Transaction）

-- 开启事务（MySQL默认自动提交，需显式关闭）
START TRANSACTION;  -- 或 BEGIN;
-- 执行SQL操作（如转账：扣款+入账）
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 提交（成功）或回滚（失败）
COMMIT;  -- 确认更改
-- ROLLBACK;  -- 撤销所有操作

2. 权限控制（GRANT/REVOKE）

-- 授权用户查询和插入权限
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';
-- 撤销权限
REVOKE DELETE ON database_name.* FROM 'username'@'host';

---

五、数据库管理

1. 备份与恢复（以MySQL为例）

# 备份数据库（命令行工具mysqldump）
mysqldump -u username -p database_name > backup.sql

# 恢复数据库
mysql -u username -p database_name < backup.sql

2. 索引操作

-- 创建索引（加速查询）
CREATE INDEX idx_employee_name ON employees(name);
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 删除索引
DROP INDEX idx_employee_name ON employees;

3. 视图（VIEW）

-- 创建视图（简化复杂查询）
CREATE VIEW active_users AS 
SELECT id, name, email FROM users WHERE status = 'active';

-- 使用视图（像表一样查询）
SELECT * FROM active_users WHERE name LIKE '张%';

---

六、常用函数

1. 聚合函数

• COUNT(*)：记录总数
• SUM(column)：数值列总和
• AVG(column)：平均值
• MAX(column)/MIN(column)：最大/最小值

2. 字符串函数

• CONCAT(str1, str2)：拼接字符串
• SUBSTRING(str, start, length)：截取子串（MySQL从1开始）
• UPPER(str)/LOWER(str)：转大写/小写
• TRIM(str)：去除首尾空格

3. 日期函数

• NOW()：当前日期时间
• CURDATE()：当前日期
• DATEDIFF(date1, date2)：日期差（天数）
• DATE_FORMAT(date, '%Y-%m-%d')：格式化日期（MySQL）

4. 条件函数

• CASE WHEN condition THEN result ELSE default END：多条件判断

  SELECT name, 
         CASE WHEN score >= 90 THEN '优秀' 
              WHEN score >= 60 THEN '及格' 
              ELSE '不及格' END AS level 
  FROM students;

---

​提示​：不同数据库（MySQL/Oracle/SQL Server/PostgreSQL）语法可能有差异（如分页、自增字段写法），实际使用时需根据具体数据库调整。建议通过数据库管理工具（如Navicat、DBeaver）或命令行实践验证！