Received results of working with database

Creating of queries

SQL is a special-purpose programming language designed for creating, modifying and managing data. SQL query is a call to a database, which executes the modifying, eliminating, sorting, receipt the data based on a special criteria and other manipulations with data. There are such SQL statements as ‘CREATE’, ‘INSERT’, ‘UPDATE’, ‘DELETE’ and others, but the most common operation in SQL is the query which is performed with the ‘SELECT’ statement. ‘SELECT’ retrieves data from one or more tables and it is the most complex statement in SQL, which includes such optional keywords and clauses as ‘FROM’, ‘WHERE’, ‘JOIN’, ‘GROUP BY’, ‘HAVING’, ‘ORDER BY’ and others.

The next step of completing my academic year project is creating queries for the considered application domain “Address book”.

The following query is an example of an‘INSERT’ query that adds data in the table “Person”.

 

INSERT INTO `person` (`Person_ID`, `Name`, `Surname`, `MiddleName`, `Email`, `Address`, `Mobile`, `Type_ID`) VALUES

(0,'Dmitriy','Andreychuk','Alekseevich','[e-mail]','Traktorostroiteley str, 140A, 128','0995674409',0),

(2,'Roman','Bezruchko','Jurievich','[e-mail]','Konoplyana str, 76, 23','0673440945',0),

COMMIT;

 

The next ‘INSERT’ query fills data in the table “Role”.

 

INSERT INTO `role` (`Role_ID`, `RoleName`) VALUES

(0,'Guest'),

(1,'User'),

(2,'Admin');

COMMIT;

 

There were the simplest examples of an ‘INSERT’ query. With the statement ‘INSERT’ it is also possible to build more difficult queries, for example to use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging.

This type of query was used to create a backup table of table “User”.

 

INSERT INTO `Users_backup` (`User_ID`, `UserName`, `UserSurname`, `UserEmail`, `Password`, `Role_ID`)

SELECT *

FROM `Users`

COMMIT;

 

This type of ‘INSERT’ query is also used with the certain restriction which can be defined by the ‘WHERE’ clause. The following query will create a new table “Users_names”, which will contain names of all users, where ‘User_ID’ is more then ‘5’.

 

INSERT INTO `Users_names` (`User_ID`, `UserName`)

SELECT (`User_ID`, `UserName`)

FROM `Users`

WHERE `User_ID`>5

COMMIT;

 

The next SQL statement we will consider is ‘UPDATE’. The following ‘UPDATE’ query changes the value of ‘UserName’ field in the table “User_names”, where `User_ID` is ‘4’.

 

UPDATE `User_names`

set UserName = 'Anton'

WHERE User_ID = '4';

 

An ‘UPDATE’ query can be also used to change all values of ‘UserName’ field in this table, but without any restrictions.

 

UPDATE `User_names`

set UserName = 'Victor'

With the statement ‘UPDATE’ it is possible to use such clauses, as ‘WHERE EXISTS’, ‘SELECT’ and even more difficult operators like ‘INNER JOIN’. The following ‘UPDATE’ query changes all values of ‘UserName’ field in the table ‘User’ if ‘User_ID’ column from table ‘User’ corresponds with the ‘User_ID’ column from table ‘User’.

 

UPDATE `user`

SET UserName='Alex'

WHERE EXISTS (SELECT * FROM user_person

WHERE user.`User_ID` = user_person.`User_ID`)

 

An ‘UPDATE’ query can also be used to change more then one field. The following query changes values of several fields in the table ‘Person’.

 

UPDATE person

SET Mobile='0674558204', Email='[e-mail]'

WHERE Person_ID = 1

 

The next SQL statement we will consider is ‘DELETE’. This type of query removes a record from any table you want. The following ‘DELETE’ query removes the value of the ‘Address’ column, where ‘Person_ID’ = ‘10’.

 

DELETE Address FROM `person`

WHERE Person_ID = 10

 

The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted like in the following example.

 

DELETE FROM `user_person`

But this operation (deleting all rows) can be performed more quickly by using the special command ‘TRUNCATE TABLE’. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column. If no seed was defined, the default value ‘1’ is used. ‘TRUNCATE TABLE’ removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. The following query removes all records from the ‘User_Person’ table.

 

TRUNCATE table `user_person`

 

