本文是我在平时工作中所遇到的数据库方面的一些知识总结

1.多条件查询

多条件查询在实际应用中用的很多,在一些页面上会要求根据时间,类别,或是其他的一些关键字来查询数据,而且这些条件并不是一定要输入的。以前有过在C#代码中根据条件选中的情况来拼where条件字串,也有过在存储过程中用if来判断传进的参数,但是后来知道早存储过程中使用OR是最简单的,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create Table Users
(
id int,
name nvarchar(20),
age int
)
create proc sp_SGetUserInfo
(
@Name nvarchar(20),
@Age int
)
as
select
*
from
Users
where
(@Name='' or name=@Name)
and
(@Age='' or age=@Age)

2.使用isnull

对于传进的参数有可能为空或是null的情况,isnull就很有用了。 通常可以这样来判断

1
ISNULL(@Name,'') --如果参数@Namenull,则返回''

3.在数据库中几个常用的日期格式转换

日期格式转换也是经常用到的,通常2008-04-12和20080412 这两种格式用的比较多,下面列出一些常用的:

1
2
3
4
5
6
7
8
CONVERT(VARCHAR(10),GETDATE(),120)
--返回-04-12
CONVERT(VARCHAR(10),GETDATE(),20)
--返回-04-12
CONVERT(VARCHAR(10),GETDATE(),112)
--返回
CONVERT(VARCHAR(10),GETDATE(),111)
--返回/04/12

4.取指定字符串中两个字符 中间的字符串

1
2
3
4
5
6
7
8
9
10
11
12
13
declare @str1 nvarchar(20)
declare @str2 nvarchar(20)
declare @str3 nvarchar(20)
set @str1 = 'ABCDEFGH'
set @str2 = 'ab'
set @str3 = 'f'
declare @a int ,@b int
set @a=CHARINDEX(right(@str2,1),@str1) --第二个字符串的最后一个字符的索引
set @b=CHARINDEX(left(@str3,1),@str1) --第三个字符串的第一个字符的索引
--结果取得是第二个字符串和?第三个字符串中间的字符
select substring(@str1,@a+1,@b-@a-1)

5.一个实现拆分由特殊符分隔的字符串的函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Create FUNCTION split
(
@StrAll varchar(8000),
@StrSeprate varchar(10)
)
RETURNS @temp TABLE(F1 VARCHAR(100))
AS
BEGIN
DECLARE @i INT
SET @StrAll =rtrim(ltrim(@StrAll ))
SET @i=charindex(@StrSeprate,@StrAll )
WHILE @i>=1
BEGIN
INSERT @temp VALUES(left(@StrAll ,@i-1))
SET @StrAll =substring(@StrAll ,@i+1,len(@StrAll )-@i)
SET @i=charindex(@StrSeprate,@StrAll )
END
IF @StrAll <>''
INSERT @temp VALUES(@StrAll )
RETURN
END

6.取出数据库中所有的表名

1
select name as tablename from sysobjects where type='U' and name<>'dtproperties'

7.sqlserver中取随机数的两种方法

a.创建一个表Rand,字段是:RandomNum ,存储0到9的数据。

使用下面SQL语句可产生随机数:

1
select top 1 RandomNum from Rand order by NewID()

b.使用sqlserver提供的Rand()函数

1
2
3
4
select cast( floor(rand()*N) as int)
--产生到N-1之间的随机数
select cast(ceiling(rand() * N) as int)
--产生到N之间的随机数

8.数据库中的集合运算(交, 并 ,差)

先创建示例表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
create table T1
(
id int
)
create table T2
(
id int
)
insert T1
select 1 union all
select 2 union all
select 3 union all
select 4
insert T2
select 3 union all
select 4
运算代码:
--交集
----------------------------------------------
--方法
select * from T1
intersect
select * from T2
--方法
select distinct
*
from
T1
where
T1.id in (select id from T2)
--方法
select distinct
*
from
T1
where exists(select id from T2 where T2.id=T1.id)
--in和exists 的不同是in只能判断唯一列,而exists可以判断多列
---------------------------------------------------
--并集
select * from T1
union all
select * from T2
----------------------------------------------------
--差集
select * from T1
except
select * from T2

返回结果就不写了,呵呵,大家运行一下就知道了。

9 在sqlserver实现除法

通常情况下在用sql语句写除法得到的都是整数部分

1
2
select 29/3
--结果为

用下面的方法可以得到小数

1
2
3
4
5
6
--定义结果变量为Decimal类型
declare @result decimal(18,2)
--关键在于除数要乘以1.0
set @result=cast((29*1.0)/3 as decimal(18,2))
select @result
--结果为9.67

10 SqlServer 中根据时间得到星期

1
2
3
4
5
6
7
select datename(weekday,'2009-06-19')
--结果星期五
select DATEPART(dw,'2009-06-19')
-- 结果6
--因为系统默认星期天为一个星期的第一天,所以星期五对应的值为

11 使用sql语句设置表主键

1
2
3
alter table TableName alter column ColumnName int not null
go
alter table TableName add constraint pk_ColumnName primary key(ID)

12 SCOPE_IDENTITY @@IDENTITY IDENT_CURRENT的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--SCOPE_IDENTITY和@@IDENTITY都返回上面操作的数据表最后row的IDENTITY 列的值
--不过SCOPE_IDENTITY受到作用域的限制,@@IDENTITY不受作用域的限制
/*
如有T1和T2两种?表?,在?表?T1中有一insert触发器,当在表T1中插入一条数据时,触发
器被激发,在T2表中插入一条数据,这时就存在两个作用域,T1 和T2 ,这时如果在
T1的insert语句后执行SCOPE_IDENTITY() 和@@IDENTITY将返回不同的值
SCOPE_IDENTITY 返回的是T1的IDENTITY
@@IDENTITY 返回的是T2的IDENTITY
*/
select SCOPE_IDENTITY()
select @@IDENTITY
--返回指?定表中的最后一个identity的值
select IDENT_CURRENT('TableName')

13 truncate和delete的区别

1
2
3
4
5
6
7
8
9
10
11
--delete 和truncate的作用作用都是将指定表中的数据清除,但两种方法有很大的区别
--delete语句是dml,这个操作会放到?rollback segement中
--事务提交之后才生效,如果有相应的trigger,执行的时候将被触发
--用delete删除的数据可以找回来
delete from tablename
--truncate是ddl, 操作立即生效,
--原数据不放到rollback segment中,不能回滚. 操作不触发trigger
--所以在使用truncate的时候要特别小心
truncate table tablename

14 sqlservr中随机取数据

1
2
3
4
5
6
--MsSql随机取数据
select top 10 * from tablename order by newid()
--Access 随机取数据
select top 10 * FROM tablename order by rnd(id)
--mySql 随机取数据
SELECT * FROM tablename order by rand() limit 10

15 根据当前时间取上个月1号的时间

1
2
select dateadd(month,-1,getdate())-
day(dateadd(month,-1,getdate()))+1

不断更新中……