Мне часто друзья задают те или иные вопросы по администрированию и я подумал, почему бы вместо того, чтобы объяснять по 100 раз одно и то же, не писать такие статьи-заметки на сайте? К тому же, это прекрасная возможность в случае чего самому воспользоваться такой заметкой, ведь Вы сами знаете, удержать в голове порой всё не возможно и так или иначе иногда приходится обращаться к настольной книге, так пусть такой книгой станет один из разделов этого сайта. Сегодня мы поговорим о том, как работать с СУБД MySQL из командной строки, да, кто-то скажет — «Накой? Ведь есть phpMyAdmin», отвечаю — зачем тратить драгоценные ресурсы сервера, устанавливая всякую дрянь по типу WebMin, phpMyAdmin и т.п., если всё тоже самое легко можно проделать надёжными, проверенными средствами. Настройка из терминала по SSH-туннелю — наш выбор!
Что ж, приступим. Соединяемся с сервером по SSH, к примеру через всеми известный PuTTY (A Free Telnet/SSH Client). Теперь перейдём непосредственно к описанию команд.
Вход на сервер MySQL программой mysql:
mysql -u root -h localhost -p
Параметр -u указывает пользователя, который будет входить в систему. Параметр -h указывает узел сети. Обычно это localhost, если вы настраиваете не удаленный сервер. Наконец, -p сообщает программе-клиенту mysql, что для доступа к базе данных будет вводиться пароль. Обратите внимание на приглашение mysql>. Именно здесь вы будете вводить все свои команды. Теперь, находясь в среде mysql в качестве корневого пользователя, мы можем начать настройку базы данных.
Мы вошли, и на экране - приглашение mysql. Сначала взглянем на список уже имеющихся баз данных. Для этого введем команду SHOW DATABASES.
Ответ дает понять, что команда выполнена без каких-либо ошибок. В данном случае, изменилась одна строка. Это относится к главной базе данных mysql, в которой содержится список всех баз данных. Но вам не нужно слишком беспокоиться о второстепенных подробностях. Последнее число означает время выполнения запроса. Убедиться, что база данных создана, мы можем, снова запустив команду SHOW DATABASES.
Чтобы заняться созданием таблиц в новой базе данных test, нам потребуется установить ее как текущую. Для этого используем команду USE. Параметром этой команды указывается название базы данных, которую нужно сделать текущей. Еще текущую базу можно устанавливать в командной строке, указывая ее название после параметра -D. Давайте продолжим и переключимся на базу данных test.
Переключение базы данных:
USE test; |
Привилегии в MySQL
Предоставление привилегий командой GRANT
.
Привилегиями определяются возможности доступа пользователей к базам данных, таблицам… почти ко всему. Сейчас только суперпользователь root из MySQL может обращаться к базе данных test, согласно данным разрешениям. Давайте создадим ещё одного пользователя, например, admin (администратор), который будет обращаться к базе данных test и работать с информацией из нее. Но прежде, чем начать, давайте взглянем на несколько упрощенный формат команды GRANT.
Примечание
: Команда GRANT считается способом создания пользователя. Поздние версии MySQL, однако, также содержат функцию CREATE_USER, хотя GRANT до сих пор предпочтительнее.
Теперь нужны привилегии, которые можно присваивать. Используя все вышесказанное, можно устанавливать следующие привилегии:
Примечание : Если вы используете MySQL для обмена данными с веб-приложением, то разъясняемые здесь привилегии CREATE, SELECT, INSERT, а также привилегии DELETE и UPDATE (описанные в руководстве по MySQL, раздел GRANT and REVOKE Syntax (англ.)) - единственные, которые, вероятно, потребуются. Многие совершают ошибку, раздавая все привилегии, когда это, в действительности, не нужно. Сверьтесь с разработчиками приложений, действительно ли такие разрешения создадут проблемы в работе.
Создание пользователя admin:
Итак, пользователи созданы; теперь протестируем их. Сначала выйдем из mysql, написав quit в строке приглашения:
Параметры объясняются в разделе команды GRANT. А сейчас мы запретим пользователю любой вид доступа. Скажем, мы выяснили, что учетная запись admin вызывает проблемы с безопасностью. Мы решаем отозвать все права. Заходим как root делаем необходимое.
Отзыв разрешений для пользователя admin:
REVOKE ALL ON test.* FROM "admin" @"localhost" ; |
Примечание : В данном случае, доступ пользователя прост, поэтому отмена прав на одну базу данных - не проблема. Но обычно вам, скорее всего, потребуется использовать *.* вместо test.*, чтобы заодно отменить доступ пользователя ко всем остальным базам данных.
Теперь взглянем, как полностью удалить учетную запись командой DELETE, и посмотрим на таблицу пользователей MySQL.
Удаление учетных записей командой DELETE
.
Таблица пользователей MySQL - это список пользователей и информации о них. Убедитесь, что вы зашли как root. Используйте основную базу данных MySQL.
Использование основной базы mysql:
Таблица user - та, что нужна. В ней 30 различных полей, и ее сложно читать. Для облегчения чтения используем третью форму команды SELECT. Искомые поля - Host (узел) и User (пользователь).
Нахождение пользователя admin в таблице user:
Теперь, получив информацию, мы можем избавиться от пользователя admin. Это делается командой DELETE; вот ее синтаксис:
Вы могли заметить, что формат DELETE чем-то схож с форматом SELECT. Укажем поле User и значение admin. Это удалит запись из таблицы user, где пользователь - admin, удаляя нашу учетную запись. Сделаем так:
Удаление пользователя admin:
Команда FLUSH PRIVILEGES нужна, чтобы обновить разрешения.
На сегодня всё. Согласитесь, всё просто! ;)
Установка и администрирование сервера MySQL.
Инсталляция и первый запуск MySQL.
Установка.
Установка MySQL проходит довольно просто.Для начала нужно скачать и распаковать исходники (как это делать я думаю писать не стоит).Найти их можно на сайте MySQL в разделе -на любой вкус и для любой ОС,так как MySQL работает практически на всех платформах.
Если вы работаете на Unix-платформе(в частности я буду приводить примеры для Linux,которые немногим отличаются для других Unix-платформ),нужно выполнить следующие шаги:
| shell> gunzip mysql-VERSION-OS.tar.gz | tar xvf - shell> ln -s mysql-VERSION-OS mysql shell> cd mysql shell> scripts/mysql_install_db shell> bin/safe_mysqld & |
Распаковывать исходники нужно в каталог /usr/local,так как в настроечных скриптах указывается именно эта директория и создать симлинк для каталога mysql-это делается,потому что в тех же скриптах указывается этот каталог.Нужно подредактировать скрипты mysql_install_db и safe_mysqld применительно к вашей системе,для того,чтобы они начали работать,в частности исправить путь для указания местоположения mysqld и баз данных. Для запуска демона mysqld нужно перейти в каталог /usr/local/mysql/bin и запустить скрипт safe_mysql в фоновом режиме.Если вы не получили никаких сообщений,это уже хорошо-значит,все прошло успешно.Если же появилось сообщение об ошибке,значит что-то не в порядке со скриптами.Можно настроить запуск сервера mysql во время загрузки компьютера.Для этого нужно поместить запуск safe_mysql в один из файлов инициализации системы(они разные в зависимости от версии Linux). Для Linux Slakware в файл /etc/rc.d/rc.local нужно добавить:
/usr/local/mysql/bin/safe_mysqld &
Что касается Windows,поклонником которого я являюсь,то здесь установка проходит практически без проблем.Распаковываете zip-архив,запускаете exe-файл,а дальше все работает само.Устанавливается обычно в каталог c:mysql. В Windows NT MySQL устанавливается в виде службы и можно указать способ запуска-ручной или автоматический (Start-Settings-Control Panel-Services-MySQL-Startup).
Запуск MySQL.
Управление сервером обычно осуществляется из командной строки.Насчет Linux я уже писал,а в Windows 95/98 откройте сеанс DOS и выполните следующие команды:
| cd mysqlbin mysqlbin>mysqld --standalone |
Эта команда запустит демон mysql в фоновом режиме.В Windows 95/98 не предусмотрен запуск mysqld в виде службы.Отсутствие сообщений при этом -хороший знак,значит все в порядке.
При запуске mysqld можно указывать следующие опции:
| -?, --help | Справка |
| -b, --basedir= | Путь к каталогу в котором установлен mysql |
| -h, --datadir | Путь к каталогу,в котором хранятся базы данных. |
| -l, --log= | Имя журнала транзакций |
| -L, --language= | Язык по умолчанию(обычно English). |
| -P, --port= | Порт для соединения. |
| --skip-grant-tables | Игнорировать таблицы привилегий. Это дает любому ПОЛНЫЙ доступ ко всем таблицам.Не следует предоставлять обычным пользователям разрешений на запуск mysqld. |
| --skip-name-resolve | Позволяет предоставлять доступ только тем хостам,чьи IP-адреса указаны в таблицах привилегий.Ипользуется для более высокого уровня защиты. |
| --skip-networking | Использовать подключения только через интерфейс localhost. |
| -V, --version | Вывести информацию о версии. |
Теперь можно попытаться войти в сервер.Для этого используется команда mysql.Изначально существует единственный пользователь,которому предоставляется право входа-root ,которая не имеет пароля.Первое,что нужно сделать-войти под именем root и установить для него пароль. Команда mysql может использовать следующие опции:
Примечание. Команды mysqld и mysql имеют еще некоторые опции,но в данный момент они особого интереса не представляют.
После установки пароля для root нужно перезагрузить сервер командой mysqladmin reload,чтобы изменения вступили в силу.После этого можно попробовать войти снова:
| mysqlbinmysql -u root -p mysql Enter password:******* |
Система привилегий и безопасность в MySQL.
База данных mysql и таблицы привилегий.
Итак,вы успешно вошли в базу данных mysql,которая используется для администрирования сервера.Что же здесь находится?А находятся здесь 5 таблиц,которые ничем не отличаются от других таблий баз данных,за исключением того,что эти таблицы используются для предоставления доступа к базам данных и таблицам в них пользователям.Рассмотрим каждую из них.
Введите следующую команду,которая покажет таблицы в базе данных mysql:
mysql>show tables;
|
Кратко рассмотрим функции каждой из таблиц:
- User
Определяет,разрешено ли пользователю,пытающемуся подключиться к серверу делать это.Содержит имя пользователя,пароль а также привилегии.Если ввести команду show columns from user; ,то получим следующее:Field Type Null Key Default Extra Host char(60) PRI User char(16) PRI Password char(8) Select_priv char(1) N Insert_priv char(1) N Update_priv char(1) N Delete_priv char(1) N Create_priv char(1) N Drop_priv char(1) N Reload_priv char(1) N Shutdown_priv char(1) N Process_priv char(1) N File_priv char(1) N Изначально эта таблица содержит пользователя root с паролем,который вы установили и именем хоста "%".По умолчанию root может входить с любого хоста,имеет все привилегии и доступ ко всем базам данных.Также в таблице содержится запись для пользователя "%",которую нужно сразу же удалить,так как она предоставляет доступ любому пользователю.
delete from user where user="%"; Для добавления нового пользователя выполните следующие действия:
insert into user (host,user,password) values ("%.domain.com","john",password("df456"); insert into user (host,user,password) values ("localhost,"mary",password("kitchen"); select host,user,password from user; Host User Password % root 456g879k34df9 %.domain.com john 657t234d980hg6 localhost mary 234d76gh88rt9 Пояснения:
1.Команда insert вставляет данные в таблицу,не забывайте завершать команды ";".
2.При вводе пароля используйте функцию password(),иначе пароль работать не будет!
3.Все пароли шифруются mysql,поэтому в поле Password вы видите абракадаьры.Это делается в целях безопасности.
4.Не есть хорошей практикой назначать привилегии пользователям в таблице user,так как в этом случае они являются глобальными и распространяются на все базы данных.Предоставляйте привилегии каждому пользователю к конкретной базе данных в таблице db,которая будет рассмотрена далее.
5.При задании имени хоста для входа через сеть рекомендуется явно указывать полное имя хоста,а не "%".В приведенном выше примере юзеру john разрешается вход на сервер со всех машин домена domain.com.Можно также указывать IP-адреса машин и маски подсетей для большей безопасности.
Определяет к каким базам данных каким пользователям и с каких хостов разрешен доступ.В этой таблице можно предоставлять каждому пользователю доступ к базам данных и назначать привилегии. Eсли выполнить команду show columns from db; получим следующее:Field Type Null Default Extra Host char(60) PRI Db char(32) PRI User char(16) PRI Select_priv char(1) N Insert_priv char(1) N Update_priv char(1) N Delete_priv char(1) N Create_priv char(1) N Drop_priv char(1) N По умолчанию,все привилегии установлены в "N".Например,предоставим юзеру john доступ к базе данных library и дадим ему привилегии select,insert и update(описание основных команд mysql будет дано в отдельном разделе,сейчас моя цель-показать,как работают таблицы привилегий).
insert into db (host,user,db,select_priv,insert_priv,update_priv) values ("%.domain.com","john","library","Y","Y","Y"); Привилегии,устанавливаемые в таблице db,распространяются только на базу данных library.Если же установить эти привилегии в таблице user,то они будут распространяться и на другие базы данных,даже если доступ к ним и не установлен явно.
- Host
Таблица host используется для расширения диапазона доступа в таблице db.К примеру,если доступ к какой-либо базе данных должен быть предоставлен более чем одному хосту,тогда следует оставить пустой колонку host в таблице db,и внести в таблицу host необходимые имена хостов. Выполним команду show columns from host;
Field Type Null Key Default Extra Host char(60) PRI Db char(32) PRI Select_priv char(1) N Insert_priv char(1) N Update_priv char(1) N Delete_priv char(1) N Create_priv char(1) N Drop_priv char(1) N
Как видно из таблицы,здесь также можно задавать привилегии для доступа к базе данных.Они обычно редко используются без необходимости.Все привилегии доступа нужно задавать в таблице db для каждого пользователя,а в таблице host только перечислить имена хостов.Сервер читает все таблицы,проверяет имя пользователя,пароль,имя хоста,имя базы данных,привилегии.Если в таблице db привилегии select,insert установлены в "Y",а в таблице host в "N",то в итоге юзер все равно получит "Y".Чтобы не вносить путаницы,лучше назначать привилегии в таблице db.
Эти 3 таблицы являются основными.В новых версиях MySQL,начиная с 3.22 добавлены еще 2 таблицы- tables_priv и columns_priv,которые позволяют задать права доступа к определенной таблице в базе данных и даже к определенной колонке.Они работают подобно таблице db,только ссылаются на таблицы и колонки. Также,начиная с версии 3.22 можно использовать команду GRANT для предоставления доступа к базам данных,таблицам и колонкам таблиц,что избавляет от необходимости вручную модифицировать таблицы db,tables_priv и columns_priv.Команда GRANT будет подробно рассмотрена в следующих разделах.
Привилегии,предоставляемые MySQL.
| Привилегия | Колонка | Где используется |
| select | Select_priv | таблицы |
| insert | Insert_priv | таблицы |
| update | Update_priv | таблицы |
| delete | Delete_priv | таблицы |
| index | Index_priv | таблицы |
| alter | Alter_priv | таблицы |
| create | Create_priv | БД,таблицы,индексы |
| drop | Drop_priv | БД или таблицы |
| grant | Grant_priv | БД или таблицы |
| references | References_priv | БД или таблицы |
| reload | Reload_priv | администрирование сервера |
| shutdown | Shutdown_priv | администрирование сервера |
| process | Process_priv | администрирование сервера |
| file | File_priv | доступ к файлам на сервере |
Select -используется для извлечения информации из таблиц.Select можно также использовать не имея разрешения на доступ к какой-либо БД,например,как калькулятор.
Insert -используется для вставки информации в таблицу.Например:
Delete -используется для удаления информации из таблиц.
Create -позволяет создавать базы данных и таблицы в них.Примеры:
| mysql>mysqladmin -u john -ptest create mydb //Создает пустую БД. mysql>use mydb; Database changed. create table monitoring (id int(5) not null auto_increment, first_name varchar(15) not null, last_name varchar(20) not null, address varchar(80) not null, primary key (id), key(last_name)); |
Drop -ипользуется для удаления таблиц и баз данных.
Grant -позволяет пользователю предоставлять другим пользователям привилегии,которыми он сам обладает.Два пользователя с различными привилегиями и привилегий GRANT могут комбинировать свои разрешения.
Index -позволяет пользователю создавать и удалять индексы.
File -пользователь,обладающий этим правом,может выполнять команды LOAD DATA INFILE и SELECT...INTO OUTFILE и может читать и записывать любой файл на сервере MySQL.
Последние 3 привилегии используются для администрирования сервера из команды mysqladmin .
Специальные пользователи MySQL.
В MySQL существуют 3 специальных пользователя:monty,admin и dummy.
- monty
Суперпользователь.Может входить на сервер как с локального хоста,так и с любого хоста в сети.Имеет доступ ко всем базам данных и все привилегии,но если root может в первый раз войти без пароля,monty должен использовать пароль и должен быть добавлен в таблицы привилегий вручную.
Следует заметить,что следует внести данные как для localhost,так и для "%",то есть любого хоста в сети.
- admin
Пользователь,который может входить на сервер с локального хоста без пароля и которому назначаются административные привилегии Reload и Process .Пользователь admin может использовать команды mysqladmin reload, mysqladmin refresh и mysqladmin flush-* ,а также mysqladmin processlist .
Доступа к базам данных admin не имеет.Привилегии для доступа к определенным БД должны быть назначены индивидуально-или в таблице db,или командой GRANT.
- dummy
Пользователь,который может входить на сервер без пароля,но только с локального хоста.Все глобальные привилегии устанавливаются в "N" кроме "USAGE",что позволяет dummy создавать пользователей без привилегий.Права доступа к базам данных должны быть заданы root"ом.
Создание и модификация баз данных и таблиц в MySQL.
Создание баз данных.
Создание базы данных в MySQL производится с помощью утилиты mysqladmin .Изначально существует только БД mysql для администратора и БД test,в которую может войти любой пользователь и которая по умолчанию пуста.Приведенный ниже пример иллюстрирует создание базы данных.
Или,находясь в другой базе данных,например в mysql ввести команду:
| mysql>use data1 Database changed. |
Теперь можно создавать таблицы и вводить информацию.
Типы данных в MySQL.
Прежде чем создавать таблицы,необходимо ознакомиться с тем,какие типы данных поддерживает MySQL.Все они представлены в нижеследующей таблице:
| BIGINT [(length)] | 8 байт целое | ||
| BLOB | Двоичный объект (максимальная длина 65535 байт) | ||
| CHAR(NUM) | Строка фиксированной длины (1 | DATE | Сохраняет информацию о дате. Использует формат "YYYY-MM-DD". Может модифицироваться как строка или число. YYYY-MM-DD ("- " может быть ЛЮБОЙ не цифрой) YY-MM-DD ("- " может бытьЛЮБОЙ не цифрой) YYMMDD YYMM Диапазон для этого типа данных от 0000-00-00 до 9999-12-31. В отличие от TIMESTAMP, DATE принимает годы и в виде двух цифр от 0000 до 0099. Это не очень полезно в большинстве случаев. Используйте задание лет четырьмя цифрами в полях типа DATE. Тип DATE имеет длину 4 байта. |
| DATETIME | Объединение типов DATE и TIME. Тип DATETIME идентичен типу TIMESTAMP со следующими исключениями: Когда запись вставляется в таблицу, содержащую поля типа DATETIME, поле DATETIME не изменяется. Диапазон для поля типа DATETIME: "0000-01-01 00:00:00" - "9999-12-31 23:59:59" при использовании в контексте строки, и "00000000000000" - "99991231235959" при использовании в контексте числа. Тип DATETIME имеет длину 8 байт. | ||
| DECIMAL (length,dec) | Десятичное число с плавающей запятой. | ||
| DOUBLE [(length,dec)] | Число (4 или 8 байт) двойной точности с максимальной длиной и фиксированном числом десятичных чисел. | ||
| FLOAT [(precision)] | Номер с плавающей запятой. FLOAT(4) и FLOAT одиночная точность. FLOAT(8) обеспечивает двойную точность. | ||
| FLOAT [(length,decimals)] | Число одиночной точности с максимальной длиной и фиксированном числом десятичных чисел (4 байта). | ||
| INT [(length)] | Целое (4 байта). | ||
| INTEGER [(length)] | Целое число 4 байта | ||
| LONGBLOB | Двоичный объект с максимальной длиной 2**32 байт. | ||
| MEDIUMBLOB | Двоичный объект с максимальной длиной 16777216 байт. | ||
| MEDIUMINT [(length)] | Целое (3 байта). | ||
| REAL [(length,dec)] | Идентично DOUBLE (8 байт). | ||
| SMALLINT [(length)] | Целое (2 байта). | ||
| TINYBLOB | Двоичный объект с максимальной длиной 255 байт. | ||
| TINYINT [(length)] | Целое число (1 байт). | ||
| VARCHAR(NUM) | Строка переменной длины (1 | TIME | Хранит информацию о времени. Использует формат "HH:MM:SS". Может использоваться как строка или число. MySQL тип TIME понимает следующий синтаксис. HH:MM:DD HHMMDD HHMM HH Данные типа TIME имеют длину 3 байта. |
| TIMESTAMP(NUM) | Автоматически изменяется при вставке/обновлении. Имеет формат YYMMDDHHMMSS или YYYYMMDDHHMMSS. Вы можете модифицировать поле TIMESTAMP при выполнении INSERT. Это полезно, когда Вы хотите установить произвольную дату/время для записи. В течение модификаций Вы не должны определять значение для вашего поля TIMESTAMP, или определять NULL как значение, для вставки. Иначе вы получите недопустимое значение для этого поля. Когда используете mysql с ODBC и Access Вы должны использовать значение 14 для NUM, поскольку это заставляет MySQL всегда использовать в годах четыре цифры. Значение 12 заставит MySQL использовать в году две цифры. Значение по умолчанию - 14. Обратите внимание, что в случае таблиц с несколькими полями TIMESTAMP только первое такое поле будет модифицироваться автоматически. |
Создание и модификация таблиц.
Cоздадим таблицу customers в БД data1:
| mysql>use data1 Database changed. mysql>create table customers (emp_id int(4) not null auto_increment, emp_name varchar(10) not null, emp_lname varchar(15) not null, address varchar(60) not null, phone int(10), primary key(emp_id)); |
Мы создали пустую таблицу.Вводить данные в нее можно несколькими способами:
а)вручную,используя команду insert into
;
б)загрузить данные из текстового файла,что является более предпочтительным,особенно если нужно ввести несколько тысяч записей.Синтаксис этой команды будет описан позже.
в)использовать утилиту mysqlimport также для загрузки данных из текстового файла.
Пример ввода данных вручную:
Что касается auto_increment в столбце emp_id ,это означает,что числовое значение этого столбца будет автоматически увеличиваться на единицу с каждой новой записью.То есть,если мы ввели значение 1001,то следующее будет 1002 и т.д.Значение в такой столбец вводится один раз для задания точки отсчета,а дальше сервер будет сам подставлять нужные значения.
Синтаксис команды LOAD DATA INFILE.
DATA INFILE "file_name.txt" INTO TABLE tbl_name ENCLOSED BY ""] ] [(col_name,...)]Предположим,существует некоторый текстовый файл 123.txt,содержащий 2000 записей,которые нужно внести в таблицу.Нужно создать таблицу,имеющую такую же структуру и такое же число полей,как и файл(а также подходящие типы данных).Предположим,что поля в файле разделены запятыми.Кроме того,файл должен находиться в нужной базе данных. Вводим следующую команду:
LOAD DATA INFILE "123.txt" into table customers fields terminated by ",";
Это все.Данные из файла помещаются в таблицу.
Для модификации таблиц и данных в них используются команды update и alter table . Рассмотрим их действие на примере таблицы customers:
| emp_id | emp_name | emp_lname | address | phone |
|---|---|---|---|---|
| 1001 | John | Walker | New York | 1235468798 |
| 1002 | Bill | Smith | Chicago | 7650945237 |
| 1003 | Jack | Nicolson | Dallas | 9874325097 |
Пример действия команды alter table :
Можете проверить это сами:)
Изменение данных в таблицах производится с помощью команды update .Для примера возьмем ту же таблицу buyers.
| mysql>update buyers set address="Seattle" where emp_lname="Smith"; |
Для модификации таблиц используются также команды Drop и Delete .
Delete
-удаляет строку из таблицы.Например,если ввести команду
delete from buyers where emp_id="1002"
будет удалена строка для Bill Smith.
Drop -если используется в Alter table удаляет колонку из таблицы.
Команда Drop также используется для удаления таблиц и баз данных.Пользоваться ею следует осторожно,так как любое удаление необратимо,восстановить данные можно только из резервной копии.
Общие проблемы и их разрешение.
Наиболее распространенной проблемой является сообщение Access denied for user... Если это рядовой пользователь,то с этим может разобраться администратор,если же такое сообщение получает администратор,то это уже проблема. Очень часто при установке пароля для root администратор забывает использовать функцию password() ,а просто вводит:
update user set password="mamamia" where user="root";
После этого при попытке войти в базу данных он получает сообщение Access denied.Такой пароль не будет прочитан.Правильно нужно ввести:
update user set password=password("mamamia") where user="root";
Это очень важный шаг и необходимо быть внимательным.Единственный выход при этом в Windows-деинсталлировать сервер и установить по новой,благо еще ничего не настроено.
Если такую проблему испытывает пользователь,вероятно причина в неправильной настройке таблиц привилегий,в первую очередь та же установка пароля,или записи в таблицах db и host неверны.Рассмотрим это на простом примере:
|
|
Очень важный нюанс:в таблице User для jerry указано разрешение Select,которое является глобальным,то есть справедливо для любой базы данных,даже если он не имеет туда доступа.Например,если jerry введет команду:
select * from data2.authors;
он получит доступ к этой таблице,хотя в таблице Db не назначен доступ к базе data2.Поэтому,без особой необходимости не нужно предоставлять пользователям привилегии в таблице User,а делать это в таблице Db для каждой конкретной базы данных.
Предположим,что jerry временно работает в другом отделе,который находится в другом домене,и когда он пытается получить доступ к базе данных,он получает Access denied,поскольку компьютерам этого домена не разрешен доступ к базе данных.В данном случае нужно поступить следующим образом:очистить поле Host в таблице Db,а в таблицу Host занести следующие данные:
update db set host="" where user="jerry";
insert into host (host,db) values ("%.domain.com","data1");
insert into host (host,db) values ("monster.domain2.com","data1");
Привилегии в таблице Host указывать не нужно.Главное,чтобы данные в таблицах привилегий нигде не дублировались,чтобы не возникало противоречий.Данные для пользователя-имя и пароль,должны назначаться в таблице User,привилегии-в таблице Db,хосты,с которых разрешен доступ,в таблице Host.Если все внимательно вводить,проблем возникать не должно.
В MySQL версий 3.22 и выше работает очень хорошая команда GRANT,с помощью которой можно предоставлять пользователям доступ к базам данных и таблицам без модификации таблиц привилегий вручную.
GRANT select,insert update,delete on data1.telephone to jerry@%.domain.com identified by "password";
С помощью этой команды можно указывать привилегии на доступ даже к определенным столбцам таблицы:
GRANT select(id,name,address,phone),update(address,phone) on data2.customers to jerry@%.domain.com identified by "password";
Применение этой команды автоматически помещает данные в таблицы Db,Tables_priv и Column_priv,что избавляет от необходимости их ручной модификации.
Если нужно отнять у пользователя какую-либо привилегию,используйте команду REVOKE.
REVOKE update(address,phone) on data2.customers from jerry@%.domain.com;
Запросы к базе данных и команда Select.
Я не ставлю целью углубляться в изучение языка SQL,об этом вы можете прочитать в любом руководстве по SQL Server,MySQL в основном поддерживает все основные команды стандарта ANSI 92,но команда Select заслуживает того,чтобы посвятить ей отдельную главу. Команда Select используется для запросов к базе данных с целью извлечения из нее информации.Синтаксис команды следующий:
SELECT select_expression,... , ...] HAVING full_where_definition rows] ]
Как видно из вышеприведенного,вместе с командой Select используются ключевые слова,использование которых очень влияет на ответ сервера.Рассмотрим каждое из них.
- DISTINCT.
.
Пропускает строки,в которых все выбранные поля идентичны,то есть устраняет дублирование данных.
- WHERE
.
Предложение команды Select,которое позволяет устанавливать предикаты,условие которых может быть верным или неверным для любой строки таблицы.Извлекаются только те строки,для которых такое утверждение верно.Например:
SELECT u_id,lname from publishers WHERE city ="New York";
Выводит колонки u_id и lname из таблицы publishers для которых значение в столбце city-New York.Это дает возможность сделать запрос более конкретным.
- Реляционные операторы.
Реляционный оператор - математический символ который указывает на определенный тип сравнения между двумя значениями. Реляционные операторы которыми располагает MySQL:
Равнo > Больше = Больше или равно Не равно
Эти операторы имеют стандартные значения для числовых значений.
Предположим что вы хотите увидеть всех заказчиков с оценкой(rating) выше 200. Так как 200 - это скалярное значение, как и значение в столбце оценки, для их сравнения вы можете использовать реляционный оператор.
SELECT * FROM Customers WHERE rating > 200;
- Булевы операторы.
Основные Булевы операторы также распознаются в MySQL. Выражения Буля - являются или верными или неверными, подобно предикатам. Булевы операторы связывают одно или более верных/неверных значений и производят единственное верное или неверное значение. Стандартными операторами Буля распознаваемыми в SQL являются:AND,OR и NOT.
Предположим вы хотите видеть всех заказчиков в Далласе,которые имеют рейтинг выше 200:
SELECT * FROM Customers WHERE city = "Dallas" AND rating > 200;
При использовании оператора AND,должны быть выполнены оба условия,то есть должны быть выбраны все заказчики из Далласа,рейтинг которых больше 200.
При использовании оператора OR,должно выполниться одно из условий.Например:
SELECT * FROM Customers WHERE city = "Dallas " OR rating > 200;
В данном случае будут выбраны все заказчики из Далласа и все имеющие рейтинг больше 200,даже если они и не из Далласа.
NOT может использоваться для инвертирования значений Буля.Пример запроса с NOT:
SELECT * FROM Customers WHERE city = "Dallas" OR NOT rating > 200;
При таком запросе будут выбраны все заказчики из Далласа и все заказчики,рейтинг которых меньше 200.В этом запросе оператор NOT применяется только к выражению rating >200.Можно сделать более сложный запрос:
SELECT * FROM Customers WHERE NOT(city = "Dallas" OR rating > 200);
В этом запросе NOT применен к обеим выражениям в скобках.В данном случае,сервер читает выражения в скобках,определяет, соответствует ли истине равенство city = "Dallas" или равенство rating > 200.Если любое условие верно, выражение Буля внутри круглых скобок верно. Однако, если выражение Буля внутри круглых скобок верно,предикат как единое целое неверен, потому что NOT преобразует верно в неверно и наоборот.То есть,будут выбраны все заказчики не находящиеся в Далласе и рейтинг которых меньше 200.
- IN.
Оператор IN определяет набор значений в которое данное значение может или не может быть включено.Например,запрос
SELECT * FROM Salespeople WHERE city = "Barcelona" OR city = "London";
может быть переписан более просто:
SELECT * FROM Salespeople WHERE city IN ("Barcelona", "London");
IN определяет набор значений с помощью имен членов набора заключенных в круглые скобки и отделенных запятыми.Затем он проверяет различные значения указанного,пытаясь найти совпадение со значениями из набора. Если это случается, то предикат верен. Когда набор содержит значения номеров а не символов, одиночные кавычки опускаются.
- BETWEEN.
Оператор BETWEEN похож на оператор IN. В отличии от определения по номерам из набора, как это делает IN, BETWEEN определяет диапазон, значения которого должны уменьшаться что делает предикат верным. Вы должны ввести ключевое слово BETWEEN с начальным значением, ключевое AND и конечное значение. В отличие от IN, BETWEEN чувствителен к порядку, и первое значение в предложении должно быть первым по алфавитному или числовому порядку.Например:
SELECT * FROM Salespeople WHERE comm BETWEEN .10 AND .12;
SELECT * FROM Salespeople WHERE city BETWEEN "Berlin" AND "London"; - LIKE.
LIKE применим только к полям типа CHAR или VARCHAR, с которыми он используется чтобы находить подстроки. Т.е. он ищет поле символа чтобы видеть, совпадает ли с условием часть его строки.В качестве условия он использует групповые символы(wildkards) - специальные символы которые могут соответствовать чему-нибудь. Имеются два типа групповых символов используемых с LIKE:
- знак "%",замещающий любое количество символов.
символ подчеркивания (_) замещает любой одиночный символ.
Если мы зададим следующие условия:
SELECT * FROM Customers WHERE fname LIKE "J%";
то будут выбраны все заказчики,чьи имена начинаются на J:John,Jerry,James и т.д.
- COUNT
.
Агрегатная функция,производит подсчет значений в столбце или числа строк в таблице.При работе со столбцом использует DISTINCT в качестве аргумента:
SELECT COUNT (DISTINCT snum) FROM Orders;
При подсчете строк имеет синтаксис:
SELECT COUNT (*) FROM Customers;
- GROUP BY.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля, и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, предположим что вы хотите найти наибольшую сумму приобретений полученную каждым продавцом. Вы можете сделать раздельный запрос для каждого из них, выбрав MAX () из таблицы для каждого значения поля. GROUP BY позволит Вам поместить их все в одну команду:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum;
- HAVING.
HAVING определяет критерии используемые чтобы удалять определенные группы из вывода, точно также как предложение WHERE делает это для индивидуальных строк.Например:
SELECT cid,cname,price,max(price) //max()-это тоже агрегатная функция FROM customers HAVING max(price)>500;
HAVING действует сходно с WHERE,но с WHERE нельзя использовать агрегатные функции.
- ORDER BY.
Эта команда упорядочивает вывод запроса согласно значениям в том или ином количестве выбранных столбцов. Многочисленные столбцы упорядочиваются один внутри другого,также как с GROUP BY.
- EXISTS.
Используется в подзапросах.
SELECT cnum, cname, city FROM Customers WHERE EXISTS (SELECT * FROM Customers WHERE city = " San Jose");
Он берет подзапрос как аргумент и оценивает его как верный если тот производит любой вывод или как неверный если тот не делает этого.Этим он отличается от других операторов предиката, в которых он не может быть неизвестным. Например, мы можем решить, извлекать ли нам некоторые данные из таблицы Заказчиков если, и только если, один или более заказчиков в этой таблице находятся в San Jose.
- UNION.
UNION отличается от подзапросов тем что в нем ни один из двух (или больше) запросов не управляются другим запросом. Все запросы выполняются независимо друг от друга, а уже вывод их - объединяется.Например:
SELECT snum, sname FROM Salespeople WHERE city = "London" UNION SELECT cnum, cname FROM Customers WHERE city = "London";
Предложение UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов.
- DESC,ASC.
DESC-DESCEDENT,вывод данных в обратном порядке(по алфавиту и численным значениям).По умолчанию используется ASC.
Ну вот вкратце и все.MySQL поддерживает почти все основные команды SQL Server,так что более подробно о команде SELECT вы можете прочитать в любом учебнике по языку SQL.
Основные утилиты MySQL.
В состав дистрибутива MySQL входят следующие утилиты:
Mysqladmin
Утилита для администрирования сервера.Может использоваться администратором,а также некоторыми пользователями,которым предоставлены определенные привилегии,например-Reload_priv,Shutdown_priv,Process_priv и File_priv .Данная команда может использоваться для создания баз данных,изменения пароля пользователя(администратор может изменить пароль любому пользователю,а рядовой пользователь-только свой собственный),перезагрузки и остановки сервера,просмотра списка процессов,запущенных на сервере. Mysqladmin поддерживает следующие команды:
Пример использования mysqladmin для изменения пароля:
mysqladmin -u bob password srawberry
Следует заметить,что в случае использования mysqladmin для установки пароля,не требуется использование функции password().Mysqladmin сам заботится о шифровании пароля.
Mysqlaccess
Используется для проверки привилегий пользователя для доступа к конкретной базе данных. Общий синтаксис:
mysqlaccess опции
Полезная утилита для проверки прав доступа пользователя,если он получает сообщение Access denied,при попытке соединиться с базой данных.
Опции:
Mysqlshow
Используется,чтобы показать,с какими базами данных работает сервер,какие таблицы содержит каждая БД и какие колонки есть в каждой таблице.Синтаксис:
mysqlshow [опции] ]]
Mysqlshow может использовать следующие параметры:
Если ввести mysqlshow без аргументов,будут показаны все базы данных,если указать имя БД, будут показаны все таблицы в ней.
Mysqldump
Используется для для создания дампа содержания базы данных (резервной копии).Можно записать содержимое базы данных в файл.Синтаксис:
mysqldump [опции] ]]
Mysqldump может использовать следующие параметры:
Isamchk
С помощью этой утилиты можно осуществлять восстановление таблиц,управление таблицами и сбор статистики.Синтаксис:
isamchk [-?adeiqrsvwzIV] [-k #] [-O xxxx=size] [-Si] [-Sr #] [-O keybuffer=#]
[-O readbuffer=#] [-O writebuffer=#] [-O sort key blocks=#] files
Isamchk используется для проверки непротиворечивости таблиц и устранения проблем,которые это может вызвать. Если пользователь часто удаляет и добавляет данные в таблицах,эта утилита будет очень полезной. Isamchk может использовать следующие опции:
При использовании опции -r нужно остановить mysqld.Перед началом работы нужно перейти в каталог,где находится таблица,нуждающаяся в проверке или ремонте.
Isamlog
Журнал транзакций,используется с опцией --log-isam=file_name демона mysqld.В файл записываются все изменения,происходящие в базе данных.Может использоваться для восстановления базы данных. Если есть резервная копия базы данных до того,как был сгенерирован isam-журнал,то информацию можно легко восстановить. Если нет копии,то нужно использовать все журналы с начала работы базы данных.
Safe_mysqld
Cкрипт для запуска демона mysqld в Unix-версиях.Выполняется при начальной загрузке системы. Для автоматического запуска нужно добавить запись в один из файлов инициализации системы.
| Создание сайтов | | |
Конфигурирование сервера MySQL может производиться двумя путями — путем задания опций в командной строке при запуске сервера и через конфигурационный файл my.ini. Также следует различать опциональные параметры, которые могут задаваться для текущего сеанса работы сервера и теряться при перезагрузке, и переменные окружения, которые действуют глобально для всего сервера и всех баз данных — изменить их можно, только прямо указывая новые значения.
Список всех доступных команд и опций можно получить, введя в консоли "mysqld --help". Следует сказать, что практически все команды универсальны и поддерживаются на всех платформах, где работает сервер MySQL, но существует несколько специфических команд для платформы Windows NT. Для всех команд существует полный синтаксис, когда команда начинается двумя символами тире (минус) "--", за которыми сразу, без пробелов, следует название команды (или опции, далее эти слова будут синонимами). Если требуется задать какое-либо значение, после названия команды следует знак "=" и задаваемое значение. Сокращенная форма предусматривает один знак тире, название команды сокращается до одой буквы, а параметры задаются через пробел. Только опция "--socket" не имеет короткой формы (она должна бы быть "-s"). Помните, что написание команд чувствительно к регистру символов, и команды "-l" и "-L" совершенно разные. В конфигурационном файле можно задавать комментарии — они отделяются знаком "#", и вся строка до конца игнорируется.
 |
Сначала познакомимся с самыми важными командами.
Команда --help самая важная, она выводит список вех доступных команд
Help или -? (в серверах версии 4.1 и выше нет краткой команды "-h"; кроме того, для запуска команды надо предварительно задать команду "--verbose") — выводит справку по доступным командам и опциям, сам сервер при этом не запускается.
--version или -V — пригодится, если надо узнать точную версию сервера MySQL и платформу, на которой он запущен.
--user= или -u — задает имя пользователя, с правами которого запускается сервер. Обычно не рекомендуется запускать сервер от имени пользователя root. Пример: "--user=vasya".
-p — используется вместе с командой "-u" и задает пароль учетной записи пользователя. Если пароля нет (инсталляция по умолчанию не предусматривает пароля для пользователя root), команду можно опустить. Обратите внимание — синтаксис команды отличается — пароль задается в строке сразу после команды, безо всяких разделителей. Пример: "--user=vasya -pverygoodpassword", где «verygoodpassword» — это пароль пользователя «vasya».
-P или --port= — определяет номер порта, через который устанавливается соединение с сервером mysqld. По умолчанию сервер слушает TCP порт 3306, но можно задать любой другой. Обратите внимание — сокращенный вариант команды пишется с большой буквы Р, так как маленькая буква р используется для задания пароля пользователя.
--bind-address= — применяется для принудительного задания IP-адреса сервера, по умолчанию берется текущий адрес компьютера.
--skip-networking — команда полностью запрещает использование сетевых соединений для подключения к серверу, работать можно только локально, с хоста localhost.
Следующая группа команд предназначена для работы с базами данных.
Вывод сообщений на русском — используйте --language=russian
Datadir= или -h — позволяет явно указать каталог, где хранятся базы данных. Может потребоваться, если вы хотите хранить базы на другом разделе или логическом диске, или же у вас есть несколько серверов, которые используют разные каталоги для хранения баз данных. Допустимо задавать как полный путь, так и относительный. Пример: "mysqld --datadir=\home\vasya\bases".
--basedir= или -b — путь к директории с установленным сервером.
--tmpdir= или -t — путь к каталогу для временных файлов.
--character-sets-dir=. Эта опция задает путь к каталогу, который содержит конфигурационные файлы для различных кодировок. Эти данные важны для правильной сортировки и поиска в строковых полях таблиц.
--language= или -L — позволяет переопределить язык вывода сообщений и ошибок. По умолчанию используется английский язык, но поддерживаются и другие — в частности, русский. Для задания языка вывода надо указать его название латинскими буквами (russian — для русского, english — для английского). Для работы команды должна существовать директория \share\, но если путь к ней отличается от стандартного, тогда вместо задания языка вам надо описать полный путь к директории — например, "--language=\user\vasya\mysql\langv\russian".
--default-character-set= — задает кодировку символов по умолчанию. Среди возможных значений параметра charset имеются: koi8_ru, german1, cp1251, win1251, koi8_ukr, win1251ukr, win1250 и другие — всего больше 20 различных кодировок. Кодировка важна, если в таблицах будут храниться тексты на различных языках, и важно учитывать национальные особенности при поиске и сортировке данных.
Это были самые важные опции и команды, которые могут понадобится при настройке сервера. Всех же команд очень много, и для их полного изучения используйте встроенную справку (вызов через команду --help).
Некоторые команды специфичны и доступны только для Windows NT
Для платформы Windows NT предусмотрено несколько специфических команд.
--console — команда появилась в версии 3.22.4 и предназначена для вывода всех сообщений об ошибках в окно консоли, что полезно при отладке или изучении влияния разных параметров на работу сервера.
--install — работает только на платформе Win2000/XP и регистрирует сервер MySQL как системный сервис, который запускается при каждой загрузке системы и постоянно существует в оперативной памяти (если надо зарегистрировать сервис, но запускать его пользователь будет сам, команда будет выглядеть как "--insatll-manual"). Команда "--remove" удаляет сервер из писка системных сервисов.
--standalone — запуск сервера под Win2000/XP в виде отдельной программы, а не системного сервиса.
Удобнее всего использовать для указанных действий графическую утилиту WinMySQLAdmin, которая входит в дистрибутив.
С ее помощью можно также создать отчет обо всех переменных и параметрах, с которыми запущен сервер, что может понадобиться при детальном изучении работы сервера и оптимизации производительности. Другие графические утилиты, вроде MySQL Administrator, позволяют не только просматривать эти данные, но и с помощью удобного интерфейса изменять их. Но в случае удаленно сервера или при ограниченных ресурсах оптимальнее всего использовать управление через командную строку. Кроме того, графический интерфейс не избавляет от необходимости знания и понимания назначения изменяемых опций.
Ниже предоставлен список наиболее полезных и часто используемых команд MySQL с примерами.
mysql в начале строки означает, что команда выполняется после входа вMySQL .
Символ # или $ в начале строки означает, что команда выполняется из командной строки.
Что бы проверить статус сервера MYSQL выполните:
для FreeBSD :
# service mysql-server status
в CentOS / RHEL :
# service mysqld status
MySQL из консоли, если сервер MySQL находится на том же хосте:
Что бы подключиться к серверу MySQL из консоли, если сервер MySQL находится на удаленном хосте db1.example.com:
$ mysql -u username -p -h db1.example.com
Работа с базами, таблицами - просмотр, удаление, редактирование записей. Консоль
Создать базу данных на MySQL сервере:
Mysql create database
Показать список всех баз данных на сервере MySQL :
Mysql use ;
Отобразить все таблицы в базе данных:
Mysql show tables;
Просмотреть формат таблицы в базе:
Mysql describe ;
Удалить базу:
Mysql drop database ;
Удалить таблицу из базы:
Mysql drop table ;
Показать все содержимое таблицы:
Mysql SELECT * FROM ;
Отобразить столбцы и содержимое столбцов в выбранной таблице:
Mysql show columns from ;
Отобразить строки в определенной таблице, содержащие " whatever ":
Mysql SELECT * FROM WHERE = "whatever";
Отобразить все записи в определенной таблице, содержащие " Bob " и телефонный номер " 3444444:
Mysql SELECT * FROM WHERE name = " Bob " AND phone_number = " 3444444 ";
Отобразить все записи, НЕ содержащие имя " Bob " и телефонный номер " 3444444 ", отсортированные по полю phone_number:
Mysql SELECT * FROM WHERE name != " Bob " AND phone_number = " 3444444 " order by phone_number;
Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 " в определенной таблице:
Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 ";
Показать все записи, начинающиеся с букв " bob " и телефонного номера " 3444444 ", ограничиваясь записями с 1-ой до 5-ой:
Mysql SELECT * FROM WHERE name like " Bob %" AND phone_number = " 3444444 " limit 1,5;
Использование регулярных выражений ("REGEXP BINARY") для поиска записей. Например, для регистро-независимого поиска - найти все записи, начинающиеся с буквы А:
Mysql SELECT * FROM WHERE rec RLIKE "^ a ";
Показать все уникальные записи:
Mysql SELECT DISTINCT FROM ; mysql SELECT , FROM ORDER BY DESC;
Показать количество строк в таблице:
Mysql SELECT COUNT(*) FROM ;
Mysql SELECT SUM(*) FROM ;
Удаление столбца:
Mysql alter table drop column ;
Добавление колонки в базу данных:
Mysql alter table add column varchar (20);
Изменение имени столбца:
Mysql alter table change varchar (50);
Создать столбец с уникальным именем, что бы избежать дубликатов в названиях:
Mysql alter table add unique ();
Изменение размера столбца:
Mysql alter table modify VARCHAR(3);
Удаление столбца из таблицы:
Mysql alter table drop index ;
Mysql LOAD DATA INFILE " /tmp/filename.csv " replace INTO TABLE FIELDS TERMINATED BY "," LINES TERMINATED BY "n" (field1,field2,field3);
Пользователи, пароли сервера MySQL - добавление, изменение пользователей и паролей. Консоль
Создание нового пользователя - подключение к серверу MySQL под root, переключение к базе данных, добавление пользователя, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql INSERT INTO user (Host,User,Password) VALUES("%"," username ", PASSWORD(" password ")); mysql flush privileges;
Изменений пользовательского пароля из консоли на удаленном хосте db1.example.org:
# mysqladmin -u username -h db1.example.org -p password " new-password "
Изменение пользовательского пароля из консоли MySQL - подключение под root, обновление пароля, обновление привилегий:
# mysql -u root -p mysql SET PASSWORD FOR " user "@" hostname " = PASSWORD(" passwordhere "); mysql flush privileges;
Восстановление/изменение пароля root сервера MySQL - остановка MySQL , запуск без таблиц привилегий, подключение под root, установка нового пароля, выход и перезапуск MySQL .
# /etc/init.d/mysql stop # mysqld_safe -skip-grant-tables & # mysql -u root mysql use mysql; mysql update user set password=PASSWORD(" newrootpassword ") where User="root"; mysql ; flush privileges; mysql quit # /etc/init.d/mysql stop # /etc/init.d/mysql start
Set a root password if there is on root password.
# mysqladmin -u root password newpassword
Обновление пароля root:
# mysqladmin -u root -p oldpassword newpassword
Установка права на подключение к серверу с хоста localhost с паролем " passwd " - подключение подroot, переключение к базе данных, установка привилегий, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql grant usage on *.* to bob @localhost identified by " passwd "; mysql flush privileges;
Установка привилегий пользователю на использование базы данных - подключение под root, переключение к базе данных, установка привилегий, обновление привилегий:
# mysql -u root -p mysql use mysql; mysql INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ("%","databasename","username","Y","Y","Y","Y","Y","N"); mysql flush privileges;
Mysql grant all privileges on databasename .* to username @localhost; mysql flush privileges;
Обновление информации в базе данных:
Mysql UPDATE SET Select_priv = "Y",Insert_priv = "Y",Update_priv = "Y" where = user";
Удаление строки в таблице:
Mysql DELETE from where = "whatever";
Обновление привилегий в базе данных:
Mysql flush privileges;
Резервные копии - создание, восстановление бд. Консоль
Создать резервную копию (dump) всех баз данных в файл alldatabases.sql:
# mysqldump -u root -p password -opt ; /tmp/alldatabases.sql
Создать резервную копию одной базы данных в файл databasename.sql:
# mysql dump -u username -p password -databases databasename ; /tmp/databasename.sql
Создать резервную копию одной таблицы в файл databasename.tablename.sql:
# mysql dump -c -u username -p password databasename tablename ; /tmp/databasename.tablename.sql
Восстановление базы данных (или таблицы) из резервной копии:
# mysql -u username -p password databasename < /tmp/databasename.sql
Создание таблиц БД. Консоль
маленькими буквами указаны имена столбцов;
ПРОПИСНЫМИ буквами - типы и атрибуты столцов;
в (скобках) - значение типа столбца.
Создать таблицу, пример 1:
mysql CREATE TABLE (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp TIME, pgpemail VARCHAR(255));Создать таблицу, пример 2:
Mysql create table (personid INT(50) NOT NULL AUTO_INTCREMENT PRIMARY KEY, firstname VARCHAR(35), middlename VARCHAR(50), lastname VARCHAR(50) default "bato");
Запуск и остановка сервера MySQL из командной строки
Запустить сервер MySQL вручную можно одним из двух способов:
Дважды щелкните на значке файла mysqld-nt.exe, расположенного в подкаталоге bin каталога, где установлена программа MySQL.
Откройте окно командной строки Windows. Для этого нажмите кнопку Пуск, в меню выберите пункт Выполнить, в появившемся окне Запуск программы в поле Открыть введите команду cmd и нажмите кнопку OK. На экране появится окно командной строки (рис. 1.25).
Рис. 1.25. Окно командной строки
В командной строке введите команду
mysqld-nt
и нажмите клавишу Enter. Сервер MySQL будет запущен.
Если при настройке сервера путь к подкаталогу bin не был добавлен в значение системной переменной Path, то для запуска сервера необходимо ввести не только имя файла, но и полный путь к нему, например:
C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt
Если вы хотите просматривать в окне командной строки диагностические сообщения о работе сервера, вместо mysqld-nt введите
mysqld-nt –console
Внимание!
Если при настройке сервера MySQL вы не указывали пароль пользователя root, то необходимо установить пароль при первом запуске сервера (иначе кто угодно сможет управлять сервером под именем root без пароля).
Чтобы установить пароль root, откройте новое окно командной строки и введите следующую команду:
mysqladmin -u root password <пароль>
(или C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqladmin -u root password <пароль>, если путь к подкаталогу bin не был добавлен в значение системной переменной Path при настройке сервера) и нажмите клавишу Enter.
В дальнейшем, если потребуется сменить пароль пользователя root, выполните такую же команду, только с использованием опции – р:
mysqladmin -u root -p password <новый пароль>
После появления приглашения Enter password (Введите пароль) укажите прежний пароль и нажмите клавишу Enter.
Наконец, если необходимо остановить сервер MySQL, выполните команду
mysqladmin -u root -p shutdown
и в ответ на приглашение Enter password (Введите пароль) введите пароль пользователя root. Нажмите клавишу Enter. Сервер MySQL будет остановлен.
Для запуска и остановки сервера MySQL можно также использовать графическую утилиту MySQL Administrator.
Запуск и остановка сервера MySQL с помощью MySQL Administrator
Чтобы запустить сервер MySQL с помощью графической утилиты MySQL Administrator, выполните следующие действия.
1. Запустите программу MySQL Administrator (Пуск → Все программы → MySQL → MySQL Administrator). На экране появится окно соединения с сервером (рис. 1.26).
Рис. 1.26.
2. Нажмите клавишу Ctrl и, удерживая ее, щелкните на кнопку Skip (Пропустить), появившуюся в правом нижнем углу окна вместо кнопки Cancel (Отмена). На экране появится главное окно MySQL Administrator (рис. 1.27).
Рис. 1.27. Главное окно MySQL Administrator
4. Если сервер MySQL не был сконфигурирован как сервис Windows, то кнопка Start Service (Запустить сервис), расположенная в правой области окна, недоступна. Необходимо выполнить следующие предварительные действия:
1) перейдите на вкладку Configure Service (Настройка сервиса). Найдите внизу вкладки кнопку Install new Service (Установить новый сервис) и нажмите ее;
2) в появившейся диалоговой панели укажите название сервиса и нажмите кнопку OK;
3) в поле Config Filename (Имя конфигурационного файла) введите путь к конфигурационному файлу my.ini (рис. 1.28), например C: \Program Files\ MySQL\MySQL Server 5.0\my.ini. Красный цвет шрифта означает, что файл не найден; если цвет сменился на обычный, то путь указан верно;
4) в поле Path to binary (Путь к исполняемому файлу) введите путь к файлу mysqld-nt.exe, например C: \Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt;
6) вернитесь на вкладку Start/Stop Service (Запуск/остановка сервиса).
Рис. 1.28. Закладка Configure Service 5. Нажмите кнопку Start Service (Запустить сервис). Сервер MySQL будет запущен.
Внимание!
Если при настройке сервера MySQL вы не указывали пароль пользователя root, то необходимо установить его при первом запуске сервера (иначе кто угодно может управлять сервером под именем root без пароля). В текущей версии MySQL Administrator установка пароля root недоступна, и для выполнения этой операции нужно использовать утилиту командной строки mysqladmin (см. подраздел «Запуск и остановка сервера MySQL из командной строки»).
Чтобы остановить сервер MySQL с помощью MySQL Administrator, выполните следующие действия.
1. Запустите программу MySQL Administrator (Пуск → Все программы → MySQL → MySQL Administrator). На экране появится окно соединения с сервером (см. рис. 1.26).
Server Host (Имя хоста) – значение localhost (локальный компьютер);
Port (Порт) – номер порта, выбранный при настройке сервера (по умолчанию – 3306);
Usename (Имя пользователя) – значение root;
Password (Пароль) – пароль пользователя root. Нажмите кнопку OK.
3. В главном окне MySQL Administrator в левой области щелкните пункт Service Control (Управление сервисом).
4. В правой области окна нажмите кнопку Stop Service (Остановить сервис). Сервер MySQL будет остановлен.
В следующем подразделе вы узнаете, как запустить сервер MySQL с помощью средств администрирования Windows.
Запуск и остановка сервера MySQL с панели управления
Если сервер MySQL был сконфигурирован как сервис Windows с помощью мастера настройки (см. подраздел «Настройка сервера MySQL») или с помощью утилиты MySQL Administrator (см. подраздел «Запуск и остановка сервера MySQL с помощью MySQL Administrator»), то запускать и останавливать его можно с помощью компонента Службы панели управления.
Чтобы вызвать компонент Службы, нажмите кнопку Пуск, в меню выберите пункт Панель управления, затем в панели управления дважды щелкните на значке Администрирование и, наконец, в окне средств администрирования дважды щелкните на значке Службы. На экране появится окно Службы (рис. 1.29) со списком всех локальных служб.
Рис. 1.29. Сервис MySQL в панели управления
В окне Службы щелкните на названии сервиса MySQL (название определяется при создании сервиса в мастере настройки или в MySQL Administrator). Затем щелкните на нужную ссылку под названием сервиса: Запустить службу, Остановить службу или Перезапустить службу.
После того как сервер MySQL запущен, к нему можно подключиться. В следующих подразделах вы узнаете, как это сделать.
Подключение к серверу из командной строки
Чтобы подключиться к серверу MySQL из командной строки, выполните следующие действия.
1. Откройте окно командной строки Windows. Для этого нажмите кнопку Пуск, в меню выберите пункт Выполнить, в появившемся окне Запуск программы введите в поле Открыть команду cmd и нажмите кнопку OK.
2. В командной строке (см. рис. 1.25) введите команду
mysql -h <Имя компьютера> -u <Имя пользователя> -p
(где <Имя компьютера> – это имя компьютера, на котором работает сервер) и нажмите клавишу Enter. После появления приглашения Enter password (Введите пароль) введите пароль пользователя.
Если требуется подключиться к серверу MySQL, работающему на этом же компьютере, имя компьютера (localhost) можно не указывать, например
mysql -u root -p
После подключения к серверу приглашение командной строки изменится на mysql> (рис. 1.30). Теперь можно приступать к работе с базой данных: добавлять таблицы, вводить и запрашивать данные, регистрировать новых пользователей и др.
Рис. 1.30. Соединение с сервером MySQL из командной строки
Чтобы отключиться от сервера, просто наберите в командной строке команду
и нажмите клавишу Enter.
Альтернативный способ подключения к серверу MySQL предоставляет графическая утилита MySQL Query Browser.
Подключение к серверу с помощью MySQL Query Browser
Утилита MySQL Query Browser – интерфейс для создания, редактирования и выполнения инструкций SQL. Она удобнее, чем командная строка. Если вы решили использовать для работы с базой данных MySQL Query Browser, то для подключения к серверу выполните следующие действия.
1. Запустите программу MySQL Query Browser (Пуск → Все программы → MySQL → MySQL Query Browser). На экране появится окно соединения с сервером (рис. 1.31).
Рис. 1.31. Окно соединения с сервером MySQL
2. В поля окна соединения с сервером введите параметры соединения:
Server Host (Имя хоста) – имя компьютера, на котором работает сервер MySQL;
Port (Порт) – номер порта, выбранный при настройке сервера (по умолчанию – 330 6);
Username (Имя пользователя) – имя пользователя;
Password (Пароль) – пароль пользователя;
Default Schema (Схема по умолчанию) – имя базы данных, с которой вы будете работать (это может быть как существующая, так и новая база данных).
3. Нажмите кнопку OK. Если вы ввели имя новой базы данных, то в появившейся диалоговой панели нажмите кнопку Yes (Да) для создания этой базы данных.
После подключения к серверу на экране появится главное окно MySQL Query Browser (рис. 1.32). В нем вы можете выполнять любые операции с базой данных: добавлять таблицы, вводить и запрашивать данные, регистрировать новых пользователей и др.
Рис. 1.32. Главное окно MySQL Query Browser
Внимание!
Шрифт, который по умолчанию используется в MySQL Query Browser для отображения SQL-запросов, не поддерживает русские буквы. Чтобы вводить русские буквы в текстах запросов, необходимо выбрать другой шрифт (например, Arial или Book Antiqua). Для этого в главном окне MySQL Query Browser откройте меню Tools (Сервис) и выберите пункт Options (Параметры). В появившемся окне Options (Параметры) в левой области щелкните пункт General Options (Общие параметры) и в правой области в поле Code Font (Шрифт кода) выберите из списка нужный шрифт. Нажмите кнопку Apply (Сохранить).
Чтобы отключиться от сервера, просто закройте окно MySQL Query Browser.
На этом мы заканчиваем знакомство с MySQL и переходим к подведению итогов.
1.6. Резюме
В этой главе были рассмотрены СУБД MySQL и графические утилиты MySQL Administrator и MySQL Query Browser. Вы освоили достаточно сложную процедуру установки и настройки сервера MySQL, научились управлять сервером и подключаться к нему. Вы также узнали, как устроена реляционная база данных и как спроектировать собственную БД.
Итак, следующим этапом является построение базы данных в MySQL. Этому посвящена вторая глава. В ней будет рассказано, как создавать таблицы, вносить в них информацию и находить нужные сведения в базе данных.
Глава 2
Управление базой данных с помощью SQL
Из этой главы вы узнаете, как работать с данными в СУБД MySQL, как определять их структуру, а также как добавлять, изменять и удалять данные. Эти операции выполняет SQL – универсальный язык структурированных запросов, являющийся стандартным средством доступа к реляционным базам данных.
Для выполнения SQL-команд вы можете использовать любое из многочисленных клиентских приложений сервера MySQL. В этой главе не будут рассматриваться приложения сторонних разработчиков. Вы познакомитесь только с приложениями, созданными компанией MySQL AB: утилитой командной строки mysql и графической утилитой MySQL Query Browser.
В обеих утилитах доступны все операции с данными. В MySQL Query Browser удобно работать с базой данных: ее компоненты наглядно представлены, можно непосредственно редактировать данные (без использования SQL-оператора UPDATE), работать с запросами, например строить их с помощью специального инструмента (при этом названия таблиц и столбцов вводить вручную не нужно), сохранять запросы в файле, экспортировать результаты запросов и многое другое. Вы можете узнать о всех возможностях MySQL Query Browser, обратившись к документации на русском языке, найти которую можно по ссылке http://dev.mysql.com/doc/query-browser/ru/index.html .
Вначале вы узнаете, как выполнять SQL-команды в MySQL Query Browser и в командной строке, а в дальнейшем будет рассмотрен только синтаксис SQL-команд.
2.1. Выполнение SQL-команд
Прежде чем выполнять SQL-команды, необходимо подключиться к работающему серверу MySQL (как это сделать, рассказывалось в главе 1). В этом разделе вы узнаете, как создавать SQL-команды и передавать их серверу для выполнения.
Если вы используете командную строку, то для выполнения SQL-команды введем ее текст в окне командной строки и нажмем клавишу Enter для отправки команды на сервер. Чтобы избежать проблем с кодировкой русскоязычных данных, перед началом работы с данными выполните команду
SET NAMES cp866;
Результат выполнения этой команды вы видите на рис. 2.1.
Рис. 2.1. Установка кодировки в командной строке
Команду SET NAMES необходимо повторять при каждом подключении к серверу с помощью командной строки. Эта команда указывает серверу, что данное клиентское приложение (утилита mysql) использует кодировку CP-866 (это кодировка командной строки Windows), и сервер будет автоматически выполнять преобразование кодировок при обмене данными с клиентским приложением.
После смены кодировки вы можете вводить в командной строке любые SQL-команды. Сообщение о результате выполнения команды, а также запрошенные данные выводятся непосредственно в окне командной строки (рис. 2.2).
Рис. 2.2. Выполнение SQL-запроса в командной строке
Утилита mysql позволяет вводить и многострочные команды (на рис. 2.3 таким образом введена команда SHOW DATABASES). Если не введена точка с запятой – признак конца команды, то при нажатии клавиши Enter утилита не отправляет команду на сервер, а предлагает продолжить ввод команды. Если вы хотите отменить ввод многострочной команды, наберите \c (рис. 2.3).
Рис. 2.3. Многострочная команда
Если вы используете MySQL Query Browser, то кодировку устанавливать не нужно – эта программа работает в кодировке UTF-8 и сама сообщает об этом серверу. Однако в MySQL Query Browser существует проблема отображения русских букв в области запросов (области, куда вводится текст SQL-команд, рис. 2.4). Для решения этой проблемы необходимо изменить шрифт, используемый в области запросов (как это сделать, рассказывалось в конце предыдущей главы). Выполнить смену шрифта достаточно один раз.
Рис. 2.4. Выполнение SQL-запроса в MySQL Query Browser
В области запросов вы можете ввести сразу несколько SQL-команд, как показано на рис. 2.4. Текущая команда (на одной из ее строк установлен курсор) выделена белым цветом фона, остальные команды отображены на светло-сером фоне. Чтобы выполнить текущую команду, вы можете нажать либо кнопку Execute, расположенную справа от области запросов, либо комбинацию клавиш Ctrl+Enter. После выполнения команды запрошенные данные выводятся в области результатов, а сообщение о результате выполнения команды – в нижней части этой области.
Теперь, когда вы научились вводить SQL-команды, приступим к управлению данными с помощью этих команд. В первую очередь мы рассмотрим команды, предназначенные для работы с базой данных в целом.
2.2. Создание базы данных
В этом разделе вы узнаете, как создать и удалить базу данных, изменить для нее кодировку по умолчанию, выбрать текущую БД, а также просмотреть список всех баз на данном сервере MySQL.
Чтобы создать базу данных, выполним команду
CREATE DATABASE <Имя базы данных>;
Например, команда
CREATE DATABASE SalesDept;
создает базу данных с именем SalesDept (Отдел продаж).
Если вам по каким-либо причинам нужно для новой базы данных установить кодировку по умолчанию, отличную от кодировки, указанной при настройке MySQL, то при создании базы данных вы можете указать нужную кодировку (character set) и/или правило сравнения (сортировки) символьных значений:
CREATE DATABASE <Имя базы данных>
CHARACTER SET <Имя кодировки>
COLLATE <Имя правила сравнения>;
Например, если вы будете в новую базу импортировать данные, которые находятся в кодировке CP-1251, то укажем эту кодировку при создании базы данных таким образом:
CREATE DATABASE SalesDept
CHARACTER SET cp1251 COLLATE cp1251_general_ci;
Совет
Чтобы просмотреть список используемых в MySQL кодировок, выполним команду SHOW CHARACTER SET; а чтобы увидеть список правил сравнения символьных значений – команду SHOW COLLATION;. При этом можно использовать оператор LIKE: например, чтобы увидеть все правила сравнения для кодировки CP-1251, выполним команду SHOW COLLATION LIKE %1251 %;. Окончание «_ci» (case insensitive) в названии правил сравнения означает, что при сравнении и сортировке регистр символов не учитывается, окончание «_cs» (case sensitive) – регистр учитывается, окончание «_bin» (binary) – сравнение и сортировка выполняются по числовым кодам символов. Для большинства правил сравнения вы можете найти описание (то есть порядок следования символов, в соответствии с которым будут упорядочиваться текстовые значения) на веб-странице http://www.collation-charts.org/mysql60/ .
Кодировка, указанная при создании базы данных, будет по умолчанию использоваться для таблиц этой базы, однако вы можете задать и другую кодировку.
Изменить кодировку и/или правило сравнения символьных значений для базы данных вы можете с помощью команды
ALTER DATABASE <Имя базы данных>
CHARACTER SET <Имя кодировки>
COLLATE <Имя правила сравнения>;
При этом кодировка, используемая в уже существующих таблицах базы данных, остается прежней; меняется только кодировка, назначаемая по умолчанию для вновь создаваемых таблиц.
Чтобы удалить ненужную или ошибочно созданную базу данных, выполните команду
DROP DATABASE <Имя базы данных>;
Внимание!
Удаление базы данных – очень ответственная операция, поскольку она приводит к удалению всех таблиц этой базы и данных, хранившихся в таблицах. Перед удалением рекомендуется создать резервную копию базы данных.
Одну из баз, созданных на данном сервере MySQL, вы можете выбрать в качестве текущей базы данных с помощью команды
USE <Имя базы данных>;
Например,:
USE SalesDept;
После этого вы можете выполнять операции с таблицами этой базы данных, не добавляя имя базы в виде префикса к имени таблицы. Например, для обращения к таблице Customers (Клиенты) базы данных SalesDept (Отдел продаж) можно вместо SalesDept.Customers писать просто Customers. Указав текущую базу, вы можете обращаться и к таблицам других баз данных, однако использование имени базы данных в виде префикса при этом обязательно. Выбор текущей базы сохраняется до момента отсоединения от сервера или до выбора другой текущей базы данных.
Чтобы увидеть список всех баз, существующих на данном сервере MySQL, выполните команду
SHOW DATABASES;
Даже если вы еще не создали ни одной базы данных, в полученном списке вы увидите три системных базы данных.
INFORMATION_SCHEMA – информационная база данных, из которой вы можете получить сведения о всех остальных базах, о структуре данных в них и о всевозможных объектах: таблицах, столбцах, первичных и внешних ключах, правах доступа, хранимых процедурах, кодировках и др. Эта база данных доступна только для чтения и является виртуальной, то есть она не хранится в виде каталога на диске: вся информация, запрашиваемая из этой БД, предоставляется динамически сервером MySQL.
Mysql – служебная база данных, которую использует сервер MySQL. В ней хранятся сведения о зарегистрированных пользователях и их правах доступа, справочная информация и др.
Test – пустая база данных, которую можно использовать для «пробы пера» или просто удалить.
Итак, вы освоили основные операции, выполняемые с базой данных как единым целым: команды CREATE DATABASE (создание), ALTER DATABASE (изменение), DROP DATABASE (удаление), USE (выбор текущей базы данных) и SHOW DATABASES (просмотр списка баз данных). Далее мы рассмотрим операции с таблицами. При этом будем считать, что вы выбрали какую-либо базу данных в качестве текущей и работаете с ее таблицами.
2.3. Работа с таблицами
В этом разделе вы узнаете, как создать, изменить и удалить таблицу, как просмотреть информацию о ней и список всех таблиц в текущей базе данных. Начнем с наиболее сложной команды – создания таблицы.
Создание таблицы
Чтобы создать таблицу, выполните команду, представленную в листинге 2.1.
Листинг 2.1. Команда создания таблицы
CREATE TABLE <Имя таблицы>
(<Имя столбца 1> <Тип столбца 1> [<Свойства столбца 1>],
<Имя столбца 2> <Тип столбца 2> [<Свойства столбца 2>],
[<Информация о ключевых столбцах и индексах>])
[<Опциональные свойства таблицы>];
Как вы видите, команда создания таблицы может включать множество параметров, однако многие из них задавать необязательно (в листинге 2.1 такие параметры заключены в квадратные скобки). В действительности для создания таблицы достаточно указать ее имя, а также имена и типы всех столбцов; остальные параметры используются в случае необходимости.
Рассмотрим вначале несколько примеров, которые помогут вам освоить команду CREATE TABLE и сразу же, не изучая ее многочисленных параметров, начать создавать собственные (простые по структуре) таблицы.
Предположим, что мы строим базу данных, которую спроектировали в главе 1. Используя команды из предыдущего раздела, мы создали пустую базу данных SalesDept (Отдел продаж) и выбрали ее в качестве текущей. Теперь создадим три таблицы: Customers (Клиенты), Products (Товары) и Orders (Заказы). В листинге 2.2 представлена команда создания таблицы Customers.
Листинг 2.2. Команда создания таблицы Customers
CREATE TABLE Customers
(id SERIAL,
name VARCHAR(100),
phone VARCHAR(20),
address VARCHAR(150),
rating INT,
PRIMARY KEY (id))
В этой команде использовались параметры: во-первых, название таблицы и, во-вторых, названия и типы столбцов, из которых будет состоять таблица (см. также табл. 1.1 в главе 1).
Id – идентификатор записи. Этому столбцу вы назначили тип SERIAL, позволяющий автоматически нумеровать строки таблицы. Ключевое слово SERIAL расшифровывается как BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Это означает, что в столбец можно вводить большие целые (BIGINT) положительные (UNSIGNED) числа, при этом автоматически контролируется отсутствие неопределенных и повторяющихся значений (NOT NULL UNIQUE). Если при добавлении строки в таблицу вы не укажете значение для этого столбца, то программа MySQL внесет в этот столбец очередной порядковый номер (AUTO_INCREMENT).
Примечание
NULL – это константа, указывающая на отсутствие значения. Если в столбце находится значение NULL, то считается, что никакого определенного значения для этого столбца не задано (поэтому мы также называем NULL неопределенным значением). Не следует путать NULL с пустой строкой («») или числом 0. Значения NULL обрабатываются особым образом: большинство функций и операторов возвращают NULL, если один из аргументов равен NULL. Например, результат сравнения 1 = 1 – истинное значение (TRUE), а результат сравнения NULL = NULL – неопределенное значение (NULL), то есть два неопределенных значения не считаются равными.
Nam – имя клиента, phone – номер телефона и address – адрес. Вы присвоили этим столбцам тип VARCHAR, поскольку они будут содержать символьные значения. В скобках указывается максимально допустимое количество символов в значении столбца.
В-третьих, вы указали, что столбец id будет первичным ключом таблицы, включив в команду создания таблицы определение PRIMARY KEY (id).
В-четвертых, вы задали для этой таблицы два опциональных параметра. Параметр ENGINE определяет тип таблицы. Таблице Customers вы присвоили тип InnoDB, так как только этот тип обеспечивает поддержание целостности связей между таблицами (более подробно о типах таблиц будет рассказано в пункте «Опциональные свойства таблицы»). Параметр CHARACTER SET определяет кодировку по умолчанию для данных в таблице. Поскольку вы не задали кодировку отдельно для столбцов name, phone и address, данные в этих столбцах будут храниться в кодировке UTF-8, которая назначена в качестве кодировки по умолчанию для таблицы Customers.
Следующий пример, который мы рассмотрим, – команда создания таблицы Products (Товары), представленная в листинге 2.3.
Листинг 2.3. Команда создания таблицы Products
CREATE TABLE Products
(id SERIAL,
description VARCHAR(100),
details TEXT,
price DECIMAL(8,2),
PRIMARY KEY (id))
ENGINE InnoDB CHARACTER SET utf8;
Эта команда очень похожа на команду создания таблицы Customers и отличается от нее только названием таблицы и набором столбцов. Столбцы id (номер товара) и description (наименование товара) таблицы Products имеют уже знакомые нам типы. Столбец details (описание) имеет тип TEXT. Этот тип удобно использовать вместо типа VARCHAR, если столбец будет содержать длинные значения: суммарная длина значений всех столбцов с типом VARCHAR ограничена 65 535 байтами для каждой таблицы, а на общую длину столбцов с типом TEXT ограничений нет. Недостатком типа TEXT является невозможность включать такие столбцы во внешний ключ таблицы, то есть создавать связь между таблицами на основе этих столбцов.
Столбец price (цена) имеет тип DECIMAL, предназначенный для хранения денежных сумм и других значений, для которых важно избежать ошибок округления. В скобках мы указали два числа: первое из них определяет максимальное количество цифр в значении столбца, второе – максимальное количество цифр после десятичного разделителя. Другими словами, цена товара может содержать до шести цифр в целой части (6 = 8–2) и до двух цифр в дробной части.
И, наконец, последний пример – команда создания таблицы Orders (Заказы), представленная в листинге 2.4.
Листинг 2.4. Команда создания таблицы Orders
CREATE TABLE Orders
(id SERIAL,
date DATE,
product_id BIGINT UNSIGNED NOT NULL,
qty INT UNSIGNED,
amount DECIMAL(10,2),
customer_id BIGINT UNSIGNED,
PRIMARY KEY (id),
FOREIGN KEY (product_id) REFERENCES Products (id)
ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (customer_id) REFERENCES Customers (id)
ON DELETE RESTRICT ON UPDATE CASCADE)
ENGINE InnoDB CHARACTER SET utf8;
Особенностью таблицы Orders является наличие внешних ключей: столбец product_id (товар) содержит номера товаров из таблицы Products, а столбец customer_id (клиент) – номера клиентов из таблицы Customers (см. также табл. 1.2 в главе 1). Поскольку номера товаров и клиентов являются большими целыми положительными числами, столбцам product_id и customer_id мы назначили тип BIGINT UNSIGNED.
Далее, чтобы обеспечить автоматическое поддержание целостности связей (о целостности мы рассказывали в главе 1), мы сообщили программе MySQL, какому первичному ключу соответствует каждый внешний ключ. Так, конструкция FOREIGN KEY (customer_id) REFERENCES Customers (id) означает, что в столбце customer_id могут содержаться только значения из столбца id таблицы Customers и неопределенные значения (NULL), а остальные значения запрещены. Для столбца product_id мы задали аналогичное ограничение и присвоили этому столбцу свойство NOT NULL, чтобы запретить регистрировать заказы с неопределенным товаром. Дополнительно мы указали для каждой из связей правила поддержания целостности (их мы также рассматривали в главе 1). Правило ON DELETE RESTRICT означает, что нельзя удалить запись о клиенте, если у этого клиента есть зарегистрированный заказ, и нельзя удалить запись о товаре, если этот товар был кем-то заказан. Правило ON UPDATE CASCADE означает, что при изменении номера клиента в таблице Customers или номера товара в таблице Products соответствующие изменения вносятся и в таблицу Orders.
Примечание
Обратите внимание, что таблицу Orders мы создали в последнюю очередь, так как первичные ключи в таблицах Customers и Products должны быть созданы раньше, чем ссылающиеся на них внешние ключи в таблице Orders. Впрочем, можно было бы создать таблицы без внешних ключей в любой последовательности, а затем добавить внешние ключи с помощью команды ALTER TABLE, которую мы рассмотрим в подразделе «Изменение структуры таблицы».
В наших примерах мы рассмотрели лишь некоторые параметры команды создания таблицы. Теперь мы перечислим все основные параметры, которые могут вам пригодиться при создании таблиц. В пункте «Типы данных в MySQL» речь пойдет о типах столбцов, в пункте «Свойства столбцов» – о настройке ключевых столбцов и, наконец, в пункте «Ключевые столбцы и индексы» – об опциональных свойствах таблицы.
Типы данных в MySQL
Как вы уже знаете, при создании таблицы нужно указать тип данных для каждого столбца. В MySQL предусмотрено множество типов данных для хранения чисел, даты/времени и символьных строк (текстов). Кроме того, существуют типы данных для хранения пространственных (spatial) объектов, которые в этой книге рассматриваться не будут.
Рассмотрим числовые типы данных.
BIT[(<Количествобитов>)].
Битовое число, содержащее заданное количество битов. Если количество битов не указано, число состоит из одного бита.
Целое число в диапазоне либо от -128 до 127, либо (если указано свойство UNSIGNED) от 0 до 255.
BOOL или BOOLEAN.
Являются синонимами к типу данных TINYINT(1) (число в скобках – это количество отображаемых цифр, см. примечание ниже). При этом ненулевое значение рассматривается как истинное (TRUE), нулевое – как ложное (FALSE).
Целое число в диапазоне либо от -32 768 до 32 767, либо (если указано свойство UNSIGNED) от 0 до 65 535.
Целое число в диапазоне либо от -8 388 608 до 8 388 607, либо (если указано свойство UNSIGNED) от 0 до 16 777 215.
INT или INTEGER.
Целое число в диапазоне либо от -2 147 483 648 до 2 147 483 647, либо (если указано свойство UNSIGNED) от 0 до 4 294 967 295.
Целое число в диапазоне либо от -9 223 372 036 854 775 808 до 9 223 372 036 854 775 807, либо (если указано свойство UNSIGNED) от 0 до 18 446 744 073 70 9 551 615.
Синоним выражения BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE (большое целое число без знака, принимающее автоматически увеличиваемые уникальные значения; значения NULL запрещены). Используется для автоматической генерации уникальных значений в столбце первичного ключа. Описание свойств UNSIGNED и AUTO_INCREMENT вы найдете в этом подразделе, а свойств NOT NULL и UNIQUE – в пункте «Свойства столбцов».
Примечание
Для всех целочисленных типов данных, кроме BOOL (BOOLEAN) и SERIAL, можно в скобках указать количество отображаемых цифр, которое используется совместно с параметром ZEROFILL: если число содержит меньшее количество цифр, то при выводе оно дополняется слева нулями. Например, если столбец таблицы определен как INT(5) ZEROFILL, то значения «1234567» и «12345» отображаются «как есть», а значение «123» – как «00123». Для типа данных BIT в скобках указывается размер числа, то есть максимальное количество хранимых битов.
