Mysql simple command statement resume-*, as, groupby, orderby, groupby, havin_MySQL

Mysql simple command statement resume-*, as, groupby, orderby, groupby, having, limit;-Cainiao is learning to fly!

It's not easy to be a Cainiao. it's hard to survive in this world.

Continue hitting the SQL command in the simple cmd command!

First, we will focus on the wildcard * problem!

If you want to search for all of them, you certainly don't want to directly select * from tablename!

However, this will have a significant impact on performance!

If you have many columns!

Because in the database

* Is parsed into various attributes for each select *, so do not be lazy during actual development.

List all attributes you want to search! To improve overall efficiency

For example:

select id,name,gender,age,job,createdate from teacher_tab order by createdate desc;

Order by column name desc is sorted by XXX in descending order

Order by column name not added, ascending

Effect 1:

When you encounter many equal sorting orders, you can consider the second column for sorting.

For example

select id,name,gender,age,job,createdate from teacher_tab order by createdate desc,age;

Effect 2 is two sorting methods

Next, how can we Group data:

For example, we need to count the number of teachers, and then calculate g = gg = male m = mm = female for the teachers and gender respectively.

The code is as follows:

select gender ,count(gender) from teacher_tab group by gender;

Effect 3:

If we only need to calculate the number of m, we can add having gender = 'M ';

4:

However, having is not recommended. when the data size is large, it is very performance-consuming to search again after grouping.

The following code is recommended:

select gender,count(gender) from teacher_tab where gender = 'm';

Sometimes we are not satisfied with the column name and want to customize other names. in this case, we need to use the as alias.

The code is as follows:

select t.age,t.name from teacher_tab t;

select count(id) as counts from teacher_tab;

We can get the column alias or the table alias!

Effect 5:

Also, use limit to limit the starting amount and offset of limit.

For example:

select * from teacher_tab limit 0,3;

select * from teacher_tab limit 1,3;

0 starts from the first entry.

1 indicates the start of the second entry.

3 indicates three records after 0

Effect 6:

Next is

The submission and regression of things. The next article will be continued.