Руководство для начинающих mysql

Данный документ поможет пользователю в настройке и использовании MySQL.

Введение

MySQL — это популярный сервер баз данных, используемый в разных приложениях. SQL означает язык структурированных запросов — (S)tructured (Q)uery (L)anguage, который MySQL использует для коммуникации с другими программами. Сверх того, MySQL имеет свои собственные расширенные функции SQL для того чтобы обеспечить пользователям дополнительный функционал. В этом документе мы рассмотрим как провести первоначальную установку MySQL, настроить базы данных и таблицы, и создать новых пользователей. Давайте начнем с установки.

Установка MySQL

Сначала убедитесь что MySQL установлен на вашу систему. В случае если вам требуется определенная функциональность MySQL, убедитесь, что установлены необходимые USE-флаги, так как они помогут в тонкой настройке инсталляции.

По завершении установки, вы увидите следующее уведомление:

You might want to run:
"emerge --config =dev-db/mysql-[version]"
if this is a new install.

Так как это новая установка, мы запустим эту команду. Вам надо нажать ENTER по запросу во время конфигурации базы данных MySQL. В процессе конфигурации устанавливается основная база данных MySQL, которая содержит служебную информацию, такую как базы данных, таблицы, пользователи, разрешения и т.д. В процессе конфигурации рекомендуется чтобы вы изменили свой пароль root так быстро, как это возможно. Мы определенно это сделаем, иначе кто-нибудь сможет волей случая появиться и взломать сервер MySQL, настроенный по умолчанию.

 * MySQL DATADIR is /var/lib/mysql
 * Press ENTER to create the mysql database and set proper
 * permissions on it, or Control-C to abort now...
 
   Preparing db table
   Preparing host table
   Preparing user table
   Preparing func table
   Preparing tables_priv table
   Preparing columns_priv table
   Installing all prepared tables
 
   To start mysqld at boot time you have to copy support-files/mysql.server
   to the right place for your system
 
   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
   To do so, issue the following commands to start the server
   and change the applicable passwords:
 
   /etc/init.d/mysql start
   /usr/bin/mysqladmin -u root -h pegasos password 'new-password'
   /usr/bin/mysqladmin -u root password 'new-password'
   Depending on your configuration, a -p option may be needed
   in the last command. See the manual for more details.

Заметка
Если предыдущая команда не выполнится из-за того, что имя хоста установлено в localhost, измените его на другое имя, например gentoo. Обновите файл /etc/conf.d/hostname и перезапустите /etc/init.d/hostname.

Некоторая нехарактерная для ebuild-файлов информация MySQL удалена отсюда, чтобы содержать этот документ настолько последовательным, насколько возможно.

Важно
Начиная с mysql-4.0.24-r2, пароли вводятся во время этапа конфигурации, что делает пароль root более надежным.

Сценарий конфигурации уже вывел команды, которые нам нужно запустить, чтобы настроить наш пароль, поэтому нам сейчас надо их выполнить.

Если вы используете OpenRC, выполните данную команду:

 * Re-caching dependency info (mtimes differ)...
 * Starting mysqld (/etc/mysql/my.cnf) ...        [ ok ]

Если вы используете systemd, вместо этого используйте следующую команду:

With >=dev-db/mariadb-10.1.18, use:

После этого установите пароль root:

Теперь вы можете проверить, что пароль root был успешно настроен, попытавшись войти на MySQL-сервер:

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Параметр -u указывает пользователя, который будет выполнять вход. Параметр -h указывает хост. Обычно это будет localhost, если только вы не настраиваете удаленный сервер. И, наконец, -p сообщает клиенту mysql что вы будете вводить пароль для доступа к базе данных. Обратите внимание на приглашение mysql>. Это то место, где вы будете вводить все ваши команды. Теперь, когда мы в командной строке mysql в качестве пользователя root, мы можем начать настраивать нашу базу данных.

Важно
Установка mysql по умолчанию приемлема для систем разработки. Для более безопасных значений по умолчанию можно запустить /usr/bin/mysql_secure_installation

Настройка Базы Данных

Создание Базы Данных

Мы вошли и приглашение mysql показано на экране. Сначала, давайте рассмотрим базы данных, которые у нас имеются в настоящий момент. Чтобы это сделать, мы используем команду SHOW DATABASES.

+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.09 sec)

Важно
Пожалуйста, запомните что команды MySQL должны оканчиваться точкой с запятой — ;

Вопреки тому факту, что тестовая база данных уже создана, мы собираемся создать нашу собственную. Базы данных создаются с использованием команды CREATE DATABASE. Мы создадим одну из них под названием gentoo.

Query OK, 1 row affected (0.08 sec)

Ответ позволяет нам узнать, что команда была выполнена без ошибок. В этом случае, одна строка была изменена. Это является отсылкой к основной базе данных mysql, которая содержит список всех баз данных. Вам не нужно сильно беспокоиться о второстепенных деталях. Последнее число является характеристикой того, насколько быстро был выполнен запрос. Мы можем проверить, что база данных была создана, запустив команду SHOW DATABASES снова.

+----------+
| Database |
+----------+
| gentoo   |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

В самом деле, наша база данных была создана. Для того чтобы работать с созданием таблиц для нашей новой базы данных gentoo, нам надо выбрать ее в качестве текущей базы данных. Чтобы это сделать, мы используем команду USE. Команда USE принимает имя базы данных, которую вы хотите использовать в качестве текущей. Другой возможностью является ее установка в командной строке после параметра -D. Давайте продолжим и переключимся к базе данных gentoo.

Database changed

Сейчас, текущей базой данных является созданная нами ранее база данных gentoo. Теперь, когда мы ей пользуемся, мы можем начать создавать таблицы и заполнять их информацией.

Работа с таблицами в MySQL

Создание таблицы

В структуру MySQL входят базы данных, таблицы, записи, и поля. Базы данных объединяют таблицы, таблицы объединяют записи, записи объединяют поля, которые содержат действительную информацию. Такая структура позволяет пользователям выбирать как они хотят обращаться к своей информации. На данный момент, мы разобрались с базами данных, теперь давайте поработаем с таблицами. Во-первых, таблицы могут быть перечислены, подобно базам данных, с использованием команды SHOW TABLES. Сейчас, в базе данных gentoo не имеется таблиц, как и показывает нам следующая команда:

Empty set (0.00 sec)

Это означает нам надо создать какие-либо таблицы. Чтобы это сделать, мы используем команду CREATE TABLE. Однако, эта команда достаточно отличается от простой команды CREATE DATABASE тем что принимает список аргументов. Формат команды следует ниже:

CREATE TABLE [table_name] ([field_name] [field_data_type]([size]));

table_name — это имя таблицы, которую мы хотим создать. В данном случае, давайте создадим таблицу с именем developers . Эта таблица будет хранить имя разработчика, email адрес и его должность.

field_name будет хранить имя поля. В этом случае мы имеем три требуемых имени: имя разработчика, email адрес, и должность.

field_data_type — это то, какой тип информации будет сохранен. Различные доступные форматы могут быть найдены по адресу MySQL Column Types Page . Для наших целей, мы будем использовать тип данных VARCHAR для всех наших полей. VARCHAR — это один из простейших типов данных, когда дело касается работы со строками.

size — это то, как много данных будет хранить одно поле. В нашем случае, мы будем использовать 128. Это означает, что поле будет иметь данные типа VARCHAR, которые занимают 128 байт. В настоящий момент, вы можете спокойно думать об этом как о 128 символах, хотя существует более техническое объяснение, которое вам предоставит сайт, указанный выше. Теперь, когда мы знаем как мы создадим таблицу, давайте это сделаем.

Query OK, 0 rows affected (0.11 sec)

Похоже, наша таблица была успешно создана. Давайте проверим это командой SHOW TABLES:

+------------------+
| Tables_in_gentoo |
+------------------+
| developers       |
+------------------+
1 row in set (0.00 sec)

Да, наша таблица существует. Однако, она, по-видимому, не имеет никакой информации о типах полей, которые мы создали. Для этого, мы используем команду DESCRIBE (или, кратко, DESC), которая принимает имя таблицы в качестве своих аргументов. Давайте посмотрим что она нам даст для нашей таблицы developers.

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Показаны разные поля и их типы. Также показано несколько дополнительных атрибутов, которые находятся за рамками данного руководства. Пожалуйста, обратитесь за подробностями к Справочному Руководству MySQL . Теперь у нас есть таблица для работы с ней. Давайте продолжим и заполним ее.

Заполнение Базы Данных MySQL

Мы заполним таблицу (или добавим в нее данные), используя команду INSERT. Подобно команде CREATE TABLE, она также имеет особый формат:

INSERT INTO table (col1, col2, ...) VALUES('value1', 'value2', ...);

Эта команда используется для вставки записи в таблицу. table содержит таблицу MySQL, в которую мы хотим ввести информацию. Имя таблицы может сопровождаться списком столбцов для вставки данных, VALUES() хранит значения, которые вы хотите вставить в таблицу. Вы можете опустить список столбцов, если вы вставляете значение в каждый из них и если Вы пишете значения в том же порядке, в котором определены столбцы. В данном случае, мы хотим вставить данные в таблицу developers. Давайте добавим примеры записей:

