MySQL — Введение

База данных — это отдельное приложение, которое хранит коллекцию данных. Каждая база данных имеет один или несколько отдельных API для создания, доступа, управления, поиска и репликации данных, которые она хранит.

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

В настоящее время мы используем системы управления реляционными базами данных (RDBMS) для хранения и управления огромным объемом данных. Это называется реляционной базой данных, поскольку все данные хранятся в разных таблицах, а отношения устанавливаются с использованием первичных ключей или других ключей, известных как внешние ключи .

Реляционная система управления базами данных (RDBMS) — это программное обеспечение, которое —

• Позволяет реализовать базу данных с таблицами, столбцами и индексами.

• Гарантирует ссылочную целостность между рядами различных таблиц.

• Обновляет индексы автоматически.

• Интерпретирует SQL-запрос и объединяет информацию из различных таблиц.

Позволяет реализовать базу данных с таблицами, столбцами и индексами.

Гарантирует ссылочную целостность между рядами различных таблиц.

Обновляет индексы автоматически.

Интерпретирует SQL-запрос и объединяет информацию из различных таблиц.

СУРБД Терминология

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

• База данных. База данных — это набор таблиц со связанными данными.

• Таблица — Таблица представляет собой матрицу с данными. Таблица в базе данных выглядит как простая электронная таблица.

• Столбец — один столбец (элемент данных) содержит данные одного и того же вида, например, почтовый индекс столбца.

• Строка. Строка (= кортеж, запись или запись) представляет собой группу связанных данных, например, данных одной подписки.

• Избыточность — Двойное хранение данных с избыточностью для ускорения работы системы.

• Первичный ключ — первичный ключ уникален. Значение ключа не может встречаться дважды в одной таблице. С помощью ключа вы можете найти только один ряд.

• Внешний ключ — внешний ключ — это связующий вывод между двумя таблицами.

• Составной ключ — составной ключ (составной ключ) — это ключ, состоящий из нескольких столбцов, поскольку один столбец недостаточно уникален.

• Индекс — Индекс в базе данных напоминает индекс в конце книги.

• Ссылочная целостность — Ссылочная целостность гарантирует, что значение внешнего ключа всегда указывает на существующую строку.

База данных. База данных — это набор таблиц со связанными данными.

Таблица — Таблица представляет собой матрицу с данными. Таблица в базе данных выглядит как простая электронная таблица.

Столбец — один столбец (элемент данных) содержит данные одного и того же вида, например, почтовый индекс столбца.

Строка. Строка (= кортеж, запись или запись) представляет собой группу связанных данных, например, данных одной подписки.

Избыточность — Двойное хранение данных с избыточностью для ускорения работы системы.

Первичный ключ — первичный ключ уникален. Значение ключа не может встречаться дважды в одной таблице. С помощью ключа вы можете найти только один ряд.

Внешний ключ — внешний ключ — это связующий вывод между двумя таблицами.

Составной ключ — составной ключ (составной ключ) — это ключ, состоящий из нескольких столбцов, поскольку один столбец недостаточно уникален.

Индекс — Индекс в базе данных напоминает индекс в конце книги.

Ссылочная целостность — Ссылочная целостность гарантирует, что значение внешнего ключа всегда указывает на существующую строку.

База данных MySQL

MySQL — это быстрая и простая в использовании СУБД, используемая для многих малых и крупных предприятий. MySQL разрабатывается, продается и поддерживается MySQL AB, шведской компанией. MySQL становится настолько популярным по многим веским причинам —

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

• MySQL — очень мощная программа сама по себе. Он обрабатывает большой набор функций самых дорогих и мощных пакетов баз данных.

• MySQL использует стандартную форму известного языка данных SQL.

• MySQL работает во многих операционных системах и со многими языками, включая PHP, PERL, C, C ++, JAVA и т. Д.

• MySQL работает очень быстро и хорошо работает даже с большими наборами данных.

• MySQL очень дружелюбен к PHP, самый ценный язык для веб-разработки.

• MySQL поддерживает большие базы данных, до 50 миллионов или более строк в таблице. Предельный размер файла по умолчанию для таблицы составляет 4 ГБ, но вы можете увеличить его (если ваша операционная система может его обработать) до теоретического предела 8 миллионов терабайт (ТБ).

• MySQL настраивается. Лицензия GPL с открытым исходным кодом позволяет программистам модифицировать программное обеспечение MySQL в соответствии со своими специфическими средами.

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

MySQL — очень мощная программа сама по себе. Он обрабатывает большой набор функций самых дорогих и мощных пакетов баз данных.

MySQL использует стандартную форму известного языка данных SQL.

MySQL работает во многих операционных системах и со многими языками, включая PHP, PERL, C, C ++, JAVA и т. Д.

MySQL работает очень быстро и хорошо работает даже с большими наборами данных.

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

MySQL поддерживает большие базы данных, до 50 миллионов или более строк в таблице. Предельный размер файла по умолчанию для таблицы составляет 4 ГБ, но вы можете увеличить его (если ваша операционная система может его обработать) до теоретического предела 8 миллионов терабайт (ТБ).

MySQL настраивается. Лицензия GPL с открытым исходным кодом позволяет программистам модифицировать программное обеспечение MySQL в соответствии со своими специфическими средами.

Прежде чем вы начнете

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

В этом руководстве основное внимание уделяется использованию MySQL в среде PHP. Многие примеры, приведенные в этом руководстве, будут полезны для программистов PHP.

Мы рекомендуем вам ознакомиться с нашим учебником PHP для вашей справки.

MySQL — Установка

Все загрузки для MySQL находятся на MySQL Downloads . Выберите номер версии MySQL Community Server, который требуется вместе с платформой, на которой он будет работать.

Установка MySQL в Linux / UNIX

Рекомендуемый способ установки MySQL в системе Linux — через RPM. MySQL AB делает доступными для загрузки на своем сайте следующие RPM —

• MySQL — сервер баз данных MySQL управляет базами данных и таблицами, контролирует доступ пользователей и обрабатывает запросы SQL.

• MySQL-клиент — клиентские программы MySQL, которые позволяют подключаться и взаимодействовать с сервером.

• MySQL-devel — библиотеки и заголовочные файлы, которые пригодятся при компиляции других программ, использующих MySQL.

• MySQL-shared — общие библиотеки для клиента MySQL.

• MySQL-bench — средства тестирования производительности и производительности для сервера баз данных MySQL.

MySQL — сервер баз данных MySQL управляет базами данных и таблицами, контролирует доступ пользователей и обрабатывает запросы SQL.

MySQL-клиент — клиентские программы MySQL, которые позволяют подключаться и взаимодействовать с сервером.

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

MySQL-shared — общие библиотеки для клиента MySQL.

MySQL-bench — средства тестирования производительности и производительности для сервера баз данных MySQL.

Перечисленные здесь RPM-пакеты для MySQL все построены на системе SuSE Linux , но обычно без проблем работают на других вариантах Linux.

Теперь вам нужно будет следовать приведенным ниже шагам, чтобы продолжить установку —

• Войдите в систему, используя пользователя root .

• Переключитесь на каталог, содержащий RPM.

• Установите сервер базы данных MySQL, выполнив следующую команду. Не забудьте заменить имя файла курсивом на имя файла вашего RPM.

Войдите в систему, используя пользователя root .

Переключитесь на каталог, содержащий RPM.

Установите сервер базы данных MySQL, выполнив следующую команду. Не забудьте заменить имя файла курсивом на имя файла вашего RPM.

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

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

Вы можете найти все связанные с MySQL двоичные файлы в / usr / bin и / usr / sbin. Все таблицы и базы данных будут созданы в каталоге / var / lib / mysql.

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

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

Установка MySQL в Windows

Установка по умолчанию в любой версии Windows теперь намного проще, чем раньше, поскольку MySQL теперь поставляется в комплекте с установщиком. Просто скачайте установочный пакет, разархивируйте его в любом месте и запустите файл setup.exe.

Установщик по умолчанию setup.exe проведет вас через простой процесс и по умолчанию установит все в C: \ mysql.

Протестируйте сервер, запустив его из командной строки в первый раз. Перейдите в расположение сервера mysqld, который, вероятно, C: \ mysql \ bin, и введите —

mysqld.exe --console

ПРИМЕЧАНИЕ. — Если вы работаете в NT, вам придется использовать mysqld-nt.exe вместо mysqld.exe

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

MySQL не добавит себя в меню «Пуск», и нет никакого приятного графического интерфейса для остановки сервера. Поэтому, если вы склонны запускать сервер двойным щелчком по исполняемому файлу mysqld, не забывайте останавливать процесс вручную, используя mysqladmin, Task List, Task Manager или другие средства, специфичные для Windows.

Проверка установки MySQL

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

Используйте утилиту mysqladmin для получения статуса сервера

Используйте бинарный файл mysqladmin для проверки версии сервера. Этот двоичный файл будет доступен в / usr / bin в Linux и в C: \ mysql \ bin в Windows.

[root@host]# mysqladmin --version

Это даст следующий результат в Linux. Это может варьироваться в зависимости от вашей установки —

mysqladmin  Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386

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

Выполните простые команды SQL, используя MySQL Client

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

Вы можете просто использовать следующую команду —

[root@host]# mysql

Это должно быть вознаграждено приглашением mysql>. Теперь вы подключены к серверу MySQL и можете выполнить все команды SQL в приглашении mysql> следующим образом:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
|   mysql  | 
|   test   |  
+----------+
2 rows in set (0.13 sec)

Шаги после установки

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

[root@host]# mysqladmin -u root password "new_password";

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

[root@host]# mysql -u root -p
Enter password:*******

Пользователи UNIX также захотят поместить ваш каталог MySQL в ваш PATH, поэтому вам не придется вводить полный путь каждый раз, когда вы хотите использовать клиент командной строки.

Для bash это было бы что-то вроде —

export PATH = $PATH:/usr/bin:/usr/sbin

Запуск MySQL во время загрузки

Если вы хотите запустить сервер MySQL во время загрузки, убедитесь, что в файле /etc/rc.local есть следующая запись.

/etc/init.d/mysqld start

Также у вас должен быть бинарный файл mysqld в каталоге /etc/init.d/.

MySQL — Администрирование

Запуск и завершение работы MySQL Server

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

ps -ef | grep mysqld

Если ваш MySql запущен, вы увидите процесс mysqld, перечисленный в вашем результате. Если сервер не запущен, вы можете запустить его с помощью следующей команды —

root@host# cd /usr/bin
./safe_mysqld &

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

root@host# cd /usr/bin
./mysqladmin -u root -p shutdown
Enter password: ******

Настройка учетной записи пользователя MySQL

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

