2022-08-18 第四小组 修身课 学习笔记（every day）

目录

聚合函数

数值型函数

字符串型函数

日期和时间函数

获取时间和日期

时间戳和日期转换函数

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

时间日期的计算

加密函数

流程控制函数

数据库设计

三范式

第一范式

第二范式

第三范式

常见的表关系

一对一

一对多

多对多

JDBC

---

聚合函数

• count：计数。count(*)≈count(1)>count(主键)

  • count(*)：MySQL对count(*)底层优化，count(0)。

  • count(1)

  • count(主键)

  • count(字段)

• min：最小值

• max：最大值

• sum：求和

• avg：平均值

数值型函数

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

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

字符串型函数

对字符串进行处理。

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

日期和时间函数

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

加密函数

-- 把传入的参数的字符串按照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

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

数据库设计

三范式

• 第一范式：要求有主键，并且要求每一个字段的原子性不能再分。

• 第二范式：要求所有的非主键字段完全依赖主键，不能产生部分依赖

• 第三范式：所有非主键字段和主键字段之间不能产生传递依赖。

第一范式

不符合第一范式表结构：

符合第一范式的表结构：

必须有主键，这是数据库设计的基本要求，一般情况下我们采用数值型或定长字符串，列不能再分，比如：联系方式。

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

第二范式

建立在第一范式的基础上，要求所有非主键字段完全依赖于主键，不能产生部分依赖。

解决方案：

学生表：学号是主键

课程表：课程编号是主键

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

第三范式

建立在第二范式基础上，非主键字段不能传递依赖于主键字段。

不满足第三范式：

解决方案：

学生表：学号是主键

课程表：课程编号是主键

常见的表关系

一对一

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

• 分为两张表，共享主键。

• 分两张表，用外键连接。

一对多

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

• 分两张表存储，在多的一方加外键

• 这个外键字段引用是一的一方的主键

多对多

• 分三张表存储，在学生表存储学生信息，在课程表存储课程信息。

• 在成绩表中存储学生和课程的对应关系。

  ---

  JDBC

导入外部依赖
需要引入mysql的驱动（指的是引入的jar包）
数据的持久化，把数据永久的保存起来。主要的方式还是存到硬盘上。
持久化的实现过程大部分是通过数据库来完成。
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
    oracle:
     jdbc:oracle:thin:@主机名称:oracle端口:数据库名
    sqlServer：微软 C#
     jdbc:sqlserver://127.0.0.1:端口号:DatabaseName=xxxx
3、用户名 root
4、密码 root

---

      使用属性文件的好处：

       1、实现了代码和数据的分离，如果需要修改配置信息，直接在属性文件中修改即可，不需要深入代码

       2、如果修改了配置信息，省去了编译的过程

 

@Test
  public void test02() 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) ? "数据库连接成功" : "数据库连接失败");
  }

 执行数据库的DML语句--增删改
 Java中3个接口分别定义了对数据库的调用的不同的方式：
 1、Statement：用来执行静态sql语句并返回它所生成的结果对象
 2、PreparedStatement：预编译，可以使用此对象多次高效的执行该语句
 3、CallableStatement：用来执行sql存储过程

 @Test
    public void test01(){
        // 1.获取连接
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = JDBCUtil.getConnection();
            stmt = conn.createStatement();
            // 2.执行sql语句
            String sql = "INSERT INTO teacher (name) VALUES ('Jay')";
            // 修改
            sql = "UPDATE teacher SET name = 'JJ' where id = 7";
            sql = "select * from teacher";
            // 返回值是执行sql语句影响的行数
            int i = stmt.executeUpdate(sql);
            // 3.处理执行sql的返回值
            System.out.println(i);
            System.out.println("操作成功...");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
           JDBCUtil.close(conn,stmt);
        }
    }

遍历查询

@Test
public void test01() {
    // 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 f
        // 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 + ",na
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 6.关闭资源
        JDBCUtil.close(conn,stmt,rs);
    }
}