常用的一些SQL语句

1、case条件语句

--查询语句的原理是  一条一条查询
--1.case...end 会得到一列值
--2.当case后面接有表达式或者字段的时候，它是做等值判断
--3.then和when后面的数据类型需要一致(可以互换)
--4.做等值判断的时候不能判断null值
select StudentName,Sex,
case
 when ClassId is null then '班级不知道'
 when BornDate>'2000-1-1' then '小孩子'
 when BornDate>'1990-1-1' then '少年'
 when BornDate>'1980-1-1' then '中年' 
 when Sex='女' then '小姑娘' 
 else '老年人' 
end
 from Student

 2、if else条件语句

select QuestionDescription,
case 
when BadCodeID<4 then QuestionType
else '没有数据类型'
end
from MES_Base_BadCode

3、在select中使用case

--根据条件查询数据的数量，然后用10除以数量，当数量为0时，默认被除数是1，不然就就是10除以数量
select 10/(case when count(*) =0 then 1 else COUNT(*) end) AS qty
from MES_Base_Factory where FactoryCode='PDD'

4、在存储过程中，使用while一次存储大量的数据

create proc MES_T_InsertTestResultData
@OrderID int, 
@LineRefID int, 
@StationRefID int, 
@TestResult int, 
@CreatedByUserID int,
@i int,
@count int
as
while(@i<@count)
begin
insert into MES_T_TestResult(OrderID, LineRefID, StationRefID, TestResult, CreatedByUserID)
values(@OrderID, @LineRefID, @StationRefID, @TestResult, @CreatedByUserID)
set @i+=1
end

5、查询过程中，相除，然后保留两位小数，最后求和

SELECT   ROUND( SUM(CAST(DATEDIFF(SECOND,  CreatedOnDate,  LastModifiedOnDate) as float)/3600),2)                   
FROM         MES_E_ProdNormalTime
WHERE     (OrderID = 171) and DeleteFlag=0

6、嵌套查询语句

--获取借出员工的姓名
--嵌套语句,如果子语句是一个结果,那么WHERE条件后面可以使用=号,如果是一个集合,那么就要用IN
SELECT     UserID, NameCN, LoginName
FROM         USER_T_User WHERE UserID IN
(SELECT UserID 
FROM  MES_E_EmployeerMobilize
WHERE  (CONVERT(nvarchar(10), CreatedOnDate, 120) = '2015-08-21')
AND (DeleteFlag = 0) AND JobDescriptionID=3 AND BorrowOutOrIn=0)

7、把日期格式化为'yyyy-MM-dd'比较

 CONVERT(nvarchar(10),ReadCardDateTime,120)='2015-09-19' 

8、根据某个查询条件把查询的数据全部一次更新

--根据条件更新数据
UPDATE   MES_T_TestResultTemp set DeleteFlag=1 where ID in (
SELECT TOP(10) ID from MES_T_TestResultTemp
WHERE     (LineRefID = 16) AND (OrderID = 13) AND (StationRefID = 607) AND DeleteFlag=0
AND TestResult=1  order by ID desc)

9、类型转换

--把int转变为float型，然后相除，最后保留两位小数点
--求转拉工时
--第一种转法：
SELECT   ROUND(CAST(SUM(TurnPullTime)AS float)/3600,2) AS Expr1
FROM         MES_E_EmployeerTurnPull
WHERE     (TurnPullStatus = 1) AND (DeleteFlag = 0) AND (OrderID = 968)
--第二种转法：
SELECT    ROUND(convert(float,SUM(TurnPullTime))/3600,2) AS Expr1
FROM         MES_E_EmployeerTurnPull
WHERE     (TurnPullStatus = 1) AND (DeleteFlag = 0) AND (OrderID = 98)

10、union使用

--union: 可以合并多个结果集
--1.列的类型一致
--2.列的数量需要对应
--3如果需要排序，只能将排序写在最后一句，同时是在union得到结果集之后，所以只能排序第一条select语句的字段
select cast(Result.StudentNo as CHAR(3)) 学号,Result.StudentResult from Result where SubjectId=3
union --默认去除重复值 Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL
select cast(Result.StudentNo as CHAR(3)) 学号,Result.StudentResult from Result where SubjectId=3

select cast(Result.StudentNo as CHAR(3)) 学号,Result.StudentResult from Result where SubjectId=3
union all--不去除重复值
select cast(Result.StudentNo as CHAR(3)) 学号,Result.StudentResult from Result where SubjectId=3

11、日期的使用

select  GETDATE() --获取当前服务器日期
--DATEADD
select DATEADD(MM,1,GETDATE())
select DATEADD(YY,-1,GETDATE())
--DATEDIFF
select DATEDIFF(YY,'1990-1-1',GETDATE()) --用后面的日期值减前面的日期值
--显示学员的年龄
select StudentNo,StudentName,DATEDIFF(yyyy,borndate,getdate()) from student
select round(DATEDIFF(DD,'1990-12-31','1991-7-5')/365.0,0)

--DATENAME 获取日期部分的字符串表现形式
select DATENAME(dw,getdate())
--DATEPART 获取指定的日期部分
--2013-12-27
select DATEPART(YYYY,GETDATE())
select DATEPART(mm,GETDATE())
select DATEPART(dd,GETDATE())

select cast(DATEPART(YYYY,GETDATE()) as CHAR(4))+'-'+cast(DATEPART(mm,GETDATE()) as CHAR(2))+'-'+cast(DATEPART(dd,GETDATE()) as CHAR(2))

