MySQL#Tutorials

| Main Article
| Discussion
| Related Articles  [?]
| Bibliography  [?]
| External Links  [?]
| Citable Version  [?]
| Tutorials [?]

|
|
|
|
|
|
|
|
|

Tutorials relating to the topic of MySQL.

## Contents

* 1 MySQL tutorial
* 1.1 User Prompts
* 1.2 mysqldump
* 1.3 Intall
* 1.4 Create User
* 1.5 Create Database
* 1.6 Data Types
* 1.6.1 Numeric
* 1.6.2 String
* 1.6.3 Binary
* 1.6.4 Time
* 1.7 Create Table
* 1.8 Insert into Table
* 1.9 Queries
* 1.9.1 Hello World
* 1.9.2 Joins
* 1.9.2.1 Right outer join
* 1.9.2.2 Left outer join
* 1.9.2.3 Inner join
* 1.9.2.4 Full outer join simulation
* 1.9.2.5 Self join
* 1.9.2.6 Help with examples
* 1.9.3 Subquries
* 1.9.3.1 Simple Subquery
* 1.9.3.2 Double Subquery
* 1.9.3.3 Subquery Errors
* 1.9.4 Aggragate Functions
* 1.9.4.1 Sum
* 1.9.4.2 Average
* 1.9.4.3 Min
* 1.9.4.4 Max
* 1.9.4.5 Std
* 1.9.5 User Variables
* 1.9.6 New tables from old
* 1.9.7 String functions
* 1.9.7.1 Pattern Matching
* 1.9.7.1.1 Using Like
* 1.9.7.1.2 Using RegExp
* 1.9.7.1.2.1 Exercises

# MySQL tutorial[edit]

## User Prompts[edit]

* 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[edit]

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

## Intall[edit]

## Create User[edit]

## Create Database[edit]

## Data Types[edit]

### Numeric[edit]

Int(N)
Decimal(N,M)

### String[edit]

Char(N)
Varchar(N)

### Binary[edit]

Binary
Blob

### Time[edit]

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

## Create Table[edit]

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[edit]

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

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

## Queries[edit]

### Hello World[edit]

A simple "Hello world" program in mysql.

Select "Hello World" as "Output";
+

-+
| output      |
+

-+
| Hello World |
+

-+

### Joins[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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[edit]

#### Simple Subquery[edit]

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[edit]

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[edit]

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[edit]

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[edit]

SELECT SUM(Price) as "Sum" FROM Product;
+

+
| sum  |
+

+
| 9.00 |
+

+

#### Average[edit]

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

+
| Avg  |
+

+
| 3.00 |
+

+

#### Min[edit]

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

+
| Min  |
+

+
| 2.75 |
+

+

#### Max[edit]

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

+
| Max  |
+

+
| 3.25 |
+

+

#### Std[edit]

Standard Deviation

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

+
| Std  |
+

+
| 0.25 |
+

+

### User Variables[edit]

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

### New tables from old[edit]

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[edit]

#### Pattern Matching[edit]

##### Using Like[edit]

##### Using RegExp[edit]

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[edit]

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