One of the SQL query series-basic query (I)

(1) retrieve all rows and columns from the table

Problem

View All data in a table.

Solution

Use the SELECT statement for the table and the special character "*".

select * from emp

Discussion

The "*" symbol in SQL has special significance. You can use this function to return each column in a specified table. Because there is no WHERE clause, each row in the table is returned. Another alternative is to list each column separately.

select empno,ename,job,sal,mgr,hiredate,comm,deptnofrom emp

In interactive queries, it is easier to use SELECT. However, when writing program code, it is best to specify each column separately, and their performance is the same. However, the specified columns displayed are more clear about which columns are returned in the query. Similarly, other users, rather than code writers, are more likely to understand the query.

(2) retrieve some rows from the table

Problem

Query rows that meet specific conditions from a table.

Solution

Use the WHERE clause to specify the rows to be retained. For example, you need to view the information of all employees whose department number is 10.

select *from empwhere deptno = 10

Discussion

You can use the WHERE clause to retrieve only the rows of interest to the user. If the expression in the WHERE clause is true in a behavior, the row is returned.

Most vendors support common operators, such as =, <,>, <=,> = ,!, <>. In addition, to query rows that meet multiple conditions, you can use AND, OR parentheses.

(3) Search for rows that meet multiple conditions

Problem

Search for rows that meet multiple conditions.

Solution

Use the WHERE clause AND the or and clause. For example, if you want to query the information of all employees in department 10, the information of all employees who have obtained the Commission, and the information of employees whose salaries do not exceed $2000 in department 20, you can use the following code:

select *from empwhere deptno = 10      or comm is not null      or sal <= 2000 and deptno=20

Discussion

You can use the combination of AND, or and parentheses to query rows that meet multiple conditions. In the solution example, the WHERE clause finds the rows that meet the following conditions:

• The DEPTNO field is 10, or
• The COMM field is NULL, or
• The maximum salary is $2000, and The DEPTNO field is 20.

(4) retrieving specific columns from a table

Problem

Queries the values of a specific column in a table instead of all columns.

Solution

Specify columns of interest. For example, you can only query the employee's name, Department number, and salary:

select ename,deptno,salfrom emp

Discussion

You can specify columns in the SELECT clause to ensure that no irrelevant data is returned. This is important when retrieving data through the network, because it can avoid time waste when retrieving unnecessary data.

(5) provide meaningful names for columns

Problem

Modify the column name returned by the query to make it more readable and easy to understand. For example, you can query the salaries and commissions of each employee.

select sal,commfrom emp

What is "sal? Is it sale? Or someone's name? What is "comm", indicating "communication? The result labels should be clearer.

Solution

To change the column name of the query result, you can use the AS keyword in this format:Original column nameASNew column name.Some databases do not need to use AS, but all databases accept this usage.

select sal as salary, comm as commissionfrom emp