preface

Mysql > select (select); Mysql > select (select);

Let’s pick up where we left off.

The query

The following data

The or query

When we go to school, we hear things like, so-and-so, you call so-and-so or so-and-so.

In this case, we need to query, is a ** or (or)** relationship.

An OR query must meet only one condition

sql

SELECT * from student WHERE (SELECT * from student WHERE (SELECT * from student WHERE ())) ; SELECT * from student WHERE name=" student "or name=" student "; SELECT * from student WHERE name=" student ";Copy the code

The execution result

Is not equal to (! =) query

** = (=)** = (=)** = (=)**)

A student learning can be good, back things back can be fast, when school may be a person, this time the teacher may say

Except XXX, the other students copied this article for three times.

So this is the reverse of the query, only need to exclude a person can!

sql

-- syntax SELECT * from student WHERE < condition >; SELECT * from student WHERE name = 'student' SELECT * from student WHERE name = 'student' = "* *";Copy the code

The execution result

In/not in the query

The students were all young people, probably in their 20s, when the teacher said, Monitor, count the people in 19,20, and 21!

Based on what we’ve learned, maybe you are.

SELECT * from student WHERE age=19 or age=20 or age=21;
Copy the code

The execution result

Find it by three or’s, which is also true, but there is a simpler way.

sql

SELECT * from student WHERE student = student WHERE student = student WHERE student = student . ; SELECT * from student WHERE age in (19,20,21);Copy the code

The execution result

Not in means data that is not in the range.

sql

SELECT * from student WHERE age not in (19,20,21);Copy the code

The execution result

Between the query

Between is suitable for range query!

sql

Grammar - select * from table where < column > between scope of < start > and < > end range; SELECT * from student WHERE age BETWEEN 20 and 22;Copy the code

The execution result

Similarly,between can be used for time.

Select * from < table name > where column > < time between < > start time and end < time >Copy the code

Fuzzy query (like)

So let’s say I have three zhangs.

The teacher may suddenly one day say to you, monitor, let us surname zhang’s list come out, I look for them to talk about something, how to do??

All we need to do is look for something that starts with a piece and doesn’t matter what comes after it.

So we’re going to use like fuzzy queries.

sql

Select * from table where < column > like '[%]< string >[%]' -- select * from student where name like "Zhang %";Copy the code

The execution result

Similarly, the % can be placed anywhere, and it is used as an indeterminate character that automatically matches one or more characters.

For example,

. WHERE name like "zhang % feng "; Matched: zhang Sanfeng WHERE name like "% fish "; WHERE name like "li %"; Matching: Li Si...Copy the code

Limit (limit)

I’m going to limit this to a little weird, but I think it’s better to use pictures.

The following data

sql

SELECT * from student LIMIT 2;Copy the code

SELECT * from student LIMIT 2,4; SELECT * from student LIMIT 2,4; SELECT * from student LIMIT 4 OFFSET 2Copy the code

Sorting (order by)

I don’t know if you noticed, but we’ve been looking in the forward order.

But there’s a problem, the data that we add, the id is actually the largest, theoretically, it should be at the top.

So, it should be in reverse order is best.

sql

Select * from (select * from (select * from (select * from (select * from (select * from (select * from)) order by (select * from (select * from)) Order by < 1> desc,< 2> asCCopy the code

For example,

Sort forward by ID

SELECT * from student ORDER BY id ASC;
Copy the code

The execution result

Sort backwards by ID

SELECT * from student ORDER BY id desc;
Copy the code

The execution result

HHH, so we reverse the data!!

Group (groupby)

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

Assume the following data

What if I want to know how many males there are and how many females there are? I can’t count…

So at this point, if you can, all the men, all the women, like this!

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

If you know this, you know the grouping.

grammar

SELECT COUNT(COUNT) from student GROUP BY student GROUP; You can use count,sum, etc. to calculate the number of compressed columns, or the total value of the compressed columnCopy the code

For example,

How many men and how many women

SELECT gender,COUNT(gender) from student GROUP BY gender;
Copy the code

The execution result

Count how many people of the same age

SELECT age,COUNT(age) as "COUNT" from student GROUP BY age;Copy the code

The execution result

** Group by must be followed by order by.

conclusion

This article mainly describes the Mysql query syntax, and then a single table query, basically so much of the above content, but from theory to events, is to take time.

We must practice frequently, what is or query,like query matters to note, especially group by group query, we must think more and contact.

If you have any problems during the operation, please leave a comment below and we will solve them as soon as we see them.

Don’t change who you are, because you may be the brightest diamond in the rough.

I am a code non Tuesday, if you think it is good, remember to like it.

Thank you for watching.

If you have any problems during the operation, please leave a comment below and we will solve them as soon as we see them.