mysql的日常使用操作

在软件工程当中，mysql数据库是非常重要的组成部分。无论是开发还是测试，掌握mysql的使用都是势在必行的。
在菜鸟教程中使用

mysql的基础概念

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
与数据结构有关
mysql数据类型：
数值、日期时间、字符串、枚举与集合、空间数据类型
int、datetime、str、ENUM、set、
空间数据类型：GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION

数据库安装

mac安装mysql
使用brew install mysql进行安装
win安装mysql
https://www.runoob.com/w3cnote/windows10-mysql-installer.html
下载zip安装包之后解压到对应的目录，而后进行msyql的配置
打开刚刚解压的文件夹，在该文件夹下创建 my.ini 配置文件，编辑 my.ini 配置以下基本信息：

点击查看代码

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8
 
[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=C:\\web\\mysql-8.0.11
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=C:\\web\\sqldata
# 允许最大连接数
max_connections=20
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

点击查看代码

首先初始化数据库，输出root用户的初始密码
`mysqld --initialize --console`
`...
2018-04-20T02:35:05.464644Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: APWCY5ws&hjQ
...`
安装
`mysqld install`
启动
`net start mysql`
再登录mysql
`mysql -u root -p`

##数据库用户管理 #用户创建

点击查看代码

CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：用户名。
host：指定用户可以从哪些主机连接。例如，localhost 仅允许本地连接，% 允许从任何主机连接。
password：用户的密码。

#用户授权刷新授权撤销

点击查看代码

GRANT privileges ON database_name.* TO 'username'@'host';
FLUSH PRIVILEGES;
查看用户权限
SHOW GRANTS FOR 'username'@'host';
撤销权限
REVOKE privileges ON database_name.* FROM 'username'@'host';

#删除用户修改密码

点击查看代码

DROP USER 'username'@'host';
修改用户密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';
修改主机
-- 删除旧用户
DROP USER 'john'@'localhost';

-- 重新创建用户并指定新的主机
CREATE USER 'john'@'%' IDENTIFIED BY 'password123';

或者创建用户时指定权限

点击查看代码

CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON test_db.* TO 'john'@'localhost';

# /etc/my.cnf 文件配置 1. 基本设置 basedir: MySQL 服务器的基本安装目录。 datadir: 存储 MySQL 数据文件的位置。 socket: MySQL 服务器的 Unix 套接字文件路径。 pid-file: 存储当前运行的 MySQL 服务器进程 ID 的文件路径。 port: MySQL 服务器监听的端口号，默认是 3306。 2. 服务器选项 bind-address: 指定 MySQL 服务器监听的 IP 地址，可以是 IP 地址或主机名。 server-id: 在复制配置中，为每个 MySQL 服务器设置一个唯一的标识符。 default-storage-engine: 默认的存储引擎，例如 InnoDB 或 MyISAM。 max_connections: 服务器可以同时维持的最大连接数。 thread_cache_size: 线程缓存的大小，用于提高新连接的启动速度。 query_cache_size: 查询缓存的大小，用于提高相同查询的效率。 default-character-set: 默认的字符集。 collation-server: 服务器的默认排序规则。 3. 性能调优 innodb_buffer_pool_size: InnoDB 存储引擎的缓冲池大小，这是 InnoDB 性能调优中最重要的参数之一。 key_buffer_size: MyISAM 存储引擎的键缓冲区大小。 table_open_cache: 可以同时打开的表的缓存数量。 thread_concurrency: 允许同时运行的线程数。 4. 安全设置 skip-networking: 禁止 MySQL 服务器监听网络连接，仅允许本地连接。 skip-grant-tables: 以无需密码的方式启动 MySQL 服务器，通常用于恢复忘记的 root 密码，但这是一个安全风险。 auth_native_password=1: 启用 MySQL 5.7 及以上版本的原生密码认证。 5. 日志设置 log_error: 错误日志文件的路径。 general_log: 记录所有客户端连接和查询的日志。 slow_query_log: 记录执行时间超过特定阈值的慢查询。 log_queries_not_using_indexes: 记录未使用索引的查询。 6. 复制设置 master_host 和 master_user: 主服务器的地址和复制用户。 master_password: 复制用户的密码。 master_log_file 和 master_log_pos: 用于复制的日志文件和位置。 #管理msyql的命令

点击查看代码

mysql> use RUNOOB;
Database changed
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| RUNOOB             |
| cdcol              |
| mysql              |
| onethink           |
| performance_schema |
| phpmyadmin         |
| test               |
| wecenter           |
| wordpress          |
+--------------------+
10 rows in set (0.02 sec)
mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl     |
| runoob_tbl       |
| tcount_tbl       |
+------------------+
3 rows in set (0.00 sec)
mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id       | int(11)      | NO   | PRI | NULL    |       |
| runoob_title    | varchar(255) | YES  |     | NULL    |       |
| runoob_author   | varchar(255) | YES  |     | NULL    |       |
| submission_date | date         | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> SHOW INDEX FROM runoob_tbl;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| runoob_tbl |          0 | PRIMARY  |            1 | runoob_id   | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> SHOW TABLE STATUS  FROM RUNOOB;   # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%';     # 表名以runoob开头的表的信息
mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G;   # 加上 \G，查询结果按列打印

##mysql表数据的增删改 #mysql连接

点击查看代码

mysql -u your_username -p

#增删改

点击查看代码

创建数据库
CREATE DATABASE 数据库名;
drop命令删除数据库
DROP DATABASE <database_name>;        -- 直接删除数据库，不检查是否存在
或
DROP DATABASE [IF EXISTS] <database_name>;
选择数据库
USE database_name;
创建数据表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
);
删除数据表
DROP TABLE table_name;     -- 直接删除表，不检查是否存在
DROP TABLE [IF EXISTS] table_name;  -- 会检查是否存在，如果存在则删除
TRUNCATE TABLE table_name;清除表数据，保留表结构
插入数据
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
更新数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
约束创建
非空约束–NOT NULL
建立表时指定约束。
create table table_name
(
id number(10) constraint 约束名 not null,   
name varchar2(255)
);
表建立好后再指定约束。
建表: create table table_name(id number(10),name varchar2(255));
指定约束: alter table table_name modify (id constraint 约束名 not null) ;
删除约束: alter table table_name drop constraint 约束名;
检查约束–CHECK
1.    建立表时指定约束。
列级约束：create table table_name
(
id number(10) constraint 约束名 check(id>1),
name varchar2(255)
);
表级约束：create table table_name(id number(10),name varchar2(255), constraint 约束名 check(id>1 and name like ‘%@%’));
表建立好后再指定约束。
建表: create table table_name(id number(10),name varchar2(255));
指定约束: alter table table_name add constraint 约束名 check(id>1);
删除约束: alter table table_name drop constraint 约束名;
唯一约束–UNIQUE 
1.    建立表时指定约束。
列级约束：create table table_name(id number(10) constraint 约束名 unique,name varchar2(255));
表级约束：create table table_name(id number(10),name varchar2(255), constraint 约束名 unique(id,name));
表建立好后再指定约束。
建表: create table table_name(id number(10),name varchar2(255));
指定约束: alter table table_name add (constraint 约束名 unique(id));
删除约束: alter table table_name drop constraint 约束名;
主键约束–PRIMARY KEY
1.    建立表时指定约束。
列级约束：create table dept1(dept number(10) constraint 约束名 primary key, dept_name varchar2(255));
表级约束：create table dept1 (dept number(10), dept_name varchar2(255), constraint 约束名 primary key(dept,dept_name));
表建立好后再指定约束。
建表: create table dept1 (dept number(10), dept_name varchar2(255));
指定约束: alter table dept1 add (constraint 约束名 primary key(dept));
删除约束: alter table dept1 drop constraint 约束名;
建立表时指定约束。
create table emp1
(
id number(10),
 name varchar2(20),
 dept number(10), 
constraint 约束名 foreign key(dept) references dept1(dept));
表建立好后再指定约束。
建表：create table emp1(id number(10), name varchar2(20), dept number(10));
指定约束：alter table emp1 add (constraint 约束名 foreign key(dept) references dept1(dept));
删除约束：alter table emp1 drop constraint 约束名;
ALTER:
增加列: ALTER  TABLE  表名  ADD  列名   类型 如：ALTER TABLE dept80 ADD (job_id VARCHAR2(9));
修改列的属性:ALTER  TABLE  表名 MODIFY  列名   类型如：ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));
修改列名:ALTER TABLE dept80 rename column 列名 to 新列名
删除列:ALTER TABLE 表名 DROP column 列名如： ALTER TABLE dept80 DROP column job_id;

