SqlServer基础汇总

一、sql语句的执行顺序

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

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

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

　　　　3、group by 对数据分组

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

　　　　5、select distinct 　　　获取结果集

　　　　6、order by　　　　　　对结果集排序

二、常用的基础知识

1.创建表和查看表结构

--exec sp_help cities  可以用来查看一张表的详细信息
--创建省份表
CREATE TABLE [dbo].[Provices] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [Name]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_Provices] PRIMARY KEY CLUSTERED ([Id] ASC)
);


--创建城市表
CREATE TABLE [dbo].[Cities] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [ProviceId] INT            NOT NULL DEFAULT 0,
    [Name]      NVARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Cities_Provices_ProviceId] FOREIGN KEY ([ProviceId]) REFERENCES [dbo].[Provices] ([Id]) ON DELETE CASCADE, 
);

　　查看表结构（原文地址）

SELECT 
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = case when a.isnullable=1 then '√'else '' end,
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM syscolumns a
　　left join systypes b on a.xusertype=b.xusertype
　　inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
　　left join syscomments e on a.cdefault=e.id
　　left join sys.extended_properties   g on a.id=G.major_id and a.colid=g.minor_id  
　　left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0
where d.name='LogInfo'    --如果只查询指定表,加上此条件
order by 
    a.id,a.colorder

2.列的管理

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

----1.添加字段 
ALTER TABLE Student ADD TESTCOL NVARCHAR(20) DEFAULT 'HELLOWORLD' NOT NULL; 


----2.修改字段长度 
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 


----3.删除字段 
ALTER TABLE Student DROP CONSTRAINT DF__CITIES__TESTCOL；
ALTER TABLE Student DROP COLUMN TESTCOL;

3.约束管理


--主键约束
ALTER TABLE Student 
ADD CONSTRAINT PK_StuNo PRIMARY KEY (StudentNo)

--唯一约束
ALTER TABLE Student 
ADD CONSTRAINT UQ_stuID UNIQUE (IdentityCard)

--默认约束（地址不详）
ALTER TABLE Student 
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR Address

--检查约束（出生日期是自1980年1月1日以后）
ALTER TABLE Student 
ADD CONSTRAINT CK_stuBornDate CHECK(BornDate>='1980-1-1')

 --添加外键约束
ALTER TABLE Student
ADD CONSTRAINT FK_Student_Grade_GradID
FOREIGN KEY(GradeID) REFERENCES Grade(GradeID)
go


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

4.控制语句和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

5.常用的全局变量

select @@ERROR as 最后一个sql错误的错误码,如果没有错误返回0,
select @@IDENTITY as 最后一次插入的记录的IDselect @@MAX_CONNECTIONS as 可用的最大连接数
select @@ROWCOUNT as 最后一句sql语句的影响行数
select @@SERVERNAME as 本地服务器名称
select @@TRANCOUNT as 当前打开的事务数
select @@VERSION as sqlserver版本

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()	获取长度	select(len('abcdef'))	6
	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