数据库基础--MySQL

已于 2025-04-01 18:36:17 修改
阅读量988 收藏 18
点赞数 38
文章标签： 数据库 mysql
于 2025-04-01 18:35:15 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45675311/article/details/146910768
版权

核心元素

  • 数据库
  • 表
  • 行（记录）
  • 列（字段）
  • 主键 （用于标识表中的每条记录，值不能重复，不能为NULL）
  • 外键（表中的一个字段，指向另外一个表的主键，用于建立两个表之间的关联关系）

数据类型

数值

  • 整数
数据类型描述
INT4字节，有符号的 32 位整数
BIGINT8字节，有符号的 64 位整数
  • 浮点数
数据类型描述
FLOAT4字节，单精度浮点数
DOUBLE8字节，双精度浮点数

字符

数据类型描述
VARCHAR可变长度字符类型，长度可指定范围为 1 到 65535 个字符
CHAR固定长度字符类型，最大长度为 255 个字符
TEXT存储大量文本数据

日期时间

数据类型描述
DATE存储日期，格式为“YYYY - MM - DD”
TIME存储时间，格式为“HH:MM:SS”
DATETIME存储日期和时间，格式为“YYYY - MM - DD HH:MM:SS”
TIMESTAMP时间戳类型，用于记录数据的创建或修改时间等（创建or修改记录的时候会自动填写，格式是DATETIME）
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

created_at 字段在创建记录时自动赋值为当前时间戳。
updated_at 字段在创建记录时也会自动赋值为当前时间戳，并且在每次更新记录时自动更新为当前时间戳。

基本操作

数据库

增 CREATE DATABASE

CREATE DATABASE test;

删 DROP DATABASE

DROP DATABASE test01;

改

如果需要重命名数据库，需要：
1、创建新数据库
2、导出原数据库

mysqldump -u username -p old_database_name > old_database_backup.sql

3、导入数据到新数据库

mysql -u username -p new_database_name < old_database_backup.sql

4、删除原数据库

查 SHOW DATABASES

SHOW DATABASES;

表

增 CREATE TABLE

CREATE TABLE user(
	id INT AUTO_INCREMENT PRIMARY KEY,
	username VARCHAR(50) NOT NULL,
	email VARCHAR(100) UNIQUE,
	update_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
# AUTO_INCREMENT  自增
# PRIMARY KEY 主键
# UNIQUE 唯一

删 DROP TABLE

DROP TABLE table_name;

改 ALTER TABLE

增加列

ALTER TABLE table_name
ADD COLUMN column_name column_type;

删除列

ALTER TABLE table_name
DROP COLUMN column_name;

修改列

ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_column_type;

查 SHOW TABLES

查看指定表的结构

DESCRIBE tabel_name; # 需要先执行 USE database_name; 切换到数据库目录下

查看有哪些表

SHOW TABLES FROM database_name; # 查看指定数据库中的表
SHOW TABLES;# 查看当前数据库的表

数据

增 INSERT INTO

INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);
INSERT INTO students (name, age, gender) VALUES ('David', 23, 'M');

删 DELETE FROM

DELETE FROM 表名 WHERE 条件;
DELETE FROM students WHERE id = 3;

改 UPDATE SET

UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2, ... WHERE 条件;
UPDATE students SET age = 23 WHERE id = 2;
UPDATE students SET gender = 'U';

查 SELECT FROM

SELECT 列名1, 列名2, ... FROM 表名 WHERE 条件;
SELECT * FROM students WHERE age > 20;

WHERE 过滤

比较运算
比较运算符用于比较两个值，常用的比较运算符包括：=、<>（或 !=）、>、<、>=、<=。

SELECT * FROM employees WHERE age = 28;

逻辑运算
逻辑运算符用于组合多个条件，常用的逻辑运算符包括：AND、OR、NOT。

SELECT * FROM employees WHERE age > 25 AND salary > 5500;

模糊查询
模糊查询使用 LIKE 运算符，结合通配符 % 和 _ 来匹配字符串。其中，%匹配任意数量单位，匹配一个单位。

SELECT * FROM employees WHERE name LIKE 'A%';
SELECT * FROM employees WHERE name LIKE '_a%';

范围查询

SELECT * FROM employees WHERE age BETWEEN 25 AND 30;

空判断

SELECT * FROM employees WHERE age is null;

ORDER BY 排序

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
ORDER BY 列名1 [ASC|DESC], 列名2 [ASC|DESC], ...;

SELECT * FROM employees ORDER BY age ASC;

ASC升序
DESC降序

GROUP BY 分组

GROUP BY 子句用于将查询结果按指定列的值分组，每个组包含具有相同列值的行。GROUP BY 通常与聚合函数（如
COUNT、SUM、AVG 等）一起使用，以对每个分组进行计算和汇总。

SELECT 列名1, 列名2, ..., 聚合函数(列名)
FROM 表名
WHERE 条件
GROUP BY 列名1, 列名2, ...;

