Joomla Software solutions Template

Payday loan

Създаване на заявки в Microsoft Access XP. Използване на QBE и SQL за извличане на данни от таблици

Извличането на данни от една или повече таблици се осъществява посредством заявки. В Access може да се създадат заявки чрез инструмент с графичен интерфейс, наречен Query By Example – QBE или да се напише заявка, като се използва SQL (Structured Query Language – език за структурирани заявки).

SQL е стандартния език, използван за дефиниране, манипулиране и извличане на данни от релационните бази данни. Въпреки че по-голямата част от заявките могат да бъдат създадени, като се използва елементарната конструктивна решетка в Access, програмата запазва всяка заявка, която бива съставена, като команда на SQL. Освен това за конструирането на някои типове заявки, които този програмен продукт предоставя, не може да се използва QBE. За тях трябва да се използва SQL.

За създаване на заявки от прозореца Database се избира Queries | New | Design и се появява QBE. Чрез командата от менюто View | SQL се отваря прозорец за писане на команди на SQL; View | Datasheet връща резултата от заявките за извличане на данни.

Общи правила на синтаксиса в SQL

• Не се прави разлика между малки и главни букви.
• Няма значение броя на празните места (интервалите) и празните редове.

Правила за изрази в Access

• Текстът се поставя в кавички.
• Датите се заграждат с диези (#).
• Имената на колоните и таблиците, съдържащи интервали, се заграждат в квадратни скоби ([]).
• Името на таблицата се отделя от името на колоната със знака точка (.), т.е. TableName.ColumnName.

Командата за извличане на данни SELECT

Най-простият вид на командата SELECT е:

SELECT column_list FROM table_name;

където column_list е списък от имената на колони на таблицата table_name, разделени със запетая. Например:

SELECT FirstName, LastName, PhoneNumber

FROM Employees;

Със символа * се указват всички колони на таблицата. Например:

SELECT * FROM Customers;

С опционалната ключова дума AS се задава ново име на предхождащия я израз. Например:

SELECT CompanyName AS [Name of Supplier], Address

FROM Suppliers;

В проектен режим се използва реда Field преди името на колоната, което се отделя с двоеточие:

Добавянето на WHERE позволява да се зададе условие, чрез което да се получат не всички редове, а само тези които удовлетворяват дадено условие. Общият вид на командата в този случай е:

SELECT column_list FROM table_name

WHERE search_condition;

Например:

SELECT *

FROM Employees

WHERE FirstName = "name1" AND LastName = "name2";

В изглед Design на заявка в Access условията се задават в реда Criteria и всички следващи редове:

Критериите за избор могат да включват функции, поддържани от Microsoft Access и стандартни оператори като +, –, *, /, \ (целочислено деление без остатък), mod (остатък при деление), ^ (степенуване), >, =, <=, както и някои специални SQL оператори:

• IS NULL проверява дали на колоната е зададена стойност; отрицанието е column_name IS NOT NULL.
• BETWEEN min_value AND max_value показва дали стойността е в зададения интервал.
• IN (value1, value2, …, valueN) връща TRUE, ако стойността е в списъка от стойности, зададен след оператора. Стойностите се изброяват, като се разделят със запетаи в SQL израза, а в QBE – със запетаи или с точки и запетаи в зависимост от настройката в Control Panel.
• LIKE "шаблон на символен низ" проверява дали символните низове съответстват на зададения шаблон. За съставяне на шаблон се използват следните обобщаващи символи:
  • ? замества един произволен символ. Например: LIKE "A?a"
  • * замества произволен набор символи. Например: LIKE "A*a"
  • # замества произволна цифра. Например: LIKE "A#a"
  • [ ] замества един символ, намиращ се в указаната област или в указаното множество. Например: LIKE "[A-D]*a" или LIKE "[acegxz]87*a?"
  • [! ] замества един символ, намиращ се извън указаната област или извън указаното множество. Например: LIKE "[!0-9]*a" или LIKE "[!acegxz]87*a#"
  • за търсене на низ, който съдържа * или ? се използват квадратни скоби []. Например LIKE "A[*]a*" търси съответствие със символните низове, започващи с A*a, тъй като при първото срещане на символа * той не се възприема като обобщаващ.

За слепване на символни низове се използва & или + (конкатенация). Например:

SELECT FirstName & " " & LastName AS Name

FROM Employees;

Ключовата дума DISTINCT ограничава изходните данни само до уникалните стойности, т.е. премахва повтарящите се редове от крайния резултат. Например:

SELECT DISTINCT City FROM Customers;

Това може да се зададе и чрез установяване на свойството Unique Values на съответната заявка на Yes:

Възможно е сортиране на редовете, получени от SELECT заявката, като се използва

ORDER BY col_name1 [{ASC|DESC}] [,col_name2 [{ASC|DESC}] [,…]].

Указва се за една или повече колони. Ключовите думи ASC и DESC се използват, за да се определи дали сортирането да се извърши съответно в нарастващ или намаляващ ред. По подразбиране се сортира в нарастващ ред и ASC може да се пропусне. Например:

1)

