SQL基础语句大全：从零开始掌握数据库操作

SQL（Structured Query Language）是管理和操作关系型数据库的标准语言。无论你是数据分析师、后端开发人员还是IT管理者，掌握SQL基础语句都至关重要。本文将系统介绍最常用的SQL语句，帮助你快速建立数据库操作基础。

一、数据库和表的基本操作

创建数据库
```sql
CREATE DATABASE company_db;
```

选择数据库
```sql
USE company_db;
```

创建表
```sql
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department VARCHAR(50),
salary DECIMAL(10,2),
hire_date DATE
);
```

删除表
```sql
DROP TABLE employees;
```

二、数据操作语言（DML）

插入数据
```sql
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('张三', '技术部', 15000.00, '2022-03-15');
```

批量插入
```sql
INSERT INTO employees (name, department, salary, hire_date)
VALUES
('李四', '市场部', 12000.00, '2021-08-20'),
('王五', '财务部', 13000.00, '2020-05-10');
```

查询数据
```sql
SELECT * FROM employees;
```

条件查询
```sql
SELECT name, salary FROM employees
WHERE salary > 10000 AND department = '技术部';
```

更新数据
```sql
UPDATE employees
SET salary = 16000.00
WHERE name = '张三';
```

删除数据
```sql
DELETE FROM employees
WHERE id = 5;
```

三、高级查询技巧

排序查询
```sql
SELECT * FROM employees
ORDER BY salary DESC, name ASC;
```

限制结果集
```sql
SELECT * FROM employees
LIMIT 10;
```

模糊查询
```sql
SELECT * FROM employees
WHERE name LIKE '张%';
```

聚合函数
```sql
SELECT
COUNT(*) as total_employees,
AVG(salary) as avg_salary,
MAX(salary) as max_salary,
MIN(salary) as min_salary
FROM employees;
```

分组查询
```sql
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 12000;
```

四、表连接查询

内连接
```sql
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
```

左连接
```sql
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
```

五、数据定义语言（DDL）

添加列
```sql
ALTER TABLE employees
ADD COLUMN email VARCHAR(100);
```

修改列
```sql
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12,2);
```

删除列
```sql
ALTER TABLE employees
DROP COLUMN email;
```

六、实用技巧和最佳实践

1. 使用别名提高可读性
```sql
SELECT
e.name AS 员工姓名,
e.salary AS 薪资
FROM employees e;
```

2. 处理空值
```sql
SELECT name, COALESCE(department, '未分配') AS department
FROM employees;
```

3. 去重查询
```sql
SELECT DISTINCT department FROM employees;
```

4. 使用CASE语句
```sql
SELECT name, salary,
CASE
WHEN salary > 15000 THEN '高薪'
WHEN salary BETWEEN 10000 AND 15000 THEN '中等'
ELSE '一般'
END AS salary_level
FROM employees;
```

七、注意事项

1. 在执行DELETE或UPDATE语句前，最好先使用SELECT确认影响的数据
2. 重要的数据操作建议在事务中执行
3. 使用WHERE子句限定操作范围，避免全表操作
4. 定期备份数据库

通过掌握这些基础SQL语句，你已经能够完成大多数常见的数据库操作任务。建议在实际工作中多加练习，逐步提升SQL技能水平。记住，熟练使用SQL的关键在于理解业务需求并选择最合适的查询方式。