数据库的一些知识点

select * from 表名
select getdate()--2020-02-22 09:04:16.237
select datediff(day,'20200201','20200210')--两个日期相差的天数
select day('20200220')--返回日20
select year('1999-02-02')--返回年1999
select "month number" = MONTH(GETDATE())--返回月份 2
select convert(varchar(10),getdate(),20)--2020-02-22
select convert(varchar ,getdate(),108)--09:10:57
select cast(字段 as bigint),* from 表名--cast(字段 as bigint)
select cast(26.5 as decimal) --27
select cast(26.5 as decimal(3,1)) --26.5
select "去掉左空格"= ltrim('      123')
select * from 表名 where 字段 like '%[3-6]'--查询出以3至6结尾的数据
select * from 表名 where 字段 like '%B'
SELECT top 1 * FROM 表名 order by newid();
SELECT top 1 * FROM 表名 order by newid();--随机挑选出一行数据
--not exists()
select (select count(*) from 表名 a where a.iid >= b.iid) ,* from 表名 b order by 1--按iid的倒叙排序
select * from (select ROW_NUMBER()OVER(ORDER BY IID)as 按时,* FROM 表名)a where 按时%2=1--按iid排序取出奇数行
SELECT iid,ISNULL(备注,123) FROM dbo.表名--将null转化为123
SELECT iid,NULLIF(备注,'2019-11-13 10:00') FROM dbo.表名--如果备注是'2019-11-13 10:00',将其替换为null
SELECT MAX(字段) FROM dbo.表名 WHERE flag=1--挑选出最大值
SELECT CAST(AVG(1.0*Flag) AS REAL) FROM dbo.表名--平均值
SELECT CAST(ROUND(AVG(1.0*Flag),2) AS REAL) FROM dbo.表名--平均值保留两位小数
select count('asdf') FROM 表名 WHERE 备注 IS not NULL
SELECT COUNT(字段1+字段2) FROM 表名--挑选出字段1+字段2都不为null的

UPDATE dbo.表名 SET 备注=NULL WHERE 备注='0'
SELECT fieldlist FROM table1 [inner] JOIN table2 ON table1.COLUMN = table2.COLUMN--内关联:字段都不为null
SELECT iid,字段1,字段2 FROM dbo.表名1 INNER JOIN 表名 ON 表名.FID = 表名1.FID AND 表名.flag = 表名1.flag AND 表名.flag = 1
SELECT iid,字段1,字段2 FROM dbo.表名 INNER JOIN 表名1 ON 表名.FID = 表名1.FID AND 表名.flag = 表名1.flag AND 表名.flag = 1
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
--1.视图
--1.1可以加密视图，不可以加密查询
--1.2创建视图
--CREATE VIEW view_name [(column_name)] AS select_statement [with checkoption]
CREATE VIEW abc AS SELECT iid,fid,字段1 FROM dbo.表名 WHERE flag=1 AND iid<100
SELECT * FROM abc
--1.3删除视图，可以同时删除多个视图
DROP VIEW abc
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--1.存储过程，类比函数、方法
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--1.游标
DECLARE cur_01 CURSOR FOR SELECT * FROM dbo.DevInfo WHERE  DevID <100
OPEN cur_01
FETCH NEXT FROM cur_01
WHILE @@FETCH_STATUS=0
BEGIN FETCH NEXT FROM cur_01 END
CLOSE cur_01
DEALLOCATE cur_01
--fetch [[next|prior|first|last]from ]cursor_name [into @variable_name [,……n]
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
--1.事务
BEGIN TRANSACTION 
--……
COMMIT TRANSACTION --rollback