SqlServer基础汇总
一、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
一个小案例，当平均成绩小于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!') |
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) |
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) |
' 123.17' |
|
| 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
