Mysql语言之数据查询

一、基础查询

#进阶1：基础查询
/*
语法：
select 查询列表 from 表名;

特点：
1. 查询列表可以使：表中的字段，常量值，表达式，函数
2. 查询的结果是一个虚拟的表格
*/

# 打开某个库： use 表名
use myemployees;

# 1. 查询表中的单个字段： select 字段 from 表名
select last_name from employees;

# 2. 查询表中的多个字段： select 字段,字段... from 表名
select last_name,salary,email from employees;

# 3. 查询表中的所有字段：select 所有字段 from 表名; 或 select * from 表名
select * from employees;

# 着重号	''
'name' 表名是一个字段，不是关键字，防止冲突时候使用

# 4. 查询常量值： select 常量值
select 100;
select 'john';

# 5. 查询表达式
select 100*98;

# 6. 查询函数
select VERSION();

# 7. 起别名： 一、使用as 	二、使用空格
# 注：如果起的别名中有空格或#等特殊字符，别名应加上""，或'' 但是最好是"" 
/*
好处：
1. 便于理解
2. 如果要查询的字段有重名的情况，使用别名可以区分开来
*/
select 100%98 as 结果;
select last_name as 姓,first_name as 名 from employees;
select last_name 姓,first_name 名 from employees;

# 8. 去重
select distinct department_id from employees;

# 9. +号的作用
/*
java 中的+号
1. 运算符：两个操作数都是数值型
2. 连接符：有一个操作数是字符串
mysql中的+号
运算符

select 100+90; 两个操作数是数值型，就做加法运算
select '123'+90; 其中一方为字符型，试图将字符型数值转换为数值型
转换成功，继续加法运算
转换失败(例：'john'+90) 字符型数值转换成0

select null+10; 只要一方为null，结果必然是null
*/

# 10. 字符串的拼接
select concat('a','b','c') as 结果;

# 11. ifnull函数
判断某字段或表达式(first)是否为null，如果为null返回指定值(second)，否则返回原本的值
select ifnull(first , second) from 表名;

# 12. isnull函数
判断某字段是否为null ，是返回1，否则返回0

二、条件查询

/*
语法：
select 查询列表 from 表名 where 筛选条件;
分类：
1. 按条件表达式筛选：条件运算符：> < = (!= ,<> 都是不等于的意思) >= <=
2. 按逻辑表达式筛选：逻辑运算符：&& || ! and or not
3. 模糊查询：
	like
	between and 
	in 
	is null

like:
1. 通常和通配符搭配使用
通配符
	% : 任意多个字符，包含0个字符
	_ : 任意单个字符
	\_ : 表示字符_
	like "_$_%" excape '$'; 表示'$'代表转义字符，是\的意思
*/

is null :仅仅可以判断NULL值，可读性较高
<=> : 仅可以判断NULL值，又可以判断普通的数值，可读性较低

三、排序查询

/*
语法：
select 查询列表 from 表 [where 筛选条件] order by 排序列表 [asc|desc(降序)];
特点：
1. asc代表升序，desc代表降序，如果不写，默认升序
*/

select * from employee order by salary asc,employee_id desc; (先按照salary升序，再按照employee_id 降序)

常见函数

/*
概念：类似于java中的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1. 隐藏了实现细节 2.提高代码的重用性
调用： select 函数名() from 表名;
分类：
	1. 单行函数 如：concat 、 length 、 ifnull等
	2. 分组函数
		功能：做统计使用，又称为统计函数、聚合函数、组函数
*/

一、字符函数

# 1. length：
select length('john'); # 4
select length('张三丰hahaha') # 15(在utp8中，一个字母占一个字节，一个汉字占3个字节)

# 2. concat拼接字符串

# 3. upper lower
select upper('john'); # JOHN
select lower('JOHN'); # john
# 例：姓变大写，名小写，拼接
select concat(upper(last_name),lower(first_name)) 姓名 from employees;

# 4. substr \ substring 注：索引从1开始
# 截取从指定索引处后面所有字符
select substr('李莫愁爱上了陆展元',7) out_put;
# 街区从指定索引处指定字符长度的字符
select substr('李莫愁爱上了陆展元',1,3) out_put;
# 例：姓名首字符大写，其他小写，用_拼接，显示
select concat(upper(substr(last_name,1,1)),"_",lower(substr(last_name,2))) out_put from employees;

