Difference between revisions of "Relational Databases"
From Gridkaschool
(→Session 1) |
(→Session 2) |
||
Line 67: | Line 67: | ||
CTRL-D |
CTRL-D |
||
− | == Session 2 == |
+ | == 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 == |
== Session 3 == |
||
== Session 4 == |
== Session 4 == |
Revision as of 20:14, 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';