Gebruiker:Annabel/queries/2

Nederlandse woorden waar de lettergrepen aan toegevoegd kunnen worden
mysql -hsql-s3 --skip-column-names u_annabel > /home/annabel/public_html/sjabs.txt <<EOF
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);
DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT nlwiktionary_p.page.page_title,nlwiktionary_p.page.page_id
FROM nlwiktionary_p.page, nlwiktionary_p.templatelinks AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-syll-'
) AS Tabelleke2
ON (Tabelleke1.tl_from = Tabelleke2.tl_from)
WHERE Tabelleke1.tl_title = '=nld='
AND Tabelleke2.tl_from IS NULL
AND nlwiktionary_p.page.page_id=Tabelleke1.tl_from
AND nlwiktionary_p.page.page_namespace = 0;

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp2
SELECT page_title,page_id
FROM u_annabel.temp AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = 'oudeschrijfwijze'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT page_title,page_id
FROM u_annabel.temp2 AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-nlphrase-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp2
SELECT page_title,page_id
FROM u_annabel.temp AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-phrase-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT page_title,page_id
FROM u_annabel.temp2 AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-nlabbr-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp2
SELECT page_title,page_id
FROM u_annabel.temp AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-suff-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT page_title,page_id
FROM u_annabel.temp2 AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-abbr-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp2
SELECT page_title,page_id
FROM u_annabel.temp AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-prov-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT page_title,page_id
FROM u_annabel.temp2 AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-pref-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

mysql -hsql-s3 --skip-column-names u_annabel > /home/annabel/public_html/sjabs.txt <<EOF
DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp2
SELECT page_title,page_id
FROM u_annabel.temp AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-nlcircumf-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;

DROP TABLE IF EXISTS temp;
CREATE TABLE temp (page_title VARCHAR(255),page_id INT(8) UNSIGNED);

INSERT IGNORE INTO /* SLOW_OK */ u_annabel.temp
SELECT page_title,page_id
FROM u_annabel.temp2 AS Tabelleke1
LEFT JOIN (
SELECT *
FROM nlwiktionary_p.templatelinks
WHERE nlwiktionary_p.templatelinks.tl_title = '-circumf-'
) AS Tabelleke2
ON (Tabelleke1.page_id = Tabelleke2.tl_from)
WHERE Tabelleke2.tl_from IS NULL;


SELECT CONCAT('# [[', u_annabel.temp.page_title, ']]')
FROM u_annabel.temp