Utilisateur:Gribeco/MySQL
Un article de Wikipédia, l'encyclopédie libre.
Sommaire |
[modifier] Connexion et syntaxe
Connexion depuis toolserver : mysql frwiki_p
mysql> select cur_title from cur where cur_title like 'Terre-Neuve%'; +-------------------------+ | cur_title | +-------------------------+ | Terre-Neuve | | Terre-Neuve-et-Labrador | | Terre-Neuve-et-Labrador | | Terre-Neuve-et-Labrador | | Terre-Neuve_et_Labrador | +-------------------------+ 5 rows in set (0.00 sec)
[modifier] Commandes
show tables; |
Liste des tables |
describe cur; |
Champs d'une table |
[modifier] Tables et champs
Description des tables et des champs
[modifier] Liste des tables
mysql> show tables; +--------------------+ | Tables_in_frwiki_p | +--------------------+ | archive | | categorylinks | | cur | | externallinks | | hitcounter | | image | | imagelinks | | interwiki | | langlinks | | logging | | math | | oldimage | | page | | pagelinks | | recentchanges | | revision | | site_stats | | templatelinks | | text | | updates | | user | | user_groups | | user_ids | +--------------------+ 23 rows in set (0.02 sec)
[modifier] Table page
Détails : meta:Page table
mysql> describe page; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | page_id | int(8) unsigned | NO | | 0 | | | page_namespace | int(11) | NO | | 0 | | | page_title | varchar(255) | NO | | | | | page_restrictions | tinyblob | NO | | | | | page_counter | bigint(20) unsigned | NO | | 0 | | | page_is_redirect | tinyint(1) unsigned | NO | | 0 | | | page_is_new | tinyint(1) unsigned | NO | | 0 | | | page_random | double unsigned | NO | | 0 | | | page_touched | varchar(14) | NO | | | | | page_latest | int(8) unsigned | NO | | 0 | | | page_len | int(8) unsigned | NO | | 0 | | +-------------------+---------------------+------+-----+---------+-------+
[modifier] Table revision
mysql> describe revision; +----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------------+------+-----+---------+-------+ | rev_id | int(8) unsigned | NO | | 0 | | | rev_page | int(8) unsigned | NO | | 0 | | | rev_text_id | int(8) unsigned | NO | | 0 | | | rev_comment | tinyblob | NO | | | | | rev_user | int(5) unsigned | NO | | 0 | | | rev_user_text | varchar(255) | NO | | | | | rev_timestamp | varchar(14) | NO | | | | | rev_minor_edit | tinyint(1) unsigned | NO | | 0 | | | rev_deleted | tinyint(1) unsigned | NO | | 0 | | +----------------+---------------------+------+-----+---------+-------+ mysql> select rev_id, rev_page, rev_user, rev_user_text, rev_timestamp from revision where rev_user_text like 'Gribeco' order by rev_timestamp desc limit 20; +----------+----------+----------+---------------+----------------+ | rev_id | rev_page | rev_user | rev_user_text | rev_timestamp | +----------+----------+----------+---------------+----------------+ | 11305135 | 803468 | 24358 | Gribeco | 20061031012724 | | 11305114 | 1120370 | 24358 | Gribeco | 20061031012438 | | 11304966 | 347408 | 24358 | Gribeco | 20061031010730 | | 11304957 | 1120353 | 24358 | Gribeco | 20061031010708 | | 11304952 | 926922 | 24358 | Gribeco | 20061031010632 | | 11304321 | 8568 | 24358 | Gribeco | 20061031000659 | | 11304319 | 6162 | 24358 | Gribeco | 20061031000651 | | 11304311 | 1120290 | 24358 | Gribeco | 20061031000621 | | 11304308 | 681138 | 24358 | Gribeco | 20061031000609 | | 11297026 | 413157 | 24358 | Gribeco | 20061030181413 | | 11296991 | 73945 | 24358 | Gribeco | 20061030181251 | | 11292558 | 30776 | 24358 | Gribeco | 20061030152646 | | 11292465 | 1118929 | 24358 | Gribeco | 20061030152313 | | 11292449 | 1116566 | 24358 | Gribeco | 20061030152233 | | 11292335 | 527899 | 24358 | Gribeco | 20061030151755 | | 11292312 | 527899 | 24358 | Gribeco | 20061030151701 | | 11292110 | 447862 | 24358 | Gribeco | 20061030150711 | | 11281826 | 1116055 | 24358 | Gribeco | 20061030044556 | | 11279925 | 1116055 | 24358 | Gribeco | 20061030003720 | | 11279732 | 1087813 | 24358 | Gribeco | 20061030002701 | +----------+----------+----------+---------------+----------------+ mysql> select count(*) from revision where rev_user_text like '82.240.65.115'; +----------+ | count(*) | +----------+ | 15 | +----------+ 1 row in set (0.02 sec)
[modifier] Table recentchanges
recentchanges contient les modifications faites dans le dernier mois (sur wp:fr). Par rapport à revision, elle contient le nom de la page.
mysql> describe recentchanges; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | rc_id | int(8) | NO | | 0 | | | rc_timestamp | varchar(14) | NO | | | | | rc_cur_time | varchar(14) | NO | | | | | rc_user | int(10) unsigned | NO | | 0 | | | rc_user_text | varchar(255) | NO | | | | | rc_namespace | int(11) | NO | | 0 | | | rc_title | varchar(255) | NO | | | | | rc_comment | varchar(255) | NO | | | | | rc_minor | tinyint(3) unsigned | NO | | 0 | | | rc_bot | tinyint(3) unsigned | NO | | 0 | | | rc_new | tinyint(3) unsigned | NO | | 0 | | | rc_cur_id | int(10) unsigned | NO | | 0 | | | rc_this_oldid | int(10) unsigned | NO | | 0 | | | rc_last_oldid | int(10) unsigned | NO | | 0 | | | rc_type | tinyint(3) unsigned | NO | | 0 | | | rc_moved_to_ns | tinyint(3) unsigned | NO | | 0 | | | rc_moved_to_title | varchar(255) | NO | | | | | rc_patrolled | tinyint(3) unsigned | NO | | 0 | | | rc_ip | char(0) | NO | | | | +-------------------+---------------------+------+-----+---------+-------+
[modifier] Table archive
archive contient les modifications supprimées.
mysql> describe archive; +---------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-----------------+------+-----+---------+-------+ | ar_namespace | int(11) | NO | | 0 | | | ar_title | varchar(255) | NO | | | | | ar_text | char(0) | NO | | | | | ar_comment | tinyblob | NO | | | | | ar_user | int(5) unsigned | NO | | 0 | | | ar_user_text | varchar(255) | NO | | | | | ar_timestamp | varchar(14) | NO | | | | | ar_minor_edit | tinyint(1) | NO | | 0 | | | ar_flags | tinyblob | NO | | | | | ar_rev_id | bigint(1) | NO | | 0 | | | ar_text_id | bigint(1) | NO | | 0 | | +---------------+-----------------+------+-----+---------+-------+
[modifier] Table user
+-------------------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-----------------+------+-----+---------+-------+ | user_id | int(5) unsigned | NO | | 0 | | | user_name | varchar(255) | NO | | | | | user_registration | varchar(14) | YES | | NULL | | | user_editcount | int(11) | YES | | NULL | | +-------------------+-----------------+------+-----+---------+-------+
[modifier] Table logging
Contient les journaux depuis fin 2004.
mysql> describe logging; +---------------+------------------+------+-----+----------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+----------------+-------+ | log_type | varchar(10) | NO | | | | | log_action | varchar(10) | NO | | | | | log_timestamp | varchar(14) | NO | | 19700101000000 | | | log_user | int(10) unsigned | NO | | 0 | | | log_namespace | int(11) | NO | | 0 | | | log_title | varchar(255) | NO | | | | | log_comment | varchar(255) | NO | | | | | log_params | blob | NO | | | | +---------------+------------------+------+-----+----------------+-------+
[modifier] Tables non mises à jour
[modifier] Table cur
Cette table n'est plus mise à jour.
mysql> describe cur; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | cur_id | int(8) unsigned | NO | | 0 | | | cur_namespace | tinyint(2) unsigned | NO | | 0 | | | cur_title | varchar(255) | NO | | | | | cur_text | mediumtext | NO | | | | | cur_comment | tinyblob | NO | | | | | cur_user | int(5) unsigned | NO | | 0 | | | cur_user_text | varchar(255) | NO | | | | | cur_timestamp | varchar(14) | NO | | | | | cur_restrictions | tinyblob | NO | | | | | cur_counter | bigint(20) unsigned | NO | | 0 | | | cur_is_redirect | tinyint(1) unsigned | NO | | 0 | | | cur_minor_edit | tinyint(1) unsigned | NO | | 0 | | | cur_is_new | tinyint(1) unsigned | NO | | 0 | | | cur_random | double unsigned | NO | | 0 | | | inverse_timestamp | varchar(14) | NO | | | | | cur_touched | varchar(14) | NO | | | | +-------------------+---------------------+------+-----+---------+-------+ 16 rows in set (0.01 sec) mysql> select cur_timestamp from cur -> order by cur_timestamp desc -> limit 10; +----------------+ | cur_timestamp | +----------------+ | 20050702225542 | | 20050702225524 | | 20050702225524 | | 20050702225517 | | 20050702225515 | | 20050702225511 | | 20050702225511 | | 20050702225502 | | 20050702225458 | | 20050702225457 | +----------------+
[modifier] Table text
Cette table n'est plus mise à jour.
mysql> describe text; +-------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------+-------+ | old_id | int(8) unsigned | NO | | 0 | | | old_namespace | tinyint(2) unsigned | NO | | 0 | | | old_title | varchar(255) | NO | | | | | old_text | mediumtext | NO | | | | | old_comment | tinyblob | NO | | | | | old_user | int(5) unsigned | NO | | 0 | | | old_user_text | varchar(255) | NO | | | | | old_timestamp | varchar(14) | NO | | | | | old_minor_edit | tinyint(1) | NO | | 0 | | | old_flags | tinyblob | NO | | | | | inverse_timestamp | varchar(14) | NO | | | | +-------------------+---------------------+------+-----+---------+-------+
[modifier] Salebot
mysql> desc user; +-----------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------------------+---------------------+------+-----+---------+-------+ | name | varchar(255) | NO | PRI | | | | id | int(8) unsigned | YES | | NULL | | | creation_time | int(11) unsigned | YES | | NULL | | | last_action_time | int(11) unsigned | YES | | NULL | | | last_action_time_str | varchar(30) | YES | | NULL | | | last_page | varchar(255) | YES | | NULL | | | action_count | int(8) unsigned | YES | | NULL | | | edit_count | int(8) unsigned | YES | | NULL | | | new_page_count | int(8) unsigned | YES | | NULL | | | vandalism_count | int(8) unsigned | YES | | NULL | | | reverted_by_human_count | int(8) unsigned | YES | | NULL | | | warn_edit_count | int(8) unsigned | YES | | NULL | | | spam_count | int(8) unsigned | YES | | NULL | | | rollback_made_count | int(8) unsigned | YES | | NULL | | | bot_revert_count | int(8) unsigned | YES | | NULL | | | bot_impossible_revert_count | int(8) unsigned | YES | | NULL | | | ignore_user | tinyint(1) unsigned | YES | | NULL | | | stop_edits | tinyint(1) unsigned | YES | | NULL | | | is_proxy | tinyint(1) unsigned | YES | | NULL | | | fqdn | varchar(255) | YES | | NULL | | +-----------------------------+---------------------+------+-----+---------+-------+