Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Advanced Search Oracle study notes     - CentOS 6.5 dual card configuration, one of the external network, a local area network connection (Linux)

- Talk Packages (Linux)

- VMware virtual machine to use bridged mode fast Internet access (Linux)

- Setting the RedHat9 Intrusion Detection System (Linux)

- Parse Server supports iOS and Android push messaging (Programming)

- Linux remote wake the computer original code [C] (Linux)

- Ubuntu 15.04 installed JDK and configured as the default JDK (Linux)

- Examples 14 grep command (Linux)

- Protection Docker container Notice (Server)

- Zabbix using Omsa monitor hardware information of Dell server (Server)

- Detailed Linux network security policies and protection measures (Linux)

- Linux Fundamentals of the text, data flow processing orders (Linux)

- Commentary Apache + Tomcat + JK implement Tomcat clustering and load (Server)

- How to install Git client in Ubuntu (Linux)

- Java Access Control (Programming)

- Xshell upload files to achieve Windows host Linux (Linux)

- Build a super simple "hardware" firewall router (Linux)

- Linux use logs to troubleshoot (Linux)

- Linux rpm command Detailed (Linux)

- Red Hat Linux security settings document (Linux)

  Advanced Search Oracle study notes
  Add Date : 2018-11-21      
  Alias the column

We often object when the query to query a column using expressions SELECT enamme, sal * 12 FROM emp

The place is so bad in the second column after column with a query using is sal * 12. Such a relatively poor readability of this we will play for the column aliases to improve readability

Alias itself is not case-sensitive. If you want to distinguish, you need to use an alias in double quotes when the alias contains spaces, you should also use double quotes

SELECT ename, sal * 12 "Annual Salary" FROM emp;

WHERE clause

Used to filter the records in the query data in the process, only the records that satisfy the WHERE clause conditions will be check out the database query table, each record must go through a filtering WHERE

Check wages of less than 2000

SELECT ename, sal FROM emp WHERE sal < 2000;

See department employee information does not belong to 10

! SELECT ename, sal, job FROM emp WHERE deptno = 10;

Discover born in 1980

SELECT ename, sal, hiredate FROM emp WHERE hiredate> TO_DATE ( '1980', 'YYYY');

Discover sal greater than 1000 and worked for clerk

SELECT ename, sal, job FROM emp WHERE sal> 1000 AND job = 'CLERK';

Discover more than CLERK wage or salary is not the limit of all SALESMAN 1500

SELECT * FROM emp WHERE sal> 1500 AND job = 'CLERK' OR job = 'SALESMAN';

Increase the priority is to look at the meaning of wage greater than 1500 CLERK and SALESMAN

SELECT * FROM emp WHERE sal> 1500 AND (job = 'CLERK' OR job = 'SALESMAN');

See emp table ename is the second letter of the name A

SELECT * FROM emp WHERE ename LIKE '_A%';

IN comparison operators can compare equal to one another in the column


Discover does not mean that all the work MANAGER and CKERK


SELECT * FROM emp WHERE sal BETWEEN 1500 AND 3000;

SELECT * FROM emp WHERE sal> ANY (3000,2000,4000);

SELECT * FROM emp WHERE sal * 12> 50000;

SELECT ename, sal FROM emp WHERE UPPER (ename) = upper ( 'rose');

Removing DISTINCT keyword is used to remove duplicate data in a given column to view the company has a position which, repeated


When we go to repeat multiple columns, these columns indicate that a combination of value is not repeated

SELECT DISTINCT deptno, job FROM emp;

ORDER BY clause is used to sort the result set

ORDER BY clause can only appear in the last SELECT statement.

ORDER BY later can specify a number of fields, sort priority from left to right,

ASC for ascending, the default is ascending, so you can not write

DESC for descending order

According to department number to sort the result set from a large knife


Discover emp table sector wages and names 10 and in descending order

SELECT ename, sal FROM emp WHERE deptno = 10 ORDER BY sal DESC;

When ordering a field containing NULL values, NULL values are considered infinite, so in descending order, NULL values will appear at the top

-------------------------------------------------- -----------------------------------------

Aggregate Functions


SELECT MAX (sal) FROM emp;

*** Aggregate functions ignore NULL values are

SELECT MAX (sal) as "the greatest", MIN (sal) as "minimal" FROM emp;

SELECT AVG (NVL (comm, 0)) as "mean" FROM emp;

SELECT AVG (sal), SUM (sal) FROM emp;

SELECT COUNT (comm) FROM emp; - number of statistics, not statistics

