SqlServer基础汇总

阅读目录

一、sql语句的执行顺序
二、常用的基础知识
6.常用的函数

回到顶部

一、sql语句的执行顺序

　　查询时数据库中使用最多的操作，一条sql语句的查询顺序是

　　　　1、from Tb1 [ join on ] 得到查询的数据源

　　　　2、where 　　　　　　对数据过滤（单条数据上过滤）

　　　　3、group by 对数据分组

　　　　4、having　　　　　　筛选分组（在组别上进行过滤）

　　　　5、select distinct 　　　获取结果集

　　　　6、order by　　　　　　对结果集排序

回到顶部

二、常用的基础知识

1.创建表和查看表结构

　　一个创建公司/员工表的栗子

--00 为了方便DBA循环调用，先删除外键，否则不能删除表Companies
alter table Employees drop constraint [FK_Employees_Companies_CmpId]

--01 创建公司Companies表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Companies]',N'U') )
    DROP TABLE [dbo].Companies
GO
CREATE TABLE [dbo].Companies (
    CmpId      int   IDENTITY (1, 1) NOT NULL,
    CmpName    nvarchar(50)   NOT NULL DEFAULT 0,
    CONSTRAINT [PK_Cmp] PRIMARY KEY CLUSTERED (CmpId desc)
);

exec sp_addextendedproperty N'MS_Description', N'公司编号PK ', N'user', N'dbo', N'table',N'Companies', N'column', N'CmpId'
GO
exec sp_addextendedproperty N'MS_Description', N'公司名称 ', N'user', N'dbo', N'table',N'Companies', N'column', N'CmpName'
GO

--02 创建员工Employees表
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = object_id(N'[dbo].[Companies]',N'U'))
    DROP TABLE [dbo].Employees
GO
CREATE TABLE [dbo].Employees (
    EmpId      int   IDENTITY (1, 1) NOT NULL,
    EmpName    nvarchar(50)   NOT NULL ,
    EmpAge     int NOT NULL,
    CreateTime datetime default GETDATE() not null,
    EmpAddress nvarchar(500),
    CmpId int Not NULL
    CONSTRAINT [PK_Emp] PRIMARY KEY CLUSTERED (EmpId desc),
    CONSTRAINT [FK_Employees_Companies_CmpId] FOREIGN KEY (CmpId) REFERENCES [dbo].[Companies] ([CmpId]) --ON DELETE CASCADE 级联删除
);

exec sp_addextendedproperty N'MS_Description', N'员工编号PK ', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpId'
GO
exec sp_addextendedproperty N'MS_Description', N'员工姓名 ', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpName'
GO
exec sp_addextendedproperty N'MS_Description', N'员工年龄 ', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpAge'
GO
exec sp_addextendedproperty N'MS_Description', N'创建时间 ', N'user', N'dbo', N'table',N'Employees', N'column', N'CreateTime'
GO
exec sp_addextendedproperty N'MS_Description', N'员工住址 ', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpAddress'
GO
exec sp_addextendedproperty N'MS_Description', N'公司编号FK ', N'user', N'dbo', N'table',N'Employees', N'column', N'CmpId'
GO

　　查看表结构

select 
    表名       = O.name,
    字段名     = C.name,
    字段说明   = isnull(p.[value],''),
    标识       = case when COLUMNPROPERTY(C.object_id,C.name,'IsIdentity')=1 then '√'else '' end,
    类型       = t.name,
    占用字节数 = C.max_length,
    字符数       = COLUMNPROPERTY(C.object_id,C.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(C.object_id,C.name,'Scale'),0),
    允许空     = case when C.is_nullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    索引名     =idx.name,
    唯一       = case when idx.is_unique=1 then '√' else '' end,
    主键       = case when idx.is_primary_key=1 then '√' else '' end,
    索引描述   =idx.type_desc
from sys.columns C 
    left join  sys.index_columns idxc on  idxc.column_id=C.column_id and idxc.object_id=c.object_id
    left join sys.indexes idx on idxc.object_id=c.object_id and idx.object_id=idxc.object_id  and idx.object_id=c.object_id and idxc.index_id=idx.index_id
　　join sys.types t on C.user_type_id=t.user_type_id 
　　inner join sys.objects o on C.object_id=O.object_id  and o.is_ms_shipped=0 and o.[type]='U'
　　left join syscomments e on C.default_object_id=e.id 
　　left join sys.extended_properties   p on C.object_id=p.major_id and C.column_id=p.minor_id  
where O.name='employees'    --替换表名
order by 
    C.name

2.索引管理　

　查看表索引（含主键索引）

--查询索引
select [object_id] =o.object_id,
       TableName=o.name,
       ColumnName=c.name,
       IndexName=idx.name,
       IndexTypeDesc=idx.type_desc,
       IndexIsUnique=idx.is_unique,
       IndexIsPrimary=idx.is_primary_key
