Washington University

2024-10-24T15:29:47Z

Paulmheider:

2024-10-12T18:17:48Z

Paulmheider:

Washington University.

Washington University

2024-09-24T17:09:25Z

Paulmheider: Created page with "Washington University"

Washington University

Databases and MySQL

2009-08-11T19:27:20Z

Paulmheider:

=SQL With MySQL=

SQL (Structured Query Language) is a language used to access the database files. While SQL is the basis for database communication, database vendors usually add their own small modifications. In order to use SQL, you need to use a client that communicates with the server. Some high-level languages have the libraries for that purpose. Alternatively, you can use ''mysql'' to communicate with the server.

=Installing MySQL=

We will need both mysql server and mysql client. You can install both packages:
apt-get install mysql-server mysql-client

When the server is being installed, debian will ask you the root password for the server. Don't leave it empty and pick a ''root'' password. You will need this password when you access your database server to administrate it. If you setup a password and forget it, you can reset it later with some extra work.

Debian repesitories contain several tools that help you manage MySQL databases easily. A very powerful MySQL administration tool is PhpMyAdmin that is accessed over the web. You can install it with
apt-get install phpmyadmin

And then, add this line to the end of your /etc/apache2/apache2.conf:
Include /etc/phpmyadmin/apache.conf
=Using MySQL=
==Starting MySQL==

The interface to mysql is ''mysql'' command. This command invoked typically with the following format
mysql -u USERNAME -pPASSORD -hHOST databasename

Note that, the user needs to have access to the database otherwise, MySQL will prompt an error. See the next section to learn how to give users access to databases

==Managing Users==
The users in a MySQL database are stored in the "user" table of the "mysql" database. You can add a user by adding a row to the ''user'' table (see SQL commands in later sections). You can access the mysql database at localhost with:

mysql -u root -pyourpassword mysql

The fields of this table are:

<code>
<pre>
+-----------------------+-----------------------------------+------+-----+---------+-------+
| 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 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+

</pre>
</code>

Important fields are Host (specifies from which host a user can access the database), User (name of the user), Password (the password for this user), and several boolean privilege attributes. MySQL provides ''grant'' command that inserts a new row to this table with most of attributes set automatically. The format of ''grant'' is
grant PRIVILEGE_NAMES on DATABASE.TABLE to 'USERNAME'@'HOSTNAME' identified by 'USERPASSWORD';

The privileges could be individual privileges, such as SELECT, INSERT, DROP etc., or it could be a group of privileges (USAGE) or it could be ALL privileges. The table name could be a wild card (*), the hostname can be anyhost, or it could contain a wild card '%'. Please note that, if you have multiple client hosts to access this database, you would need to grant permissions for each host.

The following command gives all priviliges to the user ''boss' who has the password ''employer'' if the user connects from the localhost.

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

After each grant, you need to tell mysql server to reload grant tables by flushing privileges.
flush privileges;

==Managing Databases==
The databases in mysql are crated with
create database DATABASENAME;
command. You should have administrative rights to be able to do that. A database can be deleted with ''drop'' command:
drop database DATABASENAME;

In mysql command prompt, you can change your active database with
use DATABASE;

Once the active database is changed, all your command will be applied to the new 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'', ''drop table'' command. When creating a table, the user needs to supply the attribute information.

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,date etc. MySQL manual contains full list of available types [[http://dev.mysql.com/doc/refman/5.0/en/data-types.html]]

For example, the following command will create a new table called employee
create table employee ( name char(20), dept char(20), jobtitle char(20));

You can see the tables in a database with
show tables;
command. You can get a detailed description of 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
<code>
<pre>
+-------+------------+---------------+
| name | dept | jobtitle |
+-------+------------+---------------+
| Alice | Wonderland | Lost traveler |
| Peter | Neverland | Leader |
| Frodo | Shire | Ring beerer |
+-------+------------+---------------+
</pre>
</code>

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.
<code>
<pre>
$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>";
}

</pre>
</code>

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 [http://us2.php.net/manual/en/ref.mysql.php]

=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.

CSE330 Wiki - User contributions [en]

Washington University

Washington University

Washington University

Databases and MySQL