1 How to use your MySQL account

(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
helpshows help options and available commands
help select (for example) shows help for the select command
\Psets 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
quitto 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 projectIDprojectnamebudget
    151Interface Design10000
    172E-Commerce Solution20000
    193Advertising50000

    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.