Difference between revisions of "Databases and MySQL"

From CSE330 Wiki
Jump to navigationJump to search
(Removed Ubuntu support)
 
(86 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 
=SQL With 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.  Alternatively, you can use the command line program ''mysql'' to communicate with the server.
+
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=
 
=Installing MySQL=
  
−
We will need both mysql server and mysql client. You can install both packages:
+
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 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.
+
yum install mysql-server  
  
−
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
+
next enable then start the service with the following commands
−
  apt-get install phpmyadmin
+
sudo /sbin/chkconfig --add mysqld
 +
sudo /sbin/chkconfig mysqld on
 +
sudo /sbin/service mysqld start
 +
After the server is installed, enter the command
 +
  mysqladmin -u root password 'your-password-here'
 +
to choose 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. Currently there are only rpms in the yum repository for the old version which does not yet support our version of php.
 +
 
 +
If you wish to install phpMyAdmin do the following:
 +
 
 +
1) Download the latest version of the software from [http://www.phpmyadmin.net/home_page/downloads.php here] I am using 3.4.5 in this example. Downloading the file with the .tar.gz extension is probably the easiest way to install it on Amazon EC2.
 +
 
 +
2) Copy the file to your Amazon instance.
 +
 
 +
For Mac/Linux/Cygwin users:
 +
scp -i myAmazonKey.pem phpMyAdmin-3.4.5-all-languages.tar.gz  myUserName@myec2host.amazon.com:~/.
 +
 
 +
 
 +
In Windows use winscp or cygwin command line:
 +
 
 +
This will copy the phpMyAdmin file to the home directory of myUserName.
 +
 
 +
3) Extract the software on the Amazon instance:
 +
tar -xzvf phpMyAdmin-3.4.5-all-languages.tar.gz
 +
 
 +
4) Rename the directory and set permissions
 +
mv phpMyAdmin-3.4.5-all-languages  phpmyadmin
 +
sudo chown -R apache.apache phpmyadmin
 +
 
 +
5) Move the directory to /var/www/html
 +
sudo mv phpmyadmin /var/www/html
 +
 
 +
6) Delete the tar file and install additional packages
 +
rm phpMyAdmin-3.4.5-all-languages.tar.gz
 +
  sudo yum install php-mysql
 +
sudo yum install php-mcrypt
 +
 
 +
7) Setup permissions in phpmyadmin folder
 +
cd /var/www/html/phpmyadmin/
 +
 
 +
8) Copy and edit the sample php config
 +
cp config.sample.inc.php config.inc.php
 +
sudo vi config.inc.php
 +
 
 +
Edit this line:
 +
$cfg['blowfish_secret'] = 'mySecretHere'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
 +
 
 +
 
 +
9) Restart apache service
 +
 
 +
sudo service httpd restart
 +
 
 +
10) connect to myphpadmin/
 +
 
 +
http://ec2-MY-INSTANCE-HERE.compute-1.amazonaws.com/phpmyadmin/
 +
 
 +
Login with a mysql root account that you have create (Not it is generally considered a good idea to create a separate phpadmin account to login to this webpage as well as use https to communicate with this wegpage) Ask Google for more information. Additional information can be found [http://wiki.phpmyadmin.net/pma/Quick_Install here]
  
−
And then, add this line to the end of your /etc/apache2/apache2.conf:
 
−
Include /etc/phpmyadmin/apache.conf
 
 
=Using MySQL=
 
=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==
 
==Starting MySQL==
  
−
The interface to mysql is ''mysql'' command.  This command invoked typically with the following format
+
The interface to mysql is the ''mysql'' command.  It is generally run like this:
−
  mysql -u USERNAME -pPASSORD -hHOST databasename
+
 
 +
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:
  
−
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
+
mysql -u me -p -h somewhere.dyndns.org mydb
  
−
==Managing Users==
+
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):
−
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
+
  mysql -u root -p mysql
  
−
The fields of this table are:  
+
You will be prompted for a password, which in this case is the root password you selected when you installed mysql. 
 +
 
 +
==Creating and Managing Users==
 +
User information is stored in the ''user'' table. The fields of this table are:  
  
 
<code>
 
<code>
Line 73: Line 141:
 
| max_user_connections  | int(11) unsigned                  | NO  |    | 0      |      |
 
| max_user_connections  | int(11) unsigned                  | NO  |    | 0      |      |
 
+-----------------------+-----------------------------------+------+-----+---------+-------+
 
+-----------------------+-----------------------------------+------+-----+---------+-------+
−
 
 
</pre>
 
</pre>
 
</code>
 
