数据库
在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+ 树在数据库索引中表现得更为出色,特别是在处理大量数据和频繁查询的场景下。