20220818 第一组 于芮 JDBC（第三十五天）
小白成长记——第三十五天
今天主要学习的内容是一些MySQL的小知识点以及JDBC连接MySQL数据库，偏实践性，知识点虽然不多，但是实践性很强，来看一下今天的学习笔记吧！
# MySQL常用函数
## 聚合函数
- count：计数。count(*)≈count(1)>count(主键)
- count(\*)：MySQL对count(\*)底层优化，count(0)。
- count(1)
- count(主键)
- count(字段)
- min：最小值
- max：最大值
- sum：求和
- avg：平均值
- 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：绝对值
- floor(x)：向下取整
- round(x)：四舍五入
- truncate(x,y)：返回数字x截断为y位小数的结果
- PI：圆周率，π
- rand：返回0到1的随机数
- abs：绝对值
```sql
-- 绝对值
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);
```
-- 绝对值
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：反转
- concat(s1,s2,.....sn)：合并字符串
- lower(str)：将字母转成小写
- upper(str)：将字母转成大写
- left(str,x)：返回字符串str的左边的x个字符
- right(str,x)：返回字符串str右边的x个字符
- trim：去掉左右两边的空格
- replace：替换
- substring：截取
- reverse：反转
```sql
select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');
```
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】，返回当前的系统时间和日期。
- 【curtime】和【current_time】，返回当前的系统时间。
- 【now】和【sysdate】，返回当前的系统时间和日期。
```sql
select CURRENT_DATE();
select CURTIME();
select now();
```
select CURRENT_DATE();
select CURTIME();
select now();
```
### 时间戳和日期转换函数
- 【UNIX_TIMESTAMP】获取unix时间戳函数
- 【FROM_UNIXTIME】将时间戳转换为时间格式
- 【FROM_UNIXTIME】将时间戳转换为时间格式
```sql
select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);
```
select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);
```
### 根据日期获取年月日的数值
```sql
select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());
```
select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());
```
### 时间日期的计算
```sql
-- 日期加法
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');
```
-- 日期加法
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');
```
## 加密函数
```sql
-- 把传入的参数的字符串按照md5算法进行加密，得到一个32位的16进制的字符串
select MD5('123456');
```
-- 把传入的参数的字符串按照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
- ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
- nullif(arg1,arg2)：如果arg1=arg2返回null，否则返回arg1
```sql
select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);
```
select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);
```
对一系列的值进行判断：
```sql
-- 输出学生的各科的成绩，以及评级，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;
```
-- 输出学生的各科的成绩，以及评级，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;
```
```sql
-- 行转列
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
```
-- 行转列
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](mailto:aaa@163.com , 13314569878) |
| 1002 | bbb | [bbb@163.com , 13245678945](mailto:bbb@163.com , 13245678945) |
| 1003 | ccc | [ccc@163.com , 15000456987](mailto:ccc@163.com , 15000456987) |
| ---- | ---- | ------------------------------------------------------------ |
| 1001 | aaa | [aaa@163.com , 13314569878](mailto:aaa@163.com , 13314569878) |
| 1002 | bbb | [bbb@163.com , 13245678945](mailto:bbb@163.com , 13245678945) |
| 1003 | ccc | [ccc@163.com , 15000456987](mailto:ccc@163.com , 15000456987) |
符合第一范式的表结构：
| id | name | 邮箱 | 手机号 |
| ---- | ---- | --------------------------------- | ----------- |
| 1001 | aaa | aaa@163.com | 12321321321 |
| 1002 | bbb | [bbb@163.com](mailto:bbb@163.com) | 32132654654 |
| 1003 | ccc | [ccc@163.com](mailto:ccc@163.com) | 45654654654 |
| ---- | ---- | --------------------------------- | ----------- |
| 1001 | aaa | aaa@163.com | 12321321321 |
| 1002 | bbb | [bbb@163.com](mailto:bbb@163.com) | 32132654654 |
| 1003 | ccc | [ccc@163.com](mailto: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 | 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 |
| ---- | ---- | ---- |
| 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 |
| -------- | -------- | ---- |
| 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 | 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 | 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 |
| ---- | ---- | -------- |
| 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# |
| -------- | -------- |
| 2001 | java |
| 2002 | mysql |
| 2003 | html |
| 2004 | python |
| 2005 | c++ |
| 2006 | c# |
## 常见的表关系
### 一对一
学生信息表分为基本信息表和信息信息表。
- 分为两张表，共享主键。
- 分两张表，用外键连接。
- 分两张表，用外键连接。
### 一对多
两张表，外键在多的一方。
- 分两张表存储，在多的一方加外键
- 这个外键字段引用是一的一方的主键
- 这个外键字段引用是一的一方的主键
### 多对多
- 分三张表存储，在学生表存储学生信息，在课程表存储课程信息。
- 在成绩表中存储学生和课程的对应关系。
- 在成绩表中存储学生和课程的对应关系。
# mysql未完待续....
`索引`，`视图`，存储过程，触发器，函数....
导入外部依赖
* 需要引入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
执行数据库的DML语句--增删改
*
* Java中3个接口分别定义了对数据库的调用的不同的方式：
* 1、Statement：用来执行静态sql语句并返回它所生成的结果对象
* 2、PreparedStatement：预编译，可以使用此对象多次高效的执行该语句
* 3、CallableStatement：用来执行sql存储过程
* 需要引入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
执行数据库的DML语句--增删改
*
* Java中3个接口分别定义了对数据库的调用的不同的方式：
* 1、Statement：用来执行静态sql语句并返回它所生成的结果对象
* 2、PreparedStatement：预编译，可以使用此对象多次高效的执行该语句
* 3、CallableStatement：用来执行sql存储过程
今天的学习内容就是这些了，还要继续加油啊！
