typora-copy-images-to: imgs

1)sql 4个分类
  DDL:数据定义语言
      create table 表名(字段1,类型1,....);
      alter table  表名 add/modify/change/drop .....
      show create table 表名;
      truncate table 表名;
  DML:数据操作语言,主要针对表中的数据的增删改
      insert into 表名 (字段1,....) values(v1,....),(v2,....);
      delete from 表名 where 条件;
      update 表名 set 字段1=v1,.... where 条件;
  DQL:数据查询语言,主要对表中的数据的查询 select
      select 字段1,字段2,.... from 表名 where 条件;
      比较运算符:> < = >= <= <> !=
      逻辑运算符:and(&&),or(|),not(!)
      order by 字段1,字段2,.....(排序规则:先按照第一个字段排序,如果第一个字段中出现相同的值,则按照第二个进行排序,否则以此类推)
      group by 字段,...  having 条件;
      like '通配符字符串'; %:任意多个;_:表示仅匹配一个字符
      between v1 and v2:查询字段在v1和v2之间的数据,包含边界值;
      in(v1,v2,...)
      limit offset,row_count;-- mysql中行的索引位从0开始
      多表关联查询:
          内连接查询:1)隐式内连接 2)显式内连接(select * from t1 inner join t2 on 条件 )
          外连接查询:1)左外连接(select * from t1 left outer join t2 on 条件) 
                    2)右外连接(select * from t1 right join t2 on 条件)
          子查询:一个sql查询的结果作为另个一sql查询的组成本分;
              比如:查询成绩最高的学生是谁?
                   select * from student where score=(select max(score) from student);

  DCL: 数据控制语言。创建管理员用户和分配及撤销权限
      -- 创建用户
      create user '用户名'@‘主机名’ identified by '密码';
      -- 给用户授权
      grant 权限1,权限2,....on 库.表 to '用户名'@‘主机名’ ;
      -- 撤销权限
      revoke 权限1,权限2,....on 库.表  from '用户名'@‘主机名’;
 2)事务
   在数据库中事务是一组逻辑单元,要么都成功,要么都失败;
   4大特性:
        隔离性
        原子性
        一致性
        持久性
    事务并发问题:
        脏读
        不可重复读
        幻读
    mysql数据隔离级别:
        读未提交: 脏读 不可重复读 幻读
        读已提交:不可重复读 幻读
        可重复读:幻读
        串行化
   3)索引
      主键索引
      唯一索引
      普通索引
      组合索引
      全文索引
      hash索引
      空间索引
    4)索引结构
       b+tree
    5)视图
      create view 名称 as 查询语句...;
    6)存储过程
      delimiter $
      create procedure 名称([in/out/inout] 变量名称 类型,....)
      begin
       sql逻辑;
      end$
     7)引擎
       innodb,myisam,memory等
     8)锁
       从操作粒度: 表锁和行锁
       从操作的类型:读锁(s/共享锁)和写锁(排它锁)
       myisam:对于读锁,对读操作共享,对写操作阻塞;对于写锁,都阻塞;

       innodb:行锁,对其他事务对相同行的写操作阻塞;
       表:索引失效导致表锁
       间隙锁:一个事务中写入条件扫中的数据进行加锁,同事存在的间隙也会被加锁;

1. 第五章 JDBC

1.1. 5.1 JDBC的由来

1.2. 1.JDBC概念

1.2.1. 1.1 JDBC的由来

image-20201020185614120

sun公司定义了一套操纵数据库的接口规范(JDBC),具体的实现由不同的数据库服务厂商;

一般程序访问一些硬件资源都需要安装驱动,比如我们使用声卡\网卡 就需要安装声卡驱动和网卡驱动,如果我们使用U盘,那么就需要安装U盘相关的驱动;

同样的道理,如果我们使用java访问数据库,也需要安装相关的驱动包;

数据库运行在不同的操作系统下,比如windows,linux,unix,macos等等,同时数据库又有不同的厂家提供,比如mysql,oracle,sqlserver等,如果对于程序员来说,针对不同的数据库在不同的操作系统下,安装不同的驱动软件,那么成本会很高;

所以,sun公司制定了一套规则,这套规则就是用来java连接数据库的,然后不同的厂商自己实现定义个规则即可,对于程序员只了解规则核心内容,即可对不同的数据库进行操作,这套规则叫做JDBC;

