Обновление SQL — основные запросы DDL

Вы хотите освежить свои навыки SQL? Если это так, вы пришли в нужное место! Существует пять категорий SQL-запросов — DDL, DML, DQL, DCL и TCL.

DDL (язык определения данных) — это подмножество команд SQL, которые используются для определения или изменения структуры базы данных. Наиболее распространенными командами DDL являются CREATE, ALTER, DROP, RENAME и TRUNCATE. Давайте быстро рассмотрим эти команды с помощью забавного упражнения в Microsoft SQL Server. Итак, приступим!

Сначала нам нужно создать пустую базу данных.

CREATE DATABASE tempdb;

Теперь давайте создадим несколько запросов для добавления таблиц в эту недавно созданную базу данных! Создайте две таблицы — MOVIES и DIRECTORS с указанными ниже ограничениями.

CREATE TABLE movies_T1 (
 movieId int PRIMARY KEY, --PK = movieid. Each value in this column is unique as each value uniquely identifies the entire row. 
 title varchar(35) NOT NULL, --all values in title column are strings of no more than 35 characters. This columns will not accept Null values. 
 releaseDate date NOT NULL, --releaseDate values should not be null
 director int NOT NULL, --director values should not be null
 score decimal(3,2), --movie score is a decimal value of 3 digits in total and 2 digits after decimal point
 CONSTRAINT chk_score_movie CHECK (score BETWEEN 0 AND 5) --movie score values in this column should be between 0 to 5
);

CREATE TABLE directors_T2 (
 directorId int PRIMARY KEY,
 firstName varchar(20) NOT NULL,
 lastName varchar(30) NOT NULL
);

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

• Измените таблицу movies, чтобы создать ограничение внешнего ключа, которое ссылается на таблицы directors.
• Измените таблицу movies, чтобы создать новое ограничение, чтобы гарантировать уникальность названия фильма.

--FK contraint will ensure values in director column in Movies table should be present in directorId in Directors table
--One to Many relation between the two tables as one director in directors table can direct many movies 
ALTER TABLE movies_T1 
 ADD CONSTRAINT fk_movies_directors FOREIGN KEY (director) REFERENCES directors_T2(directorId);

--constraint to ensure all movies have a unique title
ALTER TABLE movies_T1
 ADD CONSTRAINT uq_movies_title UNIQUE (title);

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

INSERT INTO directors_T2 VALUES
 (1010, 'Bob', 'Doe'),
 (1020, 'Jill', 'Condon'),
 (1050, 'Josh', 'Fooley'),
 (2010, 'Ted', 'Mckenzie'),
 (3020, 'Lucy', 'Bell');

--Note: when entering date literals in SQL it is a good practice to put the year first, then month, then day. 
INSERT INTO movies_t1 VALUES 
(110, 'The Lion King 2', '2022-12-02', 3020, 3.50),
(200, 'Beauty and the Beast', '2017-03-12', 1050, 4.20),
(300, 'Toy Story 4', '2019-04-25', 1020, 4.50),
(400, 'Mission Impossible', '2018-06-27', 2010, 5.00),
(500, 'The Secret Life of Pets', '2016-07-02', 1010, 3.90);

--run select statement to verify that all above data was populated
SELECT * FROM movies_T1;
SELECT * FROM directors_T2;

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

SELECT * INTO tempMovies FROM movies_T1;

--Note: The above query does not apply all constraints like PK or FK to the new table.
--When we run below queries, a record with duplicate movieId=110 along with the same movie title (which is unique in movies table) gets populated in this table.
SELECT * from tempMovies;
INSERT INTO tempMovies VALUES 
(110, 'The Lion King 2', '12-02-2022', 3020, 3.50);
SELECT * from tempMovies;

Теперь давайте удалим все записи в таблице tempMovies с помощью команды TRUNCATE. Вы можете помнить, что использовали команду DELETE, чтобы сделать то же самое, но есть много различий между TRUNCATE и DELETE.

TRUNCATE TABLE tempMovies;

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

--we have to delete movies table first and then dircetors table
DROP TABLE directors_T2;
DROP TABLE movies_T1;

Вот шпаргалка по этому упражнению для дальнейшего использования. Надеюсь, поможет!

Если вы нашли эту статью полезной...

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