CRUD
Основные операции, которые можно выполнять с данными…
C – create – создать/добавить данные
R – read – отобразить данные
U – update – изменить данные
D – delete – удалить данные
Символ * означает все столбцы, если используется в начале (после SELECT, UPDATE, INSERT, DELETE). Далее это означает умножение. Математические символы + – * /
1.1 Добавление данных
INSERT INTO — Добавить данные. После этого указываем столбцы, в которые вставляем данные
VALUES — после этого слова указываем данные, которые хотим добавить. Порядок должен быть таким же, как порядок столбцов, указанных после INSERT INTO
Пример:
INSERT INTO users (id, first_name, last_name, birthday)
VALUES (6, ‘Martin’, ‘Ljuter’, ‘2001-04-14’);
1.2 Отобразить данные
SELECT — показывает данные
FROM — из какой таблицы
- — показывает все столбцы. Напишите * или необходимое имя столбца.
WHERE — логическое ГДЕ. Указываем перед условиями. Например, …WHERE age > 18; Используется с SELECT, UPDATE, DELETE.
Схема:
SELECT * FROM table_name; — показывает все столбцы таблицы
SELECT column1, column2 FROM table_name; — показывает только те столбцы, которые указаны в запросе.
Примеры:
SELECT * FROM users; — SELECT показывает результаты из таблицы «users» (все столбцы таблицы).
SELECT last_name, first_name, birthday
FROM users; — показывает содержимое столбцов (last_name, first_name и birthday) из таблицы users.
Функции SELECT:
COUNT() — показывает количество строк в столбце
AVG() — среднее значение
SUM() — показывает сумму
Примеры:
SELECT COUNT(age) FROM users; — Показывает общее количество строк, а не список (например, 5).
SELECT AVG(age) FROM users; — показывает средний возраст, а не список.
1.3 Изменение данных
UPDATE — изменение / замена
UPDATE products // замена в таблице «products»
SET name = ‘taldrik’ // устанавливаем «name» в столбце «taldrik»
WHERE name = ‘talbrik’; // там, где в столбце “name” есть “talbrik”
UPDATE products
SET count = 10, // устанавливаем количество 10
price = price + 5 // устанавливаем новую цену в столбце “price” (текущая цена +5)
WHERE id=4; // там, где значение столбца id равно 4
делаем количество для ID 4 равным 10 и устанавливаем новую цену (текущая цена + 5)
1.4.1 Удаление данных
DELETE — удаление содержимого таблицы.
IS NULL — NULL или пусто
IS NOT NULL — в ячейке таблицы есть данные
DELETE FROM products WHERE count = 0
DELETE FROM users WHERE last_name IS NULL; Еще есть IS NOT NULL (не пусто)
DELETE FROM users
1.4.2 Удаление таблицы
DROP TABLE users
- Еще несколько операций с данными и условий
Вы знаете WHERE, IS, IS NOT, NULL, давайте посмотрим еще
2.1 Фильтрация данных
AND — логическое И
OR — логическое ИЛИ
NOT — логическое НЕ
Схема:
SELECT столбец1, столбец2
FROM имятаблицы WHERE условие1 AND условие2 OR условие3; — показывает столбцы, где выполнены условие1 и условие2 одновременно, или существует условие3. SELECT столбец1, столбец2 FROM имятаблицы
WHERE NOT условие1; — показывает только те столбцы, которые указаны в запросе.
Примеры:
SELECT last_name, first_name, birthday
FROM users
WHERE age > 18; — Все старше 18 лет
SELECT last_name, first_name, birthday
FROM users
WHERE age >= 18
AND sex = ‘m’; — Мужчины и все старше 18 лет
SELECT last_name, first_name, birthday
FROM users
WHERE age > 18
OR sex = ‘m’; — Женщины старше 18 лет и все мужчины
SELECT last_name, first_name, birthday
FROM users
WHERE NOT age > 18; — Возраст до 18 лет.
Сокращения:
сокращение с IN
Обычный
SELECT * FROM products
WHERE country = ‘EE’ OR country = ‘FI’ OR country = ‘LT’;
Сокращение
SELECT * FROM products
WHERE country IN (‘EE’, ‘FI’, ‘LT’);
сокращение с BETWEEN
Обычный
SELECT * FROM products
WHERE price >= 10
AND price <=20;
Сокращение
SELECT * FROM products
WHERE price BETWEEN 10 AND 20; — товар от 10 до 20 евро включительно.
2.2 Сортировка
ORDER BY — сортировка
ASC — обычная сортировка (A-Z), не обязательно указывать в запросе.
DESC — DESC означает сортировка с конца к началу (Z-A)
Примеры:
SELECT last_name, first_name
FROM users
ORDER BY last_name; — сортировка по фамилии (last_name).
SELECT last_name, first_name, birthday
FROM users
ORDER BY last_name, first_name; — сортировка по фамилии и затем по имени.
SELECT last_name, first_name
FROM users
ORDER BY birthday;
SELECT last_name, first_name
FROM users
WHERE age >= 18
ORDER BY birthday DESC;
SELECT last_name, first_name, birthday
FROM users
WHERE age >= 18
ORDER BY last_name, birthday DESC;
UPDATE products // замена в таблице “products”
SET price = price * 0.8 // устанавливаем новую цену в столбце “price” (текущая цена умножается на 0,8)
ORDER BY price DESC LIMIT 1;
умножаем цену на 0,8, затем сортируем от самой высокой цены к самой низкой и берем первую запись. Даем скидку самому дорогому товару. Здесь * означает умножение.
2.3 Ограничения
LIMIT — ограничение
LIMIT 5 – отображает только первые 5 строк таблицы
LIMIT 20,5 – пропускает первые 20 строк таблицы и показывает следующие 5.
Примеры:
SELECT *
FROM products
ORDER BY price DESC
LIMIT 4; — отображает последние 4 самых дорогих товара
SELECT *
FROM products
WHERE count > 0
ORDER BY price DESC
LIMIT 6; — отображает последние 6 товаров на складе, где количество больше нуля.
SELECT *
FROM products
WHERE count > 0
ORDER BY price DESC
LIMIT 6,3; — пропускает первые 6 и показывает следующие 3.
ПРАКТИЧЕСКИЕ ЗАДАНИЯ
Скачайте и выполните «ИМПОРТ» в вашу базу данных. https://mega.nz/file/GxRyBDCJ#rnnFVmXRm0rI4du83bsfTJypN2LkOT-s3qCSOLSgPp0
В вашей базе данных три новые таблицы, но в них немного больше данных. =)
Задание #1.
Таблица City.
Сколько записей с параметрами: население города (Population) 1500–200000 и их код страны (CountryCode) ‘KIR’
Задание #2
Таблица country
У скольких стран есть год независимости (IndepYear)?
Задание #3
Таблица countrylanguage
Конечно, основной язык в Эстонии — эстонский. На втором месте — русский. Найдите, какой язык на третьем месте (из базы данных, не из Google). Вам помогут столбцы CountryCode и percentage.
Задание #4
Таблица City
Какие города из Эстонии есть в таблице?
Задание #5
Таблица Country
Сколько стран являются Республиками (GovernmentForm)?
Задание #6
Таблица countrylanguage
Сколько стран используют голландский язык (Dutch)?
Задание #7
Таблица City
CountryCode NLD, и население больше 500000. Назовите эти города.
Задание #8
Таблица Country
Год независимости страны Судан
Задание #9
Таблица CountryLanguage
Какие языки используются в стране с кодом MTQ
Ответы:
SELECT COUNT(ID)
FROM city
WHERE Population BETWEEN 1500 AND 200000
AND CountryCode = ‘KIR’;
SELECT COUNT(Code)
FROM country
WHERE IndepYear IS NOT NULL;
SELECT * FROM countrylanguage
WHERE CountryCode = ‘EST’
ORDER BY percentage DESC LIMIT 2,1;
Таблица City
SELECT Name FROM city WHERE CountryCode = ‘EST’;
Таллин, Тарту
Таблица Country
SELECT COUNT(Name) FROM country WHERE GovernmentForm = ‘Republic’;
122
Таблица CountryLanguage
SELECT COUNT(Language) FROM countrylanguage WHERE Language = ‘Dutch’;
5
Таблица City
С
колько городов с населением больше или равно 1000000?
SELECT COUNT(Name) FROM city WHERE Population >= 1000000;
238
AND означает, что оба условия должны быть выполнены одновременно
Из таблицы city.
Сколько (их количество) городов с населением больше или равно 2000000 и те города, где население меньше 100000 человек.
SELECT COUNT(Name) FROM city
WHERE Population >= 2000000
OR Population < 100000;
609
Таблица City
CountryCode NLD, и население больше 500000. Назовите эти города.
SELECT Name FROM city WHERE CountryCode = ‘NLD’ AND Population > 500000;
Амстердам, Роттердам
Таблица Country
Год независимости страны Судан
SELECT IndepYear FROM country WHERE Name = ‘Sudan’;
1956
Таблица CountryLanguage
Какие языки используются в стране с кодом MTQ
SELECT Language FROM countrylanguage WHERE CountryCode = ‘MTQ’;
Creole French, French
