第四次学习任务总结

域完整性约束
非空约束

INSERT INTO `SUBJECT`(SUBJECTNAME,SUBJECTHOURS)
VALUES(NULL,20);#ERRORS

INSERT INTO `SUBJECT`(SUBJECTNAME,SUBJECTHOURS)
VALUES('C++',DEFAULT);

默认值约束

INSERT INTO `SUBJECT`(SUBJECTNAME,SUBJECTHOURS)
VALUES('C++',DEFAULT);

引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名（列名）
详解：FOREIGN KEY 引用的外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值
当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）

约束整合练习

事务

事务是一个原子操作，是一个最小执行单元，可以由一个或多个SQL语句组成，在同一个事务中，所有SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。
事务的边界：
开始：连接到数据库，执行一条DML语句，上一个事务结束后，又输入了一条DML语句，即事务的开始
结束：
1.提交
a：显示提交：commit
b：隐式提交：一条创建，删除语句，正常退出（客户端退出连接）
2.回滚
a：显示回滚：rollback
b:隐式回滚：非正常退出（断电，宕机），执行了创建，删除语句，但失败了，会为这个无效的语句执行回滚。
事务的原理
数据库会为每个客户端的维护一个空间独立的缓存区（回滚段），一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL语句均正常结束（commit），才会将回滚段中的数据同步到数据库，否则无论因为那种原因失败，整个事务将回滚（rollback）。
事务的特性（ACID）（面试可能会考）
Atomicity（原子性）
表示一个事务内地所有操作是一个整体，要么全成功，要么全失败
Consistency（一致性）
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态
Isolation（隔离性）
事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态数据。
Durability（持久性）
持久性事务完成之后，它对于系统的影响是永久性的。
事务应用
应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚。

#事务完成转账
#A 账户给 B 账户转账
CREATE TABLE ACCOUNT(
			ID INT PRIMARY KEY AUTO_INCREMENT,
			MONEY INT NOT NULL
)CHARSET =utf8;
INSERT INTO ACCOUNT(MONEY)VALUES(10000);
INSERT INTO ACCOUNT(MONEY)VALUES(1000);
SELECT * FROM ACCOUNT;
#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';

#用户授权
GRANT ALL ON companydb.* TO `zhangsan`;
#撤销权限(只有账户再次连接客户端才能生效)
REVOKE ALL ON companydb.* FROM `zhangsan`;
#删除用户
DROP USER `zhangsan`;

视图

视图，虚拟表，从一个表或多个表中查询出来的表，作用和真实表一样，包含一系列带有行和列的数据，视图中，用户可以使用select语句查询数据，也可以使用insert，update，delete修改记录，视图可以使用户操作方便，并保障数据库系统安全。
视图特点
优点：
简单化，数据所见即所有
安全性，用户只能查询或修改他们所能见到得到的数据。
逻辑独立性，可以屏蔽真实表结构变化带来的影响。
缺点：
性能相对较差，简单的查询也会变得稍显复杂
修改不方便，特别是复杂的聚合视图基本无法修改。

视图的创建

#CREATE VIEW 视图名AS 查询语句
SELECT * FROM t_jobs;
CREATE VIEW t_empinfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM t_employees;
select * FROM T_EMPINFO;

多表连接

CREATE VIEW T_DEPARTMENTINFO
AS
SELECT EMPLOYEE_ID , t_employees.DEPARTMENT_ID,t_locations.LOCATION_ID FROM t_employees
INNER JOIN t_departments
ON t_employees.DEPARTMENT_ID = t_departments.DEPARTMENT_ID
INNER JOIN t_locations
ON t_locations.LOCATION_ID = t_departments.LOCATION_ID;

视图的使用

SELECT * FROM T_DEPARTMENTINFO;

试图修改的两种方法

两种方法（都要后面接更新语句）
1.CREATE OR REPLACE VIEW 视图名AS查询语句(不确定视图是否存在而使用)
#存在则更新，不存在则创建

CREATE OR REPLACE VIEW t_empinfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,JOB_ID FROM t_employees;
select * FROM T_EMPINFO;

2.ALTER VIEW 视图名AS 查询语句（确定视图已经存在）

