Данные и базы данных шпора

Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе баз данных. В этой статье будет рассказано о часто используемых командах SQL. Идеально подойдёт для тех, кто хочет освежить свои знания об SQL.

Для примеров мы будем использовать OC Debian, MariaDB и консоль. Поехали…

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

Если у вас не стоит база данных, ставим ее.

Подключаемся к серверу :

-u : User.
-p : Password.
-h : Host.

Создаем новую базу данных testdb :

Показать доступные базы данных :

Выбрать базу данных testdb для использования :

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

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

Создадим таблицу со столбцами id, user, pass, data. Причем id будет автоматически увеличивать свое значение :

INT : тип столбца среднее целое число. Подписанный диапазон составляет от -2147483648 до 2147483647 .
VARCHAR : тип строка переменной длины ,может содержать буквы, цифры и специальные символы(100 , максимально сто символов).
NOT NULL : столбец не может не содержать значение ( не может быть пустым).
AUTO_INCREMENT : создает уникальный идентификатор при вставке новой записи в таблицу.
PRIMARY KEY ( id ) : данное ограничение позволяет однозначно идентифицировать каждую запись в таблице. Первичный ключ должен содержать уникальные значения. Первичный ключ не может содержать NULL значений. Каждая таблица должна иметь первичный ключ, и каждая таблица может иметь только один первичный ключ.
DATA : тип дата. Формат: гггг-ММ-ДД.

Простомтр таблиц в базу :

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

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

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

Обновление данных в таблице. Скажем заменим поля user и pass для id 1 :

WHERE это условие при котором будет произведена замена.

Удаление всех данных из таблице :

Создание запросов.

SELECT используется для получение данных. Давайте получим значения столбца user и pass .

Или получим всю таблицу :

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

В запросе мы можем использовать всяко разные условия. Выведем например все данные где user = ‘test’ :

сравнение текста;
сравнение численных значений;
логические операции AND (и), OR (или) и NOT (отрицание).

ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC.

С помощью BETWEEN мы можем выбрать определенный промежуток. Могут использованы числовые и текстовые значения, а также даты. Например с 2 по 4 запись :

Оператор LIKE используется в WHERE, чтобы задать шаблон поиска похожего значения.

С помощью оператора IN можно задать несколько значений в WHERE.

COUNT (Имя столбца) — возвращает количество строк
SUM ( Имя столбца ) — возвращает сумму значений в данном столбце
AVG ( Имя столбца ) — возвращает среднее значение данного столбца
MIN ( Имя столбца ) — возвращает наименьшее значение данного столбца
MAX ( Имя столбца ) — возвращает наибольшее значение данного столбца

Резервное копирование базы и восстановление.

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

-u : User
-p : Password
-h : Host
testdb : Name data base.
test_copy.sql : Файл дампа.

Если нужно создать копию нескольких баз, то указываем их после параметра -B

А для того чтобы сделать дамп всех баз данных, необходимо использовать параметр —all-databases (или сокращенно -A), пример:

Восстановление дампа базы :

Или через mysql-console:

На этом мы закончин с базовыми командами SQL )))

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Львиная доля мировой информации хранится в реляционных базах данных. Чтобы работать с ней, нужно владеть языком SQL-запросов.

Для решения многих стандартных задач не требуется быть SQL-виртуозом, достаточно изучить азы работы с базами:

создание и редактирование таблиц;
сохранение и обновление записей;
выборка и фильтрация данных;
индексирование полей.

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

Чтобы учиться эффективнее, сразу же закрепляйте новые знания практикой. Поиграть с SQL можно на этом замечательном ресурсе. В левой панели вы должны ввести весь код, относящийся к структуре базы данных. После этого начинайте экспериментировать с SELECT'ами в правом поле.

* В примерах используется SQL-синтаксис для MySQL 5.6. Запросы, предназначенные для разных СУБД, могут различаться.

Терминология

База данных состоит из таблиц, а таблица – из колонок и строк.

Каждая колонка, или поле таблицы, представляет собой конкретный вид информации, например, имя студента (строка) или зарплата сотрудника (число).

Каждая строка, или запись таблицы, – это описание конкретного объекта, например, студента или сотрудника.

Уровень: Новичок

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

Создадим таблицу с данными о собаках и их рационе питания:

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

Оператор DROP удаляет таблицу из базы целиком:

Если вам нужно удалить только записи, сохранив саму таблицу, воспользуйтесь оператором TRUNCATE:

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

