Перейти к содержимому

Mysql как работать с таблицей

  • автор:

Как работать с таблицами в MySQL

MySQL – одна из наиболее часто используемых систем управления реляционными базами данных (СУБД). Это позволяет очень эффективно работать с большими объемами данных. Самым важным объектом любой базы данных является таблица, и с ней связано несколько различных операций, с помощью которых вы можете управлять своими данными. Поэтому сегодня мы научимся работать с таблицами в MySQL и MariaDB в Ubuntu 20.04.

Работа с таблицами (выбор, обновление, удаление, создание таблицы, изменение таблицы и удаление таблицы) в MySQL в Ubuntu 20.04:

Для работы с таблицами в MySQL в Ubuntu 20.04 вы можете пройти все шаги, описанные ниже:

Шаг № 1. Убедитесь, что в вашей системе Ubuntu 20.04 установлена ​​база данных, совместимая с MySQL.

Если вы собираетесь работать с таблицами в MySQL, у вас должен быть установлен MySQL или MariaDB. Чтобы проверить, установлен ли MySQL в нашей системе или нет, мы выполним следующую команду в нашем терминале:

mysql --version

Если MySQL будет установлена в вашей системе, вы сможете увидеть ее версию после выполнения этой команды:

В случае если MySQL отсутствует, вы можете легко установить ее, следуя этому руководству.

Шаг № 2: Войдите в MySQL Shell из терминала Ubuntu 20.04:

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

sudo mysql

Вы войдете в оболочку MySQL, как показано на изображении ниже:

Шаг № 3: Создайте базу данных в MySQL в Ubuntu 20.04:

Теперь, когда мы находимся внутри оболочки MySQL, первое, что нам нужно сделать, это создать базу данных, чтобы мы могли создавать внутри нее таблицы для выполнения различных операций. База данных в MySQL может быть создана с помощью следующей команды:

mysql> create database DBName;

Здесь вам нужно заменить DBName любым именем, которое вы хотите для своей базы данных. Назовем ее DBTest.

Как только ваша база данных будет создана, вы получите сообщение в оболочке MySQL, подобное тому, которое показано на изображении ниже:

Шаг №4: Переключитесь на созданную базу данных в MySQL:

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

mysql> use DBTest;

Когда эта база данных будет выбрана успешно, вы получите сообщение, показанное на изображении ниже, в оболочке MySQL.

Шаг № 5: Создайте таблицу в MySQL в Ubuntu 20.04:

После переключения на желаемую базу данных мы можем создать таблицу в MySQL, выполнив следующую команду:

mysql> CREATE TABLE TableName(Col1 Datatype, Col2 Datatype, ….);

Здесь вы должны заменить TableName любым именем, которое вы хотите для своей таблицы. Мы назвали ее Student. Col1, Col2 и т.д. представляют имена столбцов вашей базы данных, тогда как Datatype относится к типам данных каждого из этих столбцов соответственно. Вы можете создать столько столбцов или атрибутов для своей таблицы, сколько захотите. Мы создали три столбца с именами StudentID, StudetName и StudentAge с типами данных int, varchar (255) и int соответственно.

Шаг № 6: Вставьте записи в недавно созданную таблицу в MySQL:

После того, как таблица была создана в базе данных MySQL, мы можем вставлять записи в эту таблицу, выполнив следующую команду:

mysql> INSERT INTO Student VALUES (1, 'Sergey', 24);

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

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

t

Шаг № 7: Отобразите записи вашей таблицы в MySQL:

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

mysql> SELECT * From Student;

Выполнение этой команды отобразит все записи вашей таблицы в красивой табличной форме, как показано на изображении ниже:

Шаг № 8: Обновите запись в таблице в MySQL:

Команда UPDATE в MySQL используется для изменения значений любой конкретной записи. Например, если вы хотите изменить значения какой-либо записи вашей таблицы, вам необходимо выполнить следующую команду:

mysql> UPDATE Student SET StudentName='Elena', StudentAge=21 WHERE StudentID=2;

Здесь вы можете заменить значение StudentID на идентификатор записи, которую вы хотите изменить.

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

Шаг № 9: Измените таблицу в MySQL в Ubuntu 20.04:

Команда ALTER в MySQL используется для добавления или удаления указанного столбца из таблицы. Например, если мы хотим удалить какой-либо конкретный столбец из нашей таблицы, мы выполним следующую команду:

mysql> ALTER TABLE Student Drop COLUMN StudentAge;

Здесь вы можете заменить StudentAge любым столбцом по вашему выбору, который хотите удалить.

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

Таким же образом вы даже можете добавить новый столбец в свою таблицу, заменив ключевое слово DROP на ADD и StudentAge на имя вашего нового столбца, который нужно добавить.

