На уроке будет рассмотрен язык запросов: оператор SELECT sql — на выборку данных
SQL-запрос Select предназначен для обычной выборки из базы данных. Т.е. если нам необходимо просто получить данные, не делая с ними никакой обработки и не внося изменений в базу данных, то можно смело использовать данный запмагарос.
Синтаксис оператора SELECT
SELECT * FROM имя_таблицы;
Это самый простой вариант работы с оператором, когда мы выбираем все записи из таблицы БД.
Символ * обозначает выборку всех записей из таблицы . При этом столбцы и строки результирующего набора не упорядочены.
Рассмотрим примеры sql запросов select:
Пример :
если вы создали локальную базу данных и заполнили таблицы, как в рассмотренном ранее (или же воспользовались сервисом sqlFiddle), то выполним следующий пример.
Необходимо выбрать все записи из таблицы teachers
SELECT * FROM имя_таблицы LIMIT 2,3;
В примере происходит выборка 3 записей из таблицы, начиная со 2 записи.
Этот запрос особо необходим при создании блока страниц навигации.
Чтобы упорядочить поля результирующего набора , их следует перечислить через запятую в нужном порядке после слова SELECT:
SELECT name, zarplata, premia FROM teachers ORDER BY name;
Выберет значения полей name , zarplata , premia и отсортирует по полю name (по алфавиту)
Пример: БД «Компьютерный магазин». Выбрать данные о скорости и памяти компьютеров. Требуется упорядочить результирующий набор по скорости процессора в порядке возрастания.
SELECT `Скорость`,`Память` FROM `pc` ORDER BY 1 ASC
Результат:
Сортировку можно выполнять по двум полям:
SELECT name, zarplata, premia FROM teachers ORDER BY name DESC;
Выберет значения полей name , zarplata , premia и отсортирует по полю name по убыванию
Удаление повторяющихся значений в SQL
В случае когда необходимо получить уникальные строки, можно использовать ключевое слово DISTINCT .
DISTINCT (в переводе с английского ОТЛИЧИЕ) - аргумент, который устраняет двойные значения :
Пример БД «Институт»: требуется узнать возможные варианты размера премий. Если не использовать Distinct , в результате будет выдаваться два одинаковых значения. Удалить в sql повторяющиеся значения можно при введении Distinct — в результате дублирующиеся значения не повторяются.
- AND ,
- или NOT
SELECT Скорость, Память FROM PC;
Результат:
В таблице PC первичным ключом является поле code . Поскольку это поле отсутствует в запросе, в приведенном выше результирующем наборе имеются дубликаты строк.
Когда требуется получить уникальные строки (например, нас интересуют только различные комбинации скорости процессора и объема памяти, а не характеристики всех имеющихся компьютеров), то нужно использовать Distinct:
SELECT DISTINCT Скорость, Память FROM PC; |
SELECT DISTINCT Скорость, Память FROM PC;
Результат:
Задание sql select 1_1. БД «Институт» Выполните запрос на выборку id и name из таблицы учителей. Отсортируйте фамилии учителей по убыванию
Язык sql: where условие
Условие выполняется предложением
WHERE
которое записывается после предложения FROM .При этом в результирующий набор попадут только те записи, для которых значение предиката равно TRUE (истина).
Пример БД «Институт»: Выводить данные преподавателя из таблицы teachers , фамилия которого Иванов
Несколько условий в SQL
Предикаты (условия) могут состоять как из одного выражения, так и из любой комбинации выражений, построенных с помощью булевых операторов:
Пример БД «Институт»: вывести код преподавателя, зарплата которого составляет 10000 , а премия 500
Реляционные операторы, встречающиеся в условиях:
= Равный
> Больше чем
>= Больше чем или равно
Не равно
Between в SQL (между)
Предикат BETWEEN проверяет, попадают ли значения проверяемого выражения в диапазон, задаваемый пограничными выражениями, соединяемыми служебным словом AND .
Синтаксис:
<Проверяемое выражение> BETWEEN <Начальное выражение> AND <Конечное выражение>
Пример БД "Институт": Вывести фамилию и зарплату преподавателя, зарплата которого между 5000 и 10000.
Пример БД "Институт": Вывести фамилию и зарплату преподавателя, зарплата которого не находится в диапазоне от 5000 до 10000.
Предикат IN
Предикат IN определяет, будет ли значение проверяемого выражения обнаружено в наборе значений, который явно определен.
Синтаксис:
< Проверяемое выражение> [ NOT ] IN (< набор значений> ) <Проверяемое выражение> IN (<набор значений>)
Пример БД "Институт": вывести имена преподавателей, зарплата которых составляет 5000 , 10000 или 11000
Пример БД "Институт": вывести имена преподавателей, зарплата которых не находится среди значений: 5000 , 10000 или 11000
Задание sql select 1_3. БД "Институт" Вывести фамилию, зарплату и премию учителей, премия которых от 2000 до 5000 рублей.
Раздел 4 Информационные системы
Введение в SQL.
Создание, изменение и удаление таблиц.
Выборка данных из таблицы.
Создание SQL-запросов.
Обработка данных в SQL.
Методика обучения данной теме в школе.
Введение в SQL. SQL - структурированный язык запросов, который дает возможность создавать и работать в реляционных базах данных, которые являются наборами связанной информации сохраняемой в таблицах. Язык ориентирован на операции с данными, представленными в виде логически взаимосвязанных совокупностей таблиц-отношений. Важнейшая особенность структур этого языка состоит в ориентации на конечный рез-тат обработки данных, а не на процедуру этой обработки. SQL сам определяет, где находятся данные, индексы и даже какие наиболее эффективные последовательности операций следует использовать для получения рез-та.
Изначально, SQL был основным способом работы пользователя с базой данных и позволял выполнять следующий набор операций: создание в базе данных новой таблицы; добавление в таблицу новых записей; изменение записей; удаление записей; выборка записей из одной или нескольких таблиц (в соответствии с заданным условием); изменение структур таблиц.
Со временем SQL обеспечил возможность описания и управления новыми хранимыми объектами (например, индексы, представления, триггеры и хранимые процедуры). SQL остаётся единственным механизмом связи между прикладным программным обеспечением и базой данных. В то же время, современные СУБД, а, также, информационные системы, использующие СУБД, предоставляют пользователю развитые средства визуального построения запросов. Каждое предложение SQL - это либо запрос данных из базы, либо обращение к базе данных, которое приводит к изменению данных в базе.
В соответствии с тем, какие изменения происходят в базе данных, различают следующие типы запросов: на создание или изменение в базе данных новых или существующих объектов; на получение данных; на добавление новых данных (записей); на удаление данных; обращения к СУБД.
Основным объектом хранения реляционной базы данных является таблица, поэтому все SQL-запросы - это операции над таблицами. В соответствии с этим, запросы делятся на:
Запросы, оперирующие самими таблицами (создание и изменение таблиц);
Запросы, оперирующие с отдельными записями (или строками таблиц) или наборами записей.
Каждая таблица описывается в виде перечисления своих полей (столбцов таблицы) с указанием: типа хранимых в каждом поле значений; связей между таблицами (задание первичных и вторичных ключей); информации, необходимой для построения индексов.
Таким образом, использование SQL сводится, по сути, к формированию всевозможных выборок строк и совершению операций над всеми записями, входящими в набор.
Команды SQL разделяются на следующие группы:
1. Команды языка определения данных - DDL (Data Definition Language). Эти SQL команды можно использовать для создания, изменения и удаления различных объектов базы данных.
2. Команды языка управления данными - DCL (Data Control Language). С помощью этих SQL команд можно управлять доступом пользователей к базе данных и использовать конкретные данные (таблицы, представления и т.д.).
3. Команды языка управления транзакциями - TCL (Тгаnsасtiоn Соntrol Language). Эти SQL команды позволяют определить исход транзакции.
4. Команды языка манипулирования данными - DML (Data Manipulation Language). Эти SQL команды позволяют пользователю перемещать данные в базу данных и из нее.
Операторы SQL делятся на:
Операторы определения данных (Data Definition Language, DDL )
CREATE создает объект БД (саму базу, таблицу, представление, пользователя и т. д.)
ALTER изменяет объект
DROP удаляет объект
Операторы манипуляции данными (Data Manipulation Language, DML )
SELECT считывает данные, удовлетворяющие заданным условиям
INSERT добавляет новые данные
UPDATE изменяет существующие данные
DELETE удаляет данные
Операторы определения доступа к данным (Data Control Language, DCL )
GRANT предоставляет пользователю (группе) разрешения на определенные операции с объектом
REVOKE отзывает ранее выданные разрешения
DENY задает запрет, имеющий приоритет над разрешением
Операторы управления транзакциями (Transaction Control Language, TCL )
COMMIT применяет транзакцию.
ROLLBACK откатывает все изменения, сделанные в контексте текущей транзакции.
SAVEPOINT делит транзакцию на более мелкие участки.
Преимущества: 1.Независимость от конкретной СУБД (тексты SQL-запросов, содержащие DDL и DML, могут быть достаточно легко перенесены из одной СУБД в другую). 2. Наличие стандартов (наличие стандартов и набора тестов для выявления совместимости и соответствия конкретной реализации SQL общепринятому стандарту только способствует «стабилизации» языка). 3. Декларативность (с помощью SQL программист описывает только то, какие данные нужно извлечь или модифицировать)
Недостатки: 1.Несоответствие реляционной модели данных 2.Повторяющиеся строки 3. Неопределённые значения (nulls) 4. Явное указание порядка колонок слева направо 5. Колонки без имени и дублирующиеся имена колонок 6. Отсутствие поддержки свойства «=» 7. Использование указателей 8. Высокая избыточность
2.2 Создание, изменение и удаление таблиц.
Создание таблицы:
Таблицы создаются командой CREATE TABLE. Эта команда создает пустую таблицу - таблицу без строк. Значения вводятся с помощью DML команды INSERT. Команда CREATE TABLE в основном определяет им таблицы, в виде описания набора имен столбцов указанных в определенном порядке. Она также определяет типы данных и размеры столбцов. Каждая таблица должна иметь по крайней мере один столбец.
Синтаксис команды:
CREATE TABLE
(
Изменение таблицы:
Команда ALTER TABLE – это содержательна форма, хотя ее возможности несколько ограничены. Она используется чтобы изменить определение существующей таблицы. Обычно, она добавляет столбцы к таблице. Иногда она может удалять столбцы или изменять их размеры, а также в некоторых программах добавлять или удалять ограничения. Типичный синтаксис чтобы добавить столбец к таблице:
ALTER TABLE
/*teachers*/ CREATE TABLE `teachers` ( `id` INT (11 ) NOT NULL , `name` VARCHAR (25 ) NOT NULL , `code` INT (11 ) , `zarplata` INT (11 ) , `premia` INT (11 ) , PRIMARY KEY (`id` ) ) ; INSERT INTO teachers VALUES (1 , "Иванов" , 1 , 10000 , 500 ) , (2 , "Петров" , 1 , 15000 , 1000 ) , (3 , "Сидоров" , 1 , 14000 , 800 ) , (4 , "Боброва" , 1 , 11000 , 800 ) ; /*lessons*/ CREATE TABLE `lessons` ( `id` INT (11 ) NOT NULL , `tid` INT (11 ) , `course` VARCHAR (25 ) , `date` VARCHAR (25 ) , PRIMARY KEY (`id` ) ) ; INSERT INTO lessons VALUES (1 , 1 , "php" , "2015-05-04" ) , (2 , 1 , "xml" , "2016-13-12" ) ; /*courses*/ CREATE TABLE `courses` ( `id` INT (11 ) NOT NULL , `tid` INT (11 ) , `title` VARCHAR (25 ) , `length` INT (11 ) , PRIMARY KEY (`id` ) ) ; INSERT INTO courses VALUES (1 , 1 , "php" , 54 ) , (2 , 1 , "xml" , 72 ) , (3 , 2 , "sql" , 25 ) ; |
/*teachers*/ CREATE TABLE `teachers` (`id` int(11) NOT NULL, `name` varchar(25) NOT NULL, `code` int(11), `zarplata` int(11), `premia` int(11), PRIMARY KEY (`id`)); insert into teachers values (1, "Иванов",1,10000,500), (2, "Петров",1,15000,1000) ,(3, "Сидоров",1,14000,800), (4,"Боброва",1,11000,800); /*lessons*/ CREATE TABLE `lessons` (`id` int(11) NOT NULL, `tid` int(11), `course` varchar(25), `date` varchar(25), PRIMARY KEY (`id`)); insert into lessons values (1,1, "php","2015-05-04"), (2,1, "xml","2016-13-12"); /*courses*/ CREATE TABLE `courses` (`id` int(11) NOT NULL, `tid` int(11), `title` varchar(25), `length` int(11), PRIMARY KEY (`id`)); insert into courses values (1,1, "php",54), (2,1, "xml",72), (3,2, "sql",25);
В результате получим таблицы с данными:
Отправка запроса:
Для того чтобы протестировать работоспособность сервиса, добавьте в правое окошко код запроса.
Пример: при помощи запроса выберите все данные из таблицы teachers, касаемые учителя с фамилией Иванов
На дальнейших уроках SQL будет использоваться та же схема, поэтому необходимо будет просто копировать схему и вставлять в левое окно сервиса.
Для онлайн визуализации схемы базы данных можно воспользоваться сервисом https://dbdesigner.net/ :
- Создать свой аккаунт (войти в него, если уже есть).
- Щелкнуть по кнопке Go to Application .
- Меню Schema -> Import .
- Скопировать и вставить в появившееся окно код создания и заполнения таблиц базы данных