SQL Server小札

表格创建
CREATE TABLE Employee
(Emp_id numeric(10,0) IDENTITY,
last_name varchar(30) NOT NULL,
first_name varchar(30) NOT NULL,
age numeric(3,0)
country varchar(30) NOT NULL
city varchar(30) NULL
)

NOTES
IDENTITY，一个表里只能定义一个标识列，默认值为(1,1),插入数据时值会递增
ROWGUIDCOL， 定义该列为行全局唯一的标识列，一个表只有一个标识符列可以定义为ROWGUIDCOL

表格的删除
DROP TABLE table_name

修改表格定义
CREATE TABLE doc_exa (colum_a INT)
ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
ALTER TABLE doc_exa ADD column_c VARCHAR(20) NULL
ALTER TABLE doc_exa DROP column_c
ALTER TABLE doc_exa CHECK CONSTRAINT column_B
ALTER TABLE doc_exa NOCHECK CONSTRAINT column_B
ENABLE TRIGGER trigger_name
ALTER TABLE doc_exa DROP column_c
DROP TABLE doc_exa

查看表属性
sp_help table_name '查看表和表中数据列有关的信息
sp_spaceused table_name '查看表格行数和表格所用的存储空间
sp_depends table_name '查看表格的这种相关性关系

表格重命名
sp_rename old_table_name,new_table_name

索引
优点：在列上建立的一种数据库对象，对表中的数据提供逻辑排序，可以提高数据的访问速度。
缺点：过多的建立索引会占据大量磁盘空间，增加系统开销

不适用
查找小表（如100行数据）中的某些数据
查找大表中绝大多数数据

适用
查找大表中有限数量的数据，使用索引是一个不错的选择

簇族索引和非簇族索引
簇族索引按照键值对进行排列，一个表只能对应一个簇族索引
非簇族索引不根据键值排序，索引结构与数据行是分开的，查找速度明显低于簇族索引

创建索引INDEX
语法
CREATE UNIQUE CLUSTERED|NOCLUSTERED INDEX index_name ON table (column)

