Пользователь: learnsql8
Пароль: 42pozyw959

Ключевые слова: программирование, язык программирования, базы данных, MySQL, SQL, запрос, таблица, лекции по программированию

Автор: Приходько Максим Александрович

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

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

Обычно таблица пользователей содержит (как минимум) следующие столбцы:

• логин (login) - уникальный идентификатор, используемый пользователем при авторизации в системе, может состоять из латинских букв, арабских цифр и символа подчеркивания, обязательно начинается с буквы, пробелы и другие специальные символы не допускаются, например: ivan, ivan2007, ivan_2007, i2v0a0n7
• пароль (password) - уникальная комбинация символов, которую должен знать только пользователь с соответствующим логином и которая используется в процессе авторизации в системе для подтверждения истинности используемого логина, допустимые символы могут варьироваться в зависимости от используемой системы (например, некоторые системы поддерживают специальные символы, другие - нет)
• отображемое имя (реальное имя) - имя, под которым другие пользователи системы будут "видеть" данного пользователя (например, в подписи его сообщений), допускается использование практически любых символов, основное назначение - персонифицировать пользователя (позволить "отображаться", например, под реальным именем на русском языке) и скрыть используемый логин для усложнения взлома учетной записи
• адрес электронной почты (email) - уникальный адрес электронной почты, используемый системой для подтверждения регистрации (для предотвращения автоматической регистрации так называемыми "ботами"), восстановления пароля и других специальных функций
• статус (status) - специальный системный код, используемый для сопоставления данной учетной записи одного из возможных статусов, например, неактивна, активна, администратор, пользователь и т. д., используется лдя разграничения прав доступа к различным разделам и функциям системы

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

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

Приставка псевдо использована неслучайно. Результат работы любой хэш-функции не является уникальным, то есть существует вероятность (пусть и очень маленькая), что две разные исходные последовательности символов будут преобразованы в один и тот же хэш-код, что в конечном итоге вызовет проблемы при авторизации пользователей в системе. С другой стороны, уникальное сопоставление - это функция, которая может быть обращена. В этом случае обратная функция будет позволять расшифровывать пароли. Хэш-функция этого недостатка лишена, как раз из-за псевдоуникальности, лишающей возможности построить алгоритм обратного преобразования хэш-кода в исходную последовательность символов, а значит и не дающей "взломать" пароль.

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

Способ 1. В первом случае в таблицу вставляется новая строка без перечисления имен столбцов и с указанием всех значений.

insert into members values('', 'new_login', md5('new_pass'), 'Новый пользователь', 'new@system.com', '0')

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

Пример неправильного использования функции md5:

insert into members values('', 'new_login', 'md5("new_pass")', 'Новый пользователь', 'new@system.com', '0')

Обратите внимание, "внутри" скобок слово new_pass стоит в последнем примере в кавычках, а не в апострофах. Сделано это для того, чтобы запрос мог быть корректно выполнен. В противном случае первый апостроф (перед new_pass) расценивался бы как закрывающий для апострофа перед md5, а значит после него должна была следовать запятая или закрывающая круглая скобка. У нас же после апострофа идет другой символ, который был бы расценен как ошибка.

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

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

insert into members (login, password, email, status) values('new_login', md5('new_pass'), 'new@system.com', '0')

Команда с произвольным порядком столбцов:

insert into members (status, email, password, login) values('0', 'new@system.com', md5('new_pass'), 'new_login')

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

О столбце id. Как было видно из предыдущих примеров, при добавлении новой записи в таблицу значение столбца id можно не указывать. В этом случае (только за счет установленного флага auto_increment!) значение столбца определяется автоматически.

Вместе с тем, значение столбца можно задавать и явно, например:

insert into members values('15', 'new_login_A', md5('new_pass_A'), 'Новый пользователь A', 'new_A@system.com', '0')

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

insert into members values('', 'new_login_A', md5('new_pass_A'), 'Новый пользователь A', 'new_A@system.com', '0')

То же самое верно для команды, описанной согласно второму способу:

insert into members (id, login, password, email, status) values('23', 'new_login_B', md5('new_pass_B'), 'new_b@system.com', '0')

insert into members (login, password, email, status) values('new_login_B', md5('new_pass_B'), 'new_b@system.com', '0')

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

Активация учетной записи обычно равносильна присваиванию определенного специального значения статусу пользователя. В нашем случае активность учетной записи пусть будет определяться значением в поле status равным 1.

Для модификации полей таблицы используется команда update. Синтаксис команды опять же фактически соответствует произнесению этой команды вслух. Сразу за ключевым словом update ("обновить") следует название таблицы, поля которой будут обновляться, а затем - ключевое слово set ("присвоить"), обозначающее начало блока описания обновляемых данных. Обновляемые значения указываются через запятую в виде: имя_столбца = 'значение'. Имена столбцов не заключаются в символы апостроф, а значения наоборот обязательно заключаются в символы апостроф. Дополнительным параметром команды служит ключевое слово where ("где"), после которого следует ряд условий, определяющих тот набор строк, поля которых буду обновлены. При необходимости обновить значения одной строки таблицы простейшим способом задать такое условие будет явное указание значения первичного ключа, который, напомним, по своему определению однозначно определяет одну строчку.

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

update members set status='1' where id=3

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

update members set status='1' where id=15 or id=16

Как уже видно из приведенного примера, оператор where, определяющий набор строк, подлежащих обновлению, допускает при задании условия использование логических операторов. В нашем примере был использован оператор OR (логическое "или"), также могут быть использованы AND (логическое "и") и NOT (логическое "не").

Задание условия выбора строк в виде равенства значений некоторого одного поля достаточно большому фиксированному набору значений при использовании логического оператора OR приводит к громоздким и неудобным конструкциям. В этом случае вместо точного равенства (=) и оператора "или" (OR) удобнее использовать ключевое слово IN, после которого в круглых скобках через запятую перечисляется набор допустимых для поля значений (значения заключаются в символы апостроф, исключение может быть сделано только для числовых полей). Например, активация двух последних записей таблицы в этом случае могла бы быть произведена с помощью следующей команды:

update members set status=1 where id in ('23', 24)

С помощью оператора update можно также активировать всех неактивных в данный момент пользователей. В этом случае условие выбора строчек, подлежащих обновлению, задается очевидным равенством status = 0:

update members set status=1 where status=0

Аналогично осуществляется "блокировка пользователей", когда их статусу присваивается значение 0. Запрос для "блокировки" всех пользователей, кроме двух самых первых, может выглядеть следующим образом:

update members set status=0 where id not in (1, 2)

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

Последняя деталь, требующая рассмотрения, - использование команды update без условия выбора обновляемых строк. В этом случае, очевидно, поля будут обновлены во всей таблице. Например, следующая команда "заблокирует" всех пользователей "без разбора", в том числе и супер-администратора (со статусом равным 99):

update members set status=0

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

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

update members set status=99 where id=1;
update members set status=1 where id=2

Обратите внимание на то, что приведенные команды можно выполнить вместе. Достигается это за счет разделения команд символом ; (точка с запятой).

Удаление учетной записи производится очень редко и равносильно удалению соответствующей строчки из таблицы. Делается это с помощью команды delete from, за которой следует название таблицы и условие выбора строк, подлежащих удалению (условие, как и в предыдущем случае, определяется оператором where).

В нашем случае требуется удалить все "некорректные" учетные записи, начиная с той, в которой был "неправильно" зашифрован пароль (id = 4). Здесь становится понятным, для чего требуются первичные ключи. Если бы мы попытались в качестве условия выбора строк для удаления задать, например, равенство логина значению new_login, то вместе с частью "некорректных" записей удалили бы и самую первую (id = 3), которую требуется оставить. Только с помощью уникальных для любой строки значений можно корректно решить поставленную нами задачу. Для удаления конкретной строки в качестве условия выбора строк требуется задать точное значение первичного ключа:

delete from members where id=4

Набор строк, как и в случае обновления, удаляется путем задания набора значений первичного ключа (одним из двух способов - с помощью логического "или" OR или с помощью оператора IN). Удалить записи с идентификаторами 6, 16 и 24 можно с помощью одного из двух запросов:

delete from members where id in (6, 16, 24)

delete from members where id=6 or id=16 or id=24

Также существует способ определения условия выбора строк с "похожими" значениями некоторого поля. Например, у двух последних строчек "похожи" значения поля login. "Похожесть" в данном случае означает, что оба логина начинаются с одного и того же набора символов new_login_. Чтобы удалить все строчки, у которых логин начинается с такой последовательности символов, следует выполнить запрос, в котором условие выбора строк для удаления задается оператором like, после которого в символах апостроф следует маска (шаблон, определяющий условие "похожести"):

delete from members where login like 'new_login_%'

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

delete from members where login like '%login_%'

С определением "похожести" следует быть еще более аккуратным, чем с обычными условиями выбора строк для обновления или удаления. В последнем запросе маска new_login_% (или %login_%) неслучайно содержала перед символом % символ _ (подчеркивание). Дело в том, что символ % не только заменяет любую последовательность символов, он также может не заменять ни одного символа. То есть его наличие в маске вовсе не означает, что на его месте что-то должно быть. Поэтому следующий запрос удалил бы и обе записи со значением new_login в поле login:

delete from members where login like 'new_login%'

Возврат к необходимому состоянию осуществляется "повторной регистрацией":

insert into members values('', 'new_login', md5('new_pass'), 'Новый пользователь', 'new@system.com', '1')

Обратите внимание, значение поле id у вновь добавленной строчки равно 25 - на 1 больше последнего использованного значения (несмотря на то, что строчка со значением 24 уже удалена из таблицы).

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

update members set password=md5('changed_pass'), real_name='Иванов Сергей Петрович', email='ivanov@system.com', status=2 where id=25

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

• шифрование поля password (пароль), когда вызов функции md5 не заключается в символы апостроф;
• обновление сразу нескольких полей, когда данные перечисляются через запятую блоками вида имя_столбца='новое_значение';
• обновление числового поля, когда его значение можно не заключать в символы апостроф.

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

• точное равенство некоторого поля некоторому значению (например, условие поиска активных пользователей можно сформулировать как равенство их статуса единице - status=1);
• равенство некоторого поля одному из значений фиксированного набора (например, условие поиска пользователей с фамилией Петров или Иванов можно сформулировать как равенство их реального имени одному из указанных значений - real_name in ('Петров', 'Иванов'));
• нахождение некоторого поля в некоторых границах (например, условие поиска администраторов среди всех пользователей системы можно сформулировать как условие превосходства их статусом единицы - status>1);
• "похожесть" некоторого поля на некоторое значение (например, условие поиска пользователей, имеющих электронный почтовый ящик на сайте www.xmail.com можно сформулировать как условие наличия в адресе электронной почты фрагмента @xmail.com - email like '%@xmail.com').

Для пользования командой select необходимо в обязательном порядке указать как минимум два блока информации. Во-первых, сразу за командой указывается набор данных, подлежащих выборке. Этот набор формулируется в виде перечисление таблиц и конкретных полей (с указанием принадлежности к таблице, если данных выбираются более чем из одной таблицы). Во-вторых, сразу за блоком набора данных, подлежащих выборке, указывается набор таблиц, из которых данная выборка будет производиться. Делается это с помощью ключевого слова from, за которым следуют имена таблиц, разделенные запятой.

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

select * from members

Выбор информации (строки) конкретного пользователя осуществляется добавлением условия, задающего необходимое значение первичного ключа:

select * from members where id=1

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

select * from members where status>1

Дальнейшая иллюстрация работы команды select требует наличия большего числа зарегистрированных пользователей. Поэтому предварительно выполним следующий запрос (как и раньше, для последовательного выполнения нескольких запросов используется символ ; для их разделения):

insert into members values('', 'new_login_A', md5('new_pass_A'), 'Иванов Сергей Петрович', 'new_A@system.com', '1');
insert into members values('', 'login_B', md5('new_pass_B'), 'Иванов Сергей Иванович', 'mail@xmail.com', '2');
insert into members values('', 'login_C', md5('pass_C'), 'Петров Иван Иванович', 'email@xmail.com', '3');
insert into members values('', 'login_A', md5('pass_A'), 'Курилова Вера Васильевна', 'new_A@new_system.com', '3')

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

select status from members

Как видно из примера, в результирующем наборе данных присутствуют дулирующиеся значения статуса. Чтобы исключить повторы, к команде select добавляется ключевое слово distinct:

select distinct status from members

Полученный набор не отсортирован, то есть значения статуса в нем расположены в общем случае в произвольном порядке (как они встречаются в таблице пользователей). Пользоваться такими данным обычно неудобно, поэтому в команду select в самом конце добавляют условие сортировки результатов, которое формируется из ключевого слова order by и перечня столбцов, по которым осуществляется сортировка, с указанием порядка сортировки. Сортировка по умолчанию производится по возрастанию, то есть от меньшего значения к большему, этому виду сортировки соответствует параметр asc, добавляемый после имени столбца. Сортировка по убыванию, то есть от большего значения к меньшему, производится с помощью указания параметра desc, также добавляемого после имени столбца.

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

Вариант 1 - с указанием только имени столбца (сортировка производится в порядке, являющимся порядком сортировки по умолчанию, то есть по возрастанию):

select distinct status from members order by status

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

select distinct status from members order by status asc

Вариант 3 - сортировка "по убыванию" производится добавлением параметра desc:

select distinct status from members order by status desc

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

Выбрать максимальное значение статуса можно с помощью следующего запроса, в котором имя поля status является атрибутом агрегатной функции max(*) (вместо * подставляется имя поля или некоторое выражение, максимальное значение которого требуется определить):

select max(status) from members

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

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

select max(status) max_status from members

Аналогично используется и функция min:

select min(status) min_status from members

Вернемся теперь к поставленному вопросу поиска пользователей. Любая операция поиска может осуществляться в двух режимах - поиск точного соответствия и поиск вхождения подтсроки (вхождение подстроки - это то, что мы раньше называли "похожестью"). Точное соответствие задается равенством некоторого поля некоторому значению: имя_поля='значение'. Вхождение подстроки задается условием "похожести": имя_поля like '%значение%'.

Поиск пользователя с точным соответствием логина значению login_A осуществляется запросом:

select * from members where login='login_A'

Поиск пользователя с вхождением в его логин подстроки login_A осуществляется запросом:

select * from members where login like '%login_A%'

Разница в работе запросов, что называется, налицо. Во втором случае за счет менее строгого условия на то, какие значения может принимать поле login, результатом выбора данных стали уже две строки (вместо одной в первом случае). Причем для одной из них поле login строго равно значению 'login_A', а для второй указанное значение является лишь частью хранимого в поле login.

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

Например, найти пользователя с реальным именем 'Иванов Сергей Петрович' можно с помощью запроса:

select * from members where real_name='Иванов Сергей Петрович'

Найти этих пользователей можно было бы и заданием только части имени:

select * from members where real_name like '%Иванов%'

Обратите внимание, что поиск в таком виде "находит" и пользователя с фамилией Петров. Это не ошибка, так как его отчество Иванович содержит в качестве подстроки указанное нами ранее значение Иванов.

Чтобы уточнить, что мы ищем пользователей именно с фамилией Иванов, необходимо в маске убрать первый символ %, запретив таким образом наличие каких-либо символов до Иванов:

select * from members where real_name like 'Иванов%'

Поиск по адресу электронной почты производится аналогично. Найдем пользователей, почтовый ящик которых зарегистрирован на домене www.system.com. В этом случае по аналогии с предыдущим примером необходимо запретить наличие символов после system.com, а значит маска не будет содержать последнего символа %:

select * from members where email like '%system.com'

Как видно из результатов работы запроса, он сформулирован недостаточно точно, так как был найден пользователь с почтовым ящиком, размещенным на домене www.new_system.com. Правильный (уточненный) запрос должен содержать и символ @:

select * from members where email like '%@system.com'

Поиск одновременно по нескольким параметрам производится с помощью задания более сложного условия выбора строк, используя логические операторы AND, OR, NOT. Например, поиск по имени и адресу электронной почты производится с помощью подобного запроса:

select * from members where email like '%@xmail.com' and real_name like '%Иванов%'

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

select distinct real_name from members where real_name not like '%систем%'

Задания и форма для ввода ответов

Кнопка для прохождения тестирования