1 of 10

MySQL - Advanced SQL

Mark Fontenot, PhD

Northeastern University

2 of 10

Installing Sakila in MySQL

  • Download sakila-db.zip from > here <.
  • Unzip sakila-db.zip and move the files into your 3200 folder (where you put stuff for DataGrip)
  • Fire up Docker Desktop and Start MySQL container
  • Open the sakila-schema.sql file and execute all the code in it. Make sure you’re connected to MySQL
  • Open the sakila-data.sql file and execute all the code in it.

2

3 of 10

Installing Sakila in MySQL

  • Create a new sql script file in DataGrip (name it whatever you want).
  • Set the default SQL dialect to MySQL
  • Connect it to MySQL server data source
  • Switch into the Sakila Database (use sakila;)
  • Show all the tables in Sakila (show tables;)

3

4 of 10

Create a New Table from Existing Data

Context is Sakila DB.

Task: Create a table that only contains information for films that start with the letter A.

4

CREATE TABLE films_start_with_a AS

SELECT *

FROM film

WHERE title LIKE 'A%';

5 of 10

Create a New Table from Existing Data

  • Duplicated data - wastes storage
  • Changes to contents in film table aren’t reflected in films_start_with_a table and vice versa.

5

CREATE TABLE films_start_with_a AS

SELECT *

FROM film

WHERE title LIKE 'A%';

6 of 10

Database Views

  • A view is a SELECT statement that is stored in the database
  • Generally thought of as read-only
    • Data is NOT copied into a new table for the view
    • Note: Some DBs support materialized views in which the view data is stored separately from the underlying table data, but not MySQL

6

7 of 10

Create View

7

CREATE VIEW <view name> AS

<some select statement>;

CREATE VIEW films_start_with_b AS

SELECT *�FROM film

WHERE title like ‘B%’;

8 of 10

Create Views

8

SELECT COUNT(*) AS count

FROM film

WHERE title LIKE 'B%';

​

​

SELECT COUNT(*) AS count

FROM films_start_with_b;

9 of 10

Why Views?

  • Simplify other queries
  • Provide a more “user friendly” version of the data for non-db folks
  • Provide an anonymized version of data
  • Provide a rolled-up version of data
  • Many others….

9

10 of 10

Alter Schema

MySQL and other RDBMSs allow you to modify the schema objects.

10

ALTER TABLE films_start_with_a

RENAME COLUMN title TO film_title;

​

ALTER TABLE films_start_with_a

ADD COLUMN subtitle VARCHAR(128) NULL;

​

ALTER TABLE films_start_with_a

DROP COLUMN subtitle;