2022-08-18 第二组刘禹彤 学习笔记
打卡35天
###学习内容
MySQL常用函数
聚合函数
- count：计数 ------------count（*）≈count（1）>count（主键）>count（字段）
count（*）：MySQL对count（*）底层优化----count（
- min：最小值
- max：最大值
- sum：求和
- avg：平均值
数值型函数
主要是对数值型进行处理
- ceiling：向上取整
- floor：向下取整
- round（x）：四舍五入
- truncate（x）：返回数字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）：字符串的长度
- cancat（s1.....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
数据库设计
三范式
- 第一范式：要求有主键，并且要求每一个字段的原子性不能再分
- 第二范式：要求所有的非主键字段完全依赖主键，不能产生部分依赖
- 第三范式：所有非主键字段华和主键字段之间不能产生传递依赖
第一范式
不符合第一范式表结构
|
id |
name |
联系方式 |
|
1001 |
aaa |
|
|
1002 |
bbb |
|
|
1003 |
ccc |
解决方案
|
d |
name |
邮箱 |
手机号 |
|
1001 |
aaa |
12321321321 |
|
|
1002 |
bbb |
32132654654 |
|
|
1003 |
ccc |
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
获取连接
- 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/数据库名?useUnicode=true&characterEncoding=utf8
8.0版本还需要传参useSSL=fasle&serverTimezone=UTC
3.用户名
4.密码
- 连接数据库
正常
// 数据库的url String url = "jdbc:mysql://127.0.0.1:3306/20220816?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(connection); System.out.println(Objects.nonNull(connection) ? "连接成功" : "连接失败");
简化版
// 数据库的ur String url = "jdbc:mysql://127.0.0.1:3306/20220816?useUnicode=true&characterEncoding=utf8"; String username = "root"; String password = "root"; // 驱动的全类名 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) ? "连接成功" : "连接失败");
- 使用属性文件的好处
1.实现了代码和数据的分离，如果要修改配置信息，直接在属性文件中修改即可，不需要深入代码
2.如果修改了配置信息，省去了编译的过程
操作数据库
- 执行数据库的DML语句---------增删改
- java中三个接口分别定义了对数据库的调用的不同方式：
1.Statement：用来执行静态sql语句并返回它所生成的结果对象
2.PreparedStatement：可以使用此对象多次高效执行
3.CollableStatement：用来执行sql存储过程
###学习心得
今天将MySQL基础DML语句学习完毕，并且开始进入JDBC的学习，学习效率还可以
###掌握情况：一般
###课上练习
package a; import org.junit.Test; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Ch04 { @Test public void test01() { // 1.获取连接 Connection conn = JDBCUtil.connection(); Statement stmt = null; ResultSet rs = null; try { // 2.获取可以执行sql语句的stmt对象 stmt = conn.createStatement(); // 3.定义sql String sql = "select id tid,name tname from teacher where id = 5"; // 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); } } }
###运行结果
