MySQL frequent sql instructions

November 24, 2019 12:04 am Published by

Repository ufficiale MySQL Developer Zone https://dev.mysql.com

--Versione MySQL
select version();

--Seleziona database da utilizzare
USE nomedb;

--Creazione utente con password
CREATE USER 'nome_utente'@'localhost' 
   IDENTIFIED BY 'inserire-passpwrd';

-- Cancellazione utente
DROP USER 'nome_utente'@'localhost';

--Assegnazione a un singolo utente di tutti i privilegi 
--di gestione di un singolo database.
GRANT ALL PRIVILEGES ON nome_database.* 
   TO 'nome_utente'@'localhost';


--elenco dello spazio disco utilizzo dall'istanza del database 
--http://stackoverflow.com/questions/184560/how-to-monitor-mysql-space
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

--caricare dei file in formato CSV
--Attenzione: la sequenza dei campi nel file deve rispecchiare con i campi nella tabella.
LOAD DATA INFILE "/etc/mysql/import/file-to-import.csv"
INTO TABLE name_table
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY """"
LINES TERMINATED BY "\n";

Categorised in: ,

This post was written by admin

Comments are closed here.