Transcript Understanding SQL Statements

Introduction to PHP and MySQL (Creating Database-Driven Websites)


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.
SQL statements fall into one of three
categories:
 Data Definition Language (DDL)
 Data Manipulation Language (DML)
 Data Control Language (DCL)

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.

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.

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.



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.
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;
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.
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.
SQL Statement
What It Does
CREATE DATABASE databasename
Creates a new database
CREATE TABLE table-name (field1,
field2, ...)
INSERT INTO table-name (field1,
field2, ...)
VALUES (value1, value2, ...)
UPDATE table-name SET
field1=value1,
field2=value2, ... [WHERE
condition]
Creates a new table
Inserts a new record into
a table
with specified values
Updates records in a
table with
new values
SQL Statement
What It Does
DELETE FROM table-name
[WHERE condition]
Deletes records from a
table
SELECT field1, field2, ... FROM
table-name
[WHERE condition]
Retrieves matching
records from
a table
RENAME table-name TO new-table- Renames a table
name
DROP TABLE table-name
Deletes a table
DROP DATABASE database-name
Deletes a database
mysql> CREATE DATABASE music;
Query OK, 1 row affected (0.05 sec)
mysql> USE music;
Database changed
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)



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
Field Type
Description
INT
A numeric type that can accept values in the range of –
2147483648 to 2147483647
DECIMAL
A numeric type with support for floating-point or
decimal numbers
DATE
A date field in the YYYY-MM-DD format
TIME
A time field in the HH:MM:SS format
DATETIME
A combined date/time type in the YYYY-MM-DD
HH:MM:SS format
YEAR
A field specifically for year displays in the range 1901 to
2155, in either YYYY or YY formats
TIMESTAMP
A timestamp type, in YYYYMMDDHHMMSS format
Field Type
Description
CHAR
A string type with a maximum size of 255 characters
and a fixed length
VARCHAR
A string type with a maximum size of 255 characters
and a variable length
TEXT
A string type with a maximum size of 65535 characters
BLOB
A binary type for variable data
ENUM
A string type that can accept one value from a list of
previously defined possible values
SET
A string type that can accept zero or more values from
a set of previously defined possible values
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.
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)
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)
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)
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)
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)
Find and copy music_mysql.sql to C:\temp
mysql> source C:\temp\music_mysql.sql
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)
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)
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)
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)
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 |
| Gimme Gimme Gimme |
| Janie's Got A Gun |
| Pray |
| SOS |
| Sure |
| Voulez Vous |
+---------------------------+
12 rows in set (0.04 sec)
mysql> SELECT song_title FROM songs
-> ORDER BY song_title DESC;
+---------------------------+
| song_title |
+---------------------------+
| Voulez Vous |
| Sure |
| SOS |
| Pray |
| Janie's Got A Gun |
| Gimme Gimme Gimme |
| En Las Delicious |
| Dancing Queen |
| Crazy |
| Babe |
| Apologize |
| Another Crack In My Heart |
+---------------------------+
12 rows in set (0.00 sec)
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 |
| Gimme Gimme Gimme |
| Janie's Got A Gun |
| Pray |
+-------------------+
5 rows in set (0.00 sec)
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 | Gimme Gimme Gimme |
| 10 | Dancing Queen |
+---------+-------------------+
4 rows in set (0.00 sec)
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 | Gimme Gimme Gimme | Abba |
| 9 | SOS | Abba |
| 10 | Dancing Queen | Abba |
| 11 | Voulez Vous | 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)
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)
mysql> DELETE FROM songs
-> WHERE fk_song_rating <= 3;
Query OK, 5 rows affected (0.02 sec)
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
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