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

This article mainly explains "how to use MySQL query sentence", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to use MySQL query statement" bar!

Query

The data are as follows

Or query

When we are at school, we will hear such words, so-and-so, you call over here.

In that case, what we want to inquire about is an or (or) relationship.

Or query can only satisfy one condition.

Sql

-- syntax SELECT * from student WHERE or...;-- for example, query data named Zhang San or Xiao Liu SELECT * from student WHERE name= "Zhang San" or name= "Xiao Liu"

Execution result

Not equal to (! =) query

The above queries are all equal to (=) operations, but this will still happen when we are in school.

A certain student can study well and recite things quickly, and he may be alone after school. At this time, the teacher may say,

With the exception of so-and-so, the other students copied this article three times.

So at this time is the opposite of the query, just need to rule out someone!

Sql

-- syntax SELECT * from student WHERE;-- for example, query other people whose name is not Zhang San, excluding Zhang San SELECT * from student WHERE name = "Zhang San"

Execution result

In/not in query

The students are still young, about 20 years old, at this time the teacher said, monitor, count the people at the age of 1919, 20 and 21!

According to what we have learned above, maybe you are so auntie.

SELECT * from student WHERE age=19 or age=20 or age=21

Execution result

It's all right to find it through three or, but there's a simpler one.

Sql

-- syntax SELECT * from student WHERE in (value 1, value 2...);-- for example, requirements are the same as above SELECT * from student WHERE age in (19Jing 20jue 21).

Execution result

Not in is not in this range of data!

Sql

-- query SELECT * from student WHERE age not in for data whose age is not in the range of 1914, 20, 21 (19, 20, 21)

Execution result

Between query

Between is suitable for range query!

Sql

-- Grammar select * from table where between and;-- for example, SELECT * from student WHERE age BETWEEN 20 and 22 when querying students aged 20-22

Execution result

In the same way, between can also be used in time.

Select * from where between and Fuzzy query (like)

Suppose the data are as follows, there are three people surnamed Zhang.

The teacher may suddenly say to you one day, monitor, list those of us surnamed Zhang, and I'll talk to them about something. What should I do?

We just need to find it, it starts with Zhang, and it doesn't matter what's behind it.

So we need to use like fuzzy query.

Sql

-- Syntax% indicates matching one or more characters in the select * from table where like'[%] [%]'--for example, a query, SELECT * from student WHERE name like "Zhang%" beginning with Zhang

Execution result

In the same way, this% can be placed anywhere, it is used as an uncertain character and can automatically match one or more characters.

For example

... WHERE name like "Zhang% Feng"; match: Zhang Sanfeng WHERE name like "% Fish"; match: Zhang Turtle WHERE name like "Li%"; match: Li Si. Limit (limit)

It's a little weird to limit this, so it's better to give an example of a picture.

The data are as follows

Sql

-- the first two lines SELECT * from student LIMIT 2

-- starting from line 2, take four more lines SELECT * from student LIMIT 2 OFFSET 4;-- ditto, write in different ways SELECT * from student LIMIT 4 OFFSET 2

Sort (order by)

I don't know if you have noticed, but the order of our previous search is all positive.

But there will be a problem, our new data, in fact, id is the largest, theoretically, should be at the top of the list.

Therefore, it should be the reverse order is the best.

Sql

-- Syntax select * from order by asc-- sort from small to large select * from order by desc-- sort from big to small select * from order by desc, asc-- sort by column 1 first, and sort by column 2 if the data is the same

For example

Sort SELECT * from student ORDER BY id ASC forward according to id

Execution result

Reverse sort SELECT * from student ORDER BY id desc according to id

Execution result

Hhh, so we can reverse the data!

Grouping (groupby)

Grouping may be difficult for a group of people. In fact, the core of grouping is to understand the concept of compression.

Suppose the data are as follows

There are men and women in gender. What if I want to know how many men there are and how many women there are? I can't count it.

So at this time, if you can, take out all the men and women, just like this!

And then I took it out and compressed it into one.

If you understand this, you will understand the grouping.

Grammar

SELECT, COUNT () from student GROUP BY;-- because one column is grouped, the information of other columns cannot be displayed, because it is pressed together and cannot be displayed-- but the number or total value of compression can be calculated through functions such as count,sum.

For example

Count the number of men and women

SELECT gender,COUNT (gender) from student GROUP BY gender

Execution result

Count the number of people of the same age

SELECT age,COUNT (age) as "quantity" from student GROUP BY age

Execution result

Note: group by must come after where and before order by.

Thank you for your reading, the above is the content of "how to use MySQL query sentence", after the study of this article, I believe you have a deeper understanding of how to use MySQL query statement, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.