Java面试手册：数据库 ④

触发器

• 触发器是一种特殊类型的存储过程，不由用户直接调用。创建触发器时会对其进行定义，以便在对特定表或列作特定类型的数据修改时执行。触发器可以查询其他的表，而且可以包含复杂的SQL语句他们主要用于强制服从复杂业务的规则或要求。
• 触发器是与表相关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据库的完整性。

• 触发器在数据库中定义了一系列的操作，可以在对指定表进行插入，更新或者删除的同时自动执行这些操作。
  • 例如：西安北站所属办公室----->北站发一篇公告，数据库向西安北站添加一条记录--->同时向办公室数据库中添加同样的记录。 这是执行了两条SQL语句
  • 如果定义一个触发器（向西安北站添加一条记录的时候，同时添加同样的记录到办公室） 这样执行一条SQL语句。

触发器的优点

• 在数据库中的，不必编写每个触发器在应用程序（java）中执行的操作。
• 开发更快，因为触发器是编写在数据库中的，不必编写每个触发器在应用程序（java）中执行的操作。
• 更容易维护（不需要频繁的修改代码，减少成本），定义触发器后，访问目标表数据库会自动调用触发器。
• 业务全局实现，如果需要修改业务，只需要修改触发器，不需要修改应用程序代码。

触发器的缺点

尽管触发器有很多优点，但是在实际的项目开发中，特别是OOP思想的深入，触发器的弊端也逐渐突显，主要：

• 过多的触发器使得数据逻辑变得复杂
• 数据操作比较隐含，不易进行调整修改
• 触发器的功能逐渐在代码逻辑或事务中替代实现，更符合OO思想。
• 建议谨慎使用触发器。

触发器的分类

SQL Server 包括两种常规类型的触发器：数据操作语言 (DML) 触发器和数据定义语言 (DDL) 触发器。 当INSERT、UPDATE 或 DELETE 语句修改指定表或视图中的数据时，可以使用 DML 触发器。 DDL 触发器激发存储过程以响应各种 DDL 语句，这些语句主要以CREATE、ALTER 和 DROP 开头。 DDL 触发器可用于管理任务.

• 通常说的触发器就是DML触发器：DML 触发器在 INSERT、UPDATE 和 DELETE 语句上操作，并且有助于在表或视图中修改数据时强制业务规则，扩展数据完整性。

• 前置触发器 ：在对目标表进行 更新，插入之前执行。即在操作之前触发
• 后置触发器 ：对目标包进行 更新，插入，删除 操作之后执行
• before delete 触发器: 在对目标表删除之前执行
• insted of 触发器： 对复杂的视图 执行插入，更新和删除时执行。

触发器的作用

• 触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改。
• 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。例如，触发器可以使用另一个表中的 SELECT 比较插入或更新的数据，以及执行其它操作，如修改数据或显示用户定义错误信息。
• 触发器还可以强制执行业务规则
• 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。

触发器的使用

   语法
   ------------------------------------------------------
       CREATE TRIGGER trigger_name 
       ON {table_name | view_name} 
       {FOR | After | Instead of} [insert, update,delete]
       AS           
       sql_statement  
   -------------------------------------------------------       
   关键词 trigger
   -------------------------------------------------------
     create trigger  触发器名称  //创建触发器
     after   insert   on   table1    //触发器类型，什么时候启动触发器 
     for  each  row  //受影响的行
     begin  //开始
     insert  into   table2(table2_id)  values(new.table1_id)；//  触发器要执行的业务
     end；

• 删除触发器: drop trigger 触发器名字；

视图

创建一张虚拟的表：少执行SQL语句，因为在和java连接的时候，每执行一条语句就会连接JDBC接口，消耗资源。

   普通员工无法查看薪资，管理成可以查看，
     SQL语句举例：
        if( type = "employee" ){
               String sql = "select  id, name  from  employee";
             }
                    
             if (tyepe = "manager"){
             String sql  =  " select  *  from employee";
             }
           String  sql = " select  *  from  employee";
           最笨的办法是创建两张表，区别是一张有薪资  一张没有薪资字段。维护数据的时候，必须同时维护两张表。

