1 / 21

Using Relational Databases and SQL

Steven Emory Department of Computer Science California State University, Los Angeles. Using Relational Databases and SQL. Lecture 9: Data Manipulation Language. Miscellany. School is almost over Last 3 lectures are pretty relaxing DML (How to update and modify a database)

calix
Download Presentation

Using Relational Databases and SQL

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. Steven Emory Department of Computer Science California State University, Los Angeles Using Relational Databases and SQL Lecture 9: Data Manipulation Language

  2. Miscellany • School is almost over • Last 3 lectures are pretty relaxing • DML (How to update and modify a database) • DDL (How to create a database)‏ • Database Design (How to design a database)‏

  3. Topics for Today • Inserting Records (Pages 164 – 167)‏ • Deleting Records (Pages 167 – 168)‏ • Updating Records (Pages 168 – 173)‏ • Transactions (Pages 173 – 179)‏

  4. Before We Start • When inserting and deleting data, you are going to mess up because nobody is perfect • If you mess up there are two ways to restore the original database: • Remove and restore the tables • Use transactions (use BEGIN and ROLLBACK)‏ • I prefer using BEGIN and ROLLBACK

  5. Inserting Records • Two syntaxes: • INSERT INTO • Insert one record at a time • INSERT SELECT • Insert one or more records at a time

  6. Inserting Records • INSERT INTO Syntax • -- Form #1: Insert whole record.INSERT INTO tablenameVALUES(value1, value2, ..., valuen); • -- Form #2: Insert partial record.INSERT INTO tablename(field1, field2, ..., fieldn)VALUES(value1, value2, ..., valuen);

  7. Inserting Records • INSERT SELECT Syntax • -- Form #1: Insert whole record.INSERT INTO destination_tableSELECT field1, field2, ..., fieldnFROM source_tablesWHERE conditions; • -- Form #2: Insert partial record.INSERT INTO destination_table(df1, df2, ..., dfn)SELECT sf1, sf2, ..., sfnFROM source_tablesWHERE conditions;

  8. INSERT Examples • Examples: • Josh Scott Wagner was just hired by Lyric Music as a salesperson working under supervisor Bob Bentley. Base salaries for new recruits are set at $50.00. Add Josh to the database.INSERTINTO SalesPeopleVALUES(5, 'Josh', 'Wagner', 'jsw', 50.00, 1); • Note that we have to use 1 instead of using a subquery. MySQL doesn't allow INSERT INTO and a subquery selection from the same table.SELECT SalesID FROM SalesPeople WHEREFirstName = 'Bob' AND LastName = 'Bentley';

  9. INSERT Examples • Way around it is to use INSERT SELECT • INSERT INTO + subquery from same table = NO!INSERTINTO SalesPeopleVALUES(5, 'Josh', 'Wagner', 'jsw', 50.00,(SELECT SalesID FROM SalesPeople WHEREFirstName = 'Bob' AND LastName = 'Bentley')); • INSERT SELECT from same table = OK!INSERTINTO SalesPeopleSELECT 5, 'Josh', 'Wagner', 'jsw', 50.00, SalesIDFROM SalesPeople WHERE FirstName = 'Bob'AND LastName = 'Bentley';

  10. INSERT Examples • Example: • Bobby Crum, a member of 21 West Elm, has also decided to join The Neurotics. Update the table XrefArtistsMembers to reflect this. Bobby won't be the responsible member for the artist he his joining.INSERTINTO XrefArtistsMembersVALUES((SELECT ArtistID FROM Artists WHERE ArtistName = 'The Neurotics'), (SELECT MemberID FROM Members WHERE FirstName = 'Bobby' AND LastName = 'Crum'), 0);

  11. INSERT Examples • Example: • -- All members of 21 West Elm have decided to also become members of The Neurotics (as non-responsible members). Update the table XrefArtistsMembers to reflect this.INSERTINTO XrefArtistsMembersSELECT MemberID, (SELECT ArtistID FROM Artists WHERE ArtistName = 'The Neurotics'), 0FROM MembersJOIN XrefArtistsMembers USING(MemberID)JOIN Artists USING(ArtistID)WHERE ArtistName = '21 West Elm';

  12. Deleting Records • Deletes one or more rows from a table • Deletes all rows without WHERE condition • Single-Table DELETE Syntax • DELETEFROM tablenameWHERE conditions; • Multi-Table DELETE Syntax • Not in book • Complicated and ugly! • On next slide

  13. Deleting Records • Multi-table DELETE Syntax #1 (Preferred)‏ • DELETE T1, T2, ..., TnFROM T1 JOIN T2 JOIN ... JOIN TnWHERE conditions; • Note: If you use table alias in the FROM clause, you must use the alias in the DELETE clause as well (see examples later on). • Multi-table DELETE Syntax #2 (Ugly)‏ • DELETEFROM T1, T2, ..., TnUSING T1 JOIN T2 JOIN ... JOIN TnWHERE conditions;

  14. DELETE Examples • Examples: • -- Delete all rows from the Artists table.DELETE FROM Artists; • -- Delete all titles by The Neurotics (subquery).DELETEFROM TitlesWHERE ArtistID = (SELECT ArtistID FROMArtists WHERE ArtistName = 'The Neurotics'); • -- Delete all titles by The Neurotics (multi-table).DELETE T FROM Titles TJOIN Artists AWHERE A.ArtistID = T.ArtistID ANDA.ArtistName = 'The Neurotics';

  15. DELETE Examples • Examples: • -- Delete all titles and tracks by the The Neurotics.DELETE T, K FROM Artists A JOIN Titles T JOIN Tracks K WHERE A.ArtistID = T.ArtistID AND T.TitleID = K.TitleID AND A.ArtistName = 'The Neurotics'; • -- Delete The Neurotics from the Artists table and all titles and tracks by the them as well.DELETE A, T, K FROM Artists A JOIN Titles T JOIN Tracks K WHERE A.ArtistID = T.ArtistID AND T.TitleID = K.TitleID AND A.ArtistName = 'The Neurotics';

  16. Updating Records • To update existing records: • -- Single-table syntax.UPDATE tablenameSET field1 = value1, field2 = value2, ...WHERE conditions; • -- Multi-table equi-join syntax.UPDATE tablename1, tablename2, ...SET field1 = value1, field2 = value2, ...WHERE conditions; • -- Multi-table subquery syntax.UPDATE tablenameSET field1 = subquery1, field2 = subquery2, ...WHERE conditions;

  17. Updating Examples • Example: • -- The Bullets have decided to change their name to The Rockets. Update the database to reflect this change.UPDATE ArtistsSET ArtistName = 'The Rockets'WHERE ArtistName = 'The Bullets';

  18. Updating Examples • Example: • -- The Bullets have decided to change their name to The Rockets and update their web domain to www.therockets.com. Update the database to reflect these changes.UPDATE ArtistsSET ArtistName = 'The Rockets', WebAddress = 'www.therockets.com'WHERE ArtistName = 'The Bullets';

  19. Updating Examples • Example: • -- All members that had Bob Bentley as a sales contact will now have Scott Bull as a sales contact. Update the database to reflect these changes. Use the subquery syntax.UPDATE MembersSET SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Scott' AND LastName = 'Bull')WHERE SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Bob' AND LastName = 'Bentley');

  20. Updating Examples • Example: • -- All members that had Bob Bentley as a sales contact will now have Scott Bull as a sales contact. Update the database to reflect these changes. Use the join syntax.UPDATE Members M, SalesPeople SSET M.SalesID = (SELECT SalesID FROM SalesPeople WHERE FirstName = 'Scott' AND LastName = 'Bull')WHERE M.SalesID = S.SalesID AND S.FirstName = 'Bob' AND S.LastName = 'Bentley';

  21. Transactions • ACID • Atomicity • Consistency • Isolation • Durability • SQL Keywords • BEGIN/START TRANSACTION • COMMIT • ROLLBACK

More Related