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 可以包含重复项

Mysql语言之数据查询

上一篇:SQL——SQL语句总结(3)


下一篇:http代理连接