--第六章--使用Transact-SQL語言和企業(yè)管理器管理數(shù)據(jù)庫

--用戶可以是用DDL 創(chuàng)建一個標準表Member 在Yuguang 數(shù)據(jù)庫中。CREATE TABLE 語句用來創(chuàng)建表，新表有4列：
Use Yuguang
CREATE TABLE Member
(id  smallint,
username    char(20),
truename     char(20),
email         char(10))
GO

--用戶可以通過ALTER TABLE 在Member表中增加一列叫做address。
ALTER TABLE Member
ADD address char(50)
GO

--刪除Member表的語句：
DROP TABLE Member
GO

--用戶希望給Member 表中增加一個新的用戶，則用戶可以這樣使用INSERT語句：
INSERT INTO Member
( username, truename, email)
VALUES ("argentina", "yuguang", "fuyuguang@yuguang.net")

--用于id是自增長的，所以在插入的時候不必插入，SQL會自動生成。如果用戶不指定子段名稱，比如
INSERT INTO Member
VALUES ("argentina ", " yuguang ", " fuyuguang@yuguang.net ")

--用戶還可以把用戶名是yuguang的用戶記錄提取出來：
SELECT id, username FROM Member
WHERE username = "yuguang"

--用戶fuyuguang希望更改他的用戶名成為use_yuguang，則可以通過下面的步驟：
UPDATE Member
SET username = "use_yuguang"
WHERE truename = "yuguang"

--刪除Member表中的所有行，通過下面兩個語句當中的一個：
DELETE FROM Member
DELETE Member

--用來刪除Member表中所有id小于100的記錄，通過下面的語句：
DELETE FROM Member
WHERE id < 100

--在DOS下運行OSQL 命令：
osql -U username -P password -S servername

--下面的代碼就是一個簡單的腳本：
use MyDB
go
sp_helpdb MyDB
go
sp_helpfilegroup
go
sp_spaceused Customer_Data
go
sp_spaceused Product_Info
go

--把所有authors表中的au_fname, au_lname兩列提取出來：
SELECT   au_fname, au_lname
FROM     authors
如果用戶使用OSQL命令行不要忘記敲入GO命令用來執(zhí)行語句：
USE      pubs
SELECT   au_fname, au_lname
FROM     authors
GO

--第一個查詢使用了DISTINCT子句，第二個查詢使用了TOP 60 PERCENT子句，第三個查詢使用了TOP 6 子句。
SELECT   DISTINCT au_fname, au_lname
FROM     authors
GO

SELECT   TOP 60 PERCENT au_fname, au_lname
FROM     authors
GO

SELECT   TOP 6 au_fname, au_lname
FROM     authors
GO

--使用*可以返回所有查詢的記錄：
SELECT   *
FROM     sales
GO

--獲取所有表中的所有被認證的列用戶可以通過IDENTITYCOL子句。下面的這個例子使用了數(shù)據(jù)庫Northwind：
USE      Northwind
GO
SELECT   IDENTITYCOL
FROM     Employees
GO

--使用一個列名稱可以允許用戶聲明一個列的頭。用戶可以使用特定的名稱區(qū)分輸出列中的數(shù)據(jù)。當在不同的表中存在兩行或者更多的行擁有相同的名字的時候，用戶可以通過在對應表的列名稱中加入特殊信息用以區(qū)分數(shù)據(jù)。下面是一個實例： 
USE      pubs
GO
SELECT   lname
FROM     employee
GO

--如果用戶希望把原來的lname更新成顯示"員工的姓氏" 則需要運行以下的語句：
SELECT   lname AS "員工的姓氏" 
FROM     employee
GO

--用戶還可以指定特定輸出結果的列名稱，如下所示，顯示最大的job_id的列名稱是"最大工作ID"： 
SELECT   MAX(job_id) AS "最大工作ID"
FROM     employee
GO

--用戶還可以在SELECT中使用ORDER BY子句。下面的語句用來返回賣出書籍的數(shù)量和書籍存儲編號。
SELECT   SUM(qty) AS Quantity_of_Books, stor_id 
FROM     sales
GROUP BY stor_id
ORDER BY Quantity_of_Books
GO

--如果用戶不指定SUM(qty)的名稱，則列的名稱將成為"無名列"，語句如下：:
SELECT   SUM(qty), stor_id FROM sales
GROUP BY stor_id
ORDER BY SUM(qty)
GO

--derived table 用來從其他表中獲取數(shù)據(jù)用于當前數(shù)據(jù)庫表的查詢工作：
USE      pubs
GO
SELECT   t.stor_name
FROM     stores AS t, (SELECT stor_id, COUNT(DISTINCT discounttype)
                         AS d_count
                         FROM     discounts
                         GROUP BY stor_id) AS w
WHERE    t.stor_id = w.stor_id AND
         w.d_count >= 1 
GO

--inner join 將聲明僅僅符合ON條件的記錄才能被加入到結果中。實例操作：
SELECT   f.stor_id, e.discounttype
FROM     stores f JOIN discounts e
ON       f.stor_id = e.stor_id
GO

--Full Outer Joins指定在結果集中包含左表或右表中不滿足聯(lián)接條件的行，并將對應于另一個表的輸出列設為 NULL。實例如下：
SELECT   f.stor_id, e.discounttype
FROM     stores f FULL OUTER JOIN discounts e
ON       f.stor_id = e.stor_id
GO

--Left Outer Joins指定在結果集中包含左表中所有不滿足聯(lián)接條件的行，且在由內(nèi)聯(lián)接返回所有的行之外，將另外一個表的輸出列設為 NULL。
SELECT   f.stor_id, e.discounttype
FROM     stores f LEFT OUTER JOIN discounts e
ON       f.stor_id = e.stor_id
GO

--Right Outer Joins 指定在結果集中包含右表中所有不滿足聯(lián)接條件的行，且在由內(nèi)聯(lián)接返回的所有行之外，將與另外一個表對應的輸出列設為 NULL。實例如下： 
SELECT   e.stor_id, f.discounttype
FROM     stores e RIGHT OUTER JOIN discounts f
ON       e.stor_id = f.stor_id
GO

--Cross Joins 指定兩個表的矢量積,實例如下：
SELECT   *
FROM     stores
GO
SELECT   *
FROM     sales
GO
SELECT   *
FROM     stores CROSS JOIN sales
GO

--Table Aliases
SELECT   t.stor_id, r.discounttype
FROM     stores t RIGHT OUTER JOIN discounts r
ON       t.stor_id = r.stor_id
GO

--下面的SELECT...INTO語句用來創(chuàng)建一個新的表，xinxi_guyuan，其中包括所有雇員的姓名和工作介紹：
SELECT   employee.fname, employee.lname, jobs.job_desc 
INTO     xinxi_guyuan
FROM     employee, jobs
WHERE    employee.job_id = jobs.job_id
GO

--查找employee表中的用戶名是yuguang的記錄的所有信息。
SELECT   *
FROM     employee
WHERE    lname = 'yuguang'
GO

--來查找job_id不等于1的記錄： 
SELECT   job_desc
FROM     jobs
WHERE    job_id <> 1
GO

--邏輯操作AND 和 OR 用來返回布爾型數(shù)值TRUE, FALSE,或者UNKNOWN。下面的查詢在WHERE查詢中是用了邏輯操作AND。
SELECT   job_desc, min_lvl, max_lvl
FROM     jobs 
WHERE    min_lvl >= 120 AND
         max_lvl <= 125
GO

--在下面的查詢中，是用了一個OR 操作。
SELECT   p.pub_name, p.state, t.title
FROM     publishers p, titles t
WHERE    p.state = "DC" OR
         p.state = "MA" AND
         t.pub_id = p.pub_id
GO

--NOT操作簡單的返回布爾型變量：
SELECT   t.title, r.royalty
FROM     titles t, roysched r
WHERE    t.title_id = r.title_id AND NOT
         r.royalty  < 10
GO

--尋找authors表中au_fname列中含有"c"的記錄。 
SELECT   au_fname
FROM     authors
WHERE    au_fname LIKE '%c%'
GO

--下列語句用來查找作者ID中包括數(shù)字724的記錄：
SELECT   *
FROM     authors
WHERE    au_id LIKE '724-__-____'
GO

--查找authors中l(wèi)ast names中的從"f" 到 "i"的紀錄：
SELECT   au_lname
FROM     authors
WHERE    au_lname LIKE "[f-i]%"
GO

--而符號[^]的使用方法和[]的方法相同，而兩者的含義正好相反：
SELECT   au_lname
FROM     authors
WHERE    au_lname LIKE '[^a-n]%'
GO

--用戶還可以通過or來聯(lián)系上述兩個通配符：
SELECT   au_lname
FROM     authors
WHERE    au_lname LIKE '[l-n]%' OR
         au_lname LIKE '[t-z]%'
