Once the MySQL database is installed you can use the included Command Line Interface (CLI) for database administration and SQL queries. This mini-tutorial will cover the steps required to complete the assignment for this subject of the course.
If you have not yet installed the MySQL Database, use the following link to download:
http://dev.mysql.com/downloads/mysql/4.1.htmlBe sure to follow the installation instructions found in Section 2 of the MySQL documentation:
http://dev.mysql.com/doc/mysql/en/installing.htmlAnd, significantly, make sure you complete the post-installation instructions in Section 2.9:
http://dev.mysql.com/doc/mysql/en/post-installation.htmlOpen a console (Command Prompt) and login with the root database account.
c:\>mysql -u root -p Enter password: ********* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 to server version: 4.1.14-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Verify what databases you have via the 'SHOW DATABASES' query. Then create a user account to administer our DVD database.
mysql> show databases;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.00 sec)
mysql> GRANT ALL ON dvdcat.* TO 'dvdadmin'@'localhost'
-> IDENTIFIED BY 'dvdadmin158';
Query OK, 0 rows affected (0.03 sec)
This creates a user, 'dvdadmin', whose rights are limited to the database 'dvdcat'. He is also limited in connecting to MySQL from the local machine. It also gave him a password.
Now, exit as the root user and login with the new account.
mysql> exit Bye c:\>mysql -u dvdadmin -p Enter password: *********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 to server version: 4.1.14-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Now we have to create a database for our new user to play in.
mysql> CREATE DATABASE dvdcat; Query OK, 1 row affected (0.00 sec) mysql> show databases; +----------+ | Database | +----------+ | dvdcat | +----------+ 1 row in set (0.00 sec)
Before we can create our table, we have to select our new database to tell the MySQL client on which database the following changes will apply.
mysql> use dvdcat; Database changed
We are now ready to create our table. Our table is going to consist of an ID, which will be unique for all entries in our table. This ID will also be our Primary Key and we'll tell the database to manage its values for us with the AUTO_INCREMENT attriubte. The other columns of our table will be the title, year, rating, a url to the film's website or reviews, and the star list. Here is the SQL statement to create the table.
mysql> CREATE TABLE dvd (
-> id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(80) NOT NULL,
-> year INTEGER(4),
-> rating INTEGER,
-> url VARCHAR(100),
-> stars LONGTEXT );
Query OK, 0 rows affected (0.02 sec)
mysql> describe dvd
-> ;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| title | varchar(80) | | | | |
| year | int(4) | YES | | NULL | |
| rating | int(11) | YES | | NULL | |
| url | varchar(100) | YES | | NULL | |
| stars | longtext | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
With the MySQL client (and many other database clients as well) SQL statements are seperated with a semi-colon. With this CLI, you can only type in one statement at a time and it is not executed until you terminate the statement with the semi-colon. This is great, since it helps when entering long, complex statements.
Our table is created and ready for inserting data. There are various ways to do this. One is to create a text file with SQL statements in it. We then can run this CLI in 'batch' mode to execute those statements in the file. See the following link from the MySQL tutorial about using the batch mode.
http://dev.mysql.com/doc/mysql/en/batch-mode.htmlYou can also enter data using a tab-delimited text file and the LOAD DATA statement. Again, from the MySQL tutorial:
http://dev.mysql.com/doc/mysql/en/loading-tables.htmlThird, the most laborious method, is used here to enter information about five DVD titles. Note, that these only enter information about the title, year, and rating. Additional information will be added once we have our servlet going.
mysql> INSERT INTO dvd (title,year,rating) VALUES (
-> 'The Lord of the Rings: The Return of the King',
-> '2003',
-> '3' );
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO dvd (title,year,rating) VALUES (
-> 'To Catch a Thief',
-> '1954',
-> '0' );
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO dvd (title,year,rating) VALUES (
-> 'Wing Commander',
-> '1999',
-> '3' );
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO dvd (title,year,rating) VALUES (
-> 'Moulin Rouge',
-> '2001',
-> '3' );
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO dvd (title,year,rating) VALUES (
-> 'Nausica: of the Valley of the Wind',
-> '2002',
-> '2' );
Query OK, 1 row affected (0.00 sec)
mysql> SELECT id,title,year,rating FROM dvd;
+----+-----------------------------------------------+------+--------+
| id | title | year | rating |
+----+-----------------------------------------------+------+--------+
| 1 | The Lord of the Rings: The Return of the King | 2003 | 3 |
| 2 | To Catch a Thief | 1954 | 0 |
| 3 | Wing Commander | 1999 | 3 |
| 4 | Moulin Rouge | 2001 | 3 |
| 5 | Nausica: of the Valley of the Wind | 2002 | 2 |
+----+-----------------------------------------------+------+--------+
5 rows in set (0.00 sec)
We now have sufficent data in our database to explore SQL statements. It is suggested to follow the recommended tutorial using either the MySQL CLI or the MySQL Query Browser (GUI). Here are the links to the SQL tutorials for your convinence.
http://www.w3schools.com/sql/default.aspHere are a few of my favorites I encountered during my play time:
DISTINCT
mysql> select distinct year from dvd; +------+ | year | +------+ | 2002 | | 2001 | | 1999 | | 1954 | | 2003 | +------+ 5 rows in set (0.00 sec)
Select constraints
mysql> select title from dvd where id=4; +------------------+ | title | +------------------+ | To Catch a Thief | +------------------+ 1 row in set (0.00 sec) mysql> select title from dvd where rating < 3; +-------------------------------------+ | title | +-------------------------------------+ | Nausica: of the Valley of the Winds | | To Catch a Thief | +-------------------------------------+ 2 rows in set (0.00 sec)
LIKE
mysql> select title from dvd where title like '%the%'; +-----------------------------------------------+ | title | +-----------------------------------------------+ | Nausica: of the Valley of the Winds | | The Lord of the Rings: The Return of the King | +-----------------------------------------------+ 2 rows in set (0.00 sec)
UPDATE
mysql> update dvd set year=1111 where id=5
-> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select id,title,year from dvd;
+----+-----------------------------------------------+------+
| id | title | year |
+----+-----------------------------------------------+------+
| 1 | Nausica: of the Valley of the Winds | 2002 |
| 2 | Moulin Rouge | 2001 |
| 3 | Wing Commander | 1999 |
| 4 | To Catch a Thief | 1954 |
| 5 | The Lord of the Rings: The Return of the King | 1111 |
+----+-----------------------------------------------+------+
5 rows in set (0.00 sec)
mysql> update dvd set year=2003 where id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select id,title,year from dvd;
+----+-----------------------------------------------+------+
| id | title | year |
+----+-----------------------------------------------+------+
| 1 | Nausica: of the Valley of the Winds | 2002 |
| 2 | Moulin Rouge | 2001 |
| 3 | Wing Commander | 1999 |
| 4 | To Catch a Thief | 1954 |
| 5 | The Lord of the Rings: The Return of the King | 2003 |
+----+-----------------------------------------------+------+
5 rows in set (0.00 sec)
More constraints
mysql> select title from dvd where title < 's'; +-------------------------------------+ | title | +-------------------------------------+ | Nausica: of the Valley of the Winds | | Moulin Rouge | +-------------------------------------+ 2 rows in set (0.00 sec) mysql> select title from dvd where title < 'S'; +-------------------------------------+ | title | +-------------------------------------+ | Nausica: of the Valley of the Winds | | Moulin Rouge | +-------------------------------------+ 2 rows in set (0.00 sec) mysql> select id,title,year from dvd where year > 1999 and title > 's'; +----+-----------------------------------------------+------+ | id | title | year | +----+-----------------------------------------------+------+ | 5 | The Lord of the Rings: The Return of the King | 2003 | +----+-----------------------------------------------+------+ 1 row in set (0.00 sec) mysql> select title from dvd where year=2003 or year=2001; +-----------------------------------------------+ | title | +-----------------------------------------------+ | Moulin Rouge | | The Lord of the Rings: The Return of the King | +-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> select title from dvd where year in (2000,2001,2002,2003); +-----------------------------------------------+ | title | +-----------------------------------------------+ | Nausica: of the Valley of the Winds | | Moulin Rouge | | The Lord of the Rings: The Return of the King | +-----------------------------------------------+ 3 rows in set (0.00 sec)
ORDER BY
mysql> select id,title,year from dvd order by year; +----+-----------------------------------------------+------+ | id | title | year | +----+-----------------------------------------------+------+ | 4 | To Catch a Thief | 1954 | | 3 | Wing Commander | 1999 | | 2 | Moulin Rouge | 2001 | | 1 | Nausica: of the Valley of the Winds | 2002 | | 5 | The Lord of the Rings: The Return of the King | 2003 | +----+-----------------------------------------------+------+ 5 rows in set (0.00 sec) mysql> select id,title,year from dvd order by year desc; +----+-----------------------------------------------+------+ | id | title | year | +----+-----------------------------------------------+------+ | 5 | The Lord of the Rings: The Return of the King | 2003 | | 1 | Nausica: of the Valley of the Winds | 2002 | | 2 | Moulin Rouge | 2001 | | 3 | Wing Commander | 1999 | | 4 | To Catch a Thief | 1954 | +----+-----------------------------------------------+------+ 5 rows in set (0.00 sec)
Aliases
mysql> select title as 'DVD Title' from dvd; +-----------------------------------------------+ | DVD Title | +-----------------------------------------------+ | Nausica: of the Valley of the Winds | | Moulin Rouge | | Wing Commander | | To Catch a Thief | | The Lord of the Rings: The Return of the King | +-----------------------------------------------+ 5 rows in set (0.00 sec)
BETWEEN
mysql> select title from dvd where year between 1960 and 2000; +----------------+ | title | +----------------+ | Wing Commander | +----------------+ 1 row in set (0.00 sec)
AVG(), COUNT(), and CONCAT()
mysql> select avg(year) from dvd; +-----------+ | avg(year) | +-----------+ | 1991.8000 | +-----------+ 1 row in set (0.00 sec) mysql> select title,year,count(*) from dvd group by year; +-----------------------------------------------+------+----------+ | title | year | count(*) | +-----------------------------------------------+------+----------+ | To Catch a Thief | 1954 | 1 | | Wing Commander | 1999 | 1 | | Moulin Rouge | 2001 | 1 | | Nausica: of the Valley of the Winds | 2002 | 1 | | The Lord of the Rings: The Return of the King | 2003 | 1 | +-----------------------------------------------+------+----------+ 5 rows in set (0.00 sec) mysql> select concat(year,", ",title) as blah from dvd; +-----------------------------------------------------+ | blah | +-----------------------------------------------------+ | 2002, Nausica: of the Valley of the Winds | | 2001, Moulin Rouge | | 1999, Wing Commander | | 1954, To Catch a Thief | | 2003, The Lord of the Rings: The Return of the King | +-----------------------------------------------------+ 5 rows in set (0.00 sec)