Шаг № 10: Удаление определенной записи из таблицы в MySQL в Ubuntu 20.04:

Оператор ALTER в MySQL используется для добавления или удаления столбцов. Однако, если вы хотите удалить определенную запись из таблицы в MySQL, вам необходимо выполнить следующую команду:

mysql> DELETE FROM Student WHERE StudentID=1;

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

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

Шаг № 11: Удалите все записи из таблицы в MySQL в Ubuntu 20.04:

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

mysql> DELETE FROM Student;

Когда эта команда будет выполнена успешно, вы получите сообщение, показанное на изображении ниже, в оболочке MySQL.

В идеале эта команда должна очистить все записи в вашей таблице, которые можно проверить, снова запустив команду SELECT, как показано на следующем изображении. Получение сообщения Empty Set указывает на то, что все записи из нашей указанной таблицы были успешно удалены.

Шаг № 12: Удалим таблицу в MySQL в Ubuntu 20.04:

Наконец, мы можем удалить таблицу, выполнив следующую команду:

mysql> DROP Table Student;

При успешном выполнении этой команды выбранная таблица будет удалена, а сообщение, показанное на изображении ниже, подтверждает это удаление.

Шаг № 13: Удаление базы данных

В заключении удалим саму базу:

mysql> DROP DATABASE DBTest;

Список баз MySQL можно посмотреть командой:

mysql> SHOW DATABASES;

Как видите, мы удалили базу DBTest

Сегодняшняя статья была в основном учебным пособием для начинающих по работе с таблицами в MySQL в Ubuntu 20.04. Мы рассмотрели основные операции в MySQL на очень простых примерах.

SQL-Ex blog

Таблицы лежат в сердце любой базы данных MySQL, обеспечивая структуру организации данных и доступа к ним других приложений. Таблицы также помогают обеспечить целостность этих данных. Чем лучше вы поймете, как создавать и модифицировать таблицы, тем легче будет управлять другими объектами базы данных и тем эффективней вы сможете работать с MySQL в целом. Наличие твердого фундамента в виде таблиц поможет вам также строить более эффективные запросы, чтобы вы могли получать требуемые данные (и только их), не снижая производительности базы данных.

Это вторая статья в серии, посвященной MySQL. Я рекомендую вам предварительно познакомиться с первой статьей, если вы не сделали этого ранее. Здесь же я сосредоточусь, главный образом, на создании, изменении и удалении таблиц, демонстрируя для этого как использование операторов SQL, так и возможности GUI в MySQL Workbench. Как и в первой статье, я использую выпуск MySQL Community на компьютере с Windows для создания примеров для настоящей статьи. Все примеры выполнены в Workbench, которая ставится вместе с Community Edition.

Использование MySQL Workbench GUI для создания базы данных

Прежде чем создавать таблицы, необходимо иметь базу данных для этих таблиц, поэтому я сначала потрачу немного времени на создание базы данных. Создание базы данных в MySQL относительно простой процесс. Вы можете выполнить простой оператор CREATE DATABASE для экземпляра, в котором вы хотите добавить базу данных. Это особенно просто, если вы планируете использовать коллацию и набор символов по умолчанию. Например, для создания базы данных travel вам нужно всего лишь выполнить следующий оператор:

CREATE DATABASE travel;

Оператор CREATE DATABASE делает ровно то, что написано. Он создает базу данных в экземпляре MySQL, к которому вы подключены. Если вы хотите убедиться, что такой базы данных еще нет до выполнения оператора, вы можете добавить предложение IF NOT EXISTS:

CREATE DATABASE IF NOT EXISTS travel;

Оба оператора говорят MySQL создать базу данных, которая использует коллацию и набор символов по умолчанию. Вы можете выполнить оператор либо из командной строки MySQL, либо в MySQL Workbench. Чтобы выполнить оператор в Workbench, нужно лишь открыть вкладку запроса, напечатать или вставить туда оператор и щелкнуть одну из кнопок «выполнить» на панели инструментов. Остальное сделает MySQL.

Вместо использования оператора CREATE DATABASE для создания базы данных вы можете использовать оператор CREATE SCHEMA. Оба оператора поддерживают одинаковый синтаксис и оба приводят к одному и тому же результату. Это происходит потому, что MySQL рассматривает базы данных и схемы как одно и то же. Фактически, MySQL рассматривает CREATE SCHEMA как синоним CREATE DATABASE. Когда вы создаете базу данных, то создаете схему. Когда вы создаете схему, вы создаете базу данных. Workbench использует оба термина, свободно переходя от одного к другому.

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

Чтобы использовать GUI для создания базы данных, сначала щелкните на кнопке создания схемы на панели инструментов Workbench. (Кнопка выглядит как стандартная иконка базы данных и имеет всплывающую подсказку Create a new schema in the connected server.) Когда откроется вкладка Schema, вам нужно только ввести имя базы данных, как показано на рис.1.