Следующая программа является примером добавления нового гостя пользователя с привилегиями SELECT, INSERT и UPDATE с паролем guest123; SQL-запрос —

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
   (host, user, password, 
   select_priv, insert_priv, update_priv) 
   VALUES ('localhost', 'guest', 
   PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
|    host   |   user  |     password     |    
+-----------+---------+------------------+
| localhost |  guest  | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

При добавлении нового пользователя не забудьте зашифровать новый пароль с помощью функции PASSWORD (), предоставляемой MySQL. Как видно из приведенного выше примера, пароль mypass зашифрован на 6f8c114b58f2ce9e.

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

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

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

Другой способ добавить учетную запись пользователя — использовать команду GRANT SQL. В следующем примере будет добавлен пользователь zara с паролем zara123 для конкретной базы данных, которая называется TUTORIALS .

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
   -> ON TUTORIALS.*
   -> TO 'zara'@'localhost'
   -> IDENTIFIED BY 'zara123';

Это также создаст запись в таблице базы данных MySQL, которая называется user .

ПРИМЕЧАНИЕ. — MySQL не завершает команду, пока вы не введете точку с запятой (;) в конце команды SQL.

Конфигурация файла /etc/my.cnf

В большинстве случаев вам не следует трогать этот файл. По умолчанию он будет иметь следующие записи —

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

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

Административная команда MySQL

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

• USE Databasename — это будет использоваться для выбора базы данных в рабочей области MySQL.

• ПОКАЗАТЬ БАЗЫ ДАННЫХ — перечисляет базы данных, которые доступны СУБД MySQL.

• SHOW TABLES — Показывает таблицы в базе данных после выбора базы данных командой use.

• SHOW COLUMNS FROM tablename: показывает атрибуты, типы атрибутов, ключевую информацию, разрешен ли NULL, значения по умолчанию и другую информацию для таблицы.

• SHOW INDEX FROM tablename — представляет подробную информацию обо всех индексах таблицы, включая ПЕРВИЧНЫЙ КЛЮЧ.

• SHOW TABLE STATUS LIKE tablename \ G — Сообщает подробную информацию о производительности и статистике СУБД MySQL.

USE Databasename — это будет использоваться для выбора базы данных в рабочей области MySQL.

ПОКАЗАТЬ БАЗЫ ДАННЫХ — перечисляет базы данных, которые доступны СУБД MySQL.

SHOW TABLES — Показывает таблицы в базе данных после выбора базы данных командой use.

SHOW COLUMNS FROM tablename: показывает атрибуты, типы атрибутов, ключевую информацию, разрешен ли NULL, значения по умолчанию и другую информацию для таблицы.

SHOW INDEX FROM tablename — представляет подробную информацию обо всех индексах таблицы, включая ПЕРВИЧНЫЙ КЛЮЧ.

SHOW TABLE STATUS LIKE tablename \ G — Сообщает подробную информацию о производительности и статистике СУБД MySQL.

В следующей главе мы обсудим, как синтаксис PHP используется в MySQL.

MySQL — синтаксис PHP

MySQL очень хорошо работает в сочетании с различными языками программирования, такими как PERL, C, C ++, JAVA и PHP. Из этих языков PHP является самым популярным из-за своих возможностей разработки веб-приложений.

В этом руководстве основное внимание уделяется использованию MySQL в среде PHP. Если вы заинтересованы в MySQL с PERL, вы можете прочитать руководство по PERL .

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

Функции PHP для использования с MySQL имеют следующий общий формат —

mysql_ function (value,value,...);

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

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

В следующем примере показан общий синтаксис PHP для вызова любой функции MySQL.

<html>
   <head>
      <title>PHP with MySQL</title>
   </head>
   
   <body>
      <?php
         $retval = mysql_ function (value, [value,...]);
         if( !$retval ) {
            die ( "Error: a related error message" );
         }
         // Otherwise MySQL  or PHP Statements
      ?>
   </body>
</html>

Начиная со следующей главы, мы увидим все важные функции MySQL вместе с PHP.

MySQL — соединение

MySQL Connection Используя MySQL Binary

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

пример

Вот простой пример подключения к серверу MySQL из командной строки:

[root@host]# mysql -u root -p
Enter password:******

Это даст вам командную строку mysql>, где вы сможете выполнить любую команду SQL. Ниже приведен результат вышеупомянутой команды —

Следующий блок кода показывает результат кода выше —

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2854760 to server version: 5.0.9

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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

Вы можете отключиться от базы данных MySQL в любое время, используя команду exit в приглашении mysql>.

mysql> exit
Bye

MySQL Connection Используя PHP Script

PHP предоставляет функцию mysql_connect () для открытия соединения с базой данных. Эта функция принимает пять параметров и возвращает идентификатор ссылки MySQL в случае успеха или FALSE в случае ошибки.

Синтаксис

connection mysql_connect(server,user,passwd,new_link,client_flag);

Sr.No.	Параметр и описание
1	сервер Необязательно — имя хоста, на котором работает сервер базы данных. Если не указан, то значением по умолчанию будет localhost: 3306 .
2	пользователь Необязательно — имя пользователя для доступа к базе данных. Если не указан, то по умолчанию будет указано имя пользователя, которому принадлежит процесс сервера.
3	ПАРОЛЬ Необязательно — пароль пользователя, обращающегося к базе данных. Если не указан, то по умолчанию будет пустой пароль.
4	new_link Необязательно — Если для mysql_connect () сделан второй вызов с теми же аргументами, новое соединение не будет установлено; вместо этого будет возвращен идентификатор уже открытого соединения.
5	client_flags Необязательно — комбинация следующих констант — MYSQL_CLIENT_SSL — использовать шифрование SSL. MYSQL_CLIENT_COMPRESS — Использовать протокол сжатия. MYSQL_CLIENT_IGNORE_SPACE — Разрешить пробел после имен функций. MYSQL_CLIENT_INTERACTIVE — разрешить интерактивное время ожидания в секундах бездействия перед закрытием соединения.

сервер

Необязательно — имя хоста, на котором работает сервер базы данных. Если не указан, то значением по умолчанию будет localhost: 3306 .

пользователь

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

ПАРОЛЬ

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

new_link

Необязательно — Если для mysql_connect () сделан второй вызов с теми же аргументами, новое соединение не будет установлено; вместо этого будет возвращен идентификатор уже открытого соединения.

client_flags

Необязательно — комбинация следующих констант —

MYSQL_CLIENT_SSL — использовать шифрование SSL.

MYSQL_CLIENT_COMPRESS — Использовать протокол сжатия.

MYSQL_CLIENT_IGNORE_SPACE — Разрешить пробел после имен функций.

MYSQL_CLIENT_INTERACTIVE — разрешить интерактивное время ожидания в секундах бездействия перед закрытием соединения.

Вы можете отключиться от базы данных MySQL в любое время, используя другую функцию PHP mysql_close () . Эта функция принимает единственный параметр, который является соединением, возвращаемым функцией mysql_connect () .

Синтаксис

bool mysql_close ( resource $link_identifier );

Если ресурс не указан, то последняя открытая база данных закрывается. Эта функция возвращает true, если она успешно закрывает соединение, в противном случае возвращает false.

пример

Попробуйте следующий пример для подключения к серверу MySQL —

<html>
   <head>
      <title>Connecting MySQL Server</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost:3306';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully';
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL — Создать базу данных

Создать базу данных с помощью mysqladmin

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

пример

Вот простой пример создания базы данных под названием TUTORIALS —

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

Это создаст базу данных MySQL под названием TUTORIALS.

Создать базу данных с помощью PHP Script

PHP использует функцию mysql_query для создания или удаления базы данных MySQL. Эта функция принимает два параметра и возвращает TRUE в случае успеха или FALSE в случае ошибки.

Синтаксис

bool mysql_query( sql, connection );

Sr.No.	Параметр и описание
1	SQL Обязательно — запрос SQL для создания или удаления базы данных MySQL
2	соединение Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

SQL

Обязательно — запрос SQL для создания или удаления базы данных MySQL

соединение

Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

пример

Следующий пример для создания базы данных —

<html>
   <head>
      <title>Creating MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'CREATE DATABASE TUTORIALS';
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create database: ' . mysql_error());
         }
         echo "Database TUTORIALS created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

Удалить базу данных MySQL

Удалить базу данных с помощью mysqladmin

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

Будьте осторожны при удалении любой базы данных, потому что вы потеряете все данные, имеющиеся в вашей базе данных.

Вот пример для удаления базы данных (учебники), созданной в предыдущей главе —

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

Это даст вам предупреждение и подтвердит, действительно ли вы хотите удалить эту базу данных или нет.

Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'TUTORIALS' database [y/N] y
Database "TUTORIALS" dropped

Удалить базу данных с помощью PHP Script

PHP использует функцию mysql_query для создания или удаления базы данных MySQL. Эта функция принимает два параметра и возвращает TRUE в случае успеха или FALSE в случае ошибки.

Синтаксис

bool mysql_query( sql, connection );

Sr.No	Параметр и описание
1	SQL Обязательно — запрос SQL для создания или удаления базы данных MySQL
2	соединение Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

SQL

Обязательно — запрос SQL для создания или удаления базы данных MySQL

соединение

Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

пример

Попробуйте следующий пример, чтобы удалить базу данных —

<html>
   <head>
      <title>Deleting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = 'DROP DATABASE TUTORIALS';
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete database: ' . mysql_error());
         }
         echo "Database TUTORIALS deleted successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

ПРЕДУПРЕЖДЕНИЕ. При удалении базы данных с использованием сценария PHP он не запрашивает никакого подтверждения. Поэтому будьте осторожны при удалении базы данных MySQL.

Выбор базы данных MySQL

Как только вы подключаетесь к серверу MySQL, вам необходимо выбрать базу данных для работы. Это потому, что может быть несколько баз данных, доступных с MySQL Server.

Выбор базы данных MySQL из командной строки

Выбрать базу данных из командной строки mysql> очень просто. Вы можете использовать команду SQL для выбора базы данных.

пример

Вот пример для выбора базы данных под названием TUTORIALS —

[root@host]# mysql -u root -p
Enter password:******
mysql> use TUTORIALS;
Database changed
mysql> 

Теперь вы выбрали базу данных TUTORIALS, и все последующие операции будут выполнены с базой данных TUTORIALS.

ПРИМЕЧАНИЕ. — Все имена баз данных, имена таблиц, имена полей таблиц чувствительны к регистру. Таким образом, вы должны будете использовать правильные имена, давая любую команду SQL.

Выбор базы данных MySQL с использованием PHP-скрипта

PHP предоставляет функцию mysql_select_db для выбора базы данных. Возвращает TRUE в случае успеха или FALSE в случае неудачи.

Синтаксис

bool mysql_select_db( db_name, connection );

Sr.No.	Параметр и описание
1	db_name Обязательно — имя базы данных MySQL для выбора
2	соединение Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

db_name

Обязательно — имя базы данных MySQL для выбора

соединение

Необязательно — если не указан, будет использовано последнее открытое соединение с помощью mysql_connect.

пример

Вот пример, показывающий, как выбрать базу данных.

<html>
   <head>
      <title>Selecting MySQL Database</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'guest';
         $dbpass = 'guest123';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully';
         mysql_select_db( 'TUTORIALS' );
         
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL — типы данных

Правильное определение полей в таблице важно для общей оптимизации вашей базы данных. Вы должны использовать только тот тип и размер поля, которые вам действительно нужны. Например, не определяйте поле шириной 10 символов, если вы знаете, что собираетесь использовать только 2 символа. Эти типы полей (или столбцов) также называются типами данных после типа данных, которые вы будете хранить в этих полях.

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

• числовой
• Дата и время
• Типы строк.

Давайте теперь обсудим их подробно.

Числовые типы данных

MySQL использует все стандартные числовые типы данных ANSI SQL, поэтому, если вы переходите на MySQL из другой системы баз данных, эти определения будут вам знакомы.

В следующем списке показаны распространенные числовые типы данных и их описания.

• INT — целое число нормального размера, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -2147483648 до 2147483647. Если не подписан, допустимый диапазон составляет от 0 до 4294967295. Можно указать ширину до 11 цифр.

• TINYINT — очень маленькое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -128 до 127. Если без знака, допустимый диапазон составляет от 0 до 255. Можно указать ширину до 4 цифр.

• SMALLINT — небольшое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -32768 до 32767. Если не подписан, допустимый диапазон составляет от 0 до 65535. Можно указать ширину до 5 цифр.

• MEDIUMINT — целое число среднего размера, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -8388608 до 8388607. Если не подписан, допустимый диапазон составляет от 0 до 16777215. Вы можете указать ширину до 9 цифр.

• BIGINT — большое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -9223372036854775808 до 9223372036854775807. Если он не подписан, допустимый диапазон составляет от 0 до 18446744073709551615. Можно указать ширину до 20 цифр.

• FLOAT (M, D) — число с плавающей точкой, которое не может быть без знака. Вы можете определить длину дисплея (M) и количество десятичных знаков (D). Это не обязательно и по умолчанию будет 10,2, где 2 — количество десятичных знаков, а 10 — общее количество цифр (включая десятичные дроби). Десятичная точность может идти до 24 мест для FLOAT.

• DOUBLE (M, D) — число с плавающей запятой двойной точности, которое не может быть без знака. Вы можете определить длину дисплея (M) и количество десятичных знаков (D). Это не обязательно и по умолчанию будет 16,4, где 4 — количество десятичных знаков. Десятичная точность может пойти в 53 места для ДВОЙНОГО. REAL является синонимом для DOUBLE.

• DECIMAL (M, D) — неупакованное число с плавающей точкой, которое не может быть без знака. В распакованных десятичных числах каждое десятичное число соответствует одному байту. Требуется определить длину отображения (M) и количество десятичных знаков (D). NUMERIC — это синоним DECIMAL.

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

TINYINT — очень маленькое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -128 до 127. Если без знака, допустимый диапазон составляет от 0 до 255. Можно указать ширину до 4 цифр.

SMALLINT — небольшое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -32768 до 32767. Если не подписан, допустимый диапазон составляет от 0 до 65535. Можно указать ширину до 5 цифр.

MEDIUMINT — целое число среднего размера, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -8388608 до 8388607. Если не подписан, допустимый диапазон составляет от 0 до 16777215. Вы можете указать ширину до 9 цифр.

BIGINT — большое целое число, которое может быть подписано или не подписано. Если подписано, допустимый диапазон составляет от -9223372036854775808 до 9223372036854775807. Если он не подписан, допустимый диапазон составляет от 0 до 18446744073709551615. Можно указать ширину до 20 цифр.

