2022-08-18 第五组赖哲栋学习笔记

标签：String 18 08 第五组主键 mysql public SELECT name

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	[email protected] , 13314569878
1002	bbb	[email protected] , 13245678945
1003	ccc	[email protected] , 15000456987

符合第一范式的表结构：

id	name	邮箱	手机号
1001	aaa	[email protected]	12321321321
1002	bbb	[email protected]	32132654654
1003	ccc	[email protected]	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个接口分别定义了对数据库的调用的不同的方式

Statement：用来执行静态sql语句并且返回它所生成的结果对象
PreparedStatement：预编译，可以使用此对象多次
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"));
    }
}

标签：String,18,08,第五组,主键,mysql,public,SELECT,name
From： https://www.cnblogs.com/laizhedong/p/16600217.html

2022-08-18 第五组赖哲栋学习笔记

MySQL常用函数

聚合函数

数值型函数

字符串函数

日期和时间函数

获取时间和日期

时间戳和日期转换函数

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

时间日期的计算

加密函数

流程控制函数

数据库设计

三范式

第一范式

第二范式

第三范式

常见的表关系

一对一

一对多

多对多

JDBC

jdbc

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

相关文章

赞助商

阅读排行

2022-08-18 第五组 赖哲栋 学习笔记

MySQL常用函数

聚合函数

数值型函数

字符串函数

日期和时间函数

获取时间和日期

时间戳和日期转换函数

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

时间日期的计算

加密函数

流程控制函数

数据库设计

三范式

第一范式

第二范式

第三范式

常见的表关系

一对一

一对多

多对多

JDBC

jdbc

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

相关文章

赞助商

阅读排行

2022-08-18 第五组赖哲栋学习笔记