MySQL/Tutorials
MySQL tutorial
Intall
Create User
Create Database
Queries
Hello World
A simple "Hello world" program in mysql.
Select "Hello World" as "Output"; +-------------+ | output | +-------------+ | Hello World | +-------------+
Joins
Lets use two tables to join: table name and table phone.
Left outer Joins, Right outer Joins, Full outer Joins and Inner Joins in SQL
This is a table of names.
mysql> select * from name order by name_id; +---------+-------+-------+ | name_id | first | last | +---------+-------+-------+ | 1 | joe | smith | | 2 | alice | jones | | 3 | fred | black | | 4 | jane | doe | +---------+-------+-------+
This is a table of phone numbers.
mysql> select * from phone; +-------+--------+---------+ | ph_id | number | name_id | +-------+--------+---------+ | 1 | 100 | 1 | | 2 | 101 | 1 | | 3 | 102 | 2 | | 4 | 103 | 3 | | 5 | 104 | NULL | +-------+--------+---------+
Right outer join
A right outer join follows. It shows all the phone numbers.
mysql> select last, number from name right join phone on name.name_id = phone.name_id; +-------+--------+ | last | number | +-------+--------+ | smith | 100 | | smith | 101 | | jones | 102 | | black | 103 | | NULL | 104 | +-------+--------+
Left outer join
A left outer join follows. It shows all the names.
mysql> select last, number from name left join phone on name.name_id = phone.name_id; +-------+--------+ | last | number | +-------+--------+ | smith | 100 | | smith | 101 | | doe | NULL | | jones | 102 | | black | 103 | +-------+--------+
Inner join
An inner join follows. It shows only names that have numbers and only numbers that have names.
mysql> select last, number from name join phone on name.name_id = phone.name_id; +-------+--------+ | last | number | +-------+--------+ | smith | 100 | | smith | 101 | | jones | 102 | | black | 103 | +-------+--------+
Full outer join simulation
A full outer join follows. It shows all names and all phone numbers.
mysql> SELECT last, number FROM name LEFT JOIN phone
ON name.name_id = phone.name_id
UNION
SELECT last, number FROM name RIGHT JOIN phone
ON name.name_id=phone.name_id;
+-------+--------+
| last | number |
+-------+--------+
| smith | 100 |
| smith | 101 |
| doe | NULL |
| jones | 102 |
| black | 103 |
| NULL | 104 |
+-------+--------+
Self join
Here we see an example of self join. We start with a single table called emp for employee.
mysql> SELECT * FROM emp; +--------+-------+---------+ | emp_id | name | boss_id | +--------+-------+---------+ | 1 | smith | NULL | | 2 | jones | 1 | | 3 | black | 1 | | 4 | gray | 3 | +--------+-------+---------+
We give the table emp two alias names, boss and worker, to create a self join.
mysql> SELECT worker.name, "works for", boss.name FROM emp AS worker JOIN emp AS boss ON worker.boss_id = boss.emp_id;
+-------+-----------+-------+ | name | works for | name | +-------+-----------+-------+ | jones | works for | smith | | black | works for | smith | | gray | works for | black | +-------+-----------+-------+
Help with examples
Here is some help for creating the source tables:
DROP TABLE IF EXISTS `name`; CREATE TABLE `name` ( `name_id` int(10) default NULL, `first` varchar(20) default NULL, `last` varchar(20) default NULL ); INSERT INTO `name` VALUES (1,'joe','smith'), (4,'jane','doe'), (2,'alice','jones'), (3,'fred','black'); DROP TABLE IF EXISTS `phone`; CREATE TABLE `phone` ( `ph_id` int(10) default NULL, `number` int(10) default NULL, `name_id` int(10) default NULL );
INSERT INTO `phone` VALUES (1,100,1), (2,101,1), (3,102,2), (4,103,3), (5,104,NULL);
Subquries
Simple Subquery
Given an employee table, emp.
mysql> select * from emp; +--------+-------+---------+ | emp_id | name | boss_id | +--------+-------+---------+ | 1 | smith | NULL | | 2 | jones | 1 | | 3 | black | 1 | | 4 | gray | 3 | +--------+-------+---------+
Using a subquery, we calculate the name of the people that work for smith.
mysql> SELECT name FROM emp
WHERE boss_id =
(SELECT emp_id FROM emp
WHERE name="smith");
+-------+
| name |
+-------+
| jones |
| black |
+-------+
Subquery Errors
A subquery value must be singular.
mysql> select name from emp where boss_id=
(select emp_id from emp where
name="smith" or name="gray");
ERROR 1242 (21000): Subquery returns more than 1 row
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 Like
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