СУБД MySQL

Синтаксис

Синтаксис SQL читается подобно естественным языкам. Утверждения начинаются с глагола, описывающего действие, далее следует субъект и предикат.

Субъект - это мысль о каком-то предмете, понятие о предмете суждения (логическое подлежащее).

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

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

					
    -- Пример выборки ФИО и возраста из таблицы покупателей
    SELECT fio, age FROM customer; 
					
				

    -- Результат выборки
    +-------------+----+
    | Иванов И.И. | 18 |
    | Петров П.П. | 26 |
    | Сидоров С.С.| 35 |
    | Ткачёв Т.Т. | 51 |
    +-------------+----+
				

					
    -- Добавили предикат
    SELECT fio, age FROM customer WHERE age < 30; 
					
				

    +-------------+----+
    | Иванов И.И. | 18 |
    | Петров П.П. | 26 |
    +-------------+----+
				

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

Каждое "предложение" SQL - это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе.

Операторы

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

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

Операторы SQL делятся на

					
    -- Пример одной из самых частых "команд"
    SELECT name FROM personal; 
					
				

					
    -- Удаление данных из таблицы
    DELETE FROM customer WHERE id='Ткачёв Т.Т.'; 
					
				

Зарезервированные слова

Полный список зарезервированных слов для MySql 5.7

ЗАПРЕЩЕНО использовать в качестве имён столбцов и таблиц зарезервированные слова

К зарезервированным словам относятся слова, использующиеся в качестве названия функций и типов данных. Некоторые слова разрешены в качестве имён для столбов и таблиц, но запрещены в ANSI SQL, как правило это слова естественного языка, уже пользующиеся многими потребителями, т.е. запретить их полностью будет болезненно для многих систем, эти слова: ACTION, BIT, DATE, ENUM, NO, TEXT, TIME и TIMESTAMP.

Операции

					
    SELECT name FROM customer WHERE age>18  AND age<35; 
					
				

    -- Результат выборки
    +-------------+----+
    | Петров П.П. | 26 |
    +-------------+----+
				

Типы данных

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

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

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

Константы

Константами, так же называемые литералами, явно записываются величины. Выделяется три типа констант: строки, числа и двоичные значения. Строка - это одна или несколько символов в одинарных кавычках. Например:

    '606345 ул.Геологов'
    'Newman'
    'Привет мир!!'
				

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

    'Kenny''s chicken'
				

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

    -1
    3.142
    6.0221415Е23
				

Двоичные константы записываются как пары шестнадцатиричных цифр (0-9A-F) в одинарных кавычках с лидирующим символом х. Примеры:

    x'01'
    x'0fff'
				

Пример

В МySQL существует два типа таблиц - MyISAM и InnoDB. Отличаются они следующим.
MyISAM

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

InnoDB

• поддержка транзакций;
• построчная блокировка. UPDATE не блокирует всю таблицу;
• отлично ведет себя при смешанной нагрузке (insert|select|update|delete).

Начиная с версии 5.5 InnoDB стал основным хранилищем по умолчанию

CREATE TABLE

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

					
    CREATE TABLE table_name {
        column1 datatype,
        column2 datatype,
        column3 datatype,
    ....
    } 
					
				

					
    -- Создадим таблицу Customer
    CREATE TABLE customer {
        id int AUTO_INCREMENT,
        fio varchar(50),
        age int
    } 
					
				

INSERT INTO

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

					
    INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
    
    --  Без указания столбцов
    INSERT INTO table_name VALUES (value1, value2, value3, ...);
					
				

					
    INSERT INTO customer (fio, age) VALUES ('Иванов И.И.', 18);
    INSERT INTO customer (fio, age) VALUES ('Петров П.П.', 26);
    INSERT INTO customer (fio, age) VALUES ('Сидоров С.С', 35);
    INSERT INTO customer (fio, age) VALUES ('Ткачёв Т.Т.', 51);
					
				

SELECT

Оператор SELECT используются для выбора данных из базы данных.

					
    SELECT column1, column2, ... FROM table_name;
					
				

    SELECT fio FROM customer;
	
    -- Результат выборки
    +--------------+
    | Иванов И.И.  |
    | Петров П.П.  |
    | Сидоров С.С. |
    | Ткачёв Т.Т.  |
    +--------------+
				

Если вы хотите, чтобы выбрать все поля, доступные в таблице, используйте символ * вместо перечисления имён полей

					
    SELECT * FROM customer;
					
				

UPDATE

Оператор UPDATE используется для изменения существующих записей в таблице.

					
    UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
					
				

Обратите внимание на пункт WHERE в операторе UPDATE. Предложение WHERE определяет, какая запись(или записи), должна быть обновлена. Если опустить предложение WHERE, все записи в таблице обновяться!

					
    UPDATE customer SET fio = 'Пупкин Г.В.', age = 90 WHERE id = 3;
					
				

DELETE

Оператор DELETE используется для удаления существующих записей в таблице.

					
    DELETE FROM table_name WHERE condition;
					
				

Предложение WHERE определяет , какая запись(или записи), должна быть удаленыа. Если опустить предложение WHERE, все записи в таблице будут удалены!

					
    DELETE FROM customer WHERE age > 50;
					
				

DROP TABLE

Оператор DROP TABLE используется для удаления существующей таблицы в базе данных.

					
    DROP TABLE table_name;
					
				

Удаление таблицы приведёт к полной потере информации, хранящейся в таблице!

Запуск

Для запуска phpMyAdmin можно воспользоваться меню OpenServer

Далее для входа нажимаем кнопку Вперёд

Подключение/отключение

Этот простой пример показывает, как соединиться с базой данных.

					
	$host = "localhost";
	$login = "root";
	$passwd = "";
	$db = "my_database";

	// Создание соединения
	$conn = mysqli_connect($host, $login, $password, $db);

	// Проверка на установление соединения
	if (!$conn) {
		die("Connection failed: " . mysqli_connect_error());
	}
	echo "Connected successfully";
	
	//Закрытие соединения
	mysqli_close($сonn);
					
				

Подключение к БД →

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

mysqli_connect
mysqli_close
mysqli_connect_errno
mysqli_connect_error

Процедурный и объектно-ориентированный интерфейс

Расширение mysqli предоставляет двойной интерфейс программисту. Поддерживаются как процедурная, так и объектно-ориентированная парадигмы программирования.

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

В дополнение к процедурному пользователи могут использовать объектно-ориентированный интерфейс. Документация заточена именно под объектный интерфейс.

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

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

Отправка запроса к MySQL из PHP сводится к простому вызову функции mysqli_query(), которая принимает два параметра: объект подключения и строку запроса на языке SQL.

					
		....
		
	$query = "SELECT * FROM city";
	$result = mysqli_query($conn, $query);
	
	var_dump($result); // возвращает FALSE в случае неудачи
	
		....
					
				

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

В отличие от командной строки MySQL, здесь в конце запроса точка с запятой не нужна, поскольку для завершения запроса используется функция mysqli_query(), которая не может использоваться для запроса, состоящего из последовательно отправляемях частей. Поэтому MySQL знает, что запрос завершен, и не ищет точки с запятой.

Функция mysqli_query() возвращает объект, который содержит результат запроса. В случае неудачи данный объект содержит значение false.

Можно подумать что содержимое переменной $result будет таким же, как тот результат, который возвращался запросом, вводимым в командной строке, с горизонтальныйми и вертикальными линиями, шапкой и т.д. Но в PHP функция mysqli_query() возвращает не такой результат. В случае успешного выполнения запроса переменная будет содержать ресурс(объект), позволяющий извлечь результаты этого запроса.

mysqli_query
mysqli_errno
mysqli_error

Извлечение результата

После возвращения объекта $result его можно использовать для поэлементного извлечения нужных вам данных с помощью имеющейся в PHP функцией mysqli_fetch_assoc().

					
// запрос к БД
if($result = mysqli_query($conn, $query)) 
	if( mysqli_num_rows($result) > 0 ){
		
		printf("<table>");
		// результат в виде ассоциативного массива
		while($row = mysqli_fetch_assoc($result))
			printf("<tr><td>%d</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $row["ID"], $row["Name"], $row["CountryCode"], $row["District"], $row["Info"]);
		
		printf("</table>");
	}
					
				

Извлечение результата функцией mysqli_fetch_assoc() →

Функция mysqli_fetch_assoc() возвращает ассоциативный массив, соответствующий результирующей выборке или NULL, если других рядов не существует.
Имена полей, возвращаемые этой функцией являются регистро-зависимыми.
Эта функция устанавливает NULL-поля в значение NULL PHP.

Существует похожая на mysqli_fetch_assoc() функция. Это функция mysqli_fetch_row(), которая выбирает одну строку данных из результирующего набора и возвращает ее в виде массива, в котором индексы элементов соответствуют номерам столбцов (начиная с 0). Каждый последующий вызов функции будет возвращать массив с данными следующей строки набора или NULL, если строки закончились.

					
$query = "SELECT * FROM city ORDER BY ID DESC LIMIT 100"; // интересный запрос для вывода последних 100 записей

// запрос к БД
if($result = mysqli_query($conn, $query)) 
	if( mysqli_num_rows($result) > 0 ){
		
		printf("<table>");
		
		while($row = mysqli_fetch_row($result))
			printf("<tr><td>%d</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>", $row[0], $row[1], $row[2], $row[3], $row[4]);
		
		printf("</table>");
					
				

Извлечение результата функцией mysqli_fetch_row() →

mysqli_fetch_assoc
mysqli_num_rows
mysqli_fetch_row