MySQL — это система управления базами данных (СУБД) с открытым исходным кодом, поддерживаемая корпорацией Oracle. MySQL — это экономичный и мощный инструмент, который помогает управлять большими данными, обеспечивая при этом безопасность. Наша сегодняшняя тема — MySQL workbench, графический инструмент, который работает с серверами и базами данных MySQL. Этот инструмент можно использовать для визуализации модулей для создания, выполнения и оптимизации нескольких запросов.

Предпосылки

Перед установкой MySQL workbench убедитесь, что сервер MySQL установлен в вашей системе. В этом руководстве мы будем использовать сервер сообщества. Если в вашей системе уже установлен сервер сообщества MySQL, вы можете пропустить следующий раздел и перейти непосредственно к установке MySQL Workbench.

Установка сервера MySQL

Во-первых, мы позаботимся о том, чтобы все ваши системные пакеты были обновлены. Для этого выполните следующую команду.

$ sudo apt update

$ sudo apt update

Если ваши пакеты уже обновлены, вторую команду вводить не нужно.

Теперь, чтобы установить сервер MySQL, введите следующую команду.

$ sudo apt install mysql-server

Когда вы закончите установку, давайте настроим сервер сообщества. Перейдите в терминал и введите следующую команду.

$ sudo mysql

У каждого нового пользователя есть имя пользователя по умолчанию «root», которое не защищено. Чтобы установить пароль, введите следующую команду.

mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’;

Мы устанавливаем наш пароль «root». Вы можете установить его на что угодно, заменив «root» (после пароля BY) в приведенной выше команде.

После завершения этого шага введите следующую команду.

mysql> flush privileges;

Теперь вы можете выйти из MySQL, набрав exit.

mysql> exit;

После того, как вы выполните эти шаги, перейдите к установке MySQL Workbench.

Установка MySQL Workbench

Введите следующую команду, чтобы проверить, запущен ли ваш сервер сообщества MySQL.

$ systemctl status mysql.service

Двигайтесь вперед, если сервер активен , как видно выше; в противном случае вам, возможно, придется снова запустить сервер.

Введите следующую команду, чтобы загрузить и установить рабочую среду MySQL.

$ sudo apt install mysql-workbench

Использование MySQL Workbench

Перейдите в меню «Действия» и в поле поиска введите «MySQL workbench» и запустите его.

Вы увидите следующий экран.

Создать локальное соединение очень просто, и при условии, что у вас установлен сервер сообщества в расположении по умолчанию, эта задача займет всего несколько минут. Когда вы запускаете workbench в первый раз, вы получаете окно с несколькими вариантами. Для каждого локально установленного сервера автоматически создается плитка подключения. Чтобы добавить больше подключений, щелкните значок плюса рядом с основным заголовком MySQL Connections.

Если вы хотите добавить соединение, появится следующее диалоговое окно. Значения по умолчанию для простой локали предварительно вводятся.

Дайте вашему соединению имя в поле Имя соединения. Наиболее важной функцией здесь является имя хоста, которое является IP-адресом хоста сервера. Сервер MySQL использует порт 3306

При новой установке доступен только пользователь root с полным доступом ко всему на сервере. Здесь мы подключимся к корню и нажмем Test Connection.

После того, как вы введете пароль и нажмете Enter, появится следующее сообщение.

Теперь вернитесь на главный экран. Вы увидите еще одну плитку подключения с именем вашего подключения.

При нажатии на эту плитку откроется новое соединение.

В MySQL Workbench вы можете многое сделать. Вы можете создавать схемы и таблицы. Добавьте данные в эти таблицы или просмотрите конкретные данные.

Теперь на приведенном выше снимке экрана раздел с пометкой «1» — это место, где вы вводите свои SQL-запросы. Раздел с пометкой «2» — это боковая панель, где вы управляете своими базами данных и схемами. В разделе с пометкой «3» отображаются выходные данные SQL-запросов, которые вы ввели в разделе с пометкой «1».

Подключение к схеме

Вы можете либо создать новую схему, либо импортировать уже существующую. Как вы можете видеть на картинке ниже, есть только схема по умолчанию с именем sys. Давайте добавим нашу новую схему.

Шаг 1.

Чтобы загрузить схему из файла, перейдите на вкладку «Server» на верхней панели навигации и нажмите «Data Import».

Шаг 2

Появится следующее диалоговое окно. Установите флажок “Import from Self-Contained File” и просмотрите файл SQL из вашей системы. Теперь нажмите кнопку Start Import.

Теперь, если в вашей схеме уже есть данные, вам не нужно их импортировать. Но если ваша схема пуста и данные для вашей схемы находятся в другом файле, выполните следующий шаг.

