oracle中dbms
In Relational Algebra, Extended Operators are those operators that are derived from the basic operators. We already have discussed Basic Operators in the previous section. Now let us discuss the Extended Operators and how they are beneficial in Relational Algebra. There are mainly three types of Extended Operators, namely:
在关系代数中, 扩展算子是从基本算子派生的那些算子。 我们已经在上一节中讨论了基本运算符。 现在让我们讨论扩展算子以及它们在关系代数中的好处。 扩展运算符主要有三种,即:
Intersection
路口
Divide
划分
Join
加入
Let us consider two tables named as A and B.
让我们考虑两个名为A和B的表。
A –
一个 -
RollNo | Name | Marks |
---|---|---|
1 | Aashi | 98 |
3 | Anjali | 79 |
4 | Brijesh | 88 |
卷号 | 名称 | 分数 |
---|---|---|
1个 | 麻司 | 98 |
3 | 安贾利 | 79 |
4 | 布里耶什 | 88 |
B –
B –
RollNo | Name | Marks |
---|---|---|
1 | Aashi | 98 |
2 | Abhishek | 87 |
3 | Anjali | 79 |
4 | Brijesh | 88 |
卷号 | 名称 | 分数 |
---|---|---|
1个 | 麻司 | 98 |
2 | 阿比舍克 | 87 |
3 | 安贾利 | 79 |
4 | 布里耶什 | 88 |
1)交叉路口 (1) Intersection)
Intersection works on the relation as 'this and that'. In relational algebra, A ∩ B returns a relation instance that contains every tuple that occurs in relation to instance A and relation instance B (both together). Here, A and B need to be union-compatible, and the schema of both result and A must be identical.
交叉点将关系作为“ this and that”进行处理 。 在关系代数中, A∩B返回一个关系实例,该关系实例包含与实例A和关系实例B (两者在一起)有关的每个元组。 这里, A和B必须是联合兼容的,并且结果和A的架构必须相同。
Syntax:
句法:
SELECT * FROM A INTERSECT SELECT * FROM B;
RollNo | Name | Marks |
---|---|---|
1 | Aashi | 98 |
3 | Anjali | 79 |
4 | Brijesh | 88 |
卷号 | 名称 | 分数 |
---|---|---|
1个 | 麻司 | 98 |
3 | 安贾利 | 79 |
4 | 布里耶什 | 88 |
2)除 (2) Divide)
Divide operator is used for the queries that contain the keyword ALL.
除运算符用于包含关键字ALL的查询。
For e.g. – Find all the students who has chosen additional subjects Machine Learning and Data Mining.
例如-查找选择了机器学习和数据挖掘其他主题的所有学生。
Student –
学生–
Student Name | Subject |
---|---|
Ashish | Machine Learning |
Ashish | Data Mining |
Shivam | Network Security |
Shivam | Data Mining |
Tarun | Network Security |
Tarun | Machine Learning |
Yash | Machine Learning |
Yash | Data Mining |
学生姓名 | 学科 |
---|---|
阿什什 | 机器学习 |
阿什什 | 数据挖掘 |
湿婆 | 网络安全 |
湿婆 | 数据挖掘 |
塔伦 | 网络安全 |
塔伦 | 机器学习 |
亚什 | 机器学习 |
亚什 | 数据挖掘 |
Subject –
学科 -
Student Name |
---|
Machine Learning |
Data Mining |
学生姓名 |
---|
机器学习 |
数据挖掘 |
Output: Student ÷ Subject
产出:学生÷科目
Student |
---|
Ashish |
Yash |
学生 |
---|
阿什什 |
亚什 |
3)加入 (3) Join )
Join operation is as its name suggest, to join or combine two or more relations’ information. Join can also be defined as a cross-product followed by selection and projection. There are several varieties of Join operation. Let’s discuss all of them one by one.
顾名思义,联接操作是联接或组合两个或多个关系的信息。 连接也可以定义为交叉产品,然后是选择和投影。 Join操作有多种。 让我们一一讨论。
Student1 –
学生1 –
RollNo | Name | Marks |
---|---|---|
1 | Ashish | 98 |
2 | Shivam | 72 |
3 | Tarun | 53 |
4 | Yash | 89 |
卷号 | 名称 | 分数 |
---|---|---|
1个 | 阿什什 | 98 |
2 | 湿婆 | 72 |
3 | 塔伦 | 53 |
4 | 亚什 | 89 |
Student2 –
学生2 –
RollNo | Name | Marks |
---|---|---|
1 | Anjali | 99 |
4 | Dinesh | 79 |
5 | Harsh | 95 |
7 | Kartik | 88 |
卷号 | 名称 | 分数 |
---|---|---|
1个 | 安贾利 | 99 |
4 | Dinesh | 79 |
5 | 苛刻 | 95 |
7 | 卡蒂克 | 88 |
一个。 条件加入 (a. Condition Join)
When you want to join two relations based on the given condition, it is termed as Condition Join. It is denoted by the symbol ⋈c.
当您要基于给定条件合并两个关系时,称为条件合并。 它由符号⋈C表示。
For e.g. – Select the students from Student1 table whose RollNo is greater than the RollNo of Student2 table.
例如:–从Student1表中选择RollNo大于Student2表的RollNo的学生。
Student1⋈cStudent1.RollNo>Student2.RollNoStudent2
Student1⋈cStudent1.RollNo > Student2.RollNo Student2
Syntax:
句法:
SELECT * FROM Student1, Student2 WHERE Student1.RollNo > Student2.RollNo;
Output –
输出–
RollNo | Name | Marks | RollNo | Name | Marks |
---|---|---|---|---|---|
2 | Shivam | 72 | 1 | Anjali | 99 |
3 | Tarun | 53 | 1 | Anjali | 99 |
4 | Yash | 89 | 1 | Anjali | 99 |
卷号 | 名称 | 分数 | 卷号 | 名称 | 分数 |
---|---|---|---|---|---|
2 | 湿婆 | 72 | 1个 | 安贾利 | 99 |
3 | 塔伦 | 53 | 1个 | 安贾利 | 99 |
4 | 亚什 | 89 | 1个 | 安贾利 | 99 |
b。 平等加盟 (b. Equi Join)
It is a special case of Condition Join. When you want to join two relations based on the equality condition, it is termed as Equi Join. It is denoted by the symbol ⋈.
这是条件连接的特例。 当您要基于相等条件连接两个关系时,它称为“等连接”。 用符号⋈表示。
For e.g. - Select the students from Student1 table whose RollNo is equalto the RollNo of Student2 table.
例如-从Student1表中选择RollNo等于Student2表的RollNo的学生。
Student1⋈Student1.RollNo=Student2.RollNoStudent2
Student1⋈Student1.RollNo =学生 2.RollNo Student2
Syntax:
句法:
SELECT * FROM Student1, Student2 WHERE Student1.RollNo=Student2.RollNo;
Output –
输出–
RollNo | Name | Marks | RollNo | Name | Marks |
---|---|---|---|---|---|
1 | Ashish | 98 | 1 | Anjali | 99 |
4 | Yash | 89 | 4 | Dinesh | 79 |
卷号 | 名称 | 分数 | 卷号 | 名称 | 分数 |
---|---|---|---|---|---|
1个 | 阿什什 | 98 | 1个 | 安贾利 | 99 |
4 | 亚什 | 89 | 4 | Dinesh | 79 |
C。 自然加入 (c. Natural Join)
Natural Join is that type of join in which equijoin is by default applied to all the attributes in two or more relation. Its specialty is if you want to consider the equality between two relations, you don’t need to define the equality; it is predefined for all the attributes if you use Natural Join. It is denoted by the symbol ⋈.
自然联接是一种联接类型,其中默认情况下将等联接应用于两个或多个关系中的所有属性。 它的特长是,如果您要考虑两个关系之间的相等性,则无需定义相等性; 如果使用自然联接,则为所有属性预定义。 用符号⋈表示。
For e.g. - Select the students from Student1 table whose RollNo is equal to the RollNo of Student2 table.
例如-从Student1表中选择RollNo等于Student2表的RollNo的学生。
Student1⋈Student2
学生1⋈学生2
Syntax:
句法:
SELECT * FROM Student1 NATURAL JOIN Student2;
Output –
输出–
RollNo | Name | Marks | RollNo | Name | Marks |
---|---|---|---|---|---|
1 | Ashish | 98 | 1 | Anjali | 99 |
4 | Yash | 89 | 4 | Dinesh | 79 |
卷号 | 名称 | 分数 | 卷号 | 名称 | 分数 |
---|---|---|---|---|---|
1个 | 阿什什 | 98 | 1个 | 安贾利 | 99 |
4 | 亚什 | 89 | 4 | Dinesh | 79 |
There is also one more type of Join Outer Join which further gets divided into Left Outer Join, Right Outer Join and Full Outer Join. We will discuss it in other section in Inner Join VS Outer Join.
联接外联接还有另一种类型,进一步分为左外联接,右外联接和全外联接。 我们将在“ 内部联接VS外部联接”的其他部分中进行讨论。
翻译自: https://www.includehelp.com/dbms/extended-operators-in-relational-algebra.aspx
oracle中dbms