# 查询每个部门的员工数量
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;
# 先按照department进行分组 然后选择组并统计

# 多列分组
# 查询每个部门中每个年龄的员工数量
SELECT department, age, COUNT(*) AS employee_count
FROM employees
GROUP BY department, age;

HAVING
HAVING 子句用于对分组后的结果进行过滤，类似于 WHERE 子句，但 HAVING 是针对分组后的聚合结果进行过滤。

# 查询员工数量大于 1 的部门
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 1;

聚合函数

COUNT 统计行数

SELECT COUNT(*) FROM employees;

SUM 计算数值列的总和

SELECT SUM(salary) FROM employees;

MAX 返回数值列中的最大值

SELECT MAX(salary) FROM employees;

MIN 返回数值列中的最小值

SELECT MIN(salary) FROM employees;

AVG 计算数值列的平均值

SELECT AVG(salary) FROM employees;

LIMIT 限制

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
LIMIT [offset,] row_count;

offset：指定从哪一行开始返回数据，从 0 开始计数。
row_count：指定返回的最大行数。

# 返回3行
SELECT * FROM employees LIMIT 3;
# 从第3行开始，返回10行
SELECT * FROM employees LIMIT 3,10;

连接查询

INNER JOIN 内连接

内连接返回两个表中满足连接条件的匹配行，即两个表的交集部分

SELECT 列名1, 列名2, ...
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;

三表相连

SELECT employees.name, departments.department_name, locations.location_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.location_id = locations.id;

OUTER JOIN 外连接

外连接可以返回一个表中的所有记录，即使在另一个表中没有匹配的记录，也会以 NULL 填充

LEFT JOIN 左连接

保证LEFT JOIN 左边的那个表项要全部列出

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;
RIGHT JOIN 右连接

保证RIGHT JOIN 右边的那个表项要全部列出

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

CROSS JOIN 交叉连接

交叉连接返回两个表的笛卡尔积，即第一个表中的每一行与第二个表中的每一行组合，它不依赖于任何连接条件，因此不需要 ON 子句。
如果 表1 有 m 行，表2 有 n 行，那么交叉连接的结果将有 m×n 行

SELECT 列名1, 列名2, ...
FROM 表1
CROSS JOIN 表2;

NATURAL JOIN 自然连接

自然连接（NATURAL JOIN）可以看作是一种自动根据同名列进行连接的内连接（INNER JOIN），并且在结果集中自动去除了重复的同名列。
如果两个表中没有同名列，NATURAL JOIN 会生成笛卡尔积（类似于 CROSS JOIN）

导入导出

导入sql文件生成数据库

mysql -u 用户名 -p 数据库名 < 文件名.sql

SELECT 列名1, 列名2, ...
FROM 表1
NATURAL JOIN 表2;

导出当前数据库为sql文件

mysqldump -u 用户名 -p 数据库名 > 文件名.sql

事务

事务是一组操作的集合，要么全部成功，要么全部失败，不能半途而废。
在 MySQL 中，默认情况下，事务是自动提交的（autocommit）。也就是说，每执行一条 SQL 语句，MySQL 就会自动将其作为一个单独的事务提交。

事务的四大特性（ACID）

原子性（Atomicity）：事务是一个不可分割的整体，要么全部成功，要么全部失败，不会出现部分完成的情况。
一致性（Consistency）：事务执行前后，数据库的状态必须保持一致。比如转账前后，总金额不能凭空多出来或少掉。
隔离性（Isolation）：多个事务同时执行时，互相之间不能干扰。比如你和别人同时转账，不能出现钱被重复扣或者重复加的情况。
持久性（Durability）：一旦事务成功提交，它的结果就会永久保存到数据库中，即使系统崩溃也不会丢失。

事务隔离级别

MySQL 支持不同的事务隔离级别，这些级别决定了一个事务可以看到其他事务所做的更改的程度。隔离级别越高，事务之间的干扰越少，但性能可能会有所下降从低到高分别为：

READ UNCOMMITTED（未提交读，最低级）
特点：一个事务可以看到其他事务尚未提交的数据。

问题：可能会读到不准确的数据，称为“脏读”（Dirty Read）。
例子：
-小明拿了 3 个苹果放进购物篮，但还没结账（事务没提交）。
-你一看库存，发现只剩 2 个苹果，就觉得只能买 2 个。
-结果小明突然改变主意，把苹果放回去了（事务回滚）。
-你看到的数据是“脏数据”，因为你基于小明没提交的操作做了决定。

READ COMMITTED（提交读）
特点：一个事务只能看到其他事务已经提交的数据。
问题：同一个事务内，读到的数据可能变来变去，称为“不可重复读”（Non-repeatable Read）。
例子：
-小明拿了 3 个苹果，还没结账时，你查库存，看到的还是 5 个（因为他没提交）。
-小明结账后（提交事务），你再查，看到只剩 2 个。
-但如果你在这期间查了两次，第一次是 5 个，第二次是 2 个，结果不一致。

