What is Database?
A database is an organized collection of structured information or data, typically stored electronically within a computer system. To manage this data effectively, a Database Management System (DBMS) is used, allowing users to handle the data efficiently. Together with the system’s applications, this data forms what we call a database system.
Furthermore, modern databases often store data in tables composed of rows and columns. This structure not only simplifies management but also facilitates modification, updating, monitoring, and streamlining of the information. Additionally, most databases utilize Structured Query Language (SQL) to record and retrieve data, ensuring that users can access the information they need quickly.
What do you need a data bass for?
- data storage, storage and reconditioning;
- Database=Organized set of AB data
- Database management software is required to organize the data (CYБД . система управления базами данных- MS SQL Server, XAMPP/localhost/phpmyAdmin, MS Access)
What is the language of structured requests (SQL)?
SQL, or Structured Query Language, is the primary programming language used in most relational databases for querying, processing data, and managing access control. Developed by IBM in the 1970s, SQL has evolved significantly over time. As a result, various extensions have been created by companies like IBM, Oracle, and Microsoft that adhere to the SQL ANSI standard. Although SQL remains widely used, it’s important to note that newer query languages are beginning to emerge.
Mileks on vaja andmebass?
- andmete salvestamine, säilitaminne ja korrastamine
- Andmebaas=AB-andmete korrastatud kogum
- Andmete korrastamiseks on vaja andmebaasi haldamise tarkvara (CYБД . система управления базами данных- MS SQL Server, XAMPP/localhost/phpmyAdmin, MS Access)
| Text | Number | Kuupäeva/Loogika |
| VARCHAR(255) TEXT CHAR(10) CHARACTER(20) Näited: nimi, isikikood, telefon | INT SMALLINT BITE Numeric (5,2) Decimal(5,2) — десятичное число, где 2 — количество знаков после запятой, а 5 — всего знаков Counter — счетчик Näited: palk (зп), vanus, pikkus, kaal jne | date/time date time INTERVAL — для хранения временного интервала логический — BOOL, BOOLEAN, BIT, Yes/No, True/False |
255 это макс число символов
100,50 £ — decimal(5,2)
Слова для теста:
Таблица- tabel
Поле=столбец — väli=veerg
запись=строка — kirje=rida
query- päring-запрос
PIIRANGUD — Ограничения
——————
1.PRIMARY KEY — первичный ключ — primaarne võti — уникальным образом идентифицирует строку таблицы, каждой строчке присваивает свой номер.
IDENTITY(1,1)-sql server (настройка для первичного ключа) или
AUTO_INCREMENT — xampp — настройка для поля первичного ключа — заполняется автоматически возрастающими числовыми значениями
2. FOREIGN KEY — внешний/ вторичный ключ — определяет внешний ключ который задает связь между двумя таблицами(связь с первичным ключом другой таблицы)
3. UNIQUE– неповторяющиеся значения
4. NOT NULL-непустое поле (пользователь не смодет сделать пустое поле для записи)
5.
SQL Server mamagement Stuudio — (localdb)/mssqllocaldb
--Tabeli loomine
CREATE TABLE opilane(
opilaneID int Primary Key identity(1,1),
eesnimi varchar(50),
perenimi varchar(50),
sunniaeg date,
opilaskodu bit);
SELECT * FROM opilane;
--andmete lisamine tabelisse
INSERT INTO opilane(eesnimi, perenimi, sunniaeg,opilaskodu)
VALUES ('Seva','Tsarev','2007-01-02', 0),
('Daria','Jermitsova','2005-05-06', 1),
('Sofia','Halchenko','2005-02-07', 0),
('Sofia','Mukhina','2005-10-07', 0),
('Daria','Halchenko','2006-06-10', 0);
SELECT * FROM opilane;
DELETE FROM opilane WHERE opilaneID=94;
DELETE FROM opilane WHERE opilaneID=96;
DELETE FROM opilane WHERE opilaneID=87;
DELETE FROM opilane WHERE opilaneID=89;
DELETE FROM opilane WHERE opilaneID=90;
DELETE FROM opilane WHERE opilaneID=91;
CREATE TABLE ryhm(
ryhmID int not null primary key identity(1,1),
ryhmNimi char(8) unique);
Select * from ryhm;
--lisamine
INSERT INTO ryhm(ryhmNimi)
VALUES ('TARpv23');
Select * from ryhm;
Select * from opilane;
--lisada uus veerg tabellisse opilane
ALTER TABLE opilane ADD ryhmID int;
--täidame ryhmID tabelis opilane
UPDATE opilane SET ryhmID=1;
--Foreign Key
ALTER TABLE opilane ADD foreign key
(ryhmID) references ryhm(ryhmID);
CREATE TABLE Hindamine(
hindamineID int not null primary key identity(1,1),
opilaneID int,
aine varchar(100),
hinne int);
SELECT * FROM Hindamine;
SELECT * FROM opilane;
ALTER TABLE Hindamine add foreign key
(opilaneID) references opilane(opilaneID);
INSERT INTO Hindamine(opilaneID, aine, hinne)
VALUES (1, 'matemaatika', 5)