一、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)
SELECT * FROM t_employee WHERE eid % 2 = 0;
SELECT * FROM t_employee WHERE eid MOD 2 = 0;
SELECT eid,basic_salary/12 AS "日薪" FROM t_salary;
SELECT eid,basic_salary DIV 12 AS "日薪" FROM t_salary;
SELECT eid+ename FROM t_employee;
SELECT eid+birthday FROM t_employee;
SELECT CONCAT(eid,":",ename) AS result FROM t_employee;
2 、比较运算符
=,>, <,>=, <=, !=(不等于<>),<=>(安全等于)
SELECT eid,basic_salary FROM t_salary WHERE 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;
SELECT eid,commission_pct FROM t_salary WHERE 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 IS NULL;
SELECT eid,commission_pct FROM t_salary WHERE 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
SELECT * FROM t_employee WHERE gender='男' AND birthday<'1990-01-01';
SELECT * FROM t_employee WHERE job_id =1 OR job_id = 2;
SELECT eid,basic_salary FROM t_salary WHERE basic_salary >=9000 AND basic_salary<=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(...)
SELECT eid,basic_salary FROM t_salary WHERE basic_salary BETWEEN 9000 AND 12000;
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 字段即可:
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
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 一起使用,而且要求 两个关联字段在关联表中名称一致,而且只能表示关联字段值相等。
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;
把关联字段写在 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;
如果有收获!!! 希望老铁们来个三连,点赞、收藏、转发
创作不易,别忘点个赞,可以让更多的人看到这篇文章,顺便鼓励我写出更好的博客
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)