Archiwa tagu: sql

Złączenia w SQL

1 odpowiedź

Dziś podręczna, graficzna ściągawka obejmująca złączenia (join: left join, inner join, right join, full outer join) w SQL Definicje podaję ze sporym uproszczeniem, bez zagłębiania się w teorię baz danych – jest to szybka ściąga przypominająca podstawowe wiadomości.

Złączenia

Złączenia (join) używane są do łączenia kilku tabel zawierających różne kolumny, lecz posiadających jedną wspólną kolumnę (klucz). Przykład – złączenie tabeli z zamówieniami z tabelą danych osobowych klientów.

Zakładając, że:

  • zapytanie nr 1 (Q1) zwraca następujący zestaw danych: A, B, C
  • zapytanie nr 2 (Q2) zwraca następujący zestaw danych: C, D, E

Pełne zapytanie będzie następujące:


SELECT * FROM Q1 LEFT JOIN Q2 USING (key);

SELECT * FROM Q1 LEFT JOIN Q2 ON (Q1.key1 = Q2.key2);

Ilustracja:

Bazy danych - złączenia

Left join

SELECT * FROM Q1 LEFT JOIN Q2 USING (key)

Inner join

SELECT * FROM Q1 INNER JOIN Q2 USING (key)

Right join

SELECT * FROM Q1 RIGHT JOIN Q2 USING (key)

Full outer join

SELECT * FROM Q1 FULL OUTER JOIN Q2 USING (key)

Innym tematem dotyczącym SQL są operacje na zbiorach. Więcej na ten temat tu: Bazy danych -ściągawka.

MySQL – dump tylko określonych wierszy

Dodaj komentarz

MySQL umożliwia wykonanie dumpa tylko określonych danych. Dzięki temu można np. podzielić dumpa jednej, dużej tabeli na parę mniejszych części. Służy temu przełącznik --where.

Przykład 1 – wykonujemy dump tabeli myVeryBigTable, lecz tylko takich wierszy, które mają id 1, 2 lub 3:

mysqldump myDatabaseName --host 127.0.0.1 --port=3306 
--user=myUserName --password
myVeryBigTable
--where="id in (1,2,3)"
> .\mySimpleDump.sql

Przykład 2 – możliwe jest także wykonanie nieco bardziej skomplikowanego zapytania:

mysqldump myDatabaseName --host 127.0.0.1 --port=3306
--user=myUserName --password
myVeryBigTable
--where="id in (select id from myOtherTable where objectId in (123,558,6987))"
> .\myDump.sql

Bazy danych – ściągawka

1 odpowiedź

Dziś podręczna, graficzna ściągawka obejmująca operacje na zbiorach (union, union all, except, intersect). Definicje podaję ze sporym uproszczeniem, bez zagłębiania się w teorię bazy danych – jest to szybka ściąga przypominająca podstawowe wiadomości. Nie wszystkie z prezentowanych tutaj zapytań mają taką samą składnię w każdej bazie danych. Zależy to od implementacji.

Operacje na zbiorach

Operacje na zbiorach (typu unia, przecięcie, wykluczenie) używane są na dwóch zbiorach zawierających te same kolumny (typ danych, ilość kolumn musi być taka sama). Przykład: różnica dwóch tabel o tych samych kolumnach, lecz różnych danych wewnątrz (pierwsza tabela może zawierać dane nieprzefiltrowane, druga przefiltrowane itd.).

bazy danych - operacje na zbiorach

Zakładając, że:

  • zapytanie nr 1 (Q1) zwraca następujący zestaw danych: A, B, C,
  • zapytanie nr 2 (Q2) zwraca następujący zestaw danych: C, D, E,

z wyników zapytania można złożyć: Czytaj dalej →

Jak odzyskać hasła w HeidiSQL

Dodaj komentarz

HeidiSQL to całkiem przyjemny program, umożliwiający przeglądanie i zarządzanie bazami danych MySQL, Microsoft SQL, PostgreSQL.

Jak odzyskać hasła do baz danych, zapisane w HeidiSQL? Okazuje się, że (niestety?) bardzo prosto. Wystarczy:

  1. Przejść do rejestru: HKEY_CURRENT_USER\Software\HeidiSQL\Servers\nazwa_zapisanego_w_heidi_serwera
  2. Znaleźć klucz Password (typ: REG_SZ)
  3. Skopiować wartość klucza
  4. Wpisać wartość w poniższy skrypt i uruchomić go w przeglądarce:

Czytaj dalej →

Dane (flagi) binarne w MySQL

Dodaj komentarz

Czasami zachodzi potrzeba przechowywania w bazie danych binarnych. Nie mam tu na myśli pól typu BLOB, w których możemy przechowywać np. obrazki lub inne pliki, lecz dość staromodne rozwiązanie, jakim są flagi bitowe. Można stwierdzić, że praktyka ta zaczerpnięta została z języków niskiego poziomu kontaktujących się z elektroniką, gdzie w ten sposób można np. sterować wyjściami urządzeń itp.