Шаг 3

Нажмите «Data Import» на вкладке «Servers», установите флажок «Import from Self-Contained File» и укажите местоположение вашего файла данных. Теперь в Default Target Schema выберите целевую схему этих данных и нажмите кнопку Start Import. Этот шаг очень похож на шаг 2.

Просмотр таблиц

Нажмите кнопку обновления, чтобы увидеть импортированную схему. В нашем случае мы видим новую схему под названием «sakila».

Вы можете развернуть «sakila», чтобы просмотреть его содержимое. Вы можете просмотреть его таблицы, развернув раздел таблиц.

Соответствующий SQL-запрос для этого действия и результирующая таблица появляются в разделе запросов, если щелкнуть значок календаря справа от названия таблиц. Пока отображается сообщение об успешном завершении.

Вы можете написать запросы для просмотра определенных строк или столбцов, например, следующий запрос выбирает первый столбец имени из таблицы актеров, чей Actor_id равен 1.

Вывод

На этом мы заканчиваем наше руководство по установке и работе с MySQL Workbench. MySQL Workbench — это удобный инструмент для манипулирования и управления базами данных с использованием серверов MySQL. Как мы видели, пользователи могут писать SQL-запросы для выполнения различных операций со своей схемой. Надеюсь, это было познавательное чтение!

MySQL имеет много типов данных для хранения строковых данных в таблице. VARCHAR и TEXT — два из них. Оба могут хранить максимум 65535 символов, но между этими типами данных, описанными в этом руководстве, есть некоторые различия.

VARCHAR против TEXT:

Существует много различий между типами данных VARCHAR и TEXT. Различия между этими типами данных описаны ниже.

Особенности VARCHAR и TEXT

Типы данных VARCHAR и TEXT по-разному хранят данные в таблице базы данных. Различные особенности этих типов данных описаны ниже.

Декларация VARCHAR и TEXT

Способы объявления типов данных VARCHAR и TEXT объясняются ниже путем создания двух таблиц с полем типов данных VARCHAR и TEXT. Длина требуется для определения типа данных VARCHAR, а длина не требуется для определения типа данных TEXT.

Выполните следующую инструкцию CREATE DATABASE, чтобы создать базу данных с именем test_db.

CREATE DATABASE test_db;

Запустите следующую инструкцию запроса, чтобы выбрать базу данных test_db перед созданием таблицы.

USE test_db;

Выполните следующую инструкцию CREATE TABLE, чтобы создать таблицу клиентов, содержащую пять полей. Здесь тип данных поля id — целое число, а тип данных полей name, email, address и contact_no — varchar, содержащий значение длины.

CREATETABLE customers(

id INTNOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

address VARCHAR(300),

contact_no VARCHAR(15));

Выполните следующую инструкцию DESCRIBE, чтобы проверить структуру таблицы клиентов.

DESCRIBE customers;

Выполните следующий запрос INSERT, чтобы вставить три записи в таблицу клиентов.

INSERT INTO `customers` (`id`, `name`, `email`, `address`, `contact_no`) VALUES ('01', 'Andrey Ex', 'Andrey@gmail.com', '25, aaaaaaaaaaaaaa, Bangladesh ', '0191275634'),

       ('02', 'Sasha Destroyer', 'Sasha@gmail.com', 'bbbbbbbbbbbbbbbbbb', '01855342357'),

       ('03', 'Masha Bubba', 'Masha@hotmail.com', 'cccccccccccccc', NULL);

Запустите следующую инструкцию SELECT, чтобы прочитать все записи таблицы клиентов.

SELECT * FROM customers;

Запустите следующую инструкцию CREATE TABLE, чтобы создать таблицу сотрудников, содержащую пять полей. Тип данных поля id — целое число. Тип данных полей name, email и contact_no — varchar. Тип данных адресного поля — текст. Здесь для поля адреса не объявлено значение длины из-за текстового типа данных.

CREATETABLE employees(

id INTNOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

address TEXT,

contact_no VARCHAR(15));

Выполните следующую инструкцию DESCRIBE, чтобы проверить структуру таблицы сотрудников.

DESCRIBE employees;

Выполните следующий запрос INSERT, чтобы вставить три записи с одинаковым содержимым таблицы клиентов в таблицу сотрудников.

INSERT INTO `employees` (`id`, `name`, `email`, `address`, `contact_no`) VALUES ('01', 'Andrey Ex', 'Andrey@gmail.com', 'aaaaaaaaaaaaaaaaaaaaaaa', '0191275634'),

       ('02', 'Sasha Destroyer', 'Sasha@gmail.com', 'bbbbbbbbbbbbbbbbbbbbbb', '01855342357'),

       ('03', 'Masha Bubba', 'Masha@hotmail.com', 'ccccccccccccccccccc', NULL);

