Difference between revisions of "Relational Databases"
From Gridkaschool
m |
(→General information) |
||
(22 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
== General information == |
== General information == |
||
− | ssh reldb@gks- |
+ | ssh reldb@gks-virt(050-061).scc.kit.edu -p24 |
+ | [[File:agenda.pdf]] |
||
− | == Session 1 == |
||
+ | |||
− | == Session 2 == |
||
− | == Session |
+ | == Session 1 - Setup == |
+ | |||
− | == Session 4 == |
||
+ | $ mysql -u root |
||
− | == Session 5 == |
||
+ | |||
− | == Session 6 == |
||
+ | 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() |
Latest revision as of 23:10, 1 September 2014
Contents
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()