---基础知识
if exists(select * from sysdatabases where name='Exam') ---判断数据库中是否存在该数据库
drop database Exam
go
Create database Exam --创建数据库
on primary
(
name='Exam_data',
filename='D:Exam_data.mdf',
size=10mb,
maxsize=20mb,
filegrowth=5%
)
log on
(
name='Exam_log',
filename='D:Exam_log.ldf',
size=10mb,
maxsize=20mb,
filegrowth=5%
)
go
drop database Exam
go
--exec sp_addumpdevice 'disk','mytest','D:mssql8backupMybeifen.dat'
--BACKUP DATABASE Exam TO mytest
use Exam
create table book( ----创建表
Id int primary key identity(1,1),
Bname nvarchar(50) not null
)
go
select * into bookNew from book ---创建一个跟book结构一样，数据一样的表
go
select * into bookNew1 from book where 1=2 ---创建一个跟book结构一样，数据不复制
go
alter table book add remark nvarchar(max) ---给表book添加新的列remark
go
alter table book drop column remark ---删除表book中的remark列
go
alter table book ---修改表book列remark的数据类型
alter column remark nvarchar(50)
go
alter table booknew add primary key (Id) --添加主键，把Id列作为主键列
go
--删除表主键
exec sp_helpconstraint booknew
go
alter table booknew
DROP CONSTRAINT PK__bookNew__3214EC070519C6AF
go
create unique NONCLUSTERED index idxname on booknew(Id) ---创建非聚集唯一索引
go
drop index booknew.idxname --删除索引
go
exec sp_rename 'book.remark','remak','column/object/或者不写，因为默认为object' --重命名book表中的列Remark的名字,第一个参数要加book.rmark，那是因为Exam里面不止只有一个表，也有可能其他表中会有相同的列，所以，必须得指定
go
exec sp_rename 'MyExam','book','object' ---重命名表book为MyExam,第三个参数可以不写，会默认为对象类型
go
exec sp_help book ---显示表的一些基本情况
go
create view bookview ---创建视图
as
select * from book
go
select * from bookview ---查询视图，就像查询表一样
drop view bookview ---删除视图
go
选择：select * from table1 where 范围
插入：insert into table1(field1,field2) values(value1,value2) ---自定义增长Id不需要插入数据
删除：delete from table1 where 范围
更新：update table1 set field1=value1 where 范围
查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!
排序：select * from table1 order by field1,field2 [desc]
总数：select count as totalcount from table1
求和：select sum(field1) as sumvalue from table1
平均：select avg(field1) as avgvalue from table1
最大：select max(field1) as maxvalue from table1
最小：select min(field1) as minvalue from table1
go
---说明：几个高级查询运算词
A： UNION 运算符
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。
B： EXCEPT 运算符
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
C： INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。
---说明：使用外连接
A、left （outer） join：
左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。
---分组:Group by:
一张表，一旦分组 完成后，查询后只能得到组相关的信息。
组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)
在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据
在selecte统计函数中的字段，不能和普通的字段放在一起；
----提升
--复制表
方法一：
select * into book1 from book where 1<>1 ---1不等于1，说明只复制表结构，不复制表数据
方法二：
select top 0 * into book3 from bookNew ---复制booknew到book3表中， top 0表示不复制数据到表中,top 1复制一行数据到表中，以此类推
--拷贝表
insert into test.dbo.book2 select * from Exam.dbo.booknew2 ---把数据库Exam中的booknew2中的数据拷贝到数据库test的book2中，两张表的结构必须一样
select * into test.dbo.book3 from Exam.dbo.booknew2 ---数据库test中的book3是新建的，在之前不能存在
insert into test.dbo.book3 select distinct * from Exam.dbo.booknew2 ---不复制表Exam.dbo.booknew2中的重复记录
--在线视图查询
select * from (SELECT a,b,c FROM a) T where t.a > 1;
--说明：日程安排提前五分钟提醒
--datediff的第一个参数interval 参数可以有以下值：
设置 描述
yyyy 年
q 季度
m 月
y 一年的日数
d 日
w 一周的日数
ww 周
h 小时
n 分钟
s 秒
select * from 日程安排 where datediff(n,开始时间,getdate())>5
---分页
方法一：如果要排序的话在子查询中排序和筛选条件
select top pageNum(页面显示条数) * from xy_role where id not in
(select top (当前页-1)*PageNum id from xy_role)
方法二：row_number() 2005版本以上的数据库才有此函数
select top 5 * from --top 5 ==> top Pagesize
(select *,row_number() over (order by id desc) as ro from book) as a ---order by id desc注意若是升序效果就是相反的
where a.ro>10 order by id desc ---a.ro>10 ==> a.ro > PageIndex*PageSize 结果看出是第三页的值10/5=2 ,页数从0开始
--说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息
--(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select BName,sellcout,category from book ta
where sellcout=(select max(sellcout) from book tb where tb.category=ta.category)
---说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
---说明：随机取出10条数据
select top 10 * from book order by newid()
--说明：随机选择记录
select newid() ---例如7333FBAD-426C-43F5-8853-A6667ACC5F01
--列出数据库里所有的表名
select name from sysobjects where type='U' --- U代表用户
---列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),
sum(case vender when 'C' then pcs else 0 end),
sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
---语法
-- CASE
-- WHEN 条件1 THEN 结果1
-- WHEN 条件2 THEN 结果2
-- WHEN 条件3 THEN 结果3
-- WHEN 条件4 THEN 结果4
-- .........
-- WHEN 条件N THEN 结果N
-- ELSE 结果X
--END
---根据销售数量，统计每个影片类型的的销售数量
select SUM(sellcout) as 销售数量, CASE category
WHEN '1' THEN '战争片'
WHEN '2' THEN '恐怖片'
WHEN '3' THEN '喜剧片'
ELSE '其他' END as 类型
FROM book
GROUP BY CASE category
WHEN '1' THEN '战争片'
WHEN '2' THEN '恐怖片'
WHEN '3' THEN '喜剧片'
ELSE '其他' END
-----技巧
---记录搜索:
开头到N条记录
Select Top N * From 表
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表 order by ID) Order by ID Desc
例子：---选择从5到10的记录 select top 5 * from (select top 10 * from book order by id) b order by id desc
Select Top M-N * From (Select Top M * From 表 order by id) a order by id desc
例子：---选择从5到10的记录 select top 5 * from book where id in(select top 10 id from book order by Id) order by id desc
N到结尾记录
Select Top N * From 表 Order by ID Desc