SELECT COUNT (*) FROM emp; - statistics entire table the number of records

The GROUP BY clause is used to record data in a table grouping, the same value for the GROUP BY grouping principles behind a given field seen as a set of

Check each department of the maximum wage Minimum wage:

SELECT MAX (sal), MIN (sal), AVG (sal), SUM (sal), deptno FROM emp GROUP BY deptno;

Aggregate functions, also known as group functions

SQL has a claim

In the SELECT clause red if there is a set of functions, it is not in the set of functions in other fields, GROUP BY clause must be present, but the reverse is not necessary

If the value of each field woes are not repeated, then this field should not normally GROUP BY clause as a packet of reference.

If you specify a GROUP BY clause, the SELECT clause is not in the set of functions in other fields in the GROUP BY clause does not appear in the

The difference between WHERE and HAVING

Same points are used as a filter, but HAVING is used in the second filter, WHERE is used in the first look-up table when the filter conditions.

HAVING is after the first query, the results obtained on the basis of the use of filtered again.


Discover priority

Executive order from clause, from back to front, right to left to put back a small amount of data

Order of execution where clause is from top to bottom, right to left, will be able to filter out the maximum number of records to uninstall the rightmost condition where clause

From left to right order of execution group by group

select clause, less an asterisk try to get the field name

-------------------------------------------------- --------------------------

Relational query

N union query tables, at least N-1 connections conditions, otherwise it will produce a Cartesian product: A total number of records the total number of records the table * Table B, meaningless set of results

Two tables joint inquiry, to add at least one join condition. Because the query is difficult to avoid the emergence of two tables with the same name field, in order to solve this problem,

Use the table name. Field to confirm

If an alias is relatively long, the name of the alias Ke table to simplify

SELECT e.ename, e.sal, d.dname, e.deptno FROM emp e, dept d WHERE e.deptno = d.pdeptno;

SELECT e.ename, e.sal, d.dname, e.deptno FROM emp e JOIN dept d ON (d.deptno = e.deptno);

SQL89 standard, the disadvantages that the connection conditions and filters should write where clause, relatively poor readability, especially multi-table query filters plus more time.

SELECT e.ename, d.DNAME FROM emp e, dept d WHERE d.DEPTNO = e.DEPTNO AND d.DNAME = 'SALES';

SQL92 connection with the proposed multi-table SELECT ... FROM JOIN ... ON ... WHERE (write-only condition)

SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = e.deptno WHERE d.dname = 'SALES'

SQL92 standard suggested that we use in the form of connection when connecting NRL

This will find the join condition defined separately ON clause, and the WHERE clause filters uninstall, readability strong

SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE d.dname = 'SALES';

Outer connection

Since the connection

Other current data table associated with the data table. Constitute a self-join. Since the table is usually designed to be connected to the same type of data showing the existence of a superior-subordinate relationship (tree structure data is usually designed to be self-connection.)

For example: Taobao category. It is designed to be self-ligation.

Usually at design time tables and their relationships to the two fields are important, they are called: primary key foreign key

Usually the first field in each table is the primary key, the primary key data stored in the data generally no relationship, he just used to identify the uniqueness of each of the data, so the primary key requirement is

Stored value and the only non-empty.

Foreign key, to save the relationship records in the table primary key value, for example,

Department table has a field called dept, dept table which is used to save the value of a primary key record that dept emp table is a foreign key

In the relationship, save the foreign key table is usually "one to many" in the "many" side
- MySQL Error Code Complete (Database)
- C ++ How to determine the types of constants (Programming)
- Install the free open source financial software GnuCash 2.6.6 under Ubuntu (Linux)
- How to manage start-up applications in Ubuntu (Linux)
- Linux dynamic libraries and Guide (Programming)
- How Ubuntu Snappy Core 2 running on Raspberry Pi (Linux)
- Nagios plugin installation tutorial of Nrpe (Linux)
- Taught you how to install Ubuntu Linux (Linux)
- Bash difference in single quotes and double quotes (Programming)
- Eclipse Android development environment installation (Linux)
- Spark source code compiler package (Linux)
- CentOS 6.5 installation and configuration Cobbler (Server)
- Json data with double backslashes to a single backslash Json data processing (Programming)
- You really do need to know a variety of programming languages (Programming)
- Storm basic framework for analysis (Programming)
- nginx.conf Optimization (Server)
- About Git (Linux)
- How do I upgrade to Ubuntu 15.04 (Beta) (Linux)
- Tmux Crash Course: Tips and adjustment (Linux)
- The difference Docker save and export commands (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.