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即可.

​