1 / 32

Anita Philipp – Spring 2010

SELECT WHERE INSERT DELETE. Structured Query Language MySQL~PHP. Anita Philipp – Spring 2010. Objectives. Study the basics of databases and MySQL Work with MySQL databases Define database tables Modify user privileges Work with database records Work with phpMyAdmin.

willis
Télécharger la présentation

Anita Philipp – Spring 2010

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. SELECT WHERE INSERT DELETE Structured Query Language MySQL~PHP Anita Philipp – Spring 2010

  2. Objectives • Study the basics of databases and MySQL • Work with MySQL databases • Define database tables • Modify user privileges • Work with database records • Work with phpMyAdmin

  3. Introduction to Databases • Database • Ordered collection of information • Can quickly access information • Each row in a database table is called a record • Record • Single complete set of related information - Row • Fields • Columns in a database table • Individual categories of information stored in a record

  4. Introduction to Databases • Flat-File Database • Stores information in a single table • Relational Database • Stores information across multiple related tables

  5. Understanding Relational Databases • Primary Table • main table in a relationship that is referenced by another table • Related Table (or “child table”) • references a primary table in a relational database • Primary Key • field that contains a unique identifier for each record in a primary table • Foreign Key • field in a related table that refers to primary key in primary table • link records across multiple tables

  6. One-to-One Relationships • Related table - one record for each record in the primary table • Breaks information into multiple, logical sets • Could be placed within a single table • Make one table confidential and accessible only by certain individuals

  7. One-to-Many Relationship • One-to-Many Relationship • one record in a primary table has many related records in a related table • Normalization • multiple related tables to reduce redundant and duplicate information

  8. One-to-Many Relationship Figure 7-3 Table with Redundant Information Figure 7-4 One-to-Many Relationship

  9. Many-to-Many Relationship Junction Table • Sometimes called “cross-reference” table • Creates a one-to-many relationship for each of the two tables in a many-to-many relationship • Compound key: employee_id and language_id • Number of years each programmer has worked with each language Figure 7-5 Many-to-Many Relationship

  10. Working with Database Management Systems • DataBase Management System (DBMS) • Application or collection of applications used to access and manage a database • Schema • Structure of a database including its tables, fields, and relationships • Flat-File Database Management System • Stores data in a flat-file format (text file, one record per line) • Sometimes refers to a one table database • Relational DataBase Management System (RDBMS) • Stores data in a relational format

  11. Working with DBMS • Query • Structured set of instructions and criteria for retrieving, adding, modifying, and deleting • Structured Query Language (SQL) • Data manipulation language • Open DataBase Connectivity (ODBC) • Allows ODBC-compliant applications to access any data source for which there is an ODBC driver

  12. SQL-Structured Query Language SQL • Most widely used language for defining and manipulating relational databases 1. Create new databases 2. Add/modify tables 3. Search for Data 4. Build new tables from existing ones 5. Remove tables and data

  13. SQL Language SQL • Data Definition Language (DDL) …defining databases, tables, indexes • Data Manipulation Language (DML) …searching, retrieving, modifying, inserting, deleting, updating table data

  14. Data Manipulation Language SQL • Parameter Declarations • …optional parameters that can be passed to the query • Manipulative Statement • …Indicates the type of action to be taken • (INSERT, DELETE, SELECT, or UPDATE) • Options • …Conditions for selecting record, grouping, ordering, • totaling of records • (FROM, WHERE, GROUP BY, HAVING, ORDER BY • Aggregate Functions • …process multiple record maybe (subset of entire table) • (AVG, COUNT, SUM, MAX, MIN)

  15. SQL Two Types of Statements Selection Query Retrieves one or more rows Action Query Actions such as update, delete Does not retrieve any rows

  16. Selection Queries

  17. Selection Query - Format

  18. Retrieving Data – SELECT Statement • SELECT Field FROM TableName Display the price SELECT fldPrice FROM tblCars; Display year and model SELECT fldYear, fldModel FROM tblCars; Display all car information SELECT* FROM tblCars; *Fields are retrieved in the order they are in the SELECT Statement

  19. Sorting Data – ODER BY Statement SELECT Field FROM TableName ORDER BY Field [ASC | DESC] Display the Model and Price … least expensive to most expensive SELECT fldModel, fldPrice FROM tblCars ORDER BY fldPrice; • Display the Price and Model* … most expensive to least expensive • SELECT fldPrice, fldModel FROM tblCars ORDER BY fldPrice DESC; • Display all information sorting by Make in ascending order and Price in descending order • SELECT * FROM tblCars ORDER BY fldMake, fldPrice DESC; *Fields are retrieved in the order they are in the SELECT Statement

  20. Restricting Data – WHERE Statement SELECT Field FROM TableName WHERE Criteria • < <= > >= = <> IN BETWEEN LIKE NOT • WHERE fldPrice > 2000 • WHERE fldPrice BETWEEN 5000 AND 10000 (Includes 5000 and 10000) • WHERE fldYear = 2010(If type is Year ~ no quotes needed) • WHERE fldDates> ‘2010-04-15’(If type is Date ~ enclose in single quotes) • WHERE fldModel LIKE ‘E%’(String enclosed in single quotes LIKE is used for pattern matching)

  21. Restricting Data – WHERE Statement LIKE - Pattern-matching Operator % Matches multiple characters_ Matches a single character WHERE fldModelLIKE ‘H%’; …Honda, Hyundai WHERE fldMakeLIKE‘H_ _ _ _’ ; …Honda WHERE fldMake= ‘O\’Hara’; If apostrophe is in the name (O’Hara), a backslash is required

  22. Restricting Data – WHERE Statement SELECT Field FROM TableName WHERE Criteria Display the Model and Price … cars over 10000 SELECT fldModel, fldPrice FROM tblCars WHERE fldPrice > 10000; Display the Year and Model… cars made in 2010 SELECT fldYear, fldModel FROM tblCarsWHERE fldYear = 2010; Display all information for cars whose Model begins with S SELECT * FROM tblCars WHERE fldModel LIKE ‘S%’;

  23. Restricting Data - DISTINCT Shows only unique fields, avoids redundancy SELECT DISTINCTFieldFROM TableName List the makes of cars, do not include name more than once SELECT DISTINCT fldMake FROM tblCars; RESULTS: With DISTINCT: Honda, HyundaiWithout DISTINCT: Honda, Hyundai, Honda

  24. Restricting Data - LIMIT Limits the number of records returned SELECT FieldFROM TableName LIMIT StartValue, NumRows; SELECT fldMake FROM tblCars LIMIT 0, 2;RESULTS: Start with the first record and display 2 records

  25. ActionQueries

  26. Action Queries Insert, Change, Delete Records • Does not retrieve a row • Performs operations on data • Returns a value indicating that operation was successfully completed

  27. Action Query -INSERT INSERT INTO Table(Fld1, Fld2, Fld3) VALUES (Val1, Val2, Val3) • Insert 2007, Red, and 10000 • INSERT INTO tblCars (fldYear, fldColor, fldPrice) VALUES (2007, ‘Red’, 10000) • *If the field was defined as date type as opposed to year type, the format would be ‘2007-04-15’.

  28. Action Query - DELETE DELETE FROM Table WHERE Criteria;DELETE * FROM Table WHERE Criteria; • Delete all cars made prior to 2010 • DELETE FROM tblCars WHERE fldYear < 2010 • Delete all black cars • DELETE FROM tblCars WHERE fldColor = ‘Black’;

  29. Action Query- UPDATE UPDATE Table SET fld1= NewValue, fld2=NewValue, etc.WHERE Criteria; • Increase the cost of all cars by 10% • UPDATE tblCarsSET fldPrice = fldPrice * 1.10; • Change the color of all red cars to maroonUPDATE tblCars SET fldColor = ‘maroon’ WHERE fldcolor = ‘red’ ;

  30. Variables in Queries Display Make $carMake = $_POST[“car_make"]; $SQLQuery = “SELECT fldMake FROM tblCars WHERE fldMake= ‘$carMake’ ”; $SQLQuery = “SELECT fldMake FROM tblCarsWHERE fldMake= ‘$_POST[“car_make"]’ ”; Insert a new record into the table $SQLQuery = “INSERT INTO tblCars (fldMake, fldModel, fldYear, fldColor, fldPrice) VALUES (‘$carMake’, ‘$carModel’, ‘$carYear’, ‘$carColor’, ‘$carPrice’)”;

  31. Primary Keys: Auto Increment If the Primary key is an auto-incremented field, use NULL for the insert value $SQLQuery = “INSERT INTOtblCars (fldID, fldMake, fldModel, fldYear, fldColor, fldPrice) VALUES (NULL,‘$carMake’, ‘$carModel’, ‘$carYear’, ‘$carColor’, ‘$carPrice’)”;

  32. TheEnd

More Related