Every household has a refrigerator. What is kept in the refrigerator? Food is kept in the refrigerator. Similarly, a database serves the same purpose for storing data. We are able to immediately seek up data since we have a database. It is provided to you after reading the data from the database, for instance, if you use a bank application every day to check your account revenue.
You may ask: my data can be stored in the excel sheet of my computer, why do I need to build a database? This is because databases have more advantages than excel. The database can store a large amount of data, allowing many people to use the data at the same time. For example, you will understand that excel is like a mobile hard disk. If you use this mobile hard disk, others will not be able to use it. While the database is like a network disk, and many people can access the data in it at the same time and the network disk can hold more data than the mobile hard disk.
How database work
There are many kinds of databases, here we focus on the most widely used relational database. A relational database is composed of multiple tables. If you have used Excel, you will know that Excel is a two-dimensional table. Every table is made up of rows and columns. Similarly, a relational database stores tables one by one, but there is a relationship between each table.
For example: multiple of tables + relationship between it
Correspondingly, to learn relational databases, we only need to master two points:
- The form structure of each table
- The relationship between the tables
The structure of a table means knowing what each table in a relational database looks like. Each table is identified by a name. A table contains columns with column names, and rows that record data. Let's take a concrete example to make it clearer.
For example, a table that store employees’ details.
Each column in the table has a name to identify the column. There are 4 columns in this table, and the column names are employee ID, name, gender and department. From the column name, you can also know what data is recorded in this table.
Data is recorded in each row of the table. One line here represents the employee's information. For example, the second line is the employee's information with employee ID 02. His name is Will Byers with gender Male in a department with the ID 01.
The relational database is composed of multiple tables.
For example, there are 3 tables stored in the employee database. What is a relationship? Relationships in life are defined by the fact that you are your dad's son and you are your son's father. The statistics do, in fact, relate to one another. How may connections be established in a relational database between tables? We will now explain using the employee tables.
These two tables are related by "Department ID". In order to see the relationship between the two tables more clearly, the same color is used (orange and yellow) to represent the information of the employee.
For example, I want to know what is the department record of the employee number "01" in the employee table, I can find the row with the "Department" value of 02 in the department table, and finally find that the employee with 01 is belong to Finance department which correspond with the ID value.
Database Management System (DBMS)
The above are the basic theories of relational database principles. Once you have the theory, of course you have to have the corresponding software implementation to use it, otherwise, no matter how powerful the theory is, it will be a bunch of useless stuff. This is like, if an architect can't build a building with only design sketches, only specific construction personnel can build a building. Therefore, the principle of relational database mentioned above is "design sketch", so who is the corresponding "architect"?
The "architect" who realizes the principle of the database is the database management system, the computer software used to manage the database. There are many kinds of relational database management systems, such as MySQL, Oracle, SQL Server, etc. are relational databases that implement the above theory. Table below shows the differences of the example relational databases in general.
|Oracle||The Oracle system is a database management system built on the basis of the Oracle relational database for data storage and management. Oracle, the world's first commercial database that supports SQL language, is positioned at high-end workstations and small computers as servers.|
|MySQL||MySQL is a relational database management system developed by the Swedish MySQL AB company and is a product of Oracle. My is one of the most popular relational database management systems. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.|
|ACCESS||ACCESS database is a database management software researched and released by Microsoft. The full name of ACCESS is Microsoft Office Access, which is a representative database management software of Microsoft. Its advantages are: friendly interface and easy operation.|
|MS SQL Server||The SQLserver database is an RMDBS database and a relational database system. The advantage of SQLserver is: true client server architecture. Graphical user interface, more intuitive and simple.|
SQL (Structured Query Language)
Building construction workers build houses by making tools like shovels, dozers, etc. So, what tools do we use to manipulate the data in the database? That tool is SQL. SQL is a language developed for operating databases. It can operate on tables in the database, such as modifying data and finding data. Think of the database as a bowl of rice, where the rice is data. Now we want to eat the rice in the bowl, how to take out the rice in the bowl? At this time, we take a pair of chopsticks and use them to manipulate the rice in the bowl. The chopsticks here are SQL, which is used to manipulate the data in the database.
Now we can understand the operating principle behind a bank application. The user stores the money in the bank database. When the user checks the balance, the SQL tool is used in the background to operate the corresponding database, find the data in it, and then return it to the user so that users can see the money deposited in the bank as well as the daily income.