SQL advanced mainly introduces predicate, case expression, nested subquery, join (join the table by column) and union operation (table as unit).
2 SQL advanced
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 '
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
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 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
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
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
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