Java基础之MySQL数据库

数据库的操作命令：

• 创建数据库hello：create database [if not exists] hello--[]里的内容为可选的
• 删除数据库hello：drop database [if exists] hello --[]里的内容为可选的
• 查看所有的数据库：show databases

数据库建表操作：

--auto_increment 表示自增
--comment 表会备注，注意，这里的 ` 符合不是单引号！
--engine是引擎，默认是innodb 
create table [if not exites] `student` {
  `id` int(4) not null auto_increment comment '学号',
  `name` varchar(20) not null default '匿名' comment '姓名',
  `birthday` datetime default null comment '出生日期',
  primary key (`id`)
}engine=innodb default charset=utf8

查看创建hello数据库的语句：show create database hello --创建表的类似。

数据表存在的位置

物理位置：在mysql的data目录下。

数据库表的操作:

• alter关键字与表的操作有关

--修改表名：alter table 旧表名 rename as 新表名
alter table teacher rename as teacher1
--增加表的字段 alter table 表名 add 字段名 属性
alter table teacher1 add age int(2)
--修改表的字段（重命名，修改约束）两种用不同的关键字！
alter table teacher1 change age age1 int（5） --重命名，modify:修改

alter table teacher1 modify age varchar（5） --修改约束 

--删除表的字段
alert table teacher1 drop age1
--删除表
drop table [if exists] teacher1
--表添加外键
--constraint：约束，references：引用
alter table `student` add
constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
--`FK_`这是固定的格式，后面接外键的名称

Tips：上诉的外键操作都是物理外键，不建议这样操作，存在外键和级联会导致delete或update表时考虑外键约束，令人头疼，利用程序来实现表的外键，表只用来存数据。

DML语言（常用）

--对表字段插入具体值的操作
insert into `teacher` (`id`,`name`,`age`) values('001','张三','18');--如果表的字段不写则会一一对应
--对表字段的内容更新的操作
update `teacher` set name ='cwx' where id=2;-- 别忘记加条件！没加则所有的name都为cwx，直接爆炸。
--删除数据
delete form `teacher` where id=1;
delete from `teacher`; --删除所有表内容
truncate table `teacher`;--清空表

Tips：delete 和 truncate的区别，truncate会清空自增的数据且不会影响事务，delete就是纯删除。

数据库引擎delete删除的问题，重启数据库发生的现象：

• InnoDB ：自增会从1开始（存在内存当中，断电即失）
• MyISAM ：继续从上一个增量开始（存在文件中，不会丢失）

DQL查询数据（重点）

• 查询指定字段：select concat("姓名：",name) from teacherconcat(a,b)：实现a，b字符串的合并。
• 发现重复数据，去重：select distinct studentId from result,利用distinct关键字去重。

模糊查询（比较运算符）

• 运算符
  • between：a between b and c --->若a在b和c之间，则返回true
  • Like ：a like b --->SQL匹配，如果a匹配b，则结果为真
  • In：a in(a1,a2,a3...) --->假设a在a1或a2或a3中，结果为真

Tips：like 一般可以结合 %（代表0个或多个字符） 或 _（代表一个字符）字符使用。如：select studentName from student where name like '陈%';(查找姓陈的名字)

联表查询Join

• 可以分为7种join，本质是3种（inner join 、left join、right join）

   --这里是根据两个表都有相同的studentNo查询出成绩和姓名
  select s.studentNo,studentName,subjectNo,studentResult from student as s
   inner join results as r where s.studentNo = r.studentNo;
  --这里用s.studentNo是因为两表中都有studentNo，所以必须给出要查询哪个表的studentNo
  

  • inner join ：如果两表中至少有一个匹配，则输出查询值，若没有则为null
  • left join ：会从左边表输出查询的所有值，即使右边没有，若没有则为null
  • right join：会从右边表输出查询的所有值，即使左边没有，若没有则为null
• 举例：三表查询

--先实现前两个表的查询，在利用学科的subjectNo进行三表查询
select s.studentNo,studentName,subjectName,studentResult from student as s
left join result on s.studentNo = r.studentNo
inner join subject as sub where r.subjectNo = sub.subjectNo 

分页和排序

• 分页用limit ：limit (起始下标),(pagesize);

--查询数据库这门课的成绩，升序，desc（降序），且从第0个数据开始，到第5个，5代表输出的个数
select `source` from result
where subjectName = '数据库'
order by `source` asc
limit 0,5;  --想要看下一页，则改为limit 5,5 第N页为limit (N-1)*5,5

Tips：order by 必须在where 后面，limit 必须在order by 后面！

子查询（在where 条件里面加计算得到的结果，如select ...from...）

• 一般子查询实现的功能联表查询也能够实现。

聚合函数

• count

select count(studentName) from student --查询学生的人数
select count(*) from student   --查询学生的人数
select count(1) from student   --查询学生的人数

Tips：三者的区别，count(指定列) 会忽略null值，count(*)或count(1)不会忽略null，其中count(*)是查询所有列，本质是计算行数，count(1)是包括了忽略所有列，将所有的字段变成1，按行查。

• AVG(xx) 、SUM(xx) 、MAX(xx) 、MIN(xx) --->求平均、求和、求最大、求最小
• group by(分组)

--查询不同课程的平均分，且平均分要大于80
select subjectName,AVG(studentResult) from result r 
inner join subject sub
group by r.subjectNo  --分组展示，如果没有加group by 则只会展示一列
having AVG(studentResult)>80;

• having(分组后的条件判断)
  • 如果分完组还想要进行筛选判断，则利用having (条件) 关键字，在group by后面。

select完整语法

select [all | distinct] xxx from table_name
[left | right | inner join table_name]    --联合查询
[where xxx]      --指定结果满足的条件
[group by xxx]   --进行分组
[having xxx]     --分组后在进行条件判断
[order by xxx]    --排序
[limit a,b]      --进行分页处理

Tips：这里的顺序是规定死的，各个关键字必须按顺序要求排好。

事务(Transaction)

• 什么是事务？
  • 简单来说就是，要么都成功，要么都失败！

Tips：MySQL默认开启事务。

• 事务原则（ACID原则）：
  • 原子性(Atomicity)，隔离性(Isolation)，持久性(Durability)，一致性(Consistency).
• 隔离性会产生的问题：
  • 脏读：一个事务读取了另一个没有提交的事务。
  • 虚读（幻读）：在同一个事务内，读取到了别人插入的数据，导致前后读出的数据不一致。
  • 不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有执行结束，另外一个事务也访问该同一数据，那么在第一个事务中的两次读取数据之间，由于第二个事务的修改第一个事务两次读到的数据可能是不一样的，这样就发生了在一个事物内两次连续读到的数据是不一样的，这种情况被称为是不可重复读。
• 提交和回滚
  • commit：提交给事务，成功提交之后就变为持久的了，不能在进行回滚！
  • rollback：回滚事务，在没提交之前可以回滚到原始数据。

索引

索引是帮助MySQL高效的获取数据的数据结构，它的本质是数据结构。在数据量庞大的时候，查询效率明显提高。

• 索引的分类
  • 主键索引（primary key）：唯一标识。
  • 唯一索引（unique key）：避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引。
  • 常规索引（key / index）：默认的，index，key关键字来设置。
  • 全文索引（fulltext）：快速定位数据。

索引原则

• 建议阅读--->索引原则深层Hash类型的索引（过程很痛苦，但要坚持啊！）
• 建议阅读--->Java实习生面试复习(六)：MySQL索引
• 建议阅读--->为什么MySQL用B+树做索引？
• 建议阅读--->什么是B树？为啥文件索引要用B树而不用二叉查找树？

三大范式（规范数据库的设计）

• 第一范式：要求数据库表的每一项都是不可分割的原子数据项（即：表中不能套表）。
• 第二范式：满足第一范式且要求表中的每一列都和主键相关，而不能只和主键的一部分相关（即：一张表只能描述一件事情）。
• 第三范式：满足第一和第二范式，且要求表中的每一项数据直接和主键相关，消除传递依赖。

Tips：考虑到性能的问题，一般在真实的项目中并不会这样做，例如在阿里公司就规定了关联查询表不得超过三张，也有可能故意给某些表增加一些冗余的字段（从多表查询到单表查询）。

JDBC

• 由于应用程序并不能直接和数据库打交道，需要引入数据库的驱动，现在数据库的种类较多，驱动也多，不好统一管理，所以SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库）规范，俗称JDBC，这些规范的实现由具体的厂商去做，对于开发人员， 只需要掌握JDBC接口的操作即可。
• 我的第一个jdbc程序

public class JdbcDemo{
  public static void main(String[] args )throw ClassNotFoundException{
    //1、加载数据库驱动
    //  DriverMannger.registerDriver(new Driver());原始的方法，先注册一个驱动，不建议使用
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2、用户信息和URL
    String url = "jdbc:mysql://localhost:3306/数据库名?useUnicode=true&&characterEncoding=utf-8&&serverTimezone=UTC";
    String username = "root";
    String password = "123456";
    //3、连接数据库
    Connection connection = DriverMannger.getConnection(url,username,password);
    //4、创建执行SQL的对象
    Statement statement = connection.createStatement();
    //5、执行SQL语句
    String sql = "select * from 表名";
    ResultSet resultSet = statement.executeQury(sql);
    //6、返回结果集并输出
    while(resultSet.next()){
    System.out.println("id="+resultSet.getObject("id"));
    System.out.println("name="+resultSet.getObject("name"));
    System.out.println("age="+resultSet.getObject("age"));
}

    //7、释放连接
    resultSet.close();
    statement.close();
    connection.close();

}
}

connection就是相当于数据库的对象，可以实现事务的提交和回滚，如，connection.commit(),connection.rollback()，但前提是要开启事务connection.setAutoCommit(false)。

• 为了是程序减少耦合，可以将连接数据库的操作放在utils包中

package 数据库相关.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
       private static String driver=null;
       private static String url=null;
       private static String username=null;
       private static String password =null;
   static {
       //获得properties文件资源
       InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("JdbcConnection.properties");
       Properties properties = new Properties();
       try {
           properties.load(in);

           driver=properties.getProperty("driver");
           url= properties.getProperty("url");
           username=properties.getProperty("username");
           password=properties.getProperty("password");
           //驱动只需要注册一次就可以
           Class.forName(driver);
       } catch (IOException e) {
           e.printStackTrace();
       } catch (ClassNotFoundException e) {
           e.printStackTrace();
       }

   }
   //获取连接的方法
   public static Connection getConnection() throws SQLException {
       return   DriverManager.getConnection(url, username, password);

   }
   //释放connection资源
   public static void close(Connection con, Statement st, ResultSet rs){
       if(con!=null){
       try {
       con.close();
       } catch (SQLException e) {
           e.printStackTrace();
       }
       }
       if(st!=null){
           try {
               st.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }

}

-->这个是JdbcConnection.properties文件
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/students?useSSL=false&serverTimezone=UTC
username=root
password=123456

package 数据库相关;
import 数据库相关.utils.JdbcUtils;
import java.sql.*;
//主函数，实现连接数据库，这里是将连接和创建驱动抽取出来了，直接利用utils包中的方法
public class ContentMysql {
    public static void main(String args[]) throws SQLException {
        Connection conn=null;
        Statement sql = null;
        ResultSet rs =null;
        try {
            conn=  JdbcUtils.getConnection();
            sql=conn.createStatement();
            rs=sql.executeQuery("SELECT * FROM mess"); //查询mess表
            while(rs.next()) {
                String number=rs.getString(1);
                String name=rs.getString(2);
                Date date=rs.getDate(3);
                float height=rs.getFloat(4);
                System.out.printf("%s\t",number);
                System.out.printf("%s\t",name);
                System.out.printf("%s\t",date);
                System.out.printf("%.2f\n",height);
            }


        }
        catch(SQLException e) {
            System.out.println(e);
        }finally {
            JdbcUtils.close(conn,sql,rs);
        }
    }

}

Tips：记住啊，properties文件应该放在src目录文件下，别乱放！否则读取不到。

SQL注入的问题（本质是SQL存在拼接）

• SQL注入存在漏洞会被攻击导致数据泄露，例如：

在登录场景下，用户输入用户名为'or '1=1 ,密码也为'or '1=1，这就导致后台判断为密码是'' or '1=1'判断为真，从而出现问题。

• PrepareStatement应运而生
  • 可以防止SQL注入，效率也比statement更高，与statement的区别：

  //preparestatement与statement的使用区别不大，一般连接都一样
    conn=  JdbcUtils.getConnection();
      sql=conn.prepareStatement(select * from mess where id=?);  //区别所在,使用？占位符代替参数
      sql.setInt(1,4);    //手动给参数赋值
      sql.executeUpdate();//现在才执行语句
   
  

数据库连接池

由于我们原先是使用数据库连接--->执行完毕--->释放，每次如果都是这样操作将会大大浪费系统资源，于是使用连接池技术。

• 池化技术：预先准备一些资源，如果有连接就过来连接语言准备好的。
• 最小连接数：设置最小的连接数，支持最小的连接资源。
• 最大连接数：设置最大连接数，业务承载上线，不是并发。
• 等待超时：设置等待时间，如果一个连接等待时间超过设置的值则取消连接。
  自己编写连接池（实现DataSource接口）
• 总结：用了数据库连接池就不需要我们在去写连接了，只是不用写连接，其他地方和我上面写的利用properties文件进行连接数据库类似，我们利用连接池主要是用它的池化技术，操作步骤上面没有什么太大的改变，使用连接池也要配置properties文件，但参数名必须使用它所规定的，不然源码扫描不到！