SQL SERVER用SQL語句建立表&用SQL語句建立資料庫

（1）查詢一個資料庫中是否存在某個表(兩種方式）：假設表名為table_name

if Exists(select * from sysobjects where name='table_name') drop table table_name 或

 if object_id('table_name') is not null drop table table_name

 同樣的操作也可用來判斷資料庫是否存在！
           

（2）對表的一些執行個體操作：

      建立一個表的執行個體：（學生成績表：grade_table)

if exists(select * from sysobjects where name = 'grade_table')
       drop table grade_table      

      go
      create table grade_table
      (
             stuID varchar(20),
             courseID varchar(20),
             grade int
      ) 
           

插入表中資料：（學生成績表：grade_table)

insert into grade_table values('10001','001','85')

      insert into grade_table values('10002','001','95')

      更新表中資料：

      update grade_table set grade=70 where stuID='10001' and courseID='001'

      删除表中資料：

      delete grade_table where stuID='10001' and courseID='001'
           

存儲過程

在Sql Server中,可以定義子程式存放在資料庫中,這樣的子程式稱為存儲過程,它是資料庫對象之一.

一 存儲過程的優點:

1: 存儲過程在伺服器端運作,執行速度快

2: 存儲過程隻執行一次,然後把編譯的二進制代碼儲存在調整緩存中,以後可從中調用,提高系統的性能.

3: 確定資料庫的安全.使用存儲過程可以完成所有的資料庫操作,并可通過程式設計方式來控制

4: 自動完成所需要的預先勢利的任務.友善客戶

二 存儲過程的類型(五類)

(1)     系統存儲過程. 由系統提供的存儲過程,可以作為指令執行各種操作.定義在系統資料庫master中,字首是sp_,例如常用的顯示系統對象資訊的sp_help存儲過程

(2)     本地存儲過程. 指使用者資料庫中建立的存儲過程,這種存儲過程完成特定的資料庫任務

(3)     臨時存儲過程. 它屬于本地存儲過程,如果存儲過程前面有一個’#’代表局部臨時存儲過程,如果有’##’代表全局臨時存儲過程,可以在所有的使用者會話中使用.

(4)     遠端存儲過程. 指從遠端服務上調用的存儲過程

(5)     擴充存儲過程. 在SQL Server環境之外執行的動态連結庫稱為擴充存儲過程,字首_sp,使用時要先加載到SQL Server系統中

三 建立使用者存儲過程

    使用者存儲過程隻能定義在目前資料庫中,可以使用SQL語句,也可使用企業管理器,這裡隻用SQL語句,注意存儲過程中不能定義如下的對象:

         Create view               (視圖)

         Create default          (預設)

         Create rule                (規則)

         Create procedure    (存儲過程)

         Create trigger          (觸發器)

1: 通過Sql指令來建立和執行存儲過程(假設使用學生表)

    定義如下存儲過程:

Use student
Go
Create procedure student_grade
As
Select stu.stuID,stu.name,course.name,course.grade
From student_table as stu,course_table as course
Where stu.stuID=course.stuID and stu.courseID=course.courseID

使用存儲過程:
Exce student_grade
Go
           

2: 建立存儲過程文法格式

Create proc[edure] procedure_name [;number] –定義過程名

[{@parameter data_type}]                                   --定義參數的類型

[varying][ =default][output]                                    --定義參數的屬性

[,…n1]

[with {recompile|encryption|recompile,encryption}] –定義存儲過程的處理方式

[for replication]

As sql_statement[…n2]                                                 --執行的操作(所有的sql語句都可以)
           

說明:

(1)     參數number為可選的整數,用于區分同名的存儲過程,以便用一條drop procedure語句删除一組存儲過程

(2)     @parameter 為存儲過程的形參,@符号作為第一個字元來指定參數名稱.data_type為參數的資料類型(如int或varchar(32)等)

(3)     Default指定存儲過程輸入參數的預設值,必須是常量或NULL,預設值中可以有通配符(%,_,[]和[^])

(4)     Recompile表明每次運作該過程時,要重新編譯;

Encryption表示SQL server加密syscomments表中包含create procedure語句文本的條目,就是加密了,别人複制資料庫而不能複制它,以防以存儲過程來讀取資料庫中定義

(5)     參數n2說明一個存儲過程可以包含多條T-SQL語句

3: 存儲過程要注意的幾點:

(1)     使用者定義的存儲過程隻能在目前資料庫中使用(臨時過程除外)

(2)     成功執行create procedure語句後,過程名存儲在sysobjects系統表中,而create procedure語句的文本存儲在syscomments中

(3)     自動執行存儲過程.SQL Server啟動時可自動執行一個或多個存儲過程,這些存儲過程必須定義在master資料庫中,并在sysadmin固定伺服器角色作為背景過程執行,并且不能有任何的參數

(4)     Sql_statement語句限制 必須使用對象所有者名(就是這個資料庫的所有者)對資料庫對象進行限定的語句有:

Create table, alter table, drop table, truncate table, create index, drop index, update statistics及DBCC語句

(5)     權限. Create procedure的權限預設授予sysadmin固定伺服器角色成員,db_ower和db_ddladmin默寫資料庫角色成員.可以把權限轉讓.

4: 存儲過程執行文法

