Shulou(Shulou.com)06/01 Report--

Do you know how to use sql query statements? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

Sql query statements: 1, view the table structure [SQL > DESC emp]; 2, query all columns [SQL > SELECT * FROM emp]; 3, query specified columns; 4, query specified rows; 5, use arithmetic expressions; 6, use logical operation symbols.

Sql query statement:

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 show 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 nvl (comm,1) = comm; comm has no value, then nvl (comm,1) = 0. SQL > SELECT ename, sal*13+nvl (comm,0) year_sal FROM emp; (year_sal is alias, 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 by department and descending 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".

Original table

Abcoriginal_a.original_b.

Query result

XXX newly named Aa newly named Bb

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

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".

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'; Thank you for reading! After reading the above, do you have a general understanding of the examples of using sql query statements? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are 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.