数据库编程总结
一.连接语句
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
