数据查询语言(Data Query Language)

数据查询语言(Data Query Language)

as可以用括号代替

– 过滤查询(带条件查询)
– 过滤条件使用的运算符 > < >= <= like in() between…and… 逻辑运算符: or and !
– like 使用 _表示匹配任意一个字符 %任意0到多个字符
– is null 表示为null is not null 表示为不null

-- 查询出工资大于1200的员工信息
select * from emp where sal>1200
-- 查询出7499号员工信息
select * from emp where empno=7499;
-- 查询出10,30号部门的所有员工
select * from emp where deptno in(10,30);
select * from emp where deptno=10 or deptno = 30;
-- 查询出奖金为null的员工
select * from emp where comm is null;
-- 查询出工资在1200 到 3500之间的员工信息
select * from emp where sal BETWEEN 1200 and 3500;

-- 查询出工资大于1200 并且名称带有S的员工信息
select * from emp where sal>200 and ename like '%S%';
-- 查询出姓名中第二个字符带M的员工信息
select * from emp where ename like '_M%'
select * from emp where ename like '%M_'

– 联表查询:两个以上表的查询叫做联表查询
– 通过主外键关联查询
– 联表查询过程中,两个表之间的数据穿插交互匹配,叫做笛卡尔积

select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
-- 排序关键字  order dy 列名 排序          两种排序方式  asc 升序(默认)   desc降序
order by deptno

-- 为了避免错误,采用等值查询,采用主外键关联匹配查询,只能一一对应 
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e,dept d
where e.deptno = d.deptno;
-- 内连接查询 inner join 表示联表查询   on表示连接条件    inner join中的inner可以省略
select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e join dept d
on e.deptno = d.deptno

– 外连接查询: 左外连接查询 右外连接查询
– 左外连接查询:以关键字左边的表为主要信息展示,没有关联匹配的数据也会展示出来
– 联表查询后展示所有的员工信息 关键字 left outer join

select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e left outer join dept d
on e.deptno = d.deptno ;

-- 右外连接查询 与左外连接查询类似 outer 可以省略
```sql
select e.empno,e.ename,e.job,e.mgr,e.hiredate as aa ,e.sal,e.comm,d.deptno,d.dname,d.loc
from emp e right join dept d
on e.deptno = d.deptno

---

– 查询过程中使用的函数
– 函数分为 单行函数 和 组函数

单行函数

:每一行之间都会受到影响得到结果
– 字符,数字,日期,转换,去空
– 字符: 转换大小写 字符的长度

-- 查询出员工姓名带s的员工信息
select * from emp where lower(ename) like '%s%'

-- 查询出员工姓名带S的员工信息
select * from emp where upper(ename) like '%S%'
-- 查询出所有员工姓名,获取到姓名的长度
select ename,length(ename) "长度" from  emp

-- 数字: 四舍五入  向上取整 向下取证  取模
-- dual表  是mysql中的结构表,用于补全查询结构,本身没有数据
select round(3.6415926) from dual;
-- 向下取证
select floor(3.6415926) from dual;
-- 截断 第二个参数表示要保留的小数位数
select TRUNCATE(3.6599999,2) from dual;
-- 向上取整
select ceil(3.1415926) from dual;
-- 取模  取余
select mod(78,4) from dual;

– 日期函数:
– 指定日期的当月最大一天

select MONTH('1999-09-15') from dual;
-- 当月时间最大天数
select LAST_DAY(hiredate) from emp;

-- 转换函数
 -- 隐式转换函数:  自动转换
 -- 显示转换函数:  采用函数显示的对数据进行转换
 --  str_to_date  DATE_FORMAT(date,format)


-- 将字符串转换为日期
select STR_TO_DATE('15-08-1999','%d-%m-%Y') from dual;

-- %y 表示年  %m表示月   %d表示日期 %h 时  %i分  %s秒
select DATE_FORMAT(hiredate,'%Y-%m-%d') from emp;
-- 显示 XXXX年XX月XX日 格式的日期
select date_format(hiredate,'%Y年%m月%d日') from emp;

–

组函数

avg平均数,sum总数,max最大数,min最小数,
ifnull 去掉空值,count 记录总数

SELECT (e.sal+IFNULL(e.comm,0)) 
FROM emp e

select AVG(sal) 平均工资,max(sal) 最高工资,min(sal) 最低工资,SUM(sal) 总工资
       ,ceil(avg(IFNULL(comm,0))) 平均奖金,COUNT(empno) 总人数 
from emp;
       

IFNULL(comm,0) 相当于一列了

---

分组查询

– group by 分组查询
– group by可以多列分组 分组了便于分部门分工作
– 规则:
group by中存在的列,在select中不一定有,
在select中存在的非组函数列,在group by中必须有

– 查询出所有职位,并统计人数

select job,count(*)  as '职位人数'
from emp
group by job

-- 查询出每个部门的平均工资和最高工资
select deptno,avg(sal)  as '平均工资',max(sal) as '最高工资',count(ename)
from emp
group by deptno

-- HAVING 分组后再次对数据进行过滤
-- 查询出每个部门的平均工资和最高工资,最高工资大于3000的部门
select deptno,avg(sal)  as '平均工资',max(sal)  as '最高工资',count(*)
from emp
group by deptno
having max(sal)>2000

子查询

单行子查询

· SELECT中使用子查询

• @@@ 返回值必须是竖着的列 ？(这里有点疑惑读者小心别被误导了)
  – 只有一列 否则：Operand should contain 1 column(s)
  – 基本和列同级别的子查询

– 统计每个部门的信息和人数

select d.*,(select count(deptno) 
            from emp 
            where deptno=d.deptno) as num
from dept d

· FROM中使用子查询

• 返回值可以是表
  –
  – 而且只有一列 否则：Operand should contain 1 column(s)

– 查询出员工编号,姓名,职位,工资,部门名称,工资要大于1000

select e.empno,e.ename,e.job,e.sal,e.dname
from (select a.*,d.dname,d.loc 
	  from emp a left join dept d
	  on a.deptno = d.deptno) as e
where e.sal>1000

@@@左右外连接不存在笛卡尔积的情况

· WHERE中使用子查询

• 返回值必须是1X1的值
  – 既select选择出一条记录
  – 而且只有一列 否则：Operand should contain 1 column(s)

– 查询出部门为’SALES’的所有员工信息

select * from emp 
where deptno = (select deptno
				from dept
				where dname = 'SALES')

· HAVING中使用子查询

– having中使用子查询
– 查询出最低工资大于30号部门最低工资的部门编号和其最低工资

select deptno,min(sal)
from emp
group by deptno
having min(sal)>(select min(sal) 
				 from emp 
			     where deptno =30)

· GROUP BY中不允许使用子查询

多行子查询

每个子查询查询结果为多个结果
– 多行操作符 in any 大于最小值,小于最大值 all 大于最大值,小于最小值

– 查询出’SALES’ 和 'ACCOUNTING’部门中所有的员工

select * 
from emp 
where deptno in (select deptno
                 from dept
                 where dname='SALES' or dname='ACCOUNTING')
																	
-- 查询出工资高于某一部门平均工资的员工信息
select *
from emp
where sal> any(select avg(sal)
								from emp
								GROUP BY deptno)
-- 查询出工资低于某一部门平均工资的员工信息
select *
from emp
where sal< any(select avg(sal)
								from emp
								GROUP BY deptno)
-- 查询出工资高于所有部门平均工资的员工信息
select *
from emp
where sal> all(select avg(sal)
								from emp
								GROUP BY deptno)
-- 查询出工资低于所有部门平均工资的员工信息
select *
from emp
where sal< all(select avg(sal)
								from emp
								GROUP BY deptno)

练习
https://www.cnblogs.com/ll564345575/p/3881196.html
https://blog.csdn.net/weixin_42385894/article/details/84342359