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(