Самые распространенные в SQL ограничения целостности (CONSTRAINTS):

DEFAULT – устанавливает значение по умолчанию;
AUTO_INCREMENT – автоматически инкрементирует значение поля для каждой следующей записи;
NOT NULL – запрещает создавать запись с пустым значением поля;
UNIQUE – следит, чтобы поле или комбинация полей оставались уникальны в пределах таблицы;
PRIMARY KEY – UNIQUE + NOT NULL. Первичный ключ должен однозначно идентифицировать запись таблицы, поэтому он должен быть уникальным и не может оставаться пустым;
CHECK – проверяет значение поля на соответствие некоторому условию.

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

Первичный ключ, автоматический инкремент, NOT NULL и значение по умолчанию мы уже использовали в примере с собаками.

Решим новую задачу – составление списка президентов:

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

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

Еще одно удобное ограничение в SQL – внешний ключ (FOREIGN KEY). Он позволяет связать поля двух разных таблиц.

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

Теперь в поле department таблицы employees нельзя будет указать произвольный отдел. Он обязательно должен содержаться в таблице departments.

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

Вы даже можете скопировать записи из одной таблицы и вставить их в другую одним запросом. Для этого нужно скомбинировать операторы INSERT и SELECT:

Оператор UPDATE используется для изменения существующих записей таблицы.

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

Запрос можно уточнить, добавив секцию WHERE с условием отбора записей.

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

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

Уровень: уверенный пользователь

Для получения данных из базы служит оператор SELECT. В SQL есть множество способов отфильтровать именно те данные, которые вам нужны, а также отсортировать их и разбить по группам.

Вот небольшая демо-база, на которой вы можете попрактиковаться:

Можно переименовывать поля для вывода:

Ограничение количества результатов:

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

Агрегатные функции могут работать со всеми записями таблицы разом, а могут и с отдельными группами. Чтобы эти группы сформировать, используйте оператор GROUP BY:

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

Очень часто нужная вам информация хранится в разных таблицах – это обусловлено законами нормализации. Поэтому важно уметь объединять их.

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

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

Соединение бывает внутреннее (INNER) и внешнее (OUTER).

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

SQL просмотрит каждую запись из таблицы employees и попытается поставить ей в соответствие каждую запись из таблицы departments. Если это удастся (id отделов совпадают), запись будет включена в результат, иначе – не будет.

Таким образом, вы не увидите Kenny Washington, у которого отдел не указан, а также все отделы, в которых нет сотрудников.

Если не указано условие для соединения таблиц, SQL создаст все возможные комбинации сотрудников и отделов.

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

Например, чтобы увидеть в результате Kenny Washington, потребуется левое внешнее соединение. Слово OUTER можно не указывать – соединение по умолчанию внешнее:

Теперь в результате есть все данные из левой таблицы (employees), даже если для них нет соответствия.

Правое соединение соответственно проигнорирует Кенни, но выведет все пустые отделы:

И наконец, полное внешнее соединение выведет и соответствия, и пустые отделы, и сотрудников без отдела.

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

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

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

SQL позволяет сделать две отдельные выборки, а затем объединить их результаты по определенному правилу:

Объединить штатных и внештатных сотрудников

Найти всех сотрудников, которые участвуют в сборной предприятия по спортивной ходьбе

Найти всех сотрудников, которые не участвуют в сборной предприятия по спортивной ходьбе и заставить участвовать :

Уровень: SQL-мастер

Views, или представления, в SQL – это SELECT-запрос, который вы можете сохранить для дальнейшего использования. Один раз написали, а потом можете пользоваться полученной таблицей, которая – внимание! – всегда остается актуальной в отличие от результата обычных запросов.

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

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

Если представление изменяемое, можно использовать при его создании CHECK OPTION для проверки изменений на соответствие некоторому предикату:

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

WITH CASCADED CHECK OPTION – проверяет запросы на всех уровнях вложенности;
WITH LOCAL CHECK OPTION – проверяет только "верхний" запрос.

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

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

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

Триггеры в SQL – это процедуры, которые автоматически запускаются при выполнении определенной операции (INSERT/UPDATE/DELETE) – до (BEFORE) или после (AFTER) нее.

Удалить существующий триггер можно с помощью оператора DROP:

Шпаргалка Шпаргалка по "Базам Данных" Тип работы: Шпаргалка. Добавлен: 14.09.2012. Год: 2011. Страниц: 9. Уникальность по antiplagiat.ru:

Билет 4. Трехзвенная модель распределения функций
AS -модель

Данная модель представляет собой систему, при которой каждая из трех функций приложения ( управление данными, обработка, представление) реализуется на отдельном компьютере.
Компонент, реализующий функции представления информации, взаимодействует с компонентом приложения как в модели DBS. Компонент приложения, расположенный на отдельном компьютере, связан с компонентом управления данными подобно модели RDA (см. вопрос 3).
Центральным звеном AS-модели является сервер приложения, на котором реализуется несколько прикладных функций, каждая из которых может быть предоставлена всем программам клиента. Серверов приложений может быть несколько, причем каждый из них предоставляет свой вид сервиса. Поступающие от клиентов к серверам запросы помещаются в очередь, из которой могут быть выбраны в соответствии с заданным приоритетом.
Компонент приложения, реализующий функции представления и являющийся клиентом для сервера приложения, может служить для организации интерфейса с конечным пользователем (т.е. обеспечивать прием данных от различных устройств или быть произвольной программой).
Достоинства: 1.гибкость и универсальность (за счет того, что различные функции реализованы на разных компьютерах); 2.- эффективность (за счет разделения функций).
Недостаток: высокие затраты ресурсов компьютера на обмен информацией.

Билет5.Транзакции. Восстановление транзакций
Транзакция
Под транзакцией понимается неделимая с точки зрения воздействия на БД последовательность операторов манипулирования данными (чтения, удаления, вставки, модификации) такая, что либо результаты всех операторов, входящих в транзакцию, отображаются в БД, либо воздействие всех этих операторов полностью отсутствует. Лозунг транзакции - "Все или ничего": при завершении транзакции оператором COMMIT результаты гарантированно фиксируются во внешней памяти (смысл слова commit - "зафиксировать" результаты транзакции); при завершении транзакции оператором ROLLBACK результаты гарантированно отсутствуют во внешней памяти (смысл слова rollback - ликвидировать результаты транзакции).
Понятие транзакции имеет непосредственную связь с понятием целостности БД. Очень часто БД может обладать такими ограничениями целостности, которые просто невозможно не нарушить, выполняя только один оператор изменения БД. Пусть например существует 2 таблицы: Сотрудники и Отделы, причем в таблице отделы есть поле, хранящее число сотрудников в отделе. Надо принять на работу нового сотрудника. Для этого нужно выполнить 2 оператора: занести новую запись в таблицу сотрудники и изменить запись в таблице отделы. Либо мы сначала изменяем таблицу сотрудники, тогда таблица отделы некоторое время будет неверна, либо сначала изменяем таблицу отделы. В любом случае после выполнения только одного оператора бд будет находиться в противоречивом состоянии.
Поэтому для поддержания подобных ограничений целостности допускается их нарушение внутри транзакции с тем условием, чтобы к моменту завершения транзакции условия целостности были соблюдены. В системах с развитыми средствами ограничения и контроля целостности каждая транзакция начинается при целостном состоянии БД и должна оставить это состояние целостными после своего завершения. Несоблюдение этого условия приводит к тому, что вместо фиксации результатов транзакции происходит ее откат (т.е. вместо оператора COMMIT выполняется оператор ROLLBACK), и БД остается в таком состоянии, в котором находилась к моменту начала транзакции, т.е. в целостном состоянии.
Ни одна СУБД не обладает механизмами деления процесса обработки данных не отдельные транзакции, это должен делать прикладной программист с помощью операторов BEGIN TRANSACTION, COMMIT или ROLLBACK
Свойства транзакций:

Восстановление транзакций
Из определения следует, что транзакции являются единицами восстановления в СУБД. Общими принципами восстановления являются следующие:

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

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

Билет 10. Модели распределенных баз данных. Однородные и неоднородные системы.
Однородные распределенные базы данных:

Однородная СУБД

Типы РаБД	Однородность	Глобальная схема	Обеспечение интерфейса
1) РаБД	+	+	Внутренние функции СУБД
2) Мультибазы данных с глоб. схемой	-	+	Пользовательский интерфейс
3) Федеративные БД	-	частичная	Пользовательский интерфейс
4) Мультибазы данных (неодн-е) с общим языком доступа	-	функции языка доступа	Пользовательский интерфейс
5) Однородные системы мультибаз данных	+	функции языка доступа	Пользовательский интерфейс + внутренние функции СУБД
6) Интероперабельные системы	множество типов источников данных	отсутствие глобальной интеграции	реализация интерфейса средствами приложения

