MySQL基础篇（运算符、排序分页、多表查询、函数）-2-阿里云开发者社区

MySQL基础篇（运算符、排序分页、多表查询、函数）-1

https://developer.aliyun.com/article/1425370

运算符

算术运算符

运算符	作用
+	加法
-	减法

*	乘法
/ 或 DIV	除法
% 或 MOD	取余

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

比较运算符

通过这些比较运算符，可以判断表中的哪些记录是符合条件的。

结果为真则返回 1
为假则返回 0
不确定则返回 NULL

符号	描述	备注
=	等于

<>, !=	不等于
>	大于
<	小于
<=	小于等于
>=	大于等于

BETWEEN	在两值之间	>=min&&<=max
NOT BETWEEN	不在两值之间
IN	在集合中

NOT IN	不在集合中
<=>	严格比较两个NULL值是否相等	两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0

LIKE	模糊匹配
REGEXP 或 RLIKE	正则式匹配
IS NULL	为空
IS NOT NULL	不为空

常用实例

1、安全等于与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。
mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+
mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+
2、BETWEEN关键字的使用
mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+
3、IN关键字的使用
mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+
4、LIKE关键字的使用
mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+
5、正则匹配的使用
mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

运算符号	作用
NOT 或 !	逻辑非
AND	逻辑与

OR	逻辑或
XOR	逻辑异或

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

运算符号	作用
&	按位与
\|	按位或

^	按位异或
!	取反
<<	左移
>>	右移

异或是相同为0，不同为1
右移也就是把二进制各个位向右边推一位，最后就相当于除以2了
左移同理把二进制各个数位向左边推一位，相当于乘2

1、异或运算
mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+
2、按位右移
mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+
3、位左
mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

练习

# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  salary NOT BETWEEN 5000 
  AND 12000
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT
  last_name,
  department_id 
FROM
  employees 
WHERE
  department_id BETWEEN 20 
  AND 50
# 3.选择公司中没有管理者的员工姓名及job_id
SELECT
  last_name,
  job_id 
FROM
  employees 
WHERE
  manager_id IS NULL
# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT
  last_name,
  salary,
  commission_pct 
FROM
  employees 
WHERE
  commission_pct IS NOT NULL
# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT
  last_name
FROM
  employees 
WHERE
  last_name LIKE '__a%';
# 6.选择姓名中有字母a和k的员工姓名
SELECT
  last_name
FROM
  employees 
WHERE
  last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';
# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT
  first_name
FROM
  employees 
WHERE
  first_name LIKE '%e';
# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT
  last_name,
  job_id 
FROM
  employees 
WHERE
  department_id BETWEEN 80 
  AND 100;
# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT
  last_name,
  salary,
  manager_id
FROM
  employees 
WHERE
  manager_id IN(100,101,110);

排序和分页

排序规则

排序使用ORDER BY子句排序

ASC：升序
DESC：降序

ORDER BY子句在SELECT语句的结尾

语法如下：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

SELECT
  last_name,
  job_id,
  department_id,
  hire_date
FROM
  employees
ORDER BY
  hire_date

多列排序

SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
ORDER BY
  hire_date,
  salary

两列排序策略一致的情况下,如下图：

分页

分页原理：将数据库中结果集一段一段显示出来

使用LIMIT关键字实现分页

LIMIT [位置偏移量,] 行数

偏移量指的是参数指示Mysql从哪一行开始显示，是一个可选参数

如果不指定，默认从第一行开始显示（第一条记录偏移量是0）

第二个行数，表示返回的记录数

实例：

从第5条记录开始后面的3条记录
以下为两种写法
SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
  LIMIT 4,3
SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
  LIMIT 3 OFFSET 4

*分页显式公式：（当前页数-1）每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

约束的好处就是可以减少数据表的网络传输量，也可以提升查询效率

如果我们指定返回结果就是一条，就可以使用LIMIT1，告诉SELECT语句只需要返回一条记录即可，这样的好处就是SELECT不需要扫描完整的表，只需要检索到一条符合条件记录即可返回

练习

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
SELECT
  last_name,
  department_id,
  salary * 12 annual_sal 
FROM
  employees 
ORDER BY
  annual_sal DESC,
  first_name ASC
#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  salary NOT BETWEEN 8000 AND 17000 
ORDER BY
  salary DESC 
  LIMIT 20,20
#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT
  last_name,
  email,
  department_id
FROM
  employees 
WHERE
  email LIKE '%e%'
