有哪些必须要掌握的数据库与sql基础知识

发布时间：2021-10-22 13:52:38 作者：iii
来源：亿速云阅读：200

# 有哪些必须要掌握的数据库与SQL基础知识

## 目录
1. [数据库基础概念](#数据库基础概念)
2. [关系型数据库核心理论](#关系型数据库核心理论)
3. [SQL语言分类与语法结构](#sql语言分类与语法结构)
4. [数据定义语言(DDL)](#数据定义语言ddl)
5. [数据操作语言(DML)](#数据操作语言dml)
6. [数据查询语言(DQL)](#数据查询语言dql)
7. [数据控制语言(DCL)](#数据控制语言dcl)
8. [事务控制语言(TCL)](#事务控制语言tcl)
9. [数据库索引与优化](#数据库索引与优化)
10. [数据库设计范式](#数据库设计范式)
11. [常见数据库管理系统](#常见数据库管理系统)
12. [学习资源推荐](#学习资源推荐)

---

## 数据库基础概念

### 什么是数据库
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，具有以下特征：
- 持久化存储
- 有组织的结构
- 可共享访问
- 统一管理机制

### 数据库管理系统(DBMS)
DBMS是管理数据库的软件系统，主要功能包括：
- 数据定义
- 数据操作
- 数据安全
- 并发控制
- 故障恢复

### 数据库类型
1. **关系型数据库**：MySQL、Oracle、SQL Server
2. **非关系型数据库**：MongoDB、Redis、Cassandra
3. **时序数据库**：InfluxDB、TimescaleDB
4. **图数据库**：Neo4j、ArangoDB

---

## 关系型数据库核心理论

### 关系模型三要素
1. **数据结构**：二维表结构
2. **数据操作**：关系代数运算
3. **完整性约束**：实体/参照/用户定义完整性

### 核心概念
- **表(Table)**：数据存储的基本单位
- **行(Row/Record)**：一条完整数据记录
- **列(Column/Field)**：数据的特定属性
- **主键(PK)**：唯一标识记录的字段
- **外键(FK)**：建立表间关联的字段

---

## SQL语言分类与语法结构

### SQL分类
| 类型 | 功能 | 常用命令 |
|------|------|----------|
| DDL | 定义数据结构 | CREATE, ALTER, DROP |
| DML | 数据增删改 | INSERT, UPDATE, DELETE |
| DQL | 数据查询 | SELECT |
| DCL | 权限控制 | GRANT, REVOKE |
| TCL | 事务控制 | COMMIT, ROLLBACK |

### 基本语法特点
- 不区分大小写（但建议关键字大写）
- 以分号(;)结束语句
- 支持单行(--)和多行(/* */)注释

---

## 数据定义语言(DDL)

### 数据库操作
```sql
-- 创建数据库
CREATE DATABASE school CHARACTER SET utf8mb4;

-- 修改数据库
ALTER DATABASE school CHARACTER SET utf8;

-- 删除数据库
DROP DATABASE school;

表操作

-- 创建表
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender CHAR(1) CHECK(gender IN ('M','F')),
    birth_date DATE,
    class_id INT,
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 修改表结构
ALTER TABLE students ADD COLUMN email VARCHAR(100);

-- 删除表
DROP TABLE students;

数据操作语言(DML)

插入数据

-- 单条插入
INSERT INTO students(name, gender) VALUES('张三', 'M');

-- 批量插入
INSERT INTO students(name, gender) VALUES
('李四', 'F'),
('王五', 'M');

更新数据

UPDATE students 
SET name = '张三丰', email = 'zhangsan@example.com'
WHERE id = 1;

删除数据

-- 条件删除
DELETE FROM students WHERE id = 5;

-- 清空表
TRUNCATE TABLE students;  -- 不可回滚，效率更高

数据查询语言(DQL)

基础查询

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

-- 查询指定字段
SELECT first_name, last_name, salary FROM employees;

-- 使用别名
SELECT first_name AS "名", last_name "姓" FROM employees;

条件查询

-- 比较运算符
SELECT * FROM products WHERE price > 100;

-- 逻辑运算符
SELECT * FROM employees 
WHERE salary > 5000 AND department = 'IT';

-- IN/NOT IN
SELECT * FROM students WHERE class_id IN (1, 3, 5);

-- LIKE模糊查询
SELECT * FROM customers WHERE name LIKE '张%';

-- BETWEEN范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

聚合函数

SELECT 
    COUNT(*) AS total_employees,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees;

分组查询

SELECT 
    department, 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

多表连接

-- 内连接
SELECT s.name, c.class_name 
FROM students s
INNER JOIN classes c ON s.class_id = c.id;

-- 左外连接
SELECT s.name, c.class_name 
FROM students s
LEFT JOIN classes c ON s.class_id = c.id;

-- 自连接(查询员工及其经理)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

子查询

-- WHERE子查询
SELECT name FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子查询
SELECT dept_name, avg_salary
FROM (
    SELECT d.name AS dept_name, AVG(e.salary) AS avg_salary
    FROM departments d
    JOIN employees e ON d.id = e.dept_id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 10000;

数据控制语言(DCL)

用户权限管理

-- 创建用户
CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'password123';

-- 授予权限
GRANT SELECT, INSERT ON school.* TO 'dev_user'@'localhost';

-- 撤销权限
REVOKE INSERT ON school.* FROM 'dev_user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'dev_user'@'localhost';

事务控制语言(TCL)

事务特性(ACID)

原子性(Atomicity)：全部成功或全部失败
一致性(Consistency)：数据状态保持一致
隔离性(Isolation)：并发事务互不干扰
持久性(Durability)：提交后永久生效

事务操作

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交或回滚
COMMIT;  -- 或 ROLLBACK;

数据库索引与优化

索引类型

普通索引：加速查询
唯一索引：确保列值唯一
主键索引：特殊的唯一索引
复合索引：多列组合索引
全文索引：文本内容搜索

创建索引

-- 创建普通索引
CREATE INDEX idx_name ON students(name);

-- 创建复合索引
CREATE INDEX idx_name_gender ON students(name, gender);

-- 查看索引
SHOW INDEX FROM students;

SQL优化建议

避免SELECT *，只查询需要的列
合理使用索引，避免全表扫描
避免在WHERE子句中对字段进行函数操作
大数据量分页使用LIMIT优化
减少子查询，优先使用JOIN

数据库设计范式

三大范式

第一范式(1NF)：字段原子性，不可再分
第二范式(2NF)：消除部分依赖（非主键字段完全依赖主键）
第三范式(3NF)：消除传递依赖（非主键字段不依赖其他非主键字段）

反范式设计

为提高查询性能，有时需要故意违反范式： - 适当的数据冗余 - 预计算字段 - 宽表设计

常见数据库管理系统

数据库	特点	适用场景
MySQL	开源、易用、性能好	Web应用、中小企业
PostgreSQL	功能强大、支持复杂查询	地理空间、复杂业务
Oracle	企业级、高可用、昂贵	大型企业、金融系统
SQL Server	微软生态、集成性好	Windows环境应用
SQLite	轻量级、嵌入式	移动应用、本地存储

学习资源推荐

在线教程

SQLZoo - 交互式SQL练习
W3Schools SQL - 基础语法参考
LeetCode数据库题库 - 实战练习

书籍推荐

《SQL必知必会》- 入门经典
《高性能MySQL》- MySQL深度优化
《数据库系统概念》- 理论经典教材

实践建议

安装MySQL/PostgreSQL本地环境
设计个人项目数据库模型
参与开源项目数据库相关工作
关注数据库技术社区(如掘金、InfoQ)

掌握这些数据库和SQL基础知识后，您已经具备了开发大多数数据驱动应用的能力。随着实践深入，可以进一步学习： - 数据库分库分表 - 读写分离 - 分布式事务 - 数据库中间件 - 大数据处理框架

祝您学习顺利！ “`

这篇文章涵盖了数据库和SQL的核心知识点，包括： 1. 基础概念和分类 2. 关系型数据库理论 3. SQL语法详解（DDL/DML/DQL/DCL/TCL） 4. 索引优化和设计范式 5. 主流数据库比较 6. 学习路径建议

总字数约2500字，采用Markdown格式编写，便于阅读和传播。如需扩展某个部分，可以进一步补充具体案例或深入原理说明。