Вывести содержимое таблицы mysql

MySQL шпаргалки

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

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

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump —no-data — u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE

Заливаем архив бекапа в базу
gunzip
или так
zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE

Создаём новую базу данных
mysqladmin -u USER -pPASSWORD create NEWDATABASE

Удобно использовать бекап с дополнительными опциями -Q -c -e , т.е.
mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql , где:

• -Q оборачивает имена обратными кавычками
• -c делает полную вставку, включая имена колонок
• -e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

Для таблиц InnoDB надо добавлять —single-transaction, это гарантирует целостность данных бекапа.
Для таблиц MyISAN это не актуально, ибо они не поддерживают транзакционность.

Общие факты

• Полезно под каждую базу на боевом сервере создавать своего пользователя
• Кодировка базы может быть любой, если она UTF8
• В большинстве случаев лучше использовать движок InnoDB
• В php лучше забыть про сильно устаревшее расширение mysql и по-возможности использовать pdo или mysqli
• Новую копию MySQL всегда можно настроить и оптимизировать
• Без особой нужды не стоит открывать MySQL наружу. Вместо этого можно сделать проброс портов
  ssh -fNL LOCAL_PORT:localhost:3306 REMOTE_USER@REMOTE_HOST

Работа с данными

Числа

• На 32-битных системах практически нет смысла ставить для типа INTEGER свойство UNSIGNED, так как такие большие числа в php не поддерживаются.
  На 64-битных системах, php поддерживает большие числа, вплоть до MySQL BIGINT со знаком.
• Связанные таблицы («Foreign keys») должны иметь полное сходство по структуре ключей. Т.е. если у нас на одной таблице для поля указано «INTEGER UNSIGNED DEFAULT 0 NOT NULL» то и на другой должно быть указано аналогично
• Для хранения булевых значений, нужно использовать TINYINT(1)
• А деньги лучше хранить в DECIMAL(10, 2), где первое число обозначает количество всех знаков, включая запятую, а второе — количество знаков после запятой. Итого, у нас получится что DECIMAL(10,2) может сохранить 9999999,99

Строки

• В старых версиях (до 5.0.3) VARCHAR была ограничена 255 символами, но сейчас можно указывать до 65535 символов
• Помните, что тип TEXT ограничен только 64 килобитами, поэтому что бы сохранять «Войну и Мир» пользуйтесь «LONGTEXT»
• Самая правильная кодировка для вашей БД UTF8

Не забывайте, что

• DATE, TIME, DATETIME — выводятся в виде строк, поэтому поиск и сравнение дат происходит через преобразование
• TIMESTAMP — хранится в виде UNIX_TIMESTAMP, и можно указать автоматически обновлять колонку
• Сравнивая типы данных DATETIME и TIMESTAMP, не забывайте делать преобразование типов, например:
  SELECT * FROM table WHERE `datetime` = DATE(`timestamp`)

Перечисления

Отладка

• Если запросы тормозят, то можно включить лог для медленных запросов в /etc/mysql/my.cnf
• А потом оптимизировать запросы через EXPLAIN
• И наблюдать за запросами удобно через программу mytop

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

Источник

Базы данных

Про Postgresql есть отдельная статья

Подключение к базе даных MySQL

Из консоли наберите

mysql -h ip_вашей_базы_данных -u имя_пользователя -p

Нажмите Enter и введите пароль.

Если вы подлючаетеся к базе данных своего сайта и не знаете ip сделайте в консоли.

Например, сайт andreyolegovich.ru имеет IP 87.236.19.34

После ввода пароля должно появиться приветствие MySQL

Посмотреть все базы данных можно с помощью команды

Обратите внимание на точку с запятой в конце

Выбрать определённую БД

Как узнать какие таблицы находятся в БД MySQL

Посмотреть список содержащихся в БД таблиц

Как посмотреть содержимое таблицы MySQL

Посмотреть структуру определённой таблицы

Посмотреть содержание определённой таблицы

Упорядоченный по ID вывод таблицы имя_таблицы

SELECT * FROM имя_таблицы ORDER BY ID;

В обратном порядке:

SELECT * FROM имя_таблицы ORDER BY ID DESC;

Запрос с условием показать только китайских производителей

SELECT * FROM table_manuf WHERE (country=’China’);

Запрос с условием показать только страны с кодом 7 или 358

SELECT * FROM table_countries WHERE (code=’7′ OR code=’358′);

Как переименовать таблицу MySQL

Переименовать таблицу car в auto с помощью ALTER TABLE (mysql.ru)

ALTER TABLE car RENAME auto;

Как добавить столбец в таблицу MySQL

Добавить новый столбец типа TEXT с именем Body:

ALTER TABLE имя_таблицы ADD Body text;

Как добавить строку в таблицу БД

Добавляем запись в таблицу с названием Имя_таблицы

INSERT INTO Имя_таблицы VALUES(100, ‘Компания’, ‘Страна’, ‘Что-то ещё’);

Как удалить строку в таблице БД

Удалить строку с ID 1 в таблице с названием Имя_таблицы

DELETE FROM Имя_таблицы WHERE ;

Как изменить значение в таблице MySQL

Изменить поле Country на Russia у записи с ID 7

UPDATE имя_таблицы SET Country = ‘Russia’ WHERE ;

Как найти все таблицы с определённым столбцом

Ищем по всей базе данных таблицы у которых есть столбец с именем Name

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = ‘Name’;

Как обратиться к базе данных с помощью PHP

$connection = mysql_connect(«localhost», «Имя_БД» , «Пароль к БД для localhost подключения»); $db = mysql_select_db(«Имя_БД»); if(!$connection || !$db) < mysql_error(); >?> mysql_connect(‘localhost’, ‘Имя_БД’,’Пароль к БД для localhost подключения’); mysql_select_db(‘Имя_БД’); $res = mysql_query(«SELECT * FROM `Название_таблицы`») or die(mysql_error()); echo’

‘; while($row = mysql_fetch_assoc($res)) < echo '

‘; > echo ‘

ID	Company	Country
‘.$row[‘ID’].’	‘.$row[‘Company’].’	‘.$row[‘Country’].’

‘; echo’ ‘; echo’ ‘;

Ошибки

MS SQL

Вывести на экран список таблиц MS SQL

SELECT TABLE_NAME FROM information_schema.tables select table_name, column_name from information_schema.columns;

Ошибки MS SQL

server management studio error 4064

Если при попытке соединения с базой данных Вы получаете следующую ошибку:

Cannot open user default database. Login failed.
Login failed for user ‘UserName’. (Microsoft SQL Server, Error: 4064)

Перейдите в Options

Если в поле Connect to database: стоит замените его на имя Вашей базы данных.

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

Postgres

Получить список таблиц Postgres

select table_name from information_schema.tables;

Получить список таблиц и столбцов Postgres

select table_name, column_name from information_schema.columns;

Получить список таблиц исключая служебные и отсортировать по имени таблицы

select table_name from information_schema.tables where table_schema=’public’ ORDER BY table_name;

Выбрать из таблицы диапазон значений