1.2.2. 1.2 JDBC的好处

  1. 我们只需要会调用JDBC接口中的方法即可,使用简单
  2. JDBC有关的类和接口:都在java.sql 和 javax.sql(扩展包) 包下
  3. 方法体由具体的数据库厂商来完成的
  4. 使用同一套Java代码,进行少量的修改就可以访问其他JDBC支持的数据库

小结:

1.说出JDBC概念?

​ jdbc是一套操纵数据库的接口规范,对应的接口实现由各大数据库服务厂商;

1.3. 2、JDBC核心API的介绍

2.1 JDBC会用到的包

  1. java.sql:JDBC访问数据库的基础包,在JavaSE中的包。如:java.sql.Connection
  2. javax.sql: JDBC访问数据库的扩展包(x表示extension,扩展)
  3. 数据库的驱动,各大数据库厂商来实现。如:MySQL的驱动:com.mysql.jdbc.Driver

2.2 JDBC四个核心对象

这几个类都是在java.sql包中(1类3接口)

  1. DriverManager(类): 数据库驱动管理类。这个类的作用:1)注册驱动; 2)创建java代码和数据库之间的连接,即获取Connection接口;
  2. Connection(接口): 是一个接口, 建立数据库连接的一个接口。作用:建立数据库和java代码之间的连接。表示与数据库创建的连接
  3. Statement(接口)、PreparedStatement(接口) (解决安全隐患问题,比如sql注入的问题): 数据库操作,向数据库发送sql语句。执行SQL语句的对象
  4. ResultSet(接口): 结果集或一张虚拟表。 Statement 发送sql语句,得到的结果 封装在 ResultSet 中。

2.3 JDBC访问数据库的步骤

  1. 由DriverManager注册驱动程序(就是sum公司提供接口的具体实现类)
  2. 创建和数据库的连接对象Connection
  3. 由客户端发送SQL语句给服务器执行,SQL语句封装成Statement对象
  4. 查询到的结果集封装成ResultSet对象
  5. 在客户端可以从ResultSet中取出数据,处理结果集
  6. 释放资源,关闭连接对象

1.4. 3、JDBC注册驱动

数据准备:

-- 用户表
create table user (
  id int primary key auto_increment,
  username varchar(30) unique not null,
  password varchar(30)
);

insert into user(username, password) values('zhangsan','123');
insert into user(username, password) values('lisi','123');
insert into user(username, password) values('wangwu','123');

select * from user;

1.创建测试工程,并导入jar包

image-20201021093158870

image-20201021093425086

2 注册驱动

2.1 DriverManager显式注册
static void registerDriver(Driver driver);//向 DriverManager 注册给定驱动程序。 

//获取所有注册的驱动类
static Enumeration<Driver> getDrivers() 
          获取带有当前调用者可以访问的所有当前已加载 JDBC 驱动程序的 Enumeration。
public class TestJDBC {

    @Test
    public void test1() throws SQLException {
        //1)注册驱动
        DriverManager.registerDriver(new Driver());
        //获取所有已经注册了的驱动类
        Enumeration<java.sql.Driver> drivers = DriverManager.getDrivers();
        while (drivers.hasMoreElements()){
            System.out.println(drivers.nextElement().toString());
        }
    }
}

1604454954717

说明:对于mysql不建议使用DriverManger的显式注册方式,会造成驱动2次注册,造成内存浪费;

注册2次的原因:

public class Driver  implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            //在类被加载的时候,静态代码块就注册了一次了
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}
2.2 隐式注册-推荐使用
//隐式注册
Class.forName("com.mysql.jdbc.Driver");
    //隐式注册方式 推荐
    @Test
    public void test2() throws SQLException, ClassNotFoundException {
        //1)注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //获取所有已经注册了的驱动类
        Enumeration<java.sql.Driver> drivers = DriverManager.getDrivers();
        while (drivers.hasMoreElements()){
            System.out.println(drivers.nextElement().toString());
        }

    }

1604455293771

小结:

如何通过jdbc注册数据库驱动?

1)显式
DriverManager.registerDriver(new 驱动类);
2)隐式注册 (推荐)
Class.forName("驱动类的全限定名称");

1.5. 4、获取连接

1.5.1. API介绍

