Master's Students: Hand in title, abstract, and outline for your protocol paper (by midnight).

All Students: Hand in select statements for the attached queries on the employees database. (Not the result sets, the queries!!!)

The command-line client that connects you to the MySQL server.

Remember that you want to set your path in .bash_profile with the following command:

export PATH=$PATH:/usr/local/mysql/bin/

Once you have done that, you can invoke the monitor and connect it to a server with the command:

mysql -h <host> -u <user> -p

-h <host> specifies the host on which the server is running.
-u <user> specifies the user name.
-p specifies that you will enter a password.

A user should have the lowest level of privilege required to perform his assigned task.

A list of privileges is in the book in Tables 9.1 and 9.2 and also at http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html.

1. Login to mysql as root:

   mysql [-h <hostname>] -u root -p
   

2. To create users with their privileges or to add privileges to an existing user, use the command:

   GRANT <priv> [<columns>]
   ON <object-spec>
   TO <user> [IDENTIFIED-BY <password>]
   [REQUIRE <ssl-options>]
   [WITH <option>]
   

   Note, square brackets identify optional parts of the command.
   • <priv> is a list of privileges, which can be any of the privileges in the lists above (e.g., SELECT, INSERT, DELETE, CREATE, ALTER, INDEX, etc.)
   • Use the optional <columns> for a list of columns that the privilege applies to.
   • <object-spec> can be:

     *

     *.*

     <db-name>.*

     <db-name>.<table-name>

     <table-name>

     <db-name>.<routing-name>

   • <user> is a login name, optionally with the host (i.e., newuser or newuser@localhost).
   • <password> is a password.
   • Optionally, you may require use of encryption by specifying <ssl-options>.
   • Optionally, you may restrict the privileges to a maximum number of operations per hour or you may allow the user to grant the same privileges to others.

   Exercise: Add various users with various privileges from the command line, then check what you did in the workbench. To check what happened, you will need to Refresh the Users and Privileges page in the Workbench. Note that if you grant privileges to a user that doesn't exist, it will be created.

   See also http://dev.mysql.com/doc/refman/5.1/en/grant.html.

3. You can also revoke privileges:

   REVOKE <priv> [<columns>]
   ON <object-spec>
   FROM <user> 
   

   Exercise: Revoke privileges. Does a user without privileges go away?

4. There is also a command to create a user:

   CREATE <user> [IDENTIFIED BY <password>]
   

   and a command to drop a user:

   DROP <user>
   

To create a new database, use the CREATE DATABASE command:

CREATE DATABASE <database>

To delete a database, use the DROP DATABASE command:

DROP DATABASE <database>

This command will drop all tables in the database. Use it carefully!!

In either command, you could say SCHEMA instead of DATABASE -- they are synonyms in this context.

To create a new table, use the CREATE TABLE command:

CREATE TABLE <table-name> ( <column-list> )

where

• <table-name> is the name of the new table.
• <column-list> is a comma-separated list of <column-name data-type> entries.

Data types are grouped into string, numeric, and date. See Tables 9.5 through 9.11 in the book for the various types. General rule: Use the "smallest" type that provides everything you need. Sometimes one type is more space-efficient than another (but likely will be less time-efficient). Make your choices depending on what is important to you.

Take a look at employees.sql for an example of a lot of create statements, starting at line 46. The first 45 lines are comments, initialization, and avoiding errors.

In the MySQL Monitor or in a SQL File Tab:

1. What command do you use to create a database? Remove a database?
2. What command do you use to create a table? Remove a table? Change the table definition?
3. What command do you use to set or increase user privileges? Reduce user privileges?