MySQL day03

MySQL

• 回顾和提问

1. 约束分类
2. 条件判断有哪些
3. 常用函数有哪些
4. 如何添加约束
5. 子查询的使用

二、本章任务

1. 完成行转列
2. 完成链接查询
3. 完成导入和导出

三、本章目标

1. 掌握链接查询的几种结构
2. 掌握行转列语法
3. 掌握数据库的导入和导出
4. 了解java中备份和恢复数据库

四、知识点

1. 行转列

1.1 什么是行转列

正常的查询结果是这样

 

可是我想要的结果是这样...

 

通过SQL语句 实现这样的功能,就叫行转列

1.2示例数据准备

有 id,名字,课程,成绩

create table test_9(

       id int,

       name varchar(22),

       course varchar(22),

       score decimal(18,2)

);

insert into test_9 (id,name,course,score)values(1,'小王','java',99);

insert into test_9 (id,name,course,score)values(2,'小张','java',89.2);

insert into test_9 (id,name,course,score)values(3,'小李','java',88);

insert into test_9 (id,name,course,score)values(4,'小王','MySQL',92.2);

insert into test_9 (id,name,course,score)values(5,'小张','MySQL',42.2);

insert into test_9 (id,name,course,score)values(6,'小李','MySQL',59.2);

1.3场景一(多行转一行多列)

可以使用下面的SQL语句（group by 与 case when结合使用即可实现）：

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;

思路分析 :

首先我们默认的情况 每个名字都输出两次,而最终结果只有一次名字,所以肯定是 以名字分组 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.4场景二(多行转一行一列)

 

相关函数

concat(值,’拼接符’,值 ) : 拼接,多行数据只会拼接一行

group_concat(值,’拼接符’,值 ) : 拼接,多行压扁到一行

思路分析 :

第一步：拆分问题，先按分组的思路

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;

演示示例：多行转一行一列

1.  DQL-连接查询

2.1 笛卡尔积

笛卡尔积,也有的叫笛卡尔乘积

多表查询中,链接的where限定条件,不能少于 表的个数-1 , 否则就会发生笛卡尔乘积 , 这个限定条件并不是随便一个限定条件,而是用于维护映射两个表的条件,比如 外键

笛卡尔乘积是一个很消耗内存的运算，笛卡尔积产生的新表，行数是原来两个表行数的乘积，列数是原来两个表列数的和。所以我们在表连接时要使用一些优化手段，避免出现笛卡尔乘积。

最简单的多表查询 : select * from 表1,表2;

示例数据 :

create table teacher(

    id int ,

    name varchar(20),

    primary key (id)

);

create table student (

    id int ,

    name varchar(20),

    teacher_id int ,

    primary key (id),

    foreign key (teacher_id) references teacher(id)

);

insert into  teacher (id,name) values(1,'张老师');

insert into  teacher (id,name) values(2,'王老师');

insert into  student (id,name,teacher_id) values(1,'小明',1);

insert into  student (id,name) values(2,'小红');

insert into  student (id,name,teacher_id) values(3,'小黑',2);

每个行都有两次

如果直接就是写的select * from teacher,student; ,那么 结果的条数等于两个表的乘积

所以 判断条件最少也要是1个,也就是两个表的个数-1

 

条数对了,因为小红没有teacher_id所以不会被查询出来

虽然条数对了,但是也会先发生一个完全的笛卡尔乘积,然后在新视图中找匹配的数据,再展示匹配的数据,会消耗内存一些

所以不推荐使用,推荐使用链接查询

演示示例：多表查询

课堂练习1：多表查询

优化上面的笛卡尔乘积的方式 :

优化一：使用等值连接条件,比如上面的where s.teahcer_id = t.id。

优化二：能使用inner join的就不要使用left join。

优化三：使用记录数更少的表当左表。

但是如果业务上有要求：

比如，我们有一张用户的基本信息表，我们还有一张用户的订单表