----查询年龄超过20周岁的6期班的学生信息。
select * from Student where ClassId=6 and DATEDIFF(yyyy,borndate,getdate())>20
----查询1月份过生日的学生信息
select * from Student where DATEPART(mm,borndate)=1
----查询今天过生日的学生姓名及所在班级
select StudentName,ClassId from Student where DATEPART(MM,borndate)=DATEPART(mm,getdate()) and DATEPART(dd,borndate)=DATEPART(dd,getdate())
----查询学号为“10”的学生Email的域名。
select substring(Email,CHARINDEX('@',email)+1,LEN(Email)) from Student where StudentNo=10
----新生入学，为其分配一个Email地址，规则如下：GZ+当前日期+4位随机数+@itcast.com
select 'GZ'+CAST(DATEPART(yyyy,getdate()) as CHAR(4))+CAST(DATEPART(mm,getdate()) as CHAR(2))+CAST(DATEPART(dd,getdate()) as CHAR(2))+cast(RIGHT(RAND(),4) as CHAR(4))+'@itcast.com'

12、表的创建约束

--使用代码创建约束：
--语法： alter table 表名  add constraint 约束名称(PK UQ FK CK DF) 约束类型(primary key     unique   default   check  foreign key)  约束说明(字段  表达式  值)
--为grade表创建主键
if exists (select * from sysobjects where name='PK_GradeId')
   alter table  Grade drop constraint PK_GradeId
alter table Grade
add constraint PK_GradeId primary key (GradeId)
--为年级名称添加唯一约束
alter table Grade
add Constraint UQ_GradeName unique(GradeName)
--为地址添加默认约束
alter table Student
add constraint DF_Address default('我在广州') for address, --也可以一次添加多条约束，每一条使用 ，  分隔
constraint CK_LoginPwd check(len(loginPwd)>=6) --从第二条约束开始不需要再使用add关键字了

--为表的年级字段添加键约束
if exists (select * from sysobjects where name='FK_Student_Grade_GradeId')
   alter table  Student drop constraint FK_Student_Grade_GradeId
alter table student --选择外键表来创建主外键
add constraint FK_Student_Grade_GradeId foreign key(GId) references Grade(GradeId)
on delete cascade

--增加外键约束时，设置级联更新、级联删除：
--[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
--[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
--ON DELETE：在删除的时候    
--ON UPDATE:在修改的时候
--NO ACTION：不做任何操作，该报错报错，该操作操作
--CASCADE：级联，删除主表数据，从表对应的记录也删除
--SET NULL：将从表的记录的对应字段值设置为null值，前提是这个字段可以允许为null
--SET DEFAULT:将从表的记录的对应字段值设置为默认值，前提是你设置了默认值，且默认值也符合主外键约束

13、查询表中某个字段同样的数据存在的数量，无列名使用having

SELECT    SdCard_Number ,count(SdCard_Number) AS kk
FROM     SR120_Raspberry_Battery_HighLevel_SdCard_Link
group by     SdCard_Number
having  count(SdCard_Number)>1

14、创建分页查询存储过程

create proc MES_E_proc
@pageIndex int,
@pageSize int,
@pageTotal int out
AS
begin
 SELECT * FROM 
 (SELECT ROW_NUMBER() OVER (ORDER BY CreateDate DESC)AS RowNum,
 ProcessCode,ProcessName,Active,Remark,CreateDate FROM MES_Route_Process WHERE IsDelete=0)AS PageData
 WHERE RowNum BETWEEN @pageSize*(@pageIndex-1)+1 AND @pageIndex*@pageSize
 
SELECT @pageTotal=COUNT(*) FROM MES_Route_Process WHERE IsDelete=0
end

15、查询函数

select CONVERT(decimal(10,2),'123.456789123')
select CONVERT(varchar(10),getdate(),103)
select ROUND(cast(2 as float)/3,2)

16、开窗函数

--开窗函数over
--排序
SELECT * FROM MyTestPass

select * ,RANK() over(order by id desc) from MyTestPass

select Name,sum(Salary) from MyTestPass group by Name

select Name,sum(Salary) over(partition by Name) from MyTestPass

select *,row_number() over(order by oprice desc) from TblOrders

select *, row_number() over(order by id desc) from MyTestPass

 17、自连接

insert into CompanyTable values('北京总部',0)
insert into CompanyTable values('深圳分公司',1)
insert into CompanyTable values('广州分公司',1)
insert into CompanyTable values('.Net部门',2)
insert into CompanyTable values('.Net部门',3)
insert into CompanyTable values('Java部门',2)
insert into CompanyTable values('Java部门',3)
insert into CompanyTable values('C++部门',2)
insert into CompanyTable values('C++部门',3)

--使用内连接把相关的数据连接起来
--第一级分类为：总部，第二级分类为：分公司，第三级分类为：部门
--思路1：查询部门名称
select CompanyName from CompanyTable as department
--思路2：查询公司名称
select CompanyName from CompanyTable as company

--自连接思路:把部门ID=主键ID
select * from CompanyTable as department
inner join CompanyTable as company
on department.CompanyID=company.ID

select company.CompanyName,department.CompanyName
from CompanyTable as department
inner join CompanyTable as company
on department.CompanyID=company.ID
order by company.CompanyName

 18、根据字段名查询数据库的哪些表包含这个字段

 SELECT table_name FROM DBA_TAB_COLUMNS WHERE COLUMN_NAME='ALARM_ID';