暴力理解JDBC,多敲多记

-- 创建自定义方法
create function getLength(str varchar(20)) returns int
    begin
       return length(str);
    end
-- 选择方法
select getLength('zzz');
-- 删除方法
drop function getLength;

行转列

多行转一行多列

-- name分组
select * from test_9 group by name;
-- id和课程不要,只要姓名和分数
select name,1 as java, 1 as mysql form test_9 group by name;
-- 引入数据
select name,
    case courese
       when 'java' then score
    end
    as java,
    case course
       when 'mysql' then score
    end
    as mysql from test_9 group by name;

因为分组,数据被压缩,如果不使用组函数,只能访问最上面的语句,如果最上面是java,就没有办法获取mysql成绩;

-- 使用聚合函数把多行数据压缩到一行
select name ,
    min(
       case course
       when 'java' then score
       end
   )as java,
    max(
       case course
       when 'mysql' then score
       end
   )as mysql
from test_9 group by name;

多行转一行一列

concat(值,'拼接符',值),拼接,多行数据只会拼接一行
group_concat(值,'拼接符',值)拼接,函数将多行压扁成一行
select name,
    concat(course,'=',score)as '各科成绩'
from test_9 group by name;
select name,
    group_concat(course,'=',score separator '|')as '各科成绩'
from test_9 group by name;
select name,
    group_concat(course,'=',score order by course asc separator '|') as '各科成绩'
from test_9 group by name;

DQL-连接查询:

笛卡尔积,又叫笛卡尔乘积,多表查询中,连接的where限定条件,不能少于表的个数-1,否则会发生笛卡尔积,这个限定条件并不是随便的限定条件,二是用于维护映射两个表的条件,比如说外键;
笛卡尔乘积是一个很消耗内存的运算,它产生的新表,行数是原来行数的乘积,列则是原来列之和,所以我们需要对其进行优化;
优化一:使用等值连接条件,比如说where id=t.id;
优化二:使用inner join而不使用left join;
优化三:使用记录数更少的表当左表;
数据库在通过连接多张表返回记录的时候,会生成一张中间的临时表,然后把临时表返回给用户;
对于left join,不管on后面跟什么条件,左表的数据全部查出来,因此想要过滤,必须把条件放在where后面;
对于inner join来说,满足on后面的条件表的数据才能查出来,起到过滤条件,也可以把条件放在where后面;
on是在生成临时表时会执行的条件,需要和连接查询一起使用,而where是在生成临时表后再进行过滤,这时已经没有join的含义了;
多表查询中,左表称为主表,先生成左表,然后再生成右表,两个表中不符合on条件的数据都不要,inner join可以直接写join,不写inner;

select * from student s inner join teacher t on s.teacher_id=t.id inner join teacher t on s.teacher_id=t.id;
select * from student s left join outer join teacher t on s.teacher_id=t.id;

left join on左连接是left outer join的简写,使用left join和left outer join是一样效果,以左表为基准,左表数据全由,右表中不符合条件的数据就不要了;
right join是以右表为基准,右边中数据都有,左表中不符合条件的没有,就在指定列上用null替代,但是视图生成的时候,还是会生成左表数据,所以我们优先用inner join和left join;
外键列比较适合做限定条件,但是外键是为了保障不能随便修改数据,是数据完整性的一种约束,而表连接是因为一张表字段无法满足需求,它们两者之间没有关系;
模拟Oracle中的full join:
通过上面可以了解到inner筛选的是两个表都符合条件的数据;
left是筛选右表中符合条件的数据,左表完全保留;
right则是和left大致相反;
可要是两个表符合不符合都有呢?mysql中取消了full join连接,但是我们可以哦那个union模拟一下;
on和group by同时出现的时候on需要写在前面;

select * from student s
    left join teacher t on s.teacher_id=t.id
union
select * from student s
    right join teacher t on s.teacher_id=t.id;

备份SQL

