MYSQL基础03 - 查询

--mysql选择查询,包含内容
1.选择行数limit
2.with rollup
3.内连接，外连接，复合条件连接查询
4.any,some,all,exists,
5.正则表达式

--t2表内容
mysql> select * from t2;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | c    | XXX  |
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | c    | XXX  |
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | c    | XXX  |
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | c    | XXX  |
+------+------+------+
12 rows in set (0.00 sec)

--获取从第11行起的2行记录，也就是11，12行的记录
mysql> select * from t2 limit 10,2;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    2 | b    | XXX  |
|    3 | c    | XXX  |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t2 limit 12,1;
Empty set (0.00 sec)

--获取第12行的记录
mysql> select * from t2 limit 11,1;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | c    | XXX  |
+------+------+------+
1 row in set (0.00 sec)

--with rollup的用法,多了一行以null开头的在第2列作汇总相加的行

mysql> select col2,sum(col1) from t2 group by col2 with rollup;
+------+-----------+
| col2 | sum(col1) |
+------+-----------+
| a    |         4 |
| b    |         8 |
| c    |        12 |
| NULL |        24 |
+------+-----------+
4 rows in set (0.00 sec)

--表T3和T4的内容
mysql> select * from t3;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|   22 | b    | XXX  |
|   33 | cc   | oo   |
+------+------+------+
3 rows in set (0.00 sec)

mysql> select * from t4;
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | xx   | mm   |
|   88 | gg   | hh   |
+------+------+------+
4 rows in set (0.00 sec)

--内连接
mysql> select t3.col1,t4.col2
    -> from t4 inner join t3
    -> on t3.col2=t4.col2
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|   22 | b    |
+------+------+
2 rows in set (0.00 sec)

--复合条件的内连接
mysql> select t3.col1,t4.col2
    -> from t4 inner join t3
    -> on t3.col2=t4.col2
    -> and t3.col1=t4.col1
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
+------+------+
1 row in set (0.00 sec)

--左连接(外连接)
mysql> 
mysql> select t3.col1,t4.col2
    -> from t4 left  outer  join t3
    -> on t3.col2=t4.col2
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|   22 | b    |
| NULL | xx   |
| NULL | gg   |
+------+------+
4 rows in set (0.00 sec)

--右连接,在ORACLE中+相当于在=号左边那个表后面(eg. a.id(+)=b.id)
mysql> select t3.col1,t4.col2
    -> from t4 right  outer  join t3
    -> on t3.col2=t4.col2
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|   22 | b    |
|   33 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> select t3.col1,t4.col2
    -> from t4 right  outer  join t3
    -> on t3.col2=t4.col2
    -> and t3.col1=t4.col1
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | a    |
|   22 | NULL |
|   33 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> select t3.col1,t4.col2
    -> from t4 right  outer  join t3
    -> on t3.col2=t4.col2
    -> and t3.col1=t4.col1
    -> and t3.col1=33
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | NULL |
|   22 | NULL |
|   33 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> 
mysql> select t3.col1,t4.col2
    -> from t4 right  outer  join t3
    -> on t3.col2=t4.col2
    -> and t3.col1=t4.col1
    -> and t4.col2='gg'
    -> ;
+------+------+
| col1 | col2 |
+------+------+
|    1 | NULL |
|   22 | NULL |
|   33 | NULL |
+------+------+
3 rows in set (0.00 sec)


--any和some用法等价，意思为只要结果中有任何的一个何满足条件就显示结果；
mysql> select * from t4 where 8>any (select col1 from t3);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | xx   | mm   |
|   88 | gg   | hh   |
+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from t4 where 8>some(select col1 from t3);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | xx   | mm   |
|   88 | gg   | hh   |
+------+------+------+
4 rows in set (0.00 sec)

--all的用法：条件必须所有的都满足才显示出结果；
mysql> select * from t4 where 8>all(select col1 from t3);
Empty set (0.00 sec)

