深入解析：【数据库基础】SQL与关系型数据库原理

数据库基础

一、数据库核心概念

1.1 数据库定义与组成

• 数据库：有组织的数据集合，便于存储、管理和检索
• 数据库系统 = 硬件 + OS + 数据库 + DBMS + 应用软件 + 用户
• DBMS（数据库管理系统）：操作和管理数据库的软件

1.2 数据层级结构

MySQL服务 → 数据库 → 数据表 → 行（记录） + 列（字段）

1.3 数据库在企业中的定位

• 存储和管理核心数据
• 应用场景：
  • 社交应用：用户信息、聊天记录
  • 电商平台：订单、支付数据
  • 游戏系统：玩家数据、装备信息

二、数据库类型与发展

2.1 数据库发展历程

1. 第一代：层次型/网状型数据库（IMS）
2. 第二代：关系型数据库（MySQL、Oracle）
3. 第三代：新型数据库（NoSQL、时序数据库）

2.2 关系型数据库

• 核心特征：
  • 基于二维表结构
  • 支持ACID特性
  • 使用SQL语言操作
• 代表产品：MySQL、Oracle、PostgreSQL、SQL Server
• 数据模型：E-R模型（实体-关系模型）

2.3 非关系型数据库（NoSQL）

• 核心特征：
  • 灵活的数据结构
  • 高性能读写
  • 高可扩展性
• 主要类型：
  • 键值数据库：Redis
  • 文档数据库：MongoDB
  • 列式数据库：HBase
  • 图数据库：Neo4j

三、关系型 vs 非关系型对比

维度	关系型数据库	非关系型数据库
数据结构	表结构固定	结构灵活多样
查询语言	SQL标准	各自专用API
事务支持	完善的ACID	有限或最终一致性
扩展性	垂直扩展为主	水平扩展容易
适用场景	复杂查询、事务系统	高并发、大数据量
代表产品	MySQL、Oracle	Redis、MongoDB

四、SQL语言分类

4.1 DDL（数据定义语言）

CREATE DATABASE/TABLE    -- 创建
ALTER TABLE             -- 修改
DROP DATABASE/TABLE     -- 删除

4.2 DML（数据操纵语言）

INSERT   -- 插入数据
UPDATE   -- 更新数据
DELETE   -- 删除数据

4.3 DQL（数据查询语言）

SELECT   -- 查询数据

4.4 DCL（数据控制语言）

GRANT    -- 授权
REVOKE   -- 撤销权限
COMMIT   -- 提交事务
ROLLBACK -- 回滚事务

五、数据库核心操作

5.1 数据库操作

-- 创建数据库
CREATE DATABASE 数据库名;
-- 选择数据库
USE 数据库名;
-- 查看所有数据库
SHOW DATABASES;

5.2 数据表操作

-- 创建表
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age INT,score DECIMAL(4,2),created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 查看表结构
DESC students;
-- 修改表结构
ALTER TABLE students ADD COLUMN email VARCHAR(100);
ALTER TABLE students DROP COLUMN age;

5.3 数据操作

-- 插入数据
INSERT INTO students (name, score) VALUES ('张三', 85.5);
-- 查询数据
SELECT * FROM students WHERE score > 60;
SELECT name, score FROM students ORDER BY score DESC;
-- 更新数据
UPDATE students SET score = 90.0 WHERE name = '张三';
-- 删除数据
DELETE FROM students WHERE score < 60;

5.4 高级查询

-- 条件查询
SELECT * FROM students WHERE score BETWEEN 60 AND 90;
-- 聚合查询
SELECT AVG(score) as avg_score, COUNT(*) as total FROM students;
-- 分组查询
SELECT name, AVG(score) FROM students GROUP BY name HAVING AVG(score) > 80;
-- 多表连接
SELECT s.name, c.course_name
FROM students s
JOIN courses c ON s.course_id = c.id;

六、数据类型详解

6.1 数值类型

• 整型：INT、TINYINT、BIGINT
• 浮点型：FLOAT、DOUBLE
• 精确小数：DECIMAL(精度,小数位)

6.2 字符串类型

• 定长：CHAR(长度) - 性能好
• 变长：VARCHAR(最大长度) - 节省空间
• 文本：TEXT - 大段文字

6.3 时间类型

• DATE：日期
• TIME：时间
• DATETIME：日期时间
• TIMESTAMP：时间戳

七、用户与权限管理

7.1 用户管理

-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
-- 删除用户
DROP USER 'username'@'host';
-- 修改密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('new_password');

7.2 权限管理

-- 授权
GRANT SELECT, INSERT ON database.table TO 'user'@'host';
-- 撤销权限
REVOKE INSERT ON database.table FROM 'user'@'host';
-- 查看权限
SHOW GRANTS FOR 'user'@'host';

7.3 权限分类

权限	说明	权限	说明
SELECT	查询	INSERT	插入
UPDATE	更新	DELETE	删除
CREATE	创建	DROP	删除
ALTER	修改	INDEX	索引
ALL	所有权限