SQL函数
select 不一定要加 from
SELECT 1+1; SELECT round(rand()*10); SELECT round(3.4); SELECT length('hello'); SELECT length(trim(' hello world ')); SELECT trim('s' from 's1001'); SELECT now(); select CURRENT_DATE; SELECT CURRENT_TIME; SELECT SYSDATE(); SELECT DATEDIFF('2023-09-20',NOW()) -- 时间天数相减(大的减小的) SELECT DATE_ADD(now(),INTERVAL 1 DAY);
SELECT id '编号', emp_name '名称', CASE WHEN age<18 THEN '少年' WHEN age<30 THEN '青年' WHEN age<40 THEN '壮年' WHEN age<60 THEN '中年' ELSE '老年' END '年龄段' from employee;
JDBC(java database connectivity)
1、导入jar包(mysql-connector-java:8.0.29)
//idea下maven项目中,在pom.xml中直接插入如下内容,刷新即可 <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.29</version> </dependency>
2、创建连接
package com.woniuxy.jdbc; import com.woniuxy.entity.Dept; import java.sql.*; import java.util.ArrayList; import java.util.List; public class Test { public static void main(String[] args) throws ClassNotFoundException, SQLException { //加载驱动,会向DriverManager中注册驱动 Class.forName("com.mysql.cj.jdbc.Driver"); //url格式:jdbc:数据库类型://服务器地址:端口号/数据库名 String url = "jdbc:mysql://localhost:3306/hrms?useUniCode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai"; //得到连接对象 Connection con = DriverManager.getConnection(url, "root", "root"); //测试连接 List<Dept> list = new ArrayList<Dept>(); String sql = "Select * from dept"; PreparedStatement ps = con.prepareStatement(sql); ResultSet res = ps.executeQuery(); while (res.next()){ //获取的字段类型需要get清楚,但是括号既可以是索引(序号数字,较快),也可以是字段名 int id = res.getInt(1); String deptName = res.getString("dept_name"); String remark = res.getString(3); Dept dept = new Dept(id, deptName, remark); list.add(dept); } System.out.println("--------------------------"); for (Dept dept : list) { System.out.println(dept); } //创建面板,每次执行都会进行编译,效果低,不推荐使用 // String sql = "insert into dept values(0,test,测试)"; // Statement statement = con.createStatement(); // boolean execute = statement.execute(sql); //执行成功,但是返回false //? 表示占位符 String sql = "insert into dept values(0,?,?)"; PreparedStatement ps = con.prepareStatement(sql); ps.setString(1,"test1"); ps.setString(2,"测试1"); //执行语句,返回受影响的行数。可以执行insert、update、delete以及DDL语句,DDL返回0(创建表) int i = ps.executeUpdate(); System.out.println(i); } }