Возьми бумагу и выпиши все категории по которым можно структурировать данные о друзьях Грега. Всего будет 9 категорий.

Категории,которые мы определим, будут исполь­зоваться для упорядочения данных.

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

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

Как насчет базы данных?

Ведь уровень оо базах данных, верно?

Уровень 18

Вступление в SQL

Вот, что должно получиться в результате 

 
Рассматриваем данные по категориям

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

Анджелина

Мендоса

14/8/1979

Системный админитсратор

Замужем

Сан-Франциско , Са

театр, танцы

новая работа

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

Имя

Фамилия

Дата рождения

Профессия

Статус

Место жительства

Электронная почта

Увлечения

Ищет

А вот как выглядит га же информация в виде ТАБЛИЦЫ из строк и столбцов.

А я уже видел такое представление дан­ных в Excel. Чем таблицы SQL отличаются от него? И что это за столбцы и строки?

 
Что такое «база данных»?

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

 

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

 

Каждый раз, когда вы выполняете поиск в Интернете, обращае­тесь за справкой, используете ТiVo, заказываете билеты, получаете штраф за превышение скорости или покупаете что-нибудь в магази­не, необходимая информация запрашивается из базы данных.

Вы и лишь немногие из окружающих вас баз данных

 
Код  под  микроскопом

База данных состоит из таблиц.

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

Например, столбец может содержать одно из зна­чений: Не женат. Женат, Разведен. 

Строка таблицы содержит всю инфор­мацию об одном объекте таблицы. В новой таблице Грега строка содер­жит полное описание одного человека.  Например, в одной строке могут храниться следующие данные: Джон, Джексон, не женат, писатель, jj@boards-r-us.com.

 

Информация в базе данных делится на таблицы.

 
Стань таблицей

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

справитесь с упражнением  проверьте, удалось ли вам достичь духовного единения  с таблицей.

Ответ

По содержимому  карточек понятно,  что речь  идет о  пончиках с  вареньем jelly_doughnuts

 
В базах данных хранится логически связанная информация

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

 
Таблицы под увеличительным стеклом 

Столбец — фрагмент данных, хранящихся в таблице.

Стро­ка (или запись) — набор столбцов, описывающих атрибуты одного объекта.

Столбцы и строки образуют таблицу.

 

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

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

Вот именно. Сначала информация о каждом человеке разделяется на категории.
Категории становятся столбцами. Каждая карточка преобразуется в запись. Вы можете взять всю информацию с карточек и преобразовать ее в таблицу.

Категории которые объеденят все данныекарточек

Имя

Фамилия

Дата рождения

Профессия

Статус

Место жительства

Электронная почта

Увлечения

Ищет

Данные  одной карточки  образуют строку

Анджелина

Мендоса

14/8/1979

Системный админитсратор

Замужем

Сан-Франциско , Са

театр, танцы

новая работа

 
 
Знакомство с типами данных

Перед вами еще несколько полезных типов данных. Их работа — хранить ваши данные без искажения. Давайте познакомимся со всеми.

 

CHAR (или CHARACTER) - строг и нетерпим требует чтоб его данные имели фиксированую длину.

 

DEC (или DECIMAIL) -  обеспечивает хранение чисел с заданой точностью.

 

INT (или INTEGER) - счита­ет, что числа должны быть целыми, но не боится отри­цательных значений.

 

BLOB - работает бoльшими бло­ками текстовых данных.

 

DATE - хранит даты - но не обращает внимания на время.

 

Скользкий тип DATETIME или TIMESTAMP в зависимости от  РСУВД.  Хранит  дату  и  время. Ее родственник TIME работает только  с  временем без  даты.

 

VARCHAR  хранит текстовые данные длиной от 255 символов. Отличается гибкостью, легко приспосабливается переменной длине данных  

Будьте осторожны! В вашей РСУБД могут исполь­зоваться другие имена типов!
К сожалению,  общепринятой системы имен типов не существует.
В вашей конкретной РСУБД некоторые типы могут называться по-другому. За информацией о правильных именах обращайтесь к документации РСУБД.

Как вы поняли типы данных?

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

Я все никак не пойму. Зачем все усложнять. Почему мы не храним все текстовые данные в столбцах типа BLOB? Так же было бы проще для всех ... И мне пришлось бы меньше запоминать.

Это же все делается по соображениям эффективности! Столбец VARCHAR или CHAR имеет фиксированный размер, не более 256 символов. В то время как столбец BLOB занимает намного больше памяти. С увеличением объема базы данных может кончиться место на жестком диске. Кроме того, со эначением BLOB нельзя выполнять некорые важные строковые операции, доступные для VARCHAR и CHAR (но об этом позднее).