Рис.1 Добавление базы данных к экземпляру MySQL

Если вы хотите использовать отличные от значений по умолчанию набор символов или коллацию, вы можете выбрать их из выпадающих списков. Например, вы можете выбрать utf8 в качестве набора символов и коллацию utf8_unicode_ci.

В MySQL вы можете установить набор символов и коллацию на нескольких уровнях: сервера, таблицы, столбца или литеральной строки. По умолчанию набор символов установлен в utf8mb4, а коллацией по умолчанию является utf8mb4_0900_ai_ci. Прежде чем отказаться от значений по умолчанию, я предлагаю вам ознакомиться с соответствующей документацией MySQL.

Вкладка Schema содержит также опцию Rename References. Однако она заблокирована и применяется только в том случае, когда вы обновляете модель базы данных. Workbench иногда содержит опции интерфейса, которые не применяются в текущей ситуации, но которые могут смутить вас, когда вы впервые начинаете работать с MySQL или Workbench. Однако обычно вы можете принимать значения по умолчанию, и не беспокоитесь об опциях, по крайней мере, до тех пор, пока не узнаете как они работают и где применимы.

Для этой статьи (предполагая, что вы хотите следовать примерам) вы можете принять набор символов и коллацию по умолчанию и щелкнуть Apply. Это запустит мастера Apply SQL Script to Database, показанного на рис.2. На первом экране мастера показан оператор SQL, который сгенерировал Workbench, но еще не применил к экземпляру MySQL.

Рис.2 Проверка оператора CREATE SCHEMA

Экран содержит также опции Algorithm и Lock Type. Обе опции относятся к онлайновой функции DDL MySQL, которая обеспечивает поддержку изменений таблицы на месте и одновременных DML. Вам не потребуется понимание этих опций прямо сейчас, и вы можете спокойно принять значения по умолчанию. (Это еще один пример опций, когда Workbench может сбить с толку.) Однако если вы хотите больше узнать об этих возможностях, информацию можно найти в документации MySQL, относящейся к InnoDB и online DDL.

Чтобы создать базу данных, щелкните кнопку Apply, которая приведет вас на следующий экран, показанный на рис.3. Этот экран просто подтверждает, что база данных была создана. Вы можете теперь щелкнуть Finish, чтобы закрыть окно диалога. Не забудьте закрыть также исходную вкладку Schema.

Рис.3 Завершение создания новой схемы (базы данных)

База данных теперь должна появиться в списке на панели Schemas в навигаторе. Если этого не произошло, щелкните на кнопке refresh (обновить) в верхнем правом углу панели. База данных (схема) travel должна теперь появиться наряду с другими базами данных в экземпляре MySQL. В моей системе есть еще только одна база данных по умолчанию sys, как показано на рис.4.

Рис.4 Появление новой базы данных в навигаторе

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

Использование MySQL Workbench GUI для создания таблицы

Вы также можете использовать MySQL Workbench GUI, чтобы добавить таблицу в базу данных. В этом случае начните с выбора узла базы данных travel в навигаторе. Вам может потребоваться выполнить двойной щелчок на узле, чтобы выбрать его. При выборе имя базы данных должно быть выделено жирным. Когда база данных выбрана, щелкните на кнопке создания таблицы на панели инструментов Workbench. (Кнопка выглядит как стандартная иконка таблицы и имеет всплывающую подсказку Create a new table in the active schema in connected server.) При щелчке на кнопку Workbench откроет вкладку Table, как показано на рис.5.

Рис.5 Добавление таблицы в Workbench GUI

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

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

MySQL также поддерживает другие движки хранилища, такие как MyISAM, MEMORY, CSV и ARCHIVE. Каждый из них имеет специфические характеристики и область использования. Сейчас я рекомендую вам принять по умолчанию InnoDB, пока вы лучше не поймете различие между ними. Я также рекомендую почитать документацию MySQL о различных типах движков.

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

Узнав основы, вы можете добавить первый столбец, который будет называться manufacturer_id. Он будет также первичным ключом и включать опцию AUTO_INCREMENT, которая скажет MySQL автоматически генерировать уникальное число для значения этого столбца, аналогично свойству IDENTITY в SQL Server.

  • PK. Конфигурирует столбец как первичный ключ.
  • NN. Конфигурирует столбец как NOT NULL.
  • UN. Конфигурирует INT базы данных как UNSIGNED.
  • AI. Конфигурирует столбец с опцией AUTO_INCREMENT.

