Archiwum kategorii: SQL polecenia funkcje

SQL REVOKE

SQL, SQL polecenia funkcje,

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

SQL, SQL polecenia funkcje,

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 *.*

    

SQL SELECT INTO

SQL, SQL polecenia funkcje, ,

W tym wpisie opiszę Wam zastosowanie składni SELECT INTO, która służy do kopiwania danych (kolumn) z jednej tabeli do drugiej tabeli. Bez zbędnych wstępów poniżej prezentuję Wam składnię takiej konstrukcji. Dobrą rzeczą jest to, że nie musimy mieć utworzonej tabeli_docelowej, zostanie ona utworzona automatycznie w chwili uruchomienia zapytania.

    

Składnia SELECT INTO

SELECT
    * — * oznacza wszystkie kolumny
INTO
    nowa_tabela
FROM
    tabela_źródłowa
— opcjonalnie warunek po WHERE
WHERE
    warunek(-ki)

Przykład zastosowania SELECT INTO (baza: Adventureworks2008R2)

No to przechodzimy do przykładu i na warsztat bieżemy bazę AdventureWorks2008R2.

Będziemy kopiowali dane z tabeli "Person.Person" (osoby o typie EM czyli Employees/Pracownicy) do tabeli "Person_tmp". W tym celu zbudujemy zapytanie.

SELECT
    *
INTO
    Person_tmp
FROM
    Person.Person
WHERE
    PersonType = 'EM'

Uruchamiamy zapytanie (F5) i powinniśmy otrzymać informację, że wszystko wykonało się poprawnie.

SQL SELECT INTO1

Widzimy, że konsturkcja wykonała się poprawnie i w tym momencie powinniśmy mieć nową tabelę o nazwie "Person_tmp" (najprawdopodobniej żeby ją zobaczyć musisz odświeżyć, strukturę bazy AdventureWorks2008R2 w drzewie baz danych, w tym celu klikasz prawym przyciskiem myszy na bazie AdventureWorks2008R2 i z menu wybierasz opcję Refresh) w której powinno znajdować się 273 rekordów które zostały do niej skopiowane.

Sprawdzamy to zapytaniem

SELECT
    *
FROM
    Person_tmp

Wynik


SQL SELECT INTO

Wszystko się zgadza w tabeli "Person_tmp" znajduje się 273 rekordy.

    

SQL INSERT INTO SELECT

SQL, SQL polecenia funkcje, , ,

Chciałbym Wam dzisiaj opisać działanie składni INSERT INTO SELECT. Jak już widzimy po słowach kluczowych coś wstawiamy (INSERT) i określamy skąd i co wrzucamy (SELECT). Żeby jakieś dane wziąć i gdzieś dodać musimy określić dwie rzeczy źródło i zakres danych do pobrania oraz cel, czyli gdzie te pobrane dane chcemy "wrzucić". To gdzie chcemy "wrzucić" określamy po słówkach INSERT INTO, a to co i skąd chcemy pobrać określamy po słówku SELECT, tutaj będziemy konstruowali po prostu zapytanie. Składnia całej konstrukcji poniżej. PAMIĘTAJ, że struktura danych kopiowanych i struktura danych do której te dane kopiujemy muszą być zgodne.

    

Składnia SQL INSERT INTO SELECT

INSERT INTO tabela_cel(nazwy_kolumn_po_przecinkach)
SELECT nazwy_kolumn_po_przecinkach FROM tabela_źródło
— opcjonalnie możemy dodać "sekcję" WHERE

Przykład zastosowania INSERT INTO SELECT (baza: Northwind)

Do przykładu wykorzystamy treningową bazę Northwind. Dodamy do niej nową tabelę "Customers_tmp" z kolumnami (CustomersID, CompanyName, City). Zrzut z tworzenia tabeli poniżej.

sql-insert-into-select

Teraz "skopiujemy" rekord o CustomersID = 'ALFKI' z tabeli Customers (ale tylko dane z kolumn: CustomersID, CompanyName, City) do tabeli "Customers_tmp" z takimi samymi kolumnami. Zapytanie poniżej.

INSERT INTO Customers_tmp
SELECT CustomerID, CompanyName, City FROM Customers WHERE CustomerID = 'ALFKI'

czyli do tabeli "Customers_tmp" wrzucimy dane z rekordu o CustomersID = 'ALFKI z tabeli "Customers", ale tylko dane z kolumn: CustomerID, CompanyName, City.
Uruchamiamy zapytanie i o poprawności wykonania zapytania mówi nam komunikat poniżej.

