Shulou(Shulou.com)05/31 Report--

This article mainly explains the "what must be mastered in the database and sql basic knowledge", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in-depth, together to study and learn "what must be mastered in the database and sql basic knowledge"!

What is the database?

You can think of the data as folders, and the database is a filing cabinet, which holds a lot of data, no matter what the data is or how it is organized.

It should be noted here that people usually use the term database to represent the database software they use, which is incorrect, which is the source of your confusion in the past.

To be exact, database software should be called DBMS, that is, database management system. The database is created and manipulated by DBMS. For example, we can create a database in sql sever.

In other words, DBMS is like a librarian. Instead of accessing the database directly, you use DBMS to access the database and ask the librarian to help you find a filing cabinet.

The classification of the database?

The database mentioned here all refers to the database management system. According to the early database theory, there are three popular database models, namely, hierarchical database, mesh database and relational database.

In today's Internet enterprises, there are two most commonly used database models, namely, relational database and non-relational database.

The relational database model reduces the complex data structure to a simple binary relationship (that is, two-dimensional tabular form).

In the relational database, the operation of the data is almost based on one or more relational tables, and the data management is realized through the operation of classification, merging, connection or selection of these related tables.

The relational popular understanding is that data exists as a two-dimensional array, which you can understand as the arrangement of books in a library.

Bookshelves and floors can be understood as relational data structures, books exist as data, and all librarians are database processes for different tasks. Some people put out fires (data repair, backup), some people organize bookshelves and books (data collation, archiving), and the user process refers to the customers who come to the library, they read books and move books, while the administrator will maintain them.

It has been more than 40 years since the birth of relational database, from the emergence of theory to the development of products, such as:

Common MySQL and Oracle databases, Oracle has risen to the dominant position in the database field, forming a huge industrial market up to tens of billions of dollars every year, and MySQL is also a database that can not be ignored, so that it has been acquired by Oracle.

Non-relational database is also called NoSQL database. NoSQL originally means "Not Only SQL", which refers to non-relational database, not "NO SQL".

Therefore, the emergence of NoSQL is not to completely negate the relational database, but as an effective supplement to the traditional database, it can play an unimaginable high efficiency and high performance in a specific scenario.

NoSQL is a broad definition of non-relational database, which breaks the situation that relational database and ACID theory have been unified for a long time. NoSQL data storage does not need a fixed table structure, and there is usually no continuous operation, so it has incomparable performance advantages compared with relational database in big data access.

SQL stores data

So, as the most common SQL in relational databases, how does it store data?

1. Table:

When you put folders in the filing cabinet, you can't just throw them into a drawer, but create files in the filing cabinet, and then put the relevant information into the relevant files, which is the table of the database.

A table is a structured file used to store a particular type of data, such as a sql file, which contains more than 20 database tables, each of which should not have the same name, but we can store tables with the same table name in different databases.

At the same time, we can not put the student data and teacher data in the same table, it will be difficult for us to extract the data, retrieval and access are more troublesome, so we should create two tables, one table for each list.

2. Column:

Columns are the field information that makes up a table, and a table can consist of one or more columns.

We can understand that each column is every field in the database table, such as the following student id column, student last name column, and exam score column are three fields.

It is very important to correctly decompose the data into multiple columns, for example, the class and student names should be independent columns, and only by splitting them apart can they be arranged and filtered by specific queue data. if the student name and class are combined in one column, filtering by class will be very difficult.

Each column in the database has a corresponding data type, which defines the type of data that the column can store.

For example, if the column stores numbers, the corresponding data type should be numeric, and if the column stores dates, text, comments, amounts, and so on, it should be specified with the appropriate data type.

Data types: allowed data types, each column should have a corresponding data type, limiting the form of data stored.

3. OK:

The data in the database table is stored according to rows, and each row is a stored data. For example, the first row is Zhang San's data, and the second row is Li Si's data.

4. Primary key:

Each row in the table should have a column that can uniquely identify itself. For example, in the student information table, we cannot use the student's name as the primary key, because we will find that once there is a duplicate name, we will have two different data.

Of course, we can't use the test results as the primary key. For example, when we extract according to the "90 points" as the primary key, we will extract two pieces of data.

So we should use the student id as the primary key, because the student id will not be repeated anyway, it is unique, so it can be used as the primary key.

So the primary key is the only identification that distinguishes the rows of the data table and is used to represent a particular row.

Without a primary key, it would be very difficult for us to update or delete specific rows in the table, which must meet two conditions:

No two rows should have the same primary key value

Each row must have a primary key value (there is no null value in the primary key column)

The ability to associate different tables in the database depends on the primary key, such as the following two tables:

For example, the above two pictures, the left picture is the student information table, the right picture is the teacher information table. The key on the left is the student ID, and the key on the right is the teacher ID. Careful readers may notice that there is also a student ID on the right, where the student ID is specifically used to join user tables, which is not the primary key. It's just that the two tables are related through the unique information of the student ID.

However, the correlation between the two tables does not correspond to the information one by one, and there will be gaps, such as:

Therefore, if we want to recognize the contents of the database, we must first understand the difference between sql, sql sever, my sql, oricle and database and database management systems, because many people look for information everywhere on the Internet when learning data analysis, and abuse these nouns in different materials, resulting in confusion and increasing the difficulty of people's cognition of sql.

Thank you for your reading, the above is the content of "what must be mastered in database and sql basic knowledge". After the study of this article, I believe you have a deeper understanding of what must be mastered in database and sql basic knowledge, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.