Знаковость целого влияет на диапазон поддерживаемых значений. Рассмотрим тип данных INT. Если столбец определен как знаковый тип данных INT, значения в столбце должны быть в диапазоне между -2147483648 и 2147483647. Однако, если тип данных является беззнаковым, значения должны находиться между 0 и 4294967295. Если вы знаете, что столбец никогда не будет хранить отрицательных значений, вы можете определить его как беззнаковый, чтобы обеспечить больший диапазон для положительных целых чисел.

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

На рис.6 показан столбец manufacturer_id как он пока определен. Обратите внимание, что нижний раздел отражает все установки, определенные в сетке столбца.

Рис.6 Добавление столбца в определение таблицы

  • Столбец manufacturer сконфигурирован имеющим тип VARCHAR(50) и NOT NULL.
  • Столбец create_date сконфигурирован имеющим тип TIMESTAMP и NOT NULL. Для него установлено значение по умолчанию CURRENT_TIMESTAMP — системная функция, которая возвращает текущую дату и время.
  • Столбец last_update сконфигурирован имеющим тип TIMESTAMP и NOT NULL. Для него установлено значение по умолчанию, которое включает функцию CURRENT_TIMESTAMP наряду с опцией ON UPDATE CURRENT_TIMESTAMP, которая генерирует значение, когда происходит обновление таблицы.

Рис.7 Добавление нескольких столбцов в определение новой таблицы

Осталось сделать еще один шаг, чтобы завершить определение таблицы. Для этого вам нужно перейти на вкладку Options и установить начальное значение AUTO_INCREMENT. Я использовал 1001, как показано на рис.8. В результате первой добавленной в таблицу записи будет присвоено значение manufacturer_id, равное 1001, а каждая последующая строка будет иметь приращение 1.

Рис.8 Установка начального значения опции AUTO_INCREMENT

Как можно видеть, имеется множество других табличных опций, которые вы можете сконфигурировать, и есть другие вкладки, на которых конфигурируются дополнительные параметры. Но сейчас мы на этом остановимся и добавим таблицу в базу данных. Для этого щелкните кнопку Apply, которая запустит мастера Apply SQL Script to Database (применить скрипт в базе данных), показанного на рис.9.

Рис.9 Проверка оператора CREATE TABLE

На этом экране вы можете проверить сгенерированный оператор SQL и, если желаете, выбрать алгоритм и тип блокировки. Вы можете непосредственно тут отредактировать оператор SQL. (Только не допустите ошибок.)

Обратите внимание, что столбец manufacturer_id имеет тип INT (беззнаковый) и определен как первичный ключ. Он также включает опцию AUTO_INCREMENT. Начальное значение AUTO_INCREMENT, 1001, определено как опция таблицы, наряду с движком хранилища InnoDB. Отметим также, что столбцы create_date и last_update включают определенные предложения DEFAULT (значения по умолчанию).

Для завершения процесса создания таблицы просто щелкните Apply, а затем Finish на следующем экране. Затем вы сможете проверть в навигаторе, что таблица была создана, что показано на рис.10.

Рис.10 Просмотр новой таблицы в навигаторе

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

Использование SQL для создания таблицы в базе данных MySQL

Функционал Workbench GUI может быть удобен для создания объектов базы данных, особенно, если вы новичок в MySQL или разработке баз данных. Он также может быть полезен в понимании различных опций, доступных при создании объекта. Однако большинство разработчиков предпочитает самим писать код SQL и, если вы уже имеете опыт работы с SQL, вам, вероятно, не составит труда адаптироваться к MySQL.

Имея это в виду, на следующем шаге мы создадим вторую таблицу в базе данных travel. Для этого вы можете использовать следующий оператор CREATE TABLE:

CREATE TABLE IF NOT EXISTS airplanes ( 
plane_id INT UNSIGNED NOT NULL AUTO_INCREMENT, 
plane VARCHAR(50) NOT NULL, 
manufacturer_id INT UNSIGNED NOT NULL, 
engine_type VARCHAR(50) NOT NULL, 
engine_count TINYINT NOT NULL, 
max_weight MEDIUMINT UNSIGNED NOT NULL, 
icao_code CHAR(4) NOT NULL, 
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 
last_update TIMESTAMP NOT NULL 
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
PRIMARY KEY (plane_id), 
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) 
REFERENCES manufacturers (manufacturer_id) ) 
ENGINE=InnoDB AUTO_INCREMENT=101;

По большей части CREATE TABLE придерживается стандарта SQL. Эта таблица подобна той, которую я создавал в первой статье этой серии. Она также использует некоторые элементы, аналогичные элементам таблицы manufacturers, созданной выше. Таблица содержит девять столбцов с разными типами данных и опциями, хотя все столбцы сконфигурированы как NOT NULL.