• 什么是视图：一个人包含某个查询的虚拟表

• 对视图进行操作，依托于真实的表
  • 主要目的简化语句
  • 对性能没有改善
• 视图允许嵌套
• 视图不能索引，没有关联，没有默认值
• 视图的用途

• 筛选表中的行，降低数据库的复杂程度，
• 防止未经许可的用户访问敏感数据，提高安全性
• 将多个物理数据抽象为一个逻辑数据库
• 一次编写多次使用
• 可授权访问表的特定部分
• 封装计算字段
• 视图的基本操作和语法
  • 创建视图 CREATE VIEW view_name AS SELECT column_naem(s) FROM table_name WHERE condition ---------------------------------------------- 创建视图 ，关键字 view：create view 视图名 as select 字段 from 表名；
  • 更新视图 CREATE OR REPLACE VIEW view_name AS SELECT table_name WHERE condition
  • 撤销视图 DROP VIEW view_name
  • 使用视图 SELECT * FROM view_name
• 他是一条SQL查询语句
• 本身不包含数据
• 是一张虚表

查询数据

• 语法 SELECT select_list //要查询的列名称 FROM table_list // 要查询的表名称 WHERE condition //行条件 GROUP BY grouping_columns // 对结果分组 HAVING condition //分组后的条件 ORDER BY sorting_columns // 对结果分组 LIMIT offset_start,row_count // 结果限定
• select
  • 通配符 * ---> select from table
  • 别名 as 什么情况下使用
  • 计算字段 拼接：sqlserver用“+”，Oracle用“||”，MySQL用“concat()”
  • 出去重复记录：distinct 一个列可能有多个重复的值，如果只想要不同的值，那么久用distinct：select distinct columns_name,column_name from table_name;
• 目标：from
  • 简单链接：inner join，若干表中有至少一个匹配，则返回行：
  • 全连接：full outer join：返回左右表中所有记录
  • 左连接left join 如果表中有至少一个匹配，则返回行
  • 有链接：right join，即使左表中没有匹配，也从右表中返回所有行
  • 别名：as --> select column_name from table_name as name
  • 链接join select column_name from table1 inner join table2 on table1.column_name=column_name select column_name(s) from table1 full outer join table2 on table1.column_name=table2.column_name; select column_name from table1 left join table2 on table1.column_name = table2.column_name left join 关键字从左表（table1）返回所用行，即使右表（table2）中没有匹配，如果右表中没有匹配，则结果为null。 select column_name from table1 right join table2 on table1.column_name = table2.column_name 关键字right join从右表中返回所有行，即使左表中没有匹配的，如果左表中没匹配则返回null。
• 过滤:where 查询EMP表中的SAL列中等于5000,3000,1500的值 select * from EMP where SAL（5000,3000,1500） ----------------------------------------------------------- select score from student where id=11 and score in (20，30): 相当于：select from student where id =11 and score=20 or score = 30; 查询emp中的comm列，中的空值 select * from emp where comm is null; --------------------------------------------------------------------- select score is null from student where id =9; 返回值为0或者1. select score btween 20 and 30 from student where id = 10;表示score>=20 & score <=3

SQL查询

• select语句:
  • select
  • from
  • where
  • order by
  • 大小写不敏感
  • Oracle中的AS可以省略
  • 别名（表&字段都可以有）
• 操作符
  • 比较操作符：=、<>(或者！=)> <
  • 逻辑操作符：between、in、like、unique、is null、exsts、all、any。
  • 链接操作符：and（优先级高于OR）,OR。
  • 求反操作符：not in
  • 算术操作符：+、-、*、/以及他们的组合
• 汇总查询
  • count、sum、max、min、avg
  • null值不考虑，除非使用count（*）
• 数据排序与分组
  • group by：他后面的的字段不能用别名，使用汇总函数的时候，才会用它select后面的字段（除了汇总函数）都要出现在他的后面。
  • having ：having就是为group而生的，用来过滤分组，where是对select结果进行限制。cube，rollup.

