SQL - Урок 4. Избор на данни - оператор SELECT
И така, в базата данни на нашия форум има три таблици: потребители (потребители), теми (теми) и публикации (съобщения). И ние искаме да видим какви данни съдържат. За да направите това, има оператор в SQL ИЗБЕРЕТЕ. Синтаксисът за използването му е както следва:SELECT select_what FROM select_from;
Вместо "what_to_select", трябва да посочим или името на колоната, чиито стойности искаме да видим, или имената на няколко колони, разделени със запетаи, или знака звездичка (*), което означава избор на всички колони на масата. Вместо "from_choose" трябва да посочите името на таблицата.
Нека първо да разгледаме всички колони от таблицата потребители:
ИЗБЕРЕТЕ * ОТ потребители;
Това са всички наши данни, които въведохме в тази таблица. Но да предположим, че искаме да разгледаме само колоната id_user (например в последния урок трябваше да знаем какво представляват id_users в таблицата с потребители, за да попълним таблицата с теми). За да направите това, ще посочим името на тази колона в заявката:
ИЗБЕРЕТЕ id_user FROM потребители;
Е, ако искаме да видим, например, имената и имейлите на нашите потребители, тогава ще изброим колоните, представляващи интерес, разделени със запетаи:
ИЗБЕРЕТЕ име, имейл ОТ потребители;
По същия начин можете да видите какви данни съдържат другите ни таблици. Да видим какви теми имаме:
ИЗБЕРЕТЕ * ОТ теми;
Сега имаме само 4 теми, а ако са 100 от тях? Бих искал да се показват, например, по азбучен ред. Има ключова дума за това в SQL. ПОДРЕДЕНИ ПОпоследвано от името на колоната, по която ще се извърши сортирането. Синтаксисът е следният:
SELECT име на_колона ОТ име_на_таблица ORDER BY име_на_колона_сортиране;
Сортирането по подразбиране е възходящо, но това може да се промени чрез добавяне на ключовата дума ОПИСАНИЕ
Сега нашите данни са сортирани в низходящ ред.
Можете да сортирате по няколко колони наведнъж. Например следната заявка ще сортира данните по колоната topic_name и ако има няколко еднакви реда в тази колона, тогава колоната id_author ще бъде сортирана в низходящ ред:
Сравнете резултата с резултата от предишната заявка.
Много често не се нуждаем от цялата информация от таблицата. Например, искаме да знаем кои теми са създадени от потребителя sveta (id=4). Има ключова дума за това в SQL. КЪДЕТО, синтаксисът за такава заявка е както следва:
За нашия пример условието е потребителският идентификатор, т.е. искаме само редове, които имат 4 в колоната id_author (потребителски идентификатор sveta):
Или искаме да знаем кой е създал темата "велосипеди":
Разбира се, би било по-удобно да се показва името на автора вместо идентификатора на автора, но имената се съхраняват в друга таблица. В следващите уроци ще научим как да избираме данни от множество таблици. Междувременно нека научим какви условия могат да бъдат зададени с помощта на ключовата дума WHERE.
| Оператор | Описание |
| = (равно) | Избраните стойности са равни на посочените Пример: ИЗБЕРЕТЕ * ОТ теми WHERE id_author=4; Резултат:
|
| > (още) | Избрани са стойности, по-големи от посочените Пример: SELECT * FROM topics WHERE id_author>2; Резултат:
|
| < (меньше) | Избрани са стойности, по-малки от посочените Пример: ИЗБЕРЕТЕ * ОТ теми WHERE id_author
|
| >= (по-голямо или равно на) | Избират се стойности, по-големи или равни на зададената стойност. Пример: ИЗБЕРЕТЕ * ОТ теми WHERE id_author>=2; Резултат:
|
| <= (меньше или равно) | Избрани са стойности, по-малки или равни на зададената стойност. Пример: ИЗБЕРЕТЕ * ОТ теми WHERE id_author
|
| != (не е равно) | Избрани са стойности, които не са равни на посочените Пример: ИЗБЕРЕТЕ * ОТ теми WHERE id_author!=1; Резултат:
|
| НЕ Е НУЛЕВ | Избират се редове, които имат стойности в посоченото поле Пример: SELECT * FROM topics WHERE id_author IS NOT NULL; Резултат:
|
| Е НУЛЕВ | Селектират се редове, които нямат стойност в указаното поле Пример: SELECT * FROM topics WHERE id_author IS NULL; Резултат:
|
| МЕЖДУ (между) | Избират се стойности между посочените стойности. Пример: SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3; Резултат:
|
| IN (съдържаща се стойност) | Стойностите, съответстващи на посочените Пример: SELECT * FROM topics WHERE id_author IN (1, 4); Резултат:
|
| НЕ В (стойността не се съдържа) | Избрани стойности, различни от посочените Пример: SELECT * FROM topics WHERE id_author NOT IN (1, 4); Резултат:
|
| ХАРЕСВАНЕ (съвпадение) | Избрани са примерни стойности Пример: SELECT * FROM topics WHERE име_на_тема LIKE "vel%"; Резултат:
|
| НЕ КАТО | Избрани са стойности, които не отговарят на извадката Пример: SELECT * FROM topics WHERE име_на_тема NOT LIKE "vel%"; Резултат:
|
Празен набор - няма такива низове.
Възможните метасимволи на оператора LIKE ще бъдат обсъдени по-долу.
Метазнаци на оператор LIKE
Търсенето на метазнаци може да се извършва само в текстови полета.Най-често срещаният метасимвол е % . Това означава всякакви знаци. Например, ако искаме да намерим думи, които започват с буквите "vel", тогава ще напишем КАТО "vel%", а ако искаме да намерим думи, които съдържат знаците "club", тогава ще напишем КАТО "% клуб%". Например:
Друг често използван метазнак е _ . За разлика от %, който обозначава малко или никакви знаци, долната черта обозначава точно един знак. Например:
Обърнете внимание на интервала между метасимвола и "риба", ако го пропуснете, заявката няма да работи, т.к. метазнак _ означава точно един символ и интервалът също е знак.
Стига за днес. В следващия урок ще научим как да правим заявки за две или повече таблици. Междувременно опитайте да направите свои собствени заявки към таблицата с публикации (съобщения).
Този урок е за SQL заявкикъм базата данни на VBA достъп. Ще разгледаме как се изпълняват VBA заявките INSERT, UPDATE, DELETE към базата данни и също така ще научим как да получим конкретна стойност от SELECT заявка.
Тези, които програмират VBA достъпи докато работят с база данни на SQL сървър, те често се сблъскват с толкова проста и необходима задача като изпращане на SQL заявка към базата данни, било то INSERT, UPDATE или проста SQL SELECT заявка. И тъй като сме начинаещи програмисти, трябва да можем и това, така че днес ще направим точно това.
Вече засегнахме темата за получаване на данни от SQL сървър, където написахме код във VBA за получаване на тези данни, например в статия за Качване на данни в текстов файл от MSSql 2008 или също засегнахме малко в материала Качване на данни от Access в шаблон на Word и Excel, но по един или друг начин го разгледахме повърхностно и днес предлагам да поговорим за това малко по-подробно.
Забележка! Всички примери по-долу се обсъждат с помощта на ADP проект на Access 2003 и база данни MSSql 2008.
Изходни данни за примери
Да кажем, че имаме таблица test_table, която ще съдържа числата и имената на месеците в годината (заявките се правят с мениджмънт студио)
СЪЗДАВАНЕ НА ТАБЛИЦА .( НЕ NULL, (50) NULL) В ДВИЖЕНИЕ
Както казах, ще използваме ADP проект, конфигуриран да работи с MS SQL 2008, в който създадох тестова форма и добавих бутон за стартиране с надпис "Бягай", които ще ни трябват, за да тестваме нашия код, т.е. ще напишем целия код в манипулатора на събития " Натискане на бутон».
Заявки за база данни INSERT, UPDATE, DELETE във VBA
За да не го протакаме дълго време, нека започнем, да кажем, че трябва да добавим ред към нашата тестова таблица ( кодът е коментиран)/
Private Sub start_Click() "Декларирайте променлива за съхраняване на низа на заявката Dim sql_query As String "Напишете заявката, от която се нуждаем, в нея sql_query = "INSERT INTO test_table (id, name_mon) VALUES ("6", "June")" "Изпълнете го с DoCmd.RunSQL sql_query End Sub
В този случай заявката се изпълнява с помощта на текущите настройки за връзка с базата данни. Можем да проверим дали данните са добавени или не.
Както можете да видите, данните са въведени.
За да изтрием един ред, пишем следния код.
Private Sub start_Click() "Декларирайте променлива за задържане на низа на заявката Dim sql_query As String "Напишете заявка за изтриване в нея sql_query = "ИЗТРИЙТЕ test_table WHERE id = 6" "Изпълнете я DoCmd.RunSQL sql_query End Sub
Ако проверим, ще видим, че желаният ред е изтрит.
За да актуализираме данните, пишем заявка за актуализиране на променливата sql_query, надявам се, че значението е ясно.
SELECT заявка към база данни във VBA
Тук нещата са малко по-интересни, отколкото с други SQL конструкции.
Първо, да речем, че трябва да получим всички данни от таблицата и например ще ги обработим и покажем в съобщение, а вие, разбира се, можете да ги използвате за други цели, за това пишем следното код
Private Sub start_Click() "Деклариране на променливи "За набор от записи от базата данни Dim RS As ADODB.Recordset "Низ на заявка Dim sql_query As String "Низ за показване на общи данни в съобщението Dim str As String "Създаване на нов обект за набор от записи RS = Нов ADODB .Recordset "Низ на заявка sql_query = "ИЗБЕРЕТЕ id, name_mon ОТ test_table" "Изпълнете заявка, като използвате текущите настройки за връзка на проекта, за да покажете съобщение str = str & RS.Fields("id") & "-" & RS. Fields("name_mon") & vbnewline "отидете на следващия запис RS.MoveNext Wend "Изходно съобщение msgbox str End Sub
Тук вече използваме VBA Access Loops, за да преминем през всички стойности в нашия набор от записи.
Но доста често е необходимо да се получат не всички стойности от набор от записи, а само една, например името на месеца по неговия код. И за това използването на цикъл е някак си скъпо, така че можем просто да напишем заявка, която ще върне само една стойност и да се обърне към нея, например, ще получим името на месеца с код 5
Private Sub start_Click() "Деклариране на променливи "За набор от записи от базата данни Dim RS As ADODB.Recordset "Низ на заявка Dim sql_query As String "Низ за показване на крайната стойност Dim str As String "Създаване на нов обект за набор от записи RS = Нов ADODB.Recordset "Низ на заявка sql_query = "ИЗБЕРЕТЕ name_mon FROM test_table WHERE id = 5" "Изпълнете заявка, като използвате текущите настройки за връзка на проекта RS.open sql_query, CurrentProject.Connection, adOpenDynamic, adLockOptimistic "Вземете нашата стойност str = RS.Fields(0 ) msgbox str end sub
За универсалност тук вече се обърнахме не по името на клетката, а по нейния индекс, т.е. 0, което е първата стойност в Набор от записи, в крайна сметка получихме стойността "Може".
Както можете да видите, всичко е съвсем просто. Ако често трябва да получите конкретна стойност от базата данни ( както в последния пример), тогава ви препоръчвам да изведете целия код в отделна функция (Как да напиша функция във VBA Access 2003) с един входен параметър, например кода на месеца ( имайки предвид нашия пример) и просто, където е необходимо да се покаже тази стойност, извикайте функцията, от която се нуждаем, с необходимия параметър и това е всичко, ние значително ще намалим VBA кода и ще подобрим възприемането на нашата програма.
Това е всичко за днес. Късмет!
Описание на образователния проект "Магазин"Схема за свързване на таблица
Описание на таблиците
m_category - продуктови категории
m_income - получаване на стоки
m_outcome - потребление на стоки
m_product - директория, описание на продукта
m_supplier - директория; информация за доставчика
m_unit - директория; единици
За да тествате на практика примерите, дадени в този урок, трябва да разполагате със следния софтуер:
Microsoft Access 2003 или по-нова версия.
SQL заявка в MS Access. Започнете
За да видите съдържанието на таблица, щракнете двукратно върху името на таблицата в левия панел:
За да превключите в режим на редактиране на полето на таблицата, изберете режим на проектиране в горния панел:
За да покажете резултата от SQL заявка, щракнете два пъти върху името на заявката в левия панел:
За да превключите към режима за редактиране на SQL заявка, изберете режима SQL в горния панел:
SQL заявка. Примери в MS Access. ИЗБЕРЕТЕ: 1-10
В SQL заявка операторът SELECT се използва за избор от таблици на база данни.SQL заявка Q001.Примерна SQL заявка за получаване само на задължителните полета в желаната последователност:
ИЗБЕРЕТЕ dt, product_id, сума
ОТ m_доходи;
SQL заявка Q002.В този пример на SQL заявка знакът звездичка (*) се използва за показване на всички колони на таблицата m_product, с други думи, за получаване на всички полета на релацията m_product:
ИЗБЕРЕТЕ *
ОТ m_product;
ЗаявкаSQLQ003.Изявлението DISTINCT се използва за премахване на дублиращи се записи и получаване на много уникални записи:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id
ОТ m_доходи;
SQL заявка Q004.Операторът ORDER BY се използва за сортиране (подреждане) на записи по стойностите на определено поле. Името на полето следва клаузата ORDER BY:
ИЗБЕРЕТЕ *
ОТ m_доход
ПОРЪЧАЙ ПО цена;
SQL заявка Q005.Операторът ASC се използва в допълнение към оператора ORDER BY и се използва за дефиниране на сортиране във възходящ ред. Операторът DESC се използва в допълнение към оператора ORDER BY и се използва за дефиниране на сортиране в низходящ ред. В случай, че не са посочени нито ASC, нито DESC, се приема наличието на ASC (по подразбиране):
ИЗБЕРЕТЕ *
ОТ m_доход
ПОРЪЧКА ПО dt DESC , цена;
SQL заявка Q006.За да изберете необходимите записи от таблицата, се използват различни логически изрази, които изразяват условието за избор. Булевият израз идва след клаузата WHERE. Пример за получаване от таблицата m_income на всички записи, за които стойността на сумата е по-голяма от 200:
ИЗБЕРЕТЕ *
ОТ m_доход
WHERE сума>200;
SQL заявка Q007.За изразяване на сложни условия се използват логическите операции И (конюнкция), ИЛИ (дизюнкция) и НЕ (логическо отрицание). Пример за получаване от таблицата m_outcome на всички записи, за които стойността на сумата е 20 и стойността на цената е по-голяма или равна на 10:
цена
ОТ m_резултат
WHERE сума=20 И цена>=10;
SQL заявка Q008.За да обедините данни от две или повече таблици, използвайте инструкциите INNER JOIN, LEFT JOIN, RIGHT JOIN. Следващият пример извлича полетата dt, product_id, сума, цена от таблицата m_income и полето за заглавие от таблицата m_product. Записът на таблицата m_income е свързан със записа на таблицата m_product, когато стойността на m_income.product_id е равна на стойността на m_product.id:
НА m_income.product_id=m_product.id;
SQL заявка Q009.Има две неща, на които трябва да обърнете внимание в тази SQL заявка: 1) текстът за търсене е ограден в единични кавички ("); 2) датата е във формат #месец/ден/година#, който е правилен за MS Достъп В други системи форматът на датата може да е различен. Пример за показване на информация за получаването на мляко на 12 юни 2011 г. Обърнете внимание на формата на датата #6/12/2011#:
ИЗБЕРЕТЕ dt, product_id, заглавие, сума, цена
ОТ m_income INNER JOIN m_product
WHERE title="Мляко" And dt=#6/12/2011#; !}
SQL заявка Q010.Инструкцията BETWEEN се използва за проверка дали диапазон от стойности принадлежи към нея. Примерна SQL заявка, показваща информация за стоки, получени между 1 юни и 30 юни 2011 г.:
ИЗБЕРЕТЕ *
ОТ m_income INNER JOIN m_product
НА m_income.product_id=m_product.id
WHERE dt МЕЖДУ #6/1/2011# и #6/30/2011#;
SQL заявка. Примери в MS Access. ИЗБЕРЕТЕ: 11-20
Една SQL заявка може да бъде вложена в друга. Подзаявката не е нищо повече от заявка в заявка. Обикновено подзаявка се използва в клауза WHERE. Но има и други начини за използване на подзаявки.Заявка Q011.Показва информация за продуктите от таблицата m_product, чиито кодове също са в таблицата m_income:
ИЗБЕРЕТЕ *
ОТ m_product
WHERE id IN (ИЗБЕРЕТЕ product_id FROM m_income);
Заявка Q012.Показва се списък с продукти от таблицата m_product, чиито кодове не са в таблицата m_outcome:
ИЗБЕРЕТЕ *
ОТ m_product
WHERE id NOT IN (ИЗБЕРЕТЕ product_id FROM m_outcome);
Заявка Q013.Тази SQL заявка връща уникален списък с кодове и имена на продукти, които имат кодове в таблицата m_income, но не и в таблицата m_outcome:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id, заглавие
ОТ m_income INNER JOIN m_product
НА m_income.product_id=m_product.id
WHERE product_id NOT IN (ИЗБЕРЕТЕ product_id FROM m_outcome);
Заявка Q014.От таблицата m_category се показва уникален списък с категории, чиито имена започват с буквата M:
ИЗБЕРЕТЕ ОТДЕЛНО заглавие
ОТ m_product
WHERE заглавие LIKE "M*";
Заявка Q015.Пример за извършване на аритметични операции върху полета в заявка и преименуване на полета в заявка (псевдоним). Този пример изчислява разход = количество*цена и печалба за всеки запис на консумация на артикул, като се приема, че печалбата е 7 процента от продажбите:
сума*цена/100*7 AS печалба
ОТ m_резултат;
Заявка Q016.Чрез анализиране и опростяване на аритметичните операции можете да увеличите скоростта на изпълнение на заявката:
ИЗБЕРЕТЕ dt, product_id, сума, цена, сума*цена КАТО сума_на резултата,
сума_изход*0,07 AS печалба
ОТ m_резултат;
Заявка Q017.С помощта на оператора INNER JOIN можете да комбинирате данни от множество таблици. В следващия пример, в зависимост от стойността на ctgry_id, всеки запис в таблицата m_income се съпоставя с името на категорията от таблицата m_category, към която принадлежи продуктът:
ИЗБЕРЕТЕ c.title, b.title, dt, сума, цена, сума*цена КАТО сума_доход
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;
Заявка Q018.Функции като SUM - сума, COUNT - количество, AVG - средно аритметично, MAX - максимална стойност, MIN - минимална стойност се наричат агрегатни функции. Те приемат множество стойности и връщат една стойност, когато се обработват. Пример за изчисляване на сумата от произведението на полетата сума и цена с помощта на агрегатната функция SUM:
ИЗБЕРЕТЕ СУМА(сума*цена) КАТО Обща_сума
ОТ m_доходи;
Заявка Q019.Пример за използване на множество агрегатни функции:
ИЗБЕРЕТЕ Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,
MAX(сума) AS Amount_Max, Min(сума) AS Amount_Min,
Брой(*) КАТО общ_брой
ОТ m_доходи;
Заявка Q020.В този пример се изчислява сборът на всички елементи с код 1, получени през юни 2011 г.:
ИЗБЕРЕТЕ Сума(сума*цена) КАТО сума_доход
ОТ m_доход
WHERE product_id=1 И dt BETWEEN #6/1/2011# AND #6/30/2011#;.
Заявка Q021.Следната SQL заявка изчислява за колко са продадени стоките с код 4 или 6:
ИЗБЕРЕТЕ Сума(сума*цена) като сума_изход
ОТ m_резултат
WHERE product_id=4 OR product_id=6;
Заявка Q022.Изчислява се за какво количество са продадени на 12.06.2011 г. стоки с код 4 или 6:
ИЗБЕРЕТЕ Сума(сума*цена) КАТО резултат_сума
ОТ m_резултат
WHERE (product_id=4 ИЛИ product_id=6) И dt=#6/12/2011#;
Заявка Q023.Задачата е следната. Изчислете общата сума, за която са кредитирани стоките от категорията "Печени продукти".
За да разрешите този проблем, трябва да работите с три таблици: m_income, m_product и m_category, защото:
- количеството и цената на кредитираните стоки се съхраняват в таблицата m_income;
- кодът на категорията на всеки продукт се съхранява в таблицата m_product;
- името на заглавието на категорията се съхранява в таблицата m_category.
За да разрешим този проблем, използваме следния алгоритъм:
- определяне на код на категория "Печени изделия" от таблица m_category чрез подзаявка;
- обединяване на таблиците m_income и m_product за определяне на категорията на всеки кредитиран продукт;
- изчисляване на сумата на получаване (= количество * цена) за стоки, чийто код на категория е равен на кода, определен от горната подзаявка.
ИЗБЕРЕТЕ
FROM m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id
WHERE ctgry_id = (SELECT id FROM m_category WHERE title="Печени изделия"); !}
Заявка Q024.Проблемът с изчисляването на общата сума на кредитираните стоки от категорията "Печени продукти" ще бъде решен чрез следния алгоритъм:
- всеки запис от таблицата m_income, в зависимост от стойността на неговия product_id, от таблицата m_category, съответства на името на категорията;
- изберете записи, за които категорията е равна на "Печени продукти";
- изчислете размера на приходите = количество * цена.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Печени изделия";
!}
Заявка Q025.Този пример изчислява колко артикула са били консумирани:
SELECT COUNT(product_id) AS product_cnt
FROM (ИЗБЕРЕТЕ ОТДЕЛЕН product_id FROM m_outcome) AS t;
Заявка Q026.Клаузата GROUP BY се използва за групиране на записи. Обикновено записите се групират по стойността на едно или повече полета и към всяка група се прилага обобщена операция. Например, следната заявка генерира отчет за продажбата на стоки. Тоест генерира се таблица, която ще съдържа имената на стоките и сумата, за която се продават:
ИЗБЕРЕТЕ заглавие, СУМА(сума*цена) КАТО резултат_сума
FROM m_product AS a INNER JOIN m_outcome AS b
НА a.id=b.product_id
ГРУПИРАНЕ ПО заглавие;
Заявка Q027.Отчет за продажбите по категории. Тоест, генерира се таблица, която ще съдържа имената на категориите продукти, общата сума, за която се продават стоките от тези категории, и средната сума на продажбите. Функцията ROUND се използва за закръгляване на средната стойност до най-близката стотна (втория десетичен знак след десетичния разделител):
ИЗБЕРЕТЕ c.title, SUM(сума*цена) AS сума_изход,
ROUND(СР.(сума*цена),2) КАТО резултат_сума_ср
FROM (m_product AS a INNER JOIN m_outcome AS b ON a.id=b.product_id)
INNER JOIN m_category AS c ON a.ctgry_id=c.id
ГРУПИРАНЕ ПО c.title;
Заявка Q028.За всеки продукт се изчислява общият и среден брой на неговите постъпления и се извежда информация за стоките, чийто общ брой на постъпленията е минимум 500:
ИЗБЕРЕТЕ product_id, SUM(сума) КАТО сума_сума,
Кръгла(Ср.(сума),2) КАТО сума_ср
ОТ m_доход
ГРУПИРАНЕ ПО product_id
HAVING Sum(amount)>=500;
Заявка Q029.Тази заявка изчислява за всеки артикул сумата и средната стойност на неговите приходи, направени през второто тримесечие на 2011 г. Ако общата сума на получаване на стоки е не по-малка от 1000, тогава се показва информация за този продукт:
ИЗБЕРЕТЕ заглавие, СУМА(сума*цена) КАТО сума_доход
FROM m_income a INNER JOIN m_product b ON a.product_id=b.id
WHERE dt МЕЖДУ #4/1/2011# И #6/30/2011#
ГРУПИРАНЕ ПО заглавие
HAVING SUM(сума*цена)>=1000;
Заявка Q030.В някои случаи е необходимо да се съпостави всеки запис от дадена таблица с всеки запис от друга таблица; което се нарича декартово произведение. Таблицата, получена в резултат на такова свързване, се нарича таблица на Декарт. Например, ако някоя таблица A има 100 записа, а таблица B има 15 записа, тогава тяхната декартова таблица ще се състои от 100*15=150 записа. Следната заявка свързва всеки запис в таблицата m_income с всеки запис в таблицата m_outcome:
ОТ м_доход, м_изход;
Заявка Q031.Пример за групиране на записи по две полета. Следната SQL заявка изчислява за всеки доставчик сумата и количеството на стоките, получени от него:
SUM(сума*цена) КАТО сума_приход
Заявка Q032.Пример за групиране на записи по две полета. Следната заявка изчислява за всеки доставчик сумата и количеството на техните продукти, продадени от нас:
ИЗБЕРЕТЕ доставчик_id, product_id, SUM(сума) КАТО сума_сума,
ГРУПИРАНЕ ПО доставчик_id, product_id;
Заявка Q033.В този пример двете заявки по-горе (q031 и q032) се използват като подзаявки. Резултатите от тези заявки се обединяват в един отчет с помощта на метода LEFT JOIN. Следната заявка показва отчет за броя и количеството получени и продадени продукти за всеки доставчик. Трябва да обърнете внимание на факта, че ако някакъв продукт вече е пристигнал, но все още не е продаден, тогава клетката outcome_sum за този запис ще бъде празна. че тази заявка е само пример за използване на относително сложни заявки като подзаявка. Ефективността на тази SQL заявка с голямо количество данни е под въпрос:
ИЗБЕРЕТЕ *
ОТ
SUM(сума*цена) КАТО сума_приход
НА a.product_id=b.id ГРУПИРАНЕ ПО доставчик_id, product_id) AS a
ЛЯВО ПРИСЪЕДИНЯВАНЕ
(ИЗБЕРЕТЕ доставчик_id, product_id, SUM(сума) КАТО сума_сума,
SUM(сума*цена) КАТО резултат_сума
FROM m_outcome AS a INNER JOIN m_product AS b
НА a.product_id=b.id ГРУПИРАНЕ ПО доставчик_id, product_id) AS b
ON (a.product_id=b.product_id) И (a.supplier_id=b.supplier_id);
Заявка Q034.В този пример двете заявки по-горе (q031 и q032) се използват като подзаявки. Резултатите от тези заявки се комбинират в един отчет с помощта на метода RIGTH JOIN. Следната заявка отпечатва отчет за сумата на плащанията на всеки клиент за платежните системи, които е използвал, и сумата на направените от тях инвестиции. Следната заявка показва отчет за броя и количеството получени и продадени продукти за всеки доставчик. Обърнете внимание, че ако даден продукт вече е продаден, но все още не е получен, клетката за сума_доход за този запис ще бъде празна. Наличието на такива празни клетки е индикатор за грешка в отчитането на продажбите, тъй като преди продажбата е необходимо първо да пристигне съответният продукт:
ИЗБЕРЕТЕ *
ОТ
(ИЗБЕРЕТЕ доставчик_id, product_id, SUM(сума) КАТО сума_сума,
SUM(сума*цена) КАТО сума_приход
FROM m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id
ГРУПИРАНЕ ПО доставчик_id, product_id) КАТО a
ДЯСНО ПРИСЪЕДИНЕТЕ
(ИЗБЕРЕТЕ доставчик_id, product_id, SUM(сума) КАТО сума_сума,
SUM(сума*цена) КАТО резултат_сума
FROM m_outcome AS a INNER JOIN m_product AS b ON a.product_id=b.id
ГРУПИРАНЕ ПО доставчик_id, product_id) AS b
ON (a.supplier_id=b.supplier_id) И (a.product_id=b.product_id);
Заявка Q035.Извежда се справка за размера на приходите и разходите по продукти. За да направите това, се създава списък с продукти според таблиците m_income и m_outcome, след което за всеки продукт от този списък се изчислява сумата от неговите приходи според таблицата m_income и сумата от неговите разходи според таблицата m_outcome:
ИЗБЕРЕТЕ product_id, SUM(в_сума) КАТО сума_на_прихода,
SUM(out_amount) КАТО сума_изход
ОТ
(ИЗБЕРЕТЕ product_id, сума КАТО in_amount, 0 AS out_amount
ОТ m_доход
СЪЮЗ ВСИЧКИ
ИЗБЕРЕТЕ product_id, 0 AS in_amount, сума AS out_amount
ОТ m_резултат) КАТО t
ГРУПИРАНЕ ПО product_id;
Заявка Q036.Функцията EXISTS връща TRUE, ако предаваният към нея набор съдържа елементи. Функцията EXISTS връща FALSE, ако наборът, който й е предаден, е празен, тоест няма елементи. Следната заявка връща продуктовите кодове, които се съдържат както в таблицата m_income, така и в таблицата m_outcome:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id
ОТ m_доход КАТО a
WHERE EXISTS(SELECT product_id FROM m_outcome AS b
Заявка Q037.Показани са продуктови кодове, които се съдържат както в таблицата m_income, така и в таблицата m_outcome:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id
ОТ m_доход КАТО a
WHERE product_id IN (ИЗБЕРЕТЕ product_id FROM m_outcome)
Заявка Q038.Показани са продуктови кодове, които се съдържат както в таблицата m_income, но не се съдържат в таблицата m_outcome:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id
ОТ m_доход КАТО a
WHERE NOT EXISTS(SELECT product_id FROM m_outcome AS b
WHERE b.product_id=a.product_id);
Заявка Q039.Показва се списък с продукти с най-висока сума на продажбите. Алгоритъмът е следният. За всеки продукт се изчислява сумата от неговите продажби. След това се определя максимумът от тези суми. След това за всеки продукт отново се изчислява сумата на неговите продажби и се показват кодът и сумата на продажбите на стоки, чиято сума на продажбите е равна на максимума:
ИЗБЕРЕТЕ product_id, SUM(сума*цена) КАТО сума_сума
ОТ m_резултат
ГРУПИРАНЕ ПО product_id
ИМАЩ СУМА(сума*цена) = (ИЗБЕРЕТЕ МАКС(s_сума)
FROM (SELECT SUM(сума*цена) AS s_сума FROM m_outcome GROUP BY product_id));
Заявка Q040.Запазената дума IIF (условен оператор) се използва за оценка на логически израз и извършване на действие в зависимост от резултата (TRUE или FALSE). В следния пример доставката на артикул се счита за "малка", ако количеството е по-малко от 500. В противен случай, т.е. количеството на разписката е по-голямо или равно на 500, доставката се счита за "голяма":
ИЗБЕРЕТЕ dt, product_id, сума,
IIF(сума ОТ m_доход;
SQL заявка Q041.В случай, че командата IIF се използва повече от веднъж, е по-удобно да се замени с командата SWITCH. Операторът SWITCH (оператор с множествен избор) се използва за оценка на логически израз и извършване на действие в зависимост от резултата. В следващия пример доставената партида се счита за „малка“, ако количеството стоки в партидата е по-малко от 500. В противен случай, т.е. ако количеството стоки е по-голямо или равно на 500, партидата се счита за „голяма ":
ИЗБЕРЕТЕ dt, product_id, сума,
SWITCH(сума =500,"голям") AS знак
ОТ m_доходи;
Заявка Q042.В следващата заявка, ако количеството стоки във входящата партида е по-малко от 300, тогава партидата се счита за "малка". В противен случай, т.е. ако условието сума ИЗБЕРЕТЕ dt, product_id, сума,
IIF(сума IIF(сума ОТ m_доход;
SQL заявка Q043.В следващата заявка, ако количеството стоки във входящата партида е по-малко от 300, тогава партидата се счита за "малка". В противен случай, т.е. ако условието сума ИЗБЕРЕТЕ dt, product_id, сума,
SWITCH(сума сума сума>=1000,"голям") AS знак
ОТ m_доходи;
SQL заявка Q044.В следната заявка продажбите са разделени на три групи: малки (до 150), средни (от 150 до 300), големи (300 и повече). След това за всяка група се изчислява общата сума:
ИЗБЕРЕТЕ категория, SUM(резултат_сума) AS Ctgry_Total
ОТ (ИЗБЕРЕТЕ сума*цена КАТО сума_изход,
IIf(сума*цена IIf(сума*цена ОТ m_резултат) AS t
ГРУПИРАНЕ ПО категория;
SQL заявка Q045.Функцията DateAdd се използва за добавяне на дни, месеци или години към дадена дата и получаване на нова дата. Следваща заявка:
1) добавя 30 дни към датата от полето dt и показва новата дата в полето dt_plus_30d;
2) добавете 1 месец към датата от полето dt и покажете новата дата в полето dt_plus_1m:
ИЗБЕРЕТЕ dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
ОТ m_доходи;
SQL заявка Q046.Функцията DateDiff е предназначена да изчислява разликата между две дати в различни единици (дни, месеци или години). Следната заявка изчислява разликата между датата в полето dt и текущата дата в дни, месеци и години:
ИЗБЕРЕТЕ dt, DateDiff("d",dt,Date()) КАТО последен_ден,
DateDiff("m",dt,Date()) КАТО last_months,
DateDiff("yyyy",dt,Date()) КАТО last_years
ОТ m_доходи;
SQL заявка Q047.Броят дни от деня на получаване на стоките (таблица m_income) до текущата дата се изчислява с помощта на функцията DateDiff и датата на изтичане се сравнява (таблица m_product):
DateDiff("d",dt,Date()) КАТО last_days
FROM m_income AS a INNER JOIN m_product AS b
НА a.product_id=b.id;
SQL заявка Q048.Изчислява се броят на дните от датата на получаване на стоките до текущата дата, след което се проверява дали този брой надвишава срока на годност:
ИЗБЕРЕТЕ a.id, product_id, dt, lifedays,
DateDiff("d",dt,Date()) AS last_days, IIf(last_days>lifedays,"Yes","No") AS date_expire
FROM m_income a INNER JOIN m_product b
НА a.product_id=b.id;
SQL заявка Q049.Изчислява се броя на месеците от датата на получаване на стоките до текущата дата. Колоната month_last1 изчислява абсолютния брой месеци, колоната month_last2 изчислява броя на пълните месеци:
ИЗБЕРЕТЕ dt, DateDiff("m",dt,Date()) КАТО month_last1,
DateDiff("m",dt,Date())-iif(day(dt)>day(date()),1,0) AS month_last2
ОТ m_доходи;
SQL заявка Q050.Показана е тримесечна справка за количеството и сумата на получените стоки за 2011 г.:
ИЗБЕРЕТЕ kvartal, SUM(резултат_сума) AS Общо
ОТ (ИЗБЕРЕТЕ сума*цена КАТО сума_изход, месец(dt) КАТО m,
SWITCH(m =10.4) AS квартал
FROM m_income WHERE година(dt)=2011) AS t
ГРУПИРАНЕ ПО блок;
Заявка Q051.Следната заявка помага да разберете дали потребителите са успели да въведат в системата информация за потреблението на стоки за сума, по-голяма от сумата на получаване на стоките:
ИЗБЕРЕТЕ product_id, SUM(in_sum) КАТО доход_sum, SUM(out_sum) AS outcome_sum
FROM (ИЗБЕРЕТЕ product_id, сума*цена като in_sum, 0 като out_sum
от m_income
СЪЮЗ ВСИЧКИ
ИЗБЕРЕТЕ product_id, 0 като in_sum, сума*цена като out_sum
от m_резултат) AS t
ГРУПИРАНЕ ПО product_id
ИМАЩ SUM(in_sum)
Заявка Q052.Номерирането на редовете, върнати от заявката, се реализира по различни начини. Например, можете да преномерирате редовете на отчет, изготвен в MS Access, като използвате самия MS Access. Можете също така да преномерирате, като използвате езици за програмиране, например VBA или PHP. Понякога обаче това трябва да се направи в самата SQL заявка. И така, следната заявка ще номерира редовете на таблицата m_income в съответствие с възходящия ред на стойностите на полето ID:
ИЗБЕРЕТЕ COUNT(*) като N, b.id, b.product_id, b.amount, b.price
FROM m_income a INNER JOIN m_income b ON a.id GROUP BY b.id, b.product_id, b.amount, b.price;
Заявка Q053.Показани са първите пет сред продуктите по обем на продажбите. Изходът на първите пет записа се извършва с помощта на инструкцията TOP:
ИЗБЕРЕТЕ ТОП 5, product_id, sum(amount*price) AS summa
ОТ m_резултат
ГРУПИРАНЕ ПО product_id
ORDER BY сума(сума*цена) DESC;
Заявка Q054.Показани са първите пет сред продуктите по обем на продажбите и редовете са номерирани като резултат:
SELECT COUNT(*) AS N, b.product_id, b.summa
ОТ
FROM m_outcome GROUP BY product_id) КАТО a
ВЪТРЕШНО СЪЕДИНЕНИЕ
(ИЗБЕРЕТЕ product_id, sum(amount*price) AS summa,
summa*10000000+product_id AS id
FROM m_outcome GROUP BY product_id) AS b
НА a.id>=b.id
ГРУПИРАНЕ ПО b.product_id, b.summa
ИМАЩ БРОЙ (*) ПОРЪЧКА ПО БРОЙ (*);
Заявка Q055.Следната SQL заявка показва използването на математическите функции COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:
SELECT (изберете count(*) от m_income) като N, 3.1415926 като pi, k,
2*pi*(k-1)/N като x, COS(x) като COS_, SIN(x) като SIN_, TAN(x) като TAN_,
SQR(x) като SQRT_, x^3 като "x^3", ABS(x) като ABS_
ОТ (ИЗБЕРЕТЕ БРОЙ(*) КАТО k
FROM m_income AS a INNER JOIN m_income AS b ON a.idGROUP BY b.id) t;
SQL заявка. Примери в MS Access. АКТУАЛИЗАЦИЯ: 1-10
Заявка U001.Следната SQL заявка за промяна увеличава цените на елементи с код 3 в таблицата m_income с 10%:АКТУАЛИЗАЦИЯ m_income SET цена = цена*1.1
WHERE product_id=3;
Заявка U002.Следната SQL заявка за актуализиране увеличава количеството на всички продукти в таблицата m_income с 22 единици, чиито имена започват с думата "Butter":
АКТУАЛИЗАЦИЯ m_income SET сума = сума+22
WHERE product_id IN (SELECT id FROM m_product WHERE заглавие LIKE "Oil*");
Заявка U003.Следната SQL заявка за промяна в таблицата m_outcome намалява цените на всички стоки, произведени от OOO Sladkoe с 2 процента:
АКТУАЛИЗАЦИЯ m_outcome SET цена = цена*0,98
WHERE product_id IN
(ИЗБЕРЕТЕ a.id ОТ m_product a INNER JOIN m_supplier b
ON a.supplier_id=b.id WHERE b.title="OOO"Сладкое"");. !}
Лаборатория №1
SQL: RETRIEVE DATA - командаИЗБЕРЕТЕ
Цел на работата:
- Запознайте се с SQL изразите
- научете как да създавате прости SQL заявки в Access с помощта на командата SELECT;
· използването на оператори IN, BETWEEN, LIKE, IS NULL.
Упражнение№1. Създайте заявка за избор в SQL режим на всички стойности на полетата СОБСТВЕНО ИМЕ и ФАМИЛИЯ от таблицата СТУДЕНТИ.
ИЗБЕРЕТЕ ИМЕ, ФАМИЛИЯ
ОТ СТУДЕНТИ;
Упражнение№2 . Създайте заявка за избор в SQL режим за всички колони на таблицата STUDENTS.
ИЗБЕРЕТЕ *
ОТ СТУДЕНТИ;
Задача номер 3.Създайте заявка за избор в SQL режим на имената на градовете, в които живеят студенти, информация за които е в таблицата ЛИЧНИ ДАННИ.
ИЗБЕРЕТЕ ОТДЕЛЕН ГРАД
ОТ [ЛИЧНИ ДАННИ];
Задача номер 4.Създайте заявка за избор в SQL режим, която избира имената на всички студенти с фамилия Иванов, чиято информация е в таблицата СТУДЕНТИ.
ИЗБЕРЕТЕ ФАМИЛИЯ, ИМЕ
ОТ СТУДЕНТИ
WHERE FURNAME="Иванов";
Задача номер 5. Създайте заявка за селекция в SQL режим за получаване на имена и фамилии на студенти, обучаващи се в група УИТ-22 на бюджетна форма на обучение.
ИЗБЕРЕТЕ ФАМИЛИЯ, ИМЕ
ОТ СТУДЕНТИ
WHERE GROUP="HIT-22" AND BUDGET=true;
Задача номер 6. Създайте заявка в SQL режим. за извадка от таблицата УЛАГАНЕ НА ИЗПИТ информация за студенти с оценки само 4 и 5.
ИЗБЕРЕТЕ *
ОТ[ПРОМЯНАИЗПИТИ]
КЪДЕТООЦЕНКАIN (4,5);
Задача номер 7.Създайте zanpoc и SQL режим за извадка от информация за студенти, които имат изпитна оценка 3 по предмета IOSU.
ИЗБЕРЕТЕ *
ОТ[ПРОМЯНАИЗПИТИ]
КЪДЕТОВЕЩ=" ISSU" ИОЦЕНКАНе е в (4.5);
Задача номер 8.Създайте заявка в SQL режим, за да изберете записи за елементи, чиито часове са между 100 и 130.
ИЗБЕРЕТЕ *
ОТАРТИКУЛИ
КЪДЕТОГЛЕДАММЕЖДУ 100 И 130;
Задача номер 9.Създайте заявка в SQL режим, за да изберете от таблицата СТУДЕНТИ информация за студенти, чиито фамилни имена започват например с буквата "С".
ИЗБЕРЕТЕ *
ОТСТУДЕНТИ
КЪДЕТОФАМИЛИЯКАТО"СЪС*";
Заключение:По време на лабораторната работа се запознахме с SQL изрази, научихме се как да създаваме прости SQL заявки в Access с помощта на командата SELECT с помощта на операторите IN, BETWEEN, LIKE.
Примерите за SQL заявки могат да се използват за учене и практикуване на писане на SQL заявки в MS Access.
Една SQL заявка може да бъде вложена в друга. Подзаявката не е нищо повече от заявка в заявка. Обикновено подзаявка се използва в клауза WHERE. Но има и други начини за използване на подзаявки.
Заявка Q011.Показва информация за продуктите от таблицата m_product, чиито кодове също са в таблицата m_income:
ИЗБЕРЕТЕ *
ОТ m_product
WHERE id IN (ИЗБЕРЕТЕ product_id FROM m_income);
Заявка Q012.Показва се списък с продукти от таблицата m_product, чиито кодове не са в таблицата m_outcome:
ИЗБЕРЕТЕ *
ОТ m_product
WHERE id NOT IN (ИЗБЕРЕТЕ product_id FROM m_outcome);
Заявка Q013.Тази SQL заявка връща уникален списък с кодове и имена на продукти, които имат кодове в таблицата m_income, но не и в таблицата m_outcome:
ИЗБЕРЕТЕ ОТДЕЛЕН product_id, заглавие
ОТ m_income INNER JOIN m_product
НА m_income.product_id=m_product.id
WHERE product_id NOT IN (ИЗБЕРЕТЕ product_id FROM m_outcome);
Заявка Q014.От таблицата m_category се показва уникален списък с категории, чиито имена започват с буквата M:
ИЗБЕРЕТЕ ОТДЕЛНО заглавие
ОТ m_product
WHERE заглавие LIKE "M*";
Заявка Q015.Пример за извършване на аритметични операции върху полета в заявка и преименуване на полета в заявка (псевдоним). Този пример изчислява разход = количество*цена и печалба за всеки запис на консумация на артикул, като се приема, че печалбата е 7 процента от продажбите:
сума*цена/100*7 AS печалба
ОТ m_резултат;
Заявка Q016.Чрез анализиране и опростяване на аритметичните операции можете да увеличите скоростта на изпълнение на заявката:
ИЗБЕРЕТЕ dt, product_id, сума, цена, сума*цена КАТО сума_на резултата,
сума_изход*0,07 AS печалба
ОТ m_резултат;
Заявка Q017.С помощта на оператора INNER JOIN можете да комбинирате данни от множество таблици. В следващия пример, в зависимост от стойността на ctgry_id, всеки запис в таблицата m_income се съпоставя с името на категорията от таблицата m_category, към която принадлежи продуктът:
ИЗБЕРЕТЕ c.title, b.title, dt, сума, цена, сума*цена КАТО сума_доход
FROM (m_income AS a INNER JOIN m_product AS b ON a.product_id=b.id)
INNER JOIN m_category AS c ON b.ctgry_id=c.id
ORDER BY c.title, b.title;
Заявка Q018.Функции като SUM - сума, COUNT - количество, AVG - средно аритметично, MAX - максимална стойност, MIN - минимална стойност се наричат агрегатни функции. Те приемат множество стойности и връщат една стойност, когато се обработват. Пример за изчисляване на сумата от произведението на полетата сума и цена с помощта на агрегатната функция SUM.