mysql学习-java大神之路

先执行from-在执行where-最后执行select

BINARY
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 
你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。

通过一个字段去重distinct
select distinct deptno from emp;
通过两个字段去重
select distinct deptno,job from emp;
sal为月薪字段，查询年薪
select sal,sal * 12 as year_income from emp;
查询每月都有500元的餐补和200元交通补助并且年底多发一个月工资的年薪
select (sal + 500 + 200) * 13 as year_income from emp;
把一列或多列中为NULL的值改变其值（改为0）
IFNULL(字段名,0)
select IFNULL(comm,0) from emp;

练习
使用比较运算符
1.查询1981年之后入职的员工信息
select * from emp where hiredate >= '1982-01-01';
2.查询年薪小于3w的员工
select *,sal * 12 year_income from emp where (sal * 12) < 30000;
3.查询所有不是销售人员的员工信息
select * from emp where job != 'salesman';
使用BETWEEN运算符
1.查询工资在2000-3000之间的员工信息
不使用between
select * from emp where sal >= 2000 and sal <= 3000;
使用between
select * from emp where sal between 2000 and 3000;
2.查询工资不在2000-3000之间的员工信息
select * from emp where sal not between 2000 and 3000;
3.查询1981年入职的员工
select * from emp where hiredate between '1981-01-01' and '1981-12-31';
使用in运算符
1.查询工资为800或1600或3000的员工
不使用in运算符
select * from emp where sal = 800 or sal = 1600 or sal = 3000;
使用in运算符
select * from emp where sal in(800,1600,3000);
2.查询工资不为800或1600或3000的员工
不使用in运算符
select * from emp where sal != 800 and sal != 1600 and sal != 3000;
使用in运算符
select * from emp where sal not in(800,1600,3000);

使用LIKE运算符
LIKE运算符必须使用通配符才有意义
    like运算符必须使用通配符才有意义：
    匹配单个字符：'_'    -> 1个
    匹配任意多个字符：%； ->0个，1个，多个

1.查询出所有雇员姓名是以A开头的全部雇员信息。
select * from emp where ename like 'A%';
2.查询出雇员姓名第二个字母是M的全部雇员信息。
select * from emp where ename like '_M%';
2.查询出雇员姓名第三个字母是M的全部雇员信息。
select * from emp where ename like '__M%';
3.查询出雇员姓名任意位置上包含字母为A的全部雇员信息。
select * from emp where ename like '%A%';

练习
1.查询姓名中有e或者a的员工姓名
select * from emp where ename like '%e%' or ename like '%a%';
2.查询工资在1500-3000之间的全部员工信息
select * from emp where sal between 1500 and 3000;
3.查询出职位是办事员(CLERK)或者是销售人员(SALESMAN)的全部信息，并且工资在1000以上。
select * from emp where (job = 'clerk' or job = 'salesman') and sal >= 1000; 
排序
order by
order by 子句出现在select语句的最后
order by 可以使用别名
先排序再分页
练习
1.查询所有员工信息，按照工资排序
select * from emp order by sal asc;
2.查询所有员工信息，按照年薪降序排序
select *,sal * 12 as income from emp order by (sal * 12) desc;
3.查询所有员工信息，按照部门和年薪降序排序
select *,(sal * 12) as income from emp order by deptno asc,(sal * 12) desc;

分页查询并排序
select * from emp order by sal asc limit 0,5;

字符函数
拼接字符串关键字
concat
select concat('My','S','QL');
->'MySQL'
select concat('My',NULL,'QL');
->NULL
length(字节)
char_length(字符)
utf-8编码，一个汉字为3字节
消除空格
LTRIM/RTRIM/TRIM
SELECT LTRIM(' A B C '),RTRIM(' A B C '),TRIM(' A B C ') FROM DULL;
字符替换
select replace('www.mysql.com','w','Ww');
->'WwWwWw.mysql.com'
字符串截取
substring
select substring('ABCDEFG',3) from dual;
结果：CDEFG
select substring('ABCDEFG',3,3) from dual;
结果: CDE
数字函数
ABS( x )：返回一个数字的绝对值：
MOD(N,M)：返回N被M除后的余数：
select ABS(-17),ABS(17),MOD(10,5),MOD(10,3) FROM DUAL;
结果：17 17 0 1
CEIL(x)：返回不小于X的最小整数值
FLOOR(x)：返回不大于X的最大整数值
SELECT CEIL(0.5),CEIL(-1.5),CEIL(1.5) FROM DUAL;
1 -1 2
LPAD(str,len,padstr)
1.返回字符串str,其左边由字符串padstr填充到len字符长度
2.假如str的长度大于len,则返回值被缩短至len字符。
round(四舍五入函数)
round(x),对x做四舍五入。
round(x,2),对x做四舍五入并保留2位小数
truncate(X,D)
舍去X小数点D位后的值；
select truncate(3.666,2);返回3.66
date_add(),增加多少year,day,second,hour....
select date_add(now(),interval 100 day);
date_sub(),减去多少year,day,second,hour....
select date_sub(now(),interval 100 day);
current_date(),current_time();/当前日期，当前时间
datediff(now(),'2018-01-01');/获取两个日期的间隔天数(查询时别忘了加select)
select year(now()) from dual;/获取年
hour,minute,month,year,last_day/时,分钟,月,年,获取一个日期或日期时间值，返回该月最后一天对应的值
//查询入职时间为1981的员工信息
select * from emp where year(hiredate) = '1981';
unix_timestamp(date);/把时间转换成秒数返回；
from_unixtime(date);/把秒数转换成日期返回；
format(x,d);/以四舍五入的方式保留小数点后d位，并将结果以字符串的形式返回
select format(123456.789,2) from dual;/123,456.79
date_format(now(),'%Y-%m-%d');/时间转换,日期转字符串
str_to_date(str,format);/把字符串转换成日期
单行函数可被嵌入到任何层，执行顺序是先内后外。
按职位分组，求出每个职位的最低最高工资
select job,MAX(sal),MIN(sal) from emp group by job;

