CREATE INSERT IN and UPDATE

Date: April 29th 2016
Last updated: April 29th 2016

This entry is a reminder on a few "getting started" routines using SQL in MySQL.

Create database and a table

show databases;

CREATE DATABASE mytests;
use mytests;

CREATE TABLE people(
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(20)
                    );

SELECT column_name 
FROM information_schema.columns 
where table_name = 'people';
/*
+-------------+
| column_name |
+-------------+
| id          |
| name        |
+-------------+

INSERT INTO people table

insert into people (name) values ('Ray');
insert into people (name) values ('Suzy');

ALTER the table by adding a column

alter table people 
add column 
country varchar(100) default 'Australia';

/* insert more data */
insert into people (name, country) 
values('Richard', 'New Zealand');

ALTER table again (adding column)

alter table people 
add column province varchar(100);

INSERT data that matches a where clause

insert into people (province) 
values('New south wales') 
where country='Australia');

UPDATE data by SETting record entries

update people 
set province=upper('wellington') 
where country='New Zealand';

change the previous UPDATE again

update people 
set province=upper('Manawatu') 
where province=upper(wellington);

convert other records to UPPER case

update people 
set province=upper(province) 
where province='New south wales';

review the records

select * from people;
/*
+----+---------+-------------+-----------------+
| id | name    | country     | province        |
+----+---------+-------------+-----------------+
|  1 | Ray     | Australia   | NEW SOUTH WALES |
|  2 | Suzy    | Australia   | NEW SOUTH WALES |
|  3 | Richard | New Zealand | MANAWATU        |
+----+---------+-------------+-----------------+
3 rows in set (0.00 sec)

CREATE another table setting FOREIGN KEY

 create table hobbies(
               ID INT AUTO_INCREMENT PRIMARY KEY, 
               hobby varchar(40)not null, 
               person_id INT NOT NULL, 
               FOREIGN KEY fk_person(person_id) 
               REFERENCES people(id));

INSERT INTO

insert into hobbies (hobby, person_id) 
values('surfing', 2); 
/*
select * from hobbies;
+----+---------+-----------+
| ID | hobby   | person_id |
+----+---------+-----------+
|  1 | surfing |         2 |
+----+---------+-----------+
1 row in set (0.00 sec)

ADD more data

insert into hobbies (hobby, person_id) 
values('Tennis', 3); 
insert into hobbies (hobby, person_id) 
values('Rugby', 1);

select * from hobbies;
/*
+----+---------+-----------+
| ID | hobby   | person_id |
+----+---------+-----------+
|  1 | surfing |         2 |
|  2 | Tennis  |         3 |
|  3 | Rugby   |         1 |
+----+---------+-----------+
3 rows in set (0.00 sec)

Check column names

select column_name 
from information_schema.columns 
where table_name="hobbies";
/*
+-------------+
| column_name |
+-------------+
| ID          |
| hobby       |
| person_id   |
+-------------+
3 rows in set (0.00 sec)

check the foriegn key is ok

select people.id, 
       name, 
       person_id as hobby_person_ID, 
       country, 
       hobby 
from people 
JOIN hobbies 
where people.id=hobbies.person_id;
/*
+----+---------+-----------------+-------------+---------+
| id | name    | hobby_person_ID | country     | hobby   |
+----+---------+-----------------+-------------+---------+
|  2 | Suzy    |               2 | Australia   | surfing |
|  3 | Richard |               3 | New Zealand | Tennis  |
|  1 | Ray     |               1 | Australia   | Rugby   |
+----+---------+-----------------+-------------+---------+

Add more data

insert into people (name, country)  
values('Gladys', 'New Zealand');

select * from people;
/*
+----+---------+-------------+-----------------+
| id | name    | country     | province        |
+----+---------+-------------+-----------------+
|  1 | Ray     | Australia   | NEW SOUTH WALES |
|  2 | Suzy    | Australia   | NEW SOUTH WALES |
|  3 | Richard | New Zealand | MANAWATU        |
|  4 | Gladys  | New Zealand | NULL            |
+----+---------+-------------+-----------------+
4 rows in set (0.00 sec)

INNER JOIN people and hobbies table

select * 
from people 
JOIN hobbies 
ON people.id=hobbies.person_id;
/*
+----+---------+-------------+-----------------+----+---------+-----------+
| id | name    | country     | province        | ID | hobby   | person_id |
+----+---------+-------------+-----------------+----+---------+-----------+
|  1 | Ray     | Australia   | NEW SOUTH WALES |  3 | Rugby   |         1 |
|  2 | Suzy    | Australia   | NEW SOUTH WALES |  1 | surfing |         2 |
|  3 | Richard | New Zealand | MANAWATU        |  2 | Tennis  |         3 |
+----+---------+-------------+-----------------+----+---------+-----------+
3 rows in set (0.00 sec)

LEFT JOIN people and hobbies table

select * 
from people 
LEFT JOIN hobbies 
ON people.id=hobbies.person_id;
/*
+----+---------+-------------+-----------------+------+---------+-----------+
| id | name    | country     | province        | ID   | hobby   | person_id |
+----+---------+-------------+-----------------+------+---------+-----------+
|  1 | Ray     | Australia   | NEW SOUTH WALES |    3 | Rugby   |         1 |
|  2 | Suzy    | Australia   | NEW SOUTH WALES |    1 | surfing |         2 |
|  3 | Richard | New Zealand | MANAWATU        |    2 | Tennis  |         3 |
|  4 | Gladys  | New Zealand | NULL            | NULL | NULL    |      NULL |
+----+---------+-------------+-----------------+------+---------+-----------+
4 rows in set (0.00 sec)

RIGHT JOIN people and hobbies table

select * 
from people 
RIGHT JOIN hobbies 
ON people.id=hobbies.person_id;
/*
+------+---------+-------------+-----------------+----+---------+-----------+
| id   | name    | country     | province        | ID | hobby   | person_id |
+------+---------+-------------+-----------------+----+---------+-----------+
|    2 | Suzy    | Australia   | NEW SOUTH WALES |  1 | surfing |         2 |
|    3 | Richard | New Zealand | MANAWATU        |  2 | Tennis  |         3 |
|    1 | Ray     | Australia   | NEW SOUTH WALES |  3 | Rugby   |         1 |
+------+---------+-------------+-----------------+----+---------+-----------+
3 rows in set (0.00 sec)

FULL JOIN both tables using UNION

select * 
from people 
LEFT JOIN hobbies 
ON people.id=hobbies.person_id 
UNION 
select * 
from people 
RIGHT JOIN hobbies 
ON people.id=hobbies.person_id;
/*
+------+---------+-------------+-----------------+------+---------+-----------+
| id   | name    | country     | province        | ID   | hobby   | person_id |
+------+---------+-------------+-----------------+------+---------+-----------+
|    1 | Ray     | Australia   | NEW SOUTH WALES |    3 | Rugby   |         1 |
|    2 | Suzy    | Australia   | NEW SOUTH WALES |    1 | surfing |         2 |
|    3 | Richard | New Zealand | MANAWATU        |    2 | Tennis  |         3 |
|    4 | Gladys  | New Zealand | NULL            | NULL | NULL    |      NULL |
+------+---------+-------------+-----------------+------+---------+-----------+
4 rows in set (0.00 sec)

FULL JOIN both tables using UNION ALL

select * 
from people 
LEFT JOIN hobbies 
ON people.id=hobbies.person_id 
UNION ALL
select * 
from people 
RIGHT JOIN hobbies 
ON people.id=hobbies.person_id;
/*
+------+---------+-------------+-----------------+------+---------+-----------+
| id   | name    | country     | province        | ID   | hobby   | person_id |
+------+---------+-------------+-----------------+------+---------+-----------+
|    1 | Ray     | Australia   | NEW SOUTH WALES |    3 | Rugby   |         1 |
|    2 | Suzy    | Australia   | NEW SOUTH WALES |    1 | surfing |         2 |
|    3 | Richard | New Zealand | MANAWATU        |    2 | Tennis  |         3 |
|    4 | Gladys  | New Zealand | NULL            | NULL | NULL    |      NULL |
|    2 | Suzy    | Australia   | NEW SOUTH WALES |    1 | surfing |         2 |
|    3 | Richard | New Zealand | MANAWATU        |    2 | Tennis  |         3 |
|    1 | Ray     | Australia   | NEW SOUTH WALES |    3 | Rugby   |         1 |
+------+---------+-------------+-----------------+------+---------+-----------+
7 rows in set (0.00 sec)

CREATE INSERT INTO and UPDATE

CREATE INSERT IN and UPDATE

results matching ""

No results matching ""