code-laboratory.com jest blogiem poświęconym w całości programowaniu oraz technologiom internetowym. Znajdziesz tutaj kod, gotowy do wykorzystania w twoich projektach.

MySQL

Mysql – wgrywanie dużej bazy
mysql -u root -p –default-character-set utf8 databasename < plik.sql

Backup
mysqldump –all-databases -u username -ppassword > backup.sql
mysqldump -u username -ppassword database_name > database_name.sql

Sortowanie po adresie IP
SELECT *
FROM `ip_table`
ORDER BY
CAST(SUBSTRING_INDEX(ip, „.”, 1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip,”.”,2),”.”,-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip,”.”,3),”.”,-1) AS UNSIGNED),
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(ip,”.”,4),”.”,-1) AS UNSIGNED);

Usuwanie duplikatów (id, email)
DELETE emails
FROM emails
LEFT OUTER JOIN (
SELECT MIN(id) as id, email
FROM emails
GROUP BY email
) as KeepRows ON
emails.id = KeepRows.id
WHERE
KeepRows.id IS NULL

Ostatni rekord z one-to-many
SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND
(p1.date < p2.date OR p1.date = p2.date AND p1.id < p2.id))
WHERE p2.id IS NULL;