MySQL第十章，课程总复习

这一章节，主要要掌握这些

掌握根据需求设计规范数据库方法

掌握安装配置mysql数据库方法

掌握使用sql语句创建数据库和数据表的方法

掌握使用sql语句对数据进行增删改查的操作的方法

掌握使用存储过程的封装sql的能力掌握使用事务保证操纵数据的完整性的能力

具备使用索引提高查询效率的能力

掌握使用的视图提高数据安全性的方法

掌握备份和回复数据库的方法

掌握创建数据库用户授予相应权限的方法

熟练掌握在java程序中使用jdbc操作数据库的方法

熟练掌握使用dao模式封装数据访问代码的方法

一、掌握根据需求设计规范数据库的方法

理解业务需求：
- 明确数据库需要存储哪些数据，以及这些数据之间的关系。
- 确定数据的访问模式，包括查询、更新、删除等操作的频率和方式。
设计数据库结构：
- 根据业务需求，设计数据库中的表、字段、数据类型、主键、外键等。
- 遵循数据库设计规范，如避免冗余、保持数据一致性等。
优化数据库性能：
- 设计合适的索引，以提高查询效率。
- 考虑分库分表、分区表等策略，以应对大规模数据。

示例：

-- 创建一个简单的学生管理系统数据库
CREATE DATABASE student_management;USE student_management;-- 创建学生表
CREATE TABLE Student (student_id INT PRIMARY KEY,  -- 学号，主键name VARCHAR(50),            -- 姓名gender CHAR(1),              -- 性别 ('M' 或 'F')age INT,                     -- 年龄create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 更新时间
);-- 创建课程表
CREATE TABLE Course (course_id INT PRIMARY KEY,  -- 课程号，主键course_name VARCHAR(100),   -- 课程名称credits INT                -- 学分
);-- 创建选课记录表
CREATE TABLE Enrollment (student_id INT,course_id INT,grade CHAR(2),PRIMARY KEY(student_id, course_id),  -- 联合主键FOREIGN KEY(student_id) REFERENCES Student(student_id),  -- 外键，关联到 Student 表FOREIGN KEY(course_id) REFERENCES Course(course_id)  -- 外键，关联到 Course 表
);

二、掌握安装配置MySQL数据库的方法

下载MySQL安装包：
- 从MySQL官方网站（如MySQL :: MySQL Community Downloads）下载最新版本的MySQL安装包。
安装MySQL：
- 运行安装包，按照提示完成安装过程。
- 在安装过程中，可以选择要安装的组件，如MySQL Server、MySQL Workbench等。
配置MySQL：
- 在安装完成后，进行MySQL的配置，包括设置密码、选择字符集、配置端口等。
- 可以使用MySQL Workbench等图形化工具进行配置，也可以通过编辑MySQL的配置文件（如my.ini）进行配置。

示例：

（以Windows系统为例）

下载并运行MySQL安装包。
选择要安装的组件，如MySQL Server和MySQL Workbench。
在配置过程中，设置MySQL的root密码，选择字符集为utf8mb4，配置端口为3306。
安装完成后，使用MySQL Workbench连接到MySQL服务器，进行进一步的配置和管理。

三、掌握使用SQL语句创建数据库和数据表的方法

创建数据库：
```
CREATE DATABASE database_name;
```
使用数据库：
```
USE database_name;
```

创建数据表：

CREATE TABLE table_name (column1 datatype constraint,column2 datatype constraint,...PRIMARY KEY (primary_key_column),FOREIGN KEY (foreign_key_column) REFERENCES other_table (other_column)
);

示例：

（见上文“一、掌握根据需求设计规范数据库的方法”中的示例）

四、掌握使用SQL语句对数据进行增删改查的操作的方法

1.插入数据：

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

2.修改数据：

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

3.删除数据：

DELETE FROM table_name WHERE condition;

4.查询数据：

SELECT column1, column2, ... FROM table_name WHERE condition;

示例：

-- 向Student表插入数据
INSERT INTO Student (student_id, name, gender, age) VALUES (1, 'Alice', 'F', 20);-- 修改Student表中id为1的学生的姓名
UPDATE Student SET name = 'Alice Smith' WHERE student_id = 1;-- 删除Student表中id为1的学生
DELETE FROM Student WHERE student_id = 1;-- 查询所有学生的姓名和年龄
SELECT name, age FROM Student;

五、掌握使用存储过程的封装SQL的能力

创建存储过程：

DELIMITER //
CREATE PROCEDURE procedure_name (IN parameter_name datatype, OUT parameter_name datatype)
BEGIN-- SQL语句
END //
DELIMITER ;

调用存储过程：

CALL procedure_name(value, @out_parameter);

示例：

DELIMITER //
CREATE PROCEDURE GetStudentById (IN student_id_in INT, OUT student_name_out VARCHAR(50))
BEGINSELECT name INTO student_name_out FROM Student WHERE student_id = student_id_in;
END //
DELIMITER ;-- 调用存储过程
CALL GetStudentById(1, @student_name);
SELECT @student_name;

六、掌握使用事务保证操纵数据的完整性的能力

开启事务：
```
START TRANSACTION;
```
提交事务：
```
COMMIT;
```
回滚事务：
```
ROLLBACK;
```

示例：

START TRANSACTION;-- 插入两条记录到Student和Enrollment表
INSERT INTO Student (student_id, name, gender, age) VALUES (2, 'Bob', 'M', 22);
INSERT INTO Enrollment (student_id, course_id, grade) VALUES (2, 101, 'A');-- 如果没有错误，提交事务
COMMIT;-- 如果有错误，回滚事务
-- ROLLBACK;

七、具备使用索引提高查询效率的能力

创建索引：

CREATE INDEX index_name ON table_name (column1, column2, ...);

删除索引：
```
DROP INDEX index_name ON table_name;
```

示例：

-- 在Student表的name字段上创建索引
CREATE INDEX idx_student_name ON Student (name);-- 删除索引
DROP INDEX idx_student_name ON Student;

八、掌握使用的视图提高数据安全性的方法

创建视图：

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

使用视图：
```
SELECT * FROM view_name;
```

示例：

-- 创建一个只包含学生姓名和年龄的视图
CREATE VIEW view_student_name_age AS SELECT name, age FROM Student;-- 使用视图查询数据
SELECT * FROM view_student_name_age;

九、掌握备份和恢复数据库的方法

备份数据库：
- 使用MySQL自带的mysqldump工具进行备份。
- 例如：mysqldump -u root -p database_name > backup_file.sql
恢复数据库：
- 使用mysql命令将备份文件导入到数据库中。
- 例如：mysql -u root -p database_name < backup_file.sql

示例：

-- 备份student_management数据库
mysqldump -u root -p student_management > student_management_backup.sql-- 恢复student_management数据库
mysql -u root -p student_management < student_management_backup.sql

十、掌握创建数据库用户授予相应权限的方法

创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授予权限：

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@

熟练掌握JDBC操作数据库

加载JDBC驱动

首先，你需要确保你的项目中包含了相应数据库的JDBC驱动。然后，在代码中加载这个驱动。
```
Class.forName("com.mysql.cj.jdbc.Driver"); // 以MySQL为例
```

建立数据库连接

使用DriverManager类来获取数据库连接。

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/yourdatabase", "username", "password");

执行SQL语句

使用Statement或PreparedStatement对象来执行SQL语句。

String sql = "SELECT * FROM yourtable";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);// 或者使用PreparedStatement
String sql2 = "SELECT * FROM yourtable WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql2);
pstmt.setInt(1, 123); // 假设id为123
ResultSet rs2 = pstmt.executeQuery();

处理结果集

遍历ResultSet对象来获取查询结果。

while (rs.next()) {int id = rs.getInt("id");String name = rs.getString("name");// ... 处理其他列
}

关闭资源

最后，确保关闭ResultSet、Statement和Connection对象以释放数据库资源。
```
rs.close();
stmt.close();
conn.close();
```

熟练掌握使用DAO模式封装数据访问代码

定义DAO接口

创建一个接口来定义数据访问方法。

public interface UserDao {User getUserById(int id);void insertUser(User user);// ... 其他方法
}

实现DAO接口

编写实现类来执行具体的数据库操作。

public class UserDaoImpl implements UserDao {private Connection conn;public UserDaoImpl(Connection conn) {this.conn = conn;}@Overridepublic User getUserById(int id) {String sql = "SELECT * FROM users WHERE id = ?";try (PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setInt(1, id);try (ResultSet rs = pstmt.executeQuery()) {if (rs.next()) {User user = new User();user.setId(rs.getInt("id"));user.setName(rs.getString("name"));// ... 设置其他属性return user;}}} catch (SQLException e) {e.printStackTrace();}return null;}@Overridepublic void insertUser(User user) {String sql = "INSERT INTO users (name, ...) VALUES (?, ...)";try (PreparedStatement pstmt = conn.prepareStatement(sql)) {pstmt.setString(1, user.getName());// ... 设置其他参数pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}// ... 实现其他方法
}

使用DAO

在你的业务逻辑中使用DAO对象来访问数据库。

public class UserService {private UserDao userDao;public UserService(Connection conn) {this.userDao = new UserDaoImpl(conn);}public User getUserById(int id) {return userDao.getUserById(id);}public void addUser(User user) {userDao.insertUser(user);}// ... 其他业务方法
}