SQL基本文法

1、建立資料庫：create database Flights; //Flights資料庫名。
樣本1：一個資料庫檔案和一個記錄檔
CREATE DATABASE stuDB
(
/*資料檔案的具體描述*/
NAME='stuDB_data', //主要資料檔案的邏輯名。
FILENAME='D:\project\stuDB_data.mdf', //主要資料檔案的實體名稱。
SIZE=5mb, //主要資料檔案的大小。
MAXSIZE=100mb, //主要資料檔案增長的最大值。
FILLEGROWTH=15%, //主要資料檔案的增長率。
)
LOG ON
(
/*記錄檔的具體描述，各參數含義同上。*/
NAME='stuDB_log',
FILENAME='D:\project\stuDB_log.ldf',
SIZE=2mb,
FILEGROWTH=1mb,
)
GO //和後續的SQL語句分隔開。
/*------資料檔案的組成：
主要資料檔案：*.mdf
次要資料檔案：*.ndf
記錄檔：*.ldf
------*/
2、刪除資料庫：drop database Flights;
3、設定資料庫選項：
EXEC sp_dboption 'pubs','read only','true' //將pubs資料庫設為唯讀。
——用EXECUTE(EXEC)命令配置SQL Server 2000資料庫。參數sp_dboption為預存程序，可以顯示或更改資料庫選項。'master'和'tempdb'不能使用此預存程序。
EXEC sp_dboption 'pubs',autoshrink,true //自動周期性收縮'pubs'資料庫檔案。
——和sp_dboption一起使用的選項autoshrink。SQL Server 2000允許把資料庫中的檔案收縮，這樣可以刪除未使用頁並建立更多空間。可以手動收縮或者設定為定期自動收縮資料庫檔案。收縮過程可以設定為後台運行， 而其他的任務可以繼續在前台進行。
EXEC sp_dboption 'pub','single user' //同一時間內只有一個使用者可以訪問這個資料庫。
——分離資料庫
EXEC sp_detach_db '資料庫名'
——附加資料庫
EXEC sp_attach_db '資料庫名','資料庫檔案的物理位置'
——更改資料庫名
EXEC sp_renamedb '舊資料庫名','新資料庫名'
——添加Windows登入帳戶
EXEC sp_grantlogin 'windows網域名稱\域帳戶' //windows網域名稱\域帳戶：電腦名稱，在電腦管理器中添加。
——添加SQL登入帳戶
EXEC sp_addlogin '帳戶名稱','密碼'
——建立資料庫使用者
EXEC sp_grantdbaccess '登入帳戶','資料庫使用者' //"資料庫使用者"為可選項，預設為登入帳戶，即資料庫使用者預設和登入帳戶同名。

