SQL tutorial series I. Data Retrieval

Before starting this tutorial, you need to prepare two tables, which are known in Oracle as EMP and dept tables.

emp (empno,ename,job,mgr,hirdate,sal,comm,depetno)dept(deptno,dname,loc)

Most SQL statements are suitable for myql

1. retrieve data from a table

Select * from EMP (* indicates that all columns must be returned)

2. retrieve some columns from the table

Select empno, ename from EMP (specify the column name, which can be separated by commas)

3. Search for columns that meet the conditions.

Select * from EMP where empmo = 12300 (restrictions can be added after where) select * from EMP where empno = 67777 and depto = 10 (join and to satisfy multiple conditions) select * from EMP where empno = 898989 or deptno = 11 (meeting the results of employees with employee number 898989 or department Number 10)

3. Alias for Column

Select Sal as salary, comm as commission from EMP (as alias can be omitted)

4. Use aliases in the where clause

Select Sal as salary from EMP where salary> 10000 is incorrect

The SQL statement is executed in the order of first from and then where for filtering. Finally, select is executed. Therefore, if select is not executed before the where statement is executed, you do not know which column the alias is.

The solution is to use the inline view.

select * from (       select sal as salary from emp  ) x where x.salary>10000

It can be seen that this efficiency is extremely low.

5. Use logical condition statements in select

If you have such a requirement, You can query an employee table. If the salary is greater than 1000, the result is high. If the salary is less than 500, the result is lower. If the salary is between the two, the result is OK.

Select ename, Sal, case when Sal <500 then 'lower '# case when then is equivalent to If else when Sal> 1000 then 'high' else' OK 'end as status in Java # Get the column alias from EMP

6 pages

Select * from EMP limit # Return the first five select * from EMP limit # obtain the last five records from the second record.

7. Restrictions on null values

Select * from EMP where COM = NULL # error select * from EMP where comm is null # correct inverse is not null

8. Convert null values to the expected values.

For example, if null is found when you query the bonus, and the expected value is 0, what should you do?

Select coalesce (Comm, 0) from EMP # It means that if comm is null, 0 is returned if it is not null.

It can also be judged by logic.

select case                when comm is null then 0                else comm             end as commissionfrom emp

9 In statement

If you want to query employee information for departments 10 and 20

Select * from EMP where deptno in (10, 20) # The inverse result is a statement like not in which the data is finally converted to select * from EMP were deptno = 10 or dept = 20. If the data volume is large, the query efficiency is very low, should exits and not exits

10. Fuzzy search

Query the information of an employee whose name starts with m

select * from emp where ename like 'M%'

The SQL statement also provides the underscore (_) to match a single character.