Query OK, 1 row affected (0.06 sec)
## (Если вы не знаете порядок столбцов в таблице или хотите вставить неполную запись)
mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane Doe');
Query OK, 1 row affected (0.01 sec)

В соответствии с полученным результатом, кажется, запись была вставлена правильно. Что если мы хотим ввести больше, чем просто одну запись? Это тот случай, когда команда LOAD DATA вступает в действие. Она загружает записи из файла, разделенного символами табуляции. Давайте это попробуем, отредактировав файл в домашнем каталоге пользователя и добавив в него записи. Мы назовем этот файл records.txt . Здесь приведен пример:

John Doe	johndoe@gentoo.org	portage
Chris White	chriswhite@gentoo.org	documentation
Sam Smith	samsmith@gentoo.org	amd64

Важно
Убедитесь, что вы знаете, с какими данными вы будете работать. Очень небезопасно использовать LOAD DATA, когда вы не уверены насчет содержимого файла!

Команда LOAD DATA имеет в каком-то смысле пространное определение, но здесь мы используем ее самую простую форму.

LOAD DATA LOCAL INFILE '/path/to/filename' INTO TABLE table;

/path/to/filename — это каталог и имя файла, которые будут использоваться. table — это имя нашей таблицы. В этом случае, наш файл — ~/records.txt, а имя таблицы — developers.

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

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

Сработало хорошо. Однако, это просто вставляет записи, и не дает вам какого-либо контроля над MySQL. Множество веб-приложений используют скрипты sql для того чтобы настроить MySQL быстро и легко. Если вы хотите использовать скрипты sql, вам нужно запустить mysql в пакетном режиме, или использовать файл в качестве источника команд. Здесь приведен пример запуска mysql в пакетном режиме:

Как и в случае с LOAD DATA, убедитесь, что вы можете сказать что делает файл sqlfile. Невозможность этого может скомпрометировать вашу базу данных! Другим способом выполнения этого является использование команды source. Эта команда запустит команды из файла sql, находясь в интерактивном режиме mysql. Здесь показано как использовать sql file в качестве источника команд:

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

Запросы к Таблицам MySQL

Запросы являются одной из основных функций любой базы данных SQL. Они помогают превратить данные в таблицах во что-то полезное. Большинство запросов выполняются командой SELECT . Команда SELECT — довольно сложна, и мы рассмотрим только три основных формы этой команды в данном документе.

## (Выбрать все записи в таблице)
SELECT * FROM table;
## (Выбрать определенные записи в таблице)
SELECT * FROM table WHERE field=value;
## (Выбрать определенные поля)
SELECT field1,field2,field3 FROM table [WHERE field=value];

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

+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Мы видим как данные, которые мы добавили с INSERT, так и вставленные LOAD DATA. Теперь, давайте предположим что мы просто хотим посмотреть запись для Chris White. Мы можем сделать это с помощью второй формы команды select, как показано ниже.

+-------------+-----------------------+---------------+
| name        | email                 | job           |
+-------------+-----------------------+---------------+
| Chris White | chriswhite@gentoo.org | documentation |
+-------------+-----------------------+---------------+
1 row in set (0.08 sec)

Как предполагалось, выбрана отдельная запись, которую мы искали. Теперь, предположим, мы только хотели узнать должность и email адрес данной персоны, но не ее имя. Мы можем это выполнить с помощью третьей формы SELECT , как здесь и показано.

+-----------------------+---------------+
| email                 | job           |
+-----------------------+---------------+
| chriswhite@gentoo.org | documentation |
+-----------------------+---------------+
1 row in set (0.04 sec)

Этот способ выбора намного легче в управлении, особенно с большими объемами информации, как мы увидим позже. А сейчас, будучи mysql пользователем root, мы обладаем неограниченными разрешениями делать с базой данных MySQL то, что мы захотим. В среде выполнения сервера, наличие пользователя с такими привилегиями может вызвать немало проблем. Для того, чтобы контролировать кто и что может делать с базами данных, мы установим привилегии.

Привилегии MySQL

Привилегии — это то, каким доступом обладают пользователи к базам данных, таблицам, почти ко всему. На данный момент в базе данных gentoo, учетная запись MySQL root — это единственная учетная запись, которая может получить к ней доступ, учитывая ее разрешения. Теперь, давайте создадим двух обычных пользователей, guest и admin, которые получат доступ к базе данных gentoo и будут работать с информацией, хранящейся в ней. Учетная запись guest будет ограниченной в правах. Все, что он сможет сделать, это получить информацию из базы данных, и только это. admin будет иметь те же самые права на управление, что и root, но только к базе данных gentoo (а не основным базам данных mysql). Перед тем как начать, давайте рассмотрим подробнее этот, в некотором смысле, упрощенный формат команды GRANT.

