> INSERT INTO cities-new (`name`, `country`) SELECT `name`, `country` FROM cities WHERE name LIKE ‘М%’;
в) копирование с обновлением повторяющихся ключей.
Если копировать таблицы несколько раз, то может возникнуть проблема повторения первичного ключа. В базах данных значения таких ключей должны быть уникальными и при попытке вставить повтор мы получим ошибку «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`);
SET =’ ‘ WHERE* где 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)
* где 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 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():
* в данном примере выполнен запрос к таблице users. Результат запроса помещен в переменную $result. Далее используется цикл while, каждая итерация которого извлекает массив данных и помещает его в переменную $mass — в каждой итерации мы работаем с одной строкой базы данных.
Используемая функция mysql_fetch_array() возвращает ассоциативный массив, с которым удобно работать, но есть еще альтернатива — mysql_fetch_row(), которая возвращает обычный нумерованный массив.
Экранирование
При необходимости включения в строку запроса спецсимвола, например, %, необходимо использовать экранирование с помощью символа обратного слэша — \
> SELECT * FROM producrions WHERE kpd = ‘100\%’
* если выполнить такой запрос без экранирования, знак %, будет восприниматься как любое количество символов после 100.
Использование переменных
Пременные задаются с помощью знака собаки, например:
* мы создали переменную number со значением 101.
Теперь можно применить переменную в запросе, например:
> INSERT INTO users (`user_number`, `user_name`) VALUES (@number, CONCAT(‘Пользователь ‘, @number));
Источник
PHP и MySQL
Запросы MySQL с примерами: часть 2.
Введение
В первой части, основы MySQL и PHP, мы научились подключаться серверу MySQL, выбирать базу данных для работы, узнали PHP-функцию отправки запросов серверу MySQL, узнали два простейших запроса (создание и удаление таблицы), ну и узнали как закрывать соединение.
Теперь мы будем более глубоко изучать запросы MySQL. Итак, приступим!
Создание таблицы — CREATE TABLE
Сейчас у нас пустая база данных, в ней нет таблиц. Поэтому сначала создадим таблицу. Мы уже знаем как это делать из первой части.
Вот код скрипта, который создаст нужную нам табличку:
В нашей таблице только два поля: логин и пароль. Пока что нам больше не нужно, не будем усложнять процесс.
Итак, таблица создана.
Добавление строк (записей) в таблицу — INSERT
Добавить новую строку в таблицу можно при помощи SQL команды insert. Вот пример:
Синтаксис запроса выглядит так:
INSERT INTO имя_таблицы (столбец1, столбец2) VALUE (‘х1’, ‘х2’)
Кавычки во вторых скобках обязательны.
На месте значений могут быть переменные. Вот пример:
Конечно, в этом примере мало смысла. Возможно, новичкам будет полезно услышать, что так в базу данных записываются логины и пароли, которые сообщают пользователи при регистрации. Эти данные хранятся в переменных, потом, после проверки, записываются в базу.
Существует быстрый способ вставки нескольких строк одним запросом INSERT :
Как видим, перечисляемые данные просто отделены запятыми.
Итак, при помощи команды INSERT мы научились добавлять записи в таблицу. Идём дальше.
Просмотр таблицы: команда SELECT
Теперь у нас есть таблица users, в которой есть строки. Прошлый скрипт можно запустить несколько раз, и каждый раз он будет добавлять строку в таблицу. Теперь мы можем не знать, сколько у нас строк в таблице. А хочется знать что у нас в ней записано.
Запросим все данные из таблицы users:
Функция mysqli_query() вернула нам — мы его помещаем в переменную и в дальнейшем будем работать с ним при помощи других функций PHP.
Число записей в запросе
Давайте определим сколько строк в нашем запросе? Я вот запустил скрипт добавления записи в таблицу сам не помню сколько раз и теперь не знаю сколько строк в моей таблице.
Если нам нужно узнать число записей в таблице, то приведённый способ не самый подходящий. Тут мы узнали число записей, найденных в запросе, но число записей в таблице ищется по другому.
Число записей в таблице SELECT COUNT(*)
Обратите внимание, тут мы использовали новую функцию PHP mysqli_fetch_row() для получения данных. Эта функция возвращает ряд результата запроса в форме простого массива, в нашем случае в ряду одно поле и оно имеет индес 0.
Просмотр результата запроса в цикле
После выполнения SQL-запроса с командой SELECT и получения идентификатора результата запроса, PHP создаёт в наборе записей результата внутренний указатель. Этот указатель автоматически перемещается на следующую запись, после обращения к текущей записи. Благодаря этому механизму набор результа запроса SELECT очень удобно просматривать в цикле.
Итак, запрашиваем все данные из таблицы users ( SELECT * FROM users ).
Функция mysqli_fetch_row() возвращает простой массив. В каждой итерации цикла мы получим массив с строкой из таблицы, доступ к полям которой мы можем получить указав числовой индекс.
Также есть функции mysqli_fetch_array() — возвращает любой тип массива, и mysqli_fetch_object() — возвращает объект.
Запрос SELECT DISTINCT — уникальные значения полей
Давайте создадим новую таблицу:
Итак, мы имеем новую, более сложную таблицу с уникальными записями. Сейчас давайте посмотрим, сколько у нас имён в базе данных.
SQL-запрос » SELECT DISTINCT name FROM users » вернул результат со всеми уникальными именами в нашей таблице. Каждое уникальное имя в новой строке результата запроса.
Сортировка результата — ORDER BY
Добавив в SQL-запрос команду ORDER BY мы сортируем результат запроса по возрастанию (цифры и буквы по алфавиту). Вот пример, в котором можно сравнить обычный запрос и отсортированный по возрасту (поле age).
Можете заменить поле age в команде ORDER BY на поле name и посмотреть результат.
Соответствие условию — WHERE
Добавив в SQL-запрос команду WHERE мы запросим только те записи, которые соответствуют условию. Например, сделаем запрос на людей младше 30 лет.
Для этого используем SQL-запрос » SELECT * FROM users WHERE age «.
Также мы можем сразу отсортировать результат по возрастанию age:
» SELECT * FROM users WHERE age «.
Если мы сделаем запрос » SELECT name FROM users WHERE age «, то в результате нам вернут только значения поля «name», но они также будут отсортированы по age.
Мы можем запросить значения двух полей: » SELECT name, age FROM users WHERE age «.
Теперь запросим все пользователей, с именем «Max».
SELECT name FROM users WHERE name!=’Max’
На этом с запросом WHERE всё.
Ограничение записей — LIMIT
Добавив в SQL-запрос команду LIMIT мы ограничим размер результата.
Запрос, который выводит первые три записи: » SELECT * FROM users LIMIT 3 «. Давайте посмотрим как он работает:
Также тут мы использовали запрос: » SELECT * FROM users LIMIT 3, 3 «. Вторая тройка указывает смещение в результате запроса.
Соответствие шаблону — LIKE
Вот пример запроса, который вернёт все записи с именами, начинающимися на букву S.
SELECT * FROM users WHERE name LIKE ‘S%’
Вот пример запроса, который вернёт все записи с фамилиями, заканчивающимися на букву s.
SELECT * FROM users WHERE name LIKE ‘%s’
Соответствие условию — IN
Этот запрос с использованием команды IN вернёт только те строки, которые строго соответствую условию.
Например, нас интересуют люди с возрастом 21, 26 и 33 года.
SELECT * FROM users WHERE age IN (21,26,33)
Максимальное и минимальное значение в столбце
Выбирает максимальное значение age в таблице users.
SELECT max(age) FROM users
Следующий запрос выбирает данные из таблицы users по полям name и age где age принимает минимальное значение.
SELECT name, min(age) FROM users
Обновление записи — UPDATE
Давайте Max Lopes установим возраст 15 лет. Это делается запросом MySQL:
UPDATE users SET age=’15’ WHERE name=’Max’ AND surname=’Lopes’
Обратите внимание на новую команду AND (and — по английски значит «и») в запросе. Если мы не уточним фамилию, то возраст 15 лет будет установлен всем Максам в таблице.
Одним запросом можно обновить два и более полей в одной строке. Делается это следующим образом:
UPDATE users SET age = ’18’, surname = ‘Coocker’ WHERE
В нашей таблице нет поля id, так что этот запрос не сработает на ней. Но это поле, содержащее уникальные номера строк, мы обязательно выучим.
Удалить запись — DELETE
Запрос к базе данных MySQL для удаления записи:
DELETE FROM users WHERE
Опять же, в нашей таблице нет поля id. Но мы можем удалить из неё всех людей, младше 18 лет.
DELETE FROM users WHERE age
Удалить таблицу — DROP TABLE
Запрос к базе данных MySQL который удаляет целиком таблицу users:
Иногда может потребоваться удалить столбец из таблицы, давайте например удалим из users столбец age:
ALTER TABLE users DROP age
Этот запрос MySQL удалил столбец окончательно и безвозвратно.
Иногда может потребоваться добавить столбец в существующую таблицу, давайте например снова добавим в таблицу users столбец age:
ALTER TABLE users ADD age TINYINT UNSIGNED
Иногда может потребоваться переименовать столбец, например столбец age переименовать в vozrast. Делаем это так:
ALTER TABLE users CHANGE age vozrast TINYINT UNSIGNED
Иногда может потребоваться переименовать таблицу:
RENAME TABLE users TO peoples
Удаление базы данных — DROP DATABASE
Этот запрос может удалить базу данных с имененм tester:
DROP DATABASE tester
У меня на денвере этот запрос работает, но на хостинге может и не сработать, если у пользователя базы данных нет прав на выполнение удаления.
Создание базы данных — CREATE DATABASE
Этот запрос создаёт базу данных с имененм tester:
CREATE DATABASE tester
У меня на денвере этот запрос работает, но на хостинге может и не сработать, если у пользователя базы данных нет прав на выполнение удаления.
Итоги
Итак, в этой части мы ознакомились с запросами к MySQL. Многие из рассмотренных нами запросов нам не часто пригодятся в процессе работы, но знать их нужно, так как они точно пригодятся в процессе разработки скриптов.
Некоторые запросы обычно делают только из phpMyAdmin (создание и удаление баз данных например).
В работе сайтов обычно нужно добавить запись в таблицу, редактировать запись или удалить запись из таблицы.
Следующим шагом будет изучение типов данных в MySQL.
Источник
