Shulou(Shulou.com)05/31 Report--

Editor to share with you what the common sql query sentence is, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Common sql query statements are: 1, view table structure [SQL > DESC emp;]; 2, query all columns [SQL > SELECT * FROM emp]; 3, query specified columns [SQL > SELECT empmo,]; 4, query specified rows [SQL > SELECT * FROM].

Common sql query statements are:

1. Simple query statement

1. View table structure

SQL > DESC emp

two。 Query all columns

SQL > SELECT * FROM emp

3. Query the specified column

SQL > SELECT empmo, ename, mgr FROM emp

SQL > SELECT DISTINCT mgr FROM emp; only shows items with different results

4. Query the specified row

SQL > SELECT * FROM emp WHERE job='CLERK'

5. Use arithmetic expressions

SQL > SELECT ename, sal*13+nvl (comm,0) FROM emp

Nvl (comm,1) means that if there is a value in comm, then there is no value in nvl (comm,1) = comm; comm, then nvl (comm,1) = 0.

SQL > SELECT ename, sal*13+nvl (comm,0) year_sal FROM emp; (year_sal is an alias and can be sorted by alias)

SQL > SELECT * FROM emp WHERE hiredate >'01-January-82'

6. Use the like operator (%, _)

% represents one or more characters, _ represents a character, [charlist] represents any single character in the character column, [^ charlist] or [! charlist] any single character that is not in the character column.

SQL > SELECT * FROM emp WHERE ename like 'thanks to T%'

7. Using In in where condition

SQL > SELECT * FROM emp WHERE job IN ('CLERK','ANALYST')

8. Query statements with empty / non-empty field content

SQL > SELECT * FROM emp WHERE mgr IS/IS NOT NULL

9. Use logical operation symbols

SQL > SELECT * FROM emp WHERE (sal > 500or job='MANAGE') and ename like'J%'

10. Sort query results by the value of the field

SQL > SELECT * FROM emp ORDER BY deptno, sal DESC

(ascending order by department and descending order by salary)

11. Use case... When... Then... End processes query results

SQL > SELECT CASE a WHEN "original_a" THEN "newly named Aa" WHEN "original_b" THEN "newly named Bb" END AS XXX

Select the a field in the table and name it XXX. When the content of an is original_a, the content is displayed as "new named Aa".

twelve。 Format date data

SQL > SELECT DATE_FORMAT (start_time, "% Y-%m-%d") as "time"

Second, complex query

1. Data packet (max,min,avg,sum,count)

SQL > SELECT MAX (sal), MIN (age), AVG (sal), SUM (sal) from emp;SQL > SELECT * FROM emp where sal= (SELECT MAX (sal) from emp); SQL > SELEC COUNT (*) FROM emp

2. Group by (for grouping statistics of query results) and having clause (for restricting grouping display of results)

SQL > SELECT deptno,MAX (sal), AVG (sal) FROM emp GROUP BY deptno;SQL > SELECT deptno,job, AVG (sal), MIN (sal) FROM emp group by deptno,job having AVG (sal) SELECT e.namethee.salpendd.dname FROM emp e, dept d WHERE e.deptno=d.deptno order by d.deptnostSQL > SELECT e.enameparente.saljins.grade FROM emp eJr Salgrade s WHER e.sal BETWEEN s.losal AND s.hisal Select A. on a.id=b.id left outer join, b.x, c.y from a left outer join (Select * from tablex where condition1) b on a.id=b.id left outer join (Select * from tabley where condition2) con a.id=c.idwhere condition3

Make a left join query with the selected data as the new table; from: https://q.cnblogs.com/q/67530/

4. Self-join (a join query for the same table)

SQL > SELECT er.ename, ee.ename mgr_name from emp er, emp ee where er.mgr=ee.empno

5. Subqueries (select statements embedded in other sql statements, also known as nested queries)

5.1 single-row subquery

SQL > SELECT ename FROM emp WHERE deptno= (SELECT deptno FROM emp where ename='SMITH')

Look up the names of people in the same department as smith in the table. Because there is only one row of the returned result, connect the subquery statement with "="

5.2 Multi-row subquery

SQL > SELECT ename,job,sal,deptno from emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10)

Look up the name, job, salary and department number of the employee in the table who is the same as the job with department number 10. Because there are multiple rows of results returned, join the subquery statement with "IN".

The difference between in and exists: the subquery after exists () is called a related subquery, and it does not return the value of the list. Just return the result of a ture or false, which works by running the main query once, and then going to the query and its corresponding results in the subquery. If it is ture, it will output, otherwise, it will not output. Then go to the subquery according to each row in the main query. The subquery after in () returns the result set, in other words, the execution order is not the same as exists (). The subquery first produces the result set, and then the main query goes to the result set to find the list of fields that meet the requirements. The output that meets the requirements, otherwise it will not be output.

5.3 using ALL

SQL > SELECT ename,sal,deptno FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30); or SQL > SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MAX (sal) FROM emp WHERE deptno=30)

Check the name, salary and department number of the employee whose salary is higher than that of all employees whose salary is higher than that of all employees with department number 30. The above two statements are functionally the same, but the execution efficiency of the function is much higher.

5.4 using ANY

SQL > SELECT ename,sal,deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno=30); or SQL > SELECT ename,sal,deptno FROM emp WHERE sal > (SELECT MIN (sal) FROM emp WHERE deptno=30)

Check the name, salary and department number of an employee whose salary is higher than that of any employee with department number 30 (as long as it is higher than a certain employee). The above two statements are functionally the same, but the execution efficiency of the function is much higher.

5.5 Multi-column subquery

SQL > SELECT * FROM emp WHERE (job, deptno) = (SELECT job, deptno FROM emp WHERE ename='SMITH')

5.6 use of subqueries in the from clause

SQL > SELECT emp.deptno,emp.ename,emp.sal,t_avgsal.avgsal FROM emp, (SELECT emp.deptno,avg (emp.sal) avgsal FROM emp GROUP BY emp.deptno) t_avgsal where emp.deptno=t_avgsal.deptno AND emp.sal > t_avgsal.avgsal ORDER BY emp.deptno

5.7 paging query

Each row of data in the database has a corresponding row number, which is called rownum.

SQL > SELECT a2.* FROM (SELECT a1.times, ROWNUM rn FROM (SELECT * FROM emp ORDER BY sal) A1 WHERE ROWNUM=6

Specifying query columns, sorting query results, and so on, only need to modify the innermost subquery.

5.8 create a new table with query results

SQL > CREATE TABLE mytable (id,name,sal,job,deptno) AS SELECT empno,ename,sal,job,deptno FROM emp

Merge queries (union Union, intersect intersection, union all Union + intersection, minus difference)

SQL > SELECT ename, sal, job FROM emp WHERE sal > 2500 UNION (INTERSECT/UNION ALL/MINUS) SELECT ename, sal, job FROM emp WHERE job='MANAGER'

The execution efficiency of merge query is much higher than that of logical query such as and,or.

5.10 insert data using subqueries

SQL > CREATE TABLE myEmp (empID number (4), name varchar2 (20), sal number (6), job varchar2 (10), dept number (2))

Build an empty table first

SQL > INSERT INTO myEmp (empID, name, sal, job, dept) SELECT empno, ename, sal, job, deptno FROM emp WHERE deptno=10

Then insert the data of department number 10 in the emp table into the new table myEmp to realize the batch query of the data.

5.11 query is used to update the data in the table

SQL > UPDATE emp SET (job, sal, comm) = (SELECT job, sal, comm FROM emp where ename='SMITH') WHERE ename='SCOTT'; is all the content of this article "what are common sql query statements?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.