SQLis arvutused


mysql> SELECT * FROM koerad ORDER BY id;
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  1 | Polli     |       2008 |              0 |
|  3 | Matu      |       2009 |              0 |
|  5 | Muki      |       2011 |              0 |
+----+-----------+------------+----------------+

SELECT koeranimi, 2012-synniaasta AS vanus FROM koerad;

mysql> SELECT koeranimi, 2012-synniaasta AS vanus FROM koerad;
+-----------+-------+
| koeranimi | vanus |
+-----------+-------+
| Polli     |     4 |
| Muki      |     1 |
| Matu      |     3 |
+-----------+-------+

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2012 |
+-------------+

SELECT koeranimi, synniaasta, YEAR(NOW())-synniaasta AS vanus FROM koerad;

mysql> SELECT koeranimi, synniaasta, YEAR(NOW())-synniaasta AS vanus FROM koerad;
+-----------+------------+-------+
| koeranimi | synniaasta | vanus |
+-----------+------------+-------+
| Polli     |       2008 |     4 |
| Muki      |       2011 |     1 |
| Matu      |       2009 |     3 |
+-----------+------------+-------+

UPDATE koerad SET mass_grammides=(YEAR(NOW())-synniaasta)*1000;

mysql> UPDATE koerad SET mass_grammides=(YEAR(NOW())-synniaasta)*1000;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM koerad;
+----+-----------+------------+----------------+
| id | koeranimi | synniaasta | mass_grammides |
+----+-----------+------------+----------------+
|  1 | Polli     |       2008 |           4000 |
|  5 | Muki      |       2011 |           1000 |
|  3 | Matu      |       2009 |           3000 |
+----+-----------+------------+----------------+

INSERT INTO koerad (koeranimi, synniaasta, mass_grammides) VALUES ('Karu', 2009, 5000);


Kokkuvõtvad funktsioonid (agregaatfunktsioonid)

SELECT MAX(synniaasta) FROM koerad;
SELECT MIN(synniaasta) FROM koerad;
SELECT AVG(mass_grammides) FROM koerad;
SELECT SUM(mass_grammides) FROM koerad;


mysql> SELECT AVG(mass_grammides) FROM koerad;
+---------------------+
| AVG(mass_grammides) |
+---------------------+
|           3250.0000 |
+---------------------+

mysql> SELECT AVG(mass_grammides) FROM koerad WHERE mass_grammides>1000;
+---------------------+
| AVG(mass_grammides) |
+---------------------+
|           4000.0000 |
+---------------------+
1 row in set (0.00 sec)

SELECT DISTINCT(synniaasta) FROM koerad; --erinevad

SELECT COUNT(*) FROM koerad;  --koeri kokku

SELECT synniaasta, COUNT(*) FROM koerad GROUP BY synniaasta;

mysql> SELECT synniaasta, AVG(mass_grammides) FROM koerad GROUP BY synniaasta;
+------------+---------------------+
| synniaasta | AVG(mass_grammides) |
+------------+---------------------+
|       2008 |           4000.0000 |
|       2009 |           4000.0000 |
|       2011 |           1000.0000 |
+------------+---------------------+

mysql> SELECT synniaasta, COUNT(*) as kogus FROM koerad GROUP BY synniaasta HAVING kogus>1;
+------------+-------+
| synniaasta | kogus |
+------------+-------+
|       2009 |     2 |
+------------+-------+
1 row in set (0.00 sec)

SELECT synniaasta, GROUP_CONCAT(koeranimi) FROM koerad GROUP BY synniaasta;
mysql> SELECT synniaasta, GROUP_CONCAT(koeranimi) FROM koerad GROUP BY synniaasta;
+------------+-------------------------+
| synniaasta | GROUP_CONCAT(koeranimi) |
+------------+-------------------------+
|       2008 | Polli                   |
|       2009 | Matu,Karu               |
|       2011 | Muki                    |
+------------+-------------------------+