java-mysql-语法
条件查询
1.条件运算符:>, <, =, !=, <>
2.按逻辑表达式 : &&, || ,!
and，or，not
&&和and:两个条件都为true.结果为true,反之为false
||和or:只要有一个条件为true,结果就为true, 反之为false
!和not:如果连接的条件本身就为false,结果为true
3.模糊查询: like,between and,in,is null，order by
常见函数：
字符函数：
IFNULL(departement_id,0) 给默认值
length 长度 LENGTH(last_name) 字节长度
concat 拼接 concat（'a','b','c'）as 结果
substr 截取的函数 索引从第一位开始位置，第二位结束位置
instr 返回子串第一次出现的位置,如果找不到返回0 instr ('李莫愁爱上了陆展元','陆展元') as 结果
trim 去除前后空格
upper 大写
lower 下写
lpad 左填充 lpad('叶丁广','12','*')
rpad 右填充 rpad('叶丁广','12','*')
replace ('叶丁广喜欢王默默 ','王默默','王章')
数字函数
round(1.55) 四舍五入的数字
round(1.255,2） 保留两位小数
ceil(1.25) 向上取整
floor(45.99) 向下取整
truncate(4.655,1) 截断（从小数点后一位截断）
MOD(10,3) 取余 10%3 或者10/3的余数 （被除数为负结果为负，被除数为正结果为正）
日期函数
now() 当前时间 %Y年%m月%d日%H时%i分%s秒' )
curddate
curdTime
year
select year(now()) 年；
month
select month(now()) 月；
day
select day(now()) 日；
hour
select hour(now()) 时；
minute
select minute(now()) 分；
second
select minute(now()) 秒；
str_to_date 字符串转日期 STR_TO_DATE('9-12-1993','%m-%d-%y') 返回的是固定格式19993-9-12
SELECT last_name,STR_TO_DATE('9-12-1993','%m-%d-%y') FROM ums_employees
date_format 日期转字符串 SELECT date_format('2018/6/6','%Y年%m月%d日')
SELECT last_name,DATE_FORMAT(create_time,'%m月/%d日 %y年') FROM ums_employees
流程控制函数
if函数： if else的效果
if(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2
select if(10<5, '大', '小') 》》小
case 函数：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
CASE departement_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资 FROM ums_employees;
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
#基础查询
IFNULL(departement_id,0) 给默认值
select IFNULL(departement_id,0) as 奖金率
DISTINCT 去重
select DISTINCT departement_id from ums_employees
as 别名 或空格 别名
select firest_name 名 ,last_name 姓 from ums_employees
案例：有特殊符号，或者关键字 用单引号或者双引号
select firest_name as 'out put' from ums_employees
---------------------------------------------------
条件查询
1.条件运算符:>, <, =, !=（或者<>）,
2.按逻辑表达式 : &&, || ,!
and，or，not
&&和and:两个条件都为true.结果为true,反之为false
||和or:只要有一个条件为true,结果就为true, 反之为false
!和not:如果连接的条件本身就为false,结果为true
3.模糊查询: like,between and,in,is null（<=> 安全等于），order by
SELECT
查询列表
from
表名
where
筛选条件
SELECT e.create_time data
FROM sys_enforce_event e
WHERE e.create_time BETWEEN '2019-03-22' AND '2019-03-25'
---------------------------------------------------
常见函数:字符串
SELECT LENGTH('只能够aaaa') 长度函数
SELECT CONCAT('叶','丁','广') 拼接函数
upper 变大写
lower 变小写
案例；将姓变大写。名变小写，然后拼接
SELECT concat(upper(last_name),lower(firest_name)) 姓名 from ums_employees
substr 截取的函数 索引从第一位开始位置，第二位结束位置
SELECT substr('李莫愁爱上了陆展元',7) as 结果
SELECT substr('李莫愁爱上了陆展元',1,3) as 结果
案例： 姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_'LOWER(SUBSTR(last_name,2))) as 结果 FROM ums_employees
lpad 用指定的字符实现左填充指定长度
SELECT lpad('叶丁广','12','*')
Rpad 用指定的字符实现左填充指定长度
SELECT Rpad('叶丁广','12','*')
replace替换
SELECT REPLACE('叶丁广喜欢王默默 ','王默默','王章')
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
常见函数:数字函数
四舍五入
round(1.55)
round(1.255,2） 保留两位小数
ceil(1.25) 向上取整
floor(45.99) 向下取整
truncate(4.655,1) 截断
MOD(10,3) 取余 10%3 或者10/3的余数
常见函数:日期函数
SELECT NOW()年月日时分秒 %Y %m %d %H %i %s
SELECT CURRENT_DATE() 年月日
SELECT CURRENT_TIME() 时分秒
获取指定的部分
SELECT YEAR(NOW())
SELECT YEAR('1998-1-25') 年
SELECT MONTH('1998-1-25') 月
str_to_date:将字符转成指定格式的日期
一一对应 月对月，日对日 年对年
STR_TO_DATE('9-12-1993','%m-%d-%y') 固定格式19993-9-12
date_format:将日期转换成字符
SELECT date_format('2018/6/6','%Y年%m月%d日') 固定格式可以自定义
案例；查询有奖金的员工和入职日期（xx月/xx日 xx年）
SELECT last_name,DATE_FORMAT(create_time,'%m月/%d日 %y年') FROM ums_employees
---------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------
流程控制函数
if函数
SELECT if(10<5,'大','小')
扩展
三元运算符
salary<60? '不及格':'及格'
案例
SELECT last_name,commission_pct, if(commission_pct is NULL,'没有奖金，呵呵','有奖金，嘻嘻') 备注 FROM ums_employees
case 函数 （java中 switch 写法） 方法一
mysql中 （java中 switch 写法）
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
when 常量3 then 要显示的值3或语句3
....
eles 要显示的值n或语句n 默认值
end
案例；查询员工的工资，要求
部门号 =30，显示的工资为1.1倍
部门号 =40，显示的工资为1.2倍
部门号 =50，显示的工资为1.3倍
其他部门，显示的工资为原工资
SELECT salary 原始工资, departement_id,
CASE departement_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资 FROM ums_employees;
case 函数 （多重if） 方法一
案例 查询员工的工资情况
如果工资》2000，显示A级
如果工资》3000，显示b级
如果工资》4000，显示c级
否则，显示D级别
SELECT salary 原工资,
CASE
WHEN salary>2000 THEN 'a'
WHEN salary>3000 THEN 'b'
WHEN salary>4000 THEN 'c'
ELSE 'd'
END as 结果 FROM ums_employees;
---------------------------------------------------
分组函数
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和 avg平均数， max最大值， min最小值 count计算个数
特点
1 sum avg 一般用于处理数值
max min count 可以处理任何类型
2 以上分组函数都忽略null值
3 可以和distinct 搭配实现去重运算
4 coun函数，一般用count(*) 用作统计行数
5 和分组函数一同查询的字段要求是group by后的字段
和distinct 搭配实现去重运算
select sum(DISTINCT salary),sum(salary) FROM employees
select count(DISTINCT salary),count(salary)from employees
count函数的详情介绍
select count(salary)FROM employees
select count(*) FROM employees
select count(1) FROM employees
查询员工表中的最大入职时间和最小入职时间的相差天数
DateDiff相差的天数
select DateDiff(max(hiredate),min(hiredate)) from employees
---------------------------------------------------------------------------------------------
分组查询
语法
select 分组函数，列（要求出现在group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1. 分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by子句的前面 where
分组后筛选 分组后的结果集 group by子句的后面 having
1分组函数做条件肯定是放在having子句中
2能用分组前筛选的，就优先考虑使用分组前筛选
2. group by 子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序要求）
3. 也可以添加排序（排序放在整个分组查询的最后）
列子：查询每个工种的最高工资
select Max(salary),job_id
FROM employees
group by job_id
例子2 查询每个位置上的部门个数
select count(*),location_id
from departements
group by location_id
例子3
查询邮箱中包含a 字符的，每个部门的平均工资
select AGV(salary),departement_id
from employees
where email like '%a%'
group by departement_id
添加分组后的条件筛选 having(就是对结果,在一次筛选)
例子 查询哪个部门的员工个数>2
1.查询每个部门的员工个数
select count(*),departement_id
from employees
group by departement_id
2.根据1的结果进行筛选，查询哪个部门的员工个数>2
select count(*),departement_id
from employees
group by departement_id
having count(*)>2
例子 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资（太复杂的可以先查基础的，在查接下来的）
1.查询每个工种有奖金的员工的最高工资
select max(salary),job_id
from employees
where commission_pct IS NOT NULL
group by job_id;
2. 根据1结果继续筛选，最高工资>12000
select max(salary),job_id
from employees
where commission_pct IS NOT NULL
group by job_id
having max(salary)>12000;
按表达式或函数分组
例子： 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
1.查询每个长度的员工个数
select count(*),LENGTH(last_name) len_name
from employees
group by length(last_name);
2添加筛选条件
select count(*) c,LENGTH(last_name) len_name
from employees
group by length
having c>5;
按多个字段分组
例子：查询每个部门，每个工作的员工的平均工资
select AVG(salary),departement_id,job_id
from employees
group by departement_id,job_id;
添加排序
例子 查询每个部门每个工作的员工的平均工资，并且按平均工资的高低显示
select AVG(salary),departement_id,job_id
from employees
where departements_id IS NOT NULL
group by departement_id,job_id
having AVG(salary)>10000
order by AVG(salary) Desc;
例子
查询员工最高工资，最低工资的差距
select max(salary) - min(salary) DIFFRENCE
FROM employees
----------------------------------------------------------------------------
连接查询
含义： 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行，表2 有n行，结果 = m*n行
发生的原因：没有有效的连接条件
如何避免 ：添加有效的连接条件
分类：
按年代分类
sql192标准： 仅仅支持内连接
sql199标准： 支持内连接+外连接（左外和右连）+交叉连接
内连接：
等值连接
非等值连接
自连接
外连接：
左外连接
右外连接
全外连接
交叉连接
sql192标准
等值连接------------------
1 多表等值连接的结果为多表的交集部分
2 n表连接，至少需要n-1个连接条件
3 多表连接的顺序没有要求
4 一般需要为表取别名
5 可以搭配前面介绍的所有子句使用，比如排序，分组，筛选
为表起别名
1.提高语句的简洁度
2.区分多个重名的字段
3、如果为了表起了别名，则查询的字段就不能使用原来的表名去限定
select e.last_name, e.job_id, j.job_title
from employees e, jobs j
where e.job_id = j.job_id
两个表的顺序可以调换的
select e.last_name, e.job_id, j.job_title
from jobs j ，employees e,jobs j
where j.job_id =e.job_id
例子
查询有奖金的员工名，部门名
select last _name ,department_name
FROM employees e, departements d
where e.departement_id =d.departement_id
and e.commission_pct IS NOT NULL
可以实现三表连接
例子：查询员工，部门名，和所在的城市
select last_name,department_name,city
from employees e, departements d,locations l
where e.departements_id =d.departement_id
and d.location_id = location_id
and city like '王'
order by department_name desc;
非等值连接------------------
查询员工的工资和工资级别
select salary ,grade_level
from employees e ,job_grades g
where salary between g.lowest_sal and g.hiredate_sal
and g.grade_level = 'A'
自连接------------------
select e.last_name employees,employees_id "别名" ，m.last_name "别名" ，m.employee_id "别名"
from employees e,employees m
where e.manager_id = m.employee_id
and e.last_name ="kochhar";
---
sql199标准
语法：
select 查询列表
from 表1 别名 【连接条件 inner,left,right,full】
join 表2 别名
on 连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】
内连接： inner
等值连接
非等值连接
自连接
外连接：
左外连接 left 【outer】
右外连接 right【outer】
全外连接 full 【outer】
交叉连接: cross
内连接------------------
语法
select 查询列表
from 表1 别名
inner 表2 别名
on 连接条件
分类：
①等值
②非等值连接
③自连接 【父子关系，在同一张表】
特点
①添加排序，分组，筛选
inner 可以省略
连接条件放在on后面，筛子的条件放在where后面
等值 案例1
查询员工名，部门名
select last_name,department_name
from departements d
inner join employees e
on d.departement_id =departement_id
等值 案例2
查询名字中包含e的员工名，和工种名
select len_name,job_title
from employees e
inner join jobs j
on e.job_id = j.job_id
where e.last_name like '%e%'
等值 案例3
查询员工名，部门名，工种名 并且按部门名降序
select last_name, department_name job_title
from employees e
inner join departements s on e.departements_id =d.departements_id
inner join jobs j on e.job_id = j.job_id
order by department_name desc
2、非等值连接 --------------------------
查询员工的工资级别
select salary,grade_level
from employees e
inner join job_grades g
on e.salary between g.lowest_sal and g.highest_sal;
3、自连接--------------------------
select d.last_name,e.last_name
from employees d
join employees e
on d.manager_id =e.employee_id
where e.last_name like '%k%'
4、外连接--------------------------
应用场景： 用于查询一个表中有，另一个表没有的记录
特点
1.外连接的查询结果为主表中的所有记录
如果从表有中和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null(用null来填充)
2.左外连接， left join 左边的是主表
右外连接， right join 右边的是主表
3.左外和右外交换两个表的顺序，可以实现相同结果
案例
select * from beauty 女神表
select * from boys 男神表
左外连接
select b.name,bo.*
from beauty b
left join boys bo
on b.boyfriend_id = bo.id
where bo.id IS NULL
交叉连接(笛卡尔乘积现象)
select b.*,bo.*
from beauty b
cross join boys bo:
