1 / 13

Structured Query Language

Structured Query Language. Also known as SQL Industry standard for accessing relational databases General format of SQL Select Select [Distinct] fieldlist From tablenames [Where search conditions] [Group By fieldlist] [Having group criterion] [Order By fieldlist [ASC|DESC]] Examples

asasia
Download Presentation

Structured Query Language

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Structured Query Language • Also known as SQL • Industry standard for accessing relational databases • General format of SQL Select Select [Distinct] fieldlist From tablenames [Where search conditions] [Group By fieldlist] [Having group criterion] [Order By fieldlist [ASC|DESC]] • Examples Select * From ContactsSelect * From Contacts, ContactTypes Where Contacts.ContactTypeID = ContactTypes.ContactTypeID Order By LastName

  2. Creating a new Dynaset • Steps to define new dynaset via data control 1) Set data control’s RecordSource property to SQL query 2) Re-open recordset using Refresh method • Example stSQL = “Select * From Contacts, ContactTypes Where “ _ & “Contacts.ContactTypeID = ContactTypes.ContactTypeID ” datBooks.RecordSource = stSQL datBooks.Refresh

  3. Database Objects • Data Access Objects (DAO) • Uses Jet DB engine to access databases • Remote Data Objects (RDO) • Used when developing client/server application that access remote ODBC database system • Requires Enterprise edition of VB & 32-bit OS • ActiveX Data Objects (ADO) • New model with VB 6.0 • Combines best features of DAO and ADO • Not limited to relational DBs • Requires OLE database driver to use with Jet DB

  4. DAO Model

  5. Data Control vs. DAO Notation

  6. Options with SQL Select • Like operator • Relational operator used to find similar matches • Expression being compared is surrounded by single quotes and normally contains * wildcard • FirstName Like ‘A*’ • returns all records whose FirstName values start with A • Aggregate Queries • Used to get summary-type information • Define new field to hold the result from an aggregate function • Inner Joins

  7. Aggregate Queries SELECT AggFunction(fieldname) As newdbfield FROM tablename [WHERE searchcriteria]

  8. Inner Joins for Multiple Tables SELECT [DISTINCT] fieldlist FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2 [WHERE searchcriteria][ORDER BY fieldname] • Example Select * From Contacts INNER JOIN ContactTypes On Contacts.ContactTypeID = ContactTypes.ContactTypeID Order By LastName

  9. Modifying DB Records • Define action query • INSERT INTO • UPDATE • DELETE • Use Execute method • Call DBObject.Execute(stSQL)

  10. Example: Add a new record Private Sub WriteToDB() Dim SQL As String SQL = "INSERT INTO Employee " SQL = SQL & "(EmpName, BirthDate, PayRate, Manager)" SQL = SQL & " VALUES (" SQL = SQL & DBTextFmt(txtEmpName.Text) & ", " SQL = SQL & CStr(DBDateFmt(txtBirthdate)) & ", " SQL = SQL & txtPayRate.Text & ", " SQL = SQL & txtMgr.Text & ")" Call fEmpDB.Execute(SQL) ' Enable navigation buttons cmdFirst.Enabled = True cmdLast.Enabled = True cmdNext.Enabled = True cmdPrevious.Enabled = True End Sub

  11. Example: Delete a record Private Sub cmdDelete_Click() Dim SQL As String SQL = "DELETE FROM Employee Where EmpID = " & _ fEmpRS("EmpID") Call fEmpDB.Execute(SQL) Call fEmpRS.Close Call fEmpDB.Close Call ReOpenDB End Sub

  12. Example: Create New Database Option Explicit Private fUserWS As Workspace Private fNewDB As Database Private Sub CreateDB() Dim FileName As String Set fUserWS = DBEngine.Workspace(0) FileName = AppendFileToPath(App.Path, “NewTestDB.mdb”) If FileExists(FileName) Then If MsgBox(“The database exists. Do you want to replace it?”, _ vbYesNo + vbQuestion) = vbYes Then Kill FileName Else Exit Sub ' Do not create the DB-already exists End If End If Set fNewDB = fUserWS.CreateDatabase(FileName, dbLangGeneral) End Sub

  13. Example: Create Database Table ‘ Create table SQL = "CREATE TABLE Employee (" & _ "EmpID COUNTER, " & _ "EmpName TEXT(30), " & _ "BirthDate DATETIME, " & _ "PayRate CURRENCY, " & _ "Manager BIT)" Call fNewDB.Execute(SQL) ‘ Create index to improve performance SQL = "CREATE UNIQUE INDEX indEmpPrimary" SQL = SQL & " ON Employee (EmpID)" SQL = SQL & " WITH PRIMARY" Call fDBPaychecks.Execute(SQL)

More Related