【数据库篇1】MySQL数据库增删查改操作

写在前面：

本篇主要记录一下啊数据库的常规操作

---

先给出增删查改等的sql语句

查询表manage是否存在

strsql = " show tables like manage ";

新建表【manage, 包含三个字段name, age, sex】

strsql = "create table manage(name varchar(20) not null, age int(4) not null, sex varchar(20) not null)";

插入数据【字段顺序可以颠倒，字段和value对应即可】

strsql = " insert into manage(sex, age, name) values('女', '24', 'Elevenoo') ";

查询数据【= 精确查询，like 模糊查询，regexp 正则表达式查询】

strsql = " select * from manage where name = 'Elevenoo' ";  // 精确查询

strsql = " select * from manage where name like 'Eleven%' ";  // 模糊查询，查询name字段中以Eleven开头的数据

strsql = " select * from manage where name like 'Eleven_' ";  // 模糊查询，查询name字段中以Eleven开头,往后匹配一个字符的数据

strsql = " select * from manage where name regexp '^Eleven&' "; //正则表达式匹配查询name字段中以Eleven开头的数据

查询整个表

strsql = " select * from manage "; 

修改数据【我老是把update 写成 updata , emmm, 注意注意】

strsql = " update  manage set sex='女', age='18' where name='Elevenoo' ";

删除数据【会删除manage表name字段中所有Elevenoo的行数据】

strsql = " delete from manage where name = 'Elevenoo' ";

 

执行数据库操作步骤

1. 打开数据库的连接

Connection conn = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC",  
        "用户名",
        "密码");

2. 创建statement对象，通过statement将sql语句送入数据库

Statement connStatement = conn.createStatement();

3. 执行sql语句

connStatement.execute(strsql);

 

解释一下Statement执行executeQuery(strsql),execute(strsql),executeUpdate(strsql)的区别和使用操作

int executeUpdate(String sql);              执行新建表,增，删，改，返回执行受到影响的行数； 

ResultSet executeQuery(String sql);    执行查，并返回ResultSet 对象；

boolean execute(String sql);                 执行sql,返回一个bool值，表示是否返回ResultSet(注意，新建表这种操作不会返回ResultSet所以不能通过execute得返回值判断表格创建成功或者失败) ；

---

JBDC驱动

Java项目链接MySQL数据库需要注册JBDC驱动，mysql-connector-java 6 之后提供com.mysql.cj.jdbc.Driver代替com.mysql.jdbc.Driver，com.mysql.cj.jdbc.Driver 通过SPI自动注册，通常不需要手动加载驱动程序类。

        //通过类的静态方法forName()加载数据库连接的类，注册jdbc驱动
//        Class.forName("com.mysql.cj.jdbc.Driver");  //SPI自动注册，不需要程序员自己加载

        //使用用户名和密码连接 mydb 数据库
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC",  
                "root",
                "18721426");

com.mysql.cj.jdbc.Driver比com.mysql.jdbc.Driver多了serverTimezone参数，使用serverTimezone=UTC会有8小时的时差（中国快全球标准8小时，比如：全球标准当前是上午1点，中国时间则为上午9点），可设置为北京时间东八区GMT%2B8 或者上海时间Asia/Shanghai。

---

Navicat

管理mysql数据库

---

代码：每个操作写成函数，sql语句通过输入的字段-value格式化，如果觉得太绕直接使用上面完整的sql语句即可。

import java.sql.*;
import java.util.*;

public class TestMySQL {


    static Connection conn;
    static Statement connStatement;

    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        System.out.println("init ...");
        //通过类的静态方法forName()加载数据库连接的类，注册jdbc驱动
//        Class.forName("com.mysql.cj.jdbc.Driver");
//        Class.forName("com.mysql.jdbc.Driver");

