本次讲到where字句中经常用到的集中较为复杂的搜索条件,包括组合的查询条件、IN运算符、NOT运算符、LIKE运算符和相关通配符。

一、概述

在上一篇文章中已经介绍了审计的概念;本篇文章主要介绍如何创建审计,以及该收集哪些审核规范。

 

  优点

  学习本节需要用到一下两张表:

二、常用的审核对象

  1.
运行速度:对于很简单的sql,存储过程没有什么优势。对于复杂的业务逻辑,因为在存储过程创建的时候,数据库已经对其进行了一次解析和优化。存储过程一旦执行,在内存中就会保留一份这个存储过程,这样下次再执行同样的存储过程时,可以从内存中直接调用,所以执行速度会比普通sql快。 
  

图片 1图片 2

2.1、服务器审核对象

1.FAILED_LOGIN_GROUP( Audit Login Failed Event Class)

指示主体尝试登录到 SQL Server,等效于 Audit Login Failed Event Class,
比如:登入失败的操作

2.SERVER_OBJECT_CHANGE_GROUP(Audit Server Object Management)

针对服务器对象执行 CREATE、ALTER 或 DROP 操作时将引发此事件。等效于
Audit Server Object Management 事件类。
比如:删除链接服务器对象等凌驾于数据库级别以上的对象(权限除外)。

3.SERVER_ROLE_MEMBER_CHANGE_GROUP

向固定服务器角色添加登录名或从中删除登录名时将引发此事件, 此事件由
sp_addsrvrolemember 和 sp_dropsrvrolemember 存储过程引发。 等效于
Audit Add Login to Server Role Event Class。
比如:授予、撤销服务器角色等

4.SERVER_PRINCIPAL_CHANGE_GROUP

创建、更改或删除服务器主体时将引发此事件
比如:创建删除登入名等

5.SUCCESSFUL_LOGIN_GROUP

指示主体已成功登录到 SQL Server。
此类中的事件由新连接引发或由连接池中重用的连接引发。 等效于 Audit Login
Event Class。
注意:每一个连接会话都会记录,开启SUCCESSFUL_LOGIN_GROUP会产生很多的记录

6.USER_CHANGE_PASSWORD_GROUP

使用 ALTER USER 语句更改包含数据库用户的密码时,测试发现无效

  2.
 减少网络传输:存储过程直接就在数据库服务器上跑,所有的数据访问都在数据库服务器内部进行,不需要传输数据到其它服务器,所以会减少一定的网络传输。但是在存储过程中没有多次数据交互,那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网,大数据的访问的瓶颈会是硬盘的速度,而不是网速。

CREATE TABLE TEACHER
(
    TNO INT NOT NULL, --教工号
    TNAME CHAR(10) NOT NULL, --教师姓名
    CNO INT NOT NULL, --课程号
    SAL INT, --工资
    DNAME CHAR(10) NOT NULL, --所在系
    TSEX CHAR(2) NOT NULL, --性别
    AGE INT NOT NULL --年龄
)
alter table TEACHER alter column SAL INT null 
INSERT INTO
dbo.TEACHER
        ( TNO, TNAME, CNO, SAL, DNAME, TSEX, AGE )
VALUES  ( 0, -- TNO - int
          '', -- TNAME - char(10)
          0, -- CNO - int
          0, -- SAL - int
          '', -- DNAME - char(10)
          '', -- TSEX - char(2)
          0  -- AGE - int
          )
          SELECT * FROM dbo.TEACHER
INSERT INTO dbo.TEACHER VALUES( 3,'王永军',1,1000,'计算机','女',45)
INSERT INTO dbo.TEACHER VALUES( 4,'刘晓婧',2,8000,'计算机','女',23)
INSERT INTO dbo.TEACHER VALUES( 5,'高维',8,6000,'电子工程','男',54)
INSERT INTO dbo.TEACHER VALUES( 6,'李伟',7,230,'机械工程','女',23)
INSERT INTO dbo.TEACHER VALUES( 7,'刘辉',3,0,'生物','女',65)
INSERT INTO dbo.TEACHER VALUES( 8,'刘伟',9,500,'计算机','男',23)
INSERT INTO dbo.TEACHER VALUES( 9,'刘静',12,0,'经济管理','男',45)
INSERT INTO dbo.TEACHER VALUES( 10,'刘奕锴',13,70000,'计算机','女',65)
INSERT INTO dbo.TEACHER VALUES( 11,'高维',14,70000,'经济管理','男',61)

