Relational Databases: Difference between revisions
From Gridkaschool
Jump to navigationJump to search
Line 174: | Line 174: | ||
insert into locations (name, owner) values ('Beyond the Wall', NULL); |
insert into locations (name, owner) values ('Beyond the Wall', NULL); |
||
mysql> select * |
mysql> select * |
||
from persons, locations; |
|||
mysql> select * |
mysql> select * |
||
from persons p, locations l |
|||
where p.noble_house = l.owner; |
|||
mysql> select p.first_name, p.last_name, l.name from persons p, locations l |
mysql> select p.first_name, p.last_name, l.name from persons p, locations l |
||
where p.noble_house = l.owner; |
|||
mysql> select p.first_name, p.last_name, l.name |
mysql> select p.first_name, p.last_name, l.name |
||
from persons p left join locations l |
|||
on p.noble_house = l.owner; |
|||
mysql> select p.first_name, p.last_name, l.name |
mysql> select p.first_name, p.last_name, l.name |
||
from persons p right join locations l |
|||
on p.noble_house = l.owner; |
|||
mysql> select p1.first_name, p2.first_name |
mysql> select p1.first_name, p2.first_name |
||
from persons p1, persons p2 |
|||
where ((p1.gender='m' and p2.gender='f') |
|||
and (p1.noble_house != p2.noble_house)); |
|||
mysql> create table forces ( |
mysql> create table forces ( |
||
Line 193: | Line 204: | ||
catapults int |
catapults int |
||
) engine=innodb; |
) engine=innodb; |
||
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Winterfell', 1000, 1000, 10); |
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Winterfell', 1000, 1000, 10); |
||
Line 201: | Line 211: | ||
mysql> select * from persons, locations, forces; |
mysql> select * from persons, locations, forces; |
||
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
||
from persons p, locations l, forces f |
|||
where p.noble_house = l.owner |
|||
and l.owner = f.owner; |
|||
mysql> select distinct noble_house from persons; |
mysql> select distinct noble_house from persons; |
||
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
||
from (select distinct noble_house from persons) p, locations l, forces f |
|||
where p.noble_house = l.owner |
|||
and l.owner = f.owner; |
|||
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Casterly Rock', 300, 10, 1); |
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Casterly Rock', 300, 10, 1); |
||
mysql> create view battleground as |
|||
select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
|||
from (select distinct noble_house from persons) p, locations l, forces f |
|||
where p.noble_house = l.owner and l.owner = f.owner; |
|||
mysql> create view noble_houses as |
mysql> create view noble_houses as |
||
select distinct noble_house from persons; |
|||
mysql> create view battleground as |
|||
select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults |
|||
from noble_houses p, locations l, forces f |
|||
where p.noble_house = l.owner |
|||
and l.owner = f.owner; |
|||
mysql> select b1.* |
|||
mysql> select b1.* from battleground b1, battleground b2 where b1.family != b2.family and b1.battlefield = b2.battlefield; |
|||
from battleground b1, battleground b2 |
|||
where b1.family != b2.family |
|||
and b1.battlefield = b2.battlefield; |
|||
mysql> create view battle as ( |
mysql> create view battle as ( |
||
select b1.* |
|||
from battleground b1, battleground b2 |
|||
where b1.family != b2.family |
|||
and b1.battlefield = b2.battlefield |
|||
); |
|||
mysql> create view strength as |
mysql> create view strength as |
||
select family, swordmen*1+archers*2+catapults*10 as battle_strength |
|||
from battle; |
|||
mysql> select * |
|||
⚫ | |||
from strength |
|||
⚫ | |||
from strength); |
|||
mysql> drop view strength; |
mysql> drop view strength; |
Revision as of 20:23, 1 September 2014
General information
ssh reldb@gks-virt(050-061).scc.kit.edu -p24
Session 1 - Setup
$ mysql -u root
mysql> show databases;
mysql> create database playground;
mysql> show databases;
CTRL-D
$ sudo ls -la /var/lib/mysql/
$ sudo ls -la /var/lib/mysql/playground
$ sudo ls -la /var/lib/mysql/mysql
$ mysql -u root
mysql> use mysql;
mysql> show tables;
mysql> select * from user;
mysql> create user 'joffrey' identified by 'kings_landing';
mysql> create user 'joffrey'@'localhost' identified by 'kings_landing';
mysql> create user 'hodor' identified by 'hodor';
mysql> create user 'hodor'@'localhost' identified by 'hodor';
mysql> select * from user;
mysql> drop user 'joffrey';
mysql> drop user 'hodor';
mysql> select * from user;
CTRL-D
$ mysql -u hodor -p
mysql> use playground;
CTRL-D
$ mysql -u root
mysql> show grants;
mysql> grant all privileges on playground.* to 'joffrey'@'localhost';
CTRL-D
$ mysql -u joffrey
mysql> show grants;
CTRL-D
Session 2 - CRUD
$ mysql -u joffrey -p
mysql> use playground;
mysql> show engines;
mysql> create table persons ( name varchar(256), gender char(1) ) engine=innodb;
mysql> describe persons;
mysql> insert into persons (name, gender) values ('Eddard Stark', 'm'); insert into persons (name, gender) values ('Catelyn Stark', 'f'); insert into persons (name, gender) values ('Robb Stark', 'm'); insert into persons (name, gender) values ('Jon Snow', 'm'); insert into persons (name, gender) values ('Arya Stark', 'f'); insert into persons (name, gender) values ('Tywin Lannister', 'm'); insert into persons (name, gender) values ('Cersei Lannister', 'f'); insert into persons (name, gender) values ('Jaime Lannister', 'm'); insert into persons (name, gender) values ('Joffrey Baratheon', 'm'); insert into persons (name, gender) values ('Tyrion Lannister', 'm'); insert into persons (name, gender) values ('Daenerys Targaryen', 'f'); insert into persons (name, gender) values ('Hodor', 'm'); insert into persons (name, gender) values ('Ygritte', 'f');
mysql> select * from persons;
mysql> select * from persons where gender='m';
mysql> select gender from persons where name='Hodor';
mysql> select gender, count(*) from persons group by gender;
mysql> select * from persons where name like '%Stark';
mysql> select substring_index(name, ' ', 1) from persons;
mysql> select substring_index(name, ' ', -1) from persons;
mysql> create table persons_2 ( first_name varchar(128), last_name varchar(128), noble_house varchar(128), gender char(1) ) engine=innodb;
mysql> select substring_index(name, ' ', 1) as first_name, substring_index(name, ' ', -1) as last_name, gender from persons;
mysql> insert into persons_2 select substring_index(name, ' ', 1) as first_name, substring_index(name, ' ', -1) as last_name, gender from persons;
mysql> insert into persons_2 (first_name, last_name, noble_house, gender) select substring_index(name, ' ', 1) as first_name, substring_index(name, ' ', -1) as last_name, substring_index(name, ' ', -1) as noble_house, gender from persons;
mysql> drop table persons;
mysql> alter table persons_2 rename persons;
mysql> select * from persons;
mysql> update persons set noble_house='Stark' where last_name='Snow';
mysql> update persons set noble_house='Lannister' where last_name='Baratheon';
mysql> update persons set last_name=NULL, noble_house=NULL where first_name in ('Hodor', 'Ygritte');
mysql> select first_name from persons where noble_house='Stark' and gender='f';
mysql> select noble_house, count(*) as members from persons group by noble_house order by members desc;
mysql> delete from persons where first_name='Joffrey';
Session 3 - Relations
$ mysql -u joffrey -p
mysql> use playground;
mysql> create table locations ( name varchar(256), owner varchar(256) ) engine=innodb;
mysql> insert into locations (name, owner) values ('Winterfell', 'Stark'); insert into locations (name, owner) values ('Casterly Rock', 'Lannister'); insert into locations (name, owner) values ('Kings Landing', 'Targaryen'); insert into locations (name, owner) values ('Nights Watch', 'The Wall'); insert into locations (name, owner) values ('Beyond the Wall', NULL);
mysql> select * from persons, locations;
mysql> select * from persons p, locations l where p.noble_house = l.owner;
mysql> select p.first_name, p.last_name, l.name from persons p, locations l where p.noble_house = l.owner;
mysql> select p.first_name, p.last_name, l.name from persons p left join locations l on p.noble_house = l.owner;
mysql> select p.first_name, p.last_name, l.name from persons p right join locations l on p.noble_house = l.owner;
mysql> select p1.first_name, p2.first_name from persons p1, persons p2 where ((p1.gender='m' and p2.gender='f') and (p1.noble_house != p2.noble_house));
mysql> create table forces ( owner varchar(256), location varchar(256), swordmen int, archers int, catapults int ) engine=innodb;
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Winterfell', 1000, 1000, 10); insert into forces (owner, location, swordmen, archers, catapults) values ('Lannister', 'Casterly Rock', 1000, 1000, 10); insert into forces (owner, location, swordmen, archers, catapults) values ('Targaryen', 'Kings Landing', 1000, 1000, 10); insert into forces (owner, location, swordmen, archers, catapults) values (NULL, 'Beyond the Wall', 1000, 1000, 10); mysql> select * from persons, locations, forces;
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults from persons p, locations l, forces f where p.noble_house = l.owner and l.owner = f.owner;
mysql> select distinct noble_house from persons;
mysql> select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults from (select distinct noble_house from persons) p, locations l, forces f where p.noble_house = l.owner and l.owner = f.owner;
mysql> insert into forces (owner, location, swordmen, archers, catapults) values ('Stark', 'Casterly Rock', 300, 10, 1);
mysql> create view battleground as select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults from (select distinct noble_house from persons) p, locations l, forces f where p.noble_house = l.owner and l.owner = f.owner;
mysql> create view noble_houses as select distinct noble_house from persons;
mysql> create view battleground as select p.noble_house as family, l.name as headquarter, f.location as battlefield, f.swordmen, f.archers, f.catapults from noble_houses p, locations l, forces f where p.noble_house = l.owner and l.owner = f.owner;
mysql> select b1.* from battleground b1, battleground b2 where b1.family != b2.family and b1.battlefield = b2.battlefield;
mysql> create view battle as ( select b1.* from battleground b1, battleground b2 where b1.family != b2.family and b1.battlefield = b2.battlefield );
mysql> create view strength as select family, swordmen*1+archers*2+catapults*10 as battle_strength from battle;
mysql> select * from strength where battle_strength = (select max(battle_strength) from strength);
mysql> drop view strength;
mysql> drop view battle;
mysql> drop view battleground;
mysql> drop view noble_houses;