-
Notifications
You must be signed in to change notification settings - Fork 34
MySQL
MySQL относится к реляционным СУБД (система управления базами данных), что значит, что в основе проектирования и построения данных лежат связи между ними, и именно связями между данными необходимо руководствоваться при проектировании баз данных.
Базы данных предназначены для работы с информацией: для хранения и сохранения, поиска и редактирования данных, подчиненных какой-то структуре.
Крупнейшей единицей хранения данных является база данных - набор таблиц, связей между ними и прочих элементов - триггеров, представлений, временных таблиц и т.п.
в СУБД может быть сколько угодно баз данных.
Основой одной базы данных является таблица. Внешне она напоминает табличку в экселе. У таблицы в "заголовке" находятся имена полей, а каждый "ряд", называемый в теории баз данных записью, содержит одну запись значения для каждого поля. Если таблица Student, к примеру, содержит имена студентов(name) и их возраст(age), то говорят, что в таблице Student поля name и age. Каждый студент, записанный в таблицу, является одной записью, и выглядит это примерно так:
Student
| name | age |
|---|---|
| Mark | 21 |
| Jeny | 22 |
| Tom | 21 |
Рассмотрим вопросы создания базы данных и таблиц поподробнее.
Если в системе установлена СУБД MySQL, можно запустить соответствующее консольное приложение mysql, которое позволяет выполнять все доступные операции с базами данных.
Если воспринимать параметр как ключ и его значение, то -u username представляет собо пару ключ параметра -u и его значение username Обратите внимание, что после указания ключа параметра (к примеру, -u) можно писать значение параметра как после пробела, так слитно с ключом параметра.
Далее следует пример вызова приложения с параметрами, указанными после ключа. Параметры означают, что вместо написанного там надо подставить ваши значения:
mysql -uusername -ppassword -Ddatabase name -hhost name
Подробнее:
- mysql - название вызываемой программы
-
-u
username- имя пользователя. Имя пользователя подставляется вместо {username} (В open server имя стандартного пользователя - root) -
-p
password- пароль пользователя. В open server пользователь root не имеет пароля, и указывать этот параметр не надо. -
-D
database name- название базы данных. Указывается, когда надо подключится к конкретной базе данных. Если такой необходимости нет, параметр не указывается. -
-h
hostname- имя или адрес хоста для базы данных. В случае с 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)Разберем эту команду подробнее:
- В первой строке выполняется команда
create table, за которой следует название таблицы и открывающая скобка для перечисления полей таблицы. - Во второй и третьей строках создаются поля таблицы. У такого рода команд есть название:
create_definition. Если речь о поле таблицы (а чаще всего так и есть), указывается имя поля (id), затем тип поля (int), затем в скобках максимальное количество символов или разрядов, которые будут заняты каждой записью в этом поле. - После указания размера поля следуют модификаторы.
- Модификатор
not nullговорит СУБД о том, что в данное поле не следует записывать значениеNULL. Еслиnot nullне указано, и при создании записи про поле ничего не будет известно, СУБД по умолчанию запишетNULL. - Модификатор
auto_incrementпозволяет автоматически заполнять указанное поле, каждый раз добавляя единицу к последнему существующему в таблице в этом поле значению. В таблице может быть только одно поле с таким модификатором. - Модификатор
primary keyозначает, что данное поле будет использовано как первичный ключ, т.е. по этому полю можно будет однозначно понять, о какой записи идет речь. - В третьей строке создается поле brand, типа
varchar, до 50 символов, по умолчанию не ноль. - Модификатор
defaultпозволяет задать для поля значение по умолчанию. В данном случае это пустая строка ''. - Затем просто закрывается скобка, открытая после
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 с именем таблицы и точкой запятой в конце.
- Практиковаться в создании пользователей и установкой их привилегий, ни в коем случае не меняя пользователя root. В случае проблем с ним проще всего переставить mysql или open server, если mysql является его частью.
- Создать указанную на уроке базу данных с указанным набором табличек.
- Попрактиковаться в изменении и удалении таблиц.
- Подготовить список вопросов по текущему и предидущим урокам.
- Желающим - ознакомиться с понятием слепка (dump) базы данных.
- Желающим - попробовать все, что изложено в данном уроке, не только в консоли, но и в phpMyAdmin.
Когда база данных создана и таблицы в ней созданы, когда все это сделано правильным пользователем с правильными правами и уровнем доступа, самое время заполнить таблицы данными. В работе с данными есть всего 4 действия: создание, получение, изменение и удаление данных. Для обозначения этих действий используется аббревиатура CRUD: Create, Read, Update, Delete.
Для экспериментов с данными нам понадобится таблица. К примеру, таблица слов из словаря:
mysql> DESC word;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| word | varchar(100) | NO | | | |
| voc_id | int(11) | NO | | 0 | |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.26 sec)На всякий случай приведу команду создания этой таблицы:
CREATE TABLE `word` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(100) NOT NULL DEFAULT '',
`voc_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)Для добавления данных в таблицу используется оператор INSERT INTO. Оператор INSERT INTO бывает нескольких видов, и мы рассмотрим основные:
Простейший вариант вставки данных в таблицу выглядит следующим образом:
INSERT INTO word SET word = "Earth", voc_id = 1;В данном примере мы вставляем запись в таблицу word, указывая конкретное значение для каждого столбца в виде пары ключ-значение (key-value pair). Ключом выступает название поля, значением - собственно информацию, которую мы хотим поместить в записи в это поле. Данный вид вставки данных применяется для добавления одной записи.
INSERT INTO word (word, voc_id) VALUES ("cat", 2), ("dog", 2), ("donkey", 2);Код выше вставляет данные в последующих скобках в соответствующие столбцы, указанные в первых скобках. Таким образом, вначале необходимо перечислить столбцы (поля), в которые планируется вносить данные, а затем через запятую перечислить обернутые скобочками наборы данных для этих полей. Такой запрос позволяет добавлять несколько записей разом.
INSERT INTO word VALUES (30, "cat", 2), (31, "dog", 2), (32, "donkey", 2);
Данный вариант запроса INSERT INTO используется, если вы планируете заполнять все столбцы, а не только выбранные.
Возможна так же вставка данных из результата запроса:
INSERT INTO word (word) SELECT word FROM word;Здесь приведен довольно простой пример, но, по сути дела, если вы построите запрос данных SELECT таким образом, чтобы количество столбцов в результате соответствовало необходимому, указанному во внешнем запросе INSERT, вы можете встроить запрос довольно серьезного уровня сложности.
Запрос SELECT используется для получения данных, и никоим образом их не изменяет. Структура его довольно сложна, и мы попробуем разобрать ее постепенно.
SELECT
<field1>,
<field2>,
<field3>
...
FROM
<table1>,
<table2>,
<view>,
<temp_table>
...
WHERE
<cond>
ORDER BY
<поле1> ASC
<поле2> DESC
LIMIT
N,MНиже приведено текстовое описание основных элементов структуры запроса SELECT, более подробное описание с примерами будет приведено позже.
- После ключевого слова
SELECTидет перечень полей таблиц, функций, вычисляемых из этих полей, констант, независимых от записей функций. Для указания всех полей исопльзуется звездочка. Этот пункт является единственным обязательным пунктом в запросеSELECT, остальные опциональны. - Далее, после ключевого слова
FROMследует перечень таблиц, представлений и временных таблиц, откуда ведется выборка. Таблицы могут быть просто перечислены, а могут быть присоединены к другим таблицам по описанным отдельно правилам, т.е. при помощиJOIN.
SELECT firtsname, lastname FROM authors;
SELECT * FROM books, articles;- Далее следует условие
WHERE, пропускающее только те записи, которые удовлетворяют перечисленным вWHEREусловиям. Все не прошедшие проверку записи отфильтровываются и не демонстрируются. - После фильтра
WHEREможет следовать группировка записей.
SELECT * FROM books WHERE genre = 'non-fiction';- Группировка выполняется при помощи ключевых слов
GROUP BY. Суть группировки в том, что записи могут объединяться по признаку или несольким признакам в одну запись, которая несет в себе некую общую для всех записей группы информацию или результат обработки информации по всей группе. КонструкцияGROUP BYможет включать в себя ключевое словоWHERE, позволяющее фильтровать результаты группировки. - После группировки может иметь место сортировка записей при помощи ключевых слов
ORDER BY. При группировке указывается поле или перечень полей, по которому необходимо отсортировать, также можно указать направление сортировки. По умолчанию осуществляется сортировка по возрастанию.
В select в перечне таблиц можно использовать:
перечень полей
as
выражения
переменные (@varName:=colName with operation)
select count(*), word from word where id > 15 group by word having length(word) > 3 order by word limit 1, 2;select * from word, vocabulary where word.voc_id = vocabulary.id;
+----+--------+--------+----+----------+
| id | word | voc_id | id | name |
+----+--------+--------+----+----------+
| 5 | donkey | 2 | 2 | Advanced |
| 6 | hello | 3 | 3 | New |
| 7 | Earth | 3 | 3 | New |
| 8 | cat | 3 | 3 | New |
| 9 | dog | 3 | 3 | New |
| 10 | donkey | 3 | 3 | New |
| 13 | hello | 1 | 1 | Basic |
...
| 32 | donkey | 2 | 2 | Advanced |
+----+--------+--------+----+----------+
19 rows in set (0.00 sec)