[Java手记]JDBC技术-数据库的操作

MySQL数据库基本操作语句

#查询数据/来自stu表的所有数据
SELECT * FROM stu;
SELECT NAME,age FROM stu;

#增加数据
INSERT INTO
stu(id,name,age,sex)
VALUES(1004,"赵六",13,"女");

#删除数据
DELETE FROM stu
WHERE NAME="赵六";

#修改数据
UPDATE stu
SET NAME="李四"
WHERE id=1002;

#条件查询
SELECT * FROM stu WHERE name="张三"
SELECT * FROM stu WHERE age>=11

#模糊查询
SELECT * FROM stu WHERE name LIKE '张%';#查找张开头
SELECT * FROM stu WHERE name LIKE '%三';#查找三结尾
SELECT * FROM stu WHERE name LIKE '%三%';#查找包含三

#分组查询
SELECT * FROM stu
GROUP BY age;

#函数查询，数数，男的几个，女的几个
SELECT sex,COUNT(*),AVG(age) FROM stu
GROUP BY sex;

#COUNT() 用于统计条数
#SUM() 和
#MAX() 最大
#MIN() 最小

#连接查询/内连接
SELECT * FROM stu #表来自stu
INNER JOIN CLASS #加入Class表
ON stu.classid=CLASS.id #ON表示他们因那一张字段联系

#左外连接/左表全显示，右表只显示关联信息
SELECT * FROM stu #表来自stu
LEFT INNER JOIN CLASS #加入Class表
ON stu.classid=CLASS.id #ON表示他们因那一张字段联系

#右外连接/右表全显示，左表只显示关联信息
SELECT * FROM stu #表来自stu
RIGHT INNER JOIN CLASS #加入Class表
ON stu.classid=CLASS.id #ON表示他们因那一张字段联系

JDBC连接数据库基本操作

创建JDBC连接

    public static void main(String[] args) {
        Connection conn=null;
        String address="jdbc:mysql://127.0.0.1:3306/pymdv?useSSL=false&serverTimezone=UTC";
        String databaseName="pymdv";
        String dateBasePwd="Qwe123456";
        try {
            //加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //链接数据库
            conn= DriverManager.getConnection(address,databaseName,dateBasePwd);
            System.out.println("连接成功！");

            //处理数据

        } catch (Exception e) {
            System.out.println("连接失败！");
            e.printStackTrace();
        }finally {
            try {
                //关闭链接资源
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

查询数据

    public static void main(String[] args) {
        Connection conn=null;
        String address="jdbc:mysql://127.0.0.1:3306/pymdv?useSSL=false&serverTimezone=UTC";
        String databaseName="pymdv";
        String dateBasePwd="Qwe123456";
        try {
            //加载JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //链接数据库
            conn= DriverManager.getConnection(address,databaseName,dateBasePwd);
            System.out.println("连接成功！");

            //处理数据
            String sql="SELECT * FROM stu";//执行SQL查询Stu表所有数据

            //Statement对象用于执行SQL语句
            Statement stmt=conn.createStatement();

            //执行SQL语句并获取查询结果
            ResultSet rs=stmt.executeQuery(sql);

            while (rs.next()){
                //打印Stu表里对应列表的所有数据
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("name"));
            }

        } catch (Exception e) {
            System.out.println("连接失败！");
            e.printStackTrace();
        }finally {
            try {
                //关闭链接资源
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

增加数据

//GetConnection类

//Main类
    public static void main(String[] args) {
        Connection conn=null;
        Statement stmt=null;

        try {
            conn=GetConnection.getConn();
            Scanner s=new Scanner(System.in);
            System.out.println("请输入ID");
            int id=s.nextInt();
            System.out.println("请输入姓名");
            String name=s.next();
            System.out.println("请输入年龄");
            int age=s.nextInt();
            System.out.println("请输入性别");
            String sex=s.next();

            String sql="insert into stu(id,name,age,sex)"
                    + "values("+id+",'"+name+"',"+age+",'"+sex+"');";

            //Statement对象用于执行SQL
            stmt=conn.createStatement();

            //执行SQL
            stmt.execute(sql);
            int count=stmt.executeUpdate(sql);//执行SQL并返回受影响的数据条数

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

删除和修改数据

//GetConnection类

//Main类
    public static void main(String[] args) {
        Connection conn=null;
        Statement stmt=null;

        try {
            conn=GetConnection.getConn();

            String sql1="DELETE FROM STU WHERE NAME='王五'";//删除
            String sql2="UPDATE STU SET SEX='未知' WHERE NAME='张三'";//修改

            //Statement对象用于执行SQL
            stmt=conn.createStatement();

            //执行SQL
            stmt.execute(sql1);
            stmt.execute(sql2);
            System.out.println("执行成功！");

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }