Oracle learning path (1): oracle introduction + basic SQL statements + conditional query + sort data theory and case studies

1. Oracle Introduction

Oracle is the first word for English translation of oracle (Oracle bone inscriptions) Unearthed from Yin.. Oracle is the world's largest supplier of information management software and services. It was founded in 1977 and is headquartered in Redwood shore, California. Oracle is famous for its complex relational database products. Oracle's relational database is the world's first database that supports the SQL language.

2. Oracle database and Oracle instance

The Oracle server consists of two parts: the Oracle database and the Oracle instance.

Oracle Database: The files that actually store data on the hard disk are organized together to form a logical whole, that is, the Oracle database. Therefore, in Oracle's view,
"Database" refers to the logical set of files on the hard disk. It must work with instances in the memory to provide data management services.

Oracle instance:The data structure in the physical memory. it consists of a shared memory pool and multiple background processes. The shared memory pool can be accessed by all processes. if you want to access the database (that is, files on the hard disk)
The data in the disk can only be achieved through the instance, and files on the hard disk cannot be directly read.

Differences:An instance can operate databases. At any time, an instance can only be associated with one database. In most cases, only one instance can operate on a database.

3. Note

The SQL language is case insensitive, but the words in the where or having condition quotation marks are case sensitive. SQL statements can be written in one or more rows. Keywords cannot be abbreviated or separated; each sub-statement is generally written by a branch .; Use indentation to improve statement readability.

Multiplication and division take precedence over addition and subtraction. the same priority operator is executed from left to right. The operations in parentheses are executed first; parentheses determine everything!

NULL values are invalid, unspecified, and unknown or unpredictable values. null values are not spaces or 0.

4. case knowledge analysis

Column alias:

Rename a column for easy calculation. Keep up with the column name. You can also add the keyword 'as' between the column name and the alias. the alias uses double quotation marks, so that the alias contains spaces or special characters and is case sensitive.

Connector:

Concatenates columns, columns, and characters. '|. It can be used to 'composite 'columns.

Example:

Select deptno | dname from dept;

 

DEPTNO | DNAME

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

10 ACCOUNTING

20 RESEARCH

30 SALES

40 OPERATIONS

 

String and date:

A string can be a character, number, or date in the SELECT list. The date and character can only appear in single quotes. Each time a row is returned, the string is output once. Characters and dates must be included in single quotes.

The characters are case sensitive and the date format is sensitive. The default date format is DD-MON-RR.

Deduplication of rows:

By default, the query returns all rows, including duplicate rows.. Use the keyword 'in the SELECT clause'DISTINCT'Delete duplicate rows.

Example: select distinct (job) from emp;

5. SQL and SQL * Plus

You can use SQL * Plus:

Describes the table structure, edits SQL statements, and executes SQL statements. Save the SQL statement in the file and save the SQL statement execution result in the file. Execute the statement in the saved file. Load the text file into the SQL * Plus editing window.

6 Other comparison operations

Use the LIKE operation to select a similar value;

The selection conditions can contain characters or numbers:% Represents zero or multiple characters (any character); _ represents one character; '%' and '-' can be used at the same time; you can use the ESCAPE identifiers to select the '%' and '_' symbols. To avoid special symbols, use ESCAPE characters. For example, convert [%] to [\ %], [_] to [\ _], and then add [ESCAPE '\']. Use IS (NOT) NULL determines the NULL value.

Example:

Select * from emp where sal between 1000 and 2000; empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 ward salesman 7698 22-2 month-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9 month-81 1250 1400 30 7844 turner salesman 7698 month-81 1500 0 30 7876 adams clerk 7788 month-87 1100 7934 7782 miller clerk 1300 month-82 10 select * from emp where sal in (1250,1600 ); empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 ward salesman 7698 22-2 month-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9 month-81 1250 1400 30 select * from emp where comm is null; empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7369 smith clerk 7902-17-12 months-80 800 20 7566 jones manager 7839 02-4month-81 2975 20 7698 blke MANAGER 7839 -81 2850 30 select * from emp where comm is not null; empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7499 allen salesman 7698 20-2 month-81 1600 300 30 7521 ward salesman 7698 22-2 month-81 1250 500 30 select * from emp where ename like 'a % '; empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7499 allen salesman 7698 20-2 month-81 1600 300 30 7876 adams clerk 7788 23-5 month-87 1100 20 select * from emp where ename like '_ L % '; empno ename job mgr hiredate sal comm deptno ---------- --------- -------------- ------ 7499 allen salesman 7698 20-2 month-81 1600 300 30 7698 blake manager 7839 01-5 month-81 2850 30 7782 CLARK MANAGER 7839-81 2450 10 select job_id from jobs where job_id like 'st \ _ % 'escape '\'; JOB_ID----------ST_CLERKST_MAN

7. Priority

You can use parentheses to change the priority order.

8. order by clause

Sort by order by clause

ASC (ascend): ascending

DESC (descend): Descending

The order by clause ends with the SELECT statement.

You can use column sorting that is not in the SELECT list.

Example:

select empno,d.deptno,ename,dname,sal from emp e,dept d where e.deptno=d.deptno order by sal asc,d.deptno desc;      EMPNO DEPTNO ENAME      DNAME            SAL                                                   ---------- ------ ---------- -------------- -----                                                         7369     20 SMITH      RESEARCH         800                                                         7900     30 JAMES      SALES            950                                                         7876     20 ADAMS      RESEARCH        1100                                                         7521     30 WARD       SALES           1250                                                         7654     30 MARTIN     SALES           1250                                                         7934     10 MILLER     ACCOUNTING      1300                                                         7844     30 TURNER     SALES           1500                                                         7499     30 ALLEN      SALES           1600                                                         7782     10 CLARK      ACCOUNTING      2450                                                         7698     30 BLAKE      SALES           2850                                                         7566     20 JONES      RESEARCH        2975                                                         EMPNO DEPTNO ENAME      DNAME            SAL                                                   ---------- ------ ---------- -------------- -----                                                         7788     20 SCOTT      RESEARCH        3000                                                         7902     20 FORD       RESEARCH        3000                                                         7839     10 KING       ACCOUNTING      5000     

Reprinted from http://blog.csdn.net/tianyazaiheruan/article/details/8759805