MySql 数据库2 事务、权限管理、视图 JDBC讲解1

---

  创建带有外键约束的关系表。
  使用 navicat 工具操作。
  先创建一张主表 t2，有 id 和 name 两个字段。再创建一张从表，也给 id 和 name 两个字段，在创建时可以选择外键关联，就是点击外键，再点左上的 + 号，让从表的 id 与主表的 id 关联。这样从表的 id 值必须是主表已有的 id 值，并且不能为 null。SQL 预览可以查看用代码怎么写。

  创建关系表时，一定要先创建主表，再创建从表。
  删除关系表时，先删除从表，再删除主表。

  事务。特性，Atomicity(原子性)，Consistency(一致性)，Isolation(隔离性)，Durability(持久性)。
  事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。
  数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

#事务完成转账
#A 账户给 B 账户转账。
#1.开启事务
START TRANSACTION;|setAutoCommit=0;#禁止自动提交 setAutoCommit=1;#开启自动提交
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了，执行 COMMIT；
COMMIT;
#4.事务内如果出现错误，执行 ROLLBACK;
ROLLBACK;

  权限管理。

#创建 新用户 和设置密码
CREATE USER `zhangsan` IDENTIFIED BY '123';

#将 companyDB下的所有表的权限都赋给 zhangsan
GRANT ALL ON companyDB.* TO `zhangsan`;

#将 zhangsan 的 companyDB 的权限撤销
REVOKE ALL ON companyDB.* FROM `zhangsan`;

#删除用户 zhangsan
DROP USER `zhangsan`;

  工具操作。
  点击 用户 -> + 添加 -> 对象权限 -> + 添加 -> 保存。

  视图。虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据。视图中，用户可以使用SELECT语句查询数据，也可以使用INSERT，UPDATE，DELETE修改记录，视图可以使用户操作方便，并保障数据库系统安全。

#创建 t_empInfo 的视图，其视图从 t_employees 表中查询到员工编号、员工姓名、员工邮箱、工资
CREATE VIEW t_empInfo 
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

#查询 t_empInfo 视图中编号为 101 的员工信息
SELECT * FROM t_empInfo where employee_id = '101';

# 修改视图
#方式 1：如果视图存在则进行修改，反之，进行创建
CREATE  OR  REPLACE VIEW t_empInfo 
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY,DEPARTMENT_ID from t_employees;

#方式 2：直接对已存在的视图进行修改
ALTER VIEW t_empInfo
AS 
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,SALARY from t_employees;

#删除t_empInfo视图
DROP VIEW t_empInfo;

  注意，视图不会独立存储数据，原表发生改变，视图也发生改变。没有优化任何查询性能。
  如果视图包含其中一种，则视图不可更新。聚合函数的结果，DISTINCT 去重后的结果，GROUP BY 分组后的结果，HAVING 筛选过滤后的结果，UNION、UNION ALL 联合后的结果。

  JDBC。
  JDBC（Java Database Connectivity） Java 连接数据库的规范（标准），可以使用 Java 语言连接数据库完成 CRUD 操作。

  环境搭建方式1。
  新建普通 java 工程，在 src 目录下新建 lib 文件夹，将驱动 mysql-connector-java-8.0.X 拖到项目的 lib 文件夹中，右键 lib -> Add as Library。
  5.x connection jar 包注册方式。

Class.forName("com.mysql.jdbc.Driver");

  环境搭建方式2。
  新建 Maven 工程，可以看到跟普通 java 工程的文件目录不一样。其中 src/main/java 里写 code，src/main/resources 里放一些配置文件。在 pom.xml 文件里放入以下代码，然后点击右下角弹出框的 Import Changes。等加载完成后就可以注册驱动。

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
</dependencies>

  8.x connection jar 包注册方式。

Class.forName("com.mysql.cj.jdbc.Driver");

  JDBC 开发步骤。

