MySQL podsetnik

Podsetnik za osnovne MySQL naredbe

Ovaj podsetnik baziran je na MySQL naredbama koje se pojavljuju u Tutorijalu.

Za sve primere u ovom podsetniku koristicemo sledecu tabelu:

name    owner   species sex     birth           death
Fluffy  Harold  cat     f       1993-02-04      NULL
Claws   Gwen    cat     m       1994-03-17      NULL
Buffy   Harold  dog     f       1989-05-13      NULL
Fang    Benny   dog     m       1990-08-27      NULL
Bowser  Diane   dog     m       1979-08-31      1995-07-29
Chirpy  Gwen    bird    f       1998-09-11      NULL
WhistlerGwen    bird    NULL    1997-12-09      NULL
Slim    Benny   snake   m       1996-04-29      NULL

Otvaranje i zatvaranje mysql terminala (monitora)

shell> sudo mysql  -- startovanje mysql terminala
mysql> quit        -- stopiranje mysql terminala

SQL naredbe

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> SHOW DATABASES;
mysql> CREATE DATABASE menazerija;
mysql> USE menazerija;
mysql> SHOW TABLES;
mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet; -- tab separated column values
mysql> INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
mysql> SELECT what_to_select FROM which_table WHERE conditions_to_satisfy;  -- general form of select statement
mysql> SELECT * FROM pet;    -- select all rows in table pet
mysql> DELETE FROM pet;  -- delete all rows in table pet
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE name = 'Bowser';
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f';
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird';
mysql> SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm') OR (species = 'dog' AND sex = 'f');
mysql> SELECT name, birth FROM pet;
mysql> SELECT owner FROM pet;
mysql> SELECT DISTINCT owner FROM pet;
mysql> SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';
mysql> SELECT name, birth FROM pet ORDER BY birth;
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY name;
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet ORDER BY age;
mysql> SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
mysql> SELECT name, birth, MONTH(birth) FROM pet;
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
mysql> SELECT '2018-10-31' + INTERVAL 1 DAY;
mysql> SELECT '2018-10-32' + INTERVAL 1 DAY;
mysql> SHOW WARNINGS;
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;
mysql> SELECT * FROM pet WHERE name LIKE 'b%';  -- pattern matching
mysql> SELECT * FROM pet WHERE name LIKE '%fy';
mysql> SELECT * FROM pet WHERE name LIKE '%w%';
mysql> SELECT * FROM pet WHERE name LIKE '_____';   -- all pets with name length of 5 characters
mysql> SELECT * FROM pet WHERE name REGEXP '^b';
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';  -- case sensitive
mysql> SELECT * FROM pet WHERE name REGEXP 'w'; -- all names that cointain w
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';  -- all names of length 5
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';   -- same as above
mysql> SELECT COUNT(*) FROM pet;
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE sex IS NOT NULL GROUP BY species, sex;