SQL Statement Learning _ database Other

(The master will not laugh at ^_^).

All right, the rest doesn't start now:

The SELECT clause mainly determines the names of columns, the number of columns, and the order in which columns are displayed from the table, and "*" indicates all the columns of the query, and the use of the select should be combined with the use of other clauses.

1.from clause:

The ①FROM clause is used to specify the queried table, attempt, or snapshot.
② If multiple entities are specified, they are divided by commas. For easy querying, especially when you are making a self connected query, you can alias the table. (Here I would like to say that a lot of people who just started using SQL always feel that this is very simple, there is nothing to learn, but later in doing some complex SQL, always can not think of this leads to the fact that the SQL statement is not difficult to start).
③ if the entity being queried is not in the current schema, the object must be qualified with the schema name.
Select *from admin.emp
④ If you are querying an entity in a remote database, you must use a database link, and if you query the data for a partition in a partitioned table, you must use the keyword partition to specify the partition name.
Select *from emp@cbf107
Select *from EMP partition (A_hiredate)

2.where clause

The WHERE clause is used to qualify the number of rows processed, select the row that satisfies the condition, and any logical condition in the WHERE clause uses the comparison operator, the data that satisfies the logical condition in the WHERE clause is returned or processed, and the column name or expression can be used in the WHERE clause.

Select ename from emp where deptno=10

① If the column type is a character type, where Ename= ' Smith ';(single quotation marks have a difference in string capitalization)
The ② column type is a digital deptno=20;
③ If the column value takes another column value, it can be represented as
where Emp.depno=dept.deptno
④in Related line information (if "or action") that corresponds to a value in the Value list in the value of the Not in column

Query table EMP has those employees and analysts
Select Name,job from emp where job in (' Clerk ', ' analyst ');
Query table The EMP has those people who aren't employees and analysts.
Select Name,job from emp where job isn't in (' clerk ', ' analyst ');

The wages of those employees are between/not 2000~3000
Select Ename,job,sal from emp where Sal between and 3000;
Select Ename,job,sal from emp where Sal not between and 3000

Select Ename,oeptno from emp where ename like ' s% ';
"%" matches 0 or N-length strings, while "_" matches only one character. If the query condition itself contains "_" or "%", in order to differentiate with the pattern match "_" and "%", you must add an escape "%" clause to the LIKE operator.
Query the line where the employee name starts with A_
Select *from emp where ename like ' a\_% ' ESCAPE ' \ ';

Is null and is not NULL
A null value (NULL) is not equivalent to 0,0 is a number, while a null value represents an unknown, non-existent, or unavailable data. It does not perform arithmetic operations like 0, NULL for querying column values for null values or non-null information.
In Oracle's RDBMS, NULL (null value) does not occupy space, determines whether a column value is empty, cannot use the "=" or "!=" operator symbol, and uses is null or is not NULL.

Select employee information with no bonus
Select Ename,job from emp where comm is null

3.order by

In a relational pattern, there is no order in which rows can be made. The order in which the line information that is determined by the ORDER BY clause is displayed, and when sorted by multiple columns, the first column name is first determined sequentially, and then the second column name determines the order.

First, in descending order of wages, in the case of the same wages, alphabetically by name

Select
ENAME employee,
Sal Salary
From
Emp
where
Deptno=30
ORDER BY
Sal DESC employee;

If you use the DISTINCT keyword in a SELECT clause, you can use only the column names listed in the SELECT clause in the ORDER BY clause, and you cannot use the alias of the column.

Not only can you sort by using the alias of a column name or column in a query statement, you can also use the position of a column in the SELECT clause to sort. (There is a very long expression in the SELECT clause, and the position of the column is useful when you want to sort with the result of this expression). For queries involving collection operations: Union,minus, you cannot use column names, in which case you must supply the location of the column.

Select
' Name: ' | | ENAME "Employee",
Sal "Salary",
From
Emp
where
Deptno=30
ORDER BY
2, 1;

4.group by

You can use the GROUP BY clause to divide rows into smaller groups in a SELECT statement, and then use the cluster function to return summary information for each group, and you can use the HAVING clause to restrict the returned result set. The GROUP BY clause can group query results and return the summary information for the row. Oracle groups the query results by the value of the expression specified in the GROUP BY clause.

In a query statement with a GROUP BY clause, the column specified in the select list is either the column specified in the GROUP BY clause or the cluster function

Select Max (SAL), Job emp Group by job;
(Note that Max (SAL), job job does not necessarily appear, but meaningful)

The select and group by of a query statement, the HAVING clause is the only place where the cluster function appears, and the cluster function cannot be used in the WHERE clause.

Select Deptno,sum (SAL) from the EMP where sal>1200 GROUP by Deptno has sum (SAL) >8500 order by Deptno;

