Quick oracle Query

SQL definition: SQL is a general data processing language specification for databases. It can perform the following functions: extract and query data, insert and modify data, and delete data, and generate and modify database objects, database Security Control, database integrity and data protection control.

SQL classification:
DDL-Data Definition Language (CREATE, ALTER, DROP, DECLARE)
DML-data manipulation language (SELECT, DELETE, UPDATE, INSERT)
DCL-Data Control Language (GRANT, REVOKE, COMMIT, ROLLBACK)

Db2 Data Type
CHAR (): the maximum length of a fixed-length string is 254.
VARCHAR (): the maximum length of a variable-length character is 4000
SMALLINT: the length of a short integer is 2 bytes.
INTEGER: the length of an INTEGER is 4 bytes.
REAL: Single-precision floating point 32-bit approximate value
DOUBLE: DOUBLE Precision Floating Point 64-bit approximate value
DECIMAL (m, n): Numeric precision is m DECIMAL is n
DATE: DATE and Time
TIME: Date and TIME
TIMESTAMP: Date and Time

DDL-database definition language: directly submitted.
CREATE: used to CREATE database objects.
DECLARE: Except for creating temporary tables used only in the process, the DECLARE statement is very similar to the CREATE statement. The only object that can be declared is a table. And must be placed in the user's temporary tablespace.
DROP: You can delete any objects created with CREATE (database object) and DECLARE (table.
ALTER: allows you to modify the information of certain database objects. The index cannot be modified.
The following describes the basic syntax based on objects:
1. database:
Create database: CREATE database DATABASE-name [using codeset codeset TERRITORY territory]
Note: The code page is incorrect.
Delete database: drop database dbname
2. Table:
Create a new table:
Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)
Create a new table based on an existing table:
A: create table tab_new like tab_old
B: create table tab_new as select col1, col2... From tab_old definition only
Modify Table:
Add a column:
Alter table tabname add column Col type
Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type. Add primary key:
Alter table tabname add primary key (COL)
Delete primary key:
Alter table tabname drop primary key (COL)
Drop table tabname
3. tablespace:
Create Table space: Create tablespace tbsname pagesize 4 K managed by database using (File 'file' size)
Add tablespace to container: Alter tablespace tablespace_name add (File 'filename' size)
Note: This operation is irreversible and cannot be deleted after being added to the container. Therefore, pay attention when adding the container.
Delete tablespace: Drop tablespace tbsname
4. Index:
Create an index: Create [unique] index idxname on tabname (COL ....)
Delete index: drop index idxname
Note: The index cannot be changed. To change the index, you must delete it and recreate it.
5. View:
Create view: create view viewname as select statement
Delete view: drop view viewname
Note: Only columns of the reference type can be modified in the view to change the column range. Other definitions cannot be modified. When the base table of a view is drop, the view becomes invalid.

DML-database manipulation language, which does not implicitly commit the current transaction. whether to submit the transaction depends on the environment settings.

SELECT: Query compliant data from the table
Note: The connection problem in the condition prevents the flute Product

DELETE: deletes data from an existing table.
UPDATE: UPDATE the data of an existing table.
INSERT: INSERT data to an existing table
Note: whether to directly submit DELETE, UPDATE, and INSERT statements depends on the environment in which the statement is executed.
Pay attention to the full transaction logs during execution.
1. When selecting, pay attention to index predicates and non-index predicates. Try to use index predicates on indexed columns.
Indexable interpretation of predicate types
Col transport con Y branch stands for>, >=, =, & lt ;=, & lt;, but & lt;> is not Indexable.
Col between con1 and con2 Y must be final in the matching series.
Col in list Y only matches one column
Col is null Y
Col like 'xyz % 'Y fuzzy match % is later.
Col like '% xyz' N fuzzy match % above.
Col1 distinct Col2 N Col1 and col2 from the same table
Col sort Expression N for example: c1 (c1 + 1)/2
Both Pred1 and Pred2 Y Pred1 and Pred2 are Indexable, which refer to columns with the same index.
In addition to (c1 = a or c1 = B), Pred1 or Pred2 N can be considered as c1 in (a, B)
Not Pred1 N or any equivalent form: Not between, Not in, Not like, and so on.
Examples of using indexes:
& Lt; 1>. Single Table index query:
A: select * from t1 where c1 = 10;
How to retrieve a c1 column without an index
How to retrieve an index on the c1 Column
B: select * from t1 where c1 = 10 and c2 between 5 and 10 and c3 like 'a %'
Only the C1 column has an index
Separate indexes on C1, C2, and C3 Columns
Joint indexes are available on columns C1, C2, and C3.
You can add index matching rules and advanced rules here, which is hard to understand !!
& Lt; 2>. Two or more table index queries:
(1) execute a query using the nested loop method
A: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t1.c2 = t2.c3
Describes the query method to see which column requires the most index.
B: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t2.c4 = 10 and t1.c2 = t2.c3
(2) Use merged connections to perform connection queries
Example: Select t1.c1, t1.c2, t2.c3, t2.c4 from T1, T2 where t1.c1 = 10 and t2.c4 = 10 and t1.c2 = t2.c3
(3) Rules or methods for connecting to more than three tables
(2) group by: implements the simple grouping function. When group by is used, query the and constants in the selected column except the group by column, use grouping functions for other columns. You can use grouping functions to implement columns that do not appear in group by, such as Min and Max.
(3) Use of having to make conditional judgment on some grouping columns.
(4) The order by clause enables SQL statements to sort the returned rows in order when the query results are displayed. The order of the returned rows is determined by the value of the expression specified by the order by clause.
2. Delete: delete records from a table
Syntax format:
Delete from tablename where (conditions)
3. insert: insert records into the table
Syntax format:
Insert into tablename (col1, col2 ,...) Values (value1, value2 ,...);
Insert into tablename (col1, col2 ,...) Values (value1, value2 ,...), (Value1, value2 ,...),......
Insert will not wait for any program and will not cause locking.
4. Update:
Syntax format:
Update tabname set (col1 = values1, col2 = values2 ,...) Where (conditions );
Note: The update speed is slow. You must create an index on the corresponding column.

