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