sql-insert-into-select

teraz żeby sprawdzić czy wszystko zadziałało musimy odpytać tabelę "Customers_tmp" o wszystkie rekordy.

SELECT
    *
FROM
    Customers_tmp

wynik zapytania poniżej

sql-insert-into-select

Na zrzucie widzimy, że w tabeli "Customers_tmp" znajdują się nowe dane, co potwiedza poprawność działania naszego zapytani.

Przykład zastosowania INSERT INTO SELECT (baza: Adventureworks)

W poprzednim przykładzie kopiowaliśmy tylko jeden rekord. Teraz spóbujmy skopiować więcej rekordow i do tego celu wykorzystam bazę AdventureWorks2008R2.

Tworzymy nową tabelę o nazwie "Person_SP" (skrót SP od Sales Person) do której będziemy kopiowali osoby z tabeli "Person.Person" ale tylko takie którę są sprzedawcami. Zakres kopiowanych danych to: BusinessEntityID, FirstName, LastName.

Tworzymy nową tabelę "Person_SP"

sql-insert-into-select

Teraz tworzymy strukturę z INSERT INTO SELECT gdzie w części SELECT zbudujemy zapytanie które pobierze nam dane (BusinessEntityID, FirstName, LastName) o osobach które sa sprzedawcami.

INSERT INTO Person_SP
SELECT BusinessEntityID, FirstName, LastName FROM Person.Person WHERE PersonType = 'SP'

W tabeli "Person.Person" takich osób jest 17 więc dane tylu osób powinny być skopiowane do tabeli "Person_SP"

Uruchamiamy zapytanie (F5).
Powinniśmy otrzymać komunikat o poprawnym wykonaniu naszej konstrukcji.

sql-insert-into-select

Teraz żeby sprawdzić czy w tabeli "Person_SP" jest 17 rekordów z danymi budujemy zapytanie.

SELECT
    *
FROM
    Person_SP

Wynikiem zapytanie powinno wyć wygenerowanie 17-tu rekordow, co widzimy na zrzucie poniżej.

sql-insert-into-select

 

    

SQL IN z CASE i podzapytaniem

SQL, SQL polecenia funkcje, ,

Polecenie IN opisywałem tutaj, ale ostatnio "buszując" w Internecie natrafiłem na ciekawe pytanie dotyczące zapytania SQL i postanowiłem rozwinąć trochę poprzedni wpis o rozwiązanie z CASE i podzapytaniem.

    

Przykład zastosowania polecenia IN z CASE

SELECT
    *
FROM
    nazwa_tabeli
WHERE
    nazwa_pola IN (
            CASE
                WHEN warunek1 THEN wartość1
                WHEN warunek2 THEN wartość2
                ELSE warunek3 THEN wartość3
            END
        )

Widzimy tutaj że w zależności od wyniku polecenie CASE zostanie wybrana wartość która będzie argumentem dla polenia IN.

Przykład zastosowania polecenia IN z podzapytaniem

SELECT
    *
FROM
    nazwa_tabeli
WHERE
    nazwa_pola IN (
            SELECT TOP 1
                nazwa_pola
            FROM
                nazwa_tabeli
            WHERE
                warunek
        )

W tym przypadku zbiorem wartości dla IN jest wynik podzapytania.


Można by pójść o krok dalej i uzależnić wartości dla zbioru IN od zmiennej.

Przykład zastosowania polecenia IN z CASE i z deklaracją zmiennej.

DECLARE @zmienna varchar(5) = '100'

SELECT
    *
FROM
    nazwa_tabeli
WHERE
    nazwa_pola IN (
            CASE
                WHEN @zmienna < 100 THEN wartość1
                WHEN @zmienna = 100 THEN wartość2
                WHEN @zmienna > 100 THEN wartość3
            END
        )

Przykład zastosowania polecenia IN z podzapytaniem i z deklaracją zmiennej.

DECLARE @zmienna varchar(5) = '100'

SELECT
    *
FROM
    nazwa_tabeli
WHERE
    nazwa_pola IN (
            SELECT TOP 1
                nazwa_pola
            FROM
                nazwa_tabeli
            WHERE
                nazwa_pola <= @zmienna
        )

    

SQL Server, DECLARE SET, zmienne lokalne

