SQL Server
日期转换
CONVERT(varchar(12) ,convert(datetime,TEMP1.有效日期), 111 )
private static readonly string connStr = ConfigurationManager.ConnectionStrings["SQLSERVER"].ConnectionString;
<connectionStrings>
<add name ="SQLSERVER" connectionString="Data Source=127.0.0.1;Initial Catalog=ybinterface_new;User ID=sa;Password=123456"/>
</connectionStrings>
2017.09.27
查询数据库下所有触发器所在的表名
select name as trigger_name,object_name(parent_obj) as tableName
from sysobjects
where xtype='TR'
SQL语句,在数据库的所有表里查找某一个字段值
比如查的“首页列表”方法:
exec Full_Search '首页列表'
CREATE proc Full_Search(@string varchar(50))
as
begin
declare @tbname varchar(50)
declare tbroy cursor for select name from sysobjects
where xtype= 'u ' --第一个游标遍历所有的表
open tbroy
fetch next from tbroy into @tbname
while @@fetch_status=0
begin
declare @colname varchar(50)
declare colroy cursor for select name from syscolumns
where id=object_id(@tbname) and xtype in (
select xtype from systypes
where name in ( 'varchar ', 'nvarchar ', 'char ', 'nchar ') --数据类型为字符型的字段
) --第二个游标是第一个游标的嵌套游标,遍历某个表的所有字段
open colroy
fetch next from colroy into @colname
while @@fetch_status=0
begin
declare @sql nvarchar(1000),@j int
select @sql= 'select @i=count(1) from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%'''
exec sp_executesql @sql,N'@i int output',@i=@j output --输出满足条件表的记录数
if @j> 0
BEGIN
select 包含字串的表名=@tbname
--exec( 'select distinct '+@colname+' from ' +@tbname + ' where '+ @colname+ ' like '+ '''%'+@string+ '%''')
END
fetch next from colroy into @colname
end
close colroy
deallocate colroy
fetch next from tbroy into @tbname
end
close tbroy
deallocate tbroy
end
go
全局查询字段
--含有医院编号字段的所有表
select a.[name] 表名 from sysobjects a,
(
select [id],count(*) b from syscolumns
where [name] ='HospitalId'
group by [id]
)
b where a.[id]=b.[id]
--同时含有医院编号和科室编号字段的所有表
select a.[name] 表名 from sysobjects a
left join
(
select [id],count(*) b from syscolumns where [name]
in('HospitalId','DepartmentId') group by [id] having count(*)>1
) b
on a.[id]=b.[id]
where b.id is not null
2个数据库联合查询
select * from HIS_chaoyang6.dbo.Mst_LongDoctorAdvice a LEFT JOIN NEWHISMessageWarning.dbo.MessageWarning b on a.id!=b.yzID
查询数据库里所有表
SELECT Name FROM SysObjects Where XType='U' ORDER BY Name
查询所有表信息
select top 1000
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS 表名,
isnull(g.[value],'无') AS 说明
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0)ORDER BY a.name
USE HIS_puen0816
SELECT sysobjects.name,sys.extended_properties.value from sysobjects
left join sys.extended_properties on sysobjects.id=sys.extended_properties.major_id
where type= 'U ' and sys.extended_properties.minor_id='0' order by name
取表字段信息
select syscolumns.name,syscolumns.length,systypes.name as tname,sys.extended_properties.[value] AS tname,syscolumns.isnullable from syscolumns
join systypes on syscolumns.xtype=systypes.xtype and systypes.name <> 'sysname '
LEFT OUTER JOIN sys.extended_properties ON
( sys.extended_properties.minor_id = syscolumns.colid
AND sys.extended_properties.major_id = syscolumns.id)
where syscolumns.id in
(select id from sysobjects where name= '表名')
--快速查看表结构(比较全面的)
SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep.[value], '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN '√'
ELSE ''
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN '√'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHERE obj.name = 'Hos_Income'--表名
ORDER BY col.colorder ;
连接字符串
<!--连接字符串,写在配置文件-->
使用windows身份验证
<add name="sql" connectionString="Data Source = .;Initial Catalog = MySql;Integrated Security = True;"/>
Sql Server身份验证
<add name="sql" connectionString="Data Source = .;Initial Catalog = MySql;User Id = sa;Password = 123456"/>
//读取连接字符串
string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
读取数据
//读取连接字符串
string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
SqlDataReader read = BookShop.DBUtility.SqlHelper.ExecuteReader(str, CommandType.Text, "SELECT * FROM userinfo");
if(read.HasRows)
{
while (read.Read())
{
string ss0 = read.GetValue(0).ToString();
string ss1 = read.GetValue(1).ToString();
string ss2 = read.GetValue(2).ToString();
}
}
插入数据
//读取连接字符串
string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
//插入数据
string sql = "insert into [userinfo] (username,userpass,phone)values('aaa','bbb','ccc')";
int result = BookShop.DBUtility.SqlHelper.ExecuteNonQuery(str, CommandType.Text, sql);
读取数据
//读取连接字符串
string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
DataSet da = BookShop.DBUtility.SqlHelper.ExecuteDataset(str, CommandType.Text, "SELECT * FROM userinfo");
DataTable s = da.Tables[0];
foreach (DataRow item in s.Rows)
{
string ss0 = item[0].ToString();
string ss1 = item[1].ToString();
string ss2 = item[2].ToString();
}
参数
//读取连接字符串
string str = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
SqlParameter sql = new SqlParameter("@aaa", "111");
int result = BookShop.DBUtility.SqlHelper.ExecuteNonQuery(str, CommandType.Text, "SELECT * FROM userinfo where usename ='@aaa'", sql);
(1) 定义:
char: 固定长度,存储ANSI字符,不足的补英文半角空格。
nchar: 固定长度,存储Unicode字符,不足的补英文半角空格
varchar: 可变长度,存储ANSI字符,根据数据长度自动变化。
nvarchar: 可变长度,存储Unicode字符,根据数据长度自动变化。
一个汉字=2 nchar
设计表保存:
保存后出现这种情况,按下面设置sql studio即可.
作者:银龙
-------------------------------------------
个性签名:独学而无友,则孤陋而寡闻。做一个灵魂有趣的人!
如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!
万水千山总是情,打赏一分行不行,所以如果你心情还比较高兴,也是可以扫码打赏博主,哈哈哈(っ•̀ω•́)っ✎⁾⁾!