Stampa
Categoria: SQL
Visite: 4651
Stella inattivaStella inattivaStella inattivaStella inattivaStella inattiva
 

Some useful snippet for the database mysql.

 

 Version of MySQL

SHOW VARIABLES LIKE "%version%";

 


 

Select a Random Row

SELECT * FROM captcha order by RAND() LIMIT 1

 


 

Backup of all Databases

mysqldump --lock-all-tables -u root -p --all-databases > dump.sql

 

Restore all Databases

mysql -u root -p < dump.sql

 


CREATE TABLE and  TRIGGER

--  Select the database
USE quakearea_q3log;

--  If exists drop the table
DROP TABLE IF EXISTS `Q3IPInfo`;

--  Create the Table
CREATE TABLE IF NOT EXISTS `Q3IPInfo` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `IP` varchar(18) NOT NULL,
  `HostName` varchar(128) ,
  `CountryCode` varchar(2),
  `Country` varchar(64),
  `Region` varchar(128),
  `City` varchar(128),
  `ZipCode` varchar(12),
  `Latitude` decimal(7,4) ,
  `Longitude` decimal(7,4), 
  `TimeZone` varchar(6),
  `DateInsert` datetime ,
  `DateUpdate` datetime ,
  PRIMARY KEY (`ID`),
  KEY `IP` (`IP`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=556 ;

-- If exists Drop the Triggers
DROP TRIGGER IF EXISTS tr_Q3IPInfo_OnInsert;
DROP TRIGGER IF EXISTS tr_Q3IPInfo_OnUpdate;

--  Create the trigger on insert to auto-store the now
--  in the DateInsert and Dateupdate if a null value is passed
CREATE DEFINER=`root`@`localhost` TRIGGER `tr_Q3IPInfo_OnInsert` 
BEFORE INSERT ON `Q3IPInfo` 
FOR EACH ROW 
SET NEW.DateInsert = IFNULL(NEW.DateInsert ,NOW()), NEW.DateUpdate = IFNULL( NEW.DateUpdate ,NOW());	

--  Create the trigger on update to auto-store the now
--  in the DateUpdate if a null value is passed	
CREATE DEFINER=`root`@`localhost` TRIGGER `tr_Q3IPInfo_OnUpdate` 
BEFORE UPDATE ON `Q3IPInfo` 
FOR EACH ROW 
SET NEW.DateUpdate = IFNULL( NEW.DateUpdate ,NOW());

-- Insert the 1st Record
INSERT INTO `Q3IPInfo` (`ID`, `IP`, `HostName`, `CountryCode`, `Country`, `Region`, `City`, `ZipCode`, `Latitude`, `Longitude`, `TimeZone`) VALUES
(INET_ATON('8.8.8.8'), '8.8.8.8', 'myhost.8.8.8.8', 'IT', 'Italy', 'Lombardia','Milan','20010', 45.466707, 9.177046,'+01:00');

-- Execute a the 1st select
SELECT INET_NTOA(ID) As IPAddress, ipinfo.* from `Q3IPInfo` as ipinfo

 


 

INSERT IF NOT EXISTS ELSE UPDATE

INSERT INTO myTable (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY 
   UPDATE  Timestamp=VALUES(Timestamp)

 


 

HOW TO INCLUDE NULL VALUES ON WHERE MYCOL '%'

// this where will NOT include null values
..where myCol like '%'

// this where will also include the null values
...where IFNULL(myCol, '') LIKE '%'

 


 

GRANT CREATE VIEW

GRANT CREATE VIEW ON myDatabase.* TO 'myUser'@'%'
GRANT CREATE VIEW ON myDatabase.* TO 'myUser'@'localhost'