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

1. Basic query operation # simple query select * from emp; / / query all records in emp select empno, job,ename from emp; / query some fields in the emp table select ename as names from emp; / / use field aliases

Deduplicating distinct

Select distinct deotno from emp; / / remove duplicate lines (for fields)

Sort order by

# default is ascending order (asc), descending order (desc) select * from emp order by sal desc; / / sort by salary in descending order select * from epm order by sal, deptno / / multi-field sort select sal*12 annsal from epm order by annsal / / alias sort

Conditional query where

Select * from emp where sal > 1000; / / select * from emp where sal with a salary greater than 1000 > 1000 and sal5000 or sal0 or 1 / 1; / / identity

Ps: precedence of operators: arithmetic > join > comparison > logic

Group query (group by)

Description: divide the data in the table into groups

Syntax: select field from where condition group by field order by field

# example: select field from where condition group by field order by field ps: in group by grouping, select clause, cannot write fields that group by does not have. Unless these fields are used in aggregate functions

Filter having

Description: the result after filtering the group can only appear after the group by

# examples: select deptno, count (1), avg (sal) from emp group by deptno having avg (sal) > 2000select avg (sal) avg_sal,deptno from emp group by deptno having avg_sal > 2000

Execution process: from-where-group by-having-select-order by

Pagination

# example: select * from emp limit 0,5; / / start with line 1 and take 5 lines

Fuzzy query

For example: select * from emp where ename like's% connectors%: indicates 0 ~ multiple arbitrary characters _: represents 1 arbitrary character 2. Inner connection and outer connection (1) Internal connection: # syntax: select table1.column,table2.column from table1,table2 where table1.column= table2.columnselect dept.DEPTNO,ename, dname from emp inner join dept on emp.DEPTNO = dept.DEPTNOselect emp.ename,dept.dname from emp inner join dept using (deptno)

Note: in general, if two pieces are connected by a foreign key, use the first and second query methods, and use one query method if it is not a foreign key connection. Fill in the using in the third way, fields with the same field names in both tables, and common column fields appear only once (that is, remove duplicate fields)

Characteristics of internal connections:

The field values that all appear in the associated table will eventually appear in the result set, the join is independent of the order (2) the outer join:

Left outer connection

Description: in the process of joining, two tables return not only the rows that meet the criteria, but also the rows in the left table that do not meet the conditions, which is called the left outer join.

# example: select deptno, dname,empno,ename from dept left join emp using (deptno) / / left external connection

Right outer connection

Description: in the process of joining, two tables return not only the rows that meet the criteria, but also the rows in the right table that do not meet the conditions, which is called the right outer join.

# example: select deptno, dname,empno,ename from dept right join emp using (deptno) / / right external connection

Full external connection

Description: in the process of joining, two tables return not only the rows that meet the criteria, but also the rows that do not meet the conditions in the two tables. This kind of join is called all-outside join. (Cartesian product)

# example: select deptno, dname,empno,ename from dept full join emp using (deptno) / / full external connection

Natural connection

Description: special equivalent connection: do not need to declare equal fields, will automatically match

# example: select * from emp natural join dept; (equivalent connection)

Characteristics of external connections

There is a master-slave division, which is related to the connection order of which is the master table, traverse which table, and then match the corresponding records with the slave table, merge, do not match with null fill 3. Subquery

Syntax: select field from table where expression operator (subquery field).

Features: the subquery is executed once before the main query, and the main query uses the results of the subquery

Use:

 -single-row subquery: if the result of using a subquery is 1 row, you can use the comparison operator (>

2000 and e1.deptno=e.deptno)

The difference between in and exists:

 -in: execute the subquery first, return the results to the main query, and the main query continues to execute

 -execute the main query first, match the values of the main query in the subquery in turn, and return true or false according to whether the query matches. If it is a true connection display, it will not be displayed.

Timing of use of subqueries and associated queries

 -subquery: query conditions and results are placed in the same table

 -associative queries, query conditions and results are distributed across multiple tables

3. Joint query

Keywords: union, union all.

Difference:

 -union: weight removal occurs

 -union all: no de-weighting occurs

Usage:

# example: select * from emp where sal > 2000 union select * from emp where deptno > 20select * from emp where sal > 2000 union all select * from emp where deptno > 20

Usage requirements: the federated result set must be consistent (the two tables are the same, and the fields of the query are the same), otherwise an error will occur.

On the intersection, difference and union of sql:

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.