java.sql.DriverManager类中有如下方法获取数据库连接

static Connection getConnection(String url, String user, String password); //试图建立到给定数据库 URL 的连接。

参数说明

  1. String url:连接数据库的URL,用于说明连接数据库的位置
  2. String user:数据库的账号
  3. String password:数据库的密码

url格式:

主协议:子协议://localhost:3306/test?参数=参数值&...&.....

举例:

jdbc:mysql://localhost:3306/day05?useUnicode=true&characterEncoding=utf8
如果连接的时本地且端口号是3306
jdbc:mysql:///day05?useUnicode=true&characterEncoding=utf8

注意事项:

如果数据出现乱码需要加上参数: ?useUnicode=true&characterEncoding=utf8,表示让数据库以UTF8编码来处理数据。
如: jdbc:mysql://localhost:3306/day04_db?useUnicode=true&characterEncoding=utf8

练习题:使用jdbc连接day05数据库

    //1)注册驱动 2)获取连接对象
    @Test
    public void test3() throws SQLException, ClassNotFoundException {
        //1)注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2)获取连接对象
        String url="jdbc:mysql://localhost:3306/day05_1";
        String userName="root";
        String password="1234";
        Connection conn = DriverManager.getConnection(url, userName, password);
        System.out.println(conn);
    }

小结:

1.JDBC获取数据库连接使用哪个API?

DriverManager.getConnection(url,userName,password);

2.通过JDBC连接mysql的URL写法?

jdbc:mysql://localhost:3306/数据库

1.6. 5、JDBC实现对单表数据增、删、改

API介绍

获取Statement对象

java.sql.Connection接口中有如下方法获取到Statement对象

Statement createStatement() 
创建一个 Statement 对象来将 SQL 语句发送到数据库

Statement的API介绍

  1. int executeUpdate(String sql)
    根据执行的DML(INSERT、UPDATE、DELETE)语句,返回受影响的行数
    
  2. ResultSet executeQuery(String sql)
    根据查询语句返回结果集,只能执行SELECT语句
    
注意:
在MySQL中,只要不是查询就是修改。
executeUpdate:用于执行增删改
executeQuery:用于执行查询

使用步骤

  1. 注册驱动
  2. 获取连接
  3. 获取Statement对象
  4. 使用Statement对象执行SQL语句
  5. 释放资源

练习题:对user表中数据实现增删改操作

// 添加用户
    @Test
    public void test4() throws ClassNotFoundException, SQLException {
        //1)注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2)获取连接对象
        String url="jdbc:mysql:///day05_1";
        String userName="root";
        String password="1234";
        Connection conn = DriverManager.getConnection(url, userName, password);
        //3)获取发送sql的对象
        Statement stm = conn.createStatement();
        System.out.println(stm);
        //4)发送添加的sql语句 int count= executeUpdate(sql);
        String addSql="insert into user values(null,'zhaoliu','1234')";
        int count = stm.executeUpdate(addSql);
        System.out.println(count);  // 1
        //5)关闭资源,释放连接
        stm.close();
        conn.close();
    }

//删除
    @Test
    public void test5() throws ClassNotFoundException, SQLException {
        //1)注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2)获取连接对象
        String url="jdbc:mysql://localhost:3306/day05_1";
        String user="root";
        String pwd="1234";
        Connection conn = DriverManager.getConnection(url, user, pwd);
        //3)获取发送sql语句的对象 Statement
        Statement stm = conn.createStatement();
        //4)发送sql
        String deleteSql="delete from user where id=4";
        int count = stm.executeUpdate(deleteSql);
        System.out.println(count);// 1
        //5)释放资源,关闭连接
        stm.close();
        conn.close();
    }


//更新
    @Test
    public void test6() throws ClassNotFoundException, SQLException {
        //1)注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2)获取连接对象
        String url="jdbc:mysql:///day05_1";
        String user="root";
        String password="1234";
        Connection conn = DriverManager.getConnection(url, user, password);
        //3)获取发送sql的对象 Statement
        Statement stm = conn.createStatement();
        //4)发送sql语句
        String updateSql="update user set username='zhaoliu',password='1234' where id=3";
        int count = stm.executeUpdate(updateSql);
        System.out.println(count);
        //5)关闭资源,释放连接
         stm.close();
         conn.close();
    }

results matching ""

    No results matching ""