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 | 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# |
常见的表关系
一对一
学生信息表分为基本信息表和信息信息表
- 分为两张表,共享主键
- 分两张表,用外键连接
一对多
两张表,外键在多的一方
- 分两张表存储,在多的一方加外键
- 这个外键字段引用是
多对多
- 分三张表存储,在学生表存储学生信息,在课程表存储课程信息
- 在成绩表中存储学生和课程的对应关系
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"));
}
}