Mysql dba system learning (23) SQL knowledge

Must understand SQL knowledge

1. query Two External table connections

There are two tables A and B:

mysql> select * from A;+------+------+| id   | Col1 |+------+------+|    1 | AA   ||    2 | BB   ||    3 | CC   |+------+------+3 rows in set (0.00 sec)mysql> select * from B;+------+------+| id   | Col2 |+------+------+|    2 | DD   ||    3 | EE   ||    4 | FF   |+------+------+3 rows in set (0.01 sec)

1. Left join between table A and Table B

First, find the data in the left Table A. Then, all records with the same id as the left table id in the right table are identified based on the conditions following on, the records in the left table are arranged in one or more rows in sequence. If no matching record exists, null is displayed.

mysql> select * from A left join B on A.id=B.id;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   | NULL | NULL ||    2 | BB   |    2 | DD   ||    3 | CC   |    3 | EE   |+------+------+------+------+3 rows in set (0.00 sec)mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A left join B on A.id=B.id;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    1 | AA   | NULL ||    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+3 rows in set (0.00 sec)

The following results are the same:

2. Right join between table A and Table B

First, check the data in the right table B. Then, all records with the same id as the right table id in the left table are identified based on the conditions following on, the record of the matched left table is arranged in one or more rows in sequence. If no matching record exists, null is displayed.

mysql> select * from A right join B on A.id=B.id;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    2 | BB   |    2 | DD   ||    3 | CC   |    3 | EE   || NULL | NULL |    4 | FF   |+------+------+------+------+3 rows in set (0.07 sec)mysql> select A.id ID ,A.Col1 C1 ,B.Col2 C2  from A right  join B on A.id=B.id;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   || NULL | NULL | FF   |+------+------+------+3 rows in set (0.00 sec)

3. Cross join between table A and Table B

Without the cross join of the where clause, the number of rows in the first table of the Cartesian product involved in the join is multiplied by the number of rows in the second table, which is equal to the Cartesian Product and the size of the result set. Cross join cannot be followed by on and can only use where. However, if the number of matched rows is returned or displayed. Without the where condition.

mysql> select *   from A cross join B  ;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   |    2 | DD   ||    2 | BB   |    2 | DD   ||    3 | CC   |    2 | DD   ||    1 | AA   |    3 | EE   ||    2 | BB   |    3 | EE   ||    3 | CC   |    3 | EE   ||    1 | AA   |    4 | FF   ||    2 | BB   |    4 | FF   ||    3 | CC   |    4 | FF   |+------+------+------+------+9 rows in set (0.00 sec)

mysql> select A.id ID,A.Col1 C1,B.Col2 C2  from A cross join B  where A.id=B.id ;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+2 rows in set (0.01 sec)

There is also a cross-join representation method.

mysql> select * from A,B ;+------+------+------+------+| id   | Col1 | id   | Col2 |+------+------+------+------+|    1 | AA   |    2 | DD   ||    2 | BB   |    2 | DD   ||    3 | CC   |    2 | DD   ||    1 | AA   |    3 | EE   ||    2 | BB   |    3 | EE   ||    3 | CC   |    3 | EE   ||    1 | AA   |    4 | FF   ||    2 | BB   |    4 | FF   ||    3 | CC   |    4 | FF   |+------+------+------+------+9 rows in set (0.02 sec)mysql> select A.id ID,A.Col1 C1,B.Col2 C2  from A , B  where A.id=B.id ;+------+------+------+| ID   | C1   | C2   |+------+------+------+|    2 | BB   | DD   ||    3 | CC   | EE   |+------+------+------+2 rows in set (0.00 sec)

4. mysql query for complete external connections between table A and table B is currently not supported)

select * from A full join B on  A.id=B.id+------+------+------+| ID   | C1   | C2   |+------+------+------+|    1 | AA   |      ||    2 | BB   | DD   ||    3 | CC   | EE   ||    4 |      | FF   |+------+------+------+

2. group by if count sum

Group by groups query results by values of one or more columns.

If expression, value1, value2) its value is value1; otherwise, value2.

Count

Sum

The ps content of a table is as follows:

mysql> select * from ps;+------------+------+| RQ         | SF   |+------------+------+| 2005-05-09 | s    || 2005-05-09 | s    || 2005-05-09 | f    || 2005-05-09 | f    || 2005-05-10 | s    || 2005-05-10 | f    || 2005-05-10 | f    |+------------+------+7 rows in set (0.00 sec)

The following results are expected:

    RQ          s        f| 2005-05-09 |  2    |   2    || 2005-05-10 |  1    |   2    |

mysql>  select  RQ,sum(if(SF="s",1,0)) s ,sum(if(SF="f",1,0)) f from ps group by RQ;+------------+------+------+| RQ         | s    | f    |+------------+------+------+| 2005-05-09 |    2 |    2 || 2005-05-10 |    1 |    2 |+------------+------+------+2 rows in set (0.00 sec)

sum(if(SF="s",1,0))The number of occurrences of s can be obtained.

This article from "Good to live" blog, please be sure to keep this source http://wolfword.blog.51cto.com/4892126/1298442