С текстом понятно, на зачем нужны разные числовые типы INT и DEC? Ведь целое число всегда можно записать как дробное без мантиссы.

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

Окей, я поня тебя. С большими обьемами данных не шутят. Там каждый байт на счету. Но это все на сегодня? Других типов нет?

Есть, но эти типы самые важные. Конкретный набор поддерживаемых типов данных также зависит от РСУБД, поэтому за дополнительной информацией следует обращаться к документации. Так­же рекомендуем книгу «SQL in а Nutshell» - это отличный справочник, в котором описаны основные различия между разными РСУБД.

Кто и что делает? Ответ на задание r2d2 о типах данных 
 
Важные моменты, которые вы не будете исползовать

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

Для вывода описания структуры таблицы используется команда DESC.


Команда DROP TABLE уничтожает таблицу со всем содержимым. Будьте внимательны, никогда не используйте ее просто так!

Для сохранения данных в таблице используется команда insert, которая существует в нескольких вариантах.


NULL — неопределенное значение, которое не равно нулю или пустой строке. Для столбца, содержащего null, выполняется условие IS  NULL, но при этом он не равен NULL.


Столбцы, значение которых не указано в команде insert, по умолчанию инициализируются NULL.
Чтобы запретить хранение null в столбце,используйте ключевые слова NOT  NULL при создании таблицы.


Условие DEFAULT определяет знэчение по умолчанию — если при заполнении табли­цы значение столбца не указано, он авто­матически заполняется этим значением.

 

CREATE TABLE 

 

Команда  создает таблицу , но для ее выполнения не­обходимо знать ИМЕНА и ТИПЫ  ДАННЫХ столбцов . Они определяются  на осове  анализа  информации,которая будет хранится в таблице .

 

DROP TABLE

 

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

 

CREATE DATABASE

 

Команда создает базу данных в которой хранятся все таблицы с данными.

 

USE DATABASE 

 

Команда отрывает базу данных для создания таблиц.

 

NULL и NOT NULL


При  создании, базы  данных следует знать,  какие  столбцы  не  должны принимать  значение NULL  -  это упростит сортировку  и  поиск данных.  Условие NOT  NULL  задается  для столбцов  при  создании  таблицы.

 

DEFAULT


Определяет значение по  умолча­нию  для  столбца;  оно  используется в  том  случае,  если значение  столбца не указано  при  вставке  строки.

 

 
DEFAULT u значения  no умолчанию

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

CREATE  TABLE  doughnut _ list

(

doughnut_name VARCHAR(10) NOT NULL,

doughnut_type VARCHAR(6) NOT NULL,

doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.00

);

 DEFAULT

 

1)ВСЕГДА должен содержать значение. Для этого  мы  не  только  объявляем  его  с ключевыми  словами  NOT NULL, но и  присваиваем   значение  по умолчанию  1.

 

2) Это значение  сохраняется в столбце  doughnut_cost,  если в команде  INSERT не указано другое значение.

 

 DEC(3,2)

Значение может  содержать до  3  цифр:  одна  до  и  две  после запятой

NOT NULL В Выходных данных DESC

А вот как будет выглядеть таблица my_contacts, если объявить все столбцы с ключевыми словами NOT  NULL:

Описание
таблицы.
Обратите
внимание
на  слово NO
в столбце
null.

Команда создает таблицу у которой все столбци обьявлены с NOT NULL

 
 
 Команда SELECT. Выборка данных

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

Трудный поиск

Грег наконец-то перенес все данные из своей картотеки в таблицу my_contacts. Теперь ему хочется отдохнуть. Он раздобыл два билета на концерт и хочет пригласить одну из своих знакомых - девушку Энн из Сан-Франциско.Чтобы найти ее адрес электронной почты, Грег просматри­вает содержимое таблицы командой SELECT из главы  1.

 

SELECT * FROM my_contacts;

 

Минутку, вы же не думали, что я не замечу знак * ? Что он здесь делает?

Звездочка (*) приказывает РСУБД вернуть зна­чения всех столбцов таблицы.

Вы должны представить сбея на месте Грега - просмотреть таблицу my_contacts и найти в ней всех Энн из Сан-Франциско. Затем выписать их имена, фамилия и електронные почты ->

Тот,  Энн:  Anne_Toth@leapinlimos.co,
Харди,  Энн:  anneh@bOttOmsup.com
Паркер,  Энн:  annep@starbuzzcofee.com
Блант,  Энн :  annbunt@breakneckpizza.com

 

Разные  Энн  и  адреса  их электронной почты

Ищем контакт


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

Улучшенная команда SELECT

Следующая команда SELECT поможет Грегу отыскать данные Энн намного Быстрее, чем при дотошном просмотре всей огромной таблицы. В этой команде мы используем условие WHERE, которое уточняет критерий отбора записей для РСУБД. Условие сужает результаты поиска, а команда возвращает только те записи, для которых это условие выполняется.

 

Знак = в условии WHERE означает, что каждое значение столбца first_name проверяется на равенство с текстом  ' Энн '. Если два значе­ния равны, то вся запись включается в результат выборки. Если нет — запись пропускается.

В этомм окне консоли показан результат запроса — подмножество записей, у которых столбец first_name содержит значение  ' Энн '.

Что это за  *   ?

Минутку, вы же не думали, что я не замечу знак * ?

Что он здесь делает?

А если я не хочу включать в выборку все столбцы? Можно ли использовать что-то другое вместо звездочки?

Да, можно. Звездочка выбирает все столбцы, но через несколько страниц вы узнаете, как ограничить выборку частью столбцов, чтобы с результатом было про­ще работать

Звездочка (*) приказывает РСУБД вернуть зна­чения всех столбцов таблицы.

 
 
Что это за ?

Звездочка (*) приказывает РСУБД вернуть значения всех столбцов таблицы

Время практики

Вы наверно подумали, что сейчас будут какие-то нудные задания, но ничего такого! Сейчас мы с вами замутим фруктовые коктели в баре Galaxy QA Academy. Просмотри таблицу всего меню, а затем магистр даст тебе SQL запрос, и с его помощью ты узнаешь какой коткель тебе достался!

Какой напиток выдаст запрос, должен сказать ты мне.

Вопрос на повышенную оценку: выясните, какой запрос не работает ... 

А какие запросы выполнят, хотя, казалось бы, не должны

Внимание, правильный ответ

 
Апострофы как специальные символы

Если вы вставляете в таблицу данные (INSERT) или делаете запрос на поиск данных (SELECT) значение VARCHAR, CHAR или BLOB, содержащее внутрен­-ний апостроф, необходимо сообщить РСУБД, что этот апостроф не завершает текст,
а является его частью и его необходимо включить в строку. Для этого можно поставить 
перед апострофом обратную косую черту.

 
Команда  с внутренним апострофом

Вы должны сообщить РСУБД, что апостроф не обозначает начало или конец строки, а является частью текста

 

Экранирование обратной косой чертой

 

Чтобы peшить эту проблему (и одновременно испра­вить команду INSERT ), поставьте перед апострофом в тексте обратную косую черту:

 

INSERT INTO my_contacts.

VALUES

(' Фанион' , ' Стрив ’ , ' steve@onionflavoredring.com' ,  'М',  '1970-01-04',  'Панк' , 'Гровер\' Милл, NJ','Не женат','Бунтарство','Единомьшшенники, гитаристы');

 

Когда  вы  ставите,  перед апострофом  префикс  \, указывающий,  что  апо­строф  является  частью текста,  это  называется «экранированием»

 

Экранирование  удвоением   апострофа

 

Апостроф также можно «экранировать» другим способом — поставив перед ним дополнительный апостроф:

 

INSERT INTO my_contacts 
VALUES

(' Фанион' , ' Стив ' , '' steve@onionflavoredring.com'',  'М',  '1970-01-04',  'Панк ' ,' Гровер’ ’ Милл, NJ','Не женат','Бунтарство','Единомышленники', 'гитаристы');

 

Апострофьі  также  мож­но  «экранировать»  удвоением, то есть заменой одного апострофа двумя.

Перепишите следующую команду с использованием двух разных способов экранирования внутреннего апострофа :

SELECT * FROM my_contacts 
WHERE 
location =  ’Гровер’ Милл, NJ’;

Сверься с ответом магистра

 
Отбор конкретных столбцов

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

Команда SELECT может включить в выборку только те столбцы, которые вам нужны.

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

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

Посмотрите только, какую красивую и лаконичную таблицу возвращает наш новый запрос. Это успех!

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

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

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

Вывод SELECT * получается слишком
длинным. А если меня интересует только адрес электронной почты? Нельзя ли
скрыть лишние столбцы?

Отбор столбцов полезен и удобен, но у него есть и другие преимущества.

С увеличением объема данных в таблице отбор столбцов ускоряет получение результатов. Ускорение проявляется и при использовании кода SQL в других языках программирования, например РНР.

Несколько способов получить «Поцелуй»

Помните нашу таблицу easy_drinks? Следующая команда SELECT вернет коктейль «Поцелуй»:

 

SELECT drink_name FROM easy_drinks 
WHERE 
main =  'вишневый сок';

Несколько способов получить «Поцелуй»

Помните нашу таблицу easy_drinks? Следующая команда SELECT вернет коктейль «Поцелуй»:

 

SELECT drink_name FROM easy_drinks 
WHERE 
main =  'вишневый сок';

 

Команда SELECT, самая используемая для тетсировщика. Давай как можно больше практиковаться - допишите четыре команды SELECT, чтобы они тоже возвращали «Поцелуй». И, чтобы закрепить, запишите еще три команды SELECT, которые возвращают коктейль «Лягуш­ка».

Если ты уже нашел ответ - давай проверим его вместе

Круто, мы уверены, что тебе очень понравилось писать запросы. Давай еще, что-нибуть выберем с помощью SQL. Используя таблицу my_contacts, напиши несколько запросов для Грега. Включите в выборку только те столбцы, которые необходимы для получения ответа. Обратите особое внимание на апострофы.

Объеденение условий

Два условия поиска - тип "с глазурью" и оценка "10" - можно объединить в один запрос при помощи ключевого слова AND. Результаты такого запроса будут удовлетворять обоим условиям.

Результат запроса AND. Даже если запрос вернет несколько записей, мы будем знать, что во всех этих заведениях есть глазированные пончики с оценкой 10, так что пойти можно в любое из них. Или во все поочередно.

Предположим, вы хотите найти в таблице easy_drinks все напит­ки, содержащие более одной унции содовой, и сделать это в одном запросе. Сложное решение с двумя запросами выглядит так:

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

 
 
Поиск числовых значений

Однако использовать два запроса вместо одного неэффективно; к тому же вы рискуете упустить напитки, в которые входит 1.75 или 3  унции содовой. Лучше воспользоваться оператором сравнения «больше»:

 
Операторы сравнения

Ранее в наших условиях WHERE использовался только оператор =. Вы только что увидели пример использования оператора >, сравнивающего одно значение с другим. Ниже приведена полная сводка операторов сравнения.

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

Этот странный знак означает "не равно". Его результат прямо противоположен результату знака =. Два значения либо равны, либо не равны - третьего не дано. Ты либо на стороне света, либо на стороне тьмы - выбирай сам!

=

<>

Всем известный знак равенства.

Означает ״Не равно״. Возвращает все записи, которых значение не совпадает с указанным

Оператор "меньше" сравнивает значение столбца, указанного слева, со значением в правой части. Если значение столбца меньше, то запись включается в возвращаемый набор.

Оператор "больше" по смыслу противоположен знаку "меньше". Он сравнивает значение столбца со значением в правой части. Если значение столбца больше, то запись включается в возвращаемый набор.

Оператор «меньше» возвращает все значения меньшие заданного.

И конечно, существует парный оператор «больше». У нас всегда есть противовес той стороне.

<

>

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

<=

Возвращаются все. записи со значением столбца, МЕНЬШИМ ИЛИ РАВНЫМ заданному.

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

>=

А это наш, темный Оператор
БОЛЬШЕ ИЛИ РАВНО.

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

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

При помощи операторов сравнения он ищет в таблице drink_infо напитки с ценой более $3.50, содержащие не более 50 калорий.

Запрос возвращает только напитки, удовлетворяющие обоим условиям - потому, что два результата объединяются ключевым словом AND. Запрос возвращает напитки «Вот тебе на», «Одинокое дерево» и «Сода плюс».

Как ты понял силу неравенств?
Практика уровня

Задание этого уровня до безобразия простое. Пускай меня разберут 1000 дроидов если ты не сделаешь его одинм махом, после всех заданий, что мы перепаяли в этом уровне! Тебе предстоит работать с реальной БД типа MySQL. В практическом задании тебе надо будет написать 10 запросов. Когда будешь готов пришли нам их через форму.

Теоретический тест уровня SQL-1

Форма для отправки созданных вами SQL запросов

Для перехода на уровень 20, необходимо набрать минимум 12 баллов (60%)  за задания уровня 19.

  • Facebook Social Icon
  • Instagram
  • Vkontakte Social Icon
  • YouTube Social  Icon
  • mail_icon

© 2017 Galaxy QA Academy. All rights are protected.