MySQL—03—MySQL的查询

一、 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()的用法示例:
见图一和图二:

示例一
向 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 中的内连接(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';

 等值连接:

非等值连接:

6.3SQL99 中的自然连接(NATURAL JOIN)

特殊的等值连接, 去掉了重复列而已;

示例一
修改 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

 

 

 

7.  聚合函数

  • where 不能放在GROUP BY 后面
  • HAVING 是跟GROUP BY 连在一起用的，放在GROUP BY 后面，此时的作用相当于WHERE
  • WHERE 后面的条件中不能有聚合函数，比如SUM(),AVG()等，而HAVING 可以
  • 综上所述, group by, 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 子句 后面可以跟多个逻辑运算符'and',表示多个条件;

• 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多行子查询

 

 

示例
查找各部门收入为部门最低的那些雇员。显示他们的名字，薪水以及部门 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正则表达式的模式及其含义

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

 

posted @ 2019-09-19 12:09  Eric-Shen  阅读(380)  评论(0)  编辑  收藏  举报