Skip to content

Latest commit

 

History

History
executable file
·
363 lines (269 loc) · 23.1 KB

11_MySQL_user_management_db_and_tables_CRUD.md

File metadata and controls

executable file
·
363 lines (269 loc) · 23.1 KB
Raw

Базы данных. Основы MySQL

Введение

MySQL относится к реляционным СУБД (система управления базами данных), что значит, что в основе проектирования и построения данных лежат связи между ними, и именно связями между данными необходимо руководствоваться при проектировании баз данных.

Базы данных предназначены для работы с информацией: для хранения и сохранения, поиска и редактирования данных, подчиненных какой-то структуре.

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

в СУБД может быть сколько угодно баз данных.

Основой одной базы данных является таблица. Внешне она напоминает табличку в экселе. У таблицы в "заголовке" находятся имена полей, а каждый "ряд", называемый в теории баз данных записью, содержит одну запись значения для каждого поля. Если таблица Student, к примеру, содержит имена студентов(name) и их возраст(age), то говорят, что в таблице Student поля name и age. Каждый студент, записанный в таблицу, является одной записью, и выглядит это примерно так:

Student

name age
Mark 21
Jeny 22
Tom 21

Рассмотрим вопросы создания базы данных и таблиц поподробнее.

Подключение к СУБД mySQL

Если в системе установлена СУБД MySQL, можно запустить соответствующее консольное приложение mysql, которое позволяет выполнять все доступные операции с базами данных.

Если воспринимать параметр как ключ и его значение, то -u username представляет собо пару ключ параметра -u и его значение username Обратите внимание, что после указания ключа параметра (к примеру, -u) можно писать значение параметра как после пробела, так слитно с ключом параметра.

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

mysql -uusername -ppassword -Ddatabase name -hhost name

Подробнее:

  • mysql - название вызываемой программы
  • -uusername - имя пользователя. Имя пользователя подставляется вместо {username} (В open server имя стандартного пользователя - root)
  • -ppassword - пароль пользователя. В open server пользователь root не имеет пароля, и указывать этот параметр не надо.
  • -Ddatabase name - название базы данных. Указывается, когда надо подключится к конкретной базе данных. Если такой необходимости нет, параметр не указывается.
  • -hhostname - имя или адрес хоста для базы данных. В случае с open server-ом это localhost, который можно не указывать.

Строка для стандартного подключения в open server:

mysql -uroot

Весь список параметров консольного приложения mysql

Выбор базы данных, просмотр таблиц

Итак, мы в консоли mysql. Просмотрим список существующих баз данных. Команда show databases; показывает все существующие и доступные вашему пользователю базы данных. Обратите внимание, что команды mysql, как и команды многих других языков, необходимо заканчивать точкой с запятой.

В случае первого запуска mysql, пользователь увидит только три базы данных:

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Далее мы на примере встроенной базы данных разберемся, как выбирать конкретную базу данных и знакомиться с ее таблицами:

mysql> use mysql;

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>

Командой use mysql; пользователь говорит СУБД о том, что хочет работать с базой данных mysql, одноименной с самой СУБД. Далее, аналогично команде show databases; для демонстрации доступных баз данных, применим команду show tables; для демонстрации списка доступных таблиц:

mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| ..........................|
| user                      |
+---------------------------+
28 rows in set (0.01 sec)

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

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

Пользователи и привилегии

Проверим, под каким пользователем мы подключены к СУБД:

mysql> select user(), current_user();
+----------------+----------------+
| user()         | current_user() |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

Итак, мы подключены как пользователь root на хостинге localhost. Встроенная в MySQL функция user() возвращает имя текущего пользователя, current_user() возвращает текущее имя пользователя, под которым пользователь аутентифицировался в текущей сессии. Иногда они могут различаться, что сигнализирует о проблеме с аутентификацией.

Пользователь root является в системах unix и linux суперпользователем, который имеет права на все. Считается небезопасным постоянно работать под суперпользователем, потому мы создадим себе другого пользователя, который будет иметь почти те же права.

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

mysql> use mysql;
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> select user, host from user;
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| mysql.sys | localhost |
| root      | localhost |
+-----------+-----------+
3 rows in set (0.01 sec)

Итак, у нас в базе два пользователя (количество пользователей может отличаться). Создадим своего пользователя специальной командой mysql:

mysql> CREATE USER 'stud'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

Итак, у нас создался новый пользователь stud, и пароль у него password.

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

mysql> show grants for stud@localhost;
+------------------------------------------+
| Grants for stud@localhost                |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'stud'@'localhost' |
+------------------------------------------+
1 row in set (0.00 sec)

Итак, новому пользователю открыта только привилегия USAGE, что, как мы увидим чуть позже, означает "Ничего нельзя". Нас это не устраивает, ведь наш текущий пользователь root имеет больше привилегий:

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Вот ALL PRIVILEGES - это серьезно, это значит, что можно все. WITH GRANT OPTION означает, что пользователь может еще и менять привилегии других пользователей.

Раз наш пользователь может раздавать привилегии, воспользуемся этим:

mysql> GRANT ALL PRIVILEGES ON * . * TO 'stud'@'localhost';
Query OK, 0 rows affected (0.01 sec)

Команда выдает привилегии указанному пользователю на указанные таблицы (*.* означает, все таблицы всех баз данных). Проверим результат:

mysql> show grants for stud@localhost;
+---------------------------------------------------+
| Grants for stud@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'stud'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

Отлично, теперь мы можем заходить под нашем пользователем и работать в полную силу не переживая (особо) о безопасности нашего подключения.

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

Выйдем из под root пользователя, зайдем под студентом и приступим к созданию новой базы данных:

mysql> \q
Bye

Вышли из консоли mysql.

$ mysql -ustud -hlocalhost -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.7.11 Homebrew

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Мы вошли, используя команду mysql -ustud -hlocalhost -p. Если просто написать -p не вводя сам пароль, система попросит ввести пароль ниже и не отобразит его на экране, что удобно и безопасно.

Проверим, под тем ли пользователем мы зашли и что у него за привилегии:

mysql> show grants;
+---------------------------------------------------+
| Grants for stud@localhost                         |
+---------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'stud'@'localhost' |
+---------------------------------------------------+
1 row in set (0.00 sec)

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

Отлично, мы готовы к созданию БД и таблиц.

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

Создание своей базы данных - дело несложное:

create database test;
Query OK, 1 row affected (0.00 sec)

База создана. Переключимся в нее и добавим к ней первую табличку, тут будет немного посложнее:

mysql> use test;
Database changed  

mysql> create table cars(
     id int(5) not null primary key auto_increment,
     brand varchar(50) not null default ''
     );
Query OK, 0 rows affected (0.01 sec)

Разберем эту команду подробнее:

  1. В первой строке выполняется команда create table, за которой следует название таблицы и открывающая скобка для перечисления полей таблицы.
  2. Во второй и третьей строках создаются поля таблицы. У такого рода команд есть название: create_definition. Если речь о поле таблицы (а чаще всего так и есть), указывается имя поля (id), затем тип поля (int), затем в скобках максимальное количество символов или разрядов, которые будут заняты каждой записью в этом поле.
  3. После указания размера поля следуют модификаторы.
  4. Модификатор not null говорит СУБД о том, что в данное поле не следует записывать значение NULL. Если not null не указано, и при создании записи про поле ничего не будет известно, СУБД по умолчанию запишет NULL.
  5. Модификатор auto_increment позволяет автоматически заполнять указанное поле, каждый раз добавляя единицу к последнему существующему в таблице в этом поле значению. В таблице может быть только одно поле с таким модификатором.
  6. Модификатор primary key означает, что данное поле будет использовано как первичный ключ, т.е. по этому полю можно будет однозначно понять, о какой записи идет речь.
  7. В третьей строке создается поле brand, типа varchar, до 50 символов, по умолчанию не ноль.
  8. Модификатор default позволяет задать для поля значение по умолчанию. В данном случае это пустая строка ''.
  9. Затем просто закрывается скобка, открытая после create table, и идет стандартная для команд mysql точка с запятой.

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

Посмотрим на результирующую таблицу:

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

Команда desc (сокращение от describe) с именем таблицы выводит краткое описание таблицы.

Так же можно посмотреть полную команду создания существующей таблицы, такой, какой ее видит mysql

mysql> show create table cars;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cars  | CREATE TABLE `cars` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `brand` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Результат в консоли немного не так красив, поскольку осуществляются переносы строк. Как видно из результата команды show create table, таблицы в mysql по умолчанию создаются на движке InnoDB и с набором символов utf8, что не может нас не радовать. Возможные движки с их плюсами и минусами мы рассмотрим позже.

Другие операции с таблицами и базами данных

Для изменения существующей таблицы есть команда alter table:

mysql> alter table cars add column year int(4) not null default 2010;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
| year  | int(4)      | NO   |     | 2010    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

Мы добавили к таблице машин колонку с годом выпуска машины, использовав команду alter table. Тем же путем удалим эту колонку:

mysql> alter table cars drop column year;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc cars;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(5)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(50) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Команда alter table довольно сложна, но и многое позволяет. Команда, как и при создании таблицы, позволяет добавлять и удалять колонки, менять их самыми различными способами, добавлять на них различные индексы, вешать ключи на таблицы и удалять их и так далее.

Для удаления таблиц есть две интересные команды:

drop table с именем таблицы просто удаляет таблицу. trunc table с именем таблицы удаляет только содержимое таблицы, не меняя ее структуры, но пересоздавая первичный ключ, так что он снова будет добавлять "с единички".

База данных удаляется командой drop database с именем таблицы и точкой запятой в конце.

Полезные cсылки

Домашнее задание

Следующий Урок