Progmar Marcin Załęczny

Język:

Utworzenie bazy danych employee i użytkownika emp o haśle emp mającego pełny dostęp do tej bazy: create database employee character set utf8 collate utf8_polish_ci;
grant all privileges on employee.* to emp@localhost identified by 'emp';
flush privileges;

Utworzenie tabeli emp z domyślnym kodowanie znaków utf8: create table emp (id integer primary key auto_increment, name varchar(32), lastname varchar(64), sex char, pesel varchar(12), nip varchar(13), gsm varchar(12), state varchar(64), note text) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE utf8_polish_ci;

Ustawienie w bieżącej bazie domyślnego kodowania utf8: set names utf8;

Wyświetlenie indeksów, kluczy głównych, kluczy obcych i wszelkich ograniczeń (constraints): select * from information_schema.table_constraints;

Usunięcie klucza obcego: ALTER TABLE wm_residents DROP FOREIGN KEY wm_residents_building_id;

Dodanie klucza głównego: ALTER TABLE Employee ADD PRIMARY KEY (last_name(6),first_name(6));

Dodanie klucza obcego: ALTER TABLE roles_users ADD CONSTRAINT roles_users_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

Dodanie indeksu: CREATE INDEX index_name ON tbl_name (col_name1, col_name2);

Dodanie indeksu unikalnego (unique): ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

Wyłączenie sprawdzania w bazie kluczy obcych: SET FOREIGN_KEY_CHECKS=0;

Zapisanie do pliku wyłącznie danych: mysqldump -u wm -p wm --no-create-info >output.txt

Zapisanie do pliku wyłącznie danych z podanych tabel: mysqldump -u wm -p wm table1, table2 --no-create-info >output.txt

Zmiana definicji kolumny: alter table wm_residents change print_blanket_type print_blanket tinyint default 1;

Skopiowanie całej tabeli (utworzenie nowej) wraz z strukturą i danymi: create table wm_defined_transfers select * from wm_transfers;

Przykłady użycia poleceń group by i join

Wypisanie wszystkich klientów wraz z ich typami z tabel clients i clienttypes: SELECT c.clientTypeID, ct.clientTypeName FROM clients as c LEFT JOIN clienttypes as ct ON (c.clientTypeID = ct.clientTypeID)

Wypisanie wszystkich typów klientów wraz z sumą klientów należących do tych typów (ID typu, nazwa typu, sum(klienci)): SELECT c.clientTypeID, ct.clientTypeName, COUNT(c.clientTypeID) FROM clients as c LEFT JOIN clienttypes as ct ON (c.clientTypeID = ct.clientTypeID) GROUP BY (c.clientTypeID)

Wyświetlenie ostatniej wizyty każdego klienta (nazwa klienta, ostatnia wizyta): SELECT c.clientName, ct.clientID, cv.clientVisitID, cv.visitDateTime, MAX(cv.visitDateTime) FROM clients as c RIGHT JOIN clientvisits as cv ON (c.clientID = cv.clientID) GROUP BY (c.clientName)

Włączenie logowania wszystkich zapytań mysql do pliku:

  1. SET GLOBAL log_output = "FILE";
  2. SET GLOBAL general_log_file = "/var/log/mysql/queries.log"
  3. SET GLOBAL general_log = 'ON';

Eksport danych do pliku csv:

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
				

Załadowanie do tabeli danych z pliku tekstowego: LOAD DATA LOCAL INFILE '/sciezka/do/pliku' INTO TABLE dane; W pliku tym poszczególne kolumny powinny być rozdzielone tabulatorami a wartość NULL jest reprezentowana przez wartość \N.

Utworzenie kopii bazy danych: mysqldump -u nazwa_uzytkownika -p nazwa_bazy > kopia_bazy.sql

Przywrócenie bazy danych z kopii zapasowej: mysql -u nazwa_uzytkownika -p nazwa_bazy < kopia_bazy.sql

Obliczenie sumy liczb oraz wyświetlenie wielkimi literami przekazanego ciągu: select 123 + 654, UPPER('sql');

Domyślnie zapytanie select zwraca wszystkie pasujące wiersze, zatem poniższe zapytania są równoważne: select uwagi from `Zamowienia zakupu`; select all uwagi from `Zamowienia zakupu`;

