1. Introduction to PHP and MySQL (Creating Database-Driven Websites) Understanding SQL Statements

2. Understanding SQL Statements • Structured Query Language, or SQL, is the standard language used to communicate with a database, add or change records and user privileges, and perform queries. • The language, which became an ANSI standard in 1989, is currently used by almost all of today’s commercial RDBMSs.

3. Understanding SQL Statements SQL statements fall into one of three categories: • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL)

4. Understanding SQL Statements • Data Definition Language (DDL) DDL consists of statements that define the structure and relationships of a database and its tables. Typically, these statements are used to create, delete, and modify databases and tables; specify field names and types; and set indexes.

5. Understanding SQL Statements • Data Manipulation Language (DML) DML statements are related to altering and extracting data from a database. These statements are used to add records to, and delete records from, a database; perform queries; retrieve table records matching one or more user-specified criteria; and join tables together using their common fields.

6. Understanding SQL Statements • Data Control Language (DCL) DCL statements are used to define access levels and security privileges for a database. You would use these statements to grant or deny user privileges; assign roles; change passwords; view permissions; and create rule sets to protect access to data.

7. Understanding SQL Statements • SQL commands resemble spoken English, which makes the language easy to learn. • The syntax is quite intuitive. Every SQL statement begins with an “action word,” like DELETE, INSERT, ALTER or DESCRIBE, and ends with a semicolon. • Whitespace, tabs, and carriage returns are ignored.

8. Understanding SQL Statements A few examples of valid SQL statements: CREATE DATABASE library; SELECT movie FROM movies WHERE rating > 4; DELETE FROM cars WHERE year_of_manufacture < 1980;

9. Table Relationships

10. Primary Keys and Foreign Keys What is a Primary Key? A primary key is used to uniquely identify each row in a table. A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key. What is a Foreign Key? A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.

11. Referential Integrity A value in a foreign key field in a record in one table must have a matching value in the primary key field of a record in a related table (or be null). A DATABASE MUST NOT CONTAIN ANY UNMATCHED FOREIGN KEY VALUES.

12. Common SQL Statements

13. Common SQL Statements

14. Creating the Database mysql> CREATE DATABASE music; Query OK, 1 row affected (0.05 sec) mysql> USE music; Database changed

15. Adding Tables mysql> CREATE TABLE artists ( -> artist_id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, -> artist_name VARCHAR (50) NOT NULL, -> artist_country CHAR (2) NOT NULL -> ); Query OK, 0 rows affected (0.07 sec)

16. Adding Tables • This statement creates a table named artists with three fields, artist_id, artist_name, and artist_country. • Notice that each field name is followed by a type declaration; this declaration identifies the type of data that the field will hold, whether string, numeric, temporal or Boolean. • MySQL supports a number of different data types

17. MySQL Data Types

18. MySQL Data Types

19. Adding Tables There are a few additional constraints (modifiers) that are set for the table in the preceding statement: • The NOT NULL modifier ensures that the field cannot accept a NULL value after each field definition. • The PRIMARY KEY modifier marks the corresponding field as the table’s primary key. • The AUTO_INCREMENT modifier, which is only available for numeric fields, tells MySQL to automatically generate a value for this field every time a new record is inserted into the table by incrementing the previous value by 1.

20. Adding Tables mysql> CREATE TABLE ratings ( -> rating_id INT(2) NOT NULL PRIMARY KEY, -> rating_name VARCHAR (50) NOT NULL -> ); Query OK, 0 rows affected (0.13 sec)

21. Adding Tables mysql> CREATE TABLE songs ( -> song_id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT, -> song_title VARCHAR(100) NOT NULL, -> fk_song_artist INT(4) NOT NULL, -> fk_song_rating INT(2) NOT NULL -> ); Query OK, 0 rows affected (0.05 sec)

