【基础知识】_MYSQL数据库与JDBC

发表于 | 分类于

MYSQL数据库与JDBC

Junit

  • Junit使用步骤：
    • 定义一个测试类
    • 定义一个测试方法，这个方法必须可以独立运行。
    • 在该测试方法前加@Test
    • 导入Junit依赖环境
  • @Before修饰的方法会在测试方法之前被自动执行，@After修饰的方法会在测试方法执行忠厚自动被执行。

反射

  • 获取字节码Class对象的三种方式：

    • Class.forName(“全类名”)：将字节码文件加载进内存，并返回Class对象。全类名：包名.类名。
    • 类名.class：通过类名的属性class获取Class对象。
    • 类的实例化对象.getClass()：getClass()方法可以用于获取Class对象，Object类中就定义了该方法。

  • 获取properties配置文件中键值对信息的一种方法：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    Properties pro = new Properties();
    //创建Properties配置文件类对象。

    ClassLoader classLoader = ReflectTest.class.getClassLoader();
    //获取class目录下的配置文件的方式：
    //classLoader是本字节码文件ReflectTest.class的类加载器，它可以找到本项目目录中的类文件和配置文件。

    InputStream is = classLoader.getResourceAsStream("pro.properties");
    //类加载器的getResourceAsStream()方法可以获取配置文件中对应字节流.

    pro.load(is);
    //将该配置文件字节流转换为一个集合。
    //该集合的内部结构即为HashTable，就是不同的key与value一一对应。

    String className = pro.getProperty("className");
    String methodName = pro.getProperty("methodName");
    //获取配置文件集合中key对应的value，这了的key一定要输入正确。

注解

  • 注解本质上就是一个接口，该接口默认继承Annotation接口。

MySQL

sql分类

  • DDL(Data Definition Language)数据定义语句：用来对数据库、表、列等数据库对象进行增删改操作。
  • DML(Data Manipulation Language)数据操作语句：用来对数据库中表中的数据进行增删改操作。
  • DQL(Data Query Language)数据查询语句：用来查询数据库中表中的数据。
  • DCL(Data Control Language)数据控制语句：用来定义数据库访问权限和安全级别等。

DDL操作

(tips：以下sql语句实例中的括号表示可有可无)

  1. 创建数据库:

    create database (if not exists) 数据库名称 (character set 字符集名);

  2. 查询所有数据库名称:

    show databases;

  3. 修改数据库的字符集:

    alter database 数据库名称 character set 字符集名称;

  4. 删除数据库:

    drop database (if exists) 数据库名称;

  5. 查询当前正在使用的数据库名称:

    select database();

  6. 指定使用某个数据库:

    use 数据库名称;

  7. 查询某个数据库中所有表的名称：

    show tables;

  8. 查询表结构(列名和数据类型):

    desc 表名;

  9. 创建表:

    create table 表名(列名1 数据类型1,…,列名n 数据类型n);

    e.g. money double(5,2):数据类型为总位数5位，小数点后两位；name varchar(20):数据类型最大能包含20个字符。

  10. 删除表:

    drop table (if exists) 表名;

  11. 修改表名:

    alter table 表名 rename to 新的表名;

  12. 修改表的字符集:

    alter table 表名 character set 字符集名称;(gbk或者utf8)

  13. 给表添加一列:

    alter table 表名 add 列名 数据类型;

  14. 修改表中列的名称和类型:

    alter table 表名 change 列名 新列名 新数据类型;(同时修改名称和类型)

    alter table 表名 modify 列名 新数据类型;(只修改类型)

  15. 删除表中的列:

    alter table 表名 drop 列名;

DML操作

  1. 往表中添加数据:

    insert into 表名(列名1,…,列名n) values(值1,…,值n);

    • 如果表名后不定义列名，则默认给所有列添加值
    • 添加多行可以直接用 values(值1,…,值n),(值1,…,值n),…,(值1,…,值n) 的形式

  2. 删除表中数据:

    delete from 表名 where 条件;

    • 这个条件主要用于确定是哪一行，比如id = 1
    • 可以不加where 条件，则删除表中所有记录

  3. 修改表中数据:

    update 表名 set 列名1=值1,列名2=值2,… where 条件;

    • 可以不加where 条件，则所有指定列中的值都改变