Система мультибаз данных – это распределенная система, кот. служит внешним интерфейсом для доступа к множеству локальных СУБД или структурируется как глобальный уровень над локальными СУБД.

На уровне систем мультибаз данных добавляется неоднородность и по-прежнему используется глобальная схема. Как и в случае с однородными распределенными БД клиентские приложения оперируют с глобальной схемой, как с большой централизованной БД. Все отображения локальной БД и содержимое обрабатывается средствами глобального уровня. Однако в отличие от однородных распределенных БД мультибазы данных с глобальной схемой не обладают функциями СУБД, позволяющими поддерживать отображение и интерфейс между локальным и глобальным уровнем (в связи с неоднородностью). Использование глобальной схемы в концептуальном отношении выделить достаточно просто, но на практике связано с серьезными проблемами.

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

Неоднородные системы мультибаз данных

Мультибазы с общим языком доступа фактически представляют собой распределенные среды управления с технологией Клиент-Сервер. В среде мультибаз данных с общим доступом (неоднор. или однор.) глобальная схема вообще отсутствует.
Интероперабельные - это системы, в которых сами приложения, выполняемые в среде той или иной СУБД, ответственны за интерфейсы между различными средами приложения, независимо от того, являются они однородными или неоднородными. Системы ориентированы главным образом на обмен данными. Дальнейшее развитие этих систем является объектно-ориентированные БД.

Принципы организации хранилища.

Процессы работы с данными.

Источниками данных могут быть:

Источники данных классифицируются:

Вся эта информация используется в словаре метаданных. В словарь метаданных автоматически включаются словари источников данных. Здесь же форматы данных для их последующего согласования, периодичность пополнения данных, согласованность во времени.
Задача словаря метаданных состоит в том, что бы освободить разработчика от необходимости стандартизировать источники данных.
Создание хранилищ данных не должно противоречить действующим системам сбора и обработки информации. Специальные компоненты словарей должны обеспечивать своевременное извлечение из словарей и обеспечить преобразование к единому формату на основе словаря метаданных.
Логическая структура данных хранилища данных отличается от структуры данных источников данных.
Для разработки эф-го процесса преобразования необходима хорошо проработанная модель корпоративных данных и модель технологии принятия решений.
Данные для пользователя удобно представлять в многоразмерных БД, где в качестве размерности могут выступать время, цена или географический регион.
Кроме извлечения данных из БД, принятия решений важен процесс извлечения знаний, в соответствии с информационными потребностями пользователя.
С т.з. пользователя в процессе извлечения знаний из БД должны решаться след. преобразования: данные ---- информация ---- знания ---- полученные решения.
Методы выявления и анализа знаний:
1.Ассоциация.
2. Последовательность – когда определяются сообщения связанные между собой во времени.
3.Классиффикация – выявление характеристик группы, к которой принадлежит объект.
4. Кластеризация – группы заранее не сформированы.
5. Прогнозирование – моделирование поведения определенного объекта на основе имеющихся знаний.
Средства извлечения знаний:
1. Нейронные сети. Пакеты, к. созданы для прогнозирования определенной области.
2. Деревья решений.
3. Индуктивное обучение.
4. Визуализация данных.
5 . Нечеткая логика.
6. Различные статистические методы.

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

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

Следующим шагом была попытка встроить семантику в механизм управления БД, Создать четвертую модель БД. Целью создания ООБД и ООСУБД является исключение ранее используемых промежуточных уровней отображения.

Свойства ООБД:
1. Высокоэффективное представление, учитывающее особенности типов, через типы, методы, объекты.
2. Использование инкапсуляции.
3. Высокая степень непротиворечивости. Операции заданного объекта являются непротиворечивыми, независимо от того, какое приложение их вызывает. Для поддержания этой возможности средствами самих объектов, изменения могут быть сделаны только в объектах, а не в приложениях.
4. Снижение стоимости разработки новых приложений за счет повторного использования описания объектов, хранящихся в библиотеке классов.

Билет16.Манифест ООБД.
В манифесте ООБД (Atkinson et al., 1989) предлагаются обязательные характеристики, которым должна отвечать любая ООБД. Их выбор основан на 2 критериях: система должна быть объектно-ориентированной и представлять собой БД.
Три класса характеристик:

Обязательные:

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

Читайте также: