|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|
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?
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.
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.