Стоит отметить один момент, связанный с столбцом max-weight, который имеет тип данных MEDIUMINT (беззнаковый). Вы помните, что этот тип данных лежит между типами данных SMALLINT и INT с точки зрения поддерживаемого диапазона чисел. Таким образом, вы имеете больше возможностей градации при работе с целыми значениями. Ни SQL Server, ни Oracle не поддерживают тип данных MEDIUMINT.

То, что вы еще не видели (по крайней мере, в этой и предыдущей статьях), это ограничение внешнего ключа (foreign key), которое определено для столбца manufacturer_id. Внешний ключ ссылается на столбец manufacturer_id в таблице manufacturers. Ограничение гарантирует, что любое значение, добавленное в таблицу airplanes, должно существовать в таблице manufacturers. Если вы попытаетесь добавить другое значение, то получите ошибку.

Определение таблицы также включает две табличных опции. Опция ENGINE задает InnoDB в качестве движка хранилища, а опция AUTO_INCREMENT устанавливает начальное значение в 101.

На данный момент оператор CREATE TABLE можно считать достаточно полным, поэтому вы можете двинуться дальше и выполнить его в Workbench. Затем вы можете увидеть таблицу в навигаторе, как показано на рис.11.

Рис.11 Просмотр таблицы airplanes в навигаторе

Как видно, под узлом Foreign Keys в навигаторе присутствует внешний ключ. Обратите внимание, что MySQL также добавил индекс для внешнего ключа, и дал ему такое же имя — foreign key. Мы обсудим индексы позже в этой серии статей.

Изменение определения таблицы в базе данных MySQL

Вы можете также использовать SQL для модификации определения таблицы в MySQL. Например, следующий оператор ALTER TABLE добавляет два столбца в таблицу airplanes:

ALTER TABLE airplanes 
ADD COLUMN wingspan DECIMAL(5,2) NOT NULL AFTER max_weight, 
ADD COLUMN plane_length DECIMAL(5,2) NOT NULL AFTER wingspan;

Оба столбца имеют тип DECIMAL(5,2). Это означает, что каждый столбец может хранить до 5 цифр с двумя десятичными знаками.

Каждое определение столбца также включает предложение AFTER, которое определяет, куда добавить столбец в определении таблицы. Например, предложение AFTER в определении столбца wingspan указывает, что столбец должен быть добавлен после столбца weight, а предложение AFTER в определении столбца plane_length говорит, что этот столбец должен быть добавлен после столбца wingspan.

Когда вы выполняете этот оператор ALTER TABLE, MySQL обновит соответствующим образом таблицу airplanes. Вы можете затем увидеть эти новые столбцы в навигаторе.

Вы можете использовать также Workbench GUI для изменения определения таблицы. Для этого выполните щелчок правой кнопкой на таблице, а затем щелкните Alter Table — откроется вкладка Table. Здесь вы можете изменять определения столбов или опции таблицы. Вы можете также добавлять или удалять столбцы. На рис.12 показана вкладка Table с выбранными столбцами wingspan и plane_length, это те столбцы, которые вы только что добавили выше.

Рис.12 Простмотр новых столбцов в редакторе таблиц

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

Для добавления столбца выполните двойной щелчок на первой ячейке в первой пустой строке сетки таблицы (ниже определения столбца last_update), а затем напечатайте имя столбца — parking_area. В той же строке напечатайте тип данных INT, выберите опцию G (что означает GENERATED) и напечатайте wingspan*plane_length в столбце Default/Expression. Выражение умножает значение размаха крыльев на значение длины самолета для получения общей площади.

Когда вы создаете сгенерированный столбец в GUI, Workbench автоматически выбирает опцию Virtual в области подробностей столбца (внизу вкладки). Это означает, что значения столбца будут генерироваться по требованию, а не при сохранении в базе данных. Опция Stored делает обратное. Значение вычисляется, когда строка вставляется в таблицу, при этом значение сохраняется пока строка не будет обновлена или удалена. Для этой статьи я использовал опцию Stored.

После создания столбца вы можете перенести его в новое местоположение в списке столбцов, перетягивая его в желаемую позицию. В этом случае я переместил столбец parking_area после столбца plane_length, как показано на рис.13.

Рис.13 Добавление сгенерированного столбца в таблицу airplanes

Это все, что вам требуется сделать для добавления сгенерированного столбца в таблицу. Для завершения процесса щелкните Apply, что приведет к запуску мастера Apply SQL Script to Database. Здесь вы можете проверить скрипт SQL, как показано на рис.14.

Рис.14 Проверка нового столбца, добавленного в таблицу airplanes

Когда Workbench генерирует оператор ALTER TABLE, он добавляет предложение GENERATED ALWAYS AS, чтобы показать, что это сгенерированный столбец. (Ключевые слова GENERATED ALWAYS не являются обязательными, и вы можете опустить их при создании своего собственного оператора SQL.) Кроме того, предложение включает вычисляемое выражение в скобках.

