MySQL常用函数

聚合函数

  • count:计数
    • count():MySQL对count()底层优化,count(0)
    • count(1)
    • count(主键)
    • count(字段)
  • min:最小值
  • max:最大值
  • sum:求和
  • avg:平均值

数值型函数

主要是对数值型进行处理

  • ceiling:向上取整
  • floor:向下取整
  • round:四舍五入
  • truncate(x,y):返回数字X阶段为y位小数的结果
  • PI:圆周率 π
  • rand:随机数 0~1
  • abs:绝对值
-- 绝对值
select ABS(-4) //4
-- 向下取整
SELECT FLOOR(4.6) //4
-- 向上取整
SELECT CEILING(-11.6) //-11
-- 四舍五入
SELECT ROUND(4.6) //5
-- 取余
SELECT MOD(60,11) //5
-- 随机数
SELECT RAND(),RAND(),RAND()
-- 截取
SELECT TRUNCATE(2.33999999,2) //2.33

字符串函数

对字符串进行处理

  • length(s):字符串的长度
  • concat(s1,s2,...,sn):合并字符串
  • lower(str):将字母转成小写
  • upper(str):将字母转成大写
  • left(str,x):返回字符串str的左边的x个字符
  • rigth(str.x):返回字符串str的右边的x个字符
  • trim:去掉两边的空格
  • replace:替换
  • substring:截取
  • reverse:反转
SELECT LEFT('abcdefg',2); //ab
SELECT RIGHT('abcdefg',2); //fg
SELECT REVERSE('hijklmn') //nmlkjih
SELECT REPLACE('abcdefg','abc','x') // xdefg

日期和时间函数

date,time,datetime,timestamp,year

获取时间和日期
  • 【curdate】和【current_date】:返回当前的系统日期
  • 【curtime】和【current_time】:返回当前的系统时间
  • 【now】和【sysdate】:返回当前的系统时间和日期
SELECT CURRENT_DATE();
SELECT CURTIME();
SELECT now();
时间戳和日期转换函数
  • 【UNIX_TINESTAMP】获取unix时间戳函数
  • 【FROM_UNXITIME】将时间戳转换成时间格式
SELECT UNIX_TIMESTAMP();
SELECT FROM_UNIXTIME(1660785720);
根据日期获取年月日的数值
SELECT MONTH(SYSDATE());
SELECT MONTHNAME(SYSDATE());
SELECT DAYNAME(SYSDATE());
SELECT DAYOFWEEK(SYSDATE());
SELECT WEEK(SYSDATE());
SELECT DAYOFMONTH(SYSDATE());
SELECT YEAR(SYSDATE());
时间日期的计算
-- 日期加法
select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);
-- 日期减法
select DATE_SUB(SYSDATE(),INTERVAL 10 DAY);
-- 时间间隔
select DATEDIFF('2023-01-01',SYSDATE());
-- 日期格式化
select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');

加密函数

-- 把传入的参数的字符串按照md5算法进行加密,得到一个32位的16进制的字符串
select MD5('123456');

md5算法是不可逆的

流程控制函数

可以进行条件判断,用来实现SQL语句的逻辑

  • if(test,t,f):如果test是真,则返回t,否则返回f
  • ifnull(arg1,arg2):如果arg1不是空,返回arg1,否则返回arg2
  • nullif(arg1,arg2):如果arg1 = arg2,返回null,否则返回arg1

对一系列的值进行判断

-- 输出学生的各科的成绩,以及评级,60以下D,60-70是C,71-80是B,80以上是A
SELECT
	*,
CASE
		WHEN score < 60 THEN 'D' 
		WHEN score >= 60 AND score < 70 THEN 'C' 
		WHEN score >= 70 AND score < 80 THEN 'B' 
		WHEN score >= 80 THEN 'A' 
		END AS '评级' 
	FROM
	mystudent;
	-- 行转列
SELECT
	user_name,
	max( CASE course WHEN '数学' THEN score ELSE 0 END ) '数学',
	max( CASE course WHEN '语文' THEN score ELSE 0 END ) '语文',
	max( CASE course WHEN '英语' THEN score ELSE 0 END ) '英语' 
FROM
	mystudent 
GROUP BY
	user_name

数据库设计

三范式

  • 第一范式:要求有主键,并且要求每一个字段的原子性不能再分
  • 第二范式:要求所有的非主键字段完全依赖主键,不能产生部分依赖
  • 第三范式:所有非主键字段和主键字段之间不能产生传递依赖
