1 / 41

Chapter 22 - SQL, MySQL, DBI and ADO

Chapter 22 - SQL, MySQL, DBI and ADO. Outline 22.1 Introduction 22.2 Relational Database Model 22.3 Relational Database Overview 22.4 Structured Query Language 22.4.1 Basic SELECT Query 22.4.2 WHERE Clause 22.4.3 GROUP BY Clause 22.4.4 ORDER BY Clause

marius
Download Presentation

Chapter 22 - SQL, MySQL, DBI and ADO

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. Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.1 Introduction 22.2 Relational Database Model 22.3 Relational Database Overview 22.4 Structured Query Language 22.4.1 Basic SELECT Query 22.4.2 WHERE Clause 22.4.3 GROUPBY Clause 22.4.4 ORDERBY Clause 22.4.5 Merging Data from Multiple Tables 22.4.6 Inserting a Record 22.4.7 Updating a Record 22.4.8 DELETEFROM Statement 22.4.9 TitleAuthor Query from Books.mdb 22.5 MySQL

  2. Chapter 22 - SQL, MySQL, DBI and ADO Outline 22.6 Introduction to DBI 22.6.1 Perl Database Interface 22.6.2 Python DB-API 22.6.3 PHP dbx module 22.7 ActiveX Data Objects (ADO) 22.8 Internet and World Wide Web Resources

  3. 22.1 Introduction • Database • Integrated collection of data • Database management system (DBMS) • Provides mechanisms for storing and organizing data • Allows users to access and store data without addressing internal representation of databases • Relational databases • Consist of data corresponding to one another • Most popular database systems in use • Uses Structured Query Language (SQL) to create queries • Examples: Oracle, MS SQL Server, MySQL, Informix

  4. 22.2 Relational Database Model • Relational database model • Logical representation of data allowing users to consider relationships between data • Consists of tables • Following figure illustrates Employee table • Might exist in personnel system • Table row called record • Table column called field • Number field is primary key • Contains unique data that cannot be duplicated • Identifies the record • Examples: social security number, employee ID number, etc.

  5. 22.2 Relational Database Model Fig. 22.1 Relational database structure.

  6. 22.2 Relational Database Model • SQL statements • Use to obtain table subsets • Complete set of keywords enable programmers to define complex queries • Results of query called result sets (or record sets) • Following table shows results of SQL query (Fig. 22.2) • Provides geographical location of several departments Fig. 22.2 Result set formed by selecting data from a table.

  7. Number Name Department Salary 23603 Jones 413 1100 24568 Kerwin 413 2000 Row/Record 34589 Larson 642 1800 35761 Myers 611 1400 47132 Neumann 413 9000 78321 Stephens 611 8500 Primary key Column/Field 22.2 Relational Database Model • Information would be better represented in two tables:

  8. 22.3 Relational Database Overview • Overview SQL using Books.mdb database • Consists of four tables • Authors, Publishers, AuthorISBN and Tables • Primary key fields in italics • Authors table (Figs. 22.3 and 22.4) • Consists of four fields • Unique ID number, first name, last name and year of birth • Contains null value for YearBorn field • Not primary key, so can contain null values • FirstName and LastName can contain null values also

  9. 22.3 Relational Database Overview

  10. 22.3 Relational Database Overview • Publishers table (Figs. 22.5 and 22.6) • Consists of two fields • Unique ID and publisher name

  11. 22.3 Relational Database Overview • Titles table (Figs. 22.7 and 22.8) • Consists of six fields • ISBN number, title, edition number, year published, book description and publisher ID number

  12. 22.3 Relational Database Overview

  13. 22.3 Relational Database Overview • AuthorISBN table (Figs. 22.9 and 22.10) • Consists of two fields • ISBN number and author ID number • Links names of authors with respective book titles

  14. 22.3 Relational Database Overview

  15. 22.3 Relational Database Overview • Microsoft Access diagram (Fig. 22.11) • Illustrates relationships between tables in database • Lines represent table relationships • One-to-many relationship • Example: line between Publishers and Titles tables • Single publisher can have many books in Titles table

  16. 22.3 Relational Database Overview Fig. 22.11 Table relationships in Books.mdb.

  17. 22.3 Relational Database Overview • Rule of Entity Integrity • Every record must have value in primary key field • Primary key values must be unique • Foreign key field (or constraints) • References primary key field in another table • Specified when creating tables • Maintains Rule of Referential Integrity • Every foreign key field value must appear in another table’s primary key field • Example: PublisherID field in Titles table • Enables information from multiple tables to be joined for analysis

  18. 22.4 Structured Query Language • Overview SQL using Books.mdb database • Following table lists some SQL keywords

  19. 22.4.1 Basic SELECT Query • Extracts information from one or more tables • Simplest form • SELECT*FROMTableName • Asterisk (*) notifies query to select all rows and columns from table • TableName specifies a table in database • Example: SELECT*FROMAuthors • Selecting specific fields • Replace asterisk (*) with field names • Example: SELECTAuthorID, LastNameFROMAuthors

  20. 22.4.1 Basic SELECT Query

  21. 22.4.2 WHERE Clause • Optional clause in SELECT query • Selects records satisfying selection criteria • Basic form • SELECTfieldName1, fieldName2FROMTableNameWHEREcriteria • Example: SELECT*FROMAuthorsWHEREYearBorn>1960 • Result set contains two authors born after 1960

  22. 22.4.2 WHERE Clause • Can contain operators • <, >, <=, >=, =, <> and LIKE • LIKE operator • Performs pattern matching with wildcard characters asterisk (*) and question mark (?) • Pattern matching allows SQL to search for particular string or string of characters • Asterisk (*) indicates string can have zero or more characters at it’s position • Example: SELECTAuthorID, FirstName, LastName, YearBornFROMAuthorsWHERELastNameLIKE‘D*’ • Result set contains two records

  23. 22.4.2 WHERE Clause

  24. 22.4.2 WHERE Clause • LIKE operator, cont. • Question mark (?) indicates single character can occupy it’s position • Example: SELECTAuthorID, FirstName, LastName, YearBornFROMAuthorsWHERELastNameLIKE‘?i*’ • Result set contains one author

  25. 22.4.2 WHERE Clause • Specifying range of characters • Use [startValue-endValue] • startValue is first character in range • endValue is last character in range

  26. 22.4.3 GROUPBY Clause • Groups result set by a particular column • Basic form • SELECTfieldName, COUNT(*) FROMTableNameGROUPBYfieldName • COUNT returns number of records selected by query • Example: SELECTAuthorID, COUNT (*) ASCountFROMAuthorISBNGROUPBYAuthorID • COUNT (*) ASCount assigns name to column that contains total count values

  27. 22.4.3 GROUPBY Clause • Combining WHERE and GROUPBY clauses • Example: SELECTAuthorID, COUNT(*) ASCountFROMAuthorISBNWHEREAuthorID<=3GROUPBYAuthorID • Result set contains three records

  28. 22.4.4 ORDERBY Clause • Sorts result set by given criteria • In ascending order (ASC) or descending order (DESC) • Basic form • SELECTfieldName1, fieldName2FROMTableNameORDERBYfieldNameASC • Can replace ASC with DESC • Example: SELECTAuthorID, FirstName, LastName,YearBornFROMAuthorsORDERBYLastNameASC

  29. 22.4.4 ORDERBY Clause • Basic form, cont. • To obtain same list in descending order • Example: SELECTAuthorID, FirstName, LastName,YearBornFROMAuthorsORDERBYLastNameDESC

  30. 22.4.4 ORDERBY Clause • Sorts multiple fields • Example: SELECTAuthorID, FirstName, LastName,YearBornFROMAuthorsORDERBYLastName, FirstName • ASC keyword is default sorting order, so is optional • First sorts by last name, then by first name

  31. 22.4.4 ORDERBY Clause • Combining WHERE and ORDERBY clauses • Example: SELECTISBN, Title, EditionNumber,YearPublished, PublisherIDFROMTitlesWHERETitleLIKE‘*HowtoProgram’ORDERBYTitleASC • Selects titles ending in “How to Program” • Sorts them in ascending order

  32. 22.4.4 ORDERBY Clause

  33. 22.4.5 Merging Data from Multiple Tables • May need to combine data from different tables • Merging data = joining tables • Combines records from different tables • Extracts records common to tables • Basic form • SELECTfieldName1, fieldName2FROMTableName1, TableName2WHERETableName1.fieldName = TableName2.fieldName • WHERE clause specifies fields to be compared from each table • Normally represent primary key field in one table and foreign key field in other table

  34. 22.4.5 Merging Data from Multiple Tables • Basic Form, cont. • Example: SELECTFirstName, LastName, ISBNFROMAuthors, AuthorISBNWHEREAuthors.AuthorID=AuthorISBN.AuthorIDORDERBYLastName, FirstName • Merges FirstName and LastName fields from Authors table with ISBN field from AuthorISBN table • Result set contains list of authors and ISBN numbers of books each author wrote • Sorts in ascending order by LastName and FirstName

  35. 22.4.5 Merging Data from Multiple Tables

  36. 22.4.5 Merging Data from Multiple Tables • Fully qualified name • TableName.fieldName specifies fields to be compared • Fields with same name in tables require “TableName.” syntax • Cross database queries require database name before TableName

  37. 22.4.6 Inserting a Record • Use INSERTINTO operation • Basic Form • INSERTINTO TableName (fieldName1, fieldName2)VALUES (value1, value2) • TableName specifies table that receives new records • Comma-separated list of field names specify the fields of TableName • Specifies columns that receive new records • VALUES specifies data to be inserted into table • Example: INSERTINTOAuthors (FirstName,LastName, YearBorn)VALUES (‘Sue’, ‘Smith’, 1960) • Inserts three values into three columns of Authors table

  38. 22.4.6 Inserting a Record

  39. 22.4.7 Updating a Record • Modifies data in tables (updates records) • Basic form: • UPDATETableNameSETfieldName = value1WHEREcriteria • SET assigns values to certain fields • Example: UPDATEAuthorsSETYearBorn=‘1969’WHERELastName=‘Deitel’ANDFirstName=‘Paul’ • AND states all components of selection criteria must be satisfied • Can replace WHERE clause with WHEREAuthorID=2

  40. 22.4.7 Updating a Record

  41. 22.4.8 DELETEFROM Statement • Removes data from tables • Basic form • DELETEFROMTableNameWHEREcriteria • Example: DELETEFROMAuthorsWHERELastName=‘Smith’ANDFirstName=‘Sue’ • Can replace WHERE clause with WHEREAuthorID=5

More Related