MySQL—03—MySQL的查询

古城微笑少年丶 2023-10-09 10:29 15阅读 0赞

--------------------

# **一、 ****MySQL的查询** #

## **1.  MySQL** **的基本查询** ##

**1.1MySQL** **的列选择**

SELECT \* | 投影列 FROM 表名

示例
    查询 departments 表中的所有数据
    select * from departments;

**1.2MySQL** **的行选择**

SELECT \* | 投影列 FROM 表名 WHERE 选择条件

示例
    查询 departments 表中部门 ID 为 4 的部门名称与工作地点 ID。
    select department_name,location_id from departments where department_id =4;

**1.3SELECT** **语句中的算术表达式**

\+ ：加法运算

\- ：减法运算

\* ：乘法运算

/ ：除法运算，返回商

% ：求余运算，返回余数

示例一
    修改 employees 表添加 salary。
    alter table employees add column salary float(9,2);
    
    
    示例二
    查询雇员的年薪。
    select employees_id,last_name,email,12*salary from employees;
    
    
    示例三
    计算 employees 表中的员工全年薪水加 100 以后的薪水是多少？
    select employees_id,last_name,email,12*salary+100 from employees;

**1.4MySQL** **中定义空值**

包含空值的算术表达式计算结果为空。

示例
    在 employees 中添加 commission_pct，计算年薪包含佣金。
    alter table employees add column commission_pct float(5,2);
    select 12*salary*commission_pct from employees;

**1.5MySQL** **中的列别名**

SELECT 列名 AS 列别名 FROM 表名 WHERE 条件

**1.5.1****示例**

查询 employees 表将雇员 laser\_name 列名改为 name。

select last\_name as name from employees;

**1.6MySQL** **中的连字符**

MySQL 中并不支持||作为连字符，需要使用 concat 函数。在参数数量上与 oracle 的 concat

函数有区别。

示例
    查询雇员表中的所有数据，将所有数据连接到一起，每列值中通过#分割。
    select concat(employees_id,'#',last_name,'#',email,"#",salary,"#",commission_pct) from employees;

1.7MySQL 中去除重复  
在 SELECT 语句中用 DISTINCT 关键字除去相同的行。

示例
    查询 employees 表，显示唯一的部门 ID。
    select distinct dept_id from employees;

## **2.  ****约束和排序数据** ##

**2.1MySQL** **中的比较条件**

**2.1.1****比较运算符**

• 等于=

• 大于>

• 大于等于>=

• 小于<

• 小于等于<=

• 不等于!=或<>

示例一

查询 employees 表，员工薪水大于等于 3000 的员工的姓名与薪水。

select \* from employees where salary >=3000;

示例二

查询 employees 表，员工薪水不等于 5000 的员工的姓名与薪水。

select \* from employees where salary <>5000;

**2.1.2****模糊查询**

• like

• %表示任意多个任意字符

• \_表示一个任意字符

示例
    查询 employees 中雇员名字第二个字母是 e 的雇员信息。
    select * from employees where last_name like '_e%'

**2.1.3****逻辑运算符**

• and

• or

• not

示例一
    查询 employees 表中雇员薪水是 5000 的并且名字中含有 d 的雇员信息
    select * from employees where salary = 5000 and last_name like '%e%'
    
    示例二
    查询 employees 表中雇员名字中不包含 u 的雇员信息
    select * from employees where last_name not like '%u%'

**2.1.4****范围查询**

• between ... and

• in 表示在一个非连续的范围内

示例一
    查询 employees 表，薪水在 3000-8000 之间的雇员信息
    select * from employees where salary between 3000 and 8000
    
    
    示例二
    查询 employyees 表，找出薪水是 5000,6000,8000 的雇员信息
    select * from employees where salary in(5000,6000,8000)

**2.1.5****空值判断**

• 判断空 is null

• 判断非空 is not null

示例一
    找出 emloyees 表中那些没有佣金的雇员
    select * from employees where commission_pct is null;
    
    
    示例二
    找出 employees 表中那些有佣金的雇员
    select * from employees where commission_pct is not null;2.2使用 ORDER BY 排序

**2.1.6排序查询**

• 用 ORDER BY 子句排序

• ASC: 升序排序，默认

• DESC: 降序排序

示例一
    查询 employees 表中的所有雇员，薪水按升序排序。
    select * from employees order by salary
    
    示例二
    查询 employees 表中的所有雇员，雇员名字按降序排序。
    select * from employees order by last_name desc

## **3.  MySQL** **中常见的单行函数** ##

**3.1****大小写控制函数**

*  LOWER(str) 转换大小写混合的字符串为小写字符串
 *  UPPER(str) 转换大小写混合的字符串为大写字符串。

**3.2****字符处理**

*  CONCAT(str1,str2,...) 将 str1、str2 等字符串连接起来
 *  SUBSTR(str,pos,len) 从 str 的第 pos 位（范围：1~str.length）开始，截取长度为 len的字符串
 *  LENGTH(str) 获取 str 的长度
 *  INSTR(str,substr) 获取 substr 在 str 中的位置
 *  LPAD(str,len,padstr)/RPAD(str,len,padstr): lpad函数从左边对字符串使用指定的字符进行填充。
 *  TRIM(str) 从 str 中删除开头和结尾的空格（不会处理字符串中间含有的空格）
 *  LTRIM(str) 从 str 中删除左侧开头的空格
 *  RTRIM(str) 从 str 中删除右侧结尾的空格
 *  REPLACE(str,from\_str,to\_str) 将 str 中的 from\_str 替换为 to\_str（会替换掉所有符合from\_str 的字符串）

**3.3****数字函数**

*  ROUND(arg1,arg2)：四舍五入指定小数的值。
 *  ROUND(arg1)：四舍五入保留整数。
 *  TRUNC(arg1,arg2)：截断指定小数的值，不做四舍五入处理。
 *  MOD(arg1,arg2)：取余。**3.4****日期函数**
 *  SYSDATE() 或者 NOW() 返回当前系统时间，格式为 YYYY-MM-DD hh-mm-ss
 *  CURDATE() 返回系统当前日期，不返回时间
 *  CURTIME() 返回当前系统中的时间，不返回日期
 *  DAYOFMONTH(date) 计算日期 d 是本月的第几天
 *  DAYOFWEEK(date) 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
 *  DAYOFYEAR(date) 返回指定年份的天数
 *  DAYNAME(date) 返回 date 日期是星期几
 *  LAST\_DAY(date) 返回 date 日期当月的最后一天

**3.4****转换函数**

DATE\_FORMAT(date,format) 将日期转换成字符串（类似 oracle 中的 to\_char()）

STR\_TO\_DATE(str,format) 将字符串转换成日期（类似 oracle 中的 to\_date()）

data_format()的用法示例:
    见图一和图二:

![1734761-20190918201836238-1225589513.png][]

![1734761-20190918201922193-851339908.png][]

示例一
    向 employees 表中添加 hire_date 列 类型为 date 类型
    alter table employees add column hire_date date
    
    
    示例二
    向 employees 表中添加一条数据，名字：King ，email：king@sxt.cn，部门 ID：1，薪水：9000，入职时间：2018 年 5 月 1 日，佣金：0.6
    insert into employees values(default,'King','king@sxt.cn',1,9000,0.6,STR_TO_DATE('2018年 5 月 1 日','%Y 年%m 月%d 日'))
    
    
    示例三
    查询 employees 表中雇员名字为 King 的雇员的入职日期，要求显示格式为 yyyy 年 MM月 dd 日。
    select DATE_FORMAT(hire_date,'%Y 年%m 月%d 日') from employees where last_name ='King'

**3.5****通用函数**

1.  IFNULL(expr1,expr2) 判断 expr1 是否为 null，如果为 null，则用 expr2 来代替 null（类似 oracle 的 NVL()函数）
2.  NULLIF(expr1,expr2) 判断 expr1 和 expr2 是否相等，如果相等则返回 null，如果不相等则返回 expr1
3.  IF(expr1,expr2,expr3) 判断 expr1 是否为真（是否不为 null），如果为真，则使用 expr2替代 expr1；如果为假，则使用 expr3 替代 expr1（类似 oracle 的 NVL2()函数,  也类似于Java的三元表达式;?）
4.  COALESCE(value,...) 判断 value 的值是否为 null，如果不为 null，则返回 value；如果为 null，则判断下一个 value 是否为 null……直至出现不为 null 的 value 并返回或者返回最后一个为 null 的 value
5.  CASE WHEN THEN ELSE END 条件函数

