通配符：
“_”: 代表匹配一个字符
“%”： 代表匹配多个字符；
[]：表示范围，可以包含多个数据
[^] 表示取反
“-“ 表示范围
逻辑与 and 逻辑或 or 逻辑非 not
聚会函数 ：
聚合函数：sum()/max()/min()/avg()/count()
where /group by /having
-----------删除数据------------------------------
delete:有选择性的删除
删除的信息不能被子表所有使用
truncate：删除整张表的所有信息
不能删除主表的新表
标识列重新赋值
--------------添加 修改--------------------
添加 insert into table1 values('','',''');
修改 ： update table set 列名 =‘’，列名=‘’
------------------查询--------------------
模糊查询 select * from table where name like ‘张%’；
select * from table 查询所有信息
select name 姓名 ，sex 性别 from table
select * from table where name is null； 查询信息为的
select top 3 from table 查询前三条信息
----in ---between --的使用---
select * from table where age in（21，23）；
select * from table where age between 10 and 20；包括10与20 在内
-----------
order by 用于排序
select* from table order by Age asc ：升序 查询
select * from table order by Age desc 降序排序
group by 分组查询 可以和聚合函数一起使用
having 需要跟Group by 结合一起使用 作用：筛选数据 having用于筛选分组后的数据
select studentName, avg(score) from student group by studentName
查询值为空： select * from table where sex is null；
---------多表连接 -----
内链接： select * from tablea a inner join tableb b on a.id=b.id ;
-------------时间的计算-------
--周年的
SELECT DATEDIFF(day,'2008-12-30',GETDATE())/365
--没有周年
SELECT DATEDIFF(YEAR,'2008-12-30',GETDATE())
--时间条件的查询----
(cast(ReplyDate as datetime) >= '2015-07-03 00:00:00') and (cast(ReplyDate as datetime) < '2015-07-03 23:59:59')
也可以用：
and NiGaoSJ between '2015-07-02 0:0:00' and '2015-07-02 23:59:59'
----------------------------------------------------
数据库的表结构改变时，顺便更新相关的视图 ：
去除里面一些重复的flowID 数据
select *from T_WorkFlow_MainInfo m inner join (SELECT * FROM dbo.T_BGSW_QingJia
WHERE (ID IN(SELECT MAX(ID) FROM T_BGSW_QingJia GROUP BY FlowID))) jq on jq.FlowID = m.FlowID
where WorkFlowType='12'
查询表单的重复的数据
select * from (select COUNT(*) as a ,id from T_River_GaugingData group by id) as B where B.a >1
---------------------------------------------------
查询近几天的某个时间点
select DateAdd(DAY ,-3,getdate())
---------------查询子节点的关系-------------------------------------------------
select * ，fID =（select count （*）from table where table。Fid = t。id ）from table t
---------------------------将时间延长一年-------------------
update T_Vote_Main set EndTime = dateadd(Y ,+365,EndTime) where FlowID = 'acad59a1d55b433aaabb9c7ae1c7e251'
---------------计算百分比的例子-------------------------
declare @A table([ID] int,[Name] nvarchar(2),[num] int)
Insert @A
select 1,N'小明',23 union all
select 2,N'小刚',33 union all
select 3,N'小红',44
Select a.[Name],
cast(a.[num]*1.0/sum(a.[num])over() as numeric(8,2)) as per
from @A as a
---------------sql查询的时候对某个字段进行排序得到排名的序号--------
row_number() over(order by kh_wo1 desc) as kh_wo1PM,
CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), --学生姓名
[Subject] NVARCHAR(30), --科目
[Score] FLOAT, --成绩
)
INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80
INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90
INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70
INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85
INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80
INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90
INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70
INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85
SELECT
UserName,
MAX(CASE Subject WHEN '语文' THEN Score ELSE 0 END) AS '语文',
MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',
MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',
MAX(CASE Subject WHEN '生物' THEN Score ELSE 0 END) AS '生物'
FROM dbo.[StudentScores]
GROUP BY UserName
--简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END --Case搜索函数 CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END
----------------
------------有店长查店长的数据没有查助理店长----------------------------------------
select account ,shopid from users t where account = (select top 1 account from users
where shopid = t.shopid and
(roleName = '店长' or roleName='助理店长')
order by rolename )
-----------------------------------
replace () 函数
select replace(‘门店asd’,'门店','')--将门店替换为 ‘’
-----------------------------------------------------------------------------------------------
----插入数据 是检查唯一标识是否存在。存在不 添加。不存在添加。 insert into Department(cdepcode,cdepname) select t.cdepcode,t.cdepname from (select 001 as cdepcode,信息部 as cdepname ) t
left join Department b on t.cdepcode=t.cdepcode where b.cdepcode is null
-----插入数据时判断该数据是否存在存在不插入 不存在插入--------
insert into B2B_DS_ORDERHZ (ordernum,payState,erpaddid,is_fp,code,bz,orderState,creatTime,orderfrom,total,paytotal,payType,editTime) select N'1521125985680614',N'是',N'erpaddid',N'fp',N'DWI00009649',N'bz',N'已受理',N'2018-3-15 22:59:45',N'pc','747'.'8','740'.'32',N'支付宝',N'2018-4-28 00:32:10' where not exists (select 1 from B2B_DS_ORDERHZ where orderNum='1521125985680614' )
--- ------ 在检索数据时 同常会用到 in 来检索数据 in 在检查数据的量比没有被检索的量。用in 比较好。 反之用 EXISTS 比较好
select GoodsId,LevelId,0 as price from MultiPrice(nolock) where
EntId='E14VWE917T6' and SaleTaxP>0
and GoodsId+LevelId not in(select GoodsId+LevelId from eh_GoodsLevel(nolock))
select GoodsId,LevelId,0 as price from MultiPrice a (nolock) where
EntId='E14VWE917T6' and SaleTaxP>0
and not EXISTS (select GoodsId+LevelId from eh_GoodsLevel b where a. GoodsId+a.LevelId = b.GoodsId+b.LevelId )