SQL_day_28_链接查询 、JDBC

目录

1. 行转列
2. 连接查询
3. JDBC

1.行转列

• 正常的查询结果是这样
  
• 可我想要这样的结果
  

1.1多行转一行多列

思路分析 :
首先我们默认的情况
每个名字都输出两次,而最终结果只有一次名字,所以肯定是
以名字分组 group by
select * from test_9 group by name;
对我们来说 ,id,课程,和分数都不需要了,只需要有名字 然后再把java和mysql放上去
select name , 1 as java , 1 as MySQL from test_9 group by name;

select
	name,max(
		case course
		when 'java' then score 
	end) 
	Java, max(
		case course
		when 'MySQL' then score 
	end) MySQL 
from test_9
group by name;

1.2多行转一行一列

• 结果如下
  

• 第一步：拆分问题，先按分组的思路
  select name,1 as ‘各科成绩’ from test_9 group by name;

• 第二步：将课程名与成绩拼接成一列
  select name,
  concat(course,’=’,score)
  as ‘各科成绩’
  from test_9 group by name;

• 第三步：利用group_concat函数将多行压扁到一行
  select name,
  group_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;

2.连接查询

2.1 inner join

使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行
同时左表中有的,右表中没有的数据,都不会生成
右表中有的,左表中没有的也一样不会生成,所以 左表和右表就算换了位置,数据行数不会变多

• 先以左表为基准查询, 只查询左右表都有的数据

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

2.2 left join

以左边的表为基准,左表中数据都有,右表中不符合条件的就没有,就在指定列上用null代替

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

3.JDBC

• java database connection

第0步: 导包
第1步：注册驱动 (仅仅做一次)
第2步：建立连接(Connection)
第3步：创建运行SQL的语句(Statement)
第4步：运行语句
第5步：处理运行结果(ResultSet)
第6步：释放资源
其中 如果是添加,删除,更新操作,可以没有第5步,查询肯定会有第五步

0. 导包
   建立lib文件夹, 其中将jar包复制进入lib, eclipse中右键jar包->build path->Add to build path

jar包可以去以下网站下载
https://repo1.maven.org/maven2/ 全球中央仓库
https://mvnrepository.com jar包

• 代码如下

 public static void mysqlJdbc() {
  Connection connection = null;
  Statement statement = null;
  ResultSet resultSet = null;
  try {
   // 1.加载驱动
   Class.forName("com.mysql.jdbc.Driver");
   // 2.链接对象
   connection = DriverManager.getConnection(
     "jdbc:mysql://localhost:3306/day_28", "root", "123456");
   // 3.创建传输对象
   statement = connection.createStatement();
   // 4.创建sql语句,并执行
   String sql = "select * from test_jdbc";
   resultSet = statement.executeQuery(sql);
   // 5.打印结果集
   while (resultSet.next()) {
    // 将数据库的列名转换为数据类型(根据别名获取-即引号中的值)
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    double money = resultSet.getDouble("money");
    System.out.println("id:" + id + "\rname:" + name + "\rmoney:"
      + money);
   }
  } catch (Exception e) {
   e.printStackTrace();
  } finally {
   // 6 关闭资源
   try {
    if (resultSet != null) {
     resultSet.close();
    }
    if (statement != null) {
     statement.close();
    }
    if (connection != null) {
     connection.close();
    }
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

• 使用prepareStatement进行DML操作

 public static void jdbcTest(int id, String name , double money){
  Connection connection = null;
  PreparedStatement  preStatement =null;
  Statement statement = null;
  ResultSet resultSet = null;
  try {
   //1.加载驱动
   Class.forName("com.mysql.jdbc.Driver");
   //2.建立连接对象
   connection = DriverManager.getConnection(
     "jdbc:mysql://localhost:3306/day_28","root","123456");
   //3.建立sql语句,并执行
   String sql = "insert into test_jdbc (id,name,money) values (?,?,?)";
   preStatement = connection.prepareStatement(sql);
   //设定问号的值,(位置,名称)
   preStatement.setInt(1, id);
   preStatement.setString(2, name);
   preStatement.setDouble(3, money);
   //获取已经更新的数据数量
   int count = preStatement.executeUpdate();
   System.out.println(count+"条数据已经被改变!");
   String sql1 = "select * from test_jdbc";
   resultSet = preStatement.executeQuery(sql1);
   //4.打印结果集
   while(resultSet.next()){
    int id1 = resultSet.getInt("id");
    String name1 = resultSet.getString("name");
    double money1 = resultSet.getDouble("money");
    System.out.println("id:" + id1 + "\rname:" + name1 + "\rmoney:"
      + money1);
   }
  } catch (Exception e) {
   e.printStackTrace();
  }finally{
   try {
    resultSet.close();
    preStatement.close();
    connection.close();
   } catch (SQLException e) {
    e.printStackTrace();
   }
  }
 }

还可以提取公共部分进行封装为DBUtil

public class DBUtill {
 //连接方法
 public static Connection getConnection(){
  Connection connection = null;
  try {
   //1.加载驱动
   Class.forName("com.mysql.jdbc.Driver");
   //2.获取连接
   connection = DriverManager.getConnection(
     "jdbc:mysql://localhost:3306/day_28","root","123456");
  } catch (Exception e) {
   e.printStackTrace();
  }
  return connection;
 }
 //关闭方法,因为AutoCloseable是有关jdbc资源操作的父类,使用多态即可调用
 public static void close(AutoCloseable obj){
  if (obj != null) {
   try {
    obj.close();
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 }
}

多语句执行

• statement.addBatch("");

public static void jdbc() {
  Connection connection = null;
  Statement statement = null;
  ResultSet resultSet = null;
  try {
   connection = DBUtill.getConnection();
   statement = connection.createStatement();
   //添加多条语句
   statement.addBatch("insert into test_jdbc (id,name,money) values (20,'小米',2500)");
   statement.addBatch("update test_jdbc set name = '大米' where id = 1");
   statement.addBatch("delete from test_jdbc where id = 1");
   String sql = "select * from test_jdbc";
   statement.executeBatch();
   resultSet = statement.executeQuery(sql);
  } catch (SQLException e) {
   e.printStackTrace();
  }finally {
   DBUtill.close(statement);
   DBUtill.close(connection);
  }
 }