Use SQL SELECT statements to retrieve data

I. official files

Book → SQL Language Reference → 19 SQL Statements: SAVEPOINT to UPDATE → SELECT

2. Basic grammar SELECT {[distinct] column name, column name, … } | *

FROM table name

[WHERE condition]

[GROUP BY grouping column name] [HAVING aggregate function] [ORDER BY sort column name ASC | DESC] III, symbol 1, single quotation marks ('): in Oracle, only single quotation marks should be used to enclose text and characters and dates, not numbers (including single and double quotation marks).

2. Double quotation marks ("): in Oracle, single and double quotation marks have different meanings. Double quotes are used to enclose column aliases containing specific characters or spaces. Double quotation marks are also used to put text in date format.

3. Apostrophes ('): in Oracle, apostrophes can also be written as two single quotes adjacent to each other. To find all the supplier names with apostrophes in the middle of the supplier name, you can write code like this:

Select * from l_suppliers where supplier_name like'%'%'

4. The & symbol: in Oracle, the & symbol is often used to indicate a variable. For example, & fox is a variable, a slightly different kind of & & fox. Whenever & fox appears in an Oracle script, you are asked to provide a value for it. With & & fox, you only need to provide the value of the variable to & & fox when it first appears. If you want to use the & symbol as a normal symbol, you should turn this feature off. To turn off this feature, run the following command: set define off, which is a SQLplus command, not a SQL command. SQLplus sets the environment in which SQL runs in Oracle.

5. Double vertical bar (| |): Oracle uses double vertical bar to represent string concatenation function.

6. An asterisk (*): select * means to select all columns, and count (*) means to calculate all rows, representing 0 or any number of characters when representing wildcards.

7. Forward slash (/): in Oracle, it is used to terminate the SQL statement. More precisely, it means "run the SQL code that is now in the buffer". The forward slash is also used as a separator.

8. Not equal to: there are many expressions:! =, ^ =, not xxx=yyy, not (xxx=yyy)

9. Add, subtract, multiply and divide: +-* /

Alias SELECT EMPNO AS "employee number", ENAME AS "employee name" FROM EMP

SELECT EMPNO employee number, ENAME employee name FROM EMP

SELECT EMPNO "employee number", ENAME "employee name" FROM EMP; 5. Remove duplicates-which departments are the employees distributed in, and list the department ID

SELECT DISTINCT DEPARTMENT_ID FROM employees VI, DESCRIBEDESC OWNER.TABLE_NAME

SELECT COLUMN_NAME

DECODE (NULLABLE,'y','', 'NOT NULL') NULLABLE

DECODE (DATA_TYPE

'RAW', DATA_TYPE | |' ('| | DATA_LENGTH | |')'

'CHAR', DATA_TYPE | |' ('| | DATA_LENGTH | |')'

'VARCHAR', DATA_TYPE | |' ('| | DATA_LENGTH | |')'

'VARCHAR2', DATA_TYPE | |' ('| | DATA_LENGTH | |')'

'NUMBER', NVL2 (DATA_PRECISION

DECODE (DATA_SCALE

0, DATA_TYPE | |'('| | DATA_PRECISION | |')'

DATA_TYPE |'('| | DATA_PRECISION | |','| | DATA_SCALE | |')')

DATA_TYPE)

DATA_TYPE) DATA_TYPE

FROM DBA_TAB_COLUMNS

WHERE OWNER = UPPER ('& sch')

AND TABLE_NAME = UPPER ('& tab')

ORDER BY COLUMN_ ID 7. Query example-- *: query all columns

Select * from employees

-- query fixed columns

Select DEPARTMENT_NAME, MANAGER_ID

From departments

-- *: represents in the operator multiplied by

Select FIRST_NAME name

SALARY * COMMISSION_PCT*12 annual bonus

From employees

Where salary > 10000

-- | |: concatenate strings

--'': indicates single quotation marks, displayed in a string, to distinguish between 'closing output'

Select last_name | |''s salary is:'| | salary aaa

From employees

Where salary > 10000

-- use q' [] 'when using multiple single quotation marks to avoid using' 'multiple times.

Select last_name | | Q'['s sa'la'ry' is:]'| | salary aaa

From employees

Where salary > 10000