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

----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----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 ----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

一个小案例，当平均成绩小于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错误的错误码
select @@IDENTITY as 最后一次插入的记录的ID
select @@LANGUAGE as 语言
select @@MAX_CONNECTIONS as 可用的最大连接数
select @@ROWCOUNT as 最后一句sql语句的影响行数
select @@SERVERNAME as 本地服务器名称
select @@TRANCOUNT as 当前打开的事务数
select @@VERSION as sqlserver版本