Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of keywords in SQL examples, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Give an alias

In actual projects, sometimes our table names and field names are so complex that our SQL is too long and complex to write. At this time, we tend to simplify some long and complex fields or table names by aliasing them.

We can use aliases (Alias) to temporarily name the data table or column, since it is an alias, that is, the original name of the original table or column will not be modified, but only for the simplified display of the current query.

To give a watch an alias:

Select * from person as pwhere p.id = 1

Once an alias is executed for the table, the alias can be directly referenced in the subquery statement of this query instead of the reference of the original table.

Alias the list:

Select name as n,age as a from perosn

In addition to using the keyword as to give the table or list aliases, you can also directly use space characters to achieve the same effect, but I think either all use as aliases, or all use spaces for aliases, do not cross-use to make your SQL complex and difficult to understand.

Eliminate duplicate records

Sometimes, there are two identical pieces of data in our database, which we also call redundant data. Of course, we don't want to find such redundant duplicate data when querying the data, and we have to filter them out.

A simple question on LeetCode:

There is a courses table, which includes: student (students) and class (courses).

Please list all classes with more than or equal to 5 students.

For example, table:

+-+-+ | student | class | +-+-+ | A | Math | | B | English | C | Math | | D | Biology | | E | Math | F | Computer | | G | Math | | H | Math | | I | Math | +-+-+

Should output:

+-+

| | class |

+-+

| | Math |

+-+

You can take a minute to think about it. It should not be difficult to use our previous basic skills.

Obviously, grouping is needed. If you want to count the number of candidates for each course, you have to group according to the discipline, and each group is the record of the students who take that subject.

Then the SQL statement comes in handy:

Select class from coursesgroup by classhaving count (student) > = 5

But after you submit it, under a large number of test cases, it will give you a hint to answer the error. If you don't believe it, what's the problem?

The problem is that the boundary condition of redundant data is not taken into account. If A selects Math twice, when we count the Math packet, we will count the number of people who choose Math once more. In fact, this is illogical. We need to filter out the data records that have been repeatedly selected.

Solutions are as follows:

Select class from coursesgroup by classhaving count (distinct student) > = 5

As some of you may see, we added a distinct keyword in front of the column parameter of the count function, which means that if the value of the student column is repeated, it will only be counted once.

Of course, in addition to being used in aggregate functions, distinct can also be used directly in the column filtering phase of a query statement, such as:

/ / take out all the students. Students with duplicate names are not allowed to appear select distinct name from students at the same time.

Connect result set

The UNION operator can combine the result joins of one or more SELECT statements into a result set, but requires that two or more results have the same number of columns with the same data type, for example:

Build a student table:

+-+ | id | name | uNo | fees | +-+ | 1 | Zhang San | 15263501 | 18000 | 2 | Li Si | 15263506 | 15960 | | 3 | Wang 2 | 15263512 | 2500 | +-+

The student form mainly includes the student's name, student number and tuition fee.

Build a faculty table:

+-+ | id | name | tNo | salary | +-+ | 1 | Miss Li | 1001 | 10000 | | 2 | Miss Yang | 1002 | 15000 | | 3 | Miss Cao | 1030 | 5000 | + |-+

Now there is a need to get the names and numbers of everyone in the school, including students and teachers. Generally speaking, it would be fine for us to have two select queries, but we can't merge and display them in one result set, which is a problem.

So we can use union to join two results and display them in a table:

Select name,uNo from studentsunionselect name,tNo from teacher

Query results:

+-+ +

| | name | uNo |

+-+ +

| | Zhang San | 15263501 | |

| | Li Si | 15263506 | |

| | Wang er | 15263512 | |

| | Miss Li | 1001 | |

| | Mr. Yang | 1002 | |

| | Mr. Cao | 1030 | |

+-+ +

It doesn't seem much more intuitive, but in addition, if there is a completely duplicate data record in two result sets, the data record will not be repeated in the merged result set.

Of course, if you don't need to delete duplicate rows when merging result sets, you can use the keyword UNION ALL instead of UNION instead.

TOP

The TOP clause is used to retrieve the first N or X% records from a data table, but it is important to note that none of the database implementations support TOP and have their own keywords instead, such as MySQL and SQLserver using the LIMIT keyword and Oracle using the ROWNUM keyword.

For example:

Select * from students limit 2

The first two pieces of data are extracted from the MySQL database, and the equivalent Oracle database is written:

Select * from studentsrownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.