Java-unit03:sql (basic query), SQL (associative query)

Unit03:sql (Basic query), SQL (associative query)

 Column Aliases when the column queried in the SELECT clause is a function or an expression, the name of the field in the result set that is queried is the name of the function or expression. You can add an alias for this column so that the field in the result set uses an alias as the name of the column. If you want the alias to be case-sensitive or contain spaces, the alias can be enclosed in double quotation marks. SELECTEname,sal* A"s Al" fromEMP and,orand withprecedence over or, you can increase precedence by parentheses. SELECTEname,job,sal fromEMPWHERESal> + andJob='salesman'ORJob='Clerk'Like    can be fuzzy match string support two wildcard characters:%: Any character (0-  Multiple) _: Single one character view name The second letter is the employee of a:SELECTEname,sal,deptno fromEMPWHEREEname like '_a%'In (list)  and not (list) are judged in the list or not in the list. Commonly used in judging the results of subqueries.  between...   and... Judge employee information within a range of 1500 to 3000SELECTEname,sal,deptno fromEMPWHERESalbetween  the  and  theAny  ,  AllMates>,>=,<,<=Determine whether the content in a list meets the requirements>any (list): greater than one of the list (greater than minimum)>all (list): greater than List all (greater than Max) any, all is usually used to determine the result of a subquery. The distinct keyword duplicates the record line removal for the specified field value on the result set. SELECT DISTINCTJob fromEMP for multi-column deduplication means that the combination of the values of these columns is not duplicatedSELECT DISTINCTJob,deptno fromEMPOrder    by clause for the result set sort order  by can only be written in the last clause of DQL order  By can have the result set ascending (ASC) or descending (DESC ) by the value of the given field to sort The company's payroll rankings?SELECTEname,sal fromEMPORDER  bySalDESCMulti-field sorting is prioritized by first sorting the result set according to the collation of the first field, sorting the value of the second field when the first field has duplicate values, and so on. SELECTEname,deptno,sal fromEMPORDER  byDeptno,salDESCIf the sorted field contains a null value, NULL is considered the maximum value. SELECTEname,comm fromEMPORDER  byComm aggregate function aggregation function is also known as: multi-line function , grouping function aggregation function is used to count the data in the result set of four pairs of values to statistical function :MAX,MIN,AVG,SUM a function to count the number of records :COUNTstatistics The maximum and minimum wage of a companySELECT MAX(SAL),MIN(SAL) fromEMP Company's payroll sum and average? SELECT SUM(SAL),AVG(SAL) fromEMP See how many people are in the company?SELECT COUNT(ename) fromThe EMP aggregate function ignores null values. SELECT SUM(comm),AVG(NVL (Comm,0)) fromEMPSELECTNVL (Comm,0) fromEMP Statistics a table records the total number of commonly used:SELECT COUNT(*) fromEMPSELECT AVG(SAL) fromEMPWHEREDeptno= -  grouping       GROUP BY clause GroupThe By clause groups the  result set According to the specified field, with the grouping principle as a set of records of the same field value, with which the aggregation function can be carefully counted. See the average salary for each department?SELECT AVG(SAL), Deptno fromEMPGROUP  byDeptno See the average salary and payroll for each position?SELECT AVG(SAL),SUM(SAL), Job fromEMPGROUP  byJobGROUP by can be grouped by multiple fields, the grouping principle for the combination of the values of these fields the same record as a group to see the number of employees in the same position with the department how many people?SELECT COUNT(*), Deptno,job fromEMPGROUP  byDeptno,job View the average salary of the department, provided that the department's average wage is higher thanSELECT AVG(SAL), Deptno fromEMPWHERE AVG(SAL)> -GROUP  byDeptnowhere you cannot use an aggregate function as a filter condition because the filter is not in the right time. WHEREis to filter the data in the first query table, filter by line, and query the records that satisfy the condition. Aggregate functions are built on the basis of the result set to be counted. This is already in place after the. HAVING clause HAVING clause must follow the GROUP BY clause to filter each grouping after group by. you can use aggregate functions and trade-offs based on results. View the average salary of the department, provided that the department's average wage is higher thanSELECT AVG(SAL), Deptno fromEMPGROUP  byDeptno having AVG(SAL)> -What is the maximum wage and minimum wage for a department with an average wage above 2000?SELECT MAX(SAL),MIN(SAL), Deptno fromEMPGROUP  byDeptno having AVG(SAL)> -See how many people have an average salary above 2000?SELECT COUNT(*), Job fromEMPGROUP  byJob having AVG(SAL)> - The result set field of the associated Query Association query is derived from the results of multiple table union queries. The key to the associated query is the connection condition, which is the correspondence between the data in the table. see the name of each employee and the department in which it is located?SELECTE.ename,e.deptno,d.dname fromEMP e,dept DWHEREE.deptno=D.deptno The connection condition in the correlation query is set up with the filter condition! Looking for employees working in New York?SELECTE.ename,d.dname,d.loc fromEMP e,dept DWHEREE.deptno=D.deptno andD.loc='NEW YORK'The Cartesian product, which is usually a meaningless result set, occurs when the link condition is not added or the link condition is not valid in the associated query. The number of records for a Cartesian product is obtained by multiplying the number of records of the table participating in the query. SELECTD.deptno,d.dname fromDept D records that do not meet the connection criteria are not queried. SELECTE.ename,d.dname fromEMP e,dept DWHEREE.deptno=D.deptno See in which city King works?SELECTE.ename,d.loc fromEMP e,dept DWHEREE.deptno=D.deptno andE.ename='KING'N-table associated query in an association query, at least n-1 Connection conditions. Internal connections are also used to complete the associated query. SELECTE.ename,d.loc fromEMP EJOINDept D onE.deptno=D.deptnoWHEREE.ename='KING'Outer connection outer joins can   also list records that do not satisfy the join condition in addition to querying the records that satisfy the join condition when making the associated query. Outer joins are divided into: Left outer connection, right outer connection, full outer join left outer connection: Join the left table as the driver table (all records are listed), then the field value from the right table will be null if the record in the driver table does not satisfy the join condition. SELECTE.ename,d.dname fromEMP Eleft| Right| Full OUTER JOINDept D onE.deptno=D.deptnoSELECTE.ename,d.dname fromEMP e,dept DWHEREE.deptno=D.deptno (+a record from a connection to the current table can correspond to the current table's own number of records such that the setting is self-connected. Self-linking holds the same data as the property but there is a hierarchy of tree-like data that is used by the subordinate relationship. SELECTEmpno,ename,mgr fromEMP See the name of each employee and the name of his boss?SELECTE.ename,m.ename fromEMP e,emp mWHEREE.mgr=M.empno to see where Smith's boss works in the city?

Java-unit03:sql (basic query), SQL (associative query)