CREATE TABLE jooksjad( epost VARCHAR(30) PRIMARY KEY, sugu ENUM('M', 'N'), synniaasta INT ); INSERT INTO jooksjad VALUES ('juku@eesti.ee', 'M', 2007); INSERT INTO jooksjad VALUES ('kati@eesti.ee', 'N', 2007); INSERT INTO jooksjad VALUES ('mari@eesti.ee', 'N', 2007); INSERT INTO jooksjad VALUES ('katrin@eesti.ee', 'N', 2008); INSERT INTO jooksjad VALUES ('mati@eesti.ee', 'M', 2008); INSERT INTO jooksjad VALUES ('madis@hot.ee', 'M', 2003); INSERT INTO jooksjad VALUES ('mihkel@hot.ee', 'M', 2002); INSERT INTO jooksjad VALUES ('margus@hot.ee', 'M', 2003); CREATE TABLE tulemused100m( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, jooksja VARCHAR(30), aegsek FLOAT, FOREIGN KEY(jooksja) REFERENCES jooksjad(epost) ); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.8); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.5); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.9); INSERT INTO tulemused100m VALUES (DEFAULT, 'kati@eesti.ee', 18.8); +----+---------------+--------+ | id | jooksja | aegsek | +----+---------------+--------+ | 1 | juku@eesti.ee | 17.8 | | 2 | juku@eesti.ee | 17.5 | | 3 | juku@eesti.ee | 17.9 | | 4 | kati@eesti.ee | 18.8 | +----+---------------+--------+ INSERT INTO tulemused100m VALUES (DEFAULT, 'piia@eesti.ee', 18.8); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`if17_jaagup_4`.`tulemused100m`, CONSTRAINT `tulemused100m_ibfk_1` FOREIGN KEY (`jooksja`) REFERENCES `jooksjad` (`epost`)) SELECT * FROM tulemused100m INNER JOIN jooksjad ON tulemused100m.jooksja=jooksjad.epost; +----+---------------+--------+---------------+------+------------+ | id | jooksja | aegsek | epost | sugu | synniaasta | +----+---------------+--------+---------------+------+------------+ | 1 | juku@eesti.ee | 17.8 | juku@eesti.ee | M | 2007 | | 2 | juku@eesti.ee | 17.5 | juku@eesti.ee | M | 2007 | | 3 | juku@eesti.ee | 17.9 | juku@eesti.ee | M | 2007 | | 4 | kati@eesti.ee | 18.8 | kati@eesti.ee | N | 2007 | +----+---------------+--------+---------------+------+------------+ SELECT * FROM tulemused100m, jooksjad WHERE tulemused100m.jooksja=jooksjad.epost; +----+---------------+--------+---------------+------+------------+ | id | jooksja | aegsek | epost | sugu | synniaasta | +----+---------------+--------+---------------+------+------------+ | 1 | juku@eesti.ee | 17.8 | juku@eesti.ee | M | 2007 | | 2 | juku@eesti.ee | 17.5 | juku@eesti.ee | M | 2007 | | 3 | juku@eesti.ee | 17.9 | juku@eesti.ee | M | 2007 | | 4 | kati@eesti.ee | 18.8 | kati@eesti.ee | N | 2007 | +----+---------------+--------+---------------+------+------------+ SELECT * FROM tulemused100m, jooksjad; +----+---------------+--------+-----------------+------+------------+ | id | jooksja | aegsek | epost | sugu | synniaasta | +----+---------------+--------+-----------------+------+------------+ | 1 | juku@eesti.ee | 17.8 | juku@eesti.ee | M | 2007 | | 2 | juku@eesti.ee | 17.5 | juku@eesti.ee | M | 2007 | | 3 | juku@eesti.ee | 17.9 | juku@eesti.ee | M | 2007 | | 4 | kati@eesti.ee | 18.8 | juku@eesti.ee | M | 2007 | | 1 | juku@eesti.ee | 17.8 | kati@eesti.ee | N | 2007 | | 2 | juku@eesti.ee | 17.5 | kati@eesti.ee | N | 2007 | | 3 | juku@eesti.ee | 17.9 | kati@eesti.ee | N | 2007 | | 4 | kati@eesti.ee | 18.8 | kati@eesti.ee | N | 2007 | | 1 | juku@eesti.ee | 17.8 | katrin@eesti.ee | N | 2008 | | 2 | juku@eesti.ee | 17.5 | katrin@eesti.ee | N | 2008 | | 3 | juku@eesti.ee | 17.9 | katrin@eesti.ee | N | 2008 | | 4 | kati@eesti.ee | 18.8 | katrin@eesti.ee | N | 2008 | | 1 | juku@eesti.ee | 17.8 | madis@hot.ee | M | 2003 | | 2 | juku@eesti.ee | 17.5 | madis@hot.ee | M | 2003 | | 3 | juku@eesti.ee | 17.9 | madis@hot.ee | M | 2003 | | 4 | kati@eesti.ee | 18.8 | madis@hot.ee | M | 2003 | | 1 | juku@eesti.ee | 17.8 | margus@hot.ee | M | 2003 | | 2 | juku@eesti.ee | 17.5 | margus@hot.ee | M | 2003 | | 3 | juku@eesti.ee | 17.9 | margus@hot.ee | M | 2003 | | 4 | kati@eesti.ee | 18.8 | margus@hot.ee | M | 2003 | | 1 | juku@eesti.ee | 17.8 | mari@eesti.ee | N | 2007 | | 2 | juku@eesti.ee | 17.5 | mari@eesti.ee | N | 2007 | | 3 | juku@eesti.ee | 17.9 | mari@eesti.ee | N | 2007 | | 4 | kati@eesti.ee | 18.8 | mari@eesti.ee | N | 2007 | | 1 | juku@eesti.ee | 17.8 | mati@eesti.ee | M | 2008 | | 2 | juku@eesti.ee | 17.5 | mati@eesti.ee | M | 2008 | | 3 | juku@eesti.ee | 17.9 | mati@eesti.ee | M | 2008 | | 4 | kati@eesti.ee | 18.8 | mati@eesti.ee | M | 2008 | | 1 | juku@eesti.ee | 17.8 | mihkel@hot.ee | M | 2002 | | 2 | juku@eesti.ee | 17.5 | mihkel@hot.ee | M | 2002 | | 3 | juku@eesti.ee | 17.9 | mihkel@hot.ee | M | 2002 | | 4 | kati@eesti.ee | 18.8 | mihkel@hot.ee | M | 2002 | +----+---------------+--------+-----------------+------+------------+ SELECT epost FROM jooksjad WHERE sugu='N'; +-----------------+ | epost | +-----------------+ | kati@eesti.ee | | katrin@eesti.ee | | mari@eesti.ee | +-----------------+ SELECT epost FROM jooksjad WHERE sugu='M'; +---------------+ | epost | +---------------+ | juku@eesti.ee | | madis@hot.ee | | margus@hot.ee | | mati@eesti.ee | | mihkel@hot.ee | +---------------+ SELECT * FROM (SELECT epost FROM jooksjad WHERE sugu='N') AS naised, (SELECT epost FROM jooksjad WHERE sugu='M') AS mehed; +-----------------+---------------+ | epost | epost | +-----------------+---------------+ | kati@eesti.ee | juku@eesti.ee | | katrin@eesti.ee | juku@eesti.ee | | mari@eesti.ee | juku@eesti.ee | | kati@eesti.ee | madis@hot.ee | | katrin@eesti.ee | madis@hot.ee | | mari@eesti.ee | madis@hot.ee | | kati@eesti.ee | margus@hot.ee | | katrin@eesti.ee | margus@hot.ee | | mari@eesti.ee | margus@hot.ee | | kati@eesti.ee | mati@eesti.ee | | katrin@eesti.ee | mati@eesti.ee | | mari@eesti.ee | mati@eesti.ee | | kati@eesti.ee | mihkel@hot.ee | | katrin@eesti.ee | mihkel@hot.ee | | mari@eesti.ee | mihkel@hot.ee | +-----------------+---------------+ 15 rows in set (0.00 sec) SELECT naised.epost AS npost, mehed.epost AS mpost FROM (SELECT epost FROM jooksjad WHERE sugu='N') AS naised, (SELECT epost FROM jooksjad WHERE sugu='M') AS mehed; +-----------------+---------------+ | npost | mpost | +-----------------+---------------+ | kati@eesti.ee | juku@eesti.ee | | katrin@eesti.ee | juku@eesti.ee | | mari@eesti.ee | juku@eesti.ee | | kati@eesti.ee | madis@hot.ee | | katrin@eesti.ee | madis@hot.ee | | mari@eesti.ee | madis@hot.ee | | kati@eesti.ee | margus@hot.ee | | katrin@eesti.ee | margus@hot.ee | | mari@eesti.ee | margus@hot.ee | | kati@eesti.ee | mati@eesti.ee | | katrin@eesti.ee | mati@eesti.ee | | mari@eesti.ee | mati@eesti.ee | | kati@eesti.ee | mihkel@hot.ee | | katrin@eesti.ee | mihkel@hot.ee | | mari@eesti.ee | mihkel@hot.ee | +-----------------+---------------+ 15 rows in set (0.00 sec) SELECT naised.epost AS npost, naised.synniaasta AS nsynniaasta, mehed.epost AS mpost, mehed.synniaasta AS msynniaasta FROM (SELECT * FROM jooksjad WHERE sugu='N') AS naised, (SELECT * FROM jooksjad WHERE sugu='M') AS mehed WHERE mehed.synniaasta > naised.synniaasta; +---------------+-------------+---------------+-------------+ | npost | nsynniaasta | mpost | msynniaasta | +---------------+-------------+---------------+-------------+ | kati@eesti.ee | 2007 | mati@eesti.ee | 2008 | | mari@eesti.ee | 2007 | mati@eesti.ee | 2008 | +---------------+-------------+---------------+-------------+ SELECT naised.epost AS npost, naised.synniaasta AS nsynniaasta, mehed.epost AS mpost, mehed.synniaasta AS msynniaasta FROM (SELECT * FROM jooksjad WHERE sugu='N') AS naised, (SELECT * FROM jooksjad WHERE sugu='M') AS mehed WHERE mehed.synniaasta < naised.synniaasta; +-----------------+-------------+---------------+-------------+ | npost | nsynniaasta | mpost | msynniaasta | +-----------------+-------------+---------------+-------------+ | katrin@eesti.ee | 2008 | juku@eesti.ee | 2007 | | kati@eesti.ee | 2007 | madis@hot.ee | 2003 | | katrin@eesti.ee | 2008 | madis@hot.ee | 2003 | | mari@eesti.ee | 2007 | madis@hot.ee | 2003 | | kati@eesti.ee | 2007 | margus@hot.ee | 2003 | | katrin@eesti.ee | 2008 | margus@hot.ee | 2003 | | mari@eesti.ee | 2007 | margus@hot.ee | 2003 | | kati@eesti.ee | 2007 | mihkel@hot.ee | 2002 | | katrin@eesti.ee | 2008 | mihkel@hot.ee | 2002 | | mari@eesti.ee | 2007 | mihkel@hot.ee | 2002 | +-----------------+-------------+---------------+-------------+ 10 rows in set (0.00 sec) -- Näidake välja paarid, kus mees ja naine on sama sünniaastaga SELECT naised.epost AS npost, naised.synniaasta AS nsynniaasta, mehed.epost AS mpost, mehed.synniaasta AS msynniaasta FROM (SELECT * FROM jooksjad WHERE sugu='N') AS naised, (SELECT * FROM jooksjad WHERE sugu='M') AS mehed WHERE mehed.synniaasta = naised.synniaasta; +-----------------+-------------+---------------+-------------+ | npost | nsynniaasta | mpost | msynniaasta | +-----------------+-------------+---------------+-------------+ | kati@eesti.ee | 2007 | juku@eesti.ee | 2007 | | mari@eesti.ee | 2007 | juku@eesti.ee | 2007 | | katrin@eesti.ee | 2008 | mati@eesti.ee | 2008 | +-----------------+-------------+---------------+-------------+ MariaDB [if17_jaagup_4]> SELECT LOCATE('@', epost) FROM jooksjad; +--------------------+ | LOCATE('@', epost) | +--------------------+ | 5 | | 5 | | 7 | | 6 | | 7 | | 5 | | 5 | | 7 | +--------------------+ 8 rows in set (0.00 sec) MariaDB [if17_jaagup_4]> SELECT SUBSTRING(epost, 4) FROM jooksjad; +---------------------+ | SUBSTRING(epost, 4) | +---------------------+ | u@eesti.ee | | i@eesti.ee | | rin@eesti.ee | | is@hot.ee | | gus@hot.ee | | i@eesti.ee | | i@eesti.ee | | kel@hot.ee | +---------------------+ SELECT SUBSTRING(epost, LOCATE('@', epost)+1) FROM jooksjad; +----------------------------------------+ | SUBSTRING(epost, LOCATE('@', epost)+1) | +----------------------------------------+ | eesti.ee | | eesti.ee | | eesti.ee | | hot.ee | | hot.ee | | eesti.ee | | eesti.ee | | hot.ee | +----------------------------------------+ SELECT SUBSTRING(epost, 1, LOCATE('@', epost)-1) FROM jooksjad; +-------------------------------------------+ | SUBSTRING(epost, 1, LOCATE('@', epost)-1) | +-------------------------------------------+ | juku | | kati | | katrin | | madis | | margus | | mari | | mati | | mihkel | +-------------------------------------------+ -- kuvage kasutajanimi ühte tulpa ning serverinimi teise tulpa SELECT SUBSTRING(epost, LOCATE('@', epost)+1) AS server, SUBSTRING(epost, 1, LOCATE('@', epost)-1) AS knimi, sugu FROM jooksjad; +----------+--------+------+ | server | knimi | sugu | +----------+--------+------+ | eesti.ee | juku | M | | eesti.ee | kati | N | | eesti.ee | katrin | N | | hot.ee | madis | M | | hot.ee | margus | M | | eesti.ee | mari | N | | eesti.ee | mati | M | | hot.ee | mihkel | M | +----------+--------+------+ -- Kuva kõik kirjasaatjate paarid, kus paarides sugu on erinev ja server on sama SELECT * FROM (SELECT SUBSTRING(epost, LOCATE('@', epost)+1) AS server, SUBSTRING(epost, 1, LOCATE('@', epost)-1) AS knimi FROM jooksjad WHERE sugu='N') AS naised, (SELECT SUBSTRING(epost, LOCATE('@', epost)+1) AS server, SUBSTRING(epost, 1, LOCATE('@', epost)-1) AS knimi FROM jooksjad WHERE sugu='M') AS mehed WHERE naised.server=mehed.server; +----------+--------+----------+-------+ | server | knimi | server | knimi | +----------+--------+----------+-------+ | eesti.ee | kati | eesti.ee | juku | | eesti.ee | katrin | eesti.ee | juku | | eesti.ee | mari | eesti.ee | juku | | eesti.ee | kati | eesti.ee | mati | | eesti.ee | katrin | eesti.ee | mati | | eesti.ee | mari | eesti.ee | mati | +----------+--------+----------+-------+ UPDATE jooksjad SET epost='mari@hot.ee' WHERE epost='mari@eesti.ee'; Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM (SELECT SUBSTRING(epost, LOCATE('@', epost)+1) AS server, SUBSTRING(epost, 1, LOCATE('@', epost)-1) AS knimi FROM jooksjad WHERE sugu='N') AS naised, (SELECT SUBSTRING(epost, LOCATE('@', epost)+1) AS server, SUBSTRING(epost, 1, LOCATE('@', epost)-1) AS knimi FROM jooksjad WHERE sugu='M') AS mehed WHERE naised.server=mehed.server; +----------+--------+----------+--------+ | server | knimi | server | knimi | +----------+--------+----------+--------+ | eesti.ee | kati | eesti.ee | juku | | eesti.ee | katrin | eesti.ee | juku | | hot.ee | mari | hot.ee | madis | | hot.ee | mari | hot.ee | margus | | eesti.ee | kati | eesti.ee | mati | | eesti.ee | katrin | eesti.ee | mati | | hot.ee | mari | hot.ee | mihkel | +----------+--------+----------+--------+ UPDATE jooksjad SET epost='juku@hot.ee' WHERE epost='juku@eesti.ee'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`if17_jaagup_4`.`tulemused100m`, CONSTRAINT `tulemused100m_ibfk_1` FOREIGN KEY (`jooksja`) REFERENCES `jooksjad` (`epost DROP TABLE tulemused100m; CREATE TABLE tulemused100m( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, jooksja VARCHAR(30), aegsek FLOAT, FOREIGN KEY(jooksja) REFERENCES jooksjad(epost) ON UPDATE CASCADE ); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.8); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.5); INSERT INTO tulemused100m VALUES (DEFAULT, 'juku@eesti.ee', 17.9); INSERT INTO tulemused100m VALUES (DEFAULT, 'kati@eesti.ee', 18.8); SELECT * FROM tulemused100m, jooksjad WHERE tulemused100m.jooksja=jooksjad.epost; +----+---------------+--------+---------------+------+------------+ | id | jooksja | aegsek | epost | sugu | synniaasta | +----+---------------+--------+---------------+------+------------+ | 1 | juku@eesti.ee | 17.8 | juku@eesti.ee | M | 2007 | | 2 | juku@eesti.ee | 17.5 | juku@eesti.ee | M | 2007 | | 3 | juku@eesti.ee | 17.9 | juku@eesti.ee | M | 2007 | | 4 | kati@eesti.ee | 18.8 | kati@eesti.ee | N | 2007 | +----+---------------+--------+---------------+------+------------+ UPDATE jooksjad SET epost='juku@hot.ee' WHERE epost='juku@eesti.ee'; SELECT * FROM tulemused100m; +----+---------------+--------+ | id | jooksja | aegsek | +----+---------------+--------+ | 1 | juku@hot.ee | 17.8 | | 2 | juku@hot.ee | 17.5 | | 3 | juku@hot.ee | 17.9 | | 4 | kati@eesti.ee | 18.8 | +----+---------------+--------+ -- kaks vanemat kummastki soost (SELECT * FROM jooksjad WHERE sugu='N' ORDER BY synniaasta limit 2) UNION ALL (SELECT * FROM jooksjad WHERE sugu='M' ORDER BY synniaasta limit 2); +---------------+------+------------+ | epost | sugu | synniaasta | +---------------+------+------------+ | kati@eesti.ee | N | 2007 | | mari@hot.ee | N | 2007 | | mihkel@hot.ee | M | 2002 | | madis@hot.ee | M | 2003 | +---------------+------+------------+ SET @jnr=0; SELECT @jnr; +------+ | @jnr | +------+ | 0 | +------+ SELECT @jnr:=@jnr+1, epost, synniaasta FROM jooksjad ORDER BY synniaasta; +--------------+-----------------+------------+ | @jnr:=@jnr+1 | epost | synniaasta | +--------------+-----------------+------------+ | 1 | mihkel@hot.ee | 2002 | | 2 | madis@hot.ee | 2003 | | 3 | margus@hot.ee | 2003 | | 4 | juku@hot.ee | 2007 | | 5 | kati@eesti.ee | 2007 | | 6 | mari@hot.ee | 2007 | | 7 | katrin@eesti.ee | 2008 | | 8 | mati@eesti.ee | 2008 | +--------------+-----------------+------------+ -- mediaansünniaastaga isik SET @jnr=0; SELECT * FROM (SELECT @jnr:=@jnr+1 AS loendur, epost, synniaasta FROM jooksjad ORDER BY synniaasta) AS tabel1 WHERE tabel1.loendur=(SELECT ROUND(COUNT(*)/2) FROM jooksjad); +---------+-------------+------------+ | loendur | epost | synniaasta | +---------+-------------+------------+ | 4 | juku@hot.ee | 2007 | +---------+-------------+------------+ SELECT epost, sugu, synniaasta, @rnumber:=IF(@eelmine=sugu, @rnumber+1, 1) AS nr, @eelmine:=sugu FROM jooksjad JOIN (SELECT @eelmine:=NULL, @rnumber:=0) AS muutujad ORDER BY sugu, synniaasta; +-----------------+------+------------+------+----------------+ | epost | sugu | synniaasta | nr | @eelmine:=sugu | +-----------------+------+------------+------+----------------+ | mihkel@hot.ee | M | 2002 | 1 | M | | madis@hot.ee | M | 2003 | 2 | M | | margus@hot.ee | M | 2003 | 3 | M | | juku@hot.ee | M | 2007 | 4 | M | | mati@eesti.ee | M | 2008 | 5 | M | | kati@eesti.ee | N | 2007 | 1 | N | | mari@hot.ee | N | 2007 | 2 | N | | katrin@eesti.ee | N | 2008 | 3 | N | +-----------------+------+------------+------+----------------+ SET @eelmine=NULL; SELECT epost, synniaasta, @rnumber:=IF(@eelmine=sugu, @rnumber+1, 1) AS nr, @eelmine:=sugu FROM jooksjad ORDER BY sugu, synniaasta; +-----------------+------------+------+----------------+ | epost | synniaasta | nr | @eelmine:=sugu | +-----------------+------------+------+----------------+ | mihkel@hot.ee | 2002 | 1 | M | | madis@hot.ee | 2003 | 2 | M | | margus@hot.ee | 2003 | 3 | M | | juku@hot.ee | 2007 | 4 | M | | mati@eesti.ee | 2008 | 5 | M | | kati@eesti.ee | 2007 | 1 | N | | mari@hot.ee | 2007 | 2 | N | | katrin@eesti.ee | 2008 | 3 | N | +-----------------+------------+------+----------------+ SET @eelmine=NULL; SELECT * FROM (SELECT epost, synniaasta, @rnumber:=IF(@eelmine=sugu, @rnumber+1, 1) AS nr, @eelmine:=sugu FROM jooksjad ORDER BY sugu, synniaasta) AS vahetabel WHERE nr<=2; +---------------+------------+------+----------------+ | epost | synniaasta | nr | @eelmine:=sugu | +---------------+------------+------+----------------+ | mihkel@hot.ee | 2002 | 1 | M | | madis@hot.ee | 2003 | 2 | M | | kati@eesti.ee | 2007 | 1 | N | | mari@hot.ee | 2007 | 2 | N | +---------------+------------+------+----------------+