Where query and fuzzy query

Function: retrieve qualified values in data

Logical operator

operator	grammar	describe
and &&	a and b || a&&b	Logic and, both are true, and the result is true
or ||	a or b || a || b	Logical or, one of which is true, the result is true
Not !	not a || !a	Logical negation

where statement example

1. Find an interval

select [tablecolumn1,...]  from tableName where [condition1 and condition2....] or [condition1 or condition2]

2. Fuzzy query

select [tablecolumn1,...] from tableName where tablecolumn between num and num

3.not query

select [tablecolumn1,...] from tableName where not condition1

4. Single condition query

select tablecolumn from tableNamewhere num>200

5.NULL condition query

Note: the expression is "IS NULL", and "= NULL" cannot be written out. Two nulls can only be judged to be equal, such as:

select tablecolumn from tableName where condition is null

If it is changed to = NULL, the query result is an empty set but will not report an error (unfamiliar with SQL and often make errors)**

Fuzzy query

operator	grammar	describe
IS NULL	a is null	If the operator is NULL, the result is true
IS NOT NULL	a is not null	If the operator is not null, the result is true
BETWEEN	a between b and c	If a is between b and c, the result is true
Like	a like b	sql matches. If a matches b, the result is true
In	a in (a1,a2,a3...)	Suppose a is in a1 or a2...

Fuzzy query example

-- condition Query criteria
-- like combination  %(Represents 0 to any character)    _((one character)
select tablecolumn1,tablecoumn2 from tableName
where condition like 'x%'


-- query***  Query followed by a word
select tablecolumn1,tablecoumn2 from tableName
where condition like 'x_'


-- The inquiry number is 100,101,102 People
select tablecolumn1 from tableName 
where condition in (100,101,102)

Join query comparison

select * from Table1 join_type Table2 on join_condition  
#Description: join_type is the connection type, join_condition is an association condition

• inner join: displays the information associated through Relation in Table1 and Table2. Redundant information is not displayed.
• left join: displays the information associated through Relation in Table1 and Table2, and displays all the information in the left table at the same time
• right join: the information associated through Relation in Table1 and Table2, and displays all the information in the right table at the same time

give an example

The establishment of database table is shown in the figure below:

• Student list
  

• Class Schedule Card
  

• Score table
  

Query results

• The query result of inner join is shown in the following figure:

 select * from T_Student inner join T_Score on T_Student.S_ID=T_Score.S_ID

• The query result of left join is shown in the following figure:

 select * from T_Student left join T_Score on T_Student.S_ID=T_Score.S_ID

• The query result of right join is shown in the following figure:

select * from T_Student right join T_Score on T_Student.S_ID=T_Score.S_ID

Subqueries and nested queries

definition

Subquery: a query nested in other queries

There are three tables as follows:

1. customers: store customer information
   
2. orderitems: only store order information, no customer information
   
3. orders: store the order number and customer id
   

Note: generally, in the sub query, the program runs in the statement nested in the innermost layer first, and then in the outer layer. Therefore, when writing sub query statements, you can first test whether the inner sub query statements output the desired content, and then test out layer by layer to increase the accuracy of sub query. Otherwise, multi-layer nesting makes the readability of statements very low.

Subquery example

Query the customer information of TNT2

Step by step:

• Find the order number of TNT2 in the orderItems table

  SELECT order_num FROM orderitems
  WHERE prod_id='TNT2';
  

• Find the customer id corresponding to the order number found in the first step in * * orders table * *

  SELECT cust_id FROM orders
  WHERE order_num
  IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');
  

• Find all the information corresponding to the customer in step 2 in customers

 ```mysql
 SELECT * FROM customers
 WHERE cust_id
 IN (SELECT cust_id FROM orders WHERE order_num IN
 (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));
 ```

Aggregate functions and group filtering

Common aggregate functions

Function name	describe
COUNT()	count
SUM()	Sum
AVG()	Average
MAX()	Find the maximum value
MIN()	Find the minimum value

Usage example of aggregate function

1. count()

   select count(column) from tableName   --count(Specify column),Will ignore all null value
   select count(*) from tableName        
   select count(1) from tableName        
   

   • count(*) includes all columns, which are equivalent to the number of rows. The column value is NULL will not be ignored in the statistical results
   • count(1) includes all columns. 1 is used to represent the code line. During statistics, the column value is NULL will not be ignored

   Execution efficiency:

   If the column name is the primary key, count (column name) will be faster than count(1)

   If the column name is not the primary key, count(1) will be faster than count (column name)

   If the table has multiple columns and no primary key, the execution efficiency of count(1) is better than count(*)

   If there is a primary key, the execution efficiency of count is the best

   If there is only one field, select count(*) is the best

2. sum()

   select sum('field') as the sum from result
   

3. avg()

   select avg('field') as average from result
   

4. max()

   select max('field') as Highest score from result
   

5. min()

   select min('field') as Lowest score from result
   

Grouping query

When making statistics on the data in the data table, it may be necessary to make statistics according to certain categories. For example, make statistics on the sum of grade s of students whose gender field values are na, nv and NULL in the student table

Parameter description

The specified field name 1 and field name 2 are the basis for grouping query results
HAVING keyword, which specifies a conditional expression to filter the grouped content
GROUP BY is generally used together with the aggregation function. If the query field appears after GROUP BY but is not included in the aggregation function, this field displays the value of the first record after grouping. In this way, the query result may not meet our expectations

Group by group alone

Use the GROUP BY keyword alone to query a record in each group,

Query the records in the student table and install the gender field value for grouping

It can be seen that three records are returned, which are the records with NULL, nv and na values in the gender field. The query results are classified according to different values in the gender field. Then, the value shows one record in each group, which has little significance. Generally, GROUP BY is used together with the aggregation function

GROUP BY and aggregate function, used together

GROUP BY and aggregation function can be used together to count the maximum, minimum and average values of a or some fields in a group

Query the student table, group according to the gender field value, and calculate the number of students in each group

It can be seen that GROUP BY groups the gender field, and then counts the number of each group through the COUNT() function

GROUP BY and HAVING keywords, used together

HAVING keyword and WHERE keyword have the same function. They are used to set conditional expressions and filter query results.

The difference between the two is that the HAVING keyword can be used with the aggregate function, while the WHERE keyword cannot. Generally, the HAVING keyword is used together with GROUP BY to filter the grouped results

Query the student table, group according to the gender field, and find out the groups whose sum of grade field values is less than 300

It can be seen that only for groups with NULL and nv values in the gender field, the sum of grade field values is less than 300