1. What is a database?
You're reading this SQL tutorial, which shows that you need to work with the database in some way. SQL is the language used to accomplish this task, so before you learn SQL, you should have an understanding of databases and some basic concepts of database technology.
You may not realize that you've been using a database. Whenever you pick a contact on your phone or look up a name from your email address book, you're using a database. When you search on the website, you are also using a database. When you log on to the network at work, you also need to rely on a database to verify your username and password. ATM cards can be used even at ATMs, and a database is used for password verification and balance checking.
Although we have been using databases, we are not very clear about what exactly a database is. Not to mention that people may use the same database term to represent different things, further exacerbating this confusion. Therefore, we first give some of the most important database terms and explain them.
Tip: Review of basic concepts
This is followed by a brief introduction to some basic database concepts. If you already have some database experience, you can use this review to consolidate it; if you are new to databases, you can learn the necessary basic knowledge. Understanding the concept of databases is an important prerequisite for mastering SQL, and if necessary, you should probably find a good book to complement the basics of databases.
1. Database
Everyone has a refrigerator in their home, what is the refrigerator used for? The fridge was used to store food.
Similarly, a database is where data is stored. It is precisely because of the database that we can directly find the data. For example, if you use The Balance Treasure every day to view your account earnings, it is given to you after reading data from the database.
Database
The container that holds the organized data (usually a file or set of files).
Note: Misuse can cause confusion
People often use the term database to represent the database software they use, which is incorrect and creates a lot of confusion. To be precise, database software should be called a database management system (DBMS). A database is a container created and manipulated through a DBMS, and what it is and what it takes, all kinds of databases are different.
2. Table
When you put materials in a filing cabinet, you don't just throw them into a drawer and you're done, but you create files in the cabinet and then put the relevant information into a specific file.
In the database realm, such files are called tables. A table is a structured file that can be used to store a specific type of data. Tables can hold customer lists, product catalogs, or other lists of information.
Table
A structured manifest of a specific type of data.
The key point here is that the data stored in the table is the same type of data or inventory. The customer's list should never be stored in the same database table as the order's list, otherwise it will be difficult to retrieve and access later. You should create two tables, one for each manifest.
Each table in the database has a name to identify itself. The name is unique, i.e. there is no other table in the database that has the same name.
Description: The table name
To make a table name unique is actually a combination of the database name and the table name, etc. Some databases also use the name of the database owner as part of the unique name. That is, although you cannot use the same table name twice in a database, you can use the same table name exactly in different databases.
Tables have characteristics that define how data is stored in the table, including what kind of data is stored, how the data is decomposed, how the parts of the information are named, and so on. This set of information that describes a table is called a schema, which can be used to describe a particular table in a database, as well as an entire database (and the relationship between the tables in it).
mode
Information about the layout and characteristics of databases and tables.
3. Columns and data types
A table consists of columns. Columnstore information about a portion of a table.
Column
A field in a table. All tables consist of one or more columns.
The best way to understand columns is to think of a database table as a grid, like a spreadsheet. Each column in the grid stores a specific piece of information. For example, in a customer table, one column stores the customer number, the other column stores the customer name, and the address, city, state, and postal code are all stored in their respective columns.
Tip: Data decomposition
It is extremely important to properly decompose the data into multiple columns. For example, cities, states, and postal codes should always be separate columns from each other. By breaking down this data, it is possible to classify and filter the data with specific columns (such as finding out all customers in a particular state or city). If cities and states are combined in a column, it can be difficult to categorize or filter by state.
You can decide how far you want to break down your data based on your specific needs. For example, you can generally store the house number and the street name together in the address. This is no problem, unless you want to sort by street name one day, in which case it is best to separate the house number from the street name.
Each column in the database has a corresponding data type. The datatype defines what kinds of data a column can store. For example, if a column stores a number (perhaps the number of items in an order), the appropriate data type should be numeric. If the column stores dates, text, comments, amounts, and so on, you should specify the appropriate data type.
There can be no national security without cybersecurity
(1) More than 200 cybersecurity series e-books
(2) Network security standard question bank information
(3) Project source code
(4) Introduction to Cyber Security Basics, Linux, Web Security, Attack and Defense Videos
(5) Network security learning route
Receive free private messages "Safe"
data type
What type of data is allowed. Each table column has a corresponding data type that restricts (or allows) the data stored in that column.
The data type limits the kinds of data that can be stored in a column (for example, to prevent character values from being entered in numeric fields). Data types also help classify data correctly and play an important role in optimizing disk usage. Therefore, you must pay special attention to the type of data you use when creating tables.
Note: The data types are compatible
Data types and their names are a major cause of SQL incompatibility. While most primitive data types are consistently supported, many advanced data types do not. To make matters worse, the same data types occasionally have different names in different DBMSs. There is nothing that users can do about this, and it is important to keep these differences in mind when creating table structures.
Fourth, OK
The data in the table is stored in rows, and each record saved is stored in its own row. If you think of a table as a grid, the vertical columns in the grid are table columns, and the table rows are horizontally rowed.
For example, a customer table can store one customer per row. The row number in the table is the number of the record.
Row
A record in a table.
Description: Is it a record or a line?
You might hear users refer to a row as a record when they mention it. Most of these two terms are interchangeable, but technically, line is the right term.
Fifth, the primary key
Each row in a table should have a column (or columns) that uniquely identifies itself. The Customer table can use the Customer Number, and the Orders table can use the Order ID. Employee IDs can be used for employee tables. The bibliography can use the isBN isbn.
Primary key
A column (or columns) whose value uniquely identifies each row in the table.
This column (or columns) that uniquely identifies each row in the table is called the primary key. Primary keys are used to represent a specific row. Without a primary key, it is extremely difficult to update or delete a specific row in a table, because you cannot guarantee that the operation will only involve the relevant row and will not harm the innocent.
Tip: Primary keys should always be defined
While a primary key is not always required, most database designers guarantee that each table they create has a primary key for later data manipulation and management.
Any column in a table can be used as a primary key, as long as it meets the following criteria:
- No two rows have the same primary key value;
- Each row must have a primary key value (null NULL is not allowed in primary key columns);
- Values in primary key columns are not allowed to be modified or updated;
- Primary key values cannot be reused (if a row is deleted from a table, its primary key cannot be assigned to new rows in the future).
A primary key is usually defined on one column of a table, but it is not necessary to do so, and it is possible to use multiple columns together as primary keys. When using multiple columns as primary keys, the above conditions must apply to all columns, and the combination of all column values must be unique (but the values of individual columns can not be unique).