## **4.  ****多表连接查询** ##

**4.1****等值连接**

示例
    查询雇员 King 所在的部门名称
    select d.department_name from employees e,departments d where e.dept_id =d.department_id and e.last_name = 'King'

**4.2****非等值连接**

示例一
    创建 sal_level 表，包含 lowest_sal，highest_sal，level。
    create table sal_level(lowest_sal int,highest_sal int ,level varchar(30))
    
    示例二
    插入数据
    1000 2999 A
    2000 4999 B
    5000 7999 C
    8000 12000 D
    insert into sal_level values(8000,12000,'D)
    
    示例三
    查询所有雇员的薪水级别。
    select e.last_name,s.level from employees e ,sal_level s where e.salary between s.lowest_sal
    and highest_sal;

**4.3****自连接**

示例一
    修改 employees 表，添加 manager_id 列
    ALTER table employees add COLUMN manager_id int
    
    示例二
    修改数据 Oldlu 是 kevin 与 King 的经理
    Taylor 是 Fox 的经理
    
    示例三
    查询每个雇员的经理的名字以及雇员的名字。
    select emp.last_name,man.last_name from employees emp ,employees man where emp.manager_id = man.employees_id

## **5.  ****外连接****(OUTER JOIN)**  ##

5.1左外连接(LEFT OUTER JOIN)

将右表中的所有数据显示出来,  包括不满足条件的;

示例一
    向 employees 表中添加一条数据，名字：Lee，email：lee@sxt.cn，入职时间为今天。他没有薪水，没有经理，没有佣金。
    insert into employees(last_name,email,hire_date) values('Lee','lee@sxt.cn',SYSDATE())
    
    示例二
    查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
    select e.last_name,d.department_name from employees e LEFT OUTER JOIN departments d on e.dept_id = d.department_id

5.2右外连接(RIGHT OUTER JOIN)

将左表中的所有数据显示出来,  包括不满足条件的;

示例一
    向 departments 表中添加一条数据，部门名称为 Testing，工作地点 ID 为 5。
    insert into departments values(default,'Testing',5)
    
    示例二
    查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
    select e.last_name,d.department_name from employees e right OUTER join departments d on e.dept_id = d.department_id;

**5.3****全外连接**

注意：MySQL 中不支持 FULL OUTER JOIN 连接; 但可以使用 union 实现全完连接。

**UNION**

可以将两个查询结果集合并，返回的行都是唯一的，如同对整个结果集合使用了DISTINCT。

**UNION ALL**

只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

**语法结构**

( SELECT 投 影 列 FROM 表 名 LEFT OUTER JOIN 表 名 ON 连 接 条 件 ) UNION ( SELECT 投影列   FROM 表名 RIGHT OUTER JOIN 表名 ON 连接条件 )

示例
    查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门以及没有部门的雇员。
    (select e.last_name,d.department_name from employees e LEFT OUTER JOIN departmentsd on e.dept_id = d.department_id)
     UNION 
    (select e1.last_name,d1.department_name from employees e1 RIGHT OUTER JOIN departments d1 on d1.department_id = e1.dept_id)

## **6.  SQL99** **标准中的查询** ##

**6.1SQL99** **中的交叉连接****(CROSS JOIN)**

示例
    使用交叉连接查询 employees 表与 departments 表
    select * from employees cross join departments

**6.2SQL99** **中的自然连接****(NATURAL JOIN)**

**![特殊的等值连接, 去掉了重复列而已;][1734761-20190919111324559-1585134905.png]**

示例一
    修改 employees 表中的 dept_id 列将该列的名称修改为 department_id
    alter table employees change column dept_id department_id int
    
    示例二
    使用自然连接查询所有有部门的雇员的名字以及部门名称。
    select e.last_name,d.department_name from employees e natural join departments d

**6.3SQL99** **中的内连接****(INNER JOIN)**

内连接分为等值连接和非等值连接;

示例
    查询雇员名字为 OldLu 的雇员 ID，薪水与部门名称。
    select e.employees_id,e.salary,d.department_name from employees e inner JOIN departments d on e.department_id = d.department_id where e.last_name = 'Oldlu';

等值连接:

![1734761-20190919111137839-1049153963.png][]

非等值连接:

## **7.  ****聚合函数** ##

*  where 不能放在GROUP BY 后面
 *  HAVING 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE
 *  WHERE 后面的条件中不能有聚合函数，比如SUM(),AVG()等，而HAVING 可以

**7.1AVG(arg)****函数**

对分组数据做平均值运算。

arg:参数类型只能是数字类型。

示例:
    求雇员的平均薪水
    select avg(salary) from employess;

**7.2SUM(arg)****函数**

对分组数据求和。

arg:参数类型只能是数字类型。

**7.3MIN(arg)****函数**

求分组中最小数据。

arg:参数类型可以是字符、数字、日期。

**7.4MAX(arg)****函数**

求分组中最大数据。

arg:参数类型可以是字符、数字、日期。

**7.5COUNT** **函数**

返回一个表中的行数。

COUNT 函数有三种格式：

• COUNT(\*)

• COUNT(expr)

• COUNT(DISTINCT expr)

## **8.  ****数据组****(GROUP BY)**  ##

**8.1****创建数据组**

group by 将满足条件的分成一行来显示;order by 是用来以某一个列为基准,进行排序的;

示例:
    计算每个部门的平均薪水
    select avg(e.salary) from employees e group by e.department_id

**8.2****约束分组结果****(HAVING)**

示例
    显示那些最高薪水大于 5000 的部门的部门号和最高薪水。
    select e.department_id,max(e.salary) from employees e group by e.department_id HAVING MAX(e.salary) > 5000

## **9.  ****子查询** ##

可以将子查询放在许多的 SQL 子句中，包括：

• WHERE 子句

• HAVING 子句

• FROM 子句

**9.1****使用子查询的原则**

• 子查询放在圆括号中。

• 将子查询放在比较条件的右边。

• 在单行子查询中用单行运算符，在多行子查询中用多行运算符。

示例
    谁的薪水比 Oldlu 高
    select em.last_name,em.salary from employees em where em.salary > (select e.salary from employees e where e.last_name = 'Oldlu')

**9.2****单行子查询**

示例
    查询 Oldlu 的同事，但是不包含他自己。
    select empl.last_name from employees empl where empl.department_id = (select e.department_id from employees e where e.last_name = 'Oldlu') and empl.last_name <> 'Oldlu'

**9.3****多行子查询**

**![1734761-20190919120829297-1199104629.png][]**

示例
    查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 ID。
    select em.last_name,em.salary,em.department_id from employees em where em.salary in(select min(e.salary) from employees e group by e.department_id)

## **10.  MySQL** **中的正则表达式** ##

*  MySQL 中允许使用正则表达式定义字符串的搜索条件，性能要高于 like。
 *  MySQL 中的正则表达式可以对整数类型或者字符类型检索。
 *  使用 REGEXP 关键字表示正则匹配。
 *  默认忽略大小写，如果要区分大小写，使用 BINARY 关键字

**10.1****正则表达式的模式及其含义**

**![1734761-20190920135333151-970281380.png][]**

**10.2“^”****符号**

^在正则表达式中表示开始

**10.2.1** **语法**

查询以 x 开头的数据(忽略大小写)

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '^x';

示例
    查询雇员表中名字是以 k 开头的雇员名字与薪水。
    select last_name,salary from employees where last_name REGEXP binary '^K'

**10.3“$”****符号**

**10.3.1** **语法**

查询以 x 结尾的数据(忽略大小写)

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 'x$';

示例
    查询雇员表中名字是以 n 结尾的雇员名字与薪水。
    select last_name,salary from employees where last_name REGEXP binary 'n$'

**10.4“.”****符号**

**10.4.1** **语法**

英文的点，它匹配任何一个字符，包括回车、换行等。

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 'x.';

示例
    查询雇员表中名字含有 o 的雇员的姓名与薪水。
    select last_name,salary from employees where last_name REGEXP 'o.'

**10.5“\*”****符号**

**语法**“\*”：星号匹配 0 个或多个字符，在它之前必须有内容。

**10.6“****+****”****符号**

**语法**"+"：加号匹配 1 个或多个字符，在它之前也必须有内容。

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 'x+';-匹配大于 1 个的任意字符

**10.7“?”****符号**

**语法**“?”：问号匹配 0 次或 1 次。

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 'x?';-匹配 0 个或 1 个字符

**10.8“****|****”****符号**

**语法**“|”：表示或者含义

SELECT 列名 FROM 表名 WHERE 列名 REGEXP 'abc|bcd';-匹配包含 abc 或 bcd

示例
    查询雇员表中名字含有 ke 或者 lu 的雇员的名字与薪水。
    select last_name,salary from employees where last_name REGEXP 'ke|lu'

**10.9“\[a-z\]”**

**语法**“\[a-z\]”：字符范围

“^\[....\]”：以什么字符开头的

“\[^....\]”：匹配不包含在\[\]的字符

SELECT 列名 FROM 表名 WHERE 列名 REGEXP '\[a-z\]';    -匹配内容包含 a-z 范围的数据

示例一
    查询雇员表中名字包含 x、y、z 字符的雇员的名字和薪水。
    select last_name,salary from employees where last_name REGEXP '[x-z]'
    
    示例二
    查询雇员名字是 t、f 开头的雇员名字与薪水。
    select last_name,salary from employees where last_name REGEXP '^[t|f]'
    
    示例三
    查询雇员的名字与薪水，不包含 oldlu。
    select last_name,salary from employees where last_name REGEXP '[^oldlu]'

**10.10 “\{n\}”**

**语法**“\{n\}”：固定次数。

select \* from student where name REGEXP 's\{2\}';--匹配以 s 连续出现 2 次的所有数据

示例一
    查询雇员名字含有连续两个 e 的雇员的姓名与薪水
    select last_name,salary from employees where last_name REGEXP 'e{2}'
    
    示例二
    查询名字中含有两个 o 的雇员的名字与薪水。
    select last_name,salary from employees where last_name REGEXP 'o.{2}'
    
    注意:不出现.表示要连续出现;  出现.表示出现n次即可,不需连续;

**10.11 “****\{n,m\}****”**

**语法**“\{n,m\}”：范围次数。

select \* from student where name REGEXP '^s\{2,5\}';--匹配以 s 开头且重复 2 到 5 次的所有数据

示例
    查询雇员名字中包含 1 个或者两个 o 的雇员姓名与薪水。
    select last_name,salary from employees where last_name REGEXP 'o.{1,2}'

# **二、****MySQL** **分页查询** #

MySQL 分页查询原则

1.  在 MySQL 数据库中使用 LIMIT 子句进行分页查询。
2.  MySQL 分页中开始位置为 0。
3.  分页子句在查询语句的最后侧。

## **1.  LIMIT** **子句** ##

**1.1****语法格式**

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 开始位置，查询数量。

示例
    查询雇员表中所有数据按 id 排序，实现分页查询，每次返回两条结果。
    select * from employees order by employees_id limit 0,2

## **2.  LIMIT OFFSET** **子句** ##

**2.1****语法格式**

SELECT 投影列 FROM 表名 WHERE 条件 ORDER BY LIMIT 查询数量 OFFSET 开始位置。

注意: 就是让查询数量和开始位置换了个位,  和limit没啥区别;?

示例
    查询雇员表中所有数据按 id 排序，使用 LIMIT OFFSET 实现分页查询，每次返回两条结果。
    select * from employees order by employees_id limit 2 offset 4

转载于:https://www.cnblogs.com/EricShen/p/11541047.html

[1734761-20190918201836238-1225589513.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190918201836238-1225589513.png
[1734761-20190918201922193-851339908.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190918201922193-851339908.png
[1734761-20190919111324559-1585134905.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190919111324559-1585134905.png
[1734761-20190919111137839-1049153963.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190919111137839-1049153963.png
[1734761-20190919120829297-1199104629.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190919120829297-1199104629.png
[1734761-20190920135333151-970281380.png]: https://img2018.cnblogs.com/blog/1734761/201909/1734761-20190920135333151-970281380.png