函数_jdbc

第一章.MySQL的常用函数

1.1 字符串函数

1.1.1 字符串函数列表概览

1.1.2 环境准备

-- 用户表
CREATE TABLE t_user (
  id int(11) NOT NULL AUTO_INCREMENT,
  uname varchar(40) DEFAULT NULL,
  age int(11) DEFAULT NULL,
  sex int(11) DEFAULT NULL,
  PRIMARY KEY (id)
);
insert  into t_user values (null,'zs',18,1);
insert  into t_user values (null,'ls',20,0);
insert  into t_user values (null,'ww',23,1);
insert  into t_user values (null,'zl',24,1);
insert  into t_user values (null,'lq',15,0);
insert  into t_user values (null,'hh',12,0);
insert  into t_user values (null,'wzx',60,null);
insert  into t_user values (null,'lb',null,null);

1.1.3 字符串连接函数

字符串连接函数主要有2个：

练习1：使用concat函数显示出 你好,uname 的结果

SELECT CONCAT('你好,' , uname) FROM t_user;

练习2：使用concat_ws函数显示出 你好,uname 的结果

SELECT CONCAT_WS(',', '你好', uname) FROM t_user;

1.1.4 字符串大小写处理函数

字符串大小写处理函数主要有2个：

练习1： 将字符串 hello 转换为大写显示

SELECT UPPER('hello'); -- HELLO

练习2：将字符串 heLLo 转换为小写显示

SELECT LOWER('heLLo'); -- hello

1.1.5 移除空格函数

可以对字符串进行按长度填充满、也可以移除空格符

练习1： 将用户id位8的用户的姓名的两边空白符移除

-- 表中数据是：'      lb   ', 使用trim后是： 'lb'
SELECT TRIM(uname) FROM t_user  WHERE id = 8;

1.1.6 子串函数

字符串也可以按条件进行截取，主要有以下可以截取子串的函数;

练习1：获取 hello,world 从第二个字符开始的完整子串

SELECT SUBSTRING("hello,world",2)

练习2：获取 hello,world 从第二个字符开始但是长度为4的子串

SELECT SUBSTR("hello,world", 2, 4); -- ello

1.2 数值函数

1.2.1 数值函数列表

1.2.2 常用数值函数练习

练习1： 获取 -12 的绝对值

select abs(-12);

练习2： 将 -11.2 向上取整

select ceil(-11.2);

练习3： 将 1.6 向下取整

select floor(1.6);

练习4： 获得2的32次幂的值

select pow(2, 32);

练习5： 获得一个在0-100之间的随机数

select rand()*100;

1.3 日期函数

1.3.1 日期函数列表

1.3.2 常用日期函数的练习

练习1：获取当前的日期

SELECT CURDATE();

练习2： 获取当前的时间（仅仅需要时分秒）

SELECT CURTIME();

练习3： 获取当前日期时间（包含年月日时分秒）

select now();

练习4: 获取到五一还有多少天

select DATEDIFF('2021-05-01',CURDATE())

1.4 流程函数

• 练习1：获取用户的姓名、性别，如果性别为1则显示1，否则显示0；要求使用if函数查询：

  SELECT uname,IF(sex=1,1,0) FROM t_user
  

• 练习2：获取用户的姓名、性别，如果性别为null则显示为1；要求使用ifnull函数查询：

  SELECT uname, IFNULL(sex, 1) FROM t_user;
  

• 举例3：

  SELECT id,uname,age,CASE WHEN IFNULL(age,0) <= 12 THEN '儿童'
  WHEN age <= 18 THEN '少年'
  WHEN age <= 40 THEN '中年'
  ELSE '老年' END '描述' FROM t_user
  

第二章 DCL语句

我们现在默认使用的都是root用户，超级管理员，拥有全部的权限。但是，一个公司里面的数据库服务器上面可能同时运行着很多个项目的数据库。所以，我们应该可以根据不同的项目建立不同的用户，分配不同的权限来管理和维护数据库。

2.1 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

关键字说明：

1.用户名：将创建的用户名
2.主机名：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
3.密码：该用户的登陆密码，密码可以为空，如果为空则l该用户可以不需要密码登陆服务器

具体操作：

-- user1用户只能在localhost这个IP登录mysql服务器
CREATE USER 'user1'@'localhost' IDENTIFIED BY '123';
-- user2用户可以在任何电脑上登录mysql服务器
CREATE USER 'user2'@'%' IDENTIFIED BY '123';

2.2 授权用户

用户创建之后，基本没什么权限！需要给用户授权

授权格式：

GRANT 权限1, 权限2... ON 数据库名.表名 TO '用户名'@'主机名';

关键字说明：

1. GRANT:授权关键字
2. 授予用户的权限，如`SELECT`，`INSERT`，`UPDATE`等。如果要授予所的权限则使用`ALL`
3. 数据库名.表名：该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如`*.*`
4. 用户名'@'主机名': 给哪个用户授权

具体操作：

1. 给user1用户分配对test这个数据库操作的权限
   

   GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON test.* TO 'user1'@'localhost';
   

2. 给user2用户分配对所有数据库操作的权限
   

   GRANT ALL ON *.* TO 'user2'@'%';
   

2.3 撤销授权

REVOKE  权限1, 权限2... ON 数据库.表名 FROM '用户名'@'主机名';

具体操作：

• 撤销user1用户对test操作的权限
  

  REVOKE ALL ON test.* FROM 'user1'@'localhost';
  

2.4 查看权限

SHOW GRANTS FOR '用户名'@'主机名';

具体操作：

• 查看user1用户的权限
  

  SHOW GRANTS FOR 'user1'@'localhost';
  

2.5 删除用户

DROP USER '用户名'@'主机名';

具体操作：

• 删除user2
  

  DROP USER 'user2'@'%';
  

2.6 修改用户密码

2.6.1 修改管理员密码

mysqladmin -uroot -p password 新密码  -- 新密码不需要加上引号

注意：需要在未登陆MySQL的情况下操作。

具体操作：

mysqladmin -uroot -p password root
输入老密码

2.6.2 修改普通用户密码

set password for '用户名'@'主机名' = password('新密码');

注意：需要在登陆MySQL的情况下操作。

具体操作：

set password for 'user1'@'localhost' = password('666666');

第三章.JDBC

1.JDBC介绍

1.概述:Java DataBase Connectivity,java数据库连接
  是一种对数据库进行操作的java技术,是一个标准,是一套API

2.JDBC四大核心对象
  a.DriverManager:用于注册驱动
  b.Connection: 表示与数据库创建的连接
  c.Statement: 操作数据库sql语句的对象
  d.ResultSet: 结果集或一张虚拟表

2.JDBC准备(导入jar包)

mysql-connector-java-5.1.37-bin.jar

3.JDBC开发步骤以及详解

1.注册驱动
  DriverManager->注册相关数据库的驱动
2.获取连接
  Connection接口
  连接数据库需要参数:
  数据库的url
  用户名
  密码

  获取:DriverManager.getConnection

3.获取执行平台
  Statement->用于执行sql语句的

  获取:Connection下的方法(createStatement())获取
4.执行sql:
  用到的Statement中的方法
5.处理结果集
  ResultSet接口
  查询之后很多数据,这些数据都会被封装到ResultSet接口中
  我们需要遍历ResultSet,将里面查询出来的数据获取出来

  增删改:不用处理结果集
  查询:需要处理结果集

  获取:需要调用Statement中的查询的方法

6.关闭资源
  通用的方法:close()
  需要关闭ResultSet,Statement,Connection

JDBC 4大核心对象:
 DriverManager
 Connection
 Statement
 ResultSet

4.JDBC注册驱动

         1.注册驱动
           DriverManager类:
           static void registerDriver(Driver driver)  ->注册驱动
           参数:java.sql 中的 Driver接口,所以传递的时候需要传递Driver的实现类对象
           实现类:传递com.mysql.jdbc下的Driver,此Driver类实现了sql包下的Driver接口

         2.注意:
           如果我们直接用DriverManager.registerDriver(new Driver());
           那么new Driver,Driver类就会被加载到内存,其中的静态成员会随着类的加载而加载
           Driver类底层有一个static代码块,而代码块中有注册驱动的操作,所以,我们相当于
           注册了2次驱动,没有必要的

         3.我们不用DriverManager.registerDriver(new Driver());
           我们想办法直接将Driver类加载到内存,让它自动执行自己底层的注册驱动
           如果直接将一个类加载到内存呢?->可以利用反射
           Class.forName("com.mysql.jdbc.Driver")

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

