preamble
Today, I'm going to explain the basic concepts of databases in simple and clear language. Overall, they are basically the same as what I explained in the video. If you find the video explanation a bit difficult to understand, take a look at this text version of the explanation, which will hopefully help you get up to speed on databases more quickly. It's important to note that the databases discussed in this article primarily refer to relational databases.
Database can be simply understood as a large data storage warehouse. Its function is similar to that of a library, but in terms of data storage and management, databases are not only able to organize large amounts of data efficiently, but also make data querying and retrieval more efficient and convenient through a variety of technical means. Because of this, we have tools like databases to help us find and utilize stored information more quickly and accurately.
relational database
What is a relational database? It simply means that a relational database is like a well-organized filing cabinet with categorized files in each drawer for you to find and manage. Each drawer has different folders, and each folder has many files in it, and these folders and files have a clear relationship and organization between them.
In addition to the above characteristics, databases have the crucial property that they must be able to search and retrieve data efficiently. In fact, almost all users can experience the convenience of this search function when using databases. For example, everyone has used this search function.
Excel spreadsheet
Here, it is necessary to explain Excel sheets in detail in order to better understand the concept of databases. Although file storage and databases have some similar features, they differ significantly in terms of data management and presentation.
Unlike the simple data management of file storage, Excel sheets provide an intuitive interface that can show the process of storing and manipulating data more clearly. By comparison, we can better understand the structure and functionality of databases. Next, we will explore how Excel sheets organize and process data efficiently in order to provide a more concrete and visual reference for understanding how databases work.
data storage
Our common tables usually have column names that are used to explain to others what the data in each column is and what it does, and such column names play a key descriptive role.
In my demo, I've listed just a few basic column names to simplify the instructions. Next, we usually enter the actual data in each row. While we can easily recognize and find the information we need by eye when the amount of data is small, the situation is very different once the amount of data increases.
At this point, simple visual scanning is no longer effective for data lookup. For example, if we need to find a specific content, such as data 2 in column G, in this case, we may find that the finding process becomes extremely tedious and time-consuming. Therefore, when faced with large amounts of data, relying on traditional manual lookup methods is not only inefficient, but may also lead to errors and omissions.
Data filtering
However, we all know that Excel has a powerful filtering function, which allows us to filter by the content of each column. In this way, we can quickly find the row where the required data is located.
For example, when we need to find data under specific conditions, just apply the corresponding filter conditions, you can quickly locate and view the rows that meet the conditions, thus greatly improving the efficiency of data processing.
In fact, this operation is actually very similar to common operations in databases. Understanding and mastering these common operating habits in Excel can help you better understand and apply the relevant functions in the database.
In this regard, we don't usually need to discuss in detail how to modify or delete data, as these operations are usually done manually with a mouse. However, it is vital to understand the filtering function. Before we can delete or modify data, we must first use the filtering function to pinpoint the data that needs to be manipulated, and then perform the corresponding deletion or modification operation.
Common Database Operations
By summarizing the above Excel table processing, we can summarize several common operations, including: adding data, querying data, modifying data and deleting data. Here, we do not need to know in-depth how to create and delete files, because in Coze Intelligent Body, these operations can be completed directly through the mouse, thus saving us part of the learning cost. Example:
Okay, so next, we'll launch into a quick study based on these operations. Typically, when data tables are created, they are initially empty, meaning there is no data. Therefore, the first step is to guide the user through the chat process to add data to the table. Since big models usually have some limitations in terms of SQL processing power and it is more difficult to control them precisely, I mainly rely on database nodes to handle these data operations in the actual workflow.
Next, we will use the template database provided by the smart body for demonstration purposes. With this example, we can show in detail how to apply the above method in practice.
Add Data
So, the word "add" translates directly to "insert" in English. The next key component is the "table name". You can't create a student table and then add student data to a teacher table, right? It's the same thing. Then there are the column names, which are called "fields" in the database, and each field corresponds to a column of data. In order to add the data, we need to fill in the "Name" field exactly as it is, and not put the data anywhere else or it will get messed up. In short, this is the basic concept of data structure. In a database, this operation is called a SQL statement.
insert into book_notes (section,name)
When we need to assign a value to a field in a database, we usually use the word "value" in English. However, in database operations, especially when multiple columns are involved, we must use the plural form "values" even if we are assigning a value to only one column. Also, the values provided need to be in the order of the fields. As an example:
insert into book_notes (section,name) values(2,'also called Pilgrimage the West or Monkey')
Note that when storing string text, we must wrap the text in two English single quotes (''). This practice is to ensure that the text is correctly parsed and stored in the database. Only then will the database be able to process and store this string data correctly, avoiding possible syntax errors or data storage problems.
Query Data
After we have the data, we need to perform a query operation. In English, the word "query" directly translates to "select". When you need to look at specific fields (columns), you must explicitly list these fields. At the same time, you also need to specify the data table to query (equivalent to Excel files). In this way, we can write the following SQL statement to complete the query operation:
select section,name from book_notes
Yes, there is no need for separate parentheses in all cases except when adding data where you need to use parentheses for field names to clarify their placement.
Then again, this type of query doesn't quite fit with what we're used to. We usually only query the data we need. For example, in an Excel file, we would filter on a column; in a database, we could use the keywordWHERE to achieve similar filtering functionality. To filter which field to use, simply add theWHERE Just specify the appropriate field after the keyword. Example:
select section,name from book_notes where section = 1
As you can see, this query returns only the data we need, so that we can further process and analyze this data in our workflow without additional filtering or cleaning of irrelevant information.
Modify data
Next, modify the word, directly translated into English is [update], first of all, we must specify the table name of the data to be modified, in which case, we can update the data in a particular table, but it must be clear what rows of data to be updated, the same is also the use of the [where] keyword for screening, we filtered that after that one or a part of the data, we would like to change the data in that column, [set] the word, directly translated into English is [set], well, combined, the basic format of the SQL update statement is as follows:
update book_notes set section = 2 where section =1
Just remember the order in which they were written.
Delete data
Deleting data is very simple. We can identify the data to be deleted by filtering the conditions, which are usually specified in the [WHERE] clause. To translate the word "delete" into English, it is [DELETE]. Next, we simply specify the table name. The final SQL statement will look like this:
delete from book_notes where section = 2
Well, up to here, the four basic operations of the database, namely, add (INSERT), check (SELECT), change (UPDATE) and delete (DELETE), are the most commonly used statements by programmers in practice. These operations form the basis of database management and data manipulation. Of course, in the face of more complex business logic and requirements, more advanced knowledge and skills can be derived, but these elements are beyond the scope of our current discussion.
summarize
Although we have only touched on the basics of databases in this article, real-world applications may encounter more complex business logic and requirements. These complex scenarios usually require a deeper understanding of more advanced features and techniques. While they are beyond the scope of this article, mastering the basics is the first step toward advanced skills.
Hopefully, through this article, you have gained a clear understanding of databases and have become proficient in applying these basic operations in the real world. In the future, as you gain a deeper understanding of database knowledge, you will be better able to tackle various data management and analysis challenges. Thank you for reading and I hope this has been helpful.
I'm Rain, a Java server-side coder, studying the mysteries of AI technology. I love technical communication and sharing, and I am passionate about open source community. I'm also a Tencent Cloud Creative Star, an expert blogger in Ali Cloud, a Huawei Cloud Expert, and an excellent author in Nuggets.
💡 I won't be shy about sharing my personal explorations and experiences on the path of technology, in the hope that I can bring some inspiration and help to your learning and growth.
🌟 Welcome to the effortless drizzle! 🌟