数据库编程总结

 数据库编程中，由于数据库类型不同和系统编译环境多样，相同功能的语句稍有差异，常常记不了那么清楚，今天做写这篇文章一为总结，二为备忘。

一.连接语句

SQL Server

ConnectionString = "integrated security=SSPI;data source=(local);persist security info=False;initial catalog=[数据库名称]"

ConnectionString = "driver=SQL Server;DATABASE=[数据库名称];UID=sa;PWD=;SERVER=[服务器名称]"

Access

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=[路径+*.mdb];UserID=[ ];Password=[ ]";

ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source="+Server.MapPath("*.mdb")

PostgreSQL

ConnectionString = "Server= [服务器IP] ;Port= [端口号] ;User Id= [用户名] ;Password= [密码] ;Database= [数据库名称] ;Encoding= UNICODE;"

二.数据库操作过程

C# + SQL Server

   SqlConnection conn = new SqlConnection ();  //连接对象实例化
   
   conn.ConnectionString = "integrated security=SSPI;data source=(local);" + "persist security info=False;initial catalog=seek";  //设定连接字符串
       
   string sqlstr = "select * from expert";   //设定查询语句

   SqlCommand  comm = new SqlCommand(sqlstr,conn);   //查询命令实例化
   
   DataSet dataset = new DataSet();

   SqlDataAdapter adapter = new SqlDataAdapter(comm.CommandText,conn.ConnectionString);

   conn.Open();

   adapter.Fill(dataset);

   datagrid.SetDataBinding(dataset,"Table");

   conn.Close();

 vb+access

Dim ConnectString As String
ConnectString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & strAppPath & ";PWD=123"
Dim sqlstr As String
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
sqlstr = "select * from myTable"
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.Open ConnectString
rst.Open Trim$(sqlstr), cnn, adOpenKeyset, adLockOptimistic
Set rst = Nothing
Set cnn = Nothing

Delphi+SQL Server

 connection := TADOConnection.Create(nil);
 connection.ConnectionString := 'Provider=SQLOLEDB.1;User ID=sa;Password=;Initial Catalog=myDB;Data Source=10.0.0.1';

 query := TADOQuery.Create(Self);
 query.Connection:=connection;
 datasource := TDataSource.Create(nil);
 datasource.DataSet := query;
 dbGrid.DataSource := datasource;
 
 query.Close;
 query.SQL.Clear;
 query.SQL.Text:='select * from myTable where '''+good+'''='''+numstr+'''';
 query.ExecSQL;
 dbGrid.Refresh;
 query.close

三.SQL语句

 基本结构

SELECT语法：

SELECT [DISTINCT]
(column [{, column } ] )| *
FROM table [ { , table} ]
[ORDER BY column [ASC] | [DESC
[{ , column [ASC] | [DESC } ] ]
WHERE predicate [ { logical-connector predicate } ];
------------------------------------------------------

INSERT语法：

INSERT INTO table
[(column { ,column})]
VALUES
(columnvalue [{,columnvalue}]);
------------------------------------------------------

UPDATE语法：

UPDATE table
SET column = value [{, column = value}]
[ WHERE predicate [ { logical-connector predicate}]];
------------------------------------------------------

DELETE语法：

DELETE FROM table
[WHERE predicate [ { logical-connector predicate} ] ];

几个关于SQL语法的网页

http://oracle.chinaitlab.com/PLSQL/37362.html

http://www.njqxcj.com/dnpx/sql.htm

http://cgzcgb.bokee.com/2804850.html