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;
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:
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