Soovime luua tabelit koerad. Tulpadeks id, koeranimi, synniaasta

CREATE TABLE koerad(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  koeranimi VARCHAR(30),
  synniaasta INT
);

Tabeli eemaldamiseks

DROP TABLE koerad;

Andmete sisestamine:

INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Polli', 2008);
INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Muki', 2007);
INSERT INTO koerad (koeranimi, synniaasta) VALUES ('Matu', 2009);

Küsimine:

SELECT * FROM koerad;

+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Polli     |       2008 |
|  2 | Muki      |       2007 |
|  3 | Matu      |       2009 |
+----+-----------+------------+

SELECT * FROM koerad ORDER BY synniaasta;

+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  2 | Muki      |       2007 |
|  1 | Polli     |       2008 |
|  3 | Matu      |       2009 |
+----+-----------+------------+


Muutmine:

UPDATE koerad SET koeranimi='Muri' WHERE id=2;


mysql> SELECT * FROM koerad;
+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Polli     |       2008 |
|  2 | Muri      |       2007 |
|  3 | Matu      |       2009 |
+----+-----------+------------+

DELETE FROM koerad WHERE id=2;


mysql> DELETE FROM koerad WHERE id=2;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM koerad;
+----+-----------+------------+
| id | koeranimi | synniaasta |
+----+-----------+------------+
|  1 | Polli     |       2008 |
|  3 | Matu      |       2009 |
+----+-----------+------------+


--- Tabeli struktuuri muutmine
ALTER TABLE koerad ADD mass INT;


mysql> ALTER TABLE koerad ADD mass INT;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ALTER TABLE koerad DROP mass;


mysql> ALTER TABLE koerad DROP mass;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

ALTER TABLE koerad ADD mass INT DEFAULT 0;

mysql> ALTER TABLE koerad ADD mass INT DEFAULT 0;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM koerad ORDER BY id;
+----+-----------+------------+------+
| id | koeranimi | synniaasta | mass |
+----+-----------+------------+------+
|  1 | Polli     |       2008 |    0 |
|  3 | Matu      |       2009 |    0 |
|  5 | Muki      |       2011 |    0 |
+----+-----------+------------+------+
3 rows in set (0.00 sec)

ALTER TABLE koerad CHANGE mass mass_grammides INT;

mysql> ALTER TABLE koerad CHANGE mass mass_grammides INT;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

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