Shulou(Shulou.com)05/31 Report--

This article mainly explains "what SQL sentences are there". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "which SQL sentences".

The select clause mainly determines the information such as the column name, the number of columns and the order in which the columns are displayed from the table. "*" means to query all columns, and the use of select should be combined with the use of other clauses.

1.from clause:

The ① from clause is used to specify the table, attempt, or snapshot to be queried.

If ② specifies multiple entities, separate them with commas. For the convenience of query, especially when self-join query, you can give the table an alias. I would like to say here that many friends who just started using sql always thought it was very simple and had nothing to learn, but later, when they were doing some complex sql, they always couldn't remember this, which made the not-so-difficult sql statement impossible 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 ④ queries entities in a remote database, it must use database links, and if it queries data for a partition in a partition table, it 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 limit the number of rows processed, to select rows that meet the conditions, any logical condition in the where clause uses a comparison operator, data that meets the logical conditions in the where clause is returned or processed, and column names or expressions can be used in the where clause.

Select ename from emp where deptno=10

① if the column type is character, where ename='smith'; (there is a difference between the case of the string in single quotation marks)

② column type is numeric deptno=20

③ if the column value takes another column value, it can be expressed in the following form

Where emp.depno=dept.deptno

④ in and not in select the relevant row information that the value of the column matches one of the values in the values list (if "or operation" is performed)

Look up the employees and analysts in the table EMP

Select name,job from emp where job in ('clerk','analyst')

Query table EMP which people are not employees and analysts

Select name,job from emp where job not in ('clerk','analyst')

There are those employees whose wages are between 2000 and 3000.

Select ename,job,sal from emp where sal between 2000 and 3000

Select ename,job,sal from emp where sal not between 2000 and 3000

Select ename,oeptno from emp where ename like's%'

"%" matches 0 or n strings of length, while "_" matches only one character. If the query condition itself contains "_" or "%", in order to distinguish it from the pattern matches "_" 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 is not the same as zero zero is a number, and a null value represents unknown, nonexistent, or unavailable data. It cannot perform arithmetic operations like 0, and null is used to query information where the column value is null or non-empty.

In Oracle's RDBMS, null (null value) does not take up space, to determine whether a column of values is empty, can not use the "=" or "! =" operation symbol, but use is null or is not null.

Select employee information without bonus

Select ename,job from emp where comm is null

3.order by

In a relational model, there is no order of rows. The order in which the row information determined by the order by clause is displayed. When sorting by multiple columns, the order is first determined by the first column name, and then by the second column name.

First, it is arranged in descending order of wages, and in the case of the same salary, in alphabetical order of names

Select

Ename employee

Sal salary

From

Emp

Where

Deptno=30

Order by

Sal desc employee

If you use the distinct keyword in the select clause, only the column names listed in the select clause can be used in the order by clause, not column aliases.

In a query statement, you can sort using not only the column name or column alias, but also the position of the column in the select clause. There is a very long expression in the select clause, and it is useful to use the position of the column when sorting with the results of this expression. For queries that involve collection operations: union,minus, column names cannot be used, in which case the location of the column must be provided.

Select

'name:' | | ename "employee"

Sal "salary"

From

Emp

Where

Deptno=30

Order by

2,1

4.group by

In a select statement, you can use the group by clause to divide rows into smaller groups, then use the clustering function to return summary information for each group, and you can use the having clause to restrict the result set returned. The group by clause groups query results and returns summary information for rows Oracle groups query results according to 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 contains a clustering function

Select max (sal), job emp group by job

(note max (sal), the job of job does not have to appear, but it is meaningful.)

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

Select deptno,sum (sal) from emp where sal > 1200 group by deptno having sum (sal) > 8500 order by deptno

When the having clause is used in the gropu by clause, only the groups that meet the having condition are returned in the query results. There can be where clause and having clause in a sql statement. Having is similar to the where clause and is used to set qualification