主键的作用是什么主键和索引的关系是什么主键和连表查询之间的关系 ##mysql的查询 #简单查询

点击查看代码

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];
-- 选择所有列的所有行
SELECT * FROM users;

-- 选择特定列的所有行
SELECT username, email FROM users;

-- 添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;

-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;

-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;

-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;
模糊查询
SELECT * from tbl_user where usrname LIKE ‘%zhao%’
连接范围查询：使用BETWEEN ..AND..
	SELECT AGE FROM tbl_user where age BETWEEN ‘18’ AND ‘24’

4.离散范围查询：使用 IN   (NOT IN)
	SELECT * from tbl_user where username IN(‘liuyifei’,’zhaoying’)
涉及空值的查询 :使用 IS NULL	(IS NOT NULL)
	SELECT * from tbl_user where email IS NULL
COUNT(计数)函数: COUNT(*)返回表中记录总数。
    
       SELECT COUNT(*) FROMemployees WHERE department_id = 50;
在SELECT子句中使用关键字’DISTINCT’删除重复行

      SELECT DISTINCT department_id
COUNT(DISTINCT expr)返回expr非空且不重复的记录总数
SUM 函数返回数值列的总数（总额）
SELECT SUM(column_name) FROM table_name
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SELECT AVG(column_name) FROM table_nam
MAX 函数返回一列中的最大值。NULL 值不包括在计算中
SELECT MAX(column_name) FROM table_name
FROM employees;
MIN 函数返回一列中的最小值。NULL 值不包括在计算中
SELECT MIN(column_name) FROM table_name
Group by
GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT column_name1, aggregate_function(column_name2)
FROM table_name
GROUP BY column_name1

#复杂查询

点击查看代码

HAVING
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
左连接+分组
SELECT 
  a.status,
  COUNT(a.id) AS activity_count
FROM `activity` a  
LEFT JOIN activity_sku asku ON a.id = asku.activity_id 
GROUP BY a.status 
LIMIT 0, 25;
复制表结构及其数据
create table table_name2 as select * from table_name1;
只复制表结构
create table table_name3  as select * from table_name1 where 1=2;
只复制表数据(需要两个表的结构一样)
insert into table_name3 SELECT * FROM table_name1 ;
复制表数据 
insert into table_name3 (column_name1, column_name2) SELECT column_name1, column_name2 FROM table_name1;
查询活动status为10的活动sku
SELECT 
  a.id,
  asku.sku_code
FROM `activity` a  
LEFT JOIN activity_sku asku ON a.id = asku.activity_id AND a.status = 10
GROUP BY a.id, asku.sku_code  -- 添加asku.sku_code到GROUP BY
LIMIT 0, 25;
查询sku表中没有数据的活动id
SELECT a.*
FROM activities a                 -- A表：活动表
LEFT JOIN activity_prizes b       -- B表：奖品表
  ON a.id = b.activity_id         -- 通过活动ID关联
WHERE b.id IS NULL;               -- 筛选奖品表中无匹配记录的活动

#查询试题

点击查看代码

