MySQL/Tutorials

MySQL tutorial

User Prompts

• Linux prompt commands
  • $ mysql
  • $ mysqldump

• mysql prompts
  • mysql> mysql prompt, mysql client needs a sql query
  • -> arrow prompt, mysql client needs you to finish a query with a semicolon
  • '> single quote prompt, mysql client needs a single quote
  • "> double quote prompt, mysql client needs a double quote

mysqldump

• Linux commands to backup and restore tables
  • $ mysqldump test > mydump.sql
  • $ cat mydump.sql | mysql test

Data Types

Numeric

Int(N)
Decimal(N,M)

String

Char(N)
Varchar(N)

Binary

Binary
Blob

Time

Date          insert format is: "20050102"
Time
DateTime      
Timestamp     saves the date and time at the time of the insert

Create Table

CREATE TABLE emp (emp_id INT(10), l_name VARCHAR(20), boss_id INT(10);

Creates a table called emp with three fields, emp_id, l_name, and boss_id. The first and last field are of type integers, the last name field is of type variable character.

Insert into Table

INSERT INTO EMP VALUES (1, "smith", NULL);

Creates a row with the values 1, smith, and null in the table Emp.

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

We calculate the name of the people that work for smith using a subquery.

mysql> SELECT name FROM emp 
         WHERE boss_id =
           (SELECT emp_id FROM emp 
              WHERE name="smith");
+-------+
| name  |
+-------+
| jones |
| black |
+-------+

Double Subquery

What is the name of the boss of the boss of mr gray?

mysql> SELECT name FROM emp WHERE emp_id=
         (SELECT boss_id FROM emp WHERE emp_id = 
           (SELECT boss_id FROM emp WHERE name = "gray"));
+-------+
| name  |
+-------+
| smith |
+-------+

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

User Variables

mysql> set @smith_id = (select emp_id from emp where name = "smith");

New tables from old

mysql> create table emp_copy (select * from emp); 
  
mysql> select * from emp_copy;
+--------+-------+---------+
| emp_id | name  | boss_id |
+--------+-------+---------+
|      1 | smith |    NULL | 
|      2 | Jones |       1 | 
|      3 | Black |       1 | 
|      4 | Gray  |       3 | 
+--------+-------+---------+

mysql> select * from emp_copy where boss_id = @smith_id;
+--------+-------+---------+
| emp_id | name  | boss_id |
+--------+-------+---------+
|      2 | Jones |       1 | 
|      3 | Black |       1 | 
+--------+-------+---------+

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