Miesięczne archiwum: Październik 2017

Baza Biblioteka 2. Pokaż 10 najczęściej wypożyczających czytelników.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 2

Treść:  Pokaż 10 najczęściej wypożyczających czytelników.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     C.login
    ,COUNT(*) as [Liczba wypozyczen]

from
    Wypozyczenia W
    join Czytelnicy C on W.id_czytelnik = C.id_czytelnik
    
group by
    C.login    

order by
    COUNT(*) desc   

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


 

Baza Biblioteka 1. Pokaż 10 najczęściej wypożyczanych książek.

SQL tutorial. Baza Biblioteka ćwiczenia. Środowisko SQL SERVER.

Baza: Biblioteka

Zadanie nr: 1

Treść:  Pokaż 10 najczęściej wypożyczanych książek.

Polecenia/funkcje w zapytaniu: SELECT TOP, COUNT(), JOIN, GROUP BY, ORDER BY


SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka


Rozwiązanie:

Zapytanie:

select top 10
     K.tytul
    ,COUNT(*) as [Ilosc wypozyczen]

from
    wypozyczenia W
    join Ksiazki K on W.id_ksiazka = K.id_ksiazka
    
group by
     K.tytul
    ,W.id_ksiazka
    
order by
    COUNT(*) desc

Wynik:

SQL tutorial.  SQL SERVER. Baza Biblioteka

Pobierz skrypt sql


 SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka SQL tutorial. SQL SERVER. Baza Biblioteka

PostgreSQL funkcja OVERLAY() – zamiana ciągów znaków

Ciekawą funkcją w PostgreSQL jest funkcja OVERLAY() dzięki której możemy podmienić jeden ciąg znaków na drugi.
Zwracany typ: TEXT

PostgreSQL OVERLAY() składnia

OVERLAY([łańcuch_główny] placing [łańcuch_do_podmiany] from [pozycja_od_której_zaczynamy_nadpisywanie] for [ile_znaków_nadpisujemy])

Przykład zastosowania funkcji OVERLAY()
Zamień w zdaniu 'Ala ma kota.' słowo 'kota' na 'psa'.

SELECT
    OVERLAY('Ala ma kota.' placing 'psa' from 8 for 4)

Wynik

Ala ma psa.

Zwóć uwagę, że zastąpiliśmy ciąg znaków składający się z czterech znaków, ciągiem znaków składającym się z trzech znaków. Funkcja działa jakby dwu etapowo. Pierwszy etap to wycięcie z łańcucha głównego wycinka który chcemy zastąpić, a drugi etap to wstawienie w to miejsce ciągu który chcemy do łańcucha głównego wstawić.

W pierwszym przykładzie w miejsce dłuższego wycinka wstawiliśmy krótszy ciąg. Spróbujmy teraz odwrotnie. Zamieńmy teraz słowo 'psa' na 'kota'.

SELECT
    OVERLAY('Ala ma psa.' placing 'kota' from 8 for 3)

Wynik

Ala ma kota.

Myślę, że już wszystko jasne.


postgresqlpostgresql

PostgreSQL funkcja TRIM (leading, trailing, both) – wycinamy znaki z łańcucha głównego

Kolejną przydatną funkcją dotyczącą łańcuchów znaków jest funkcja TRIM(). Dzięki tej funkcji możemy wycinać zdefiniowane znaki lub ciągi znaków z przodu, z tyłu lub z obu stron naraz z głównego łańcucha znaków. Pamiętaj przy funkcji TRIM() wielkość znaków ma znaczenie.
Zwracany typ: TEXT

PostgreSQL TRIM() składnia

TRIM( [słowo_kluczowe] [znaki] from łańcuch_główny )

słowo kluczowe – tutaj używamy słów kluczowych leading – oznacza cięcie z przodu ciągu, trailing – oznacza cięcie od tyłu ciągu, both – oznacza cięcie z obu stron naraz.
znaki – tutaj określamy ciąg znaków lub pojedyncze znaki do ucięcia.
łańcuch_główny – tutaj określamy łańcuch główny z którego będziemy ucinać znaki.


Przykłady zastosowania funkcji TRIM()

SELECT
    TRIM(leading 'ala' from 'Ala ma kota.')

Wynik

Ala ma kota

Wykorzystaliśmy słówko leading więc funkcja TRIM powinna wyciąć z łańcucha głównego z początku zdania wyraz 'ala'. Dlaczego tego nie zrobił? Jak wcześniej napisałem przy funkcji TRIM wielkość liter ma znaczenie. Zwróć uwagę, że 'ala' jako ciąg znaków do wycięcia  jest napisane od małej litery 'a' a 'Ala' w zdaniu głównym jest napisane od dużej litery 'A'.

No to teraz zróbmy to jak należy.

SELECT
    TRIM(leading 'Ala' from 'Ala ma kota.')

Wynik

 ma kota

Zwróć uwagę, że w wyniku ' ma kota' przed słówkiem 'ma' jest spacja. Pamiętaj, spacja to też znak, a kazaliśmy funkcji TRIM wyciąć tylko 'Ala'.

Teraz wytnijmy coś od tyłu ciągu

SELECT
    TRIM(trailing 'a.t' from 'Ala ma kota.')

Wynik

Ala ma ko

Tutaj mamy ciekawy przykład. Widzimy w wyniku, że ucieliśmy ciąg 'ta.' a w paramtrach określiliśmy ciąg 'a.t'. Jak już pisałm wcześniej kompilator sprawdza czy znaki na końcu łańcucha głównego odpowiadają znakom ze zdefiniowanego przez nas ciągu niezależnie od ich pozycji w ciągu.

Teraz utnijmy coś z obu stron.

SELECT
    TRIM(both 'atA' from 'Ala ma kota.')

Wynik

la ma kota.

Dlaczego z łańcucha głównego zostało wycięte tylko 'A' z początku ciągu? Bo w zdefiniowanych przez nas znakach znalazł się znak 'A' (nieważne na którym miejscu) a na końcu łańcucha głównego jest znak '.' którego nie było w zdefiniowanych przez nas znakach. Zasada jest taka że ucinanie znaków zaczyna się od wskazanego kierunku znak po znaku. Jeżeli w naszym ciągu znaków na początku lub na końcu będzie znak który nie znajduje się w zdefiniowanych przez nas znakach to ucinanie zostanie przerwane, nawet jeżeli zaraz za tym pierwszym znakiem są znaki które są zdefiniowane w naszym ciągu do ucięcia.

Jeżeli wyświetlamy lub chcemy obrabiać jakieś ciągi znaków i nie jesteśmy pewni czy dane nie zawierają zbędnych spacji na początku lub końcu łańcucha znaków (np. czy użytkownicy wprowadzający dane do bazy nie wstawili przez przypadek zbędnych spacji) to funkcja TRIM() nadaje się doskonale do tego zadania.

W naszym przykładzie użyjemy zdania 'Ala ma kota.' ale na początku i na końcu zdania wstawimy po dwie spacje. Wtedy nasz ciąg przybieże postać '  Ala ma kota.  '. Teraz funkcją TRIM() usuniemy zbędne znaki z początku i końca ciągu. Między słówkiem both a from nie wstawiamy żadnych znaków, bo chcemy żeby funkcja usunęła tylko zbędne spacje.

SELECT
    TRIM(both from '  Ala ma kota.  ')

Wynik

Ala ma kota.

W wyniku otrzymujemy zdanie 'Ala ma kota.' bez spacji na początku i na końcu zdania.

Funkcja TRIM() na jeszcze jedną składnię. Możemy napisać tak jak na początku

TRIM( [słowo_kluczowe] [znaki] FROM łańcuch_główny )

lub

TRIM( [słowo_kluczowe] FROM łańcuch_główny [znaki] )

efekt końcowy będzie identyczny


postgresqlpostgresql

PostgreSQL EXISTS – sprawdzamy czy zapytanie zwraca wyniki