DCL-Data Control Language

Grant-grant User Permissions
Revoke-Revoke User Permissions
COMMIT-COMMIT transactions to make database modifications permanent
ROLLBACK: rolls back the transaction, removes all modifications made after the previous COMMIT command, and restores the database content to the status after the previous COMMIT execution ..

1. GRANT: All users or administrators GRANT access permissions to other users.
Syntax format:
Grant [all privileges | privileges,...] On tabname | viewname to [public | user,...]
2. REVOKE: cancels the access permission of a user.
Syntax format:
Revoke [all privileges | privileges,...] On tabname | viewname from [public | user,...]
Note: you cannot cancel any permissions of instance-level users. They are not authorized by grant but by group.
3. COMMIT: records the modifications made in the transaction to the database in a permanent manner.
Syntax format:
Commit [work]
4. ROLLBACK: undo all the changes made since the last submission.
Syntax format:
Rollback [work]

Introduction to advanced SQL
I. Computing term used between queries
A: UNION operator
The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2.
B: Random t operator
The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: INTERSECT Operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.
Note: The query results of several computation words must be consistent.
Ii. External Connection
A. left outer join:
Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table.
B: right outer join:
Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.
C: full outer join:
Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

Note: Composite outer connections are connected from left to right. The result set of the left join and the right join
Iii. Super grouping and mobile Functions
A: grouping sets: Used to form multi-level groups in a single SQL statement.
Example: Select company_id, node_id, count (customer_id) from customer group by grouping sets (company_id, node_id)
B: rollup: multiple groups can be formed in a single database operation.
Example: Select company_id, node_id, count (customer_id) from customer group by rollup (company_id, node_id)
Note: rollup operations are not interchangeable. It is important to specify the order of user groups.
C: Cube: generate all groups in the grouping table.
Example: Select company_id, node_id, count (customer_id) from customer group by cube (company_id, node_id)
D: over: Mobile functions can help you analyze mobile data.
Select date, AVG (qty) over (order by date rows between 1 preceding and 1 following) as values from sale

Appendix: common functions
1. type conversion functions:
Convert to numeric type:
Decimal, double, Integer, smallint, real
Hex (arg): convert to the hexadecimal representation of the parameter.
Converted to string type:
Char, varchar
Digits (arg): returns the string representation of arg, which must be decimal.
Converted to date and time:
Date, time, timestamp
2. Date and Time:
Year, quarter, month, week, day, hour, minute, second
Dayofyear (arg): returns the Daily Value of arg within the year.
Dayofweek (arg): returns the Daily Value of arg within a week.
Days (arg): returns the integer representation of the date, from-01-01.
Midnight_seconds (arg): the number of seconds between midnight and arg.
Monthname (arg): returns the month name of arg.
Dayname (arg): returns the week of arg.
3. String functions:
Length, lcase, ucase, ltrim, rtrim
Coalesce (arg1, arg2 ....) : The first non-null parameter in the returned parameter set.
Concat (arg1, arg2): connects two strings: arg1 and arg2.
Insert (arg1, POs, size, arg2): returns one. arg1 is deleted from the POs and inserted into this position.
Left (ARG, length): returns the leftmost length string of Arg.
Locate (arg1, arg2, & lt; POS>): searches for the location where arg1 first appeared in arg2. If the POS is specified, the location where arg1 first appeared starts from the POS of arg2.
Posstr (arg1, arg2): returns the position where arg2 first appeared in arg1.
Repeat (arg1, num_times): returns the string that arg1 is repeated for num_times.
Replace (arg1, arg2, arg3): replace all arg2 in arg1 with arg3.
Right (ARG, length): returns a string consisting of the Left length bytes of Arg.
Space (ARG): returns a string containing Arg spaces.
Substr (arg1, POs, & lt; length>): returns the length starting from the POs position in arg1. If the length is not specified, the remaining characters are returned.
4. mathematical functions:
Abs, count, max, min, sum
Ceil (arg): returns the smallest integer greater than or equal to arg.
Floor (arg): returns the smallest integer less than or equal to the parameter.
Mod (arg1, arg2): returns the remainder of arg1 divided by arg2. the symbol is the same as that of arg1.
Rand (): returns a random number between 1 and 1.
Power (arg1, arg2): returns the arg2 Power of arg1.
Round (arg1, arg2): rounding to truncation. arg2 is the number of digits. If arg2 is negative, rounding to the number before the decimal point.
Sigh (arg): returns the symbol indicator of arg. -1, 0, 1 indicates.
Truncate (arg1, arg2): truncates arg1. arg2 is the number of digits. If arg2 is a negative number, the arg2 before the decimal point of arg1 is retained.

5. Others:
Nullif (arg1, arg2): If the two parameters are equal, null is returned. Otherwise, parameter 1 is returned.