Mysql query operations

1. select * fromtable_name for all records. * indicates all fields in the table. 2. Keywords distinctselectdistinct field name from table name are used to query non-repeated records; 3. Keywords whereselect * from table name where condition for condition query; conditions after where are a field comparison, enable

1. select * from table_name for all records. * indicates all fields in the table. 2. Keywords distinct select distinct field name from table name are used to query non-repeated records; 3. Keywords where select * from table name where condition are used for condition query; the condition following the where clause is a comparison of a field.

1. Select All records

Select * from table_name;

* Indicates all fields in the table.

2. query records that are not repeated

Distinct

Select distinctField nameFromTable Name;

3. Conditional Query

Where keyword

Select * from table name where condition;

The condition after where is a field comparison. You can use =,>, <,> =, <=, and ,! = And Other comparison operators; multiple conditions can also use or, and other logical operations.

4. Sorting and Restriction

Use the keyword orderDesc (descending), asc (ascending), and limit (part of the sorting result is displayed)

Order by can be followed by multiple sorting fields, and each sorting field can have a different sorting order.

For example, the records in the emp table are sorted in the descending order of the wage sal.

Select * from emp order by sal;

Select * from emp order by sal, deptno desc; (sal ascending, septno descending)

Sort by the first field first. When the first field is the same, sort by the second field.

Select... limit offset_start, row_count;

Offset_start indicates the start offset. The default value is 0. Row_count indicates the number of displayed rows.

Example: select * from emp order by sal limit 1, 3;

The first three records are displayed. The starting offset is 1 and the second record is displayed.

OrderBy and limit are usually used together to control the paging display of records.

5. Aggregation

In many cases, You Need To summarize the number of people in the company or the number of people in each department.

Syntax:

Select Field 1, Field 2... Function name from Table NameWhere where_condition1Group Field 11 and field 12With rollupHavingWhere_condition2;

The function name indicates the aggregate operation to be performed, that is, the aggregate function. Commonly Used Aggregate functions include sum (sum), count (*) (number of records), max (maximum value), and min (minimum value ).

The group by keyword indicates the fields to be classified and aggregated,

The having keyword indicates that the results after classification are filtered by conditions.

The difference between having and where is that having is used to filter the aggregated results, while where is used to filter records before aggregation. If the logic permits, we can use where to filter the results first, so that the result set becomes smaller, greatly improving the efficiency of aggregation, and finally checking whether having is used for filtering based on logic.

Select sum (sal), max (sal), min (sal) from emp;

6. Table join

To display fields in multiple tables at the same time, you can use table join to implement this function.

Table join is divided into inner join and outer join. The main difference between them is that inner join only selects matching records from two tables, the external connection selects other unmatched records. We usually use internal connections.

For example, you can query the names of all employees and their departments. Because the names and departments of employees are placed in the emp table and dept table, you need to use table join for query.

Select ename, deptname from emp, dept where emp. deptno = dept. deptno;

(Ename is the field in the table emp, and deptname is the field in the dept table)

Outer join is divided into left join and right join. (The keywords are left join and right join)

Left join: contains all records in the left table or even records that do not match the records in the right table.

Right join: contains all records in the right table, or even records that do not match in the left table.

Example: select ename, deptname from emp left join deptOn emp. deptno = dept. deptno;

Select ename, deptname fromDeptRight joinEmpOn emp. deptno = dept. deptno;

Ename is the field in the table emp, and deptname is the field in the dept table.Contains all records in the table emp. The preceding two statements are equivalent.

7. Word Query

In some cases, when performing a query, the condition is the result of another select statement. subquery is required. The keywords used for subqueries mainly include in, not in, =, and ,! =, Exists, not exists, and so on.

Select * from emp where deptno in (select deptno from dept );

(This indicates that all records in the table emp are deleted except that the Department corresponding to the dept table does not exist .)

In some cases, subqueries can be converted into Table connections. For example, the preceding statement can be written as follows:

Select emp. * from emp, dept where emp. deptno = dept. deptno;

8. Record Union

We often encounter such an application. After the data of the two tables is queried according to certain query conditions, the results are merged and displayed. In this case, you need to use the union and union all keywords to implement this function. Difference between union and union all: union all combines the results, while union performs distict once on the results after union all to remove duplicate records.

Select * from table1Union | union all select * from table2;

For example:

① Select deptno from empUnion all select deptno from dept;

② Select deptno from empUnion select deptno from dept;

The record in ① will have repeated records, and in ② there will be no Repeated Records.