What are the sql query statements?

sql query statement: 1. View the table structure [SQL>DESC emp]; 2. Query all columns [SQL>SELECT * FROM emp]; 3. Query the specified column; 4. Query the specified row; 5. Use arithmetic expressions; 6. Use logical operation symbols.

Recommended (free): sql tutorial

sql query statement:

1. Simple query statement

1. View table structure

SQL>DESC emp;

2. Query all columns

SQL>SELECT * FROM emp;

3. Query specified columns

SQL>SELECT empmo, ename, mgr FROM emp;
SQL>SELECT DISTINCT mgr FROM emp; 只显示结果不同的项

4. Query specified rows

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

5. Use arithmetic expressions

SQL>SELECT ename, sal*13+nvl(comm,0)  FROM emp; 
nvl(comm,1)的意思是，如果comm中有值，则nvl(comm,1)=comm; comm中无值，则nvl(comm,1)=0。
SQL>SELECT ename, sal*13+nvl(comm,0) year_sal FROM emp; （year_sal为别名，可按别名排序）
SQL>SELECT * FROM emp WHERE hiredate>'01-1月-82';

6. Use like operator (%,_)

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

SQL>SELECT * FROM emp WHERE ename like 'S__T%';

7. Use In

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

# in the where condition. 8. Query the statement that the field content is empty/non-empty

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

9. Use logical operation symbols

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

10. Sort query results by field value

SQL>SELECT * FROM emp ORDER BY deptno, sal DESC; (按部门升序，并按薪酬降序)

11 . Use case ... when ... then ... end to process the query results

SQL>SELECT CASE a WHEN "original_a" THEN "新命名Aa" WHEN "original_b" THEN "新命名Bb" END AS XXX；

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

Original table

Query results

12. Format date data

SQL>SELECT DATE_FORMAT(start_time,"%Y-%m-%d") as "时间";

2. Complex Query

1. Data grouping (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 (used for query Group statistics of results) and having clause (used to limit group display 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)<2000;

Summary for data grouping:

• a. The grouping function only Can appear in the select list, having, order by clause (cannot appear in where)

• b. If the select statement also contains group by, having, order by, then Their order is group by, having, order by.

• c. If there are columns, expressions and grouping functions in the select column, these columns and expressions must appear in the group by clause, otherwise an error will occur. That is: the column name in the SELECT clause must be a grouping column or column function

3. Multi-table query

SQL>SELECT e.name,e.sal,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno order by d.deptno;
SQL>SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHER e.sal BETWEEN s.losal AND s.hisal;
Select a.*, 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)c
　　on a.id=c.id
where condition3;

4. Self-join (referring to the connection query of the same table)

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

5. Subquery (select statement embedded in other sql statements, also called nested query)

5.1 Single row subquery

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

Query the names of people in the same department as smith in the query table. Because the return result is only one row, use "=" to connect the subquery statement

5.2 Multi-row subquery

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

Query the names and jobs of employees with the same job as department number 10 in the table , salary, department number. Because there are multiple rows in the returned result, "IN" is used to connect the subquery statements.

5.3 Use ALL

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

to query the name, salary and department number of employees whose salary is higher than that of all employees with department number 30. The above two statements are functionally the same, but in terms of execution efficiency, the function will be much higher.

5.4 Use ANY

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

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

5.5 Multi-column subquery

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

5.6 Using subquery 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 The corresponding row number is called rownum.

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

To specify the query column, sort the query results, etc., you 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;

5.9 Merge query (union union, intersect intersection, union all union intersection, minus difference set)

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

Merge query The execution efficiency is much higher than logical queries such as and and or.

5.10 Use subqueries to insert data

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

First create an empty table;

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

Then insert the data with department number 10 in the emp table into the new table myEmp to achieve Batch query of data.

5.11 Using a query 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';

Related free learning recommendations: php programming (video)

The above is the detailed content of What are the sql query statements?. For more information, please follow other related articles on the PHP Chinese website!