DQL操作

  1. 查询表中数据:

    select 列名列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的字段 order by 排序 limit 分页限定;

    • 列名可以是一个运算表达式，比如列名1+列名2，得到的就是两列数据相加的结果

    • 如果要查询所有列的数据值，则可以用*来替代列名列表

    • distinct可以加在select后面用于去除重复的结果集

    • ifnull(列名,表达式)可以加在列名列表中，表示如果该列中某个数据为null时，则用表达式表示该数据

    • as可以加在列名和表名后面用来给列名和表名起别名，其中as可以省略

    • where子句后面跟的条件可以是：

      1. <、>、<=、>=、=、!=
      2. BETWEEN…AND
      3. IN (集合)
      4. LIKE :_表示单个任意字符，%表示任意个任意字符，e.g. LIKE”马%”表示所有姓马的，LIKE”%马%”表示只要包含马字就行
      5. IS NULL
      6. and 或 &&
      7. or 或 ||
      8. not 或 ！

    • order by 排序列名1 排序方式1,排序列名2 排序方式2…

      排序方式有ASC表示升序，DESC表示降序；排序列名1的数据相等时，才会再将相等的行按排序列名2排序

    • 聚合函数：将一列数据作为一个整体，进行纵向的计算，聚合函数通常放在查询语句的列名中，作为一个查询结果列：

      count 、max、min、sum、avg(计算平均值)，e.g. select count(ifnull(math,0)) from student:计算学生中有math的行的个数，其中包括math数据为null的行；select avg(math) from student:计算学生数学成绩平均值。

    • group by 关键字可以将查询结果按照某个字段或多个字段进行分组，字段中值相等的为一组

      group by与聚合函数共同使用时，先进行group by分组，再按组内计算聚合函数结果

    • having 条件表达式：与group by搭配使用，主要用来限制分组查询结果的显示，符合条件表达式的行才会被显示

    • limit 每页的第一行在整个表中的行索引,每页显示的行数：用于分页查询，数据库中的表是没有页这个概念的，但是查询结果表格可以有页这个概念

      每页的第一行在整个表中的行索引 = (当前的页码 - 1) * 每页显示的行数

约束

  • 非空约束：not null，某一列的值不能为null

    e.g. create table stu(id int not null);

  • 唯一约束：unique，某一列的值不能重复

    e.g. create table stu(id int unque);

  • 主键约束：primary key，非空并且唯一，一张表只能有一个主键，主键是表的唯一标识

    e.g. create table stu(id int primary key);

  • 自动增长：auto_increment，在添加某一列数值类型的值时，只需要添加null，表会自动将该位置赋值为上一行同列中的数值+1。也可以自己随意添加一个值，然后下一行的自动增长就在此基础上+1。

  • 外键约束：foreign key，让表与表产生关系，从而保证数据的正确性

    • 创建表时，添加外键:

      create table 表名 {…外键列名 外键数据类型 constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)};

      • 此处的外键名称不对应实际数据，主要用于后面删除外键
      • 有foreign key的表叫从表，被从表中外键references的表叫主表
      • 在从表中添加行时，外键列添加的值一定要是主表列中存在的值，否则无法添加成功；同理删除或者修改主表列中的值时，应先将对应外键列中的值设置为空或者其他值，否则会报错

    • 删除外键:

      alter table 表名 drop foreign key 外键名称;

    • 创建表之后，再添加外键:

      alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称);

  • 外键约束_级联操作：外键约束中的主表列中的数据做出删除或者修改操作时，对应的从表中的外键列中数据也自动做出改变(作用其实就是为了简化上文中加粗下划线文字部分所提到的情况)

    • 添加级联操作:

      alter table 表名 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade on delete cascade;

      • 要在已经建立好外键约束的表上添加级联操作，只能先删除外键，再一并添加外键和级联操作
      • on update cascade和on delete cascade分别为级联更新和级联删除，它们可以一起添加；级联删除主要是指当我们删除主表中某一行，从表中对应外键列所在行也会被自动删除。

多表关系

  • 一对一关系实现：在任意一方添加唯一外键指向另一方的主键
  • 一对多关系实现：将多的一方作为从表，建立外键，指向一的一方的主键
  • 多对多关系实现：需要借助第三张中间表，中间表至少包含两个列，这两个列都作为第三张表的外键，分别指向前两张表的主键

