作为一枚程序员来说和数据库打交道是不可避免的,现收集一下工作中常用的SQL语句,希望能给大家带来一些帮助,当然不全面,欢迎补充! 1、执行插入语句,获取自动生成的递增的ID值 INSERT INTO SysRole (RoleName,RoleDesc) VALUES('超级管理员','描述')SELECT @@identity as 'id' 2、在同一数据表找到相同属性的记录 SELECT RoleName FROM SysRole GROUP BY RoleName HAVING COUNT(ID)>1 3、随机提取记录 SELECT RoleName FROM SysRole ORDER BY NEWID 4、按照姓氏笔画排序 SELECT RoleName FROM SysRole ORDER BY RoleName COLLATE Chinese_PRC_Stroke_CI_AS 5、获取某个日期所在月份的最大天数 SELECT DAY(DATEADD(dd,-DAY('2017-5-12'),DATEADD(mm,1,'2017-5-12'))) as 'DayNumber' 6、实现用0或1 来显式 男或女 select name ,Sex=case Sexwhen '0' then '男'when '1' then '女'endfrom Sys_user 7、显式文章、提交人和最后回复时间 select a.title,a.username,b.adddatefrom tablename as a ,select(max(adddate)) from tablenamewhere tablename.title=a.title) b 8、嵌套子查询 select a,b,c from table1 where a in(select a from table2) 9、复制表结构(源表名:a ,目标表名:b) select * into from a where 1<>1或者select top 0 * into [b] from [a] 10、复制表数据(源表名:a ,目标表名:b) insert into b(name,desc,createtime) as select name,desc,createtime from table1 as a 11、通配符的使用 select * from table1 where name like 'A-L%'--筛选name列首字母在A~L之间的记录select * from table1 where name like'[ABCD]%'--筛选name列首字母是 A、B、C或D的记录select * from table1 where name like '[A-DH]%'--筛选name列首字母在A~D或者是H的记录select * from table1 where name like '[^D]%'--筛选name列首字母不是D的记录select * from table1 where name like 'Xiao_ming'-- _ 匹配任意单个字符 12、操作列 alter table table1 add desc nvarchar(2000) not null default '' --新增一列alter table table1 alter column desc nvarchar(500) --修改列alter table table1 drop colmn desc --删除列 13、存储过程 --插入数据的存储过程示例CREATE procedure [dbo].[proc_CreateUser]@username nvarchar(200),@password nvarchar(200),@truename nvarchar(200),@role intasinsert INTO Users (UserName,Password,TrueName,Role,CreateDate)VALUES(@username,@password,@truename,@role,GETDATE)--更新数据示例CREATE procedure [dbo].[proc_UpdateUser]@id int,@username nvarchar(200)asupdate Usersset UserName=@usernamewhere Id=@id--删除数据CREATE procedure [dbo].[proc_DelUser]@id intasdelete Users where Id=@id 14、视图示例 create view view_userasselect id from usergo 欢迎关注我的公众号(同步更新文章):DoNet技术分享平台 |
|