下面这部分是关于使用Navicat工具的,右键我们需要保存的表,会有一个转存SQL文件的选项,里面有仅结构(只创建表或者数据库语句,没有数据)和结构和数据(就是创建语句和数据都有),这样我们的sql文件就生成了;
然后在navicat中右键点击数据库,点击后面的运行SQL文件,选择我们要运行的文件即可生成我们的表;
除了使用native,我们还可以使用CMD数据导入和导出;
从mysql文件夹的bin地址栏进入cmd中,mysqldump -u 用户名 -p 密码 需要导出的数据库>导出文件的保存位置;
导入操作,同样是进入bin目录下然后cmd,mysql -u 用户名 -p 密码 导入那个数据库<导入的文件路径;

JDBC

这是本文最后一部分也是学习Java,MySQL最为重要的一部分,就是JDBC;
像native的图形化操作虽然方便,但是在程序运转的时候,人是没有办法及时处理这些数据的,还是需要依赖程序语言,Java连接数据库就是使用的JDBC,它定义了一类规范标准,对应的是各种接口和抽象类,具体实现交给各数据库厂商去完成,MySQL的有自己的实现类并打成jar包发布,供程序开发人员使用;
这里提供两个下载地址:
https://repo1.maven.org/maven2
https://mvnrepository.com/
下载好之后,我们在自己的项目导入jar,以ecpliese为例,我们在项目右键Build Path然后Configure Build Path将之加入我们的项目的CLASSPATH;
使用JDBC,最开始肯定是导包,然后注册驱动,建立连接,创建运行SQL语句,运行语句,处理运行结果,释放资源;
导包除了使用上面的方法,还可以使用在java项目下创建一个lib文件夹,然后将我们需要导入的包复制到lib中,右键->Build Path->Add to Path;