select * from table where (number > ‘111’ and number mysql -h XXX.XXX.XXX.XXX -u username -p USE db_name; SHOW tables; MY_QUERY

Где XXX.XXX.XXX.XXX — IP адрес сервера на котором установлена БД

username нужно заменить на настоящее имя пользователя БД

db_name — заменить на настоящее имя БД.

Вполне возможен вариант, когда username и db_name одинаковые

Tables_in_db_name
Table01
Table02

CRUD — акроним, обозначающий четыре базовые функции, используемые при работе с базами данных: создание (англ. create), чтение (read), модификация (update), удаление (delete). Введён Джеймсом Мартином (англ. James Martin) в 1983 году[2] как стандартная классификация функций по манипуляции данными.

В SQL этим функциям, операциям соответствуют операторы Insert (создание записей), Select (чтение записей), Update (редактирование записей), Delete (удаление записей). В некоторых CASE-средствах использовались специализированные CRUD-матрицы или CRUD-диаграммы, в которых для каждой сущности указывалось, какие базовые функции с этой сущностью выполняет тот или иной процесс или та или иная роль. В системах, реализующих доступ к базе данных через API в стиле REST, эти функции реализуются зачастую (но не обязательно) через HTTP-методы PUT, GET, PATCH и DELETE соответственно.

Хотя традиционно оперирование в стиле CRUD применяется к базам данных, такой подход может быть распространён на любые хранимые вычислительные сущности (файлы, структуры в памяти, объекты). Шаблон проектирования ActiveRecord обеспечивает соответствие функций CRUD объектно-ориентированному подходу, и широко используется в различных фреймворках для доступа к базам данных из объектно-ориентированных языков программирования.

Ошибки при работе с Postgres

Вы хотите удалить что-то из таблицы TABLE_NAME , но получаете ошибку

ERROR: update or delete on table » TABLE_NAME violates foreign key constraint » fk_ANOTHER_TABLE_SOME_id » on table » ANOTHER_TABLE » DETAIL: Key (id)=( SOME_ID ) is still referenced from table » ANOTHER_TABLE «. SQL state: 23503

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

Чтобы её обойти нужно удалить элементы другой таблицы ( ANOTHER_TABLE ) которые ссылаются на ту, из которой Вы хотите удалить ( TABLE_NAME ).

Вычислить эти элементы можно по значению Key ( SOME_ID )

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

Ошибки MySQL

Ошибка: 1064

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘

Говорит о том, что в синтаксисе допущены ошибки. Допустим, Вы создаёте таблицу и задаёте столбцы неправильно.

Например, вместо , ставите ;

id INT ; model VARCHAR(20) ; modified_time TIMESTAMP ; name VARCHAR(20) ;

Или не указали длину для VARCHAR

id INT, model VARCHAR, modified_time TIMESTAMP, name VARCHAR,

Это легко исправить

id INT, model VARCHAR(20), modified_time TIMESTAMP, name VARCHAR(20),

Изучите внимательно документацию по MySQL

Ошибка: Table X already exists

ERROR 1050 (42S01) at line 3: Table ‘tableName’ already exists

Источник

Команды MySQL

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

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

Команды и ключевые слова SQL нечувствительны к регистру. Все три команды — CREATE, create и CrEaTe — абсолютно идентичны по смыслу. Но чтобы было понятнее, для команд рекомендуется использовать буквы верхнего регистра.

Имена таблиц чувствительны к регистру в Linux и Mac OS X, но нечувствительны в Windows. Поэтому из соображений переносимости нужно всегда выбирать буквы одного из регистров и пользоваться только ими. Для имен таблиц рекомендуется использовать буквы нижнего регистра.

Создание баз данных и таблиц

Создание базы данных

Для создания новой базы данных вы можете использовать следующую команду:

При успешном выполнении команды будет выведено сообщение:

Создание новой базы данных

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

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

Организация доступа пользователей

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

База данных MySQL позволяет чрезвычайно точно определять права доступа различных пользователей, которые подключаются к базе данных с помощью клиентских программ, находящихся в различных точках системы. Права доступа подразделяются на четыре нисходящих уровня: глобальные, базы данных, таблицы и столбцы. Поэтому теоретически предусмотрена возможность регламентировать доступ конкретного пользователя для записи данных только в указанные столбцы указанных таблиц указанных баз данных на указанном сервере MySQL. Столь же легко можно, не задумываясь, предоставить любому пользователю базы данных, подключающемуся откуда угодно, такие же права, как пользователю базы данных root (но такая организация защиты доступа категорически не рекомендуется).

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

Для добавления или редактирования прав доступа пользователей в базе данных MySQL могут применяться два разных способа (при условии, что модификацией прав доступа занимается пользователь базы данных root): непосредственное выполнение операторов SQL (например, ввод буквы Y вручную в каждое соответствующее поле каждой соответствующей таблицы прав доступа) или использование синтаксических конструкций GRANT и REVOKE. Последний способ является более легким и менее опасным, если допущена небольшая ошибка, поскольку в большинстве случаев попытка выполнения ошибочного запроса окончится неудачей с сообщением об ошибке SQL, но брешь в системе защиты при этом не возникнет.

Чтобы ввести информацию о новом пользователе MySQL, можно применить следующий оператор:

где данные о столбцах (column) и таблицах (table) являются необязательными, а с помощью списка, разделенного запятыми, могут быть заданы дополнительные сведения о типах прав доступа priv_types.

Если предоставлены права доступа ALL на уровне столбца, таблицы или базы данных, то пользователь получает возможность применять только тот набор прав доступа, который соответствует указанному уровню. Необходимо соблюдать исключительную осторожность при предоставлении пользователям следующих прав доступа, поскольку все эти права являются опасными: GRANT, ALTER, CREATE, DROP, FILE, SHUTDOWN, PROCESS. Такие права доступа не требуются ни одному обычному пользователю базы данных, особенно пользователю PHP.

Синтаксическая конструкция оператора отмены прав доступа весьма похожа на соответствующий оператор предоставления прав доступа, но проще него:

После предоставления или отмены прав доступа для любого пользователя необходимо вынудить базу данных выполнить перезагрузку в память новых данных о правах доступа. Для этого требуется ввести команду FLUSH PRIVILEGES. Можно также остановить и снова запустить сервер, но такое решение во многих обстоятельствах неприменимо на практике.

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

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

Если доступ осуществляется исключительно локально, то применимы практически любые права доступа. Если в ходе разработки требуется проводить эксперименты со схемой базы данных, то наиболее подходящим является именно такая конфигурация, поэтому разработчику, кроме обычных прав доступа для выполнения операций SELECT, INSERT и UPDATE, могут быть предоставлены права доступа наподобие ALTER, CREATE, DELETE и DROP. При таких обстоятельствах многие администраторы считают приемлемым просто предоставить локальному пользователю права доступа ALL PRIVILEGES к определенной базе данных, как показано ниже (права этого пользователя мы будем использовать в последующих примерах):

Код SQL — создание пользователя с максимальными правами

Автономный веб-сайт

База данных, находящаяся на отдельном хосте, по-видимому, должна будет принимать запросы на установление соединений от многочисленных веб-серверов, находящихся в том же домене. На практике для всех серверных компьютеров должны предоставляться лишь права доступа SELECT, INSERT, UPDATE и, возможно, DELETE, хотя во многих системах удаление данных фактически не происходит, поэтому уровень безопасности немного повышается, если право доступа DELETE не предоставляется.

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

Тем не менее в подобной ситуации чаще всего используется также репликация по принципу «ведущий-ведомый». Часто подобные кластеры баз данных MySQL имеют такую конфигурацию, что все запросы на выполнение операций записи поступают в ведущую базу данных, но ведомые базы данных не выполняют никаких других действий, кроме очень быстрого обслуживания операций чтения. В таком случае в каждой ведомой базе данных предоставляются только права доступа SELECT, а в ведущей базе данных предоставляются только права INSERT И UPDATE; при этом, возможно, эти права назначаются двум разным пользователям базы данных.

Создание таблиц

Для определения структуры новой таблицы базы данных служит команда CREATE TABLE. Когда создается таблица базы данных, каждый столбец может содержать дополнительные параметры, помимо имени и типа данных. Если при добавлении новой записи в таблицу поле не должно оставаться пустым, в его определении указывается ключевое слово NOT NULL. Ключевое слово PRIMARY KEY определяет, какое поле будет использоваться в качестве первичного ключа. Автоматическое заполнение ключевого поля можно определить с помощью ключевого слова AUTO_INCREMENT. Например:

Код SQL — создание таблицы

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

Если все в порядке, то вы увидите структуру новой таблицы:

Структура вновь созданной таблицы

Команда DESCRIBE является неоценимым средством отладки, когда нужно убедиться в успешном создании таблицы MySQL. Этой командой можно воспользоваться также для того, чтобы просмотреть имена полей или столбцов таблицы и типы данных в каждом из них (это удобно при работе с консолью, при использовании phpMyAdmin успешные команды подсвечиваются зеленым цветом, а структуру таблицы можно посмотреть по ссылке главного меню «Структура»).

Типы данных MySQL

Типы данных MySQL подразделяются на три основные разновидности: числовые типы; типы, предназначенные для представления даты и времени; а также строковые (или символьные) типы. Применение этих типов данных в основном не связано с какими-либо сложностями, тем более, что для обычного пользователя сайта не имеет значения, например, какой тип данных применяется в сценариях для представления целочисленных данных, INT или MEDIUMINT. Однако программисты могут многое сделать, чтобы добиться создания наиболее компактных и быстродействующих баз данных.

В таблице ниже перечислены типы данных MySQL, предусмотренные в текущих версиях, и указаны их возможные значения:

Типы данных MySQL

Обозначение	Занимаемый объем (байт)	Область применения
TINYINT, BOOL	1	При использовании в формате представления без знака позволяет хранить значения от 0 до 255; в противном случае — от -128 до 127. В будущем должен быть предусмотрен новый логический тип, но до сих пор для представления логических значений использовался тип данных TINYINT, т.е. BOOL синоним TINYINT(1)
SMALLINT	2	Целое число в диапазоне от -32768 до 32767
MEDIUMINT	3	Целое число в диапазоне от -8388608 до 8388607
INT, INTEGER	4	Целое число в диапазоне от -2e 32 до 2e 32 — 1
BIGINT	8	Целое число в диапазоне от -2e 64 до 2e 64 — 1
FLOAT	4	Число с плавающей точкой одинарной точности
DOUBLE	8	Число с плавающей точкой двойной точности
DECIMAL	Произвольное, в зависимости от точности	Распакованное число с плавающей точкой, которое хранится в таком же формате, как CHAR. Используется для представления небольших десятичных значений, таких как денежные суммы
DATE	3	Отображается в формате YYYY-MM-DD
DATETIME, TIMESTAMP	8	Отображается в формате YYYY-MM-DD HH:MM:SS
TIME	3	Отображается в формате HHH:MM:SS, где HHH — значение от -838 до 838. Это позволяет применять значения типа time для представления продолжительности времени между двумя событиями
YEAR	1	Отображается в формате YYYY, который представляет значения от 1901 до 2155
CHAR	N байт	Строка постоянной длины. Строка, имеющая длину меньше объявленной, дополняется справа пробелами. Значение N должно быть меньше или равно 255
VARCHAR	N байт	Строка переменной длины. Значение N должно быть меньше или равно 255
BINARY	N байт	Сохраняет байтовые строки
TINYBLOB, TINYTEXT	до 255	Сохраняет строки, операции сортировки и сравнения данных типа blob выполняются с учетом регистра; операции с данными типа text — без учета регистра
BLOB, TEXT	до 64 Кбайт	Длинные строки
MEDIUMBLOB, MEDIUMTEXT	до 16 Мбайт	Длинные строки
LONGBLOB, LONGTEXT	до 4 Гбайт	Длинные строки
ENUM(value1, . valueN)	1 или 2	Коллекция значений (65536 возможных значений)
SET(value1, . valueN)	до 8	Коллекция значений (64 возможных значений)

Тип данных AUTO_INCREMENT

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

Работа с таблицами

Добавление данных в таблицу

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

Здесь видно, что в команде необходимо указать, в какую таблицу будут добавляться данные, и определить список значений. Если перечень столбцов (COLUMNS) не указан, значения должны следовать в том же порядке, в каком определялись столбцы при создании таблицы (если вы не пропускаете какие-либо значения). Есть определенные правила, регламентирующие порядок заполнения базы данных с помощью команд SQL:

числовые значения должны указываться без кавычек;

строковые значения всегда должны быть в кавычках;

значения даты и времени всегда должны быть в кавычках;

функции должны указываться без кавычек;

значение NULL никогда не должно заключаться в кавычки.

Наконец, если в строке отсутствует какое-либо значение, оно по умолчанию подразумевается равным значению NULL. Однако если поле не может иметь значение NULL (то есть когда оно было определено как NOT NULL), и вы не указали значение для этого поля, будет сгенерировано сообщение об ошибке.

Давайте вставим в нашу таблицу data несколько пользователей:

Код SQL — вставка данных в таблицу

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

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

Кодировка таблицы

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

Неверная кодировка таблицы

Как вы наверное догадались эта ошибка связана с неверной кодировкой таблицы. В моем случае по умолчанию стоит кодировка latin1, которая не позволяет отображать русскоязычные символы. Чтобы изменить кодировку таблицы на приемлемую (например UTF-8) нужно воспользоваться следующей конструкцией:

Код SQL — замена кодировки таблицы Правильная кодировка

Манипулирование определениями таблиц

Создав таблицу и начав заполнять ее информацией, вы можете обнаружить, что потребовалось изменить типы полей. Например, увеличить размер поля name, вмещающего 32 символа, до 100 символов. Можно было бы начать все с нуля, полностью переопределив таблицу, но при этом будут утеряны данные. К счастью, MySQL позволяет изменять типы полей без потери данных.

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

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

Следующая команда переименует таблицу data в users_data:

Код SQL — переименование таблицы

Изменение типа данных столбца

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

Следующая команда изменит поле name таким образом, что оно будет вмещать до 100 символов:

Кроме того, команда MODIFY может принимать два необязательных параметра, изменяющих порядок следования столбцов в таблице. С помощью ключевого слова FIRST можно сделать столбец первым в таблице, а с помощью ключевого слова AFTER имя_столбца – поместить столбец после указанного. Например, следующая команда разместит столбец name после столбца year:

Добавление столбца

Добавить новый столбец позволяет команда:

Следующая команда добавит в таблицу users_data столбец типа DATETIME с именем regDate:

В этой команде, как и в конструкции ALTER TABLE MODIFY, можно определить позицию вставляемого столбца с помощью ключевых слов FIRST и AFTER имя_столбца.

Переименование столбца

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

Ниже приводится пример переименования столбца regDate в regTime. При работе с этой командой вы можете одновременно изменять определение столбца. Однако даже если определение столбца не изменяется, вам все же придется указывать его полное определение:

Удаление столбца

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

Следующая команда удалит столбец regTime:

Удаление всей таблицы

Иногда требуется удалить и целую таблицу. Полное удаление таблицы со всеми данными выполняется с помощью команды DROP:

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

Выполнение запросов к базе данных

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

Здесь столбцы – перечень имен полей, значения которых будут отбираться из таблиц. Необязательное ключевое слово WHERE задает ограничение на отбор строк, другими словами, ключевое слово WHERE ограничивает результаты, возвращаемые запросом. Например, строки могут быть отвергнуты запросом, если некоторое их поле не равно какому-либо значению, либо больше или меньше его. Ключевое слово ORDER BY позволяет определить требуемый порядок сортировки информации, возвращаемой запросом.

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

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

Ограничение результатов с помощью WHERE

Если вас интересует только какие-то определенные поля таблицы, вы можете ограничить набор возвращаемых данных с помощью ключевого слова WHERE, например:

Условные выражения должны следовать за ключевым словом WHERE. C помощью логических операторов AND и OR в конструкции WHERE можно определить сразу несколько условий. Порядок исполнения логических операторов изменяется с помощью круглых скобок ().

Определение порядка сортировки

Как уже говорилось, изменить порядок сортировки результирующего набора данных позволяет ключевое слово ORDER BY. По умолчанию ORDER BY задает сортировку в порядке возрастания, поэтому для сортировки пользователей в алфавитном порядке можно просто указать ORDER BY name. Чтобы назначить противоположный порядок сортировки, следует добавить ключевое слово DESC после имени поля name. Например, получить список пользователей, отсортированный в алфавитном порядке по убыванию, можно следующим запросом:

Соединение таблиц

Инструкция SELECT позволяет выполнять запросы сразу к нескольким таблицам. В примере ниже создается таблица purchases (покупки), в которую добавляются несколько строк для примера. Затем формируется запрос для получения списка всех купленных товаров с указанием идентификатора покупателя:

В результате вы получите:

Выборка значений из одной таблицы по ключу другой

Часть запроса users_data.*, product сообщает о необходимости выбрать все поля из таблицы users_data и единственное поле product из таблицы purchases. Часть связывает таблицы. Вы могли бы определить список отбираемых столбцов как (*), тогда в результирующий набор попали бы все поля обеих таблиц.

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

Получим результат аналогичный предыдущему.

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

Псевдонимы

Перечисляя таблицы в запросе, используйте псевдонимы (aliases). Чтобы определить псевдоним таблицы, нужно после ее полного имени поставить ключевое слово AS и затем указать псевдоним. Например, присвоим в запросе таблице users_data псевдоним «u», а таблице purchases псевдоним «p»:

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

Модификация данных в базе данных

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

Например, обновим таблицу user_data:

Данный запрос изменит значение поля year для всех пользователей с именем ‘Александр’ в таблице users_data, установив его равным значению ‘1980’. Этот прием позволяет исправлять ошибочные данные и вносить изменения.

Удаление данных из базы

Команда DELETE удаляет строки или записи из таблицы. В команде DELETE используется то же ключевое слово WHERE, что и в инструкции UPDATE: удаляются все строки, соответствующие условию. В случае отсутствия ключевого слова WHERE будут удалены все записи в таблице.

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

Функции поиска

Как вы заметили в предыдущих примерах, MySQL обладает возможностью отыскивать конкретные данные. Однако мы пока еще не рассматривали синтаксис поиска. В MySQL роль шаблонного символа исполняет символ (%), используемый совместно с ключевым словом LIKE. То есть этим символом можно буквально представить все, что угодно. Это напоминает поиск файлов в проводнике Windows по строке *.doc – будут найдены все файлы документов, независимо от имен. По умолчанию поиск выполняется без учета регистра букв.

Например, выполнить общий поиск можно с помощью следующего синтаксиса:

Код SQL Поиск по таблице

Этот запрос нашел все записи, в значении поля name которых есть символ (р). Заметим, что здесь мы использовали два символа (%), окружив ими символ (р) – (%р%). Это означает, что до и после искомого символа может быть что угодно. Если хотите, можете использовать только один шаблонный символ – жесткого правила на этот счет нет.

Символ (%), помещенный в любое место строки в инструкции LIKE, означает, что на этом месте в строке может быть что угодно. Еще один шаблонный символ – символ подчеркивания (_). Он соответствует любому единственному символу. С использованием этого шаблонного символа можно выполнить такой поиск:

В результате будут получены все строки, где имя пользователя начинается с «Elen» и кончается любым символом.

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

Источник