Working with MySQL

Contents 1 Database Basics 2 Querying the Database 3 Using MySQL 4 Using the MySQL Command Line: Windows 5 Command Line Instructions: Macintosh 6 Commands 7 Creating Users and Databases

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 uid name 1 ralph 2 kim

Cities uid city 1 Wethersfield 2 Hartford

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

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

mysql>show databases;

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

Command	Parameter	Meaning
quit		Exit the mysql system
use	Database name	Use a specific database
show	tables or databases	Show a list of available tables or databases
describe	table name	Describe a table's columns
status		Display database version and status
source	filename	Execute 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;