Oracle experiences 1 -- oracle introduction @ basic SQL statement @ conditional query @ sort data theory and case studies

Oracle experiences 1 -- oracle introduction @ basic SQL statement @ 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. These files 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 data in a database (that is, files on a hard disk), you must use an instance to access the data. You cannot directly read files on the hard disk.
Difference: an instance can operate on a database. 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 'distinct 'in the SELECT clause to 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 condition 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: [SQL] 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 20
 
7934 miller clerk 7782 23-1 month-82 1300 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-12-80 800 20
 
7566 jones manager 7839 2975-81 20
 
7698 blake manager 7839 01-5 months-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 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 months-81 2850 30
 
7782 clark manager 7839-81 2450 10
 

Select job_id from jobs where job_id like 'st \ _ % 'escape '\';
 
JOB_ID
 
----------
 
ST_CLERK
 
ST_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:
[SQL]
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