例
CREATE TABLE emp_pay
(employeeID int NOT NULL,
base_pay money NOT NULL,
commission decimal(2,2) NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX employeeID_ind ON emp_pay (employeeID)

索引删除
DROP INDEX table.index
DROP INDEX emp_pay.employeeID

DBMS
数据库管理系统(database management system)

遍历
所谓遍历(Traversal)，是指沿着某条搜索路线，依次对树中每个结点均做一次且仅做一次访问

TOP关键字
只返回前面一定数量的数据
语法
SELECT [TOP integer|TOP integer PERCENT] column_name FROM table_name
例
select top 10 * from customer
select top 10 percent * from customer

DISTINCT关键字
优点：能够从返回的结果数据集合中删除重复行，使返回结果更简洁。
缺点：须花费额外的CPU时间来执行查询数据的分类和整理，导致查询速度变慢
例
select distinct country from publishers
注意：DISTINCT关键字作用范围是整个查询列表，而不是单一的列，如同时对两列数据进行
查询时，将返回两列数据的唯一组合。

操作查询的列名
可以用AS关键字，=号来连接
例
select title_id AS '图书代号', '原价'=price,price-price*0.3 '现价' from titles

BETWEEN,IN关键字

LIKE字句模糊查询
通配符
%：任意个字符
_：单个字符
[]：方括号里列出的任意一个字符
[^]：任意一个没有在方括号列出的字符

ESCAPE转义字符
select coll
from example
where coll like 't[X[XYZ]Z]'
escape 't'

排序ORDER BY，ASC，DESC
select strore_name,city from stores order by 1 desc
1代表所处的位置

INSERT添加表格数据
INSERT publishers
(pub_id,pub_name,city,state)
VALUES
('9993','SANCO','WASHINGTON','WA')

UPDATE实现数据修改
语法
UPDATE table_name
set
column_name={expression|DEFAULT|NULL}
[from table_name]
where searchcondition
例
update authors set au_lname='JOE' where au_id='172-32-1176'

update authors set state='ZZ'
from (select top 10 * from authors order by au_lname) AS t1
where authors.au_id=t1.au_id

DELETE删除表中数据
DELETE [FROM] table_name WHERE search_conditions
删除所有来自CA州的作家
delete from authors where state='CA'

删除表中每一行数据
delete from authors

TRUNCATE TABLE清空表格
清空表格中所有数据，只留下一个表格定义，执行速度比delete快,不进行日志的记录，删除数据后
无法恢复。
TRUNCATE table_name

视图
与直接使用表相比，视图有许多优点
1.对表中需要的数据横向(select语句实现)和纵向(where子句实现)分割,有限制的显示数据，简化
浏览数据的工作
2.简化对表格的存储操作
3.设置访问许可权限，限制用户访问，保护数据，不同用户可以建立不同视图
4.为数据库重构提供一定的逻辑独立性

视图创建
语法
CREATE VIEW view_name
AS
SELECT Statement
[WITH CHECK OPTION]
例
create view author_view
AS
select au_id,au_fname,au_lname
from authors

删除视图
DROP VIEW view_name

修改视图
先建立了一个可以访问所有作家的视图，然后修改定义，只有华盛顿州的作家可以被查询
create view all_authors (au_fname,u_lname,address,city,zip)
AS
select au_fname,au_lname,address,city,zip
from authors

alter view all_authors (au_fname,u_lname,address,city,zip)
AS
select au_fname,au_lname,address,city,zip
from authors
where state ='WA'

视图信息浏览
sp_help author_view '浏览视图中各列列表
sp_depends '返回引用了哪些别的数据库对象
sp_helptext '检索存储在视图，触发器，存储过程中的文本

INSERT,UPDATE与表格操作一致

删除视图中数据
通过视图删除数据最终都体现为从基本表中删除数据

WITH CHECK OPTION
使用WITH CHECK OPTION可以有效地对不合理数据进行筛选，建议使用
WITH ENCRYPTION
隐藏生成视图的代码

create view wa_publishers_2
with encyption
AS
select *
from publishers
where state='WA'
with check option

视图许可权限
不同用户授予不同使用权限
通过使用select字句限制用户对某些底层基本表列的访问
通过使用where字句限制用户对某些底层基本表行的访问

整数数据类型
bit
bigbit
int
smallint
tinyint

货币数据类型
money
smallmoney

数字数据类型
decimal
numeric

浮点数据类型
real
float

日期/时间数据类型
datetime

字符数据类型
char
varchar
text

笛卡尔乘积
问题：出现在对表格连接条件的限制上
select title_id,au_fname
from titles,authors

注意：如有N个表格出现在FROM后面，定义的连接条件不得少于N-1个

JOIN  ON关键字
JOIN用于连接两个不同的表格，ON用于给出这两个表格之间的连接条件
select titles.title_id,au_fname
from titles join titleauthor
on titleauthor.title_id=titles.title_id
join authors
on authors.au_id=titleauthor.au_id
where authors.state="CA"

另一种写法
select titles.title_id,au_fname
from titles,titleauthor,authors
where titleauthor.title_id=titles.title_id
and authors.au_id=titleauthor.au_id and authors.state="CA"

表格别名的使用
目的：简化查询语句的写法
写法1
select titles.title_id,au_fname
from titles t join titleauthor ta
on ta.title_id=t.title_id
join authors a
on a.au_id=ta.au_id
where a.state="CA"
写法2
select titles.title_id,au_fname
from titles as t,titleauthor as ta,authors as a
where ta.title_id=t.title_id
and a.au_id=ta.au_id and a.state="CA"

UNION子句
可以对UNION操作的数据集结果进行排序，把ORDER BY子句放在最后的SELECT子句后面
但排序的依据是第一个SELECT列表中的列
select state
from authors
union
select state
from publishers
order by state

统计函数
sum()
avg()
min()
max()
count()
count(*)
与统计函数一起使用distinct
select avg(qty) "avg_qty",sum(qty) "sum_qty",count(distinct stor_id)
where sales

count(*)进行行数统计
select count(*)
from publishers
where state ="CA"

GROUP BY使用方法
按某一列数值进行分类，在分类的基础上进行查询，需要使用GROUP BY
select type,pub_id,avg(price) "avg_price",sum(price) "sum_price"
from title
where type in ("business","trad_cook","mod_cook")
group by type,pub_id

HAVING关键字筛选结果
select type,avg(price) "avg_price"
from titles
where price>10
order by type
having avg(price)>18

ALL关键字
GROUP BY子句中提供了All关键字，只有当select语句中，同时使用了where子句的情况下才有
意义，如使用了All关键字，所有被GROUP BY子句分类的数据集都将出现在结果集中
select type,pub_id,avg(price) "avg_price",sum(price) "sum_price"
from title
where type in ("business","trad_cook")
group by all type,pub_id
order by type

CUBE关键字
多维立方体，数据列的数据进行交叉组合产生的结果集。
select type,pub_id,avg(price) "avg_price",sum(price) "sum_price"
from title
where type in ("business","trad_cook","mod_cook")
group by type,pub_id
with cube

ROLLUP关键字
类似CUBE关键字，但能够压缩掉无意义的行
select type,pub_id,avg(price) "avg_price",sum(price) "sum_price"
from title
where type in ("business","trad_cook","mod_cook")
group by type,pub_id
with rollup

COMPUTE,COMPUTE BY
select type,price,advance
form. titles
order by type
compute sum(price),sum(advance) by type

嵌套查询中使用EXISTS关键字
select title_id,au_id
from titleauthor
where exists
(select *
from authors
where authors.au_id=titleauthor.au_id
and state="CA"
order by title_id

列清单中使用嵌套查询
select a.au_lname,t.title_id,
"Quantity Sold" = (select sum(qty) from sales where title_id=t.title_id)
from authors a,titles t,titleauthors ta
where a.state="CA"
and a.au_id=ta.au_id
and ta.title_id=t.title_id
order by a.au_lname

约束
列级约束:列级约束是行定义的一部分，只能应用爱一列上
表级约束:表级约束的定义独立于列的定义，可以应用在一个表中的多列上

浏览具体表的约束信息
sp_helpconstraint table_name

PRIMARY KEY约束
利用表中的一列或多列数据来唯一地标识一行数据，为了有效实现数据管理，
每一张表都应该有自己的主键，且只能有一个主键(PRIMARY KEY约束唯一性)

主键操作
在创建表格时，定义主键，主键是表格定义的一部分。
主键添加
修改或删除表上已经定义的主键
--主键已存在，先删除，再建立
--主键被外键引用，可以先删外键，再删主键

列级主键约束
create table test1
(
job_id smallint identity(1,1) primary key clustered,
job_desc varchar(50) not null
default
)
表级主键
create table test2
(
event_type int,
event_time datetime,
event_site char(50),
event_desc char(1024),
constraint even_key primary key (event_type,event_time)
)

查看主键信息
sp_pkey table_name

主键删除
alter table test1
drop constraint pk_test1_440b1d61

主键修改
alter table test1
add constraint pk_id primary key clustered (job_id)

UNIQUE约束
用来确保不受主键约束的列上的数据唯一性

PRIMARY KEY约束与UNIQUE约束区别
UNIQUE约束主要用在非主键的一类或多列上要求数据唯一的情况
UNIQUE约束允许在该列上存在NULL值，主键不允许
一个表上可以设置多个UNIQUE约束，而一个表上只能设置一个主键

UNIQUE约束操作
在创建表格时，定义UNIQUE约束，UNIQUE约束是表格定义的一部分。
UNIQUE约束添加
修改或删除表上已经定义的UNIQUE约束
--UNIQUE约束已存在，先删除，再建立
--UNIQUE约束可以在列级或表级上设置

create table test3
(
event_name char(20),
event_type char(20),
event_time datetime,
event_id int primary key clustered,
constraint uniq_event unique (event_type,event_time)
)

删除UNIQUE方法
添加UNIQUE方法
--同PRIMARY KEY

CHECK约束
检查数据值的合理性来实现数据的完整性
create table sales
(
saleid int identify(100000,1) not for replication,
check not for replication (saleid<=199999),
salesregion char(2),
constraint id_pk primary key (saleid)
)

表上所有约束无效
alter table authors
nocheck constraint all

FOREIGN KEY约束
用来维护两个表之间的一致性关系，外键的建立主要是通过将一个表中的主键所在列包含在另
一个表中，这些列就是另一个表的外键

局部变量
可以在一个DECLARE语句中声明多个变量
DECLARE @LastName NVARCHAR(30),
  @FirstName NVARCHAR(30),
  @State NCHAR(2)
  

局部变量赋值
SELECT语句赋值
SET语句赋值
查询功能和赋值功能不能混合使用

DECLARE @MyCounter INT
SELECT @MyCounter=20

DECLARE @row int
SET @row = (select count(*) from authors)

全局变量
用来记录SQL Server服务器活动状态的一组数据。

IF ELSE条件判断结构
BEGIN  END语句块
可以将一组SQL语句封装成一个完整的SQL语句块

if (select avg(price) from titles where type = "business")< $20

BEGIN
print "这是一些优秀的经济类书籍："
select title
from titles
where type="business"
END

else
print "平均书价超过$20."

WHILE循环结构
在满足条件的情况下，重复执行同样的语句

WHILE NOT EXISTS(SELECT price
      from titles
      WHERE price <$30)
BEGIN
UPDATE titles
SET price = price * 1.5
SELECT MAX(price) FROM titles
IF (SELECT MAX(price) FROM titles) > $50
BREAK
ELSE
CONTINUE

CASE多选择
CASE语句可以更方便地实现多重选择的情况，可以避免编写多重的IF THEN嵌套循环。
select "作者"=
 case
  when "CA" then "来自加利福尼亚"
  when "KS" then "来自堪萨斯州"
  when "MI" then "来自马萨诸塞州"
  when "IN" then "来自印第安纳州"
  when "TN" then "来自田纳西州"
  else "来自其他州"
 end
 au_fname+"."+au_name
 from authors
 order by state
 
 
游标
游标类似C语言的指针一样的语言结构，数据库执行的大多数SQL命令都是同时处理集合内部的所有
数据，有时候用户也需要对这些数据集合中的每一行进行操作。如果没有游标，这种工作不得不放
到数据前端，用高级语言实现。这将导致不必要的数据传输，从而延长执行的时间。通过游标可以在
服务器端有效地解决这个问题

事务
BEGIN TRANSACTION transaction_name
开始一个事务单元
COMMIT TRANSACTION transaction_name
完成一个事务单元
ROLLBACK TRANSACTION transaction_name
回滚一个事务单元
SAVE TRANSACTION savepoint_name
设置保存点

回滚：只有在保存点到回滚语句之间的操作被取消。
BEGIN TRANSACTION exampletrans
USE pubs
GO
UPDATE titleauthor
SET royaltype =35
FROM titleauthor,titles
WHERE royaltyper =25
AND titleauthor.title_id=titles.title_id
AND title= 'The Gourmet Microwave'
GO
SAVE TRANSACTION percentchange
UPDATE titles
SET price = price * 2
WHERE title= 'The Gourmet Microwave'
GO
ROLLBACK TRANSACTION percentchanged
PRINT "程序继续执行"
COMMIT TRANSACTION

存储过程
是一组预先编译好的Transact-SQL代码。
既可以作为数据库的对象，也可以作为一个单元被用户调用

存储过程类似其它编程语言里的过程(procedure)
存储过程可以接收参数，并以输出参数的形式返回多个参数给调用存储过程的过程或批处理
存储过程也可以容纳对数据库进行查询、修改的编程语句，也可以调用其他的存储过程
可以返回执行存储过程的状态值以反映存储过程的执行情况
存储过程不同于函数，存储过程不能在被调用的位置上返回数据，也不能被应用在语句当中
USE pubs
GO
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'au_info' AND type='p')
DROP PROCEDURE au_info
GO
CREATE PROCEDURE author_information
AS
SELECT au_lname,au_fname,title,pub_name
FROM authors a
JOIN titleauthor ta
ON a.au_id=ta.au_id
JOIN titles t
ON t.title_id=ta.title_id
JOIN publishers p
ON t.pub_id=p.pub_id
GO

存储过程管理
查看存储过程文本信息
sp_helptext author_information

修改存储过程的名字
sp_rename author_information,authors_information

存储过程执行
EXECUTE procedure_name

删除存储过程
DROP procedure procedure_name

修改存储过程
同Create

通过存储过程传递参数

USE pubs
GO
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'titles_sum' AND type='p')
DROP PROCEDURE titles_sum
GO

CREATE PROCEDURE titles_sum @TITLE varchar(40)='%',@sum money OUTPUT
AS
SELECT 'Title Name'=title
FROM titles
WHERE title LIKE @TITLE

SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO