Data retrieval of the technology of the Big Liar database

1-Simple data retrieval: We can retrieve all the data in a table using the keyword Select,select*from, which is often used in the database.

2-Retrieve the required columns: The result of the statement above contains the data for each column in the table, and sometimes does not require all the column data, such as: we just need to retrieve all the employee numbers, all we need to do is to replace the * number with the field you need, such as the Select Fnumber from table name. If you do not retrieve a list of worthy words, such as I need three columns of value in the middle with ', ' The number is divided on the line, is not very simple, it is so simple.

3 Column Aliases: Sometimes we look at the variables of our own defined fields, we need to give a good nickname to the field if we look uncomfortable or don't know the name. For example, select Fnumber as employee from table name. As is not required at the time of definition, we can omit as.

4-Filter by condition: In the process of actual development we need to retrieve some data from the data table according to certain conditions. For example: We want to search all employees with a salary of less than 5000 yuan, we can write code to control the selected data so inefficient, and sometimes if it is a multi-table joint query, the basic logic is not good to write. So the database takes this issue into account, and internally supports the ability to filter the data, just specify the Where condition for the SELECT statement. For example: The Select fname from table name where the Fsalary<5000.where statement supports complex filtering conditions, such as retrieving information about employees who have paid less than 5000 or older than 25 years. For example: SELECT * from table name where fslary<5000 or Fage >25. This writing efficiency is very high, because the database optimizes the algorithm to query.

5-Summary of data

Sometimes we need to summarize some data, count the number of employees, the number of employees who are older than 25 years of age, and so on some wonderful needs, we need the aggregate functions defined in the database, such as max--calculate the maximum value of the field, min--the minimum value of the calculated field, avg--the average of the calculated field, sum--calculates the total value of the field, count--the number of statistical data.

The above functions all have a parameter that represents the name of the field. For example, the highest salary is queried for employees older than 25 years old.

Select Max (fsalary) from table name where Fage >25. For a convenient reference to the results of a query, you can also specify a name for the result of the calculation of the aggregate function select Max (fsalary) as max_salary from table name W Here Fage >25. Note the difference between the count (*) and the count (field name): If there are values for a field that are null, the data is not counted.

6-Sort

Sort of everyone familiar, here not much nonsense, directly on demand such as according to the ranking of wages from high to low

SELECT * FROM table name ORDER by fsalary ASC. Do not write ASC, the default is in ascending order, Desc is descending.

　　

Data retrieval of the technology of the Big Liar database