--------------------------------------------------------------------------------
1、删除VS最近打开文件和项目列表
删除方法:
打开注册表,找到HKEY_USERS/S-1-5-21-699429042-3081107387-3585865962-500(不同的机器不知道是不是一样)/Software/Microsoft/VisualStudio/8.0(VS的版本) 找到FileMRUList项和 ProjectMRUList项,删除那些值列表就行了。
//------------------------------------------------------------------------------------------------------------------
2、C#中一些字符串操作的常用用法
--------------------------------------------------------------------------------
//获得汉字的区位码
byte[] array = new byte[2];
array = System.Text.Encoding.Default.GetBytes("啊");
int i1 = (short)(array[0] - ''/0'');
int i2 = (short)(array[1] - ''/0'');
//unicode解码方式下的汉字码
array = System.Text.Encoding.Unicode.GetBytes("啊");
i1 = (short)(array[0] - ''/0'');
i2 = (short)(array[1] - ''/0'');
//unicode反解码为汉字
string str = "4a55";
string s1 = str.Substring(0,2);
string s2 = str.Substring(2,2);
int t1 = Convert.ToInt32(s1,16);
int t2 = Convert.ToInt32(s2,16);
array[0] = (byte)t1;
array[1] = (byte)t2;
string s = System.Text.Encoding.Unicode.GetString(array);
//default方式反解码为汉字
array[0] = (byte)196;
array[1] = (byte)207;
s = System.Text.Encoding.Default.GetString(array);
//取字符串长度
s = "iam方枪枪";
int len = s.Length;//will output as 6
byte[] sarr = System.Text.Encoding.Default.GetBytes(s);
len = sarr.Length;//will output as 3+3*2=9
//字符串相加
System.Text.StringBuilder sb = new System.Text.StringBuilder("");
sb.Append("i ");
sb.Append("am ");
sb.Append("方枪枪");
/
string --> byte array
byte[] data=Syste.Text.Encoding.ASCII.GetBytes(string);
string --> byte
byte data = Convert.ToByte(string);
byte[]-->string
string string = Encoding.ASCII.GetString( bytes, 0, nBytesSize );
//---------------------------------------------------------------------------------------------------------------
3、用Visual C#实现文件下载
作者: 王凯明 发表时间: 2003-11-23 11:06:45
--------------------------------------------------------------------------------
一.概述:
本文通过一个实例向大家介绍用Visual C#进行Internet通讯编程的一些基本知识。我们知道.Net类包含了请求/响应层、应用协议层、传输层等层次。在本程序中,我们运用了位于请求/响应层的WebRequest类以及WebClient类等来实现高抽象程度的Internet通讯服务。本程序的功能是完成网络文件的下载。
二.实现原理:
程序实现的原理比较简单,主要用到了WebClient类和FileStream类。其中WebClient类处于System.Net名字空间中,该类的主要功能是提供向URI标识的资源发送数据和从URI标识的资源接收数据的公共方法。我们利用其中的DownloadFile()方法将网络文件下载到本地。然后用FileStream类的实例对象以数据流的方式将文件数据写入本地文件。这样就完成了网络文件的下载。
三.实现步骤:
首先,打开Visual Studio.Net,新建一个Visual C# Windows应用程序的工程,不妨命名为"MyGetCar"。
接着,布置主界面。我们先往主窗体上添加如下控件:两个标签控件、两个文本框控件、一个按钮控件以及一个状态栏控件。
完成主窗体的设计,我们接着完成代码的编写。
在理解了基本原理的基础上去完成代码的编写是相当容易。程序中我们主要用到的是WebClient类,不过在我们调用WebClient类的实例对象前,我们需要用WebRequest类的对象发出对统一资源标识符(URI)的请求。
try
{
WebRequest myre=WebRequest.Create(URLAddress);
}
catch(WebException exp)
{
MessageBox.Show(exp.Message,"Error");
}
这是一个try-catch语句,try块完成向URI的请求,catch块则捕捉可能的异常并显示异常信息。其中的URLAddress为被请求的网络主机名。
在请求成功后,我们就可以运用WebClient类的实例对象中的DownloadFile()方法实现文件的下载了。其函数原型如下:
public void DownloadFile( string address, string fileName);
其中,参数address为从中下载数据的 URI,fileName为要接收数据的本地文件的名称。
之后我们用OpenRead()方法来打开一个可读的流,该流完成从具有指定URI的资源下载数据的功能。其函数原型如下:
public Stream OpenRead(string address);
其中,参数address同上。
最后就是新建一个StreamReader对象从中读取文件的数据,并运用一个while循环体不断读取数据,只到读完所有的数据。
还有在使用以上方法时,你将可能需要处理以下几种异常:
WebException:下载数据时发生错误。
UriFormatException:通过组合 BaseAddress、address 和 QueryString 所构成的 URI 无效。
这部分的代码如下:(client为WebClient对象,在本类的开头处声明即可)
statusBar.Text = "开始下载文件...";
client.DownloadFile(URLAddress,fileName);
Stream str = client.OpenRead(URLAddress);
StreamReader reader = new StreamReader(str);
byte[] mbyte = new byte[100000];
int allmybyte = (int)mbyte.Length;
int startmbyte = 0;
statusBar.Text = "正在接收数据...";
while(allmybyte>0)
{
int m = str.Read(mbyte,startmbyte,allmybyte);
if(m==0)
break;
startmbyte+=m;
allmybyte-=m;
}
完成了文件数据的读取工作后,我们运用FileStream类的实例对象将这些数据写入本地文件中:
FileStream fstr = new FileStream(Path,FileMode.OpenOrCreate,FileAccess.Write);
fstr.Write(mbyte,0,startmbyte);
这样,程序主体部分的代码已经完成了,不过要完成全部程序还需要一些工作。由于在程序接收网络文件数据的时候运用到了while循环体,这样会很占程序资源,表现的形式就是主窗体不能自由移动。为了解决这个问题,我们在程序中用到了多线程机制。我们在响应按钮的事件中新建一个线程,该线程就是用来实现网络文件下载功能的。如此,文件下载的线程和程序主线程并存,共享进程资源,使得程序顺畅运行。这样,我们在按钮控件的消息响应函数里添加如下代码:
Thread th = new Thread(new ThreadStart(StartDownload));
th.Start();
该线程的实现函数就是StartDownload(),而上面介绍的那些代码就是这个函数的主体部分。
最后,因为程序中运用到了WebRequest、WebClient、FileStream、Thread等类,所以最重要的就是在程序的开始处添加如下名字空间:
using System.Net;
using System.IO;
using System.Threading;
下面就是程序的源代码:
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Net;
using System.IO;
using System.Threading;
namespace MyGetCar
{
///
/// Form1 的摘要说明。
///
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Label label1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox srcAddress;
private System.Windows.Forms.TextBox tarAddress;
private System.Windows.Forms.StatusBar statusBar;
private System.Windows.Forms.Button Start;
private WebClient client = new WebClient();
///
/// 必需的设计器变量。
///
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
///
/// 清理所有正在使用的资源。
///
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
///
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
///
private void InitializeComponent()
{
this.label1 = new System.Windows.Forms.Label();
this.label2 = new System.Windows.Forms.Label();
this.srcAddress = new System.Windows.Forms.TextBox();
this.tarAddress = new System.Windows.Forms.TextBox();
this.statusBar = new System.Windows.Forms.StatusBar();
this.Start = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// label1
//
this.label1.Location = new System.Drawing.Point(8, 32);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(72, 23);
this.label1.TabIndex = 0;
this.label1.Text = "文件地址:";
this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
//
// label2
//
this.label2.Location = new System.Drawing.Point(8, 72);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(72, 23);
this.label2.TabIndex = 1;
this.label2.Text = "另存到:";
this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
//
// srcAddress
//
this.srcAddress.Location = new System.Drawing.Point(80, 32);
this.srcAddress.Name = "srcAddress";
this.srcAddress.Size = new System.Drawing.Size(216, 21);
this.srcAddress.TabIndex = 2;
this.srcAddress.Text = "";
//
// tarAddress
//
this.tarAddress.Location = new System.Drawing.Point(80, 72);
this.tarAddress.Name = "tarAddress";
this.tarAddress.Size = new System.Drawing.Size(216, 21);
this.tarAddress.TabIndex = 3;
this.tarAddress.Text = "";
//
// statusBar
//
this.statusBar.Location = new System.Drawing.Point(0, 151);
this.statusBar.Name = "statusBar";
this.statusBar.Size = new System.Drawing.Size(312, 22);
this.statusBar.TabIndex = 4;
//
// Start
//
this.Start.FlatStyle = System.Windows.Forms.FlatStyle.Flat;
this.Start.Location = new System.Drawing.Point(216, 112);
this.Start.Name = "Start";
this.Start.Size = new System.Drawing.Size(75, 24);
this.Start.TabIndex = 5;
this.Start.Text = "开始下载";
this.Start.Click += new System.EventHandler(this.Start_Click);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6, 14);
this.ClientSize = new System.Drawing.Size(312, 173);
this.Controls.AddRange(new System.Windows.Forms.Control[] {
this.Start,
this.statusBar,
this.tarAddress,
this.srcAddress,
this.label2,
this.label1});
this.MaximizeBox = false;
this.Name = "Form1";
this.Text = "文件下载器";
this.ResumeLayout(false);
}
#endregion
///
/// 应用程序的主入口点。
///
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void StartDownload()
{
Start.Enabled = false;
string URL = srcAddress.Text;
int n = URL.LastIndexOf("/");
string URLAddress = URL.Substring(0,n);
string fileName = URL.Substring(n+1,URL.Length-n-1);
string Dir = tarAddress.Text;
string Path = Dir+"<A href="file:"+fileName">//"+fileName;
try
{
WebRequest myre=WebRequest.Create(URLAddress);
}
catch(WebException exp)
{
MessageBox.Show(exp.Message,"Error");
}
try
{
statusBar.Text = "开始下载文件...";
client.DownloadFile(URLAddress,fileName);
Stream str = client.OpenRead(URLAddress);
StreamReader reader = new StreamReader(str);
byte[] mbyte = new byte[100000];
int allmybyte = (int)mbyte.Length;
int startmbyte = 0;
statusBar.Text = "正在接收数据...";
while(allmybyte>0)
{
int m = str.Read(mbyte,startmbyte,allmybyte);
if(m==0)
break;
startmbyte+=m;
allmybyte-=m;
}
FileStream fstr = new FileStream(Path,FileMode.OpenOrCreate,FileAccess.Write);
fstr.Write(mbyte,0,startmbyte);
str.Close();
fstr.Close();
statusBar.Text = "下载完毕!";
}
catch(WebException exp)
{
MessageBox.Show(exp.Message,"Error");
statusBar.Text = "";
}
Start.Enabled = true;
}
private void Start_Click(object sender, System.EventArgs e)
{
Thread th = new Thread(new ThreadStart(StartDownload));
th.Start();
}
}
}
四.总结:
以上我通过一个实例向大家展示了如何用Visual C#实现网络文件的下载,我们不难发现用Visual C#进行Internet通讯编程是非常方便的。在上面的程序中,我们仅仅用到了WebClient类的一些方法,而WebClient类不光提供了网络文件下载的方法,还提供了文件上传的方法,有兴趣的读者不妨一试――用之实现一个文件上传器。同时这个程序只是一个非常简单的例子,程序下载完一个网页后,它所获得的仅仅是主页面的内容,并不能获得其中的图片、CSS等文件,所以要做出一个比较好的文件下载器还需读者进一步改进之。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4、获取系统时间和系统时间10后的时间:
System.DateTime dt=new DateTime();
dt=System.DateTime.Now;
System.DateTime dt2=new DateTime();
dt2=dt.AddDays(10);
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5、DataSet dsChange = dsc1FlexGrid.GetChanges(); 获取更改的数据集
DataSet ds= new dataset(); 定义一新的数据集
ds.clear(); 清空此数据集
ds.Merge(dsChange); 将更改的数据集的值传给新建数据集
dsc1FlexGrid.AcceptChanges(); 释放更改的更改的数据集
------------------------------------------------------------------------------------------------------------------------------
6、使C1FlexGrid中的第I列不可更改的属性:
c1FGStockCheck.Cols[i].AllowEditing=false;
------------------------------------------------------------------------------------------------------------------------------
7、将字段名与表格绑定的方法:
#region setc1FGStockCheck()
private void setc1FGStockCheck()
{
string sql = "select * "+
" from TL_TEMPSTOCKPILEN where 1=2 ";
DataSet ds = DataBaseAgent.GetDataFromDB(sql); 获取数据集
dsc1FlexGrid.Clear(); 另一数据集清空
dsc1FlexGrid.Merge(ds); 将获取的数据集绑定到数据集
DataBaseAgent.SetFlexGridStyle(c1FGStockCheck);
c1FGStockCheck.SetDataBinding(dsc1FlexGrid, dsc1FlexGrid.Tables[0].TableName);
DataBaseAgent.ConvertColumnCaption(c1FGStockCheck,"TL_TEMPSTOCKPILEN");
}
#endregion
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8、#region //复选框触发方法
private void SelectCheb()
{
if(this.dsOldMain.Tables[0].Rows.Count<=0)
{
return;
}
for(int i=1;i<this.c1FGOldMain.Rows.Count;i++)
{
if(i==this.c1FGOldMain.RowSel)
{
continue;
}
this.c1FGOldMain[i,"c_sign"]=false;
}
tbDepotID.Text= Convert.ToString(c1FGOldMain[this.c1FGOldMain.RowSel,"c_depotid"]);
if(Convert.ToBoolean(this.c1FGOldMain[this.c1FGOldMain.RowSel,"c_sign"])==true)
{
this.c1FGOldMain[this.c1FGOldMain.RowSel,"c_sign"]=false;
}
else
{
this.c1FGOldMain[this.c1FGOldMain.RowSel,"c_sign"]=true;
}
}
#endregion
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9、如何设置060416格式的年月日
首先要引用using System.Globalization;
然后调用函数:DateTime.Now.ToString("yyMMddHH",DateTimeFormatInfo.InvariantInfo)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
数据库中类型为Date型的数据,如何在FlexGrid里面显示出完整的日期时间格式:
c1FGInStock.Cols["d_time"].Format="yyyy-MM-dd HH:mm:ss";
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
精妙SQL:
说明:复制表(只复制结构,源表名:a 新表名:b)
SQL: select * into b from a where 1<>1
说明:拷贝表(拷贝数据,源表名:a 目标表名:b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明:显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明:外连接查询(表名1:a 表名2:b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
说明:两张关联表,删除主表中已经在副表中没有的信息
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明:--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM (+)AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM
说明:--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
说明:
从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源)
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明:四表联查问题:
SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
说明:得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#region 多窗体界面的程序设计方式(用到了TreeView/ ListView/ RichTextBox/ Panel各一个和两个 Splitter)
private System.Windows.Forms.TreeView treeView1;
private System.Windows.Forms.ListView listView1;
private System.Windows.Forms.RichTextBox richTextBox1;
private System.Windows.Forms.Splitter splitter2;
private System.Windows.Forms.Splitter splitter1;
private System.Windows.Forms.Panel panel1;
public void OutlookUI()
{
// Create an instance of each control being used.
this.treeView1 = new System.Windows.Forms.TreeView();
this.listView1 = new System.Windows.Forms.ListView();
this.richTextBox1 = new System.Windows.Forms.RichTextBox();
this.splitter2 = new System.Windows.Forms.Splitter();
this.splitter1 = new System.Windows.Forms.Splitter();
this.panel1 = new System.Windows.Forms.Panel();
// Insert code here to hook up event methods.
// Set properties of TreeView control.
this.treeView1.Dock = System.Windows.Forms.DockStyle.Left;
this.treeView1.Width = this.ClientSize.Width / 3;
this.treeView1.TabIndex = 0;
this.treeView1.Nodes.Add("treeView");
// Set properties of ListView control.
this.listView1.Dock = System.Windows.Forms.DockStyle.Top;
this.listView1.Height = this.ClientSize.Height * 2 / 3;
this.listView1.TabIndex = 0;
this.listView1.Items.Add("listView");
// Set properties of RichTextBox control.
this.richTextBox1.Dock = System.Windows.Forms.DockStyle.Fill;
this.richTextBox1.TabIndex = 2;
this.richTextBox1.Text = "richTextBox1";
// Set properties of Panel's Splitter control.
this.splitter2.Dock = System.Windows.Forms.DockStyle.Top;
// Width is irrelevant if splitter is docked to Top.
this.splitter2.Height = 3;
// Use a different color to distinguish the two splitters.
this.splitter2.BackColor = Color.Blue;
this.splitter2.TabIndex = 1;
// Set TabStop to false for ease of use when negotiating
// the user interface.
this.splitter2.TabStop = false;
// Set properties of Form's Splitter control.
this.splitter1.Location = new System.Drawing.Point(121, 0);
this.splitter1.Size = new System.Drawing.Size(3, 273);
this.splitter1.BackColor = Color.Red;
this.splitter1.TabIndex = 1;
// Set TabStop to false for ease of use when negotiating
// the user interface.
this.splitter1.TabStop = false;
// Add the appropriate controls to the Panel.
// The order of the controls in this call is critical.
this.panel1.Controls.AddRange(new System.Windows.Forms.Control[]
{richTextBox1, splitter2, listView1});
// Set properties of Panel control.
this.panel1.Dock = System.Windows.Forms.DockStyle.Fill;
this.panel1.TabIndex = 2;
// Add the rest of the controls to the form.
// The order of the controls in this call is critical.
this.Controls.AddRange(new System.Windows.Forms.Control[]
{panel1, splitter1, treeView1});
this.Text = "Intricate UI Example";
}
#endregion
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
当光标放在一个控件上面时,用提示框提示在这个控件上要完成的工作:
this.toolTip1.SetToolTip(textBox1,"请在这里输入您的姓名");
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
saveFileDialog使用:
saveFileDialog1.Filter="文本格式文件(*.txt)|*.txt";
saveFileDialog1.ShowDialog();
string strFileName=this.saveFileDialog1.FileName;
if(strFileName.Trim()!="")
this.RichTextBox1.SaveFile(strFileName,RichTextBoxStreamType.PlainText);
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
如何获取程序文件信息
System.Diagnostics.FileVersionInfo myInfo=new FileVersionInfo.GetVersionInfo(FileName);
如何获取当前程序文件信息
Application.CompanyName;
Application.CurrentCulture;
...
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查询结果字符类型左对齐,数据类型右对齐
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select distinct amount from checks;
distinct限制了一列中重复数据的输出。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
加减乘除取模运算符在查询语句中的使用,别名用引号括起来可以给列多个字的标题
select t.n_accountweight A,t.n_startweight B,(t.n_startweight-t.n_accountweight)/2 "BA www sss", t.rowid from tl_productstockpilen t where (t.n_startweight-t.n_accountweight)>=0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NULL没有任何值,在SQL中用=NULL将查询不到任何数据,通常用IS NULL 判断此列是不是为NULL
select * from TableName where Columns is null;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
通配符"_"可在SQL中代替一个字或一个字符,可与%联合使用,也可同时使用多个通配符
select t.*, t.rowid from tl_productstockpilen t where t.c_materialname like '_头_'
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
连接(||)符号的使用
select t.i_stockmode||'周星星'||t.c_isfreeze AA, t.rowid from tl_productstockpilen t where t.c_materialname like '%头_';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
union和union all和intersect和minus
union返回两个查询结果之和并去掉重复部分后的结果;
union all 返回两个查询结果之和包括重复的部分的结果;
minus 返回第一个查询中不在第二个查询中的结果;
intersect 返回两个查询结果中都有的部分;
select t.a from TableName1 t
union/union all / intersect
select g.a from TableName2 g;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
count函数返回一个值,即所有满足WHERE子句的行的总数,也可以用列名代替count括号中的*号
select count(*) "总数" from tl_productstockpilen t where t.n_startweight>0;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sum求和函数,返回一列中所有数值之和
avg求平均值函数,返回一列中所有数值的平均值
二者均只可作用于数值型数据
select sum(t.n_accountweight) "结前总数",sum(t.n_startweight) "结存总数" from tl_productstockpilen t where t.c_isfreeze='自由';
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
min/max 函数,返回一列中最小值和最大值,二者均可用于字符型数据
select max(t.c_id),min(t.c_id) from tl_productstockpilen t;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
variance产生标准差方,stddev用来得到一列数据的标准差。
合计函数大组合:
select count(t.n_accountweight),avg(t.n_accountweight),sum(t.n_accountweight),max(t.n_accountweight),min(t.n_accountweight), variance(t.n_accountweight) A ,stddev(t.n_accountweight) from tl_productstockpilen t ;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
add_months函数可以将某一天的月份增加一个数值
select t.d_time,add_months(t.d_time,2), t.rowid from tl_productstockpilen t where add_months(t.d_time,2)=to_date('2006-10-07 20:05:51','yyyy-MM-dd hh24:mi:ss');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
last_day函数返回指定月的最后一天
select distinct last_day(to_date('2006-02-01','yyyy-MM-dd')) from tl_productstockpilen t ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
months_between(x,y)函数得到x月和y月之间有几个月;
可用于求出具体一日期之前的数据,如:
select t.*, t.rowid from tl_productstockpilen t where months_between(to_date('2006-08-01','yyyy-MM-dd'),t.d_time)>0;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
new_time函数依照所在的时区调整时间
select t.d_time EDT,new_time(t.d_time,'EDT','PDT'),NEW_TIME(T.D_TIME,'EDT','BDT'), t.rowid from tl_productstockpilen t;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
next_day函数返回指定日期或其后的第一个星期几的日期。
查询d_time之后第一个星期二的日期
select t.d_time,next_day(t.d_time,'星期二') from tl_productstockpilen t;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sysdate返回系统时间和日期
select distinct sysdate from dual;
select t.* from tl_productstockpilen t where t.d_time < sysdate;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
abs(数值字段名)---返回指定数值的绝对值;
ceil(数值字段名)--返回大于或等于指定数值的最小整数值;
floor(数值字段名)-返回小于或等于指定数值的最大整数值;
select t.n_accountweight,abs(t.n_accountweight),ceil(t.n_accountweight),floor(t.n_accountweight) from tl_productstockpilen t where t.d_time < sysdate;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
角度变成弧度:角度×0.01745 (0.01745是弧度除以角度所得的值)
sin/sinh/cos/cosh/tan/tanh用法:
select A,SIN(A*0.01745),SINH(A*0.01745) from tabelname;
exp函数是以E为底的指数函数,exp(a)表示求e的a次幂。
ln函数返回参数的自然对数,log函数返回以第2个参数为底的第1个参数的对数值:
ln(A); log(B,10)
MOD函数与%的功能相同,都是求模:
MOD(A,B) ---A除以B后的余数。
power(A,B) ---求A的B次方是多少,B为正数
select t.n_accountweight,power(t.n_accountweight,2), t.rowid from tl_productstockpilen t ;
sqrt(A)---X的平方等于A,得到X的值。A的值必须大于等于0
sign函数在参数值小于0时返回-1,参数为0时返回0,参数值大于0时返回1
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
字符函数:
chr(字段名)-------将字段中的ASCLL码值转换成字符;
concat(A,B)-------相当于||,将两字符连接;
initcap(A)--------将字符的首写字母大写显示,其它的均为小写
select concat(t.c_sign,t.c_id) AA,t.c_sign||t.c_id BB,t.c_sign,t.c_id, chr(abs(t.n_accountweight)),initcap(t.c_id) from tl_productstockpilen t;
LOWER(A)------将A字符串都变成小写
UPPER(B)------将B字符串都变成大写
select t.c_pid,lower(t.c_pid) A,upper(t.c_pid) B from tl_productstockpilen t;
lpad(要操作的字符,将变成的长度,不足要补充的字符);
rpad(要操作的字符,将变成的长度,不足要补充的字符);
select t.c_weightunit,lpad(t.c_weightunit,10,'*'),rpad(t.c_weightunit,10,'*'),lpad(t.c_weightunit,10) from tl_productstockpilen t;
trim(字段名)---删除空格,与lpad/rpad同,可在trim前面加"l"或"r"表示去掉左边或右边的空格;
select t.c_weightunit,rtrim(lpad(t.c_weightunit,10)),ltrim(lpad(t.c_weightunit,10)) BB,rtrim(t.c_weightunit,'**') from tl_productstockpilen t;
replace(被查找的字符串,要查找的字符,将替换为什么样的字符),第三个参数如省略则删除查找到的字符;
select t.c_weightunit,replace(t.c_weightunit,'N','O') A,replace(t.c_weightunit,'O') B from tl_productstockpilen t;
substr(t.c_id,5,5)---从c_id字段中的第五个字符开始取5个字符;
substr(t.c_id,-5,5)--从c_id字段右边开始数,从第五个字符开始取5个字符。
select t.c_id,substr(t.c_id,5,5) B,substr(t.c_id,-5,5) A from tl_productstockpilen t;
translate(字符串, 字符串中的字符,随意字符)---将字符串中的字符转换成随意字符;
select t.c_materialname,translate(t.c_materialname,'焦油','大树') AA from tl_productstockpilen t;
instr(t.c_id,'1',4,2)---返回c_id中从第四位开始,第二次出现1的位置。
select t.c_id,instr(t.c_id,'1',4,2) A,instr(t.c_id,'1',3,1) B from tl_productstockpilen t;
length(字符串)---返回字符串的长度。
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
转换函数:
to_char(字段名)---将不同数据类型的字段转换成字符串类型;
to_number(字段名)--将字符串转换成一个数值。
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
greatest(字段名,字段名。。。)--取这些字段中数据的最大值;
least(字段名,字段名。。。)---取这些字段中数据的最小值;
select t.c_id,greatest(t.c_id,t.c_pid,t.i_stockmode) A,least(t.c_id,t.c_pid,t.i_stockmode) B from tl_productstockpilen t;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
使用数据库表的当前用户:(user)
select user from tablename;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
group by / having :以物资名称分组,且分组后重量大于814的记录
select t.c_materialname,sum(t.n_accountweight) from tl_productstockpilen t group by t.c_materialname having sum(t.n_accountweight)>814;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
笛卡儿乘积:多表交叉连接,假如表1有4行,表2有5行,则:select * from 表1,表2 的结果是20条记录。
内部连接是表中行与行之间的连接,产生的新行数目等于各表行数乘积,WHERE子句的结果肯定在这些行中;
外部连接显示一个表中的所有数据行,即使匹配的数据并不在连接表中。 ((LEFT )OUT JOIN...ON...) 默认是外部的右连接,返回右侧表的全集。
select * from tb_material a,tl_productstockpilen b where b.c_materialname(+)=a.c_materialname;
(+)在哪个旁边则表示的是哪一个的外连接,以哪一个为基准。
自连接解决找上司问题:
select e1.name,e2.name from emp e1,emp e2 where e1.manager_id=e2.id;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
exists---当子查询结果不为空时返回TRUE,否则为FALSE
select t.* from tl_productstockpilen t where exists (select e.* from tl_productinstockn e where e.c_sign='true');
在子句中加上两个表相关的查询条件,可避免返回所有的数据集,如在上句后头加上:
and e.c_materialid=t.c_materialid ,将只返回e表中也同时有的物资的记录。
子查询只查一列与查询所有列的效果是一样的,所以可以只查询第一列提高子查询的性能,如:
select t.* from tl_productstockpilen t where exists (select 1 from tl_productinstockn e where e.c_sign='False' and e.c_materialid=t.c_materialid );
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
any ----some -----all 用法:
select t.* from tl_productstockpilen t where t.c_isfreeze=any (select e.c_isfreeze from tl_productstockpilen e where e.c_isfreeze='质检');
可见,三者与IN的用法有相似之处
但是,IN 类似于多个等式,而ANY/SOME/ALL可用于其他关系运算符,如大于、小于:
select t.* from tl_productstockpilen t where t.c_isfreeze<>all (select e.c_isfreeze from tl_productstockpilen e where e.c_isfreeze='质检');
上式返回的是所有不为质检的记录。
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
去小数函数:trunc(列名)
select t.N_ACCOUNTWEIGHT,TRUNC(T.N_ACCOUNTWEIGHT) AA from tl_productstockpilen@jh_dblink t ;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
months_between(参数1,参数2)---返回两个给定日期的月数
select months_between(t.d_time,next_day(t.d_time,6)) AA from tl_productstockpilen@jh_dblink t;
next_day(列名,阿拉伯数字)-----以指定格式返回给定时间的下一天是星期几
select t.d_time,next_day(t.d_time,6) AA from tl_productstockpilen@jh_dblink t;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
将日期格式转换成字符格式:to_char(列名,'格式')
select t.d_time,to_char(t.d_time,'mm-dd-yyyy') AA from tl_productstockpilen@jh_dblink t;
将字符串转换成日期格式:to_date(列名,'列名字符格式')
select t.d_time,to_date(to_char(t.d_time,'mm-dd-yyyy'),'MM-dd-yyyy') AA from tl_productstockpilen@jh_dblink t;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
查看自己的用户信息:
select * from user_users;
查看自己的权限:
select * from user_sys_privs;
查看自己的角色信息:
select * from user_role_privs;
查看所有用户:
select * from all_users;
查看你所拥有的表和相关对象的清单:
select * from user_catalog;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
自动产生SQL语句并保存到一个文件中:
spool name.sql;
select 'select count(*) from '||table_name||';' from cat;
这两个语句将产生查询所有表的行数的SQL语句:select count(*) from tablename ,并将这些SQL语句保存到C:/Program Files/PLSQL Developer/name.sql 文件中。
执行:start name.sql
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
给多个用户授予系统权限:
spool grants.sql;
select 'grant connect,resource to '||username||';' from sys.dba_users where username not in ('sys','system','scott','ryan','po7','demo');
执行:start grants.sql
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
为一组表创建视图:
spool views.sql;
select 'create view '||table_name||'_view as select * from '||table_name||';' from cat;
执行:start views.sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
截断一用户的所有表
spool trunc.sql;
select 'truncate table '||table_name||';' from all_tables where owner = 'ryan';
执行:start trunc.sql;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
nvl(字段A,字符值)----如果字段A的值为空,则用字符值替代
select c_id,nvl(c_pare1,'zjr') pare1,nvl(c_batchid,'zjr1') batchid from tl_productinstockh;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
从表A中取出第N条到第M条的记录(id为表A的关键字)
select top m-n+1 * from A where id not in (select top n-1 id from A);
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
当数据集DS中记录改变时的数据集
DataSet dsChange=ds.GetChanges();