Now that you have data in the MYLIBRARY table, you can query the table to find the books you own accurately. SELECT statement is used to get data from the table. To use the SELECT statement to get data, just tell it the table name and column name.
Select all _ my _ books from my library;
Although the query can generate a list of book information, it is not particularly useful. Book information is a large amount of data. The only advantage of storing it in a relational database is that it can be easily retrieved or printed. What if you want to search data? To find out whether there is a specific book, you must get all the records and manually traverse each record one by one! This is not our expectation for complex RDBMS software.
It is necessary to adopt some method to identify the special keywords of records stored in the table, such as book name or ISBN number. The standard programming answer to this question is to parse the record, divide it into multiple segments, and loop through it to find a specific target word. Repeat this process for each record in the table, and SQL cannot perform any similar operations without vendor-specific process extensions. This way violates the essence that SQL language is a declarative language and needs a deep understanding of data structure. Let's look at the first record entered into the MYLIBRARY table again.
SQL Bible by Alex Crighel Boris M Trukhnov Paperback: Page
Publisher: Willie; Version (April) Language: English
International standard book number:
How do we divide records into different pieces of information? What is the mark between each segment? How to distinguish the name and author of a book? If space characters are used as separators, SQL and Bible will be divided into different segments, but logically, SQL and Bible belong to the same information segment. How do you know that by is a preposition, not part of the author's name? The solution comes from the structured nature of SQL. After all, SQL is a structured query language. In order to solve this problem, more columns are needed to store different pieces of information. By splitting a tedious string into several semantically closely related data fragments, each data fragment can be identified independently, because each fragment becomes a separate column and returns to the CREATE TABLE statement (delete the existing table first).
Delete table myLibrary
Create a new table according to the discussion above.
Create table myLibrary
(
title VARCHAR()
Author VARCHAR ()
Author VARCHAR ()
Publisher VARCHAR ()
Page integer
publish_date VARCHAR()
i *** n VARCHAR()
book_language VARCHAR()
)
In the structure of the new table, the original single column is split into multiple columns. In addition, you can add a second column to separate the author's first name and last name into two separate columns (this is a data modeling process and will be discussed in Chapter 2). At present, all columns use the same data type except the data type of PAGES column is set to INTEGER type to indicate how many pages the book contains, which shortens the content of each column. The reason for setting the PAGES column to INTEGER type will be further explained later in this chapter. Readers may also consider modifying the data type of the PUBLISH_DATE column. Generally, the behavior of date data is different from that of character data. The DBMS provides data types specific to date and time.
Now, it is not necessary to store all the data in the same bucket. There are more choices for data types of each column, and different columns can be defined as different data types. When inserting or updating data in each column (which will be introduced later in this chapter), it is recommended not to confuse data types.
Data types will be discussed later in this chapter, and they will be introduced in detail in chapter 1.
Readers may have noticed that there are two authors in the new MYLIBRARY table, which is to adapt to the situation that a book has two authors. This also leads to another question: what should we do when a book has only one author or a book has only one author? This problem will be discussed in depth in the data modeling part of chapter one. Here, the reader only needs to note that the unused columns will automatically fill in the default values. If readers find that it is often necessary to add new columns to the table, it is best to spend some time reading about database normalization (see Chapter).
Next, the steps of inserting data into the new table are exactly the same as those described above. The only difference is that the list of values has become longer, because it originally contained only one column, but now it contains four columns in the list of values. Except for the value of the PAGES column, all other data provided must be enclosed in single quotation marks. Quotation marks indicate that these data are character data, and no quotation marks indicate numerical data.
Insert values into my database (
SQL bible
Alex Crighel
Boris Trukhnov
Willie
April
English)
Back to Directory SQL Practical Beginners
Editor's recommendation
Oracle indexing technology
High performance MySQL
Lishi Xinzhi/Article/program/SQL/20 13 1 1/ 16484