Выполните следующую инструкцию SELECT, чтобы прочитать все записи таблицы сотрудников.

SELECT * FROM employees;

Производительность VARCHAR и TEXT

Ранее уже упоминалось, что тип данных TEXT работает быстрее, чем тип данных VARCHAR. Вы должны выбрать базу данных, содержащую таблицы с полем типа данных VARCHAR и TEXT, и включить профилирование текущего сеанса MySQL, чтобы проверить, какой тип данных быстрее между VARCHAR и TEXT.

База данных была выбрана здесь во время создания таблицы. Таким образом, нам не нужно выбирать его снова. Выполните следующую инструкцию SET, чтобы включить профилирование SESSION.

SET SESSION profiling = 1;

Выполните следующий запрос SELECT, чтобы прочитать все записи таблицы клиентов.

SELECT * FROM customers;

Выполните следующий запрос SELECT, чтобы прочитать все записи таблицы сотрудников.

SELECT * FROM employees;

Выполните следующую команду, чтобы проверить производительность двух предыдущих запросов SELECT.

SHOW PROFILES;

Следующий вывод появится после выполнения команды SHOW PROFILES. Согласно выходным данным, запрос SELECT для таблицы клиентов содержит поле адреса типа данных VARCHAR, требуемое 0,00101000 секунд, а запрос SELECT для таблицы сотрудников, содержащей поле адреса типа данных TEXT, требует 0,00078125 секунд. Это доказывает, что тип данных TEXT работает быстрее, чем тип данных VARCHAR для тех же данных.

Вы можете сравнить выходные данные следующих двух запросов, чтобы подробно проверить производительность типов данных VARCHAR и TEXT.

SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=1;



SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2;

Вывод:

Оба типа данных VARCHAR и TEXT важны для создания таблиц в базе данных MySQL. Различия между этими типами данных были должным образом объяснены в этом руководстве с примерами, чтобы помочь пользователям MySQL выбрать правильный тип данных поля таблицы во время создания таблицы.

В MySQL существует множество встроенных функций для выполнения различных типов операций со строковыми данными. Иногда требуется разделить строковые данные на основе любого разделителя при выполнении запроса SELECT. Функция SUBSTRING_INDEX() используется для разделения строковых данных, полученных запросом, на основе определенного разделителя. В этой статье обсуждается способ использования этой функции в запросе SELECT.

Синтаксис SUBSTRING_INDEX():

Функция SUBSTRING_INDEX() принимает три аргумента и возвращает значение подстроки. Синтаксис функции SUBSTRING_INDEX() представлен ниже:

string SUBSTRING_INDEX(string, delimiter, count);

• Первый аргумент — это строковое значение, которое будет разделено.
• Второй аргумент — это разделитель, который будет использоваться для разделения строкового значения.
• Третий аргумент определяет количество вхождений разделителя. Оно может быть положительным или отрицательным. Если значение третьего аргумента положительное, то значение подстроки будет возвращено слева. Если значение третьего аргумента отрицательное, значение подстроки будет возвращено справа.

Разделить строку с помощью функции SUBSTRING_INDEX():

В этой части статьи показаны различные варианты использования функции SUBSTRING_INDEX().

Пример 1: Разделить строку на основе положительного значения счетчика

В этой части руководства показаны четыре варианта использования функции SUBSTRING_INDEX() с положительным значением счетчика и другим разделителем.

Запустите следующую инструкцию SELECT, которая использует функцию SUBSTRING_INDEX() с положительным значением счетчика, 1, и пробелом в качестве разделителя. Основная строка «Welcome to AndreyEx» состоит из трех слов. Таким образом, в выводе будет напечатано первое слово строки.

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', ' ', 1);

Запустите следующую инструкцию SELECT, которая использует функцию SUBSTRING_INDEX() с положительным значением счетчика, 2, и символом «o» в качестве разделителя. Основная строка «Welcome to AndreyEx» содержит два раза символ «o». Второй раз «o» появилось во втором слове «to». Таким образом, вывод будет «Welcome to».

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'o', 2);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'to', 1);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'to', 3);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', ' ', -1);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'e', -2);

SELECT SUBSTRING_INDEX('Welcome to AndreyEx', 'in', -2);

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE customers(

id INT NOT NULL PRIMARY KEY,

name VARCHAR(30) NOT NULL,

email VARCHAR(50),

contact_no VARCHAR(15));

