Working with MySQL

Revision as of 14:53, 29 March 2010

Database Basics

A relational database is a structured repository of information divided into a collection of tables with rows and columns that can be linked together (related) by one or more shared values.

For example, consider the following tables:

In this example the uid column (user id) in both tables can be used to link the user's name with the user's city. For some tables, a column (attribute) can be designated as a unique key, which means that for a given value (1) only one row of the table can have that value.

Querying the Database

Once you've created a database and stored information in it, you can retrieve the information by performing queries. For example, you can retrieve all users where * means ALL:

SELECT * FROM users;

+-----+-------+
| uid | name  |
+-----+-------+
|   1 | ralph | 
|   2 | kim   | 
+-----+-------+

Or you can make a query that gathers information from more than one table. For example, if we want a list of users and their cities, we would query "Select the user name and city from the users and cities tables where they have the same uid:

SELECT users.name,city FROM users, cities WHERE users.uid=cities.uid;

+-------+--------------+
| name  | city         |
+-------+--------------+
| ralph | wethersfield | 
| kim   | hartford     | 
+-------+--------------+
2 rows in set (0.00 sec)

Using MySQL

To use MySQL you will need the following information:

• The IP address of the database server--e.g., http://localhost.
• The name of the database--e.g., cpsc110
• The username--e.g., root.
• The password--e.g., test1234.

Command Line Instructions: Macintosh

• Here on instructions on how to use the Macintosh command line under MAMP.

• To login to MySQL:

mysql -h localhost -u root -ptest1234

• To login using the full pathname on the Mac (assuming MAMP has null password):

/Applications/MAMP/Library/bin/mysql

• If you leave off the password, you will be prompted:

mysql -h localhost -u root -p
Enter password: 

• The MySQL Greeting and Command Prompt

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 132
Server version: 5.0.51 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

Commands

MySQL commands are given at the prompt, ending with a semicolor.

mysql>show databases;

+--------------------+
| Database           |
+--------------------+
| cpsc110            | 
| jokes              | 
| mysql              | 
+--------------------+
3 rows in set (0.01 sec)

Creating Users and Databases

The GRANT command is used to create users using the syntax. This will allow other users (besides root) to use the database.

GRANT PRIVILEGES ON DATABASE.OBJECTS TO username@hostname IDENTIFIED BY password

For example:

GRANT ALL PRIVILEGES ON *.* TO 'ralph'@'localhost' IDENTIFIED BY 'secret';

The CREATE command is used to create a named database. Once the database is created, tables associated with it can be created an populated.

CREATE DATABASE jokes;