# 5. instr 返回子串第一次出现的索引，找不到返回0
select instr('杨不悔爱上了殷六侠','殷六侠') as out_put;

# 6. trim 
select trim('		张翠山		') as out_put; # 去掉了前后空格
select trim('a' from 'aaaaaaa张aaaaaa翠aaaaaa山aaaaaaa') as out_put; # 去掉前后指定字符a

# 7. lpad 用指定字符实现左填充指定长度，如果给定字符串小于给定长度，会进行从左往右的截断
select lpad('殷素素',10,'*') as out_put;
# 8. rpad 用指定字符实现右填充指定长度，如果给定字符串小于给定长度，会进行从左往右的截断

# 9. replace 替换
select replace("周芷若周芷若张无忌爱上了周芷若","周芷若","赵敏") as out_put; # 赵敏赵敏张无忌爱上了赵敏

二、数学函数

# 1. round 四舍五入
select round(1.65); # 2
select round(-1.45); # -1
select round(-1.65); # -2

# 2. ceil 向上取整，返回>= 该参数的最小整数

# 3. floor 向下取整

# 4. truncate 截断

select truncate(1,699999,1); # 1.6
# 5. mod 取余 mod(a,b) = a - ( a / b ) * b
select mod(10,3); # 1

三、日期函数

# 1. now 返回当前系统日期+时间
select now();

# 2. curdate 返回当前系统日期，不包含时间
select curdate();

# 3. curtime 返回当前时间，不包含日期
select curtime();

# 获取指定的部分，年、月、日、小时、分钟、秒
select year(now()) 年;
select year('1998-1-1') 年; # 1998

# 4. str_to_date : 将日期格式的字符转换为指定格式的日期
str_to_date('9-13-1999','%m-%d-%Y') # 1999-13-9

# 5. date_format 将日期转换为字符
select date_format(now(),'%y年%m月%d日') as out_put;

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01,02...)
%c	月份（1,2,...)
%d	日（01，02）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00,01）
%s	秒（00,01）

四、其他函数

select version(); # 查看版本号
select database(); # 查看当前数据库
select user(); # 当前用户

五、流程控制函数

# 1. if函数：if-else效果
select if(10>5,'大','小') # 判断第一个参数是真是假，真返回第二个参数，否则，返回第三个参数

# 2. case函数
# 使用一：switch-case效果
#MySQL中：
# case 要判断的字段或表达式
# when 常量1 then 要显示的值1或语句1
# when 常量2 then 要显示的值2或语句2
# ...
# else 要显示的值n或语句n
# end

# 使用二：类似于多重if
mysql中：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

五、分组函数

分类：

1. sum 求和
2. avg 平均值
3. max 最大值
4. min
5. count 计算个数

特点：

1. sum、avg 一般用于处理数值型

   max、min、count 可以处理任何类型

2. 以上分组函数都忽略null值

3. 可以和distinct搭配实现去重的运算

4. count 函数的单独介绍

   select count(salary) from employees;
   select count(*) from employees; # 统计总行数
   select count(1) from employees; # 统计总行数 count(参数) 参数可以为任意值
   # 效率：
   # Myisam存储引擎下，count(*) 效率高
   # Innodb存储引擎下，count(*) 和 count(1)的效率差不多，比count(字段)要高一些
   

   一般使用count(*) 用于统计行数

5. 和分组函数一同查询的字段要求是group by 后的字段

五、分组查询：

语法：

select 分组函数 ，列（要求出现在group by 的后面） from 表 [where 筛选条件] group by 分组的列表 [order by 子句]

注意：

查询列表必须特殊，要求是分组函数和group by 后出现的字段

特点

一、分组查询中的筛选条件分为两类

	数据源	位置	关键字
分组前的筛选	原始表	group by 子句的前边	where
分组后的筛选	分组后的结果集	group by 子句的后边	having

1. 分组函数条件肯定是放在having子句中
2. 能用分组前筛选的，就优先考虑使用分组前筛选

二、group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求，表达式或函数（用的较少）

三、 也可以添加排序（排序放在整个分组查询的最后）

