Mysql高阶语句介绍(正则和运算)
mysql> select * from test1;
+------+-----------+--------+------------+--------+----------+
| id | name | score | address | hobbid | addr |
+------+-----------+--------+------------+--------+----------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL |
| 7 | lilei | 50.00 | nanjing | 5 | NULL |
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL |
| 1 | zhangsan | 80.00 | beijing | 2 | |
+------+-----------+--------+------------+--------+----------+
7 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp '^li'; //查询以li开头的name
+------+-------+
| id | name |
+------+-------+
| 7 | lilei |
| 2 | lisi |
+------+-------+
2 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'i$'; //查询以i结尾的name
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 7 | lilei |
| 2 | lisi |
| 4 | tianqi |
| 5 | wangdi |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'l.l..'; //查询l与l之间的任一字符和l后面的任意连个字符的name
+------+-------+
| id | name |
+------+-------+
| 7 | lilei |
+------+-------+
1 row in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'ang*'; //查询an后有g出现零次或多次的name
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 4 | tianqi |
| 5 | wangdi |
| 3 | wangwu |
| 1 | zhangsan |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'ang+'; //g必须出现一次或多次
+------+----------+
| id | name |
+------+----------+
| 5 | wangdi |
| 3 | wangwu |
| 1 | zhangsan |
+------+----------+
3 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'ang?'; //g可以不出现或者只出现一次,两次的不匹配
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 4 | tianqi |
| 5 | wangdi |
| 3 | wangwu |
| 1 | zhangsan |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'an|zh'; //查询包含an或者zh的name
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 4 | tianqi |
| 5 | wangdi |
| 3 | wangwu |
| 1 | zhangsan |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp '^[s|z]'; //查询以s或z开头的name
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
+------+----------+
1 row in set (0.00 sec)
mysql> select id,name from test1 where name regexp '[^zhangsan]'; //查询不包含zhangsan的name
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 7 | lilei |
| 2 | lisi |
| 4 | tianqi |
| 5 | wangdi |
| 3 | wangwu |
+------+-----------+
6 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp '^[^w]'; //查询不以w开头的name
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 7 | lilei |
| 2 | lisi |
| 4 | tianqi |
| 1 | zhangsan |
+------+-----------+
5 rows in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'n{2}'; //查询n连续出现两次的name
+------+--------+
| id | name |
+------+--------+
| 8 | jinnan |
+------+--------+
1 row in set (0.00 sec)
mysql> select id,name from test1 where name regexp 'n{1,2}'; //查询n出现最少一次,或者最多两次
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 4 | tianqi |
| 5 | wangdi |
| 3 | wangwu |
| 1 | zhangsan |
| 8 | jinnan |
+------+-----------+
6 rows in set (0.00 sec)
mysql> select 1+1,1-1,4*4,5/6,5%3; //除法会保留4位有效小数
+-----+-----+-----+--------+------+
| 1+1 | 1-1 | 4*4 | 5/6 | 5%3 |
+-----+-----+-----+--------+------+
| 2 | 0 | 16 | 0.8333 | 2 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)
mysql> create table num as select 1+1,1-1,4*4,5/6,5%3;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
mysql> desc num;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| 1+1 | int(3) | NO | | 0 | |
| 1-1 | int(3) | NO | | 0 | |
| 4*4 | int(3) | NO | | 0 | |
| 5/6 | decimal(5,4) | YES | | NULL | |
| 5%3 | int(1) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select 3=4,1=1,3='3',3='2','a'='a',null='a',null=0,null=null; //null与任何值比返回都是null
+-----+-----+-------+-------+---------+----------+--------+-----------+
| 3=4 | 1=1 | 3='3' | 3='2' | 'a'='a' | null='a' | null=0 | null=null |
+-----+-----+-------+-------+---------+----------+--------+-----------+
| 0 | 1 | 1 | 0 | 1 | NULL | NULL | NULL |
+-----+-----+-------+-------+---------+----------+--------+-----------+
1 row in set (0.00 sec)
mysql> select 'abc'<>'acb',1!=1,1!=2,null<>null; //字符串是按照顺序比较
+--------------+------+------+------------+
| 'abc'<>'acb' | 1!=1 | 1!=2 | null<>null |
+--------------+------+------+------------+
| 1 | 0 | 1 | NULL |
+--------------+------+------+------------+
1 row in set (0.00 sec)
mysql> select 5>4,5>=4,3<2,3<=3,'a'<='b',null<'q'; //大于等于或小于等于只要满足一个都是返回1,null比较返回null,字符串比较会先转化为ASCII码再比较
+-----+------+-----+------+----------+----------+
| 5>4 | 5>=4 | 3<2 | 3<=3 | 'a'<='b' | null<'q' |
+-----+------+-----+------+----------+----------+
| 1 | 1 | 0 | 1 | 1 | NULL |
+-----+------+-----+------+----------+----------+
1 row in set (0.00 sec)
mysql> select 0 is null,'a' is not null,null is null; //判断是否为null值
+-----------+-----------------+--------------+
| 0 is null | 'a' is not null | null is null |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
1 row in set (0.00 sec)
mysql> select 4 between 1 and 5,'a' between 'a' and 'b'; //Between and 覆盖的范围是>=和<=关系
+-------------------+-------------------------+
| 4 between 1 and 5 | 'a' between 'a' and 'b' |
+-------------------+-------------------------+
| 1 | 1 |
+-------------------+-------------------------+
1 row in set (0.00 sec)
mysql> select least(4,5,6),greatest(4,5,6),least('a','b','c'),greatest('a','b','c'),least(4,5,null),greatest(null,'a','b');
+--------------+-----------------+--------------------+-----------------------+-----------------+------------------------+
| least(4,5,6) | greatest(4,5,6) | least('a','b','c') | greatest('a','b','c') | least(4,5,null) | greatest(null,'a','b') |
+--------------+-----------------+--------------------+-----------------------+-----------------+------------------------+
| 4 | 6 | a | c | NULL | NULL |
+--------------+-----------------+--------------------+-----------------------+-----------------+------------------------+
1 row in set (0.00 sec)
mysql> select 1 in (1,2,3), 2 not in ('a','b','c'), 'a' in ('c','b');
+--------------+------------------------+------------------+
| 1 in (1,2,3) | 2 not in ('a','b','c') | 'a' in ('c','b') |
+--------------+------------------------+------------------+
| 1 | 1 | 0 |
+--------------+------------------------+------------------+
1 row in set, 3 warnings (0.00 sec)
mysql> select 'abc' like 'a%','abc' like 'ab_', 'abc' not like 'a__';
+-----------------+------------------+----------------------+
| 'abc' like 'a%' | 'abc' like 'ab_' | 'abc' not like 'a__' |
+-----------------+------------------+----------------------+
| 1 | 1 | 0 |
+-----------------+------------------+----------------------+
1 row in set (0.00 sec)
mysql> select !0,!3,not 0, ! null; //如果 NOT 后面的操作数为 0 时,所得值为 1,如果操作数为非 0 时,所得值为 0,如果操作数为 NULL 时,所得值为 NULL
+----+----+-------+--------+
| !0 | !3 | not 0 | ! null |
+----+----+-------+--------+
| 1 | 0 | 1 | NULL |
+----+----+-------+--------+
1 row in set (0.00 sec)
mysql> select 2 and 3,4 && 0,4 && null,0 and null,null and null; //0与null返回是0,null非零数返回为null
+---------+--------+-----------+------------+---------------+
| 2 and 3 | 4 && 0 | 4 && null | 0 and null | null and null |
+---------+--------+-----------+------------+---------------+
| 1 | 0 | NULL | 0 | NULL |
+---------+--------+-----------+------------+---------------+
1 row in set (0.00 sec)
mysql> select 2 or 9,9 or 0, 9 or null, 0 or 0,null or null,0 or null; //null或非零返回1,0或null返回null
+--------+--------+-----------+--------+--------------+-----------+
| 2 or 9 | 9 or 0 | 9 or null | 0 or 0 | null or null | 0 or null |
+--------+--------+-----------+--------+--------------+-----------+
| 1 | 1 | 1 | 0 | NULL | NULL |
+--------+--------+-----------+--------+--------------+-----------+
1 row in set (0.00 sec)
mysql> select 0 or null or 9; //也可以同时多个数数比较
+----------------+
| 0 or null or 9 |
+----------------+
| 1 |
+----------------+
1 row in set (0.01 sec)
mysql> select 2 xor 3,1 xor 0,0 xor null,null xor null,0 xor 0; //有一个0,一个非零会返回1,两者都非零或零返回0,null与其他返回null
+---------+---------+------------+---------------+---------+
| 2 xor 3 | 1 xor 0 | 0 xor null | null xor null | 0 xor 0 |
+---------+---------+------------+---------------+---------+
| 0 | 1 | NULL | NULL | 0 |
+---------+---------+------------+---------------+---------+
1 row in set (0.00 sec)
>> 按位右移
mysql> select 10 & 15,10 | 15,10 ^ 15,5 &~1,10 << 4,256 >> 8;
+---------+---------+---------+-------+---------+----------+
| 10 & 15 | 10 | 15 | 10 ^ 15 | 5 &~1 | 10 << 4 | 256 >> 8 |
+---------+---------+---------+-------+---------+----------+
| 10 | 15 | 5 | 4 | 160 | 1 |
+---------+---------+---------+-------+---------+----------+
1 row in set (0.01 sec)
mysql> select * from test1
-> ;
+------+-----------+--------+------------+--------+----------+
| id | name | score | address | hobbid | addr |
+------+-----------+--------+------------+--------+----------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL |
| 7 | lilei | 50.00 | nanjing | 5 | NULL |
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL |
| 1 | zhangsan | 80.00 | beijing | 2 | |
| 8 | jinnan | 60.00 | shanghai | 2 | NULL |
+------+-----------+--------+------------+--------+----------+
8 rows in set (0.00 sec)
mysql> select * from qq;
+----+-----------+-------+--------------+--------+
| id | name | score | address_new | hobby |
+----+-----------+-------+--------------+--------+
| 1 | zhangsan | 90.00 | 地址不详 | NULL |
| 2 | wangwu | 70.00 | 上海 | 篮球 |
| 6 | hanmeimei | 40.00 | nanjing | 3 |
+----+-----------+-------+--------------+--------+
3 rows in set (0.00 sec)
mysql> select test1.id,test1.name from test1 inner join qq on test1.name=qq.name; //查询两张表相同的name并显示test1表的id,name字段记录
+------+-----------+
| id | name |
+------+-----------+
| 6 | hanmeimei |
| 3 | wangwu |
| 1 | zhangsan |
+------+-----------+
3 rows in set (0.00 sec)
mysql> select * from test1 left join qq on test1.name=qq.name; //以左边表为主表,显示表内容,右边表没有的内容显示null
+------+-----------+--------+------------+--------+----------+------+-----------+-------+--------------+--------+
| id | name | score | address | hobbid | addr | id | name | score | address_new | hobby |
+------+-----------+--------+------------+--------+----------+------+-----------+-------+--------------+--------+
| 1 | zhangsan | 80.00 | beijing | 2 | | 1 | zhangsan | 90.00 | 地址不详 | NULL |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL | 2 | wangwu | 70.00 | 上海 | 篮球 |
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL | 6 | hanmeimei | 40.00 | nanjing | 3 |
| 7 | lilei | 50.00 | nanjing | 5 | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | lisi | 90.00 | shengzheng | 2 | shanghai | NULL | NULL | NULL | NULL | NULL |
| 4 | tianqi | 100.00 | hangzhou | 5 | shanghai | NULL | NULL | NULL | NULL | NULL |
| 5 | wangdi | 98.00 | laowo | 3 | shanghai | NULL | NULL | NULL | NULL | NULL |
| 8 | jinnan | 60.00 | shanghai | 2 | NULL | NULL | NULL | NULL | NULL | NULL |
+------+-----------+--------+------------+--------+----------+------+-----------+-------+--------------+--------+
8 rows in set (0.00 sec)
mysql> select * from test1 right join qq on test1.name=qq.name; //以右边表作为主表显示表内容,左边没有的内容显示null
+------+-----------+-------+----------+--------+------+----+-----------+-------+--------------+--------+
| id | name | score | address | hobbid | addr | id | name | score | address_new | hobby |
+------+-----------+-------+----------+--------+------+----+-----------+-------+--------------+--------+
| 6 | hanmeimei | 40.00 | nanjing | 3 | NULL | 6 | hanmeimei | 40.00 | nanjing | 3 |
| 3 | wangwu | 50.00 | shanghai | 4 | NULL | 2 | wangwu | 70.00 | 上海 | 篮球 |
| 1 | zhangsan | 80.00 | beijing | 2 | | 1 | zhangsan | 90.00 | 地址不详 | NULL |
+------+-----------+-------+----------+--------+------+----+-----------+-------+--------------+--------+
3 rows in set (0.00 sec)
连接查询
- 内连接是默认表连接,可省略inner ,只用 join ,查询结果为两表相同字段,最好不要超过三张表
- 左连接中主表在左边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来
- 右连接中主表在右边,主表内容会全部显示出来,在从表中没匹配到的以NULL显示出来