多表查询

一次查询多张数据库表:

select 列名1,…,列名n from 表名1,…,表名n where 查询条件;

当无查询条件，查询结果为笛卡尔积，其行数为所有表的行数的乘积，其中大部分结果都是无用的错误结果。可以通过设置合适的查询条件来消除无用的错误结果，主要得到三种较好的查询方式：内连接查询、外连接查询、子查询。

  • 内连接查询：从结果表中删除没有通过外键连接相匹配的所有行

    隐式语法：

    select 列名1,…,列名n from 表名1,…,表名n where 查询条件;

    e.g. select t1.’id’,t1.’name’,t2.’age’ from employee t1,department t2 where t1.’id’=t2.’id’;

    • 上例中t1和t2分别为两表的别名
    • 列名要加上表名前缀和引号
    • 上例中的查询条件为两表中的id相等

    显式语法：

    select 列名1,…,列名n from 表名1 (inner) join 表名2 on 查询条件;

    • 隐式和显式写法查询出来的结果一样，只是写法的不同
    • inner可加可不加

  • 外连接查询：分为左外连接和右外连接。以一张表为基表，该基表的内容会全部显示，然后加上左、右连接表中满足查询条件的内容。如果基表中的数据在另一张表中没有记录，则结果集同一行中连接表部分的数据显示为null。

    • 左外连接:

      select 列名列表 from 表1 left (outer) join 表2 on 查询条件;

      查询的是表1的所有数据以及两表的交集部分

    • 右外连接:

      select 列名列表 from 表1 right (outer) join 表2 on 查询条件;

      查询的是表2的所有数据以及两表的交际部分

  • 子查询：子查询就是查询中嵌套查询，子查询结果可以是单行单列、多行单列、多行多列的

    e.g. select * from emp where emp.salary < (select avg(salary) from emp);

DCL操作

  • 添加用户:

    create user ‘用户名‘@’主机名’ identified by ‘密码’;

    • 这里的主机名指的是电脑的ip地址，如果直接写成%，则代表任何电脑都可以用该用户名访问该服务器。

  • 删除用户:

    drop user ‘用户名‘@’主机名’;

  • 修改用户密码:

    update user set password = password(‘新密码’) where user = ‘用户名’;

  • 查询用户:

    use mysql;

    select * from user;

  • 查询某个用户的权限:

    show grants for ‘用户名‘@’主机名’;

  • 授予某个用户权限:

    grant 权限列表 on 数据库名.表名 to ‘用户名‘@’主机名’;

    • 可以通过查询root用户的权限，看看具体有哪些权限可以授予

  • 撤销权限:

    remove 权限列表 on 数据库名.表名 from ‘用户名‘@’主机名’;

JDBC

JDBC基本使用步骤

  1. 导入驱动jar包:创建一个文件夹命名为libs，将jar包放到该文件夹中，右键该文件夹–>add as library。这样才是真正把jar包加入到项目中了，使项目程序能使用jar包中的api。

  2. 注册驱动。(其实就是通过反射获取libs中的Driver类的Class类对象，将其放入jvm内存中)

  3. 获取数据库连接对象。

  4. 定义sql语句。(最后不用加分号。)

  5. 获取sql对象Statement。

  6. 执行sql。

  7. 释放资源。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    Class.forName("com.mysql.jdbc.Driver");
    //注册驱动。(其实就是通过反射获取libs中的Driver类的Class类对象，将其放入jvm内存中)

    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","root");
    //获取数据库连接对象。

    String sql = "update stu set name = '金子涵' where id = 3";
    //定义sql语句。(最后不用加分号。)

    Statement stmt = conn.createStatement();
    //获取sql对象Statement。

    int count = stmt.executeUpdate(sql);
    //执行sql。

    System.out.println(count);
    //处理结果。

    stmt.close();
    conn.close();
    //释放资源。
  • 在for、while循环语句中，在每次循环内部创建的局部变量的寿命就只有该次循环，不会存活到下一次循环，所以在下一次循环中可以创建一个新的同名局部变量。

