一、MySQL常用函数

1、聚合函数

2、数值型函数

3、字符串型函数

4、日期和时间函数

一、MySQL常用函数

1、聚合函数

count：计数。count(*)≈count(1)>count(主键)
- count(*)：MySQL对count(*)底层优化，count(0)。
- count(1)
- count(主键)
- count(字段)
min：最小值
max：最大值
sum：求和
avg：平均值

2、数值型函数

主要是对数值型进行处理。

ceiling(x)：向上取整
floor(x)：向下取整
round(x)：四舍五入
truncate(x,y)：返回数字x截断为y位小数的结果
PI：圆周率，π
rand：返回0到1的随机数
abs：绝对值

-- 绝对值

select ABS(-4) 4的绝对值,ABS(-1.1);

-- 向下取整，向上取整，四舍五入

select CEILING(4.1),FLOOR(1.1),ROUND(-4.4)

-- 取余

select MOD(60,11);

-- 随机数

select RAND(),RAND(),RAND()

-- 截断

select TRUNCATE(2.33999999,2);

3、字符串型函数

对字符串进行处理。

length(s)：字符串的长度
concat(s1,s2,.....sn)：合并字符串
lower(str)：将字母转成小写
upper(str)：将字母转成大写
left(str,x)：返回字符串str的左边的x个字符
right(str,x)：返回字符串str右边的x个字符
trim：去掉左右两边的空格
replace：替换
substring：截取
reverse：反转

select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');

4、日期和时间函数

date,time,datetime,timestamp,year。

获取时间和日期

【curdate】和【current_date】，返回当前的系统日期。
【curtime】和【current_time】，返回当前的系统时间。
【now】和【sysdate】，返回当前的系统时间和日期。

select CURRENT_DATE();
select CURTIME();
select now();

时间戳和日期转换函数

【UNIX_TIMESTAMP】获取unix时间戳函数
【FROM_UNIXTIME】将时间戳转换为时间格式

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');

5、加密函数

-- 把传入的参数的字符串按照md5算法进行加密，得到一个32位的16进制的字符串

select MD5('123456');

md5算法是不可逆的。

6、流程控制函数

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

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

select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);

对一系列的值进行判断：

-- 输出学生的各科的成绩，以及评级，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

二、数据库设计

1、三范式

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

第一范式

不符合第一范式表结构：

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#

2、常见的表关系

一对一

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

分为两张表，共享主键。
分两张表，用外键连接。

一对多

两张表，外键在多的一方。

分两张表存储，在多的一方加外键
这个外键字段引用是一的一方的主键

多对多

分三张表存储，在学生表存储学生信息，在课程表存储课程信息。
在成绩表中存储学生和课程的对应关系。

三、JDBC

导入外部依赖，需要引入MySQL的驱动

1.数据的持久化

把数据永久的白存起来，主要的方式还是存在硬盘
持久化的实现过程大部分是通过数据库来完成。

2.JDBC：
（1）数据库的驱动：java.sql.Driver接口，需要驱动程序需要实现的接口
       MySQL：com.mysql.jdbc.Driver
       Oracle：oracle.jdbc.Driver.OracleDriver
       反射，class.forName（"com.mysql.jdbc.Driver"）
（2）URL地址：
       JDBC协议：jdbc:mysql://主机名称：端口号、数据库名
       jdbc:mysql: //127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
        8.0版本还需要传参usessL=faLse&serverTimezone=UTC
          pracle:
jdbc : oracle:thin :@主机名称: oracLe端口:数据库名sqlserver:微软C#
jdbc:sqlserver:/ /127.0.0.1:端口号:DatabaseName=XXXX

（3）用户名
（4）密码

连接数据库：

