Among the database operation statements, the most frequently used and considered the most important is the
SELECT query statement. We have used SELECT * FROM table_name in many places; this statement is used to view all the contents in a table. And SELECT is used in conjunction with various restrictive keywords and has various rich functions.
The basic format of the SELECT statement is:
SELECT column name to query FROM table name WHERE restrictions;
SELECT statements often have WHERE constraints, which are used to achieve more precise queries. WHERE restrictions can have mathematical symbols (=,<,>,>=,<=)
For example: SELECT name,age FROM employee WHERE age>25;
Or find the name, age, and phone of an employee named Mary:
SELECT name,age,phone FROM employee WHERE name='LLODY';
1, and and or
From these two words, you can understand their role. There can be more than one restriction after WHERE, and according to the logical relationship between the conditions, you can use [condition one OR condition two]] and [condition one AND condition two];
For example: SELECT name,age FROM employee WHERE age<25 OR age>30;
#Filter out that age is greater than 25 and age is less than 30
SELECT name,age FROM employee WHERE age>25 AND age<30;
And the restriction just now age>25 AND age<30, if you need to include the two numbers 25 and 30, you can replace it with age BETWEEN 25 AND 30:
2. in and not in
The keywords IN and NOT IN are as obvious as their names, and are used to filter results within a certain range of "in" or "out".
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
3. like
The keyword LIKE can be used to implement fuzzy queries, commonly found in search functions.
Commonly used with LIKE are wildcards, which represent unknown characters. The wildcard characters in SQL are _ and%. Where _ represents an unspecified character and% represents an indefinite number of unspecified characters
For example, if you only want to remember the first four digits of the phone number as 1101, and forget the last two digits, you can use two _ wildcards instead.
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
In order to make the query results look more pleasing, we may need to sort the results by a column, which requires the use of ORDER BY sort keywords. By default, the result of ORDER BY is sorted in ascending order, and the keywords ASC and DESC can be used to specify ascending or descending order. For example, we sort in descending order of salary.
SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
If DESC or ASC is not added later, it will be arranged in ascending order by default. Application scenario: Blog posts are displayed in chronological order in the blog system.
4. SQL functions and calculations
If DESC or ASC is not added later, it will be arranged in ascending order by default. Application scenario: Blog posts are displayed in chronological order in the blog system.
Function name: COUNT SUM AVG MAX MIN
Function: Count, Sum, Average, Maximum, Minimum
The COUNT function can be used for any data type (because it is only a count), while the SUM and AVG functions can only perform calculations on numeric data types. MAX and MIN can be used for numeric, string, or date-time data types.
Calculate the maximum and minimum of salary.
For example: SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
5. Subquery
The SELECT statements discussed above only involve data in one table, but sometimes multiple tables must be processed to obtain the required information. For example: I want to know that the employee named "Tom" has done several projects. Employee information is stored in the employee table, but engineering information is stored in the project table.
For such situations, we can use subqueries:
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project GROUP BY of_dpt HAVING of_dpt IN (SELECT in_dpt FROM employee WHERE name='Tom');
The above code contains two SELECT statements, the second SELECT statement will return the data form of a set, and then the first SELECT statement is judged by in.
The HAVING keyword can have the same effect as WHERE, which means that the conditional filtering operation will be performed next.
The difference is that HAVING is used to filter the grouped data
6. Connect query:
When dealing with multiple tables, subqueries are only useful if the results come from one table. However, if you need to display data from two or more tables, you must use a join operation. The basic idea of joining is to operate two or more tables as a new table, as follows:
For example: SELECT id,name,people_num FROM employee,department WHERE employee.in_dpt = department.dpt_name ORDER BY id;
This statement queries the number of employees in each department, where the employee's id and name are from the employee table, and people_num is from the department table:
Another join statement format is to use JOIN ON syntax, the statement just now is equivalent to:
For example: SELECT id,name,people_num FROM employee JOIN department ON employee.in_dpt = department.dpt_name ORDER BY id;