5.JDBC获取连接

         2.获取连接:
           DriverManager类中的方法:
             static Connection getConnection(String url, String user, String password)
           参数:
             url(数据库的地址):  jdbc:mysql://localhost:3306/数据库名称
             user(数据库的用户名):root
             password(数据库的密码):自己安装数据库时设置的那个密码
           返回值:
             Connection

        String url = "jdbc:mysql://localhost:3306/day04";
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, username, password);

经验值:

Access denied for user ‘root1’@’localhost’ (using password: YES):可能是用户名或者密码错误

Unknown database ‘day05’ :数据库名写写错了

当我们获取连接之后,先输出一下Connection,保证能正确连接上数据库再往下写

*平时我们写代码,如果代码量大,我们最后写几步执行一次

6.JDBC实现增删改操作

create table `user`(
  uid int primary key auto_increment,
  username varchar(100),
  `password` varchar(100)
);

/*
  添加的功能
*/

    @Test
  public void insert() throws Exception {
     /*
         1.注册驱动
           DriverManager类:
           static void registerDriver(Driver driver)  ->注册驱动
           参数:java.sql 中的 Driver接口,所以传递的时候需要传递Driver的实现类对象
           实现类:传递com.mysql.jdbc下的Driver,此Driver类实现了sql包下的Driver接口

         2.注意:
           如果我们直接用DriverManager.registerDriver(new Driver());
           那么new Driver,Driver类就会被加载到内存,其中的静态成员会随着类的加载而加载
           Driver类底层有一个static代码块,而代码块中有注册驱动的操作,所以,我们相当于
           注册了2次驱动,没有必要的

         3.我们不用DriverManager.registerDriver(new Driver());
           我们想办法直接将Driver类加载到内存,让它自动执行自己底层的注册驱动
           如果直接将一个类加载到内存呢?->可以利用反射
           Class.forName("com.mysql.jdbc.Driver")
      */
       Class.forName("com.mysql.jdbc.Driver");

       /*
         2.获取连接:
           DriverManager类中的方法:
             static Connection getConnection(String url, String user, String password)
           参数:
             url(数据库的地址):  jdbc:mysql://localhost:3306/数据库名称
             user(数据库的用户名):root
             password(数据库的密码):自己安装数据库时设置的那个密码
           返回值:
             Connection
        */
        String url = "jdbc:mysql://localhost:3306/day04";
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, username, password);

        /*
          3.获取执行平台
            Connection下的方法:
            Statement createStatement()
            返回值:Statement
         */
        Statement statement = connection.createStatement();

        // 准备sql
        String sql = "INSERT INTO user (id,username,password) VALUES (1,'柳岩','5436D');";

        /*
          4.执行sql
            Statement中的方法:
            int executeUpdate(String sql)  -> 针对于增删改操作
            ResultSet executeQuery(String sql)  ->针对于查询,会将查询出来的结果放到ResultSet中
         */
        int i = statement.executeUpdate(sql);
        System.out.println(i);

        /*
          5.处理结果集:
            结果集专门指ResultSet,查询
            现在我们没有结果集,无需处理
         */

        /*
          关闭资源
         */
        statement.close();
        connection.close();
    }
}

/*
  删除功能
*/
@Test
    public void delete()throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/day04";
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.获取执行平台
        Statement statement = connection.createStatement();
        String sql = "delete from user where id = 1;";
        //4.执行sql
        statement.executeUpdate(sql);
        //5.处理结果集->因为不是查询,不用处理
        //6.关闭资源
        statement.close();
        connection.close();

    }

7.JDBC实现查询操作

ResultSet executeQuery(String sql)  ->针对于查询,会将查询出来的结果放到ResultSet中

ResultSet:结果集
ResultSet中的方法:
  boolean next() -> 判断结果集中有没有下一个元素
  int getInt(int columnIndex) -> 获取指定第几列的数据 
                                 比如:columnIndex为1->证明要获取第一列数据
  int getInt(String columnLabel)-> 根据传递的列名获取对应的数据  

  String getString(int columnIndex) -> 获取指定第几列的数据,返回的是String
  String getString(String columnLabel) -> 根据传递的列名获取对应的数据,返回的是字符串


