Oracle Learning (ii) _ Multi-table operation

--First part: SQL Basic--ch1 Simple Query--CH2 query basic concept--CH3 data filtering--Part Two: Multi-table Operation--CH4 set theory--ch5 inner Connection--CH6 outer connection--ch7 subquery--Part three: Data grouping  --ch8 Simple Statistics--CH9 data packet--ch10 packet data filtering-Part IV: SQL function--CH11 built-in function--ch12 CASE expression-Part V: DML statement--ch13 Insert data--ch14 Modify data --ch15 Delete Data--------------------------------------------------------------------------the first part: SQL Foundation "ch1 Simple Query"--1.1 Select Introduction Select is a core part of SQL. Used to extract information from the database. The select operation can be subdivided into: SELECT statement Select expression Select query three-part--1.2 SELECT statement A SELECT statement consists of several separate keywords, called clauses.                     Consisting of the following five clauses: (1) The SELECT clause specifies the columns you want in the query result set. < necessary > (2) The FROM clause specifies the table or view from which the column in the SELECT clause is removed.                     < necessary > (3) The WHERE clause is used to filter the rows returned from the FROM clause. The optional]where clause is followed by an expression, called a predicate. The value may be true, false, or unknown. You can use comparison operators, logical operators, and special operators to validate these expressions. (4)           GROUP BY clause if statistical functions are used in the SELECT clause to obtain summary information, the GROUP BY clause can be used to separate the information into groups. [Optional] (5) Having clauses are generally used in combination with group by to filter the information after grouping. The optional]having clause also follows an expression whose value may be true, false, or unknown, or you can use comparison operators, logical operators, and special operators to validate the expressions. --1.3 Basic Select query 1) query single-column select column_name from table_name; SELECT customer_id From customers;2) query multi-column comma-delimited, column can not be in order select Column_name,column_name,... from table_name; Select customer_id, first_name, last_name from customers;3) query all columns SELECT * FROM table_name; SELECT * from customers;--1.4 de-duplicated rows select product_type_id from Products; SELECT DISTINCT product_type_id from Products; SELECT DISTINCT product_id, product_type_id from the products; Select UNIQUE product_type_id from products;--1.5 The result set returned by the SELECT statement is generally unordered; the order of the records in the result set depends primarily on their physical location in the table. ORDER BY clause--asc default ascending desc descending--nulls First nulls last--sorted by column name, column name can not appear in the SELECT clause select * FROM Customers ORDER by customer_id ; Select First_Name, last_name from Customers order by customer_id;--alias sort select first_name, last_name, salary as SL from E  Mployees ORDER by SL desc;--Sort By column ordinal select First_Name, last_name, salary from Employees order by 3 desc;--Multi-column sort select * from Productsorder by Product_type_id,product_id;--asc/descselect * FROM Customers ORDER by Last_Name DESC, dob asc;--nulls fi Rst/nulls Lastselect * FROM Customers ORDER by phone nulls First, DOB NULLS last; "CH2 query basic concept"--2.1select clause content (select list) 1) column reference (table_name.) column_name2) literal value string literal ' 51testing ' This is a sample SQL ' 12345 ' "You" D Better study hard ' numeric literal 911-3.1415.88883.4e5 date literal dates ' 2012-12-21 ' to_date (' 2012-10-10 20:20:20 ', ' yyyy-mm-dd hh24:mi:ss ')-- 2.2 Expressions like to say: I love you! mouth + words and expressions meaning operator + gesture (operator) + operand (data)-&gt     ; The value of the operand (data) is called an expression by using the operator (operator) to concatenate a certain rule with a meaningful formula. The operand (data) itself is an expression, because it is itself a value. Two properties of an expression: 1) The expression has a type; 2) the expression returns a value. When an expression defines an operation that completes, it returns a value to the SQL statement that participates in further processing. --2.3sql the properties of the Data Type table column: Name, type, and each column in the value database has a data type assigned. The data stored in this column can only be in this category. The data type also determines what the user can do with this column. intcharvarchar2numberdate--2.4 expression type 1) A join expression puts two or more items into a string character string Literal | | CHARACTER STRING literalcolumn REFERENCE COLUMN REFERENCE51 | | ' Testing ' first_name | | Last_Name ' This is ' | |                    first_name2) Mathematical expressions for subtraction operations NUMERIC Literal + NUMERIC literalcolumn reference-column REFERENCE */1 + 2pRice * 2price * price 3) Date Expression date Literal + Non-decimal NUMERIC Literal (non-fractional numeric literal) COLUMN R Eference-date Literal COLUMN referencedate ' 2000-1-1 ' + 1dob-1--2.5 use expressions in Select to create in a query Build a computed column find a specified column value filter the rows in the result set join two tables 1) join expression Select First_Name | | Last_Name from Customers;       2) Mathematical expression Select Price, price*2 from products;3) date expression Select Dob, dob-1 from Customers; inductive: value expression (an expression that returns a value of a particular type) Literal value + COLUMN Reference-function * (VALUE expression)/| | CHARACTER | |     NUMERIC +-*/date +---2.6 null value NULL1) indicates no or unknown in a table, if a column in a row has no value, it is called a null value (NULL) NULL and 0, an empty string, or a space. 2) Null value problem: (1) NULL relational operator (= <> > >= < <=), Unknownselect * from Customerswhere customer_id <> NULL, (2) NULL arithmetic operator (+-*/) Nullselect null+3 from dual; "CH3 data filter"--3.1 using where refinement information 1) The WHERE clause is used to filter the SELECT statement from a table Out of the information. Contains a query condition, the filtering of information is done by this query condition. Query criteria provide a selection mechanism to select only the rows you want or to remove unwanted rows from the results. All rows in the logical table defined by the FROM clause areThe filter to pass the query criteria. A query condition contains one or more predicates, each of which is an expression (a relational expression) that examines one or more value expressions and returns True, false, or unknown. You can also concatenate multiple predicates together to form a query condition (a logical expression) with and or or with two logical operators. For a particular row, this line appears in the final result set if the result of the entire query condition is true. SELECT * FROM customers WHERE customer_id = 2; SELECT * FROM Customers WHERE customer_id > 2 and DOB > DATE ' 2000-1-1 '; 2) relational and logical expression relationship expressions: with relational operators (= <> > >= < <= between and in like is NULL) a formula that joins a two-value expression, called a relational expression. Logical expression: a meaningful expression that uses a logical operator (and OR not) to concatenate a relational expression is called a logical representation. Five basic predicates: 1) comparision includes six comparison operators: = <> < <= > >= to compare the size relationship of two value expressions 2) The RANGEBETWEEN predicate can be used to verify that the value of a given value expression falls within a certain range. The range of this value can be determined with two value expressions, which can be separated by the and keyword. Value_expr between A and B <=> value_expr >=a and value_expr <= B3) The MEMBERSHIPIN predicate is used to determine whether the value of a value expression matches a value in a given list. Value_expr in (v1,v2,v3,...) <=>value_expr = v1 or value_expr = v2 or value_expr = V3 ... 4) The Pattern matchlike predicate is used to determine whether a value expression of type string matches a given string. 5) The Nullis NULL predicate is used to determine whether the value of a value expression is empty. --3.2 query Condition definition 1) Compare select * FROM Customers WHERE customer_id <> 2; SELECT customer_id, first_name, Last_nAme from Customers where customer_id > 8;2) range select * FROM Customers WHERE dob between date ' 1968-02-05 ' and date ' 19 70-05-20 '; 3) collection member SELECT * FROM Customers WHERE customer_id in (1, 2, 3); 4) pattern match--wildcard--underline matches any character at the specified position--percent percent match starts at the specified position Any character--escape option allows you to match wildcards with text select * FROM Customers WHERE first_name like ' _o% '; INSERT into customers VALUES (n, ' cai_ Zhiguo ', ' Czg ', sysdate,13812345678); INSERT into Customers VALUES (n, ' Cai|zhiguo ', ' Czg ', sysdate,13812345678); COMMIT; SELECT * FROM customers WHERE first_name like '%i\_zhi% ' ESCAPE ' \ '; SELECT * FROM customers where first_name like '%ih_zhi% ' ESCAPE ' H '; 5) null value SELECT * FROM Customers where DOB is null;6) Notsel ECT * FROM Customers WHERE first_name isn't like ' _o% ';          SELECT * FROM customers WHERE dob are not NULL; --use of more than 3.3 conditions 1) and or two or more conditions can be connected using an and or an OR operator. And: Each row that appears in the result set must satisfy all the conditions that you want to connect to (satisfy all relational expressions). It can be understood that, on the basis of satisfying the first relational expression, the second relational expression is filtered, and then the third relational expression is filtered ... The filter is executed sequentially until the last expression, resulting in the result set returned. And true falsetrue true Falsefalse FALSE falsesElect * FROM Customers WHERE DOB > DATE ' 1970-6-1 ' and customer_id > 3;or: Rows in the result set can be selected as long as any one of the join conditions is satisfied (satisfies any one of the relational expressions). It can be understood that the result set of the first relational expression is satisfied first, and then the result set of the second relational expression is satisfied separately, and then the third one is obtained separately ... The result set of each relationship expression is calculated separately, then the result sets are merged, and the duplicate records are rejected, resulting in the returned results. Or True falsetrue true TrueFalse true Falseselect * FROM Customers WHERE DOB > DATE ' 1970-6-1 ' OR custo   mer_id > 3; 2) and OR use SELECT * FROM Employees e WHERE e.title = ' CEO ' OR e.title = ' CFO ' and e.salary = 600000;3 ' priority operator processing priority: + (positive)-(negative) */+-= <> < <= > >= between in Like is Nullnotandor () changing the precedence of parentheses is handled before conditions without parentheses; two or more parentheses, from left to right A nested parenthesized condition that is handled from inside to outside. SELECT * FROM Employees e WHERE (e.title = ' CEO ' OR e.title = ' CFO ') and e.salary = 600000;4) NULL complements any predicate that deals with null values whose result is unkno Wn If a row is to appear in the result set, its predicate must be true, and if the result is false or unknown, the row is excluded from the result set.     and true false Unknowntrue true false Unknownfalse false Falseunknown unknown false   Unknownor true FALSE unknowntrue true True TrueFalseTrue FALSE Unknownunknown true unknown unknown (expression) not (expression) True Falsefalse Trueunknown Unknown