Creating users

The CREATE USER SQL statement will define users and set the authentication method, commonly by password but other plugins may be available.

An example CREATE USER command is:

CREATE USER '[user]'@'[host]' IDENTIFIED BY '[password]';

user is the name of the user and host is the hostname the user will be accessing from. In most cases, this will be localhost. To create our users for this example:

(admin)

(guest)

Важно
A host of ‘localhost’ does not mean DNS localhost (127.0.0.1) to MySQL. Instead, it refers to the UNIX socket connection and not TCP/IP.

Предоставление Привилегий Командой GRANT

Let’s have a closer look at this somewhat simplified format of the GRANT command.

GRANT [privileges] ON database.* TO '[user]'@'[host]' IDENTIFIED BY '[password]';

Во-первых, мы имеем привилегии, которые мы хотим назначить. Исходя из того что мы выучили на данный момент, имеются несколько привилегий, которые вы можете установить:

• ALL — Дает полный контроль базы данных со всеми привилегиями
• CREATE — Позволяет пользователям создавать таблицы
• SELECT — Позволяет пользователям делать запросы к таблицам
• INSERT — Позволяет пользователям вставлять данные в таблицу
• SHOW DATABASES — Позволяет пользователям просматривать список баз данных
• USAGE — Пользователь не имеет привилегий
• GRANT OPTION — Позволяет пользователям предоставлять привилегии

Для пользователя admin, подойдет уровень ALL. Для пользователя guest, SELECT будет достаточно для доступа только на чтение. database — это база данных, над которой пользователь, как мы того желаем, должен иметь эти разрешения. В этом примере, базой данных является gentoo. .* означает все таблицы. Если бы вы хотели, вы могли бы установить права доступа для каждой из таблиц. user — это имя пользователя, а host — имя хоста, с которого пользователь будет получать доступ. В большинстве случаев, это будет localhost. И наконец, password — это пароль пользователя. Учитывая эту информацию, давайте продолжим и создадим наших пользователей.

Теперь, когда мы настроили наших пользователей, давайте их протестируем. Сначала, мы завершим работу mysql, введя quit в командной строке:

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

Проверка Пользовательских Разрешений

Сейчас мы должны попытаться войти в качестве пользователя guest. В настоящий момент, пользователь guest имеет только привилегии SELECT. Это, просто-напросто, сводится к способности поиска в базе данных, и ничему другому. Продолжайте и войдите как пользователь guest.

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Теперь мы должны протестировать пользовательские ограничения. Давайте переключимся к базе данных gentoo:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

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

ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

Как видите, эта функция не сработала, так как наш пользователь не имеет соответствующих прав доступа. Однако, одной из привилегий, которую мы предоставили, является выражение SELECT. Давайте попробуем:

+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Команда завершилась успешно, и мы мельком увидели, что могут делать пользовательские разрешения. Мы, однако же, также создали и учетную запись admin. Она была создана для того чтобы показать, что даже пользователи, которым предоставлены все права, все же могут иметь ограничения. Завершите работу MySQL и войдите под учетной записью admin.

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Для начала, мы попытаемся создать новую базу данных от учетной записи admin. Этот пользователь будет иметь права доступа схожие с учетной записью root в MySQL, и будет способен выполнить любые изменения выбранной базы данных gentoo. Это протестирует права доступа данного пользователя к главной базе данных MySQL. Вспомните, что ранее мы установили разрешения только для определенной базы данных.

ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'

В самом деле, пользователь admin не может создавать базы данных в основной базе данных MySQL, вопреки всем своим разрешениям к базе данных gentoo. Однако, мы все еще способны использовать учетную запись admin для изменения базы данных gentoo, как показано на этом примере вставки данных.

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)

Пользователь admin может получить доступ к базе данных по желанию. Иногда, мы должны избавиться от пользовательских разрешений. Примером может быть все что угодно, от пользователя, вызывающего проблемы, до уволенного сотрудника. Давайте рассмотрим как отключить пользовательские разрешения командой REVOKE.

Удаление Прав Доступа Пользователя Командой REVOKE

Команда REVOKE позволяет нам запретить доступ пользователю. Мы можем запретить или все права на доступ, или определенные права. В действительности, формат весьма схож с командой GRANT .

REVOKE [privileges] ON database.* FROM '[user]'@'[host]';

Здесь, параметры объясняются в разделе команды GRANT . Однако же, в этом разделе, мы собираемся запретить пользователю доступ полностью. Давайте предположим, мы обнаружили, что учетная запись guest вызывает некоторые проблемы с безопасностью. Мы решаем отозвать все привилегии. Мы заходим под учетной записью root и делаем то, что необходимо.

Query OK, 0 rows affected (0.00 sec)

Заметка
В данном случае, права доступа пользователя достаточно просты, поэтому их удаление из базы данных не является проблемой. Однако, в более общем случае, вы, скорее всего, использовали бы *.* вместо gentoo.* для того чтобы удалить права доступа пользователя ко всем другим базам данных.

Давайте теперь выйдем и попытаемся зайти в качестве пользователя guest.

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.25
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Хотя мы и были способны выполнить вход, наши права доступа к gentoo теперь исчезли.

ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

И наш проблемный пользователь больше не может получить доступ к базе данных gentoo. Пожалуйста, заметьте, что пользователь все еще может выполнить вход. Это потому что он остается в основной базе данных MySQL. Давайте рассмотрим как полностью удалить учетную запись командой DELETE и взглянем на таблицу пользователей MySQL.

Удаление Учетных Записей с Использованием DELETE

Таблица пользователей MySQL является перечислением всех пользователей и информации о них. Убедитесь, что вы выполнили вход под root. Затем продолжайте и используйте основную базу данных MySQL.

DROP USER will delete the record in the user table and all privilege tables. Let’s go ahead and do that:

Query OK, 1 row affected (0.07 sec)

Кажется, сработало на отлично. Давайте это протестируем, выйдя и попытаясь войти как пользователь guest.

Enter password:
ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)

Наш пользователь успешно удален!

Заключение

В то время как это руководство сфокусировано, в основном, на настройке MySQL из командной строки, доступно несколько альтернатив с графическим интерфейсом:

Это завершает вводное руководство к MySQL. Я надеюсь это дало вам лучшее понимание основ MySQL и настройки базы данных.

This page is based on a document formerly found on our main website gentoo.org.
The following people contributed to the original document: Chris White, Shyam Mani, Xavier Neys
They are listed here because wiki history does not allow for any external attribution. If you edit the wiki article, please do not add yourself here; your contributions are recorded on each article’s associated history page.

MySQL Tutorial is the second blog in this blog series. In the previous blog ‘What is MySQL‘ , I introduced you to all the basic terminologies that you needed to understand before you get started with this relational database. In this blog of MySQL, you will be learning all the operations and command that you need to explore your databases.

The topics covered in this blog are mainly divided into 4 categories: DDL, DML, DCL & TCL.

• The DDL (Data Definition Language) consists of those commands which are used to define the database. Example: CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME.
• The DML (Data Manipulation Language) commands deal with the manipulation of data present in the database. Example: SELECT, INSERT, UPDATE, DELETE.
• The DCL (Data Control Language) commands deal with the rights, permissions and other controls of the database system. Example: GRANT, INVOKE
• The TCL ( Transaction Control Language) consists of those commands which mainly deal with the transaction of the database.

Apart from the commands, following are the other topics covered in the blog:

• Different Types Of Keys In Database
• Constraints Used In Database
• Nested Queries
• Joins
• Set Operations

We are going to cover each of these categories one by one.

In this blog on MySQL Tutorial, I am going to consider the below database as an example, to show you how to write commands.

Table 1: Sample Database – MySQL Tutorial

So, let’s get started now!

MySQL Tutorial: Data Definition (DDL) Commands

This section consists of those commands, by which you can define your database. The commands are:

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

Now, before I start with the commands, let me just tell you the way to mention the comments in MySQL.

Comments

Like any other programming language, there are mainly two types of comments.

• Single-Line Comments – The single line comments start with ‘–‘. So, any text mentioned after — till the end of the line will be ignored by the compiler.

Example:

--Select all:
SELECT * FROM Students;

• Multi-Line Comments – The Multi-line comments start with /* and end with */. So, any text mentioned between /* and */ will be ignored by the compiler.

Example:

/*Select all the columns
of all the records
in the Students table:*/
SELECT * FROM Students;

Now, that you know how to mention comments in MySQL, let’s continue with the DDL commands.

CREATE  

The create statement is used to either create a schema, tables or an index.

The ‘CREATE SCHEMA’ Statement

This statement is used to create a database.

Syntax:

CREATE SCHEMA Database_Name;

Example:

CREATE SCHEMA StudentsInfo;

The ‘CREATE TABLE’ Statement

This statement is used to create a new table in a database.

Syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Example:

CREATE TABLE Students
(
StudentID int,
StudentName varchar(255),
ParentName varchar(255),
Address varchar(255),
PostalCode int,
City varchar(255)
);

The ‘CREATE TABLE AS’ Statement

This statement is used to create a new table from an existing table. So, this table gets the same column definitions as that of the existing table.

Syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Example:

CREATE TABLE ExampleTable AS
SELECT Studentname, Parentname
FROM Students;

ALTER

The ALTER command is used to add, modify or delete constraints or columns.

The ‘ALTER TABLE’ Statement

This statement is used to either add, modify or delete constraints and columns from a table.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Students
ADD DateOfBirth date;

DROP

The DROP command is used to delete the database, tables or columns.

The ‘DROP SCHEMA’ Statement

This statement is used to drop the complete schema.

Syntax:

DROP SCHEMA schema_name;

Example:

DROP SCHEMA StudentsInfo;

The ‘DROP TABLE’ Statement

This statement is used to drop the entire table with all its values.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE table_name;

TRUNCATE

This statement is used to delete the data which is present inside a table, but the table doesn’t get deleted.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Students;

RENAME

This statement is used to rename one or more tables.

Syntax:

RENAME TABLE 

     tbl_name TO new_tbl_name

     [, tbl_name2 TO new_tbl_name2] ...

Example:

RENAME Students TO Infostudents;

Now, before I move into the further sections, let me tell you the various types of Keys and Constraints that you need to mention while manipulating the databases.

MySQL Tutorial: Different Types Of Keys In Database

There are mainly 5 types of Keys, that can be mentioned in the database.

• Candidate Key – The minimal set of attributes which can uniquely identify a tuple is known as a candidate key. A relation can hold more than a single candidate key, where the key is either a simple or composite key.
• Super Key – The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key is a superkey, but vice-versa isn’t true.
• Primary Key – A set of attributes that can be used to uniquely identify every tuple is also a primary key. So, if there are 3-4 candidate keys present in a relationship, then out those, one can be chosen as a primary key.
• Alternate Key – The candidate key other than the primary key is called as an alternate key.
• Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers.

Master the language of databases with our comprehensive SQL Course.

MySQL Tutorial: Constraints Used In Database

Refer to the image below are the constraints used in the database.

Figure 1: Constraints Used In Database – MySQL Tutorial

Now, that you know the various types of keys and constraints, let’s move on to the next section i.e Data Manipulation Commands.

Want to be a certified Database Administrator?

MySQL Tutorial: Data Manipulation (DML) Commands

This section consists of those commands, by which you can manipulate your database. The commands are:

• USE
• INSERT
• UPDATE
• DELETE
• SELECT

Apart from these commands, there are also other manipulative operators/functions such as:

• LOGICAL OPERATORS
• ARITHMETIC,BITWISE,COMPARISON & COMPOUND OPERATORS
• AGGREGATE FUNCTIONS
• SPECIAL OPERATORS

USE

The USE statement is used to mention which database has to be used to perform all the operations.

Syntax:

USE Database_name;

Example:

USE StudentsInfo;

INSERT

This statement is used to insert new records in a table. 

Syntax:

The INSERT INTO statement can be written in the following two ways:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

--You need not mention the column names

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example:

INSERT INTO Infostudents(StudentID, StudentName, ParentName, Address, City, PostalCode, Country)
VALUES ('06', 'Sanjana','Jagannath', 'Banjara Hills', 'Hyderabad', '500046', 'India');

INSERT INTO Infostudents
VALUES ('07', 'Shivantini','Praveen', 'Camel Street', 'Kolkata', '700096', 'India');

UPDATE

This statement is used to modify the existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example:

UPDATE Infostudents
SET StudentName = 'Alfred', City= 'Frankfurt'
WHERE StudentID = 1;

DELETE

This statement is used to delete existing records in a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Infostudents
WHERE StudentName='Salomao';

SELECT

This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.

The following are the two ways of using this statement:

Syntax:

SELECT column1, column2, ...
FROM table_name;

--(*) is used to select all from the table

SELECT * FROM table_name;

Example:

SELECT StudentName, City FROM Infostudents;
SELECT * FROM Infostudents;

Apart from the individual SELECT keyword, we will be also seeing the following statements, which are used with the SELECT keyword:

• DISTINCT
• ORDER BY
• GROUP BY
• HAVING Clause

The ‘SELECT DISTINCT’ Statement

This statement is used to return only distinct or different values. So, if you have a table with duplicate values, then you can use this statement to list distinct values.

Syntax:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example:

SELECT Country FROM Students;

The ‘ORDER BY’ Statement

This statement is used to sort the desired results in ascending or descending order. By default, the results would be sorted in ascending order. If you want the records in the result-set in descending order, then use the DESC keyword.

Syntax:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

Example:

SELECT * FROM Infostudents
ORDER BY Country;  

SELECT * FROM Infostudents
ORDER BY Country DESC;

SELECT * FROM Infostudents
ORDER BY Country, StudentName;

SELECT * FROM Infostudents
ORDER BY Country ASC, StudentName DESC;

The ‘GROUP BY’ Statement

This statement is used with the aggregate functions to group the result-set by one or more columns.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

Example:

SELECT COUNT(StudentID), Country
FROM Infostudents
GROUP BY Country
ORDER BY COUNT(StudentID) DESC;

The ‘HAVING’ Clause Statement

Since the WHERE keyword cannot be used with aggregate functions, the HAVING clause was introduced.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

Example:

SELECT COUNT(StudentID), City
FROM Infostudents
GROUP BY City
HAVING COUNT(Fees) > 23000;

LOGICAL OPERATORS

This set of operators consists of logical operators such as AND/OR/NOT.

AND OPERATOR

The AND operator is used to filter records that rely on more than one condition. This operator displays the records, which satisfy all the conditions separated by AND, and give the output TRUE.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

Example:

SELECT * FROM Infostudents
WHERE Country='Brazil' AND City='Rio Claro';

OR OPERATOR

The OR operator displays those records which satisfy any of the conditions separated by OR and gives the output TRUE.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

Example:

SELECT * FROM Infostudents
WHERE City='Toronto' OR City='Seoul';

NOT OPERATOR

This operator displays a record when the condition (s) is NOT TRUE.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

Example:

SELECT * FROM Infostudents
WHERE NOT Country='India';
--You can also combine all the above three operators and write a query like this:
SELECT * FROM Infostudents
WHERE Country='India' AND (City='Bangalore' OR City='Canada');

Interested in cracking Interviews for Database Administrator?

ARITHMETIC, BITWISE, COMPARISON & COMPOUND OPERATORS

Refer to the image below.

Figure 2: Arithmetic, Bitwise, Comparison & Compound Operators – MySQL Tutorial

AGGREGATE FUNCTIONS

This section of the article include the following functions:

• MIN()
• MAX()
• COUNT()
• AVG()
• SUM()

MIN() Function

This function returns the smallest value of the selected column in a table.

Syntax:

SELECT MIN(column_name)
FROMtable_name
WHEREcondition;

Example:

SELECT MIN(StudentID) AS SmallestID
FROM Infostudents;

MAX() Function

This function returns the largest value of the selected column in a table.

Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

Example:

SELECT MAX(Fees) AS MaximumFees
FROM Infostudents;

COUNT() Function

This function returns the number of rows that match the specified criteria.

Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

SELECT COUNT(StudentID)
FROM Infostudents;

AVG() Function

This function returns the average value of a numeric column that you choose.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

SELECT AVG(Fees)
FROM Infostudents;

SUM() Function

This function returns the total sum of a numeric column that you choose.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

SELECT SUM(Fees)
FROM Infostudents;

SPECIAL OPERATORS

This section includes the following operators:

• BETWEEN
• IS NULL
• LIKE
• IN
• EXISTS
• ALL
• ANY

BETWEEN Operator

This operator is an inclusive operator which selects values(numbers, texts or dates) within a given range.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example:

SELECT * FROM Infostudents
WHERE Fees BETWEEN 20000 AND 40000;

IS NULL Operator

Since it is not possible to test for the NULL values with the comparison operators(=, <, >), we can use IS NULL and IS NOT NULL operators instead.

Syntax:

--Syntax for IS NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

--Syntax for IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example:

SELECT StudentName, ParentName, Address FROM Infostudents
WHERE Address IS NULL;

SELECT StudentName, ParentName, Address FROM Infostudents
WHERE Address IS NOT NULL;

LIKE Operator

This operator is used in a WHERE clause to search for a specified pattern in a column of a table.

The mentioned below are the two wildcards that are used in conjunction with the LIKE operator:

• % – The percent sign represents zero, one, or multiple characters
• _ – The underscore represents a single character

SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

SELECT * FROM Infostudents
WHERE StudentName LIKE 'S%';

IN Operator

This is a shorthand operator for multiple OR conditions which allows you to specify multiple values in a WHERE clause.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:

SELECT * FROM Infostudents
WHERE Country IN ('Algeria', 'India', 'Brazil');

Note: You can also use IN while writing Nested Queries. Consider the below syntax:

EXISTS Operator

This operator is used to test if a record exists or not.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE&nbsp;EXISTS&nbsp;(SELECT&nbsp;ParentName&nbsp;FROM&nbsp;Infostudents&nbsp;WHERE&nbsp;StudentId = 05&nbsp;AND&nbsp;Price <&nbsp;25000);

ALL Operator

This operator is used with a WHERE or HAVING clause and returns true if all of the subquery values meet the condition.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE StudentID = ALL (SELECT StudentID FROM Infostudents WHERE Fees > 20000);

ANY Operator

Similar to the ALL operator, the ANY operator is also used with a WHERE or HAVING clause and returns true if any of the subquery values meet the condition.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

Example:

SELECT StudentName
FROM Infostudents
WHERE StudentID = ANY (SELECT SttudentID FROM Infostudents WHERE Fees BETWEEN 22000 AND 23000);

Now, that I have told you a lot about DML commands, let me just tell you in short about Nested Queries,Joins and Set Operations.

MySQL Tutorial: Nested Queries

Nested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:

Fig 3: Representation Of Nested Queries – MySQL Tutorial

MySQL Tutorial: Joins

JOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins: 

• INNER JOIN: This join returns those records which have matching values in both the tables.
• FULL JOIN: This join returns all those records which either have a match in the left or the right table.
• LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from the right table.
• RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.

Refer to the image below.

Fig 4: Representation Of Joins – MySQL Tutorial

Let’s consider the below table apart from the Infostudents table, to understand the syntax of joins.

Table 3: Sample Database – MySQL Tutorial

INNER JOIN

Syntax:

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Courses.CourseID, Infostudents.StudentName
FROM Courses
INNER JOIN Infostudents ON Courses.StudentID = Infostudents.StudentID;

FULL JOIN

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Infostudents.StudentName, Courses.CourseID
FROM Infostudents
FULL OUTER JOIN Orders ON Infostudents.StudentID=Orders.StudentID
ORDER BY Infostudents.StudentName;

LEFT JOIN

Syntax:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Infostudents.StudentName, Courses.CourseID
FROM Infostudents
LEFT JOIN Courses ON Infostudents.CustomerID = Courses.StudentID
ORDER BY Infostudents.StudentName;

RIGHT JOIN

Syntax:

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Example:

SELECT Courses.CourseID
FROM Courses
RIGHT JOIN Infostudents ON Courses.StudentID = Infostudents.StudentID 
ORDER BY Courses.CourseID;

MySQL Tutorial: Set Operations

There are mainly three set operations: UNION, INTERSECT, SET DIFFERENCE. You can refer to the image below to understand the set operations in SQL.

Now, that you guys know the DML commadsn. Let’s move onto our next section and see the DCL commands.

MySQL Tutorial: Data Control (DCL) Commands

This section consists of those commands which are used to control privileges in the database. The commands are:

• GRANT
• REVOKE

GRANT

This command is used to provide user access privileges or other privileges for the database.

Syntax:

GRANT privileges ON object TO user;

Example:

GRANT CREATE ANY TABLE TO localhost;

REVOKE

This command is used to withdraw user’s access privileges given by using the GRANT command.

Syntax:

REVOKE privileges ON object FROM user;

Example:

REVOKE INSERT ON *.* FROM Infostudents;

Now, let’s move on to the last section of this blog i.e. the TCL Commands.

MySQL Tutorial: Transaction Control (TCL) Commands

This section of commands mainly deals with the transaction of the database. The commands are:

• COMMIT
• ROLLBACK
• SAVEPOINT
  • RELEASE SAVEPOINT
• SET TRANSACTION

COMMIT

This command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

Syntax:

COMMIT;

Example:

DELETE FROM Infostudents WHERE Fees = 42145;

COMMIT;

ROLLBACK

This command is used to undo transactions since the last COMMIT or ROLLBACK command was issued.

Syntax:

ROLLBACK;

Example:

DELETE FROM Infostudents WHERE Fees = 42145;

ROLLBACK;

SAVEPOINT

This command creates points within the groups of transactions in which to ROLLBACK. So, with this command, you can simply roll the transaction back to a certain point without rolling back the entire transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME; --Syntax for saving the SAVEPOINT

ROLLBACK TO SAVEPOINT_NAME; --Syntax for rolling back to the Savepoint command

Example:

SAVEPOINT SP1;

DELETE FROM Infostudents WHERE Fees = 42145;

SAVEPOINT SP2;

RELEASE SAVEPOINT

You can use this command to remove a SAVEPOINT that you have created.

Syntax:

RELEASE SAVEPOINT SAVEPOINT_NAME;

Example:

RELEASE SAVEPOINT SP2;

SET TRANSACTION

This command gives a name to the transaction.

Syntax:

SET TRANSACTION [ READ WRITE | READ ONLY ];

I hope you enjoyed reading this blog on MySQL Tutorial blog. We have seen the different commands that will help you write queries and play around with your databases.

If you wish to learn more about MySQL and get to know this open source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of ”MySQL Tutorial” and I will get back to you.