收集一些工作中常用的经典SQL语句

2、在同一数据表找到相同属性的记录

<span style="color: #0000ff;">SELECT</span> RoleName <span style="color: #0000ff;">FROM</span> SysRole <span style="color: #0000ff;">GROUP</span> <span style="color: #0000ff;">BY</span> RoleName <span style="color: #0000ff;">HAVING</span> <span style="color: #ff00ff;">COUNT</span>(ID)<span style="color: #808080;">></span><span style="color: #800000; font-weight: bold;">1</span>

3、随机提取记录

<span style="color: #0000ff;">SELECT</span> RoleName <span style="color: #0000ff;">FROM</span> SysRole <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> <span style="color: #ff00ff;">NEWID</span>()

4、按照姓氏笔画排序

<span style="color: #0000ff;">SELECT</span> RoleName <span style="color: #0000ff;">FROM</span> SysRole <span style="color: #0000ff;">ORDER</span> <span style="color: #0000ff;">BY</span> RoleName COLLATE Chinese_PRC_Stroke_CI_AS

5、获取某个日期所在月份的最大天数

<span style="color: #0000ff;">SELECT</span> <span style="color: #ff00ff;">DAY</span>(<span style="color: #ff00ff;">DATEADD</span>(dd,<span style="color: #808080;">-</span><span style="color: #ff00ff;">DAY</span>(<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2017-5-12</span><span style="color: #ff0000;">‘</span>),<span style="color: #ff00ff;">DATEADD</span>(mm,<span style="color: #800000; font-weight: bold;">1</span>,<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">2017-5-12</span><span style="color: #ff0000;">‘</span>))) <span style="color: #0000ff;">as</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">DayNumber</span><span style="color: #ff0000;">‘</span>

6、实现用0或1 来显式 男或女

<span style="color: #0000ff;">select</span> name ,Sex<span style="color: #808080;">=</span>
<span style="color: #ff00ff;">case</span><span style="color: #000000;"> Sex
</span><span style="color: #0000ff;">when</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">0</span><span style="color: #ff0000;">‘</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;">when</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">1</span><span style="color: #ff0000;">‘</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;">end</span>
<span style="color: #0000ff;">from</span> Sys_user

7、显式文章、提交人和最后回复时间

<span style="color: #0000ff;">select</span><span style="color: #000000;"> a.title,a.username,b.adddate
</span><span style="color: #0000ff;">from</span> tablename <span style="color: #0000ff;">as</span> a ,<span style="color: #0000ff;">select</span>(<span style="color: #ff00ff;">max</span>(adddate)) <span style="color: #0000ff;">from</span><span style="color: #000000;"> tablename
</span><span style="color: #0000ff;">where</span> tablename.title<span style="color: #808080;">=</span>a.title) b

8、嵌套子查询

<span style="color: #0000ff;">select</span> a,b,c <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> a <span style="color: #808080;">in</span>(<span style="color: #0000ff;">select</span> a <span style="color: #0000ff;">from</span> table2)

9、复制表结构(源表名：a ,目标表名：b)

<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">into</span> <span style="color: #0000ff;">from</span> a <span style="color: #0000ff;">where</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #808080;"><></span><span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">
或者
</span><span style="color: #0000ff;">select</span> <span style="color: #0000ff;">top</span> <span style="color: #800000; font-weight: bold;">0</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">into</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">b</span><span style="color: #ff0000;">]</span> <span style="color: #0000ff;">from</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">a</span><span style="color: #ff0000;">]</span>

10、复制表数据(源表名：a ,目标表名：b)

<span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">into</span> b(name,<span style="color: #0000ff;">desc</span>,createtime) <span style="color: #0000ff;">as</span> <span style="color: #0000ff;">select</span> name,<span style="color: #0000ff;">desc</span>,createtime <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">as</span> a

11、通配符的使用

<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">A-L%</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">--</span><span style="color: #008080;">筛选name列首字母在A~L之间的记录</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span><span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[ABCD]%</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">--</span><span style="color: #008080;">筛选name列首字母是 A、B、C或D的记录</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[A-DH]%</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">--</span><span style="color: #008080;">筛选name列首字母在A~D或者是H的记录</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[^D]%</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">--</span><span style="color: #008080;">筛选name列首字母不是D的记录</span>
<span style="color: #0000ff;">select</span> <span style="color: #808080;">*</span> <span style="color: #0000ff;">from</span> table1 <span style="color: #0000ff;">where</span> name <span style="color: #808080;">like</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">Xiao_ming</span><span style="color: #ff0000;">‘</span>
<span style="color: #008080;">--</span><span style="color: #008080;"> _ 匹配任意单个字符</span>

12、操作列

<span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> table1 <span style="color: #0000ff;">add</span> <span style="color: #0000ff;">desc</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">2000</span>) <span style="color: #808080;">not</span> <span style="color: #0000ff;">null</span> <span style="color: #0000ff;">default</span> <span style="color: #ff0000;">‘‘</span> <span style="color: #008080;">--</span><span style="color: #008080;">新增一列</span>
<span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> table1 <span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">column</span>  <span style="color: #0000ff;">desc</span>  <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">500</span>) <span style="color: #008080;">--</span><span style="color: #008080;">修改列</span>
<span style="color: #0000ff;">alter</span> <span style="color: #0000ff;">table</span> table1 <span style="color: #0000ff;">drop</span> colmn <span style="color: #0000ff;">desc</span> <span style="color: #008080;">--</span><span style="color: #008080;">删除列</span>

13、存储过程

<span style="color: #008080;">--</span><span style="color: #008080;">插入数据的存储过程示例</span>
<span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">procedure</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">proc_CreateUser</span><span style="color: #ff0000;">]</span>
<span style="color: #008000;">@username</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">),
</span><span style="color: #008000;">@password</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">),
</span><span style="color: #008000;">@truename</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">),
</span><span style="color: #008000;">@role</span> <span style="color: #0000ff;">int</span>
<span style="color: #0000ff;">as</span>
<span style="color: #0000ff;">insert</span> <span style="color: #0000ff;">INTO</span><span style="color: #000000;">  Users (UserName,Password,TrueName,Role,CreateDate)
</span><span style="color: #0000ff;">VALUES</span>(<span style="color: #008000;">@username</span>,<span style="color: #008000;">@password</span>,<span style="color: #008000;">@truename</span>,<span style="color: #008000;">@role</span>,<span style="color: #ff00ff;">GETDATE</span><span style="color: #000000;">())
</span><span style="color: #008080;">--</span><span style="color: #008080;">更新数据示例</span>
<span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">procedure</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">proc_UpdateUser</span><span style="color: #ff0000;">]</span>
<span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span><span style="color: #000000;">,
</span><span style="color: #008000;">@username</span> <span style="color: #0000ff;">nvarchar</span>(<span style="color: #800000; font-weight: bold;">200</span><span style="color: #000000;">)
</span><span style="color: #0000ff;">as</span>
<span style="color: #0000ff;">update</span><span style="color: #000000;"> Users
</span><span style="color: #0000ff;">set</span> UserName<span style="color: #808080;">=</span><span style="color: #008000;">@username</span>
<span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #008000;">@id</span>
<span style="color: #008080;">--</span><span style="color: #008080;">删除数据</span>
<span style="color: #0000ff;">CREATE</span> <span style="color: #0000ff;">procedure</span> <span style="color: #ff0000;">[</span><span style="color: #ff0000;">dbo</span><span style="color: #ff0000;">]</span>.<span style="color: #ff0000;">[</span><span style="color: #ff0000;">proc_DelUser</span><span style="color: #ff0000;">]</span>
<span style="color: #008000;">@id</span> <span style="color: #0000ff;">int</span>
<span style="color: #0000ff;">as</span>
<span style="color: #0000ff;">delete</span> Users <span style="color: #0000ff;">where</span> Id<span style="color: #808080;">=</span><span style="color: #008000;">@id</span>

14、视图示例

<span style="color: #0000ff;">create</span> <span style="color: #0000ff;">view</span><span style="color: #000000;">  view_user
</span><span style="color: #0000ff;">as</span>
<span style="color: #0000ff;">select</span>　id <span style="color: #0000ff;">from</span> <span style="color: #ff00ff;">user</span>
<span style="color: #0000ff;">go</span>

欢迎关注我的公众号（同步更新文章）：DoNet技术分享平台

收集一些工作中常用的经典SQL语句

标签：常用   平台   查询   经典   sel   ras   insert   timestamp   password