Mysql#Tutorials

From Citizendium
This article is developing and not approved.
 Main Article
 Discussion
Related Articles  [?]
Bibliography  [?]
External Links  [?]
Citable Version  [?]
Tutorials [?]
 
https://en.citizendium.org/wiki/index.php?title=MySQL/Tutorials&printable=yes
Tutorials relating to the topic of MySQL.

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
↧ Download as ZWI file | Last modified: 03/04/2025 23:57:25 | 8 views
☰ Source: https://citizendium.org/wiki/MySQL/Tutorials | License: CC BY-SA 3.0

✘
ZWI is not signed. [what is this?]

ZWI viewer by the EncyclosphereKSF