数据库二（查询）

DQL：数据查询语言 select 

查询的结果是一张虚拟的二维表，这个“二维表”在数据库中根本不存在

-- (1)查询表中的全部数据   select * from 表名;
select * from emp;
select * from dept;
-- (2)查询表中的部分列  select 列名1,列名2,... from 表名;
select empno,ename,job from emp;
   -- 对查询结果的列使用别名
select empno as 员工编号 ,ename as 员工姓名,job as 职位 from emp;
select empno 员工编号,ename 员工姓名,job 职位 from emp;
 -- (3)查询部分行  select ... from 表名 where ...
 -- 查询薪水低于3000的员工信息
select * from emp where sal<3000;
-- 查询薪水高于等于3000的员工信息
select * from emp where sal>=3000;
-- 查询职位为职员的员工信息
select * from emp where job='CLERK';  -- 不区别大小写
select * from emp where job='clerk'; -- 不区别大小写
select * from emp where  BINARY job='clerk';  -- 加上binary 之后对于值就区分大小写
 /** =即是关系运算符也是赋值运算符
     =与 where一起使用， 关系运算符
     =与set 一起使用，赋值运算符
      update 表名 set 列名=值  ....*/
-- 查询职位不是职员的员工信息
select * from emp where job <>'CLERK';
select * from emp where job !='CLERK';
-- 查询员工的薪水是在1000到3000之间的员工信息
select * from emp where sal >=1000 and sal<=3000;
select * from emp where sal >=1000 && sal<=3000;
-- 查询职位是职员或销售的员工信息
select * from emp where job='CLERK'or job='SALESMAN';
select * from emp where job='CLERK' || job='SALESMAN';
-- 查询空值问题 
 -- 查询所有津贴为null的员工信息
select * from emp where comm is null;
-- 查询津贴不为null的员工信息
select * from emp where comm is not null;
-- 去掉重复数据
 -- 查询员工表中的的员工一共从事几种职位
select DISTINCT job from emp;
select  distinct ename,job from emp;
-- (4)对查询结果进行排序 order by asc/DESC
-- 查询所有员工的信息并按照薪水降序
select * from emp order by sal desc;
-- 查询所有员工信息，并按照姓名升序
select * from emp order by ename ;
-- 查询部门编号为30的员工信息，并按照入职时间由早到晚
select * from emp where deptno=30 order by  hiredate;
-- 查询员的信息，并按照薪水排序，如果薪水相同，则按照入职时间降序
select * from emp order by sal ,hiredate desc;

 

 

mysql中的函数 
  函数-->相当于java中的方法

 mysql中的函数-->相当是java常用类中的方法

 数值型函数-->Math类的方法
 字符型函数-->String,Charactor中的方法
 日期型函数-->Date类中的方法

单行函数:一行得一个结果

-- 字符型
select ascii('abc'); -- 最左侧字符的ascii值
select bin(5);  -- 得到5的二进制数
select CHAR_LENGTH(ename) from emp; -- 字符串中字符的个数
select CHAR_LENGTH('helloworld');
select CONCAT('hello','world','java'); -- 字符串连接
select insert('hellowrold',6,4,'javaSE'); -- 6是开始位置，4是替换掉字符的个数
select left('helloworld',5); -- 从左侧截取5个字符
select right('helloworld',5); -- 从右侧截取5个字符
select upper('helloworld'); -- 转大写
select lower('HELLO');  -- 转小字
select ltrim('   hello   world   '); -- 去掉字符串左右的空格
select rtrim('   hello   world   ');
select length(rtrim('   hello   world   '));
select substring('helloworld',6,3); -- 从6开始截三个
select REVERSE('helloworld');
-- 数值型
 -- 算术运算符 +,-,*,/,%
select 3+5 ;
select '3'+5; -- 自动类型转换 将字符转数值型
select 3-5;
select 3*5;
select 3/5; -- 非整除
select 3 div 5; -- 整除
select 5%4;  -- 余数
select mod(5,4) ;  -- mod叫函数
select abs(2),abs(-2),abs(0);  -- 绝对值
select CEILING(1.000001),ceil(-9.9999999) ;
select FLOOR(1.999999),floor(-9.00001);
select round(1.4),round(1.5); -- 四舍五入
select PI();
select pow(2,3),power(2,3);  -- 2的3次方
select rand();  -- [0,1]
select sqrt(4); -- 开平方

--日期时间型函数
select curdate(); -- 年月日   2019-06-27
select CURDATE()+0; -- 20190627
select CURTIME(); -- 时分秒
select now(); -- 年月日，时分秒
select SYSDATE();
select ADDDATE(now(),INTERVAL 2 day);  -- 返回的是在指定的day,year,month加或减
select ADDDATE(now(),INTERVAL -2 day);
select ADDDATE(now(),INTERVAL -2 year);
select ADDDATE(now(),INTERVAL -2 month);
select date(now()); --年月日
select time(now()); --时分秒
select DATEDIFF(CURDATE(),'2008-08-08');--两个日期之间的天数差
select DAYOFWEEK(now()); --一周的第几天
select DAYOFYEAR(now());
select DAYOFMONTH(now());
select DAYNAME(now()); --星期四
-- 查询员工的服务的工作年限
select e.*,floor(DATEDIFF(now(),hiredate)/365) as 服务年限 
from emp e order by 服务年限 desc;
 --查询员工姓的前三个字母