When a HAVING clause is used in a gropu by clause, only the group that satisfies the having condition is returned in the query result. You can have a WHERE clause and a HAVING clause in an SQL statement. Having is similar to a WHERE clause and is used to set qualifications

The role of the WHERE clause is to remove rows that do not conform to the where condition before grouping the query results, that is, to filter the data before grouping, in conditions that cannot contain a clustered function, and where conditions are used to display a particular row.
The HAVING clause is used to filter the groups that meet the criteria, that is, to filter the data after grouping, often including the clustering function, using the having condition to display a specific group, or multiple grouping criteria for grouping.

Inquire about the number of employees per position in each department
Select Deptno,job,count (*) from the EMP group by Deptno,job;

5. Multi-Table Connection

A Cartesian connection is a public relationship that does not specify multiple tables in a WHERE clause when fetching data from multiple tables (that is, each record in a table is connected to each record in another table). If the first table has M records, and the second table uses N records, the result is a m*n record. Try to avoid creating a Cartesian connection. Therefore, there are generally n table connections, at least N-1 connection conditions.

Select
D.dname,d.loc,e.ename,e.sal
From
Delpt d,emp E
where
D.deptno=e.deptno;

Once a table alias is defined, you cannot use the table name to restrict the name of the column in this SELECT statement, and you should limit the alias with a table alias.

Which employees are paid at the third level?

Select
Empno,ename,sal
From
Emp,salgrade
where
Grade=3
and
Sal between Losal and Hisal;

Find out which level each employee in the EMP table belongs to.

Select
Empno,ename,sal,grade
From
Emp,sal,grade
where
Emp.sal between Lowsal and Hisal.

5. Outer connection and inner connection

Sometimes, even if there is no corresponding row in the attached table, the user may want to see the data from a table, and Oracle provides an outer join to implement the feature.
The inner join refers to a connection query that only shows records that fully meet the conditions of the connection, that is, an equivalent connection, and the query result of an outer join is an extension of the result of the inner join query. An outer join not only returns all records that satisfy a join condition but also returns records in one table that do not have matching rows in another table. The outer join operator is "+". The "+" number is placed on the side where the information is incomplete in the connection condition (that is, the side that does not have the corresponding line). Operator "+" affects the establishment of NULL rows. Build one or more lines of NULL to match the complete row of information in the connected table.

The outer join operator "+" can only appear on one side of an expression in the WHERE clause.

If there are multiple join conditions between multiple tables, the outer join operator cannot use the Or,in logical operator to combine with other conditions.

If the ename of the deptno=10 in the EMP table is null, the LOC of the deptno=20 in the Dept table is null:

1.

Select
Ename,dept.deptno,loc
From
Emp,dept
where
Emp.depno (+) =dept.deptno;

If there are no values in the Dept.deptno in the Emp.deptno, the outer joins are
ENAME produces a null value in the result. (emp.deptno=10)

2.

Select
Ename,dept.deptno,loc
From
Emp,dept
where
Emp.depno=dept.deptno (+);

If there are no values in the Emp.deptno in the Dept.deptno, the outer joins are
LOC produces a null value in the result. (DEPT.DEPTNO=20)

5. Self-Connection

A self-connection is a connection between different rows of the same table. The connection is not affected by other tables. You can use a self connection to compare the values of a column that is not in the same table. Because a self-connection query involves only a single table and its own connection. So the table name appears two times in the FROM clause, is represented by two different aliases, two aliases are treated as two different tables, and as with other tables, there is one or more related column connections between aliases. In order to distinguish between columns of different rows of the same table, the name is limited before the names.

Select
Worker.ename,
Manager.ename Manager
From
EMP Worker,
EMP Manager
where
Work.mgr=manager.empno;

7. Set operation

The base combination operator can be used to select data from multiple tables.

①union operations
Use to find the set of two result sets (all records for two result sets) and automatically remove duplicate rows.

Select Ename,sal from account where sal>2000
Union
Select Ename,sal from the Where sal>2000
Union
Select Ename,sal from sales where sal>2000;

Note: The ename,sal must be consistent.

②union all operation
Use to find the set of two result sets (all records in two result sets) source sky, and do not remove duplicate rows.

Select Ename,sal from account where sal>2000
Union
Select Ename,sal from the Where sal>2000
Union
Select Ename,sal from sales where sal>2000;

③intersect operations
The Intersect operation returns the same part of the query result.

What are the same positions in each department?

Select Job from Account
Intersect
Select Job from the
Intersect
Select Job from sales;

④minus operations
Minus returns the difference set of two result sets. (a row that exists in the first result set and that does not exist in the second result set.) ）

What jobs are there in the Finance Department, but not in the sales department?

Select Job from Account
Minus
Select Job from sales;