SQL query Statements

query Table Structure

DESC table name ;

querying all Records

SELECT * from table name;

querying a specified record

Select Name,age,birthday from table name;

querying some fields for different records (DE-weight)

SELECT DISTINCT name,birthday from table name;

Field Operation Query

Select Sal*12,age from table name;

using aliases for columns

Select Sal*12 as "annual wage", age ages, name username from table name;

Note: As can be omitted

How to handle null values

Select SAL*12+NVL (jiang,0) as "annual wage", age from table name;

NVL function: If Jiang is empty, take 0

Fuzzy query like

SELECT * from table name where name like ' Li% ';

SELECT * from table name where name like ' Li _ ';

% represents any number of characters, _ represents an arbitrary character

Sort

1. Ascending sort, no ASC defaults to add ASC

SELECT * FROM table name where condition order by sort field [ASC];

2. Descending sort

SELECT * FROM table name where condition order by sort field desc;

3. Multi-sorting, sorted in descending order of field 1, if field 1 is duplicated, repeat parts sorted by field 2 ascending order

SELECT * FROM table name where condition order by sort Field 1 desc, sort field 2;

Group queries

Select Country name, sum (population), sum (area) from table name Group by country name;

When a group by country name is used, a set of data belonging to the same country will return only one row of values, that is, all fields except the same country in the table can only be returned with a value after an aggregate function such as Sum,count.

Select Country name, sum (population), sum (area) from table name GROUP by country having sum (area) >100000;

The having in fact is the where used before, the function is to filter groups of data after the group

Precautions

1) GROUP BY statement can be used alone

2) Having a statement can only be used with the GROUP BY statement

3) If you include group By,having,order by in the SELECT statement, then their order is group By,having,order by

equivalent multi-table query

Querying the associated data in multiple data tables according to the conditions of equivalence requires that some fields of the associated data table have the same properties, that is, have the same data type, width, and range of values

Select A.uname,a.age,b.uanme,b.age, b.sal from, b where A.age=b.age and b.sal>1000;

equivalent connection ( inner connection )

SELECT * from the AA inner join BB on aa.aid=bb.bid;

Returns only rows in which the junction fields are equal in two tables

Equivalent to the SELECT * from AA,BB where aa.aid=bb.bid;

left Connection ( left outer connection )

SELECT * from AA left join BB on Aa.aid=bb.bid;

is based on the record of AA table, AA can be regarded as the left table, BB can be regarded as the right table, the left join is based on left table, that is to say, the records of the table will be all out, and the right table will only display records matching the search criteria

Equivalent to the SELECT * from AA,BB where aa.aid=bb.bid (+);

Right Connection ( right outer connection )

SELECT * from AA right join BB on aa.aid=bb.bid;

The opposite of the left join is equivalent to the SELECT * from AA,BB where Aa.aid (+) =bb.bid;

Full Connection ( full outer connection )  

SELECT * from AA full join BB on aa.aid=bb.bid;

Returns all rows from only two tables

Equivalent to

SELECT * from AA left join BB on Aa.aid=bb.bid

Union

SELECT * from AA right join BB on Aa.aid=bb.bid

Nested Queries

In can replace ' = '

1. Simple Nesting implementation

Select Bumen from a where employid= (select Employid from b where Name= ' Zhang San ');

1) (select Employid from b where Name= ' Zhang San '

Check out the employee number whose name is Zhang San

2) Select Bumen from a where employid= employee number

Check out the department where employee Number 10 is located

2. Nesting Modified field data

Update student Set sal= (select sal+300 from table name where empno=7559) where empno=7599;

3. And operation of nested queries (sum of elements of A and B)

Select Sal from a union select Sal from b

4. Nested query of the cross-operation (belonging to A and b)

Select Sal from a intersect select Sal from b

5. Nested queries for differential operations (A and not B)

Select Sal from a minus select Sal from b

fetch data from a table (first few, several to several)

SELECT * from a where rownum<=2 (take the top two records from the data table)

SELECT * FROM (SELECT * to Scott.emp ORDER BY Sal Desc) A where rownum<6; (sort the records in the data table, take the first 5 records after sorting)

Paging Query

SELECT * FROM (select RowNum rn,tb.* from (SELECT * from Meetingroom ORDER by Roomid) TB) a where rn>= 1 and RN < ; 3

1) First, the data in the Meetingroom table is arranged in full sequence.
2) Check the travel number again
3) Paging according to the line number, such as (to find the behavior of 1 to 2, two rows of records)

SQL query Statements