Слайд 2
План занятия:
Основы реляционных баз данных
Работа с SQL
Слайд 3
1. Основы реляционных баз данных
Базой данных (БД) называется
организованная в соответствии с определенными правилами и поддерживаемая в
памяти компьютера совокупность сведений об объектах, процессах, событиях или явлениях, относящихся к некоторой предметной области, теме или задаче. Она организована таким образом, чтобы обеспечить информационные потребности пользователей, а также удобное хранение этой совокупности данных, как в целом, так и любой ее части.
Другими словами это хранилище данных.
Слайд 4
1. Основы реляционных баз данных
Реляционная база данных представляет
собой множество взаимосвязанных таблиц, каждая из которых содержит информацию
об объектах определенного вида. Каждая строка таблицы содержит данные об одном объекте (например, автомобиле, компьютере, пользователе), а столбцы таблицы содержат различные характеристики этих объектов - атрибуты (например, номер двигателя, марка процессора, телефоны фирм или клиентов).
Слайд 5
1. Основы реляционных баз данных
Таким образом все данные
представлены в виде таблиц, разбитых на строки и столбцы.
На их пересечении расположены данные.
Столбцы называют полями или атрибутами
Строки называют записями или кортежами
Слайд 6
1. Основы реляционных баз данных
Основные свойства таблиц в
реляционных БД:
В таблице не может быть двух одинаковых строк
Столбцы
располагаются в определенном порядке, который определяется при создании таблицы. В таблице может быть ни одной строки, но должен быть хотя бы один столбец
У каждого столбца есть уникальное имя(в пределах таблицы) и все значения в столбце имеют один тип данных(числа, текст, дата…)
На пересечении каждого столбца и строки может находиться атомарное значение(одно значение, не состоящее из группы значений)
Слайд 7
1. Основы реляционных баз данных
Первичный ключ(Primary key) –
столбец, значения которого во всех строках различны. Первичные ключи
менять нельзя.
Вторичный ключ(Foreign key) – столбец, каждое значение которого соответствует какому либо первичному ключу из другой таблицы.
Слайд 8
1. Основы реляционных баз данных
СУБД (системы управления базами
данных) – совокупность языковых и программных средств, которые осуществляют
доступ к данным; позволяют их создавать, менять, удалять; обеспечивают безопасность и т.д.
Другими словами СУБД – система, позволяющая создавать базы данных и манипулировать сведениями из них.
Несмотря на наличие международного стандарта, многие компании, занимающиеся разработкой СУБД (например, Oracle, Microsoft, MySQL), вносят изменения в язык SQL, применяемый в разрабатываемой СУБД. Таким образом, появляются специфичные для каждой конкретной СУБД диалекты языка SQL.
Слайд 9
1. Основы реляционных баз данных
Любая СУБД позволяет выполнять
следующие операции с данными:
добавление записей в таблицы;
удаление записей из
таблицы;
обновление значений некоторых полей в одной или нескольких записях в таблицах БД;
поиск одной или нескольких записей, удовлетворяющих заданному условию.
Слайд 10
1. Основы реляционных баз данных
Для выполнения этих операций
применяется механизм запросов. Результатом выполнения запросов является либо отобранное
по определенным критериям множество записей, либо изменения в таблицах. Запросы к базе формируются на специально созданном для этого языке, который называется SQL
Слайд 11
2. Работа с SQL
Structured Query Language(SQL) – стандартный
язык управления реляционными базами данных с архитектурой клиент-сервер.
Download
client: http://bit.ly/Uwfz43
Connection name: any name
Host: h04.hvosting.ua
Username: user*(1..10)
Password: 12
Schema: runtest
Слайд 12
2. Работа с SQL
What Can SQL do?
SQL can
execute queries against a database
SQL can retrieve data from
a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
Слайд 13
2. Работа с SQL
SQL can be divided into
two parts:
Data Definition Language (DDL)
The Data Manipulation Language
(DML)
Слайд 14
2. Работа с SQL
DDL statements are used to
define the database structure:
CREATE DATABASE - creates a
new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
Слайд 15
2. Работа с SQL
DML statements are used for
managing data:
SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
Слайд 16
2. Работа с SQL
2.1. DDL
Create new table:
Right click
on DB(i.e., “runtest”)
Choose “Create new” -> “Table” option
Specify table
name (“User*_test_table”)
Add and configure columns
Check the code for table creation (“CREATE code” tab)
Click on “Save” button
Слайд 17
2. Работа с SQL
2.1. DDL
DB data types:
INT –
any number from “-2,147,483,648” to “2,147,483,648”
VARCHAR(x) – Where x is the
number of characters to store.
DECIMAL(p,s) – where p is a precision value; s is a scale value. For example, numeric(6,2).
FLOAT – store fractional (non-integer) values
TEXT - maximum string length is 2 147 483 647 characters*
BIT – stands for Boolean type and can be “0” or “1”
Слайд 18
2. Работа с SQL
2.1. DDL
Rules for fields in
DB:
Auto increment – RDBMS will automatically increment value of
this field for every insert operation
Primary key – data for this filed will be used for building related queries between tables. This field should be unique
Not null – the value of this field is mandatory
Unique – value of this field should be unique
Zero fill – if value is not indicated, RDBMS will set “0” in this field. Value “” and “0” is not equal for DB
Слайд 19
2. Работа с SQL
2.1. DDL
Populate the newly created
table:
Select appropriate table
Select “Data” tab
Click on “+” button
Populate all
columns in the newly added row
Click somewhere outside the row to apply changes
Слайд 20
2. Работа с SQL
2.1. DDL
Delete the table:
Right click
on appropriate table
Choose “Drop …” option
Confirm drop action
Слайд 21
2. Работа с SQL
2.2. DML
Database Tables
A database most
often contains one or more tables. Each table is
identified by a name (e.g. ”Persons" or "Orders"). Tables contain records (rows) with data.
Below is an example of a table called "Persons":
The table above contains three records (one for each person) and five columns (P_Id, LastName, FirstName, Address, and City).
Слайд 22
2. Работа с SQL
2.2. DML
The SQL SELECT Statement:
Most
of the actions you need to perform on a
database are done with SQL statements.
The following SQL statement will select all the records in the "Persons" table:
SELECT * FROM Persons; *
The result-set will look like this
SQL is not case sensitive, but field names and values ARE case sensitive
Слайд 23
2. Работа с SQL
2.2. DML
The SQL SELECT Statement:
Now
we want to select the content of the columns
named "LastName" and "FirstName" from the table above.
We use the following SELECT statement:
SELECT LastName, FirstName FROM Persons;
The result-set will look like this:
Слайд 24
2. Работа с SQL
2.2. DML
The SQL SELECT DISTINCT
Statement:
In a table, some of the columns may contain
duplicate values. This is not a problem, however, sometimes you will want to list only the different (distinct) values in a table.
The DISTINCT keyword can be used to return only distinct (different) values.
SELECT DISTINCT City FROM Persons;
The result-set will look like this:
Слайд 25
2. Работа с SQL
2.2. DML
SQL WHERE Clause:
The WHERE
Слайд 26
2. Работа с SQL
2.2. DML
Quotes Around Text Fields
SQL
uses single quotes around text values.
Although, numeric values should
not be enclosed in quotes.
For text values:
This is correct: SELECT * FROM Persons WHERE FirstName = 'Tove';
This is wrong: SELECT * FROM Persons WHERE FirstName = Tove;
For numeric values:
This is correct: SELECT * FROM Persons WHERE Year=1965;
This is wrong: SELECT * FROM Persons WHERE Year='1965';
Слайд 27
2. Работа с SQL
2.2. DML
Operators Allowed in the
WHERE Clause
With the WHERE clause, the following operators can
be used:
Note: In some versions of SQL the <> operator may be written as !=
Слайд 28
2. Работа с SQL
2.2. DML
The SQL AND &
OR Operators:
AND operator displays a record if both the
first condition and the second condition are true.
OR operator displays a record if either the first condition OR the second condition is true.
Слайд 29
2. Работа с SQL
2.2. DML
AND operator example:
The "Persons"
table:
Now we want to select only the persons with
the first name equal to "Tove" AND the city equal to "Svendson":
SELECT * FROM Persons WHERE City='Sandnes' AND FirstName='Tove';
The result-set will look like this:
Слайд 30
2. Работа с SQL
2.2. DML
OR operator example:
The "Persons"
table:
Now we want to select only the persons with
the first name equal to "Tove" OR the first name equal to "Ola”:
SELECT * FROM Persons WHERE FirstName='Tove' OR FirstName='OLA' ;
The result-set will look like this:
Слайд 31
2. Работа с SQL
2.2. DML
Combining AND & OR:
The
"Persons" table:
Now we want to select the persons with
the city equal to "Sandnes" AND the first name equal to "Tove" OR to "Kari":
SELECT * FROM Persons WHERE City='Sandness' AND (FirstName='Tove' OR FirstName='Kari');
The result-set will look like this:
Слайд 32
2. Работа с SQL
2.2. DML
The SQL LIKE Operator:
The
LIKE operator is used in a WHERE clause to
search for a specified pattern in a column.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
Слайд 33
2. Работа с SQL
2.2. DML
LIKE operator example #1:
The
"Persons" table:
Now we want to select the persons from
city that starts with "s":
SELECT * FROM Persons WHERE City LIKE ‘S%’;
The result-set will look like this:
Слайд 34
2. Работа с SQL
2.2. DML
LIKE operator example #2:
The
"Persons" table:
Now we want to select the persons from
city that ends with "s":
SELECT * FROM Persons WHERE City LIKE ‘%s’;
The result-set will look like this:
Слайд 35
2. Работа с SQL
2.2. DML
LIKE operator example #3:
The
"Persons" table:
Now we want to select the persons living
in a city that contains the pattern "tav":
SELECT * FROM Persons WHERE City LIKE ‘%tav%’;
The result-set will look like this:
Слайд 36
2. Работа с SQL
2.2. DML
LIKE operator example #4:
The
"Persons" table:
Now we want to select the persons living
in a city that does NOT contain the pattern "tav", by using the NOT keyword:
SELECT * FROM Persons WHERE City NOT LIKE ‘%tav%’;
The result-set will look like this:
Слайд 37
2. Работа с SQL
2.2. DML
The SQL ORDER BY
Keyword
The ORDER BY keyword is used to sort the
result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.
Слайд 38
2. Работа с SQL
2.2. DML
ORDER BY keyword example
#1:
The "Persons" table:
Now we want to select all the
persons from the table above, however, we want to sort the persons by their last name:
SELECT * FROM Persons ORDER BY LastName;
The result-set will look like this:
Слайд 39
2. Работа с SQL
2.2. DML
ORDER BY keyword example
#2:
The "Persons" table:
Now we want to select all the
persons from the table above, however, we want to sort the persons descending by their last name:
SELECT * FROM Persons ORDER BY LastName DESC;
The result-set will look like this:
Слайд 40
2. Работа с SQL
2.2. DML
The SQL INSERT INTO
Statement
The INSERT INTO statement is used to insert new
records in a table.
It is possible to write the INSERT INTO statement in two forms:
The first form does not specify the column names where the data will be inserted, only their values:
INSERT INTO table_name VALUES (value1,value2,value3,...);
The second form specifies both the column names and the values to be inserted:
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
Слайд 41
2. Работа с SQL
2.2. DML
INSERT INTO example #1:
The
"Persons" table:
Now we want to insert a new row
in the "Persons" table:
INSERT INTO Persons
VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger');
The result-set will look like this:
Слайд 42
2. Работа с SQL
2.2. DML
INSERT INTO example #2:
The
"Persons" table:
The following SQL statement will add a new
row, but only add data in the "P_Id", "LastName” , "FirstName" columns:
INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob');
The result-set will look like this:
Слайд 43
2. Работа с SQL
2.2. DML
The SQL UPDATE Statement:
The
UPDATE statement is used to update existing records in
a table.
UPDATE table_name SET column1=value1,column2=value2,...
WHERE some_column=some_value;
Note: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
Слайд 44
2. Работа с SQL
2.2. DML
UPDATE example #1:
The "Persons"
table:
Now we want to update the person "Tjessem, Jakob"
in the "Persons" table:
UPDATE Persons SET Address='Nissestien 67', City='Sandnes'
WHERE LastName='Tjessem' AND FirstName='Jakob';
The result-set will look like this:
Слайд 45
2. Работа с SQL
2.2. DML
UPDATE example #2:
The "Persons"
table:
Now we want to delete the person "Tjessem, Jakob”
:
DELETE FROM Persons WHERE LastName='Tjessem' AND FirstName='Jakob';
The result-set will look like this:
Слайд 48
2. Работа с SQL
2.2. DML
DELETE example #2:
The "Persons"
table:
Now we want to delete all rows from “Persons”
table:
DELETE FROM Persons;
As result-set all the data from Persons table will be deleted.
Note: Be very careful when deleting records. You cannot undo this statement!
Слайд 49
2. Работа с SQL
2.2. DML
SQL JOINS:
The JOIN keyword
is used in an SQL statement to query data
from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
The purpose is to bind data together, across tables, without repeating all of the data in every table.
Слайд 50
2. Работа с SQL
2.2. DML
SQL JOINS:
The "Persons" table:
Note:
"P_Id" is the primary key in the "Persons" table.
The
"Orders" table:
Note: "O_Id" is the primary key in the ”Orders" table, "P_Id" column refers to the persons in the "Persons" table without using their names. Relationship between the two tables above is the "P_Id" column.
Слайд 51
2. Работа с SQL
2.2. DML
SQL JOINS:
Before we continue
with examples, we will list the types of JOIN
you can use, and the differences between them.
JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
Слайд 52
2. Работа с SQL
2.2. DML
SQL JOINS: INNER JOIN
The
INNER JOIN keyword selects all rows from both tables
as long as there is a match between the columns in both tables:
SELECT column_name(s) FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
PS: INNER JOIN is the same as JOIN
Слайд 53
2. Работа с SQL
2.2. DML
SQL JOINS: INNER JOIN
Now
we want to list all the persons with any
orders:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName;
The result-set will look like this:
If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.
Слайд 54
2. Работа с SQL
2.2. DML
SQL JOINS: LEFT JOIN
The LEFT JOIN keyword returns all rows from the
left table (table_name1), even if there are no matches in the right table (table_name2):
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
Слайд 55
2. Работа с SQL
2.2. DML
SQL JOINS: LEFT JOIN
Now
we want to list all the persons and their
orders - if any:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName;
The result-set will look like this:
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
Слайд 56
2. Работа с SQL
2.2. DML
SQL JOINS: RIGHT JOIN
The RIGHT JOIN keyword returns all the rows from
the right table (table_name2), even if there are no matches in the left table (table_name1):
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;
Слайд 57
2. Работа с SQL
2.2. DML
SQL JOINS: RIGHT JOIN
Now
we want to list orders with containing persons -
if any:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName;
The result-set will look like this:
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
Слайд 58
2. Работа с SQL
What to read and what
should you know about definitions related to SQL:
Data types
http://bit.ly/1l20Lj9
Primary Key http://bit.ly/1ixPFaP
Foreign Key http://bit.ly/Ux5CDv
Index http://bit.ly/19FI00c
Stored procedure http://bit.ly/1e97Wc5
Nested queries
Work with strings in SQL
Слайд 59
Домашнее задание:
Создать таблицы:
Заполнить таблицы данными (50 записей в
PeopleInfo_user*, 20 записей в CountryInfo_user*)
http://bit.ly/TlwLIA - для быстрой генерации
данных
