MySQL/Tutorials

MySQL tutorial

Queries

Joins

Lets create two tables to join: name and phone.

mysql> SELECT * FROM name;     
+---------+-------+-------+  
| name_id | first | last  |  
+---------+-------+-------+   
|       1 | joe   | smith |    
|       2 | alice | jones |   
|       2 | fred  | black |  
+---------+-------+-------+   

mysql> SELECT * FROM phone;
+-------+--------+---------+  
| ph_id | number | name_id |  
+-------+--------+---------+  
|     1 |    100 |       1 |  
|     2 |    101 |       1 |  
|     3 |    102 |       2 |  
|     4 |    103 |       3 |   
+-------+--------+---------+   
 
mysql> SELECT name.last, phone.number FROM 
              name join phone 
              on    
              name.name_id = phone.name_id;
  
+-------+--------+
| last  | number |
+-------+--------+
| smith |    100 |
| smith |    101 |
| jones |    102 |
| black |    102 |
+-------+--------+

Aggragate Functions

Aggragate functions are functions that combine multiple rows into one output value. Nulls are ignored by aggragate functions unless all the input is NULL in which case the answer is NULL.

Table: Product
+----+---------------+-------+
| Id | Name          | Price |
+----+---------------+-------+
|  1 | bread         | 3.00  |
|  2 | peanut_butter | 3.25  |
|  3 | jam           | 2.75  |
+----+---------------+-------+

Sum

SELECT SUM(Price) as "Sum" FROM Product;
+------+
| sum  |
+------+
| 9.00 |  
+------+

Average

SELECT AVG(Price) as "Avg" FROM Product;
+------+
| Avg  |
+------+
| 3.00 |  
+------+

Min

SELECT MIN(Price) as "Min" FROM Product;
+------+
| Min  |
+------+
| 2.75 |  
+------+

Max

SELECT MAX(Price) as "Max" FROM Product;
+------+
| Max  |
+------+
| 3.25 |  
+------+

Std

Standard Deviation

SELECT STD(Price) AS "Std" FROM Product;
+------+
| Std  |
+------+
| 0.25 |  
+------+

String functions

Pattern Matching

Using RegExp

Regular expressions in SQL

Lets create a simple table.

CREATE TABLE word (name varchar(20));
INSERT into word VALUES ("people"),("places"),("things"),("ppl");

SELECT * FROM word;
+--------+
| name   | 
+--------+
| people |
| places |
| things |
| ppl    |
+--------+

Look for two p's in a row.

SELECT * FROM word WHERE name RegExp "[p]{2}" 
+--------+
| name   | 
+--------+
| ppl    |
+--------+

A "." is any character. A "+" is one or more copies of a character. A "C{n}" looks for n copies of C. Look for two p's but not next to one another.

SELECT * FROM word WHERE name RegExp "p.+p" 
+--------+
| name   | 
+--------+
| people |
+--------+

Exercises

Give a sql regular expression query that will select:

1) only things  
2) only ppl and places
3) only people and places
4) only things and places