</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
+
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. Note that ''grant'' can be used to both manage the privileges of existing users and to create new users.  
−
  grant PRIVILEGE_NAMES on DATABASE.TABLE to 'USERNAME'@'HOSTNAME' identified by 'USERPASSWORD';
+
 
 +
===Creating Users===
 +
The syntax of ''GRANT'' when used to create a user is the following:
 +
 
 +
  GRANT PRIVILEGE_NAMES on DATABASE.TABLE to USERNAME@'HOSTNAME' identified by 'USERPASSWORD';
 +
 
 +
There is also a ''CREATE USER'' command which creates a user with no privileges (you will have to grant them privileges later.
 +
The syntax of ''CREATE USER'' which is very similar to that of grant is described below:
 +
CREATE USER USER_NAME@'HOST_NAME' IDENTIFIED BY 'password';
  
−
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 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.
  
−
The following command gives all priviliges to the user ''boss' who has the password ''employer'' if the user connects from the localhost.
+
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';
+
  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:
  
−
After each grant, you need to tell mysql server to reload grant tables by flushing privileges.
 
 
   flush privileges;
 
   flush privileges;
 +
 +
===Granting Privileges to Existing Users===
 +
Additionally, ''GRANT'' is used to manage privileges for existing users.
 +
 +
To grant ''SELECT'' privileges to user ''david@%'' on the table ''products'' in database ''info'' we use the following command:
 +
GRANT SELECT ON info.products TO david@'%';
 +
 +
Note that we can also grant users the INSERT, SELECT and UPDATE privileges to a user on a per column basis.
 +
To grant ''SELECT'' privileges to user ''TODD@%'' only for the column ''GPA'' in the table ''student_info'' database ''info'' we use the following command:
 +
GRANT SELECT(student_info) TO TODD@'%' ON info.student_info;
 +
 +
As before, after each grant you must tell MySQL to reload the grant tables by flushing the privileges:
 +
FLUSH PRIVILEGES;
 +
 +
===Granting the Ability to Grant: With Grant Option===
 +
Additionally, in order to allow the user to grant other users privileges you must add ''WITH GRANT OPTION'' to your grant statement. For example:
 +
GRANT SELECT on powerplant.reactions, TO homer@'powerplant.springfield.gov' WITH GRANT OPTION;
 +
The above command not only allows user ''homer@powerplant.springfield.gov'' to ''SELECT'' from the powerplant database's reactions table, but also allows him to grant other users the ability to select on those databases.
 +
 +
i.e. Homer can issue the following command:
 +
GRANT SELECT ON powerplant.reactions TO moe@'moestavern';
 +
 +
===A Note on Privileges===
 +
Privileges in MySQL are highly flexible in terms of specificity, that is to say that you can grant a user specific privileges such as SELECT on a database, table, or column level. Note that only the SELECT, INSERT, and UPDATE privileges are available at the column level, if this seems confusing to you, take a moment to think about why this might be. For instance, why shouldn't there exist a column-level DROP privilege?
 +
 +
The flexibility allows a database administrator (you) to give other users access only to the minimal level of privileges that they require, in accordance with the [http://en.wikipedia.org/wiki/Principle_of_least_privilege principal of least privilege].
  
 
==Managing Databases==
 
==Managing Databases==
−
The databases in mysql are crated with
+
Databases in mysql are created with:
 +
 
 
  create database DATABASENAME;
 
  create database DATABASENAME;
−
command. You should have administrative rights to be able to do that. A database can be deleted with ''drop'' command:
+
 
 +
The user must have administrative rights in order to create a database. A database can be deleted with the ''drop'' command:
 +
 
 
  drop database DATABASENAME;
 
  drop database DATABASENAME;
  
−
In mysql command prompt, you can change your active database with
+
At the mysql command prompt, you can change your active database like so:
 +
 
 
  use DATABASE;
 
  use DATABASE;
  
−
Once the active database is changed, all your command will be applied to the new active 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:
  
−
For example, the following commands will create and switch to a new database
 
 
  create database company;
 
  create database company;
 
  use company;
 
  use company;
  
 
==Managing Tables==
 
==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.
+
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,.....);
 
  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]]