Przykład 1: urządzenie posiada 7 diod LED. W bazie danych chcemy przechować informację, które diody mają się świecić.

Przykład 2: mamy kilka założonych z góry funkcji w aplikacji, których nie będziemy rozbudowywać o kolejne elementy: dostęp administracyjny, dostęp tylko do czytania, dostęp do czytania + edycji itp. Chcemy ustawić użytkownikowi uprawnienia do poszczególnych funkcji.

Flagi bitowe pozwalają na przechowywanie różnych informacji w prostej formie.

Wystarczy kolumna w tabeli przechowująca zwykłe dane liczbowe.

Do manipulacji danymi można posłużyć się zapytaniami:

Wstawianie danych:

INSERT INTO table_name(column_name) VALUES( B'1110000');

Wybieranie danych:

SELECT CONV(column_name, 10, 2) FROM table_name;

Funkcja CONV konwertuje dane z liczby o podstawie x do liczby o podstawie y. Parametry:

SELECT CONV(column_name, podstawa z jakiej konwertować, podstawa do jakiej konwertujemy)

Wyszukiwanie danych:

SELECT column_name FROM table_name WHERE column_name & B('1110000');

Sqlite i skrypty batch

Dodaj komentarz

Dostęp do SQLite jest możliwy nie tylko z poziomu aplikacji. Dzięki kilku sztuczkom, można w łatwy sposób wykonywać operacje na tej świetnej bazie danych z poziomu zwykłych skryptów batch w Windowsie. Więcej na ten temat można przeczytać w poniższym wpisie:
http://bigcode.wordpress.com/2010/12/12/accessing-sqlite-from-a-dos-batch-file/

Na tej bazie stworzyłam sobie bardzo prostą aplikację logującą w bazie danych mój czas pracy. Aplikacja (w zasadzie to zestaw skryptów) jest wykonana głównie w celach poglądowych, aczkolwiek gdyby dopracować ją, mogłaby stanowić „niskobudżetowy” licznik czasu pracy.

Przygotowałam sobie następujący schemat danych:

DROP TABLE IF EXISTS workEventsDict;
DROP TABLE IF EXISTS workTime;
DROP TABLE IF EXISTS daySummary;
DROP INDEX IF EXISTS eventDescrIndex;

CREATE TABLE workEventsDict (
  id INTEGER PRIMARY KEY,
  symbol TEXT,-- short symbol: START itp.
  eventDescr TEXT
);

CREATE INDEX eventDescrIndex ON workEventsDict (eventDescr);

CREATE TABLE workTime (
  id INTEGER PRIMARY KEY,
  eventDate DATE,
  eventTime TIME,
  eventDescr REFERENCES workEvents(eventDescr)
    ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE daySummary (
  id INTEGER PRIMARY KEY,
  day DATE,
  workTimeSummary TIME,
  overTime TIME,
  note TEXT-- holiday or other notes
);

INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (1, "START", "system started");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (2, "STOP", "system stopped");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (3, "LOCK", "profile locked");
INSERT INTO workEventsDict (id, symbol, eventDescr)
  VALUES (4, "UNLOCK", "profile unlocked");

W dalszej części wpisu prezentuję dalsze kody źródłowe.
Czytaj dalej →

Instalacja PostGIS do PostgreSQL

Dodaj komentarz

Dziś instrukcja instalacji rozszerzenia PostGis dla PostgreSQL, umożliwiającego obsługę danych przestrzennych.

Pierwszym krokiem jest instalacja PostgreSQL – ja wybieram wersję 9.2.

Następnie dodajemy dodatek PostGis:

  1. Przejście do katalogu: X:\Program Files\PostgreSQL\9.2\bin
  2. Uruchomienie StackBuilder.exe – program do ściągania aktualizacji i dodatkowych funkcjonalności do serwera PostgreSQL
  3. Wybieramy z wywijanego pola nasz serwer.
  4. W polu Spatial Extensions wybieramy PostGIS
  5. Czekamy, aż ściągnie się PostGis.

Jak wyświetlić listę wszystkich tabel w bazie danych PostgreSQL

7 komentarzy

Pracując z konsolowym klientem bazy danych PostgreSQL często przydatne jest wyświetlenie listy obecnych w bazie tabel. Niestety nie ma tu czegoś na kształt zwykłego SELECT * FROM BazaDanych, stosowanego do tabeli, ale są inne możliwości.

Spójrzmy na kody poniżej.

Najprostsza opcja to:
\d

Nieco bardziej rozbudowana, z możliwością wyświetlania określonych typow danych:

SELECT * FROM information_schema.tables 
WHERE table_schema='public' 
AND table_type='BASE TABLE';

Kod wyświetla listę tabeli – pobiera ją z pliku, który można znaleźć mniej więcej pod ścieżką: pgsql/src/bin/psql/describe.c.

Inną opcją jest też uruchomienie psql \d, psql -E – zwłaszcza pod Linuxem.