SQL Exists ⚡ ️Group by ⚡ ️Case when ⚡ ️Having ⚡ Common functions

Time:2021-8-28

SQL Exists  ⚡ ️Group by  ⚡ ️Case when  ⚡ ️Having  ⚡ Common functions

Exists usage

tablea
aid aNAME  
1   A1
2   A2
3   A3

tableb
bID AID bNAME
1   1     B1
2   2     B2  
3   2     B3

These are two tables

select * from tablea a
where EXISTS (select * from tableb b where a.aid = b.aid )

select * from tablea a
where EXISTS (select * from tableb b where a.aid = 1 )

select * from tablea a
where EXISTS (select * from tableb b where b.aid = 1 )

The return results of the following three statements are:

1 A1
2 A2
============================

1 A1

============================
1 A1
2 A2
3 A3

You can understand that
Can be seen as front and backDouble layer for loop
If the statement after exists returns true every time it is executed, the previous statement will be executed. If false is returned every time it is executed, the previous statement will not be executed. It’s that simple

Example 1:
For the first cycle outside, when a.aid = 1, there can be data in it, so print 1 in front
The second cycle outside can have data when a.aid = 2, so print 2 in front
There is no data in the third cycle outside when a.aid = 3, so it is not printed

Example 2:
For the first cycle outside, there is data when a.aid = 1, so print 1
The second cycle outside. It doesn't hold when a.aid = 2. Don't print

Example 3:
For the first cycle outside, the cycle inside is also set up for printing
For the second cycle outside, the cycle inside is also set up for printing
For the third cycle outside, the cycle inside is also set up for printing

Case when usage

The first format: simple case function:

Format description    

    Case column name

    When condition value 1 then option 1

    When condition value 2 then option 2

    Else default end
eg:

    select
    case   job_level
    when     '1'     then    '1111'
    when    '2'     then    '1111'
    when    '3'     then    '1111'
    else     'eee' end
    from      dbo.employee

The second format: case search function

Format description    

    case  

    When column name = condition value 1 then option 1

    When column name = condition value 2 then option 2

    Else default end
eg:

    update  employee
    set         e_wage =
    case
    when   job_level = '1'    then e_wage*1.97
    when   job_level = '2'   then e_wage*1.07
    when   job_level = '3'   then e_wage*1.06
    else     e_wage*1.05
    end

Tip: usually when we write a case when statement,It's easy to forget the end. Be sure to remember!

Comparison: the two formats can achieve the same function.

The writing method of simple case function is relatively simple, but compared with case search function, there are some functional limitations, such as writing judgment. There is another problem to pay attention to, the case functionOnly the first one is returnedFor qualified values, the rest of the case will be automatically ignored.
  
  

Group by

In English, understanding is grouping. “Aggregate function” must be used together. At least one grouping flag field is required.

Note: the aggregate function is—sum()、count()、avg()And so on are “aggregate functions”
Let’s check all genders first

select location_name from test 
location_name
Chain
Chain
UK
US
select location_name from test group by location_name
location_name
Chain
UK
US
select location_name,count(location_name) from test group by location_name
location_name count(location_name)
Chain 2
UK 1
US 1

Note the usage difference between having and where:

1. Having can only be used in group byafter, the results after groupingFilter(that is, grouping is a prerequisite for using having).

2. Where must be in group bybefore

3. The conditional expression after where is not allowedAggregate function, and having can.

Supplement: having usage

Similarly, using the student form in this article, if you want to query the records of students with an average score higher than 80, you can write as follows:

SELECT id, COUNT(course) as numcourse, AVG(score) as avgscore

FROM student

GROUP BY id

HAVING AVG(score)>=80;

Supplement: common functions

Function name               effect

 AVG()                Returns the average value of a field

 COUNT()             Returns the number of rows in a field

 MAX()                Returns the maximum value of a field

 MIN()                Returns the minimum value of a field

 SUM()                Returns the sum of the fields
 
  CONCAT               String connection                SELECT CONCAT('my','s',ql');

 (str1,str2...)                                                Return: MySQL

 INSERT               String substitution                Select Insert ('This is the SQL Server database ', 3,10,' MySQL ');

 (str,pos,len,newstr)                                     Return: This is the MySQL database

 LOWER               Convert string to lowercase        SELECT LOWER(MySQL);

 (str)                                                            Return: MySQL

 UPPER               Convert string to uppercase        SELECT UPPER(MySQL);

 (str)                                                            Return: MySQL

 SUBSTRING            String interception                 SELECT SUBSTRING('JavaMySQLOracle',5,5);

 (str,num,len)                                               Return: MySQL

SQL Exists  ⚡ ️Group by  ⚡ ️Case when  ⚡ ️Having  ⚡ Common functions

It’s not easy to create. If this article can help you, please give me your support. Give me roses with lingering fragrance in my hands. I’m grandpa of the audience