• In the database, if you want to query the use of SQL-DML syntax operation, define the complexity of the query, the first use of a single data table (emp), a single table as follows (capital letters are used for fixed syntax components and lowercase letters are used for replaceable parts) :
2.select [distinct] *|Column name [alias], column name [alias],...|Data calculationfromData table [alias]Copy the code
  • fromThe clause identifies the source of the data to be queried;
  • distinct: Eliminates the display of duplicate rows;
  • “*“: Queries the contents of all columns in the table.

1. Example: Query all employee details (contents of all rows and columns in employee table)

select * 
from emp;
Copy the code

2. Example: Query all employee numbers, names, basic monthly salary, now you can’t use “*”, because * indicates all columns

select empno,ename,sal
from emp;
Copy the code



3. Query the number, name, position, and annual income of each employee.

This process must be mathematically calculated, so once you do the math, consider multiplying and dividing before adding and subtracting

select empno,ename,job,sal*12 
from emp;
Copy the code

  • Now the query results did appear, but found that the query results in the display column is not good
  • In order to display the column information more clearly, it is recommended to set an alias for the column:income(Annual income)
select empno,ename,job,sal*12 income
from emp;
Copy the code

  • In fact, aliases can use any character (including Chinese):, but English is recommended.
selectEmpno,ename, name,job,sal*12Annual salaryfrom emp;
Copy the code



4. Check the number, name, position and annual income of each employee of T (200 yuan for food allowance, 100 yuan for telephone allowance, 200 yuan for travel allowance, and 15 yuan at the end of the year)

select empno,ename,job,sal*15+(200+100+200)*12 income
from emp;
Copy the code



5. Check all job listings (job listings arejobField)

select job from emp;
Copy the code

  • It is obvious that the job information must be duplicated, because there must be multiple employees for one job. Can be found inselectClause is appended with a”distinct“Flag: Eliminate duplicate data

  • But fordistinctThe operation has one caveat: it does deduplication only when all columns are duplicated. The following cannot be eliminated:

  • In the previous query is to query the existing data columns in the data table, in fact, can also be directly constant query. But if you want to query with constants, keep the following principles in mind:
  1. If it is a string, the string must be usedSingle quotes('), such as:'hello world; '
  2. If it is a number, it can be written directly, for example:10;

  • If you now want to append an alias to a constant, the alias is not a constant and can be written without single quotes:

  • In a simple query, and if there’s a lot of constants, can be a constant with the combination of content, use “| |” to manipulate the string concatenation

6. Case: String connection

select Name of Employee: || ename || , Position: ' || job info 
from emp;
Copy the code
  • Once connected, only one column of data is returned

  • Conclusion:
  1. A simple query contains only two basic clauses: select, FROM;
  2. To determine the source of the data, the FROM clause takes precedence over the SELECT clause, which controls only the columns of data displayed.