现在我们要求在页面上展示，所有用户的订单记录

这种情况下我们就必须使用left join了，因为inner join 会丢数据

假设基本信息表中有A B C三个用户（3条记录）

订单表中有A B两个人的100条订单记录

这种情况下，我们除了使用left join外，还必须要让基本信息表当左表，订单表当右表。

MYSQL支持的表连接查询有inner join，left join，right join（right join我们工作中基本不用）。

2.2 inner join

插入一条示例数据

INSERT INTO teacher (`id`, `name`) VALUES ('3', '孙老师');

1

select *

from teacher tea

inner join student stu on tea.id = stu.teacher_id;

2

select *

from student stu

inner join teacher tea on tea.id = stu.teacher_id;

总结 :

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。
在使用 join 连接查询 时，on和where条件的区别如下：
       1、on条件是在生成临时表时使用的条件,需要和链接查询一起使用。
       2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

链接查询,会发生笛卡尔乘积,但是不是完全的笛卡尔乘积,在生成视图的时候,会进行匹配,不符合条件的就不要了

结果数据是以左表数据为准,先生成左表数据,再生成右表数据

使用内连接的话,会以左边表为基准(student),生成新视图的时候,先生成左边表中的数据,然后再去匹配右边表中是否有符合条件的,没有的话,就不生成这一行

同时左表中有的,右表中没有的数据,都不会生成

右表中有的,左表中没有的也一样不会生成,所以 左表和右表就算换了位置,数据行数不会变多

但是会丢失数据,不符合 条件的数据不会查询出来,所以 刚添加的 孙老师就不会查询出来的,就算是teacher表在左边,也一样不会查询出来孙老师,并且学生小红也没有被查询处理

因为学生表中 teacher_id列 没有保存孙老师的ID,并且小红也没有保存老师的ID,所以都不要

多表查询是有左右表之分的,一般左表是主表,以左边为主

Inner join  也可以直接写join 不写inner

演示示例：inner join

2.3 left join

left join on : 左连接,又称左外链接,是 left outer join 的简写 ,使用left join 和 使用 left outer join 是一样的效果

1 查询结果,显示小红,但是不显示孙老师

select * from student s

    left join teacher t on s.teacher_id = t.id;

2 查询结果显示孙老师,但是不显示小红

select * from teacher t

    left join student s on s.teacher_id = t.id;

总结 :

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

生成视图的时候,也是先生成左表的数据

演示示例：left join

2.4 right join

right join on : 右链接,又称右外连接,是 right outer join 的简写,使用right join 和 使用 right outer join 是一样的

1 查询结果,显示孙老师,但是不显示小红

select * from student s

    right join teacher t on s.teacher_id = t.id;

2 查询结果显示小红,但是不显示孙老师

select * from teacher t

    right join student s on s.teacher_id = t.id;

总结 :

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

但是视图生成的时候,还是会先生成左表数据

以上可以看出，student right join teacher 显示的内容是与teacher left join student相同的。而teacher right join student 显示的内容又与student left join student相同。

所以，我们工作中，right join基本不用。用的最多的是inner join 与left join。

PS：外键与表连接没有任何关系，不要混淆。

外键是为了保证你不能随便删除/插入/修改数据，是数据完整性的一种约束机制。

而表连接是因为一张表的字段无法满足业务需求（你想查的字段来自于2张甚至多张表）

一个是为了增删改，一个是为了查，它俩之间没有联系。

能使用 inner join 就不使用 left join

            能使用 left join 就不使用 right join

演示示例：right join

2.4 模拟Oracle中的full join

上面几个链接查询中

inner是两个表都符合条件的数据

left join 是 左表都有,右表符合条件才有

right join 是 右表都有,左表符合条件才有

那么能不能让两个表,别管符合不符合,都有呢?

full join / full outer join ,但是MySQL这里给取消了,比如Oracle就有

