Summary of keywords of database query statements, and Keywords of database statements

Summary of keywords of database query statements, and Keywords of database statements
After reading the niuyun video, I wrote the background code in the first 17 episodes, especially when talking about query statements. I suddenly felt that the keywords used in the previously learned query statements had vanished, I can't remember anything. By watching videos, I can help me recall some of them. Here I will summarize the meanings and usage of the keywords used in the query statements. I. select statement (single table) 1. Simplest query:

Select * from [where]

Select column1, column2.... from [where]

Note the keywords used for condition filtering in the where clause, such as '%' in the like logical operator (matching one or more characters) and '_' (match only one. This is also useful in the news publishing system.

For example, search by title:

Select top 10 n.id,n.title,n.createtime,c.[name]   from news n inner join category c on c.caid=c.id   where n.title like '%' + @title + '%' 

 

Of course there are many other keywords, such as between, not, in, and so on.

2. DISTINCT keyword

This keyword is mainly used to retrieve the unique value in the column. For example, if the value of a field in the record is duplicate (Langfang, Beijing, Langfang, Beijing ), use the DISTINCT keyword to retrieve the unique value, that is, any duplicate value is counted only once, and the result is: (Langfang, Beijing ).

select DISTINCT city from [table]  

3. Use aliases

The alias can be used to display the desired name for easy reading. Select city as city from...

4. group by and having clauses

Group by is used to group the queried result set. It must be placed after the from clause or where clause in the select statement.

A having clause is similar to a where clause, followed by a group by clause and serves as a query condition.

Differences from where clauses: The where clause acts on the query conditions in a record, while the having clause acts on the query conditions in a column.

For example:

Select location from citytable where city = 'beijing' -- query the location of a city named 'beijing' select city group by city having count (memberId)> = 3 -- Query cities with the total number of city members greater than or equal to 3 and group by city name

Ii. Multi-Table query 1. inner join

Required. The same match must be found in multiple tables. Where on indicates the condition of the target table, and n and c are aliases.

    Select *      From news n      Inner join category c       on c.caid=c.id  

The query result to be executed must be a record with the same category number in both tables.

For example, the following is an example of a niuyun video:

The id in the category table indicates the id of the news category, while the caid in the news table indicates the category of the news.

The result after the preceding query statement is executed:

Inner join table

The result shows that the category number exists in both tables. Inner join also includes equijoin and non-equivalence, which are mainly determined by the conditions following on.

2. left join

Left Outer Join: when connecting, all items in the table on the left of the on condition are queried. If no matching item exists in the right table, null is used instead.

Run the preceding two tables

    select * from category c left join news n on  c.id=n.caid  

Result:

3. right join

As the name suggests, the result of the Right outer join is opposite to that of the left join. All items in the right table are queried, while those in the left table without matching items are replaced by null.

4. full join

All items on the left and right return results. No corresponding item is replaced by null.

Iii. Others

In addition to the preceding query keywords, nested query, in keyword usage, sorting SQL records by numbers, and sequential query are also involved. Exploitation

SELECT ROW_NUMBER () OVER (order by id desc) AS Row -- Row is an alias

The above only involves a summary of some of the query keywords, which are also frequently used. Only by gradually learning can we find that the original knowledge should be constantly recalled and applied to discover its deeper meaning.