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 back
Double 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 function
Only the first one is returnedFor qualified values, the rest of the case will be automatically ignored.
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
select location_name from test group by location_name
select location_name,count(location_name) from test group by location_name
Note the usage difference between having and where:
1. Having can only be used in group by
after, the results after grouping
Filter(that is, grouping is a prerequisite for using having).
2. Where must be in group by
3. The conditional expression after where is not allowed
Aggregate 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
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