--EXISTS用法与ORACLE一样
mysql> select * from t4 where exists(select 1 from t3 where col2='b');
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
|    3 | xx   | mm   |
|   88 | gg   | hh   |
+------+------+------+
4 rows in set (0.00 sec)

mysql> select * from t4 where exists(select 1 from t3 where t3.col2=t4.col2);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from t4 where not exists(select 1 from t3 where t3.col2=t4.col2);
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    3 | xx   | mm   |
|   88 | gg   | hh   |
+------+------+------+
2 rows in set (0.00 sec)

--表T4的值，重新更新了下，开始MYSQL正则表达式的用法regexp
mysql> select * from t4;
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
|    1 | a      | XXX   |
|    2 | b      | XXX   |
|    3 | xx     | mm    |
|   88 | gg     | hh    |
|  123 | bottle | moon  |
|  998 | fly    | happy |
+------+--------+-------+
6 rows in set (0.00 sec)

--匹配以b开头的列值
mysql> select * from t4 where col2 regexp '^b';
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|    2 | b      | XXX  |
|  123 | bottle | moon |
+------+--------+------+
2 rows in set (0.02 sec)

--匹配以b开头的列值且b后面必须跟一个值
mysql> select * from t4 where col2 regexp '^b.';
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|  123 | bottle | moon |
+------+--------+------+
1 row in set (0.00 sec)

--匹配包含gg且中间包含0个或多个值
mysql> select * from t4 where col2 regexp 'g*g';
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|   88 | gg   | hh   |
+------+------+------+
1 row in set (0.00 sec)

--区配包含gg且中间包含1个值
mysql> select * from t4 where col2 regexp 'g.g';
Empty set (0.00 sec)

--匹配包含字符gg
mysql> select * from t4 where col2 regexp 'gg';
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|   88 | gg   | hh   |
+------+------+------+
1 row in set (0.00 sec)

--匹配以py结尾列的值

mysql> select * from t4 where col3 regexp 'py$';
+------+------+-------+
| col1 | col2 | col3  |
+------+------+-------+
|  998 | fly  | happy |
+------+------+-------+
1 row in set (0.00 sec)

--匹配值中包含b或y的列值与[b|y]功能相同
mysql> select * from t4 where col2 regexp '[by]';
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
|    2 | b      | XXX   |
|  123 | bottle | moon  |
|  998 | fly    | happy |
+------+--------+-------+
3 rows in set (0.00 sec)


mysql> select * from t4 where col2 regexp '[b|y]';
+------+--------+-------+
| col1 | col2   | col3  |
+------+--------+-------+
|    2 | b      | XXX   |
|  123 | bottle | moon  |
|  998 | fly    | happy |
+------+--------+-------+
3 rows in set (0.00 sec)

--匹配包含1或2或3的列值，也可以写成[123]
mysql> select * from t4 where col1 regexp '[1-3]';
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|    1 | a      | XXX  |
|    2 | b      | XXX  |
|    3 | xx     | mm   |
|  123 | bottle | moon |
+------+--------+------+
4 rows in set (0.00 sec)

--匹配列值不包含1或2或3的列值
mysql> select * from t4 where col1 regexp '[^1-3]';
+------+------+-------+
| col1 | col2 | col3  |
+------+------+-------+
|   88 | gg   | hh    |
|  998 | fly  | happy |
+------+------+-------+
2 rows in set (0.00 sec)

--匹配列值至少出现t2次以上的列值
mysql> select * from t4 where col2 regexp 't{2}';
+------+--------+------+
| col1 | col2   | col3 |
+------+--------+------+
|  123 | bottle | moon |
+------+--------+------+
1 row in set (0.00 sec)

--匹配列值到少出现X1次，至多3次的列值
mysql> select * from t4 where col3 regexp 'X{1,3}';
+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
|    1 | a    | XXX  |
|    2 | b    | XXX  |
+------+------+------+
2 rows in set (0.00 sec)