// 注册驱动,但是仅仅是把其类加载到内存中
Class.forName("com.mysql.jdbc.Driver");
// 注册驱动,创建堆内存对象,也就是会有一个import的操作 下面两个语句是相同作用
Class.forName("com.mysql.jdbc.Driver").newInstance();
new com.mysql.jdbc.Driver;
// 建立连接
第一个参数url(jdbc:mysql://IP:端口号/数据库),第二个参数是数据库用户名;第三个参数是数据库密码;
// 接下来导包的时候我们需要导入的是java.sql,因为导包尽量导父类包,不要导子类包
Connection conn = DriverManager.getConnection("","","");
// 创建SQL语句
String sql = "select * from test_9";
Statement stmt = conn.createStatement();
// 运行语句
ResultSet rs = stmt.executeQuery(sql);
// 处理语句
while(rs.next){
    System.out.println(rs.getInt("id")+" ");
    // 如果传入的是整型值,那么会或等对应的列
    System.out.println(rs.getInt(1));// 不建议使用
    System.out.println(rs.getString("id")+" ");
   System.out.println(rs.getString("name")+" ");
   System.out.println(rs.getString("course")+" ");
   System.out.println(rs.getDouble("score")+" ");
}
// 释放资源 依旧是先关闭最后开启的
rs.close();
stmt.close();
conn.close();

下面我会将代码优化一遍,然后放在下面,大家可以自行感受一下将变量先声明和使用try…catch…finally的优点:

package day27_JDBC;
import java.security.interfaces.RSAKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/**
* @                           _ooOoo_
*                            o8888888o
*                            88" . "88
*                            (| -_- |)
*                            O\  =  /O
*                         ____/`---'\____
*                       .'  \\|     |//  `.
*                      /  \\|||  :  |||//  \
*                     /  _||||| -:- |||||-  \
*                     |   | \\\  -  /// |   |
*                     | \_|  ''\---/''  |   |
*                     \  .-\__  `-`  ___/-. /
*                   ___`. .'  /--.--\  `. . __
*                ."" '<  `.___\_<|>_/___.'  >'"".
*               | | :  `- \`.;`\ _ /`;.`/ - ` : | |
*               \  \ `-.   \_ __\ /__ _/   .-` /  /
*           ======`-.____`-.___\_____/___.-`____.-'======
*                             `=---='
*           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*                     佛祖保佑        永无BUG
* @author 作者 SkyMei
* @version 创建时间：2021年4月27日 下午6:41:47
* 类说明
*/
public class Day27_JDBC01 {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
             Class.forName("com.mysql.jdbc.Driver");
             connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/***","root","***");
             String sqlString = "select * from test";
             statement = connection.createStatement();
             resultSet =  statement.executeQuery(sqlString);
             while(resultSet.next()){
                 System.out.println(resultSet.getInt("id")+" ");
                 System.out.println(resultSet.getString("name")+" ");
             }
        } catch (Exception e) {
             // TODO: handle exception
             e.printStackTrace();
        }finally{
             try {
                 if(resultSet!=null){
                      resultSet.close();
                 }
                 if (statement!=null) {
                      statement.close();
                 }
                 if (connection!=null) {
                      connection.close();
                 }
             } catch (Exception e2) {
                 // TODO: handle exception
                 e2.printStackTrace();
             }
        }
    }
}

除了Select之外,我们还可以用DML来通过JDBC操作数据;
stmt = conn.createStatement();
String sql = “insert into test() values(”","")";
// 返回影响了几条数据
int count = stmt.executeUpdate(sql);
但是我们如果使用Statement会出现一个问题,哪就是Statement比较经过用于执行静态SQL语句,如果我们用它进行增删改很可能会导致SQL注入,所以我们尽量使用PrepareStatement,它是预编译的SQL语句对象,使用它的时候,SQL会被数据库预编译和预解析,放在缓冲区,每次执行一个PrepareStatement对象的时候,会被重新解析一次,但不会被再次编译,可以重复使用,减少编译此处,提高数据库性能,为了避免SQL注入,它使用单引号将一些字符转义,并且?可以当作动态包含的参数;
PrepareStatement prst = null
…
String sql = “select * from test where id=?”;
prst = conn.prepareStatement(sql);
// 设置第一个?的值
prst.setInt(1,id);
String sql = “insert into test() values(?,?,?)”;
prst.setInt(1,id);
prst.setString(2,name);
prst.setDouble(3,money);
// 返回也是影响的条数
int count = prst.executeUpdate();
上面我写的简化的代码,其中连接数据库,关闭数据库的操作其实只需要进行一遍就可以,所以我们可以单独把它们抽出来,变成独立的方法,又可以进一步简化代码;

package day27_JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @                           _ooOoo_
*                            o8888888o
*                            88" . "88
*                            (| -_- |)
*                            O\  =  /O
*                         ____/`---'\____
*                       .'  \\|     |//  `.
*                      /  \\|||  :  |||//  \
*                     /  _||||| -:- |||||-  \
*                     |   | \\\  -  /// |   |
*                     | \_|  ''\---/''  |   |
*                     \  .-\__  `-`  ___/-. /
*                   ___`. .'  /--.--\  `. . __
*                ."" '<  `.___\_<|>_/___.'  >'"".
*               | | :  `- \`.;`\ _ /`;.`/ - ` : | |
*               \  \ `-.   \_ __\ /__ _/   .-` /  /
*           ======`-.____`-.___\_____/___.-`____.-'======
*                             `=---='
*           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
*                     佛祖保佑        永无BUG
* @author 作者 SkyMei
* @version 创建时间：2021年4月27日 下午7:10:23
* 类说明
*/
public class Day27_JDBC02 {
    public static void main(String[] args) throws  ClassNotFoundException, SQLException {
        // TODO Auto-generated method stub
         getConnection();
        close(getConnection());
    }
    public static Connection getConnection() throws  ClassNotFoundException,SQLException {
        String username = "root";
        String password = "root";
        String url =  "jdbc:mysql://127.0.0.1:3306/***";
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection =  DriverManager.getConnection(url,username,password);
        return connection;
    }
    // 因为Connection和Statement/PrepareStatement以及ResultSet都实现了AutoCloseable接口,所以我们可以直接写AutoCloseable接口
    public static void close(AutoCloseable obj) {
        if(obj!=null){
             try {
                 obj.close();
             } catch (Exception e) {
                 // TODO: handle exception
                 e.printStackTrace();
             }
        }
    }
}

前面部分都是单语句的执行,那么对于多语句操作,我们应该怎么处理呢?Statement和PreparedStatement实现相同吗?
我们一般使用Batch来实现JDBC的多语句执行;
stmt.addBatch(“sql语句”);
stmt.addBatch();
stmt.executeBatch();
String sql = “”;
prst.setInt()
prst.setString;
prst.addBatch();
prst.executeBatch();