sqlServer?? 基礎知識

大綱

備份數據庫：

?

還原數據庫：

USE [master] GO/****** Object: StoredProcedure [dbo].[RestoreDB] Script Date: 01/18/2018 12:13:53 ******/ SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ON GOCREATE proc [dbo].[RestoreDB] @Dbname varchar(200)='', @path varchar(500), @id int as--1 殺死其他進程 DECLARE @SQL VARCHAR(MAX); SET @SQL='' SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID) FROM master..sysprocesses WHERE dbid=DB_ID(@Dbname); EXEC(@SQL);--2 修改為單用戶 exec('ALTER DATABASE '+@Dbname+' SET SINGLE_USER;')create table ##target_db_backups([bak_id] [int] NOT NULL,[bak_address] [nvarchar](300) NOT NULL,[bak_date] [datetime] NOT NULL,[remarks] [nvarchar](300) NOT NULL,[account_id] [int] NOT NULL,[last_restore_date] [datetime] NULL) --3 備份 XXX --4 存檔日志--> exec('insert into ##target_db_backups select * from '+@Dbname+'.dbo.Sys_Backups;')--5 還原---- exec(';restore database '+@Dbname+' from disk = '''+@path+''' WITH REPLACE;') --6 恢復日志 exec('truncate table '+@Dbname+'.dbo.Sys_Backups;') exec('insert into '+@Dbname+'.dbo.Sys_Backups select * from ##target_db_backups;') exec('drop table ##target_db_backups;') --7 更新日志 exec('update '+@Dbname+'.dbo.Sys_Backups set last_restore_date=GETDATE() where bak_id='+@id+';') --8 恢復主鍵表 exec('update '+@Dbname+'.dbo.FW_Squence set svalue=(select max(bak_id) from '+@Dbname+'.dbo.Sys_Backups) where skey=''sys_backups''') --9 切換到多用戶 exec('ALTER DATABASE '+@Dbname+' SET MULTI_USER;')GO

?

------

創建數據庫 1

創建表 2

備份表 3

刪除表 4

修改表 5

查詢出重復的數據 6

增刪改查 7

添加約束 8

分頁存儲過程 9

排序 10

類型轉換 11

表連接 12

事務 13

獲取數據庫信息 14

sql函數 15?

游標 16

?

use Books --------------------------------------------------------------------------------------------------------------------創建數據庫 1 create database BookShop on ( name='BookShop.mdf', filename='E:\Data\BookShop.mdf', size=10mb, maxsize=1024MB, filegrowth =10% ) log on ( name='BookShop_log.ldf', filename='E:\Data\BookShop_log.ldf' ) use bookshop go ------------------------------------------------------------------------------------------------------------------------創建表 2 ----------一一個主鍵 create table Users ( Id int identity(1,1) primary key(Id), UName nvarchar(50) not null, UPwd varchar(50) not null, UDelFlag int not null,) go--------------組合主鍵 create table Users1 ( UName nvarchar(50) not null, UName1 nvarchar(50) not null, primary key(UName,UName1), UPwd varchar(50) not null, UDelFlag int not null,) go ------------------------------------------------------------------------------------------------------------------------備份表 3 --------新表不存在，在復制的時候，自動創建新表 select * into newStudent from student; --------新表存在，在復制之前，表必須建好,注意：創建的新表主鍵必須不是自動增長，否則報錯 insert into newStudent select * from student; --------復制表結構 select top 0,* into newstudnet form student; --效率比下面效率高，優先使用 select * into newstudnet form student where 1<>1;-效率低 ------------------------------------------------------------------------------------------------------------------------刪除表 4--刪除表中的所有數據，表還在，主鍵自增不變 delete from Users; --刪除表，表不存在 drop table Users; --刪除表中所有數據，主鍵自增重置默認值，不觸發delete觸發器，速度快 truncate table Users; ------------------------------------------------------------------------------------------------------------------------修改表 5 -------------------------手動（增刪）一列，及修改數據類型 --增加一列 alter table Users add URegistTime datetime; --刪除一列 alter table Users drop column URegistTme; --修改某列的數據類型 alter table Users alter column URegistTime datetime;--------------------------------------------------------------------------------------------------------------查詢出重復的數據 6 select Name from Users group by Name having count(Name) > 1; --------------------------------------------刪除重復數據，保留一條，某個字段數據重復 --刪除主鍵小的，保留大的 delete from Grade where grade in (select Grade, from Grade group by Grade having count(*)>1) and id not in (select min(Id) from Grade group by Grade having count(Grade)>1) --備份表的方式,刪除重復數據，保留重復數據的一條，這是指的記錄重復，而不是僅僅某個字段重復 select distinct * into Users1 from Users drop table Users ----------------------------------------------------------------------------------------------------------------------增刪改查 7 --插入 insert into Users( UName, UPwd,UDelFlag) values( '李四','lisi',0) ----------一次插入多條數據 insert into Score( Name, Score) select '6',110 union all select '7',120 Union all select'8',130 Union all select '9',140Union all select '10',150 --刪除 delete from Users where Id=2 --修改 update Users set UName='張三' where Id=1 -----------------------------------------------------------查詢 select * from users--簡單查詢 ----------------------------------------縱表轉橫表查詢 select Name ,sum(case Course when '語文' then Score else 0 end) as 語文 ,sum(case Course when '數學' then Score else 0 end) as 數學 ,sum(case Course when '英語' then Score else 0 end) as 英語 from Test group by Name----------------------------------------橫表轉縱表查詢 select Name as 姓名,'語文' as 科目,Chineses as 分數 from Test1 union all select Name as 姓名,'數學' as 科目,Math as 分數 from Test1 union all select Name as 姓名,'英語' as 科目,English as 分數 from Test1 go ---------------------分頁查詢 select top 2 * from Users where Id not in (select top (2 * 3) Id from Users order by Id) order by Id go---------------------------------子查詢--獨立子查詢,切記：子查詢的結果只能是一個值 --一個表 select * from Score where Name=(select Name from Score where Score=80 ) select * from Score where Name in(select Name from Score where Score=80 ) select * from Score where Name not in(select Name from Score where Score=80 ) --兩個表 select * from Score where Name in (select Name from Grade where name='2' or Name='3') select * from Score where Name not in (select Name from Grade where name='2' or Name='3') --相關子查詢 select * from Score as s where exists(select Name from Grade as g where s.Name=g.Name and g.Name='2') select * from Score as s where not exists(select Name from Grade as g where s.Name=g.Name and g.Name='2')--------------------帶條件查詢 --between and 已優化，效率高，優先使用； id>2 and id<4 select * from UserInfo where Id between 2 and 4 --in ;id=1 or id=2 or id=3 select * from UserInfo where Id in(1,2,3) --------------------模糊查詢(主要針對字符串操作) --通配符：_ 、 % 、 [] 、 ^ --like , not like --只能匹配一個任意字符 select * from UserInfo where UName like '張_王'; --匹配單個字符王字的，只有一個字符 select * from UserInfo where UName like '王'; --匹配后面以王字結尾的 select * from UserInfo where UName like '%王'; --匹配前面以王字開頭的 select * from UserInfo where UName like '王%'; --匹配包含王字的 select * from UserInfo where UName like '%王%'; --只能匹配一個字符 ,必須是：a-z,0-9 select * from UserInfo where UName like '[王]'; --不像 select * from UserInfo where UName like '[^張]';----------------------------------------------------------------------------------------------------------------------添加約束 8--主鍵約束（一個主鍵） alter table Users add constraint PK_Users primary key(Id);--主鍵約束(組合主鍵) alter table Users add constraint PK_Users primary key(UName,UName1);--外鍵約束 alter table Users add constraint FK_Users foreign key(UsersInfoId) references UsersInfo(UsersInfoId); --非空約束 alter table Users alter column UPwd varchar(50) not null ; --唯一約束 alter table Users add constraint UQ_Users unique(UName); --默認約束alter table Users add constraint DK_Users default(getdate()) for UTime;--時間默認值alter table Users add constraint DK_Users default(0) for age;--年齡默認值------------------------------------------------------------------------------------------------------------------分頁存儲過程 9create procedure usp_GetPage --當前頁碼 @pageIndex int, --每頁條數 @pageSize int, --總頁碼數 @pageCount int output as begin set @pageCount=(ceiling((select count(*) from Users)*1.0/@pageSize)); select * from (select ROW_NUMBER() over(order by Id asc) as num,* from Users)as u where u.num between @pageSize*(@pageIndex-1)+1 and @pageSize*@pageIndex end declare @count int exec usp_GetPage 11,10,@count output-------------------------------------------------------------------------------------------------------------------------排序 10 --order by 子句位于SELECT語句的末尾，帶where的放在where的后面,默認是asc排序， --可以根據多個列排序,前提是,第一個列都一樣時,則會以第二個列排序 select * from UserInfo order by Age desc --帶where select * from UserInfo where age<20 order by Age desc --沒有出現在GROUP BY子句中的列是不能放到SELECT語句后的列名列表中的 （聚合函數中除外） select UName from UserInfo group by UName --having 相當于where 對分組后，但賽選的列必須是分組的列，才能進行賽選，必須放在 group by 后面 select UName from UserInfo group by UName having UName='張三'---------------------------------------------------------------------------------------------------------------------類型轉換 11 --cast 類型轉換 select cast('張三' as varchar); --轉換成int,然后可以進行運算 select cast(right('5',3) as int); select cast(right('5',3) as int)+1; select cast(right('5',3) as int)-1; select cast(right('5',3) as int)*5; select cast(right('5',3) as int)/5; --convert 將日期轉換成指定格式的字符串 select convert(varchar(50),getdate(),120);-----------------------------------------------------------------------------------------------------------------------表連接 12 --內聯 select Grade,Score.Score from Grade inner join Score on Score.Name=Grade.Name --左外聯 select Grade,Score.Score from Grade left join Score on Grade.Name=Score.Name --右外聯 select Grade,Score.Score from Score right join Grade on Score.Name=Grade.Name-------------------------------------------------------------------------------------------------------------------------事務 13 declare @sumError int=0 --錯誤 --打開事務 begin transaction update score set score=score+1 where id=1 set @sumError=@sumError+@@ERROR update score set score=score-1 where id=10 set @sumError=@sumError+@@ERROR if(@sumError>0) begin --事務回滾 rollback transaction end --事務提交 commit transaction---------------------------------------------------------------------------------------------------------------獲取數據庫信息 14-------------------------------------------------------------通過視圖獲取數據庫信息，表信息，字段信息 --字段類型：xtype=編號，如忘記可以通過 select*from syscolumns或者select * from systypes 查看--查看數據庫中的所有類型對應的信息select * from systypes 34 image 35 text 36 uniqueidentifier 48 tinyint 52 smallint 56 int 58 smalldatetime 59 real 60 money 61 datetime 62 float 98 sql_variant 99 ntext 104 bit 106 decimal 108 numeric 122 smallmoney 127 bigint 165 varbinary 167 varchar 173 binary 175 char 189 timestamp 231 sysname 231 nvarchar 239 nchar ---------------------------------------------查詢服務器--遠程連接數據庫 select * from opendatasource('SQLOLEDB','Data Source=遠程ip;User ID=sa;Password=密碼').庫名.dbo.表名--查詢所有的用戶 islogin='1'表示帳戶 islogin='0'表示角色 status='2'表示用戶帳戶 status='0'表示糸統帳戶select * from sys.sysusers--查詢出所有的數據庫,statusselect * from master..sysdatabases --查詢出所有的表,根據需求篩選;xtyep='u'select *from sysobjects --查詢出去表中所有字段，根據需求篩選：idselect*from syscolumns--指定的表名中的所有字段select * from syscolumns where id = object_id('grade') --主鍵select * from syscolumns where id = object_id('grade') and colstat = 1-------------------------------------------------通過存儲過程獲取數據庫信息，表信息，字段信息 --獲取所有數據庫名 exec sp_databases --獲取所有的表名,當前選中的表名 exec sp_tables --獲取表中所有字段名 exec sp_columns books-----------------------------------------------------------------------------------------------------------------------------------------------------------------------sql函數 15 -----------------------------------------------------聚合函數 --總數 select count(Age) as 總數 from UserInfo --和 select 和= sum(Age) from UserInfo --最大 select max(Age)as 最大值 from UserInfo --最小 select min(Age) as 最小值 from UserInfo --平均值 select avg(Age) as 平均值 from UserInfo-------------------------------------------------------日期函數 --當前時間 select getdate(); --指定部分 select datepart(month,getdate()); --返回指定部分的===上面的 year(),month(),day(),hour(),minute(),second(); --為指定的時間進行加值，減值操作 select dateadd(MONTH,3,getdate()); --時間差 select datediff(MONTH,getdate(),getdate());----------------------------------------------------字符串函數 --計算字符串字符個數 select len('張立平'); --計算字符串所占字節數，一個漢字兩個字節 select datalength('張三王五');--不是字符串函數 --轉大寫 select upper('abc'); --轉小寫 select lower('ABC'); --去掉左邊的空格 select ltrim(' abcaba'); --去掉右邊的空格 select rtrim('abcaba '); --截取左邊的一個字符串 select left('abc',1) --截取右邊的一個字符串 select right('abc',1); --截取字符串的從指定位置開始，截取指定的字符個數，而不是字節個數 select substring('張三李四王無為',1,3);-------------------------------------------------------------------------------------------------------------------------游標(指針) 16 declare test_cursor3 cursor global--定義for select TJJLID,Id from PersonalInfo--查詢 open test_cursor3--打開游標 declare @tjjlid int--定義變量 declare @Id int--定義變量 while @@fetch_status=0 begin fetch next from test_cursor3 into @tjjlid,@Id--下一行 if not exists(select HBSAG from TJJL where Id=@tjjlid)--是否存在 UPDATE PersonalInfo SET HBSAG=3 where Id=@Id--修改 else UPDATE PersonalInfo SET HBSAG=(select HBSAG from TJJL where Id=@tjjlid) where Id=@Id--修改 end close test_cursor3--關閉游標 deallocate test_cursor3--釋放游標 View Code

?

?跨數據庫備份表：

錯誤：消息 15281，級別 16，狀態 1，第 1 行
SQL Server 阻止了對組件“Ad Hoc Distributed Queries”的 STATEMENT“OpenRowset/OpenDatasource”的訪問，因為此組件已作為此服務器安全配置的一部分而被關閉。系統管理員可以通過使用 sp_configure 啟用“Ad Hoc Distributed Queries”。有關啟用“Ad Hoc Distributed Queries”的詳細信息，請搜索 SQL Server 聯機叢書中的“Ad Hoc Distributed Queries”。

?

前兩行意思是：開啟權限：

后兩句意思是：關閉權限；

exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'Ad Hoc Distributed Queries',1 RECONFIGURESELECT * into cbeosm.dbo.FW_Squence from openrowset ('SQLOLEDB' , '192.168.1.240' ; 'sa' ; '123' ,cbeosm.dbo.FW_Squence)SELECT * FROM cbeosm.dbo.FW_Squenceexec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure exec sp_configure 'show advanced options',0 reconfigure

?

?

?

?

?

?

整理未完成，那里有不正確的，或者有更好的方法，請評論，內容僅供初學者參考。謝謝！

?

轉載于:https://www.cnblogs.com/zlp520/p/3552129.html

總結

以上是生活随笔為你收集整理的sqlServer基础知识的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。