Hello everyone Let's take a brief look at the basics of SQL. Let's find out what SQL is, what commands, methods and features there are.
Let's start!
Basic terminology
Database - is some organized set of information.
Relational database - is a database built on a relational data model (according to the mathematical theory of relations).
Database management system (DBMS) - is a set of programs that allows you to manage the creation and use of a database.
SQL Language
SQL (structured query language) - is a programming language designed to work with relational databases.
The peculiarity of SQL - is a declarative programming language, we describe what we want to get, not how
Types of SQL statements
Groups of SQL statements:
- Operators for working with database objects.
- Data manipulation operators.
- Transaction Management Commands
- Data protection and management operators.
We will consider only data manipulation operators, because it is with them that you will most often meet in the course of your work.
SELECT request. Basic syntax
- Minimum query:
SELECT * FROM table WHERE condition;
- "Separate" and "order by" operators:
SELECT DISTINCT f1, f2 FROM table
WHERE condition ORDER BY f2 DESC;
- Graph operator:
SELECT QUANTITY (*) FROM table;
Subqueries
Example: print the count of different values in the "f1" field of the table.
SELECT COUNT(*) FROM (SELECT DISTINCT f1 FROM table WHERE condition);
The subquery is written in parentheses, the result of its work is a table.
The exists operator
A quick way to check if a query returns at least one value.
SELECT smth FROM table
WHERE EXISTS (SELECT * FROM table2 WHERE table2.field = table.field1);
Sometimes it is convenient to use aliases:
SELECT field1 as f1, field2 as f2
FROM table AS first_table
WHERE EXISTS
(SELECT * FROM table2 AS second_table
WHERE second_table.field = first_table.f1);
Group by operator
SELECT field, COUNT(*) FROM table
WHERE condition GROUP BY field HAVING having_condition;
In the HAVING operator, unlike where, aggregating functions can be used.
Some aggregating functions:
- SUM
- COUNT
- MIN
- MAX
Merging tables
SELECT column_name(s) FROM table1
UNION (UNION ALL)
SELECT column_name(s) FROM table2
Keys
Types of key relationships:
- 1 to 1
- 1 to many
- many to 1
Many to many relationship
JOIN operator
Changing the contents of tables
- Adding an element:
INSERT INTO table_name values (v1, v2, …);
INSERT INTO table_name (col1, col2) VALUES (v1, v2);
- Updating an element:
UPDATE table_name SET col1 = val1, col2 = val2 WHERE condition;
- Deleting an element:
DELETE FROM table_name WHERE condition;
I hope my efforts will help someone to deal with such a topic as SQL.
I wish you good luck!
FAQ
I am a beginner, how should I learn Python?
Look into the following series:
Learning Python
Step by Step to Junior
Ideas
Would you mentor me?
Of course I am ready to participate in the training. The big difficulty is that English is not my native language and it will be possible to communicate through a translator
Would you like to collaborate on our organization?
If you have interesting ideas, we will be happy to invite your writing from our organization. Write in private messages or in social networks below
Connect to me on
Top comments (3)
Of course! No problem! I will be very glad if the material helps as many people as possible! Just don't forget to leave a link to the source. This will help attract even more
Thank you sir
Always happy to help