T-SQL 的简单查询语句

最新推荐文章于 2024-10-30 22:57:10 发布

weixin_34194087

最新推荐文章于 2024-10-30 22:57:10 发布

阅读量271

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/cl1006/p/4344648.html

版权

通配符：

“_”: 代表匹配一个字符

“%”：代表匹配多个字符；

[]：表示范围，可以包含多个数据

[^] 表示取反

“-“ 表示范围

逻辑与 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'
----------------------------------------------------

数据库的表结构改变时，顺便更新相关的视图：

select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

-----------------------------------------------------------------------------

去除里面一些重复的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,

------------------数据库中行列互转--------------------------------------------------

--http://192.168.0.168:6606/OperatorSystem/Income/getShopTargetsYaer
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 )

转载于:https://www.cnblogs.com/cl1006/p/4344648.html