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
Intall
Create User
Create Database
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 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