Working with MySQL Data

Insert Statements

  • Used to add data to tables, typically one row at a time.
  • Frequently generated automatically by computer programs.
  • The MySQL Workbench's load bulk data wizard generates insert statements from imported data files.
  • Syntax:
  • insert into table_name (column1, column2, ...) values (value1, value2, ...);
  • If adding data to all columns, the column list can be omitted.
  • If a column is omitted and has a default value, that value will be used.
  • Example:
  • insert into movies_basic (title, genre, release_year, director, studio, critic_rating) values ('challenge of the emperor', 'adventure', 2010, 'Miley Watson', 'Unknown', 7.2);
  • If the number of values doesn't match the number of columns, MySQL will throw an error.
  • Multiple rows can be added with a single insert statement by providing multiple sets of values, each enclosed in parentheses and separated by commas:
  • insert into movies_basic (title, genre, release_year, director, studio, critic_rating) values ('dishonor of power', ...), ('night of the maze', ...);

Update Statements

  • Used to change values in existing rows of a table.
  • Requires a where clause to filter the rows to update.
  • Syntax:
  • update table_name set column1 = value1, column2 = value2, ... where condition;
  • Example:
  • update movies_basic set director = 'Mike Watson' where director = 'Miley Watson';
  • Without a where clause, all rows in the table will be updated, making it very important to use the where clause carefully.
    • It's recommended to test the where clause with a select query before running the update statement.
    • select * from movies_basic where director = 'Miley Watson';
  • MySQL Workbench may prevent update statements without a key value in the where clause.
  • This "safe updates" feature can be disabled in Edit -> Preferences -> SQL Editor.

Delete Statements

  • Used to remove rows from a table.
  • Requires a where clause to specify which rows to delete.
  • Syntax:
  • delete from table_name where condition;
  • Example:
  • delete from movies_basic where release_year < 1927;
  • Without a where clause, all rows in the table will be deleted, which is possible but should be done cautiously.
  • Deleted auto-incremented ID values are not reused by default.
    • To reset the auto increment counter, use alter table table_name auto_increment = value;.
    • alter table movies_basic auto_increment = 54;
  • To remove all data from a table, it's faster to use a truncate statement.
    • truncate resets the auto-increment counter and drops and recreates the table.
  • Syntax:
    • truncate table table_name;

CRUD Operations

  • Create (insert)
  • Read (select)
  • Update (update)
  • Delete (delete)

Challenge Solution

  • Goal: Correct mistakes in a movie database using insert, update, and delete statements.
  • Steps:
    • Insert Rince Perez filmography:
    • insert into movies_basic (title, genre, release_year, director, studio, critic_rating) values ('run for the forest', ...), ('luck of the night', ...), ('invader glory', ...);
    • Update Falstad Group