賢爸知識分享站

介紹 Null值 Null值表示是未知的值與空值或零值不同

======== 新增 dbNull資料庫 ========

Create Database dbNull

Use dbNull

Create Table Test(

id int, name nvarchar(20),

c1 float, c2 float, c3 float)

======== 新增要作測試的 Null 值 ========

insert into Test(id, name, c1,c2, c3)

values(1, N'John', 55, 62, 73),

(2, N'Branda', 81, 92, null),

(3, N'Susan', 76, 66, 44),

(4, N'Kitty', 66, null, 73),

(5, N'Jeniffer', 96, 56, 84),

(6, N'Lin', null, null, null)

======== Null的意義 ========

Null的成因

1. Insert Into ... Values(Null,...)

2. t1(c1, c2, c3) Insert Into t1(c1, c2) Values(10, 20) c3 會自動帶入default value

3. Alter Table t1 Add c1 int

4. Outer Join

5. 和null 發生運算

======== 列出結果 c3 is null ========

Select * From TEST

Where c3 is null

======== 下面列出的結果 大家可以試一下 ========

Select t.id, t.name, t.c1,t.c2, t.c3, t.c1 + t.c2 + t.c3

From Test t

Select t.id, t.name, t.c1,t.c2, t.c3,

isnull(t.c1,0) + isnull(t.c2,0) + isnull(t.c3, 0)

From Test t

Select t.id, t.name, t.c1,t.c2, t.c3,

(t.c1 + t.c2) / t.c3

From Test t

Select t.id, t.name, t.c1,t.c2, t.c3,

(isnull(t.c1,0) + isnull(t.c2,0)) / t.c3

From Test t

Select c1, c2, c3 From TEST

Select SUM(c1), SUM(c2), SUM(c3) From TEST

Select c1, c2, c3 From TEST

Select MAX(c1), MAX(c2), MAX(c3) From TEST

Select c1, c2, c3 From TEST

Select MIN(c1), MIN(c2), MIN(c3) From TEST

Select c1, c2, c3 From TEST

Select AVG(c1), AVG(c2), AVG(c3) From TEST

Select c1, c2, c3 From TEST

Select COUNT(c1), COUNT(c2), COUNT(c3) From TEST

Select COUNT(*) From TEST