Workbench добавляет также ключевое слово STORED к определению столбца, показывающее, что вычисляемые значения должны быть сохранены, а не вычисляться по требованию. Еще определение включает предложение AFTER, указывающее, что столбец должен быть добавлен после столбца plane_length.

Если все нормально, щелкните еще раз Apply, а затем Finish, чтобы завершить работу мастера. затем вы можете подтвердить обновление таблицы в навигаторе.

Удаление таблицы из базы данных MySQL

Как и при других DDL действиях в Workbench, вы можете использовать SQL или GUI для удаления таблицы из базы данных. Например, вы можете удалить таблицу airplanes, выполнив следующий оператор DROP TABLE, который включает дополнительное предложение IF EXISTS:

DROP TABLE IF EXISTS airplanes;

Вы можете также удалить таблицу в навигаторе. Для этого щелкните правой кнопкой на таблице, а затем щелкните Drop Table. Это приведет к появлению диалогового окна Drop Table, показанного на рис.15. Щелкните Drop Now для удаления таблицы.

Рис.15 Удаление таблицы в Workbench

Обратите внимание, что диалог включает также опцию Review SQL. Щелкните её, если вы захотите вместо этого просмотреть оператор DROP TABLE, который сгнерировал Workbench. Вы сможете затем выполнить отсюда этот оператор.

Работа с таблицами в базе данных MySQL

