Shulou(Shulou.com)06/01 Report--

This chapter includes:

Use the select statement to retrieve information from one or more database tables

Use arithmetic expressions to evaluate

Use the WHERE clause to filter rows

Sort the rows retrieved from the table

1. Single table query data

The SELECT statement is used to retrieve information from a database table.

Select * from customers; / / Select all the columns in a table

Select c.name from cutomers c; / / Select a column in the table

Use the where clause to filter rows

You can use the WHERE clause in a query to specify the rows you want to retrieve.

Select * from customers where customer_id = 2

Understand line identifiers

Each row in the oracle database has a unique row identifier ROWID. Row identifiers are used within the Oracle database to store the physical location of rows. Rowid is often referred to as a pseudo column. Does not appear in the table structure

Understand the line number

Another pseudo column of oracle is ROWNUM, which returns the row number of each row in the result set

SQL > select rowid,rownum,f.serial_no,f.code_id from fault_barrier_base_code f where rownum

< 20;ROWID ROWNUM SERIAL_NO CODE_ID-------------------------------------------------------------------------------- ---------- --------- ----------------------------------------AAGlTlAAOAAJEhUAAA 1 5 YJ_SOAPAAGlTlAAOAAJEhUAAB 2 6 SC_SOAPAAGlTlAAOAAJEhUAAC 3 9 88AAGlTlAAOAAJEhUAAD 4 22 12AAGlTlAAOAAJEhUAAE 5 23 52AAGlTlAAOAAJEhUAAF 6 24 82AAGlTlAAOAAJEhUAAG 7 25 94AAGlTlAAOAAJEhUAAH 8 29 21AAGlTlAAOAAJEhUAAI 9 30 REPLACE_NOCHECK_SECROSCODEAAGlTlAAOAAJEhVAAA 10 10 89AAGlTlAAOAAJEhVAAB 11 13 RECORD_MONITOR_LOGAAGlTlAAOAAJEhVAAC 12 14 92AAGlTlAAOAAJEhVAAD 13 15 DEFAULT_URL_FALGAAGlTlAAOAAJEhVAAE 14 32 98AAGlTlAAOAAJEhVAAF 15 37 CHECK_DEFAULT_SECRESCODE_DESCAAGlTlAAOAAJEhVAAG 16 38 NOT_FAULT_BARRIER_ENABLEAAGlTlAAOAAJEhVAAH 17 39 NO_FAULT_BARRIER_CRONAAGlTlAAOAAJEhVAAI 18 40 GROUP_SPECIAL_MSISDNAAGlTlAAOAAJEhVAAJ 19 41 GROUP_SPECIAL_BIPCODE_ACTIVECODE19 rows selected 执行算术运算 Oracle在SQL语句中使用算术表达式来进行算术运算，包括+、-、*、/ 日期运算：在2016年6月6日上加上一天 SQL>

Select to_date (sysdate) + 7 as "one week later" from dual;one week later-2016/6/13SQL > select to_date (sysdate)-7 as "one week ago" from dual;one week ago-2016/5/30

Column operations on tables are also supported in arithmetic operations.

Suppresses the display of the duplicate line keyword DISTINCT

Compare values with =, or! =, =, ANY (compare with any value), ALL

Use SQL operators, LIKE (fuzzy query), IN (range query), BETWEEN (range query), IS NULL,IS NAN (non-numeric), IS INFINITE

Use the logical operators x AND y (return true when both x and y are true), x OR y (return true if one of x and y is true), NOT x (return true if x is false)

Sort rows using the ORDER BY clause

Use the ORDER BY clause to sort the rows retrieved by the query. The ORDER BY clause must come after the FROM or WHERE clause (if the WHERE clause is provided).

Execute the SELECT statements of the two tables

A join can be used to join any number of tables. Number of connections = total number of tables used in the query-1

Select * from fault_barrier_code;select * from fault_barrier_sec_rspcode sec,fault_barrier_code fc where sec.bip_code = fc.bip_code and sec.act_code = fc.act_code and sec.plat_code = fc.plat_code

3. Connection conditions and connection types

Join conditions (join condition) can be divided into two categories, depending on the operators used in the connection:

Equal connections (equijion) use operators in connections.

Unequal join (non-equijoin) uses operators other than the equal sign in the connection, such as, BETWEEN

There are 3 types of connections

Inner join (inner join) returns a row only if the column in the join contains a value that meets the join condition.

Outer join (outer join) returns a row even if a column in the join condition contains a null value

Self join returns rows connected to the same table

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.