SELECT FirstName, LastName, Address, PhoneNumber,

HireDate, TerminationDate

FROM Employees

ORDER BY FirstName ASC, LastName ASC;

За задаване на ред на сортиране в проектен изглед се използва ред Sort на мрежата QBE:

2)

SELECT FirstName, LastName, Address, PhoneNumber,

HireDate, TerminationDate

FROM Employees

WHERE TerminationDate IS NULL

ORDER BY HireDate DESC, FirstName ASC, LastName ASC;

Задачи

Задача 1. Да се напише заявка, извличаща данните за всички служители, родени през дадена година.

Решение:

SELECT * FROM Employees

WHERE EGN LIKE "70*";

или

SELECT * FROM Employees

WHERE EGN LIKE "70########";

Задача 2. Да се напише заявка, извличаща всички клиенти, чиито имена (CompanyName) не съдържат цифри.

Решение:

SELECT * FROM Customers

WHERE CompanyName NOT LIKE "*#*";

или

SELECT * FROM Customers

WHERE CompanyName NOT LIKE "*[0-9]*";

Задача 3. Да се напише заявка, извличаща данните за служителите, които работят в момента, т.е. не са напуснали.

Решение:

SELECT * FROM Employees

WHERE TerminationDate IS NULL;

Задача 4. Да се напише заявка, извличаща всички клиенти, които не са от даден град.

Решение:

SELECT * FROM Customers

WHERE City "Велико Търново";

Тази заявка няма върне редовете със стойност NULL в колоната City, тъй като WHERE връща само редовете, за които условието има стойност TRUE, а сравнението NULL "Велико Търново" връща стойността UNKNOWN (при наличие на стойност NULL се работи с тризначната логика). За да бъдат върнати и редовете със стойност NULL, трябва да се използва следната заявка:

SELECT * FROM Customers

WHERE City "Велико Търново" OR City IS NULL;

Задача 5. Да се напише заявка, която извлича клиентите с идентификатор 1, 10 или 87 и са от Варна, София или Велико Търново.

Решение:

SELECT * FROM Customers

WHERE CustomerID IN (1, 10, 87)

AND City IN ("Варна", "София", "Велико Търново");

Задача 6. Да се напише заявка, която извлича данните за продуктите, чиито имена попадат в азбучния обхват A÷F и продажбата им не е преустановена.

Решение:

SELECT * FROM Products

WHERE ProductName BETWEEN "A" AND "F"

AND Discontinued = False;

или

SELECT * FROM Products

WHERE ProductName LIKE "[A-E]*"

AND Discontinued = False;

За разлика от първата заявка в резултата от изпълнението на втората заявка няма да се включи символния низ "F".

Задача 7. Да се напише заявка, която извлича данните за продуктите, наличното количество на които е критично и продажбата им не е преустановена.

Решение:

SELECT * FROM Products

WHERE Stock <= ReorderLevel AND Discontinued = False;

Задача 8. Да се напише заявка, която избира колоните от таблицата за продажбите, извършени от служителите с идентификатори 2, 5, 12 или 90 през даден период от време (т.е. между две конкретни дати).

Решение:

SELECT * FROM Sales

WHERE EmployeeID IN (2, 5, 12, 90)

AND SaleDate BETWEEN #01/10/2003# AND #31/12/2003#;

Задача 9. Да се напише правило за валидност на колоната:

9.1. EGN в таблицата Employees, за да се осигури въвеждане на точно 10 цифри;

9.2. PhoneNumber, за да се осигури въвеждане на произволен брой цифри, наклонени черти (/), интервали или тирета.

Решение:

9.1. Validation Rule: [EGN] LIKE "##########"

9.2. Validation Rule: [PhoneNumber] NOT LIKE "*[!0-9/ -]* "