select SUBSTRING(ename ,1 ,3) from emp;
--查询员工姓名的长度大于5的员工的编号，姓名
select empno,ename from emp where length(ename)>5;

多行函数: 多行得一个结果

 

 

流程控制函数   相当于java中的分支结构

 select * from emp ;
select empno,ename,job, sal+comm from emp; -- 如果没有津贴，不发工资
select empno,ename,job ,sal+ifnull(comm,0) from emp; -- if comm is null sal+0,否则sal+COMM
 -- 双分支结构 (二选一执行)
 -- IF (sal>=3000) syso("high") else syso("low")
select empno,ename,job ,if(sal>=3000,'high','low') as grade from emp;
-- 双分支结构
select empno,ename,job, 
   case  when sal>=3000 then 'high'
           else 'low'
   end
   as grade
from emp;
-- 多分支结构
select empno,ename,job ,
  case when sal>=3000  then 'high'
       else case  when sal>=2500 then 'mid'
                  else  'low'
             end
  end
as grade
from emp;

 

 

单行函数 ：一行得一个结果
多行函数  ：多行得一个结果  (分组函数，聚合函数)
 sum()  :对于某个字段中数据进行累加求和，只应用于数值型
avg()  ：对于某个字段中数据计算平均值  ，只应用于数值型

max()  :对于某个字段中的数据求最大值 ,字符型，数值型，日期型
min()  :对于某个字段中的数据求最小值

count() :统计行的个数

 select * from emp;
-- 查询20号部分的员工的总薪水
select sum(sal)as 总薪水 from emp where deptno=20;  -- 只需要告诉mysql做什么，不需要告诉它怎么做
select round(avg(sal))as 平均薪水 from emp where deptno=20;
select max(ename) ,min(ename) from emp;  -- 英文字母的升序  字符型
select max(hiredate),min(hiredate) from emp;  -- 查询入职时间最晚的和入职时间最早的
-- 想计算入职时间最晚的与入职之间最早的时间差是多少年
select DATEDIFF( max(hiredate),min(hiredate))/365 from emp;
select max(sal) ,min(sal) from emp;
--
select count(*) from emp where deptno=20; -- 建议使用* 
select count(1) from emp where deptno=20; -- 也可以，count(常量)
select * from emp where deptno=20;

什么是分组？ group by 

select * from emp;
-- 查询20号部分的员工的总薪水
select sum(sal)as 总薪水 from emp where deptno=20;  -- 只需要告诉mysql做什么，不需要告诉它怎么做
select round(avg(sal))as 平均薪水 from emp where deptno=20;
select max(ename) ,min(ename) from emp;  -- 英文字母的升序  字符型
select max(hiredate),min(hiredate) from emp;  -- 查询入职时间最晚的和入职时间最早的
-- 想计算入职时间最晚的与入职之间最早的时间差是多少年
select DATEDIFF( max(hiredate),min(hiredate))/365 from emp;
select max(sal) ,min(sal) from emp;
--
select count(*) from emp where deptno=20; -- 建议使用* 
select count(1) from emp where deptno=20; -- 也可以，count(常量)
select * from emp where deptno=20;
-- 查询每个部分的总人数
select deptno, count(*) from emp where deptno is not null group by deptno;
-- 查询每个部门的平均薪水
select deptno,avg(sal) from emp where deptno is not null group by deptno;
-- 查询每种职位的平均薪水
select job ,avg(sal) from emp  group by job order by avg(sal) desc;
-- select后只能写分组项和分组函数 
select job ,avg(sal) from emp  group by job order by avg(sal) desc;
select deptno, count(*) from emp where deptno is not null  group by deptno
-- 查询每个部分的总人数不低于5人的部门
select deptno, count(*) from emp where deptno is not null group by deptno
having count(*)>5;  -- having 是分组之后的条件判断
select deptno, count(*) from emp  group by deptno
having deptno is not null; 
/**where与having都用于条件判断（筛选条件）
   where 应用在group by之前
   having 应用在group by之后
   非分组函数，只是普通的条件判断，使用where与having结果相同，但是建议使用where
    where之后只有是普通的条件判断
    having之后可以是普通的条件判断，也可以是分组函数的条件判断
*/

--查询每个部门的总人数
select  * from emp order by deptno;

复习:单表查询的语法
  select .... from 表名 [where] ...[ group by]..[having]...[order by  asc/desc];

