sql随手笔记

--查询库中所有表名和表行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC
--查询所有表名
select name from sysobjects where xtype='u'
--查询信息
select * from sys.tables
--查询库中表名和占用量与行数
select
object_name(id) tablename,
8*reserved/1024 reserved,
rtrim(8*dpages)+'kb' used,
8*(reserved-dpages)/1024 unused,
8*dpages/1024-rows/1024*minlen/1024 free,
rows
--,*
from sysindexes
where indid=1
order by tablename,reserved desc

//创建File文件 
class Program
    {
        private const string FILE_NAME = "ErroLog.txt";
        static void Main(string[] args)
        {
            WriteFile("11111");
        }      
          public static void WriteFile(string str)
         {
             StreamWriter sr;            
             if (File.Exists(FILE_NAME)) //如果文件存在,则创建File.AppendText对象
             {
                 sr = File.AppendText(FILE_NAME);
             }
             else   //如果文件不存在,则创建File.CreateText对象
             {
                 sr = File.CreateText(FILE_NAME);
             }
             sr.WriteLine(str);
             sr.Close();
         }
    }

 public static string registered(string username, string password)
        {
            string path = @"D:\t.txt";
            //判断是否已经有了这个文件
            if (!System.IO.File.Exists(path))
            { 
                //没有则创建这个文件
                FileStream fs1 = new FileStream(path, FileMode.Create, FileAccess.Write);//创建写入文件 
                //设置文件属性为隐藏
                System.IO.File.SetAttributes(path, FileAttributes.Hidden);
                StreamWriter sw = new StreamWriter(fs1);
                sw.WriteLine(username.Trim() + "+" + password.Trim());//开始写入值
                sw.Close();
                fs1.Close();
                return "注册成功";
            }
            else
            {
                FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Write);
                System.IO.File.SetAttributes(path, FileAttributes.Hidden);
                StreamWriter sr = new StreamWriter(fs);
                sr.WriteLine(username.Trim() + "+" + password.Trim());//开始写入值
                sr.Close();
                fs.Close();
                return "注册成功";
            }
        }

//在C盘创建txt文件
            string filepath = @"C:\FLFG\1.txt";
            if (!File.Exists(filepath))
            {
                File.WriteAllText(filepath,"aaaa",Encoding.Default);
            }

  //已有的txt文件中追加日志log.txt               
File.AppendAllText(@"C:\FLFG\log.txt", "\r\n" + fi + "-----采集失败！！！");

select 'truncate table',name from sys.tables; 

//查询数据库中所有表的行数
SELECT a.name, b.rows
FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id
WHERE (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY a.name,b.rows DESC

Access基础
--在msysobjects 表中 where type=1 and flags=0  可以查出库中所有表名
SELECT name FROM msysobjects WHERE type=1 and flags=0 ORDER BY NAME
//ODBU链接远程服务器
SELECT * 
INTO zz  
from [ODBC;Driver={SQL Server};
Server=168.160.13.9;
Database=GFJG_DataBase;
Uid=sa;
Pwd=istic00;
].zz
---------------------------------
--获取表中所有字段名
SELECT name FROM syscolumns WHERE id in(SELECT id FROM sysobjects WHERE(name='zz')) 
 
--获取表中所有字段名
Select name from syscolumns Where ID=OBJECT_ID('zz')
 
--获取数据库中所有表 
SELECT NAME FROM SYSOBJECTS WHERE XTYPE='U' ORDER BY NAME

-------------------------------------------------------------------------------------------------------------
--备份数据

backup database [J_STAGE_20190115增量]

to disk = 'E:\Program Files\Microsoft SQL Server\backup\J_STAGE_20190115增量.bak' --specify new backup file



--压缩备份数据	

backup database [J_STAGE]
to disk = 'E:\Program Files\Microsoft SQL Server\backup\J_STAGE_20190115.bak' --specify new backup file
with
compression,
format,
init,
skip,
stats=5

--------------------------------------------------------------
select * into 新表 from 旧表 where 1<>1;
----------------
--删除字段
alter table fk_Max_yw drop column id
--添加字段
alter table yw_tool_Sh add qkbh nvarchar(500)

 MessageBox.Show("成功","删除内容",  MessageBoxButtons.OKCancel,MessageBoxIcon.Question);
？
 MessageBox.Show("成功","删除内容",  MessageBoxButtons.OKCancel,MessageBoxIcon.Exclamation);
！ 
MessageBox.Show("成功","删除内容",  MessageBoxButtons.OKCancel,MessageBoxIcon.Error);
x