MySQL Intro

1 How to use your MySQL account

Create your mysql account here. You may need to use your current or your previous Napier password.
Keep your MySqlUsername and password in a safe place.
Forgotten password: use this webpage again.
Map your Unix drives by clicking on the blue folder at the bottom, then: Faculty of Engineering, Computing and Creative Industries => School of Computing => Unix => Map Unix drives.
Login to DCS/Socweb8 (using your normal username/password) by going to School of Computing => Unix => SSH to DCS. Use your normal login and password (the same one you use for Napier email etc).
Type the following at the command-line:
mysql -h mysqlhost3 -u MySqlUsername -D MySqlUsername -p
replacing MySqlUsername with your MySQL username.
(Note: if "mysqlhost3" does not work, try "mysqlhost" instead.)
When prompted type your MySQL password.

(For the login, you could also type
mysql -h mysqlhost3 -u MySqlUsername -p
But then you need to type use MySqlUsername after you login to get to your own database.)

2 Important MySQL commands (to be typed at the "mysql>" prompt)

show databases;	show which databases are available
select database();	show the name of the current database
select version();	show mysql version
show tables;	show the tables in your current database
help	shows help options and available commands
help select	(for example) shows help for the select command
\P	sets the Pager so that results are displayed one page at a time; if this is on, you need to type 'q' to exit the result display
quit	to exit mysql
describe [table name];	to describe the table with that name
use [database name];	to change to the database with that name
source [filename.sql];	execute the commands in the file with that name

Notes:

Commands must be ended with ";". If you see the "->" prompt instead of "mysql>", it means that your command isn't finish. Type ; to finish it.

Use arrow keys to go back to a previous command. If your command produces an error, use arrow keys to go back and edit it.

2.1 Exercises

1) Try the first 6 commands from the table above (up to "help select").

2) Try the CAT; command which you remember from Oracle. (This is where MySQL and Oracle differ. MySQL uses show databases.)

3) Install the Jobs database as follows: download this jobs.sql file and save it on your I:drive. (The I:drive is the same location as DCS/Socweb1.) At the "mysql>" prompt, type source jobs.sql.
If you are interested how the installation works: have a look at the contents of the jobs.sql file by going to your I:drive with Windows Explorer and opening the file in Wordpad.

4) Type show tables;. The data in the jobs.sql file is the same as the data in the on-line interface which was used last week. The only difference is that the on-line interface is for an Oracle database.

5) Try a few queries from the previous weeks in MySQL. For example, try the first question from Tutorial 2: "List the employee number, position and salary of any employee who has been or is some sort of Analyst." Is there any difference between the MySQL result and the Oracle result?

3 Creating tables

In your MySQL interface you are allowed to create, drop and update tables and to insert values. The jobs.sql file contains examples of how to create tables and insert data. You can open the jobs.sql file in Wordpad.

3.1 Exercises

6) Create a table called "project" which contains the following data:

empno projectID projectname budget
15 1 Interface Design 10000
17 2 E-Commerce Solution 20000
19 3 Advertising 50000

empno	projectID	projectname	budget
15	1	Interface Design	10000
17	2	E-Commerce Solution	20000
19	3	Advertising	50000

The employee number (empno) belongs to the employee who is in charge of project. Choose appropriate datatypes for each of the fields. Create the table and insert the data. Decide which keys you might need (primary key, foreign key) and make sure that these are correctly implemented.

Hint: If MySQL complains about warnings or errors when you insert the data, use "show warnings" and "show errors" to look at the warnings and errors.

7) The company has decided that the budgets are too small. Update the project table. Add 5000 to each of the budgets.

8) The company decides to take project number 3 from employee 19 and give it to employee 20. Update the table to reflect this change.

4 Creating a backup

The following mysqldump command can be used (at the Unix command-line prompt) to create a backup:
mysqldump --skip-lock-tables -h mysqlhost3 -u MySqlUsername MySqlUsername -p > dump.sql

4.1 Exercise

9) Quit MySQL. Type the command above at the Unix command-line prompt (not at the "mysql>" prompt). Make sure you replace MySqlUsername with your username. When prompted, type the MySQL password. This will create a backup of your database in the "dump.sql" file. Open the "dump.sql" file in Wordpad. Observe how this file differs from the original "jobs.sql" file. Only continue to the next exercise, if the "dump.sql" file correctly contains your data.

10) Login to MySQL. Drop all your tables. (Note that tables which contain foreign keys must be dropped before you drop the table to which the foreign key points. This is because of referential integrity constraints.)
Verify that the tables are dropped, by typing show tables;. Restore the database from the "dump.sql" file by typing source dump.sql. (You may get some "access denied" errors, but you can ignore these, if most of the messages say "OK"). Verify that your tables are back and contain data.