What is the query language for Python

Learn SQL basics

So far we have simply created and accessed databases with Python without any basic knowledge of SQL. We quickly reached our limits in various campaigns due to the lack of basic knowledge. Basics have advantages and bring security to the application and use of databases or SQL. When we talk about SQL, we are talking about one relational database management system (RDBMS: Relational Database Management System). To put it bluntly: data is in a relationship - a relation. These relationships can be evaluated and output.

The abbreviation SQL stands for "Structured Query Language" - a structured query language.

SQL itself has 4 major areas:

  • DDL: Data Definition Language
  • DML: Data Manipulation Language
  • DQL: Data Query Language
  • DCL: Data Control Language

What do these areas do for us?

DDL: Data Definition Language

Before we can use a database system, we must first set up our database with its tables. In good German: which fields are there and what do we call them so that we can conveniently access them again. So we define our data or data fields and can generate the desired structure in the database using an SQL statement. Without a database and data, we cannot use the following areas, such as filling or evaluating the database with data.

DML: Data Manipulation Language

After we have created our desired structure via the DDL, we want to be able to write, change and delete data in our database system. This is exactly what you do with the DML.

DQL: Data Query Language

Reading out the database also with certain conditions (you don't always want all the data). Several tables can be linked here and the result can be output from them.

DCL: Data Control Language

Data Control Language: data control language (better access control language)

Not everyone is allowed to access all data. It therefore makes sense to include a control here.

The DCL also includes transaction control, so that it is also ensured that all changes or actions take place in the database. Otherwise (depending on the system) there is an automatic return to the defined starting point. Just imagine a bank transfer where the payer is debited but not credited to the recipient. That'll cause trouble! Hence either both or nothing at all!

ANSI SQL

Here the term ANSI-SQL, so that you have read it and can locate it. The American National Standards Institute (ANSI) has established standards. These are also available for SQL. However, there have been several different ones over the years as there have been improvements and enhancements to SQL. The various SQL servers essentially offer the same function, which can, however, be slightly different in use, which at the beginning goes beyond the knowledge we need. Should you ever have sleepless nights, you can pass the night away with the various ANSI SQL variants.

MySQL, MariaSQL, Microsoft SQL Server (MSSQL), Oracle - various database systems

There are different ones and at the beginning you use one to get into the matter.

Funny on the side: MySQL - the first 2 letters of the name of the database come from the first name "My". This is the daughter of MySQL AB co-founder Michael Widenius (called Monty). His second daughter is called Maria (was also used as the name for a database system: MariaSQL).

The various database servers are all based on the standard of the current SQL version.

Terms related to SQL

Statements, statement: SQL is an instruction language, not a programming language!

Keywords:

individual SQL commands

Application:

  • not case-sensitive (upper and lower case does not make any difference), but there are recommendations in the form of key words always in upper case, the rest in lower case or mixed spelling.
  • Spaces, tabs and line breaks can be used as you wish. The best possible legibility is the goal here!

Storage of the data with SQL

The data is stored in tables. Everyone will column (one also speaks of Fields or data fields) named with a name to be able to access it later. It is determined whether only text, whole numbers, numbers with decimal places or e.g. a date should be saved in this data field. Here one speaks of Data typesthat are available. Roughly speaking, we have numbers, alphanumeric characters (to put it bluntly, text that can also contain numbers, but which we do not calculate with) and date types. The definition of the data types is very important because you can only calculate with numbers and it also has an impact on sorting. In the application, we will then precisely define the required data type when defining the fields. Then later in more detail in the appropriate chapter.

In addition, a field length is also assigned for most data types.

Name of data record: The rows in the table are called records. Here we have all the individual data that belongs to this data set. In the case of a telephone book, there would then be, for example, first name, last name, zip code, telephone number

Tables thus contain information of the same kind.

A data record is a compilation of individual pieces of information that belong together meaningfully and are therefore difficult to separate. In our example from the phone book, it makes sense that the first name and last name are available as data fields in the same table.

Primary key (primary key column): In order to be able to access exactly a desired data record during the application, we need primary keys. Using the primary key, we can edit the required data record. If we did not have a primary key, the name Müller, for example, would often have bad luck, since several Müller can appear from a certain number of addresses. So it is difficult to search for the surname and expect that there will always be exactly 1 hit. So we need a unique number. If a primary key is defined in our table, we usually no longer have to worry about the fact that it is unique. If you try to assign a primary key several times, we receive an error message from the DBMS - this ensures that there is only one. The assignment of the primary key can run automatically, thus ensuring that unambiguous access is possible.

Relationships in relational database: Let's look at our example with the phone list. In addition to the first and last name, we save the zip code - but not the location! Why? This is where the advantage of relational databases comes into play. It's all about relationships: our postcode system in Germany has exactly one place for each postcode. So it makes little sense to save the place in the table with the name in addition to the postcode. We can create another table for the locations, in which only the zip code and associated locations are saved. In the case of the new table, our postcode is the primary key of our table "ortnames" (we are now just assuming places in Germany). Our zip code field in the “telephone book” table is also called “foreign key”. This foreign key from the "Telefonbuch" table points to the primary key "ZIP" in the "Ortnames" table.

These relationships can be represented in database diagrams.

[image [example database diagrams]]

You can usually see the primary key by means of a key symbol and the type of connection of the data is immediately visible by means of a line. The ends of the lines show the type of relation (link):

  • Key: primary key
  • Infinite sign: 0, 1 or more data records can be affected

Referential Integrity (RI): referential integrity is used to ensure data integrity between the tables. By linking the foreign key and the primary key, the database system can check whether a data record is available and whether the foreign key can be used. It is also not easily possible to change the primary key if it is already being used as a foreign key.

It can only be checked for the formal correctness - the user is still obliged to think along with the content!

Let's look at these basics in specific applications. Then it becomes tangible. In the following chapter we use the primary key for the unambiguous selection of a data record.

Recommend • Social Bookmarks • Thank you