MySQL（4）运算符 | 关联查询详解

一、MySQL 的运算符

（1）算术运算符：+ - * /（除也可以写成 div) %（取模可以写成 mod） （2）比较运算符：= > >= < <= !=（不等于还可以写成<>） <=>（安全等于） （3）逻辑运算符：&&（逻辑与也可以写成 and） ||（逻辑或也可以写成 or） not（逻辑非） （4）范围：表达式 between … and … （也可以写成 表达式>=… and 表达式 <=…） 表达式 not between … and …（也可以写成 表达式<… || 表达式 >…） （5）集合：in (值，值，值…) not in(值，值，值…) （6）模糊查询：LIKE NOT LIKE，通配符：%表示 0-n 个字符，_下划线代表一个字符 （7）位运算符：&（按位与） |（按位或）^（按位异或）~（按位取反）>>（右移）<<（左移） （8）NULL 值判断，is null 或 is not null，如果使用 null=null，null<>null,null=0,null<>0,null=false 等都不对。 不过 xxx is null 可以使用 xxx <=> null ，xxx is not null 可以写成 not xxx <=> null 结论：所有的运算符遇到 NULL 结果都是 NULL，除了<=>

1 、算术运算符

+,-,*,/(div),%(mod) #筛选出 eid 是偶数的员工 SELECT * FROM t_employee WHERE eid % 2 = 0; SELECT * FROM t_employee WHERE eid MOD 2 = 0; #查看每天的基本工资值，每个月按 22 天算 SELECT eid,basic_salary/12 AS "日薪" FROM t_salary; #div 也表示除，但是只保留整数部分 SELECT eid,basic_salary DIV 12 AS "日薪" FROM t_salary; #关于+，在 Java 中，+的左右两边如果有字符串，那么表示字符串的拼接,但是在 MySQL 中+只表示数值相加， #如果遇到非数值类型，先尝试转成数值，如果转失败，就按 0 计算 SELECT eid+ename FROM t_employee; SELECT eid+birthday FROM t_employee; #MySQL 中字符串拼接要使用字符串函数实现 SELECT CONCAT(eid,":",ename) AS result FROM t_employee;

2 、比较运算符

=，>, <，>=, <=， !=(不等于<>)，<=>（安全等于） #查询 basic_salary!=10000 SELECT eid,basic_salary FROM t_salary WHERE basic_salary != 10000; SELECT eid,basic_salary FROM t_salary WHERE basic_salary <> 10000; #查询 basic_salary=10000，注意在 Java 中比较是== SELECT eid,basic_salary FROM t_salary WHERE basic_salary = 10000; #查询 commission_pct 等于 0.40 SELECT eid,commission_pct FROM t_salary WHERE commission_pct = 0.40; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> 0.40; #查询 commission_pct 等于 NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL; SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL; #查询 commission_pct 不等于 NULL SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL; SELECT eid,commission_pct FROM t_salary WHERE NOT commission_pct <=> NULL;

3 、逻辑运算符

与&&，或||，非! 与 AND，或 OR ，非 NOT，异或 XOR #查询性别男，并且在 90 以前出生的员工 SELECT * FROM t_employee WHERE gender='男' AND birthday<'1990-01-01'; #查询职位编号 job_id 是 1 或 2 的员工 SELECT * FROM t_employee WHERE job_id =1 OR job_id = 2; #查询基本薪资是在 9000-12000 之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE basic_salary >=9000 AND basic_salary<=12000; #查询基本薪资不在 9000-12000 之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE NOT (basic_salary >=9000 AND basic_salary<=12000); SELECT eid,basic_salary FROM t_salary WHERE basic_salary <9000 OR basic_salary>12000;

4 、范围和集合

between ... and ... 和 not between ... and ... in(集合) 和 not in(...) #查询基本薪资是在 9000-12000 之间的员工编号和基本薪资 SELECT eid,basic_salary FROM t_salary WHERE basic_salary BETWEEN 9000 AND 12000; #查询 eid 是 1,3,5 的基本工资 SELECT eid,basic_salary FROM t_salary WHERE eid IN (1,3,5);

5 、模糊查询

like 和 通配符 一起使用 like _ 匹配单个字符 like % 匹配任意个字符 #查询名字中有'冰'字的员工信息 SELECT * FROM t_employee WHERE ename LIKE '%冰%'; #查询姓李的员工信息 SELECT * FROM t_employee WHERE ename LIKE '李%'; #查询姓李，名字就一个字的员工信息 SELECT * FROM t_employee WHERE ename LIKE '李_'; #查询李冰冰的信息 SELECT * FROM t_employee WHERE ename LIKE '李冰冰'; 通常情况，可以使用 FIND_IN_SET()函数或 LIKE 操作符搜索 SET 值： mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0; mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%'; 第 1 个语句找出 SET_col 包含 value set 成员的行。第 2 个类似，但有所不同：它在其它地方找出 set_col 包含 value的行，甚至是在另一个 SET 成员的子字符串中。 下面的语句也是合法的： mysql> SELECT * FROM tbl_name WHERE set_col & 1; mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; 第 1 个语句寻找包含第 1 个 set 成员的值。第 2 个语句寻找一个确切匹配的值。应注意第 2 类的比较。将 set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。 如果想要为 SET 列确定所有可能的值，使用 SHOW COLUMNS FROM tbl_name LIKE set_col 并解析输出中第 2 列的 SET定义。 有什么实际应用呢？ 比如我们设定用户的权限控制，一个用户可能会有多种权限，我们使用所有权限创建一个 SET 类型的字段，我们不需要用一系列 int 来定义各种权限了，直接使用一个 SET 字段即可： /* 用户权限 permission 表 */ create table user_permission( id int UNSIGNED not null auto_increment, user_id int not null , permission set('阅读','评论','发帖') not null, primary key(id), unique (user_id) ); desc user_permission; insert into user_permission values (0,1,'阅读'),(0,2,'阅读'),(0,3,'阅读,评论'); insert into user_permission values (0,4,'阅读,评论,发帖'); select *,permission+0 from user_permission; select permission from user_permission where user_id=1; select * from user_permission where permission & 10; SELECT * FROM user_permission WHERE FIND_IN_SET('评论',permission)>0;

6 、NULL 值判断与计算处理

NULL 值判断与处理 #查询奖金百分比不为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NOT NULL; #查询奖金百分比为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct IS NULL; 关于 null 值计算 所有运算符遇到 null 都是 null #计算实际的薪资： basic_salary + salary * 奖金百分比 #函数：IFNULL(表达式,用什么值代替) SELECT eid,basic_salary + performance_salary *(1+ commission_pct) FROM t_salary;#错误的 SELECT eid,basic_salary + performance_salary *(1+ IFNULL(commission_pct,0)) FROM t_salary; <=>安全等于 #查询奖金百分比为空的员工编号 SELECT eid,commission_pct FROM t_salary WHERE commission_pct <=> NULL;

7 、位运算符

>> << & | ~ ^（异或） SELECT 2^3,2&3,2|3,2>>3,2<<3,~3;

二、关联查询

作用：从 2 张或多张表中，取出有关联的数据。

关联查询一共有几种情况：

内连接：INNER JOIN 、CROSS JOIN外连接：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、全外连接（FULL OUTER JOIN）自连接：当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。

说明： （1）连接 n 个表,至少需要 n-1 个连接条件。 例如：连接三个表，至少需要两个连接条件。 （2）当两个关联查询的表如果有字段名字相同，并且要查询中涉及该关联字段，那么需要使用表名前缀加以区分 （3）当如果表名比较长时，可以给表取别名，简化 SQL 语句

1、笛卡尔积

定义：将两（或多）个表的所有行进行组合，连接后的行数为两（或多）个表的乘积数。 在 MySQL 中如下情况会出现笛卡尔积，主要是因为缺少关联条件或者关联条件不准确。

注：外连接必须写关联条件，否则报语法错误。

#查询员工姓名和所在部门名称 SELECT ename,dname FROM t_employee,t_department; SELECT ename,dname FROM t_employee INNER JOIN t_department; SELECT ename,dname FROM t_employee CROSS JOIN t_department; SELECT ename,dname FROM t_employee JOIN t_department;

2、关联条件

表连接的约束条件可以有三种方式：WHERE, ON, USING。

WHERE：适用于所有关联查询ON：只能和 JOIN 一起使用，只能写关联条件。虽然关联条件可以并到 WHERE 中和其他条件一起写，但分开写可读性更好。USING：只能和 JOIN 一起使用，而且要求 两个关联字段在关联表中名称一致，而且只能表示关联字段值相等。 #把关联条件写在 where 后面 SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did; #把关联条件写在 on 后面，只能和 JOIN 一起使用 SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did; 把关联字段写在 using()中，只能和 JOIN 一起使用，而且两个表中的关联字段必须名称相同，而且只能表示= #查询员工姓名与基本工资 SELECT ename,basic_salary FROM t_employee INNER JOIN t_salary USING(eid); n 张表关联，需要 n-1 个关联条件 #查询员工姓名，基本工资，部门名称 SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid; SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

3、内连接（INNER JOIN）

有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。

格式：

隐式：SELECT [cols_list] from 表 1,表 2 where [condition]显式：SELECT [cols_list] from 表 1 INNER JOIN 表 2 ON [关联条件] where [其他筛选条件] SELECT [cols_list] from 表 1 CROSS JOIN 表 2 ON [关联条件] where [其他筛选条件] SELECT [cols_list] from 表 1 JOIN 表 2 ON [关联条件] where [其他筛选条件] #查询员工姓名和所在部门名称 SELECT ename,dname FROM t_employee,t_department WHERE t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee INNER JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee CROSS JOIN t_department ON t_employee.dept_id=t_department.did; SELECT ename,dname FROM t_employee JOIN t_department ON t_employee.dept_id=t_department.did; #查询员工姓名，基本工资，部门名称 SELECT ename,basic_salary,dname FROM t_employee,t_department,t_salary WHERE t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid; SELECT ename,basic_salary,dname FROM t_employee INNER JOIN t_department INNER JOIN t_salary ON t_employee.dept_id=t_department.did AND t_employee.eid=t_salary.eid;

4、外连接（OUTER JOIN）

外连接分为：

左外连接（LEFT OUTER JOIN），简称左连接（LEFT JOIN）。右外连接（RIGHT OUTER JOIN），简称右连接（RIGHT JOIN）。全外连接（FULL OUTER JOIN），简称全连接（FULL JOIN）。

左连接（LEFT JOIN）

右外连接（RIGHT JOIN）

外连接（FULL JOIN）

mysql 不支持 FULL JOIN，但是可以用 left join union right join 代替。

5、自连接

当 table1 和 table2 本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

#查询员工姓名以及领导姓名，仅显示有领导的员工 SELECT emp.ename,mgr.ename FROM t_employee AS emp, t_employee AS mgr WHERE emp.mid = mgr.eid; #查询员工姓名以及领导姓名，仅显示有领导的员工 SELECT emp.ename,mgr.ename FROM t_employee AS emp INNER JOIN t_employee AS mgr ON emp.mid = mgr.eid; #查询所有员工姓名及其领导姓名 SELECT emp.ename,mgr.ename FROM t_employee AS emp LEFT JOIN t_employee AS mgr ON emp.mid = mgr.eid;

---

如果有收获！！！ 希望老铁们来个三连，点赞、收藏、转发 创作不易，别忘点个赞，可以让更多的人看到这篇文章，顺便鼓励我写出更好的博客