Таблицы MySQL поддерживают разнообразные опции как на уровне столбца, так и на уровне таблицы, больше, чем могут быть исчерпывающе рассмотрены в отдельной статье. Вы можете также создавать временные таблицы или секционированные таблицы. Вы не потратите зря время на просмотр документации MySQL по оператору CREATE TABLE. Вы сможете увидеть множество способов определения таблицы MySQL.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

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

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

  • 1 Начало работы с MySQL
    • 1.1 Введение
    • 1.2 Установка MySQL
    • 2.1 Создание Базы Данных
    • 3.1 Создание таблицы
    • 3.2 Заполнение Базы Данных MySQL
    • 3.3 Запросы к Таблицам MySQL
    • 4.1 Creating users
    • 4.2 Предоставление Привилегий Командой GRANT
    • 4.3 Проверка Пользовательских Разрешений
    • 4.4 Удаление Прав Доступа Пользователя Командой REVOKE
    • 4.5 Удаление Учетных Записей с Использованием DELETE
    • 4.6 Заключение

    Начало работы с MySQL

    Введение

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

    Установка MySQL

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

    root # emerge —ask mysql

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

    КОД Сообщение einfo MySQL

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

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

    root # emerge —config =dev-db/mysql-[version]

    * 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, выполните данную команду:

    root # /etc/init.d/mysql start

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

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

    root # systemctl restart mysqld.service
    root # systemctl restart mariadb.service

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

    root # /usr/bin/mysqladmin -u root -h localhost password ‘new-password’

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

    user $ mysql -u root -h localhost -p

    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 .

    mysql> SHOW DATABASES;

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

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

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

    mysql> CREATE DATABASE gentoo;

    Query OK, 1 row affected (0.08 sec)

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

    mysql> SHOW DATABASES;

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

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

    mysql> USE gentoo;

    Database changed

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

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

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

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

    mysql> SHOW TABLES;

    Empty set (0.00 sec)

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

    КОД Синтаксис CREATE TABLE

    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 символах, хотя существует более техническое объяснение, которое вам предоставит сайт, указанный выше. Теперь, когда мы знаем как мы создадим таблицу, давайте это сделаем.

    mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));

    Query OK, 0 rows affected (0.11 sec)

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

    mysql> SHOW TABLES;

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

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

    mysql> DESCRIBE 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

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

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

    mysql> INSERT INTO developers VALUES(‘Joe Smith’, ‘joesmith@gentoo.org’, ‘toolchain’);

    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 . Здесь приведен пример:

    КОД ~/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

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

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

    mysql> LOAD DATA LOCAL INFILE ‘~/records.txt’ INTO TABLE developers;

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

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

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

    user $ mysql -u root -h localhost -p < sqlfile

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

    mysql> source sqlfile;

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

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

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

    КОД Виды команды SELECT

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

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

    mysql> SELECT * FROM developers;

    +-------------+-----------------------+----------------+ | 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, как показано ниже.

    mysql> SELECT * FROM developers WHERE name = ‘Chris White’;

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

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

    mysql> SELECT email,job FROM developers WHERE name = ‘Chris White’;

    +-----------------------+---------------+ | 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 Syntax

    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:

    mysql> CREATE USER ‘admin’@’localhost’ IDENTIFIED BY ‘password’;
    mysql> CREATE USER ‘guest’@’localhost’ IDENTIFIED BY ‘password’;

    Важно
    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

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

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

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

    Заметка
    Если вы запустили MySQL чтобы передавать данные веб-приложению, CREATE , SELECT , INSERT (обсуждалось здесь же), DELETE и UPDATE (для получения дальнейшей информации посмотрите раздел Справочного Руководства MySQL — Синтаксис GRANT и REVOKE) — это единственные разрешения, которые вам, скорее всего, понадобятся. Большинство делает ошибку, предоставляя все разрешения, когда в этом нет действительной необходимости. Сверьтесь с разработчиками приложения, чтобы посмотреть, не вызовут ли такие разрешения проблемы в общей работе.

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

    mysql> GRANT ALL ON gentoo.* TO ‘admin’@’localhost’ IDENTIFIED BY ‘password’;
    mysql> GRANT SELECT ON gentoo.* TO ‘guest’@’localhost’ IDENTIFIED BY ‘password’;

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

    mysql> quit

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

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

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

    user $ mysql -u guest -h localhost -p

    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:

    mysql> USE 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> CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));

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

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

    mysql> SELECT * FROM developers;

    +-------------+-----------------------+----------------+ | 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.

    mysql> quit
    user $ mysql -u admin -h localhost -p

    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. Вспомните, что ранее мы установили разрешения только для определенной базы данных.

    mysql> CREATE DATABASE gentoo2;

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

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

    mysql> USE 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

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

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

    mysql> REVOKE ALL ON gentoo.* FROM ‘guest’@’localhost’;

    Query OK, 0 rows affected (0.00 sec)

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

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

    user $ mysql -u guest -h localhost -p

    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 теперь исчезли.

    mysql> USE 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:

    mysql> DROP USER ‘guest’@’localhost’;

    Query OK, 1 row affected (0.07 sec)

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

    mysql> quit
    user $ mysql -u guest -h localhost -p

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

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

    Заключение

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

    • phpMyAdmin — Популярный инструмент администрирования MySQL, основанный на php.
    • mysqlnavigator — Интерфейс QT к MySQL.
    • gmyclient — MySQL клиент, основанный на GNOME.
    • knoda — клиент MySQL для KDE.

    Это завершает вводное руководство к 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 в PHP

    PHP поддерживает работу с базой данных MySQL. Специальные встроенные функции для работы с MySQL позволяют просто и эффективно работать с этой СУБД: выполнять любые запросы, читать и записывать данные, обрабатывать ошибки.

    Сценарий, который подключается к БД, выполняет запрос и показывает результат, будет состоять всего из нескольких строк. Для работы с MySQL не надо ничего дополнительно устанавливать и настраивать; всё необходимое уже доступно вместе со стандартной поставкой PHP.

    Что такое mysqli?

    mysqli (MySQL Improved) — это расширение PHP, которое добавляет в язык полную поддержку баз данных MySQL. Это расширение поддерживает множество возможностей современных версий MySQL.

    Как выглядит работа с базой данных

    Типичный процесс работы с СУБД в PHP-сценарии состоит из нескольких шагов:

    1. Установить подключение к серверу СУБД, передав необходимые параметры: адрес, логин, пароль.
    2. Убедиться, что подключение прошло успешно: сервер СУБД доступен, логин и пароль верные и так далее.
    3. Сформировать правильный SQL запрос (например, на чтение данных из таблицы).
    4. Убедиться, что запрос был выполнен успешно.
    5. Получить результат от СУБД в виде массива из записей.
    6. Использовать полученные записи в своём сценарии (например, показать их в виде таблицы).

    Функция mysqli connect: соединение с MySQL

    Перед началом работы с данными внутри MySQL, нужно открыть соединение с сервером СУБД. В PHP это делается с помощью стандартной функции mysqli_connect() . Функция возвращает результат — ресурс соединения. Данный ресурс используется для всех следующих операций с MySQL.

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

    • Адрес сервера СУБД;
    • Логин;
    • Пароль.

    Если вы следовали стандартной процедуре установки MySQL или используете OpenServer, то адресом сервера будет localhost , логином — root . При использовании OpenServer пароль для подключения — это пустая строка ‘’ , а при самостоятельной установке MySQL пароль вы задавали в одном из шагов мастера установки.

    Базовый синтаксис функции mysqli_connect() :

    mysqli_connect(, , , );

    Проверка соединения

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

    Соединение с MySQL устанавливается один раз в сценарии, а затем используется при всех запросах к БД.

    Результатом выполнения функции mysqli_connect() будет значение специального типа — ресурс. Если подключение к MySQL не удалось, то функция mysqli_connect() вместо ресурса вернёт логическое значение типа «ложь» — false . Хорошей практикой будет всегда проверять результат выполнения этой функции и сравнивать его с ложью.

    Соединение с MySQL и проверка на ошибки:

     else < print("Соединение установлено успешно"); >?>

    Функция mysqli_connect_error() просто возвращает текстовое описание последней ошибки MySQL.

    Установка кодировки

    Первым делом после установки соединения крайне желательно явно задать кодировку, которая будет использоваться при обмене данными с MySQL. Если этого не сделать, то вместо записей со значениями, написанными кириллицей, можно получить последовательность из знаков вопроса: . . Вызовите эту функцию сразу после успешной установки соединения: mysqli_set_charset($con, «utf8»);

    Выполнение запросов

    Установив соединение и определив кодировку мы готовы выполнить свои первые SQL-запросы. Вы уже умеете составлять корректные SQL команды и выполнять их через консольный или визуальный интерфейс MySQL-клиента. Те же самые запросы можно отправлять без изменений и из PHP-сценария. Помогут в этом несколько встроенных функций языка.

    Два вида запросов

    Следует разделять все SQL-запросы на две группы:

    1. Чтение информации (SELECT).
    2. Модификация (UPDATE, INSERT, DELETE).

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

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

    Добавление записи

    Вернёмся к нашему проекту — дневнику наблюдений за погодой. Начнём практическую работу с заполнения таблиц данными. Для начала добавим хотя бы один город в таблицу cities .

    Выражение INSERT INTO используется для добавления новых записей в таблицу базы данных.

    Составим корректный SQL-запрос на вставку записи с именем города, а затем выполним его путём передачи этого запроса в функцию mysqli_query() , чтобы добавить новые данные в таблицу.

    Обратите внимание, что первым параметром для функции mysqli_query() передаётся ресурс подключения, полученный от функции mysqli_connect() , вторым параметром следует строка с SQL-запросом.

    При запросах на изменение данных (не SELECT) результатом выполнения будет логическое значение — true или false , которое будет означать, что запрос выполнить не удалось. Для получения строки с описанием ошибки существует функция mysqli_error($link) .

    Функция insert id: как получить идентификатор добавленной записи

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

    Здесь пригодится функция mysqli_insert_id() . Она принимает единственный аргумент — ресурс соединения, а возвращает идентификатор последней добавленной записи.

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

     else < $sql = 'INSERT INTO cities SET name = "Санкт-Петербург"'; $result = mysqli_query($link, $sql); if ($result == false) < print("Произошла ошибка при выполнении запроса"); >else < $city_id = mysqli_insert_id($link); $sql = 'INSERT INTO weather_log SET city_id = ' . $city_id . ', day = "2017-09-03", temperature = 10, cloud = 1'; $result = mysqli_query($link, $sql); if ($result == false) < print("Произошла ошибка при выполнении запроса"); >> >

    Чтение записей

    Другая частая операция при работе с базами данных в PHP — это получение записей из таблиц (запросы типа SELECT). Составим SQL-запрос, который будет использовать SELECT выражение. Затем выполним этот запрос с помощью функции mysqli_query() , чтобы получить данные из таблицы.

    В этом примере показано, как вывести все существующие города из таблицы cities :

    В примере выше результат выполнения функции mysqli_query() сохранён в переменной $result . В этой переменной находятся не данные из таблицы, а специальный тип данных — так называемая ссылка на результаты запроса.

    Чтобы получить действительные данные, то есть записи из таблицы, следует использовать другую функцию — mysqli_fetch_array() — и передать ей единственным параметром эту самую ссылку. Теперь каждый вызов функции mysqli_fetch_array() будет возвращать следующую запись из всего результирующего набора записей в виде ассоциативного массива.

    Цикл while здесь используется для «прохода» по всем записям из полученного набора записей. Значение поля каждой записи можно узнать просто обратившись по ключу этого ассоциативного массива.

    Как получить сразу все записи в виде двумерного массива

    Иногда бывает удобно после запроса на чтение не вызывать в цикле mysqli_fetch_array для извлечения очередной записи по порядку, а получить их сразу все одним вызовом. PHP так тоже умеет.

    Функция mysqli_fetch_all($res, MYSQLI_ASSOC) вернёт двумерный массив со всеми записями из результата последнего запроса. Перепишем пример с показом существующих городов с её использованием:

    Как узнать количество записей

    Часто бывает необходимо узнать, сколько всего записей вернёт выполненный SQL-запрос. Это может помочь при организации постраничной навигации или просто в качестве информации. Узнать число записей поможет функция mysqli_num_rows() , которой следует передать ссылку на результат запроса.

    «Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.

    Похожие публикации:
    1. Spring boot starter parent что это
    2. Sql shell psql как пользоваться
    3. Как создать массив в си шарп
    4. Что такое начертание курсив

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *