Databases and MySQL

SQL With MySQL

SQL (Structured Query Language) is a language used to interact with a relational database. While SQL is the standard basis for database communication, different database vendors usually add their own small modifications or enhancements. In order to use SQL, you need to use a client that communicates with the server. Many high-level languages like Java, C, and PHP have the libraries for that purpose. We will be using MySQL in this class (pronounced: My S-Q-L), which is one of the most widely used databases. You can use the command line program mysql to communicate with the server directly.

Installing MySQL

You will need both the mysql server and the mysql client. Installing the server automatically installs the client package, so you can get them both by running:

apt-get install mysql-server 

When the server is being installed, it will ask you for a root password for the mysql server. Pick anything you like for the password, but make sure you can remember it. You will need this password when you access your database server for administration purposes (like adding new databases or database users). If you forget the password, you can reset it later, but it's not a simple process.

There are also several tools that can help you manage mysql databases easily. One in particular is is PhpMyAdmin that allows you to manage your database over the web. You can install it with:

apt-get install phpmyadmin

Installing phpmyadmin will automatically configure Apache for you if you let it. When installing, select Apache2 with the space bar when it asks which databases to auto configure. Then selecte Yes when it asks if you want to configure the database for phpmyadmin. You will have to supply the root mysql password you chose when installing mysql. You will also have to add a phpmyadmin password for use later. Make sure to pick something you can remember.

Using MySQL

Each mysql server can host many different databases. Each database contains many tables, which store the actual information in the database. There is no relationship between data or tables in separate databases, i.e., each database should be a self contained entity.

mysql also has its own user structure which means that you will have to add mysql users and give them permissions to access specific databases.

Starting MySQL

The interface to mysql is the mysql command. It is generally run like this:

mysql -u USERNAME -p -h HOSTNAME DATABASENAME

Note that, the user USERNAME needs to have access to the database DATABASENAME, otherwise mysql will return an error. See the next section to learn how to give users access to databases. When you run the command as above, it will prompt you for a password, which is the password for user USERNAME. Also, if you are connecting to a mysql server on the local PC, you can leave off the -h HOSTNAME argument. For example, to connect to the mydb database on the local host with user me, do:

mysql -u me -p mydb

To do the same for a mysql server running on host somewhere.dyndns.org, do:

mysql -u me -p -h somewhere.dyndns.org mydb

All administrative information about the mysql server is contained in the mysql database on the server. To manage users, you need to access mysql as the root user for the mysql database (assuming you are on the same PC with the server):

mysql -u root -p mysql

You will be prompted for a password, which in this case is the root password you selected when you installed mysql.

Managing Users

User information is stored in the user table. The fields of this table are:

+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field                 | Type                              | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host                  | char(60)                          | NO   | PRI |         |       |
| User                  | char(16)                          | NO   | PRI |         |       |
| Password              | char(41)                          | NO   |     |         |       |
| Select_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv          | enum('N','Y')                     | NO   |     | N       |       |
| File_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv            | enum('N','Y')                     | NO   |     | N       |       |
| References_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv      | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type              | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher            | blob                              | NO   |     |         |       |
| x509_issuer           | blob                              | NO   |     |         |       |
| x509_subject          | blob                              | NO   |     |         |       |
| max_questions         | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates           | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections       | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections  | int(11) unsigned                  | NO   |     | 0       |       |
+-----------------------+-----------------------------------+------+-----+---------+-------+

The important fields are Host (which specifies from which host(s) a user can access the database), User (name of the user), Password (the password for this user), and several boolean privilege attributes. Fortunately, mysql provides the grant command that makes dealing with mysql users simpler. The format of grant is

grant PRIVILEGE_NAMES on DATABASE.TABLE to 'USERNAME'@'HOSTNAME' identified by 'USERPASSWORD';

The PRIVILEGE_NAMES could be individual privileges (SELECT, INSERT, DROP, etc.), it could be a group of privileges (USAGE), or it could be the special ALL privilege which grants USERNAME all possible privileges. The TABLE name can be, and often is, a wild card (*) which means that USERNAME is given the indicated privileges for all tables in the database DATABASE. The HOSTNAME can be any host name (somewhere.dyndns.org), it could contain the SQL wild card '%' (%.dynsdns.org) to allow access from multiple hosts that share a common domain, or it could be just the wild card (%) to indicate access should be granted from all hosts.

Here is an example that gives all privileges to the user boss' with the password employer, where the user connects only from the localhost.

grant all  on company.* to 'boss'@'localhost' identified by 'employer';

After each grant, you need to tell mysql to reload grant tables by flushing privileges:

 flush privileges;

Managing Databases

Databases in mysql are created with:

create database DATABASENAME;

The user must have administrative rights in order to create a database. A database can be deleted with the drop command:

drop database DATABASENAME;

At the mysql command prompt, you can change your active database like so:

use DATABASE;

Once the active database is changed, all your commands will be applied to the newly active database.

For example, the following commands will create and switch to a new database:

create database company;
use company;

Managing Tables

