一、基础查询
#进阶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
五、分组函数
分类:
sum 求和
avg 平均值
max 最大值
min
count 计算个数
特点:
sum、avg 一般用于处理数值型
max、min、count 可以处理任何类型
以上分组函数都忽略null值
可以和distinct搭配实现去重的运算
count 函数的单独介绍
select count(salary) from employees;
select count(*) from employees; # 统计总行数
select count(1) from employees; # 统计总行数 count(参数) 参数可以为任意值
# 效率:
# Myisam存储引擎下,count(*) 效率高
# Innodb存储引擎下,count(*) 和 count(1)的效率差不多,比count(字段)要高一些
一般使用count(*) 用于统计行数
和分组函数一同查询的字段要求是group by 后的字段
五、分组查询:
语法:
select 分组函数 ,列(要求出现在group by 的后面) from 表 [where 筛选条件] group by 分组的列表 [order by 子句]
注意:
查询列表必须特殊,要求是分组函数和group by 后出现的字段
特点
一、分组查询中的筛选条件分为两类
数据源
位置
关键字
分组前的筛选
原始表
group by 子句的前边
where
分组后的筛选
分组后的结果集
group by 子句的后边
having
分组函数条件肯定是放在having子句中
能用分组前筛选的,就优先考虑使用分组前筛选
二、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后面:
标量子查询(单行子查询)
列子查询(多行子查询)
行子查询(多列多行)
特点:
子查询都会放在小括号内
子查询一般放在条件的右侧
标量子查询,一般搭配着单行操作符使用(> < >= <= = <>)
列子查询,一般搭配多行操作符使用(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(条目数);
总结:
limit语句放在查询语句的最后,执行顺序也是最后
公式:
要显示的页数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
...
应用场景:要查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时。
特点:
要求多条查询语句的查询列数是一致的
要求多条查询语句的查询的每一列的类型和顺序最好一致
union关键字默认去重,如果使用 union all 可以包含重复项