Data analysis SQL advanced

1 Introduction

SQL advanced mainly introduces predicate, case expression, nested subquery, join (join the table by column) and union operation (table as unit).

2 SQL advanced

2.1 predicate

• LIKE

Before introducing like, first introduce wildcard characters. MySQL only supports percent sign (%) and underline (_) , percent sign replaces 0 or more characters, and underline replaces one character. Wildcards and regular expressions are not the same thing, you can search for regular expressions if you are interested. The like operator can be understood to be equal to (=).

SELECT website ,city FROM web_ city  WHERE website like '%o%' and city like '_ Gu '

• BETWEEN

The between operator takes a field between a certain range and can be numeric, text and date. Always with and, between… And.

SELECT website  FROM web_city  WHERE website BETWEEN 'A' AND 'H' 

• IS NULL

SQL null value judgment, can not use the equal sign (=) or like, use is null. Null value is null value, nothing, web_ In city, null is written for display. In fact, this null is a character, not a null value. Please note. Write the null value “directly” and it’s OK. There’s nothing in the quotes.

SELECT website,city FROM web_city  WHERE website is NULL 

• IN

The in operator also takes a field in the range. Compared with between and, the range of in is more explicit.

SELECT website,city FROM web_ City where website in ('google ',' Taobao ',' Shenzhen ')

• EXIST

Exist is often used in multi table query, because it is meaningless to judge whether a record exists alone. for instance.

SELECT * FROM web_ city  WHERE EXISTS (SELECT * from city_ Nation where city ='silicon valley ') (1)
SELECT * FROM web_city  WHERE true （2）

The first statement is equivalent to the meaning of the second statement and returns all records.

SELECT * FROM web_city  WHERE EXISTS (SELECT * from city_nation WHERE web_city.city = city_nation.city) 

2.2 case expression

Case < single valued expression >
    When < expression value > then < SQL statement or return value >
    When < expression value > then < SQL statement or return value >
    ...
    When < expression value > then < SQL statement or return value >
    Else < SQL statement or return value >
END

The syntax of case expression is as above, but the syntax is still too abstract. Three usages of case expression are summarized.

• Equivalent conversion

Select *, (case when website ='google 'then' 1 'else' 0 'end) as' equivalent conversion' from Web_ city

In the case expression, it can be understood that case (assumed) when (then) ends (else). It is better to use brackets to enclose the whole and see the personal writing method.

• Range conversion

Select *, (case when website >'a 'then' 1 'else' 0 'end) as' equivalent conversion' from Web_ city

Range conversion is to convert the value within the range to a certain value, such as the score grade. When the score is lower than 60, fail, 60 ~ 80, good, above 80, excellent.

• Column to line

Select max (case when website ='google 'then city else' 0 'end) as' column forward 1', max (case when website ='taobao 'then city else' 0 'end) as' column forward 2' from web_ city

Through the above statement, the city column can be converted into a row. I only turn the first two, and the latter is similar.

2.3 nested subquery

Nested subquery is to nest the select from where expression in the select from where expression. The select from where expression can be placed in the where clause and the from clause. Of course, there is the with clause. Those who are interested can go to Baidu to learn about it.

• Subqueries in the where clause

SELECT * FROM web_city where website in (SELECT website FROM web_city where website ='Google') 

• Subquery in from clause

SELECT S.* FROM (SELECT * FROM web_city where website ='Google') as S 

2.4 join

Join is the combination of tables and tables based on common fields. It mainly introduces inner connection, left connection, right connection and full connection. Inner join is to take out the public line. The left connection is mainly on the left side, with all the left side taking, some taking on the right side and no empty space. The right connection is mainly on the right side. Full connection means all fetching, which is not supported in MySQL and can be tested on SQL server.

• Inner join

SELECT a.*,b.* from web_city a INNER JOIN city_nation b on a.city=b.city 

• Left join

SELECT a.*,b.* from web_city a left JOIN city_nation b on a.city=b.city 

• Right join

SELECT a.*,b.* from web_city a RIGHT  JOIN city_nation b on a.city=b.city 

• Full connection

SELECT a.*,b.* from web_city a FULL JOIN  city_nation b on a.city=b.city 

2.5 union operation

Union operation to remove duplicate items. Union all operation without removing duplicate items

• UNION

SELECT a.*,b.* from web_city a INNER JOIN city_nation b on a.city=b.city
UNION 
SELECT a.*,b.* from web_city a left JOIN city_nation b on a.city=b.city 

• UNION ALL

SELECT a.*,b.* from web_city a INNER JOIN city_nation b on a.city=b.city
UNION ALL
SELECT a.*,b.* from web_city a left JOIN city_nation b on a.city=b.city 

3 Summary

Grammar is just grammar, or should we have ideas in it? What do I want to do with it? I hope you can think more.

Not for praise, but for usefulness