``` 以下是一组SQL试题，涵盖基础查询、表关联、聚合函数、子查询等核心知识点，并附带详细解析。你可以通过练习这些题目巩固SQL技能，也可用于面试或学习自测。

一、基础查询题

1. 表结构说明

-- 学生表（students）
CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT,
  gender CHAR(1),
  class_id INT
);

-- 课程表（courses）
CREATE TABLE courses (
  id INT PRIMARY KEY,
  course_name VARCHAR(100),
  teacher VARCHAR(50)
);

-- 成绩表（scores）
CREATE TABLE scores (
  id INT PRIMARY KEY,
  student_id INT,
  course_id INT,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

2. 题目

查询所有年龄大于18岁的女生信息

SELECT * FROM students WHERE age > 18 AND gender = '女';

查询每个班级的学生数量，并按人数降序排列

SELECT class_id, COUNT(id) AS student_count 
FROM students 
GROUP BY class_id 
ORDER BY student_count DESC;

查询选修了“数学”课程且成绩大于80分的学生姓名和成绩

SELECT s.name, sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE c.course_name = '数学' AND sc.score > 80;

二、进阶关联与子查询

查询没有选修任何课程的学生姓名
```
SELECT s.name
FROM students s
WHERE NOT EXISTS (
  SELECT 1 FROM scores WHERE student_id = s.id
);
```
解析：使用NOT EXISTS子查询，当学生在成绩表中无记录时返回。

查询各课程的平均分，并筛选出平均分大于75分的课程

SELECT c.course_name, AVG(sc.score) AS avg_score
FROM courses c
JOIN scores sc ON c.id = sc.course_id
GROUP BY c.course_name
HAVING avg_score > 75;

解析：通过HAVING筛选分组后的聚合结果。

查询每个学生的最高成绩课程名称

SELECT s.name, c.course_name, sc.score
FROM students s
JOIN scores sc ON s.id = sc.student_id
JOIN courses c ON sc.course_id = c.id
WHERE (sc.student_id, sc.score) IN (
  SELECT student_id, MAX(score) 
  FROM scores 
  GROUP BY student_id
);

解析：子查询先获取每个学生的最高成绩，再关联主表查询课程名称。

三、性能优化与高级操作

优化查询：查询选修超过3门课程的学生姓名
原始写法：

SELECT s.name
FROM students s
JOIN (
  SELECT student_id FROM scores 
  GROUP BY student_id 
  HAVING COUNT(course_id) > 3
) sc ON s.id = sc.student_id;

优化点：在scores(student_id)添加索引，减少分组耗时。

使用窗口函数查询各班级成绩排名前2的学生

WITH ranked_students AS (
  SELECT 
    s.name, s.class_id, sc.score,
    ROW_NUMBER() OVER (
      PARTITION BY s.class_id 
      ORDER BY sc.score DESC
    ) AS rank
  FROM students s
  JOIN scores sc ON s.id = sc.student_id
)
SELECT * FROM ranked_students WHERE rank <= 2;

解析：PARTITION BY按班级分组，ROW_NUMBER()生成排名。

批量更新：将“英语”课程成绩低于60分的学生成绩提高10分

UPDATE scores sc
JOIN courses c ON sc.course_id = c.id
SET sc.score = sc.score + 10
WHERE c.course_name = '英语' AND sc.score < 60;

四、表设计与事务题

设计一个订单表，包含订单基本信息和商品明细，要求满足第三范式（3NF）
表结构设计：
- orders表：order_id, customer_id, order_time, total_amount
- order_items表：id, order_id, product_id, quantity, price
- products表：product_id, product_name, category, price
  解析：拆分主表与明细表，避免数据冗余。

编写事务代码：实现转账功能（从账户A转100元到账户B）

START TRANSACTION;
BEGIN TRY
  -- 扣除账户A余额
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
  -- 增加账户B余额
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
  COMMIT;
END TRY
BEGIN CATCH
  ROLLBACK;
  SELECT '转账失败：' + ERROR_MESSAGE();
END CATCH;

解析：使用事务保证操作原子性，错误时回滚。

五、逻辑推理题

表数据如下，查询连续3天签到的用户

-- 签到表（checkins）: user_id, checkin_date
SELECT DISTINCT a.user_id
FROM checkins a
JOIN checkins b ON a.user_id = b.user_id AND DATEDIFF(b.checkin_date, a.checkin_date) = 1
JOIN checkins c ON a.user_id = c.user_id AND DATEDIFF(c.checkin_date, a.checkin_date) = 2;

解析：通过自连接关联连续日期，DATEDIFF计算日期差。

六、常见错误与解决方案

错误信息：#1055 - Expression not in GROUP BY
场景：

SELECT s.class_id, s.name
FROM students s
GROUP BY s.class_id;

原因：s.name未在GROUP BY中，且非聚合列。
解决方案：

SELECT s.class_id, MAX(s.name) AS name  -- 使用聚合函数
FROM students s
GROUP BY s.class_id;

七、附加挑战题

查询每个学生最近一次的考试成绩（MySQL 8.0+）

SELECT s.name, c.course_name, sc.score
FROM students s
JOIN (
  SELECT student_id, course_id, score,
    ROW_NUMBER() OVER (
      PARTITION BY student_id 
      ORDER BY exam_time DESC
    ) AS rn
  FROM scores
) sc ON s.id = sc.student_id AND sc.rn = 1
JOIN courses c ON sc.course_id = c.id;

答案解析与学习建议

核心知识点回顾：
- 关联查询：JOIN、LEFT JOIN
- 聚合函数：COUNT、AVG、MAX
- 分组筛选：GROUP BY + HAVING
- 子查询：IN、EXISTS
- 窗口函数：ROW_NUMBER、RANK
学习资源推荐：
- 官方文档：MySQL 8.0 Reference Manual
- 实战平台：LeetCode SQL题目
- 书籍：《SQL必知必会》、《高性能MySQL》

如果需要针对某类题型深入练习或解答疑问，可以进一步说明需求，我会提供更具体的案例和解析。

</details>

mysql当中索引、视图、存储过程的作用

索引

索引主要通过数据结构优化检索路径和减少数据扫描范围来提高查询效率，以下是具体原理及实现逻辑：

一、索引的核心数据结构：以B+树为例

索引通常采用B+树（或哈希、全文等结构）组织数据，其特点如下：

分层存储：类似字典的目录结构，将数据按字段值排序后分层存储，上层节点是下层节点的索引。
有序性：所有叶子节点按顺序链接，便于范围查询。
快速定位：通过树的层级遍历，将查询复杂度从全表扫描的O(n)降低到O(log n)。

示例：若对user表的name字段创建B+树索引：

索引树按name字母顺序排序；
查询name='张三'时，从根节点开始对比，逐层缩小范围，最终定位到对应数据页。

二、索引提高查询效率的具体机制

1. 避免全表扫描，缩小检索范围

全表扫描：无索引时，数据库需遍历表中所有行，效率极低（如100万行数据需扫描100万次）。
索引扫描：通过索引快速定位目标数据的物理位置，仅需扫描少量索引节点和对应数据页。
- 例：查询WHERE age=30，若age有索引，直接通过索引找到所有age=30的记录位置，无需扫描其他行。

2. 利用索引排序特性优化查询

索引本身有序：B+树叶子节点按顺序排列，可直接用于ORDER BY、GROUP BY等排序操作。
- 例：查询SELECT * FROM user ORDER BY age，若age有索引，数据库无需额外排序，直接按索引顺序读取数据。

3. 覆盖索引：直接从索引获取结果

若查询字段均包含在索引中（即“覆盖索引”），无需回表查询数据行，直接返回索引结果。
- 例：表结构为user(id, name, age)，若对(name, age)创建联合索引，查询SELECT name, age WHERE name='张三'时，可直接从索引获取结果，无需访问数据表。

4. 优化JOIN操作

多表JOIN时，若连接条件字段有索引，可快速定位匹配记录，减少表关联的计算量。
- 例：SELECT a.* FROM table_a a JOIN table_b b ON a.id = b.a_id，若b.a_id有索引，可快速找到table_b中匹配a.id的记录。

三、索引失效场景（效率可能下降）

字段类型不匹配：如索引为varchar类型，查询时传入数字（未加引号），导致索引失效。
使用左模糊查询：如WHERE name LIKE '%张三'，无法利用B+树的有序性。
索引列参与计算或函数：如WHERE age+1=31，需改为WHERE age=30。
数据分布不均：如索引字段90%的值为0，索引优化效果有限（可考虑分区或直方图统计）。

四、总结：索引的“空间换时间”本质

优势：通过预排序和数据结构优化，将查询时间从O(n)降至O(log n)，大幅提升检索效率。
代价：占用额外存储空间（约为表大小的10%-30%），且数据更新时需维护索引结构，可能降低写入性能。

最佳实践：为高频查询字段、主键、外键创建索引，避免过度索引（平衡查询与写入性能）。

视图

视图（View）是数据库中一个虚拟的表，它基于真实表的查询结果构建，本身并不存储实际数据。下面从定义、作用及应用场景等方面详细说明：

一、视图的本质与定义

虚拟表特性：视图由SELECT语句定义，可理解为“存储的查询结果”，每次访问视图时会动态执行底层查询。

语法示例：

CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;

二、视图的核心作用

1. 简化复杂查询，提升可读性

封装复杂逻辑：将多表JOIN、子查询等复杂操作封装为视图，后续查询直接调用视图名。
示例：
原复杂查询：

SELECT e.name, d.dept_name 
FROM employee e JOIN department d ON e.dept_id = d.id 
WHERE d.location = '北京';

封装为视图：

CREATE VIEW beijing_employees AS
SELECT e.name, d.dept_name FROM employee e JOIN department d ON e.dept_id = d.id WHERE d.location = '北京';

后续查询只需：SELECT * FROM beijing_employees;

2. 数据安全与权限控制

限制数据访问范围：通过视图仅暴露部分字段或行，避免用户直接访问完整表。
场景：
- 给客服部门创建视图，仅包含客户姓名、电话（隐藏身份证号等敏感字段）；
- 通过条件过滤（如WHERE role='普通用户'）限制视图返回的数据行。

3. 屏蔽底层表结构变更

逻辑独立性：表结构变更时，只需修改视图定义，无需修改应用层查询语句。
示例：
表employee新增字段email，若视图未使用该字段，则应用层查询视图的语句无需修改。

4. 实现数据聚合与复用

预定义常用聚合：如按部门统计员工人数的视图，可重复用于不同报表查询。
```
CREATE VIEW dept_emp_count AS
SELECT dept_id, COUNT(*) AS emp_num FROM employee GROUP BY dept_id;
```

5. 支持跨表统一视角

合并多表数据：将多个表的相似字段整合到视图中，形成统一查询接口。
场景：
分库存储的订单数据（如按年份分表），可通过视图合并为统一的all_orders视图。

三、视图的分类与特性

1. 普通视图（Standard View）

动态查询：每次访问时执行底层SELECT语句，数据实时更新；
不可修改限制：大部分情况下无法通过视图直接修改底层表数据（仅当视图满足特定条件时可更新，如单表简单查询）。

2. 物化视图（Materialized View）

存储实际数据：定期或实时将查询结果存储到物理表中，适用于频繁查询且数据更新不频繁的场景；
应用场景：数据仓库中的报表统计，通过物化视图缓存聚合结果，减少重复计算。

3. 索引视图（Indexed View）

提升查询性能：对物化视图创建索引，加速复杂聚合查询（如SQL Server支持此特性）。

四、视图的局限性

性能依赖底层查询：若视图定义包含复杂JOIN或大表查询，访问视图可能仍存在性能瓶颈；
更新限制：多表关联视图通常不支持数据修改（INSERT/UPDATE/DELETE）；
不存储数据：无法像真实表一样进行磁盘级优化（如分区、压缩）。

五、最佳实践

高频复杂查询优先封装：如多表关联、多条件过滤的查询；
控制视图嵌套深度：避免多层视图嵌套（如视图A引用视图B），影响性能和可读性；
定期评估视图必要性：若底层表结构频繁变更，需及时更新视图定义。

通过视图，数据库可实现“逻辑建模”与“物理存储”的分离，在简化开发、增强安全性和提升复用性方面具有重要价值。

事务

事务（Transaction）是数据库管理系统（DBMS）中一组不可分割的操作集合，它作为一个逻辑单元执行，要么全部成功，要么全部失败。下面从定义、核心特性、作用及应用场景等方面详细说明：

一、事务的本质与定义

逻辑单元特性：将多个数据库操作（如查询、插入、更新、删除）捆绑为一个整体，确保数据一致性。

典型语法示例（以MySQL为例）：

START TRANSACTION;  -- 开始事务
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 扣款
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 收款
COMMIT;  -- 提交事务（所有操作生效）
-- 或 ROLLBACK;  -- 回滚事务（所有操作撤销）

二、事务的核心特性（ACID原则）

1. 原子性（Atomicity）

不可分割性：事务中的操作要么全部完成，要么全部不执行。
案例：转账操作中，若扣款成功但收款失败，事务会回滚至初始状态，避免资金丢失。

2. 一致性（Consistency）

数据合法性保障：事务执行前后，数据需符合预设的完整性约束（如主键唯一、字段非空）。
案例：订单创建时，若库存扣减成功但订单记录插入失败，事务回滚以确保库存与订单数据一致。

3. 隔离性（Isolation）

并发操作互不干扰：多个事务同时执行时，彼此的操作相互隔离，避免数据污染。
隔离级别（从低到高）：
- 读未提交（Read Uncommitted）：可能读取到其他事务未提交的数据（脏读）；
- 读已提交（Read Committed）：只能读取已提交的数据，但可能出现不可重复读；
- 可重复读（Repeatable Read）：同一事务内多次查询结果一致（MySQL默认级别）；
- 串行化（Serializable）：最高级别，通过锁实现事务串行执行，避免所有并发问题。

4. 持久性（Durability）

数据永久保存：事务提交后，其修改会持久化到数据库（即使系统崩溃也不丢失）。
实现机制：通过日志（如Redo Log）记录事务操作，确保故障恢复时数据可恢复。

三、事务的核心作用

1. 保障数据完整性

避免部分操作导致的数据不一致：如转账、订单支付等场景中，确保资金流与业务记录同步。
案例：电商下单时，同时扣减库存、生成订单记录，若其中一步失败，事务回滚以避免“有订单无库存”的情况。

2. 支持并发控制

通过隔离级别管理多事务冲突：防止并发操作导致的数据错误（如脏读、幻读）。
场景：多个用户同时修改同一商品库存时，事务隔离性确保库存扣减结果正确。

3. 简化错误处理

批量操作统一回滚：无需手动处理每个操作的失败情况，降低代码复杂度。
示例：批量导入数据时，若某条记录违反约束，整个事务回滚，避免脏数据入库。

4. 支持复杂业务逻辑

组合多操作实现原子性业务：如银行转账需同时操作两个账户，事务确保操作的整体性。
跨表操作一致性：如订单表与用户表的关联操作（更新用户积分的同时记录订单）。

四、事务的应用场景

1. 金融交易系统

银行转账、股票交易等场景，确保资金变动与账户记录一致。

2. 电商订单系统

下单时同时扣减库存、生成订单、记录用户积分，保证多表数据同步。

3. 票务/预约系统

购票时锁定座位、扣除余额、生成订单，避免超售问题。

4. 数据库迁移与批量操作

批量更新数据时，若中途出错可回滚，避免部分数据更新导致的不一致。

五、事务的实现与性能考量

1. 实现机制

锁（Lock）：在事务执行期间锁定操作的数据，防止其他事务修改；
日志（Log）：通过重做日志（Redo Log）和撤销日志（Undo Log）记录操作，确保持久性和回滚能力。

2. 性能影响

隔离级别越高，性能开销越大：如串行化级别会导致事务排队，影响并发效率；
大事务风险：长时间占用锁资源，可能导致其他事务阻塞，甚至引发死锁。

3. 优化建议

缩小事务范围：仅将必要操作放入事务，避免包含耗时逻辑（如文件读写、网络请求）；
合理选择隔离级别：根据业务场景权衡一致性与并发性能（如读多写少场景可使用读已提交）；
监控死锁与超时：通过数据库工具检测并处理事务冲突。

六、分布式事务（拓展）

跨数据库的事务问题：当操作涉及多个数据库（如微服务架构），需通过分布式事务解决方案（如2PC、TCC、SAGA）保证一致性。
典型场景：跨库转账、多服务数据联动更新。

事务是数据库实现数据可靠性的核心机制，通过ACID原则确保复杂操作的一致性，是金融、电商等关键业务系统的基础保障。合理使用事务可在数据安全与系统性能间取得平衡。

存储过程

存储过程（Stored Procedure）是数据库中一组预编译并存储在服务器上的SQL语句及控制逻辑的集合，它可以接受参数、执行操作并返回结果。下面从定义、核心特性、作用及应用场景等方面详细说明：

一、存储过程的本质与定义

预编译可重用模块：将常用SQL操作（如查询、更新、复杂逻辑处理）封装为独立模块，存储在数据库中，可被多次调用。

典型语法示例（以MySQL为例）：

-- 创建存储过程：计算用户订单总金额  
CREATE PROCEDURE calculate_order_total(IN user_id INT, OUT total_amount DECIMAL(10,2))  
BEGIN  
  SELECT SUM(amount) INTO total_amount FROM orders WHERE user_id = user_id;  
END;  

-- 调用存储过程  
CALL calculate_order_total(1001, @total);  
SELECT @total;  -- 输出结果

二、存储过程的核心特性

1. 参数化设计

输入参数（IN）：接收调用时传入的数据（如用户ID、查询条件）；
输出参数（OUT）：向调用方返回处理结果（如计算后的总金额）；
输入输出参数（INOUT）：既接收数据又返回修改后的值。

2. 流程控制能力

支持条件判断（IF、CASE）、循环（LOOP、WHILE、REPEAT）、异常处理等编程逻辑，可实现复杂业务逻辑。
示例：根据订单状态批量更新库存，若某条订单更新失败则跳过并记录日志。

3. 预编译与缓存

首次执行时编译为执行计划并缓存，后续调用直接使用缓存计划，减少SQL解析开销。

三、存储过程的核心作用

1. 提升查询性能与效率

减少客户端与数据库交互：将多次SQL请求封装为一个存储过程调用，降低网络传输开销。
示例：客户端需查询用户信息、订单列表、积分记录，若使用存储过程可一次性返回结果，避免三次独立查询。

2. 实现复杂业务逻辑

封装跨表操作与计算逻辑：如电商订单系统中，计算订单折扣、税费、运费并更新库存，可在存储过程中一次性处理。
避免业务代码与SQL耦合：将数据处理逻辑集中在数据库层，简化应用层代码。

3. 增强数据安全性

限制直接SQL访问：应用程序通过调用存储过程操作数据，避免用户直接执行危险SQL（如DELETE、DROP）。
细粒度权限控制：仅授予用户调用存储过程的权限，而非表的增删改查权限。

4. 提高代码可维护性

集中管理数据逻辑：当业务规则变更时，只需修改存储过程，无需调整多处应用代码。
复用性：多个应用或模块可调用同一存储过程，避免逻辑重复开发。

四、存储过程的应用场景

1. 复杂报表与统计计算

金融系统中生成月度财务报表，需聚合多表数据并进行复杂计算（如汇总、分组、排序）。

2. 事务性批量操作

电商库存扣减：同时更新商品库存表、订单表、流水表，并记录操作日志，确保原子性。

3. 权限控制与数据过滤

企业OA系统中，根据用户角色动态过滤数据（如部门经理只能查询本部门员工信息）。

4. 异构系统数据同步

跨数据库同步数据（如MySQL与Oracle之间的数据迁移），通过存储过程处理字段映射与转换。

五、存储过程的优缺点与性能考量

1. 优势

性能优化：预编译与缓存机制减少SQL解析时间，适合高频调用场景；
逻辑封装：复杂业务逻辑下沉至数据库层，降低应用层复杂度；
跨语言兼容：应用程序（Java、Python等）可通过标准接口调用，与编程语言解耦。

2. 局限性

移植性差：不同数据库（如MySQL、SQL Server、Oracle）的存储过程语法存在差异，迁移成本高；
调试困难：缺乏可视化调试工具，错误定位比应用层代码更复杂；
版本管理问题：存储过程更新需直接操作数据库，难以与应用代码版本同步。

3. 性能优化建议

避免大事务存储过程：长时间占用数据库连接或锁资源，影响并发；
拆分复杂逻辑：将单一存储过程拆分为多个小模块，提高可读性与执行效率；
合理使用索引：存储过程中涉及的查询需确保表已创建合适索引，避免全表扫描。

六、存储过程与其他数据库对象的对比

对象	存储过程	视图	函数
本质	可执行的逻辑代码块	虚拟表（查询结果的映射）	返回单一值的计算逻辑
参数支持	支持IN/OUT/INOUT参数	不支持参数	支持输入参数（无输出参数）
修改数据	可执行增删改查操作	仅支持有限的更新操作	一般不修改数据（只读）
应用场景	复杂业务逻辑、批量操作	简化查询、数据权限控制	单行计算（如字符串处理、数值计算）

七、存储过程的发展与替代方案

微服务架构下的争议：传统单体应用中存储过程可提升效率，但在微服务架构中，因数据库分库分表与跨服务调用，更倾向于将逻辑放在应用层（如使用MyBatis、Hibernate等ORM框架）；
替代方案：
- 应用层SQL封装：通过框架将SQL语句与业务逻辑结合，提升移植性；
- 存储过程即服务（SPaaS）：部分云数据库提供托管存储过程服务，简化跨数据库兼容问题。
  存储过程是数据库层实现业务逻辑的强大工具，尤其适合对性能、安全性要求高的场景（如金融、电信系统）。在使用时需权衡移植性与效率，根据系统架构选择合适的技术方案。

mysql和nosql的特点

MySQL和NoSQL是两类不同的数据库系统，它们在数据模型、适用场景、性能特性等方面存在显著差异。以下是详细对比：

一、核心区别概览

维度	MySQL（关系型数据库）	NoSQL（非关系型数据库）
数据模型	结构化数据，基于表、行、列的二维关系模型	非结构化或半结构化，支持键值、文档、图、列族等模型
查询语言	SQL（结构化查询语言）	无统一查询语言，各数据库自定义API（如MongoDB的聚合框架）
事务支持	支持完整ACID事务（如转账操作）	部分支持弱事务（如MongoDB的多文档事务）或不支持事务
水平扩展能力	传统架构难扩展，需分库分表；NewSQL架构（如TiDB）支持水平扩展	天然支持分布式集群，通过分片（Sharding）轻松扩展
数据一致性	强一致性（所有操作立即可见）	支持多种一致性模型（强一致性、弱一致性、最终一致性）
适用场景	关系复杂、事务要求高的场景（如金融、电商订单）	高并发读写、海量数据存储、灵活架构（如社交网络、日志分析）

二、数据模型对比

1. MySQL（关系型）

结构化设计：数据按表结构存储，需预先定义字段类型和关系（如主键、外键）。
示例：
```
CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);
```
关系约束：通过JOIN操作关联多表数据，支持复杂查询。

2. NoSQL（以MongoDB为例）

文档模型：数据以JSON/BJSON格式存储，无需预定义结构，支持嵌套文档。
示例：

{
  "_id": "123",
  "name": "张三",
  "age": 25,
  "address": {
    "city": "北京",
    "zip": "100000"
  },
  "hobbies": ["阅读", "跑步"]
}

灵活 schema：同一集合中的文档可拥有不同字段，适合快速迭代的场景。

三、性能与扩展性对比

1. MySQL

垂直扩展为主：通过增加服务器配置（CPU、内存、磁盘）提升性能，但存在硬件瓶颈。
水平扩展挑战：分库分表需应用层手动实现，运维复杂度高；NewSQL（如TiDB）虽支持自动分片，但仍保留SQL特性。

2. NoSQL（以MongoDB/Cassandra为例）

水平扩展天然支持：
- 分片（Sharding）：自动将数据分散到多个节点，轻松应对PB级数据；
- 复制集（Replica Set）：通过多副本提高可用性和读性能。
高并发读写优化：
- 基于内存的存储引擎（如Redis）支持百万级QPS；
- 分布式架构减少单点压力。

四、一致性与事务对比

1. MySQL

强一致性：所有事务遵循ACID原则，确保数据始终一致。
示例：

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

2. NoSQL

多样一致性模型：
- 强一致性：如MongoDB的多文档事务（4.0+版本）；
- 最终一致性：如Cassandra，写入后允许短暂不一致，但最终达到一致状态；
- 弱一致性：如Redis，适用于缓存场景，不保证数据实时一致。
适用场景：对一致性要求不高的场景（如社交动态、统计数据）可容忍短暂不一致。

五、典型适用场景

1. MySQL适用场景

金融交易系统（如银行转账、支付）；
电商订单管理（需保证订单、库存、支付的一致性）；
企业资源规划（ERP）系统（复杂业务逻辑和报表）。

2. NoSQL适用场景

键值存储（Redis）：缓存、计数器、实时排行榜；
文档存储（MongoDB）：内容管理系统、用户画像；
列族存储（Cassandra）：日志分析、物联网海量数据；
图数据库（Neo4j）：社交网络关系分析、知识图谱。

六、选型建议

场景需求	优先选择MySQL	优先选择NoSQL
数据结构固定且关系复杂	✅	❌
高并发读写且数据模型灵活	❌	✅
强事务一致性要求	✅	❌（部分支持弱事务）
海量数据存储与水平扩展	❌（需分库分表）	✅
快速迭代开发	❌（需频繁修改表结构）	✅（灵活schema）

七、混合架构趋势

现代应用常采用混合架构，结合两者优势：

MySQL：存储核心业务数据（如用户信息、订单）；
Redis：缓存高频访问数据（如热门商品）；
MongoDB：存储非结构化数据（如用户行为日志）；
Elasticsearch：提供全文搜索能力。

例如，电商系统中：

订单数据用MySQL保证事务性；
商品浏览记录用MongoDB存储；
实时热搜榜用Redis缓存；
商品搜索用Elasticsearch实现。

总结

MySQL：适合对数据一致性、事务性要求高的结构化数据场景，是传统企业应用的首选。
NoSQL：在高并发、海量数据、灵活架构场景下表现优异，是互联网、大数据领域的主流选择。
选型关键：根据业务场景权衡一致性、扩展性、开发效率，必要时采用混合架构。

点击查看代码

posted @ 2025-05-23 20:27 莫西无邪阅读(18) 评论(0) 收藏举报

刷新页面返回顶部