4、收縮資料庫：
DBCC SHRINKDATABASE(PUBS,10) //用DBCC命令收縮資料庫，減少'pubs'資料庫中檔案大小，並允許其有 10%的未用空間。
5、建立表：
creat table Airlines_Master(Aircode char(2),AirName varchar(15))
建立表Airlines_Master，表有兩個欄位Aircode和AirName，資料類型char和varchar，大小2，15。
6、SQL Server資料類型
7、資料完整：
（1）實體完整性：
“實體完整性”的規則規定，基表主鍵的任何部分都不可以接受空值。
主鍵：唯一地標識表中的記錄的一個或一組列稱為“主鍵”。
每個表都應有一個主鍵。
（2）值域完整性：
指定輸入到給定列的資料的合法性。
（3）參考完整性：
“參考完整性”的規則規定，所引用的外部資料必須存在。
DBMS負責確保外鍵的屬性值有效，並且在該屬性作為主鍵的表中能找到與之對應的匹配項。僅當在其他表中沒有與之對應的外鍵時，主表中的記錄才能刪除。外鍵中不能引入新的值。
外鍵：是一個或一組列，其中列的值與另外一個表中的主鍵或唯一鍵匹配。
１、兩個表是通過外部索引鍵關聯起來的。
２、在給定的表中，要將某一列設定為外鍵，那麼該列應該對應另外一個表的主鍵或唯一鍵。
３、兩個通過外鍵關係關聯到一起的表，其相應欄位的類型定義應該相同。
（4）使用者定義完整性：
8、主鍵（PRIMARY KEY）約束
（1）主鍵（PRIMARY KEY）：
——建立主鍵方法一：
create table table_name
(PNR_no int PRIMARY KEY) //在建table_name表時，為PNR_no列建立主鍵約束。
——建立主鍵方法二：（更改主鍵約束）
alter table table_name
add constraint PK_const
PRIMARY KEY(PNR_NO)
//為表table_name的PNR_NO列添加PK_const（約束名）主鍵約束。
（2）按鍵組合：
（3）標識主鍵：
（4）唯一約束：
（5）識別屬性：
——IDENTITY(自動成長)
creat table table_name
(PNR_no int IDENTITY(1,1))
//建立表table_name，PNR_no列名，IDENTITY屬性，1：初始值。1：步長（可以是負值）。
9、修改資料庫表：
ALTER TABLE table_name
[ALTER COLUMN column_name int] //指定要修改的列,int 指定將列修改為新的資料類型int。
|ADD column_name int //int要添加列的資料類型，ADD向表中加一列。
|DROP COLUMN column_name //DROP COLUMN 從表中刪除一列
10、刪除資料庫中表：
drop table table_name
drop table不能刪除有外鍵約束引用的表。
11、約束和約束對象
12、DEFAULT
13、外鍵約束：
14、添加和刪除表的約束對象：
——在建立表時建立約束：
create table table_name
(column_first char(2) primary key,column_second varchar(15));
//建立table_name表，給column_first 列建立主鍵約束。
——在現有表中建立約束
alter table table_name add constraint check_name check(unityprice>=10)
//給表table_name添加check_name約束條件為unityprice>=10，add constraint 關鍵字。
——添加主鍵約束（stuNo作為主鍵）
ALTER TABLE stuInfo
ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNO)
——添加唯一約束（社會安全號碼唯一，因為每人的社會安全號碼是全國唯一的）
ALTER TABLE stuInfo
ADD CONSTRAINT UQ_stuID UNIQUE(stuID)
——添加預設約束（如地址不添，預設值為“地址不詳”）
ALTER TABLE stuInfo
ADD CONSTRAINT DF_stuAddress DEFAULT('地址不詳') FOR stuAddress
——添加檢查check約束，要求年齡只能在15—40歲之間。
ALTER TABLE stuInfo
ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND40)
——添加外鍵約束（主表stuInfo和從表stuMarks建立關係，關聯欄位為stuNo）
ALTER TABLE stuMarks
ADD CONSTRAINT FK_stuNo
FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo)
GO
——刪除約束
ALTER TABLE stuInfo
DROP CONSTRAINT DF_stuAddress
15、T-SQL中的條件運算式和邏輯運算子：
（1）一元運算子：
（2）二元運算子：
（3）比較子：
（4）萬用字元：
（5）邏輯運算子：
16、插入語句：
INSERT INTO jobs VALUES('Graphic Artist',25,100) //插入一行。
17、將一個表中的資料添加到另一個表中：
CREATE TABLE author_details(au_id varchar(11),au_lname varchar(40),)
GO
INSERT author_details SELECT authors.au_id,authors.au_lname, FROM authors
//將authors表中的au_id和au_lname列的內容插入到author_details表中。
18、更新表中的資料
（1）更新一行
UPDATA table_name
SET price=price+(25/100*price)
WHERE table_id='TC7777'
//更新table_name表的列table_id的TC7777行的price列的值為price+(25/100*price)。
（2）更新多行
UPDATA table_name
SET column_first='aaa',column_second=1
//將table_name表的column_first和column_second的所有行更新為aaa和1.
（3）使用關聯資訊更新
——內聯結
——外串連
——自連結
19、從表中刪除資料
（1）刪除一行資料
delete from pub_info where pub_id=9999 //刪除pub_id值為9999的行
（2）刪除多行資料
delete from stores where state='CA' // 刪除state所有值為CA的行。
（3）刪除表中的所有資料
truncate table sales //truncate table 用於刪除表中所有行的命令。
不能用於有外鍵約束引用的表，這種情況下，需要使用不帶WHERE子句的DELETE語句。
20、查詢語句：
SELECT column_1,column_2 FROM table_name;
//列名用“，”分開，";"是可選的。
SELECT * FROM table_name //搜尋table_name的所有列。
SELECT au_fname FROM authors WHERE state='CA'; //WHERE指定了查詢條件
SELECT state FROM authors GROUP BY state //查詢authors表的state列，並按不同內容的state列分組。
SELECT * FROM authors WHERE state='CA' ORDER BY au_fname
//查詢authors表的所有符合state='CA' 條件的列，並按au_fname排序。參數未指定預設為升序。DESC是降序，ASC是升序。
21、在查詢中使用常量：
當串連字元列時，為了獲得正確的格式或可讀性，可以使用字串常量。常數一般不會在結果集中作為單獨的列指定。通常在顯示結果集時，使用應用程式將常數值合并到結果集中比通過伺服器合并常數值效率更高。
SELECT title_id+':'+title+'->'+type FROM titles
//搜尋titles表中的title_id列、title列、type列，並用###：ttt->xx的形式顯示。
注意：在已選的表中使用加號（+）時，一定要注意列的資料類型。該列與其左右兩邊的列的資料類型應該一致。否則SQL Server會給出錯誤資訊。
22、使用AS子句命名列：
SELECT PNR_no AS 'PNR NUMBER' FROM Reservation
//搜尋Reservation表的PNR_no列，顯示列名為“PNR NUMBER”
23、使用識別欄位：
SELECT IDENTITY(datatype,seed,increment) AS column_name
INTO Table2
FROM Table1
//檢索表Table1，將檢索到的內容放到表Table2中列名為column_name的列中，datatype為資料類型（Int或Decimal）；seed為第一行的值，increment為遞增的步長。
24、使用TOP子句限制查詢返回行數。
SELECT TOP 3 * FROM table;
//顯示table表的前三行。
SELECT TOP 40 PERCENT * FROM table;
//顯示表table所有行中前40%的行。
25、分組查詢
26、彙總函式
——SUM:返回運算式中所有數值的總和。
——AVG:返回運算式中所有數值的平均值。
——COUNT:返回提供的運算式中非空值的數目。
——MAX:返回運算式中最大的值。
——MIN:返回運算式中最小的值。
27、使用HAVING子句選擇行
28、向資料庫使用者授權
USE database_name //資料庫名、
GO
//為DBUser分配對錶table_name的select,insert,updata 許可權。
GRANT select,insert,updata ON table_name TO DBUser
//為DBUser2分配建表的許可權。
GRANT create table TO DBUser2
29、使用變數
——局部變數
DECLARE @variable_naem DataType //@variable_naem 局部變數名稱，DataType為資料類型。
——局部變數的付值
1、
SET @variable_name=value
2、
SELECT @variable_name=value
——全域變數
全域變數有系統定義和維護。
@@ERROR 　　　　　　　　　　最後一個Ｔ－ＳＱＬ錯誤的錯誤號碼
@@IDENTITY　　　　　　　　　最後一個插入的標識值
@@LANGUAGE　　　　　　　　當前使用語言的名稱
@@MAX_CONNECTIONS　　　　可以建立的同時連結的最大數目
@@ROWCOUNT　　　　　　　　受上一個ＳＱＬ語言影響的行數
@@SERVERNAME　　　　　　　　本機伺服器的名稱
@@SERVICENAME　　　　　　　該電腦上的ＳＱＬ服務的名稱
@@TIMETICKS　　　　　　　　　當前電腦上每刻度的微秒數
@@TRANSCOUNT　　　　　　　當前串連開啟的事務數
@@VERSION　　　　　　　　　　SQL Server的版本資訊
30、輸出語句
print '伺服器的名稱：'+@@SERVERNAME //本機伺服器名稱
SELECT @@ SERVERNAME AS '伺服器名稱'
樣本：
//@@ERROR返回的是整型數值，用convert(varchar(5),@@ERROR)的方式將它轉換為字串。
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge)VALUES('梅超風','s25318','女','23')
print '當前錯誤號碼'+convert(varchar(5),@@ERROR) //如果大於0，表示上一條語句執行有錯誤
print '剛才報名的學員，座位號為：' +convert(varchar(5),@@IDENTITY)
UPDATA stuinfo SET stuAge=85 WHERE stuName='李文才'
print 'SQL Server 的版本'+@@VERSION
GO
//輸出結果為
當前錯誤號碼0
剛才報名的學員，座位號：12
伺服器：訊息547，層級16，狀態1，行1
UPDATA 語句與COLUMN CHECK 條件約束’CK_stuAge‘衝突。該衝突發生於資料庫'stuDB',表'stuInfo'
語句終止
當前錯誤號碼547
SQL Server的版本Microsoft SQL Server 2000-8.00.2039(Intel X86)
……
31、邏輯控制語句
IF-ELSE:
IF(條件)
BEGIN
語句1
語句2
END
ELSE
……
WHILE迴圈語句：
WHILE(條件）
BEGIN
語句或語句塊
[BREAK]
END
CASE多分支語句：
CASE
WHEN 條件1 THEN 結果1
WHEN 條件2 THEN 結果2
[ELSE 其他結果]
END

32、批處理語句“GO”
"GO"就是批處理的標誌，它是一條或多條SQL語句的集合，SQL Server將批處理語句編譯成一個可執行單元，此單元成為執行計畫。每個批處理可以編譯成單個執行計畫，從而提高執行效率。如果批處理包含多條SQL語 句，執行這些語句所需的所有最佳化的步驟將編譯在單個執行計畫中。
批處理的主要好處就是簡化資料庫的管理。
批處理樣本如下：
USE Master
GO
//GO關鍵字標誌批處理結束。
另一個樣本：
SELECT * FROM stuInfo
SELECT * FROM stuMarks
UPDATA stuMarks SET writtenExam=writtenExam+2
GO
//此時三條語句組成一個執行計畫，然後再執行。
一般是將一些邏輯相關的業務動作陳述式，放置在同一批中，這完全由代碼編寫者決定。
但是，SQL Server規定：如果是建庫、建表語句、以及我們後面學習的預存程序和視圖等，則必須在語句末 尾加“GO”批處理標誌。
33、簡單子查詢
方法一：採用T-SQL變數實現
DECLARE @age INT //定義變數，用於存放李斯文的年齡
SELECT @age=stuAge FROM stuInfo where stuName='李斯文' // 求出李斯文的年齡。
SELECT * FROM stuInfo WHERE stuAge>@age //篩選比李斯文年齡大的學員。
GO
方法二：採用子查詢實現。
SELECT * FROM stuInfo
WHERE stuAge>(SELECT stuAge FROM stuIfo where stuName='李斯文')
GO
//必須保證子查詢返回的值不能多於一個。
——將多表間資料群組合在一起，替換串連查詢。
方法一：採用表串連
SELECT stuName FROM stuInfo INNER JOIN stuMarks //INSERT JOIN 內串連
ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam=60
GO
方法二：採用子查詢
SELECT stuName FROM stuInfo
WHERE stuNo=(SELECT stuNo FROM stuMarks WHERE writtenExam=60)
go
一般來說，表串連都可以用子查詢替換，但反過來說卻不一定。有的子查詢不能用表串連來替換。子查詢比較靈活、方便，形式多樣，適合於作為查詢的篩選條件。而表串連更適合查多表的資料。
34、IN和NOT IN子查詢
使用“=”、“>”等計較運算子號，要求子查詢只能返回一條或空的記錄。SQL Server中，當子查詢跟隨在=、!=、<、<=、>、>=之後，不允許子查詢返回多條記錄。
——採用IN子查詢
//查詢參見考試的學員名單
SELECT stuName FROM stuInfo
WHERE stuNo IN (SELECT stuNo FROM stuMarks)
go
——採用NOT IN子查詢
//查詢未參加考試學員的名單
SELECT stuName FROM stuInfo
WHERE stuNo NOT IN （SELECT stuNo FROM stuMarks）
GO
35、EXISTS和NOT EXISTS子查詢
IF EXISTS(SELECT * FROM sysDatabase WHERE name='stuDB')
DROP DATABASE stuDB
CREATE DATABASE stuDB
……建庫代碼略
問題：檢查本次考試，本班如果有人筆試成績達到80分以上，則每人提2分。否則，每人允許提5分。
IF EXISTS(SELECT * FROM stuMarks WHERE writtenExam>80)
BEGIN
print '本班有人筆試成績高於80分，每人只加2分，加分後的成績為：'
UPDATE stuMarks SET writtenExam=writtenExam+2
SELECT * FROM stuMarks
END
GO
//EXISTS 和IN 一樣，同樣允許添加NOT取反，表示不存在。
問題：檢查本次考試，本班如果沒有一人通過考試（筆試和機試成績都>60分），則實體偏難，每人加3分，否則，每人加1分。
IF NOT EXISTS(SELECT * FROM stuMarks WHERE writtenExam>60 AND labExam>60)
BEGIN
print '本班無人通過考試，考試題偏難，每人加3分，加分後的成績為：'
UPDATE stuMarks SET writtenExam=writtenExam+3，labExam=labExam+3
SELECT * FROM stuMarks
END
ELSE
BEGIN
print '本班考試成績一般，每人只加1分，加分後的成績為：'
UPDATE stuMarks SET writtenExam=writtenExam+1，labExam=labExam+1
SELECT * FROM stuMarks
END
GO
36、T-SQL語句的綜合應用
假定目前本次考試學員資訊表（stuIfo）和學員成績表（stuMarks）的未經處理資料為如下：
stuIfo表：
stuName stuNo stuSex stuAge stuSeat stuAddress
1 張秋麗 s25301 男 18 1 北京海澱
2 李文才 s25302 男 31 3 地址不詳
3 李斯文 s25303 女 22 2 河南洛陽
4 歐陽俊雄 s25034 男 28 4 新疆威武哈
5 梅超風 s25318 女 23 5 地址不詳
stuMarks表：
ExamNo stuNo writtenExam labExam
1 S271811 s25303 93 59
2 S271813 s25302 63 91
3 S271816 s25301 90 83
4 S271817 s25318 63 53
問題：
1、統計本次考試的缺考情況，結構如下：
應到人數 實到人數 缺考人數
1 5 4 1
2、提取學員的成績資訊並儲存結果，包括學員姓名、學號、筆試成績、機試成績、是否通過。
3、比較筆試平均分和機試平均分，較低者進行迴圈提分，但提分後最高分不能超過97分。
4、提分後，統計學員的成績和通過情況，如下：
姓名 學號 筆試成績 機試成績 是否通過
1 張秋麗 s25301 90 89 是
2 李文才 s25302 63 97 是
3 李斯文 s25303 93 65 是
4 歐陽俊雄 s25034 缺考 缺考 否
5 梅超風 s25318 63 59 否