        //使用用户名和密码连接 mydb 数据库
        conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/mydb?useSSL=false&serverTimezone=UTC",  //使用UTC会有8小时的时差（中国快全球标准8小时，比如：全球标准当前是上午1点，中国时间则为上午9点），可设置为北京时间东八区GMT%2B8 或者上海时间Asia/Shanghai。
                "root",
                "18721426");

        //判断是否连接成功
        if(conn!=null) System.out.println("connect db successed! ...");
        else { System.out.println("connect db failed! ...");return; }

        // 使用JDBC创建Connection后，执行sql需要先创建Statement
        // (创建Statement对象: 建立到特定数据库的连接,用该连接发送SQL语句)
        connStatement = conn.createStatement();

        //新建表
        String tablename = "manage";
        List<String> fields=new ArrayList<>();
        fields.add("name varchar(20) not null");
        fields.add("age int(4) not null");
        fields.add("sex varchar(20) not null");
        if(!createTable(tablename,fields)) return;


        //插入数据
        Map<String, String> fieldsmap = new HashMap<>();
        String[] fv = {"name","Elevenoo"};      //主键
        fieldsmap.put("age","24");
        fieldsmap.put("sex","女");
        insertDate(tablename, fieldsmap,fv);

        //查找数据
        fv = new String[]{"name","Eleven%"};  //模糊查询，往haha后匹配0/1/2...个字符, %的位置表示在哪里匹配
