数据库之路——SQL提升

1、between的使用：

①select*from 表名 where time between 值1 and 值2（包括边界值）

②select a,b,c, from 表名 where a not between 数值1 and 数值2（不包括边界值）

2、in的使用

select*from 表名 where a [not]in (‘值1’,’值2’,’值4’,’值6’)

3、嵌套查询

①两张关联表，删除主表中存在，而副表中没有的信息 deletefrom 表1 wherenotexists ( select*from 表2 where 表1.field1=表2table2.field1 )

②找b值相同的a值最大的记录select a,b,c from 表名 ta where a=(selectmax(a) from 表名 tb where tb.b=ta.b)

③选择第10-15的记录select top 5 * from (select top 15 * from 表名 order by id asc) 表别名 order by id desc

4、随机抽取n条数据

select top n *from 表名 order by newid()

5、按姓氏笔画排序 Select*From 表名 OrderBy CustomerName Collate Chinese_PRC_Stroke_ci_as

6、数据库加密

select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') =1--相同；否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') =1--相同；否则不相同

7、取出表中日期（time字段）为当天的所有记录

select*from 表名 where  datediff(dd,time,getdate())=0

8、根据条件分类显示：语数英三门课，根据成绩大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格

select

(case when 语文>=80 then '优秀'
      when 语文>=60 then '及格'
      else '不及格') as 语文,
(case when 数学>=80 then '优秀'
      when 数学>=60 then '及格'
      else '不及格') as 数学,
(case when 英语>=80 then '优秀'
      when 英语>=60 then '及格'
      else '不及格') as 英语,
from 表名

9、删除冗余信息，保留相同记录中自动编号最小的记录

delete 表名 where 自动编号 not in(select min(自动编号) from 表名 group by 字段1，字段2，字段3...)

10、同一表数据比较

①对同一表同一列数据进行比较（内连接）：查找语文、数学都大于80分的人

select 表别名1.[名字] from 表名 表别名1 inner join 表名 表别名2
on 表别名1.[名字]=表别名2.[名字] and 表别名1.[科目]='语文' and 表别名2.[科目]='数学'
where 表别名1.[分数]>80 and 表别名2.[分数]>80

②对同一表的不同列数据进行比较：查找表中所有name字段相同的数据

select*from 表名 表别名 where (表别名.name) in  (select name from 表名 group by name  having count(*) > 1)

11、根据条件标记：60分以下标为fail，其余标为pass

select courseid, coursename ,score ,decode（sign(score-60),-1,'fail','pass') as mark from course

decode相当于if then，decode(判断条件,条件1,结果1,条件2,结果2,默认结果)

数据库对象：

12、触发器

一种特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算（如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发）。

在SQL Server 2000里，有INSTEAD-OF和AFTER两种触发器。

INSTEAD-OF触发器是替代数据操控语言(DML)语句对表格执行语句的存储过程。

AFTER触发器要在DML语句在数据库里使用之后才执行。

13、存储过程

一个预编译的 SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL ，使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用。

14、索引

一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

15、维护数据库完整性和一致性的方法及优缺点

①约束，效率最高，也最方便。

②触发器，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。

③自写业务逻辑，麻烦，编程复杂，效率低下。

16、事物

事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。

17、锁

锁是实现事务的关键，锁可以保证事务的完整性和并发性。可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。