« MySQL/Manipulation de données » : différence entre les versions

Contenu supprimé Contenu ajouté
Aucun résumé des modifications
Aucun résumé des modifications
Ligne 109 :
</source>
You can order the rows before deleting them, and then delete only a given number of rows.
 
 
== CREATE ==
Soit l'exemple suivant qui sera utilisé pour les sélections ensuite (toute ressemblance avec un framework connu est purement non fortuite : si vous avez déjà votre propre wiki, il est possible de sauter cette phase pour passer directement au paragraphe SELECT).
 
{{(|Création d'une base}}
<source lang=sql>
CREATE DATABASE wiki1;
USE wiki1;
 
-- Liste des utilisateurs
CREATE TABLE IF NOT EXISTS `wiki1_user` (
`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varbinary(255) NOT NULL DEFAULT '',
`user_real_name` varbinary(255) NOT NULL DEFAULT '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` binary(14) DEFAULT NULL,
`user_email` tinyblob NOT NULL,
`user_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_token` binary(32) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`user_email_authenticated` binary(14) DEFAULT NULL,
`user_email_token` binary(32) DEFAULT NULL,
`user_email_token_expires` binary(14) DEFAULT NULL,
`user_registration` binary(14) DEFAULT NULL,
`user_editcount` int(11) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`),
KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=41 ;
 
INSERT INTO `wiki1_user` (`user_id`, `user_name`, `user_real_name`, `user_password`, `user_newpassword`, `user_newpass_time`, `user_email`, `user_touched`, `user_token`, `user_email_authenticated`, `user_email_token`, `user_email_token_expires`, `user_registration`, `user_editcount`) VALUES
(1, 'Utilisateur1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 1000),
(2, 'Utilisateur2', '', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 800),
(3, 'Bot1', 'admin', '', '', NULL, '', '', '', '', '', '20130101', '20130101', 5000),
(4, 'Utilisateur3', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 500),
(5, 'Utilisateur4', '', '', '', NULL, '', '', '', '', '', '20130102', '20130102', 200);
 
 
-- Liste des pages
CREATE TABLE IF NOT EXISTS `wiki1_page` (
`page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`page_namespace` int(11) NOT NULL,
`page_title` varbinary(255) NOT NULL,
`page_restrictions` tinyblob NOT NULL,
`page_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
`page_is_redirect` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_is_new` tinyint(3) unsigned NOT NULL DEFAULT '0',
`page_random` double unsigned NOT NULL,
`page_touched` binary(14) NOT NULL DEFAULT '\0\0\0\0\0\0\0\0\0\0\0\0\0\0',
`page_latest` int(10) unsigned NOT NULL,
`page_len` int(10) unsigned NOT NULL,
PRIMARY KEY (`page_id`),
UNIQUE KEY `name_title` (`page_namespace`,`page_title`),
KEY `page_random` (`page_random`),
KEY `page_len` (`page_len`),
KEY `page_redirect_namespace_len` (`page_is_redirect`,`page_namespace`,`page_len`)
) ENGINE=InnoDB DEFAULT CHARSET=binary AUTO_INCREMENT=8;
 
INSERT INTO `wiki1_page` (`page_id`, `page_namespace`, `page_title`, `page_restrictions`, `page_counter`, `page_is_redirect`, `page_is_new`, `page_random`, `page_touched`, `page_latest`, `page_len`) VALUES
(1, 0, 'Accueil', '', 0, 0, 0, 0, '', 0, 0),
(2, 8, 'Sidebar', '', 0, 0, 0, 0, '', 0, 0),
(3, 0, 'MySQL', '', 0, 0, 0, 0, '', 0, 0),
(4, 0, 'PHP', '', 0, 0, 0, 0, '', 0, 0);
 
 
-- Propriétés des pages
CREATE TABLE IF NOT EXISTS `wiki1_page_props` (
`pp_page` int(11) NOT NULL,
`pp_propname` varbinary(60) NOT NULL,
`pp_value` blob NOT NULL,
UNIQUE KEY `pp_page_propname` (`pp_page`,`pp_propname`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
 
INSERT INTO `wiki1_page_props` (`pp_page`, `pp_propname`, `pp_value`) VALUES
(1, 'noindex', ''),
(2, 'defaultsort', ''),
(2, 'noindex', '');
 
 
-- Hyperliens dans les pages
CREATE TABLE IF NOT EXISTS `wiki1_pagelinks` (
`pl_from` int(10) unsigned NOT NULL DEFAULT '0',
`pl_namespace` int(11) NOT NULL DEFAULT '0',
`pl_title` varbinary(255) NOT NULL DEFAULT '',
UNIQUE KEY `pl_from` (`pl_from`,`pl_namespace`,`pl_title`),
UNIQUE KEY `pl_namespace` (`pl_namespace`,`pl_title`,`pl_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;
 
INSERT INTO `wiki1_pagelinks` (`pl_from`, `pl_namespace`, `pl_title`) VALUES
(1, 0, 'Lien1'),
(3, 0, 'Lien2');
</source>
{{)}}