第一范式

不符合第一范式表结构:

id name 联系方式
1001 aaa aaa@163.com , 13314569878
1002 bbb bbb@163.com , 13245678945
1003 ccc ccc@163.com , 15000456987

符合第一范式的表结构:

id name 邮箱 手机号
1001 aaa aaa@163.com 12321321321
1002 bbb bbb@163.com 32132654654
1003 ccc ccc@163.com 45654654654
  • 必须有主键,这是数据库设计的基本要求,一般情况下我们才去数值型或定长字符串,列不能再分

关于第一范式,保证每一行的数据唯一,每个表必须有主键

第二范式
  • 在第一范式的基础上,所有非主键字段完全依赖于主键,不能产生部分依赖
学号 性别 姓名 课程编号 课程名称 教室 成绩
1001 a 2001 java 301 89
1002 b 2002 mysql 302 90
1003 c 2003 html 303 91
1004 d 2004 python 304 52
1005 e 2005 c++ 305 67
1006 f 2006 c# 306 84
解决方案:

学生表:学号是主键

学号 性别 姓名
1001 a
1002 b
1003 c
1004 d
1005 e
1006 f

课程表:课程编号是主键

课程编号 课程名称 教室
2001 java 301
2002 mysql 302
2003 html 303
2004 python 304
2005 c++ 305
2006 c# 306

成绩表:学号和课程编号为联合主键

学号 课程编号 成绩
1001 2001 89
1002 2002 90
1003 2003 91
1004 2004 52
1005 2005 67
1006 2006 84
第三范式
  • 在第二范式的基础上,非主键字段不能传递依赖于主键字段
    不满足第三范式:
学号 姓名 课程编号 课程名称
1001 a 2001 java
1002 b 2002 mysql
1003 c 2003 html
1004 d 2004 python
1005 e 2005 c++
1006 f 2006 c#

解决方案:

学生表:学号是主键

学号 姓名 课程编号
1001 a 2001
1002 b 2002
1003 c 2003
1004 d 2004
1005 e 2005
1006 f 2006

课程表:课程编号是主键

课程编号 课程名称
2001 java
2002 mysql
2003 html
2004 python
2005 c++
2006 c#

常见的表关系

一对一

学生信息表分为基本信息表和信息信息表

  • 分为两张表,共享主键
  • 分两张表,用外键连接
一对多

两张表,外键在多的一方

  • 分两张表存储,在多的一方加外键
  • 这个外键字段引用是
多对多
  • 分三张表存储,在学生表存储学生信息,在课程表存储课程信息
  • 在成绩表中存储学生和课程的对应关系

JDBC

  • 导入外部依赖
  • 需要引入mysql的驱动(指的是引入jar包)
    • 数据的持久化,把数据永久的保存起来。主要的方式还是存到硬盘上
    • 持久化的实现过程大部分通过数据库来完成

jdbc

  • 数据库的驱动:java.sql.Driver接口,所有驱动程序需要实现的接口

    • mysql:com.mysql.jdbc.Driver
    • oracle:oracle.jdbc.driver.OracleDriver
    • 反射:Class.forName("com.mysql.jdbc.Driver")
  • URL地址

    • jdbc协议
    • jdbc:mysql://主机名称:端口号/数据库名
    • dbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
    • 8.0版本还需要传参useSSL=false&serverTimezone=UTC
  • 用户名 root

  • 密码 root

连接数据库

    @Test
    public void test01() throws ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, SQLException {
        //数据库的url
        String url = "jdbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root";
        //驱动的全类名
        String driverName = "com.mysql.jdbc.Driver";

        //1、加载驱动类
        Class clazz = Class.forName(driverName);
        //2、实例化Driver
        Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
        //3、注册成功
        DriverManager.registerDriver(driver);
        //4、获取连接
        Connection connection = DriverManager.getConnection(url,username,password);
        System.out.println(Objects.nonNull(connection)? "数据库连接成功" : "数据库连接失败");
    }

简易版

----------创建jdbc.propertied文件----------
/*
      使用属性文件的好处:
       1、实现了代码和数据的分离,如果需要修改配置信息,直接在属性文件中修改即可,不需要深入代码
       2、如果修改了配置信息,省去了编译的过程
     */
mysql.url=jdbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
mysql.username=root
mysql.password=root
mysql.driverName=com.mysql.jdbc.Driver

------------------------------------------------------
   @Test
    public void test01() throws ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, SQLException, IOException {

        Properties properties = new Properties();
        //读取外部的properties属性文件
        properties.load(Ch01.class.getClassLoader().getResourceAsStream("jdbc.properties"));

        String url = properties.getProperty("mysql.url");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");

        String driverName = properties.getProperty("mysql.driverName");

        //1、加载驱动类
        Class clazz = Class.forName(driverName);
        //4、获取连接
        Connection connection = DriverManager.getConnection(url,username,password);
        System.out.println(Objects.nonNull(connection)? "数据库连接成功" : "数据库连接失败");
    }

执行数据库的DML语句--增删改

Java中有3个接口分别定义了对数据库的调用的不同的方式

  1. Statement:用来执行静态sql语句并且返回它所生成的结果对象
  2. PreparedStatement:预编译,可以使用此对象多次
  3. CallableStatement:用来执行sql存储过程
----------封装工具类----------
public class JDBCUtil{ 
    //连接数据库
    public static Connection getConnection() {
        Properties properties = new Properties();
        Connection connection = null;
        try {
            properties.load(Ch01.class.getClassLoader().getResourceAsStream("jdbc.properties"));
            String url = properties.getProperty("mysql.url");
            String username = properties.getProperty("mysql.username");
            String password = properties.getProperty("mysql.password");
            String driverName = properties.getProperty("mysql.driverName");

            Class.forName(driverName);
            connection = DriverManager.getConnection(url, username, password);
            System.out.println(Objects.nonNull(connection) ? "数据库连接成功" : "数据库连接失败");
        } catch (IOException | ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    //关闭数据库
    public static void lose(Connection conn, Statement stmt) {
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close(Connection conn, Statement stmt, ResultSet rs) {
        if(Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        if(Objects.nonNull(rs)){
            try {
                rs.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}

增删改查示例:

    @Test
    public void test01(){
        
        Connection conn = null;
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            // 1.获取连接
            conn = JDBCUtil.getConnection();
            stmt = conn.createStatement();
            // 2.执行sql语句
            //添加数据
            String sql = "INSERT INTO teacher (name) VALUES ('Jay')";
            //修改数据
            String sql1 = "UPDATE teacher SET name = 'JJ' WHERE id = 6";
            //删除数据
            String sql2 = "DELETE FROM teacher WHERE id = 6 ";
            // 返回值是执行sql语句影响的行数
            int i = stmt.executeUpdate(sql2);
            //查询数据
            String sql1 = "SELECT * FROM teacher";
            //调用查询方法
            resultSet = stmt.executeQuery(sql1);
                //判断resultSet结果集的下一行是否为空(遍历结果集ResultSet)
                while (resultSet.next()) {
                    String name = resultSet.getObject("name").toString();
                    System.out.println(name);
                }
            3.处理执行sql的返回值
            System.out.println("操作成功...");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtil.close(conn,stmt,resultSet);
        }
    }

封装增删改查方法

-----------------------------创建一个Teacher类
public class Teacher {
    private Integer id;
    private String name;

    public Teacher() {
    }

    public Teacher(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}
-------------------------------------封装增删改查的方法
import Utils.JDBCUtil;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {

    Connection conn = null;
    Statement stmt = null;
    {
        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.createStatement();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //增删改查
    public int update(String sql, Object...args){
        int i = 0;
        try {
            i = stmt.executeUpdate(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.newclose(conn,stmt);
        }
        return i;
    }

    //查询所有
    public List<Teacher> queryAll(String sql){
        List<Teacher> teachers = new ArrayList<>(16);
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                Teacher teacher = new Teacher(id,name);
                teachers.add(teacher);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCUtil.close(conn,stmt,rs);
        }
        return teachers;
    }
}

---------------------------------------测试类
public class TeacherDaoTest {
    public static void main(String[] args) {
        TeacherDao t = new TeacherDao();
        //添加数据
        t.update("INSERT INTO teacher (name) VALUES ('JJ') ");
        //更新数据
        t.update("UPDATE teacher SET name = 'Jay' WHERE id = 7");
        //删除数据
        System.out.println(t.update("DELETE FROM teacher WHERE id = 7"));
        //查询所有数据
        System.out.println(t.queryAll("SELECT * FROM teacher"));
    }
}