MYSQL基础03 - 查询

--mysql选择查询,包含内容1.选择行数limit2.with rollup3.内连接，外连接，复合条件连接查询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正则表达式的用法regexpmysql> 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)--匹配包含字符ggmysql> 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)