The syntax of using ‘DELETE’ statement allows to include in query such clauses as ‘LIMIT’. In this case the delete operation is performed on a deletion of all rows defined with values in the ‘WHERE’ and ‘LIMIT’ constructions. The following example deletes 11 rows from the ‘User’ table beginning with the row where ‘User_ID’ value is ‘6’.

 

DELETE FROM `user`

WHERE User_ID > 5

LIMIT 11

 

The next SQL statement we will consider is ‘SELECT’. SQL ‘SELECT’ statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets. ‘SELECT’ is the mostly used SQL command. This type of query has many optional clauses:

1) WHERE, that specifies which rows to retrieve.

2) GROUP BY, which groups rows sharing a property so that an aggregate function can be applied to each group.

3) HAVING, that selects among the groups defined by the ‘GROUP BY’ clause.

4) ORDER BY, which specifies an order in which to return the rows.

5) AS, which provides an alias which can be used to temporarily rename tables or columns.

The following query selects all fields from the table ‘Person’ and sorts this records by surname. This query has one restriction: a name of existing persons must be ‘Vladislav’.

 

SELECT *

FROM person

WHERE Name='Vladislav'

ORDER BY Surname;

 

The next query selects all records from the table ‘Person’ who has ‘Kyivstar’ as a mobile operator.

 

SELECT *

from person

WHERE Mobile LIKE '067%' OR Mobile LIKE '097%'

OR Mobile LIKE '098%' OR Mobile LIKE '096%'

ORDER BY Name

 

The result of this query is shown in a figure 3.1.

 

 

Figure 3.1 – The result of ‘SELECT’ query

The next ‘SELECT’ query contains a ‘COUNT AS’ construction. This query selects those names from the table ‘Person’ which meet more then once.

 

SELECT Name, COUNT(Name) AS 'Amount'

FROM person

GROUP BY Name

HAVING Amount > 1;

 

With the ‘SELECT’ statement it is also possible to use a ‘JOIN’ construction. The following query joins two tables – ‘User_Person’ and ‘User’. The new table consisting of rows of the first and second tables will be the result of this query.

 

SELECT user.`UserName`, `user`.`UserSurname`,

MAX(`user_person`.`MeetingDate`) AS LastDate

FROM user

JOIN user_person ON user.`User_ID`=user_person.`User_ID`

GROUP BY user.`UserName`, user.`UserSurname`

ORDER BY LastDate

 

The next type of queries we will consider is nested queries. Nested queries are queries which are contained in a body of other query. The following nested query consists of the ‘SELECT IN SELECT’ construction.

 

SELECT UserName, UserSurname

FROM user

WHERE User_ID IN

(SELECT User_ID

FROM user_person

WHERE Person_ID = 3)

The result of this query is shown on a figure 3.2.

 

 

Figure 3.2 – The result of the first nested query

 

It is possible to use more than one ‘SELECT IN SELECT’ construction if you need to use three or more tables to get a necessary data. The following example selects the type of person, which was created by user with value of the identifier equally ‘4’.

 

SELECT Value FROM type

WHERE Type_ID IN

(SELECT Type_ID

FROM person

WHERE Person_ID IN

(SELECT Person_ID

FROM user_person

WHERE User_ID = 4));

 

The result of this nested query is shown on a figure 3.3.

 

 

Figure 3.3 – The result of the second nested query

 

Syntax of nested queries allows to use even 'JOIN' constructions. The following example of nested queries connects three tables to obtain the necessary data – a name and a surname of the user and a name and a surname of the created person.

 

SELECT user.`UserName`, `user`.`UserSurname`,

MAX(user_person.`MeetingDate`) AS LastDate,

person.Name, person.`Surname`

FROM `user`

JOIN user_person ON user.User_ID = user_person.`User_ID`

JOIN person ON `user_person`.`Person_ID` = person.`Person_ID`

 

The result of this query is shown in a figure 3.4.

 

 

Figure 3.4 – The result of the third nested query

 

In conclusion we will consider the ‘JOIN’ queries. ‘JOIN’ constructions were used in the previous queries, but there were no examples of using ‘FULL OUTER JOIN’, ‘LEFT OUTER JOIN’ or ‘RIGHT OUTER JOIN’ statements. The following query is an example of ‘LEFT OUTER JOIN’ query, which connects two tables – ‘User’ and ‘Role’ to check which user has not a role.

 

SELECT * from user

LEFT OUTER JOIN role

ON user.`Role_ID`=role.`Role_ID`

WHERE RoleName IS NULL

 