public class Ch01 {
    @Test
    public void test03() throws ClassNotFoundException, SQLException, IOException {

    /*
      使用属性文件的好处：
       1、实现了代码和数据的分离，如果需要修改配置信息，直接在属性文件中修改即可，不需要深入代码
       2、如果修改了配置信息，省去了编译的过程
     */
        Properties properties = new Properties();

        // 读取外部的properties属性文件
        // 记住就好，复制粘贴就好
        properties.load(Ch01.class.getClassLoader().getResourceAsStream("jdbc.properties"));

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

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



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

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

        @Test
        public void test01() throws ClassNotFoundException, NoSuchMethodException,  InstantiationException, IllegalAccessException, SQLException, InvocationTargetException {
            // 数据库的url
            String url = "jdbc:mysql://127.0.0.1:3306/Book?useUnicode=true&characterEncoding=utf8";
            String username = "root";
            String password = "mysql";
            // 驱动的全类名
            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(connection);
            System.out.println(Objects.nonNull(connection) ? "数据库连接成功" : "数据库连接失败");

    }

    @Test
    public void test04() throws ClassNotFoundException, SQLException, IOException {

    /*
      使用属性文件的好处：
       1、实现了代码和数据的分离，如果需要修改配置信息，直接在属性文件中修改即可，不需要深入代码
       2、如果修改了配置信息，省去了编译的过程
     */
        // 读取外部的properties属性文件
        // 记住就好，复制粘贴就好
        Properties properties = new Properties();
        properties.load(Ch01.class.getClassLoader().getResourceAsStream("jdbc.properties"));

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

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

}

连接数据库和关流的工具类

public class JDBCUtil {


    public static Connection getConnection() throws IOException {

        Properties properties = new Properties();

        Connection conn = null;
        try {
        properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));

        // 数据库的url
        String url = properties.getProperty("mysql.url");
        String driverName = properties.getProperty("mysql.driverName");
        String username = properties.getProperty("mysql.username");
        String password = properties.getProperty("mysql.password");

        conn =  DriverManager.getConnection(url, username, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }





    public static void close(Connection conn, Statement stmt) {
        if (Objects.nonNull(stmt)){
            try {
                stmt.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(conn)){
            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 throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(conn)){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if (Objects.nonNull(rs)){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

向数据库的表里加入数据

public class Ch02 {
    @Test
    public void test01 (){
            // 1.获取连接
            Connection conn = null;
            Statement stmt = null;
            try {
                conn = JDBCUtil.getConnection();
                stmt = conn.createStatement();
                // 2.执行sql语句
                String sql = "INSERT INTO teachers (name) VALUES ( 'NU')";
                // 返回值是执行sql语句影响的行数
                int i = stmt.executeUpdate(sql);
                // 3.处理执行sql的返回值
                System.out.println(i);
                System.out.println("操作成功...");
            } catch (SQLException | IOException e) {
                throw new RuntimeException(e);
            } finally {
                JDBCUtil.close(conn,stmt);
            }
        }

    }

查询数据是否在数据库的表中

public class Ch03 {
    @Test
    public void test01() throws IOException {
        // 1.获取连接
        Connection conn = JDBCUtil.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 2.获取可以执行sql语句的stmt对象
            stmt = conn.createStatement();
            // 3.定义sql
            String sql = "select id tid,name tname from teachers where id = 3";
            // 4.执行查询的sql，会得到一个ResultSet
            // ResultSet就是封装了查询结果的一个对象
            rs = stmt.executeQuery(sql);
            // 5.遍历结果集ResultSet
            while(rs.next()) {
                int id = rs.getInt("tid");
                String name = rs.getString("tname");
                System.out.println("id:" + id + ",name:" + name);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6.关闭资源
            JDBCUtil.close(conn,stmt,rs);
        }

    }

}

小测试

public class Ch01 {
    public static void main(String[] args) throws IOException {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名");
        String name = scanner.next();
        System.out.println("请输入密码");
        String password = scanner.next();
        if (login(name,password) == true){
            System.out.println("登陆成功");
        }else {
            System.out.println("用户名或密码错误！！！");
        }

    }

    public static boolean login(String name,String password) throws IOException {
        if (name == null || password == null){
            return false;
        }
        Connection conn = JDBCUtil.getConnection();
        Statement stmt=null;
        ResultSet rs=null;
        try {
            stmt = conn.createStatement();
            String sql = "select * from account where username='"+name+"' and password='"+password+"'";
            rs = stmt.executeQuery(sql);
            return rs.next();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JDBCUtil.close(conn,stmt,rs);
        }
        return true;
    }
}

2022-08-18 第五组张明敏学习笔记

一、MySQL常用函数

1、聚合函数

2、数值型函数

3、字符串型函数

4、日期和时间函数

获取时间和日期

时间戳和日期转换函数

根据日期获取年月日的数值

时间日期的计算

5、加密函数

6、流程控制函数

二、数据库设计

1、三范式

第一范式

第二范式

第三范式

2、常见的表关系

一对一

一对多

多对多

三、JDBC

连接数据库：

连接数据库和关流的工具类

向数据库的表里加入数据

查询数据是否在数据库的表中

小测试

网站公告

今日签到

热门文章

最新发布

2022-08-18 第五组 张明敏 学习笔记

一、MySQL常用函数

1、聚合函数

2、数值型函数

3、字符串型函数

4、日期和时间函数

获取时间和日期

时间戳和日期转换函数

根据日期获取年月日的数值

时间日期的计算

5、加密函数

6、流程控制函数

二、数据库设计

1、三范式

第一范式

第二范式

第三范式

2、常见的表关系

一对一

一对多

多对多

三、JDBC

连接数据库：

连接数据库和关流的工具类

向数据库的表里加入数据

查询数据是否在数据库的表中

小测试

网站公告

今日签到

热门文章

最新发布

2022-08-18 第五组张明敏学习笔记