FLOAT (M, D) — число с плавающей точкой, которое не может быть без знака. Вы можете определить длину дисплея (M) и количество десятичных знаков (D). Это не обязательно и по умолчанию будет 10,2, где 2 — количество десятичных знаков, а 10 — общее количество цифр (включая десятичные дроби). Десятичная точность может идти до 24 мест для FLOAT.

DOUBLE (M, D) — число с плавающей запятой двойной точности, которое не может быть без знака. Вы можете определить длину дисплея (M) и количество десятичных знаков (D). Это не обязательно и по умолчанию будет 16,4, где 4 — количество десятичных знаков. Десятичная точность может пойти в 53 места для ДВОЙНОГО. REAL является синонимом для DOUBLE.

DECIMAL (M, D) — неупакованное число с плавающей точкой, которое не может быть без знака. В распакованных десятичных числах каждое десятичное число соответствует одному байту. Требуется определить длину отображения (M) и количество десятичных знаков (D). NUMERIC — это синоним DECIMAL.

Типы даты и времени

Типы данных даты и времени MySQL:

• ДАТА — дата в формате ГГГГ-ММ-ДД, между 1000-01-01 и 9999-12-31. Например, 30 декабря 1973 года будет храниться как 1973-12-30.

• DATETIME — комбинация даты и времени в формате ГГГГ-ММ-ДД ЧЧ: ММ: СС, между 1000-01-01 00:00:00 и 9999-12-31 23:59:59. Например, 3:30 дня 30 декабря 1973 года будет храниться как 1973-12-30 15:30:00.

• TIMESTAMP — отметка времени между полуночью 1 января 1970 г. и примерно 2037 г. Это похоже на предыдущий формат DATETIME, только без дефисов между числами; 3:30 во второй половине дня 30 декабря 1973 г. будет сохранено как 19731230153000 (ГГГГММДДЧЧММСС).

• ВРЕМЯ — сохраняет время в формате ЧЧ: ММ: СС.

• ГОД (М) — Сохраняет год в двухзначном или четырехзначном формате. Если длина указана как 2 (например, ГОД (2)), ГОД может быть между 1970 и 2069 (70-69). Если длина указана как 4, то ГОД может быть от 1901 до 2155. Длина по умолчанию — 4.

ДАТА — дата в формате ГГГГ-ММ-ДД, между 1000-01-01 и 9999-12-31. Например, 30 декабря 1973 года будет храниться как 1973-12-30.

DATETIME — комбинация даты и времени в формате ГГГГ-ММ-ДД ЧЧ: ММ: СС, между 1000-01-01 00:00:00 и 9999-12-31 23:59:59. Например, 3:30 дня 30 декабря 1973 года будет храниться как 1973-12-30 15:30:00.

TIMESTAMP — отметка времени между полуночью 1 января 1970 г. и примерно 2037 г. Это похоже на предыдущий формат DATETIME, только без дефисов между числами; 3:30 во второй половине дня 30 декабря 1973 г. будет сохранено как 19731230153000 (ГГГГММДДЧЧММСС).

ВРЕМЯ — сохраняет время в формате ЧЧ: ММ: СС.

ГОД (М) — Сохраняет год в двухзначном или четырехзначном формате. Если длина указана как 2 (например, ГОД (2)), ГОД может быть между 1970 и 2069 (70-69). Если длина указана как 4, то ГОД может быть от 1901 до 2155. Длина по умолчанию — 4.

Типы строк

Хотя числовой тип и тип даты являются забавными, большинство данных, которые вы будете хранить, будут в строковом формате. Этот список описывает общие типы данных строки в MySQL.

• CHAR (M) — строка фиксированной длины длиной от 1 до 255 символов (например, CHAR (5)), дополненная справа пробелами до указанной длины при сохранении. Определение длины не требуется, но по умолчанию 1.

• VARCHAR (M) — строка переменной длины длиной от 1 до 255 символов. Например, VARCHAR (25). Вы должны определить длину при создании поля VARCHAR.

• BLOB или TEXT — поле длиной не более 65535 символов. BLOB — это «большие двоичные объекты», которые используются для хранения больших объемов двоичных данных, таких как изображения или другие типы файлов. Поля, определенные как ТЕКСТ, также содержат большие объемы данных. Разница между ними заключается в том, что сортировки и сравнения сохраненных данных чувствительны к регистру на BLOB и не чувствительны к регистру в полях TEXT. Вы не указываете длину с BLOB или TEXT.

• TINYBLOB или TINYTEXT — столбец BLOB или TEXT, максимальная длина которого составляет 255 символов. Вы не указываете длину с TINYBLOB или TINYTEXT.

• MEDIUMBLOB или MEDIUMTEXT — столбец BLOB или TEXT, максимальная длина которого составляет 16777215 символов. Вы не указываете длину с MEDIUMBLOB или MEDIUMTEXT.

• LONGBLOB или LONGTEXT — столбец BLOB или TEXT, максимальная длина которого 4294967295 символов. Вы не указываете длину с LONGBLOB или LONGTEXT.

• ENUM — перечисление, которое является необычным термином для списка. При определении ENUM вы создаете список элементов, из которых необходимо выбрать значение (или оно может быть NULL). Например, если вы хотите, чтобы ваше поле содержало «A», «B» или «C», вы должны определить свой ENUM как ENUM («A», «B», «C») и только эти значения (или NULL). мог когда-нибудь заполнить это поле.

CHAR (M) — строка фиксированной длины длиной от 1 до 255 символов (например, CHAR (5)), дополненная справа пробелами до указанной длины при сохранении. Определение длины не требуется, но по умолчанию 1.

VARCHAR (M) — строка переменной длины длиной от 1 до 255 символов. Например, VARCHAR (25). Вы должны определить длину при создании поля VARCHAR.

BLOB или TEXT — поле длиной не более 65535 символов. BLOB — это «большие двоичные объекты», которые используются для хранения больших объемов двоичных данных, таких как изображения или другие типы файлов. Поля, определенные как ТЕКСТ, также содержат большие объемы данных. Разница между ними заключается в том, что сортировки и сравнения сохраненных данных чувствительны к регистру на BLOB и не чувствительны к регистру в полях TEXT. Вы не указываете длину с BLOB или TEXT.

TINYBLOB или TINYTEXT — столбец BLOB или TEXT, максимальная длина которого составляет 255 символов. Вы не указываете длину с TINYBLOB или TINYTEXT.

MEDIUMBLOB или MEDIUMTEXT — столбец BLOB или TEXT, максимальная длина которого составляет 16777215 символов. Вы не указываете длину с MEDIUMBLOB или MEDIUMTEXT.

LONGBLOB или LONGTEXT — столбец BLOB или TEXT, максимальная длина которого 4294967295 символов. Вы не указываете длину с LONGBLOB или LONGTEXT.

ENUM — перечисление, которое является необычным термином для списка. При определении ENUM вы создаете список элементов, из которых необходимо выбрать значение (или оно может быть NULL). Например, если вы хотите, чтобы ваше поле содержало «A», «B» или «C», вы должны определить свой ENUM как ENUM («A», «B», «C») и только эти значения (или NULL). мог когда-нибудь заполнить это поле.

В следующей главе мы обсудим, как создавать таблицы в MySQL.

Создать таблицы MySQL

Для начала, команда создания таблицы требует следующих деталей —

• Название стола
• Наименование полей
• Определения для каждого поля

Синтаксис

Вот общий синтаксис SQL для создания таблицы MySQL:

CREATE TABLE table_name (column_name column_type);

Теперь мы создадим следующую таблицу в базе данных TUTORIALS .

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
   PRIMARY KEY ( tutorial_id )
);

Здесь некоторые пункты требуют объяснения —

• Атрибут поля NOT NULL используется, потому что мы не хотим, чтобы это поле было NULL. Таким образом, если пользователь попытается создать запись со значением NULL, MySQL выдаст ошибку.

• Атрибут поля AUTO_INCREMENT говорит MySQL, что нужно идти дальше и добавить следующий доступный номер в поле id.

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

Атрибут поля NOT NULL используется, потому что мы не хотим, чтобы это поле было NULL. Таким образом, если пользователь попытается создать запись со значением NULL, MySQL выдаст ошибку.

Атрибут поля AUTO_INCREMENT говорит MySQL, что нужно идти дальше и добавить следующий доступный номер в поле id.

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

Создание таблиц из командной строки

Создать таблицу MySQL легко из приглашения mysql>. Вы будете использовать команду SQL CREATE TABLE для создания таблицы.

пример

Вот пример, который создаст tutorials_tbl —

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> CREATE TABLE tutorials_tbl(
   -> tutorial_id INT NOT NULL AUTO_INCREMENT,
   -> tutorial_title VARCHAR(100) NOT NULL,
   -> tutorial_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( tutorial_id )
   -> );
Query OK, 0 rows affected (0.16 sec)
mysql>

ПРИМЕЧАНИЕ. — MySQL не завершает команду, пока вы не дадите точку с запятой (;) в конце команды SQL.

Создание таблиц с использованием PHP Script

Для создания новой таблицы в любой существующей базе данных вам необходимо использовать функцию PHP mysql_query () . Вы передадите второй аргумент с правильной командой SQL для создания таблицы.

пример

Следующая программа является примером для создания таблицы с использованием сценария PHP —

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = "CREATE TABLE tutorials_tbl( ".
            "tutorial_id INT NOT NULL AUTO_INCREMENT, ".
            "tutorial_title VARCHAR(100) NOT NULL, ".
            "tutorial_author VARCHAR(40) NOT NULL, ".
            "submission_date DATE, ".
            "PRIMARY KEY ( tutorial_id )); ";
         mysql_select_db( 'TUTORIALS' );
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not create table: ' . mysql_error());
         }
         echo "Table created successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

Отбросьте таблицы MySQL

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

Синтаксис

Вот общий синтаксис SQL для удаления таблицы MySQL —

DROP TABLE table_name ;

Удаление таблиц из командной строки

Чтобы удалить таблицы из командной строки, нам нужно выполнить команду SQL DROP TABLE в командной строке mysql>.

пример

Следующая программа является примером, который удаляет tutorials_tbl —

root@host# mysql -u root -p
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> DROP TABLE tutorials_tbl
Query OK, 0 rows affected (0.8 sec)
mysql>

Удаление таблиц с использованием PHP-скрипта

Чтобы удалить существующую таблицу в любой базе данных, вам нужно использовать функцию PHP mysql_query () . Вы передадите второй аргумент с правильной командой SQL для удаления таблицы.

пример

<html>
   <head>
      <title>Creating MySQL Tables</title>
   </head>
   
   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'root';
         $dbpass = 'rootpassword';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         echo 'Connected successfully<br />';
         $sql = "DROP TABLE tutorials_tbl";
         mysql_select_db( 'TUTORIALS' );
         $retval = mysql_query( $sql, $conn );
         
         if(! $retval ) {
            die('Could not delete table: ' . mysql_error());
         }
         echo "Table deleted successfully\n";
         mysql_close($conn);
      ?>
   </body>
</html>

MySQL — Вставить запрос

Чтобы вставить данные в таблицу MySQL, вам необходимо использовать команду SQL INSERT INTO . Вы можете вставить данные в таблицу MySQL, используя приглашение mysql> или любой другой скрипт, например PHP.

Синтаксис

Вот общий синтаксис SQL команды INSERT INTO для вставки данных в таблицу MySQL:

INSERT INTO table_name ( field1, field2,...fieldN )
   VALUES
   ( value1, value2,...valueN );

Чтобы вставить строковые типы данных, необходимо сохранить все значения в двойных или одинарных кавычках. Например «значение» .

Вставка данных из командной строки

Чтобы вставить данные из командной строки, мы будем использовать команду SQL INSERT INTO для вставки данных в таблицу MySQL tutorials_tbl.

пример

Следующий пример создаст 3 записи в таблицу tutorials_tbl —

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed

mysql> INSERT INTO tutorials_tbl 
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("Learn PHP", "John Poul", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("Learn MySQL", "Abdul S", NOW());
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO tutorials_tbl
   ->(tutorial_title, tutorial_author, submission_date)
   ->VALUES
   ->("JAVA Tutorial", "Sanjay", '2007-05-06');
Query OK, 1 row affected (0.01 sec)
mysql>

ПРИМЕЧАНИЕ. — Обратите внимание, что все знаки со стрелками (->) не являются частью команды SQL. Они указывают новую строку, и они автоматически создаются приглашением MySQL при нажатии клавиши ввода без точки с запятой в конце каждой строки команды.

В приведенном выше примере мы не предоставили tutorial_id, потому что во время создания таблицы мы указали опцию AUTO_INCREMENT для этого поля. Поэтому MySQL позаботится об автоматической вставке этих идентификаторов. Здесь NOW () — это функция MySQL, которая возвращает текущую дату и время.

Вставка данных с использованием PHP-скрипта

Вы можете использовать ту же команду SQL INSERT INTO в функции PHP mysql_query () для вставки данных в таблицу MySQL.

пример

Этот пример получит три параметра от пользователя и вставит их в таблицу MySQL:

<html>

   <head>
      <title>Add New Record in MySQL Database</title>
   </head>

   <body>
      <?php
         if(isset($_POST['add'])) {
            $dbhost = 'localhost:3036';
            $dbuser = 'root';
            $dbpass = 'rootpassword';
            $conn = mysql_connect($dbhost, $dbuser, $dbpass);
         
            if(! $conn ) {
               die('Could not connect: ' . mysql_error());
            }

            if(! get_magic_quotes_gpc() ) {
               $tutorial_title = addslashes ($_POST['tutorial_title']);
               $tutorial_author = addslashes ($_POST['tutorial_author']);
            } else {
               $tutorial_title = $_POST['tutorial_title'];
               $tutorial_author = $_POST['tutorial_author'];
            }

            $submission_date = $_POST['submission_date'];
   
            $sql = "INSERT INTO tutorials_tbl ".
               "(tutorial_title,tutorial_author, submission_date) "."VALUES ".
               "('$tutorial_title','$tutorial_author','$submission_date')";
               mysql_select_db('TUTORIALS');
            $retval = mysql_query( $sql, $conn );
         
            if(! $retval ) {
               die('Could not enter data: ' . mysql_error());
            }
         
            echo "Entered data successfully\n";
            mysql_close($conn);
         } else {
      ?>
   
      <form method = "post" action = "<?php $_PHP_SELF ?>">
         <table width = "600" border = "0" cellspacing = "1" cellpadding = "2">
            <tr>
               <td width = "250">Tutorial Title</td>
               <td>
                  <input name = "tutorial_title" type = "text" id = "tutorial_title">
               </td>
            </tr>
         
            <tr>
               <td width = "250">Tutorial Author</td>
               <td>
                  <input name = "tutorial_author" type = "text" id = "tutorial_author">
               </td>
            </tr>
         
            <tr>
               <td width = "250">Submission Date [   yyyy-mm-dd ]</td>
               <td>
                  <input name = "submission_date" type = "text" id = "submission_date">
               </td>
            </tr>
      
            <tr>
               <td width = "250"> </td>
               <td> </td>
            </tr>
         
            <tr>
               <td width = "250"> </td>
               <td>
                  <input name = "add" type = "submit" id = "add"  value = "Add Tutorial">
               </td>
            </tr>
         </table>
      </form>
   <?php
      }
   ?>
   </body>
</html>

При выполнении вставки данных лучше всего использовать функцию get_magic_quotes_gpc (), чтобы проверить, установлена ​​или нет текущая конфигурация для магической цитаты. Если эта функция возвращает false, используйте функцию addlashes () для добавления косой черты перед кавычками.

Вы можете использовать множество проверок для проверки правильности введенных данных и принятия соответствующих мер.

MySQL — выберите запрос

Команда SQL SELECT используется для извлечения данных из базы данных MySQL. Вы можете использовать эту команду в приглашении mysql>, а также в любом скрипте, таком как PHP.

Синтаксис

Вот общий синтаксис SQL команды SELECT для извлечения данных из таблицы MySQL:

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[OFFSET M ][LIMIT N]

• Вы можете использовать одну или несколько таблиц, разделенных запятой, чтобы включить различные условия, используя предложение WHERE, но предложение WHERE является необязательной частью команды SELECT.

• Вы можете выбрать одно или несколько полей в одной команде SELECT.

• Вы можете указать звездочку (*) вместо полей. В этом случае SELECT вернет все поля.

• Вы можете указать любое условие, используя предложение WHERE.

• Вы можете указать смещение, используя OFFSET, откуда SELECT начнет возвращать записи. По умолчанию смещение начинается с нуля.

• Вы можете ограничить количество возвратов, используя атрибут LIMIT .

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

Вы можете выбрать одно или несколько полей в одной команде SELECT.

Вы можете указать звездочку (*) вместо полей. В этом случае SELECT вернет все поля.

Вы можете указать любое условие, используя предложение WHERE.

Вы можете указать смещение, используя OFFSET, откуда SELECT начнет возвращать записи. По умолчанию смещение начинается с нуля.

Вы можете ограничить количество возвратов, используя атрибут LIMIT .

Извлечение данных из командной строки

Это будет использовать команду SQL SELECT для извлечения данных из таблицы MySQL tutorials_tbl .

пример

Следующий пример вернет все записи из таблицы tutorials_tbl —

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Learn PHP      | John Poul       | 2007-05-21      |
|           2 | Learn MySQL    | Abdul S         | 2007-05-21      |
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.01 sec)

mysql>

Извлечение данных с использованием PHP-скрипта

Вы можете использовать ту же команду SQL SELECT в функцию PHP mysql_query () . Эта функция используется для выполнения команды SQL, а затем другая функция PHP mysql_fetch_array () может использоваться для извлечения всех выбранных данных. Эта функция возвращает строку в виде ассоциативного массива, числового массива или обоих. Эта функция возвращает FALSE, если строк больше нет.

Следующая программа представляет собой простой пример, который покажет, как извлекать / отображать записи из таблицы tutorials_tbl .

пример

Следующий блок кода отобразит все записи из таблицы tutorials_tbl.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

Содержимое строк присваивается переменной $ row, а затем выводятся значения в этой строке.

ПРИМЕЧАНИЕ. — Не забывайте ставить фигурные скобки, когда хотите вставить значение массива непосредственно в строку.

В приведенном выше примере константа MYSQL_ASSOC используется в качестве второго аргумента функции PHP mysql_fetch_array () , поэтому она возвращает строку в виде ассоциативного массива. С ассоциативным массивом вы можете получить доступ к полю, используя их имя вместо индекса.

PHP предоставляет другую функцию mysql_fetch_assoc () , которая также возвращает строку в виде ассоциативного массива.

пример

В следующем примере отображаются все записи из таблицы tutorial_tbl с использованием функции mysql_fetch_assoc ().

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_assoc($retval)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

Вы также можете использовать константу MYSQL_NUM в качестве второго аргумента функции PHP mysql_fetch_array (). Это заставит функцию возвращать массив с числовым индексом.

пример

Попробуйте следующий пример, чтобы отобразить все записи из таблицы tutorials_tbl, используя аргумент MYSQL_NUM.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

Все три приведенных выше примера дадут одинаковый результат.

Освобождение памяти

Хорошей практикой является освобождение памяти курсора в конце каждого оператора SELECT. Это можно сделать с помощью функции PHP mysql_free_result () . Следующая программа является примером, показывающим, как ее следует использовать.

пример

Попробуйте следующий пример —

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   
   $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
      FROM tutorials_tbl';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }
   
   while($row = mysql_fetch_array($retval, MYSQL_NUM)) {
      echo "Tutorial ID :{$row[0]}  <br> ".
         "Title: {$row[1]} <br> ".
         "Author: {$row[2]} <br> ".
         "Submission Date : {$row[3]} <br> ".
         "--------------------------------<br>";
   }
   mysql_free_result($retval);
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

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

MySQL — предложение WHERE

Мы видели команду SQL SELECT для извлечения данных из таблицы MySQL. Мы можем использовать условное предложение, называемое предложением WHERE, чтобы отфильтровать результаты. Используя это предложение WHERE, мы можем указать критерии выбора для выбора требуемых записей из таблицы.

Синтаксис

Следующий блок кода имеет общий синтаксис SQL команды SELECT с предложением WHERE для извлечения данных из таблицы MySQL:

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

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

• Вы можете указать любое условие, используя предложение WHERE.

• Вы можете указать более одного условия, используя операторы AND или OR .

• Предложение WHERE может использоваться вместе с командой SQL DELETE или UPDATE также для указания условия.

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

Вы можете указать любое условие, используя предложение WHERE.

Вы можете указать более одного условия, используя операторы AND или OR .

Предложение WHERE может использоваться вместе с командой SQL DELETE или UPDATE также для указания условия.

Предложение WHERE работает как условие if в любом языке программирования. Это предложение используется для сравнения заданного значения со значением поля, доступным в таблице MySQL. Если данное значение извне равно значению доступного поля в таблице MySQL, то он возвращает эту строку.

Вот список операторов, которые можно использовать с предложением WHERE .

Предположим, что поле A содержит 10, а поле B содержит 20, тогда —

оператор	Описание	пример
знак равно	Проверяет, равны ли значения двух операндов или нет, если да, то условие становится истинным.	(A = B) не соответствует действительности.
знак равно	Проверяет, равны ли значения двух операндов или нет, если значения не равны, то условие становится истинным.	(A! = B) верно.
>	Проверяет, больше ли значение левого операнда, чем значение правого операнда, если да, то условие становится истинным.	(A> B) не соответствует действительности.
<	Проверяет, меньше ли значение левого операнда, чем значение правого операнда, если да, тогда условие становится истинным.	(A <B) верно.
> =	Проверяет, больше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(A> = B) не соответствует действительности.
<=	Проверяет, меньше ли значение левого операнда или равно значению правого операнда, если да, то условие становится истинным.	(A <= B) верно.

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

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

Если данное условие не соответствует ни одной записи в таблице, запрос не будет возвращать ни одной строки.

Извлечение данных из командной строки

Это будет использовать команду SQL SELECT с предложением WHERE для извлечения выбранных данных из таблицы MySQL — tutorials_tbl .

пример

В следующем примере будут возвращены все записи из таблицы tutorials_tbl, для которой имя автора Sanjay .

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl WHERE tutorial_author = 'Sanjay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-21   |      
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Если сравнение строки LIKE не выполняется, сравнение не учитывает регистр. Вы можете сделать поиск чувствительным к регистру, используя ключевое слово BINARY следующим образом:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl \
   WHERE BINARY tutorial_author = 'sanjay';
Empty set (0.02 sec)

mysql>

Извлечение данных с использованием PHP-скрипта

Вы можете использовать ту же команду SQL SELECT с WHERE CLAUSE в функцию PHP mysql_query () . Эта функция используется для выполнения команды SQL, а затем другая функция PHP mysql_fetch_array () может использоваться для извлечения всех выбранных данных. Эта функция возвращает строку в виде ассоциативного массива, числового массива или обоих. Эта функция возвращает FALSE, если строк больше нет.

пример

В следующем примере будут возвращены все записи из таблицы tutorials_tbl, для которой имя автора Sanjay —

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      WHERE tutorial_author = "Sanjay"';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
      "Title: {$row['tutorial_title']} <br> ".
      "Author: {$row['tutorial_author']} <br> ".
      "Submission Date : {$row['submission_date']} <br> ".
      "--------------------------------<br>";
   } 

   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL — ОБНОВЛЕНИЕ запроса

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

Синтаксис

Следующий блок кода имеет общий синтаксис SQL команды UPDATE для изменения данных в таблице MySQL:

UPDATE table_name SET field1 = new-value1, field2 = new-value2
[WHERE Clause]

• Вы можете обновить одно или несколько полей в целом.
• Вы можете указать любое условие, используя предложение WHERE.
• Вы можете обновлять значения в одной таблице за раз.

Предложение WHERE очень полезно, когда вы хотите обновить выбранные строки в таблице.

Обновление данных из командной строки

Это будет использовать команду SQL UPDATE с предложением WHERE для обновления выбранных данных в таблице MySQL tutorials_tbl .

пример

В следующем примере будет обновлено поле tutorial_title для записи с идентификатором tutorial_id, равным 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> UPDATE tutorials_tbl 
   -> SET tutorial_title = 'Learning JAVA' 
   -> WHERE tutorial_id = 3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Обновление данных с использованием PHP-скрипта

Вы можете использовать команду SQL UPDATE с или без WHERE CLAUSE в функцию PHP — mysql_query () . Эта функция выполнит команду SQL аналогично тому, как она выполняется в приглашении mysql>.

пример

В следующем примере обновляется поле tutorial_title для записи, имеющей tutorial_id = 3.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'UPDATE tutorials_tbl
      SET tutorial_title="Learning JAVA"
      WHERE tutorial_id=3';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not update data: ' . mysql_error());
   }
   echo "Updated data successfully\n";
   mysql_close($conn);
?>

MySQL — УДАЛИТЬ Запрос

Если вы хотите удалить запись из любой таблицы MySQL, вы можете использовать команду SQL DELETE FROM . Вы можете использовать эту команду в приглашении mysql>, а также в любом скрипте, таком как PHP.

