Articles Tagués ‘mysql’

Antisèche MYSQL

Publié: 18/03/2014 dans Programmation
Tags:, ,

Voici une petite anti-sèche des commandes MySQL les plus courantes. On sait jamais, ça peut être utile…
(Surtout pour moi qui ai une ram datant de l’époque mérovingienne…)

CREATE DATABASE `maindb` /*!40100 DEFAULT CHARACTER SET utf8 */;

DROP TABLE IF EXISTS `maindb`.`log`;
CREATE TABLE  `maindb`.`log` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`client` varchar(64) NOT NULL,
`method` varchar(16) NOT NULL,
`component` varchar(32) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`freetext` varchar(256) NOT NULL,
`ack` enum(’0′,’1′) NOT NULL DEFAULT ’0′,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO log (client, method, component, freetext)
VALUES (‘Client01′, ‘PS’, ‘COMP01′, “”);

INSERT INTO log (client, method, component, freetext)
VALUES (‘Client01′, ‘SNMP’, ‘COMP01′, ‘No response from localhost’);

SELECT * FROM log;

SELECT * FROM log WHERE ack=’0′;

CREATE VIEW `maindb`.`not_ack` AS
SELECT id, client, method, component, timestamp, freetext FROM log WHERE ack=’0′;

UPDATE log SET ack=’1′ WHERE client=’Client01′;

DELETE FROM log WHERE client=’Client01′;

SELECT * FROM not_ack;

SELECT * FROM maindb.log WHERE DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR) <= timestamp;

DELETE FROM maindb.log WHERE (DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) > timestamp) AND (ack=’1′);

Procédures et fonctions stockées

Procédure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `maindb`.`CLEAN_OLD_ACK` $$
CREATE PROCEDURE `maindb`.`CLEAN_OLD_ACK` ()
BEGIN
DELETE FROM maindb.log
WHERE (DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) > timestamp)
AND (ack=’1′);
END $$

DELIMITER ;

Procédure renvoyant une valeur passée en paramètre:

DELIMITER $$

DROP PROCEDURE IF EXISTS `maindb`.`NBALERTS` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `NBALERTS`(OUT result BIGINT)
BEGIN
SELECT COUNT(*) INTO result FROM log;
END $$

DELIMITER ;

On peut l’utiliser ainsi:

CALL NBALERTS(@a);
SELECT @a;

Procédure utilisant une variable utilisateur:

SELECT value INTO @age FROM config WHERE token=’age_purge’ LIMIT 1;
SELECT * FROM log WHERE (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(timestamp) > (86400*@age));

Fonction:

DELIMITER $$

DROP FUNCTION IF EXISTS `maindb`.`bonjour` $$
CREATE FUNCTION `maindb`.`bonjour` (s CHAR(20)) RETURNS CHAR(50)
BEGIN
RETURN CONCAT(‘Bonjour, ‘,s,’!’);
END $$

DELIMITER ;

Elle peut s’utiliser ainsi:

SELECT bonjour(‘moi’);

Création de vue:

select
`maindb`.`user`.`Id` AS `id_user`,
concat(`maindb`.`user`.`prenom`,’ ‘,`maindb`.`user`.`nom`,’ (‘,`maindb`.`user`.`pseudo`,’)’) AS `nom`,
`maindb`.`user_objet`.`objet` AS `objet`
from (`maindb`.`user` left join `maindb`.`user_objet` on((`maindb`.`user`.`Id` = `maindb`.`user_objet`.`id_user`)))

Client MySQL en C#

Vous aurez besoin du connecteur MySQL .NET, disponible sur le site de MySQL.

using System;
using System.Collections.Generic;
using System.Text;
using System.Threading;
using MySql.Data.MySqlClient;

namespace mysql
{
class MySQLConnector
{
private MySqlConnection ConnectionRessource;
private string DBName;
private string Server;
private string Login;
private string Password;
private Boolean dbUp;
private string connectionString;

private string requete;
private MySqlCommand com;
private MySqlDataReader dr;

public MySQLConnector()
{
this.DBName = “testdb”;
this.Server = “localhost”;
this.Login = “root”;
this.Password = “pass”;
this.dbUp = false;
this.connectionString = “Server=” + this.Server + “;” +
“Database=” + this.DBName + “;” +
“User ID=” + this.Login + “;” +
“Password=” + this.Password + “;”;
try
{
ConnectionRessource = new MySqlConnection(connectionString);
ConnectionRessource.Open();
this.dbUp = true;
}
catch(MySqlException e)
{
System.Console.Out.WriteLine(e.Message);
}
}

public void Close()
{
try
{
ConnectionRessource.Close();
}
catch (MySqlException e)
{
System.Console.Out.WriteLine(e.Message);
}
}

public void test()
{
requete = “SELECT * FROM table”;
com=new MySqlCommand(requete,ConnectionRessource);
dr = com.ExecuteReader();
while(dr.Read())
{
for(int i=0;i<dr.FieldCount;i++)
{
System.Console.Out.Write(dr.GetValue(i)+” “);
}
System.Console.Out.WriteLine(“”);
}
}
}
}