Good day! Began to study the database and wondered. To work with them you need some kind of DBMS ala MySQL, Oracle, Microsoft Access, etc. But these are just programs that allow us to work with the database, i.e. understand the SQL language and execute the necessary commands, but what is the database itself? Is it something like a heap, a managed memory area? What is it at the computer level? Where is my data stored. And, if I don’t have a DBMS, what does the operating system offer me? Does any database go with it (what does microsoft offer, what macos, what linux)? And again, when working with the database through the DBMS, when I want to view all the databases, it is obvious that only those created directly by me are shown to me, but the operating system itself, some applications, of course, store their data. So that's why I don't see them? And in general, are my operating system and myself using the same databases?
4 answers
No, [R] DBMS is not only involved in executing SQL (and not all DBMS are doing this at all), they usually also have a data storage engine (and sometimes not one). The engine can store data in files, in memory, not store at all or store with the help of another database .
Database files can exist separately, but they are usually compatible only with the DBMS in which they were created, and usually up to the version. These files are not intended for distribution.
Under the conditions of a database server that is accessible over a network of multiple clients (MySQL, PostgreSQL, Oracle, MSSQL, etc.), either a dump or some sort of replication is used to transfer data. It is an idea to transfer internal database files: it can work, but reliability is not guaranteed.
But databases are also embedded (H2, SQLite), they are usually intended for exclusive use by a single client . They are usually subject to similar compatibility restrictions, but the engines of such databases are usually embedded directly in the applications that use them. And once the data storage engine is distributed with the application, you can safely deliver the data file for it.
Such files make little sense to distribute as part of the OS distribution. They are definitely not needed by the kernel, and it is quite debatable to consider software “bundled” that works with the database.
So the OS does not depend on them and does not use them inside, although they may have components for working with them to work with them in order to make life easier for some applications. Having installed any DBMS you will not get access to any new data.
RDBMS itself has databases for internal use. Let's say PostgreSQL has the template0 and template1 bases , which are used as templates for creating new databases: CREATE DATABASE actually makes a clone of template1 . In cases where it is important to create an absolutely empty database, it is customary to clone template0 . But by default both are empty.
I understand the author does not understand the meaning of the phrase 'Database'. This is not some kind of data structure for the system, it is actually an abstract concept. There is a pretty good article on the wiki . A database can be called essentially any files or other data structures, and a DBMS is a program that can operate with this data. Each DBMS has its own file structure. Each system also has many different tasks for which internal databases are used. For example, in Windows, to quickly find files in the file system, indexes are built for files and folders. This is a kind of internal database, which is controlled by some code written inside the OS.
The SQL language is just a standard for accessing data in a database. That is, the user of the DBMS, in whatever popular DBMS he works, he will write requests in one language (in general, there are subtleties, of course). Inside, each DBMS will parse this query and search inside the database in different ways. That's what made so many DBMS now and so many disputes about which one and where is better to use.
The database structure itself may lie in one file or in several, it may even be in memory, even one DBMS can store data differently, depending on the task, and the user himself adjusts the behavior of the DBMS.
There are many different classifications of databases, but this is a separate science.
MySQL, MSSQL, Oracle, etc. these are not interfaces for working with some abstract finished database at the axis level, roughly speaking this is the database itself, in each case different.
The most common option for storing data in the database. That is, depending on the implementation, the database may be located in one file or in the set. There are options (for example, memory tables in mysql), in which data is stored in RAM.
- Yeah. Those. yet in the end are the files? But you can’t say that without MySQL I don’t have databases on my local computer? In the same visual studio entity framework I can create a database. Or is he acting in the role of a DBMS or VS? - Joseph Katzman
- SQLLite allows you to create InMemory database. And there are no files there - Anton Shchyrov
- VS cannot create a MySQL database without a MySQL server. MS SQL, Oracle, Postgree DB is completely similar - Anton Shchyrov
- The @JosephKatzman entity framework runs on top of the DBMS. - Bald
- @JosephKatzman, VS uses LocalDB for such cases. If you don’t know for sure, then yes, you can’t say, any program can use this or that database for its needs, and moreover, deploy it locally on your machine. Search for such bd will need pens. A rough example: you wrote an application that creates and uses a kind of local database, gave it to a friend, and then he became a happy owner of database without even knowing it. (provided that he has all the necessary software on the machine) EF is orm to facilitate the work with the data source. - DanielOlivo
In Windows, you can work with Access databases. There is no graphical interface of MS Access itself, but the access components are installed. On Android, this is SQLLite.
About "see all bases" I did not understand the question. Why do you need it? Each application runs from its database, the location of which she knows. This question is similar to "How to download the Internet?"
- Yes, the question is incomprehensible, I agree, but you read my comment in question. I would rather ask if the database created by me would be exactly the same (on the physical level or slightly higher) than the one created by the operating system? Those. Will it be the same database, but with a different name and stored elsewhere? Or maybe some metadata is added to the system database, indicating that it was the system that created it? - Joseph Katzman
- Neither the system nor you create a database. DB creates a DBMS. And the question of the binary format of the database itself lies entirely on the DBMS. If the
CREATE DATABASErequest arrives, the database is created and it does not matter who executed the request. The binary structure may depend on a heap of parameters: the version of the database server, the file system, the size of the cluster and the page, the support for encryption ... - Anton Shchyrov - @JosephKatzman, what does "the same database" mean? When you create a new database, this is a new database, even if it was copied from the old one. Perhaps the misuse of the concept of "database". Working with the file system or other storage is subject to the rules of the axis, which is exactly for such things. If (and I do not know, litter) there are some service axes of the DB, then they can in principle work past the OS kernel, since were introduced by the developers themselves. - DanielOlivo
C:\ProgramData\MySQL\MySQL Server 5.6\data. - Alexander