Sql 简明教程
SQL - RDBMS Concepts
What is RDBMS?
RDBMS 代表 *R*elational *D*atabase *M*anagement *S*ystem。RDBMS 是 SQL 和所有现代数据库系统（例如 MS SQL Server、IBM DB2、Oracle、MySQL 和 Microsoft Access）的基础。
RDBMS stands for *R*elational *D*atabase *M*anagement *S*ystem. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
关系数据库管理系统 (RDBMS) 是一种数据库管理系统 (DBMS)，它基于 E. F. Codd 在 1970 年引入的关系模型。
A Relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd in 1970.
What is a Table?
RDBMS 中的数据存储在称为 tables 的数据库对象中。此表基本上是相关数据条目的集合，由多列和多行组成。
The data in an RDBMS is stored in database objects known as tables. This table is basically a collection of related data entries and it consists of numerous columns and rows.
请记住，表是关系数据库中最常见且最简单的数据存储形式。以下是一个 CUSTOMERS 表的示例，其中存储了客户的 ID、姓名、年龄、工资、城市和国家/地区：
Remember, a table is the most common and simplest form of data storage in a relational database. Following is an example of a CUSTOMERS table which stores customer’s ID, Name, Age, Salary, City and Country −
ID |
Name |
Age |
Salary |
City |
Country |
1 |
Ramesh |
32 |
2000.00 |
Hyderabad |
India |
2 |
Mukesh |
40 |
5000.00 |
New York |
USA |
3 |
Sumit |
45 |
4500.00 |
Muscat |
Oman |
4 |
Kaushik |
25 |
2500.00 |
Kolkata |
India |
5 |
Hardik |
29 |
3500.00 |
Bhopal |
India |
6 |
Komal |
38 |
3500.00 |
Saharanpur |
India |
7 |
Ayush |
25 |
3500.00 |
Delhi |
India |
8 |
Javed |
29 |
3700.00 |
Delhi |
India |
What is a Field?
每个表都分解为称为字段的较小实体。字段是表中的一列，旨在维护表中每个记录的特定信息。
Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.
例如，我们的 CUSTOMERS 表包含不同的字段，如 ID、姓名、年龄、工资、城市和国家/地区。
For example, our CUSTOMERS table consists of different fields like ID, Name, Age, Salary, City and Country.
What is a Record or a Row?
记录也称为数据行，是表中存在的每个单独条目。例如，上方的 CUSTOMERS 表中有 7 条记录。以下是 CUSTOMERS 表中的一行数据或记录：
A record is also called as a row of data is each individual entry that exists in a table. For example, there are 7 records in the above CUSTOMERS table. Following is a single row of data or record in the CUSTOMERS table −
ID |
Name |
Age |
Salary |
City |
Country |
1 |
Ramesh |
32 |
2000.00 |
Hyderabad |
India |
记录是表中的水平实体。
A record is a horizontal entity in a table.
What is a Column?
列是表中的垂直实体，包含与表中特定字段关联的所有信息。
A column is a vertical entity in a table that contains all information associated with a specific field in a table.
例如，我们的 CUSTOMERS 表有不同的列来表示 ID、姓名、年龄、工资、城市和国家/地区。
For example, our CUSTOMERS table have different columns to represent ID, Name, Age, Salary, City and Country.
What is a NULL Value?
表中的 NULL 值是字段中看似空白的值，这意味着带有 NULL 值的字段是没有值。
A NULL value in a table is a value in a field that appears to be blank, which means a field with a NULL value is a field with no value.
理解 NULL 值与零值或包含空格的字段不同非常重要。带有 NULL 值的字段是没有值。在记录的创建期间，留下该字段空白。下表有三个记录，其中第一条记录的工资为 NULL 值，第二条记录的工资为零值。
It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. A field with a NULL value is the one that has been left blank during a record creation. Following table has three records where first record has NULL value for the salary and second record has a zero value for the salary.
ID |
Name |
Age |
Salary |
City |
Country |
1 |
Ramesh |
32 |
Hyderabad |
India |
|
2 |
Mukesh |
40 |
00.00 |
New York |
USA |
3 |
Sumit |
45 |
4500.00 |
Muscat |
Oman |
SQL Constraints
约束是对表上的数据列实施的规则。它们用于限制可以进入表的类型。这确保了数据库中数据的准确性和可靠性。
Constraints are the rules enforced on data columns on a table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
约束可以是列级别或表级别。列级别约束仅应用于一列，而表级别约束应用于整个表。
Constraints can either be column level or table level. Column level constraints are applied only to one column whereas, table level constraints are applied to the entire table.
以下是 SQL 中一些最常用的约束：
Following are some of the most commonly used constraints available in SQL −
S.No. |
Constraints |
1 |
NOT NULL ConstraintEnsures that a column cannot have a NULL value. |
2 |
DEFAULT ConstraintProvides a default value for a column when none is specified. |
3 |
UNIQUE KeyEnsures that all the values in a column are different. |
4 |
PRIMARY KeyUniquely identifies each row/record in a database table. |
5 |
FOREIGN KeyUniquely identifies a row/record in any another database table. |
6 |
CHECK ConstraintEnsures that all values in a column satisfy certain conditions. |
7 |
INDEX ConstraintUsed to create and retrieve data from the database very quickly. |
Data Integrity
每个 RDBMS 都有以下数据完整性类别 −
The following categories of data integrity exist with each RDBMS −
-
Entity Integrity − This ensures that there are no duplicate rows in a table.
-
Domain Integrity − Enforces valid entries for a given column by restricting the type, the format, or the range of values.
-
Referential integrity − Rows cannot be deleted, which are used by other records.
-
User-Defined Integrity − Enforces some specific business rules that do not fall into entity, domain or referential integrity.
Database Normalization
数据库范式化是有效组织数据库中数据的过程。此范式化过程有以下两个原因 −
Database normalization is the process of efficiently organizing data in a database. There are two reasons of this normalization process −
-
Eliminating redundant data, for example, storing the same data in more than one table.
-
Ensuring data dependencies make sense.
这两个原因都是有价值的目标，因为它们减少了数据库消耗的空间，并确保数据以逻辑方式存储。范式化包含一系列指南，可帮助您创建良好的数据库结构。
Both these reasons are worthy goals as they reduce the amount of space a database consumes and ensures that data is logically stored. Normalization consists of a series of guidelines that help guide you in creating a good database structure.
范式化准则分为范式；将范式视为数据库结构的格式或布局方式。范式旨在组织数据库结构，以便符合第一范式的规则，然后是第二范式，最后是第三范式。
Normalization guidelines are divided into normal forms; think of a form as the format or the way a database structure is laid out. The aim of normal forms is to organize the database structure, so that it complies with the rules of first normal form, then second normal form and finally the third normal form.
进一步研究第四范式、第五范式等范式由您决定，但一般而言，第三范式对于普通的数据库应用程序而言绰绰有余。
It is your choice to take it further and go to the Fourth Normal Form, Fifth Normal Form and so on, but in general, the Third Normal Form is more than enough for a normal Database Application.