INSERT INTO `customers` (`id`, `name`, `email`, `contact_no`) VALUES

('001', 'Andrey Ex', 'info@andreyex.ru', '+8 000 00 00 000'),

('002', 'Maxs Destroyer', 'Max***@yandex.com', '+8 000 00 00 000'),

('003', 'Alex Annihilator', 'Alex***@yandex.com', '+8 000 00 00 000');

SELECT * FROM customers;

SELECT

id, SUBSTRING_INDEX(name,' ',1) AS `First Name`, email, SUBSTRING_INDEX(contact_no,'+88',-1) AS Phone

FROM customers;

Неактивное шифрование предотвращает доступ злоумышленника к зашифрованным данным, хранящимся на диске, даже если у него есть доступ к системе. Базы данных с открытым исходным кодом MySQL и MariaDB теперь поддерживают функцию шифрования в состоянии покоя, которая отвечает требованиям нового законодательства ЕС о защите данных. Шифрование MySQL в состоянии покоя немного отличается от MariaDB, поскольку MySQL обеспечивает шифрование только для таблиц InnoDB. В то время как MariaDB также предоставляет возможность шифровать файлы, такие как журналы повторного выполнения, журналы медленных операций, журналы аудита, журналы ошибок и т. д. Однако оба они не могут зашифровать данные в ОЗУ и защитить их от вредоносного корня.

В этой статье мы научимся настраивать шифрование на уровне базы данных для MariaDB.

Начиная

Для шифрования данных в состоянии покоя требуется плагин шифрования вместе с управлением ключами. Плагин шифрования отвечает за управление ключом шифрования, а также за шифрование/дешифрование данных.

MariaDB предоставляет три решения для управления ключами шифрования, поэтому то, как ваши базы данных управляют ключом шифрования, зависит от используемого вами решения. В этом руководстве будет продемонстрировано шифрование на уровне базы данных с помощью решения MariaDB File Key Management. Однако этот плагин не поддерживает функцию ротации ключей.

Если вы используете сервер LAMP, файлы для добавления этого плагина находятся в каталоге «/opt/lamp». В противном случае изменения вносятся в папку «/etc/mysql/conf.d».

Создание ключей шифрования

Перед шифрованием базы данных с помощью плагина управления ключами файлов нам необходимо создать файлы, содержащие ключи шифрования. Мы создадим файл с двумя частями информации. Это ключ шифрования в шестнадцатеричном формате вместе с 32-битным идентификатором ключа.

Мы создадим новую папку «keys» в каталоге « /etc/mysql/» и воспользуемся утилитой OpenSSL для случайной генерации трех шестнадцатеричных строк и перенаправления вывода в новый файл в папке ключей. Введите следующие команды:

ubuntu@ubuntu:~$ sudo mkdir /etc/mysql/keys

ubuntu@ubuntu:~$ echo -n "1;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "2;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

ubuntu@ubuntu:~$ echo -n "3;"$openssl rand hex 32 > /etc/mysql/keys/enc_keys"

Где 1,2,3 — ключевые идентификаторы; мы включаем их, чтобы создать ссылку на ключи шифрования, используя переменную innodb_default_encryption_key_id в MariaDB. Выходной файл будет выглядеть так:

1;01495ba35e1c9602e14e40bd6de41bb8

2;3cffa4a5d288e90108394dbf639664f8

3;9953297ed1a58ae837486318840f5f1d

ubuntu@ubuntu:~$ openssl rand -hex 192> /etc/mysql/keys/enc_paswd.key

ubuntu@ubuntu:~$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/keys/enc_paswd.key -in /etc/mysql/keys/enc_key.txt -out /etc/mysql/keys/enc_key.enc && sudo rm /etc/mysql/keys/enc_key.txt

[sqld]



#File Key Management Plugin

plugin_load_add=file_key_management

file_key_management = ON file_key_management_encryption_algorithm=aes_cbc file_key_management_filename = /etc/mysql/keys/enc_keys.enc

file_key_management_filekey = /etc/mysql/keys/enc_paswd.key



# InnoDB/XtraDB Encryption Setup

innodb_default_encryption_key_id = 1

innodb_encrypt_tables = ON

innodb_encrypt_log = ON

innodb_encryption_threads = 4



# Aria Encryption Setup

aria_encrypt_tables = ON



# Temp & Log Encryption

encrypt-tmp-disk-tables = 1

encrypt-tmp-files = 1

encrypt_binlog = ON

sudo chown -R mysql:root /etc/mysql/keys

sudo chmod 500 /etc/mysql/keys/

sudo chown mysql:root /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc



sudo chmod 600 /etc/mysql/keys/enc_paswd.key /etc/mysql/keys/enc_key.enc

sudo service mysql restart

Таблица базы данных может хранить различные типы данных, и иногда нам нужно преобразовать данные на уровне строк в данные на уровне столбцов. Эту проблему можно решить с помощью функции PIVOT(). Эта функция используется для поворота строк таблицы в значения столбцов. Но эта функция поддерживается очень немногими серверами баз данных, такими как Oracle или SQL Server. Если вы хотите выполнить ту же задачу в таблице базы данных MySQL, вам необходимо написать запрос SELECT, используя оператор CASE для поворота строк в столбцы. В статье показано, как выполнять задачу функции PIVOT() в связанных таблицах базы данных MySQL.

Предпосылка:

Вам необходимо создать базу данных и несколько связанных таблиц, в которых строки одной таблицы будут преобразованы в столбцы, такие как функция PIVOT(). Выполните следующие операторы SQL, чтобы создать базу данных с именем unidb и создать три таблицы с именами ‘students’, ‘courses’ и ‘result’. студенты и таблицы результатов будут связаны отношением «один ко многим», а таблицы курсов и результатов будут связаны здесь отношением «один ко многим». Запрос CREATE таблицы результатов содержит два ограничения внешнего ключа для полей, std_id и course_id.

CREATE DATABASE unidb;

USE unidb;



CREATE TABLE students (

id INT PRIMARY KEY,

name varchar(50) NOT NULL,

department VARCHAR(15) NOT NULL);



CREATE TABLE courses (

course_id VARCHAR(20) PRIMARY KEY,

name varchar(50) NOT NULL,

credit SMALLINT NOT NULL);



CREATE TABLE result(

std_id INT NOT NULL,

course_id VARCHAR(20) NOT NULL,

mark_type VARCHAR(20) NOT NULL,

marks SMALLINT NOT NULL,

FOREIGN KEY (std_id) REFERENCES students(id),

FOREIGN KEY (course_id) REFERENCES courses(course_id),

PRIMARY KEY (std_id, course_id, mark_type));

Вставьте некоторые записи в студентов, курсы и таблицы результатов. Значения должны быть вставлены в таблицы в соответствии с ограничениями, установленными во время создания таблицы.

INSERT INTO students VALUES

( '1937463', 'AndreyEx', 'CSE'),

( '1937464', 'Destroyer', 'CSE'),

( '1937465', 'Alex', 'CSE'),





INSERT INTO courses VALUES

( 'CSE-401', 'Объектно-ориентированное программирование', 3),

( 'CSE-403', 'Структура данных', 2),

( 'CSE-407', 'Программирование Unix', 2);



INSERT INTO result VALUES

( '1937463', 'CSE-401','Внутренний экзамен' ,15),

( '1937463', 'CSE-401','Промежуточный экзамен' ,20),

( '1937463', 'CSE-401','Итоговый экзамен', 35),

SELECT * FROM result;

SELECT result.std_id, result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен"

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

SELECT result.std_id,result.course_id,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Внутренний экзамен",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Промежуточный экзамен",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Итоговый экзамен",

SUM( result.marks) as Total

FROM result

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

SELECT students.name as `Student Name` , courses.name as `Course Name`,

MAX(CASE WHEN result.mark_type = "Внутренний экзамен" THEN result.marks END) "Вн",

MAX(CASE WHEN result.mark_type = "Промежуточный экзамен" THEN result.marks END) "Пр",

MAX(CASE WHEN result.mark_type = "Итоговый экзамен" THEN result.marks END) "Финал",

SUM( result.marks) as Total

FROM students, courses, result

WHERE result.std_id = students.id and result.course_id= courses.course_id

GROUP BY result.std_id, result.course_id

ORDER BY result.std_id, result.course_id ASC;

После установки phpMyAdmin при входе в систему с root вы получаете следующую ошибку

Вход в систему root был заблокирован по соображениям безопасности.

1698 - Access denied for user 'root'@'localhost'

mysqli_real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'

Нам нужно самостоятельно создать пользователя-администратора.

1 Используйте Терминал для входа в mysql как root, если вы создали пароль root для mysql, если он пуст, используйте следующую команду

sudo mysql -p -u root

2 Создайте нового пользователя с новым паролем, здесь мы используем andreyex и testpassword в качестве примера

CREATE USER 'andreyex'@'localhost' IDENTIFIED BY 'testpassword';

Предоставить новому пользователю права суперпользователя

GRANT ALL PRIVILEGES ON *.* TO 'andreyex'@'localhost' WITH GRANT OPTION;

Теперь вы можете использовать новый логин для входа в phpMyAdmin.