/*------------ Seznam Pohledu Views------------*/

SHOW FULL TABLES IN <databaze> WHERE TABLE_TYPE LIKE 'VIEW';

SELECT * FROM <název pohledu views>





/*------------ Tvorba pohled ------------*/

CREATE VIEW MujPohled AS SELECT * FROM studenti;

/*--------------------------------------*/




/*------- Věk ------------------------ */ 

SELECT jmeno, year(CURDATE())-year(datum_narozeni) as vek 
from studenti




SELECT jmeno, 
CASE WHEN DAYOFYEAR(CURDATE())>= DAYOFYEAR(datum_narozeni) THEN 
   YEAR(CURDATE())-YEAR(datum_narozeni)  
ELSE 
   YEAR(CURDATE())-YEAR(datum_narozeni) - 1 
END AS vek 
FROM studenti




/* ----- ziskat informace z pohledu ---- */

SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = "jméno_pohledu"


SELECT VIEW_DEFINITION 
FROM INFORMATION_SCHEMA.VIEWS 
  WHERE TABLE_SCHEMA = 'jméno_databáze' 
  AND 
  TABLE_NAME = 'jméno_pohledu'



------------------------------
Syntaxe pohledu Views
-----------------------------------

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]





/* ------- Podklad tabulka ------------------ */

CREATE TABLE `studenti` (
  `uzivatele_id` int,
  `jmeno` varchar(60),
  `prijmeni` varchar(60),
  `pohlavi` varchar(1),
  `PLAT` int,
  `datum_narozeni` date,
  `mesto` varchar(20),
  `fakulta` int
);

insert into studenti values ('1', 'jana', 'krátka', 'f', '23500', '1954-12-22', 'Brno', '0');
insert into studenti values ('2', 'Jana', 'Nováková', 'F', '42261', '2016-11-11', 'Brno', '1');
insert into studenti values ('3', 'Sophia', 'Santiago', 'F', '20758', '1962-11-16', 'Brno', '8');
insert into studenti values ('4', 'Jarek', 'Benes', 'M', '52483', '2017-01-11', 'Praha', '8');
insert into studenti values ('5', 'Petr', 'Malý', 'M', '10000', '2001-09-23', 'Praha', '8');
insert into studenti values ('6', 'Honza', 'Adamec', 'M', '29990', '1977-07-27', 'Praha', '7');
insert into studenti values ('7', 'Petra', 'Adamcova', 'F', '23500', '1982-03-16', 'Olomouc', '6');
insert into studenti values ('8', 'Zuzka', 'Bezva', 'F', '39752', '2016-02-29', 'Ostrava', '7');
insert into studenti values ('9', 'Ivan', 'Sobota', 'M', '15274', '1900-02-01', 'Pardubice', '3');
insert into studenti values ('10', 'Petr', 'Novak', 'M', '27679', '2002-05-20', 'Zlin', '2');
insert into studenti values ('11', 'Jirka', 'Plavec', 'M', '23500', '2017-02-05', 'Opava', '1');
insert into studenti values ('12', 'Iva', 'Nováková', 'F', '25371', '2007-06-07', 'Opava', '0');
insert into studenti values ('14', 'Petr', 'Novak', 'M', '21072', '2011-06-15', 'Pardubice', '7');
insert into studenti values ('15', 'Iva', 'Nováková', 'F', '23181', '1994-02-13', 'Brno', '8');