SQL query Statement collation

First of all, this is a table structure of my query for the following statement

T_user Insert the following data

Insert the following data into the T_depart

T_role Insert the following data

Well, after we've built the table, we start the database query statement

A simple query statement

1. View table structure

DESC T_user

2. Querying all Columns

SELECT * from T_user

3. Querying a specified column

SELECT Userno,username from T_user

SELECT DISTINCT Departno from T_user only show items with different results

4. Querying a specified row

SELECT * from T_user WHERE username= ' admin '

5. Using an arithmetic expression

SELECT * from T_user WHERE departno>1

6. Using the LIKE operator (%,_)

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

SELECT * from T_user WHERE username like ' a__m% '

7. Using in (multi-condition) in the Where condition

SELECT * from T_user WHERE Userno in (' 1 ', ' 3 ')

8. Query for empty/non-empty statements of field contents

SELECT * from T_user WHERE Userno are not NULL

9. Using Logical operation Symbols

SELECT * from T_user WHERE username= ' test1 ' and departno= ' 2 '

10. Sort the results of the query by the values of the fields

SELECT * from T_user ORDER by Userno DESC (in descending order of Userno, without DESC in ascending order)

Complex Query Statements

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 (Grouping statistics for query results) and HAVING clause (used to restrict grouping display results)

Sql>select Deptno,max (SAL), AVG (SAL) from the EMP GROUP by Deptno;

Sql>select deptno, Job, avg (SAL), MIN (SAL) from EMP Group by Deptno,job have AVG (SAL) <2000;

For a summary of data groupings:

A. The grouping function can only appear in the select list, having, ORDER BY clause (cannot appear in the where)

B. If the SELECT statement contains both group BY, have, order by, then the order by IS group BY, have, order by.

C. In the Select column, if there are columns, expressions, and grouping functions, the columns and expressions must appear in the GROUP BY clause, or an error will occur.

Using GROUP BY is not a precondition for having a having.

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;

4. Self-connect (refers 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. Subqueries (SELECT statements embedded in other SQL statements, also called nested queries)

5.1 Single-line subquery

Sql>select ename from the EMP where deptno= (SELECT deptno from EMP where Ename= ' Smith '); the name of the person in the query table that is in the same department as Smith. Because only one row is returned, use "=" to concatenate the subquery statement

5.2 Multi-row subqueries

Sql>select Ename,job,sal,deptno from EMP where job in (SELECT DISTINCT job from EMP where deptno=10), query table with the same staff with department Number 10 The name, work, salary, department number of the worker. Because there are multiple rows in the returned result, the subquery is concatenated with "in".

In and exists: exists () the subquery that follows is called the correlated subquery, which is not the value of the returned list. Just returns the result of a ture or false, which runs the main query once, and then queries the subquery for its corresponding results. If the ture is output, the reverse is not output. Then, according to each row in the main query to go to the subquery query. In () is the subquery that returns the result set, in other words the execution order is different from the 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 match the requirements. The output that meets the requirements, or vice versa.

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,d Eptno from the EMP where sal> (SELECT MAX (SAL) from EMP where deptno=30), the name, salary, and department number of the employee whose salary is higher than the department number 30th for all employees. The above two statements are functionally identical, but the function is much higher on the execution efficiency.

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,d Eptno from the EMP where sal> (SELECT MIN (SAL) from EMP where deptno=30), query the employee whose salary is higher than the department number 30th (as long as it is higher than the salary of an employee), Salary and department number. The above two statements are functionally identical, but the function is much higher on the execution efficiency.

5.5 Multi-column subqueries

Sql>select * from EMP where (job, deptno) = (SELECT job, deptno from emp where ename= ' SMITH ');

5.6 Using 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 Paged Query

Each row of data in a database has a corresponding line number, called RowNum.

Sql>select a2.* from (select a1.*, ROWNUM rn from (SELECT * from emp ORDER by Sal) A1 where rownum<=10) A2 where rn& gt;=6;

Specify query columns, query results sorting, and so on, only need to modify the innermost sub-query.

5.8 Creating 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 set, intersect intersection, UNION All unions + 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 queries are executed much more efficiently than logical queries such as And,or.

5.10 Inserting data using subqueries

Sql>create TABLE myemp (EmpID number (4), name VARCHAR2, sal number (6), Job varchar2, 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 unit Number 10 in the EMP table into the new table myemp, and realize the batch query of the data.

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

SQL query Statement collation