//        fv = new String[]{"name","Eleveno_"};  //模糊查询haha*,往haha后匹配单个字符，不能匹配到haha， _的位置表示在哪里匹配
        ResultSet resultSet = findData(tablename, fv, false);  //flag=true表示精确查询，false表示模糊查询
        while (resultSet.next()){
            for(String s:fields) {
                int m = s.indexOf(" ");
                String f = s.substring(0,m);
                System.out.print(f+"="+resultSet.getString(f)+"; ");
            }
            System.out.println();
        }


        //更改数据
        fieldsmap.clear();
        fieldsmap.put("age","18");
        fieldsmap.put("sex","女");
        fv = new String[]{"name","Elevenoo"};
        updateData(tablename, fieldsmap, fv);

        //删除数据
        fv = new String[]{"name", "Elevenoo"};
        deleteData(tablename, fv);


        //遍历整个表
        ResultSet resultSet1 = watchTable(tablename);
        List<List<String>> list = new ArrayList<>();
        while (resultSet1.next()){
            List<String> lst = new ArrayList<>();
            for(String s:fields) {
                int m = s.indexOf(" ");
                String f = s.substring(0,m);
                lst.add(resultSet1.getString(f));
            }
            list.add(lst);
        }
        //按name首字母排序
        list.sort((lst1,lst2)-> lst2.get(0).charAt(0)<lst1.get(0).charAt(0) ? 1:-1);
        System.out.println(list);

        //按age排序
        list.sort((lst1,lst2)-> Integer.valueOf(lst2.get(1))<Integer.valueOf(lst1.get(1)) ? 1:-1);
        System.out.println(list);
        
    }


    /**
     * 新建表
     * 先判断表是否存在，如果不存在，则创建
     *
     * @param tablename  表名
     * @param fields     字段
     * @return
     * @throws SQLException
     */
    private static boolean createTable(String tablename, List<String> fields)throws SQLException {

        if(connStatement.executeQuery("show tables like '"+tablename+"'").next()){System.out.println("table "+tablename+" existed!"); return true;}
        //格式化sql语句
        String sqlstr = "create table "+tablename+"(";
        for(String field : fields)
            sqlstr+=(field+", ");
        sqlstr=sqlstr.substring(0,sqlstr.length()-2)+")";

        System.out.println("sql: "+sqlstr);

        /**
         * int executeUpdate(String sql); 执行新建表,增，删，改，返回执行受到影响的行数。
         * ResultSet executeQuery(String sql); 执行查，并返回ResultSet 对象。
         * boolean execute(String sql); 执行sql,返回一个bool值，表示是否返回ResultSet(注意，新建表这种操作不会返回ResultSet所以不能通过execute得返回值判断表格创建成功或者失败)
         */
        if(connStatement.executeUpdate(sqlstr)==0) {System.out.println("create table successed!");return true;}
        else { System.out.println("create table failed! ..."); return false; }
    }

    /**
     * 插入数据
     * 首先判断name是否已经存在，存在则插入失败，不存在则添加
     *
     * @param tablename  表名
     * @param fields     字段-数据
     * @param fv         主键-数据
     * @return
     * @throws SQLException
     */
    private static boolean insertDate(String tablename, Map<String,String> fields,String[] fv) throws SQLException {

        if(findData(tablename,fv,true).next()) {System.out.println(fv[0]+" "+fv[1] +" existed!");return false;}

        String[] fildesName = getKey(fields);
        String[] fildesValue = getValues(fields);

        String sqlstr = "insert into "+tablename+"(";
        for(String n : fildesName)
            sqlstr+=(n+", ");
        sqlstr+=(fv[0]+") values(");
        for(String v : fildesValue)
            sqlstr += ("'"+v + "', ");
        sqlstr+=("'"+fv[1]+"')");

        System.out.println("sql: "+sqlstr);


        if(connStatement.executeUpdate(sqlstr)!=-1){ System.out.println("insert data succesed!");return true;}
        else {System.out.println("insert data failed!"); return false;}
    }

    /**
     * 更改数据
     * @param tablename  表名
     * @param fields     需要被更新的字段-数据
     * @param fv         主键-数据
     * @return
     * @throws SQLException
     */
    private static boolean updateData(String tablename,Map<String,String> fields,String[] fv) throws SQLException {

        if(!findData(tablename,fv,true).next()) {System.out.println(fv[0]+" "+fv[1] +" unexisted!");return false;}

        String[] fildesName = getKey(fields);
        String[] fildesValue = getValues(fields);

        String sqlstr = "update  "+tablename+" set ";
        for(int i=0;i<fildesName.length;i++)
            sqlstr+=(fildesName[i]+"='"+fildesValue[i]+"', ");
        sqlstr=sqlstr.substring(0,sqlstr.length()-2)+" where "+fv[0]+"='"+fv[1]+"'";

        System.out.println("sql: "+sqlstr);

        if(connStatement.executeUpdate(sqlstr)!=-1){ System.out.println("update  data succesed!");return true;}
        else {System.out.println("update  data failed!"); return false;}
    }

    /**
     * 删除数据
     * @param tablename  表名
     * @param fv         需要被删除的字段-数据所在行
     * @return
     */

    private static boolean deleteData(String tablename,String[] fv) throws SQLException {

        if(!findData(tablename,fv,true).next()) {System.out.println(fv[0]+" "+fv[1] +" unexisted!");return false;}

        //删除所有fv[0]='fv[1]'的数据行
        String sqlstr = "delete from "+tablename+" where ";
        sqlstr+=(fv[0]+"='"+fv[1]+"'");
        System.out.println("sql: "+sqlstr);
        if(connStatement.executeUpdate(sqlstr)!=0){ System.out.println("delete data succesed!"); return true;}
        else { System.out.println("delete data failed!"); return false;}
    }

    /**
     * 查找数据
     * @param tablename 表名
     * @param fv        需要查找的字段-数据
     * @return
     * @throws SQLException
     */
    private static ResultSet findData(String tablename,String[] fv, boolean flag) throws SQLException {
        String str = "=";
        if(!flag) str="like";
        String sqlstr = "select * from "+tablename+" where "+fv[0]+" "+str+" ?";
        PreparedStatement pst = conn.prepareStatement(sqlstr);
        pst.setString(1,fv[1]);
        ResultSet resultSet = pst.executeQuery();
        return resultSet;
    }

    private static ResultSet watchTable(String tablename) throws SQLException {
        String sqlstr = "select * from "+tablename;
        ResultSet resultSet = connStatement.executeQuery(sqlstr);
        return resultSet;
    }

    private static String[] getKey(Map<String,String> fields){
        Set<String> keySet = fields.keySet();
        String[] strs = new String[keySet.size()];
        int i=0;
        for(String s : keySet)
            strs[i++]=s;
        return strs;
    }

    private static String[] getValues(Map<String,String> fields){
        Collection<String> values = fields.values();
        String[] strs = new String[values.size()];
        int i=0;
        for(String s : values)
            strs[i++]=s;
        return strs;
    }
}