+
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 [[http://dev.mysql.com/doc/refman/5.0/en/data-types.html]].
  
−
For example, the following command will create a new table called employee
+
For example, the following command will create a new table called employee:
−
  create table employee ( name char(20), dept char(20), jobtitle char(20));
+
 
 +
  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:
  
−
You can see the tables in a database with
 
 
  show tables;
 
  show tables;
−
command. You can get a detailed description of a table with:
 
−
describe TABLENAME;
 
  
 +
You can get a detailed description of the columns in a table with:
  
 +
describe TABLENAME;
  
 
==SQL Commands==
 
==SQL Commands==
 
===Inserting New Data===
 
===Inserting New Data===
−
''insert into'' command inserts a new row to the database
+
====Inserting Entries Manually====
 +
The ''insert into'' command inserts a new row into a table in a database.  The general form of the command is:
 +
 
 
  insert into TABLENAME values (ATTRIBUTE1_VALUE, ATTRIBUTE2_VALUE,.....)
 
  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:
+
 
 +
The above commands adds one row the table TABLENAME.  The number of values supplied in parenthesis must be equal to the number of columns in the table, and the values are added in the same order as the order of the columns.
 +
 
 +
If you want to insert a new row with only some values set in the row, you can specify which columns to use:
 +
 
 
  insert into TABLENAME (ATTIBUTE1,ATTRIBUTE2) values (ATTRIBUTE1_VALUE, ATTRIBUTE2_VALUE)
 
  insert into TABLENAME (ATTIBUTE1,ATTRIBUTE2) values (ATTRIBUTE1_VALUE, ATTRIBUTE2_VALUE)
  
 
For example, the following commands will insert new employees to the company database.
 
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 ("Alice","Sales","Lost traveler","2000-03-09",4);
−
  insert into employee values ("Peter","Neverland","Leader");
+
insert into employee values ("Peter","Management","Leader","2004-06-13",59);
−
  insert into employee values ("Frodo","Shire","Ring beerer");
+
insert into employee values ("Frodo","Accounting","Ring bearer","2002-01-01",17);
 +
 
 +
Here is another example that leaves some of the fields in the new row blank:
 +
 
 +
insert into employee (name,jobtitle) values ("Fry","Delivery Boy");
 +
====Loading Data From a File====
 +
The ''load data infile'' command populates a table using data stored in a text file. The general form of the command is:
 +
LOAD DATA INFILE 'dataFile.txt' INTO TABLE table_name;
 +
where ''dataFile.txt'' is located relative to the server's data directory if a path name with leading components is given, otherwise if no leading components are given, the server assumes the file is in the default database's database directory. Also, if an absolute path is specified, the server uses it. For this Command to work, you must issue the following command to grant the FILE privilege to your user
 +
GRANT FILE on *.* to 'user'@'host';
 +
 
 +
Alternatively, and quite usefully, you can load data from a local file (a file residing on the same machine as the client) using the LOCAL keyword. When loading data from the local machine you do not need to grant your user the FILE privilege, think about why this might be the case.
 +
Hint: what is going on behind the scenes when you load the data in from a local machine when your database is hosted on a different machine?
 +
when the local keyword is used, it is best to specify an absolute path as in the following:
 +
LOAD DATA LOCAL INFILE '/home/linus_torvalds/Documents/dataFile.txt' INTO TABLE table_name;
 +
 
 +
By default the text files are assumed to have tab-delimited columns with column values specified in the same order as they are defined in the table (though you can specify additional/alternative delimiters using additional options).
 +
 
 
===Retrieving Existing Data===
 
===Retrieving Existing Data===
−
''select'' command returns the rows from a database where some conditions are matched.
+
The ''select'' command returns rows from a table, optionally based on some conditions:
 +
 
 
  select ATTRIBUTE1,ATTRIBUTE2,..... from TABLE [where CONDITION]
 
  select ATTRIBUTE1,ATTRIBUTE2,..... from TABLE [where CONDITION]
  
−
Instead of individual attributes, you can ask to receive all row with ''select *''
+
In this case, you specify which columns to retrieve with the ATTRIBUTE fields, from table TABLE.  You can also specify the ''*'' attribute that is used to return all columns in the table.  For example:
  
−
For example
 
 
  select * from employee;
 
  select * from employee;
−
will return
+
 
 +
This might return:
 
<code>
 
<code>
 
<pre>
 
<pre>
−
+-------+------------+---------------+
+
+-------+------------+---------------+------------+----+
−
| name  | dept      | jobtitle      |
+
| name  | dept      | jobtitle      | joined    | id |
−
+-------+------------+---------------+
+
+-------+------------+---------------+------------+----+
−
| Alice | Wonderland | Lost traveler |
+
| Alice | Wonderland | Lost traveler | 2000-03-09 | 4  |
−
| Peter | Neverland  | Leader        |
+
| Peter | Neverland  | Leader        | 2004-06-13 | 59 |
−
| Frodo | Shire      | Ring beerer   |
+
| Frodo | Shire      | Ring bearer   | 2002-01-01 | 17 |
−
+-------+------------+---------------+
+
| Fry  |            | Delivery Boy  |            |    |
 +
+-------+------------+---------------+------------+----+
 
</pre>
 
</pre>
 
</code>
 
</code>
  
−
or you can get Peter's title.
+
Here is another example to only get get Peter's job title:
−
  select `jobtitle` from employee where `name` like 'Peter';
+
 
 +
  select jobtitle from employee where name='Peter';
 +
====Joins====
 +
To quote the [http://en.wikipedia.org/wiki/Join_%28SQL%29 wikepedia entry on joins],
 +
A JOIN is a means for combining fields from two [or more] tables by using values common to each.
 +
 
 +
See the tutorial [http://www.w3schools.com/sql/sql_join.asp here] for a good explanation of how to use joins (see the next 4 chapters for explanations of inner, left, right and full joins).
 +
 
 +
====Aggregate Functions====
 +
The following is a brief overview of aggregate functions, however it is highly recommended that you go over [http://www.mysqltutorial.org/mysql-aggregate-functions.aspx this tutorial].
 +
Aggregate functions are functions which take multiple data entries in one column or group and return a single value.
 +
MySQL's aggregate functions include AVG (average), STD (standard deviation), SUM, MAX, MIN, and COUNT.
 +
Aggregate functions are often, if not necessarily, used in conjunction with the GROUP BY clause which uses a field in the table to define the groups (sets) on which to apply the aggregate function.
 +
The HAVING clause is used to filter the aggregate data and is similar in function to the where clause.
  
 
===Updating Existing Data===
 
===Updating Existing Data===
−
''update'' command is used to update individual rows. The format is
+
The ''update'' command is used to modify existing data in a table. The format is similar to ''select'':
−
  update TABLENAME set ATTRIBUTE1=value1,ATTRIBUTE2=value2 .... [where conditions]
+
 
 +
  update TABLENAME set ATTRIBUTE1=value1,ATTRIBUTE2=value2 .... [where CONDITION]
  
−
For example, the following will update Frodo's job title.
+
For example, this will update Frodo's department:
  
−
  update employee set `jobtitle`='Hero' where `name` like 'Frodo'
+
  update employee set jobtitle='Hero' where name='Frodo';
  
 
===Deleting Existing Data===
 
===Deleting Existing Data===
−
''delete from'' command is used to delete row(s) from the database
+
The ''delete from'' command is used to delete rows from a table.  Again, the format is similar to the previous commands:
 +
 
 
  delete from employee where CONDITION;
 
  delete from employee where CONDITION;
  
−
For example, the following command will delete Frodo.
+
For example, the following command will delete Fry from the employee table:
 +
 
 +
delete from employee where name='Alice';
 +
 
 +
You can also use wild cards in the CONDITIONS for any SQL command.  For example, this command would delete both Fry and Frodo from the employee table:
  
−
  delete from employee where `name` like 'Frodo';
+
  delete from employee where name like 'Fr%';
−
where as
+
 
−
  delete from  employee;
+
In this case the CONDITION change from ''name='' to ''name like'' and the comparator uses the SQL wild card (%) to match any number of characters.  The above command, then, deletes all row where the name column starts with ''Fr''
−
deletes everything from the table.
+
 
 +
You can also delete all rows from a table with:
 +
 
 +
  delete from employee;
 +
 
 +
==Keys==
 +
 
 +
===Primary Keys===
 +
A primary key is one or many data fields, which taken together can uniquely identify a row in a table.
 +
Therefore, there cannot be rows with equivalent primary keys within one table.
 +
 
 +
See the Wikipedia entry on [http://en.wikipedia.org/wiki/Unique_key unique keys]:
 +
Note that primary keys are a more specific case of unique keys.
 +
 
 +
====Examples====
 +
Use the following syntax to define a primary key in a CREATE TABLE statement
 +
  CREATE TABLE(
 +
              employee_id INT, first_name CHAR(50), last_name CHAR(50),
 +
              PRIMARY KEY(employee_ID)
 +
              );
 +
 
 +
There is an abbreviated syntax for declaring primary keys comprised of only one field. Here is the CREATE TABLE statement to define the table using the shorter syntax:
 +
CREATE TABLE(
 +
              employee_id INT PRIMARY KEY,  first_name CHAR(50), last_name CHAR(50)
 +
            );
 +
 
 +
 
 +
Consider the following command which creates a table containing the dates of the last emissions check for cars identified by their license plates' state and numbers.
 +
CREATE TABLE(
 +
              state CHAR(2), license_plate_number CHAR(7), last_emissions_check DATE,
 +
              PRIMARY KEY(state, license_plate_number)
 +
            );
 +
 +
The plate number alone is not enough to uniquely identify a row because there may be a car with the same plate number from a different state. So we define what is called a ''composite primary key'' which uses both the state and license_plate_number fields. ''Composite keys'' are so named because they are composed of multiple fields.
 +
 
 +
===Foreign Keys===
 +
 
 +
See the wikipedia entry for [http://en.wikipedia.org/wiki/Foreign_key foreign keys] for a nice definition of what a foreign key is.
 +
 
 +
[http://articles.techrepublic.com.com/5100-10878_11-6035435.html Here] is a brief intro to foreign keys in MySQL, which is worth a read if you have never used foreign keys before.
 +
 
 +
[http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html This] is the MySQL reference manual page on foreign keys. This is a more in depth document which details foreign keys in MySQL's InnoDB. It is highly recommended that you look it over, paying careful attention to the section enumerating the conditions to which foreign keys are subject.
  
 
=PHP and MySQL=
 
=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.
+
In order to use mysql function in php, you need to have the the ''php-mysql'' and ''php-mcrypt'' packages installed.  This is most likely already installed, but it's ok to try to install it again like normal with yum install.  If it's already on your system, yum install will not do anything. Once installed, you can use PHP to connect to a mysql database by using the ''mysql_connect'' PHP function:
 +
 
 +
$sqlserver=mysql_connect('HOSTNAME','DATABASEUSER','PASSWORD');
 +
 
 +
The HOSTNAME is either an IP address or DNS name of the machine running the mysql server, the DATABASEUSER is the name of a user who can run the required commands, and the PASSWORD is that user's password. ''mysql_connect'' returns a mysql object that you will use later to communicate with the server. If the connection fails, $sqlserver will be NULL.
 +
 
 +
Once connected, you can select a database with the ''mysql_select_db'' PHP function:
  
−
  $link=mysql_connect('HOSTNAME','DATABASEUSER','PASSWORD');
+
  mysql_select_db('DATABASENAME',$sqlserver);
  
−
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:
+
This would use the connection opened earlier ($sqlserver) to access the DATABASENAME 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
+
Note that the connection automatically closes as soon as the script finishes execution.
−
mysql_close($link);
+
However, you can explicitly close a connection with ''mysql_close'':
  
−
SQL commands are send through ''mysql_query'' command.
+
mysql_close($sqlserver);
  
−
mysql_query(SQLQUERY)
+
SQL commands are sent to the database through the ''mysql_query'' command:
  
−
for example,
+
mysql_query(SQLQUERY, $sqlserver)
−
  mysql_query("insert into employee values ('Sheridan','B5','Commander')") or die('Error, insert query failed');
+
 
 +
SQLQUERY is any valid SQL statement, including select, delete, update, and insert statements.  Here is an example:
 +
 
 +
  mysql_query("insert into employee values ('Sheridan','Command','Commander','2006-02-25)", $sqlserver) or die('Error, insert query failed');
 +
 
 +
This example inserts a new row into the employee table, or causes the PHP script to stop processing with ''die'' is an error occurs.  ''mysql_query'' also receives data from the server. For example, if you run a ''select'' query, then you can loop over the results for each returned row:
  
−
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>
 
<code>
−
<pre>  
+
<pre>
−
   $result = mysql_query('select * from employee');
+
   $result = mysql_query('select * from employee',$sqlserver);
  
 
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
 
   {
 
   {
−
     echo "Name :{$row['name']} <br>" .
+
     echo "Name: {$row['name']} <br>" .
−
         "Department : {$row['dept']} <br>" .
+
         "Department: {$row['dept']} <br>" .
−
         "Job Title : {$row['jobtitle']} <br><br>";
+
         "Job Title: {$row['jobtitle']} <br>";
 
   }
 
   }
−
 
 
</pre>
 
</pre>
 
</code>
 
</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.
+
After the select query is sent, 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 values can be reached by the name of the column from which the data came. If you want a numeric array, you can use MYSQL_NUM. The default is MYSQL_BOTH. If you are interested in the total number of rows returned, you can call the ''mysql_num_rows'' function.
 +
 
 +
The [http://us2.php.net/manual/en/ref.mysql.php PHP manual] has fairly detailed descriptions of each mysql related function, it is a great reference if you're not sure of how to do something in PHP.
 +
 
 +
==Protecting against SQL Injection Attacks==
 +
SQL injection attacks pose a major security risk to the security and integrity of your database if you do not protect from them.
  
−
PHP Manual has a very detailed description of each mysql related function [http://us2.php.net/manual/en/ref.mysql.php]
+
See [http://www.tizag.com/mysqlTutorial/mysql-php-sql-injection.php this] for a very helpful tutorial on protecting against SQL injection.
  
 
=Other MySQL Information=
 
=Other MySQL Information=
 +
The configuration file for mysql is  ''/etc/mysql/my.cnf''. It specifies mysql directories and other parameters.  For example, if you want to migrate all the data from one mysql server to another, it is sufficient to copy files from ''datadir''. Another interesting parameter is ''bind-address''. By default, it restricts the access to mysql to the localhost, so you will probably want to comment it out, thereby allowing access from any host.
  
−
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.
+
''mysqladmin'' is the official command line program to communicate with the server for management tasks related to the server.  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 root password selected when the mysql server was installed.
  
−
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.
+
[[Category:Obsolete Content]]

Latest revision as of 17:19, 24 August 2017

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: 

yum install mysql-server

next enable then start the service with the following commands 

sudo /sbin/chkconfig --add mysqld
sudo /sbin/chkconfig mysqld on
sudo /sbin/service mysqld start

After the server is installed, enter the command 

 mysqladmin -u root password 'your-password-here'

to choose 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. Currently there are only rpms in the yum repository for the old version which does not yet support our version of php.

If you wish to install phpMyAdmin do the following:

1) Download the latest version of the software from here I am using 3.4.5 in this example. Downloading the file with the .tar.gz extension is probably the easiest way to install it on Amazon EC2.

2) Copy the file to your Amazon instance.

For Mac/Linux/Cygwin users: 

scp -i myAmazonKey.pem phpMyAdmin-3.4.5-all-languages.tar.gz  myUserName@myec2host.amazon.com:~/.


In Windows use winscp or cygwin command line:

This will copy the phpMyAdmin file to the home directory of myUserName.

3) Extract the software on the Amazon instance: 

tar -xzvf phpMyAdmin-3.4.5-all-languages.tar.gz

4) Rename the directory and set permissions 

mv phpMyAdmin-3.4.5-all-languages  phpmyadmin
sudo chown -R apache.apache phpmyadmin

5) Move the directory to /var/www/html 

sudo mv phpmyadmin /var/www/html

6) Delete the tar file and install additional packages 

rm phpMyAdmin-3.4.5-all-languages.tar.gz
sudo yum install php-mysql
sudo yum install php-mcrypt

7) Setup permissions in phpmyadmin folder 

cd /var/www/html/phpmyadmin/

8) Copy and edit the sample php config 

cp config.sample.inc.php config.inc.php
sudo vi config.inc.php

Edit this line: 

$cfg['blowfish_secret'] = 'mySecretHere'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */


9) Restart apache service 

sudo service httpd restart

10) connect to myphpadmin/

http://ec2-MY-INSTANCE-HERE.compute-1.amazonaws.com/phpmyadmin/

Login with a mysql root account that you have create (Not it is generally considered a good idea to create a separate phpadmin account to login to this webpage as well as use https to communicate with this wegpage) Ask Google for more information. Additional information can be found here

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.

Creating and 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. Note that grant can be used to both manage the privileges of existing users and to create new users.

Creating Users

The syntax of GRANT when used to create a user is the following: 

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

There is also a CREATE USER command which creates a user with no privileges (you will have to grant them privileges later. The syntax of CREATE USER which is very similar to that of grant is described below: 

CREATE USER USER_NAME@'HOST_NAME' IDENTIFIED BY 'password';

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;

Granting Privileges to Existing Users

Additionally, GRANT is used to manage privileges for existing users.

To grant SELECT privileges to user david@% on the table products in database info we use the following command: 

GRANT SELECT ON info.products TO david@'%';

Note that we can also grant users the INSERT, SELECT and UPDATE privileges to a user on a per column basis. To grant SELECT privileges to user TODD@% only for the column GPA in the table student_info database info we use the following command: 

GRANT SELECT(student_info) TO TODD@'%' ON info.student_info;

As before, after each grant you must tell MySQL to reload the grant tables by flushing the privileges: 

FLUSH PRIVILEGES;

Granting the Ability to Grant: With Grant Option

Additionally, in order to allow the user to grant other users privileges you must add WITH GRANT OPTION to your grant statement. For example: 

GRANT SELECT on powerplant.reactions, TO homer@'powerplant.springfield.gov' WITH GRANT OPTION;

The above command not only allows user homer@powerplant.springfield.gov to SELECT from the powerplant database's reactions table, but also allows him to grant other users the ability to select on those databases.

i.e. Homer can issue the following command: 

GRANT SELECT ON powerplant.reactions TO moe@'moestavern';

A Note on Privileges

Privileges in MySQL are highly flexible in terms of specificity, that is to say that you can grant a user specific privileges such as SELECT on a database, table, or column level. Note that only the SELECT, INSERT, and UPDATE privileges are available at the column level, if this seems confusing to you, take a moment to think about why this might be. For instance, why shouldn't there exist a column-level DROP privilege?

The flexibility allows a database administrator (you) to give other users access only to the minimal level of privileges that they require, in accordance with the principal of least privilege.

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

Inserting Entries Manually

The insert into command inserts a new row into a table in a database. The general form of the command is: 

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

The above commands adds one row the table TABLENAME. The number of values supplied in parenthesis must be equal to the number of columns in the table, and the values are added in the same order as the order of the columns.

If you want to insert a new row with only some values set in the row, you can specify which columns to use: 

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","Sales","Lost traveler","2000-03-09",4);
insert into employee values ("Peter","Management","Leader","2004-06-13",59);
insert into employee values ("Frodo","Accounting","Ring bearer","2002-01-01",17);

Here is another example that leaves some of the fields in the new row blank: 

insert into employee (name,jobtitle) values ("Fry","Delivery Boy");

Loading Data From a File

The load data infile command populates a table using data stored in a text file. The general form of the command is: 

LOAD DATA INFILE 'dataFile.txt' INTO TABLE table_name;

where dataFile.txt is located relative to the server's data directory if a path name with leading components is given, otherwise if no leading components are given, the server assumes the file is in the default database's database directory. Also, if an absolute path is specified, the server uses it. For this Command to work, you must issue the following command to grant the FILE privilege to your user 

GRANT FILE on *.* to 'user'@'host';

Alternatively, and quite usefully, you can load data from a local file (a file residing on the same machine as the client) using the LOCAL keyword. When loading data from the local machine you do not need to grant your user the FILE privilege, think about why this might be the case. Hint: what is going on behind the scenes when you load the data in from a local machine when your database is hosted on a different machine? when the local keyword is used, it is best to specify an absolute path as in the following: 

LOAD DATA LOCAL INFILE '/home/linus_torvalds/Documents/dataFile.txt' INTO TABLE table_name;

By default the text files are assumed to have tab-delimited columns with column values specified in the same order as they are defined in the table (though you can specify additional/alternative delimiters using additional options).

Retrieving Existing Data

The select command returns rows from a table, optionally based on some conditions: 

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

In this case, you specify which columns to retrieve with the ATTRIBUTE fields, from table TABLE. You can also specify the * attribute that is used to return all columns in the table. For example: 

select * from employee;

This might return: 

+-------+------------+---------------+------------+----+
| name  | dept       | jobtitle      | joined     | id |
+-------+------------+---------------+------------+----+
| Alice | Wonderland | Lost traveler | 2000-03-09 | 4  |
| Peter | Neverland  | Leader        | 2004-06-13 | 59 |
| Frodo | Shire      | Ring bearer   | 2002-01-01 | 17 |
| Fry   |            | Delivery Boy  |            |    |
+-------+------------+---------------+------------+----+

Here is another example to only get get Peter's job title: 

select jobtitle from employee where name='Peter';

Joins

To quote the wikepedia entry on joins, A JOIN is a means for combining fields from two [or more] tables by using values common to each.

See the tutorial here for a good explanation of how to use joins (see the next 4 chapters for explanations of inner, left, right and full joins).

Aggregate Functions

The following is a brief overview of aggregate functions, however it is highly recommended that you go over this tutorial. Aggregate functions are functions which take multiple data entries in one column or group and return a single value. MySQL's aggregate functions include AVG (average), STD (standard deviation), SUM, MAX, MIN, and COUNT. Aggregate functions are often, if not necessarily, used in conjunction with the GROUP BY clause which uses a field in the table to define the groups (sets) on which to apply the aggregate function. The HAVING clause is used to filter the aggregate data and is similar in function to the where clause.

Updating Existing Data

The update command is used to modify existing data in a table. The format is similar to select: 

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

For example, this will update Frodo's department: 

update employee set jobtitle='Hero' where name='Frodo';

Deleting Existing Data

The delete from command is used to delete rows from a table. Again, the format is similar to the previous commands: 

delete from employee where CONDITION;

For example, the following command will delete Fry from the employee table: 

delete from employee where name='Alice';

You can also use wild cards in the CONDITIONS for any SQL command. For example, this command would delete both Fry and Frodo from the employee table: 

delete from employee where name like 'Fr%';

In this case the CONDITION change from name= to name like and the comparator uses the SQL wild card (%) to match any number of characters. The above command, then, deletes all row where the name column starts with Fr

You can also delete all rows from a table with: 

delete from employee;

Keys

Primary Keys

A primary key is one or many data fields, which taken together can uniquely identify a row in a table. Therefore, there cannot be rows with equivalent primary keys within one table.

See the Wikipedia entry on unique keys: Note that primary keys are a more specific case of unique keys.

Examples

Use the following syntax to define a primary key in a CREATE TABLE statement 

CREATE TABLE( 
              employee_id INT, first_name CHAR(50), last_name CHAR(50),
              PRIMARY KEY(employee_ID)
             );

There is an abbreviated syntax for declaring primary keys comprised of only one field. Here is the CREATE TABLE statement to define the table using the shorter syntax: 

CREATE TABLE(
              employee_id INT PRIMARY KEY,  first_name CHAR(50), last_name CHAR(50)
            );


Consider the following command which creates a table containing the dates of the last emissions check for cars identified by their license plates' state and numbers. 

CREATE TABLE( 
              state CHAR(2), license_plate_number CHAR(7), last_emissions_check DATE,
              PRIMARY KEY(state, license_plate_number)
            );

The plate number alone is not enough to uniquely identify a row because there may be a car with the same plate number from a different state. So we define what is called a composite primary key which uses both the state and license_plate_number fields. Composite keys are so named because they are composed of multiple fields.

Foreign Keys

See the wikipedia entry for foreign keys for a nice definition of what a foreign key is.

Here is a brief intro to foreign keys in MySQL, which is worth a read if you have never used foreign keys before.

This is the MySQL reference manual page on foreign keys. This is a more in depth document which details foreign keys in MySQL's InnoDB. It is highly recommended that you look it over, paying careful attention to the section enumerating the conditions to which foreign keys are subject.

PHP and MySQL

In order to use mysql function in php, you need to have the the php-mysql and php-mcrypt packages installed. This is most likely already installed, but it's ok to try to install it again like normal with yum install. If it's already on your system, yum install will not do anything. Once installed, you can use PHP to connect to a mysql database by using the mysql_connect PHP function: 

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

The HOSTNAME is either an IP address or DNS name of the machine running the mysql server, the DATABASEUSER is the name of a user who can run the required commands, and the PASSWORD is that user's password. mysql_connect returns a mysql object that you will use later to communicate with the server. If the connection fails, $sqlserver will be NULL.

Once connected, you can select a database with the mysql_select_db PHP function: 

mysql_select_db('DATABASENAME',$sqlserver);

This would use the connection opened earlier ($sqlserver) to access the DATABASENAME database.

Note that the connection automatically closes as soon as the script finishes execution. However, you can explicitly close a connection with mysql_close: 

mysql_close($sqlserver);

SQL commands are sent to the database through the mysql_query command: 

mysql_query(SQLQUERY, $sqlserver)

SQLQUERY is any valid SQL statement, including select, delete, update, and insert statements. Here is an example: 

mysql_query("insert into employee values ('Sheridan','Command','Commander','2006-02-25)", $sqlserver) or die('Error, insert query failed');

This example inserts a new row into the employee table, or causes the PHP script to stop processing with die is an error occurs. mysql_query also receives data from the server. For example, if you run a select query, then you can loop over the results for each returned row: 

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

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

After the select query is sent, 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 values can be reached by the name of the column from which the data came. If you want a numeric array, you can use MYSQL_NUM. The default is MYSQL_BOTH. If you are interested in the total number of rows returned, you can call the mysql_num_rows function.

The PHP manual has fairly detailed descriptions of each mysql related function, it is a great reference if you're not sure of how to do something in PHP.

Protecting against SQL Injection Attacks

SQL injection attacks pose a major security risk to the security and integrity of your database if you do not protect from them.

See this for a very helpful tutorial on protecting against SQL injection.

Other MySQL Information

The configuration file for mysql is /etc/mysql/my.cnf. It specifies mysql directories and other parameters. For example, if you want to migrate all the data from one mysql server to another, it is sufficient to copy files from datadir. Another interesting parameter is bind-address. By default, it restricts the access to mysql to the localhost, so you will probably want to comment it out, thereby allowing access from any host.

mysqladmin is the official command line program to communicate with the server for management tasks related to the server. 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 root password selected when the mysql server was installed.

Retrieved from "http://classes.engineering.wustl.edu/cse330/index.php?title=Databases_and_MySQL&oldid=2356"