ORDER BY
  LENGTH(email),
  department_id ASC

多表查询

多个表一起查询，表与表之间是有关系的（一对一、一对多），它们之间一定有关联字段（唯一），这个关联字段可能建立了外键，也可能没有建立外键。

笛卡尔积的错误会在下面条件下产生：

省略多个表的连接条件
连接条件（或者是关联条件）无效
所有表中的所有行互相连接

为了避免它，可以在WHERE加入有效的连接条件

SQL连接标准 SQL92\SQL99

SQL92

形式简单, 语句较长, 可读性较差.

SQL99

语法更为复杂, 可读性强

SQL92中采用(+)代表从表所在位置

SELECT * FROM player, team WHERE player.team_id = team.team_id(+);

在SQL99中:

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id;

SQL92查询中, 把所有需要连接的表放在FROM后, 在WHERE中写明连接的条件.

SQL99更灵活, 不需要一次性把所有需要连接的表放在from后, 而是采用JOIN方式, 每次连接一张表, 可以多次使用join进行连接.

建议多表连接使用SQL99标准, 可读性强.

分类

等值连接 vs 非等值连接

等值连接

每一列记录都有与之对应的值，如下图示

非等值连接

记录部分对应，不在是全部对应，如下图示

自连接 vs 非自连接

自连接

自己连接自己，本质是同一张表，通过取别名的形式对表形成不同的意义

内连接 vs 外连接

INNER JOIN（内连接,或等值连接）：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
**LEFT JOIN（左连接）：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。**没有匹配的行时, 结果表中相应的列为空(NULL)。
RIGHT JOIN（右连接）：两个表在连接过程中除了返回满足连接条件的行以外还**返回右表中不满足条件的行，这种连接称为右外连接。**没有匹配的行时, 结果表中相应的列为空(NULL)。

b6e2503bf7aff6ec52b18cdf2d0213bb_43002656a11e1421bed73ae9f4105654.gif

SELECT
  e.employee_id,
  e.last_name,
  e.department_id,
  d.department_id,
  d.location_id 
FROM
  employees e
  JOIN departments d ON ( e.department_id = d.department_id );

d8e99e0bdfd6dc64aaf89a8134b0c17c_06e8a4ad07aa352d8b5beeb0b977d98c.gif

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  LEFT OUTER JOIN departments d ON ( e.department_id = d.department_id );

31c637337ab0428655c1f7dbc871b555_ce40578141e2b34327aac8fc39f6db3c.gif

SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  RIGHT OUTER JOIN departments d ON ( e.department_id = d.department_id );

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

合并查询结果利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION

SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL

和上面比起来，UNION没有进行去重操作

七种SQL JOINS

-- 七种SQL JOIN
-- 1、内连接
-- A∩B
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  JOIN departments d ON e.department_id = d.department_id;
-- 2、左连接
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id;
-- 3、右连接
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id;
-- 4、左中位置的实现
-- 表达的意思就是员工没有部门的
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  d.department_id IS NULL;
-- 为什么在左连接的基础上加上WHERE d.department_id IS NULL就实现了？
-- 因为左连接是合并指定列的行，返回包含满足条件的和左表所有的行
-- 那么这个图意思就是在左连接查询结果的基础上，舍去同时满足的部分
-- 那同时满足的条件是什么呢？e.department_id = d.department_id
-- 所以我们在加上WHERE进行约束取e.department_id IS NULL或d.department_id IS NULL
-- 最好用d.department_id IS NULL这种方式
-- 5、右中位置的实现
-- 同上
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
  e.department_id IS NULL;
-- 6、满外连接
-- 通过UNION ALL进行连接(左连接和右中位置的实现)
-- UNION ALL效率更高，它不必去重
-- UNION ALL连接的表，显示的字段、类型必须一致
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id IS NULL;
-- 7、右下位置实现
-- 通过UNION ALL进行连接(左中位置实现和右中位置的实现)
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  d.department_id IS NULL UNION ALL
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id IS NULL;

练习

多表查询联系

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT
  e.job_id,
  d.location_id 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id = 90
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT
  e.last_name,
  d.department_name,
  l.location_id,
  l.city 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id
WHERE
  commission_pct IS NOT NULL;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT
  e.last_name,
  e.job_id,
  d.department_name,
  d.department_id 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id 
WHERE
  l.city = 'Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT
  d.department_name,
  l.street_address,
  e.last_name,
  e.job_id,
  e.salary
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id 
WHERE
  d.department_name = 'Executive';
# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp   manager   Mgr
kochhar   101   king      100
SELECT
  emp.last_name employees,
  emp.employee_id "Emp#",
  mgr.last_name manager,
  mgr.employee_id "Mgr#" 
FROM
  employees emp
  LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;
# 7.查询哪些部门没有员工
SELECT
  d.department_id 
FROM
  departments d
  LEFT JOIN employees e ON e.department_id = d.department_id
WHERE
  e.department_id IS NULL;
# 8. 查询哪个城市没有部门
SELECT
  l.city 
FROM
  locations l
  LEFT JOIN departments d ON l.location_id = d.location_id
WHERE
  d.location_id IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT
  e.last_name,
  e.salary,
  e.email,
  e.phone_number 
FROM
  employees e
  JOIN departments d ON e.department_id = d.department_id 
WHERE
  department_name = 'Sales' 
  OR department_name = 'IT'

SQL JOINS练习

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- 1. 所有有门派的人员信息
-- （ A、B两表共有）
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
#1.所有有门派的人员信息
-- （ A、B两表共有）
SELECT
  * 
FROM
  t_dept d
  JOIN t_emp e ON d.id = e.deptId
#2.列出所有用户，并显示其机构信息
-- （A的全集）
SELECT
  e.`name`,
  e.`age`,
  d.`deptName`
FROM
  t_emp e
  JOIN t_dept d ON d.id = e.deptId
#3.列出所有门派
-- （B的全集）
SELECT
 *
FROM
  t_dept
#4.所有不入门派的人员
-- （A的独有）
SELECT
  e.`name`,
  e.`age`
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL
#5.所有没人入的门派
-- （B的独有）
SELECT
  d.deptName,
  d.address
FROM
  t_dept d
  LEFT JOIN t_emp e ON d.id = e.deptId
WHERE
  e.deptId IS NULL
#6.列出所有人员和机构的对照关系
-- (AB全有)
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_dept d
  RIGHT JOIN t_emp e ON d.id = e.deptId UNION ALL
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
#7.列出所有没入派的人员和没人入的门派
-- （A的独有+B的独有）
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL UNION ALL
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_dept d
  LEFT JOIN t_emp e ON d.id = e.deptId
WHERE
  e.deptId IS NULL;

多表查询容易出现的问题

使用外连接连接出现重复记录

在使用连接查询的时候，例如以A表为主表，左连接B表，我们期望的是A表有多少条记录，查询结果就有多少条记录，但是可能会出现这样的结果，就是查询出来的记录总条数，多余A表的记录总条数，并且是查询的结果显示出来时有部分列是重复的，简单的说，就是产生了笛卡尔积。

解决的办法就是采用唯一键去关联做链接查询

别名加不加引号问题

1.mysql中在设置别名时如果没有特殊字符空格可以不要加引号

我这里的别名起的中间有空格，这属于特殊字符

SELECT
  CONCAT(
    last_name,
    ' earns ',
    TRUNCATE ( salary, 0 ),
    ' monthly but he wants ',
  TRUNCATE ( salary * 3, 0 )) 'Dream Salary'
FROM
  employees

2.mysql中设置了引号别名如果没有特殊字符引用时可以直接引用不要加引号

3.如果别名中含有特殊字符那么需要加引号引用时用飘号包裹别名

MySQL基础篇（运算符、排序分页、多表查询、函数）-3

https://developer.aliyun.com/article/1425415?spm=a2c6h.13148508.setting.14.51724f0e3jEOAZ

MySQL基础篇（运算符、排序分页、多表查询、函数）-2

运算符

算术运算符

比较运算符

逻辑运算符

位运算符

运算符优先级

练习

排序和分页

排序规则

多列排序

分页

练习

多表查询

SQL连接标准 SQL92\SQL99

自连接 vs 非自连接

满外连接(FULL OUTER JOIN)

UNION ALL

七种SQL JOINS

练习

多表查询联系

SQL JOINS练习

别名加不加引号问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL基础篇（运算符、排序分页、多表查询、函数）-2

运算符

算术运算符

比较运算符

逻辑运算符

位运算符

运算符优先级

练习

排序和分页

排序规则

多列排序

分页

练习

多表查询

SQL连接标准 SQL92\SQL99

自连接 vs 非自连接

满外连接(FULL OUTER JOIN)

UNION ALL

七种SQL JOINS

练习

多表查询联系

SQL JOINS练习

别名加不加引号问题

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像