Bardzo przydatną funkcją w PostgreSQL-u jest funkcja EXISTS. Za jej pomocą możemy sprawdzić czy zapytanie lub podzapytanie zwraca jakieś wyniki. Wielu początkujących programistów stawia znak równości między sytuacją, kiedy zapytanie wykonalo się poprawnie ale nie zwróciło żadnych wyników a sytuacją kiedy zapytanie wykonało się poprawnie i zwraca jakieś rekordy. W obu przypadkach zapytanie wykonało się bez błędów ale niekiedy nie zwraca żadnego wyniku (żadnego rekordu). Są takie sytuacje kiedy nasze dalsze działania uzależniamy od sytuacji gdy zapytanie zwraca jakieś rekordy. Ja także znalazłem się ostatnio w takiej sytuacji i moje poszukiwania zakończyły się sukcesem, funkcja EXISTS.
Zwracany typ: BOOLEAN (true or false)

PostgreSQL EXISTS() składnia

EXISTS(zapytanie)


Przykład zastosowania funkcji EXISTS() (Baza: Biblioteka)

Wyświetl wszystkich autorów ksiżek z tabeli 'Autorzy' jeżeli w tabeli tej znajduje się autor o imieniu i nazwisku: Jarosław Testowy

W tabeli 'Autorzy' znajduje się 1000 rekordów ale nie ma autora o takim imieniu i nazwisku, więc zapytanie nie powinno zwrócić żadnych wyników. Poniżej efekt uruchomienia zapytania.

postgresql funkcja function exists sprawdzamy czy zapytanie zwraca wyniki

Widzimy, że wszystko zadziałało poprawnie zapytanie nie zwróciło żadnych wyników bo wartość zwrócona przez funkcję EXISTS była false, z uwagi na to, że w bazie nie było żadnego autora o szukanym imieniu i nazwisku.

 


postgresqlpostgresql

T-SQL SQL Server / Funkcja CHARINDEX

Za pomocą funkcji CHARINDEX możemy sprawdzić czy dany łańcuch znaków zawiera jakiś poszukiwany przez nas ciąg znaków. Naszym poszukiwanym ciągiem będzie jakiś tekst. Funkcja ta zwróci nam pozycję pierwszego znaku poszukiwanego tekstu z pierwszego wystąpienia naszego poszukiwanego tekstu. Funkcja CHARINDEX() jest bardzo podobna w działaniu do funkcji PATINDEX(). Różnica między tymi funkcjami polega na tym, że zdefiniowany wzorzec (poszukiwany tekst) w funkcji CHARINDEX() nie może zawierać znaków wieloznacznych a wzorzec w funkcji PATINDEX() może zawierać takie znaki.

Przykład zastosowania funkcji CHARINDEX

Sprawdź czy w zdaniu 'Ala ma kota' znajduje się tekst 'kot' i zwróć pozycję od której zaczyna się ten tekst w zdaniu.

— Dekarujemy sobie łańcuch główny, czyli zdanie 'Ala ma kota'
DECLARE
    @zdanie NVARCHAR(11) = 'Ala ma kota'

SELECT
     CHARINDEX('kot' , @zdanie) as [Pozycja pierwszego wystąpienia ‚kot’]

wynik

Pozycja pierwszego wystąpienia 'kot'

8

dla funkcji CHARINDEX() wielkość znaków w tekście nie ma znaczenia poniżej przykład

DECLARE
    @zdanie NVARCHAR(11) = 'Ala ma kota'

SELECT
     CHARINDEX('KoT' , @zdanie) as [Pozycja pierwszego wystapienia ‚kot’]

Zwróć uwagę, że kot w zdaniu 'Ala ma kota' jest pisany małymi literami, a w funkcji CHARINDEX() chcemy znaleźć ciąg KoT, gdzie K i T pisane są dużymi literami.

wynik

Pozycja pierwszego wystąpienia 'kot'

8

Widzimy więc, że wielkość liter w szukanym wzorcu nie ma znaczenia.


  SQL tutorial. AdventureWorks exercises SQL SERVER

T-SQL SQL Server / Funkcja PATINDEX

Za pomocą funkcji PATINDEX możemy sprawdzić czy dany łańcuch znaków zawiera jakiś poszukiwany przez nas ciąg znaków. Naszym poszukiwanym ciągiem może być konkretny text lub wzorzec utworzony z symboli wieloznacznych np. wyrażony w postaci wyrażenia regularnego. Funkcja ta zwróci nam pozycję pierwszego znaku poszukiwanego tekstu (który może być także wzorcem) z pierwszego wystąpienia poszukiwanego ciągu znaków (lub ciągu który pasuje do zdefiniowanego wzorca). Funkcja PATINDEX() jest bardzo podobna w działaniu do funkcji CHARINDEX(). Różnica między tymi funkcjami polega na tym, że zdefiniowany wzorzec (poszukiwany tekst) w funkcji PATINDEX() może zawierać znakie wieloznaczne a w funkcji CHARINDEX() nie.

Przykład zastosowania funkcji PATINDEX

Sprawdź czy w ciągu znaków 'Pierwszy dokument ma numer PL1020304050, a drugi dokument ma postać PL6050403020' znajduje się podciąg znaków który jest zbudowany wg wzoru: dwie duże litery od A-Z + 10 cyfr i zwróć pozycję od której zaczyna się poszukiwany ciąg znaków w ciągu głownym oraz wytnij ten kawałek tekstu z łańcucha głównego.

— Dekarujemy sobie łańcuch główny
DECLARE
    @lancuch_glowny NVARCHAR(100) = 'Pierwszy dokument ma numer PL1020304050, a drugi dokument ma postać PL6050403020'

