MYSQL(二)

一、事务

       （1）什么是事务？

                         为了完成某个业务而对数据库进行一系列操作，这些操作要么全部成功，要么全部失败。比如，“转帐”。资金帐户 -50 ,股票帐户 +50。

       （2）事务的特点？

                       1）原子性：事务所涉及的各个操作要么全部成功，要么全部失败。

                      2）一致性：事务完成之后，不能有非法的数据写入数据库。

                     3）隔离性：多个事务可以并发执行，可以在一定程度上彼此不受影响。

                     4）持久性：事务完成之后，数据要写入数据库（也就是说数据要保存在硬盘上）。

       （3）隔离级别

                   1）读未提交：一个事务可以读取到另外一个事务尚未提交的数据，即发生了“脏读”，此外，该隔离级别还可能产生“不可重复读取”和“幻影读取”问题 

                     2）读已提交: 一个事务只能读取到另外一个事务已经提交的数据。该隔离级别解决了“脏读”问题，但是有可能会产生“不可重复读取”和“幻影读取”问题。

                      3）可重复读取:在同一个事务当中，多次查询（查询条件不变），得到的结果是一样的。该隔离级别解决了“脏读”问题、“不可重复读取”问题，但是仍然有可能产生“幻影读取”问题。  

                       4）序列化: 多个事务不能并发执行了，只能一个一个执行（排队）。该隔离级别解决了“脏读”、“不可重复读取”和“幻影读取”问题。但是，程序的并发性没有了，也就是说，程序整体的性能会下降。

         总结：

                a.隔离级别从低到高依次是“读未提交”----》”读已提交“----》”可重复读取“---》”序列化“。

                b.隔离级越高，并发性越低。

               c.在实际应用当中，要依据业务需要设置相应的隔离级别，一般来说，对数据一致性要求不高的场合，可以将事务隔离级别设置得低一些，常见的是设置为”读已提交“，反之，应该设置得高一些，比如银行，经常将隔离级别设置为序列化。  

二、视图

     （1）什么是视图？

                      是基于已的表或者视图创建的虚拟表。

        （2）如何创建视图？

                         

 create view 视图名 as select

例：

create database jsd2007db default character set utf8;

use jsd2007db;

create table t_employee(
id int primary key auto_increment,
name varchar(50),
salary int,
age int
);
insert into t_employee values(null,'King',25000,33);
insert into t_employee values(null,'Sally',15000,23);
insert into t_employee values(null,'Eric',28000,43);

create view v_employee as select * from t_employee;

（3）如何删除视图？

drop view 视图名

 drop view v_employee;

（4）视图的优点

a. 简单化：

可以将一些复杂的查询（比如基于多张表的join查询）定义成一个视图。

b.逻辑独立性：

可以屏蔽真实表（底层表）结构变化带来的影响。

c.安全性：

开发人员只能查询到他们所能看到的数据。

（5）视图的缺点：

a.视图并不存放任何数据，也就是说，对视图的查询，会转换成对底层表的查询。比起直接查询表，查询视图来慢一些。

b.对视图进行添加、删除、修改会有一些限制（多表构成的视图，不能够进行这些操作）。

补充知识：

一条sql语句的执行过程: select ...from a join b on .... where ...group by...having ...order by...limit 。

执行顺序：

step1. from语句：对a表和b表，先执行笛卡尔积的运算,生成临时表v1。

step2. on语句：对临时表v1按照on条件进行过滤，生成临时表v2。

step3. where语句：对临时表v2按照where条件进行过滤，生成临时表v3。

step4. group by 语句：对临时表v3进行分组，生成临时表v4。

step5. having语句：对临时表v4进行过滤， 生成临时表v5。

step6.select语句：对临时表v5按照select,对字段进行选择，生成临时表v6。

step7.order by语句：对临时表v6进行排序，生成生成临时表v7。

step8.limit语句：对临时表v7进行分页处理。

比如select s.name,d.name,d.city from t_staff s join t_dept d on s.dept_id = d.id这条sql语句，执行过程如下：

三、约束 

数据库中的五种约束
#五大约束
　　1、主键约束（Primay Key Coustraint） 唯一性，非空性
　　2、唯一约束 （Unique Counstraint）唯一性，可以空，但只能有一个
　　3、检查约束 （Check Counstraint）对该列数据的范围、格式的限制（如：年龄、性别等）
　　4、默认约束 （Default Counstraint）该数据的默认值
　　5、外键约束 （Foreign Key Counstraint）需要建立两表间的关系并引用主表的列

#五大约束的语法示例
1、添加主键约束（将UserId作为主键）

 alter table UserId
add constraint PK_UserId primary key (UserId)

2、添加唯一约束（身份证号唯一，因为每个人的都不一样）

　　alter table UserInfo
　　add constraint UQ_IDNumber unique(IdentityCardNumber)

3、添加默认约束（如果地址不填 默认为“地址不详”）

　alter table UserInfo
　　add constraint DF_UserAddress default (‘地址不详’) for UserAddress

4、添加检查约束 （对年龄加以限定 20-40岁之间）

alter table UserInfo
add constraint CK_UserAge check (UserAge between 20 and 40)
alter table UserInfo
add constraint CK_UserSex check (UserSex=’男’ or UserSex=’女′)

5、添加外键约束 (主表UserInfo和从表UserOrder建立关系，关联字段UserId)

alter table UserOrder
add constraint FK_UserId_UserId foreign key(UserId)references UserInfo(UserId)

例:

--主表，指的是被其它表参照的表。
 create table t_class(
 	id int primary key,
 	name varchar(50)
 );
 --插入记录时，要先插入主表中相应的记录，然后再插入从表中的记录
 insert into t_class values(100,'jsd2007');
 insert into t_class values(200,'jsd2008');
 
 --从表，指的是该表中的外键值必须参照主表的主键值
 create table t_student(
 	id int primary key auto_increment,
 	name varchar(50),
    cid int,
 	foreign key(cid) references t_class(id)
 );
 insert into t_student values(null,'Sally',100);
 insert into t_student values(null,'King',200);
 --外键值可以为null。
 insert into t_student values(null,'Eric',null);

#SQL Server中五大约束详解
　　约束（Constraint）是Microsoft SQL Server 提供的自动保持数据库完整性的一种方法，定义了可输入表或表的单个列中的数据的限制条件。在SQL Server 中有5 种约束：主关键字约束（Primary Key Constraint）、外关键字约束（Foreign Key Constraint）、惟一性约束（Unique Constraint）、检查约束（Check Constraint）和缺省约束（Default Constraint）。

1、主关键字约束
　　主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，即能惟一地指定一行记录。每个表中只能有一列被指定为主关键字，且IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性。

此处应有说明：多列组成的主键叫联合主键，而且联合主键约束只能设定为表级约束；单列组成的主键，既可设定为列级约束，也可以设定为表级约束。

联合主键：

联合主键就是用2个或2个以上的字段组成主键。用这个主键包含的字段作为主键，这个组合在数据表中是唯一，且加了主键索引。
　　 可以这么理解，比如，你的订单表里有很多字段，一般情况只要有个订单号bill_no做主键就可以了，但是，现在要求可能会有补充订单，使用相同的订单号，那么这时单独使用订单号就不可以了，因为会有重复。那么你可以再使用个订单序列号bill_seq来作为区别。把bill_no和bill_seq设成联合主键。即使bill_no相同，bill_seq不同也是可以的。

2、外关键字约束
　　外关键字约束定义了表之间的关系。当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时，就可以将这些列或列的组合定义为外关键字，并设定它适合哪个表中哪些列相关联。这样，当在定义主关键字约束的表中更新列值，时其它表中有与之相关联的外关键字约束的表中的外关键字列也将被相应地做相同的更新。外关键字约束的作用还体现在，当向含有外关键字的表插入数据时，如果与之相关联的表的列中无与插入的外关键字列值相同的值时，系统会拒绝插入数据。与主关键字相同，不能使用一个定义为 TEXT 或IMAGE 数据类型的列创建外关键字。外关键字最多由16 个列组成。

指定在删除表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在删除父表数据行时会将子表中对应的数据行删除；如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的删除操作回滚。NO ACTION 是缺省值。

          ON UPDATE {CASCADE | NO ACTION}
　　　指定在更新表中数据时，对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下，如果指定了值CASCADE，则在更新父表数据行时会将子表中对应的数据行更新；如果指定的是NO ACTION，则SQL Server 会产生一个错误，并将父表中的更新操作回滚。NO ACTION 是缺省值。

          NOT FOR REPLICATION
　 　  指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用。

3、唯一性约束
　  　唯一性约束指定一个或多个列的组合的值具有唯一性，以防止在列中输入重复的值。唯一性约束指定的列可以有NULL 属性。由于主关键字值是具有唯一性的，因此主关键字列不能再设定唯一性约束。唯一性约束最多由16 个列组成

4、检查约束
　  　检查约束对输入列或整个表中的值设置检查条件，以限制输入值，保证数据库的数据完整性。可以对每个列设置复合检查。

        注意：对计算列不能作除检查约束外的任何约束。

5、缺省约束
　 　缺省约束通过定义列的缺省值或使用数据库的缺省值对象绑定表的列，来指定列的缺省值。SQL Server 推荐使用缺省约束，而不使用定义缺省值的方式来指定列的缺省值。

6、列约束和表约束
　　对于数据库来说，约束又分为列约束（Column Constraint）和表约束（Table Constraint）。
      列约束作为列定义的一部分只作用于此列本身。表约束作为表定义的一部分，可以作用于多个列。

由上可知，1，主键、外键、唯一、检查这四项，既可以创建列约束，也可以创建表约束。而缺省 和 非空只能创建列约束。

