SQL

使用Visual Studio 2012連接SQL資料庫

模組 Pub_Module.vb

Public DBSchoolcnStr As String = "data source=.\SQL2008;initial catalog=school;User id=xxxxxx;Password=??????;"

程式內連接 Access 資料庫

Dim con As SqlClient.SqlConnection = New SqlClient.SqlConnection(DBSchoolcnStr)

Dim com As String = "select * from tableName"

Dim adtp As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(com, con)

Dim dt As DataTable = New DataTable()

con.Open()

adtp.Fill(dt)

For i=0 To dt.rows.count() - 1

變數名稱 = dt.rows(i).item("欄位名稱")

Next For

con.Close()

adtp.Dispose()

文獻參考：W3school：https://www.w3schools.com/sql/default.asp

常用摘要

Select 篩選

SELECT WHERE 紀錄篩選

SELECT column1, column2, ...

FROM table_name

WHERE condition; 

JOIN 關聯：基於相同欄位名稱，用於連結兩個或以上的資料表。

SELECT table1.column_name1, table2.column_name1, table1.column_name2

FROM Orders

INNER JOIN table2 ON table1.column_name1 = table2.column_name1; 

INNER JOIN: table1與table2內具有相同的值才會被篩選出來。。

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

或

USING (column_name);

Inner Join Reference: https://www.w3schools.com/sql/sql_join_inner.asp

LEFT JOIN: 所有table1+table2有相同的值才會被篩選出來。

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name = table2.column_name;

https://www.w3schools.com/sql/sql_join_left.asp

UPDATE INNER JOIN: 資料表SignIn關連到資料表Student，更新SignIn資料表CalssID, Seat兩個欄位內容。

update SignIN A Inner Join Student B On A.StudentID = B.StudentID SET A.ClassID=B.ClassID, A.Seat=B.Seat