Benutzer:Kolossos/SQL-Oberstübchen

aus Wikipedia, der freien Enzyklopädie

• Aufräumen:

DELETE FROM `pub_CSV-test` WHERE `lat` = 0 AND `lon` = 0  LIMIT 10000 
DELETE FROM `pub_CSV-test` WHERE `title` like 'List of %' LIMIT 10000 

• Einspielen

LOAD DATA LOCAL INFILE '/home/sk/data/geo/de/coordinates_de_split.txt' IGNORE INTO TABLE `pub_CSV_test4` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(`t` ,`lang` , `Titel` , `t` , `t` , `t` , `t` , `t` , `t` , `t` , `lat` , `t` , `t` , `t` , `t` ,`lon`,`t` , `t` , `t` , `t`,`t` ,`type`,`pop`,`t`,`style`,`t`,`Country`,`Subregion`,`t`,`t`,`t`,`t`,`t`,`t`,`t`,`image`,`psize`,`Category`, `Titel_en`,`Titel_de`,`Titel_fr`,`Titel_pl`,`Titel_ja`,`Titel_nl`,`Titel_it`,`Titel_pt`,`Titel_sv`,`Titel_es`,`Titel_ru` )

de+en einspielen Primärschlüssel (de+en) löschen andere Sprachen einspielen

• Löschen was es in de oder en gibt

DELETE FROM `pub_CSV-test` WHERE `lang` LIKE CONVERT(_utf8 'ru' USING latin1) COLLATE latin1_swedish_ci AND (`Titel_en` <> CONVERT(_utf8 USING latin1) COLLATE latin1_swedish_ci OR `Titel_de` <> CONVERT(_utf8 USING latin1) COLLATE latin1_swedish_ci)

• Statistik

SELECT lang, COUNT(*) FROM `pub_CSV-test` GROUP BY lang SELECT style , COUNT(*)FROM `pub_CSV_test3` GROUP BY style ORDER BY `COUNT( * )` DESC

• Duplikate entfernen

delete test from test,
(select count(*) as counter,
 min(page) as page,
 test2
 from test
 group by test2
 having counter > 1) as doppel
 where test.test2 = doppel.test2 and
 test.page != doppel.page

• Komplettes laden des Errorfiles

LOAD DATA LOCAL INFILE '/home/sk/data/geo/de/coordinates_de_sort.txt' IGNORE INTO TABLE `pub_CSV_error1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'( `t` ,`lang` , `Titel` , `error_nr` , `message` , `coor_bad` , `coor_nr` , `templates` , `t` , `t` ,`lat`,`t` , `t` , `t` , `t` ,`lon`,`t` , `t` , `t` , `t`,`t` ,`type`,`pop`,`t`,`style`,`t`,`Country`,`Subregion`,`t`,`t`,`t`,`t`,`t`,`t`,`t`,`image`,`psize`,`Category`,`Titel_en`,`Titel_de`,`Titel_fr`,`Titel_pl`,`Titel_ja`,`Titel_nl`,`Titel_it`,`Titel_pt`,`Titel_sv`,`Titel_es`,`Titel_ru` )

• Map1

SELECT ROUND(`Lon`,0),ROUND(`Lat`,0),  COUNT(*)  FROM `geo_de_old` GROUP BY ROUND(`Lon`,0),ROUND(`Lat`,0)

• Maybe

LOAD DATA LOCAL INFILE '/home/sk/data/geo/cs/coordinates_cs_maybe.txt' IGNORE INTO TABLE `pub_maybe` FIELDS TERMINATED BY '\t'    
OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'(
`lang` , `title` , `t` , `t` ,`t` , `t` ,  `t` , `t` ,`Cat`) 

• Kopieren eines DB-Ausschnittes:

INSERT INTO `u_kolossos`.`pub_de-wiki`
SELECT `page_title`,`page_len`
FROM `dewiki_p`.`page` WHERE `page_namespace` =0

• ALTER TABLE `pub_CSV_test3_si`

  CHARACTER SET 'utf8'

• Maybe-Checker

Button Statistik 27.01.07 2 380 3 2113 4 427

SELECT ready, COUNT(*) FROM `pub_maybe` GROUP BY ready

• Dump über die Konsole

mysqldump u_kolossos pub_CSV_test3  > pub_CSV_test3.sql

• Tabellen abgleichen mit Unterabfragen

SELECT * FROM `pub_maybe` WHERE title NOT IN ( SELECT Titel FROM `pub_CSV_test3`) AND `ready` =3 LIMIT 0 , 30

[Bearbeiten] Templatetiger

SELECT count( * ) , `tp_name` FROM `pub_tt1` GROUP BY `tp_name` ORDER BY count( * ) DESC LIMIT 0 , 30

• Einspielen

LOAD DATA LOCAL INFILE '/home/sk/data/geo/de/coordinates_de_templates.txt' IGNORE INTO TABLE `pub_tt1` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'