我们在不考虑具体类型时,可不可以直接获取各个列呢?可以
  Object getObject(int columnIndex) ->获取指定第几列的数据  
  Object getObject(String columnLabel) ->根据传递的列名获取对应的数据

    //查询
    @Test
    public void query()throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取连接
        String url = "jdbc:mysql://localhost:3306/day04";
        String username = "root";
        String password = "root";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.获取执行平台
        Statement statement = connection.createStatement();
        String sql = "select * from user";
        //4.执行sql
        ResultSet resultSet = statement.executeQuery(sql);
        /*
           5.处理结果集
             boolean next() -> 判断结果集中有没有下一个元素
             int getInt(int columnIndex) -> 获取指定第几列的数据
                                            比如:columnIndex为1->证明要获取第一列数据
             int getInt(String columnLabel)-> 根据传递的列名获取对应的数据

             String getString(int columnIndex) -> 获取指定第几列的数据,返回的是String
             String getString(String columnLabel) -> 根据传递的列名获取对应的数据,返回的是字符串
         */
        while(resultSet.next()){
           // int id = resultSet.getInt(1);
            //int id = resultSet.getInt("id");

            //int id = resultSet.getInt(2);

            //int id = resultSet.getInt("username");
            //String id = resultSet.getString(1);
            //String id = resultSet.getString("id");

            Object id = resultSet.getObject("id");
            Object name = resultSet.getObject("username");
            Object pwd = resultSet.getObject("password");
            System.out.println(id+"..."+name+"..."+pwd);
        }
        //6.关闭资源
        resultSet.close();
        statement.close();
        connection.close();
    }

8.JDBC工具类使用

public class JDBCUtils {
  private static String url;
  private static String username;
  private static String password;

  //注册驱动,初始化url,username,password是最先初始化的,所以放在static代码块中
    static{
        //注册驱动
      try {
          Class.forName("com.mysql.jdbc.Driver");
          url = "jdbc:mysql://localhost:3306/day04";
          username = "root";
          password = "root";
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      }
   }

   public static Connection getConn()throws Exception{
       Connection connection = DriverManager.getConnection(url, username, password);
       return connection;
   }

