java开发工程师入门第二阶段(04-mysql数据库)_java开发之java数据库编程第二章mysol初体验-优快云博客

本文链接：https://blog.youkuaiyun.com/javaLkx/article/details/123754150

文章目录

一.视图View

1.概念

可视化的表，视图当做是一个特殊的表，是指，把sql执行的结果，直接缓存到了视图中。
下次还要发起相同的sql，直接查视图。现在用的少,了解即可.
使用： 1，创建视图 2，使用视图

2.测试

create view 视图名 as  SQL语句;
select * from 视图名;
#视图：就是一个特殊的表，缓存上次的查询结果
#好处是提高了SQL的复用率，坏处是占内存无法被优化
 
#1.创建视图
CREATE VIEW emp_view AS
SELECT * FROM emp WHERE ename LIKE '%a%' #模糊查询，名字里包含a的
#2.使用视图
SELECT * FROM emp_view

二.数据库设计的三范式

1.概述

简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式,也是作为数据库设计的一些规则.
关系型数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。范式越高，冗余最低，一般到三范式，再往上，表越多，可能导致查询效率下降。所以有时为了提高运行效率，可以让数据冗余.

2.分类

1NF的定义为：符合1NF的关系中的每个属性都不可再分

2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖,也就是说,表里的每个字段都要依赖于主键

3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖

3.总结

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

三.SQL优化

查询SQL尽量不要使用select *，而是具体字段
避免在where子句中使用or来连接条件
使用varchar代替char
尽量使用数值替代字符串类型
创建name字段的索引
索引不宜太多，一般5个以内
索引不适合建在有大量重复数据的字段上
避免在where子句中使用!=或<>操作符
批量插入性能提升
复合索引最左特性
不要有超过5个以上的表连接
inner join 、left join、right join，优先使用inner join

四:JDBC

1.概念

我们学习了数据库，数据库实现了数据的持久化，但我们最终要在程序里处理数据啊，那java代码中怎么去访问数据库读写数据呢？

这就要用到sun公司设定的一套数据库标准了，这套标准就是JDBC（Java Database Connectivity）。但它只是规范，不做具体实现。于是数据库厂商又根据JDBC标准，实现自家的驱动Driver。如：mysql驱动com.mysql.cj.jdbc.Driver，Oracle的驱动oracle.jdbc.OracleDriver。有了这套解决方案，java就可以访问数据库中的数据了。

Java中提倡面向接口开发，而最经典的接口设计莫过于JDBC数据库接口。

2.使用步骤

导入jar包(丰富的工具类)

获取和数据库的连接(用户名、密码)

通过程序执行SQL

通过程序处理结果

3.idea 创建项目并导入jar包

创建stage2 Java工程
创建lib目录，拷贝驱动objbc6-11.1.0.7.0到lib目录下
项目引用这个外部jar包

4.案例

package cn.tedu.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//利用jdbc,操作数据库
//需求,查询部门表的所有数据
public class Test1 {
    public static void main(String[] args) throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库的连接
        /*String url="协议://数据库的服务器的IP地址:端口号/数据库名*/
        String url= "jdbc:mysql://localhost:3306/cgb211101";
        Connection c = DriverManager.getConnection(url,"root","root");
        //3.获取传输器
        Statement s = c.createStatement();
        //4.执行SQL
        ResultSet r = s.executeQuery("select * from dept");//执行查询的SQL
        System.out.println("java程序与数据库连接成功!!!");
        //5.处理结果--遍历结果
        //next()从来判断,只要r里有数据就返回true,没有数据就返回false
        while (r.next()){
            //获取数据getXxx()
            int d1= r.getInt(1);//获取第一列的一个整数值
            String d2= r.getString(2);//获取第二列的一个整数值
            String d3= r.getString(3);//列的一个整数值
            System.out.println(d1+d2+d3);
        }
        //6.关闭资源
        r.close();//关闭结果集ResultSet
        s.close();//关闭传输器Statement
        c.close();//关闭连接Connection
    }
}

5.SQL攻击注入

/*自己准备user2表(id/name/password),准备数据
    CREATE TABLE `user2` (
          `id` int(11)  PRIMARY KEY  auto_increment,
          `name` varchar(10) default NULL,
          `password` varchar(10) default NULL
    ) ;
 */
