About Oracle Database (7) Query 1

Querying all column data

SELECT * from table name;

Querying the specified column data is more efficient than querying all column data

Select column name, column name, column name from table name; --Executes the code after the From, finds the table, executes the code after the Select, finds the specified column

Distinct queries and does not contain duplicate records are available for aggregate functions

Select DISTINCT column 1 from table name; such as: Query the company set up what positions Select Job from emp;

Select DISTINCT column 1, column 2 from table name; --Remove duplicates of columns 1 and 2

For example: Remove more than two columns exactly the same as the select distinct Job,deptno from EMP;

Basic query (query by criteria)

SELECT * FROM table name where Condition --query all conditions that satisfy

For example: Query Department number 11 employee SELECT * from EMP where deptno=11;

For example, an employee who queries the department number 11 and has a salary greater than 4000 select * from EMP where deptno=11 and sal>4000;

Aggregate functions are used to count Max min avg count sum using an aggregate function, you cannot query other columns in the statement (except grouped)

A list of the and

Select SUM (column name) from table name;

To alias a column

The name of the from table from the Select SUM (column name); = Select sum (column name) as the name from the From table name;

Select Column Name 1 starting with Name 1, column name 2 from the name 2, column name 3 from the name 3 from the table;

To alias a table

The alias of the Select Table. Column 1, alias for table. Column 2 alias from table name table;

Ask for quantity

Tabular how many records select COUNT (*) from table name;

Select COUNT (*) from the name from the table;

For example, the number of departments that query number 11 select COUNT (*) Number of people from EMP where deptno=11;

Such as: Query company set up a few posts select count (distinct job) from EMP; --Remove duplicates first, and then ask for quantity

Highest, lowest, average

Such as: What is the maximum number?

Select Max (column name) from table name;

Select Max (column name) maximum from table name;

Such as: what is the minimum number?

Select min (column name) from table name;

Select min (column name) minimum from table name;

Such as: What is the average number

Select AVG (column name) from table name;

Select AVG (column name) average from table name;

For example: select Max (SAL) is highest, min (sal) is lowest, avg (SAL) is averaged from EMP;

Grouping GROUP by aggregate functions are generally grouped together with groupings that can be understood as categories

such as: SELECT COUNT (column name) from table name where sex= ' male ';

Select Column name, count (column name) each group of people from table name Group By column name;

such as: Select Job,count per group of people from the EMP group by job;

Such as: Department Group (DNO department number, Sal salary, EMP table name)

Select Dno,sum (SAL) Department salary, AVG (SAL) Department average salary from EMP Group by DNO;

Select group columns, aggregate functions from table name GROUP by group

Note: If the alias contains special characters or aliases are keywords, you must use double quotation marks

"+" addition operator, can only add numbers, cannot add string

The number +null=null, using a NVL function, sets the null setting to 0

such as: Select ENAME,NVL (comm,0) from EMP;

such as: Select ENAME,SAL,COMM,SAL+NVL (comm,0) total wages from EMP;

Fixed value (Cannot add string, will error)

Select Ename sal+10000 from EMP; --No database modification

numeric characters are automatically converted to numbers

such as: select Ename sal+ ' 10000 ' from EMP;

"| |" Connection operator, you can connect any type of data

Example: Select Ename | | ' He sent this month ' | | sal| | ' Block money ' from table name;

About Oracle Database (7) Query 1