(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