MySql数据库

最新推荐文章于 2024-09-08 10:40:56 发布

҉人҉字҉拖҉

最新推荐文章于 2024-09-08 10:40:56 发布

阅读量551

点赞数 30

文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_43684842/article/details/139607060

版权

数据库

在MySQL中，增（INSERT）、删（DELETE）、改（UPDATE）和查（SELECT）是四种基本的数据库操作。下面我们将详细介绍这些操作，并提供相应的示例代码。

1. 插入数据（INSERT）

插入数据是将新记录添加到表中。

示例

假设我们有一个名为students的表，表结构如下：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    grade VARCHAR(10)
);

我们可以使用INSERT语句将数据插入到该表中。

INSERT INTO students (name, age, grade) VALUES ('John Doe', 20, 'A');

这条语句会将一条新记录插入到students表中，name为’John Doe’，age为20，grade为’A’。

2. 查询数据（SELECT）

查询数据是从表中检索记录。

示例

查询所有学生的信息：

SELECT * FROM students;

查询特定学生的信息：

SELECT name, age FROM students WHERE grade = 'A';

这条语句会检索students表中所有grade为’A’的学生的name和age。

3. 更新数据（UPDATE）

更新数据是修改表中现有记录的值。

示例

将学生John Doe的年龄更新为21：

UPDATE students SET age = 21 WHERE name = 'John Doe';

这条语句会将students表中name为’John Doe’的记录的age更新为21。

4. 删除数据（DELETE）

删除数据是从表中移除记录。

示例

删除name为’John Doe’的学生记录：

DELETE FROM students WHERE name = 'John Doe';

这条语句会删除students表中name为’John Doe’的记录。

综合示例

假设我们需要进行一系列操作：插入新学生、查询所有学生、更新某个学生的成绩、然后删除某个学生。以下是这些操作的综合示例：

-- 插入新学生
INSERT INTO students (name, age, grade) VALUES ('Jane Smith', 22, 'B');

-- 查询所有学生
SELECT * FROM students;

-- 更新Jane Smith的成绩为A
UPDATE students SET grade = 'A' WHERE name = 'Jane Smith';

-- 删除name为Jane Smith的学生记录
DELETE FROM students WHERE name = 'Jane Smith';

通过这些示例，可以看出MySQL中增删改查操作的基本使用方法。在实际开发中，这些操作可以组合使用，以满足各种数据处理需求。

5.多表查询

多表查询是指在一个查询中从多个表中检索数据。MySQL 支持多种类型的多表查询，例如使用 JOIN 连接表，或者使用子查询。下面是一些常用的多表查询示例。

1. 内连接（INNER JOIN）

内连接返回两个表中匹配的记录。

示例

假设我们有两张表：students 和 courses，它们的结构如下：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

查询每个学生及其所选课程的信息：

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

2. 左连接（LEFT JOIN）

左连接返回左表中的所有记录以及右表中匹配的记录。如果左表中的记录在右表中没有匹配，则结果为 NULL。

示例

查询每个学生及其所选课程的信息，如果学生没有选课，课程名称为 NULL：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id;

3. 右连接（RIGHT JOIN）

右连接返回右表中的所有记录以及左表中匹配的记录。如果右表中的记录在左表中没有匹配，则结果为 NULL。

示例

查询每个课程及其对应的学生信息，如果某课程没有学生选，则学生名称为 NULL：

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

4. 全连接（FULL JOIN）

MySQL 本身不支持全连接（FULL JOIN），可以通过使用 UNION 将左连接和右连接的结果组合起来。

示例

查询每个学生及其所选课程的信息，包括没有选课的学生和没有学生选的课程：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses ON students.id = courses.student_id
UNION
SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses ON students.id = courses.student_id;

5. 交叉连接（CROSS JOIN）

交叉连接返回两个表的笛卡尔积，即每个表的所有记录的组合。

示例

查询所有学生和所有课程的所有可能组合：

SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

6. 子查询

子查询是一个查询嵌套在另一个查询中，可以用于在多表查询中获取所需数据。

示例

查询所有选修了"Math"课程的学生姓名：

SELECT name
FROM students
WHERE id IN (SELECT student_id FROM courses WHERE course_name = 'Math');

这些示例展示了 MySQL 中多表查询的基本用法。实际开发中，可以根据需求选择合适的查询类型，并使用适当的连接条件和筛选条件来获取所需的数据。

6.视图

视图（View）是一个虚拟表，表示基于 SQL 查询的结果集。视图不存储实际数据，它动态地从基础表中生成数据。视图在 MySQL 中可以用于简化复杂查询、提高安全性和数据的抽象。

创建视图

创建视图使用 CREATE VIEW 语句。下面是一个简单的示例。

示例

假设我们有两张表：students 和 courses，它们的结构如下：

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

CREATE TABLE courses (
    student_id INT,
    course_name VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

我们希望创建一个视图，显示每个学生及其所选课程的信息。

CREATE VIEW student_courses AS
SELECT students.id, students.name, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

使用视图

创建视图后，可以像查询普通表一样查询视图。

SELECT * FROM student_courses;

修改视图

可以使用 ALTER VIEW 语句来修改视图。假设我们想在视图中添加学生年龄：

ALTER VIEW student_courses AS
SELECT students.id, students.name, students.age, courses.course_name
FROM students
INNER JOIN courses ON students.id = courses.student_id;

删除视图

使用 DROP VIEW 语句删除视图。

DROP VIEW student_courses;

视图的优点

简化复杂查询：通过创建视图，可以将复杂的 SQL 查询简化为简单的 SELECT 语句。
数据安全：视图可以限制用户对基础表的访问，只显示必要的数据。
数据抽象：视图可以隐藏表结构的复杂性，提供统一的数据接口。

示例：视图的实际应用

假设我们有一个公司员工数据库，包含员工信息和部门信息两张表：

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    emp_salary DECIMAL(10, 2),
    dept_id INT
);

CREATE TABLE departments (
    dept_id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100)
);

我们希望创建一个视图，显示每个员工的姓名、工资和部门名称。

CREATE VIEW employee_details AS
SELECT employees.emp_id, employees.emp_name, employees.emp_salary, departments.dept_name
FROM employees
INNER JOIN departments ON employees.dept_id = departments.dept_id;

查询视图以获取员工详细信息：

SELECT * FROM employee_details;

示例数据插入和查询

插入一些示例数据：

INSERT INTO departments (dept_name) VALUES ('HR'), ('Engineering'), ('Marketing');

INSERT INTO employees (emp_name, emp_salary, dept_id) VALUES
('Alice', 70000, 1),
('Bob', 80000, 2),
('Charlie', 75000, 3);

查询视图：

SELECT * FROM employee_details;

输出结果可能如下：

+--------+----------+------------+-------------+
| emp_id | emp_name | emp_salary | dept_name   |
+--------+----------+------------+-------------+
|      1 | Alice    | 70000.00   | HR          |
|      2 | Bob      | 80000.00   | Engineering |
|      3 | Charlie  | 75000.00   | Marketing   |
+--------+----------+------------+-------------+

通过这些示例，我们可以看到视图如何帮助简化查询并提高数据的可读性和安全性。

7.事务

在 MySQL 中，事务（Transaction）是一组逻辑操作单元，这些操作要么全部执行，要么全部不执行。事务的主要特性是 ACID 属性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

事务的基本操作

BEGIN 或 START TRANSACTION：开始一个事务。
COMMIT：提交事务，将所有事务操作的结果持久化到数据库。
ROLLBACK：回滚事务，撤销所有事务操作。

示例

假设我们有两个账户的表 accounts，我们需要从一个账户转账到另一个账户。这时就需要用到事务，确保转账操作的原子性。

表结构

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(10, 2)
);

插入示例数据

INSERT INTO accounts (account_id, account_name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 1000.00);

示例事务：从 Alice 转账 200 给 Bob

-- 开始事务
START TRANSACTION;

-- 从 Alice 的账户扣除 200
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- 向 Bob 的账户增加 200
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

-- 检查账户余额，确保 Alice 的余额不会变成负数
-- 这个步骤是可选的，根据业务规则添加

-- 如果以上操作都成功，提交事务
COMMIT;

-- 如果有任何错误，回滚事务
ROLLBACK;

事务控制的示例

假设在转账过程中，我们发现 Alice 的余额不足，这时我们需要回滚事务。

-- 开始事务
START TRANSACTION;

-- 从 Alice 的账户扣除 1200
UPDATE accounts SET balance = balance - 1200 WHERE account_id = 1;

-- 向 Bob 的账户增加 1200
UPDATE accounts SET balance = balance + 1200 WHERE account_id = 2;

-- 检查 Alice 的账户余额
SELECT balance FROM accounts WHERE account_id = 1;

-- 如果余额不足，则回滚事务
-- 这里假设我们有个检查逻辑发现余额不足
ROLLBACK;

-- 如果余额充足，则提交事务
-- COMMIT;

事务隔离级别

MySQL 支持以下四种事务隔离级别：

READ UNCOMMITTED：允许读取未提交的数据（脏读）。
READ COMMITTED：只允许读取已提交的数据。
REPEATABLE READ：同一事务中多次读取的结果是一样的（MySQL 默认）。
SERIALIZABLE：最高隔离级别，完全串行化的读，防止幻读。

设置隔离级别：

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