SELECT
     PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' , @lancuch_glowny) as [Pozycja pierwszego wystapienia lancucha]
    ,SUBSTRING(@lancuch_glowny , PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' , @lancuch_glowny),12) as [Skopiowany lancuch]


  SQL tutorial. AdventureWorks exercises SQL SERVER

SQL REVOKE

W ostatnim wpisie omawialiśmy polecenie GRANT którym możemy nadawać uprawnienia do tabel dla poszczególnych użytkowników. Skoro uprawnienia możemy nadać to i możemy je odebrać i do tego służy polecenie REVOKE.


    


Składnia REVOKE

REVOKE
    uprawnienia_użytkownika ON nazwa_tabeli FROM nazwa_użytkownika

uprawnienia użytkownika – to lista która może zawierać jedno lub więcej (oddzielonych przecinkami) uprawnień które chcemy odebrać użytkownikowi
nazwa tabeli – tutaj wpisujemy nazwę tabeli z które chcemy odebrać uprawnienie(-a)
nazwa_użytkownika – tutaj wpisujemy nazwę użytkownika, któremu chcemy odebrać uprawnienia 


Odbieranie uprawnień w SQL SERVERZE

Krok 1. Uruchamiamy Management Studio

Krok 2. W gałęzi Security -> Logins możemy sprawdzić jakich użytkowników mamy na serwerze. Sprawdzając właściwości poszczególnych tabel możemy dowiedzieć się jakie uprawnienia dany użytkownik ma na danej tabeli. W przykładzie do wpisu gdzie poznawaliśmy polecenie GRANT utworzyliśmy użytkownika USER i nadaliśmy mu uprawnienie SELECT do tabeli WYPOZYCZENIA. To teraz odbierzemy mu to uprawnienie.

Krok 3. W oknie zapytań wybieramy odpowiedni kontekst (odpowiednią bazę danych – Biblioteka) i poleceniem REVOKE odbierzemy uprawnienie SELECT naszemu użytkownikowi USER
REVOKE select ON Wypozyczenia FROM [DESKTOP\USER]

po uruchomieniu komendy REVOKE powinniśmy otrzymać komunikat

Command(s) completed successfully

w przypadku błędnego podania użytkownika np. USER2 otrzymamy komunikat

Cannot find the user 'DESKTOP\USER2", because it does not exist or you do not have permission

Krok 5. Teraz we właściwościach tabeli Wypozyczenia możemy sprawdzić i zobaczyć czy uprawnienia zostały odebrane. W tym celu klikamy PPM na tabeli Wypozyczenia w bazie Biblioteka i wybieramy opcję Properties. W oknie "Table Properties – Wypozyczenia" w sekcji "Select a page" klikamy na "Permissions". Na poniższym zrzucie widzimy że w części "Users or roles" jest pusto i w części "Permissions" jest także pusto. Widzimy więc, że odebraliśmy uprawnienie SELECT użytkownikowi USER do tej tabeli.

sql server revoke odbieranie uprawnień użytkownikom


    

SQL GRANT

Tworząc i administrując bazami danych przychodzi taki moment kiedy potrzebujemy nadać komuś uprawnienia do naszej bazy i niekoniecznie chcemy aby miał on wszystkie uprawnienia do całej naszej bazy np. jakiś analityk potrzebuje robić okresowe zestawienia sprzedaży, jakaś aplikacja korzysta z naszej bazy danych itd. Zwróć uwagę, że jenek użytkownik potrzebuje większe uprawnienia np. odczyt ale również i zapis danych na jakichś tabelach, a drugiemu użytkownikowi wystarczy tylko odczyt na jednej tabeli. Rozwiązaniem tej sytuacji jest stworzenie użytkownika na bazie i nadanie mu odpowiednich uprawnień. Możemy to zrobić dzięki poleceniu GRANT. Jedna uwaga we różnych środowiskach bazodanowych uprawnienia nadajemy w różny sposób tzn. niektóre środowiska jak np. SQL Server wymaga już utworzonego użytkownika i dopiero dla utworzonego użytkownika możemy nadać uprawnienia.


    


Składnia GRANT

GRANT
    uprawnienia_użytkownika ON nazwa_tabeli TO nazwa_użytkownika

uprawnienia użytkownika – to lista która może zawierać jedno lub więcej (oddzielonych przecinkami) uprawnień które chcemy nadać użytkownikowi
nazwa tabeli – tutaj wpisujemy nazwę tabeli na którą chcemy nadać uprawnienie
nazwa_użytkownika – tutaj wpisujemy nazwę użytkownika, któremu chcemy nadać uprawnienia 


Nadawanie uprawnień w SQL SERVERZE

Krok 1. Uruchamiamy Management Studio

Krok 2. Przechodzimy do gałęzi Security -> Logins i sprawdzamy czy nasz użytkownik (któremu chcemy nadać uprawnienia) jest na liście. Jeżeli jest to z poziomu okna zapytań możemy poleceniem GRANT nadać użytkownikowi wymagane uprawnienia. Jeżeli użytkownika nie ma na liście klikamy prawym przyciskiem myszy (dalej PPM) i z menu wybieramy opcję "New Login"

Krok 3. W oknie "Login – New" wprowadzamy nazwę użytkownika (UWAGA wymagana jest pełna nazwa użytkownika razem z domeną: domena\nazwa_użytkownika), możemy określić metodę autektykacji użytkownika a nawet domyślą bazę danych. Po wypełnieniu formatki potwierdzamy nasz wybór przyciskiem OK. W tym momencie nasz użytkownik powiniem znaleźć się na liście Security -> Logins

sql grant sql server

Krok 4. W oknie zapytań wybieramy odpowiedni kontekst (odpowiednią bazę danych) i poleceniem GRANT nadajemy odpowiednia uprawnienia naszemu użytkownikowi np.
GRANT select ON Wypozyczenia TO [DESKTOP\USER]

Krok 5. Teraz we właściwościach tabeli Wypozyczenia możemy sprawdzić i zobaczyć czy uprawnienia zostały nadane. W tym celu klikamy PPM na tabeli Wypozyczenia w bazie Biblioteka i wybieramy opcję Properties. W oknie "Table Properties – Wypozyczenia" w sekcji "Select a page" klikamy na "Permissions". Na poniższym zrzucie widzimy nadane uprawnienie SELECT dla użytkowniak USER.

sql grant sql server


Nadawanie uprawnień w PhpMyAdmin (MySQL)

Krok 1. Łączymy się z PhpMyAdmin

Krok 2. W oknie głównym szukaj opcji "User accounts" i dalej "Add user account".

sql grant phpmyadmin mysql

Krok 3. W kolejnym oknie wypełniamy informacje w sekcji "Login information". Są to informacje dotyczące użytkowniak, a następnie zjeżdżamy stroną w dół i wypełniamy jakie uprawnienia chcemy nadać temu użytkownikowi i potwierdzamy nasz wybór przyciskiem GO na dole strony.

sql grant phpmyadmin mysql

sql grant phpmyadmin mysql

Krok 4. Pojawi nam się nowe okno gdzie zostaniemy poinformowani o pozytywnym nadaniu uprawnień oraz zobaczymy polecenie którym serwer nadał uprawnienia użytkownikowi. Możemy z niego wyczytać, że na początku został utworzony nowy użytkownik o nazwie "user" i nadano mu odpowiednie uprawnienia.
sql grant phpmyadmin mysql

GRANT SELECT ON *.* TO 'user'@'localhost'

widzimy, że użytkownikowi 'user' zostało nadane upoważnienie SELECT na wszytkich tabelach *.*


    

Wypełniamy bazę danych testowymi danymi

Witam, dzisiaj opiszę jak w łatwy i szybki sposób wypełnić pustą strukturę bazy danych testowymi danymi. Do tego celu użyję narzędzia MS Excel. Na początku opiszę jak wygenerować odpowiednie dane a później pokażę jak stworzyć do nich zapytanie którym "wrzucimy" dane do bazy. Generowanie danych do bazy pokażę na przykładzie bazy "Biblioteka" której proces tworzenia opisałem >>>tutaj<<<

Dane będziemy "wrzucać" do bazy "Biblioteka" na środowisku SQL SERVER, w aplikacji Management Studio.


Generujemy dane do tabeli KATEGORIE

Krok 1. Otwieramy program MS Excel.

Krok 2. W Internecie wyszukujemy sobie wykaz kategorii książek. Chodzi o jakikolwiek wykaz kategorii książkowych z jakiejkolwiek strony i wklejamy go do jednego z arkuszów Excela do pierwszej kolumny A.

generowanie danych do testowej bazy danych

Krok 3. Teraz przechodzimy do kolumny B. W pierwszych dwóch komórkach wpisujemy wartości 1 i 2. Zaznaczamy te oba pola i dwukrotnie klikamy prawym przyciskiem myszy na czarnym kwadraciku w prawym dolnym rogu zakresu który zaznaczyliśmy.

generowanie danych do testowej bazy danych

dwukrotne kliknięcie spowoduje automatyczne wypełnienie poniższych komórek kolejnymi wartościami aż do wiersza 26.

generowanie danych do testowej bazy danych

ta nowa kolumna posłuży nam jako wartości pola "id_kategoria"

Krok 4. Teraz ustawiamy się w komórce C1 i wpisujemy formułę

="insert into Kategorie values("&TEKST(B1;"0")&",'"&A1&"');"

Po kliknięciu w enter (zatwierdzeniu formuły) w komórce gdzie wpisaliśmy naszą formułę powinniśmy uzyskać wartość (poniżej wytłumaszę bardziej szczegółowo formułę)

insert into Kategorie values(1,'Akcja');

widzimy, że otrzymaliśmy gotowy kod do wrzucenia do bazy do tabeli "Kategorie" jednego rekordu. Ustawiamy się w komórce C1 i znowu dwukrotnie klikemy na czarny kwadracić.

generowanie danych do testowej bazy danych

w tym momencie Excel automatycznie wypełni kolumnę danymi aż do wiersza 26 kompiując naszą formułę do wszyskich poniższych komórek.

generowanie danych do testowej bazy danych

teraz wystarczy skopiować zaznaczony powyżej zakres komórek i wkleić do Management Studio i uruchomić kod.

Zatrzymajmy się jednak przy formule z początku kroku 4
="insert into Kategorie values("  &  TEKST(B1;"0")  &  ",'"  &  A1  &  "');"

jest to po prostu złączenie kilku tekstów w jedną całość za pomocą łącznika "&".  Powyżej przed i po łączniku dałem po dwie spacje żebyście wizualnie zobaczyli poszczególne ciągi znaków. Każdy pojedynczy ciąg znaków umieszczamy w podwójnych cudzysłowach " " (chyba, że dołączamy komórkę której typ tekst wtedy wpisujemy tylko adres tej komórki np. &A1) i po prostu łączymy je w jedną całość. Jednak w naszej formule musieliśmy posłużyć się funkcją TEKST() która pomogła nam zamienić wartości liczbowe z kolumny B w tekst, bo nie możemy łączyć różnych typów danych i wyświetlać ich w jednej komórce.


Generujemy dane do tabeli AUTORZY

Krok 1. Uruchamiamy MS Excel

Krok 2. Pierwszą kolumnę A zostawimy na formułę końcową. Ustawiamy się więc w komórce B1 w wpisujemy tam wartość 1 a w komórce B2 wpisujemy wartość 2. Zaznaczamy obie komórki i przeciągamy (wciskamy PPM i trzymamy) czarny kwadracik (prawy dolny róg) w dół do wiersza np.100.

generowanie danych do testowej bazy danych

generowanie danych do testowej bazy danych

Krok 3. Przechodzimy do arkusza nr 2 i kopiujemy do pierwszej kolumny imiona osób. Ja w Internecie wyszukałem wykaz imion żeńskich i męskich i skopiowałem dane z tego wykazu do drugiego arkusza. Zmieniamy nazwę drugiego arkusza na "imiona".Po skopiowaniu danych dane z wykazu imion wypełniły mi zakres danych od A1 do A334

generowanie danych do testowej bazy danych

Krok 4. Teraz przechodzimy do trzeciego arkusza i do pierwszej kolumny wprowadzamy duże litery alfaberu. Zmieniamy jego nazwę na "alfabet"

generowanie danych do testowej bazy danych

Krok 5. Wracamy do pierwszego arkusza i klikamy w komórkę A1, gdzie wprowadzimy formułę. Naszą formułę rozpoczynamy od kodu
="insert into Autorzy values("

Krok 6. teraz dodamy do naszej formuły część która stworzy dane do pola "id_autor"
&TEKST(B1,"0")

powyższy kod zamieni nam liczbę z kolumny B1 na tekst.

Krok 7. Teraz wygenerujmy kod który wypełni nam pole "imie" w tabeli Autorzy. do naszej formuły dodajemy
&",'"

bo pole id_autro jest liczbą więc po tej wartości musi stać przecinek a kolejne pole będzie imienie które jest tekstem więc musimy dać znak '. Teraz możemy dodać kod dotyczący samego imienia. Pamiętajmy, że w drugim arkuszu w pierwszej kolumnie mamy wstawione imiona od komórki A1 do A334. Jak chcę zrobić tak, żeby w każdym wierszu (w pierwszym arkuszu) w którym wykona się nasza formuła Excel automatycznie wylosował jakieś imię z arkusza "imie" i wstawił do arkusza pierwszego. Kawałek kodu który dodamy do naszej formuły będzie wyglądał tak.
&INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))

funkcja INDEKS ma trzy parametry INDEKS(tablica ; numer_wiersza ; [numer_kolumny])
w pierwszym parametrze wskazujemy zakres komórek z którego będziemy losowali wartość imiona!$A$1:imiona!$A$334 . Zwróć uwagę, że przy adresach komórek zastosowałem znak $ który mówi o tym, że przy kopiowaniu formuły z jednej do drugiej komórki, zakres komórek z arkusza "imiona" będzie stały i nie będzie się przesuwał wraz z kopiowaniem tej formuły do innych komórek.
w drugim parametrze podajemy numer wiersza z którego będziemy chcieli "skopiować" wartość. W naszym przypadku chcemy aby za każdym razem Excel wziął wartość (imię) z losowaj komórki więc musimy zastosować funkcję LOS.ZAKR(od;do). Funkcja ta za każdym jej wywołaniem zwróci nam losową liczbę z podanego zakresu np. LOS.ZAKR(1;30) zwróci nam liczbę z zakresu od 1 do 30.
trzeci parametr numer kolumny jest opcjonalny. Z uwagi na to, że w arkuszu imiona mamy tylko jedną kolumnę to ten parametr możemy pominąć lub wstawić tam wartość 1.

Krok 8. Przejdźmy do wygenerowania nazwisk. Przydałoby się żeby nazwiska nie były realne (ochrona danych osobowych) więc pokażę Wam jak w łatwy sposób wygenerować fikcyjne dane do pola nazwisko. Do naszych celów wykorzystamy trzeci arkusz "alfabet".

