7. ALL operators

ALL operators will value with another focus of ALL values, ALL operators must begin with comparison operators, followed by a subquery,

Here are ALL the grammar of the operator:

Comparison_operator ALL (subquery)
The following example for salary is greater than all employee wages of employees in the department ID for 8 information:

SELECT
The first_name, last_name, salary
The FROM
Employees
WHERE
Salary & gt;=ALL (SELECT
Salary is
The FROM
Employees
WHERE
Department_id=8)
The ORDER BY salary DESC;
Execute the query above, get the following results -

+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
| first_name | last_name | salary |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
24000 | | Steven | Lee |
17000 | | Neena | Wong |
17000 | | Lex | Liang |
John | | Liu | 14000 |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
4 rows in the set
8. ANY operator

ANY operator according to the condition to value compared with ANY of a set of values, as shown in the following:

Comparison_operator ANY (subquery)
Similar to ALL operators, ANY operator must begin with comparison operators, followed by a subquery,

For example, the following statement for salary is greater than the average salary of all employees: each department

SELECT
The first_name, last_name, salary
The FROM
Employees
WHERE
Salary & gt; ANY (SELECT
AVG (salary)
The FROM
Employees
GROUP BY department_id)
The ORDER BY the first_name, last_name;
Execute the query above, get the following results -

+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
| first_name | last_name | salary |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
9000 | | Alexander | Lee |
11000 | | Avg | Su |
6000 | | Bruce | Wong |
Charles | | Yang | 6200 |
9000 | | Daniel | Chen |
. .
12000 | | Shelley | Wu |
24000 | | Steven | Lee |
6500 | | Susan | Zhou |
4800 | | Valli | Chen |
8300 | | William | Wu |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- --
32 rows in the set
Is ANY alias, please note that as a result, use them interchangeably,

9. The EXISTS operator

Whether the EXISTS operator tests the subquery contains any line:

The EXISTS (subquery)
If the query returns one or more lines, the result of the EXISTS to true; Otherwise the result is false,

For example, the following statement to find all the employee has a dependent information:

SELECT
The first_name, last_name
The FROM
Employees e
WHERE
The EXISTS (SELECT
1
The FROM
Dependents d
WHERE
D.e mployee_id=e.e mployee_id);
Execute the query above, get the following results -

+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +
| first_name | last_name |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +
| Steven | Lee |
| Neena | Wong |
| Lex | Liang |
| Alexander | Lee |
| Bruce | Wong |
| | David Liang |
| Valli | Chen |
| Diana | Chen |
. .
| Shelley | Wu |
| William | Wu |
+ -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - +
30 rows in the set
Through the above study, now, you should be

CodePudding user response:

2. SQL TRUNCATE TABLE and DELETE

Logically, TRUNCATE TABLE statement and do not bring the WHERE clause of the DELETE statement is provided to DELETE all the data from the TABLE of the same effect, but they do exist some differences:

When using the DELETE statement, the database system will record the operation, with some effort, you can roll back the deleted data, however, when using TRUNCATE TABLE statement, use it unless in the transaction has not yet been submitted, otherwise cannot be rolled back,

To DELETE data from the TABLE referenced by the foreign key constraints, cannot use the TRUNCATE TABLE statement, in this case, you must use the DELETE statement,

If a TABLE is associated with the trigger, the TRUNCATE TABLE statement will not trigger the delete trigger,

After performing TRUNCATE TABLE statement, some database system will automatically incremental column (or identity, sequence, etc.) the value of the reset to its initial value, the DELETE statement is not the case,
With the WHERE clause of the DELETE statement to DELETE some data from the TABLE, the TRUNCATE TABLE statement to DELETE all the data from the TABLE all the time,

CodePudding user response:

Thanks your sharing, come on!

CodePudding user response:

Thanks for sharing, knowledge suggested that wrote the blog,