SQLServer 实用语法大全

发布时间：2020-12-12 13:36:59 所属栏目：MsSql教程来源：网络整理

导读：SQLServer 实用语法大全一、SQLserver篇 1、将表名作为参数的存储过程 eg: create proc s_Table_SearchInfo? @TableName nvarchar(100)? AS ? Begin declare @value nvarchar(50), ? ? ? ? ? ? ? ? @sql nvarchar(1000)? set @sql= ' select * from ' + @Ta

SQLServer 实用语法大全

一、SQLserver篇

1、将表名作为参数的存储过程
eg:
create proc s_Table_SearchInfo?
@TableName nvarchar(100)?
AS
? Begin
declare @value nvarchar(50),
? ? ? ? ? ? ? ? @sql nvarchar(1000)?
set @sql= ' select * from ' + @TableName?
exec sp_executesql @sql,N'@value ? int ? output ',@value output ??
select @value
End
GO

调用：存储过程名'表名'
eg:s_Table_SearchInfo'StuInfo'

2、模糊查询（分页）的存储过程
eg:
alter proc SA_FuzzySearch
@Name Nvarchar(20),
@MaximumRows int,?
@StartRowIndex int,
@Type Nvarchar(20)
AS?
? ? Begin
? ? declare @temp varchar(500)
? ? set @temp = 'Select Top ' + cast(@MaximumRows as varchar(2))
? ? set @temp = @temp + ' * From SA Where SA_ID Not In (Select Top ' +cast(@StartRowIndex as varchar(2))+ ' SA_ID From SA Where SA_Name Like '
? ? set @temp = @temp + '(''%'+ @Name +'%'') or SA_IsAdministrator like (''%'+ @Type +'%'') Order By SA_ID Desc) and SA_Name Like (''%'+ @Name +'%'') or SA_IsAdministrator like (''%'+ @Type +'%'') Order By SA_ID Desc'
? ? exec (@temp)
? ? End
Go

3、判断一个数据库是否offline
?If EXISTS(select * From master.dbo.sysdatabases where name='dd' and status<>512)
? ? print '是'
?else
? ? print '否'

4、SQL Server中判断数据库对象是否存在：
? if EXISTS(select * from sys.databases where Name = 'ZHyry')
? ? ? BEGIN?
? ? ? ? print '存在'
? ? ? END
? ?ELSE
? ? ? BEGIN
? ? ? ? print '不存在'
? ? ? END

5、SQL Server中判断表中字段是否存在：
?
　　if exists(select * from syscolumns where name='colname1' and id=object_id('数据库名.Owner.表名'))
?
　　print '存在'
?
　　else
?
　　print '不存在'

6、Access中判断表对象是否存在：

　　Select Count(*) AS Qty FROM MSysObjects Where ((MSysObjects.Name) Like '表名');

? ??

? ?SQLServer中判断表是否存在：

? ? ? ??if EXISTS(select TOP 1 * from sys.objects o where o.type=N'U' AND o.name =N'ZHNews')
? ? ? ? ?print '存在'
? ? ? else
? ? ? ? ?print '不存在'?
? ? ? --或者 ? ?
? ? ? if EXISTS(SELECT TOP 1 * FROM sys.tables t WHERE t.type =N'U' AND t.name =N'Trade')
? ? ? ? ? print '存在'
? ? ? else
? ? ? ? ?print '不存在'??

?

7、事务
--建立添加新信息的存储过程(s_DataDict)
alter proc s_DataDict_InsertInfo
@Name nvarchar(30),
@CName nvarchar(30),
@Time datetime
AS
? Begin
? ? --开始事务
Begin transaction
? ? --判断表是否存在
if exists(select * from sysobjects where Name = @Name)
? ? ? ? Begin
? ? ? ? --捕获错误信息
? ? ? ? raiserror('该表已创建！',1,1)
? ? ? ? --回滚事务
? ?rollback transaction
? ? ? ? End
? ? else
? ? ? ? Begin ? ? ?
? ? ? ? insert into s_DataDict values(newID(),@Name,@CName,@Time)
? ?--建立相应的子表
? ? ? ? exec(' create table ' + @Name + '(
? ?Pid uniqueidentifier not null,
? ? ? ? DataDictpid uniqueidentifier not null,
? ?Name nvarchar(30) not null,
? ?OrderMark int not null
? ?)')?
? ? ? ? --以及相应的主外键
? ? ? ? exec(' alter table ' + @Name +
? ? ? ? ' add constraint constraint_' + @Name +?
? ? ? ? ' Foreign key (DataDictpid)
? ? ? ? references s_DataDict(Pid)
? ? ? ? ')
? ? ? ? --提交事务
? ? ? ? commit transaction?
? ? ? ? End ?
? End
GO

(
附加：查询赋值 ? ? ? ?
? ? ? declare @TName nvarchar(50)
? ? ? select @TName = TableName from s_DataDict where Pid = @ID ? ?
)

8、重命名数据库、表、列
a:alter database 数据库名
? modify name = 新数据库名
b:exec sp_rename '旧表名'，'新表名'
c:exec sp_rename '旧表名.旧列名'，'新表名.新列名'

9、删除字段值相同的ID大的数据
/*
* distinct: 函数指如有多项相同只显示一项
* select * from 表 where 字段 in(select 字段 from 表 group by 字段 having count(1)>1)：查询出项多次的数据
*/
Declare @num int,@i int
select @num = Count(s_Name) from s_Info group by s_Name having count(1)>1
set @i = 0
while @i <= @num
Begin
delete from s_Info where s_ID in(select Max(s_ID) from s_Info where s_Name in (select s_Name from s_Info group by s_Name having count(1) > 1))
set @i = @i + 1
End
Go

10、查询重复的数据
select id,[name],次数 = count(*) from D?
? ? ? ?group by id,[name] having count(*) >= 2
/*
?* 查询不重复的信息
? ?select id,[name] having count(*) = 1
?*/?

11、查询数据库的所有表信息（包括系统表）
? ? select * from sysobjects?

/*
?* 查询数据库的所有表信息（不包括系统表）
?* select * from sysobjects?
?* ? ? where xtype = 'u'?
?*/

12、--复制表(已经存在，新表的结构必须和旧表结构相同)
? ? insert into OlerdTable select * from NewTable

? ? --复制表(不存在)
? ? select * into NewTable from OldTable

13、--本月第一天：
? ?select dateadd(dd,-day(getdate())+1,getdate())
? ?
? ?--本月最后一天：
? ?select dateadd(dd,-day(getdate()),dateadd(m,getdate()))

? ?--获取当前的星期数
? ?select 星期 = datepart(WeekDay,getdate()-1)
? ?select 星期 = DateName(dw,getDate())?

? ?--时间函数大全
? ?年 = DateName(year,GetDate())
? ?月 = DateName(month,GetDate())
? ?日 = DateName(day,GetDate())
? ?星期 = DateName(dw,GetDate())
? ?周 = DateName(week,GetDate())
? ?时 = DateName(hour,GetDate()
? ?分 = DateName(minute,GetDate())
? ?秒 = DateName(second,GetDate())?
? ?毫秒 = DateName(millisecond,GetDate())

? ? 1.一个月第一天的
　　SELECT DATEADD(mm,DATEDIFF(mm,getdate()),0)

　　2.本周的星期一
　　SELECT DATEADD(wk,DATEDIFF(wk,0)
? ?
? ? 扩展：
? ? ? ? SELECT DATEADD(wk,1)--周二
? ? ? ? ……
? ? ? ? SELECT DATEADD(wk,6)--周末

　　3.一年的第一天
　　SELECT DATEADD(yy,DATEDIFF(yy,0)

　　4.季度的第一天
　　SELECT DATEADD(qq,DATEDIFF(qq,0)

　　5.当天的半夜
　　SELECT DATEADD(dd,DATEDIFF(dd,0)

　　6.上个月的最后一天
　　SELECT dateadd(ms,-3,DATEADD(mm,0))

　　7.去年的最后一天
　　SELECT dateadd(ms,DATEADD(yy,0))

　　8.本月的最后一天
　　SELECT dateadd(ms,DATEDIFF(m,getdate())+1,0))

　　9.本年的最后一天
　　SELECT dateadd(ms,0))

　　10.本月的第一个星期一
　　select DATEADD(wk,dateadd(dd,6-datepart(day,getdate())),0)

14、游标
/*
?* 声明游标:
?* DECLARE 游标名 CURSOR FOR T_SQL;
?*
?*打开游标:
?*OPEN 游标名
?*
?*关闭游标:
?*CLOSE 游标名
?*
?*删除游标:
?*DEALLOCATE 游标名
?*
?*游标读取数据：
?*FETCH NEXT FROM 游标名
?*(或者)Fetch FIRST from 游标名
?*/

--声明游标
DECLARE deleteTable CURSOR FOR?
SELECT ltrim(rtrim(NAME)) FROM Sysobjects WHERE NAME LIKE '%000079'

--打开游标
OPEN deleteTable

--关闭游标
CLOSE deleteTable

--删除游标
DEALLOCATE deleteTable

--读取数据
FETCH NEXT FROM deleteTable INTO @Value1,@Value2
--或则 Fetch first from mycursor

--判断游标是否存在 deleteTable:游标名
if cursor_status('global','deleteTable')=-3 and cursor_status('local','deleteTable')=-3
? ?print '不存在'
else
? ?print '存在'

CREATE PROC PROC_EA
AS
? BEGIN
? ? ? ?DECLARE EAMCMT4 CURSOR FOR
? ? ? ?SELECT TOP 545 UserId,CreateTime FROM EA_MCMT4 ORDER BY CreateTime DESC
? ? ? ?
? ? ? ?DECLARE @UserId nchar(50),@DateTime smallDatetime,@Id int
? ? ? ?SET @Id = 560

? ? ? ?OPEN EAMCMT4
? ? ? ?WHILE @@FETCH_STATUS = 0
? ? ? ?BEGIn
? FETCH NEXT FROM EAMCMT4 INTO @UserId,@DateTime
? UPDATE EA SET userId=@UserId,[DateTime]= @DateTime WHERE Id=@Id?
? SET @Id = @Id + 1
? ? ? ?END
? ? ? ?CLOSE EAMCMT4
? ? ? ?DEALLOCATE EAMCMT4
? END
GO ?

--清空日志
DUMP TRANSACTION DBName WITH NO_LOG

--收缩数据库文件
DBCC SHRINKFILE('dazhou_Log',1)

/*
?*由此推导出SQLserver分页语句
?*pageSize: ?每页显示数据条数
?*TableName：查询表名
?*pageIndex：分页索引(默认为1,即首页)
?*pageCount: 总页数
?*/
IF pageIndex > 0 AND pageIndex <= pageCount
? ?BEGIN
SELECT TOP pageSize * FROM TableName tn WHERE tn.ID NOT IN(
? SELECT TOP (pageIndex-1)*pageSize tn.ID FROM TableName tn ORDER BY tn.ID ASC)
? ORDER BY tn.ID ASC
? ?END
ELSE
? ?BEGIN
? ? ? ?SELECT TOP pageSize * FROM TableName
? ?END?

二、Mysql篇

纯CMD的方式操作MySql
启动MySql服务
命令行 > net start mysql
如果是Win7系统需要使用管理员身份运行

关闭服务
net stop mysql

进入MySql命令行界面
登录 mysql -u 用户名 -p 回车，然后输入密码
mysql -u root -p
welcome

修改密码
update mysql.user set password = '新密码'
where user = '用户名'
update mysql.user set password = 'heshang'
where user = 'root'
修改完毕后需要重启服务才能生效

查看表结构
describe 表名

查看表：
show tables;

在mysql中默认情况下不显示bit类型，需要通过输出格式化来进行显示。
两种主要方式：
进制转换：二进制、八进制、十六进制。
编码转换：ASCII
十六进制：hex(字段名)
ASCII：ord(字段名)

查看字段值的长度
length(字段名)

MySql中没有identity标识列，只有自动生成auto_increment。
MySql中如果表中有自动生成列，则Insert时不能够省略字段名。
自动生成列必须和主键一起使用。

自动生成列和标识列的区别
标识列是只读的，不允许添加。
自动生成列在值为空的时候会自动生成值，在值不为空的时候不会自动生成。

导出数据库
mysqldump -u root -p 数据库名>文件名.sql;
该命令必须在MySql安装目录中的bin目录下执行。

导入数据库
source 路径/文件名.sql
导入数据库时，必须在mysql中建立相应数据库

返回数据库最上级
use mysql;
show
修改表结构中的字段
alter table 表名
change column 字段名新字段名新类型新约束...;

重命名一张表
alter table 表名 rename 新表名;

删除字段
alter table 表名
drop 字段名;

删除主键
alter table 表名

drop primary key;

备注：很多是网上搜集而来，正确性还有待验证。

? ? ? ? ? ?疏漏之处还请多多指正，谢谢！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!