WHAT IS MYSQL?

MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language

What is mySQL and why do I need it?

This article applies to Linux Dedicated Servers.

This article explains the functionality of the mySQL database application.

MySQL is a relational database management system based on SQL – Structured Query Language. The application is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications.

The most common use for mySQL however, is for the purpose of a web database. It can be used to store anything from a single record of information to an entire inventory of available products for an online store.

In association with a scripting language such as PHP or Perl (both offered on our hosting accounts) it is possible to create websites which will interact in real-time with a mySQL database to rapidly display categorised and searchable information to a website user.

Difference between PHP and MySQL

Key difference: PHP is a server-side scripting language that has its main implementation in web development. However, it can be used as a general-purpose programming language. MySQL, on the other hand, is an open source relational database management system (RDBMS). MySQL is a popular choice of database for use in web applications.

PHP is a server-side scripting language that has its main implementation in web development. However, it can be used as a general-purpose programming language. PHP was originally created by Rasmus Lerdorf in 1995 and it is currently managed by The PHP Group. PHP originally stood for Personal Home Page, however it was later renamed. It now stands for PHP: Hypertext Preprocessor, a recursive acronym. PHP is free software released under the PHP License, as is incompatible with the GNU General Public License (GPL) due to restrictions on the usage of the term PHP.

PHP is an open source, server-side, HTML embedded scripting language. It can basically perform any task that other CGI programs can, but it is mainly used to create dynamic Web pages. Its main advantage is that it is compatible with many types of databases. Furthermore, PHP can talk across networks using IMAP, SNMP, NNTP, POP3, or HTTP.

PHP includes a command-line interface capability and can be used in standalone graphical applications. PHP commands can be embedded directly into an HTML source document rather than calling an external file to process data. In the HTML document, the PHP script is enclosed within special PHP tags. Due to these tags, the programmer can alternate between HTML and PHP instead of having to rely on heavy amounts of code to output HTML. Also, as PHP is executed on the server, the client cannot view the PHP code.

MySQL, on the other hand, is an open source relational database management system (RDBMS). MySQL is officially pronounced as “My S-Q-L”, but it is also called “My Sequel”. It is named after co-founder Michael Widenius’ daughter, My. SQL stands for Structured Query Language. MySQL is the world’s most used RDBMS, and runs as a server providing multi-user access to a number of databases. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, which is now owned by Oracle Corporation.

MySQL is a popular choice of database for use in web applications. It is a central component of the widely used ‘LAMP’ open source web application software stack, as well as other AMP stacks. LAMP stands for “Linux, Apache, MySQL, Perl/PHP/Python.” MySQL is often used by free-software-open source projects that require a full-featured database management system, such as TYPO3, Joomla, WordPress, phpBB, MyBB, Drupal, etc. MySQL is also used in many high-profile, large-scale World Wide Web products, including Wikipedia, Google, Facebook, Twitter, Flickr, Nokia.com, and YouTube.

The main difference between PHP and MySQL is that PHP is a scripting language, whereas MySQL is a relational database management system. They are two completely different things and hence are used for two different purposes.

As stated, PHP is a server-side scripting language. A server-side scripting language allows the user to embed little programs or scripts into the HTML of a Web page. In executing, such scripts allow the user to control what will actually appear in the browser window. These scripts more flexible than what is possible using straight HTML.

MySQL, on other hand, is a RDBMS, which allows the integration of a database either on the web or on a server. It can often be used in collaboration with PHP or other scripting languages such as JavaScript.

Examples of Common mysql Queries

Here are examples of how to solve some common problems with MySQL.

Some of the examples use the table shop to hold the price of each article (item number) for certain traders (dealers). Supposing that each trader has a single fixed price per article, then (article, dealer) is a primary key for the records.

Start the command-line tool mysql and select a database:

mysql your-database-name

(In most MySQL installations, you can use the database-name ‘test’).

You can create the example table as:

CREATE TABLE shop (
 article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
 dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
 price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
 PRIMARY KEY(article, dealer));

INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
(3,'D',1.25),(4,'D',19.95);

Okay, so the example data is:

mysql> SELECT * FROM shop;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The Maximum Value for a Column

“What’s the highest item number?”

SELECT MAX(article) AS article FROM shop

+---------+
| article |
+---------+
|       4 |
+---------+

The Row Holding the Maximum of a Certain Column

“Find number, dealer, and price of the most expensive article.”

In ANSI SQL this is easily done with a sub-query:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop)

In MySQL (which does not yet have sub-selects), just do it in two steps:

1. Get the maximum price value from the table with a SELECT statement.
2. Using this value compile the actual query:SELECT article, dealer, price FROM shop WHERE price=19.95

Another solution is to sort all rows descending by price and only get the first row using the MySQL-specific LIMIT clause:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1

NOTE: If there are several most expensive articles (for example, each $19.95) the LIMIT solution shows only one of them!

Maximum of Column per Group

“What’s the highest price per article?”

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

The Rows Holding the Group-Wise Maximum of a Certain Field

“For each article, find the dealer(s) with the most expensive price.”

In ANSI SQL, I’d do it with a sub-query like this:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

In MySQL it’s best to do it in several steps:

1. Get the list of (article,maxprice).
2. For each article get the corresponding rows that have the stored maximum price.

This can easily be done with a temporary table:

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

If you don’t use a TEMPORARY table, you must also lock the ‘tmp’ table. “Can it be done with a single query?” Yes, but only by using a quite inefficient trick that I call the “MAX-CONCAT trick”:

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

The last example can, of course, be made a bit more efficient by doing the splitting of the concatenated column in the client.

Using User Variables

You can use MySQL user variables to remember results without having to store them in temporary variables in the client. See Section 6.1.4.

For example, to find the articles with the highest and lowest price you can do:

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

Using Foreign Keys

In MySQL 3.23.44 and up, InnoDB tables support checking of foreign key constraints.

You don’t actually need foreign keys to join 2 tables. The only things MySQL currently doesn’t do (in types other than InnoDB), are CHECK to make sure that the keys you use really exist in the table(s) you’re referencing and automatically delete rows from a table with a foreign key definition. If you use your keys like normal, it’ll work just fine:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
    PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID( )),
(NULL, 'dress', 'white', LAST_INSERT_ID( )),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID( ));


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID( )),
(NULL, 'polo', 'red', LAST_INSERT_ID( )),
(NULL, 'dress', 'blue', LAST_INSERT_ID( )),
(NULL, 't-shirt', 'white', LAST_INSERT_ID( ));


SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | color  | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color  | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

Searching on Two Keys

MySQL doesn’t yet optimise when you search on two different keys combined with OR (searching on one key with different OR parts is optimised quite well):

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
OR  field2_index = '1'

The reason is that we haven’t yet had time to come up with an efficient way to handle this in the general case. (The AND handling is, in comparison, now completely general and works very well.)

For the moment you can solve this very efficiently by using a TEMPORARY table. This type of optimisation is also very good if you are using very complicated queries where the SQL server does the optimisations in the wrong order.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

The way to solve the query shown here is, in effect, a UNION of two queries. See Section 6.4.1.2.

Calculating Visits per Day

The following shows an idea of how you can use the bit group functions to calculate the number of days per month a user has visited a web page:

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
             day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
            (2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
       GROUP BY year,month;

Which returns:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

The above calculates how many different days was used for a given year/month combination, with automatic removal of duplicate entries.

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL, PRIMARY KEY (id));
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
("lax"),("whale");
SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
+----+---------+

For MyISAM and BDB tables you can specify AUTO_INCREMENT on secondary column in a multi-column key. In this case the generated value for the auto-increment column is calculated as MAX(auto_increment_column)+1) WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT
             PRIMARY KEY (grp,id));
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
            ("bird","penguin"),("fish","lax"),("mammal","whale");
SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
+--------+----+---------+

Note that in this case, the AUTO_INCREMENT value will be reused if you delete the row with the biggest AUTO_INCREMENT value in any group.

You can get the used AUTO_INCREMENT key with the LAST_INSERT_ID( ) SQL function or the mysql_insert_id( ) API function.

drop your comment