generowanie danych do testowej bazy danych

nasze nazwisko będzie się składało z pierwszych trzech znaków imienia i trzech kolejnych losowo wybranych znaków z alfabetu z arkusza "alfabet". Wracamy do pierwszego arkusza i do naszej formuły dodajemy kod. Założyłem, że nazwiska będę podawał dużymi literami.
&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

no tutaj już robi się bardziej skomplikowanie ale po kolei
na początku skopiujemy 3 znaki od lewej z imienia i zamienimy to na duże litery. Do tego celu muszę wykorzystać trzy funkcje: INDEKS(), LEWY() i LITERY.WIELKIE()

na początek wyciągniemy trzy znaki z losowo wybranego imienia z arkusza "imiona".
INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))

funkcji INDEKS() już chyba nie trzeba tłumaczyć bo wytłumaczyłem to wcześniej. Mamy więc pobrane losowe imię ale teraz musimy wyciąć z niego tylko 3 znaki od lewej. Wykorzystamy do tego funkcję LEWY() w której podajemy dwa parametry LEWY(tekst ; ilość_znaków_do_wycięcia). W naszym przypadki jako pierwszy parametr podamy cały kod funkcji INDEX, czyli INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334)) a jako drugi parametr podamy cyfrę 3, bo chcemy wyciąć trzy znaki. Nasz kod przybierze postać.
LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3)

