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

  • and:并且 select * from emp where sal>2000 and sal<3000
  • or:或者 select * from emp where sal> or sal <3000
  • not:非: select * from emp where not sal>=500;
  • between and 判断值是否在某个区间内:
  • is null:判断是否为null
  • 关系运算符:=、!=、<>(不等于)、>、<、<=、>=; --->select * from table_name where name="smith"
  • between..and..两者之间
  • in(set):判断值是否在某个确定的值的集合内
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)差集

原文发布于微信公众号 - Java大联盟(javaunion)

原文发表时间:2018-11-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一个爱吃西瓜的程序员

学习SQL【2】-数据库与SQL

一:数据库是什么? 1:定义 ● 将大量数据通过计算机加工而成的可以进行高效访问的数据集合称为数据库(DB)。 ● 用来管理数据库的计算机系统称为数据库管理系...

3569
来自专栏java一日一条

InnoDB引擎算法和优化

索引是应用程序设计和开发的一个重要方面。如果索引太多,应用的性能可能会受到影响;如果索引太少,对查询性能又会产生影响。

1021
来自专栏Danny的专栏

《高性能 MySQL》读书笔记

1、隔离级别有四种: READ UNCOMMITTED(未提交读),同事务中某个语句的修改,即使没有提交,对其他事务也是可见的。这个也叫脏读。 READ...

1912
来自专栏禅林阆苑

mysql学习总结04 — SQL数据操作

mysql 中 SELECT 命令类似于其他编程语言的 print 或 write,可用来显示字符串、数字、数学表达式的结果等

2523
来自专栏lestat's blog

mysql开发规范

命名规范 库名、表名、字段名必须使用小写字母,并采用下划线分割 库名、表名、字段名禁用超过32个字符。须见名知意 库名、表名、字段名禁用使 MySQL保留字 临...

44317
来自专栏乐沙弥的世界

绑定变量及其优缺点

    绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容 易折...

1012
来自专栏java学习

数据库介绍以及使用

第1章 数据库介绍 1.1 数据库概述 l 什么是数据库 数据库就是存储数据的仓库,其本质是一个文件系统,数据按照特定的格式将数据存储起来,用户可以...

3554
来自专栏张善友的专栏

Entity Framework Core 实现MySQL 的TimeStamp/RowVersion 并发控制

将通用的序列号生成器库 从SQL Server迁移到Mysql 遇到的一个问题,就是TimeStamp/RowVersion并发控制类型在非Microsoft ...

3348
来自专栏从流域到海域

SQL连接查询(最全面)

连接查询是关系数据库中最主要的查询,主要包括内连接、外连接和交叉连接等。通过连接运算符可以实现多个表查询。 在关系数据库管理系统中,表建立时各数据之间...

2567
来自专栏JavaEdge

MySQL必知必会分页whereupdatelimit字符串截取order by排序ength和char_lengthreplace函数1 键2 数据库事务的ACID3 视图4 删除连接

40214

扫码关注云+社区

领取腾讯云代金券