The function of the where clause is to remove the rows that do not meet the where condition before grouping the query results, that is, to filter the data before grouping, the condition cannot contain a cluster function, and the where condition is used to display specific rows.

The role of the having clause is to filter groups that meet the criteria, that is, to filter data after grouping, which often contains clustering functions, to display specific groups using having conditions, or to group using multiple grouping criteria.

Query the number of employees for each position in each department

Select deptno,job,count (*) from emp group by deptno,job

5. Multi-table connection

Cartesian join means that when fetching data from multiple tables, the public relations of multiple tables are not specified in the where clause (that is, taking each record from one table as a connection with each record in another table). If there are M records in the first table and N records in the second table, the result is M records. Try to avoid Cartesian connections. Therefore, there are generally N table connections and at least 1 Nmuri connection condition.

Select

D.dname,D.Loc,E.ename,e.sal

From

Delpt D,emp E

Where

D.deptno=E.deptno

Once the alias of the table is defined, the column name cannot be restricted with the table name in this select statement, but the alias of the table should be restricted.

Which employees' wages belong to 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. External connection and internal connection

Sometimes, even if there are no corresponding rows in the joined table, the user may want to see the data from a table, and Oracle provides external joins to implement this function.

The inner join means that the join query only displays the records that fully meet the join conditions, that is, the equivalent join, and the query result of the outer join is the extension of the query result of the inner join. The outer join returns not only all records that meet the join criteria, but also records in one table that do not have matching rows in another table. The operator of the outer connection is "+". The "+" sign is placed on the side of the connection condition where the information is incomplete (that is, the side where there is no corresponding line). The operator "+" affects the establishment of NULL lines. Create one or more rows of NULL to match the rows with complete information in the joined table.

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

If there are multiple join conditions between multiple tables, the outer join operator cannot be combined with other conditions using the or,in logical operator.

If the ename of deptno=10 in the emp table is null, and the loc of deptno=20 in the dept table is null:

1.

Select

Ename,dept.deptno,loc

From

Emp,dept

Where

Emp.depno (+) = dept.deptno

If there is a value in dept.deptno that does not have a value in emp.deptno, when making an external join

As a result, ename produces a null value. (emp.deptno=10)

two。

Select

Ename,dept.deptno,loc

From

Emp,dept

Where

Emp.depno=dept.deptno (+)

If there is a value in emp.deptno that does not have a value in dept.deptno, when making an external join

As a result, loc produces a null value. (dept.deptno=20)

5. Self-connection

A self-join is a join between different rows of the same table. The connection is not affected by other tables. Using self-join, you can compare the values of a column of different rows in the same table. Because the self-join query only involves the connection between a table and itself. So the table name appears twice in the from clause, represented by two different aliases, which are treated as two different tables, and like other table joins, one or more related column joins are used between aliases. In order to distinguish between different columns of the same table, the alias before the name is restricted.

Select

Worker.ename

Manager.ename manager

From

Emp worker

Emp manager

Where

Work.mgr=manager.empno

7. Set operation

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

① UNION operation

Used to find the union of two result sets (all records of the two result sets) and automatically remove duplicate rows.

Select ename,sal from account where sal > 2000

Union

Select ename,sal from research where sal > 2000

Union

Select ename,sal from sales where sal > 2000

Note: ename,sal must be consistent.

② UNION ALL operation

Used to find the union 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 research where sal > 2000

Union

Select ename,sal from sales where sal > 2000

③ INTERSECT operation

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 research

Intersect

Select Job from sales

④ MINUS operation

Minus returns the difference between the two result sets. (rows that exist in the first result set but not in the second result set. )

Which positions are available in the finance department but not in the sales department?

Select Job from account

Minus

Select Job from sales

Thank you for your reading, the above is the content of "what are the SQL statements", after the study of this article, I believe you have a deeper understanding of the question of what SQL statements there are, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.