Working with MySQL

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) links 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; +-+---+ +-+---+ +-+---+ 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; +---+--+ +---+--+ +---+--+ 2 rows in set (0.00 sec)
 * uid | name |
 * 1 | ralph |
 * 2 | kim  |
 * name | city         |
 * ralph | wethersfield |
 * kim  | hartford     |

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
mysql -h localhost -u root -ptest1234 /Applications/xampp/xamppfiles/bin/mysql -h localhost -u root -ptest1234 mysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 132 Server version: 5.0.51 Source distribution
 * To login to MySQL:
 * To login using the full pathname on the Mac (assuming XAMPP):
 * If you leave off the password, you will be prompted:
 * The MySQL Greeting and Command Prompt

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; ++ ++ ++ 3 rows in set (0.01 sec)
 * Database          |
 * cpsc110           |
 * jokes             |
 * mysql             |

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;