class: center, middle # Introduction to MySQL
Session IV ## Oct 23, 2014
Coalition for Queens #### Dan Goldin
email@example.com --- # Agenda - Quick review - Updating data - Inserting data - Deleting data - Modifying tables - Creating tables - Dropping tables --- # Survey time - [https://c4qnyc.wufoo.com/forms/sql-workshop-survey/](https://c4qnyc.wufoo.com/forms/sql-workshop-survey/) --- # Quick review - What does this query do? ```sql SELECT t.name as team_name, q.name as position, sum(points) as total_points FROM players p JOIN positions q ON p.position_id = q.id JOIN stats s ON p.id = s.player_id JOIN teams t on p.team_id = t.id GROUP BY t.name, q.name; ``` --- # Quick review - How about this one? ```sql SELECT name FROM players WHERE id IN ( SELECT player_id FROM stats GROUP BY player_id HAVING AVG(passing_tds) > 1 AND AVG(rushing_yds) > 20 ); ``` --- # Updating data - Modify existing rows in a table - UPDATE [table] SET [field1] = [value1], [field2] = [value2] WHERE [condition]; --- # Updating data ```sql UPDATE players SET name = 'Dan Goldin' WHERE id = 11; UPDATE players SET name = 'Dan Goldin', team_id = 2 WHERE id = 11; UPDATE schedule SET week = 18 WHERE week > 10 AND week < 11; ``` --- # Exercises - Write queries to do the following: * Put everyone with the last name Johnson on team_id 10 * Update Eli Manning's record to use his birth name: "Elisha Nelson Manning" --- # Inserting data - Insert new rows into a table - INSERT INTO [table] VALUES ([values]); - INSERT INTO [table] ([fields]) VALUES ([values]); - INSERT INTO [table] ([fields]) VALUES ([values]),([values]); --- # Inserting data ```sql INSERT INTO positions VALUES (5, 'K'); INSERT INTO positions (name) VALUES ('K'); INSERT INTO players (name, position_id, team_id) VALUES ('Dan Goldin', 1, 22); INSERT INTO players (name, position_id, team_id) VALUES ('Dan Goldin', 1, 22), ('Someone Else', 2, 10); ``` --- # Exercises - Write queries to do the following: * Create a new NFL team * Add a week 18 game to the schedule * Add a player named after you to the players table --- # Deleting data - Delete row(s) from a table - DELETE FROM [table] WHERE [condition]; --- # Deleting data ```sql DELETE FROM positions WHERE name = 'QB'; DELETE FROM schedule WHERE week IN (1,3,5,7); DELETE FROM schedule WHERE home_id IN ( SELECT id FROM teams WHERE name in ('Ari', 'Atl') ); ``` --- # Exercises - Write queries to do the following: * Delete the player you created * Delete all players with the first name 'Ryan' * Delete all players that average less than 10 points a week --- # Modifying tables - Modify the columns in a table - ALTER [table] ADD COLUMN [column_name] [column_definition]; - ALTER [table] MODIFY [column_name] [new_column_definition]; - ALTER [table] DROP COLUMN [column_name]; --- # Modifying tables ```sql ALTER TABLE teams ADD COLUMN long_name VARCHAR(20); ALTER TABLE teams MODIFY long_name VARCHAR(40); ALTER TABLE teams DROP COLUMN long_name; ``` --- # Exercises - Add a first name and a last name column to the players table - Add a price_per_ticket column to the schedule table --- # Creating tables - Create new tables - CREATE TABLE [table] AS (fields); --- # Creating tables ```sql CREATE TABLE stadiums ( id int(10) unsigned NOT NULL AUTO_INCREMENT, team_id int(10) unsigned NOT NULL, name varchar(100) NOT NULL ); ``` --- # Exercises - Think of a useful or interesting table and add it to the database. --- # Dropping tables - Remove a table from our database - DROP TABLE [table]; --- # Dropping tables ```sql DROP TABLE positions; DROP TABLE stadiums; ``` --- # Questions?