from sys.indexes idx
join  sys.index_columns idxc
on idx.object_id=idxc.object_id and idx.index_id=idxc.index_id
join sys.columns c
on idxc.object_id=c.object_id and idxc.column_id=c.column_id
join sys.objects o
on o.object_id=c.object_id and o.is_ms_shipped=0 and o.[type]='U'
where o.object_id=object_id(N'Employees', N'U')--单个表添加这个条件
order by o.name  ,c.name

　　添加索引

----添加索引，给员工表添加CreateTime索引，这样写是因为不知道原来索引的名字
if exists(SELECT top 1 * FROM sys.indexes where object_id=object_id(N'Employees', N'U'))
begin 

　　declare @DropIdxScript nvarchar(255),@IdxName nvarchar(100);
　　--获取Employees.CreateTime上的索引名
　　select top 1 @IdxName= IDX.[name] FROM sys.indexes IDX
　　　　INNER JOIN sys.index_columns IDXC
　　　　ON IDX.[object_id]=IDXC.[object_id]
　　　　　　AND IDX.index_id=IDXC.index_id
　　　　INNER JOIN sys.columns C
　　　　ON IDX.[object_id]=C.[object_id]
　　　　　　AND IDXC.Column_id=C.Column_id
　　　　INNER JOIN sys.objects O
　　　　ON O.[object_id]=IDX.[object_id]
　　　　　　AND O.type='U'
　　　　　　AND O.is_ms_shipped=0
　　where O.object_id=object_id(N'Employees', N'U') and C.[name]='CreateTime'

　　set @DropIdxScript='drop index '+ @IdxName +' On Employees'
　　print @DropIdxScript
　　exec(@DropIdxScript)
　　--drop index IDX_Employees_CreateTime on Employees;
end 
create index IDX_Employees_CreateTime on Employees(CreateTime)

3.列的管理

　　对字段进行修改和删除的时候要首先删除和字段相关的约束，否则无法修改/删除成功

----1.添加字段并加上索引（直接drop的话会造成数据丢失）
if not exists (select top 1 1 from dbo.syscolumns WHERE id = object_id(N'Employees',N'U') and name= N'EmpAddress')
begin
alter table [dbo].Employees ADD EmpAddress VARCHAR(50)
end
go
if not exists (select top 1 1 from sys.extended_properties ep inner join sys.columns c on ep.major_id=c.object_id and ep.minor_id=c.column_id where c.object_id=object_id(N'Employees',N'U') and c.name=N'EmpAddress')
exec sp_addextendedproperty N'MS_Description', N'员工住址', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpAddress'
go
--设置索引
if exists(SELECT top 1 1 FROM sys.indexes where object_id=object_id(N'Employees', N'U') and [name]='IDX_Employees_EmpAddress')
begin 
drop index IDX_Employees_EmpAddress on Employees;
end 
create index IDX_Employees_EmpAddress on Employees(EmpAddress)

----2.添加一个带约束的字段
if not exists (select top 1 1 from dbo.syscolumns WHERE id = object_id(N'Employees',N'U') and name= N'EmpAddress')
begin
alter table Employees add EmpAddress VARCHAR(50) not null constraint DF_Employees_EmpAddress DEFAULT (N'默认地址')
end

if not exists (select top 1 1 from sys.default_constraints where object_id = object_id(N'DF_Employees_EmpAddress') and parent_object_id = object_id(N'Employees',N'U'))
begin
alter table Employees add constraint DF_Employees_EmpAddress default(N'默认地址') for EmpAddress
end

if not exists (select top 1 1 from sys.extended_properties ep inner join sys.columns c on ep.major_id=c.object_id and ep.minor_id=c.column_id where c.object_id=object_id(N'Employees',N'U') and c.name=N'EmpAddress')
begin
exec sp_addextendedproperty N'MS_Description', N'员工住址', N'user', N'dbo', N'table',N'Employees', N'column', N'EmpAddress3'
end

----3.修改字段长度 ，先删除约束再修改
ALTER TABLE Student DROP CONSTRAINT DF__CITIES__TESTCOL; 
ALTER TABLE Student ALTER COLUMN TESTCOL VARCHAR(100) NOT NULL; 
ALTER TABLE Student ADD DEFAULT ('HELLOWORLD1') FOR TESTCOL WITH VALUES 

----4.删除字段 ，先删除约束再修改
ALTER TABLE Student DROP CONSTRAINT DF__CITIES__TESTCOL；
ALTER TABLE Student DROP COLUMN TESTCOL;

