SQL queries. DML (Data Manipulation Language) - Relational databases - Database - Каталог статей

Главная » Статьи » Database » Relational databases

SQL queries. DML (Data Manipulation Language)

11.4.2.4 describe the basic SQL queries for working with one table in a database: SELECT, UPDATE, INSERT, and DELETE

11.4.2.5 use SQL SELECT for data selection in more tables

SQL queries. Data Manipulation Language.

Data Manipulation Language (DML) is a computer language including commands permitting users to manipulate data in a database.

The SQL SELECT Statement

The SELECT statement is used to select data from a database.

Syntax	Example
SELECT column1, column2, ... FROM table_name;	SELECT surname, firstname FROM users;
SELECT column1, column2, ... FROM table_name WHERE condition;	SELECT surname, firstname, iin FROM users WHERE userID=5;

Additionally: SQL ORDER BY Keyword, SQL AND, OR, and NOT Operators

The SQL INSERT Statement

The INSERT command is used to enter records into a table. Values are entered into the table corresponding order to the listed columns.

Syntax	Example
INSERT INTO tablename (field1, field2, ...) VALUES ('value1', 'value2', ...);	INSERT INTO users (surname, firstname, address, age) VALUES ('Aliyev', 'Ruslan', 'Konaev dist. 7-15', 15);
INSERT INTO tablename VALUES ('value1', 'value2', ...);	INSERT INTO users VALUES (userID, 'Aliyev', 'Ruslan', 'Konaev dist. 7-15', 15);

SQL UPDATE Statement

The UPDATE statement is used to modify the existing records in a table.

Syntax	Example
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;	UPDATE users SET surname = "Makeev", firstname = "Maksim" WHERE userID = 67;

SQL DELETE Statement

The DELETE statement is used to delete existing records in a table.

Syntax	Example
DELETE FROM table_name WHERE condition;	DELETE FROM users WHERE iin = "950210350464";

SELECT from several tables

If you need to select data from two or more related tables, then for each field you need to specify the table name and add the equality of fields related by a foreign key to the selection conditions.

Example,
ERD of relational database:

Task. Write an SQL statement to select all appointments to room 204 to get the next output:

Decision:

SELECT Dentist.dentist_surname, Patient.patient_surname, Patient.patient_firstname, Appointments.date
FROM Dentist, Patient, Appointments
WHERE Appointments.room = 204 AND Dentist.dentistID = Appointments.dentistID AND Patient.patientID = Appointments.patientID

Video 2. SQL queries. Add records into table