//需求：利用jdbc,根据用户名和密码查询cgb2104库里的user表
//SQL注入攻击问题
private static void login() {
    try{
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql:///cgb2104?characterEncoding=utf8";
        Connection conn = DriverManager.getConnection(url, "root", "root");
        Statement st = conn.createStatement();
// String sql ="select * from user2 where name='jack' and password='123456'";//写死了

        String user = new Scanner(System.in).nextLine();//用户输入jack'#
        String pwd = new Scanner(System.in).nextLine();
        //SQL注入攻击问题：本质上是因为SQL语句中出现了特殊符号#,改变了SQL语义
String sql ="select * from user2 where name='"+user+"' and password='"+pwd+"'";
        ResultSet rs = st.executeQuery(sql);//执行查询的SQL,返回结果集
        if(rs.next()){
            System.out.println("登录成功~");
        }else{
            System.out.println("登录失败~");
        }
        st.close();
        conn.close();
    }catch(Exception e){
        e.printStackTrace();//有异常,直接打印异常信息
        //System.out.println("执行失败。。。");//上线
    }
}

6.SQL注入的解决方案

package cn.tedu.jdbc;
import java.sql.*;
import java.util.Scanner;
public class Test7 {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url= "jdbc:mysql://localhost:3306/cgb211101";
        Connection c = DriverManager.getConnection(url,"root","root");
        System.out.println("请您输入账号:");
        String s1 = new Scanner(System.in).nextLine();
        System.out.println("请您输入密码:");
        String s2 = new Scanner(System.in).nextLine();

        //Statement s = c.createStatement();
        //String sql="select * from user where name='"+s1+"' and pwd='"+s2+"'";
        //动态拼接SQL语句
        //新的传输器,执行的SQL有新写法--sql骨架
        String sql= "select * from user where name=? and pwd=?";
        //获取新的传输器--安全/高效
        PreparedStatement s = c.prepareStatement(sql);
        s.setString(1,s1);//给第一个问号?,设置s1的值
        s.setString(2,s2);//给第二个问号?,设置s2的值
        ResultSet r = s.executeQuery();
        //处理结果集(根据用户名和密码去查,只会查出来一条记录)
        if (r.next()){//如果查到了数据,才表示数据库里面有这个用户信息
            System.out.println("登录成功");
        }else {//如果没有查到数据信息
            System.out.println("请重新输入...");
        }
        //释放资源
        r.close();
        s.close();
        c.close();
        //问题:当用户输入特殊值:小张'#时,,甚至不需要密码也能登录
        //原因:#在SQL中表示注释的意思,相当于后面的条件被注释掉了
        //select * from user where name='jack'#' and pad='123456'
        //现象叫SQL攻击/SQL注入,本质上就是因为SQL语句中出现了特殊符号#
        //导致了.SQL语义发生改变
        //createStatement传输器不安全,低效,改为prepareStatement:把特殊符号当做普通的文本符号在使用,没有注释的意思了
    }
}

7.优化: 提供jdbc的工具类

package cn.tedu.jdbc;

import java.sql.*;

//充当了jdbc的工具类,抽取一些共性代码
public class JDBCUtils {
    /**
     * 释放资源
     * @param r 结果集
     * @param s 传输器
     * @param c 连接器
     */
    static public void close(ResultSet r, PreparedStatement s,Connection c){
        if(r != null){//防止了空指针异常
            try {
                r.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                r = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(s != null) {//防止空指针异常
            try {
                s.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                s = null;//手动置空,等着GC进行垃圾回收
            }
        }
        if(c != null) {//防止空指针异常
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {//就是怕close()执行失败导致发生了异常,进行了catch
                c = null;//手动置空,等着GC进行垃圾回收
            }
        }
    }
    /**
     * 获取数据库的连接
     * @return 将给调用者返回一个和数据库连接的对象Connection
     * @throws Exception
     * static:保证资源在内存中,贮存的时间长.只会加载一次节省内存
     * public:工具类可以被所有人使用,最大的访问权限方便调用来调用
     */
    static public Connection get() throws Exception{
        //1,注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2,获取连接
        String url="jdbc:mysql://localhost:3306/cgb211101?characterEncoding=utf8";
        Connection c = DriverManager.getConnection(url, "root", "root");
        //把获取到的数据库的连接,返回给调用者
        return c;
    }
}

8.使用工具类(用新的传输器新增)

package cn.tedu.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

//利用新的传输器结合着工具类,新增一个用户信息
public class Test6 {
    public static void main(String[] args) {
       //扩大变量的作用范围:为了让try  catch  finally都能用
       Connection c = null;
       PreparedStatement p = null;
       try {
            //1,利用工具类,来获取数据库的连接
            c = JDBCUtils.get();
            //2,获取传输器,执行SQL
            String sql="insert into user values(null,?,?)";
            p = c.prepareStatement(sql);
            //给SQL绑定参数
            p.setObject(1,"jerry");
            p.setObject(2,"123");
            //3,执行SQL
            p.executeUpdate();//执行增删改的SQL,返回一个影响行数(通常不处理)
       }catch (Exception e){
           System.out.println("数据插入失败!!");
       }finally {//保证一定会被执行的代码
           //利用工具类close完成释放资源(新增业务,没有结果集,传入null就可以了)
           JDBCUtils.close(null,p,c);
       }
    }
}