MySQL小练习（使用JDBC操作数据库）

叁歲伎倆 2023-10-10 22:26 75阅读 0赞

> 题目：
> 
> 1.创建一个数据库(学号+姓名缩写,如: 2020001zs)在数据库中创建一张表 (五个以上字段) ;
> 
> 2.使用JDBC(使用PreparedStatement接口) 操作数据库对表中的数据进行增删改查操作

**目录**

一、数据库

1.创建数据库

2.创建表

3.添加数据

二、JDBC

1.准备环境

2.查询数据

3.添加数据

4.删除数据

5.修改数据

--------------------

### 一、数据库 ###

#### 1.创建数据库 ####

![cdfb18dd52b24d72be4a083c2f17f7ef.png][]

![4d564a2714ca4cb8b88921ffcc178444.png][]

#### 2.创建表 ####

题目说：数据库中创建一张表(五个以上字段)，咋们就弄5个

drop table if exists student_course;
    create table student_course
    (
        course_id     varchar(10)  comment '课程号',
        course_name   varchar(15) comment '课程名',
        course_number double unsigned comment '学分数',
        student_time  int unsigned comment '学时数',
        teacher       varchar(10) comment '任课教师'
    )
        comment '课程表';
    
    select *
    from student_course;

![5210e275b9ad49daac9f654cdb9d3363.png][]

![53e2728247174a9984c39bfce8f53096.png][]

#### 3.添加数据 ####

INSERT INTO student_course
    values ('K001', '计算机图形学', 2.5, 40, '胡晶晶'),
           ('K002', '计算机应用基础', 3, 48, '任泉'),
           ('K006', '数据结构', 4, 64, '马跃先'),
           ('M001', '政治经济学', 4, 64, '孔繁新'),
           ('S001', '高等数学', 3, 48, '赵晓尘');
    
    			 select *
    from student_course;

![86e11263b5f349c88d84d2d0d8c124f6.png][]

### 二、JDBC ###

#### 1.准备环境 ####

点击进入[mysql jar包下载官网][mysql jar]

![b1aab80ab9e049e89fbffb9fdc288397.png][] **MySQL如果是5版本：**

![b89db93df3af4b2897b64111e5015845.png][]

![64785f809b9143b3a2269a302fc030a1.png][]

**我的mysql是8版本，所以下载这个：**![b5c18db86f81465aaf695cb85d45bc67.png][]

![7df5848dd04e450685b6243e8799e6a6.png][]

![7744ea250a6d4fd68d17d797e28e82e4.png][]

解压：![3b261cfcf99d4ebdafa7606c214268a2.png][]

![0560075d5ad541e5847d132b3e9f14a5.png][]

![55bbb22fc8264c37870d1de0c07640da.png][]

![7551a074bf334e93b777c7eea5626173.png][]

#### 2.查询数据 ####

**5.x版本的驱动文件jar包对应的是：  
Class.forName("com.mysql.jdbc.Driver");  
语句来加载数据库驱动**

**而我使用的是8.0x版本的数据库驱动文件，对此，需要将加载数据库驱动的语句更改为：  
Class.forName("com.mysql.cj.jdbc.Driver");**