SQL polecenia funkcje, ,

W niniejszym wpisie opiszę zmienne lokalne (deklarację DECLARE i inicjalizację SET) w środowisku SQL Server.

Przy pisaniu zapytań często istnieje potrzeba "wrzucenia" jednej lub kilku zmiennych do naszego zapytania. Plusami takiego rozwiązania jest niewątplikwie to, że wszystkie zmienne mamy zadeklarowane w jednym miejscu i nie musimy później modyfikować naszego kodu zapytania tylko zmieniamy wartości poszczególnych zmiennych, które są użyte do budowania naszego zapytania.

Deklaracja zmiennej

Do deklaracji zmiennej wykorzystujemy polecenie DECLARE

DECLARE @nazwa_zmiennej typ_zmiennej;

np.

DECLARE @liczba int;

Po deklaracji przychodzi czas na inicjalizację, czyli przypisanie wartości.

Do inicjalizacji wykorzystujemy polecnie SET

SET @nazwa_zmiennej = wartość_zmiennej;

np.

SET @liczba = 1;

Jak to z reguły była deklarację i inicjalizację możemy wykonać w jednym kroku.

Deklaracja i inicjalizacja zmiennej.

DECLARE @liczba int = 1;

Później w kodzie zapytania, jeżeli chcemy odwołać się do zmiennej, wykorzystujemy zapis @nazwa_zmiennej.

Przykład. Zadeklaruj i przypisz wartość 1 do zmiennej @liczba, typu int. Wyświetl wartość zmiennej @liczba.

DECLARE @liczba int = 1;
SELECT
    @liczba

SQL ALTER TABLE

SQL polecenia funkcje,

SQL ALTER TABLE

    

DEFINICJA

Polecenie ALTER TABLE służy do modyfikowania kolumn w istniejących tabelach. Modyfikacja ta może polegać na dodaniu nowej kolumny, usunięciu istniejącej kolumny lub zmianie (np. typu danych) istniejącej kolumny. Za pomocą tego polecenie może także dodawać lub usuwać ograniczenia z kolumn w tabeli.

Przykład dodania nowej kolumny o nazwie "MiddleName" (o typie danych varchar) do tabeli o nazwie "Employees"
SQL SERVER baza: Northwind

ALTER TABLE
    Employees
ADD
    MiddleName varchar(10);   

Przykład modyfikacji, zmiany typu danych z varchar na int, na kolumnie "MiddleName" w tabeli "Employees"
SQL SERVER baza:Northwind

ALTER TABLE
    Employees
ALTER COLUMN
    MiddleName int;   

Przykład usunięcia kolumny "MiddleName" z tabeli o nazwie "Employees" (baza: Northwind).
SQL SERVER baza: Northwind

ALTER TABLE
    Employees
DROP COLUMN
    MiddleName;

    

SQL CHECK

SQL polecenia funkcje,

SQL CHECK

    

DEFINICJA

CHECK to kolejne ograniczenie (CONSTRAINTS) które można nadać na kolumny w tabeli. Ogranicza ono zakres wartości które mogą się pojawić w kolumnie. Ograniczenie to możemy nałożyć na jedną kolumnę, wtedy ognicza ono wartości we wskazanej kolumnie lub nałożyć je na wiele kolumn. Ograniczenie CHECK możemy nadać w momencie tworzenia nowej tabeli lub na już istniejącej tabeli.

Przykład zastosowania CHECK przy tworzeniu nowej tabeli (SQL SERVER).

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL CHECK (ID_pracownik >0)
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    ,Adres varchar(50)
    ,Email varchar(30)
    ,Telefon int
)

 Przykład zastosowania CHECK przy tworzeniu nowej tabeli (MySQL).

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    ,Adres varchar(50)
    ,Email varchar(30)
    ,Telefon int
   ,CHECK (ID_pracownik > 0)
)

Ogranicznie CHECK możemy nadać już na istniejącą tabelę (kolumnę).

ALTER TABLE Pracownicy
ADD CHECK (ID_pracownik > 0)

    

SQL UNIQUE

SQL polecenia funkcje,

SQL UNIQUE

    

DEFINICJA

