Advanced query and difficulties of Oracle and Mysql SQL

Advanced query and difficulties of Oracle and Mysql SQL
Mysql query Yang Kai exclusive Channel OracleSQL 1. Connection query 1. Internal Connection is used to return all records that meet the connection conditions. By default, if no join operator is specified during connection query execution, these join queries belong to internal connections. SQL code 1. SELECT. dname, B. ename from dept a, emp B where. deptno = B. deptno and. deptno = 10; specify the inner join option in the FROM clause, or specify the inner join. SQL code 1. SELECT. dname, B. ename from dept a inner join emp B where. deptno = B. deptno and. deptno = 10; Starting from Oracle9i, if the primary key column of the master table has the same name as the external key column of the slave table, you can use the natural join keyword to automatically perform the internal JOIN operation. SQL code 1. SELECT dname, ename FROM dept NATURAL JOIN emp; 2. LEFT OUTER JOIN is achieved by specifying the LEFT [OUTER] JOIN option. When left Outer Join is used, not only all records meeting the join conditions are returned, but other rows in the left table of the join operators that do not meet the join conditions are also returned. SQL code 1. SELECT. dname, B. ename FROM dept a left join emp B ON. deptno = B. deptno AND. deptno = 10; 3. the right outer join is implemented by specifying the RIGHT [OUTER] JOIN option. When the right outer join is used, not only all rows meeting the join condition are returned, but other rows in the table on the right that do not meet the join condition operator are also returned. SQL code 1. SELECT. dname, B. ename FROM dept a right join emp B ON. deptno = B. deptno AND. deptno = 10; 4. the full outer join is implemented by specifying the FULL [OUTER] JOIN option. When a full outer join is used, not only all rows meeting the connection conditions are returned, but also all other rows that do not meet the connection conditions are returned. SQL code 1. SELECT. dname, B. ename FROM dept a full join emp B ON. deptno = B. deptno AND. deptno = 10; equivalent to removing the where clause SQL code without connection 1. SELECT. dname, B. ename from dept a, emp; 5. when external connections are executed using the (+) operator before Oracle9i, the join operator (+) is used. Although external join operations can be performed using the operator (+. However, we recommend that you use outer join to execute external connections when Oracle starts from Oralce9i. Syntax: SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 (+) = table2.column2; when the (+) operator is used to execute an external join, this operator should be placed at one end of the display of fewer rows (completely satisfying the join condition rows. In a word, (+) should be placed at one end of the right table during left Outer Join. When the right outer join is performed, (+) is placed at one end of the left table. Left Outer Join: SQL code 1. SELECT. dname, B. ename FROM dept a, emp B where. deptno = B. deptno (+) AND B. deptno (+) = 10; right outer join SQL code 1. SELECT. dname, B. ename FROM dept a, emp B where. deptno (+) = B. deptno AND. deptno (+) = 10; 2. recursive query 1. syntax SQL code 1. select * from .... Where [result filtering Condition Statement] 2. start with [Start condition filter statement] 3. connect by prior [intermediate record filtering Condition Statement] 2. example SQL code 1. select * from company t Where t. flag = 1 2. start with t. company_id = 50500000 3. connect by prior t. company_id = t. parent_id Description: SQL code 1. select [level], column, expr from table [where condition] 2. [start with] // [start point] 3. [connect by prior + primary key = foreign key or foreign key = primary key]. top-down: Put the primary key on the left and the foreign key on the right. B. Bottom-up: Put the primary key on the right and the foreign key on the left. C. level (pseudo-column) level, which is not fixed. 3. Change the database Time Format 1. Temporarily change alter session set nls_date_format = 'yyyy-MM-DD HH24: MI: ss' 2. permanent change in the registry [HKEY_CODE_MACHINE \ SOFTWARE \ ORACLE], add the NLS_DATE_FORMAT with the value of YYYY-MM-DD HH24: MI: SS. 3. In Unix. add the following content to the profile file: NLS_DATE_FORMAT = YYYY-MM-DD HH24: MI: SS export NLS_DATE_FORMAT attached: Under SQLPLUS, implement medium-English character set conversion SQL code 1. alter session set nls_language = 'American '; 2. alter session set nls_language = 'simplified CHINESE '; 4. Copy the table and data. 1) Create Table SQL code 1. create table test as select * from dept; -- copy data and structure from a known table. create table test as select * from dept where 1 = 2; -- copy the structure from a known table but not including data to create a view: SQL code 1. CREATE OR REP Lateral VIEW dept_10 as select empno, ename, job, sal, deptno FROM emp WHERE deptno = 10 order by empno; create a Materialized VIEW: SQL code 1. create materiallized view summary_emp as select deptno, job, avg (sal) avgsal, sum (sal) sumsal FROM emp group by cube (deptno, job); Materialized VIEW Management is used for summary, pre-computed, replicated, or distributed data objects can be used in large databases to increase the query speed of tables involved in SUM, COUNT, AVG, MIN, and MAX, as long as statistics are created on the Materialized View Management, the QUERY optimizer automatically uses the Materialized View Management. This feature is called query rewrite (QUERY rewriting ). different from common views, Materialized View Management Stores data, occupying the physical space of the database. 2) insert data without using the column list: SQL code 1. insert into test select * from dept; 3) use DEFAULT to insert data: If a column has a DEFAULT value, the DEFAULT value is used. If the column does not have a DEFAULT value, NULL is automatically used. SQL code 1. insert into dept values (10, 'marketing Department ', default); 4) use a subquery to insert data to insert all the content found into SQL code 1. insert into employee (empno, ename, SQL, deptno) 2. select empno, ename, sal, deptno from emp 3. where deptno = 10; insert the detected content into SQL code 1. insert into employee (empno, ename, SQL, deptno) 2. sel Ect empno, 'yang kai', sal, deptno from emp 3. where deptno = 10; when loading a large volume of data, use the direct Loading Method: SQL code 1. insert/* + APPEND */into employee (empno, ename, SQL, deptno) 2. select empno, ename, sal, deptno from emp 3. where deptno = 10; 5) insert data into multiple tables. From Oracle9i, you can use the INSERT statement to INSERT data from a table to multiple tables at the same time. Use the ALL operator to execute the subquery after the INTO clause in each Condition Clause. SQL code 1. insert all when deptno = 10 then into t_dept10 2. when deptno = 20 then into t_dept20 3. when deptno = 30 then into t_dept30 4. when job = 'cler' then into t_cler5. else into t_other 6. select * from emp; use the FIRST operator to insert multiple tables. If the data already meets the preceding conditions and has been inserted to a table, this row of data will not be used again in subsequent inserts. SQL code 1. insert first when deptno = 10 then into t_dept10 2. when deptno = 20 then into t_dept20 3. when deptno = 30 then into t_dept30 4. when job = 'cler' then into t_cler5. else into t_other 6. select * from emp; 5. Multi-column subquery multi-column subquery refers to the subquery statement that returns multi-column data. When a multi-column subquery returns a single row of data, you can use a single row comparison character in the WHERE clause. When a multi-column subquery returns multiple rows of data, multi-row comparison operators (IN, ANY, ALL) must be used IN the WHERE clause ). For example, all employees who have the same department and position as 10000 are displayed: SQL code 1. SELECT ename, job, sal, deptno FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE id = 10000); when the paired comparison is executed, because the data of multiple columns must be matched at the same time, you must use multi-column subqueries. Show all employees whose salaries and allowances exactly match the salaries and allowances of Department 30 employees: SQL code 1. SELECT ename, sal, comm, dptno FROM emp WHERE (sal, nvl (comm,-1) IN (SELECT sal, nvl (comm,-1) FROM emp WHERE deptno = 30); multiple multi-row subqueries should be used for non-paired comparison. If the displayed salary matches the Department 30 salary list and the subsidy matches all employees in the department 30 subsidy list: SQL code 1. SELECT ename, sal, comm, deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30) AND nvl (comm,-1) IN (SELECT nvl (comm, -1) FROM emp WHERE deptno = 30); 6. subqueries related to subqueries in the from clause and subqueries are subqueries that need to reference the primary query table column, is implemented through the EXISTS predicate. Show all employees working in new york: SQL code 1. SELECT ename, job, sal, deptno FROM emp where exists (SELECT 1 FROM dept WHERE dept. deptno = emp. deptno AND dept. loc = 'New YORK '); When a subquery is used in the FROM clause, the subquery is treated as a view. Therefore, it is also called an embedded view. If the employee information is displayed higher than the average salary of the Department: SQL code 1. SELECT ename, job, sal FROM emp, (SELECT deptno, avg (sal) avgsal FROM emp group by deptno) dept WHERE emp. deptno = dept. deptno AND sal> dept. avgsal; 7. Merge query set operators include UNION, union all, INTERSECT, and MINUS. When using the set operator, make sure that the number of columns and Data Types for Different queries match. 1. For LOB, VARRAY, and nested columns, the set operator is invalid. 2. For LONG columns, the UNION, INTERSECT, and MINUS operators are invalid. 3. If the selection list contains an expression, you must specify the column alias for it. (1) The UNION operator is used to obtain the UNION of two result sets. When this operator is used, repeated rows in the result set are automatically removed. The results in the first column are sorted. SQL code 1. SELECT ename, sal, job FROM emp WHERE sal> 2500 union select ename, sal, job FROM emp WHERE job = 'manager'; (2) the union all operator obtains the UNION set of two result sets, but does not cancel duplicate values or sort by any column. SQL code 1. SELECT ename, sal, job FROM emp WHERE sal> 2500 union all select ename, sal, job FROM emp WHERE job = 'manager'; (3) the INTERSECT operator is used to obtain the intersection of two result sets. When this operator is used, only data that exists in both result sets is displayed and sorted in the first column. SQL code 1. SELECT ename, sal, job FROM emp WHERE sal> 2500 intersect select ename, sal, job FROM emp WHERE job = 'manager'; (4) MINUS is used to obtain the difference set of two result sets. Sort by the first column. SQL code 1. SELECT ename, sal, job FROM emp WHERE sal> 2500 minus select ename, sal, job FROM emp WHERE job = 'manager'; 8. CASE expressions and reverse queries Use CASE expressions, you can avoid calling the process to complete the condition branch operation. SQL code 1. SELECT ename, sal, case when sal> 3000 THEN 3 WHEN sal> 2000 THEN 2 ELSE 1 END grade FROM emp WHERE deptno = 10; by default, WHEN performing a query operation, only recently submitted data can be seen. Starting from Oracle9i, you can use the Flashback Query feature to view the data submitted at a certain time point in the past. NOTE: If reverse query is used, the database must be displayed in the UNDO management mode, and the initialization parameter undo_retention limits the retention time of UNDO data. SQL code 1. SELECT ename, sal FROM emp as of timestamp to_timestamp ('2017-01-01 12:12:00 ', 'yyyy-MM-DD HH24: MI: ss') WHERE ename = 'clark '; 9. Use the WITH clause to reuse a subquery starting from Oracle9i. You can use the WITH clause to specify a name for the subquery and complete all tasks in a statement to avoid using a temporary table. If the total salary of a department is higher than 1/3 of the total salary of an employee, the Department name and total salary are displayed. SQL code 1. WITH summary AS (SELECT dname, SUM (sal) AS dept_total FROM emp, dept WHERE emp. deptno = dept. deptno group by dname) 2. SELECT dname, dept_total FROM summary WHERE dept_total> (3. select sum (dept_total) * 1/3 FROM summary); 10. oracle deduplication SQL code 1 for records. delete from [TABLE_NAME] 2. where rowid not in (3. select min (ROWID) FROM [TABLE_NAME] 4. group by [COL1, COL2, COL3. ..] 5 .); count the total number of non-repeated hotel names SQL code 1. select count (DISTINCT _id) FROM t_app_travel_hotel_comment; 11. The NULL values after SQL sorting can be assumed that the values in all content are the largest. Therefore, the value of NULL after sorting in ascending order is the last and the value of NULL after sorting in reverse order is the first! When "nulls first" is specified, the NULL values are listed at the beginning, whether in ascending or descending order. When "nulls last" is specified, whether in ascending or descending order, NULL values are arranged at the end. SQL code 1. SELECT * FROM t ORDER BY x DESC NULLS LAST;