Синтаксис

Следующий блок кода имеет общий синтаксис SQL команды DELETE для удаления данных из таблицы MySQL.

DELETE FROM table_name [WHERE Clause]

• Если предложение WHERE не указано, то все записи будут удалены из данной таблицы MySQL.

• Вы можете указать любое условие, используя предложение WHERE.

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

Если предложение WHERE не указано, то все записи будут удалены из данной таблицы MySQL.

Вы можете указать любое условие, используя предложение WHERE.

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

Предложение WHERE очень полезно, когда вы хотите удалить выбранные строки в таблице.

Удаление данных из командной строки

Для удаления выбранных данных в таблицу MySQL будет использована команда SQL DELETE с предложением WHERE — tutorials_tbl .

пример

В следующем примере удаляется запись из tutorial_tbl, у которой tutorial_id равен 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Удаление данных с помощью PHP-скрипта

Вы можете использовать команду SQL DELETE с или без WHERE CLAUSE в функцию PHP — mysql_query () . Эта функция будет выполнять команду SQL так же, как она выполняется в приглашении mysql>.

пример

Попробуйте в следующем примере удалить запись из tutorial_tbl, у которой tutorial_id равен 3.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'DELETE FROM tutorials_tbl WHERE tutorial_id = 3';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not delete data: ' . mysql_error());
   }
   echo "Deleted data successfully\n";
   mysql_close($conn);
?>

MySQL — как статья

Мы видели команду SQL SELECT для извлечения данных из таблицы MySQL. Мы также можем использовать условное предложение, называемое предложением WHERE , для выбора необходимых записей.

Предложение WHERE со знаком «равно» (=) отлично работает, когда мы хотим получить точное совпадение. Например, если «tutorial_author = ‘Sanjay'». Но может быть требование, когда мы хотим отфильтровать все результаты, где имя tutorial_author должно содержать «jay». Это можно сделать с помощью предложения SQL LIKE вместе с предложением WHERE.

Если предложение SQL LIKE используется вместе с символом%, то оно будет работать как метасимвол (*), как в UNIX, при этом перечисляя все файлы или каталоги в командной строке. Без символа% предложение LIKE очень похоже на знак равенства вместе с предложением WHERE.

Синтаксис

Следующий блок кода имеет общий синтаксис SQL команды SELECT вместе с предложением LIKE для извлечения данных из таблицы MySQL.

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• Вы можете указать любое условие, используя предложение WHERE.

• Вы можете использовать предложение LIKE вместе с предложением WHERE.

• Вы можете использовать предложение LIKE вместо знака равенства .

• Когда LIKE используется вместе со знаком%, он будет работать как поиск метасимволов.

• Вы можете указать более одного условия, используя операторы AND или OR .

• Предложение WHERE … LIKE может использоваться вместе с командой SQL DELETE или UPDATE также для указания условия.

Вы можете указать любое условие, используя предложение WHERE.

Вы можете использовать предложение LIKE вместе с предложением WHERE.

Вы можете использовать предложение LIKE вместо знака равенства .

Когда LIKE используется вместе со знаком%, он будет работать как поиск метасимволов.

Вы можете указать более одного условия, используя операторы AND или OR .

Предложение WHERE … LIKE может использоваться вместе с командой SQL DELETE или UPDATE также для указания условия.

Использование предложения LIKE в командной строке

Это будет использовать команду SQL SELECT с предложением WHERE … LIKE для извлечения выбранных данных из таблицы MySQL — tutorials_tbl .

пример

В следующем примере будут возвращены все записи из таблицы tutorials_tbl, для которых имя автора заканчивается на jay —

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl 
   -> WHERE tutorial_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      |  JAVA Tutorial |     Sanjay      |    2007-05-21   |   
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Использование предложения LIKE внутри PHP Script

Вы можете использовать похожий синтаксис предложения WHERE … LIKE в функции PHP — mysql_query () . Эта функция используется для выполнения команды SQL, а затем другая функция PHP — mysql_fetch_array () может использоваться для извлечения всех выбранных данных, если предложение WHERE … LIKE используется вместе с командой SELECT.

Но если предложение WHERE … LIKE используется с командой DELETE или UPDATE, дальнейший вызов функции PHP не требуется.

пример

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

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      WHERE tutorial_author LIKE "%jay%"';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL — сортировка результатов

Мы видели команду SQL SELECT для извлечения данных из таблицы MySQL. Когда вы выбираете строки, сервер MySQL может возвращать их в любом порядке, если только вы не указали, как отсортировать результат. Но вы сортируете результирующий набор, добавляя предложение ORDER BY , которое именует столбец или столбцы, которые вы хотите отсортировать.

Синтаксис

Следующий блок кода представляет собой общий синтаксис SQL команды SELECT вместе с предложением ORDER BY для сортировки данных из таблицы MySQL.

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

• Вы можете отсортировать возвращаемый результат по любому полю, если оно указано в списке.

• Вы можете отсортировать результат по нескольким полям.

• Вы можете использовать ключевое слово ASC или DESC, чтобы получить результат в порядке возрастания или убывания. По умолчанию это возрастающий порядок.

• Вы можете использовать предложение WHERE … LIKE обычным способом, чтобы поставить условие.

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

Вы можете отсортировать результат по нескольким полям.

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

Вы можете использовать предложение WHERE … LIKE обычным способом, чтобы поставить условие.

Использование предложения ORDER BY в командной строке

Это будет использовать команду SQL SELECT с предложением ORDER BY для извлечения данных из таблицы MySQL — tutorials_tbl .

пример

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

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      2      |  Learn MySQL   |     Abdul S     |    2007-05-24   |   
|      1      |   Learn PHP    |    John Poul    |    2007-05-24   |   
|      3      | JAVA Tutorial  |     Sanjay      |    2007-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.42 sec)

mysql>

Проверьте все имена авторов, перечисленные в порядке возрастания.

Использование предложения ORDER BY внутри скрипта PHP

Вы можете использовать похожий синтаксис предложения ORDER BY в функции PHP — mysql_query () . Эта функция используется для выполнения команды SQL, а затем другая функция PHP mysql_fetch_array () может использоваться для извлечения всех выбранных данных.

пример

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

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }
   $sql = 'SELECT tutorial_id, tutorial_title, 
      tutorial_author, submission_date
      FROM tutorials_tbl
      ORDER BY  tutorial_author DESC';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Tutorial ID :{$row['tutorial_id']}  <br> ".
         "Title: {$row['tutorial_title']} <br> ".
         "Author: {$row['tutorial_author']} <br> ".
         "Submission Date : {$row['submission_date']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

Использование MySQl Joins

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

Вы можете использовать несколько таблиц в одном запросе SQL. Акт объединения в MySQL относится к объединению двух или более таблиц в одну таблицу.

Вы можете использовать JOINS в инструкциях SELECT, UPDATE и DELETE для объединения таблиц MySQL. Мы также увидим пример LEFT JOIN, который отличается от простого MySQL JOIN.

Использование объединений в командной строке

Предположим, у нас есть две таблицы tcount_tbl и tutorials_tbl , в TUTORIALS. Теперь взгляните на примеры, приведенные ниже —

пример

Следующие примеры —

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * FROM tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|      mahran     |       20       |     
|      mahnaz     |      NULL      |        
|       Jen       |      NULL      |          
|      Gill       |       20       |          
|    John Poul    |        1       |      
|     Sanjay      |        1       |        
+-----------------+----------------+
6 rows in set (0.01 sec)
mysql> SELECT * from tutorials_tbl;
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      1      |  Learn PHP     |     John Poul   |    2007-05-24   |   
|      2      |  Learn MySQL   |      Abdul S    |    2007-05-24   |   
|      3      | JAVA Tutorial  |      Sanjay     |    2007-05-06   |   
+-------------+----------------+-----------------+-----------------+
3 rows in set (0.00 sec)
mysql>

Теперь мы можем написать SQL-запрос для объединения этих двух таблиц. Этот запрос выберет всех авторов из таблицы tutorials_tbl и выберет соответствующее количество учебников из tcount_tbl .

mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   -> FROM tutorials_tbl a, tcount_tbl b
   -> WHERE a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |        1       |
|      3      |     Sanjay      |        1       |
+-------------+-----------------+----------------+
2 rows in set (0.01 sec)
mysql>

Использование объединений в скрипте PHP

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

пример

Следующий пример —

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
      FROM tutorials_tbl a, tcount_tbl b
      WHERE a.tutorial_author = b.tutorial_author';

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );

   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "Tutorial ID: {$row['tutorial_id']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL LEFT JOIN

Левое соединение MySQL отличается от простого соединения. MySQL LEFT JOIN дает дополнительное рассмотрение таблицы слева.

Если я выполняю LEFT JOIN , я получаю все записи, которые совпадают одинаково, и IN ADDITION я получаю дополнительную запись для каждой несопоставленной записи в левой таблице соединения: таким образом, гарантируя (в моем примере), что каждый AUTHOR получает упомянуть.

пример

Попробуйте следующий пример, чтобы понять левое соединение.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count
   -> FROM tutorials_tbl a LEFT JOIN tcount_tbl b
   -> ON a.tutorial_author = b.tutorial_author;
+-------------+-----------------+----------------+
| tutorial_id | tutorial_author | tutorial_count |
+-------------+-----------------+----------------+
|      1      |    John Poul    |       1        |
|      2      |     Abdul S     |      NULL      |
|      3      |     Sanjay      |       1        |
+-------------+-----------------+----------------+
3 rows in set (0.02 sec)

Вам нужно больше практиковаться, чтобы познакомиться с JOINS. Это немного сложная концепция в MySQL / SQL и станет более понятной при выполнении реальных примеров.

Обработка MySQL NULL-значений

Мы видели команду SQL SELECT вместе с предложением WHERE для извлечения данных из таблицы MySQL, но когда мы пытаемся дать условие, которое сравнивает значение поля или столбца с NULL , оно не работает должным образом.

Чтобы справиться с такой ситуацией, MySQL предоставляет три оператора —

• IS NULL — этот оператор возвращает true, если значение столбца равно NULL.

• IS NOT NULL — этот оператор возвращает true, если значение столбца не равно NULL.

• <=> — Этот оператор сравнивает значения, которые (в отличие от оператора =) верны даже для двух значений NULL.

IS NULL — этот оператор возвращает true, если значение столбца равно NULL.

IS NOT NULL — этот оператор возвращает true, если значение столбца не равно NULL.

<=> — Этот оператор сравнивает значения, которые (в отличие от оператора =) верны даже для двух значений NULL.

Условия, связанные с NULL, особенные. Вы не можете использовать = NULL или! = NULL для поиска значений NULL в столбцах. Такие сравнения всегда терпят неудачу, потому что невозможно определить, являются ли они истинными или нет. Иногда даже NULL = NULL терпит неудачу.

Чтобы найти столбцы, которые являются или не равны NULL, используйте IS NULL или IS NOT NULL .

Использование значений NULL в командной строке

Предположим, что в базе данных TUTORIALS есть таблица с именем tcount_tbl, и она содержит два столбца, а именно tutorial_author и tutorial_count , где NULL tutorial_count указывает, что значение неизвестно.

пример

Попробуйте следующие примеры —

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tcount_tbl
   -> (
   -> tutorial_author varchar(40) NOT NULL,
   -> tutorial_count  INT
   -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO tcount_tbl
   -> (tutorial_author, tutorial_count) values ('mahran', 20);

mysql> INSERT INTO tcount_tbl
   -> (tutorial_author, tutorial_count) values ('mahnaz', NULL);

mysql> INSERT INTO tcount_tbl
   -> (tutorial_author, tutorial_count) values ('Jen', NULL);

mysql> INSERT INTO tcount_tbl
   -> (tutorial_author, tutorial_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahran      |       20       |
|     mahnaz      |      NULL      |
|      Jen        |      NULL      |
|     Gill        |       20       |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql>

Вы можете видеть, что = и ! = Не работают со значениями NULL следующим образом —

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL;
Empty set (0.01 sec)

Чтобы найти записи, в которых столбец tutorial_count равен или не равен NULL, запросы должны быть записаны, как показано в следующей программе.

mysql> SELECT * FROM tcount_tbl 
   -> WHERE tutorial_count IS NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahnaz      |      NULL      |
|      Jen        |      NULL      |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
   -> WHERE tutorial_count IS NOT NULL;
+-----------------+----------------+
| tutorial_author | tutorial_count |
+-----------------+----------------+
|     mahran      |       20       |
|     Gill        |       20       |
+-----------------+----------------+
2 rows in set (0.00 sec)

Обработка значений NULL в PHP-скрипте

Вы можете использовать условие if … else для подготовки запроса на основе значения NULL.

пример

В следующем примере берется tutorial_count извне, а затем сравнивается со значением, доступным в таблице.

<?php
   $dbhost = 'localhost:3036';
   $dbuser = 'root';
   $dbpass = 'rootpassword';
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);

   if(! $conn ) {
      die('Could not connect: ' . mysql_error());
   }

   if( isset($tutorial_count )) {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count = $tutorial_count';
   } else {
      $sql = 'SELECT tutorial_author, tutorial_count
         FROM  tcount_tbl
         WHERE tutorial_count IS $tutorial_count';
   }

   mysql_select_db('TUTORIALS');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Could not get data: ' . mysql_error());
   }

   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Author:{$row['tutorial_author']}  <br> ".
         "Count: {$row['tutorial_count']} <br> ".
         "--------------------------------<br>";
   } 
   echo "Fetched data successfully\n";
   mysql_close($conn);
