Archives pour la catégorie mysql

Mysql subquery

Database

CREATE TABLE IF NOT EXISTS `author` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
INSERT INTO `author` (`id`, `name`) VALUES
(1, 'HiO'),
(2, 'Blu'),
(3, 'Pouet');
 
CREATE TABLE IF NOT EXISTS `post` (
  `a_id` INT(10) UNSIGNED DEFAULT NULL,
  `title` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `post` (`a_id`, `title`) VALUES
(1, 'My name is HiO :)'),
(NULL, 'I am an anonymous :)'),
(2, 'My name is Blu :D'),
(3, 'My name is Pouet ><''');

Subquery

SELECT *, (SELECT COUNT(post.a_id) FROM post WHERE post.a_id = author.id) AS post_count FROM `author`

Result

$author = array(
  array('id'=>'1','name'=>'HiO','post_count'=>'1'),
  array('id'=>'2','name'=>'Blu','post_count'=>'1'),
  array('id'=>'3','name'=>'Pouet','post_count'=>'1')
);

Subquery in where clause

SELECT * FROM `post` WHERE a_id IN (SELECT id FROM author)

Result

$post = array(
  array('a_id'=>'1','title'=>'My name is HiO :)'),
  array('a_id'=>'2','title'=>'My name is Blu :D'),
  array('a_id'=>'3','title'=>'My name is Pouet ><\'')
);

Mysql join

Database

CREATE TABLE IF NOT EXISTS `author` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
INSERT INTO `author` (`id`, `name`) VALUES
(1, 'HiO'),
(2, 'Blu'),
(3, 'Pouet');
 
CREATE TABLE IF NOT EXISTS `post` (
  `a_id` INT(10) UNSIGNED DEFAULT NULL,
  `title` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `post` (`a_id`, `title`) VALUES
(1, 'My name is HiO :)'),
(NULL, 'I am an anonymous :)'),
(2, 'My name is Blu :D'),
(3, 'My name is Pouet ><''');

INNER JOIN

SELECT * FROM `post` INNER JOIN author ON post.a_id = author.id

Result

$post = array(
  array('a_id'=>'1','title'=>'My name is HiO :)','id'=>'1','name'=>'HiO'),
  array('a_id'=>'2','title'=>'My name is Blu :D','id'=>'2','name'=>'Blu'),
  array('a_id'=>'3','title'=>'My name is Pouet ><\'','id'=>'3','name'=>'Pouet')
);

LEFT JOIN

SELECT * FROM `post` LEFT JOIN author ON post.a_id = author.id

Result

$post = array(
  array('a_id'=>'1','title'=>'My name is HiO :)','id'=>'1','name'=>'HiO'),
  array('a_id'=>NULL,'title'=>'I am an anonymous :)','id'=>NULL,'name'=>NULL),
  array('a_id'=>'2','title'=>'My name is Blu :D','id'=>'2','name'=>'Blu'),
  array('a_id'=>'3','title'=>'My name is Pouet ><\'','id'=>'3','name'=>'Pouet')
);

RIGHT JOIN

SELECT * FROM `author` RIGHT JOIN post ON author.id = post.a_id

Result

$author = array(
  array('id'=>'1','name'=>'HiO','a_id'=>'1','title'=>'My name is HiO :)'),
  array('id'=>NULL,'name'=>NULL,'a_id'=>NULL,'title'=>'I am an anonymous :)'),
  array('id'=>'2','name'=>'Blu','a_id'=>'2','title'=>'My name is Blu :D'),
  array('id'=>'3','name'=>'Pouet','a_id'=>'3','title'=>'My name is Pouet ><\'')
);

MySQL Les différents moteurs de stockage

Un article tres interressant sur MySQL et ses differents moteurs de stockage.

MySQL, contrairement aux autres SGBD, a la possibilité d’utiliser plusieurs moteurs de stockage dans une seule et même base de données. Cette faculté constitue un des points forts de MySQL. Cet article a pour but de vous montrer ce qui se cache derrière ces différents moteurs et comment les exploiter au mieux.

Source

MySQL Script bash pour dumper une db

Voila un ptit script pratique pour dumper une db et la compresser ^^

mysqldump.sh

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

#!/bin/bash
 
tar_args='-czf'
tmpdir='/tmp'
mysqldump_args='--user=root --password=password -c'
 
mysqldump $mysqldump_args $1 > $tmpdir/$1.sql
 
if test ! -d $2
    then
    echo 'output dir does not exists';
fi
 
if test -f $tmpdir/$1.sql
    then
    tar $tar_args $2/$1.tar.gz $tmpdir/$1.sql && rm $tmpdir/$1.sql
fi

Utilisation

/root/mysqldump.sh wordpress /home/hio/

Explication
Premier argument = nom de la db
Deuxieme argument = Repertoire qui contiendra la db dans un tar.gz

On trouvera donc un wordpress.tar.gz dans /home/hio/

hio@phpoulpe:/home/hio# ls -l /home/hio/wordpress.tar.gz
-rw-r--r-- 1 hio hio 78423 mar  3 22:05 /home/hio/wordpress.tar.gz

Simple, pratique et efficace ^^