sqlServer 基础知识

sqlServer 基础知识

大纲

创建数据库 1

创建表 2

备份表 3

删除表 4

修改表 5

查询出重复的数据 6

增删改查 7

添加约束 8

分页存储过程 9

排序 10

类型转换 11

表连接 12

事务 13

获取数据库信息 14

sql函数 15

  1 use Books  2 --------------------------------------------------------------------------------------------------------------------创建数据库 1  3 create database BookShop  4 on  5 (  6 name='BookShop.mdf',  7 filename='E:/Data/BookShop.mdf',  8 size=10mb,  9 maxsize=1024MB, 10 filegrowth =10% 11 ) 12 log on 13 ( 14 name='BookShop_log.ldf', 15 filename='E:/Data/BookShop_log.ldf' 16 ) 17 use bookshop 18 go 19 ------------------------------------------------------------------------------------------------------------------------创建表 2 20 ----------一一个主键 21 create table Users  22 ( 23 Id int identity(1,1) PRimary key(Id), 24 UName nvarchar(50) not null, 25 UPwd varchar(50) not null, 26 UDelFlag int not null, 27  28 ) 29 go 30  31 --------------组合主键 32 create table Users1  33 ( 34 UName nvarchar(50) not null, 35 UName1 nvarchar(50) not null, 36 primary key(UName,UName1), 37 UPwd varchar(50) not null, 38 UDelFlag int not null, 39  40 ) 41 go 42 ------------------------------------------------------------------------------------------------------------------------备份表 3 43 --------新表不存在，在复制的时候，自动创建新表 44 select * into newStudent from student; 45 --------新表存在，在复制之前，表必须建好 46 insert into newStudent select * from student; 47 --------复制表结构 48 select top 0,* into newstudnet  form student; --效率比下面效率高，优先使用 49 select * into newstudnet  form student where 1<>1;-效率低 50 ------------------------------------------------------------------------------------------------------------------------删除表 4 51  52 --删除表中的所有数据，表还在，主键自增不变 53 delete from Users; 54 --删除表，表不存在 55 drop table Users; 56 --删除表中所有数据，主键自增重置默认值，不触发delete触发器，速度快 57 truncate table Users; 58 ------------------------------------------------------------------------------------------------------------------------修改表 5 59 -------------------------手动（增删）一列，及修改数据类型  60 --增加一列 61 alter table Users add  URegistTime datetime; 62 --删除一列 63 alter table Users drop column URegistTme; 64 --修改某列的数据类型 65 alter table Users alter column URegistTime datetime; 66  67 --------------------------------------------------------------------------------------------------------------查询出重复的数据 6 68 select Name from Users group by Name having count(Name) > 1; 69 --------------------------------------------删除重复数据，保留一条，某个字段数据重复 70 --删除主键小的，保留大的 71 delete from Grade  72 where grade in 73 (select Grade, from Grade group by Grade having count(*)>1) and id 74 not in (select min(Id) from Grade group by Grade having count(Grade)>1) 75 --备份表的方式,删除重复数据，保留重复数据的一条，这是指的记录重复，而不是仅仅某个字段重复 76 select distinct * into Users1 from Users 77 drop table Users 78 ----------------------------------------------------------------------------------------------------------------------增删改查 7 79 --插入 80 insert into Users( UName, UPwd,UDelFlag) values( '李四','lisi',0) 81 ----------一次插入多条数据 82 insert into Score( Name, Score) 83 select '6',110 union all 84 select '7',120 Union all 85 select'8',130 Union all 86 select '9',140Union all  87 select '10',150 88 --删除 89 delete from  Users where Id=2 90 --修改 91 update Users set UName='张三' where Id=1 92 -----------------------------------------------------------查询 93 select * from users--简单查询 94 ----------------------------------------纵表转横表查询 95 select Name 96 ,sum(case Course when '语文' then Score else 0 end) as 语文 97 ,sum(case Course when '数学' then Score else 0 end) as 数学 98 ,sum(case Course when '英语' then Score else 0 end) as 英语  99 from Test group by Name100 101 ----------------------------------------横表转纵表查询102 select Name as 姓名,'语文' as 科目,Chineses as 分数 from Test1 union all103 select Name as 姓名,'数学' as 科目,Math as 分数 from Test1 union all 104 select Name as 姓名,'英语' as 科目,English as 分数 from Test1105 go106 ---------------------分页查询107 select top 2 * from Users  where Id not in (select top (2 * 3) Id from Users order by Id) order by Id 108 109 go110 111 ---------------------------------子查询112 113 --独立子查询,切记：子查询的结果只能是一个值114 --一个表115 select * from Score where Name=(select Name from Score where Score=80 )116 select * from Score where Name in(select Name from Score where Score=80 )117 select * from Score where Name not in(select Name from Score where Score=80 )118 --两个表119 select * from Score where Name in (select Name from Grade where name='2' or Name='3')120 select * from Score where Name not in (select Name from Grade where name='2' or Name='3')121 --相关子查询122 select * from Score as s   where  exists(select Name from Grade as g where s.Name=g.Name and  g.Name='2')123 select * from Score as s   where not  exists(select Name from Grade as g where s.Name=g.Name and  g.Name='2')124 125 --------------------带条件查询126 --between and   已优化，效率高，优先使用； id>2 and id<4127 select * from UserInfo where Id between 2 and 4128 --in ;id=1 or id=2 or id=3129 select * from UserInfo where Id in(1,2,3)130 --------------------模糊查询(主要针对字符串操作)131 --通配符：_    、   %   、  []   、  ^132 --like  , not like133 --只能匹配一个任意字符134 select * from UserInfo where UName like '张_王';135 --匹配单个字符王字的，只有一个字符136 select * from UserInfo where UName like '王';137 --匹配后面以王字结尾的138 select * from UserInfo where UName like '%王';139 --匹配前面以王字开头的140 select * from UserInfo where UName like '王%';141 --匹配包含王字的142 select * from UserInfo where UName like '%王%';143 --只能匹配一个字符 ,必须是：a-z,0-9144 select * from UserInfo where UName like '[王]';145 --不像146 select * from UserInfo where UName like  '[^张]';147 148 149 ----------------------------------------------------------------------------------------------------------------------添加约束 8150 151 --主键约束（一个主键）152 alter table Users add  constraint PK_Users primary key(Id);153 154 --主键约束(组合主键)155 alter table Users add  constraint PK_Users primary key(UName,UName1);156 157 --外键约束158 alter table Users add  constraint FK_Users foreign key(UsersInfoId) references UsersInfo(UsersInfoId);159 --非空约束160 alter table Users  alter column UPwd varchar(50) not null ;161 --唯一约束162 alter table Users add  constraint UQ_Users unique(UName);163 --默认约束164  alter table Users add  constraint DK_Users default(getdate()) for UTime;--时间默认值165  alter table Users add  constraint DK_Users default(0) for age;--年龄默认值166 167 ------------------------------------------------------------------------------------------------------------------分页存储过程 9168  create procedure usp_GetPage169 --当前页码170 @pageIndex int,171 --每页条数172 @pageSize int,173 --总页码数174 @pageCount int output175 as176 begin177 set @pageCount=(ceiling((select count(*) from Users)*1.0/@pageSize));178 select * from179 (select ROW_NUMBER() over(order by Id asc) as num,* from Users)as u180 where u.num181 between182 @pageSize*(@pageIndex-1)+1183 and184 @pageSize*@pageIndex185 end186 declare @count int 187 exec usp_GetPage 11,10,@count output188 189 -------------------------------------------------------------------------------------------------------------------------排序 10190 --order by 子句位于SELECT语句的末尾，带where的放在where的后面,默认是asc排序，  191 --可以根据多个列排序,前提是,第一个列都一样时,则会以第二个列排序192 select * from UserInfo  order by Age desc193 --带where194 select * from UserInfo where age<20  order by Age desc195 --没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）196 select UName from UserInfo group by UName197 --having 相当于where 对分组后，但赛选的列必须是分组的列，才能进行赛选，必须放在 group by  后面198 select UName from UserInfo group by UName having UName='张三'199 200 ---------------------------------------------------------------------------------------------------------------------类型转换 11201 --cast 类型转换202 select cast('张三' as varchar);203 --转换成int,然后可以进行运算204 select cast(