Working with MySQL

Contents

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:

Users
uidname
1ralph
2kim

  

Cities
uidcity
1Wethersfield
2Hartford

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.

Using the MySQL Command Line: Windows

If you installed EasyPHP WAMP 3.0, you will be able to run the MySQL executable from the following director:

\Program Files\EasyPHP 3.0\mysq\bin

The default user name is root and there is no password associated with it. So to login to MySQL's command line interface, use the following command:

"\Program Files\EasyPHP 3.0\mysq\bin" -u root

Command Line Instructions: Macintosh

  • 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 semicolon.

mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| cpsc110            | 
| jokes              | 
| mysql              | 
+--------------------+
3 rows in set (0.01 sec)
CommandParameterMeaning
quit Exit the mysql system
useDatabase nameUse a specific database
showtables or databasesShow a list of available tables or databases
describetable nameDescribe a table's columns
status Display database version and status
sourcefilenameExecute MySQL commands from a file or script

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;

MySQL Tutorial

There is an excellent and detailed tutorial on MySQL on Google Code University.