UNIQUE to jedno z ograniczeń (CONSTRAINTS) które można „nałożyć” na kolumny w tabeli. Możemy wskazać kolumnę, która ma przechowywać unikalne wartości. Początkujący programiści często mylą UNIQUE z PRIMARY KEY. Są pewne różnice. W odróżnieniu od klucza głównego (który może być tylko jeden w tabeli) kolumn z ograniczeniem UNIQUE może być więcej niż jedna w tabeli. Kolumny te mogą (w odróżnieniu od kluczy głównych) przechowywać wartości NULL.

Przykład zastosowania UNIQUE przy tworzeniu nowej tabeli (SQL SERVER).

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL UNIQUE
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    ,Adres varchar(50)
    ,Email varchar(30)
    ,Telefon int
)

 Przykład zastosowania UNIQUE przy tworzeniu nowej tabeli (MySQL).

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    ,Adres varchar(50)
    ,Email varchar(30)
    ,Telefon int
   ,UNIQUE(ID_pracownik)
)

 Ograniczeniu UNIQUE można nadać nazwę i nałożyć je na więcej niż jedną kolumnę.

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    ,Adres varchar(50)
    ,Email varchar(30)
    ,Telefon int
   ,CONSTRAINTS unique_Person UNIQUE (ID_pracownik,Nazwisko)
)

Ogranicznie UNIQUE możemy nadać już na istniejącą tabelę (kolumnę).

ALTER TABLE Pracownicy
ADD UNIQUE (ID_pracownik)

 Tak jak przy tworzeniu tabeli ograniczenie UNIQUE możemy nadać na więcej niż jedną kolumnę i nadać mu nazwę.

ALTER TABLE Pracownicy
ADD CONSTRAINT unique_Person  UNIQUE (ID_pracownik, Nazwisko)

UNIQUE możemy także usunąć (SQL SERVER).

ALTER TABLE Pracownicy
DROP CONSTRAINT unique_Person

    

SQL CONSTRAINTS

SQL polecenia funkcje,

SQL CONSTRAINTS (ograniczenia)

    

DEFINICJA

CONSTRAINTS (ograniczenia) to reguły które określają jakie dane mogą się znajdować w tabelach (kolumnach). Część z tych zasad "pilnuje" aby dane wprowadzane do naszej bazy danych były logicznie spójne a część "pilnuje" także żeby działania wykonywane na danych odbywały się zgodnie z tymi zasadami. Z reguły nadajemy je, podczas deklaracji struktury tabel, ale są też sposoby na ich dodanie do już istniejącej struktury (tabeli). Jest kilka takich reguł które krótko opiszę poniżej.

Ogólna składnia CONSTRAINTS

CREATE TABLE nazwa_tabeli
(
    …
    nazwa_kolumny typ_danych(rozmiar) nazwa_ograniczenia
    …
)

NOT NULL

Wykorzystując tą zasadę określamy, że dana kolumna nie może przechowywać wartości NULL.
Przykład zastosowania NOT NULL

CREATE TABLE Pracownicy
(
     ID_pracownik int NOT NULL
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    …    
)

UNIQUE

Zasada która "mówi", że kolumna musi przechowywać wartości unikalne. Kolumna z zasadą UNIQUE może przechowywać wartość NULL.

Przykład zastosowania UNIQUE

CREATE TABLE Pracownicy
(
     ID_pracownik int UNIQUE
    ,Imie varchar(20)
    ,Nazwisko varchar(50)
    …    
)

PRIMARY KEY (klucz główny)

To zasada która "mówi", że kolumna musi zawierać unikalne wartości które w jednoznaczny sposób identyfikują każdy rekord. W kolumnie tej nie możemy przechowywać wartości NULL. Z powyższego opisu można jasno wywnioskować, że PRIMARY KEY  to takie połączenie NOT NULL i UNIQUE. Więcej na temat PRIMARY KEY przeczytasz tutaj.

FOREIGN KEY (klucz obcy)

To definiowanie relacji między tabelami (określenie zasad spójności danych w tabelach). Wartości przechowywane w kolumnie która jest zdefinowana jako klucz obcy w pierwszej tabeli zawsze będą miały swój odpowiednik w kolumnie która jest zdefiniowana jako klucz główny w drugiej tabeli. Więcej na temat kluczy obcych przeczytasz tutaj.

CHECK

Ta zasada "zba" o to, żeby w danej kolumnie znajdowały się wartości które spełniają określony przez nas warunek. Inaczej mówiąc warunek ten musi być prawdziwy dla wszystkich rekordów w danej tabeli.

DEFAULT

Zasada DEFAULT określa wartość domyślną dla danej kolumny.