Блог @artybondar

Обратно на сайт компании

Поиск по тегам

Избранное ★

РСС
9 заметок с тегом

MySQL

Детально о JOIN в SQL

23 августа 2016, 15:56

Рассмотрим 7 типов join:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL OUTER JOIN (в MySQL UNION)
  5. LEFT JOIN EXCLUDING INNER JOIN
  6. RIGHT JOIN EXCLUDING INNER JOIN
  7. OUTER JOIN EXCLUDING INNER JOIN


    Наглядно посмотрим в чем разница.

А теперь подробно о каждом из них.


Inner JOIN

SELECT <select_list> 
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key



Left JOIN

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key



Right JOIN

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key



Outer JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key



Left Excluding JOIN

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL



Right Excluding JOIN

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL



Outer Excluding JOIN

SELECT <select_list>
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL OR B.Key IS NULL



А теперь все это на примере 2 простых таблиц из БД:

TABLE_A
  PK Value
---- ----------
   1 FOX
   2 COP
   3 TAXI
   6 WASHINGTON
   7 DELL
   5 ARIZONA
   4 LINCOLN
  10 LUCENT

TABLE_B
  PK Value
---- ----------
   1 TROT
   2 CAR
   3 CAB
   6 MONUMENT
   7 PC
   8 MICROSOFT
   9 APPLE
  11 SCOTCH

-- INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
       B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7

(5 row(s) affected)

-- LEFT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
  10 LUCENT     NULL       NULL

(8 row(s) affected)

-- RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(8 row(s) affected)

-- OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 FOX        TROT          1
   2 COP        CAR           2
   3 TAXI       CAB           3
   6 WASHINGTON MONUMENT      6
   7 DELL       PC            7
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(11 row(s) affected)

-- LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
LEFT JOIN Table_B B
ON A.PK = B.PK
WHERE B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4 LINCOLN    NULL       NULL
   5 ARIZONA    NULL       NULL
  10 LUCENT     NULL       NULL
(3 row(s) affected)

-- RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
RIGHT JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11

(3 row(s) affected)

-- OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,
B.Value AS B_Value, B.PK AS B_PK
FROM Table_A A
FULL OUTER JOIN Table_B B
ON A.PK = B.PK
WHERE A.PK IS NULL
OR B.PK IS NULL

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL NULL       MICROSOFT     8
NULL NULL       APPLE         9
NULL NULL       SCOTCH       11
   5 ARIZONA    NULL       NULL
   4 LINCOLN    NULL       NULL
  10 LUCENT     NULL       NULL

(6 row(s) affected)
INNER JOIN   JOIN   LEFT JOIN   MySQL   OUTER JOIN   RIGHT JOIN   SQL
нет комментариев

Конвертация из MyISAM в InnoDB и обратно для таблиц MySQL

26 апреля 2016, 11:46

Конвертация будет проводиться из sql дампа.


Сначала в конфиге mysql проверяем включена ли поддержка InnoDB и другие параметры.


#skip-innodb

innodb_data_file_path = ibdata1:100M:autoextend //минимальный файл 100мб и разрешено авто-увеличение файла


При желании вместо innodb_data_file_path можно использовать innodb_file_per_table, тогда под каждую таблицу будет использоваться свой отдельный файл.


После изменения нужно перезапустить mysql


Итак, процесс конвертации:

mysqldump --opt -u USER -p DBNAME > dbname.sql //создание дампа
sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' dbname.sql > dbname.innodb.sql //MyISAM → InnoDB
sed s/ENGINE=InnoDB/ENGINE=MyISAM/g dbname.sql > dbname.myisam.sql //InnoDB → MyISAM

mysqladmin -u USER -p drop DBNAME //удаляем
mysqladmin -u USER -p create DBNAME //создаем снова
mysql -u USER -p DBNAME < dbname.{innodb|myisam}.sql //заливаем дамп


Готово.

InnoDB   MyISAM   MySQL   Конвертация
нет комментариев

Основы операций с MySQL

25 апреля 2016, 17:28

Итак, рассмотрим основные операции в Mysql.
Сначала подключиться к БД mysql, хост localhost, под пользователем root, используя интерактивный ввод пароля:

mysql -u root -p
mysql -u root -p dbname
mysql -u root -h localhost -pPASSWORD // подключение с паролем PASSWORD
mysql -u root -h artana.ru -p // удалённый хост artana.ru


