Previous review

• order by

    select * from `group` order by max_size;

    select * from `group` order by max_size desc;

• Query number (limit)

    select * from `group` limit 2;

    select * from `group` limit 2,3;

• Sorting and Query Quantity Combination

    select * from `group` order by max_size limit 2;

• group by

    select max_size,count(*) from `group` group by max_size;

• Fuzzy query (like)

    select * from `group` where name like '%b%';

    select * from `group` where name like 'z_';

• Detailed explanation of foreign keys

If you have doubts about the above, you can check it out Last blog

Subquery

The missing distinct

The Type of Maximum Number of Query Groups

    select distinct max_size from `group`;

having

Usage: Same as where

Difference: where is used to query data, and having is mainly used to filter query data.

/* View Grouping Types with Number of Grouping Types greater than 1 */
select type,count(*) from `group` group by type having count(*) >1;

where sub-query

/* Get the largest number of groupings */
select * from `group` order by max_size desc limit 1;

/* Using subqueries */
select * from `group` where 
    max_size = (select max(max_size) from `group`);

in subquery

Query Grouping Numbers 3, 4, 5 Grouping

select * from `group` where id in (3,4,5);

between ... and ...

If the interval is large, consider using this

select * from `group` where id between 3 and 5;

multi-table query

Groups that do not have members in query groupings

select * from `group` where id not in 
    (select group_id from member group by group_id);

Query results use multi-table data

Query all users and display other data such as the name of their group

select m.user_id,m.user_name,m.user_password,m.descp,
    g.id,g.name,g.max_size,g.type,g.description 
    from member m,`group` g where m.group_id = g.id;

You can try if you don't write conditions.

select m.user_id,m.user_name,m.user_password,m.descp,
    g.id,g.name,g.max_size,g.type,g.description 
    from member m,`group` g ;

left join query on...)

Left join query: The first table in the two tables prevails and null if no second data exists

SELECT g.id, g.name, g.max_size, g.type, g.description
    , m.user_id, m.user_password, m.descp
FROM `group` g
    LEFT JOIN member m ON g.id = m.group_id;

right join query on...)

Return to the right table if there is no data in the left table.

SELECT g.id, g.name, g.max_size, g.type, g.description
    , m.user_id, m.user_password, m.descp
FROM `group` g
    RIGHT JOIN member m ON g.id = m.group_id;

full join query on...)

Return columns as long as one table matches successfully

inner join... on...)

Only if both tables match successfully will the column be returned

SELECT g.id, g.name, g.max_size, g.type, g.description
    , m.user_id, m.user_password, m.descp
FROM `group` g
    INNER JOIN member m ON g.id = m.group_id;

mysql has completed this series of blogs. If you have any other suggestions, you can leave a message.

Welcome to your message reprint
Focus on Wechat Public Number: SimFG, Read Blog Points anytime, anywhere