MySql learning, mysql learning notes

Source: Internet
Author: User

MySql learning, mysql learning notes
Set two database tables for database query

  • The first table user contains:
User_id Username Age Sex
1 Alps1992 22 Man
  • The second table toy contains
User_id Toyname
1 OnePiece
Common query: Query Keyword:

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

Keyword query example

select * from user as u order by age limit 5;
//asUsed as an alias,order byUsed for sorting (from small to large, from large to small isDESC),limitNumber of results.

select sum(age) from user group by sex;
// Sum calculates the male age and female age of the user respectively, and the group by group.

avgIs Average,minIs the minimum value,maxIs the maximum value, andsumSame usage

select count(user_id) from user;
// Return the number of user_id, that is, the number of users in the table.

// Query the information of all users and sort the information by age:
select * from user order by age;

// Query the information named chen:
select * from user where username='chen';

// Query the names of 18-year-olds and sort the IDs in ascending order.
select username from user where age=18 order by user_id DESC;

Subquery: Keyword:

IN, NOT IN, EXISTS, NOT EXISTS.

// Query the names of all persons whose toyname is pig:
select username from user where user_id in (select user_id from toy where toyname='onepiece');

Join query: cross join

select u.username, t.toyname from user as u cross join toy as t;
// Cross join is to match the values of all the first and second tables one by one.
// For example, if the first table has five values and the second table has four values, this result should have 20 records.

Inner join: equal join: (equal join)

Select u. username, t. toyname from user as u inner join toy as t on (you can also use where) u. user_id = t. user_id;
// The result is their own toys.

Unequal join: (non-equal join)

Select u. username, t. toyname from user as u inner join toy as t on (you can also use where) u. user_id <> t. user_id order by u. username;
// The result is that the toys are not found.

Natural join: (natural join)

This is only available when the column names in the two joined tables are equal.

Select u. username, t. toyname from user as u natural join toy as t; // the same result as the above equal join.
Outer join: outer join

Left outer join and right outer join are similar: left outer join; right outer join;

Select u. usrename, t. toyname from user u inner join toy t on (where cannot be used here) u. user_id = t. user_id; // This is used to query the user's name and the name of the toy he owns.

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

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 names of owners of all toys. If no owner is NULL

UNION: UNION, INTERSECT, EXCEPT T

The combined query is to merge several query results into a table.

Select user_id from user union select user_id from toy order by user_id; // the columns of the two join query statements must be the same. union can be connected to multiple query statements, if order by is added after the last statement (repeated automatic removal)

union all:

select user_id from user union all select user_id from toy order by user_id;
// The same as the union operation method, that is, all values are returned in the result of union all, instead of copying each value. Duplicate packages are also listed.

Use union to create a table:
Create table m_union as select user_id from user union select user_id from toy; // This will take out the user_id in the user and the user_id in the toy table and put it in the m_union table created in the heart.

INTERSECT,EXCEPT

  • INTERSECT is the intersection.
  • Distinct t indicates the difference set.

select user_id from user intersect select user_id from toy;
// Returns the same part of the two select queries (here is the user_id of the child with toys)

select user_id from user except select user_id from toy;
// Returns the first select minus the second select result. (Here is the user_id of the child without toys)

Generally, there are so many subqueries, and then join and convert them.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.