1 /*--------------------------------------SQL语法复习知识-----------------------------------------------*/
2 use master
3 go
4 --检查是否存在,存在删除
5 if exists (select * from sysdatabases where name='ReviewDatabase')
6 drop database databaseName
7 go
8 --创建数据库
9 create database ReviewDatabase--数据库名为ReviewDatabase(对象资源管理器显示的名称)
10 on
11 (
12 name='ReviewDatabase',--文件在电脑上显示的文件名（物理名）
13 filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase.mdf',--数据文件保存的位置注意盘符下的文件夹必须事先存在
14 size=5,--数据库初始大小
15 maxsize=100,--数据库最大多少
16 filegrowth=10%--自动增长百分之多少
17 )
18 log on--创建对应的日志文件
19 (
20 name='ReviewDatabase_log',
21 filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase_log.ldf',
22 size=4,
23 maxsize=50,
24 filegrowth=10%
25 )
26 /*-----------------------------------------------------------------------------------------------------*/
27 --创建表
28 use ReviewDatabase
29 go
30 --检查是否存在,存在删除
31 if exists(select * from sys.tables where name='t1')
32 drop table t1
33 go
34 --创建表
35 create table t1--不要在主表里创建外键
36 (
37 id1 int identity(1,1) not null,--从1自增1起(identity(1,1),not null可省,默认为null)
38 id2 int constraint fk_t1 primary key,--设置为主键(名字可省)
39 id3 int not null constraint id4_unique unique,--唯一
40 id4 uniqueidentifier not null constraint t1_newid default(newid()),--newid()随机函数,设置默认值
41 tel int not null constraint ch_t1_tel check(tel like '7809[0-9][0-9][1-9]'),
42 name varchar(10) not null,
43 )
44 if exists(select * from sys.tables where name='t2')
45 drop table t2
46 create table t2
47 (
48 id1 int,
49 id2 int not null constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade,--主外键级联更新,删除
50 constraint pk_t2 primary key(id1),
51 )
52 /*-----------------------------------------------------------------------------------------------------*/
53 --插入数据到表
54 insert t1(id2,id3,tel,name) values(1,1,7809001,'a')
55 insert t1(id2,id3,tel,name) values(2,2,7809002,'b')
56 insert t1(id2,id3,tel,name) values(3,3,7809003,'c')
57 insert t1(id2,id3,tel,name) values(4,4,7809004,'d')
58 insert t1(id2,id3,tel,name) values(5,5,7809005,'e')
59 insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
60 insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
61 insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
62 insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
63
64 insert t2(id1,id2) values(1,1)
65 insert t2(id1,id2) values(2,2)
66 insert t2(id1,id2) values(3,3)
67 insert t2(id1,id2) values(4,4)
68 insert t2(id1,id2) values(5,5)
69 insert t2(id1,id2) values(6,6)
70 insert t2(id1,id2) values(7,7)
71 insert t2(id1,id2) values(8,8)
72 insert t2(id1,id2) values(9,9)
73
74 insert into t1(id2,id3,tel,name) values(10,10,7809011,'j')
75 insert into t2(id1,id2) values(10,10)
76
77 select * into #temp from t2--将t2复制到临时表
78 select * from #temp--本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的,当用户从 SQL Server 实例断开连接时被删除
79 insert into t3 select * from #temp--把临时表中的数据插入到另一个表中
80
81 select * from t1,t2 where t1.id2=t2.id2
82 update t2 set id2=10 where id1=1--出错,外键冲突
83 update t1 set id2=10 where id1=1--正确,t2.id2也会更改
84 /*-----------------------------------------------------------------------------------------------------*/
85 alter table t2 add id3 int null--添加一列
86
87 alter table t2 add constraint pk_t2_id3 primary key(id3)--t2添加一个主键
88 alter table t2 drop pk_t2_id3--t2主键删除
89
90 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2)--添加一个外键
91 alter table t2 drop fk_t1_t2--删除外键
92
93 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade--级联更新
94 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on delete cascade--级联更新
95 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade--级联更新和删除
96
97 alter table t2 add constraint un_t2_id3 unique(id3)--添加唯一列,非聚集
98 alter table t2 drop un_t2_id3--删除唯一列,非聚集
99
100 alter table t2 add constraint un_t2_id3 unique clustered(id3)--添加唯一聚集
101 alter table t2 drop constraint un_t2_id3
102
103 alter table t1 add constraint de_t1 default '默认值' for name--添加默认值
104 alter table t1 drop constraint de_t1
105
106 alter table t2 with nocheck add constraint ch_t2 check (id3 like '[1-9]')--添加check
107 alter table t2 drop constraint de_t2
108 alter table t2 nocheck constraint ch_t2--设置check无效
109 alter table t2 check constraint ch_t2--设置check重新有效
110 /*-----------------------------------------------------------------------------------------------------*/
111 --索引是不可更改的,想更改必须删除重新建
112 create unique clustered index in_t2 on t2(id1)
113 drop index in_t2 on t2
114 /*-----------------------------------------------------------------------------------------------------*/
115 --常用查询
116 exec sp_help t1--查看约束
117 select * from t1 order by id1 asc --order by "asc 升,desc 降"
118 select distinct * from t1 --消除重复行
119 select * from t2 where id1='1' --选择where"=,>,<.>=,<=,<>"
120 insert into t2(id1,id2) values('7','7') --插入insert(t2(内可省,但values要一一对应))
121 delete from t2 where id1='6' --删除delete
122 update t1 set name='liuyi' where id1='1' --更新update
123 --'%'零个和多个任意的字符,'_'任意一个字符,'[]'指定的范围作保单个字符,'[^]不在指定的范围作保单个字符
124 select * from t2 where id2 not like '%666%' --查找"字符串比较like,not like"
125 select * from t2 where id1='1' or id1='2' --查找"逻辑运算符 and,or"
126 select * from t2 where not id1='1' --查找"逻辑运算符 not"
127 select * from t2 where id1 between '1' and '6' --查找"值的范围 between,not betweent"
128 select * from t2 where id1 in('1','3','6') --查找"值的列表in ,not in"
129 select * from t2 where id1 is not null --查找"未知值is null ,not is null"
130
131 select t1.id1,t1.name from t1 where exists --指定一个子查询，检测行的存在
132 (select t1.id2 from t1,t2 where t1.id2=t2.id2 and t2.id1 between 6 and 9)
133 /*-----------------------------------------------------------------------------------------------------*/
134 --聚合函数
135 select count(*) as '总数' from t1 --总数
136 select sum(id1) as '求和' from t1 --求和
137 select avg(id1) as '平均' from t1 --平均
138 select max(id1) as '最大' from t1 --最大
139 select min(id1) as '最小' from t1 --最小
140 select id1,name,COUNT(*) from t1 group by id1,name --group by
141 select id1,name,COUNT(*) from t1 group by id1,name having id1>2 --group by ,having
142 select id1,name,sum(id1) from t1 group by id1,name with rollup --group by ,with rollup
143 select id1,name,sum(id1) from t1 group by id1,name with cube --group by ,with cube
144 --select id,name,sum(id) grouping(id) from s1
145 select id1 name from t1 order by id1 compute sum(id1) --compute
146 select id1,name from t1 order by id1 compute sum(id1) by id --compute by
147 /*-----------------------------------------------------------------------------------------------------*/
148 --联接
149 select * from t1 inner join t2 on t1.id2=t2.id2--内联接1
150 select * from t1,t2 where t1.id2=t2.id2--内联接2
151 select * from t1 left join t2 on t1.id2=t2.id2--左外联接
152 select * from t1 right join t2 on t1.id2=t2.id2--右外联接
153 select * from t1 full join t2 on t1.id2=t2.id2--全外联接
154 select * from t1 cross join t2--交叉联接1
155 select * from t1,t2--交叉联接2
156 --合并多个结果集
157 select t1.id1,t1.id2 from t1
158 union
159 select t2.id1,t2.id2 from t2
160 /*-----------------------------------------------------------------------------------------------------*/
161 --事务
162 begin tran
163 begin tran
164 begin tran
165 print @@trancount--计算事务的个数 对于嵌套的transaction来讲，每次begin transaction都让它加一，每次commit tran都会让它减一
166 rollback tran--rollback是很特殊的，它会直接把@@TRANCOUNT设置为0
167 print @@trancount
168 begin tran
169 print @@trancount
170 --嵌套
171 delete t1 where id1>5--删除数据,
172 begin tran out1
173 insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
174 insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
175 begin tran inn1
176 insert t2(id1,id2) values(6,6)
177 insert t2(id1,id2) values(7,7)
178 save tran inn1SavePoint
179 insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
180 insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
181 insert t2(id1,id2) values(8,8)
182 insert t2(id1,id2) values(9,9)
183 rollback tran inn1SavePoint--8-9之间的回滚了,没有插入
184 commit tran inn1
185 commit tran out1--如果没有提交事务会出现死锁
186
187 select * from t1,t2 where t1.id2=t2.id2
188 /*-----------------------------------------------------------------------------------------------------*/
2 use master
3 go
4 --检查是否存在,存在删除
5 if exists (select * from sysdatabases where name='ReviewDatabase')
6 drop database databaseName
7 go
8 --创建数据库
9 create database ReviewDatabase--数据库名为ReviewDatabase(对象资源管理器显示的名称)
10 on
11 (
12 name='ReviewDatabase',--文件在电脑上显示的文件名（物理名）
13 filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase.mdf',--数据文件保存的位置注意盘符下的文件夹必须事先存在
14 size=5,--数据库初始大小
15 maxsize=100,--数据库最大多少
16 filegrowth=10%--自动增长百分之多少
17 )
18 log on--创建对应的日志文件
19 (
20 name='ReviewDatabase_log',
21 filename='F:\各类小功能\我的源码\SQL Server\database\ReviewDatabase_log.ldf',
22 size=4,
23 maxsize=50,
24 filegrowth=10%
25 )
26 /*-----------------------------------------------------------------------------------------------------*/
27 --创建表
28 use ReviewDatabase
29 go
30 --检查是否存在,存在删除
31 if exists(select * from sys.tables where name='t1')
32 drop table t1
33 go
34 --创建表
35 create table t1--不要在主表里创建外键
36 (
37 id1 int identity(1,1) not null,--从1自增1起(identity(1,1),not null可省,默认为null)
38 id2 int constraint fk_t1 primary key,--设置为主键(名字可省)
39 id3 int not null constraint id4_unique unique,--唯一
40 id4 uniqueidentifier not null constraint t1_newid default(newid()),--newid()随机函数,设置默认值
41 tel int not null constraint ch_t1_tel check(tel like '7809[0-9][0-9][1-9]'),
42 name varchar(10) not null,
43 )
44 if exists(select * from sys.tables where name='t2')
45 drop table t2
46 create table t2
47 (
48 id1 int,
49 id2 int not null constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade,--主外键级联更新,删除
50 constraint pk_t2 primary key(id1),
51 )
52 /*-----------------------------------------------------------------------------------------------------*/
53 --插入数据到表
54 insert t1(id2,id3,tel,name) values(1,1,7809001,'a')
55 insert t1(id2,id3,tel,name) values(2,2,7809002,'b')
56 insert t1(id2,id3,tel,name) values(3,3,7809003,'c')
57 insert t1(id2,id3,tel,name) values(4,4,7809004,'d')
58 insert t1(id2,id3,tel,name) values(5,5,7809005,'e')
59 insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
60 insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
61 insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
62 insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
63
64 insert t2(id1,id2) values(1,1)
65 insert t2(id1,id2) values(2,2)
66 insert t2(id1,id2) values(3,3)
67 insert t2(id1,id2) values(4,4)
68 insert t2(id1,id2) values(5,5)
69 insert t2(id1,id2) values(6,6)
70 insert t2(id1,id2) values(7,7)
71 insert t2(id1,id2) values(8,8)
72 insert t2(id1,id2) values(9,9)
73
74 insert into t1(id2,id3,tel,name) values(10,10,7809011,'j')
75 insert into t2(id1,id2) values(10,10)
76
77 select * into #temp from t2--将t2复制到临时表
78 select * from #temp--本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的,当用户从 SQL Server 实例断开连接时被删除
79 insert into t3 select * from #temp--把临时表中的数据插入到另一个表中
80
81 select * from t1,t2 where t1.id2=t2.id2
82 update t2 set id2=10 where id1=1--出错,外键冲突
83 update t1 set id2=10 where id1=1--正确,t2.id2也会更改
84 /*-----------------------------------------------------------------------------------------------------*/
85 alter table t2 add id3 int null--添加一列
86
87 alter table t2 add constraint pk_t2_id3 primary key(id3)--t2添加一个主键
88 alter table t2 drop pk_t2_id3--t2主键删除
89
90 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2)--添加一个外键
91 alter table t2 drop fk_t1_t2--删除外键
92
93 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade--级联更新
94 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on delete cascade--级联更新
95 alter table t2 add constraint fk_t1_t2 foreign key(id2) references t1(id2) on update cascade on delete cascade--级联更新和删除
96
97 alter table t2 add constraint un_t2_id3 unique(id3)--添加唯一列,非聚集
98 alter table t2 drop un_t2_id3--删除唯一列,非聚集
99
100 alter table t2 add constraint un_t2_id3 unique clustered(id3)--添加唯一聚集
101 alter table t2 drop constraint un_t2_id3
102
103 alter table t1 add constraint de_t1 default '默认值' for name--添加默认值
104 alter table t1 drop constraint de_t1
105
106 alter table t2 with nocheck add constraint ch_t2 check (id3 like '[1-9]')--添加check
107 alter table t2 drop constraint de_t2
108 alter table t2 nocheck constraint ch_t2--设置check无效
109 alter table t2 check constraint ch_t2--设置check重新有效
110 /*-----------------------------------------------------------------------------------------------------*/
111 --索引是不可更改的,想更改必须删除重新建
112 create unique clustered index in_t2 on t2(id1)
113 drop index in_t2 on t2
114 /*-----------------------------------------------------------------------------------------------------*/
115 --常用查询
116 exec sp_help t1--查看约束
117 select * from t1 order by id1 asc --order by "asc 升,desc 降"
118 select distinct * from t1 --消除重复行
119 select * from t2 where id1='1' --选择where"=,>,<.>=,<=,<>"
120 insert into t2(id1,id2) values('7','7') --插入insert(t2(内可省,但values要一一对应))
121 delete from t2 where id1='6' --删除delete
122 update t1 set name='liuyi' where id1='1' --更新update
123 --'%'零个和多个任意的字符,'_'任意一个字符,'[]'指定的范围作保单个字符,'[^]不在指定的范围作保单个字符
124 select * from t2 where id2 not like '%666%' --查找"字符串比较like,not like"
125 select * from t2 where id1='1' or id1='2' --查找"逻辑运算符 and,or"
126 select * from t2 where not id1='1' --查找"逻辑运算符 not"
127 select * from t2 where id1 between '1' and '6' --查找"值的范围 between,not betweent"
128 select * from t2 where id1 in('1','3','6') --查找"值的列表in ,not in"
129 select * from t2 where id1 is not null --查找"未知值is null ,not is null"
130
131 select t1.id1,t1.name from t1 where exists --指定一个子查询，检测行的存在
132 (select t1.id2 from t1,t2 where t1.id2=t2.id2 and t2.id1 between 6 and 9)
133 /*-----------------------------------------------------------------------------------------------------*/
134 --聚合函数
135 select count(*) as '总数' from t1 --总数
136 select sum(id1) as '求和' from t1 --求和
137 select avg(id1) as '平均' from t1 --平均
138 select max(id1) as '最大' from t1 --最大
139 select min(id1) as '最小' from t1 --最小
140 select id1,name,COUNT(*) from t1 group by id1,name --group by
141 select id1,name,COUNT(*) from t1 group by id1,name having id1>2 --group by ,having
142 select id1,name,sum(id1) from t1 group by id1,name with rollup --group by ,with rollup
143 select id1,name,sum(id1) from t1 group by id1,name with cube --group by ,with cube
144 --select id,name,sum(id) grouping(id) from s1
145 select id1 name from t1 order by id1 compute sum(id1) --compute
146 select id1,name from t1 order by id1 compute sum(id1) by id --compute by
147 /*-----------------------------------------------------------------------------------------------------*/
148 --联接
149 select * from t1 inner join t2 on t1.id2=t2.id2--内联接1
150 select * from t1,t2 where t1.id2=t2.id2--内联接2
151 select * from t1 left join t2 on t1.id2=t2.id2--左外联接
152 select * from t1 right join t2 on t1.id2=t2.id2--右外联接
153 select * from t1 full join t2 on t1.id2=t2.id2--全外联接
154 select * from t1 cross join t2--交叉联接1
155 select * from t1,t2--交叉联接2
156 --合并多个结果集
157 select t1.id1,t1.id2 from t1
158 union
159 select t2.id1,t2.id2 from t2
160 /*-----------------------------------------------------------------------------------------------------*/
161 --事务
162 begin tran
163 begin tran
164 begin tran
165 print @@trancount--计算事务的个数 对于嵌套的transaction来讲，每次begin transaction都让它加一，每次commit tran都会让它减一
166 rollback tran--rollback是很特殊的，它会直接把@@TRANCOUNT设置为0
167 print @@trancount
168 begin tran
169 print @@trancount
170 --嵌套
171 delete t1 where id1>5--删除数据,
172 begin tran out1
173 insert t1(id2,id3,tel,name) values(6,6,7809006,'f')
174 insert t1(id2,id3,tel,name) values(7,7,7809007,'g')
175 begin tran inn1
176 insert t2(id1,id2) values(6,6)
177 insert t2(id1,id2) values(7,7)
178 save tran inn1SavePoint
179 insert t1(id2,id3,tel,name) values(8,8,7809008,'h')
180 insert t1(id2,id3,tel,name) values(9,9,7809009,'i')
181 insert t2(id1,id2) values(8,8)
182 insert t2(id1,id2) values(9,9)
183 rollback tran inn1SavePoint--8-9之间的回滚了,没有插入
184 commit tran inn1
185 commit tran out1--如果没有提交事务会出现死锁
186
187 select * from t1,t2 where t1.id2=t2.id2
188 /*-----------------------------------------------------------------------------------------------------*/