CREATE TABLE COURSE
(
    CNO INT NOT NULL, --课程号
    CNAME CHAR(30) NOT NULL, --课程名称
    CTIME INT NOT NULL, --学时
    SCOUNT INT NOT NULL, --容纳人数
    CTEST SMALLDATETIME NOT NULL, --考试时间
)
SELECT * FROM dbo.COURSE
INSERT INTO dbo.COURSE VALUES( 4,'应用数学基础',48,120,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 5,'生物工程概论',32,80,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 1,'计算机软件基础',32,70,'2006-7-8')
INSERT INTO dbo.COURSE VALUES( 2,'计算机软件基础',24,80,'2006-6-28')
INSERT INTO dbo.COURSE VALUES( 8,'模拟电路设计',28,90,'2006-7-10')
INSERT INTO dbo.COURSE VALUES( 7,'机械设计实践',48,68,'2006-7-14')
INSERT INTO dbo.COURSE VALUES( 3,'生物化学',32,40,'2006-7-2')
INSERT INTO dbo.COURSE VALUES( 9,'数据库设计',16,80,'2006-7-1')
INSERT INTO dbo.COURSE VALUES( 6,'设计理论',28,45,'2006-6-30')
INSERT INTO dbo.COURSE VALUES( 10,'计算机入门',25,150,'2006-6-29')
INSERT INTO dbo.COURSE VALUES( 11,'数字电路设计基础',30,125,'2006-6-20')
DROP TABLE dbo.COURSE

2.2、审核自身审核对象

AUDIT_CHANGE_GROUP

  3.
可维护性:的存储过程有些时候比程序更容易维护,这是因为可以实时更新DB端的存储过程。  有些bug,直接改存储过程里的业务逻辑,就搞定了。 

数据表

2.3、数据库审核对象

  4. 增强安全性:提高代码安全,防止 SQL注入。这一点sql语句也可以做到。

  

DDL相关

1.DATABASE_PRINCIPAL_CHANGE_GROUP

在数据库中创建、更改或删除主体(如用户)时,将引发此事件。 等效于 Audit
Database Principal Management Event Class。
比如:创建删除登入名等

2.DATABASE_ROLE_MEMBER_CHANGE_GROUP

向数据库角色添加登录名或从中删除登录名时将引发此事件。 此事件类与
sp_addrolemember、sp_changegroup 和 sp_droprolemember
存储过程一起使用。等效于 Audit Add Member to DB Role 事件类
比如:授予、撤销服务器角色等

3.DATABASE_CHANGE_GROUP(Audit Database Management 事件)

创建、更改或删除数据库时将引发此事件。创建、更改或删除任何数据库时均将引发此事件。等效于
Audit Database Management 事件类。
比如:删除创建数据库、修改数据库属性等数据库本身的修改操作

4.DATABASE_OBJECT_CHANGE_GROUP(Audit Database Object Management
事件)

针对数据库对象(如架构)执行 CREATE、ALTER 或 DROP
语句时将引发此事件。创建、更改或删除任何数据库对象时均将引发此事件。等效于
Audit Database Object Management 事件类。
比如:Serive
Broker相关对象、存储、安全等凌驾于用户创建的对象以上的对象(权限除外)。

5.SCHEMA_OBJECT_CHANGE_GROUP( Audit Schema Object Management Event
Class)

针对架构执行 CREATE、ALTER 或 DROP 操作时将引发此事件。等效于 Audit
Schema Object Management 事件类。此事件针对架构对象引发。等效于 Audit
Object Derived Permission
事件类。任何数据库的任何架构发生更改时,均将引发此事件。等效于 Audit
Statement Permission 事件类。
比如:表、存储过程、视图、函数、架构等对象。

  5. 可扩展性:应用程序和数据库操作分开,独立进行,而不是相互在一起。方便以后的扩展和DBA维护优化。

  以下是各复杂条件的查询:

DML相关

SELECT、DELETE、INSERT、UPDATE

  缺点  

