MySQL Learning-queries/Sub-queries/connection queries/federated queries

Database query set two database tables

• The first table, user, consists of:

Age

user_id	username		Sex
1	Alps1992	22	Mans

• The second table toy contains

user_id	Toyname
1	OnePiece

General query: Query keywords:

AS， SUM， DESC， GROUP BY， ORDER BY， AVG， MIN， MAX， COUNT， LIMIT;

Keyword Query examples

select * from user as u order by age limit 5;
Used to as make aliases that are used to order by sort (from small to large, from large to small DESC ) to limit the number of bars used to fetch results.

select sum(age) from user group by sex;
Sum, respectively, of the age of the male in the user and the age of the woman and the group by group.

avgIt's the average, the min minimum, the max maximum, and the sum usage.

select count(user_id) from user;
Returns the number of user_id in the table.

Find information for everyone and sort by age:
select * from user order by age;

Query the information called Chen:
select * from user where username=‘chen‘;

Check the name of the person aged 18 and let the id be sorted from the big to the small
select username from user where age=18 order by user_id DESC;

Sub-query: keywords:

IN， NOT IN， EXISTS， NOT EXISTS。

Check the names of all people who have toyname as pig:
select username from user where user_id in (select user_id from toy where toyname=‘onepiece‘);

Join query: Cross joins first: crosses join

select u.username, t.toyname from user as u cross join toy as t;
A cross connection is the value one by one for all the first and second tables.
For example: If the first table has 5 values and the second table has 4 values, the result should be 20 records.

INNER JOIN: INNER JOIN equal connection: (equal join)

select u.username, t.toyname from user as u inner join toy as t on(也可以用where) u.user_id = t.user_id;
The result is a toy of their own.

Unequal connections: (non-equal join)

select u.username, t.toyname from user as u inner join toy as t on(也可以用where) u.user_id <> t.user_id order by u.username;
The result is that they don't have a toy.

Natural connection: (natural join)

This is used when the names of the columns in the connected two tables are equal.

select u.username, t.toyname from user as u natural join toy as t; //和上面的相等连接的结果是一样的。

Outer joins: OUTER JOIN

The left outer join and right outer joins are similar: outer join; Right outer join;

select u.usrename, t.toyname from user u inner join toy t on (这里不能用where) u.user_id = t.user_id;//这个是查询用户姓名和他所拥有的玩具名字

select u.username, t.toyname from user u left outer join toy t on u.user_id = t.user_id;
This is based on left table (user), to query all user names and the names of the toys he owns.

select u.username, t.toyname from user u right outer join toy t on u.user_id = t.user_id;
This is based on the right table (toy), to query the owner name of all toys, null for no owner

Union: Union, INTERSECT, EXCEPT

A federated query is a combination of several query results into a table

select user_id from user union select user_id from toy order by user_id;//联合的两个查询语句的列必须相同， union可以连接多个， 假如排序在最后一条语句后面加order by;（重复自动去除）

union all:

select user_id from user union all select user_id from toy order by user_id;
As with union, all values are returned in the result of the Union all, not the copy of each value, and the repetition of the package is also listed.

To create a table with union:

create table m_union as select user_id from user union select user_id from toy;//这样会把user里的user_id和toy表里的user_id的内容都拿出来放到心创建的m_union表格里。

INTERSECT,EXCEPT

• Intersect is the intersection.
• Except is the meaning of the difference set

select user_id from user intersect select user_id from toy;
is to return the same portion of the two select queries. (This is the user_id of children with toys)

select user_id from user except select user_id from toy;
Is the part that returns the first select minus the second select result. (This is the user_id of children without toys)

So much for the first time. Join conversion subquery

MySQL Learning-queries/Sub-queries/connection queries/federated queries