GO

--LIKE關鍵字還可以和NOT操作相結合，返回所有不匹配條件的記錄：
SELECT   title
FROM     titles
WHERE    title NOT LIKE '%Is%'
GO

--在 pubs 數(shù)據(jù)庫 titles 表的 notes 列中搜索字符串"50% off when 100 or more copies are purchased"：
USE pubs
GO
SELECT notes
FROM titles
WHERE notes LIKE '50%% off when 100 or more copies are purchased' 
   ESCAPE '%'
GO

--下面的語句用來查找書籍的價格在$20到$30之間的所有記錄：
SELECT   price, title
FROM     titles
WHERE    price BETWEEN 20.00 AND 30.00
GO

--	用戶還可以通過在BETWEEN中使用NOT關鍵字。下列的記錄顯示在$10 到 $40之外的書籍的記錄：
SELECT   price, title
FROM     titles
WHERE    price NOT BETWEEN 10.00 AND 40.00
GO

--下面的語句用來查找作者姓氏在" del Castillo " 到 "Hunter"之間的所有記錄：
SELECT   au_lname
FROM     authors
WHERE    au_lname BETWEEN 'del Castillo' AND 'Hunter'
GO

--查找書籍名稱中advance沒有內(nèi)容的所有記錄：
SELECT   title , advance
FROM     titles
WHERE    advance IS NULL
GO

--IS NOT NULL的用法相同，但是意義相反：
SELECT   title , advance
FROM     titles
WHERE    advance IS NOT NULL
GO

--IN關鍵字用來進行子查詢，實例代碼：
SELECT   job_id , job_desc
FROM     jobs
WHERE    job_desc IN ('Operations Manager', 
                      'Marketing Manager',
                      'Designer')
GO

--下面的實例應用了兩次IN關鍵字：
SELECT   fname, lname , job_id --uter query
FROM     employee
WHERE    job_id IN ( SELECT job_id    --Inner query, or subquery
                     FROM   jobs
                     WHERE  job_desc IN ('Operations Manager',
                                         'Marketing Manager',
                                         'Designer'))
GO

IN關鍵字往往可以加上NOT操作。實例如下：
FROM     publishers
WHERE    state NOT IN ('MA','NY')
GO

--下列的語句用來察看ANSI nulls的狀態(tài)：
sp_dboption "pubs", "ANSI nulls"
GO

--如果 ANSI nulls被設置成OFF，則可以通過下面的語句改變設置：
sp_dboption 'pubs', 'ANSI nulls', TRUE
GO

--使用 EXISTS 關鍵字引入一個子查詢時，就相當于進行一次存在測試,實例代碼：
SELECT   au_fname, au_lname
FROM     authors 
WHERE    EXISTS (SELECT au_id
                 FROM   titleauthor
                 WHERE  titleauthor.au_id = authors.au_id)
GO

--用來查找所有賣出圖書的書名ID：
SELECT   title_id, SUM(qty)
FROM     sales 
GROUP BY title_id
GO

----如果用戶希望把（無名列）換成Total Sales 的代碼如下：
SELECT   title_id, SUM(qty) AS "總銷售"
FROM     sales
GROUP BY title_id
GO

--用戶希望查找書籍平均價格，然后通過type和publisher進行分類，代碼如下：
SELECT   type, pub_id, AVG(price) AS "Average Price"
FROM     titles
GROUP BY type, pub_id
GO

--顯示上了12%稅的每本書的平均價格，然后通過type 和publisher ID進行排序，代碼如下：
SELECT   type, pub_id,AVG(price) AS "平均價格"
FROM     titles
WHERE    royalty = 15
GROUP BY ALL type, pub_id 
GO

--通過用HAVING來查找平均價格高于$10的書籍：
SELECT   type, pub_id, AVG(price) AS "平均價格"
FROM     titles
GROUP BY type, pub_id
HAVING   AVG(price) > 10.00
GO

--用戶還可以在語句中使用邏輯操作AND：
SELECT   type, pub_id, AVG(price) AS "平均價格"
FROM     titles
GROUP BY type, pub_id
HAVING   AVG(price) >= 10.00 AND
         AVG(price) <= 30.00
GO

--用戶可以使用BETWEEN子句代替上面實例中的AND操作，代碼如下：
SELECT   type, pub_id, AVG(price) AS "平均價格"
FROM     titles
GROUP BY type, pub_id
HAVING   AVG(price) BETWEEN 10.00 AND 20.00
GO

