The basic query

After data is inserted into the database, you can query the operation, query mainly use SELECT statement, but the syntax is slightly more, the following is the most basic and simplest query:

select * from emp;
Copy the code

The asterisk indicates that all fields are to be queried.

select ename,hiredate from emp;
Copy the code

You can also separate different fields with commas. The above statement indicates that only ename and hiredate are queried.

Query records that do not duplicate

In some cases, the distinct keyword is used to delete the queried records. To show the effect, we insert a duplicate data.

Insert into EMp values (' Peter ','2001-02-02', 2000,1);Copy the code

Now that we have this data, we will delete the department number as follows:

select distinct deptno from emp;
Copy the code

The following information is displayed:

It can be seen that there are clearly two departments whose numbers are 1, but now only 1, 2 and 3 are displayed.

Conditions of the query

Sometimes the user does not need all of the data, but rather filters the data based on certain criteria, using the WHERE keyword. For example, query the record whose department id is 1.

select * from emp where deptno=1;
Copy the code

This shows only the records with department number 1. In addition to = there are other operators such as >, <, >=, <=,! Or, and can be used to connect multiple conditions. For example:

select * from emp where deptno=1 and sal>6000;
Copy the code

Only one record is displayed.

Sorting and limiting

The order by keyword is used if you need to sort a field of data, such as by salary.

select * from emp order by sal;
Copy the code

If you need to reverse order from high to low:

select * from emp order by sal desc;
Copy the code

If only part of the sorted result is displayed, use the limit keyword. For example, it shows the top two earners.

 select * from emp order by sal desc limit 2;
Copy the code

Aggregation operations

In many cases, users need to perform some summary operations, for example, let’s first count the total number of people in the company.

select count(*) from emp;
Copy the code

You can see there are only four people in the company right now. Further, to find out how many people are in each department, type the following:

select deptno,count(*) from emp group by deptno;
Copy the code

As you can see, there are two people in section one, while the others are all supported by one person.

If you want to count the number of people in each department as well as the total number of people:

select deptno,count(*) from emp group by deptno with rollup;
Copy the code

A department whose number is greater than 1.

select deptno,count(*) from emp group by deptno having count(*) >1;
Copy the code

Collect the total salaries of all employees in the company, highest and lowest.

select sum(sal),max(sal),min(sal) from emp;
Copy the code

Table joins

So far all of our queries are concentrated in a single table, so if you need to link two tables together to query, you need to use the table join, generally divided into inner join and outer join, outer connect inside and left outer connect and right outer connect, commonly used is the inner join and left outer connect two ways. The inner join, as the name implies, selects only the intersection part of the two tables that match each other, while the outer join selects other parts that do not match each other.

insert into emp values ('jack','2008-09-01','8500',4);
Copy the code

Create a new table, the department table, and insert some data:

create table dept (deptno int(2),deptname varchar(10));
Copy the code
insert into dept (1,'tech'),(2,'sale'),(3,'hr');
Copy the code

After the operation, the current data of the two tables are as follows:

Let’s start with the inner join:

select ename,hiredate,sal,deptname from emp,dept where emp.deptno=dept.deptno;
Copy the code

This way is using the department number two tables of the communist party of China in some fields connect two tables together, see in all of the data, but will find that the inside of the emp jack the man did not show out, number 4, it is because his department is an illegal, does not exist in the dept table from the data, so only show the intersection.

Here is another useful way to join, left outer join:

select ename,hiredate,sal,deptname from emp left join dept on emp.deptno=dept.deptno;
Copy the code

The difference between the inner join and the inner join lies in that Jack also selects the left outer join, so the data in the left table shall be the criterion when selecting data, even though this row of data does not correspond to the second table.

The subquery

The condition required to perform a query is the result of another SELECT statement. Such a query is called a subquery, which is simply a combination of two statements. Take the following example:

select * from emp where deptno in (select deptno from dept);
Copy the code

If the subquery has a unique number of records, you can also use = instead of in

select * from emp where deptno = (select deptno from dept limit 1);
Copy the code

OK, the simple query statement summed up so much!