[exce[ute]]

{[@return_staus = ]

{procedure_name[;number]|@procedure_name_var}

[[@parameter = ]{value|@variable[ouput]|[default]}

[,..n]

[with recompile]}
           

說明:

         @return_status為可選的整形變量,儲存存儲過程的傳回狀态,execute語句使用該變量前,必須對其定義.

         Procedure_name和number用于調用定義一組存儲過程中一某一個,procedure_name代表了存儲過程的組名,number用來指定哪一個.

         Procedure_name_var為create procedure中定義的存儲過程名

         @parameter為create procedure中定義的參數名,value為存儲過程的實參;

         @variable為output參數傳回的值

         Default表示不提供實參,而是使用對應的預設值

         n 表示可以實參可以有多個

5: 舉例

(1)     建立資料庫和相應的表

create database student  --建立資料庫

go

use student

go

-----------------------------建立學生表------------------------

if object_id('student_table') is not null

drop table student_table

create table student_table

(

studentID varchar(20) primary key,

sname varchar(20) not null,

sex varchar(2),

birthday datetime default getdate()

)

go

insert into student_table values('101','王五','男','')

insert into student_table values('102','李四','男','')

go

select * from student_table

-------------------------------------建立課程表------------------------

go

if object_id('course_table') is not null

drop table course_table

create table course_table

(

courseID varchar(20) primary key,

cname varchar(20) not null,

)

go

insert into course_table values('001','C語言')

insert into course_table values('002','資料結構')

go

select * from course_table

--------------------------------------建立學生課程表--------------------------------

go

if object_id('student_course_table') is not null

drop table student_course_table

create table student_course_table

(

studentID varchar(20) foreign key references student_table(studentID),

courseID varchar(20) foreign key references course_table(courseID),

grade int

)

go

insert into student_course_table values('101','001',67)

insert into student_course_table values('101','002',77)

insert into student_course_table values('102','001',97)

insert into student_course_table values('102','002',57)

go

select * from student_course_table

(1)     設計簡單的存儲過程

從student資料庫的三個表中查詢,傳回學生學号,姓名,課程名,成績,學分

Use student

--檢查是否已存在同名的存儲過程,或有,删除

If object_id('student_info') is not null

           Drop procedure student_info

Go

Create procedure student_info

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table as a inner join student_course_table as b

                    On a.studentID = b.studentID inner join course_table as c

                    On b.courseID = c.courseID

執行:

exec student_info

或execute student_info

(2)     使用帶參數的存儲過程

從student資料庫的三個表中查詢某個人指定的成績和學分

Use student

If exists(select name from sysobjects where name='student_info1' and type='p')

           Drop procedure student_info1

Go

Create procedure student_info1

           @sname char(8),@cname char(16)

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table as a inner join student_course_table as b

                    On a.studentID = b.studentID inner join course_table as c

                    On b.courseID = c.courseID

           where [email protected] and [email protected]

執行:(多種方式

 execute student_info1 ‘王五’,’C語言’

或

 Exec student_info1 @name=’王五’,@cname=’C語言’

(3)     使用帶有通配符參數的存儲過程

從三個表的連接配接中傳回指定學生學号,姓名,所選課程名稱及成績,該存儲過程使用了模式比對,如果沒有提供參數,則使用預設的預設值

Use student

If object_id('stu_info') is not null

           Drop procedure stu_info

Go

Create procedure st_info

           @name varchar(30)='王%'

As

Select a.studentID,a.sname,c.cname,b.grade

           From student_table a inner join student_course_table b

                    on a.studentID = b.studentID inner join course_table c

                    on b.courseID =c.courseID

           where a.sname like @name

go     

        執行該存儲過程

使用預設參數;execute stu_info

使用實參; exec stu_info @name=’王%’ 或exec stu_info ‘王%’

(4)     使用帶output參數的存儲過程

用于計算指定學生的總學分,存儲過程中使用了一個輸入參數和一個輸出參數

Use student

Go

If exists(select name from sysobjects where name='totalcredit' and type='p')

           Drop procedure totalcredit

Go

Create procedure totalcredit @name varchar(30),@total int OUTPUT

As

Select @total=sum(grade)

           From student_table a,student_course_table b,course_table

           Where [email protected] and a.studentID=b.studentID

           Group by a.studentID

Go

注意:output變量必須在定義存儲過程和使用該變量時都定義

執行:

Declare @t_credit char(20),@total int [email protected]将作為OUTPUT變量必須先定義

Exec totalcredit ‘王五’,@total OUTPUT ---OUTPUT必須為大寫

Select ‘王五’,@total

go

觸發器

觸發器是一類特殊的存儲過程.它與表的關系密切,用于保護表中的資料,當有操作影響到觸發器保護的資料時,觸發器自動 執行,例如使用觸發器實作多個表間資料的一緻性.

一般情況下,對表資料的操作有插入,修改,删除,因而維護資料的觸發器也可分為三類:

INSERT,UPDATE和DELETE

一、使用SQL指令建立觸發器

文法格式如下：

Create trigger tigger_name on {table|view}             --指定觸發器的名稱和操作對象

[with encryption]                                                               --是否使用加密方式

{{{for|after|instead of} {[delete[,][insert][,],[update]]—定義觸發器的類型

[not for replication]                                                          --說明該觸發器不用于複制

As

[{if update(column)[{AND|OR} update(column)]

[…n]

|if(columns_update(){ bitwise_operator } updated_bitmask)

{comparison_operator } column_bitmask [..n]}]   --兩個if語句用來說明觸發器執行的條件

Sql_statement […n]                                                          --一條或若幹條sql語句

}}

        說明：

(1)     Table | view指在其上執行觸發器的表或視圖

(2)     After關鍵字用于說明觸發器在指定操作都成功執行後觸發,after是預設設定,不能在視力上定義after觸發器

(3)     If update(column)子句用于測試在指定的列上(column)上進行的insert或update操作不能用于delete操作,傳回值為true或false

(4)     If(columns_update())子句用于測試是否插入或更新了指定的列,傳回二進制位資料,若為0沒有成功更新,若為1,更新成功

(5)     Bitwise_operator為用于比較去處的位運算符.update_bitmask的值為整型的位屏蔽碼,與實際更新或插入的列對應.例如表t 包含列C0,C1,C2,C3和C4.假定該表上有update觸發器,若要檢查列C0,C2,C4是否都有更新,可指定update_bitmask的值為00010101(即0x15);若要檢查是否隻有列C1有更新,可指定updated_bitmask的值為00000010

(6)     Comparison_operator為比較運算符;columns_bitmask為列屏蔽友,用來檢查是否已更新或插入對應列

(7)     Sql_statement為觸發器的SQL語句,當執行delete,insert或update時,對應的解發器生效.

(8)     N表示觸發器可包含多條SQL語句

二 觸發器中使用的特殊表

    執行觸發器時,系統建立了兩個特殊的邏輯表:inserted表和deleted表

         Inserted表:當向表中插入資料時.insert觸發器觸發執行,新的記錄插入到觸發器表和inserted表中

         Deleted表:用于儲存已從表中删除的記錄,當觸發一個delete觸發器時,被删除的記錄存放到deleted表中

    修改一條記錄等于插入一條新記錄,同時删除舊記錄.對定義了update觸發器的表記錄修改時,表中原記錄移到deleted表中,修改過的記錄插入到inserted表中,觸發器可檢查deleted和inserted表

    例:檢索deleted,inserted表中的所有記錄

                   Select * from deleted

                   Select * from inserted

三 使用觸發器的限制

(1)     Create trigger必須是批進行中的第一條語句,并且隻能應用到一個表中

(2)     觸發器隻能在目前的資料庫中建立,但觸發器可以引用目前資料庫的外部對象

(3)     如果指定觸發器所有者名限定觸發器,要以相同的方式限定表名

(4)     在同一create trigger語句中,可以為多種操作(如insert或update)定義相同的觸發器操作

(5)     如果表中存在外鍵,不能定義insert和update觸發器

(6)     觸發器中可指定set語句,執行期間有效,執行完恢複到以前的狀态

(7)     觸發器中不能指定如下T-SQL語句:

Create database,alter database,load database,restore database,drop database,load log,restore log,disk init,disk resize和reconfigure

(8)     觸發器不能有任何結果集傳回

四 舉例

對于student資料庫,如果在student_table表中添加或更改資料,則将向用戶端顯示一條資訊

/*使用帶有提示消息的觸發器

USE master

GO

EXEC sp_addmessage 50021, 10, 'no update or delete','us_english',false,replace

EXEC sp_addmessage 50021, 10, '不能插入或更新','簡體中文',false,replace

go

Use student

If exists(select name from sysobjects where name=’reminder’ and type=’tr’)

           Drop trigger reminder

Go

Create trigger reminder on student_table

           For insert,update

           As raiserror(4008,16,10)

消息4008是sysmessages中的使用者定義消息,有關建立使用者消息的方法看下sp_addmessage存儲過程

注意:SQL server 在調用sp_addmessage 時 有一個參數@lang是用來指明所需要加入的message的語種的，如果沒有指定，則認為是語言是會話的預設語言，如果你安裝的是中文版，則一般是中文，而SQL server有強制必須先增加英文的錯誤資訊之後才能增加中文的錯誤資訊，是以必須現增加英文版的錯誤資訊。然後再增加本地語種的錯誤資訊。

使用sp_dropmessag删除sysmessages表中添加的資訊。直接delete删除不掉

例如:use master go exec sp_dropmessage 50021

(1)     在資料庫student中建立一個觸發器,當向student_course_table中插入一記錄時,檢查該記錄的學号在student_table表中是否存在,檢查課程号在course_table中是否存在,若都有則插入,否則不執行插入

Use student

If object_id('check_trig') is not null

           Drop trigger check_trig

Go

Create trigger check_trig on student_course_table

For insert

As

If exists(select *

           from inserted a

           Where a.studentID not in(select b.studentID from student_table b) or

           a.courseID not in(select c.courseID from course_table c))

           Begin

                    Raiserror('違背資料的一緻性',16,1)

                    Rollback transaction

           End

(2)     在student資料庫的student_course_table表中建立一個觸發器,若對學号列和課程列修改,則給出提示資訊,并取消修改操作.

通過調用colunms_updated()函數,可快速測試對學号列和課程号列修改所做的更改

Use student

Go

Create trigger update_trig

           On student_course_table

           For update

As

If(columns_updated() &3)>0

           Begin

                    Raiserror(‘違背資料的一緻性’,16,1)

                    Rollback transaction

           End

Go

(3)     Inserted of觸發器的設計

如果視圖的資料來自于多個基表,則必須使用instead of 觸發器支援引用表中資料的插入,更新和删除操作

例如:若在一個多表視圖上定義了instead of insert觸發器,那視圖的值可能允許為空,也可能不為空,若視圖某列的值不允許為空,則inset語句必須為該列提供相應的值.

     如果視圖的列為以下幾種情況之一:

1>     基表中的計算列

2>     基表中的辨別列

3>     具有timestamp資料類型的基表列

該視圖的insert語句必須為這些列指定值,instead of 觸發器在構成将值插入基表的insert語句時會指定的值

例:在student資料庫中的建立表,視圖和觸發器,以說明instead of insert觸發器的使用

Use student

go

Create table books

(

bookKey int identity(1,1),

bookName varchar(10) not null,

color varchar(10) not null,

computedCol as (bookName + color),

Page int,

)

Go

--建立一個視圖,包含基表中的所有列

Create view view2

As

Select * from books

Go

--在view2視圖上建立一個instead of insert觸發器

Create trigger insteadTrig on view2

Instead of insert

As

Begin

           Insert into books

           Select bookName,color,page from inserted

End

Go

直接引用books表的insert語句不能為bookKey字段和computedCor字段提供值

--正确的insert語句

Insert into books(bookName,color,page)

           Values('計算機','紅色',100)

--檢視結果

select * from books

2計算機 紅色    計算機紅色     100 

--不正确的insert語句(就是插入所有列)

Insert into books values(2,'計算機輔助教程','紅色','綠色',100)

但對于引用view2視力的insert語句為每一列都指定值:例如

--對于view2正确的insert語句 

Insert into view2 values(2,'計算機','紅色','綠色',100)

--檢視結果

Select * from view2

2計算機 紅色    計算機紅色     100 

3計算機 紅色    計算機紅色     100 

在執行視圖的插入語句時,雖然将bookKey和computedCor字段的值傳遞了insertedTrig觸發器中,但觸發器中的insert語句沒有選擇inserted表的中bookKey和computedCol字段的值(即你寫入的這兩個字段的值并不真正插入的,它是由系統自動生成或是計算列) 

    五:删除觸發器

        利用SQL語句琰删除觸發器

                    Drop trigger trigger_name[…n]

 以下是一些網站上引用

用SQL語句建立表&用SQL語句建立資料庫

資料庫中的所有資料存儲在表中。資料表包括行和列。列決定了表中資料的類型。行包含了實際的資料。 

例如，資料庫pubs中的表authors有九個字段。其中的一個字段名為為au_lname，這個字段被用來存儲作者的名字資訊。每次向這個表中添加新作者時，作者名字就被添加到這個字段，産生一條新記錄。 

通過定義字段，你可以建立一個新表。每個字段有一個名字和一個特定的資料類型（資料類型在後面的“字段類型”一節中講述），例如字段au_lname存儲的是字元型資料。一個字段也可以存儲其它類型的資料。 

使用SQL Sever，建立一個新表的方法是很多的。你可以可執行一個SQL語句或使用SQL事務管理器（SQL Enterprise Manager）來建立一個新表。在下一節裡，你将學會如何用SQL語句來建立一個新表。 

一、用CREATE語句建立表

注意： 

如果你還沒有建立自己的資料庫，現在就跳回到第三章建立這個庫。你絕不能向master,tempdb或任何其他任何系統資料庫中添加資料。 

從SQL Sever程式組（在工作列中）中啟動ISQL/w程式。出現查詢視窗後，從視窗頂部的下拉清單中選擇你在第三章所建立的資料庫。下一步，在查詢視窗中鍵入下面的SQL語句，單擊執行查詢按鈕，執行這個語句： 

CREATE TABLE guestbook (visitor VARCHAR(40),comments TEXT,entrydate DATETIME) 

如果一切正常，你會在結果視窗中看到如下的文字（如果出現異常，請參閱第三章）： 

This command dit not return data ,and it did not return any rows 

祝賀你，你已經建立了你的第一個表！ 

你所建立的表名為guestbook，你可以使用這個表來存儲來字你站點通路者的資訊。你是用CREATE TABLE語句建立的這個表，這個語句有兩部分：第一部份指定表的名字；第二部份是括在括号中的各字段的名稱和屬性，互相之間用逗号隔開。 

表guestbook有三個字段：visitor,comments 和entrydate。

Visitor         字段存儲通路者的名字，

Comments     字段存儲通路者對你站點的意見，

Entrydate      字段存儲通路者通路你站點的日期和時間。 

注意每個字段名後面都跟有一個專門的表達式。例如，字段名comments後面跟有表達式TEXT。這個表達式指定了字段的資料類型。資料類型決定了一個字段可以存儲什麼樣的資料。因為字段comments包含文本資訊，其資料類型定義為文本型。 

二、字段類型 

不同的字段類型用來存放不同類型的資料。建立和使用表時，你更應該了解五種常用的字段類型：字元型，文本型，數值型，邏輯性和日期型。 

（1）字元型資料 

字元型資料非常有用。當你需要存儲短的字元串資訊時，你總是要用到字元型資料。例如，你可以把從HTML form的文本框中搜集到的資訊放在字元型字段中。 

要建立一個字段用來存放可變長度的字元串資訊，你可以使用表達式 VARCHAR。考慮你前面建立的表guestbook： 

CREATE TABLE guestbook (visitor VARCHAR(40),comments TEXT,entrydate DATETIME) 

在這個例子中，字段visitor的資料類型為VARCHAR。注意跟在資料類型後面的括号中的數字。這個數字指定了這個字段所允許存放的字元串的最大長度。在這個例子中，字段visitor能存放的字元串最長為四十個字元。如果名字太長，字元串會被截斷，隻保留四十個字元。 

VARCHAR類型可以存儲的字元串最長為255個字元。要存儲更長的字元串資料，可以使用文本型資料（下一節中講述）。 

另一種字元型資料用來存儲固定長度的字元資料。下面是一個使用這種資料類型的例子： 

CREATE TABLE guestbook (visitor CHAR(40),comments TEXT,entrydate DATETIME) 

在這個例子中，字段visitor被用來存儲四十個字元的固定長度字元串。表達式CHAR指定了這個字段應該是固定長度的字元串。 

VARCHAR型和CHAR型資料的這個差别是細微的，但是非常重要。假如你向一個長度為四十個字元的VARCHAR型字段中輸入資料Bill Gates。當你以後從這個字段中取出此資料時，你取出的資料其長度為十個字元——字元串Bill Gates的長度。 

現在假如你把字元串輸入一個長度為四十個字元的CHAR型字段中，那麼當你取出資料時，所取出的資料長度将是四十個字元。字元串的後面會被附加多餘的空格。 

當你建立自己的站點時，你會發現使用VARCHAR型字段要比CHAR型字段友善的多。使用VARCHAR型字段時，你不需要為剪掉你資料中多餘的空格而操心。 

VARCHAR型字段的另一個突出的好處是它可以比CHAR型字段占用更少的記憶體和硬碟空間。當你的資料庫很大時，這種記憶體和磁盤空間的節省會變得非常重要。 

（2）文本型資料 

字元型資料限制了字元串的長度不能超過255個字元。而使用文本型資料，你可以存放超過二十億個字元的字元串。當你需要存儲大串的字元時，應該使用文本型資料。 

這裡有一個使用文本型資料的例子： 

CREATE TABLE guestbook (visitor VARCHAR(40),comments TEXT,entrydate DATETIME) 

在這個例子中，字段comments被用來存放通路者對你站點的意見。注意文本型資料沒有長度，而上一節中所講的字元型資料是有長度的。一個文本型字段中的資料通常要麼為空，要麼很大。 

當你從HTML form的多行文本編輯框（TEXTAREA）中收集資料時，你應該把收集的資訊存儲于文本型字段中。但是，無論何時，隻要你能避免使用文本型字段，你就應該不适用它。文本型字段既大且慢，濫用文本型字段會使伺服器速度變慢。文本型字段還會吃掉大量的磁盤空間。 

警告： 

一旦你向文本型字段中輸入了任何資料（甚至是空值），就會有2K的空間被自動配置設定給該資料。除非删除該記錄，否則你無法收回這部分存儲空間。

（3）數值型資料 

SQL Sever支援許多種不同的數值型資料。你可以存儲整數、小數、和錢數。 

通常，當你需要在表中的存放數字時，你要使用整型（INT）資料。INT型資料的表數範圍是從-2，147，483，647到2，147，483，647的整數。下面是一個如何使用INT型資料的例子： 

CREATE TABLE visitlog (visitor VARCHAR(40),numvisits INT) 

這個表可以用來記錄你站點被通路的次數。隻要沒有人通路你的站點超過2，147，483，647次，nubvisits字段就可以存儲通路次數。 

為了節省記憶體空間，你可以使用SMALLINT型資料。SMALLINT 型資料可以存儲從-32768到32768的整數。這種資料類型的使用方法與INT型完全相同。 

最後，如果你實在需要節省空間，你可以使用TINYINT型資料。同樣，這種類型的使用方法也與INT型相同，不同的是這種類型的字段隻能存儲從０到255的整數。TINYINT型字段不能用來存儲負數。 

通常，為了節省空間，應該盡可能的使用最小的整型資料。一個TINYINT型資料隻占用一個位元組；一個INT型資料占用四個位元組。這看起來似乎差别不大，但是在比較大的表中，位元組數的增長是很快的。另一方面，一旦你已經建立了一個字段，要修改它是很困難的。是以，為安全起見，你應該預測以下，一個字段所需要存儲的數值最大有可能是多大，然後選擇适當的資料類型。 

為了能對字段所存放的資料有更多的控制，你可以使用NUMERIC型資料來同時表示一個數的整數部分和小數部分。NUMERIC型資料使你能表示非常大的數——比INT型資料要大得多。一個NUMERIC型字段可以存儲從-10^38到10^38範圍内的數。NUMERIC型資料還使你能表示有小數部分的數。例如，你可以在NUMERIC型字段中存儲小數3.14。 

當定義一個NUMERIC型字段時，你需要同時指定整數部分的大小和小數部分的大小。這裡有一個使用這種資料類型的例子： 

CREATE TABLE numeric_data (bignumber NUMERIC(28,0), fraction NUMERIC (5,4) ) 

當這個語句執行時，将建立一個名為numeric_data的包含兩個字段的表。字段bignumber可以存儲直到28位的整數。字段fraction可以存儲有五位整數部分和四位小數部分的小數。 

一個NUMERIC型資料的整數部分最大隻能有28位，小數部分的位數必須小于或等于整數部分的位數，小數部分可以是零。 

你可以使用INT型或NUMERIC型資料來存儲錢數。但是，專門有另外兩種資料類型用于此目的。如果你希望你的網點能掙很多錢，你可以使用MONEY型資料。如果你的野心不大，你可以使用SMALLMONEY型資料。MONEY型資料可以存儲從-922，337，203，685，477.5808到922，337，203，685，477.5807的錢數。如果你需要存儲比這還大的金額，你可以使用NUMERIC型資料。 

SMALLMONEY型資料隻能存儲從-214，748.3648到214，748.3647 的錢數。同樣，如果可以的話，你應該用SMALLMONEY型來代替MONEY型資料，以節省空間。下面的例子顯示了如何使用這兩種表示錢的資料類型： 

CREATE TABLE products (product VARCHAR(40),price MONEY, 

Discount_price SMALLMONEY) 

這個表可以用來存儲商品的折扣和普通售價。字段price 的資料類型是MONEY，字段discount_price的資料類型是SMALLMONEY。 

(4)存儲邏輯值 

如果你使用複選框（CHECKBOX）從網頁中搜集資訊，你可以把此資訊存儲在BIT型字段中。BIT型字段隻能取兩個值：0或1。這裡有一個如何使用這種字段的例子： 

CREATE TABLE opinion (visitor VARCHAR(40),good BIT) 

這個表可以用來存放對你的網站進行民意調查所得的資訊。通路者可以投票表示他們是否喜歡你的網站。如果他們投YES，就在BIT型字段中存入1。反之，如果他們投NO，就在字段中存入0（在下一章裡，你将學會如何計算投票）。 

當心，在你建立好一個表之後，你不能向表中添加BIT型字段。如果你打算在一個表中包含BIT型字段，你必須在建立表時完成。 

(5)存儲日期和時間 

當你建立一個網站時，你也許需要記錄在一段時間内的通路者數量。為了能夠存儲日期和時間，你需要使用DATETIME型資料，如下例所示： 

CREATE TABL visitorlog(arrivaltime DATETIME ,departuretime DATETIME) 

這個表可以用來記錄通路者進入和離開你網站的時間和日期。一個DATETIME型的字段可以存儲的日期範圍是從1753年1月１日第一毫秒到9999年12月31日最後一毫秒。 

如果你不需要覆寫這麼大範圍的日期和時間，你可以使用SMALLDATETIME型資料。它與DATETIME型資料同樣使用，隻不過它能表示的日期和時間範圍比DATETIME型資料小，而且不如DATETIME型資料精确。一個SMALLDATETIME型的字段能夠存儲從１９00年１月１日到２０７９年６月６日的日期，它隻能精确到秒。 

DATETIME型字段在你輸入日期和時間之前并不包含實際的資料，認識這一點是重要的。在下一章，你将學習怎樣使用大量的SQL函數來讀取和操作日期和時間（參見下面的“預設值”一節）。你也可以在VBScript和JScript 中使用日期和時間函數來向一個DATETIME型字段中輸入日期和時間。 

三、字段屬性 

上一節介紹了如何建立包含不同類型字段的表。在這一節中，你将學會如何使用字段的三個屬性。這些屬性允許你控制空值，預設值和辨別值。 

（1）允許和禁止空值 

大多數字段可以接受空值（NULL）。當一個字段接受了空值後，如果你不改變它，它将一直保持空值。空值（NULL）和零是不同的，嚴格的說，空值表示沒有任何值。 

為了允許一個字段接受空值，你要在字段定義的後面使用表達式NULL。例如，下面的表中兩個字段都允許接受空值： 

CREATE TABLE empty (empty1 CHAR (40) NULL,empty2 INT NULL) 

注意： 

BIT型資料不能是空值。一個這種類型的字段必須取0或者１。 

有時你需要禁止一個字段使用空值。例如，假設有一個表存儲着信用卡号碼和信用卡有效日期，你不會希望有人輸入一個信用卡号碼但不輸入有效日期。為了強制兩個字段都輸入資料，你可以用下面的方法建立這個表： 

CREATE TABLE creditcards (creditcard_number CHAR(20) NOT NULL, 

Creditcard_expire DATETIME NOT NULL) 

注意字段定義的後面跟有表達式NOT NULL。通過包含表達式NOT NULL，你可以禁止任何人隻在一個字段中插入資料，而不輸入另一個字段的資料。 

你将會發現，在你建設自己的網站過程中，這種禁止空值的能力是非常有用的。如果你指定一個字段不能接受空值，那麼當你試圖輸入一個空值時，會有錯誤警告。這些錯誤警告可以為程式調試提供有價值的線索。 

（2）預設值 

假設有一個存儲位址資訊的表，這個表的字段包括街道、城市、州、郵政編碼和國家。如果你預計位址的大部分是在美國，你可以把這個值作為country字段的預設值。 

為了在建立一個表時指定預設值，你可以使用表達式DEFAULT。請看下面這個在建立表時使用預設值的例子： 

CREATE TABLE addresses (street VARCHAR(60) NULL, 

city VARCHAR(40) NULL, 

state VARCHAR(20) NULL 

zip VARCHAR(20) NULL, 

country VARCHAR(30) DEFAULT ‘USA’) 

在這個例子中，字段country的預設值被指定為美國。注意單引号的使用，引号指明這是字元型資料。為了給非字元型的字段指定預設值，不要把該值擴在引号中： 

CREATE TABLE orders(price MONEY DEFAULT $38.00, 

quantity INT DEFAULT 50, 

entrydate DATETIME DEFAULT GETDATE()) 

在這個CREATE TABLE語句中，每個字段都指定了一個預設值。注意DATETIME型字段entrydate所指定的預設值,該預設值是函數Getdate()的傳回值,該函數傳回目前的日期和時間。 

（3）辨別字段 

每個表可以有一個也隻能有一個辨別字段。一個辨別字段是唯一辨別表中每條記錄的特殊字段。例如，資料庫pubs中的表jobs包含了一個唯一辨別每個工作辨別字段： 

job_id job_desc 

……………………………………………………………. 

1 New Hire Job not specified 

2 Chief Executive officer 

3 Bushness Operations Manager 

4 Chief Financial Officier 

5 Publisher 

字段job_id為每個工作提供了唯一的一個數字。如果你決定增加一個新工作，新增記錄的job_id字段會被自動賦給一個新的唯一值。 

為了建立一個辨別字段，你隻需在字段定義後面加上表達式IDENTITY即可。你隻能把NUMERIC型或INT型字段設為辨別字段，這裡有一個例子： 

CREATE TABLE visitorID (theID NUBERIC(18) IDENTITY,name VARCHAR(40)) 

這個語句所建立的表包含一個名為theid的辨別字段。每當一個新的通路者名字添加到這個表中時，這個字段就被自動賦給一個新值。你可以用這個表為你的站點的每一個使用者提供唯一辨別。 

技巧： 

建立一個标示字段時，注意使用足夠大的資料類型。例如你使用TINYINT型資料，那麼你隻能向表中添加255個記錄。如果你預計一個表可能會變得很大，你應該使用NUMERIC型資料。 

辨別字段的存在會使你想嘗試許多不可能的事情。例如，你也許想利用辨別字段來對記錄進行基于它們在表中位置的運算。你應該抛棄這種意圖。每個記錄的辨別字段的值是互不相同的，但是，這并不禁止一個辨別字段的辨別數字之間存在間隔。例如，你永遠不要試圖利用一個表的辨別字段來取出表中的前十個記錄。這種操作會導緻失敗，比如說６号記錄和7号記錄根本不存在。

四、删除和修改表 

要删除一個表，你可以使用SQL語句DROP TABLE。例如，又從資料庫中徹底删除表mytable，你要使用如下的語句： 

DROP TABLE mytable 

警告： 

使用DROP TABLE指令時一定要小心。一旦一個表被删除之後，你将無法恢複它。 

當你建設一個站點時，你很可能需要向資料庫中輸入測試資料。而當你準備向世界提供你的網點時，你會想清空表中的這些測試資訊。如果你想清除表中的所有資料但不删除這個表，你可以使用TRUNCATE TABLE語句。例如，下面的這個SQL語句從表mytable中删除所有資料： 

TRUNCATE TABLE mytable 

雖然你不能删除和修改已經存在的字段，但你可以增加新字段。最容易的實作方法是使用SQL事務管理器中的Manager Tables視窗。你也可以使用SQL語句ALTER TABLE。下面是一個如何使用這種語句的例子： 

ALTER TABLE mytable ADD mynewcolumn INT NULL 

這個語句向表mytable中增加了一個新字段mynewcolumn。當你增加新字段時，你必須允許它接受空值，因為表中原來可能已經有了許多記錄。

 總結

為了大家更容易了解我舉出的SQL語句，本文假定已經建立了一個學生成績管理資料庫，全文均以學生成績的管理為例來描述。

1 .在查詢結果中顯示列名：

a.用as關鍵字： select  name  as   ' 姓名 '   from  students  order   by  age

b.直接表示： select  name  ' 姓名 '   from  students  order   by  age

2 .精确查找:

a.用in限定範圍： select   *   from  students  where  native  in  ( ' 湖南 ' ,  ' 四川 ' )

b. between

and ： select   *   from  students  where  age  between   20   and   30

c.“ = ”： select   *   from  students  where  name  =   ' 李山 '

d. like : select   *   from  students  where  name  like   ' 李% '  (注意查詢條件中有“ % ”，則說明是部分比對，而且還有先後資訊在裡面，即查找以“李”開頭的比對項。是以若查詢有“李”的所有對象，應該指令： ' %李% ' ;若是第二個字為李，則應為 ' _李% ' 或 ' _李 ' 或 ' _李_ ' 。)

e. [] 比對檢查符： select   *   from  courses  where  cno  like   ' [AC]% '  (表示或的關系，與" in (

)"類似，而且" [] "可以表示範圍，如： select   *   from  courses  where  cno  like   ' [A-C]% ' )

3 .對于時間類型變量的處理

a. smalldatetime ：直接按照字元串處理的方式進行處理，例如：

select   *   from  students  where  birth  >   =   ' 1980-1-1 '   and  birth  <=   ' 1980-12-31 '

4 .集函數

a. count ()求和，如： select   count ( * )  from  students (求學生總人數)

b. avg (列)求平均，如： select   avg (mark)  from  grades  where  cno = ’B2’

c. max (列)和min(列)，求最大與最小

5 .分組group

常用于統計時，如分組查總數：

select  gender, count (sno) 

from  students

group   by  gender

(檢視男女學生各有多少)

注意：從哪種角度分組就從哪列" group   by "

對于多重分組，隻需将分組規則羅列。比如查詢各屆各專業的男女同學人數，那麼分組規則有：屆别(grade)、專業(mno)和性别(gender)，是以有" group   by  grade, mno, gender"

select  grade, mno, gender,  count ( * )

from  students

group   by  grade, mno, gender

通常group還和having聯用，比如查詢1門課以上不及格的學生，則按學号(sno)分類有：

select  sno, count ( * )  from  grades 

where  mark < 60

group   by  sno

having   count ( * ) > 1

6 .UNION聯合

合并查詢結果，如：

SELECT   *   FROM  students

WHERE  name  like  ‘張 % ’

UNION   [ ALL ]

SELECT   *   FROM  students

WHERE  name  like  ‘李 % ’

7 .多表查詢

a.内連接配接

select  g.sno,s.name,c.coursename 

from  grades g  JOIN  students s  ON  g.sno = s.sno

JOIN  courses c  ON  g.cno = c.cno

(注意可以引用别名)

b.外連接配接

b1.左連接配接

select  courses.cno, max (coursename), count (sno) 

from  courses  LEFT   JOIN  grades  ON  courses.cno = grades.cno 

group   by  courses.cno

左連接配接特點：顯示全部左邊表中的所有項目，即使其中有些項中的資料未填寫完全。

左外連接配接傳回那些存在于左表而右表中卻沒有的行，再加上内連接配接的行。

b2.右連接配接

與左連接配接類似

b3.全連接配接

select  sno,name,major 

from  students  FULL   JOIN  majors  ON  students.mno = majors.mno

兩邊表中的内容全部顯示

c.自身連接配接

select  c1.cno,c1.coursename,c1.pno,c2.coursename 

from  courses c1,courses c2  where  c1.pno = c2.cno

采用别名解決問題。

d.交叉連接配接

select  lastname + firstname  from  lastname  CROSS   JOIN  firstanme

相當于做笛卡兒積

8 .嵌套查詢

a.用關鍵字IN,如查詢李山的同鄉：

select   *   from  students

where  native  in  ( select  native  from  students  where  name = ’ 李山’)

b.使用關鍵字EXIST,比如，下面兩句是等價的：

select   *   from  students

where  sno  in  ( select  sno  from  grades  where  cno = ’B2’)

select   *   from  students  where   exists  

( select   *   from  grades  where  

grades.sno = students.sno  AND  cno = ’B2’)

9 .關于排序order

a.對于排序order，有兩種方法：asc升序和desc降序

b.對于排序order,可以按照查詢條件中的某項排列，而且這項可用數字表示，如：

select  sno, count ( * ) , avg (mark)  from  grades 

group   by  sno

having   avg (mark) > 85

order   by   3

10 .其他

a.對于有空格的識别名稱，應該用" [] "包覆。

b.對于某列中沒有資料的特定查詢可以用null判斷，如select sno,courseno  from  grades  where  mark  IS   NULL

c.注意區分在嵌套查詢中使用的any與all的差別，any相當于邏輯運算“ || ”而all則相當于邏輯運算“ && ”

d.注意在做否定意義的查詢是小心進入陷阱：

如，沒有選修‘B2’課程的學生 ：

select  students. *

from  students, grades

where  students.sno = grades.sno

AND  grades.cno  <>  ’B2’

上面的查詢方式是錯誤的，正确方式見下方：

select   *   from  students

where   not   exists  ( select   *   from  grades 

where  grades.sno = students.sno  AND  cno = ' B2 ' )

11 .關于有難度多重嵌套查詢的解決思想：

如，選修了全部課程的學生：

select   *

from  students

where   not   exists  (  select   *

from  courses

where   NOT   EXISTS  

( select   *

from  grades

where  sno = students.sno

AND  cno = courses.cno))

最外一重：從學生表中選，排除那些有課沒選的。用not exist。由于讨論對象是課程，是以第二重查詢從course表中找，排除那些選了課的即可。

添加資料庫

按 Ctrl+C 複制代碼 按 Ctrl+C 複制代碼 sql server 2005 怎麼把性别bit 類型的1、0轉換成中文男或女顯示啊？ select case 性别

       when 1 then '男'

       when 0 then '女'

end as 性别

from tablea 按 Ctrl+C 複制代碼 按 Ctrl+C 複制代碼

http://www.cnblogs.com/gaojun/archive/2009/04/29/1445959.html