----5. 添加一个计算列，入职年份=当前年份-创建年份
IF EXISTS (SELECT * FROM dbo.syscolumns WHERE ID = OBJECT_ID(N'Employees') AND NAME = 'HireYear')
BEGIN
ALTER TABLE Employees DROP COLUMN HireYear
END
ALTER TABLE Employees ADD HireYear AS (Year(GETDATE())-YEAR(CreateTime))
GO

4.约束管理

--主键约束 员工EmpID设置主键
select * from sys.objects where parent_object_id=object_id(N'Employees', N'U') and is_ms_shipped=0 and type='PK'
alter table dbo.Employees  add constraint PK_EmpID PRIMARY KEY (EmpID)

--唯一约束 员工名字唯一
select * from sys.objects where parent_object_id=object_id(N'Employees', N'U') and  and is_ms_shipped=0 and type='UQ')
alter table Employees add constraint UQ_EmpName UNIQUE (EmpName)

--默认约束（员工默认地址为‘地址不详’，删除以前的约束，添加信息默认值）
declare @DFName nvarchar(200)
select top 1 @DFName=dc.name from sys.default_constraints dc 
inner join sys.columns c on dc.parent_object_id=c.object_id and dc.parent_column_id=c.column_id 
where c.object_id=object_id(N'Employees',N'U') and c.name=N'EmpAddress' and dc.is_ms_shipped=0 and dc.type='D'
if @DFName is not null and len(@DFName)>0
begin 
exec ('alter table Employees drop constraint '+ @DFName)
end
alter table Employees add constraint DF_EmpAddress default ('地址不详') for EmpAddress--检查约束（员工年龄在18~60岁之间）

--check约束
select * from sys.objects where parent_object_id=object_id(N'Employees', N'U') and is_ms_shipped=0 and type='C')
alter table Employees add constraint CK_EmpAge CHECK(EmpAge>=18 AND EmpAge<=60)

----查看和修改约束
exec sp_helpconstraint 'Student' --查看Student表的约束
alter table Student drop constraint FK_Student_Grade_GradID--删除约束

5.控制语句和TryCatch

----------IF ELSE栗子
declare @score int; SET @score=44;
if (@score>=90)     begin print '优秀'; end
else if(@score>=60) begin print '及格'; end
else                begin print '不及格'; end
----output:不及格

----------WHILE栗子
declare @i int; declare @sum int;
set @i=1; set @sum=0; 
while(@i<=100) 
begin
    SET @sum=@sum+@i ;SET @i+=1;
end
print @sum 
----output:5050


----------CASE栗子
declare @sex int; SET @sex=1; 
-----写法1
select
CASE
    WHEN @sex=0 THEN 'female'
    WHEN @sex=1 THEN 'male'
    ELSE 'unknown'
END 
-----写法2
select
CASE @sex
    WHEN 0 THEN 'female'
    WHEN 1 THEN 'male'
    ELSE 'unknown' 
END
----output:male

-----------TryCatch栗子
declare @result int
begin try
    set @result=5/0
end try
begin catch
    set @result=0
    PRINT ERROR_MESSAGE() 
end catch
print @result
----output:遇到以零作除数错误。 0

View Code

一个小案例，当平均成绩小于60的时候进行加分来降低不及格率，90分以上的不加分，80分以上的加1分，其他的加3分

declare @avgscore int;
select @avgscore=avg(score) from stuScore
while(@avgscore<60)
begin 
 update stuScore set score+=
   case 
     when score>=90 then 0
     when score>=80 then 1
     else 3
   end
 select @avgscore=avg(score) from stuScore;
end

6.常用的全局变量

select @@ERROR as 最后一个sql错误的错误码,如果没有错误返回0,
select @@IDENTITY as 最后一次插入的记录的IDselect @@MAX_CONNECTIONS as 可用的最大连接数
select @@ROWCOUNT as 最后一句sql语句的影响行数

回到顶部

6.常用的函数

	函数	说明	sample	output
string	lower() upper()	转小写转大写	select(lower('abcDEF')) select(upper('abcDEF'))	abcdef ABCDEF
	charindex() patindex()	特定字符出现的索引，patindex()支持匹配符	select charindex('el','hello,world!') select patindex('%e_%','hello,world!')	2 2
	replace stuff()	replace全局替换,stuff精确替换	select replace('hello,tom!hello,jerry!','hello','hi') select stuff('hello,tom!hello,jerry!',11,5,'hi')--替换第11个字符的后5位（第二个hello）	hi,tom!hi,jerry! hello,tom!hi,jerry!
	concat()	拼接多个字符串	select(concat('aa','123','bb'))	aa123bb
	substring()	指定位置截取指定长度	select(substring('abcdef',2,3))	bcd
	left() right()	从左边截取n个字符从右边截取n个字符	select(left('abcdef',2)) select(right('abcdef',2))	ab ef
	len() datalength()	获取字符数获取字节数	select(len(N'abcdef')) datalength(N'abcdef')	6 12
	reverse()	反转	select(reverse('abcdef'))	fedcba