加载配置文件

  • 使用类加载器动态加载配置文件

    1
    2
    3
    Properties pro = new Properties();
    InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
    pro.load(is);
    • 可以是放在项目中任意目录下

  • 使用字符文件读取类+绝对路径加载配置文件**

    1
    2
    Properties pro = new Properties();
    pro.load(new FileReader("E:\\files\\IntelliJ IDEA\\037.1JDBCTemplate\\dataSource\\src\\druid.properties"));
  • 字符串中\\表示\，或者把\\换成/也可以。
  • 此处第二个文件夹名为IntelliJ IDEA，可以带空格。

JDBC连接池&JDBCTemplate

  • 获取Druid数据库连接池对象的基本步骤

    1. 导入jar包

    2. 定义properties配置文件

    3. 加载配置文件

    4. 获取连接池对象

    5. 获取连接对象

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      Properties pro = new Properties();
      InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
      pro.load(is);
      //加载配置文件

      DataSource ds = DruidDataSourceFactory.createDataSource(pro);
      //获取连接池对象

      Connection conn = ds.getConnection();
      //获取连接对象

  • 使用Template的基本步骤

    1. 导入jar包

    2. 创建JdbcTemplate对象

    3. 调用JdbcTemplate对象的方法来完成CRUD操作

      1
      JdbcTemplate template = new JdbcTemplate(ds);
      • JdbcTemplate类的构造函数的输入参数类型为数据库连接池
      • 与不使用Template对比可以发现，代码简单了很多，连接的获取、结果集的操作、资源的释放、连接的归还等操作都已经被JdbcTemplate类封装好了，不用我们再去写。

  • JdbcTemplate的方法

    • update():用于执行DML语句，对表中数据进行增、删、改操作。

      1
      2
      3
      4
      String sql = "update bank set money = 5000 where id = ?";
      int count = template.update(sql,2);
      //template.update()方法的输入为sql语句和sql语句中的?实参列表。
      //返回值为sql增删改语句对数据库中表的影响行数。

    • queryForMap():用于对表中数据进行查询操作，将查询结果封装为map集合，将列名作为key，将数据值作为value。使用这个方法进行查询的sql语句的查询结果行数只能是1。

      1
      2
      3
      4
      5
      6
      String sql = "select * from student where id = ?";
      Map<String,Object> map = template.queryForMap(sql,1);
      //1.Map是一个接口，这里用于声明一个Map引用变量，在声明时一定要确定泛型，
      //该Map中表名作为key，各种不同数据类型作为value，所以key声明为String，value声明为Object。
      //2.这个Map引用变量指向的肯定是Map的一个实现类对象，具体是怎样的实现类我们不知道，
      //也不需要知道，JdbcTemplate已经帮我们把该实现类的基本方法接口编写封装好了。

    • queryForList():用于对表中数据进行查询操作，将查询结果封装为List集合。使用这个方法进行查询的sql语句的查询结果行数应大于1，实际上就是把查询结果的每一行封装成一个Map集合。

      1
      2
      3
      4
      String sql = "select * from student";
      List<Map<String,Object>> list = template.queryForList(sql);
      //这里的list的节点就是JdbcTemplate封装好的Map实现类对象，很多方法接口都可以套用。
      //这里的List也是官方接口，list引用变量具体指向的实现类对象结构也是由JdbcTemplate定义封装好的。

    • queryForObject():用于查询，并将查询结果封装为定义好的对象。

      1
      2
      3
      String sql = "select count(id) from student";
      Integer total = template.queryForObject(sql,Integer.class);
      //template.queryForObject()方法的输入除了sql语句，还有一个要返回的结果类型的Class对象。

    • query():用于查询，并将结果封装为JavaBean对象。

      1
      2
      3
      4
      5
      6
      7
      8
      String sql = "select * from student";
      List<Student> list = template.query(sql, new BeanPropertyRowMapper<Student>(Student.class));
      //这里的query方法同样是直接得到一个List实现类对象，只不过其中节点是JavaBean对象。
      //使用BeanPropertyRowMapper实现类，可以完成数据到JavaBean的自动封装。

      //(注意这里的被查询表student中如果有值为null的数据，则Student类中的数据类型
      //必须是引用数据类型的封装类，不能是基本数据类型，比如不能是int，要写成Integer，
      //因为int不能等于null，而Integer对象可以等于null。)
      • 一般我们使用BeanPropertyRowMapper类来作为query()方法的输入参数，这个类可以将查询结果的每一行数据自动封装成JavaBean实例对象。