--用戶選擇了書籍價格的總和，然后在進行查找總和大于$10的記錄，代碼如下：
SELECT   SUM(price)
FROM     titles
WHERE    type = 'mod_cook'
HAVING   SUM(price) > 10
GO

--ORDER BY子句用來排列查詢結果記錄的順序,用戶可以在ORDER子句中加入多個條件：
SELECT   au_lname, au_fname
FROM     authors
ORDER BY au_lname ASC
GO

--首先通過job_id排序，然后通過lname排序，最后通過fname排序。：
SELECT   job_id, lname, fname
FROM     employee
ORDER BY job_id, lname, fname
GO

--用戶可以在ORDER BY子句的使用同時，還可以使用GROUP BY子句：
SELECT   type, pub_id, AVG(price) AS "Average Price"
FROM     titles
GROUP BY type, pub_id
ORDER BY type
GO

--用戶還可以使用自定義的詞組進行排序：
SELECT   type, pub_id, AVG(price) AS "平均價格"
FROM     titles
GROUP BY type, pub_id
ORDER BY '平均價格'
GO

--UNION操作,實例代碼如下：
SELECT   city, state
FROM     publishers
UNION    SELECT city, state
         FROM   stores
GO

--用戶可以子定義列的名稱，代碼如下：
SELECT   city AS "所有城市", state AS "所有地區(qū)" 
FROM     publishers
UNION    SELECT city, state
         FROM   stores
GO

--如果通過同一個表查詢，就不用再寫一次SELECT語句：
SELECT   city, country
FROM     publishers
UNION    SELECT city, state
         FROM   stores
GO

--用戶還可以在查詢語句的時候使用GROUP BY 或者HAVING：
SELECT   type AS "類型" , COUNT(title) AS "標題個數(shù)" 
FROM     titles
GROUP BY type
UNION    SELECT   pub_name, COUNT(titles.title)
         FROM     publishers, titles
         WHERE    publishers.pub_id = titles.pub_id
         GROUP BY pub_name
GO

--COUNT可以計算查詢結果的個數(shù)：
SELECT   COUNT(*)
FROM     publishers
GO

--AVG, COUNT, MAX, MIN,和SUM 功能都可以使用ALL 和 DISTINCT操作。實例如下：
SELECT   MAX(price) - MIN(price) AS "價格差異"
FROM     titles
GO

--在下面的實例中，SUM將計算總和：
SELECT   stores.stor_name, SUM(sales.qty) AS "全部項目排序" 
FROM     sales, stores
WHERE    sales.stor_id = stores.stor_id
GROUP BY stor_name
GO

--改變空值,實例：
ALTER TABLE member
ALTER COLUMN note Tinyint NULL
GO

--如果NOTES是空的，則把內(nèi)容賦成None。
UPDATE TITLES SET NOTES = 'None'
WHERE NOTES IS NULL
GO

--把一個列設置成非空（NOT NULL）：
ALTER TABLE notes
ALTER COLUMN notes Char(300) NOT NULL
GO

--增加（刪除）ROWGUIDCOL特性的語法：
ALTER TABLE <table_name>
ALTER COLUMN <column_name> ADD(DROP) ROWGUIDCOL
GO

--增加ROWGUIDCOL的實例：
ALTER TABLE Bicycle_Sales
ALTER COLUMN  unique_id ADD ROWGUIDCOL
GO

--刪除ROWGUIDCOL的實例：
ALTER TABLE Bicycle_Sales
ALTER COLUMN  unique_id DROP ROWGUIDCOL
GO

--在member表中增加一個新列，叫做chanpin_id使用下列語句：
ALTER TABLE member
ADD chanpin_id Tinyint  NOT  NULL
DEFAULT 0
GO

--把chanpin _id的屬性改成空：
ALTER TABLE  member
ADD chanpin_id Tinyint NULL
DEFAULT 0
GO

--強制現(xiàn)存的列中數(shù)據(jù)的默認只是0而不是NULL，需要使用WITH VALUES操作：
ALTER TABLE  member
ADD chanpin_id  Tinyint NULL
DEFAULT 0 WITH VALUES
GO

--下列命令用來刪除member 表中的chanpin_id 列：
ALTER TABLE member
DROP COLUMN  chanpin_id
GO

--下例將表 customers 中的列 contact title 重命名為 title。
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'