Relational Databases

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;

Session 4 - Constraints

$ mysql -u root

mysql> SET @@global.sql_mode = "STRICT_ALL_TABLES";

CTRL-D

$ mysql -u joffrey -p

mysql> use playground;

mysql> insert into persons values ('First', 'Last', 'House', 'x');

mysql> insert into persons values ('Second', 'Last', 'House', NULL);

mysql> alter table persons modify gender enum('m', 'f');

mysql> select * from persons;

mysql> update persons set gender=NULL where gender='x';

mysql> alter table persons modify gender enum('m', 'f');

mysql> delete from persons where gender is null;

mysql> alter table persons modify gender enum('m', 'f') not null;

mysql> insert into persons values ('First', 'Last', 'House', 'x');

mysql> alter table persons add constraint pk_persons primary key (first_name, last_name);

mysql> alter table persons add column id int first;

mysql> select * from persons;

mysql> alter table persons drop column id;

mysql> alter table persons add column id int primary key auto_increment first;

mysql> select * from persons;

mysql> alter table persons add unique uq_first_last (first_name, last_name);

mysql> show create table persons;

mysql> alter table locations add constraint pk_locations primary key(name);