Using Relational Databases and SQL

1
Using Relational Databases and SQL
Lecture 9 Data Manipulation Language

• Steven Emory
• Department of Computer Science
• California State University, Los Angeles

2
Data Manipulation Language

• DML for short
• Contains commands for modifying table data
• Insertion commands (INSERT INTO)
• Deletion commands (DELETE)
• Update commands (UPDATE)
• Not a query
• Queries extract data from the database
• Commands do not extract data from the database

3
Before We Start

• When modifying the database 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
• Use BEGIN before entering DML commands
• Use ROLLBACK to undo all changes
• USE COMMIT to accept all changes

4
Transactions

• ACID
• Atomicity
• Consistency
• Isolation
• Durability
• SQL Keywords
• BEGIN/START TRANSACTION
• COMMIT
• 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. Non-specified
  fieldnames are assigned default values.INSERT
  INTO tablename(field1, field2, ...,
  fieldn)VALUES(value1, value2, ..., valuen)
• IGNORE
• You can use the IGNORE keyword between INSERT and
  INTO to suppress duplicate error messages.

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. Non-specified
  fieldnames are assigned default values. INSERT
  INTO destination_table(df1, df2, ..., dfn)SELECT
  sf1, sf2, ..., sfnFROM source_tablesWHERE
  conditions

8
INSERT INTO Example

• Example
• Add Kung Fu Panda into the database.INSERT INTO
  MoviesVALUES(7, 'Kung Fu Panda', '2008-06-06',
  'G', 92, 'USA', 'English', (SELECT CompanyID FROM
  Companies WHERE Name 'Dreamworks Pictures'))

9
INSERT INTO and Subqueries

• As in the previous example, subqueries in the
  INSERT command work, but only if the update table
  and the subquery table are different.
• This rule only applies to MySQL, other database
  management systems may behave differently.

10
INSERT SELECT Example

• Example
• Associate all movies that have The X Files
  anywhere in the title with the romance genre.
  INSERT IGNORE INTO XRefGenresMoviesSELECT
  MovieID, 'Romance'FROM MoviesWHERE Title LIKE
  'The X Files'

11
Deleting Records

• Deletes one or more rows from a table
• Deletes all rows without WHERE condition
• Two syntaxes
• Single-Table DELETE Syntax
• Multi-Table DELETE Syntax

12
Single-Table DELETE Syntax

• Deletes one or more rows from a table
• Deletes all rows without WHERE condition
• Syntax
• DELETEFROM tablenameWHERE conditions

13
Single-Table DELETE Syntax

• Examples
• Delete all ratings.
• Delete all ratings by semory.

14
Single-Table DELETE Syntax

• Solutions
• -- Delete all ratings.DELETE FROM Ratings
• -- Delete all ratings by semory.DELETE FROM
  RatingsWHERE MemberID (SELECT MemberID FROM
  Members WHERE Username 'semory')

15
Multi-Table DELETE Syntax

• Deletes rows from multiple tables
• You must be very cautious or else you may delete
  something you didnt want to delete
• Syntax
• 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).

16
Multi-Table DELETE Syntax

• Examples
• -- Delete all ratings by semory (use multi-table
  delete syntax instead of using single-table
  delete syntax with a subquery).
• -- Delete all directors from the database (from
  both the People and XRefDirectorsMovies tables).

17
Multi-Table DELETE Syntax

• Examples
• -- Delete all ratings by semory.DELETE Ratings
  FROM Members JOIN Ratings USING(MemberID) WHERE
  Username 'semory'
• -- Delete all ratings by semory
  (alternate).DELETE RFROM Members A JOIN Ratings
  RUSING(MemberID) WHERE Username 'semory'
• -- Delete all directors from the database.DELETE
  P, DFROM People P JOIN XRefDirectorsMovies DON
  P.PersonID D.DirectorID

18
Multi-Table DELETE Syntax

• There is a big problem in the last example.
• Multi-table delete can lead to orphaned records
  if misused (a foreign key with no primary key).
• DELETE P, DFROM People P JOIN XRefDirectorsMovies
  DON P.PersonID D.DirectorID
• Jonathan Frakes is both an actor and a director.
• If we delete him from the People and
  XRefDirectorsMovies tables, we orphan him in the
  XRefActorsMovies and Spouses table!

19
Multi-Table DELETE Syntax

• Therefore, be careful what you delete!
• If you delete a record, and that record is
  referenced somewhere else, you have an orphaned
  record!
• Best solution is to only delete records from the
  XRefDirectorsMovies table and leave the People
  table alone since these people may be referenced
  somewhere else.
• DELETE FROM XRefDirectorsMovies

20
Updating Records

• To update existing records, you may use one of
  the following syntaxes
• Single-table syntax.
• Multi-table equi-join syntax.
• Only equi-join is supported.
• You may not use any other join syntax (JOIN ON,
  JOIN USING, etc.)

21
Updating Records

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

22
Updating Records

• You may use the IGNORE keyword immediately after
  UPDATE to ignore errors when an update produces
  duplicate primary keys.
• Example
• -- ERROR! BEEP!UPDATE MoviesSET MovieID
  1WHERE MovieID 2
• -- Error will be ignored!UPDATE IGNORE
  MoviesSET MovieID 1WHERE MovieID 2

23
Updating Records

• Examples
• -- The user ojisan has decided to change his
  username to uncle_steve. Update the database to
  reflect this change.
• -- The user colderstone has decided to change his
  username and password to jackstone and
  slsev0812z3, respectively. Update the database to
  reflect this change.
• -- Dreamworks Pictures went bankrupt and was
  bought out by Paramount Pictures. Modify all
  CompanyIDs in the Movies table to reflect this
  change.

24
Updating Records

• Examples
• -- The user ojisan has decided to change his
  username to uncle_steve. Update the database to
  reflect this change.UPDATE MembersSET Username
  'uncle_steve'WHERE Username 'ojisan'

25
Updating Records

• Examples
• -- The user colderstone has decided to change his
  username and password to jackstone and
  slsev0812z3, respectively. Update the database to
  reflect this change.UPDATE AccountsSET Username
  'jackstone', Password 'slsev0812z3' WHERE
  Username 'colderstone'

26
Updating Records

• Examples
• -- Dreamworks Pictures went bankrupt and was
  bought out by Paramount Pictures. Modify all
  CompanyIDs in the Movies table to reflect this
  change.UPDATE MoviesSET CompanyID (SELECT
  CompanyID FROM Companies WHERE Name 'Paramount
  Pictures')WHERE CompanyID (SELECT CompanyID
  FROM Companies WHERE Name 'Dreamworks
  Pictures')