模拟一下 full join 的功能

使用 union 获取并集

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;

 

演示示例：full join

课堂练习2：链接查询

1.  数据库导入和导出

3.1 使用Navicat数据导入和导出

导出

使用Navicat最为简单

右键要导出的表或者数据库,

转储SQL文件,

仅结构 是只有创建表/数据库 语句,没有数据

结构和数据 是有创建语句,也有数据

 

然后会生成一个sql文件

 

导入

右键数据库

运行 SQL文件

 

会弹出一个窗口

 

选择完成之后会有相应的地址填写进去

 

点击开始就行

 

然后刷新一下表/数据库就可以了

课堂练习3：使用Navicat数据导入和导出

3.2 使用CMD数据导入和导出

导出

打开CMD控制台 进入到 MySQL安装路径下的bin目录下

 

或者直接在地址地方输入cmd,如下图

也可以直接打开

mysqldump -u用户名 -p密码  需要导出的数据库 > d:\tianliang.sql(导出的文件的保存位置)

-u用户名 -p密码 要备份的数据库, > 是把文件保存到哪里, < 是文件在哪里导进来

执行命令 mysqldump -uroot -proot _06_> d:\tianliang.sql

就被导出了

导入

还是进入到bin目录下

 mysql -u用户名 -p密码  导入到哪个数据库< d:\tianliang.sql(被导入的文件路径)

-u用户名 -p密码  需要导入的数据库  需要导入的sql文件

执行命令 mysql -uroot -proot _06_< d:\tianliang.sql

 

导入完成

课堂练习4：使用CMD数据导入和导出

3.3 使用Java执行CMD数据导入和导出

演示示例：MySQLUtil

4. JDBC介绍

我们之前学习MYSQL时，为了使用MYSQL服务，我们通常要使用客户端与MYSQL服务进行连接，然后才能输入SQL语句进行数据库的各种操作。客户端有命令行与图形界面2种。

但是在更多的环境下，由我们人直接操作数据是很低效的，比如双11这种业务场景下，一秒中往往要在库中生成上千万甚至几亿条数据，靠人来手工操作是不现实的，只能依赖于程序进行这种高并发的操作。

程序语言有多种，比如Java、Python、C/C++等，程序语言如果想执行SQL语句，也必须要先与数据库进行连接，数据库也有多种，比如MySQL、Oracle、SQL Server等。

不同的程序语言去连接不同的数据库，如果没有一个统一的标准或者规范，肯定是相当混乱的。Java语言对此的解决方案就是JDBC。

JDBC定义了一套规范标准，它对应的是各种接口与抽象类（通常对应java.sql包下面的各种类与接口），具体实现交给各数据库厂商去完成， MySQL的有自己的实现类并打成jar包发布，供程序开发人员使用；Oracle也有自己的实现jar包。

我们开发人员在使用的时候，要根据连接数据库的不同，去对应的官网上下载对应数据库版本与程序语言的数据库驱动（Java语言对应的是一个jar包）。（比如我们使用MySQL 5.1，就要去MySQL官网下载Java语言对应的jar包）

JDBC : Java DataBase Connectivity  (java数据库链接)

  是让java链接数据库的API

API : Application Programming Intergace (应用程序接口)

    就是函数库

所以 JDBC 就是提供java连接数据库的应用程序接口的,只是接口或者抽象类

而JDBC就是java中提供的一个规范,基本都是接口和抽象类作为父类,具体的实现,是数据库厂商去弄的,只不过这些厂商需要按照我的接口标准来实现

如果我们要想操作数据库,就需要把厂商开发的实现类,导入进来

然后在项目上右键  ->  Build Path  ->  Configure Build Path...，将之加入我们项目的CLASSPATH。

5. JDBC使用步骤

第0步: 导包

第1步：注册驱动 (仅仅做一次)

　　第2步：建立连接(Connection)