22. Adding Records mysql> INSERT INTO artists (artist_id, artist_name, artist_country) -> VALUES ('1', 'Aerosmith', 'US'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO artists (artist_name, artist_country) -> VALUES ('Abba', 'SE'); Query OK, 1 row affected (0.00 sec)

23. Adding Records mysql> INSERT INTO ratings (rating_id, rating_name) VALUES (4, 'Good'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO ratings (rating_id, rating_name) VALUES (5, 'Excellent'); Query OK, 1 row affected (0.00 sec)

24. Adding Records mysql> INSERT INTO songs (song_title, fk_song_artist, fk_song_rating) -> VALUES ('Janie\'s Got A Gun', 1, 4); Query OK, 1 row affected (0.04 sec) mysql> INSERT INTO songs (song_title, fk_song_artist, fk_song_rating) -> VALUES ('Crazy', 1, 5); Query OK, 1 row affected (0.00 sec)

25. Adding Records by Executing Script File Find and copy music_mysql.sql to C:\temp mysql> source C:\temp\music_mysql.sql

26. Executing Queries mysql> SELECT artist_id, artist_name FROM artists; +-----------+-------------+ | artist_id | artist_name | +-----------+-------------+ | 1 | Aerosmith | | 2 | Abba | | 3 | Timbaland | | 4 | Take That | | 5 | Girls Aloud | | 6 | Cubanismo | +-----------+-------------+ 6 rows in set (0.00 sec)

27. Executing Queries - Restrict mysql> SELECT artist_id, artist_name FROM artists -> WHERE artist_country = 'US'; +-----------+-------------+ | artist_id | artist_name | +-----------+-------------+ | 1 | Aerosmith | | 3 | Timbaland | +-----------+-------------+ 2 rows in set (0.00 sec)

28. Executing Queries - Comparison mysql> SELECT song_title, fk_song_rating FROM songs -> WHERE fk_song_rating >= 4; +-------------------+----------------+ | song_title | fk_song_rating | • +-------------------+----------------+ | Janie's Got A Gun | 4 | | Crazy | 5 | | En Las Delicious | 5 | | Pray | 4 | | Apologize | 4 | | SOS | 4 | | Dancing Queen | 4 | +-------------------+----------------+ 7 rows in set (0.00 sec)

29. Executing Queries - Logical mysql> SELECT artist_name, artist_country FROM artists -> WHERE artist_country = 'US' -> OR artist_country = 'UK'; +-------------+----------------+ | artist_name | artist_country | +-------------+----------------+ | Aerosmith | US | | Timbaland | US | | Take That | UK | | Girls Aloud | UK | +-------------+----------------+ 4 rows in set (0.02 sec)

30. Executing Queries - Sorting mysql> SELECT song_title FROM songs -> ORDER BY song_title; +---------------------------+ | song_title | +---------------------------+ | Another Crack In My Heart | | Apologize | | Babe | | Crazy | | Dancing Queen | | En Las Delicious | | GimmeGimmeGimme | | Janie's Got A Gun | | Pray | | SOS | | Sure | | VoulezVous | +---------------------------+ 12 rows in set (0.04 sec)

31. Executing Queries -Sorting mysql> SELECT song_title FROM songs -> ORDER BY song_title DESC; +---------------------------+ | song_title | +---------------------------+ | VoulezVous | | Sure | | SOS | | Pray | | Janie's Got A Gun | | GimmeGimmeGimme | | En Las Delicious | | Dancing Queen | | Crazy | | Babe | | Apologize | | Another Crack In My Heart | +---------------------------+ 12 rows in set (0.00 sec)

32. Executing Queries - LIMIT To display rows 4–9 (inclusive) of a result set, use the following statement: mysql> SELECT song_title FROM songs -> ORDER BY song_title -> LIMIT 3,6; +-------------------+ | song_title | +-------------------+ | Crazy | | Dancing Queen | | En Las Delicious | | GimmeGimmeGimme | | Janie's Got A Gun | | Pray | +-------------------+ 5 rows in set (0.00 sec)

33. Executing Queries – Wild Cards The SQL SELECT statement also supports a LIKE clause, which can be used to search within text fields using wildcards. There are two types of wildcards allowed in a LIKE clause - the % character, which is used to signify zero or more occurrences of a character, and the _ character, which is used to signify exactly one occurrence of a character. The following example illustrates a LIKE clause in action, searching for song titles with the character 'g' in them: mysql> SELECT song_id, song_title FROM songs -> WHERE song_title LIKE '%g%'; +---------+-------------------+ | song_id | song_title | +---------+-------------------+ | 1 | Janie's Got A Gun | | 7 | Apologize | | 8 | GimmeGimmeGimme | | 10 | Dancing Queen | +---------+-------------------+ 4 rows in set (0.00 sec)

34. Executing Queries – Joining Tables mysql> SELECT song_id, song_title, artist_name FROM songs, artists -> WHERE songs.fk_song_artist = artists.artist_id; +---------+---------------------------+-------------+ | song_id | song_title | artist_name | +---------+---------------------------+-------------+ | 1 | Janie's Got A Gun | Aerosmith | | 2 | Crazy | Aerosmith | | 8 | GimmeGimmeGimme | Abba | | 9 | SOS | Abba | | 10 | Dancing Queen | Abba | | 11 | VoulezVous | Abba | | 7 | Apologize | Timbaland | | 4 | Sure | Take That | | 5 | Pray | Take That | | 6 | Another Crack In My Heart | Take That | | 12 | Babe | Take That | | 3 | En Las Delicious | Cubanismo | +---------+---------------------------+-------------+ 12 rows in set (0.00 sec)

35. Executing Queries – Joining Tables mysql> SELECT song_title, artist_name, rating_name -> FROM songs, artists, ratings -> WHERE songs.fk_song_artist = artists.artist_id -> AND songs.fk_song_rating = ratings.rating_id -> AND ratings.rating_id >= 4 -> AND artists.artist_country != 'US'; +------------------+-------------+-------------+ | song_title | artist_name | rating_name | +------------------+-------------+-------------+ | En Las Delicious | Cubanismo | Excellent | | Pray | Take That | Good | | SOS | Abba | Good | | Dancing Queen | Abba | Good | +------------------+-------------+-------------+ 4 rows in set (0.02 sec)

36. Executing Queries – Deleting Records mysql> DELETE FROM songs -> WHERE fk_song_rating <= 3; Query OK, 5 rows affected (0.02 sec)

37. Executing Queries – Updating Records mysql> UPDATE ratings SET rating_name = 'Fantastic' -> WHERE rating_name = 'Excellent'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

38. Executing Queries – Updating Records mysql> UPDATE songs SET song_title = 'Waterloo', -> fk_song_rating = 5 -> WHERE song_id = 9; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

39. Executing Queries