teraz wystarczy zamienić otrzymany ciąg znaków na duże litery za pomocą funkcji LITERY.WIELKIE() gdzie jako parametr podajemy tylko tekst który chcemy zamienić na duże litery. W naszym przypadku będzie to kod który otrzymaliśmy w poprzednim kroku czyli LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3), więc nasz nowy kod będzie wyglądał tak.
LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))

Teraz wystarczy dołączyć do naszego kodu trzy losowo wybrane znaki z arkusza "alfabet". Pojedynczy znak wyciągamy następującym kodem.
INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

żeby mieć trzy losowe znaki musimy powtórzyć ten kod trzykrotnie ze znakiem & pomiędzy nimi. Cały nasz kod generujący trzy dowolne znaki będzie wyglądał więc następująco
INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

teraz złączamy wszystkie elementy razem i otrzymujemy
&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))

ok mamy już nazwisko. Teraz wystarczy tylko zakończyć formułę i złączyć wszystkie jej części w całość. Kod całej formułygenerującej całe zapytanie będzie wyglądał następująco.
="insert into Autorzy  values("&TEKST(B1;"0")&",'"&INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334))&"','"&LITERY.WIELKIE(LEWY(INDEKS(imiona!$A$1:imiona!$A$334;LOS.ZAKR(1;334));3))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))
&INDEKS(alfabet!$A$1:alfabet!$A$26;LOS.ZAKR(1;26))&"');"

powyższa formuła w każdym wierszu wygeneruje nam tekst w strukturze jak poniżej.
insert into Autorzy values(1,'Wiktoria','KRYJWB');

teraz wystarczy tylko skopiować formułę w dół więc ustawiamy się pierwszej komórce (A1) w pierwszym arkuszu i klikamy dwukrotnie na czarny kwadracik w prawym dolnym rogu komórki.

generowanie danych do testowej bazy danych

Excel automatycznie skopiuje formułę dół aż do komórki A100, bo tyle wierszy "stworzyliśmy" w kroku 1.

generowanie danych do testowej bazy danych


z pomoca funkcji opisanych powyżej jesteście w stanie wygenerować dane do każdej z tabel bazy "Biblioteka"

Bazę 'Bibliteka' z wygenerowanymi testowymi danymi możesz pobrać >>>tutaj<<<