总结：

# 例1：查询每个工种的最高工资
select max(salary),job_id from employees group by job_id;

六、连接查询

又称为：多表查询，当查询的字段来自于多个表时，就会用到多表查询

笛卡尔乘积现象：表1有m行，表2有n行，结果有m*n行

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：

• 按年代分类：
  • SQL92标准 ： 仅仅支持内连接 【也支持一些外连接（Oracle，sqlserver支持，但是不稳定）mysql不支持】
  • SQL99标准（推荐）：支持所有内连接 + 外连接（左外链接，右外连接） + 交叉连接
• 按功能分类
  • *内连接 inner
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接
    • *左外链接 left[outer]
    • *右外连接 right[outer]
    • 全外链接 full[outer]
  • 交叉连接 cross join

/*
SQL92标准
*/
# 1. 等值连接
# 语法 select 查询列表 from 表1 别名1,表2 别名2 where 表1.key = 表2.key [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]
# 2.为表起别名
#	好处：
#	提高语句的简洁度
#	区分多个重名的字段
#	注： 如果为表起了别名，则查询的字段就不能使用原来的表名去限定

select e.last_name , e.job_id , j.job_title 
from employees as e,jobs j # from employees e , jobs
where e.'job_id' = j.'job_id';

# 3.两个表的顺序可以调换
select e.last_name , e.job_id , j.job_title 
from jobs j , employees e # from employees e , jobs
where e.'job_id' = j.'job_id';

# 4. 可以加筛选
# 5. 可以加分组
# 6. 可以加排序
# 7. 可以实现三表连接

# 2. 非等值连接
# 语法 select 查询列表 from 表1 别名1,表2 别名2 where 非等值得连接条件 [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]

# 3. 自连接
# 语法 select 查询列表 from 表1 别名1,表2 别名2 where 等值得连接条件 [and 筛选条件] [group by 分组字段] [having 分组后的筛选] [order by 排序字段]

# sql 99 语法
/*
语法：
	select 查询列表
	from 表1 别名 [连接类型]
	join 表2 别名 
	on 连接条件
	[
	where 筛选条件
	group by 分组
	having 筛选条件
	order by 排序列表
	limit 子句
	];
*/

# 1. 内连接
/*
语法：
	select 查询列表
	from 表1 别名
	[inner] join 表2 别名
	on 连接条件;
	where 筛选条件
	group by 分组列表
	having 分组后的筛选
	order by 排序列表
	limit 子句;
特点：
	表的顺序可以调换
	内连接的结果 = 多表的交集
	n表连接，至少需要n-1个连接条件

分类：
	等值
	非等值
	自连接
*/
# 1. 等值连接
# 例：查询员工名，部门名
select last_name , department_name
from employees e
inner join departments d
on e.'department_id' = d.'department_id';

# 例：查询名字中包含e的员工名和工种名
select last_name,job_title
from employees e
inner join jobs j
on e.'job_id' = j.'job_id'
where e.'last_name' like '%e%';

# 2. 非等值连接
# 例：查询工资级别的个数>20的个数，并按工资级别降序
select count(*) , grade_level
from employees e
join job_grades g
on e.'salary' between g.'lowest_sal' and g.'highest_sal'
group by grade_level
having count(*)>20
order by grade_level desc;

# 3. 自连接

/*
二、 外连接
应用场景：用于查询一个表中有，另一个表中没有的纪录

语法：
	select 查询列表
	from 表1 别名
	left|right|full [outer] join 表2 别名
	on 连接条件;
	where 筛选条件
	group by 分组列表
	having 分组后的筛选
	order by 排序列表
	limit 子句;

特点：
1. 查询结果 = 主表中的所有行
	如果从表中有和他匹配的，则显示匹配的值
	如果从表中没有和他匹配的，显示null
	外链接查询结果=内连接结果+主表中有而从表中没有的记录
2. 左外链接，left join 左边的是主表
   右外连接，right join 右边的是主表
   全外链接，两边都是主表
3. 左外和右外交换两个表的顺序，可以实现同样的效果
4. 全外连接 = 内连接的结果+表1中有但是表2没有的+表2中有但表1没有的
5. 一般用于查询除了交集部分的剩余的不匹配的行
*/