REPEATABLE READ（可重复读，默认级别）
特点：在同一个事务中，多次读取同一数据时，结果是一致的。其他事务不能修改你正在读取的数据。
问题：虽然避免了“不可重复读”，但可能会出现“幻读”，即你看到的数据在事务提交时可能被其他事务修改了。
例子：
-你开始事务时查了库存，看到 5 个苹果。
-小明在这期间拿走 3 个并结账（提交了）。
-你再次查库存，在你的事务结束前，依然看到 5 个（因为事务开始时的“快照”没变）。
-等你提交事务后，再查才会看到 2 个。

SERIALIZABLE（可串行化）
特点：事务完全隔离，一次只允许一个事务执行，就像排队一样，一个事务完成了，下一个事务才能开始。
问题：虽然最安全，但性能最低，因为事务需要依次排队执行，不能并行处理。
例子：
-你在查库存和买苹果时，小明只能等着。
-你买完 2 个苹果提交事务后，小明才能开始查库存和买。
-这样完全不会有干扰，但效率很低，就像超市只有一个收银台，所有人排队结账。

手动控制事务

1、关闭自动提交

SET autocommit = 0;

2、开启事务

START TRANSACTION;

3、执行一系列操作

INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 2000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

4、提交事务

COMMIT;

如果在执行过程中发现任何问题，可以回滚事务

ROLLBACK;

5、重新开启自动提交（可选）

SET autocommit = 1;

索引

在 MySQL 中，索引是数据库用来加速查询的一种数据结构。索引（Index）就像是书的目录。想象你有一本很厚的书，没有目录，想找某一页内容，你得一页一页翻（全表扫描）。但如果有目录，你直接查目录就能快速找到页码（索引查询）。

索引原理

MySQL 的索引通常用 B+树。当你在表上创建索引时，数据库会根据索引列的值构建一棵树。当你查询数据时，数据库会通过这棵树快速找到需要的数据，而不用扫描整个表。

索引的好处和代价

好处：

  • 加快查询速度：尤其是 WHERE、ORDER BY、JOIN 等操作。
  • 提高效率：大数据量时效果更明显。

代价：

  • 占用空间：索引本身是个额外的数据结构，像书的目录也得占几页纸。
  • 减慢写操作：每次插入、更新、删除数据时，索引也要跟着更新。

所以，索引不是越多越好，要用在经常查询的列上。一般用在：经常查的列、唯一性强的列。

索引的类型

主键索引（Primary Key）：唯一且不为空，比如学生的学号。
唯一索引（Unique Index）：值不能重复，但可以为空，比如身份证号。
普通索引（Index）：没有唯一性限制，比如书名。
全文索引（Full-Text Index）：用于搜索大段文字，比如文章内容。
复合索引（Composite Index）：多个列组合成的索引，比如“姓名+年龄”。

创建索引

CREATE INDEX idx_age ON students(age);

创建复合索引

CREATE INDEX idx_age_class ON students(age, class);

数据库会先按“年龄”排序，再按“班级”细分，查询效率更高。
注意：复合索引有顺序，age 在前，class 在后。如果只查 class，这个索引可能没用。

检查索引效果

EXPLAIN SELECT * FROM students WHERE age = 15;

如果结果里显示 key: idx_age，说明用上了索引。

视图

视图就像是数据库里的一面“虚拟镜子”。它不是一个真实的表，而是基于一个或多个真实表（基表）的查询结果。你可以把它想象成一个“快捷方式”或“过滤器”，方便你查看数据。

特点

虚拟表：视图本身不存储数据，只是显示查询的结果。
简化查询：把复杂的查询封装起来，方便复用。（方便给别人看）
安全性：可以限制用户只看到部分数据。

操作

创建视图

CREATE VIEW view_class_a AS
SELECT * FROM students WHERE class = '初一A';

查看视图

SELECT * FROM view_class_a;

视图修改
如果视图基于单个表且没有复杂的计算（比如聚合函数），可以直接更新（改视图的数据会改变原表的数据）：

UPDATE view_class_a SET age = 16 WHERE id = 1;

但如果是多表联合或有 GROUP BY 的视图，通常只能读，不能改。

触发器

触发器是一个“自动执行的程序”，在特定事件发生时触发。比如表里数据被插入、更新或删除时，自动做一些操作。

特点

自动触发：不需要手动调用。
事件绑定：可以绑定在插入（INSERT）、更新（UPDATE）、删除（DELETE）上。
用途：数据校验、日志记录、联动更新。

操作

创建trigger

CREATE TRIGGER log_new_student
AFTER INSERT ON students
FOR EACH ROW
INSERT INTO logs (operation_time, student_name)
VALUES (NOW(), NEW.name);
# AFTER INSERT：在插入学生表后触发。
# FOR EACH ROW：对每插入一行执行一次。
# NEW.name：新插入行的“姓名”列值。

触发器的类型
BEFORE：在操作前触发，比如检查数据合法性。
AFTER：在操作后触发，比如记录日志。
支持的事件：INSERT、UPDATE、DELETE。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值