　　第3步：创建运行SQL的语句(Statement)

　　第4步：运行语句

　　第5步：处理运行结果(ResultSet)

第6步：释放资源

其中 如果是添加,删除,更新操作,可以没有第5步,查询肯定会有第五步

5.0 导包

创建java项目

创建lib文件夹

把mysql-connector-java-5.1.38-bin.jar复制到lib中

右键  ->  Build Path  -> Add to Build Path

5.1 注冊驱动

创建java类 JDBC_01_Base_DQL

Class.forName("com.mysql.jdbc.Driver");

5.2 建立连接(Connection)

第一个参数是url

jdbc:mysql://IP:端口/数据库

第二个参数是数据库用户名

第三个参数是数据库密码

5.3 创建运行SQL的语句(Statement)

5.4 运行语句

   

5.5 处理运行结果(ResultSet)

while (rs.next()) {

           //  在循环遍历中,把数据取出来

           System.out.print(rs.getInt("id") + "    ");

       // 如果传入是整型值 就会获取对应的列,比如下面 就是获取第一列的值,不建议使用

           System.out.print(rs.getInt(1) + "    ");

           System.out.print(rs.getString("id") + "  ");

           // 字符串不能用int来接收,除非这个 字符串是纯数字

           // System.out.print(rs.getInt("name") +"    ");

           System.out.print(rs.getString("name") + "  ");

           System.out.print(rs.getString("course") + "  ");

           System.out.print(rs.getDouble("score")+"  ");

           // 3 对应的是name列,如果更改表结构,把name列放到第四位了,那么这里就获取不到name了

           // 所以 不灵活,不推荐使用

           System.out.println(rs.getString(3));

       }

5.6 释放资源

先打开的资源后关闭

演示示例：JDBC_01_Base_DQL_00

课堂练习1：使用JDBC查询test_jdbc表

6 代码优化

上面程序中,有可能会导致释放资源出现问题

比如查询语句写错了等,这时候会抛出异常,那么关闭语句就不会执行

所以我们应该使用try...catch...finally来优化一下