查询

• 多表关联查询：
  • 内联结：
  • 外联结：分为左联结和联结（left join 和 right join） select * from mess left join user on "user".id = 1; select * from student left join classes on student.cid = classes.id and classes.name = "Java"; select * from mess right join user on "user".id = 1; select * from student right join classes on student.cid = classes.id and classes.name = "Java";
  • 右联结：右表所有和左表满足条件的数据
  • 左联结：显示左边表的所有信息 和右边表满足条件的数据（在left join 左边的为左表 右边的为右表）
  • 嵌套查询： select * from orders where customer_id = (select id from sustomer where name ="卫庄")
  • 联结查询： select * from mess inner join user where mess.user_id = "user".id select * from student , clsses where student.cid and classes.name = "Java"; // 可以将长的字段名字或者表的 名字 都可以用 as + 新的字符代替原来的名字。

like(模糊查询)

• 如果不带%号，等同于精确查询，实际没有意义。模糊查询使用较多。%占位符 为空 不为null
  • like:查询某个字段包含“字符”关键字的数据 select * form student where name like "%查询的关键字符%"
  • 在数据库中查询以字符“我”开头的数据：select * from student where name like "我%"
  • 在数据库中查询以字符“我”结尾 的数据： elect * from student where name like "%我"
  • 查询字段长度为 n（即字符的个数，中英文一样） 的数据：用下划线条数表示长度n的数。select * from student where name like "_"；
  • name 长度为3，并且以“我” 开头;select * from student where name like "我_ _"
  • name 长度为3 ，并且“我” 在中间;select * from student where name like "我"；
  • name 长度为3，并且“我” 最后；select * from student where name like "_ _我"；
  • 字符集[charlist] :读取name以字母A到H字母开头的： select * from where name like regexp '^A.H';
  • 不在任何字符列表中的单一字符charlist ：选取name不以A到H字母开头的网站： select * from emp where like regexp '^A.H'

分组：group by

用于结合合计函数，根据一个或者多个列对结果集进行分组（其后面如果要添加条件不能使用where，而是having）

select * column_naem,function(column_name) from table_name GROUP BY column_name

having:

对分组后的数据进行筛选

查询每个部分的工资大于1200的员工平均工资，且平均工资大于2200
 select deptno,AVG(sal) from emp where sal>1200【分组之前的条件查询】 group by deptno HAVING AVG(sal)>2200;

排序：Oder by

根据指定的列对结果集进行排序

select column_name,column_name from table_name order by column_name ASC|DESC;（asc升序，desc降序）

子查询

• 语法：

select column_name[column_name] from table1[table2] where 
 column_name operator (select column_name[column_name] from table1[table2][where])

• 例子

select * from customers where id in (select id from customers where salary>4500)

• 组合查询：union，合并两个或多个select语句的结果
• union使用规则
  • union必须有两个或者两个以上的select组成，语句之间用union分离
  • union中的每个查询必须包含相同的列、表达式或聚集函数
  • 列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如不同的数值类型，不同的日期类型）
• union使用场景
  • 在一个查询中从不同的表返回结构数据
  • union只会选取结果中的不同的值，union all会选择选取重复的值
  • 对一个表执行多个查询，按一个查询返回数据
• union使用语法 ：

select column_name(s) from table1 union select column_name(s) from table2

• union使用列子 select cust_name,cust_contact,cust_emall from customers where cust_state in("il","in","mi") union select cust_name,cust_contact,cust_emall from customers where cust_name = "fun4All"

复杂度查询

• 多表查询
  • 等值联结（内联结）
  • 自联结
  • 外联结（left join，right join，full join）
  • 联结
• 要考虑的问题
  • 使用基表
  • 结合多个主键
  • 笛卡尔积
  • 根据数据字典写多表联结
• 子查询
  • 只能查询单个列
  • 子查询中不能使用order by
  • 使用子查询来查找不确定的值
  • 主要用于where语句和having语句
• 组合查询
  • union 去重合并
  • union all 合并
  • intersect 交集
  • expect（minus）差集