sqlServer基础知识

sqlServer   基础知识

大纲

备份数据库：

还原数据库：

USE [master]
GO

/****** Object:  StoredProcedure [dbo].[RestoreDB]    Script Date: 01/18/2018 12:13:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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:DataBookShop.mdf',
size=10mb,
maxsize=1024MB,
filegrowth =10%
)
log on
(
name='BookShop_log.ldf',
filename='E:DataBookShop_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;--年龄默认值

------------------------------------------------------------------------------------------------------------------分页存储过程 9
 create 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

 --查询出所有的数据库,status
  select * from master..sysdatabases 
  --查询出所有的表,根据需求筛选;xtyep='u'
  select *from sysobjects 
  --查询出去表中所有字段，根据需求筛选：id
  select*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--释放游标

 跨数据库备份表：

错误：消息 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 RECONFIGURE

SELECT * into  cbeosm.dbo.FW_Squence from openrowset ('SQLOLEDB' , '192.168.1.240' ; 'sa' ; '123' ,cbeosm.dbo.FW_Squence)

SELECT * FROM cbeosm.dbo.FW_Squence


exec sp_configure 'Ad Hoc Distributed Queries',0 reconfigure
exec sp_configure 'show advanced options',0 reconfigure

整理未完成，那里有不正确的，或者有更好的方法，请评论，内容仅供初学者参考。谢谢！