1 / 36

Queries

Queries. How do we ask questions of the data? What is SELECT? What is FROM? What is WHERE? What is a calculated field?. What Is a Query?. A query is a question you ask of your database. You can: display data from multiple tables control which fields display

kaye-carrillo
Télécharger la présentation

Queries

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. Queries How do we ask questions of the data? What is SELECT? What is FROM? What is WHERE? What is a calculated field?

  2. What Is a Query? • A query is a question you ask of your database. You can: • display data from multiple tables • control which fields display • perform calculations on field values • save a query automatically CS 105 Fall 2007

  3. Sample query Note we have to capitalize the table names CS 105 Fall 2007

  4. The results of a queryare displayed as aresult set (NOT SAVED AUTOMATICALLY, COULD CHANGE THE NEXT TIME IT IS RUN)—called recordset in some programming situations. Select Query also called a Result Set CS 105 Fall 2007

  5. Query Types & Descriptions • Select query-- most common query Retrieves and displays specific data requested from one or more tables; can specify display order—let’s try some: • Action queries do mass record updates in one operation. Types: Update Query: alter the data in a Table Append Query: adds records from one table to another table Delete Query: deletes certain records, for example <1980 Make Table Query: creates a new Table from a query’s results. CS 105 Fall 2007

  6. Reserved or Key Words • Select * fromCustomers • Select, *, and from are reserved (key) words and symbols that have special meaning in SQL • SQLyog displays key words and symbols in blue. • After the word from, if SQL sees the word Customers, it assumes it's a name of a table • (see appendix E for a complete list of reserved or key words). CS 105 Fall 2007

  7. Strings wherecust_name= “Village Toys” • After the key wordwhere,SQL assumesthat the wordcust_nameis the name of a field (column) “Village Toys” is a string • A string is a sequenceof ASCII symbols surrounded by singleor doublequotes that denotes a value (piece of information) CS 105 Fall 2007

  8. Logical operators AND condition (happy only with both) OR condition (happy with either one) NOT condition (happy with anything but this one) For NOT, must be NOT (name = ‘smith’) Select * from Pets where not( name = "Hortence“ ) CS 105 Fall 2007

  9. Relational Criteria, as inWhereLastName =“Smith” See Lesson 4 in SAMS book = > < (equal to, greater than, less than) <= >= < > Not equal to LIKE Pattern matching operator BETWEEN IS NULL CS 105 Fall 2007

  10. Examples of Relational Query Criteria • LIKE‘Smith’ Age between 21 and 65 (Age >=21 And Age <=65 ) <=98000returns values of less than or equal to 98000 Not (firstname= ‘Smith’) Select records with values other than Smith CS 105 Fall 2007

  11. Example: Do not do this!!!! Select * from Customers where firstname ="Harry“ or “Fay” • Note: Repeatfield firstnameeach timebecause—this is the worst part—the Query will RUN but the results will be WRONG! CS 105 Fall 2007

  12. Order of precedence: • When more than one logical operator is used in a query, NOT is evaluated first, then AND, and finally OR • To make sure the statement is read the way you want it to be, use parentheses, because any statement within parentheses is evaluated first! CS 105 Fall 2007

  13. Example: select * from Movies where Rating < 5 or Rating > 8.5 and year > 2000 select * from Movies where (Rating < 5 or Rating > 8.5) and year > 2000 CS 105 Fall 2007

  14. Multiple Tables • Two tables can be linked by a common field • Why would we use several tables rather than one big one? • How can you link one table to another? Customers Billing CS 105 Fall 2007

  15. Tables relating to other tables via fields CS 105 Fall 2007

  16. Join using Where -- Lesson 13, in SAMs • When you are looking for data from two tables, you want to limit your “hits” to records that match • You relate one table to another by a common field • You want a single set of output is returned, and the join associates the correct rows in each table on the fly • Oops—what does “on the fly” mean? (not a permanent relationship) CS 105 Fall 2007

  17. A simple WHERE join • You do not need to specify Products.prod_name because Vendors does not have a field named prod_name CS 105 Fall 2007

  18. What happens if you don’t use the Where join? Case Sensitive For Table Names • Every vendor is listed with every product, so total: 9 * 6 = 54 records (!!!) CS 105 Fall 2007

  19. You get too many hits!! CS 105 Fall 2007

  20. Order of statements in a Select Query See Lesson 5 in Sams • The Where clause comes after the From clause • The Order By clause must be last, or you will get an error message CS 105 Fall 2007

  21. Fields that you show aren’t necessarily the ones that you use for the join or filter… CS 105 Fall 2007

  22. Note: if field names are duplicated in various tables, refer to fields specifically (note the table that they are from) CS 105 Fall 2007

  23. In ascending order, NULL fields are at the top CS 105 Fall 2007

  24. Wildcards The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. In another database, the wildcard might be different (such as*) select FirstName, Lastname, City from Employee where FirstName LIKE “ Mi% “; This SQL statement will match any first names that start with ‘Mi'. . CS 105 Fall 2007

  25. More Wildcard Characters Lesson 6 in Sams Underscore: _ means that a character must be present Like ‘_oat’ returns boat but not oat Note: From or from both work fine CS 105 Fall 2007

  26. Case sensitivity • MySQL is running on UNIX, therefore it is case sensitive • However, inside a search string, so far we are finding that case doesn’t matter—both these work: CS 105 Fall 2007

  27. Calculated field – Lesson 7 in Sams ASsets up analias for a calculated value CS 105 Fall 2007

  28. Another calculated field: CS 105 Fall 2007

  29. Another Alias field …before: CS 105 Fall 2007

  30. How do we make it look attractive? Instead of • Detroit MI 44444 Why not • Detroit, MI 44444 CS 105 Fall 2007

  31. Joining Words Together:Concatenation • Concatenation isputting two words together • Concatenation can be done with a function that takes two or more arguments separated by commas In SQL it works like this: Concat(field one,field two) CS 105 Fall 2007

  32. However, it could look ugly concat(cust_city,cust_state,cust_zip) DetroitMI44444 How do we add a space? How do we insert a comma? CS 105 Fall 2007

  33. After: • SQLyog uses the CONCAT function – creates an Alias field by putting 3 columns together use Concat function rather than material shown in SAMs CS 105 Fall 2007

  34. Commenting your SQL code • You start comment lines with /* and end with */ CS 105 Fall 2007

  35. Remark or Comment Statement in SQL, Excel, VBA • Used for documentation. • Not “executable” • SQLyog uses green font • Most of the work you do out in the workplace is maintaining code--code without comments is unusable, and you have to start from scratch (!) CS 105 Fall 2007

  36. To Summarize: • How do we ask questions of the data? • What is SELECT? • What is FROM? • What is WHERE? • What is a calculated field? • Your moment of Zen http://www.youtube.com/watch?v=KERwnA8VfFM CS 105 Fall 2007

More Related