import java.sql.*;
    
    public class Main {
    
        public static void main(String[] args) throws Exception {
            PreparedStatement ps = null;
            Connection con = null;
            ResultSet rs = null;
    
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/20202122084tsj?serverTimezone=GMT%2B8", "root", "root");
                String sql = "select * from student_course;";
                ps = con.prepareStatement(sql);
                rs = ps.executeQuery();
                while (rs.next()) {
                    System.out.println("编号:" + rs.getString(1) + "\t" + "课程名:" + rs.getString(2) + "\t" + "学分数:" + rs.getString(3) + "\t" + "学时数:" + rs.getString(4) + "\t" + "任课教师:" + rs.getString(5));
                }
    
                rs.close();
                ps.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    
    }

![b5127ccd4cac4aa182794ca9dee195c4.png][]

#### 3.添加数据 ####

import java.sql.*;
    public class InsertData {
        public static void main(String[] args) {
            Connection con = null;
            PreparedStatement ps = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/20202122084tsj?serverTimezone=GMT%2B8", "root", "root");
                String sql = "INSERT INTO student_course values(?,?,?,?,?);";
                ps = con.prepareStatement(sql);
                ps.setString(1, "K111");
                ps.setString(2, "javaWeb");
                ps.setDouble(3, 3);
                ps.setInt(4, 48);
                ps.setString(5, "丁老师");
                ps.execute();
                ps.close();
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

![1cf923e8da9f4846aebab7e03d99670b.png][]

#### 4.删除数据 ####

import java.sql.*;
    
    public class DeleteData {
        public static void main(String[] args){
            Connection con = null;
            PreparedStatement ps = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/20202122084tsj?serverTimezone=GMT%2B8", "root", "root");
                String sql = "delete from student_course where course_id  = ?;";
                ps = con.prepareStatement(sql);
                ps.setString(1,"K111");
                ps.execute();
                ps.close();
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

**之前添加的数据被删除了**

![01d287ed22e24683a7089e6e0f0d55d9.png][]

####  ####

#### 5.修改数据 ####

import java.sql.*;
    
    public class UpdateData {
        public static void main(String[] ags){
            Connection con = null;
            PreparedStatement ps = null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                con = DriverManager.getConnection("jdbc:mysql://localhost:3306/20202122084tsj?serverTimezone=GMT%2B8", "root", "root");
                String sql = "update student_course set course_name = ?,teacher = ? where course_id  = ?;";
                ps = con.prepareStatement(sql);
                ps.setString(1,"SpringCloud");
                ps.setString(2,"王老师");
                ps.setString(3,"S001");
                ps.execute();
                ps.close();
                con.close();
            }catch (Exception e){
                e.printStackTrace();
            }
        }
    }

![429c546cada44b00b73d35d9ae4792fc.png][]

>  **代码冗余严重，之后会有工具类简化**

[cdfb18dd52b24d72be4a083c2f17f7ef.png]: https://img-blog.csdnimg.cn/cdfb18dd52b24d72be4a083c2f17f7ef.png
[4d564a2714ca4cb8b88921ffcc178444.png]: https://img-blog.csdnimg.cn/4d564a2714ca4cb8b88921ffcc178444.png
[5210e275b9ad49daac9f654cdb9d3363.png]: https://img-blog.csdnimg.cn/5210e275b9ad49daac9f654cdb9d3363.png
[53e2728247174a9984c39bfce8f53096.png]: https://img-blog.csdnimg.cn/53e2728247174a9984c39bfce8f53096.png
[86e11263b5f349c88d84d2d0d8c124f6.png]: https://img-blog.csdnimg.cn/86e11263b5f349c88d84d2d0d8c124f6.png
[mysql jar]: https://dev.mysql.com/downloads/
[b1aab80ab9e049e89fbffb9fdc288397.png]: https://img-blog.csdnimg.cn/b1aab80ab9e049e89fbffb9fdc288397.png
[b89db93df3af4b2897b64111e5015845.png]: https://img-blog.csdnimg.cn/b89db93df3af4b2897b64111e5015845.png
[64785f809b9143b3a2269a302fc030a1.png]: https://img-blog.csdnimg.cn/64785f809b9143b3a2269a302fc030a1.png
[b5c18db86f81465aaf695cb85d45bc67.png]: https://img-blog.csdnimg.cn/b5c18db86f81465aaf695cb85d45bc67.png
[7df5848dd04e450685b6243e8799e6a6.png]: https://img-blog.csdnimg.cn/7df5848dd04e450685b6243e8799e6a6.png
[7744ea250a6d4fd68d17d797e28e82e4.png]: https://img-blog.csdnimg.cn/7744ea250a6d4fd68d17d797e28e82e4.png
[3b261cfcf99d4ebdafa7606c214268a2.png]: https://img-blog.csdnimg.cn/3b261cfcf99d4ebdafa7606c214268a2.png
[0560075d5ad541e5847d132b3e9f14a5.png]: https://img-blog.csdnimg.cn/0560075d5ad541e5847d132b3e9f14a5.png
[55bbb22fc8264c37870d1de0c07640da.png]: https://img-blog.csdnimg.cn/55bbb22fc8264c37870d1de0c07640da.png
[7551a074bf334e93b777c7eea5626173.png]: https://img-blog.csdnimg.cn/7551a074bf334e93b777c7eea5626173.png
[b5127ccd4cac4aa182794ca9dee195c4.png]: https://img-blog.csdnimg.cn/b5127ccd4cac4aa182794ca9dee195c4.png
[1cf923e8da9f4846aebab7e03d99670b.png]: https://img-blog.csdnimg.cn/1cf923e8da9f4846aebab7e03d99670b.png
[01d287ed22e24683a7089e6e0f0d55d9.png]: https://img-blog.csdnimg.cn/01d287ed22e24683a7089e6e0f0d55d9.png
[429c546cada44b00b73d35d9ae4792fc.png]: https://img-blog.csdnimg.cn/429c546cada44b00b73d35d9ae4792fc.png