-- Luciano Nicastro @ INAF
--
-- Data ultimo aggiornamento: 28/11/2017
--
-- Notare:
--   eseguire il MySQL monitor (client) come utente root o dall'area progammi
--   o da terminale nell'area di  lavoro:
--

mysql --local-infile=1 -u root -p

--
--   di seguito il prompt "mysql>" viene omesso per semplificare il taglia-e-incolla
--

-- Creare il database opus2017

create database opus2017;

-- Creare il database "world" e inseriamo le 3 tabelle associate

create database world;

-- Adesso ci spostiamo in quel DB

use world;

-- Inseriamo le 3 tabelle associate usando lo script sql

source world.sql



-- Creare un utente utile per le dimostrazioni; lo chiamiamo "opus"

create user opus@localhost identified by "opuspass";

-- Assegna tutti i privilegi (select, insert, delete, update, ...) all'utente "opus" per tutte le tabelle nel DB "opus2017"

grant all privileges on opus2017.* to opus@localhost;

flush privileges;

-- Vediamoli

show grants for opus@localhost;

-- Adesso ci spostiamo nel DB "world"

use world;

-- Vediamo le tabelle presenti

show tables;

-- Contiamo i numero di righe nella tabella "city"

select count(*) from city;

-- Vediamo la struttura delle tabelle "country" e "countrylanguage"

describe country;

describe countrylanguage;

-- Vediamo il comando con cui e' stata creata (o si puo' ricreare) la tabella "countrylanguage"

show create table countrylanguage;

-- Vediamo piu' informazioni sugli indici presenti nella tabella "city"

show key from city;

-- Help dei comandi del monitor (client), NON di SQL: "help" oppure "?"

help

-- Per avere l'help di compionenti specifici usare "help" con parametri aggiuntivi. Per vedere quelli disponibili:

help contents

-- Ad esmepio per avere aiuto sui comandi DML (manipolazione dati)

help Data Manipulation

-- Per quello amministrativi

help Administration

-- e cosi' via

-- Esercitazione: eseguire vari SELECT sulle tabelle

...




-- Alcuni comandi frequenti

help 
help contents
status
prompt MySQL>
prompt
show tables;
show table status like 'country%' ;
delimiter //  -- cambia il delimitatore finale di comando da ; a //
show collation //
show engines\G
delimiter ;  -- rimettiamo il delimitatrore standard ;
select version(), database(), user(), connection_id();
select curdate(), curtime(), now();

select @sql_mode;
select @@sql_mode;


-- Facciamo pratica con query di tipo SELECT, crazione di nuove tabelle e cambiando il formato delle colonne

SELECT * FROM countrylanguage WHERE Language = 'zulu';
SELECT * FROM countrylanguage WHERE Language like 'w%';
SELECT * FROM countrylanguage WHERE Language IN ('urdu', 'zulu', 'wolof') ORDER BY Language;

SELECT *
FROM countrylanguage
WHERE Language IN ('Urdu', 'Zulu', 'Wolof')
ORDER BY FIELD(Language, "Wolof", "Zulu", "Urdu");


-- Use dello switch CASE

SELECT *,
CASE Language
WHEN 'Wolof' THEN 1
WHEN 'Zulu' THEN 2
WHEN 'Urdu' THEN 3
ELSE NULL 
END AS orderMe
FROM countrylanguage
WHERE Language IN ('Urdu', 'Zulu', 'Wolof') 
ORDER BY orderMe;


-- Una semplice JOIN tra tabelle: tutte le lingue europee

SELECT * FROM Country
JOIN CountryLanguage
ON Country.Code=CountryLanguage.CountryCode
WHERE Continent='Europe';


...


-- Facciamo pratica con il catalogo "Bright Stars Catalogue"

-- Clicca  QUI 




-- Facciamo pratica con il catalogo di Messier

-- Clicca  QUI 



-- Le tabelle nel DB di sistema "information_schema"

show tables from information_schema;
select * from information_schema.tables where table_name='bsc';
select * from information_schema.columns where table_name='bsc';
select column_name from information_schema.columns where table_name = 'bsc';
SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='opus2017';
SELECT table_name FROM INFORMATION_SCHEMA.TABLES where table_schema='opus2017' AND table_type = 'View';

describe INFORMATION_SCHEMA.ROUTINES;

SELECT routine_name FROM INFORMATION_SCHEMA.ROUTINES;
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE routine_name like 'ver%';



-- Creiamo una funzione per mostrare valori numerici in notazione scientifica

drop function if exists sci;
DELIMITER $$
create function sci (num DOUBLE) returns text deterministic language sql
begin
  set @exp = IF(num=0, 0, FLOOR(LOG10(ABS(num))));
  return CONCAT(FORMAT(num*POWER(10,-@exp),6),'e',@exp);
end$$
DELIMITER ;


-- Notare l'uso del delimitatore temporaneo $$
-- Proviamola sulla tabella "bsc"

select sci(RAd), sci(DECd), sci(Vmag) from bsc limit 3;

select sci(max(RAd) * PI()/180), sci(max(DECd) * PI()/180), sci(min(Vmag)) from bsc;


-- Usare help _nome_funzione_ per vedere cosa fanno le varie funziuni SQL usate, ad es.

help FLOOR;