# mysql learning notes

## Where query and fuzzy query

Function: retrieve qualified values in data

#### Logical operator

operatorgrammardescribe
and &&a and b || a&&bLogic and, both are true, and the result is true
or ||a or b || a || bLogical or, one of which is true, the result is true
Not !not a || !aLogical 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

operatorgrammardescribe
IS NULLa is nullIf the operator is NULL, the result is true
IS NOT NULLa is not nullIf the operator is not null, the result is true
BETWEENa between b and cIf a is between b and c, the result is true
Likea like bsql matches. If a matches b, the result is true
Ina 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 namedescribe
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

Keywords: Database MySQL

Added by timon on Thu, 10 Mar 2022 03:09:14 +0200