当前位置：Gxlcms > 数据库问题 > 项目中常用的SQL语句

项目中常用的SQL语句

时间：2021-07-01 10:21:17 帮助过：16人阅读

***** Script for SelectTopNRows command from SSMS ******/ SELECT [RoleId] ,[RoleOrderId] ,[RoleName] ,[RoleStatus] ,[RoleInsertTime] ,[RoleUpdateTime] ,[RoleRemark] FROM [Math_RoleInfo] where exists ( SELECT * from Math_User_Role_Select where [Math_RoleInfo].[RoleId]=Math_User_Role_Select.[RoleId])

2、case when 的两种情况

<span style="color: #008080;">/*</span><span style="color: #008080;">***** Script for SelectTopNRows command from SSMS  *****</span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span>   <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleId</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleOrderId</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleName</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleStatus</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleInsertTime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleUpdateTime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleRemark</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
    , </span><span style="color: #ff00ff;">case</span><span style="color: #000000;">  RoleStatus
     </span><span style="color: #0000ff;">when</span> <span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">不正常</span><span style="color: #ff0000;">‘</span>
     <span style="color: #0000ff;">else</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">默认值</span><span style="color: #ff0000;">‘</span>  
     <span style="color: #0000ff;">end</span> <span style="color: #0000ff;">as</span><span style="color: #000000;"> Name1
  </span><span style="color: #0000ff;">FROM</span>  <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Math_RoleInfo</span><span style="color: #ff0000;">]</span>

<span style="color: #008080;">/*</span><span style="color: #008080;">***** Script for SelectTopNRows command from SSMS  *****</span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span>   <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleId</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleOrderId</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleName</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleStatus</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleInsertTime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleUpdateTime</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
      ,</span><span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleRemark</span><span style="color: #ff0000;">]</span><span style="color: #000000;">
    , </span><span style="color: #ff00ff;">case</span>  
     <span style="color: #0000ff;">when</span> RoleStatus<span style="color: #808080;">=</span><span style="color: #800000; font-weight: bold;">2</span> <span style="color: #0000ff;">then</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">不正常</span><span style="color: #ff0000;">‘</span>
     <span style="color: #0000ff;">else</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">默认值</span><span style="color: #ff0000;">‘</span>  
     <span style="color: #0000ff;">end</span> <span style="color: #0000ff;">as</span><span style="color: #000000;"> Name1
  </span><span style="color: #0000ff;">FROM</span>  <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Math_RoleInfo</span><span style="color: #ff0000;">]</span>

3、substring("abcdef",2,3) 得到 bcd

4、left("abcdefg",1) a

5、right("abcdefg",1)f

6、cast 和convert

<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">cast</span>(<span style="color: #800000; font-weight: bold;">1</span> <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">400</span>)) <span style="color: #0000ff;">as</span> Name<br><br>

<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #ff00ff;">getdate</span>(),<span style="color: #800000; font-weight: bold;">20</span>)<span style="color: #008080;">/*</span><span style="color: #008080;">2018-02-27</span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #ff00ff;">getdate</span>(),<span style="color: #800000; font-weight: bold;">120</span>) <span style="color: #008080;">--</span><span style="color: #008080;">2018-02-27</span>
<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">varchar</span>(<span style="color: #800000; font-weight: bold;">10</span>),<span style="color: #ff00ff;">getdate</span>(),<span style="color: #800000; font-weight: bold;">102</span>)<span style="color: #008080;">--</span><span style="color: #008080;">2018.02.27</span>

7、group by

<span style="color: #008080;">/*</span><span style="color: #008080;">***** Script for SelectTopNRows command from SSMS  *****</span><span style="color: #008080;">*/</span>
<span style="color: #0000ff;">SELECT</span>   <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleId</span><span style="color: #ff0000;">]</span>,<span style="color: #ff00ff;">max</span>(<span style="color: #ff00ff;">isnull</span>(RoleStatus,<span style="color: #800000; font-weight: bold;">100</span>)) <span style="color: #0000ff;">as</span><span style="color: #000000;"> maliang
      
  </span><span style="color: #0000ff;">FROM</span>  <span style="color: #ff0000;">[</span><span style="color: #ff0000;">Math_RoleInfo</span><span style="color: #ff0000;">]</span>
  <span style="color: #0000ff;">group</span> <span style="color: #0000ff;">by</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">RoleId</span><span style="color: #ff0000;">]</span>
  <span style="color: #0000ff;">order</span> <span style="color: #0000ff;">by</span><span style="color: #000000;"> maliang
 
  </span>

8、dateadd操作

<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">dateadd</span>(<span style="color: #ff00ff;">year</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">dateadd</span>(<span style="color: #ff00ff;">month</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">dateadd</span>(<span style="color: #ff00ff;">day</span>,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">dateadd</span>(quarter,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff00ff;">getdate</span>())

9、year month day函数

<span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">year</span>(<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">month</span>(<span style="color: #ff00ff;">getdate</span><span style="color: #000000;">())
</span><span style="color: #0000ff;">select</span> <span style="color: #ff00ff;">day</span>(<span style="color: #ff00ff;">getdate</span>())

10、datediff()

定义和用法

DATEDIFF() 函数返回两个日期之间的时间。

语法

DATEDIFF(<em>datepart</em>,<em>startdate</em>,<em>enddate</em>)

startdate 和 enddate 参数是合法的日期表达式。

datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

实例

例子 1

使用如下 SELECT 语句：

SELECT DATEDIFF(day,‘2008-12-29‘,‘2008-12-30‘) AS DiffDate

结果：

DiffDate
1

例子 2

使用如下 SELECT 语句：

SELECT DATEDIFF(day,‘2008-12-30‘,‘2008-12-29‘) AS DiffDate

结果：

DiffDate
-1

项目中常用的SQL语句

标签：convert stat 关键字 ffd where div ble use substr

项目中常用的SQL语句

定义和用法

语法

实例

例子 1

例子 2

人气教程排行