Работа с данными в Azure Data Studio

  • 20 мин

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

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

Открытие новой таблицы запросов

  1. Чтобы начать работу с SQL в базе данных SQL Azure, необходимо открыть новый лист запросов. В Azure Data Studio щелкните правой кнопкой мыши сервер базы данных карточек в левой области и выберите новый запрос.

    Снимок экрана: выбор нового запроса для листа запросов.

  2. После открытия новой таблицы запросов убедитесь, что вы подключены к базе данных SQL Azure с помощью раскрывающегося списка "База данных".

    Снимок экрана, показывающий, как убедиться, что вы подключены к базе данных Azure SQL с помощью выпадающего списка

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

В следующем разделе будет использоваться лист запросов в Azure Data Studio. После копирования и вставки команды на лист можно выделить код SQL. После выделения нажмите клавишу F5 или нажмите зеленую кнопку "Выполнить ", чтобы запустить код SQL в базе данных SQL Azure.

Снимок экрана: выбор зеленой кнопки

  1. Чтобы начать, вставьте строку в таблицу карточек с помощью следующего SQL.

    Заметка

    Чтобы упростить вставку изображений, необходимо преобразовать текст в varbinary с помощью команды convert SQL.

    insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
values(N'Duck', N'monster',N'white',10,N'Quack Attack', 1, CONVERT(varbinary(max), 'DUCK_PICTURE_HERE', 0));

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

    select * from dbo.cards;

  3. В предыдущем упражнении все столбцы не принимают значения NULL, кроме столбца card_text . Проверьте правило здесь, исключив столбец card_art. Выполните следующую инструкцию на листе запросов:

    insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status)
values(N'Sword', N'weapon',N'black',10,N'S Words', 1);

    База данных не разрешает вставку и напоминает нам о том, какое правило нарушается (значение NULL в столбце card_art).

    Снимок экрана: недопустимое значение NULL в столбце card_art.

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

    insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
values(N'Sword', N'weapon',N'brown',10,N'S Words', 1, CONVERT(varbinary(max), 'SWORD_PICTURE_HERE', 0));

    Как видно, эта вставка не будет зафиксирована в базе данных, так как она нарушила правило для card_color.

    Снимок экрана, показывающий, как запрос нарушил правило для

  5. Перейдя к таблице card_translations, вы можете увидеть, как внешний ключ предотвращает вставки, у которых нет соответствующих строк в родительской таблице. Следующий SQL-запрос попытается вставить строку в таблицу card_translations, но с card_id, равным 2. В таблице карточек есть только одна строка, которая имеет card_id1. Скопируйте и вставьте следующую инструкцию SQL и запустите ее на листе запросов:

    Insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(2,N'Spanish',N'Pato',N'Ataque de graznar')

    Внешний ключ предотвращает вставку, как ожидалось:

    Снимок экрана, показывающий, как внешний ключ предотвращает вставку с card_id не в таблице карточек.

  6. С помощью правильной card_id можно создать строку в таблице переводов, выполнив следующий SQL на листе запросов:

    insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(1,N'Spanish',N'Pato',N'Ataque de graznar')

  7. С строкой в таблице карточек и строкой в таблице card_translations объедините две таблицы в запросе и получите все строки, которые имеют общий card_id.

    select c.card_id 'card_id from cards table', 
       t.card_id 'card_id from card_translation table',
       c.card_name, t.translation_card_name
from dbo.cards c,
        dbo.card_translations t
where c.card_id = t.card_id;

    Снимок экрана: результаты запроса из присоединенных карточек и таблиц card_translations.

  8. Следующая инструкция SQL вставляет строку в таблицу наборов. Вы будете использовать встроенную функцию GETDATE() для вставки сегодняшних данных в формате ГГГГ-ММ-ДД. Хотя функция GETDATE также возвращает часы, минуты и секунды, тип данных — date, поэтому дополнительные данные времени будут усечены.

    insert into dbo.sets (set_name, set_date)
values(N'First Set', GETDATE());

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

    select * from dbo.sets;

  9. Карточка находится в таблице карточек, а набор находится в таблице наборов. Используя эти две строки, вы можете записать карточку и набор в таблицу set_lists, включая card_id и set_id. В этой таблице есть два внешних ключа, так будет ли следующий SQL-запрос работать, если вы попытаетесь вставить правильный card_id, но неверный set_id в таблицу set_lists? Запустите следующий SQL на листе запросов:

    insert into dbo.set_lists (card_id, set_id)