public class Test1 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 注册
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 连接 mydb1 数据库
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "123");
        // 执行 对象
        Statement statement = conn.createStatement();
        // 执行 插入数据
        String sql1 = "insert into student(sno,sname,ssex,sbirthday,class) values(110,'张三','男','2020-12-10 00:00:00','90000')";
		
		// 删除
        String sql1 = "delete from student where sno=110";

		// 执行结果 1 成功
        int result = statement.executeUpdate(sql1);
        System.out.println(result);

		// 查询操作
        String sql1 = "select * from student";
        
		// 查询结果
        ResultSet resultSet = statement.executeQuery(sql1);

        while (resultSet.next()) {
        // 下标从 1 开始 通过索引获取查询结果
//            int result1 = resultSet.getInt(1);
//            String result2 = resultSet.getString(2);

			// 通过字段名获取查询结果
            int result1 = resultSet.getInt("sno");
            String result2 = resultSet.getString("sname");
            System.out.println(result1 + "\t" + result2);
        }

		// 关闭 释放资源
        statement.close();
        conn.close();
    }
}

  SQL注入问题。
  用户输入的数据中有 SQL 关键字或语法并且参与了 SQL 语句的编译，导致 SQL 语句编译后的条件含义为 true，一直得到正确的结果。这种现象称为 SQL 注入。

public class Test2 {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {


        Scanner input = new Scanner(System.in);

        System.out.println("请输入用户名");
        String username = input.next();

        System.out.println("请输入密码");
        String password = input.next();

        login(username,password);        
    }

    private static void login(String username, String password) {

        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

             conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "123");

            statement = conn.createStatement();
            String sql1 = "select * from user where username='" + username +"'and password='" + password + "'";
             resultSet = statement.executeQuery(sql1);
            if (resultSet.next()) {
                System.out.println("登陆成功");
            } else {
				System.out.println("账号或密码错");
			}
        } catch ( SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭 释放资源
            resultSet.close();
        	statement.close();
        	conn.close();
        }
    }
}

  上面的例子，如果用户名输入 ’ or 1=1 # ，那么总是登陆成功。因为 sql 执行语句成为了 select * from user where username=’’ or 1=1 #…。
  解决这个问题 ，要用到 PreparedStatement。PreparedStatement 继承了 Statement 接口。预编译 SQL 语句，效率高；安全，避免SQL注入；可以动态的填充数据，执行多个同构的 SQL 语句。

String sql1 = "select * from user where username=? and password=?";
 PreparedStatement prst = conn.prepareStatement(sql1);
// 1，代表第 1 个 ? 占位符
prst.setString(1,username);
prst.setString(2,password);
resultSet  =  prst.executeQuery();

   使用 .properties 文件实现跨平台 和封装。

// db.properties
// 普通 java 工程放在 src 目录下，Maven 工程放在 resources 目录下。 

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mydb1
user=root
password=123

// 工具类

public class Myutil {

    private static String driver;
    private static String url;
    private static String user;
    private static String password;

	// 静态代码块 只执行一次
    static  {
        try {
            Properties pro = new Properties();
            // 通过反射找到文件地址
            InputStream resourceAsStream = Myutil.class.getClassLoader().getResourceAsStream("/db.properties");
            pro.load(resourceAsStream);

			// 取出文件内数据
            driver = pro.getProperty("driver");
             url = pro.getProperty("url");
             user = pro.getProperty("user");
             password = pro.getProperty("password");

			// 注册
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

	// 封装 连接方法
    public  static Connection sqlConnection() {
        Connection conn = null;

        try {
            conn = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return  conn;
    }

	// 封装释放 数据库资源方法
    public  static void closeAll(AutoCloseable ... ss) {
        for (AutoCloseable c: ss) {
            if (c != null) {
                try {
                    c.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

// 实体类
public class Score {

    static String sno;
    static String sname;
    static double degree;

    public Score() {
    }

    public Score(String sno,String sname,double degree) {
        this.sno = sno;
        this.sname = sname;
        this.degree = degree;
    }

    public static String getSno() {
        return sno;
    }

    public static void setSno(String sno) {
        Score.sno = sno;
    }

    public static String getSname() {
        return sname;
    }

    public static void setSname(String sname) {
        Score.sname = sname;
    }

    public static double getDegree() {
        return degree;
    }

    public static void setDegree(double degree) {
        Score.degree = degree;
    }

}

// 取出 sql 表中的数据 存储在 list 中
public class Test1 {

    public static void main(String[] args) throws SQLException {

        Connection conn = Myutil.sqlConnection();
        PreparedStatement preparedStatement = conn.prepareStatement("select  * from score");
        ResultSet resultSet = preparedStatement.executeQuery();

        List<Score> list = new ArrayList<Score>();

        while (resultSet.next()) {
            String sno = resultSet.getString("sno");
            String sname = resultSet.getString("cno");

            double degree = resultSet.getDouble("degree");

            Score score = new Score(sno,sname,degree);
            list.add(score);
        }

        for(Score s: list) {
            System.out.println(s);
        }
        
        Myutil.closeAll(resultSet,preparedStatement,conn);
    }

}

---