?>

MySQL — регулярные выражения

Вы видели сопоставление с шаблоном MySQL с LIKE …% . MySQL поддерживает другой тип операции сопоставления с образцом на основе регулярных выражений и оператора REGEXP . Если вы знакомы с PHP или PERL, то вам очень просто понять, потому что это сопоставление аналогично сценариям регулярных выражений.

Ниже приведена таблица шаблонов, которую можно использовать вместе с оператором REGEXP .

Шаблон	Какой шаблон соответствует
^	Начало строки
$	Конец строки
,	Любой отдельный персонаж
[…]	Любой символ, указанный в квадратных скобках
[^ …]	Любой символ, не указанный в квадратных скобках
p1 | p2 | p3	Чередование; соответствует любому из паттернов p1, p2 или p3
*	Ноль или более экземпляров предыдущего элемента
+	Один или несколько экземпляров предыдущего элемента
{П}	n экземпляров предыдущего элемента
{Т, п}	от m до n экземпляров предыдущего элемента

Примеры

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

Предположим, у нас есть таблица с именем person_tbl, и в ней есть поле с именем name —

Запросить поиск всех имен, начинающихся с ‘st’ —

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

Запрос, чтобы найти все имена, оканчивающиеся на ‘ok’ —

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

Запрос, чтобы найти все имена, которые содержат «Мар» —

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

Запрос, чтобы найти все имена, начиная с гласной и заканчивая ‘ok’ —

mysql> SELECT FirstName FROM intque.person_tbl WHERE FirstName REGEXP '^[aeiou].*ok$';

MySQL — Транзакции

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

Практически вы объедините множество SQL-запросов в группу и выполните все их вместе как часть транзакции.

Свойства сделок

Транзакции имеют следующие четыре стандартных свойства, обычно обозначаемых аббревиатурой ACID —

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

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

• Изоляция — это позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.

• Долговечность — это гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

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

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

Изоляция — это позволяет транзакциям работать независимо друг от друга и быть прозрачными друг для друга.

Долговечность — это гарантирует, что результат или результат совершенной транзакции сохраняется в случае сбоя системы.

В MySQL транзакции начинаются с оператора BEGIN WORK и заканчиваются оператором COMMIT или ROLLBACK . Команды SQL между начальным и конечным операторами составляют основную часть транзакции.

COMMIT и ROLLBACK

Эти два ключевых слова Commit и Rollback в основном используются для транзакций MySQL.

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

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

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

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

Вы можете контролировать поведение транзакции, установив переменную сеанса AUTOCOMMIT . Если для AUTOCOMMIT задано значение 1 (по умолчанию), то каждый оператор SQL (внутри транзакции или нет) считается завершенной транзакцией и фиксируется по умолчанию после ее завершения.

Когда для AUTOCOMMIT установлено значение 0, с помощью команды SET AUTOCOMMIT = 0 последующая серия операторов действует как транзакция, и никакие действия не фиксируются, пока не будет выполнен явный оператор COMMIT.

Вы можете выполнить эти команды SQL в PHP с помощью функции mysql_query () .

Общий пример транзакции

Эта последовательность событий не зависит от используемого языка программирования. Логический путь может быть создан на любом языке, который вы используете для создания своего приложения.

Вы можете выполнить эти команды SQL в PHP с помощью функции mysql_query () .

• Начните транзакцию, введя команду SQL BEGIN WORK .

• Выполните одну или несколько команд SQL, таких как SELECT, INSERT, UPDATE или DELETE.

• Проверьте, нет ли ошибок, и все ли соответствует вашему требованию.

• Если есть какая-либо ошибка, введите команду ROLLBACK, в противном случае введите команду COMMIT.

Начните транзакцию, введя команду SQL BEGIN WORK .

Выполните одну или несколько команд SQL, таких как SELECT, INSERT, UPDATE или DELETE.

Проверьте, нет ли ошибок, и все ли соответствует вашему требованию.

Если есть какая-либо ошибка, введите команду ROLLBACK, в противном случае введите команду COMMIT.

Транзакционно-безопасные типы таблиц в MySQL

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

Поддержка таблиц InnoDB требует определенного параметра компиляции при компиляции MySQL из источника. Если ваша версия MySQL не поддерживает InnoDB, попросите интернет-провайдера создать версию MySQL с поддержкой типов таблиц InnoDB или загрузить и установить бинарный дистрибутив MySQL-Max для Windows или Linux / UNIX и работать с типом таблицы в среда разработки.

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

Например, следующий код создает таблицу InnoDB с именем tcount_tbl —

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table tcount_tbl
   -> (
   -> tutorial_author varchar(40) NOT NULL,
   -> tutorial_count  INT
   -> ) TYPE = InnoDB ;
Query OK, 0 rows affected (0.05 sec)

Для получения более подробной информации о InnoDB, вы можете нажать на следующую ссылку — InnoDB

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

MySQL — команда ALTER

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

Давайте начнем с создания таблицы с именем testalter_tbl .

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> create table testalter_tbl
   -> (
   -> i INT,
   -> c CHAR(1)
   -> );
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   i   | int(11) | YES  |     |   NULL  |       |
|   c   | char(1) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Удаление, добавление или изменение положения столбца

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

mysql> ALTER TABLE testalter_tbl  DROP i;

Предложение DROP не будет работать, если в таблице остается только один столбец.

Чтобы добавить столбец, используйте ADD и укажите определение столбца. Следующий оператор восстанавливает столбец i в testalter_tbl —

mysql> ALTER TABLE testalter_tbl ADD i INT;

После выполнения этого оператора testalter будет содержать те же два столбца, что и при первом создании таблицы, но не будет иметь такую ​​же структуру. Это связано с тем, что по умолчанию в конец таблицы добавляются новые столбцы. Так что, хотя я изначально был первым столбцом в mytbl, теперь это последний.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Чтобы указать, что вы хотите столбец в определенной позиции в таблице, либо используйте FIRST, чтобы сделать его первым столбцом, либо AFTER col_name, чтобы указать, что новый столбец должен быть помещен после col_name.

Попробуйте следующие операторы ALTER TABLE , используя SHOW COLUMNS после каждого, чтобы увидеть, какой эффект у каждого из них —

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

Спецификаторы FIRST и AFTER работают только с предложением ADD. Это означает, что если вы хотите изменить положение существующего столбца в таблице, вы должны сначала УДАЛИТЬ его, а затем ДОБАВИТЬ в новую позицию.

Изменение (изменение) определения столбца или имени

Чтобы изменить определение столбца, используйте предложение MODIFY или CHANGE вместе с командой ALTER.

Например, чтобы изменить столбец c с CHAR (1) на CHAR (10), вы можете использовать следующую команду:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

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

Попробуйте следующий пример —

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

Если вы теперь используете CHANGE для преобразования j из BIGINT обратно в INT без изменения имени столбца, оператор будет выглядеть так, как показано ниже —

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

Влияние ALTER TABLE на нулевые атрибуты и атрибуты значений по умолчанию. Когда вы изменяете или меняете столбец, вы также можете указать, может ли столбец содержать значения NULL и каково его значение по умолчанию. Фактически, если вы этого не сделаете, MySQL автоматически назначит значения для этих атрибутов.

Следующий блок кода является примером, где столбец NOT NULL будет иметь значение по умолчанию 100.

mysql> ALTER TABLE testalter_tbl 
   -> MODIFY j BIGINT NOT NULL DEFAULT 100;

Если вы не используете вышеупомянутую команду, тогда MySQL заполнит значения NULL во всех столбцах.

Изменение (изменение) значения по умолчанию для столбца

Вы можете изменить значение по умолчанию для любого столбца с помощью команды ALTER .

Попробуйте следующий пример.

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   1000  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

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

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Изменение (изменение) типа таблицы

Вы можете использовать тип таблицы, используя предложение TYPE вместе с командой ALTER. Попробуйте следующий пример, чтобы изменить testalter_tbl на тип таблицы MYISAM .

Чтобы узнать текущий тип таблицы, используйте оператор SHOW TABLE STATUS .

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Переименование (изменение) таблицы

Чтобы переименовать таблицу, используйте опцию RENAME инструкции ALTER TABLE .

Попробуйте следующий пример переименовать testalter_tbl в alter_tbl .

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

Вы можете использовать команду ALTER для создания и удаления команды INDEX для файла MySQL. Мы подробно обсудим эту команду в следующей главе.

MySQL — ИНДЕКСЫ

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

При создании индекса следует учитывать, какие все столбцы будут использоваться для выполнения запросов SQL и создания одного или нескольких индексов для этих столбцов.

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

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

Операторы INSERT и UPDATE занимают больше времени для таблиц, имеющих индексы, тогда как операторы SELECT становятся быстрыми для этих таблиц. Причина в том, что при выполнении вставки или обновления базе данных также необходимо вставлять или обновлять значения индекса.

Простой и уникальный индекс

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

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...);

Вы можете использовать один или несколько столбцов для создания индекса.

Например, мы можем создать индекс для tutorials_tbl, используя tutorial_author .

CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author)

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

Если вы хотите проиндексировать значения в столбце в порядке убывания, вы можете добавить зарезервированное слово DESC после имени столбца.

mysql> CREATE UNIQUE INDEX AUTHOR_INDEX ON tutorials_tbl (tutorial_author DESC)

Команда ALTER для добавления и удаления INDEX

Существует четыре типа операторов для добавления индексов в таблицу:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) — этот оператор добавляет PRIMARY KEY , что означает, что индексированные значения должны быть уникальными и не могут быть NULL.

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) — этот оператор создает индекс, для которого значения должны быть уникальными (за исключением значений NULL, которые могут появляться несколько раз).

• ALTER TABLE tbl_name ADD INDEX index_name (column_list) — это добавляет обычный индекс, в котором любое значение может появляться более одного раза.

• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) — это создает специальный индекс FULLTEXT, который используется для целей текстового поиска.

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) — этот оператор добавляет PRIMARY KEY , что означает, что индексированные значения должны быть уникальными и не могут быть NULL.

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) — этот оператор создает индекс, для которого значения должны быть уникальными (за исключением значений NULL, которые могут появляться несколько раз).

ALTER TABLE tbl_name ADD INDEX index_name (column_list) — это добавляет обычный индекс, в котором любое значение может появляться более одного раза.

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) — это создает специальный индекс FULLTEXT, который используется для целей текстового поиска.

Следующий блок кода является примером добавления индекса в существующую таблицу.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

Вы можете удалить любой ИНДЕКС, используя предложение DROP вместе с командой ALTER.

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

mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

Вы можете удалить любой ИНДЕКС, используя предложение DROP вместе с командой ALTER.

Команда ALTER для добавления и удаления первичного ключа

Вы также можете добавить первичный ключ таким же образом. Но убедитесь, что первичный ключ работает со столбцами, которые не равны NULL.

Следующий блок кода является примером добавления первичного ключа в существующую таблицу. Это сделает сначала столбец NOT NULL, а затем добавит его в качестве первичного ключа.

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

Вы можете использовать команду ALTER для удаления первичного ключа следующим образом:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Чтобы удалить индекс, который не является ПЕРВИЧНЫМ КЛЮЧОМ, необходимо указать имя индекса.

Отображение информации INDEX

Вы можете использовать команду SHOW INDEX для вывода списка всех индексов, связанных с таблицей. Вывод в вертикальном формате (заданный \ G) часто полезен с этим оператором, чтобы избежать переноса длинных строк —

Попробуйте следующий пример —

mysql> SHOW INDEX FROM table_name \G
........

MySQL — временные таблицы

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

Что такое временные таблицы?