values(1,23);

    Как ожидалось, вставка была заблокирована нарушением внешнего ключа:

    Снимок экрана, показывающий, как вставка в таблицу set_lists заблокирована из-за нарушений внешнего ключа.

    Выполнение запроса с правильными идентификаторами приведет к успешной вставке в таблицу set_lists. Запустите следующий SQL на листе запросов:

    insert into dbo.set_lists (card_id, set_id)
values(1,1);

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

    select c.card_id, c.card_name, 
        t.translation_card_name, 
        s.set_name, s.set_date
from dbo.cards c,
        dbo.card_translations t,
        dbo.sets s,
        dbo.set_lists l
where c.card_id = t.card_id
    and c.card_id = l.card_id
    and s.set_id = l.set_id;

    Снимок экрана: sql-запрос, присоединенный ко всем 4 таблицам.

  11. Если вы вспомните о главе по ограничениям таблицы, там был раздел, в котором упоминались уникальные ограничения. Эти ограничения позволяют применять правило между одним или несколькими столбцами, чтобы гарантировать, что в таблице нет повторяющихся значений, аналогичных столбцу первичного ключа. Глядя на таблицу set_lists , возможно, вы заметили, что можно вставить card_id и set_id в эту таблицу несколько раз, создавая повторяющиеся данные. При применении уникального ограничения можно предотвратить это поведение.

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

    ALTER TABLE dbo.set_lists   
ADD CONSTRAINT one_card_in_set UNIQUE (card_id, set_id);   
GO

    И теперь повторите попытку следующего SQL:

    insert into dbo.set_lists (card_id, set_id)
values(1,1);

    Вы увидите сообщение об ошибке, останавливающее эту вставку и предотвращающее дублирование данных в таблице.

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

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

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

    declare @cardTable2 table (card_id int);
declare @cardTable3 table (card_id int);
declare @cardTable4 table (card_id int);
declare @cardTable5 table (card_id int);
insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
OUTPUT Inserted.[card_id] into @cardTable2
values(N'Sword', N'weapon',N'white',10,N'+2 Power', 1, CONVERT(varbinary(max), 'SWORD_PICTURE_HERE', 0));
insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
OUTPUT Inserted.[card_id] into @cardTable3
values(N'Caterpillar', N'monster',N'green',10,NULL, 1, CONVERT(varbinary(max), 'CRAWLY_PICTURE_HERE', 0));
insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
OUTPUT Inserted.[card_id] into @cardTable4
values(N'Goblin', N'monster',N'red',10,N'Can set fires', 1, CONVERT(varbinary(max), 'GOBLIN_PICTURE_HERE', 0));
insert into dbo.cards (card_name, card_type, card_color, card_power, card_text, card_status, card_art)
OUTPUT Inserted.[card_id] into @cardTable5
values(N'Full Plate Armor', N'armor',N'black',10,N'+4 Protection', 1, CONVERT(varbinary(max), 'PLATE_ARMOR_PICTURE_HERE', 0));
declare @card2i int = (select card_id from @cardTable2);
declare @card3i int = (select card_id from @cardTable3);
declare @card4i int = (select card_id from @cardTable4);
declare @card5i int = (select card_id from @cardTable5);
insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(@card2i,N'Spanish',N'Espada',N'+2 Poder')
insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(@card3i,N'Japanese',N'毛虫',NULL)
insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(@card4i,N'French',N'Lutin',N'Puede provocar incendios')
insert into dbo.card_translations(card_id, translation_card_language, translation_card_name, translation_card_text)
values(@card5i,N'German',N'Vollständige Plattenpanzerung',N'+4 Schutz')
insert into dbo.sets (set_name, set_date)
values(N'Second Set', GETDATE());
insert into dbo.set_lists (card_id, set_id)
values(@card2i,1);
insert into dbo.set_lists (card_id, set_id)
values(@card3i,1);
insert into dbo.set_lists (card_id, set_id)
values(@card4i,1);
insert into dbo.set_lists (card_id, set_id)
values(@card3i,2);
insert into dbo.set_lists (card_id, set_id)
values(@card4i,2);
insert into dbo.set_lists (card_id, set_id)
values(@card5i,2);

  13. При повторном выполнении этого SQL-запроса соединения четырёх таблиц вы увидите все карточки, их переводы и наборы, к которым они относятся. Выполните следующую инструкцию SQL на листе запросов:

    select c.card_id, c.card_name, 
        t.translation_card_language,
        t.translation_card_name, 
        s.set_name, s.set_date
from dbo.cards c,
        dbo.card_translations t,
        dbo.sets s,
        dbo.set_lists l
where c.card_id = t.card_id
    and c.card_id = l.card_id
    and s.set_id = l.set_id
order by s.set_id, c.card_id;

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