# 例：查询男朋友，不在男神表中的女神名
select b.name,bo.*
from beauty b
left outer join boys bo
on b.'boyfriend_id' = bo.'id';

# 全外链接 不支持 得到交集部分
use girls
select b.*,bo.*
from beauty b
full outer join boys bo
on b.'boyfriend_id' = bo.'boyfriend_id';

# 交叉连接 —— 笛卡尔乘积
select b.*,bo.*
from beauty b
cross join boys bo;

七、子查询

含义：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询

外面的语句可以为 insert , update , delete , select 一般 select 作为外部语句较多，外部如果为select语句，则此语句称为外查询或主查询

分类：

• 按子查询出现的位置
  • select 后面
    • 仅仅支持标量自查询
  • from 后面
    • 支持表子查询
  • △ where 或 having 后面
    • 标量子查询
    • 列子查询
    • 行子查询
  • exists 后面（相关子查询）
    • 表子查询
• 按结果集的行列数不同
  • 标量子查询（结果集只有一行一列）
  • 列子查询（结果集一列多行）
  • 行子查询（结果集一行多列）
  • 表子查询（结果集一般为多行多列，行列数任意）

一、 where 或 having后面：

1. 标量子查询（单行子查询）
2. 列子查询（多行子查询）
3. 行子查询（多列多行）

特点：

1. 子查询都会放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询，一般搭配着单行操作符使用（> < >= <= = <>)
4. 列子查询，一般搭配多行操作符使用（IN , ANY\SOME , ALL)

# 1. 标量子查询
# 例1：查询谁的工资大于Abel
# 1. 查询Abel工资
select salary
from employees
where last_name = 'Abel'
# 2. 查询员工的信息，满足salary>问题1的结果
select *
from employees
where salary>{
	select salary
	from employees
	where last_name = 'Abel'
};

# 2. 列子查询
# 多行比较操作符：
#	in / not in 	于列表中的任意一个
#	any | some 		和子查询返回的某一个值比较
#	all				和子查询返回的所有值比较

# any 和 all 的区别
# 比如：a>any(10,20,30) 大于10，20,30 中任意一个就可以，也就是求最小值
# 比如：a>all(10,20,30) 大于10，20，30 中任意一个才可以，也就是求最大值

# 3. 行子查询（结果集一行(或多行)多列）
select *
from employees
where (employee_id,salary) = (
	select min(employee_id),max(salary)
	from employees
)

二、select 后面

里边只支持标量子查询

三、from后面

# 例：查询每个部门的平均工资的工资等级
# 1. 查询每个部门的平均工资
select avg(salary) , department_id
from employees
group by department_id

select * from job_grades;

# 2. 连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal
select ag_dep.*,g.'grade_level'
from (
	select avg(salary) , department_id
	from employees
	group by department_id
) ag_dep # 必须起别名，否则找不到表
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;

四、exists后面（相关子查询）

语法：

exists(完整的查询语句)

结果：1 或 0

# 例：查询有员工的部门名
# in
select department_name
from departments d
where d.'department_id' in(
	select department_id
	from employees
)

# exists
select department_name
from departments
where exists(
	select *
	from employees
	where d.'department_id' = e.'department_id'
);

八、分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求

语法：

select 查询列表

from 表

[

join type join 表2

on 连接条件

where 筛选条件

group by 分组字段

having 分组后的筛选

order by 排序的字段

]

limit [offset(要显示条目的起始索引，从0开始),] size(条目数);

总结：

1. limit语句放在查询语句的最后，执行顺序也是最后

2. 公式：

   要显示的页数page ， 每页的条目数size

   select 查询列表

   from 表

   limit (page-1)*size ， size;

# 例1：查询前五条员工的信息
select * from employees limit 0,5;
select * from employees limit 5;
# 例2：查询第11——第25条
select * from employees limit 10,15;
# 例3：有奖金的员工信息，并且工资较高的前十名显示
select * from employees where commission_pct is not null order by salary desc limit 10;

九、union联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果

语法：

查询语句1

union

查询语句2

union

...

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时。

特点：

1. 要求多条查询语句的查询列数是一致的
2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
3. union关键字默认去重，如果使用 union all 可以包含重复项