SQL 语句复习

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