Zapytania SQL (MySQL) na szybko
Zapytania proste
Aktualizuj wiersz (update record)
UPDATE `tab` SET `col1`='1', `col2`='2' WHERE `id`='5';
http://dev.mysql.com/doc/refman/5.0/en/update.html
MYSQL dodanie kilku rekordów (MYSQL multiple insert)
INSERT INTO `mytab` VALUES (1, 'a'), (2, 'b'), (3, 'c');
result table:
+----+--------+
| id | letter |
+----+--------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+--------+
Usuń rekord (delete record)
DELETE FROM `myTab` WHERE id=5;
Zlicz rekordy (count records)
//all records
SELECT COUNT(*) FROM `myTab`;
//all where name = marek
SELECT COUNT(*) FROM `myTab` WHERE `name`='marek';
WHERE: pole przyjmuje jedną z wartości (WHERE: field's value is equal to one of values)
SELECT `id`, `name` FROM `tab` WHERE id in (1,20,23,45,60);
WHERE: pole przyjmuje jedną z wartości + podzapytanie (WHERE: field's value is equal to one of values + subquery)
Te zespoły nie zaśpiewają w Opolu:
SELECT `nazwa` FROM `propozycje_do_opola`
WHERE `nazwa` in (
SELECT `nazwa_grupy` FROM `lista_grup_satanistycznych_men`
);
Modyfikacje tabel
Zmień nazwę tabeli (rename table)
RENAME TABLE `tab_name` TO `new_tab_name`;
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html
Dodaj kolumnę (add column)
ALTER TABLE `tab` ADD `col` int(11);
//dodaj po kolumnie `col0`
ALTER TABLE `tab` ADD `col` int(11) AFTER `col0`;
//dodaj na początku
ALTER TABLE `tab` ADD `col` int(11) FIRST;
Zmień nazwę kolumny (change column name)
ALTER TABLE `tab` CHANGE `old_name` `new_name` int(11);
Pobierz zapytanie do stworzenia tabeli (Get query for creating table - table create)
show create table `table_name`;
Skasuj tabelę (delete table)
DROP TABLE `table_name`;
Skasuj kilka tabeli (delete multiple tables)
DROP TABLE `table_1_name`, `table_2_name`, `table_3_name`;
Zmień/zresetuj AUTO_INCREMENT (modify/change AUTO_INCREMENT)
ALTER TABLE `tabname` AUTO_INCREMENT=20;
ALTER TABLE `tabname` AUTO_INCREMENT=1;
Baza danych
lista baz danych (list databases)
SHOW DATABASES;
Przełącz do bazy (switch to db)
USE `db_name`;
Jaka wersja bazy mysql (mysql - which version)
SELECT VERSION();
Pokaż tabele w bazie (show tables in db)
SHOW TABLES;
Pokaż szczegółowe informacje o tabelach (Show details about tables in db)
SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE 'tabname';
Pokaż kolumny tabeli (Show table columns)
DESCRIBE `tab_name`;
Zmienne środowiskowe (Environmental variables)
SHOW VARIABLES;
SHOW VARIABLES LIKE "char%";
--mysql5 charset/encoding variables
character_set_client
character_set_connection
character_set_database
character_set_filesystem
character_set_results
character_set_server
character_set_system
Zapytania bardziej złożone
Duplikowanie rekordu/ów (Duplicate row/s)
Pierwsze zapytanie duplikuje rekord, drugie duplikuje wszystkie rkordy o col1 równym 90 i zmienia w nich wartość col1 na 100.
//duplicate record
INSERT INTO `myTab` (SELECT * FROM `myTab` WHERE `id`=3);
//duplicate records having col1 equal to 90
//and change in inserted rows col1 to 100
INSERT INTO `myTab` (`col1`, `col2`, `col3`)
(
SELECT ‘100’, `col2`, `col3` FROM `myTab` WHERE `col1`=90
);
Połącz tabelę z samą sobą (join table to itself)
SELECT tab1.user, tab2.user FROM `users` AS tab1
JOIN `users` AS tab2
ON tab1.id=tab2.parent_id;
+----+--------+-----------+
| id | user | parent_id |
|----+--------+-----------+
| 1 | admin | 1 |
| 2 | foo | 1 |
| 3 | bar | 1 |
| 4 | foobar | 1 |
| 5 | root | 5 |
| 6 | noroot | 5 |
| 7 | looser | 5 |
+----+--------+-----------+
result:
+-------+--------+
| user | user |
+-------+--------+
| admin | admin |
| admin | foo |
| admin | bar |
| admin | foobar |
| root | root |
| root | noroot |
| root | looser |
+-------+--------+';
Złączenie dwóch tabel używając LIKE (joining 2 tables using LIKE)
znajdź rekordy o określonych id gdzie t2.full_str zaczyna się od t1.part_str
SELECT t1.id, t2.id, t1.part_str, t2.full_str
from t1
join t2
on t2.full_str like concat(t1.part_str, '%')
where t1.id=10 and t2.id=20;
result:
+---------------------------------------------+
| t1.id | t2.id | t1.part_str | t2.full_str |
+---------------------------------------------+
| 10 | 20 | foo | foobar string |
+---------------------------------------------+
Wyświetl Unix timestamp w postaci daty (Select Unix timestamp as date)
Funkcje daty i czasu - manual mySQL
SELECT FROM_UNIXTIME(`timestamp_column`) FROM `table`;
SELECT FROM_UNIXTIME(1);
// result
+---------------------+
| 1970-01-01 01:00:01 |
+---------------------+
Oblicz różnicę czasu (time difference)
Zwraca różnicę pomiędzy datami
//for mysql 4.1.1 and above
SELECT TIMEDIFF('2007-10-05 07:38:19', '2007-10-05 06:55:27') AS `TDiff`
//for mysql below 4.1.1
SELECT SEC_TO_TIME((UNIX_TIMESTAMP('2007-10-05 07:38:19') - UNIX_TIMESTAMP('2007-10-05 06:55:27'))) AS `TDiff`;
+--------------+
| TDiff |
+--------------+
| 00:42:52 |
+--------------+
Szereguj wg. ilości znaków (order by string length)
SELECT * FROM `myTab` ORDER BY CHAR_LENGTH(`string_col`)
Funkcja IF() (IF() function)
SELECT IF(CONDITION, IF_TRUE, IF_FALSE);
//Example:
SELECT `id`, IF(`id`>2, 'ID IS BIGGER THAN 2', 'ID IS NOT BIGGER THAN 2') AS `COMPARE` FROM myTab
//Table:
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
//Result:
+----+-------------------------+
| id | COMPARE |
+----+-------------------------+
| 1 | ID IS NOT BIGGER THAN 2 |
| 2 | ID IS NOT BIGGER THAN 2 |
| 3 | ID IS BIGGER THAN 2 |
| 4 | ID IS BIGGER THAN 2 |
+----+-------------------------+
Manual mySQL dotyczący funkcji do kotroli przepływu danych.
Szereguj wg. wartości najbliższej do podanej (Order by value closest to given)
Użyj ABS().
+------+
| sec |
+------+
| 12 |
| 15 |
| 9 |
| 7 |
| 11 |
+------+
//find time closest to 10 seconds
SELECT
ABS(`sec`-10) as `ord`
, `sec`
, `sec`-10 as `diff`
FROM `times` ORDER BY `ord`;
//result
+-------------------+
| ord | sec | diff |
+-------------------+
| 1 | 9 | -1 |
| 1 | 11 | 1 |
| 2 | 12 | 2 |
| 3 | 7 | -3 |
| 5 | 15 | 5 |
+-------------------+
Losowy rekord (random record)
SELECT * FROM `tabname` ORDER BY RAND() LIMIT 1;
Liczba losowa (random number)
//0-9
SELECT floor(rand() * 10);
Losowy porządek (random order)
SELECT * FROM `my_tab` ORDER BY random();
Formatuj miejsca dziesiętne (format decimal places)
SELECT truncate(102.3, 4);
//102.3000
SELECT truncate(10.8999, 2);
//10.89
SELECT truncate(999, -1);
//990
SELECT truncate(999, -2);
//900
SELECT round(102.3, 4);
//102.3000
SELECT round(10.8999, 2);
//10.90
SELECT round(989, -1);
//990
SELECT round(989, -2);
//1000
Pokaż wyniki z dwóch tabel używając jednego zapytania (show results from two tables using one query)
UNION eliminuje duplikaty (jak DISTINCT). Aby pokazać wszystko użyj UNION ALL.
//tab1
+-------+
| name |
+-------+
| Jan |
| Maria |
| Piotr |
+-------+
//tab2
+---------+
| surname |
+---------+
| Maria |
| Rokita |
| Piotr |
+---------+
SELECT `name` ftom `tab1`
UNION
SELECT `surname` FROM `tab2`;
//result
+--------+
| name |
+--------+
| Jan |
| Maria |
| Piotr |
| Rokita |
+--------+
SELECT `name` ftom `tab1`
UNION ALL
SELECT `surname` FROM `tab2`;
//result
+--------+
| name |
+--------+
| Jan |
| Maria |
| Piotr |
| Maria |
| Rokita |
| Piotr |
+--------+
WHERE [NOT] EXISTS - resultaty zależne od od innej tabeli (WHERE [NOT] EXISTS - results depend on other table)
Zapytanie dzieki użyciu WHERE [NOT] EXISTS zwraca rekordy zależnie do tego czy podzapytanie zwraca rekordy czy nie.
//tab1
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
//tab2
+-------------+
| id | letter |
+-------------+
| 1 | a |
| 1 | c |
| 2 | b |
| 2 | a |
+-------------+
Wybierz tylko `id` z `tab1` NIE posiadające odpowiedników (id) w tab2 lub odpowiedników NIE POSIADAJĄCYCH w ŻADNYM rekordzie w kolumnie `letter` litery 'b'.
SELECT `id` FROM `tab1` WHERE NOT EXISTS(
SELECT * FROM `tab2` WHERE `tab1`.`id` = `tab2`.`id` AND `tab2`.`letter`='b'
);
//result
+----+
| id |
+----+
| 1 |
| 3 |
+----+
Wybierz tylko `id` z `tab1` posiadające odpowiedniki (id) w tab2 i dodatkowo odpowiednik posiada w dowolnym rekordzie w kolumnie `letter` literę 'b'.
SELECT `id` FROM `tab1` WHERE EXISTS(
SELECT * FROM `tab2` WHERE `tab1`.`id` = `tab2`.`id` AND `tab2`.`letter`='b'
);
//result
+----+
| id |
+----+
| 2 |
+----+
Wybierz tylko `id` z `tab1` posiadające odpowiedniki (id) w tab2 i dodatkowo odpowiednik posiada w dowolnym rekordzie w kolumnie `letter` literę 'a'.
SELECT `id` FROM `tab1` WHERE EXISTS(
SELECT * FROM `tab2` WHERE `tab1`.`id` = `tab2`.`id` AND `tab2`.`letter`='a'
);
//result
+----+
| id |
+----+
| 1 |
| 2 |
+----+
Wybierz i usuń duplikaty z tabeli(Select and delete duplicates from table)
Aby wybrać zduplikowane rekordy z tabeli:
To select duplicated records from table:
-- table people
id | name
---+------
1 | jan
2 | jan
3 | pol
4 | jan
5 | pol
6 | anna
---+------
-- select all old records that have duplicates
-- (old = with lower id)
SELECT id FROM people WHERE EXISTS(
SELECT * FROM people AS tmp WHERE tmp.id > people.id AND tmp.name = people.name
);
-- result
id
---
1
2
3
---
Aby skasować zduplikowane rekordy z tabeli:
To delete duplicated records from table:
DELETE FROM PEOPLE WHERE id IN (
SELECT id FROM(
SELECT p.id FROM people AS p WHERE EXISTS(
SELECT * FROM people AS tmp WHERE tmp.id > p.id AND tmp.name = p.name
)
) AS x
);
The key is SELECT id FROM(...) AS x subquery. You will get SQL Error: You can't specify target table 'TABNAME' for update in FROM clause error otherwise!
XHTML 1.0
Wyślij mi maila / kontakt