Wyświetlenie tylko unikalnych wierszy z tabeli: select distinct uwagi from `Zamowienia zakupu`; Słowo kluczowe distinct może wystąpić w klauzuli SELECT tylko raz i zawsze dotyczy wszystkich wymienionych w tej klauzuli kolumn.

Zastosowanie wyrażeń w instrukcji select: SELECT `Oplata za wysylke`, `Oplata za wysylke` * 5 FROM Zamowienia; SELECT Ilosc, `Cena jednostkowa`, Ilosc * `Cena jednostkowa` FROM `Szczegoly zamowien`; SELECT `Cena jednostkowa` + Rabat * `Cena jednostkowa`, (`Cena jednostkowa` + Rabat) * `Cena jednostkowa` FROM `Szczegoly zamowien`;

Łączenie danych tekstowych: SELECT `Kod produktu`,`Nazwa produktu`, CONCAT(`Kod produktu`, ' ', `Nazwa produktu`) FROM Produkty;

Zwraca wartość bezwzględną liczby: select abs(1), abs(-1);      -- wynik: 1,1

Zwraca najmniejszą liczbę całkowitą równą argumentowi wywołania lub większą od niego: select ceiling(123.23);      -- wynik: 124

Zwraca największą liczbę całkowitą równą argumentowi wywołania lub mniejszą od niego: select floor(123.93);      -- wynik: 123

Podnosi liczbę do potęgi określonej przez drugi argument wywołania: select power(5,3);      -- wynik: 125

Zwraca pseudolosową liczbę z zakresu od 0 do 1: select rand();

Zaokrągla liczbę do określonej przez drugi argument wywołania liczby miejsc po przecinku. Jeżeli drugi argument będzie ujemny, zaokrąglona zostanie część całkowita: select round(81.46,1), round(9,-1);      -- wynik: 81.5, 10

Zwraca pierwiastek kwadratowy liczby: select sqrt(81);      -- wynik: 9

Zwraca długość w bajtach podanego tekstu. (Polskie litery są liczone jako 2 bajty utf-8): select length('Marcin Załęczny');      -- wynik: 17

Zwraca długość w znakach podanego tekstu: select char_length('Marcin Załęczny');      -- wynik: 15 select length(_utf8 '€'), char_length(_utf8 '€')      -- wynik: 3, 1

Konwertuje wszystkie duże litery na małe, działa z UTF-8: select lower('Marcin Załęczny');      -- wynik: marcin załęczny

Konwertuje wszystkie małe litery na duże, działa z UTF-8: select upper('Marcin Załęczny');      -- wynik: MARCIN ZAŁĘCZNY

Usuwa z podanego tekstu wiodące spacje: select ltrim(' Marcin Załęczny');      -- wynik: Marcin Załęczny

Usuwa z podanego tekstu końcowe spacje: select rtrim('Marcin Załęczny ');      -- wynik: Marcin Załęczny

Usuwa z podanego tekstu początkowe i końcowe spacje: select trim(' Marcin Załęczny ');      -- wynik: Marcin Załęczny

Zamienia w podanym ciągu znaków wskazaną frazę na inną: select replace('Stop','St','H');      -- wynik: Hop

Powtarza łańcuch określoną ilość razy: select repeat('A1',3);      -- wynik: A1A1A1

Zwraca podłańcuch z podanego łańcucha. Drugi argument wskazuje miejsce w łańcuchu, od którego zaczynamy (licząc od 1) natomiast trzeci argument określa ilość znaków do zwrócenia: select substring('Marcin Załęczny',5,7);      -- wynik: in Załę

Do podanej daty dodaje określoną ilość dni: select adddate('2014-12-28', interval 31 day);      -- wynik: 2015-01-28

Wyświetla różnicę w dniach podanych dwóch dat. Od pierwszej daty jest odejmowana druga data: select datediff('2007-12-31 23:59:59','2007-12-30');      -- wynik: 1 select datediff('2010-11-30','2010-12-31');      -- wynik: -31

Zwraca numer dnia przekazanej w argumencie daty: select day(now());      -- wynik: 11 Zwraca numer miesiąca przekazanej w argumencie daty: select month(now());      -- wynik: 7 Zwraca numer roku przekazanej w argumencie daty: select year(now());      -- wynik: 2014

Zwraca bieżącą datę i czas select now();      -- wynik: 2014-07-11 15:53:49