Similar to databases, tables are created and deleted with create table and drop table, respectively. When creating a table, the user needs to supply the attribute information for each column in the table.

create table TABLENAME (ATTRIBUTE TYPE, ATTRIBUTE TYPE,.....);

mysql contains several attribute types for strings, numerical values, dates, etc. The common types include char, varchar, text, blob, int, float, and date. The mysql manual contains a full list of available types [[1]].

For example, the following command will create a new table called employee:

create table employee (name char(40), dept char(20), jobtitle char(30), joined date, id int);

The employee table has 5 columns, a name string with a maximum length of 40 characters, a dept string with a maximum length of 20 characters, a jobtitle string with a maximum length of 30 characters, a joined date, and an id integer.

You can see the tables in a database by running:

show tables;

You can get a detailed description of the columns in a table with:

describe TABLENAME;

SQL Commands

Inserting New Data

insert into command inserts a new row to the database

insert into TABLENAME values (ATTRIBUTE1_VALUE, ATTRIBUTE2_VALUE,.....)

If you want to insert a new row with only some attributes set, you can do that by telling which attributes you want to be set:

insert into TABLENAME (ATTIBUTE1,ATTRIBUTE2) values (ATTRIBUTE1_VALUE, ATTRIBUTE2_VALUE)

For example, the following commands will insert new employees to the company database.

 insert into employee values ("Alice","Wonderland","Lost traveler");
 insert into employee values ("Peter","Neverland","Leader");
 insert into employee values ("Frodo","Shire","Ring beerer");

Retrieving Existing Data

select command returns the rows from a database where some conditions are matched.

select ATTRIBUTE1,ATTRIBUTE2,..... from TABLE [where CONDITION]

Instead of individual attributes, you can ask to receive all row with select *

For example

select * from employee;

will return

+-------+------------+---------------+
| name  | dept       | jobtitle      |
+-------+------------+---------------+
| Alice | Wonderland | Lost traveler |
| Peter | Neverland  | Leader        |
| Frodo | Shire      | Ring beerer   |
+-------+------------+---------------+

or you can get Peter's title.

select `jobtitle` from employee where `name` like 'Peter';

Updating Existing Data

update command is used to update individual rows. The format is

update TABLENAME set ATTRIBUTE1=value1,ATTRIBUTE2=value2 .... [where conditions]

For example, the following will update Frodo's job title.

update employee set `jobtitle`='Hero' where `name` like 'Frodo'

Deleting Existing Data

delete from command is used to delete row(s) from the database

delete from employee where CONDITION;

For example, the following command will delete Frodo.

delete from employee where `name` like 'Frodo';

where as

delete from  employee;

deletes everything from the table.

PHP and MySQL

In order to use mysql function in php, you need to install php5-mysql package first. Then from a PHP page, you can connect to a mysql database by using mysql_connect command.

$link=mysql_connect('HOSTNAME','DATABASEUSER','PASSWORD');

The hostname is either IP or name of the server running MySQL daemon, the database user is the name of the user who can run the required commands and password is the user's the user's password. mysql_connect returns a mysql object that you will use later to communicate with the server. If the connection fails, $link will be NULL. Once connected, you can select a database:

mysql_select_db('DATABASENAME',[$link]);

If $link is not specified, mysql_select_db tries to find an open connection.

You can close a connection with mysql_close

mysql_close($link);

SQL commands are send through mysql_query command.

mysql_query(SQLQUERY) 

for example,

mysql_query("insert into employee values ('Sheridan','B5','Commander')") or die('Error, insert query failed');

will insert data to employee table. mysql_query also receive the data from the server. For example, if you have send a select query, then you can loop over the results for each returned row.

 
  $result = mysql_query('select * from employee');

  while($row = mysql_fetch_array($result, MYSQL_ASSOC))
  {
    echo "Name :{$row['name']} <br>" .
         "Department : {$row['dept']} <br>" .
         "Job Title : {$row['jobtitle']} <br><br>";
  }

After the sql command is sent, the individual rows are fetched with mysql_fetch_array. The parameter MYSQL_ASSOC tells the function that the results should be in the format of an associative array, i.e., the attributes can be reached by their names. If you want a numeric array, you can use MYSQL_NUM. The default is MYSQL_BOTH, hence you can avoid specifying this parameter. If you are interested in the number of rows returned, you can call mysql_num_rows function.

PHP Manual has a very detailed description of each mysql related function [2]

Other MySQL Information

The configuration file for MySQL is /etc/mysql/my.cnf. It specifies MySQL directories and other parameters. If you want to migrate MySQL, it is sufficient to copy files from datadir, which is in debian located at /var/lib/mysql. One interesting parameter is bind-address. By default, it restricts the database access to the localhost, so you may want to comment it out.

The program mysqladmin is the official program to communicate with the server for managerial tasks. Common tasks that can be handled by mysqladmin include flushing some MySQL elements (such as tables, privileges etc.), kill mysql threads, restart, shutdown, or validate a client. In order to use this command, you need to provide the administrator password.