ALTER VIEW t_empinfo
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL FROM t_employees;
select * FROM T_EMPINFO;

删除视图（原表不会发生改变）

DROP VIEW T_EMPINFO;

视图的注意事项

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

** SQL语言分类总结 **

数据查询语言DQL： SELECT , WHERE , ORDER BY , GROUP BY , HAVING.
数据定义语言DDL： CREATE , ALTER , DROP .
数据库操作语言DML:INSERT , UPDATE , DELETE.
事务处理语言TPL: COMMIT , ROLLBACK.
数据控制语言DCL: GRANT , REVOKE.

JDBC

JDBC java连接数据库，可以使用java语言连接数据库完成CRUD操作
JDBC核心思想：
java中定义了访问数据库的接口，可以为多种关系型数据库提供统一的访问方式。由数据库厂商提供驱动实现类（driver数据库驱动）

JDBC开发步骤

1.注册驱动 加载驱动
2.获得连接
3.获取发送SQL语句的对象
4.编写SQL语句，执行SQL语句
5.处理接收数据
6.释放资源，先开后关

 注意：在编写DML语句时，一定要注意字符串参数的引号是英文下的单引号'值';
    DML语句：增删改时，返回受影响行数（int）
    DQL语句：查询时，返回结果数据时（ResultSet结果集）

public class textJDBC {
    public static void main(String[] args) throws Exception{
        //注册驱动 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获得连接
        String url="jdbc:mysql://localhost:3306/companydb?characteEncoding=utf-8&serverTimezone=UTC";
        String user = "root";
        String password="password";
        Connection connection = DriverManager.getConnection(url,user,password);

        if(connection != null){
        System.out.println("连接成功数据库了！");
        }else{
            System.out.println("连接失败了！");
        }
        //3.获取发送SQL语句的对象
        Statement statement = connection.createStatement();
        //4.编写SQL语句，执行SQL语句
        String sql ="insert into t_jobs(job_id,job_title,min_salary,max_salary) values('h5_Mar','h5_Manager',4000,10000)";
        int result = statement.executeUpdate(sql);//DML操作调用方法
//        System.out.println(result);
//      5.处理接收结果
        if(result == 1){
            System.out.println("成功！");
        }else{
            System.out.println("失败！");
        }
        //释放资源，先开后关
        statement.close();
        connection.close();
        /*
        注意：在编写DML语句时，一定要注意字符串参数的引号是英文下的单引号'值';
        DML语句：增删改时，返回受影响行数（int）
        DQL语句：查询时，返回结果数据时（ResultSet结果集）
        */
    }
}

执行SQL语句传入数据类型

1.增删改语句传入类型为int

String sql ="insert into t_jobs(job_id,job_title,min_salary,max_salary) values('h5_Mar','h5_Manager',4000,10000)";
        int result = statement.executeUpdate(sql);//DML操作调用方法

2.查找语句传入类型为resultset类型

        ResultSet resultSet = statement.executeQuery("select * from t_jobs;");

ResultSet结果集
1.resultset结果遍历（将查找列名从左往右从1开始编号）

while (resultSet.next()){
            String job_id =resultSet.getString(1);
            String job_title = resultSet.getString(2);
            String min_salary = resultSet.getString(3);
            String max_salary = resultSet.getString(4);
            System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
        }

2.ResultSet结果集遍历（直接用列名遍历数据）

while (resultSet.next()) {
            String job_id = resultSet.getString("job_id");
            String job_title =resultSet.getString("job_title");
            String min_salary = resultSet.getString("min_salary");
            String max_salary = resultSet.getString("max_salary");
            System.out.println(job_id+"\t"+job_title+"\t"+min_salary+"\t"+max_salary);
        }

Statement和PreparedStatement对象

SQL注入问题

//存在SQL注入问题
        ResultSet resultSet = statement.executeQuery("select * from user where username='"+username+"' and password = '"+password+"';");

SQL注入问题
PreparedStatement 继承了 statement接口，执行SQL语句方法无异
1.预编译SQL语句，效率高
2.安全，避免SQL注入
3.可以动态的填充数据，执行多个同构的SQL语句

        PreparedStatement preparedStatement = connection.prepareStatement("select * from user where username=? and password =?;");
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);