7、关于约束的其他操作
　　#删除约束      

ALTER TABLE employees DROP CONSTRAINT emp_manager_fk;

　　#关闭约束

  ALTER TABLE employees DISABLE CONSTRAINT emp_emp_id_pk CASCADE; //如果没有被引用则不需CASCADE关键字

　　#打开约束

ALTER TABLE employees ENABLE CONSTRAINT emp_emp_id_pk; //注意,打开一个先前关闭的被引用的主键约束,并不能自动打开相关的外部键约束

注：
　　1. 添加主键约束会自动创建唯一索引。如果表中尚未创建 聚焦索引，则自动创建聚焦唯一索引。如果表中已存在聚焦索引，则自动创建非聚焦索引。

        2. 添加唯一约束会自动创建唯一索引。如果未在unique关键字后加上［nonclustered｜clustered］,则默认会创建非聚焦索引。

四、存储过程

           (1) 概念: 

                     存储过程是一组存储在数据库中为了完成特定功能的SQL语句

         （2）创建存储过程

                create procedure 存储过程名（【参数类型  参数名  数据类型】）

注:

     参数类型有三种,分别是in ,out ,inout :

            in (输入参数) : 是缺省值 , 调用存储过程时,必须指定改参数的值,该参数的值可以在存储过程中进行修改,但是改参数的值不能够返回

           out(输出参数) : 该参数的值可以在存储过程中进行修改,并且参数的值能够返回

          inout(输入输出参数) : 该参数的值可以在存储过程中进行修改,并且参数的值能够返回

 -- delimiter // 这句话的作用是将结束符号由";"改为//,避免控制台执行了不完整的存储过程。
 delimiter //
 create procedure proc_find()
 begin 
 	select * from t_employee;
 end
 //
 delimiter ;
 
 --调用存储过程


 
 
 --创建一个带in参数的存储过程
 delimiter //
 create procedure proc_find2(in sid int)
 begin
 	select * from t_employee where id=sid;
 end
 //
 delimiter ;
 
 --in参数在调用时，必须指定相应的值。
 call proc_find2(1);
 
 
 --创建一个带 out参数的存储过程,out参数可以返回。
 
 delimiter //
 create procedure proc_find3(out max_sal int)
 begin
 	select max(salary) into max_sal from t_employee;
 end
 //
 delimiter ;
 
 -- @maxSal是会话变量(指的是在当前连接中有效的变量)

         （3）使用jdbc调用存储过程

DBUtils类:

public class DBUtils {
    public static Connection getConnection() throws Exception {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsd2007db?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true","root","root");

        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
        return connection;
    }

    public static void main(String[] args) throws Exception {
        System.out.println(getConnection());
    }
}

测试类:

public class CallProcedureDemo {
    /**
     * 调用不带参的存储过程
     */
    public static void test01() throws Exception {
        try (Connection connection = DBUtils.getConnection()) {
            //调用存储过程,必须写CallableStatement,该接口是PreparedStatement的子接口
            CallableStatement callableStatement = connection.prepareCall("{call proc_find()}");
            ResultSet resultSet = callableStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getInt("salary"));
                System.out.println(resultSet.getInt("age"));
                System.out.println("------------------------");
            }
        }catch (Exception e){
             e.printStackTrace();
             throw e;
        }
    }
    public static void test02() throws Exception {
        try (Connection connection = DBUtils.getConnection()) {
            //调用存储过程,必须写CallableStatement,该接口是PreparedStatement的子接口
            CallableStatement callableStatement = connection.prepareCall("{call proc_find2(?)}");
            callableStatement.setInt(1,3);
            ResultSet resultSet = callableStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getInt("id"));
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getInt("salary"));
                System.out.println(resultSet.getInt("age"));
                System.out.println("----------------------------------------------------------------");
            }
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
    }
    public static void main(String[] args) throws Exception {
        test01();
        test02();
    }
}

（4）存储过程的优缺点

        1.优点

                    a.复用代码：可以将多个应用相同的业务逻辑使用存储过程集中写在数据库这一端，这样各个应用就不用写了。

                    b.安全性：防止sql注入，并且可以给存储过程赋于相应的权限，只有相应权限的客户端才能访问。

                   c.提升性能：如果某个业务功能需要客户端与数据库之间进行多次访问，每次访问会传递过来一些sql语句，然后数据库返回中间结果（中间结果并不重要），最后才能完成这些业务功能。也就是说，客户端与数据库之间要进行数据                                          密集性操作，这时，使用存储过程可以减少客户端与数据库之间的访问次数（可以将每次访问所涉及的sql集中写在存储过程里面）。

      2.缺点：

                   a.存储过程依赖于特定的数据库，很难移植。

                   b.存储过程功能有限。

五、索引

(1) 索引定义

索引是对数据库表中的一列或者多列进行排序的一种