------组合查询条件
    --1、and运算符 查询年龄大于30的女老师
    SELECT * FROM dbo.TEACHER WHERE AGE>30 AND TSEX='女'


    --2、多个and运算符 查询年龄大于30的计算机系女老师
    SELECT * FROM dbo.TEACHER WHERE AGE>30 AND TSEX='女' AND DNAME='计算机'


    --3、or运算符 查询计算机系和生物系女老师的姓名和工资
    SELECT TNAME,SAL,DNAME FROM dbo.TEACHER WHERE DNAME='计算机' OR DNAME='生物'


    --4、or and运算符混合使用 查询计算机系和生物系老师的姓名和工资
    SELECT TNAME,SAL,DNAME FROM dbo.TEACHER WHERE DNAME='计算机' OR DNAME='生物' AND TSEX='女' ORDER BY SAL
    SELECT TNAME,SAL,DNAME FROM dbo.TEACHER WHERE (DNAME='计算机' OR DNAME='生物') AND TSEX='女' ORDER BY SAL
    --AND OR 一起使用的时候,要注意运算符的优先级。AND要高于OR,所以第一条线执行AND,加上括号后才是正确的结果


    --5、IN和NOT IN运算符使用 查询计算机系和生物系的所有信息
    SELECT * FROM dbo.TEACHER WHERE DNAME IN('计算机','生物')
    SELECT * FROM dbo.TEACHER WHERE DNAME NOT IN('计算机','生物')
    --使用IN有以下优点
        --1、当选择条件很多时,采用IN运算符就显得很便捷,只需要在括号内用逗号间隔依次罗列即可,运行效率比OR运算符要高
        --2、使用IN运算符,其后面所列的条件可以使另一条SELECT语句,即子查询,如下:
    SELECT * FROM dbo.TEACHER WHERE DNAME IN(SELECT DNAME FROM dbo.TEACHER WHERE AGE>65)


    --6、NOT BETWEEN运算符混合使用 查询年龄不在40~50岁的教师姓名,年龄,系,性别
    SELECT TNAME,AGE,DNAME,TSEX FROM dbo.TEACHER WHERE AGE NOT BETWEEN 40 AND 50 ORDER BY AGE
    --我们在用OR来得到相同的数据,相比之下,似乎OR更简洁,注意,MySQL不支持NOT运算符
    SELECT TNAME,AGE,DNAME,TSEX FROM dbo.TEACHER WHERE AGE<40 OR age>50 ORDER BY AGE


    --7、使用LIKE运算符查询
    SELECT * FROM dbo.TEACHER WHERE DNAME LIKE '计算机'
    --%通配符,表示任意字符匹配不计较字符的多少,可以在''中间任意位置使用,以XX开头或结尾或中间
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '%电路%' --中间有"电路"的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '%基础'  --以“基础”结尾的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '计算机%'  --以“计算机”开头的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '计算机%基础'  


    --_通配符,表示任意一个字符匹配,可以在''中间任意位置使用,以XX开头或结尾或中间
    --   _代表一个字符,__代表两个字符
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '__电路__'  --中间带有“电路”的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '____基础'  --以“基础”结尾,且前面是四个字的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '计算机__'  --以“计算机”开头,两个字结尾的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '计算机__基础'  --中间两个字的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '____'  --查询四个字的系


    --"[]"通配符用于指定一系列的字符,只要满足这些字符其中之一且位置出现在"[]"通配符的位置,字符串就满足查询条件
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '[模]%'  --以“模“开头的
    SELECT * FROM dbo.COURSE WHERE CNAME LIKE '[^模]%'  --加上"^"表示不以"模拟"开头的


    ------以上三种通配符是可以自由组合使用的
  ------以上所有的运算符和条件查询都是可以组合使用的,根据实际情况选择适合的查询方法才是明智的选择

三、创建服务审核

  
1. SQL本身是一种结构化查询语言,但不是面向对象的的,本质上还是过程化的语言,面对复杂的业务逻辑,过程化的处理会很吃力。同时SQL擅长的是数据查询而非业务逻辑的处理,如果如果把业务逻辑全放在存储过程里面,违背了这一原则。

发表评论

电子邮件地址不会被公开。 必填项已用*标注