Создание дамп баз данных

mysqldump --opt -u root -p dbname > dbname.sql
mysqldump --opt -u root -p dbname mytable > dbname.mytable.sql //отдельной БД mytable

Для всех баз данных — ключ:

--all-databases

По умолчанию хранимые функции и процедуры не добавляться в backup.
нужно добавить ключь

-R, --routines


Управление таблицами в БД MySQL

SHOW DATABASES; //Просмотр
SHOW TABLES; //Просмотр текущей таблицы
USE dbname; //Переключиться
CREATE DATABASE dbname; //Создание
DROP DATABASE dbname; //Удаление
ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; //Кодировка


Управление привилегиями

GRANT ALL PRIVILEGES ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1' WITH GRANT OPTION; //Создание user1 со всеми правами как у root

GRANT ALL ON dbname.* TO 'user'@'localhost' IDENTIFIED BY 'password'; //Создание user с полными правами на БД dbname

GRANT INSERT ON dbname.* TO 'user'@'localhost'; //Права Вставь на бд dbname

REVOKE ALL ON userdb.* FROM 'user'@'localhost'; //Удаление прав на бд

USE mysql;
DELETE FROM USER WHERE USER='user'; //Полное удаление всех прав 

SHOW GRANTS; //Посмотреть список прав пользователя
SHOW GRANTS FOR 'user'@'localhost'; //Посмотреть права для user
SELECT USER, host FROM mysql.user; //Список всех пользователей
FLUSH PRIVILEGES; //Перезагрузка привилегий


Сжатие дампа с помощью bzip2:

bzip2 dbname.sql
mysqldump --opt -u root -p dbname | bzip2 > dbname.sql.bz2 //сжатие на лету


Восстановление БД из дамп файла:

mysql -u root -p dbname < dbname.sql
bzcat dbname.sql.bz2 | mysql -u root -p dbname //если дамп сжат


Перенос БД между серверами MySQL

mysqldump --opt -u root -p dbname | mysql --host=remote-host -u user -p -C dbname //-С указывает режим компрессии данных


Еще использование утилиты mysqladmin

mysqladmin -u root -p create dbname //создание
mysqladmin -u root -p drop dbname //удаление
mysqladmin -u root password PaS$wOrD //задать новый пароль
MySQL
нет комментариев

Linux команды для сервера

9 ноября 2015, 11:49

Команды управления для Apache

/etc/init.d/httpd start - запуск
/etc/init.d/httpd stop - стоп
/etc/init.d/httpd status - состояние
/etc/init.d/httpd restart - перезапуск

Команды управления для MySQL

/etc/init.d/mysqld start - запуск
/etc/init.d/mysqld stop - стоп
/etc/init.d/mysqld status - состояние
/etc/init.d/mysqld restart - перезапуск
Apache   CentOS   Linux   MySQL
нет комментариев

SQL объединения данных из нескольких таблиц

5 октября 2015, 21:07

Primary Key представляет собой столбец, который служит как уникальный идентификатор для строки в таблице. Значения в этом столбце должны быть уникальными и не может быть NULL.

Foreign Key представляет собой столбец, который содержит первичный ключ другой таблицы в базе данных. Он используется для идентификации конкретной строки в указанной таблице.

JOIN используются в SQL для объединения данных из нескольких таблиц.

INNER JOIN результатом будет объединения строк из разных таблиц, если условие соединения верно.

LEFT OUTER JOIN вернется каждую строку в левой таблице, и если условие соединения не выполняется, NULL значения используются для заполнения столбцов из правой таблицы.

AS это ключевое слово в SQL, что позволяет переименовать столбец или таблицу в наборе результатов, используя псевдоним.

Примеры запросов:

SELECT * FROM albums JOIN artists ON albums.artist_id = artists.id;

SELECT * FROM albums LEFT JOIN artists ON albums.artist_id = artists.id;

SELECT 
  albums.name AS 'Album',
  albums.year,
  artists.name AS 'Artist'
FROM albums JOIN artists ON albums.artist_id = artists.id WHERE albums.year > 2010;
JOIN   MySQL   SQL
нет комментариев
Ctrl + ↓ Ранее
Все права защищены © ArtyBondar, 2015—2017