There is not any user without a role in created tables, so the result of this query will be an empty table. This result is shown on a figure 3.5.

 

 

Figure 3.5 – The result of the first ‘JOIN’ query

 

The following query uses the ‘RIGHT OUTER JOIN’ statement. The results of this query show which type of person was never used by creating a ‘Person’ table.

 

SELECT Person_ID, Name, Surname, type.`Value`

FROM person

RIGHT OUTER JOIN type

ON person.`Type_ID`=type.`Type_ID`

 

The result of this ‘RIGHT OUTER JOIN’ query is shown on a figure 3.6.

 

 

Figure 3.6 – The result of the second ‘JOIN’ query

The following query selects those people who were created more than one time.

 

SELECT Name, COUNT(user_person.`Person_ID`) AS 'Amount'

FROM person

RIGHT OUTER JOIN user_person

ON person.`Person_ID`=user_person.`Person_ID`

GROUP BY user_person.`Person_ID`

HAVING Amount > 1

 

The result of this query is shown on a figure 3.7.

 

 

Figure 3.7 – The result of the third ‘JOIN’ query

 

3.3.2 Розробка уявлень для відображення результатів вибірки

Під уявленням (View) в базах даних розуміють такий об’єкт, котрий є результатом виконання запиту до бази даних, визначеного за допомогою оператора ‘SELECT’, в момент звернення до уявлення.

Уявлення іноді називають «віртуальними таблицями», оскільки результат є доступним для користувача у вигляді таблиці. Але саме уявлення не містить даних, а витягує їх з таблиць в момент звернення до нього. Якщо дані змінені у базовій таблиці, то і користувач отримає актуальні записи при зверненні до уявлення. Уявлення можуть основуватися як на таблицях, так і на інших уявленнях, тобто можуть бути вкладеними.

Переваги використання уявлень:

1) Дає можливість гнучкого налаштування прав доступу до даних за рахунок того, що права даються не на таблицю, а на уявлення. Це дуже зручно у випадку якщо користувачеві потрібно дати права на окремі рядки таблиці або можливість отримання не самих даних, а результату якихось дій над ними.

2) Дозволяє розділити логіку зберігання даних та програмного забезпечення. Можна змінювати структуру даних, не порушуючи програмний код, потрібно лише створити уявлення, аналогічні таблицям, до яких раніше зверталися додатки. Це дуже зручно, коли немає можливості змінити програмний код або до однієї бази даних звертаються кілька додатків з різними вимогами до структури даних.

3) Зручність у використанні за рахунок автоматичного виконання таких дій, як доступ до певної частини рядків і/або стовпців, отримання даних з декількох таблиць та їх перетворення за допомогою різних функцій.

4) Уявлення приховують від прикладної програми складність запитів і саму структуру таблиць БД. Коли прикладній програмі потрібна таблиця з певним набором даних, вона робить найпростіший запит з підготовленого уявлення.

Наступне уявлення «Person_Type» з’єднує необхідну інформацію із двох таблиць – «Person» і «Type» для зручного перегляду усіх створених контактів та їх типів.

 

CREATE VIEW Person_Type (Name, Surname, Mobile, Type)

AS SELECT Name, Surname, Mobile, type.`Value`

FROM person

JOIN type ON `person`.`Type_ID` = type.`Type_ID`

 

Результат уявлення «Person_Type» зображено на рисунку 3.8.

 

 

Рисунок 3.8 – Результат уявлення «Person_Type»

 

У базі даних існує таблиця «User_Person», котра зберігає ідентифікатор користувача та контактів, котрих він створив, а також дату їх знайомства. Але для зручного використання необхідно також знати хоча б ім’я та прізвище користувача та контакту. Щоб кожного разу для отримання цієї інформації не виконувати команду «SELECT», було створене наступне уявлення «Person_Users».

 

CREATE VIEW Person_Users (UserName, UserSurname, MeetingDate,

PersonName, PersonSurname, Type) AS

SELECT user.UserName, user.UserSurname, user_person.`MeetingDate`,

`person`.`Name`, person.`Surname`, type.`Value`

FROM person

JOIN type ON `person`.`Type_ID` = type.`Type_ID`

JOIN user_person ON person.`Person_ID` = user_person.`Person_ID`

JOIN user ON user_person.`User_ID` = user.`User_ID`

Результат створеного уявлення «Person_Users» зображено на рисунку 3.9.

 

 

Рисунок 3.9 – Результат уявлення «Person_Users»