jesteś w: Główna > Inne > Zapytania sql
Ostatnia aktualizacja tej strony: 2009-06-12, 21:06:24

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 |
+-------------------+

Mysql math functions.

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!

Menu

Działy:

Quick start:

Inne:

animacje Flash

Diablo 2: