sql server 2000

此系统应用方便简洁

--第六章--使用Transact-SQL语言和企业管理器管理数据库

--用户可以是用DDL 创建一个标准表Member 在Yuguang 数据库中。CREATE TABLE 语句用来创建表，新表有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", "[e-mail]")

--用于id是自增长的，所以在插入的时候不必插入，SQL会自动生成。如果用户不指定子段名称，比如
INSERT INTO Member
VALUES ("argentina ", " yuguang ", " [e-mail] ")

--用户还可以把用户名是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命令用来执行语句：
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子句。下面的这个例子使用了数据库Northwind：
USE      Northwind
GO
SELECT   IDENTITYCOL
FROM     Employees
GO

--使用一个列名称可以允许用户声明一个列的头。用户可以使用特定的名称区分输出列中的数据。当在不同的表中存在两行或者更多的行拥有相同的名字的时候，用户可以通过在对应表的列名称中加入特殊信息用以区分数据。下面是一个实例： 
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子句。下面的语句用来返回卖出书籍的数量和书籍存储编号。
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 用来从其他表中获取数据用于当前数据库表的查询工作：
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指定在结果集中包含左表或右表中不满足联接条件的行，并将对应于另一个表的输出列设为 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指定在结果集中包含左表中所有不满足联接条件的行，且在由内联接返回所有的行之外，将另外一个表的输出列设为 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 指定在结果集中包含右表中所有不满足联接条件的行，且在由内联接返回的所有行之外，将与另外一个表对应的输出列设为 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语句用来创建一个新的表，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 用来返回布尔型数值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中包括数字724的记录：
SELECT   *
FROM     authors
WHERE    au_id LIKE '724-__-____'
GO

--查找authors中last 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来联系上述两个通配符：
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 数据库 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没有内容的所有记录：
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的状态：
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 "所有地区" 
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 "标题个数" 
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可以计算查询结果的个数：
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是空的，则把内容赋成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

--强制现存的列中数据的默认只是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'