MySQL常用进阶查询
一、正则表达式
- MySQL正则表达式通常是在检索数据库记录的时候,根据指定的匹配模式来匹配记录中符合要求的特殊字符串
- MySQL的正则表达式使用regexp这个关键字来指定正则表达式的匹配模式
匹配模式 | 说明 |
---|---|
^ | 匹配文本的开始字符 |
$ | 匹配文本的结束字符 |
. | 匹配任何单个字符 |
* | 匹配零个或多个在它前面的字符 |
+ | 匹配前面的字符1次或多次 |
字符串 | 匹配包含指定的字符串 |
p1|p2 | 匹配p1或p2 |
[…] | 匹配字符集合中的任意一个字符 |
[^…] | 匹配不在括号中的任何字符 |
{n} | 匹配前面的字符串n次 |
{n,m} | 匹配前面的字符串最少n次,最多m次 |
PS | ^虽然表示匹配开始字符,但也需要看^所处的位置 例如:[^a]b表示不包含括号中的a,则表示以b为开头 |
1.1 以特定字符串开头与结尾的记录
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的编号和姓名列里用正则表达式(regexp)匹配以刘开头的字符】
mysql> select 编号,姓名 from qz2 where 姓名 regexp '^刘';
+--------+--------+
| 编号 | 姓名 |
+--------+--------+
| 1 | 刘一 |
| 7 | 刘二 |
+--------+--------+
【在qz2表的编号和姓名列里用正则表达式(regexp)匹配以二结尾的字符】
mysql> select 编号,姓名 from qz2 where 姓名 regexp '二$';
+--------+--------+
| 编号 | 姓名 |
+--------+--------+
| 2 | 王二 |
| 7 | 刘二 |
+--------+--------+
1.2 匹配任何单个字符(以“.”代替字符串中的任意一个字符)
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的得分列里用正则表达式(regexp)匹配以1开头,中间5个字符,0结尾的字符】
mysql> select * from qz2 where 得分 regexp '1.....0';
+--------+--------+---------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 5 | 教授 | 100.000 | 成都 | 3 |
+--------+--------+---------+--------+--------+
1.3 匹配前面字符任意多次(0个或多个)
【在qz2表的姓名列里用正则表达式(regexp)匹配字符一前面有0个或者多个字符的字符】
mysql> select * from qz2 where 姓名 regexp '一*';
+--------+--------+--------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
+--------+--------+--------+--------+--------+
1.4 匹配前面字符至少一次(1次或多次)
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的姓名列里用正则表达式(regexp)匹配有字符‘二’一次或者多次的字符】
mysql> select * from qz2 where 姓名 regexp '二+';
+--------+--------+--------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+--------+--------+--------+
【在qz2表的姓名列里用正则表达式(regexp)匹配有字符‘一’一次或者多次的字符】
mysql> select * from qz2 where 姓名 regexp '一+';
+--------+--------+--------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
+--------+--------+--------+--------+--------+
1.5 匹配包含指定的字符串
mysql> select * from qz2 where 得分 regexp '0';
+--------+--------+---------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
7 rows in set (0.00 sec)
【在qz2表的得分列里用正则表达式(regexp)匹配包含指定字符‘9’的字符】
mysql> select * from qz2 where 得分 regexp '9';
+--------+--------+--------+--------+--------+
| 编号 | 姓名 | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 4 | 田五 | 99.000 | 南京 | 5 |
+--------+--------+--------+--------+--------+
1.6 匹配包含或者关系的记录(匹配p1|p2)
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的name列里用正则表达式(regexp)匹配包含字符‘明’或‘授’的字符】
mysql> select * from qz2 where name regexp '明|授';
+--------+--------+---------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
+--------+--------+---------+--------+--------+
1.7 匹配指定字符集中的任意一个
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的得分列里用正则表达式(regexp)匹配的字符包含指定字符集‘9或者6’的字符】
mysql> select * from qz2 where 得分 regexp '[9,6]';
+--------+--------+--------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
+--------+--------+--------+--------+--------+
1.8 匹配不在括号内的任何字符
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表的编号和得分列里用正则表达式(regexp)匹配不包含字符‘3,4,5,6’的字符】
mysql> select 编号,得分 from qz2 where 编号 regexp '[^3,4,5,6]';
+--------+--------+
| 编号 | 得分 |
+--------+--------+
| 1 | 80.000 |
| 2 | 90.000 |
| 7 | 80.000 |
+--------+--------+
1.9 匹配前面的字符串n次
mysql> select * from qz2;
+--------+--------+---------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+---------+--------+--------+
| 1 | 刘一 | 80.000 | 北京 | 2 |
| 2 | 王二 | 90.000 | 深圳 | 2 |
| 3 | 李四 | 60.000 | 上海 | 4 |
| 4 | 田五 | 99.000 | 南京 | 5 |
| 5 | 教授 | 100.000 | 成都 | 3 |
| 6 | 小明 | 10.000 | 镇江 | 3 |
| 7 | 刘二 | 80.000 | 北京 | 2 |
+--------+--------+---------+--------+--------+
【在qz2表得分列里用正则表达式(regexp)匹配包含字符串'9'2次的字符】
mysql> select * from qz2 where 得分 regexp '9{2}';
+--------+--------+--------+--------+--------+
| 编号 | name | 得分 | 地址 | 特长 |
+--------+--------+--------+--------+--------+
| 4 | 田五 | 99.000 | 南京 | 5 |
+--------+--------+--------+--------+--------+
二、运算符(算术、比较、逻辑、位与)
- MySQL的运算符用于对记录中的字段值进行运算
- MySQL的运算符共有四种(算术、比较、逻辑、位与)
2.1 算术运算
- 在除法运算和求余运算中,除数不能为0,若除数是0,则返回的结果为NULL
- 如果有多个运算符,按照先乘除后加减的优先级进行运算,相同优先级的运算符没有先后顺序
运算符 | 说明 |
---|---|
+ | 加法 |
- | 减法 |
* | 乘法 |
/ | 除法 |
% | 取余 |
mysql> select 1+2,2-1,3*3,5/2,4/2,6%3;
+-----+-----+-----+--------+--------+------+
| 1+2 | 2-1 | 3*3 | 5/2 | 4/2 | 6%3 |
+-----+-----+-----+--------+--------+------+
| 3 | 1 | 9 | 2.5000 | 2.0000 | 0 |
+-----+-----+-----+--------+--------+------+
【除法默认小数点后4位】
mysql> create table ys1 select 1+2,2-1,3*3,22222225/2,400000/2,6%3;
mysql> desc ys1;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| 1+2 | int(3) | NO | | 0 | |
| 2-1 | int(3) | NO | | 0 | |
| 3*3 | int(3) | NO | | 0 | |
| 22222225/2 | decimal(12,4) | YES | | NULL | |
| 400000/2 | decimal(10,4) | YES | | NULL | |
| 6%3 | int(1) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
2.2 比较运算符
- 字符串的比较默认不区分大小写,可使用binary来区分
- 比较对象:数字或者字符
运算符 | 说明 |
---|---|
= | 等于 |
!=或<> | 不等于 |
like | 通配符匹配 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
is null | 判断1个值是否为NULL |
is not noull | 判断1个值是否不为NULL |
between and | 两者之间 |
greatest | 两个或多个参数时返回最大值 |
least | 两个或多个参数时放回最小值 |
in | 在集合中 |
2.2.1 等于(=)
- 等号(=)用来判断数字、字符串和表达式是否相等,如果相等则返回1,如果不相等则返回0
- 如果比较的两者有一个值是NULL,则比较的结果为NULL
- 字符的比较是根据ASCII码来判断的,如果ASCII码相等,则表示这两个字符相同;如果ASCII码不相等,则表示两个字符不相同
- 这里需要注意的ASCII码:a(97)、A(65)、数字0(48)
mysql> select 3=4,5='5','e'='b',(4+4)=(7+1),'6'=null,'6'='a';
+-----+-------+---------+-------------+----------+---------+
| 3=4 | 5='5' | 'e'='b' | (4+4)=(7+1) | '6'=null | '6'='a' |
+-----+-------+---------+-------------+----------+---------+
| 0 | 1 | 0 | 1 | NULL | 0 |
+-----+-------+---------+-------------+----------+---------+
- 通过返回结果可以看出:如果两者都是整数,则按照整数值进行比较;
如果一个整数与一个字符串比较,则会自动将字符串转换为数字,再进行比较(一般不会把这两者放一起做对比)
如果两者都是字符串,则按照字符串进行比较
如果两者中至少有一个值是NULL,则结果为NULL
2.2.2 不等于(!=或<>)
- 用于针对数字、字符串和表达式不相等的比较
- 不相等则返回1,相等则返回0。与等于(=)的返回值相反,同时不等于(!=或<>)无法用于判断是否为NULL
mysql> select 'qz'<>'qqzz',3<>4,6!=6,3.333!=3,null<>null;
+--------------+------+------+----------+------------+
| 'qz'<>'qqzz' | 3<>4 | 6!=6 | 3.333!=3 | null<>null |
+--------------+------+------+----------+------------+
| 1 | 1 | 0 | 1 | NULL |
+--------------+------+------+----------+------------+
2.2.3 大于、大于等于、小于、小于等于
- 大于(>)运算符用来判断左侧的操作数是否大于右侧的操作数,若大于返回1,否则返回0,同样也不能用于判断NULL
- 小于(<)运算符用来判断左侧的操作数是否小于右侧的操作数,若小于返回1,否则返回0,同样也不能用于判断NULL
- 大于等于(>=)运算符用来判断左侧的操作数是否大于等于右侧的操作数,若大于返回1,否则返回0,同样也不能用于判断NULL
- 小于等于(<=)运算符用来判断左侧的操作数是否小于等于右侧的操作数,若小于返回1,否则返回0,同样也不能用于判断NULL
mysql> select 111>12,'b'>'c',201>=203,(7+8)>=(6+7),5.55<4,2<3,'y'<='z',55<=55.55,'p'>=null;
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+
| 111>12 | 'b'>'c' | 201>=203 | (7+8)>=(6+7) | 5.55<4 | 2<3 | 'y'<='z' | 55<=55.55 | 'p'>=null |
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+
| 1 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | NULL |
+--------+---------+----------+--------------+--------+-----+----------+-----------+-----------+
2.2.4 判断一个值为或者不为null(is null、is not null)
- is null 判断一个值是否为null,如果为则返回1,否则返回0
- is not null 判断一个值是否不为null,如果不为null则返回1,否则返回0
mysql> select 5 is null,'q' is not null,null is null;
+-----------+-----------------+--------------+
| 5 is null | 'q' is not null | null is null |
+-----------+-----------------+--------------+
| 0 | 1 | 1 |
+-----------+-----------------+--------------+
2.2.5 两者之间(between…and…)
- between比较运算符通常用于判断一个值是否落在某两个值之间,是则回复1,不是则返回0
mysql> select 10 between 10 and 15,10 between 5 and 20,10 between 100 and 200,'b' between 'a' and 'f';
+----------------------+---------------------+------------------------+-------------------------+
| 10 between 10 and 15 | 10 between 5 and 20 | 10 between 100 and 200 | 'b' between 'a' and 'f' |
+----------------------+---------------------+------------------------+-------------------------+
| 1 | 1 | 0 | 1 |
+----------------------+---------------------+------------------------+-------------------------+
2.2.6 两个或多个参数时返回最小(least )或最大值(greatest)
- 如果一个为null,则返回null
- 数字对比则按大小排列
- 字母对比,则按从a-b顺序,字母越前越“小”
mysql> select least(5,7,9),least('d','e','f'),greatest(5,7,9),greatest('d','e','f');
+--------------+--------------------+-----------------+-----------------------+
| least(5,7,9) | least('d','e','f') | greatest(5,7,9) | greatest('d','e','f') |
+--------------+--------------------+-----------------+-----------------------+
| 5 | d | 9 | f |
+--------------+--------------------+-----------------+-----------------------+
2.2.7 在或不再集合中(in,not in)
- in 判断一个值是否在对应的列表中,在返回1,不在返回0
- not in 判断一个值是否不在对应的列表中,不在返回1,在返回0
mysql> select 3 in (1,3,5,7),'e' not in('d','e','f');
+----------------+-------------------------+
| 3 in (1,3,5,7) | 'e' not in('d','e','f') |
+----------------+-------------------------+
| 1 | 0 |
+----------------+-------------------------+
2.2.8 通配符匹配
- like 用来匹配字符串,如果匹配成功则返回1,不成功返回0
- like 支持两种通配符:’%’:用于匹配任意数目的字符,’ ':只能匹配一个字符
- not like 与 like 相反,如果没有匹配成功则返回1,如果成功则返回0
mysql> select 'qwer' like 'qwe_','qwe' like '%e','qwe' not like '%ty';
+--------------------+-----------------+----------------------+
| 'qwer' like 'qwe_' | 'qwe' like '%e' | 'qwe' not like '%ty' |
+--------------------+-----------------+----------------------+
| 1 | 1 | 1 |
+--------------------+-----------------+----------------------+
2.3 逻辑运算符(布尔运算符)
- 逻辑运算符又被称为布尔运算符,通常用来判断表达式的真假。若为真则返回1,否则返回0
- 真和假也可以用true 和 false 表示
运算符 | 说明 |
---|---|
not 或 ! | 逻辑非 |
and 或 && | 逻辑与 |
or | 逻辑或 |
xor | 逻辑异或 |
2.3.1 逻辑非(not或!)
- 将跟在逻辑非后面的值取反,如果not后面的操作数为0时,则所得值为1;如果操作数为非0时,所得值为0;如果操作数为null时,所得值为null
mysql> select not 3,!4,not 0,!(10-10);
+-------+----+-------+----------+
| not 3 | !4 | not 0 | !(10-10) |
+-------+----+-------+----------+
| 0 | 0 | 1 | 1 |
+-------+----+-------+----------+
【因为优先级的问题,所以!1+1返回1,not 1+1返回0】
mysql> select !1+1,not 1+1;
+------+---------+
| !1+1 | not 1+1 |
+------+---------+
| 1 | 0 |
+------+---------+
2.3.2 逻辑与(and或&&)
- 当所有操作数都为非零值并且不为null时,返回值为1
- 当有一个或者多个操作为0时,返回值为0
- 当操作数中又任何一个为null时,返回值为null
mysql> select 22 and 33,44 && 0,0 && null,1 and null;
+-----------+---------+-----------+------------+
| 22 and 33 | 44 && 0 | 0 && null | 1 and null |
+-----------+---------+-----------+------------+
| 1 | 0 | 0 | NULL |
+-----------+---------+-----------+------------+
2.3.3 逻辑或(or)
- 当两个操作数都为非null值时,如果有任意一个操作数为非零值,则返回1,否则返回0
- 当有一个操作数为null时,如果另一个操作数为非零值,则返回1,否则返回null
- 如果两个操作数均为null时,则返回null
mysql> select 22 or 33,0 or null,null or null;
+----------+-----------+--------------+
| 22 or 33 | 0 or null | null or null |
+----------+-----------+--------------+
| 1 | NULL | NULL |
+----------+-----------+--------------+
2.3.4 逻辑异或(xor)
- 当任意一个操作数为null时,返回值为null
- 对于非null的操作数,如果两个操作数都是非0值或者都是0值,则返回0
- 如果一个为0值,一个为非0值,返回1
mysql> select 22 xor 33,0 xor 11,11 xor 0 ,1 xor null,null xor 33;
+-----------+----------+----------+------------+-------------+
| 22 xor 33 | 0 xor 11 | 11 xor 0 | 1 xor null | null xor 33 |
+-----------+----------+----------+------------+-------------+
| 0 | 1 | 1 | NULL | NULL |
+-----------+----------+----------+------------+-------------+
2.4 位与运算符
- 位与运算符是在二进制数上进行计算的运算符
- 位与运算会先将操作数变成二进制数,再进行位与运算(也就是两个数的二进制数相乘)
- 最后再将计算结果从二进制数变回十进制数
运算符 | 说明 |
---|---|
& | 按位与 |
| | 按位或 |
^ | 按位异或 |
! | 取反 |
<< | 左移 |
>> | 右移 |
2.4.1 按位与运算(&)
- 按位与运算(&):当对应的二进制位都是1时,则运算结果为1,否则为0。
- 因为10转换为二进制数为1010,15转换为二进制数为1111,所以10 & 15结果为10
mysql> select 10 & 15;
+---------+
| 10 & 15 |
+---------+
| 10 |
+---------+
2.4.2 按位或运算(|)
- 当对应的二进制位有一个或两个为1时,运算结果为1,否则为0
- 因为10转换为二进制数为1010,15转换为二进制数为1111,所以10 | 15结果为15
mysql> select 10 | 15;
+---------+
| 10 | 15 |
+---------+
| 15 |
+---------+
2.4.3 按位异或运算(^)
- 当对应的二进制位不相同时,运算结果为1,否则为0
- 因为10转换为二进制数为1010,15转换为二进制数为1111,所以10 ^ 15结果为5
mysql> select 10 ^ 15;
+---------+
| 10 ^ 15 |
+---------+
| 5 |
+---------+
2.4.4 按位取反(~)
- 是将对应的二进制数逐位反转,即1取反后变为0,0取反后变为1
- 因为10转换为二进制数为1010,15转换为二进制数为1111,所以取反后为0101与0000,再进行求与操作后结果为0000,转换为十进制为0
mysql> select 10 &~ 15;
+----------+
| 10 &~ 15 |
+----------+
| 0 |
+----------+
2.5 运算符的优先级
- 不管哪种运算符,在使用过程中都有优先级问题
- 运算符的优先级决定了不同的运算符在计算过程中的先后顺序
- 级别越高的运算符会先进行计算,如果运算符的基本相同,MySQL则会按照顺序从左到右依次进行计算
运算符 | 优先级 |
---|---|
! | 1 |
~ | 2 |
^ | 3 |
* / % | 4 |
+ - | 5 |
>> << | 6 |
& | 7 |
| | 8 |
=、<=>、>=、> <=、<、<>、!= is、like、regexp、in | 9 |
between、case、when then、else | 10 |
not | 11 |
&& and | 12 |
or xor | 13 |
: = | 14 |
三、连接查询(内、左、右)
- MySQL的连接查询通常是将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接
- 要先确定一个主表作为结果集,然后将其他表的行有选择性的连接到选定的主表结果集上
3.1 实验库+表创建准备
mysql> create database qz1;
mysql> use qz1
mysql> CREATE TABLE qz1 (q_id int(11) DEFAULT NULL,q_name varchar(32) DEFAULT NULL,q_level int(11) DEFAULT NULL);
mysql> CREATE TABLE qz2 (w_id int(11) DEFAULT NULL,w_name varchar(32) DEFAULT NULL,w_level int(11) DEFAULT NULL);
mysql> insert into qz1(q_id, q_name, q_level) values(1, 'aaa', 15);
mysql> insert into qz1(q_id, q_name, q_level) values(2, 'bbb', 25);
mysql> insert into qz1(q_id, q_name, q_level) values(3, 'ccc', 35);
mysql> insert into qz1(q_id, q_name, q_level) values(4, 'ddd', 45);
mysql> insert into qz2(w_id, w_name, w_level) values(2, 'bbb', 25);
mysql> insert into qz2(w_id, w_name, w_level) values(3, 'ccc', 35);
mysql> insert into qz2(w_id, w_name, w_level) values(5, 'eee', 55);
mysql> insert into qz2(w_id, w_name, w_level) values(6, 'fff', 65);
mysql> select * from qz1;
+------+--------+---------+
| q_id | q_name | q_level |
+------+--------+---------+
| 1 | aaa | 15 |
| 2 | bbb | 25 |
| 3 | ccc | 35 |
| 4 | ddd | 45 |
+------+--------+---------+
mysql> select * from qz2;
+------+--------+---------+
| w_id | w_name | w_level |
+------+--------+---------+
| 2 | bbb | 25 |
| 3 | ccc | 35 |
| 5 | eee | 55 |
| 6 | fff | 65 |
+------+--------+---------+
3.2 内连接(系统默认)
- MySQL中的内连接就是两张表或多张表中同时符合某种条件的数据记录的组合
- 通常在from子句中使用关键字inner join来连接多张表,并使用on子句设置连接条件,内连接也是系统默认的表连接,所以在from子句后可以省略inner关键字,只使用关键字 join
- 当同时有多个表时,也可以连续使用inner join来实现多表的内连接。但是为了更好的性能,不建议超过三个表
- 语法:select 字段 from 表1 inner join 表2 on 表1.字段 = 表2.字段;
mysql> select q_id,q_name,q_level from qz1 q inner join qz2 w on q_id=w_id;
+------+--------+---------+
| q_id | q_name | q_level |
+------+--------+---------+
| 2 | bbb | 25 |
| 3 | ccc | 35 |
+------+--------+---------+
3.3 左连接(左外连接)
- 左连接也可以被称为左外连接,在from子句中使用left join或者left outer join关键字来表示
- 左连接以左侧表(语法中左侧)为基础表,接收左表的所有行,并用这些行与右侧参考表中的记录进行匹配,也就是说匹配左表中的所有行以及右表中符合条件的行
- 语法1:select * from 表1 left join 表2 on 表1.字段 = 表2.字段;
- 语法2:select * from 表1 left outer join 表2 on 表1.字段 = 表2.字段;
【左连接中左表的记录将会全部展示出来,而右表只会显示符合搜索条件的记录,右表记录不足的地方均展示为NULL】
mysql> select * from qz1 left join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
| 2 | bbb | 25 | 2 | bbb | 25 |
| 3 | ccc | 35 | 3 | ccc | 35 |
| 1 | aaa | 15 | NULL | NULL | NULL |
| 4 | ddd | 45 | NULL | NULL | NULL |
+------+--------+---------+------+--------+---------+
mysql> select * from qz1 left outer join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
| 2 | bbb | 25 | 2 | bbb | 25 |
| 3 | ccc | 35 | 3 | ccc | 35 |
| 1 | aaa | 15 | NULL | NULL | NULL |
| 4 | ddd | 45 | NULL | NULL | NULL |
+------+--------+---------+------+--------+---------+
3.4 右连接(右外连接 )
- 右连接也称为右外连接,在from子句中使用right join或者right outer join关键字表示
- 右连接跟左连接正好相反,它是以右表(语法中右侧)为基础表,用于接收右表中的所有行,并用这些记录与左表中的行进行匹配
- 语法1:select * from 表1 right join 表2 on 表1.字段 = 表2.字段;
- 语法2:select * from 表1 right outer join 表2 on 表1.字段 = 表2.字段;
【在右连接的查询结果中,除了符合匹配规则的行外,还包括右表中有但是左表中不匹配的行,但是这些记录在左表中以NULL补足】
mysql> select * from qz1 right join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
| 2 | bbb | 25 | 2 | bbb | 25 |
| 3 | ccc | 35 | 3 | ccc | 35 |
| NULL | NULL | NULL | 5 | eee | 55 |
| NULL | NULL | NULL | 6 | fff | 65 |
+------+--------+---------+------+--------+---------+
mysql> select * from qz1 right outer join qz2 on qz1.q_id=qz2.w_id;
+------+--------+---------+------+--------+---------+
| q_id | q_name | q_level | w_id | w_name | w_level |
+------+--------+---------+------+--------+---------+
| 2 | bbb | 25 | 2 | bbb | 25 |
| 3 | ccc | 35 | 3 | ccc | 35 |
| NULL | NULL | NULL | 5 | eee | 55 |
| NULL | NULL | NULL | 6 | fff | 65 |
+------+--------+---------+------+--------+---------+
四、总结
4.1 正则
- MySQL正则表达式的作用:在检索数据库记录的时候,根据指定的匹配模式来匹配记录中符合要求的特殊字符串
- MySQL的正则表达式使用regexp这个关键字来指定正则表达式的匹配模式
4.2 运算符
- MySQL的运算符的作用:用于对记录中的字段值进行运算
- MySQL的运算符共有四种(算术、比较、逻辑、位与)
4.3 连接查询
- MySQL的连接查询作用:将来自两个或多个表的行结合起来,基于这些表之间的共同字段,进行数据的拼接
- 连接查询分为:内查询(系统默认)、左查询(左外连接查询)、右查询(右外连接查询)