Примеры SQL-запросов в MariaDB (MySQL)
В инструкции мы рассмотрим пример запросов MySQL/MariaDB, которые будут интересны новичкам или опытным пользователям в качестве шпаргалки.
Все запросы, за исключением выборки, несут потенциальную опасность для данных, которые хранятся в базе. Перед началом работы с рабочей базой, сделайте резервную копию.
Простые примеры использования SELECT
> SELECT <fields1> FROM <table> [JOIN <table2>] [ WHERE <conditions> ORDER BY <fields2> LIMIT <count> ]
* где fields1 — поля для выборки через запятую, также можно указать все поля знаком *; table — имя таблицы, из которой вытаскиваем данные; conditions — условия выборки; fields2 — поле или поля через запятую, по которым выполнить сортировку; count — количество строк для выгрузки.
* запрос в квадратных скобках не является обязательным для выборки данных.
1. Обычная выборка данных
> SELECT * FROM users
* в данном примере мы получаем список всех записей из таблицы users.
2. Выборка данных с объединением двух таблиц (JOIN)
SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id
* в данном примере идет выборка данных с объединением таблиц users и users_rights. Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.
3. Выборка с интервалом по времени и/или дате
Варианты запросов и примеров могут быть самые разные. Мы рассмотрим те, с которыми чаще всего приходилось иметь дело автору.
а) известна точка начала и определенный временной интервал:
> SELECT * FROM users WHERE date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
* будут выбраны данные за последний час (поле date).
б) известны дата начала и дата окончания:
> SELECT * FROM users WHERE date >= '2017-10-25' AND date <= '2017-11-25'
* выбираем данные в промежутке между 25.10.2017 и 25.11.2017.
в) известны даты начала и окончания + время:
> SELECT * FROM users WHERE DATE(date) BETWEEN '2018-03-25 00:15:00' AND '2018-04-25 15:33:09';
* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд.
г) вытаскиваем данные за определенные месяц и год:
> SELECT * FROM study WHERE MONTH(date) = 4 AND YEAR(date) = 2018
* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.
д) текущая дата минут год:
> SELECT * FROM study WHERE date < (CURDATE() — INTERVAL 1 YEAR)
* мы получим данные, которые имеют в колонке date дату, старше одного года.
4. Выборка максимального, минимального и среднего значения
> SELECT max(area), min(area), avg(area) FROM country
* max — максимальное значение; min — минимальное; avg — среднее.
5. Использование длины строки
> SELECT * FROM users WHERE CHAR_LENGTH(name) = 5;
* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.
6. Использование лимитов (LIMIT)
Применяется для ограничения количества выводимых результатов. Синтаксис:
<основной запрос> LIMIT [<число2>,] <число1>
* где число1 — сколько результатов вернуть; число2 — сколько результатов пропустить, необязательный параметр — если его не писать, то отсчет начнется с первой строки.
а) извлечь максимум 15 строк:
> SELECT * FROM users LIMIT 15;
б) выбрать строки с 16 по 25 (запрос со смещением):
> SELECT * FROM users LIMIT 15, 10;
* 15 строк пропускаем, 10 извлекаем.
Примеры более сложных запросов или используемых редко
1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)
> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING 'utf8') SEPARATOR ', ') as ids FROM users
* из таблицы users извлекаются данные по полю id, все они помещаются в одну строку, значения разделяются запятыми.
2. Группировка данных по двум и более полям
> SELECT * FROM users GROUP BY CONCAT(title, '::', birth)
* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.
3. Объединение результатов из двух и более таблиц (UNION)
а) простой вариант использования UNION:
> (SELECT id, fio, address, 'Пользователи' as type FROM users)
UNION
(SELECT id, fio, address, 'Покупатели' as type FROM customers)
* в данном примере идет выборка данных из таблиц users и customers.
б) если нам нужно использовать WHERE после UNION, запрос будет сложнее:
> SELECT * FROM (
(SELECT id, fio, address, 'Пользователи' as type FROM users)
UNION
(SELECT id, fio, address, 'Покупатели' as type FROM customers)
) as U
WHERE U.address LIKE "Садовая%"
4. Выборка средних значений, сгруппированных за каждый час
SELECT avg(temperature), DATE_FORMAT(datetimeupdate, '%Y-%m-%d %H') as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, '%Y-%m-%d %H')
* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).
5. Использование операторов IF и CASE
Данные операторы позволяют определять исход запроса исходя из условия.
а) выбрать пол мужской или женский:
SELECT IF(sex = 'm', 'мужчина', 'женщина') as sex FROM people
* в данном примере мы возвращаем слово «мужчина», если поле sex равно 'm', иначе — «женщина».
б) заменяем идентификатор времени года более понятным человеку значением:
SELECT CASE season_id WHEN 1 THEN 'зима' WHEN 2 THEN 'весна' WHEN 3 THEN 'лето' WHEN 4 THEN 'осень' ELSE 'неправильный идентификатор времени года' END as season FROM ` seasons
* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.
Вставка (INSERT)
> INSERT INTO <table> (<fields>) VALUES (<values>)
> INSERT INTO <table> VALUES (<values>)
* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.
1. Вставка нескольких строк одним запросом:
> INSERT INTO cities (`name`, `country`) VALUES ('Москва', 'Россия'), ('Париж', 'Франция'), ('Фунафути' ,'Тувалу');
* в данном примере мы одним SQL-запросом добавим 3 записи.
2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):
Синтаксис при копировании строк из одной таблицы в другую выглядит так:
> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;
* где table1 — куда копируем; table2 — откуда копируем.
а) скопировать все без разбора:
> INSERT INTO cities-new SELECT * FROM cities;
* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.
б) скопировать определенные столбцы строк с условием:
> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE 'М%';
* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.
в) копирование с обновлением повторяющихся ключей.
Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry 'xxx' for key 'PRIMARY'». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:
> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);
* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.
Обновление (UPDATE)
* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).
Обновление с использованием замены (REPLACE):
UPDATE cities SET name = REPLACE(name, 'Масква', 'Москва');
UPDATE cities SET name = REPLACE(name, 'Масква', 'Москва') WHERE country = 'Россия';
UPDATE cities SET name = REPLACE(name, 'Ма', 'Мо') WHERE name = 'Масква';
Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:
SELECT REPLACE(name, 'Ма', 'Мо') FROM cities WHERE name = 'Масква';
Удаление (DELETE)
> DELETE FROM <table> WHERE <conditions>
* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).
Более сложный вариант — удаление данных с объединением таблиц. Запрос будет такого вида:
> DELETE u FROM users u JOIN users_rights r ON r.user_id=u.id WHERE r.admin='1'
* в данном примере мы удалим записи только из таблицы users (u), которые при объединении с таблицей users_rights будут соответствовать условию r.admin='1'.
Создание таблицы
> CREATE TABLE <table> (<field1> <options1>, <field2> <options2>) <table options>
> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8).
Использование запросов в PHP
Подключаемся к базе данных:
mysql_connect ('localhost', 'login', 'password') or die ("MySQL connect error");
mysql_select_db ('db_name');
mysql_query("SET NAMES 'utf8'");
* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.
Также можно создать постоянное подключение:
mysql_pconnect ('localhost', 'login', 'password') or die ("MySQL connect error");
* однако есть вероятность достигнуть максимально разрешенного лимита хостинга. Данным способом стоит пользоваться на собственных серверах, где мы сами можем контролировать ситуацию.
* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).
Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():
$result = mysql_query("SELECT * FROM users");
while ($mass = mysql_fetch_array($result)) <
echo $mass[name] . '<br>';
>
* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.
Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.
Экранирование
При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \
> SELECT * FROM producrions WHERE kpd = '100\%'
* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.
Использование переменных
Пременные задаются с помощью знака собаки, например:
> SET @number = 101;
* мы создали переменную number со значением 101.
Теперь можно применить переменную в запросе, например:
> INSERT INTO users (`user_number`, `user_name`) VALUES (@number, CONCAT('Пользователь ', @number));
Примеры простейших SQL-запросов
Оператор SELECT (выбрать) языка SQL является самым важным и самым часто используемым оператором. Он предназначен для выборки информации из таблиц базы данных. Упрощенный синтаксис оператора SELECT выглядит следующим образом.
SELECT [ ALL | DISTINCT ] select_item_commalist
FROM table_reference_commalist
[ WHERE conditional_expression ]
[ GROUP BY column_name_commalist ]
[ HAVING conditional_expression ]
[ ORDER BY order_item_commalist ]
В квадратных скобках указаны элементы, которые могут отсутствовать в запросе.
Ключевое слово SELECT сообщает базе данных, что данное предложение является запросом на извлечение информации.
После слова SELECT через запятую перечисляются наименования полей (список атрибутов), содержимое которых запрашивается.
Обязательным ключевым словом в предложении-запросе SELECT является слово FROM (из). За ключевым словом FROM указывается список разделенных запятыми имен таблиц, из ко-торых извлекается информация.
Например, select title, description from film
Любой SQL-запрос должен заканчиваться символом «;» (точка с запятой). Результатом данного запроса будет таблица.
Порядок следования столбцов в этой таблице соответствует порядку полей, указанному в запросе
Если необходимо вывести значения всех, столбцов таблицы, то можно вместо перечисления их имен использовать символ «*» (звездочка).
SELECT * FROM film;
В данном случае результатом выполнения запроса будет вся таблица film.
Еще раз обратим внимание на то, что получаемые в результате SQL-запроса таблицы не в полной мере отвечают определению реляционного отношения. В частности, в них могут оказаться кортежи (строки) с одинаковыми значениями атрибутов.
Например, запрос «список имен актеров», можно записать в следующем виде.
select first_name from actor;
Его результатом будет таблица, в таблице встречаются одинаковые строки. Для исключения из результата SELECT-запроса повторяющихся записей используется ключевое слово DISTINCT (отличный).
Если запрос SELECT извлекает множество полей, то DISTINCT исключает дубликаты строк, в которых значения всех выбранных полей идентичны.
Предыдущий запрос можно записать в следующем виде.
select distinct first_name from actor;
В результате получим таблицу, в которой дубликаты строк исключены.
Ключевое слово ALL (все), в отличие от DISTINCT, оказывает противоположное действие, то есть при его использовании повторяющиеся строки включаются в состав выходных данных. Режим, задаваемый ключевым словом ALL, действует по умолчанию, поэтому в реальных запросах для этих целей оно практически не используется.
Использование в операторе SELECT предложения, определяемого ключевым словом WHERE (где), позволяет задавать выражение условия (предикат), принимающее значение истина или ложь для значений полей строк таблиц, к которым обращается оператор SELECT. Предложение WHERE определяет, какие строки указанных таблиц должны быть выбраны. В таблицу, яв-ляющуюся результатом запроса, включаются только те строки, для которых условие (предикат), указанное в предложении WHERE, принимает значение истина.
Пример: Написать запрос, выполняющий выборку фамилий всех актеров с именем PENELOPE
select last_name from actor
В задаваемых в предложении WHERE условиях могут использоваться операции сравнения, определяемые операторами = (равно), > (больше), = (больше или равно), (не равно), а также логические операторы AND, OR И NOT.
Например, запрос для получения названий и описаний фильмов — короткометражек (короче 60 мин), прокат которых стоит меньше 3$ , будет выглядеть таким образом:
select title, description from film
where length SELECT * FROM users
2. Выборка данных с объединением двух таблиц (JOIN):
SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id
* в данном примере идет выборка данных с объединением таблиц users и users_rights . Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.
3. Выборка с интервалом по времени и/или дате
а) известна точка начала и определенный временной интервал:
* будут выбраны данные за последний час (поле date ).
б) известны дата начала и дата окончания:
25.10.2017 и 25.11.2017 .
в) известны даты начала и окончания + время:
* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд .
г) вытаскиваем данные за определенные месяц и год:
* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.
4. Выборка максимального, минимального и среднего значения:
> SELECT max(area), min(area), avg(area) FROM country
* max — максимальное значение; min — минимальное; avg — среднее.
5. Использование длины строки:
* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.
Примеры более сложных запросов или используемых редко
1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT):
* из таблицы users извлекаются данные по полю id , все они помещаются в одну строку, значения разделяются запятыми .
2. Группировка данных по двум и более полям:
> SELECT * FROM users GROUP BY CONCAT(title, «::», birth)
* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth . Перед группировкой мы делаем объединение полей в одну строку с разделителем :: .
3. Объединение результатов из двух таблиц (UNION):
> (SELECT id, fio, address, «Пользователи» as type FROM users)
UNION
(SELECT id, fio, address, «Покупатели» as type FROM customers)
* в данном примере идет выборка данных из таблиц users и customers .
4. Выборка средних значений, сгруппированных за каждый час:
SELECT avg(temperature), DATE_FORMAT(datetimeupdate, «%Y-%m-%d %H») as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, «%Y-%m-%d %H»)
* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).
Вставка (INSERT)
* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.
Примеры использования insert
1. Вставка нескольких строк одним запросом:
> INSERT INTO cities («name», «country») VALUES («Москва», «Россия»), («Париж», «Франция»), («Фунафути» ,»Тувалу»);
* в данном примере мы одним SQL-запросом добавим 3 записи.
2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):
* извлекаем все записи из таблицы cities , названия которых начинаются на «М» и заносим в таблицу cities-new .
Обновление (UPDATE)
* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).
Обновление с использованием замены (REPLACE):
UPDATE cities SET name = REPLACE(name, «Масква», «Москва»);
Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:
Удаление (DELETE)
* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).
Создание таблицы
> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
* где table — имя таблицы (в примере users_rights ); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights ); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL ); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8 ).
Использование запросов в PHP
Подключаемся к базе данных:
mysql_connect («localhost», «login», «password») or die («MySQL connect error»);
mysql_select_db («db_name»);
mysql_query(«SET NAMES «utf8″»);
* где подключение выполняется к базе на локальном сервере (localhost ); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name ; используемая кодировка UTF-8 .
Также можно создать постоянное подключение:
mysql_pconnect («localhost», «login», «password») or die («MySQL connect error»);
* однако есть вероятность достигнуть максимально разрешенного лимита хостинга. Данным способом стоит пользоваться на собственных серверах, где мы сами можем контролировать ситуацию.
* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).
Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():
$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) <
echo $mass . «
«;
>
* в данном примере выполнен запрос к таблице users . Результат запроса помещен в переменную $result . Далее используется цикл while , каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.
Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.
Экранирование
При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \
* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.
На этом все. Если Вам нужно помочь с выполнением запроса, пишите мне на почту
Содержание статьи
1. Самые простые MySQL запросы
2. Простые SELECT (выбрать) запросы
3. Простые INSERT (новая запись) запросы
4. Простые UPDATE (перезаписать, дописать) запросы
5. Простые DELETE (удалить запись) запросы
6. Простые DROP (удалить таблицу) запросы
7. Сложные MySQL запросы
8. MySQL запросы и переменные PHP
1. Самые простые SQL запросы
SHOW databases;
2. Выведет список ВСЕХ таблиц в Базе Данных base_name.
SHOW tables in base_name;
2. Простые SELECT (выбрать) запросы к базе данных MySQL
1. Выбирает ВСЕ данные в таблице tbl_name.
SELECT * FROM tbl_name;
2. Выведет количество записей в таблице tbl_name.
SELECT count(*) FROM tbl_name;
3. Выбирает (SELECT) из(FROM) таблицы tbl_name лимит (LIMIT) 3 записи, начиная с 2.
SELECT * FROM tbl_name LIMIT 2,3;
4. Выбирает (SELECT) ВСЕ (*) записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id по порядку.
SELECT * FROM tbl_name ORDER BY id;
5. Выбирает (SELECT) ВСЕ записи из (FROM) таблицы tbl_name и сортирует их (ORDER BY) по полю id в ОБРАТНОМ порядке.
SELECT * FROM tbl_name ORDER BY id DESC;
6. Выбирает (SELECT ) ВСЕ (*) записи из (FROM ) таблицы users и сортирует их (ORDER BY ) по полю id в порядке возрастания, лимит (LIMIT ) первые 5 записей.
SELECT * FROM users ORDER BY id LIMIT 5;
7. Выбирает все записи из таблицы users , где поле fname соответствует значению Gena .
SELECT * FROM users WHERE fname=»Gena»;
8. Выбирает все записи из таблицы users , где значение поля fname начинается с Ge .
SELECT * FROM users WHERE fname LIKE «Ge%»;
9. Выбирает все записи из таблицы users , где fname заканчивается на na , и упорядочивает записи в порядке возрастания значения id .
SELECT * FROM users WHERE fname LIKE «%na» ORDER BY id;
10. Выбирает все данные из колонок fname , lname из таблице users .
SELECT fname, lname FROM users;
11. Допустим у Вас в таблице пользовательских данных есть страна. Так вот если Вы хотите вывести ТОЛЬКО список встречающихся значений (чтобы, например, Россия не выводилось 20 раз, а только один), то используем DISTINCT. Выведет, из массы повторяющихся значений Россия, Украина, Беларусь. Таким образом, из таблицы users колонки country будут выведены ВСЕ УНИКАЛЬНЫЕ значения
SELECT DISTINCT country FROM users;
12. Выбирает ВСЕ данные строк из таблицы users где age имеет значения 18,19 и 21.
SELECT * FROM users WHERE age IN (18,19,21);
13. Выбирает МАКСИМАЛЬНОЕ значение age в таблице users . То есть если у Вас в таблице самое большее значение age (с англ. возраст) равно 55, то результатом запроса будет 55.
SELECT max(age) FROM users;
14. Выберет данные из таблицы users по полям name и age ГДЕ age принимает самое маленькое значение.
SELECT name, min(age) FROM users;
15. Выберет данные из таблицы users по полю name ГДЕ id НЕ РАВЕН 2.
SELECT name FROM users WHERE id!=»2″;
3. Простые INSERT (новая запись) запросы
1. Делает новую запись в таблице users , в поле name вставляет Сергей, а в поле age вставляет 25. Таким образом, в таблицу дописывается новая строки с данными значениями. Если колонок больше, то они оставшиеся останутся либо пустыми, либо с установленными по умолчанию значениями.
INSERT INTO users (name, age) VALUES («Сергей», «25»);
4. Простые UPDATE запросы к базе данных MySQL
1. В таблице users age становится 18.
UPDATE users SET age = «18» WHERE ;
2. Всё то же самое, что и в первом запросе, просто показан синтаксис запроса, где перезаписываются два поля и более.
В таблице users ГДЕ id равно 3 значение поля age становится 18, а country Россия.
UPDATE users SET age = «18», country = «Россия» WHERE ;
5. Простые DELETE (удалить запись) запросы к базе данных MySQL
1. Удаляет строку из таблицы users ГДЕ id равен 10.
DELETE FROM users WHERE ;
6. Простые DROP (удалить таблицу) запросы к базе данных MySQL
1. Удаляет целиком таблицу tbl_name .
DROP TABLE tbl_name;
7. Сложные запросы к базе данных MySQL
SELECT id,name,country FROM users,admins WHERE TO_DAYS(NOW()) — TO_DAYS(registration_date) Что может делать
Язык sql позволяет:
- создавать таблицы;
- изменять получать и хранить разные данные;
- объединять информацию в блоки;
- защитить данные;
- создавать запросы в access.
Важно! Разобравшись с sql вы сможете писать приложения для Вордпресс любой сложности.
Какая структура
БД состоит из таблиц, которые можно представить в виде Эксель файла.
У нее имеется имя, колонки и ряд с какой-то информацией. Создавать подобные таблицы можно при помощи sql запросов.
Что нужно знать
Основные моменты при изучении Sql
Как уже отмечалось выше, запросы применяются для обработки и ввода новой информации в БД, состоящую из таблиц. Каждая ее строка — это отдельная запись. Итак, создадим БД. Для этого напишите команду:
Create database ‘bazaname’
В кавычках пишем имя БД на латинице. Старайтесь придумать для нее понятное имя. Не создавайте базу типа «111», «www» и тому подобное.
После создания БД устанавливаем :
SET NAMES ‘utf-8’
Это нужно чтобы контент на сайте правильно отображаться.
Теперь создаем таблицу:
CREATE TABLE ‘bazaname’ . ‘table’ (
id INT(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
log VARCHAR(10),
pass VARCHAR(10),
Во второй строке мы прописали три атрибута. Посмотрим, что они означают:
- Атрибут NOT NULL означает, что ячейка не будет пустой (поле обязательное для заполнения);
- Значение AUTO_INCREMENT — автозаполнение;
- PRIMARY KEY — первичный ключ.
Как добавить информацию
Чтобы заполнить поля созданной таблицы значениями, используется оператор INSERT. Пишем такие строки кода:
INSERT INTO ‘table’
(login , pass , date) VALUES
(‘Vasa’, ‘87654321’, ‘2017-06-21 18:38:44’);
В скобках указываем название столбцов, а в следующей — значения.
Важно! Соблюдайте последовательность названий и значений столбцов.
Как обновить информацию
Для этого используется команда UPDATE. Посмотрим, как изменить пароль для конкретного пользователя. Пишем такие строки кода:
UPDATE ‘table’ SET pass = ‘12345678’ WHERE >
Теперь поменяйте пароль ‘12345678’. Изменения происходят в строке с «id»=1. Если не писать команду WHERE — поменяются все строки, а не конкретная.
Рекомендую вам приобрести книгу « SQL для чайников ». С ее помощью вы сможете шаг за шагом профессионально работать с БД. Вся информация построена по принципу от простого к сложному, и будет хорошо восприниматься.
Как удалить запись
Если вы написали что-то не так, исправьте это при помощи команды DELETE. Работает так же, как и UPDATE. Пишем такой код:
DELETE FROM ‘table’ WHERE >
Выборка информации
Для извлечения значений из БД используется команда SELECT. Пишем такой код:
SELECT * FROM ‘table’ WHERE >
В данном примере в таблице выбираем все имеющиеся поля. Это происходит если прописать в команде звездочку «*». Если нужно выбрать какое-то выборочное значение пишем так:
SELECT log , pass FROM table WHERE >
Необходимо отметить, что умения работать с базами данных будет недостаточно. Для создания профессионального интернет-проекта придется научиться добавлять на страницы данные из БД. Для этого ознакомьтесь с языком веб-программирования php. В этом вам поможет классный курс Михаила Русакова .
Удаление таблицы
Происходит при помощи запроса DROP. Для этого напишем такие строки:
DROP TABLE table;
Вывод записи из таблицы по определенному условию
Рассмотрим такой код:
SELECT id, countri, city FROM table WHERE people>150000000
Он отобразит записи стран где населения больше ста пятидесяти миллионов.
Объединение
Связать вместе несколько таблиц возможно используя Join. Как это работает посмотрите подробнее в этом видео:
PHP и MySQL
Еще раз хочу подчеркнуть, что запросы при создании интернет-проекта — это обычное дело. Чтобы их использовать в php-документах выполните такой алгоритм действий:
- Соединяемся с БД при помощи команды mysql_connect();
- Используя mysql_select_db() выбираем нужную БД;
- Обрабатываем запрос при помощи mysql_fetch_array();
- Закрываем соединение командой mysql_close().
Важно! Работать с БД не сложно. Главное — правильно написать запрос.
Начинающие вебмастера подумают. А что почитать по этой теме? Хотелось бы порекомендовать книгу Мартина Грабера « SQL для простых смертных ». Она написана так, что новичкам все будет понятно. Используйте ее в качестве настольной книги.
Но это теория. Как же обстоит дело на практике? В действительности интернет-проект нужно не только создать, но еще и вывести в ТОП Гугла и Яндекса. В этом вас поможет видеокурс « Создание и раскрутка сайта ».
Видео инструкция
Остались еще вопросы? Посмотрите подробнее онлайн видео.
Вывод
Итак, разобраться с написанием sql запросов не так трудно, как кажется, но сделать это нужно любому вебмастеру. В этом помогут видеокурсы, описанные выше. Подпишитесь на мою группу ВКонтакте чтобы первыми узнавать о появлении новой интересной информации.
ADMINS24
Все запросы, за исключением выборки, несут потенциальную опасность для данных, которые хранятся в базе. Перед началом работы с рабочей базой, сделайте резервную копию.
Простые примеры использования SELECT
> SELECT <fields1> FROM <table> [JOIN <table2>] [ WHERE <conditions> ORDER BY <fields2> LIMIT <count> ]
* где fields1 — поля для выборки через запятую, также можно указать все поля знаком *; table — имя таблицы, из которой вытаскиваем данные; conditions — условия выборки; fields2 — поле или поля через запятую, по которым выполнить сортировку; count — количество строк для выгрузки.
* запрос в квадратных скобках не является обязательным для выборки данных.
1. Обычная выборка данных
> SELECT * FROM users
* в данном примере мы получаем список всех записей из таблицы users.
2. Выборка данных с объединением двух таблиц (JOIN)
SELECT u.name, r.* FROM users u JOIN users_rights r ON r.user_id=u.id
* в данном примере идет выборка данных с объединением таблиц users и users_rights. Объединяются они по полям user_id (в таблице users_rights) и id (users). Извлекается поле name из первой таблицы и все поля из второй.
3. Выборка с интервалом по времени и/или дате
а) известна точка начала и определенный временной интервал:
> SELECT * FROM users WHERE date >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
* будут выбраны данные за последний час (поле date).
б) известны дата начала и дата окончания:
> SELECT * FROM users WHERE date >= ‘2017-10-25’ AND date <= ‘2017-11-25’
* выбираем данные в промежутке между 25.10.2017 и 25.11.2017.
в) известны даты начала и окончания + время:
> SELECT * FROM users WHERE DATE(date) BETWEEN ‘2018-03-25 00:15:00’ AND ‘2018-04-25 15:33:09’;
* выбираем данные в промежутке между 25.03.2018 0 часов 15 минут и 25.04.2018 15 часов 33 минуты и 9 секунд.
г) вытаскиваем данные за определенные месяц и год:
> SELECT * FROM study WHERE MONTH(date) = 4 AND YEAR(date) = 2018
* извлечем данные, где в поле date присутствуют значения для апреля 2018 года.
4. Выборка максимального, минимального и среднего значения
> SELECT max(area), min(area), avg(area) FROM country
* max — максимальное значение; min — минимальное; avg — среднее.
5. Использование длины строки
> SELECT * FROM users WHERE CHAR_LENGTH(name) = 5;
* данный запрос должен показать всех пользователей, имя которых состоит из 5 символов.
6. Использование лимитов (LIMIT)
Применяется для ограничения количества выводимых результатов. Синтаксис:
<основной запрос> LIMIT [<число2>,] <число1>
* где число1 — сколько результатов вернуть; число2 — сколько результатов пропустить, необязательный параметр — если его не писать, то отсчет начнется с первой строки.
а) извлечь максимум 15 строк:
> SELECT * FROM users LIMIT 15;
б) выбрать строки с 16 по 25 (запрос со смещением):
> SELECT * FROM users LIMIT 15, 10;
* 15 строк пропускаем, 10 извлекаем.
Примеры более сложных запросов или используемых редко
1. Объединение с группировкой выбранных данных в одну строку (GROUP_CONCAT)
> SELECT GROUP_CONCAT(DISTINCT CONVERT(id USING ‘utf8’) SEPARATOR ‘, ‘) as ids FROM users
* из таблицы users извлекаются данные по полю id, все они помещаются в одну строку, значения разделяются запятыми.
2. Группировка данных по двум и более полям
> SELECT * FROM users GROUP BY CONCAT(title, ‘::’, birth)
* итого, в данном примере мы сделаем выгрузку данных из таблицы users и сгруппируем их по полям title и birth. Перед группировкой мы делаем объединение полей в одну строку с разделителем ::.
3. Объединение результатов из двух таблиц (UNION)
> (SELECT id, fio, address, ‘Пользователи’ as type FROM users)
UNION
(SELECT id, fio, address, ‘Покупатели’ as type FROM customers)
* в данном примере идет выборка данных из таблиц users и customers.
4. Выборка средних значений, сгруппированных за каждый час
SELECT avg(temperature), DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’) as hour_datetime FROM archive GROUP BY DATE_FORMAT(datetimeupdate, ‘%Y-%m-%d %H’)
* здесь мы извлекаем среднее значение поля temperature из таблицы archive и группируем по полю datetimeupdate (с разделением времени за каждый час).
5. Использование операторов IF и CASE
Данные операторы позволяют определять исход запроса исходя из условия.
а) выбрать пол мужской или женский:
SELECT IF(sex = ‘m’, ‘мужчина’, ‘женщина’) as sex FROM people
* в данном примере мы возвращаем слово «мужчина», если поле sex равно ‘m‘, иначе — «женщина».
б) заменяем идентификатор времени года более понятным человеку значением:
SELECT CASE season_id WHEN 1 THEN ‘зима’ WHEN 2 THEN ‘весна’ WHEN 3 THEN ‘лето’ WHEN 4 THEN ‘осень’ ELSE ‘неправильный идентификатор времени года’ END as season FROM ` seasons
* в данном примере мы используем оператор CASE. Если 1, то вернем слово «зима», если 2 — «весна» и так далее.
Вставка (INSERT)
> INSERT INTO <table> (<fields>) VALUES (<values>)
> INSERT INTO <table> VALUES (<values>)
* где table — имя таблицы, в которую заносим данные; fields — перечисление полей через запятую; values — перечисление значений через запятую.
* первый вариант позволит сделать вставку только по перечисленным полям — остальные получат значения по умолчанию. Второй вариант потребует вставки для всех полей.
1. Вставка нескольких строк одним запросом:
* в данном примере мы одним SQL-запросом добавим 3 записи.
2. Вставка из другой таблицы (копирование строк, INSERT + SELECT):
Синтаксис при копировании строк из одной таблицы в другую выглядит так:
> INSERT INTO <table1> SELECT * FROM <table2> WHERE <условие для select>;
* где table1 — куда копируем; table2 — откуда копируем.
а) скопировать все без разбора:
> INSERT INTO cities-new SELECT * FROM cities;
* в данном примере мы скопируем все строки из таблицы cities в таблицу cities-new.
б) скопировать определенные столбцы строк с условием:
> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;
* извлекаем все записи из таблицы cities, названия которых начинаются на «М» и заносим в таблицу cities-new.
в) копирование с обновлением повторяющихся ключей.
Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «Duplicate entry ‘xxx’ for key ‘PRIMARY’». Чтобы новые строки вставить, а повторяющиеся обновить (если есть изменения), используем «ON DUPLICATE KEY UPDATE»:
> INSERT INTO cities-new SELECT * FROM cities ON DUPLICATE KEY UPDATE `name`=VALUES(`name`), `country`=VALUES(`country`);
* в данном примере, как и в предыдущих, мы копируем данные из таблицы cities в таблицу cities-new. Но при совпадении значений первичного ключа мы будем обновлять поля name и country.
Обновление (UPDATE)
* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).
Обновление с использованием замены (REPLACE):
UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’);
UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’) WHERE country = ‘Россия’;
UPDATE cities SET name = REPLACE(name, ‘Ма’, ‘Мо’) WHERE name = ‘Масква’;
Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:
SELECT REPLACE(name, ‘Ма’, ‘Мо’) FROM cities WHERE name = ‘Масква’;
Удаление (DELETE)
> DELETE FROM <table> WHERE <conditions>
* где table — имя таблицы; conditions — условие (как и в случае с UPDATE, использовать DELETE без условия опасно — СУБД не запросит подтверждения, а просто удалит все данные).
Более сложный вариант — удаление данных с объединением таблиц. Запрос будет такого вида:
> DELETE u FROM users u JOIN users_rights r ON r.user_id=u.id WHERE r.admin=’1′
* в данном примере мы удалим записи только из таблицы users (u), которые при объединении с таблицей users_rights будут соответствовать условию r.admin=’1′.
Создание таблицы
> CREATE TABLE <table> (<field1> <options1>, <field2> <options2>) <table options>
> CREATE TABLE IF NOT EXISTS `users_rights` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`rights` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
* где table — имя таблицы (в примере users_rights); field1, field2 — имя полей (в примере создается 3 поля — id, user_id, rights); options1, options2 — параметры поля (в примере int(10) unsigned NOT NULL); table options — общие параметры таблицы (в примере ENGINE=InnoDB DEFAULT CHARSET=utf8).
Использование запросов в PHP
Подключаемся к базе данных:
mysql_connect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
mysql_select_db (‘db_name’);
mysql_query(«SET NAMES ‘utf8′»);
* где подключение выполняется к базе на локальном сервере (localhost); учетные данные для подключения — login и password (соответственно, логин и пароль); в качестве базы используется db_name; используемая кодировка UTF-8.
Также можно создать постоянное подключение:
mysql_pconnect (‘localhost’, ‘login’, ‘password’) or die («MySQL connect error»);
* однако есть вероятность достигнуть максимально разрешенного лимита хостинга. Данным способом стоит пользоваться на собственных серверах, где мы сами можем контролировать ситуацию.
* в PHP выполняется автоматически, кроме постоянных подключений (mysql_pconnect).
Запрос к MySQL (Mariadb) в PHP делается функцией mysql_query(), а извлечение данных из запроса — mysql_fetch_array():
$result = mysql_query(«SELECT * FROM users»);
while ($mass = mysql_fetch_array($result)) <
echo $mass[name] . ‘<br>’;
>
* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.
Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.
Экранирование
При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \
> SELECT * FROM producrions WHERE kpd = ‘100\%’
* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.
Использование переменных
Пременные задаются с помощью знака собаки, например:
> SET @number = 101;
* мы создали переменную number со значением 101.
Теперь можно применить переменную в запросе, например:
> INSERT INTO users (`user_number`, `user_name`) VALUES (@number, CONCAT(‘Пользователь ‘, @number));
© Copyright Admins24. All Rights Reserved.
Cookies & Privacy
Продолжая использовать данный сайт вы принимаете политику конфиденциальности и cookies