day25-数据库-JDBC
一、mysql数据库
- 表结构的设计原则:
- 开发中处理一对多的问题:在多表(从表)中添加一个外键,名称一般为主表的名称_id,字段类型一般和主表保持一致,为了保证数据的有效性和完整性,在多表的外键上添加一个外键约束即可(但开发中基本不会添加,因为有约束后,操作单表很不方便,但是要有外键字段,且外键的联系要清楚)
- 开发中处理多对多的问题:引入一张中间表,存放两张表的主键,一般会将这两个字段设置为联合主键,这样就可以将多对多的关系拆分成两个一对多的了,为了保证数据的完整性和一致性,需要在中间表上添加两个外键约束即可。(实际中一般不会添加)
2.查询语句 中条件筛选的 关键字 where--group by--having--order by
- 查询中用到的关键词主要包含六个,并且他们的顺序依次为
elect--from--where--group by--having--order by
- group by 分组:select 列a,聚合函数(聚合函数规范) from 表明 where 过滤条件 group by 列a
- group by 字句也和where条件语句结合在一起使用。当结合在一起时,where在前,group by 在后。即先对select xx from xx的记录集合用where进行筛选,然后再使用group by 对筛选后的结果进行分组
- 使用having字句对分组后的结果进行筛选:语法跟where差不多
- 但需要注意having和where的用法区别:
1.having只能用在group by之后,对分组后的结果进行筛选(即使用having的前提条件是分组)。
2.where肯定在group by 之前,即也在having之前。
3.where后的条件表达式里不允许使用聚合函数,而having可以。
- 但需要注意having和where的用法区别:
- 当一个查询语句同时出现了where,group by,having,order by的时候,执行顺序和编写顺序是:
1.执行where xx对全表数据做筛选,返回第1个结果集。
2.针对第1个结果集使用group by分组,返回第2个结果集。
4.针对第2个结集执行having xx进行筛选,返回第3个结果集。
3.针对第3个结果集中的每1组数据执行select xx,有几组就执行几次,返回第4个结果集。
5.针对第4个结果集排序。
-
例子:
完成一个复杂的查询语句,需求如下:
按由高到低的顺序显示个人平均分在70分以上的学生姓名和平均分,为了尽可能地提高平均分,在计算平均分前不包括分数在60分以下的成绩,并且也不计算贱人(jr)的成绩。 分析:
1.要求显示学生姓名和平均分
因此确定第1步select s_name,avg(score) from student
2.计算平均分前不包括分数在60分以下的成绩,并且也不计算贱人(jr)的成绩
因此确定第2步 where score>=60 and s_name!=’jr’ 3.显示个人平均分
相同名字的学生(同一个学生)考了多门科目 因此按姓名分组 确定第3步 group by s_name 4.显示个人平均分在70分以上
因此确定第4步 having avg(s_score)>=70 5.按由高到低的顺序
因此确定第5步 order by avg(s_score) desc
五、索引
1.索引是单独的数据库对象,索引也需要被维护。 2.索引可以提高查询速度,但会降增删改的速度。 3.通过一定的查询触发,并不是越多越好。 什么时候不适合用索引?
1.当增删改的操作大于查询的操作时。 2.查询的语句大于所有语句的三分之一时。
创建索引语法:create index 索引名 on 表明 (列名) 删除索引语法:drop index 索引名
二、JDBC
- JDBC六步:
// 1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); // 2.获取连接 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/qf", "root", "root"); // 3.编写sql语句,获得操作对象 String sql = "select * from student"; PreparedStatement st = conn.prepareStatement(sql); // 4.执行sql,返回结果 ResultSet rs = st.executeQuery(); // 5.处理结果 while (rs.next()) { System.out.println(rs.getString(1)); } // 6.关闭资源 rs.close(); st.close(); conn.close();
- 封装JDBC工具类,编写配置文件db.properties.注意配置文件一定要放在src目录下。(将操作方法同时封装进工具类中,方便后面的操作)
- JDBC六步:
-
-
项目中新建普通文件夹:lib
-
拷贝所需jar包至该目录下
-
右键jar包 -> build path -> add to build path
-
/** * JDBC 工具类的封装 * * @author Administrator * */ public class DBUtil { private static Connection conn; private PreparedStatement prest; private ResultSet rs; //只要使用工具类必须要加载驱动和打开连接,所以可以放在静态代码块中 static { try { // 1.加载驱动 Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { throw new RuntimeException("驱动加载失败,请检查驱动"); } // 打开连接 openConnection(); } public DBUtil() { } /** * 2.打开一个连接 */ public static void openConnection() { try { // 读取配置文件 获得 数据库连接信息 Properties prop = new Properties(); prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties")); // 拿到对应的数据 String url = prop.getProperty("url"); String user = prop.getProperty("user"); String password = prop.getProperty("password"); // 拿到连接 conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { throw new RuntimeException("连接获取失败"); } } /** * 3.拿到一个sql操作对象 * * @param sql要执行的sql * (会对sql进行预编译,防止sql注入) * @return 返回一个preparedStatement对象,其中包含了已经编译好的sql语句 */ public PreparedStatement getPrepareStatement(String sql) { try { prest = conn.prepareStatement(sql); } catch (SQLException e) { throw new RuntimeException("PrepareStatement对象获取失败"); } return prest; } /** * 4.封装查询的方法 * * @param sql * 查询的sql语句 * @param obj * 语句中问号的设置的值的数组 * @return rs 返回查询结果 */ public ResultSet executeQuery(String sql, Object obj[]) { // 先对sql预编译 拿到PrepareStatement对象 prest = getPrepareStatement(sql); try { // 循环给语句中的?赋值 if (obj != null) { for (int i = 0; i < obj.length; i++) { prest.setObject(i + 1, obj[i]); } } // 执行查询操作 rs = prest.executeQuery(); } catch (SQLException e) { e.printStackTrace(); } return rs; } /** * 5.封装更新的方法 * * @param sql * 需要执行的sql语句 * @param obj * 语句中?需要设置的值的数组 * @return 返回一个影响数据库的行数 */ public int executeUpdate(String sql, Object obj[]) { // 打印一下传入的sql 防止sql语句有误 // System.out.println(sql); // 拿到一个PrepareStatement对象,里面包含了预编译的sql语句 prest = getPrepareStatement(sql); // 更新结果的标志 int rs = 0; try { if (obj != null) { for (int i = 0; i < obj.length; i++) { prest.setObject(i + 1, obj[i]); } } // 执行更新操作 rs = prest.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); rs=-1; } return rs; } /** * 6.关闭资源 先打开的后关闭,后打开的先关闭 */ public void close() { try { // 先判空 if (rs != null) { rs.close(); } if (prest != null) { prest.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } }