Relational Databases

From Gridkaschool
Jump to navigationJump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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);

Session 5 - Transactions

TERMINAL 0:

$ mysql -u root

mysql> use playground;

mysql> create table tx_test (name varchar(4), age integer) engine=innodb;

mysql> insert into tx_test values ('jack', 28);
       insert into tx_test values ('jill', 25);

mysql> select * from tx_test;

mysql> select @@global.tx_isolation;

mysql> set global transaction isolation level read uncommitted;

--- DIRTY READ ---

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> select age from tx_test where name='jack';

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> update tx_test set age=21 where name='jack';

TERMINAL 1:

mysql> select age from tx_test where name='jack';

TERMINAL 2:

mysql> rollback;

CTRL-D

--- NON-REPEATABLE READ ---

TERMINAL 0:

mysql> set global transaction isolation level read committed;

TERMINAL 1:

mysql> start transaction;

mysql> select * from tx_test where name='jack';

TERMINAL 2:

mysql> start transaction;

mysql> update tx_test set age=29 where name='jack';

mysql> commit;

CTRL-D

TERMINAL 1:

mysql> select * from tx_test where name='jack';

CTRL-D

--- PHANTOM READ ---

TERMINAL 0:

mysql> set global transaction isolation level repeatable read;

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> select * from tx_test where age between 20 and 30;

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

mysql> insert into tx_test values ('bob', 23);

mysql> commit;

CTRL-D

TERMINAL 1:

mysql> select * from tx_test where age between 20 and 30;

mysql> commit;

mysql> select * from tx_test where age between 20 and 30;

CTRL-D

--- DEADLOCK ---

TERMINAL 0:

mysql> alter table tx_test add money integer;

mysql> update tx_test set money=100;

mysql> delete from tx_test where name='bob';

mysql> set global transaction isolation level read committed;

TERMINAL 1:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

TERMINAL 2:

$ mysql -u joffrey -p

mysql> use playground;

mysql> start transaction;

TERMINAL 1:

mysql> update tx_test set money=money-50 where name='jack';

TERMINAL 2:

mysql> update tx_test set money=money-50 where name='jill';

TERMINAL 1:

mysql> update tx_test set money=money+50 where name='jill';

TERMINAL 2:

mysql> update tx_test set money=money+50 where name='jack';

TERMINAL 1:

mysql> commit;

Session 6 - SQL Injection

$ mysql -u root -p

mysql> use playground

mysql> create table users (id integer, login varchar(255), password varchar(255)) engine=innodb;

mysql> insert into users values (0, 'root', sha(concat('salt', 'hunter2')));

mysql> select * from users where login='root' and password=sha(concat('salt','hunter2'));

CTRL-D



#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

session = MySQLdb.connect(host="localhost",
                          user="joffrey",
                          passwd="kings_landing",
                          db="playground",
                          cursorclass = MySQLdb.cursors.SSCursor)
cur = session.cursor()

login = raw_input('login: ')
password = raw_input('password: ')

print "select * from users where login=\'%s\' and password=sha(concat('salt', '%s'))" % (login, password)

cur.execute("select * from users where login=\'%s\' and password=sha(concat('salt', '%s'))" % (login, password))

if cur.fetchone() is not None:
    print 'logged in'
else:
    print 'wrong login or password'

cur.close()
session.close()

$ ./inject.py

login: root
password: ')) or 1=1;#

$ ./inject.py

login: root
password: ')) or 1=1; update users set password=sha(concat('salt','mypass'));#

Session 7 - Indexes

#!/usr/bin/python

import random
import time

import MySQLdb

con = MySQLdb.connect(host="localhost",
                      user="joffrey",
                      passwd="kings_landing",
                      db="playground")
con.autocommit(True)

cur = con.cursor()

try: cur.execute('drop table large_table')
except: pass

cur.execute('create table large_table (l char, s varchar(255), n int) engine=innodb')

ts = time.time()
for i in xrange(1000000):
    if i%10 == 0:
        print i
    cur.execute("INSERT INTO large_table VALUES ('%s', '%s', %i)" % (chr(random.randrange(65, 91)),
                                                                     .join([chr(random.randrange(65, 91)) for i in xrange(1,16)]),
                                                                     random.randint(0,1000000)))
print 'time taken:', time.time()-ts

con.close()

$ mysql -u joffrey -p

mysql> use playground;

mysql> select l, count(*) from large_table group by l;

mysql> create index idx_l on large_table(l);

mysql> select s, count(*) from large_table where s like 'AB%';

mysql> create index idx_l on large_table(l);

mysql> select s, count(*) from large_table where s like 'AB%';

Session 8 - Partitioning

create table large_table (l char, s varchar(255), n int) engine=innodb
partition by range (n) (
 partition p0 values less than (500000),
 partition p1 values less than maxvalue
);

create table large_table (l char, s varchar(255), n int) engine=innodb
partition by hash(n)
partitions 10;

create table large_table (l char, s varchar(255), n int) engine=innodb
partition by list(l) (
 partition p0 values in ('A', ... , 'L'),
 partition p1 values in ('M, ... , 'Z')
);

Session 9 - Execution plans

$ mysql -u joffrey -p

mysql> use playground

mysql> explain <any statement>

Session X - Application development

/* gcc -Wall $(mysql_config --cflags --libs) basic.c -o basic */

#include <stdio.h>
#include <stdlib.h>

#include <mysql.h>

int main()
{

  MYSQL *con = mysql_init(NULL);
  MYSQL_RES *cur;
  MYSQL_ROW row;
  int cols, i;

  mysql_real_connect(con,
                    "localhost",
                    "joffrey",
                    "kings_landing",
                    "playground",
                    0, NULL, 0);

  mysql_query(con, "select * from persons");

  cur = mysql_store_result(con);

  cols = mysql_num_fields(cur);
  while ((row = mysql_fetch_row(cur))) {
    for(i = 0; i < cols; i++) {
      printf("%s ", row[i] ? row[i] : "<NULL>");
    }
    printf("\n");
  }

  mysql_free_result(cur);
  mysql_close(con);

  return 0;
}

#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

session = MySQLdb.connect(host="localhost",
                          user="joffrey",
                          passwd="kings_landing",
                          db="playground",
                          cursorclass = MySQLdb.cursors.SSCursor)
cur = session.cursor()

cur.execute('select * from persons')

for row in cur.fetchall():
    for col in row:
        print col if col else '<NULL>',
    print

cur.close()
session.close()

#!/usr/bin/python

import MySQLdb
import MySQLdb.cursors

from multiprocessing import Process
from random import randint
from time import sleep

def get_session():
    return MySQLdb.connect(host='localhost',
                           user='joffrey',
                           passwd='kings_landing',
                           db='playground',
                           cursorclass=MySQLdb.cursors.SSCursor)

def bootstrap_tables():
    cur = get_session().cursor()
    try:
        print 'trying to create new table'
        cur.execute('create table mp_test (value int)')
    except:
        print 'table alread existed, truncating instead'
        cur.execute('truncate table mp_test')
    cur.close()

def insert_rows():
    while True:
        print 'inserting'
        session = get_session()
        cur = session.cursor()
        cur.execute('insert into mp_test values (%s)', randint(0,9))
        session.commit()
        cur.close()
        session.close()
        sleep(0.1)

def delete_rows():
    while True:
        print 'deleting'
        session = get_session()
        cur = session.cursor()
        cur.execute('delete from mp_test where value = %s', randint(0,9))
        session.commit()
        cur.close()
        session.close()
        sleep(0.1)

def count_rows():
    while True:
        session = get_session()
        cur = session.cursor()
        cur.execute('select count(*) from mp_test')
        res = cur.fetchone()
        print 'rows', res[0]
        cur.close()
        session.close()
        sleep(1)

if __name__ == '__main__':
    bootstrap_tables()
    p_inserter = Process(target=insert_rows)
    p_deleter = Process(target=delete_rows)
    p_counter = Process(target=count_rows)
    p_inserter.start()
    p_deleter.start()
    p_counter.start()
    p_inserter.join()
    p_deleter.join()
    p_counter.join()
Retrieved from "https://wiki.scc.kit.edu/gridkaschool/index.php?title=Relational_Databases&oldid=1742"