   //关闭资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
       if (resultSet!=null){
           try {
               resultSet.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }

        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Demo02Jdbc {
    //查询
    @Test
    public void query()throws Exception{
        //1.获取连接
        Connection conn = JDBCUtils.getConn();
        //2.获取执行平台
        Statement statement = conn.createStatement();
        String sql = "select * from user";
        //4.执行sql
        ResultSet resultSet = statement.executeQuery(sql);

        while(resultSet.next()){

            Object id = resultSet.getObject("id");
            Object name = resultSet.getObject("username");
            Object pwd = resultSet.getObject("password");
            System.out.println(id+"..."+name+"..."+pwd);
        }
        //6.关闭资源
      JDBCUtils.close(conn,statement,resultSet);
    }
}

第四章.PreparedStatement预处理对象

1.sql注入的问题以及解决方式(预处理对象)

public class Demo03Jdbc {
    public static void main(String[] args) throws Exception {
        //1.创建Scanner对象
        Scanner sc = new Scanner(System.in);
        //2.键盘录入
        System.out.println("请你输入用户名:");
        String username = sc.nextLine();
        System.out.println("请你输入密码:");
        String password = sc.nextLine();

        //3.获取连接
        Connection conn = JDBCUtils.getConn();
        //4.获取执行平台
        Statement statement = conn.createStatement();

        String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";

        System.out.println(sql);

        //5.执行sql
        ResultSet resultSet = statement.executeQuery(sql);

        //6.处理结果集
        if(resultSet.next()){
            System.out.println("登录成功!");
        }else{
            System.out.println("登录失败!");
        }

        //7.关闭资源
        JDBCUtils.close(conn,statement,resultSet);
    }
}


345' or '1' = '1-->sql注入,导致程序不安全

PreparedStatement  extends Statement
1.获取:Connection中的方法:
       PreparedStatement prepareStatement(String sql)
2.编写sql时,支持占位符?
  String sql = "select * from user where username = ? and password = ? "
3.方法:
  void setObject(int parameterIndex, Object x)  
                 parameterIndex : 代表的是几个?
                 x:给?赋予什么值

  int executeUpdate()->针对于增删改
  ResultSet executeQuery() -> 针对于查询

不使用PreparedStatement时:

select * from user where username = '柳岩' and password = '345' or '1' = '1';

345\' or \'1\' = \'1

使用PreparedStatement:

select * from user where username = '柳岩' and password = '345\' or \'1\' = \'1';
经过PreparedStatement一处理中间所有的'都会变成普通的字符串内容,mysql就会认为345\' or \'1\' = \'1都是密码,所以登录不上

2.使用预处理对象(PreparedStatement)实现操作

public class Demo04Jdbc_Login {
    public static void main(String[] args) throws Exception {
        //1.创建Scanner对象
        Scanner sc = new Scanner(System.in);
        //2.键盘录入
        System.out.println("请你输入用户名:");
        String username = sc.nextLine();
        System.out.println("请你输入密码:");
        String password = sc.nextLine();

        //3.获取连接
        Connection conn = JDBCUtils.getConn();
        /*
           4.获取执行平台
             Connection中的方法:
             PreparedStatement prepareStatement(String sql)


         */
        String sql = "select * from user where username = ? and password = ?";
        PreparedStatement pst = conn.prepareStatement(sql);

        //为?赋值
        pst.setObject(1,username);
        pst.setObject(2,password);

        System.out.println(sql);

        /*
           5.执行sql
             PreparedStatement中的方法
             int executeUpdate()->针对于增删改
             ResultSet executeQuery() -> 针对于查询
         */
        ResultSet resultSet = pst.executeQuery();

        //6.处理结果集
        if(resultSet.next()){
            System.out.println("登录成功!");
        }else{
            System.out.println("登录失败!");
        }

        //7.关闭资源
        JDBCUtils.close(conn,pst,resultSet);
    }
}

3.使用预处理对象(PreparedStatement)实现查询操作

public class Demo05JDBC_PreparedStatement {
    public static void main(String[] args) throws Exception {
        //获取连接
        Connection conn = JDBCUtils.getConn();
        //准备sql
        String sql = "select * from user";
        //获取PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        //由于没有占位符,我们查询的是所有,所以不用为占位符赋值了
        //执行sql
        ResultSet resultSet = pst.executeQuery();

        //处理结果集
        while(resultSet.next()){
            Object id = resultSet.getObject("id");
            Object username = resultSet.getObject("username");
            Object password = resultSet.getObject("password");
            System.out.println(id+"..."+username+"..."+password);
        }
        //释放资源
        JDBCUtils.close(conn,pst,resultSet);

    }
}

4.使用读取配置文件的方式编写工具类(扩展)

在src下创建jdbc.properties文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/day04_37
username=root
password=root

public class JDBCUtils2 {
  private static String driverClassName;
  private static String url;
  private static String username;
  private static String password;

  //注册驱动,初始化url,username,password是最先初始化的,所以放在static代码块中
    static{
        //注册驱动
      try {
         //创建Properties集合
          Properties properties = new Properties();
          //利用字节输入流读取配置文件
          InputStream in = JDBCUtils2.class.getClassLoader().getResourceAsStream("jdbc.properties");
          properties.load(in);

          //获取Properties集合中的数据
          driverClassName = properties.getProperty("driverClassName");
          url = properties.getProperty("url");
          username = properties.getProperty("username");
          password = properties.getProperty("password");

          Class.forName(driverClassName);
      } catch (Exception e) {
          e.printStackTrace();
      }
   }

   public static Connection getConn(){
       Connection connection = null;
       try {
           connection = DriverManager.getConnection(url, username, password);
       } catch (SQLException e) {
           e.printStackTrace();
       }
       return connection;
   }

   //关闭资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
       if (resultSet!=null){
           try {
               resultSet.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }

        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public class Demo06JDBC_PreparedStatement {
    public static void main(String[] args) throws Exception {
        //获取连接
        Connection conn = JDBCUtils2.getConn();
        //准备sql
        String sql = "select * from user";
        //获取PreparedStatement对象
        PreparedStatement pst = conn.prepareStatement(sql);
        //由于没有占位符,我们查询的是所有,所以不用为占位符赋值了
        //执行sql
        ResultSet resultSet = pst.executeQuery();

        //处理结果集
        while(resultSet.next()){
            Object id = resultSet.getObject("id");
            Object username = resultSet.getObject("username");
            Object password = resultSet.getObject("password");
            System.out.println(id+"..."+username+"..."+password);
        }
        //释放资源
        JDBCUtils2.close(conn,pst,resultSet);

    }
}