以刚才的练习为例,对test_jdbc表的查询进行优化

       Connection conn = null;

       Statement stmt = null;

       ResultSet rs = null ;

       try {

           // 1 加载驱动

           Class.forName("com.mysql.jdbc.Driver");

           // 2 创建数据库连接对象

           // 导包使用的都是java.sql的

            conn = DriverManager.getConnection(

                  "jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

          

           // 3 创建语句传输对象

              String sql = "select * from test_jdbc";

               stmt = conn.createStatement();

             

              // 4 接收数据库结果集

               rs = stmt.executeQuery(sql);

              while (rs.next()) {

                  //  在循环遍历中,把数据取出来

                  System.out.print(rs.getInt("id") + "    ");

                  System.out.print(rs.getString("name") + "  ");

                  System.out.println(rs.getDouble("money")+"  ");

              }

      

       } catch (Exception e) {

           e.printStackTrace();

       }finally{

           try {

              if (rs != null) {

                  rs.close();

              }

              if (stmt != null) {

                  stmt.close();

              }

              if (conn != null) {

                  conn.close();

              }

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

   

演示示例：JDBC_01_Base_DQL_01

7 DML

Data Manipulation Language : 数据操作语言

涉及的关键字有 : delete,update,insert

和查询的操作几乎一样,就是把第4步和第5步更改一下

       Connection conn = null;

       Statement stmt = null;

       ResultSet rs = null;

       try {

           // 1 加载驱动

           Class.forName("com.mysql.jdbc.Driver");

           // 2 创建数据库连接对象

           conn = DriverManager.getConnection(

                  "jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

           // 3 语句传输对象

           stmt = conn.createStatement();

           String sql = "insert into test_jdbc (id,name,money) values (4,'小小',999.9)";

           // sql = "update test_jdbc set money=money+1000 where id=1 ";

           // sql = "delete from test_jdbc where id = 1";

           // 如果是查询,就返回true,不是就返回false,价值不大,所以用的不多,添加,删除,更新都可以用这个方法

           // stmt.execute(sql);

           // 返回值是int,返回影响了几条数据(更改了几条/删除了几条/添加了几条),添加,删除,更新都可以用这个方法

           int count = stmt.executeUpdate(sql);

           System.out.println("影响了 " + count + " 条数据");

       } catch (Exception e) {

           e.printStackTrace();

       } finally {

           try {

              // 关闭资源,从上到下依次关闭,后打开的先关闭

              if (rs != null) {

                  rs.close();

              }

              if (stmt != null) {

                  stmt.close();

              }

              if (conn != null) {

                  conn.close();

              }

           } catch (Exception e2) {

              e2.printStackTrace();

           }

       }

    演示示例：JDBC_02_DML

8. PreparedStatement

添加或者更新的时候,尽量使用 PreparedStatement ,而不是使用Statement

  Statement 和 PreparedStatement 的区别

 

  Statement用于执行静态SQL语句,在执行的时候,必须指定一个事先准备好的SQL语句,并且相对不安全,会有SQL注入的风险

 

  PreparedStatement是预编译的SQL语句对象,sql语句被预编译并保存在对象中, 被封装的sql语句中可以使用动态包含的参数 ? ,

  在执行的时候,可以为?传递参数

 

  使用PreparedStatement对象执行sql的时候,sql被数据库进行预编译和预解析,然后被放到缓冲区,

  每当执行同一个PreparedStatement对象时,他就会被解析一次,但不会被再次编译 可以重复使用,可以减少编译次数,提高数据库性能

  并且能够避免SQL注入,相对安全(把’ 单引号 使用 \ 转义,避免SQL注入 )

8.1 DQL

    使用PreparedStatement 执行查询

public static void load(int id) {

       Connection conn = null;

       PreparedStatement prst = null;

       ResultSet rs = null;

       try {

           // 1 加载驱动

           Class.forName("com.mysql.jdbc.Driver");

           // 2 创建数据库连接对象

           conn = DriverManager.getConnection(

                  "jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

           // 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符

           String sql = "select * from test_jdbc where id = ?";

           // 3 语句传输对象

           prst = conn.prepareStatement(sql);

           // 设置第一个?的值

           prst.setInt(1, id);

           rs = prst.executeQuery();

           while (rs.next()) {

              System.out.print(rs.getInt("id") + "   ");

              System.out.print(rs.getString("name") + "   ");

              System.out.println(rs.getString("money") + "   ");

           }

       } catch (Exception e) {

           e.printStackTrace();

       } finally {

           try {

              // 关闭资源,从上到下依次关闭,后打开的先关闭

              if (rs != null) {

                  rs.close();

              }

              if (prst != null) {

                  prst.close();

              }

              if (conn != null) {

                  conn.close();

              }

           } catch (Exception e2) {

              e2.printStackTrace();

           }

       }

    }

演示示例：JDBC_03_PreparedStatement_load

8.1 DML

使用PreparedStatement 执行增删改,以添加为例

    public static void add(int id, String name, double money) {

       Connection conn = null;

       PreparedStatement prst = null;

       try {

           // 1 加载驱动

           Class.forName("com.mysql.jdbc.Driver");

           // 2 创建数据库连接对象

           conn = DriverManager.getConnection(

                  "jdbc:mysql://127.0.0.1:3306/_06_", "root", "root");

           // 这里我们用? 问号代替值,可以叫占位符,也可以叫通配符

           String sql = "insert into test_jdbc (id,name,money) values (?,?,?)";

           // 3 语句传输对象

           prst = conn.prepareStatement(sql);

           // 设置第一个?的值

           prst.setInt(1, id);

           prst.setString(2, name);

           prst.setDouble(3, money);

           // 返回也是影响的条数

           int count = prst.executeUpdate();

           System.out.println("影响了 "+count+" 条数据");

       } catch (Exception e) {

           e.printStackTrace();

       } finally {

           try {

              // 关闭资源,从上到下依次关闭,后打开的先关闭

              if (prst != null) {

                  prst.close();

              }

              if (conn != null) {

                  conn.close();

              }

           } catch (Exception e2) {

              e2.printStackTrace();

           }

       }

    }

演示示例：JDBC_03_PreparedStatement_add

9. 封装工具类

主要针对2个方面进行改进封装。

以下的代码我们每个方法中都会重复一次，这是没必要的，因为不同的方法其实只是具体执行的SQL语句的内容不同，对于获取连接与释放资源，对应的逻辑是相同的。我们完全可以把这一段逻辑抽取出来，形成独立的类与方法，再在实际应用时调用对应的类和方法就可以了。

1 创建连接这些

2 关闭资源这些

创建链接这些可以这样进行优化

    public static Connection getConnection() throws ClassNotFoundException,

           SQLException {

       String username = "root";

       String password = "root";

       String url = "jdbc:mysql://127.0.0.1:3306/_06_";

       Class.forName("com.mysql.jdbc.Driver");

       Connection connection = DriverManager.getConnection(url, username,

              password);

       return connection;

    }

关闭资源这些可以这样进行优化

因为Connection和Statement/PreparedStatement以及ResultSet都实现了AutoCloseable接口

所以我们可以直接写AutoCloseable

    public static void close(AutoCloseable obj) {

       if (obj != null) {

           try {

              obj.close();

           } catch (Exception e) {

              e.printStackTrace();

           }

       }

    }

演示示例：DBUtil和JDBC_04_TestDBUtil

课堂练习2：使用DBUtil对test_jdbc表进行增删改查各一次

10. Batch多语句操作

  在一次任务中,执行多条数据

10.1 Statement实现

       Connection conn = null;

       Statement stmt = null;

       try {

           conn = DBUtil.getConnection();

           stmt = conn.createStatement();

           stmt.addBatch("insert into test_jdbc (id,name,money) values(21,'stmt多条测试1',99.12)");

           stmt.addBatch("insert into test_jdbc (id,name,money) values(22,'stmt多条测试2',99.22)");

           stmt.addBatch("insert into test_jdbc (id,name,money) values(23,'stmt多条测试3',99.32)");

           stmt.addBatch("insert into test_jdbc (id,name,money) values(24,'stmt多条测试4',99.42)");

           stmt.executeBatch();

           System.out.println("执行成功");

       } catch (Exception e) {

           e.printStackTrace();

       } finally {

           DBUtil.close(stmt);

           DBUtil.close(conn);

       }

    演示示例：JDBC_05_Batch_Statement

10.2 PreparedStatement实现

       Connection conn = null;

       PreparedStatement prst = null;

       try {

           conn = DBUtil.getConnection();

           String sql = "insert into test_jdbc (id,name,money) values(?,?,?)";

           prst = conn.prepareStatement(sql);

           prst.setInt(1, 31);

           prst.setString(2, "prst多条测试1");

           prst.setDouble(3, 11.1);

           prst.addBatch();

           prst.setInt(1, 32);

           prst.setString(2, "prst多条测试2");

           prst.setDouble(3, 21.1);

           prst.addBatch();

           prst.setInt(1, 33);

           prst.setString(2, "prst多条测试3");

           prst.setDouble(3, 31.1);

           prst.addBatch();

           prst.executeBatch();

           System.out.println("执行成功");

       } catch (Exception e) {

           e.printStackTrace();

       } finally {

           DBUtil.close(prst);

           DBUtil.close(conn);

       }

       演示示例：JDBC_05_Batch_PreparedStatement

课堂练习3：使用Batch对test_jdbc表进行多行数据增删改各一次