8.约束

在 MySQL 中，约束（Constraint）用于在表中限制数据的类型，以确保数据的准确性和可靠性。常见的约束类型包括主键约束、唯一约束、外键约束、检查约束和非空约束。

约束类型及示例

主键约束（PRIMARY KEY）

确保每行记录有一个唯一标识符，且不能为空。

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

唯一约束（UNIQUE）

确保列中的所有值都是唯一的。

CREATE TABLE employees (
    employee_id INT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100)
);

外键约束（FOREIGN KEY）

确保一个表中的值必须在另一个表中存在，从而维护表之间的关系。

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

检查约束（CHECK）

确保列中的值满足指定的条件。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2) CHECK (price > 0)
);

非空约束（NOT NULL）

确保列不能包含 NULL 值。

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL
);

综合示例

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18),
    city_id INT,
    FOREIGN KEY (city_id) REFERENCES cities(city_id)
);

CREATE TABLE cities (
    city_id INT PRIMARY KEY AUTO_INCREMENT,
    city_name VARCHAR(100) NOT NULL
);

在这个综合示例中：

customer_id 是主键，唯一且自动递增。
name 列设置了非空约束。
email 列设置了唯一约束，确保每个客户的电子邮件地址是唯一的。
age 列设置了检查约束，确保客户年龄必须大于或等于 18。
city_id 是一个外键，确保引用的城市 ID 必须存在于 cities 表中。

修改表添加约束

如果你需要在已经存在的表上添加约束，可以使用 ALTER TABLE 语句。

-- 添加唯一约束
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

-- 添加检查约束
ALTER TABLE products ADD CONSTRAINT check_price CHECK (price > 0);

-- 添加外键约束
ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);

删除约束

如果你需要删除已经存在的约束，可以使用 ALTER TABLE 语句。

-- 删除唯一约束
ALTER TABLE employees DROP INDEX unique_email;

-- 删除检查约束
ALTER TABLE products DROP CONSTRAINT check_price;

-- 删除外键约束
ALTER TABLE employees DROP FOREIGN KEY fk_department;

通过使用这些约束，MySQL 可以帮助确保数据的一致性和完整性，防止无效数据的插入。

9.MySql的索引

MySQL 选择使用 B+ 树而不是 B 树作为其索引结构，主要是因为 B+ 树在数据库应用中的一些关键性能方面优于 B 树。以下是具体原因和比较：

1. 树的高度和节点存储

B 树：
- 每个节点都包含键和值（数据）。
- 数据和指针混合存储在节点中。
- 由于数据和指针都存储在节点中，因此一个节点能存储的键较少，树的高度可能会更高。
B+ 树：
- 只有叶子节点包含值（数据），内部节点只存储键。
- 所有的实际数据都存储在叶子节点，内部节点只用于导航。
- 由于内部节点存储更多的键（没有数据部分），树的高度通常较低，减少了磁盘 I/O 操作。

2. 顺序访问和范围查询效率

B 树：
- 顺序访问不方便，需要在树中进行中序遍历才能获得顺序数据。
- 范围查询效率较低。
B+ 树：
- 叶子节点通过链表相连，天然支持顺序访问。
- 范围查询时只需要在叶子节点链表上顺序扫描即可，非常高效。

3. 磁盘读写效率

B 树：
- 数据分散在所有节点上，读写数据时需要更多的随机访问。
- 内部节点和叶子节点都有数据，导致缓存命中率较低。
B+ 树：
- 内部节点较小，可以存储更多的键，减少树的高度。
- 叶子节点集中存储数据，可以更有效地利用磁盘预读特性。
- 内部节点可以被更高效地缓存，从而减少磁盘 I/O 次数。

4. 稳定性和维护

B 树：
- 由于所有节点都存储数据，节点的插入和删除操作会导致更多的节点分裂和合并，维护成本较高。
B+ 树：
- 内部节点只存储键而不存储数据，插入和删除操作只影响叶子节点。
- 内部节点的稳定性较高，树的结构更稳定，维护成本更低。

总结

综上所述，MySQL 选择 B+ 树作为索引结构是因为其更低的树高度、更高的顺序访问和范围查询效率、更高的磁盘读写效率以及更稳定的维护成本。这些优点使得 B+ 树在数据库索引中表现得更为出色，特别是在处理大量数据和频繁查询的场景下。

҉人҉字҉拖҉

关注

30
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库

综上所述，MySQL 选择 B+ 树作为索引结构是因为其更低的树高度、更高的顺序访问和范围查询效率、更高的磁盘读写效率以及更稳定的维护成本。这些优点使得 B+ 树在数据库索引中表现得更为出色，特别是在处理大量数据和频繁查询的场景下。
复制链接

扫一扫