where 和 having 的区别
where是分组之前做筛选，having是分组之后做筛选
笛卡儿积：
多表查询时没有连接条件的表关系返回的结果     
像这样select * from emp,dept;
解决方案：增加连接条件
连接n张表，至少需要n-1个连接条件

连接查询
    内连接查询
        隐式内连接：看不到JOIN
        显式内连接：看得到JOIN
 隐式内连接查询语法：
select <select_list>
from 表名称 A，表名称 B
where 查询条件 AND 消除笛卡尔积的连接条件 
order by 排序字段 asc|dese ,排序字段 asc|dese;

查询员工的姓名，工资，所在部门的名称，以及工资的等级
select e.ename,e.sal,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal

显示内连接的查询语法
select * from 表名称 A INNER JOIN B ON 条件

select e.ename,e.sal,d.dname,s.grade from emp e JOIN dept d ON e.deptno = d.deptno JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

使用union做全连接
select e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno union select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;

批量新增数据(新增查询出来的数据，相当于复制数据)
insert into dept (dname,loc) select dname,loc from dept;

查询出每个部门的编号，名称，部门人数，平均工资
select d.deptno,d.dname,count(e.empno),avg(sal) from dept d left join emp e where  d.deptno = e.deptno;

使用group by的语法
1.出现在select列表中的字段，如果出现的位置不是在组函数中，那么必须出现在group by子句中
2.在group by子句中出现的字段，可以不出现在select列表中
3.如果没有group by子句select列表中任何列表或表达式不能使用统计函数：
分组函数单独使用：
select count(empno) from emp;
错误的使用，出现了其他字段：
select empno,count(empno) from emp;
-----------------------------
如果现在要进行分组的话，则select 子句之后，只能出现分组的字段和统计函数，其他的字段不能出现：
正确做法：
select job,count(empno),avg(sal)
from emp
group by job;
错误的做法：
select deptno,job,count(empno),avg(sal) 
from emp
group by job;

分组函数练习：
1.按照职位分组，求出每个职位的最高和最低工资
select job,max(sal),min(sal) from emp group by job;
2.查询出每一个部门员工的平均工资
select deptno,avg(sal) from emp group by deptno;
3.查询平均工资高于2000的部门和其平均工资
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
4.查询各个部门和岗位的平均工资
select deptno,job,avg(sal) from emp group by deptno,job;
5.查询各个管理人员下员工的平均工资，其中最低工资不能低于1300，不计算老板
select mgr,avg(sal) from emp where mgr is not null group by mgr having avg(sal) >= 1300;
6.查询在80，81，82，83年各进公司多少人
select year(hiredate) y,count(empno) from emp group by y having y in('1980','1981','1982','1983');
having 对分组的结果做筛选
where 对查询的数据做筛选

子查询
练习：
1.查询大于公司平均工资的员工姓名
select ename,sal from emp where sal > (select avg(sal) from emp);
2.查询出工资比MARTIN还要高的全部雇员信息
select * from emp where sal>(select sal from emp where ename = 'MARTIN');
注意事项：
1.子查询要用括号括起来
2.将子查询放在比较运算符的右边(增加可读性)
3.对单行子查询使用单行运算符
4.对多行子查询使用多行运算符
子查询分类：
单行单列子查询：放在where后面；
多行单列子查询：放在where后面；
多列子查询：好比是一张临时表，放在from后面；
多行单列子查询使用多行比较运算符
in：与列表中的任意一个值相等
any：与子查询返回的任意一个值比较
    1>：=any:此时和in操作符相同。
    2>：>any:大于子查询中最小的数据
    3>：<any:大于子查询中最大的数据
all：与子查询返回的每一个值比较
    1>：>all:大于子查询中最大的数据
    2>：<all:小于子查询中最小的数据
练习
查询平均工资高于公司平均工资的部门信息
select deptno,avg(sal) from dept join emp using(deptno) group by deptno having avg(sal)>
(
select avg(sal) from emp
)
多列子查询
子查询返回的结果是多行多列/一行多列，就可以看成一张表
注意！多行多列的子查询返回的结果必须设置一个临时表的名称；
查询每个部门的编号，名称，部门人数，平均工资：
select d.deptno,d.dname,count(e.empno),avg(e.sal) from dept d left join emp e on d.deptno=e.deptno group by d.deptno,d.dname;

用到的数据表

提取码:2p6j