Временные таблицы были добавлены в MySQL версии 3.23. Если вы используете более старую версию MySQL, чем 3.23, вы не можете использовать временные таблицы, но вы можете использовать таблицы кучи .

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

пример

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

mysql> CREATE TEMPORARY TABLE SalesSummary (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
|   cucumber   |   100.25    |     90.00      |         2        |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

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

Удаление временных таблиц

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

Следующая программа является примером удаления временной таблицы:

mysql> CREATE TEMPORARY TABLE SalesSummary (
   -> product_name VARCHAR(50) NOT NULL
   -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
   -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
   -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
   -> (product_name, total_sales, avg_unit_price, total_units_sold)
   -> VALUES
   -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
|   cucumber   |   100.25    |     90.00      |         2        |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'TUTORIALS.SalesSummary' doesn't exist

MySQL — таблицы клонов

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

Вы можете справиться с этой ситуацией, следуя инструкциям ниже:

• Используйте SHOW CREATE TABLE, чтобы получить инструкцию CREATE TABLE, которая определяет структуру исходной таблицы, индексы и все.

• Измените оператор, чтобы изменить имя таблицы на имя таблицы клонов, и выполните оператор. Таким образом, у вас будет точная таблица клонов.

• По желанию, если вам также необходимо скопировать содержимое таблицы, введите оператор INSERT INTO … SELECT.

Используйте SHOW CREATE TABLE, чтобы получить инструкцию CREATE TABLE, которая определяет структуру исходной таблицы, индексы и все.

Измените оператор, чтобы изменить имя таблицы на имя таблицы клонов, и выполните оператор. Таким образом, у вас будет точная таблица клонов.

По желанию, если вам также необходимо скопировать содержимое таблицы, введите оператор INSERT INTO … SELECT.

пример

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

Шаг 1 — Получить полную структуру таблицы.

mysql> SHOW CREATE TABLE tutorials_tbl \G;
*************************** 1. row ***************************
      Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
   `tutorial_id` int(11) NOT NULL auto_increment,
   `tutorial_title` varchar(100) NOT NULL default '',
   `tutorial_author` varchar(40) NOT NULL default '',
   `submission_date` date default NULL,
   PRIMARY KEY  (`tutorial_id`),
   UNIQUE KEY `AUTHOR_INDEX` (`tutorial_author`)
) TYPE = MyISAM
1 row in set (0.00 sec)

ERROR:
No query specified

Шаг 2 — Переименуйте эту таблицу и создайте другую таблицу.

mysql> CREATE TABLE clone_tbl (
   -> tutorial_id int(11) NOT NULL auto_increment,
   -> tutorial_title varchar(100) NOT NULL default '',
   -> tutorial_author varchar(40) NOT NULL default '',
   -> submission_date date default NULL,
   -> PRIMARY KEY  (tutorial_id),
   -> UNIQUE KEY AUTHOR_INDEX (tutorial_author)
-> ) TYPE = MyISAM;
Query OK, 0 rows affected (1.80 sec)

Шаг 3 — После выполнения шага 2 вы создадите таблицу клонов в своей базе данных. Если вы хотите скопировать данные из старой таблицы, вы можете сделать это с помощью оператора INSERT INTO … SELECT.

mysql> INSERT INTO clone_tbl (tutorial_id,
   -> tutorial_title,
   -> tutorial_author,
   -> submission_date)
   
   -> SELECT tutorial_id,tutorial_title,
   -> tutorial_author,submission_date
   -> FROM tutorials_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

Наконец, у вас будет точная таблица клонов, как вы и хотели.

MySQL — база данных

Получение и использование метаданных MySQL

Существует три типа информации, которую вы хотели бы получить от MySQL.

• Информация о результате запросов — включает количество записей, затронутых любым оператором SELECT, UPDATE или DELETE.

• Информация о таблицах и базах данных — включает в себя информацию, относящуюся к структуре таблиц и баз данных.

• Информация о сервере MySQL. Сюда входит состояние сервера базы данных, номер версии и т. Д.

Информация о результате запросов — включает количество записей, затронутых любым оператором SELECT, UPDATE или DELETE.

Информация о таблицах и базах данных — включает в себя информацию, относящуюся к структуре таблиц и баз данных.

Информация о сервере MySQL. Сюда входит состояние сервера базы данных, номер версии и т. Д.

Очень легко получить всю эту информацию в командной строке MySQL, но при использовании API-интерфейсов PERL или PHP нам необходимо явно вызывать различные API-интерфейсы для получения всей этой информации.

Получение количества строк, затронутых запросом

Давайте теперь посмотрим, как получить эту информацию.

Пример PERL

В сценариях DBI количество затронутых строк возвращается do () или командой execute () , в зависимости от того, как вы выполняете запрос.

# Method 1
# execute $query using do( )
my $count = $dbh->do ($query);
# report 0 rows if an error occurred
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

# Method 2
# execute query using prepare( ) plus execute( )
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d rows were affected\n", (defined ($count) ? $count : 0);

Пример PHP

В PHP вызовите функцию mysql_affered_rows (), чтобы узнать, сколько строк изменилось в запросе.

$result_id = mysql_query ($query, $conn_id);
# report 0 rows if the query failed
$count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
print ("$count rows were affected\n");

Листинг таблиц и баз данных

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

Помимо метода, показанного в следующем блоке кода, вы можете использовать запросы SHOW TABLES или SHOW DATABASES, чтобы получить список таблиц или баз данных либо в PHP, либо в PERL.

Пример PERL

# Get all the tables available in current database.
my @tables = $dbh->tables ( );

foreach $table (@tables ){
   print "Table Name $table\n";
}

Пример PHP

<?php
   $con = mysql_connect("localhost", "userid", "password");
   
   if (!$con) {
      die('Could not connect: ' . mysql_error());
   }
   $db_list = mysql_list_dbs($con);

   while ($db = mysql_fetch_object($db_list)) {
      echo $db->Database . "<br />";
   }
   mysql_close($con);
?>

Получение метаданных сервера

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

Sr.No.	Команда и описание
1	ВЫБЕРИТЕ ВЕРСИЮ () Строка версии сервера
2	ВЫБЕРИТЕ БАЗУ ДАННЫХ () Текущее имя базы данных (пусто, если нет)
3	ВЫБЕРИТЕ ПОЛЬЗОВАТЕЛЯ () Текущее имя пользователя
4	ПОКАЗАТЬ СТАТУС Индикаторы состояния сервера
5	ПОКАЗАТЬ ПЕРЕМЕННЫЕ Переменные конфигурации сервера

ВЫБЕРИТЕ ВЕРСИЮ ()

Строка версии сервера

ВЫБЕРИТЕ БАЗУ ДАННЫХ ()

Текущее имя базы данных (пусто, если нет)

ВЫБЕРИТЕ ПОЛЬЗОВАТЕЛЯ ()

Текущее имя пользователя

ПОКАЗАТЬ СТАТУС

Индикаторы состояния сервера

ПОКАЗАТЬ ПЕРЕМЕННЫЕ

Переменные конфигурации сервера

Использование последовательностей MySQL

Последовательность — это набор целых чисел 1, 2, 3, …, которые сгенерированы в определенном порядке. Последовательности часто используются в базах данных, поскольку многие приложения требуют, чтобы каждая строка в таблице содержала уникальное значение, а последовательности предоставляют простой способ их создания.

Эта глава описывает, как использовать последовательности в MySQL.

Использование столбца AUTO_INCREMENT

Простейший способ использования последовательностей в MySQL состоит в том, чтобы определить столбец как AUTO_INCREMENT и оставить все остальное на усмотрение MySQL.

пример

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

mysql> CREATE TABLE insect
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
   -> (NULL,'housefly','2001-09-10','kitchen'),
   -> (NULL,'millipede','2001-09-10','driveway'),
   -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id |    name     |    date    |   origin   |
+----+-------------+------------+------------+
|  1 |  housefly   | 2001-09-10 |   kitchen  |
|  2 |  millipede  | 2001-09-10 |  driveway  |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

Получить значения AUTO_INCREMENT

LAST_INSERT_ID () — это функция SQL, поэтому вы можете использовать ее из любого клиента, который понимает, как создавать операторы SQL. В противном случае сценарии PERL и PHP предоставляют эксклюзивные функции для извлечения автоматически увеличенного значения последней записи.

Пример PERL

Используйте атрибут mysql_insertid для получения значения AUTO_INCREMENT, сгенерированного запросом. Этот атрибут доступен через дескриптор базы данных или дескриптор оператора, в зависимости от того, как вы выполняете запрос.

Следующий пример ссылается на него через дескриптор базы данных.

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};

Пример PHP

После выдачи запроса, который генерирует значение AUTO_INCREMENT, получите это значение, вызвав команду mysql_insert_id () .

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);

Изменение нумерации существующей последовательности

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

Если вы решите, что повторное упорядочение столбца AUTO_INCREMENT является неизбежным, способ сделать это — удалить столбец из таблицы, а затем добавить его снова.

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

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
   -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
   -> ADD PRIMARY KEY (id);

Начало последовательности по определенному значению

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

Следующая программа — пример, который показывает, как MySQL начнет последовательность с 100.

mysql> CREATE TABLE insect
   -> (
   -> id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100,
   -> PRIMARY KEY (id),
   -> name VARCHAR(30) NOT NULL, # type of insect
   -> date DATE NOT NULL, # date collected
   -> origin VARCHAR(30) NOT NULL # where collected
);

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

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

MySQL — обработка дубликатов

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

Предотвращение появления дубликатов в таблице

Вы можете использовать PRIMARY KEY или UNIQUE Index для таблицы с соответствующими полями, чтобы остановить повторяющиеся записи.

Давайте рассмотрим пример. В следующей таблице нет такого индекса или первичного ключа, поэтому допускается дублирование записей для first_name и last_name .

CREATE TABLE person_tbl (
   first_name CHAR(20),
   last_name CHAR(20),
   sex CHAR(10)
);

Чтобы предотвратить создание в этой таблице нескольких записей с одинаковыми значениями имени и фамилии, добавьте PRIMARY KEY к ее определению. Когда вы делаете это, также необходимо объявить индексированные столбцы как NOT NULL , потому что PRIMARY KEY не допускает значения NULL —

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

Наличие уникального индекса в таблице обычно приводит к возникновению ошибки, если вы вставляете в таблицу запись, которая дублирует существующую запись в столбце или столбцах, определяющих индекс.

Используйте команду INSERT IGNORE, а не команду INSERT . Если запись не дублирует существующую запись, MySQL вставляет ее как обычно. Если запись является дубликатом, то ключевое слово IGNORE говорит MySQL, что нужно отбросить ее без предупреждения.

Следующий пример не выдает ошибку и в то же время не будет вставлять дублирующиеся записи.

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

Используйте команду REPLACE, а не команду INSERT. Если запись новая, она вставляется так же, как и INSERT. Если это дубликат, новая запись заменяет старую.

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 1 row affected (0.00 sec)

mysql> REPLACE INTO person_tbl (last_name, first_name)
   -> VALUES( 'Ajay', 'Kumar');
Query OK, 2 rows affected (0.00 sec)

Команды INSERT IGNORE и REPLACE следует выбирать в соответствии с поведением обработки дубликатов, которое вы хотите применить. Команда INSERT IGNORE сохраняет первый набор дублированных записей и отбрасывает остальные. Команда REPLACE сохраняет последний набор дубликатов и удаляет все предыдущие.

Другой способ обеспечить уникальность — добавить в таблицу индекс UNIQUE, а не PRIMARY KEY.

CREATE TABLE person_tbl (
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

Подсчет и идентификация дубликатов

Ниже приведен запрос на подсчет повторяющихся записей с именем first_name и last_name в таблице.

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
   -> FROM person_tbl
   -> GROUP BY last_name, first_name
   -> HAVING repetitions > 1;

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

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

• Перечислите эти столбцы в списке выбора столбцов вместе с COUNT (*) .

• Перечислите также столбцы в предложении GROUP BY .

• Добавьте предложение HAVING , которое исключает уникальные значения, так как число групп должно быть больше единицы.

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

Перечислите эти столбцы в списке выбора столбцов вместе с COUNT (*) .

Перечислите также столбцы в предложении GROUP BY .

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

Исключение дубликатов из результата запроса

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

mysql> SELECT DISTINCT last_name, first_name
   -> FROM person_tbl
   -> ORDER BY last_name;

Альтернативой команде DISTINCT является добавление предложения GROUP BY с именами столбцов, которые вы выбираете. Это приводит к удалению дубликатов и выбору только уникальных комбинаций значений в указанных столбцах.

mysql> SELECT last_name, first_name
   -> FROM person_tbl
   -> GROUP BY (last_name, first_name);

Удаление дубликатов с помощью замены таблицы

Если у вас есть дубликаты записей в таблице, и вы хотите удалить все дубликаты записей из этой таблицы, выполните следующую процедуру.

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
   -> FROM person_tbl;
   -> GROUP BY (last_name, first_name);

mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

Простой способ удалить дубликаты записей из таблицы — добавить в эту таблицу ИНДЕКС или ПЕРВИЧНЫЙ КЛЮЧ. Даже если эта таблица уже доступна, вы можете использовать эту технику, чтобы удалить дубликаты записей, и вы будете в безопасности в будущем.

mysql> ALTER IGNORE TABLE person_tbl
   -> ADD PRIMARY KEY (last_name, first_name);

MySQL — и SQL-инъекция

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

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

Никогда не доверяйте данным, предоставленным пользователем, обрабатывайте эти данные только после проверки; как правило, это делается путем сопоставления с образцом. В следующем примере имя пользователя ограничено буквенно-цифровыми символами и подчеркиванием и длиной от 8 до 20 символов — измените эти правила по мере необходимости.

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)) {
   $result = mysql_query("SELECT * FROM users WHERE username = $matches[0]");
} else  {
   echo "username not accepted";
}

Чтобы продемонстрировать эту проблему, рассмотрим следующую выдержку.

// supposed input
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name = '{$name}'");

Предполагается, что вызов функции извлекает запись из таблицы пользователей, где столбец имени совпадает с именем, указанным пользователем. При нормальных обстоятельствах $ name будет содержать только буквенно-цифровые символы и, возможно, пробелы. Но здесь, добавив совершенно новый запрос к $ name , обращение к базе данных превращается в катастрофу. Внедренный запрос DELETE удаляет все записи у пользователей.

К счастью, если вы используете MySQL, функция mysql_query () не разрешает составлять запросы или выполнять несколько запросов за один вызов функции. Если вы попытаетесь сложить запросы, вызов не удастся.

Однако другие расширения баз данных PHP, такие как SQLite и PostgreSQL , успешно выполняют суммированные запросы, выполняя все запросы, представленные в одной строке, и создают серьезную проблему безопасности.

Предотвращение SQL-инъекций

Вы можете грамотно обрабатывать все escape-символы на языках сценариев, таких как PERL и PHP. Расширение MySQL для PHP предоставляет функцию mysql_real_escape_string () для экранирования входных символов, которые являются специальными для MySQL.

if (get_magic_quotes_gpc()) {
   $name = stripslashes($name);
}

$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name = '{$name}'");

Как затруднительное положение

Чтобы решить проблему LIKE, пользовательский механизм экранирования должен преобразовывать предоставленные пользователем символы% и _ в литералы. Используйте addcslashes () , функцию, которая позволяет вам указать диапазон символов для экранирования.

$sub = addcslashes(mysql_real_escape_string("%something_"), "%_");
// $sub == \%something\_
mysql_query("SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

MySQL — экспорт базы данных

Самый простой способ экспорта табличных данных в текстовый файл — использование оператора SELECT … INTO OUTFILE, который экспортирует результат запроса непосредственно в файл на хосте сервера.

Экспорт данных с помощью оператора SELECT … INTO OUTFILE

Синтаксис этого оператора объединяет обычную команду SELECT с именем файла INTO OUTFILE в конце. Формат вывода по умолчанию такой же, как и для команды LOAD DATA. Итак, следующий оператор экспортирует таблицу tutorials_tbl в /tmp/tutorials.txt в виде файла с разделителями табуляцией и строкой, заканчивающейся строкой .

mysql> SELECT * FROM tutorials_tbl 
   -> INTO OUTFILE '/tmp/tutorials.txt';

Вы можете изменить формат вывода, используя различные параметры, чтобы указать, как заключать в кавычки и разделять столбцы и записи. Чтобы экспортировать таблицу tutorial_tbl в формате CSV со строками, оканчивающимися CRLF, используйте следующий код.

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
   -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   -> LINES TERMINATED BY '\r\n';

SELECT … INTO OUTFILE имеет следующие свойства —

• Выходной файл создается непосредственно сервером MySQL, поэтому имя файла должно указывать, куда вы хотите записать файл на хост сервера. ЛОКАЛЬНОЙ версии оператора, аналогичной ЛОКАЛЬНОЙ версии LOAD DATA, не существует .

• У вас должна быть привилегия MySQL FILE для выполнения инструкции SELECT … INTO .

• Выходной файл не должен уже существовать. Это препятствует тому, чтобы MySQL забивал файлы, которые могут быть важны.

• У вас должна быть учетная запись на хосте сервера или какой-то другой способ получить файл с этого хоста. В противном случае команда SELECT … INTO OUTFILE , скорее всего, не будет иметь для вас никакого значения.

• В UNIX файл создается для чтения всем миром и принадлежит серверу MySQL. Это означает, что, хотя вы сможете прочитать файл, вы не сможете удалить его.

Выходной файл создается непосредственно сервером MySQL, поэтому имя файла должно указывать, куда вы хотите записать файл на хост сервера. ЛОКАЛЬНОЙ версии оператора, аналогичной ЛОКАЛЬНОЙ версии LOAD DATA, не существует .

У вас должна быть привилегия MySQL FILE для выполнения инструкции SELECT … INTO .

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

У вас должна быть учетная запись на хосте сервера или какой-то другой способ получить файл с этого хоста. В противном случае команда SELECT … INTO OUTFILE , скорее всего, не будет иметь для вас никакого значения.

В UNIX файл создается для чтения всем миром и принадлежит серверу MySQL. Это означает, что, хотя вы сможете прочитать файл, вы не сможете удалить его.

Экспорт таблиц в виде необработанных данных

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

Чтобы вывести таблицу в виде файла данных, вы должны указать опцию —tab, которая указывает каталог, в который вы хотите, чтобы сервер MySQL записал файл.

Например, чтобы выгрузить таблицу tutorials_tbl из базы данных TUTORIALS в файл в каталоге / tmp , используйте команду, как показано ниже.

$ mysqldump -u root -p --no-create-info \
   --tab=/tmp tutorials tutorials_tbl
password ******

Экспорт содержимого таблицы или определений в формате SQL

Чтобы экспортировать таблицу в формате SQL в файл, используйте команду, показанную ниже.

$ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

Это создаст файл с содержимым, как показано ниже.

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
   tutorial_id int(11) NOT NULL auto_increment,
   tutorial_title varchar(100) NOT NULL default '',
   tutorial_author varchar(40) NOT NULL default '',
   submission_date date default NULL,
   PRIMARY KEY  (tutorial_id),
   UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE = MyISAM;

--
-- Dumping data for table `tutorials_tbl`
--

INSERT INTO tutorials_tbl 
   VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl 
   VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

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

$ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

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

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

Опция —all-database доступна в версии MySQL 3.23.12. Этот метод может быть использован для реализации стратегии резервного копирования базы данных.

Копирование таблиц или баз данных на другой хост

Если вы хотите скопировать таблицы или базы данных с одного сервера MySQL на другой, используйте mysqldump с именем базы данных и именем таблицы.

Выполните следующую команду на исходном хосте. Это сбросит всю базу данных в файл dump.txt .

$ mysqldump -u root -p database_name table_name > dump.txt
password *****

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

Теперь загрузите файл ftp dump.txt на другом хосте и используйте следующую команду. Перед выполнением этой команды убедитесь, что вы создали имя_базы_данных на конечном сервере.

$ mysql -u root -p database_name < dump.txt
password *****

Другой способ сделать это без использования промежуточного файла — это отправить выходные данные mysqldump напрямую по сети на удаленный сервер MySQL. Если вы можете подключиться к обоим серверам с хоста, на котором находится исходная база данных, используйте следующую команду (убедитесь, что у вас есть доступ на обоих серверах).

$ mysqldump -u root -p database_name \
   | mysql -h other-host.com database_name

В mysqldump половина команды подключается к локальному серверу и записывает вывод дампа в канал. Оставшаяся половина команды подключается к удаленному серверу MySQL на other-host.com. Он читает канал для ввода и отправляет каждое утверждение на сервер other-host.com.

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

В MySQL есть два простых способа загрузки данных в базу данных MySQL из ранее сохраненного файла.

Импорт данных с помощью LOAD DATA

MySQL предоставляет оператор LOAD DATA, который действует как загрузчик больших объемов данных. Вот пример оператора, который читает файл dump.txt из вашего текущего каталога и загружает его в таблицу mytbl в текущей базе данных.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

• Если ключевое слово LOCAL отсутствует, MySQL ищет файл данных на хосте сервера, используя поиск по абсолютному пути , который полностью определяет местоположение файла, начиная с корня файловой системы. MySQL читает файл из указанного местоположения.

• По умолчанию LOAD DATA предполагает, что файлы данных содержат строки, которые заканчиваются переводами строк (новыми строками), а значения данных в строке разделяются вкладками.

• Чтобы явно указать формат файла, используйте предложение FIELDS для описания характеристик полей в строке и предложение LINES для указания конца строки. Следующая инструкция LOAD DATA указывает, что файл данных содержит значения, разделенные двоеточиями и строками, оканчивающимися возвратом каретки и символом новой строки.

Если ключевое слово LOCAL отсутствует, MySQL ищет файл данных на хосте сервера, используя поиск по абсолютному пути , который полностью определяет местоположение файла, начиная с корня файловой системы. MySQL читает файл из указанного местоположения.

По умолчанию LOAD DATA предполагает, что файлы данных содержат строки, которые заканчиваются переводами строк (новыми строками), а значения данных в строке разделяются вкладками.

Чтобы явно указать формат файла, используйте предложение FIELDS для описания характеристик полей в строке и предложение LINES для указания конца строки. Следующая инструкция LOAD DATA указывает, что файл данных содержит значения, разделенные двоеточиями и строками, оканчивающимися возвратом каретки и символом новой строки.

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
   -> FIELDS TERMINATED BY ':'
   -> LINES TERMINATED BY '\r\n';

• Команда LOAD DATA предполагает, что столбцы в файле данных имеют тот же порядок, что и столбцы в таблице. Если это не так, вы можете указать список, чтобы указать, в какие столбцы таблицы должны быть загружены столбцы файла данных. Предположим, что в вашей таблице есть столбцы a, b и c, но последовательные столбцы в файле данных соответствуют столбцам b, c и a.

Команда LOAD DATA предполагает, что столбцы в файле данных имеют тот же порядок, что и столбцы в таблице. Если это не так, вы можете указать список, чтобы указать, в какие столбцы таблицы должны быть загружены столбцы файла данных. Предположим, что в вашей таблице есть столбцы a, b и c, но последовательные столбцы в файле данных соответствуют столбцам b, c и a.

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

mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
   -> INTO TABLE mytbl (b, c, a);

Импортирование данных с помощью mysqlimport

MySQL также включает в себя служебную программу mysqlimport, которая действует как обертка вокруг LOAD DATA, так что вы можете загружать входные файлы непосредственно из командной строки.

Чтобы загрузить данные из файла dump.txt в mytbl , используйте следующую команду в командной строке UNIX.

$ mysqlimport -u root -p --local database_name dump.txt
password *****

Если вы используете mysqlimport , параметры командной строки предоставляют спецификаторы формата. Команды mysqlimport, которые соответствуют двум предыдущим операторам LOAD DATA, выглядят так, как показано в следующем блоке кода.

$ mysqlimport -u root -p --local --fields-terminated-by = ":" \
   --lines-terminated-by = "\r\n"  database_name dump.txt
password *****

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

Оператор mysqlimport использует опцию —columns для указания порядка столбцов —

$ mysqlimport -u root -p --local --columns=b,c,a \
   database_name dump.txt
password *****

Обработка цитат и специальных символов

Предложение FIELDS может указывать другие параметры формата, кроме TERMINATED BY . По умолчанию LOAD DATA предполагает, что значения не заключены в кавычки, и интерпретирует обратную косую черту (\) как escape-символ для специальных символов. Чтобы явно указать символ кавычки, используйте команду ENCLOSED BY . MySQL удалит этот символ с концов значений данных во время обработки ввода. Чтобы изменить управляющий символ по умолчанию, используйте ESCAPED BY .

Когда вы указываете ENCLOSED BY, чтобы указать, что символы кавычек должны быть удалены из значений данных, можно включить символ кавычки буквально в значения данных, удвоив его или поставив перед ним escape-символ.

Например, если кавычки и escape-символы равны «и \», входное значение «a», «b \» c » будет интерпретироваться как « b »c .

Для mysqlimport соответствующие параметры командной строки для указания кавычек и экранирующих значений — —fields-enclosed-by и —fields-escaped-by .