datetime	declare @datex datetime ; select @datex='2008-6-12 07:30:45'
	year,month,day	获取年月日	select year(@datex)	2008
	datepart()	获取时间组成	select datepart(weekday,@datex) select datepart(weekday, @datex)--这里按默认@@datefirst=7	2008 5(周四)
	datediff()	获取时间差	select datediff(year,'1949-10-1',@datex)	59
	dateadd()	时间滑动	select dateadd(day,1,'2008-6-12 07:30:45:111')--一天后	2008-06-13 07:30:45.110
	eomonth()	获取所在月的最后一天	select eomonth('2008-6-12 07:30:45:111')	2008-06-30
		时间戳、时间互转	select datediff(second,'1970-01-01 00:00:00',getutcdate())--获取时间戳 select dateadd(second,1591872696,'1970-1-1 08:00:00')--时间戳转为北京时间
math	ceiling()/floor()	向上/下函数	select ceiling(3.1415) select floor(3.1415)	4 3
	round()	四舍五入	select round(3.1415,2) --保留2位小数 select ceiling(round(3.1415,0)) --四舍五入取整	3.1400 3
	rand()	获取0-1的随机数	select rand() select floor(rand()100) --0-99的随机整数 select ceiling(rand()100) --1-100的随机整数	0.256880502233242 85 32
数据/格式转换	cast()/convert()/parse()	类型转换 ,parse()从字符串解析为其他类型,语法和cast一样； try_cast(),try_convert(),try_parse()在转换不成功的时候, 不抛异常而是返回null	select cast('123' as int) select cast('123.167' as int) select cast('123.167' as decimal(10,4)) select cast('123.167' as decimal(10,2))--自动四舍五入 select convert(int ,'123')	123 报错 123.1670 123.17 123
	str()	数值类型转字符串，第二个参数为字符串总长度（包含小数点），位数不够的时候在整数部分前补空格；第三个参数是小数位数，可自动四舍五入或补零	select str('123.167',8,2) select str('123.167',10,4)	' 123.17' ' 123.1670'
	format()	从其他数据格式转换为字符串，格式化利器	select format(getdate(),'yyyy-MM-dd HH:mm:ss') select format(getdate(),'yyyyMMdd') select format(getdate(),'HH:mm:ss') select format(123.167,'0.0000') select format(123.167,'0.00') --自动四舍五入	2020-06-12 16:40:13 20200612 16:40:13 123.1670 123.17
其他	isdate() isnumeric()	可解析为datetime/number类型返回1，否则返回0	select isdate('2008-6-12 07:30:45') select isnumeric('123.167')	1 1
	isnull(exp,default)	exp为null返回default	select isnull(null,'defaultVal')	defaultVal
	iif(exp,val1,val2)	三元运算	iif(1=1,'good','bad')	good

补充内容：

1.datepart种类

datapart有year/quarter/month/dayofyear/day/week/weekday/hour/minute/second/millisecond等，datediff(weekday/dayofyear,@startdate,@enddate)相当于datediff(day,@startdate,@enddate);

2.@@datefrist和weekday

weekday表示一周的第几天，和语言相关，select @@lauguage。

中文环境下默认@@datefirst=7，即一周的第一天是星期日。我们可以修改@@datefirst把一周的第一天改成周一，即@@datafirst=1

select @@datefirst as  默认datefirst --7
select datepart(weekday ,'2008-6-12 07:30:45:111') --5
set datefirst 1
select @@datefirst as  修改后的datefrist --1
select datepart(weekday ,'2008-6-12 07:30:45:111') --4

其他内容补充：

exec sp_executesql用法：

    declare @CName nvarchar(50)=N'zhangsan'
    declare @Password nvarchar(50)
    exec sp_executesql N'select top 1 @Password=Password from UserInfo where CName=@CName and Status=@Status'
                      ,N'@CName nvarchar(50),@Status char(2),@Password nvarchar(50) output'
                      ,@CName,@Status='01',@Password=@Password out
    print @password

posted @ 2018-05-16 17:36 捞月亮的猴子阅读(1008) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【译】Visual Studio 中新的强大生产力特性
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
· 字符编码：从基础到乱码解决

公告

昵称：捞月亮的猴子
园龄： 8年7个月
粉丝： 529
关注： 14

+加关注

2025年3月

日

一

二

三

四

五

六

捞月亮的猴子

五岳寻仙不辞远，一生好入名山游

SqlServer基础汇总

一、sql语句的执行顺序

二、常用的基础知识

1.创建表和查看表结构

2.索引管理

3.列的管理

4.约束管理

5.控制语句和TryCatch

6.常用的全局变量

6.常用的函数

公告

积分与排名

随笔分类

阅读排行榜

推荐排行榜

最新评论