Oracle_sql Training: First unit: SELECT statement: ①: String join operator: | | ②: Removing duplicate rows:distinct second unit: Condition restriction and sort ①: Keyword: where②: comparison operator: =,>,<,between...and...,in,like,is null &NBSP: Use like to make a fuzzy match: You can use% or _ as a wildcard;% represents 0 or more characters. _ Represents a single character. Escape for the identity escape character escape '/'. ③: Order by &NBSP;ASC (ascending default)/desc (reverse) Third unit: single-line function ①: Case conversion: Lower () a--a UPPER () a--a Initcap () First--first②: string manipulation function &NBS P results concat (' Hello ', ' world ') &NBSP;HELLOWORLDSUBSTR (' Hellowo Rld ', 1,5) hellolength (' HelloWorld ') &NBSP;10INSTR (' HelloWorld ', ' W ') & Nbsp;6lpad (salary,10, ' * ') *****24000rpad (Salary, *, ' * ') & nbsp 24000*****trim (' H ' From ' HelloWorld ') Elloworldtrim (' HelloWorld ') helloworldtrim (' Hello Wor LD ') hello World③: numeric operator function: function &NBS P results round (45.926, 2) 45.93TRUNC (45.926, 2) 45.92mod (1600, 300) 10④: date operation function: function &NBS P , &NB Sp results months_between (' 01-sep-95 ', ' 11-jan-94 ') &N Bsp 19.6774194add_months (' 11-jan-94 ', 6) &N Bsp 11-jul-94next_day (' 01-sep-95 ', ' FRIDAY ') &NBSP ; &nbSp 8-sep-95next_day (' 01-sep-95 ', 1 nbsp 3-sep-95next_day (' 1995-09-01 ', 1) &NBSP ; Ora01861:literal does not Match Format stringnext_day (to_date (' 1995-09-01 ', ' Yyyy-mm-dd '), 1) 3-sep-95last_day (' 01-feb-95 ') , &NB Sp 28-feb-95round (' 25-jul-95 ', ' MONTH ') &N Bsp 1-aug-95round (' 25-jul-95 ', ' yea R ') &nbSp , &NB Sp 1-jan-96trunc (' 25-jul-95 ', ' MONTH ') &N Bsp 1-jul-95trunc (' 25-jul-95 ', ' year ') &NBSP ; , &NB Sp 1-jan-9⑤: Different types of data conversion functions: Privacy translation Rules for Oracle data types: For assignment actions: from to VARCHAR2 or CHAR NUMBERVARCHAR2 or CHAR datenumber &NBSP ; varchar2date &NBSP ; varchar for an expression comparison operation can only be: from to VARCHAR2 or CHAR number VARCHAR2 or CHAR Date to_char () function: Date-to-string conversion to_char (DAT E, ' Format_model '); date formatting element meaning yyyy 4 digit year &NBSP ; English description of year mm & nbsp;2 Digit months month English description month Mon & nbsp Three letter English description month abbreviation DD 2 digit date Day &nbs P English description Day of the week dy Three-letter English description of the week abbreviation HH24:MI:SS AM minutes and seconds of formatting ddspth &NBS P English description of the day of the month FM format keyword, optional to_char () function: numeric to character Conversion of strings To_char(number, ' Format_model '); numeric formatting element meaning 9 represents a number 0 force display 0$ put a dollar placeholder l use a floating-point local currency symbol. Displays a decimal placeholder that displays a thousand-bit placeholder to_number ( ) function: string-to-number conversion to_number (char[, ' Format_model '); To_date () function: string-to-date conversion to_date (char[, ' Format_model ') other commonly used single-line function functions Nvl (EXPR1, EXPR2) If Expr1 is empty, This returns EXPR2NVL2 (Expr1, EXPR2, EXPR3) If Expr1 is empty, this returns EXPR3 (2nd result) otherwise returns EXPR2NULLIF (EXPR1, EXPR2) if expr1 and expr2 are equal, return empty COALESCE (EXPR1, expr2, ..., exprn) if Expr1 is not empty, return to EXPR1, end, otherwise calculate EXPR2 until a non-null value is found, or if all is null, you can only return null conditional expression: DECODE function: DECODE (col|expression, Search1, Result1 [, SEARCH2, Result2,...,] [, default]) Unit four: Multi-table correlation query: 1, equals link 2, unequal link. 3, external link (left link, right link). 4, self-junction unit fifth: Grouping calculation functions and group by sentence grouping calculation functions: The input of a single row is a rowset (a rowset can be all rows of a table, or a group of rows after grouping by a dimension), as opposed to a one-line function or a multiline function. And the output is a value; grouping calculation function (Common): include 1, sum (sum) 2, averaging (AVG) 3, COUNT (count) 4, standard deviation (STDDEV) 5, Variance (VARIANCE) 6, Large value (MAX) 7, small value (MIN) 8, Count function Description: Function usage count (*) returns the number of rows for all rows that satisfy the selection criteria, including rows with null values and duplicate row count (expr), which returns the number of rows that satisfy the selection criteria and that the expression is not empty. Count (DISTINCT expr) returns the number of rows that satisfy the selection criteria and that the expression is not empty and not duplicated. 9. Group ByselWhen grouping calculation function expressions and other standalone fields are selected at the same time in an ECT query statement, other fields must appear in the GROUP BY clause, otherwise it is not legal. You cannot use a grouping calculation function expression in a where condition, and when such a requirement occurs, use the HAVING clause unit sixth: subquery selectselect_list fromtable Whereexproperator ( Selectselect_list from table); unit seventh: DML statement dml:data manipulation Language, data manipulation language; In simple words, add, delete, Change the statement insert into table [(column [, Column ...])] VALUES (value [, value ...]); UPDATE table setcolumn= value[, column = value, ...] [WHERE condition];D elete [from]table [wherecondition]; nineth: Database objects-table Rule requirements: Table Naming requirements and naming requirements for the columns in the table: 1, must begin with a letter 2, Length cannot exceed 30 characters 3, can only contain a–z, A–z, 0–9, _, $, and #4, cannot be the same name as an existing object in the database 5, cannot use oracle database retention Data type description VARCHAR2 (size) Variable long string char (size) fixed length string number (p,s) Variable Long value date datetime long variable grow string, large enough to 2GCLOB variable grow string data, large can be 4GRAW and long RAW Binary data blob binary data, large to 4G bfile stored in external file binary data, large to 4growid64 18-bit length of data, The address timestamp to identify the row is a date type that is accurate to the minute of the minute (the enhanced data type provided after 9i) INTERVAL year to month represents the interval for several months (the enhanced data type provided after 9i-extremely rare) INTERVAL day to Second represents the interval of a few hours and a few seconds (enhanced data types provided after 9i-extremely rare) syntax for changing tables: TimAdd: ALTER TABLE tableadd (column Datatype[default expr] [, column datatype] ...); Change column: Alter TABLE tablemodify (column Datatype[default expr] [, column datatype] ...); Delete columns: ALTER TABLE tabledrop (column); Delete tables: drop tableName; Change table name: RENAME oldtablenameto newtablename; Tenth: Database objects-the concept of constraint constraints: The Oracle database uses "constraints" to prevent illegal "additions and deletions" to data in database tables. Common constraints include the following: not null (non-null constraint) unique (uniqueness constraint) PRIMARY key (PRIMARY KEY constraint) FOREIGN key (Foreign KEY constraint) check (custom constraint) create constraint syntax individually: ALTER TABLE tablenameadd constraint ConstraintName ConstraintType (Column1,...); Delete constraint: ALTER TABLE Tablenamedrop CONSTRAINT constraintname; Invalid/effective constraint: ALTER TABLE tablename disable CONSTRAINT constraintname cascade; alter TABLE tablenameenable CONSTRAINT constraintname; What constraints exist in the query system: SELECT constraint_name, Constraint_type, Search_conditionfrom user_constraints Unit 11th: Database Object-View View concept: Sometimes we need to correlate multiple tables to get a query result set, and sometimes we want toTo write complex conditions to get a desired result set, we don't want to write a complex SQL statement every time we think about this data. We can create these result sets as the syntax created by the View-view view: Create [OR REPLACE] [force| Noforce] VIEW view[(alias[, alias] ...) As Subquery[with CHECK OPTION [CONSTRAINT constraint]][with READ only [CONSTRAINT constraint]] delete view: Drop View Viewna me; 12th: Database objects-concepts of sequence, index, synonym sequence: Sometimes we define a column in a table as the primary key, and when we insert data into a table, the assignment to the primary key field requires uniqueness. We want to have a self-growing type of database object, and every time we get it, it automatically grows to ensure that the next fetch is definitely not the same value, so we can make it easier for the Oracle database to provide the "sequence" object to meet our requirements Sequence creation: Create SEQUENCE sequence[increment by N][start with N][{maxvalue n| Nomaxvalue}][{minvalue n| nominvalue}][{cycle | Nocycle}][{cache n| nocache}]; change sequence definition: Alter SEQUENCE sequenceincrement by 20MAXVALUE 999999nocache nocycle; Delete sequence: Drop SEQUENCE sequence; Index Creation Example: CREATE INDEX Emp_last_name_idxon employees (last_name); Synonym concept: When database User A wants to access a table in database user B Table1, you need to prefix select * from B.table1 but we want to access a table in another database by Db-link we need to add the @ suffix select * FROM [email protected] In order to simplify the notation in the program, OracleFor synonyms, that is, you can create a synonym under a user to point to the Table1 under the B user, you can access the synonym at a later time, without prefixing the syntax: CREATE synonym Table1 for b.table1 Unit 13th: Controlling User Rights syntax: GRANT object_priv [(columns)]on object to{user|role| Public}[with GRANT OPTION] (delivery rights) Retract permission: REVOKE {privilege [, privilege...]| All}on objectfrom {user[, user...]| role| public} unionunion all Keep duplicate records INTERSECT Fetch intersection MINUS Take the difference set select employee_id,job_idfrom employeesminusselect employee_id,job_id from job_history; 14th: Group by sentence enhancement ①: Use Rollup in GROUP by to generate regular group summary rows and grouping subtotals: 1, regular group rows, 2, 3, layered small subtotals, Rollup followed by n fields, will be n+1 sub-group, Reduce one field to group from left to right, then Union②: Use Cube in GROUP by to produce rollup result set + multidimensional crosstab data source:
Oracle SQL Knowledge Summary