两表查询的92标准
 语法  :select ...from 表名1,表名2  where  表名1.列=表名2.列
  举例:select * from emp e,dept d where d.deptno= e.deptno;

 

--查询部门名称为sales的员工的编号，姓名，职位
select * from dept;
select empno,ename,job,dname from emp e, dept d 
where e.deptno=d.deptno and dname='sales';
--查询每个部门的员工的平均薪水
select dname,avg(sal) from emp e ,dept d where e.deptno=d.deptno group by d.dname
having avg(sal)>2000 order by avg(sal) asc;

 

--多表连查询 ，要求，N张表做连接查询，连接条件为n-1
--多表连查询 ，要求，N张表做连接查询，连接条件为n-1
create table student(
   stuno int(4) primary key auto_increment,
   stuname varchar(20) not null,
   clazzid int(4) 
);
create table clazz(
   clazzno int(4) primary key auto_increment,
   clazzname varchar(20),
   place int (4)
);
create table school(
        schoolid int(4) primary key auto_increment,
  schoolname varchar(20)
);
--外键关系
alter table clazz add constraint fk_place foreign key (place)
REFERENCES school (schoolid);
alter table student add constraint fk_clazzid foreign key(clazzid)
REFERENCES clazz(clazzno);
--添入测试数据
insert into school (schoolname) values ('京南校区');
insert into school (schoolname) values ('西三旗校区');
insert into school (schoolname) values ('东三旗校区');
insert into school (schoolname) values ('上海校区');
insert into school (schoolname) values ('琛圳校区');
select * from school;
insert into clazz (clazzname,place) values ('零基础java',1);
insert into clazz (clazzname,place) values ('java大数据就业',2);
insert into clazz (clazzname,place) values ('H5前端',1);
insert into clazz (clazzname,place) values ('人工智能',2);
insert into clazz (clazzname,place) values ('python',1);
select * from clazz;
insert into student (stuname,clazzid) values ('张三',1);
insert into student (stuname,clazzid) values ('李四',2);
insert into student (stuname,clazzid) values ('王五',1);
insert into student (stuname,clazzid) values ('陈六',1);
insert into student (stuname,clazzid) values ('麻小七',3);
select * from student;
--查询学生的姓名，所学课程名称及所在的校区名称
select stuname,clazzname,schoolname from student s, clazz c,school SH
where s.clazzid=c.clazzno and c.place=SH.schoolid;

99标准的多表连接查询 (内连接查询)  两个表的地位是相等的，查询的是两个表中“等值”列
 语法： select ...from 表名1  [inner] join  表名2 on 连接条件  [where]
 
select empno,ename,job,sal,dname from emp e ,dept d 
where e.deptno=d.deptno and dname='sales';
-- e.deptno=d.deptno 称为连接条件
-- dname='sales' 限定条件(筛选条件)  99年的时候 XXX  这样不好，将连条件与限定条件放在一起了，不容易区分
--所以99标准中，将连接条件与限定条件进行了区分，连接条件使用on，限定条件使用where
select empno,ename,job,sal,dname from emp e
inner join dept d  on e.deptno=d.deptno
where dname='sales';
--三表连接
select stuname,clazzname,schoolname from student s
inner join clazz c on s.clazzid=c.clazzno
inner join school sc on c.place=sc.schoolid
where clazzname like '%java%';  --模糊查询 %代表的是任意个字符 _代表的是一个字符

外连接查询 :查询中的两个表有主次关系，可以查询一个表的所有数据
 --查询所有员工的信息，包含没有部门的员工

--外连接
 --查询的是所有的员工的信息，包含没有部门的员工
select empno,ename,job ,sal ,dname from emp e LEFT JOIN dept d on e.deptno=d.deptno;
select empno,ename,job ,sal ,dname from dept d right join emp e on e.deptno=d.deptno;
--查询所有部门的信息，包含没有员工的部门
select empno,ename,job ,sal,dname,loc from   dept d left join emp e on e.deptno=d.deptno;
select empno,ename,job ,sal,dname,loc from emp e right join dept d on e.deptno=d.deptno;
--查询员工表的所有的信息和部门表的所有信息，包含没有部门的员工和没有员工的部门
 (mysql不支持,oracle支持)
select empno,ename,job ,dname ,loc from emp e    full  outer  join dept d
on e.deptno=d.deptno;
自连接查询 :一张表当作两张表来使用
 
  --查询入职时间早于领导 的员工信息 92
select e.empno,e.ename,e.hiredate as 员工入职时间,m.ename as 领导姓名 ,m.hiredate as 领导的入职时间
 from emp e ,emp m where e.mgr=m.empno  and  e.hiredate <m.hiredate;
 -- 99 
select e.empno,e.ename,e.hiredate as 员工入职时间,m.ename as 领导姓名 ,m.hiredate as 领导的入职时间
 from emp e inner join emp m on e.mgr=m.empno 
where  e.hiredate <m.hiredate;