精华内容
下载资源
问答
  • 动力节点MySQL数据库视频中的sql文件,整理不易,很完整。
  • 动力节点郭鑫mysql课程讲义 ,完整的pdf版 MySQL数据库视频中的sql语句文件
  • B站动力节点SQL34道题学习视频链接 二、以下是建表语句和我手写的SQL语句 数据库名:bjpowernode 建表sql 百度云链接 链接:https://pan.baidu.com/s/1YtPor787nuDTgDY_VJsaSQ 提取码:1234 #1.取得各部门最高薪水...

    一、之前对sql的操作仅仅存在低级的CRUD语句

    于是去B站学习了动力节点的34道题,差不多用了一天时间。
    因为除了做出来,还要学会理解其含义

    B站动力节点SQL34道题学习视频链接

    二、以下是建表语句和我手写的SQL语句

    数据库名:bjpowernode
    建表sql 百度云链接
    链接:https://pan.baidu.com/s/1YtPor787nuDTgDY_VJsaSQ
    提取码:1234

    #1.取得各部门最高薪水(员工名 最高薪水 部门)-难点最高薪水可能不止一人
    select e.ename,t.*
    from emp e
    join (SELECT max(sal) as maxsal,DEPTNO from emp GROUP BY DEPTNO) t
    on
    e.SAL=t.maxsal and e.DEPTNO=t.DEPTNO
    
    #2.哪些人的薪水在部门平均薪水之上
    select t.*,ename,sal,emp.DEPTNO from emp
    join (select deptno,avg(sal) as avgsal from emp group by deptno) t
    on emp.sal>t.avgsal and emp.DEPTNO=t.DEPTNO
    
    #3.取得部门中所有人的 平均薪水的等级
    select e.DEPTNO,avg(s.grade)
    from emp e
    join salgrade s
    on e.SAL BETWEEN s.LOSAL and s.HISAL 
    GROUP BY DEPTNO
    
    #4.不用组函数取得最高薪水s
    #使用降序 limit 1
    select ENAME,sal from emp ORDER BY sal desc limit 1
    #使用max()
    #表的自连接
    select sal from emp
    where sal not in(
    select distinct a.sal from emp a
    join emp b
    on a.SAL<b.SAL)
    
    
    #5.取得平均薪水最高的部门的部门编号
    方案一:降序取第一个
    select avg(sal),deptno 
    from emp 
    group by deptno
    ORDER BY avg(sal) desc
    limit 1
    方案二:使用max
    select t.deptno,max(t.avgsal) as avgsal
    from 
    (select deptno,avg(sal) avgsal from emp group by deptno) t
    方案三:使用having
    select deptno,avg(sal) as avgsal
    from emp
    group by deptno
    having 
    avgsal = (select max(t.avgsal) as avgsal 
    from 
    (select deptno,avg(sal) avgsal from emp group by deptno) t)
    
    
    #6.取得平均薪水最高的部门名称
    select a.deptno,avg(a.sal),b.dname
    from emp a
    join dept b
    on a.deptno=b.deptno
    group by a.deptno
    order by avg(a.sal) desc
    limit 1
    
    #7.求平均薪水的等级最低的部门的部门名称 难点:不能根据薪水去取最小值 因为可能多个薪水都处于最小级别 要先求出最小级别
    #第一步找出每个部门的平均薪水等级
    select t.*,s.grade,d.dname from salgrade s
    join
    (select deptno,avg(sal) as avgsal from emp group by deptno) t
    join dept d
    on t.avgsal between s.LOSAL and s.HISAL
    and t.deptno=d.deptno
    where grade=
    (
    select grade from salgrade a
    join
    (select deptno,avg(sal) as avgsal from emp group by deptno order by avgsal limit 1) b
    on b.avgsal between a.LOSAL and a.HISAL
    )
    
    #8.取得比普通员工(员工代码没有在mgr字段上出现的)的最高薪水还高的领导人姓名
    select * from emp
    where empno in(select distinct mgr from emp where mgr is not null)
    having sal>
    (select max(sal) maxsal from emp 
    where empno not in(select distinct mgr from emp where mgr is not null)
    )
    
    #9.取得薪水最高的前五名
    select * from emp 
    order by sal desc
    limit 5
    
    #10.取得薪水最高的第六到第十名员工
    select * from emp 
    order by sal desc
    limit 5,5
    
    #11.取得最后入职的五名员工
    select * from emp
    ORDER BY HIREDATE desc
    limit 5
    
    #12.取得每个薪水等级有多少员工
    select s.grade,count(e.empno)
    from emp e
    join salgrade s
    on e.sal between s.LOSAL and s.HISAL
    group by s.grade
    
    #-------------学生信息表查询
    #13、面试题:
    -- 有3个表s(学生表),C(课程表),SC(学生选课表)
    -- s (SNO, SNAME)代表(学号,姓名)
    -- c (CNO, CNAME, CTEACHER) 代表(课号,课名,教师)
    -- sc (sNO, CNO, SCGRADE) 代表(学号,课号,成绩)
    -- 1,找出没选过黎明"老师的所有学生姓名。
    -- 2,列出2门以上(含2门)不及格学生姓名及平均成绩。
    -- 3,即学过1号课程又学过2号课所有学生的姓名。
    
    
    
    
    #14.列出所有员工及领导的姓名
    select a.ename "员工",ifnull(b.ename,'the boss') "领导"
    from emp a
    left join emp b
    on a.mgr=b.empno
    
    #15.列出受雇日期早于其直接上级的所有员工的编号,姓名,部门名称
    select a.empno,a.ename,c.dname,a.HIREDATE, b.ENAME "领导",b.HIREDATE "领导入职" from emp a
    join emp b
    on a.HIREDATE<b.HIREDATE and a.mgr=b.empno
    join dept c
    on a.deptno=c.deptno
    
    #16.列出部门名称和这些部门的员工信息,同时列出哪些没有员工的部门
    select a.*,b.*
    from dept a
    left join emp b
    on b.deptno=a.deptno
    order by a.DEPTNO
    
    #17.列出至少有五个员工的所有部门
    select deptno,count(*) from emp
    group by deptno
    having(count(*)>=5)
    
    select a.*,count(b.empno) as emptotal
    from dept a
    left join emp b
    on b.deptno=a.deptno
    group by a.deptno
    having(count(b.empno)>=5)
    
    
    #18.列出薪金比“SMITH”多的所有员工信息
    select * from emp
    where sal>(select sal from emp where ename='SMITH')
    
    #19.列出所有“CLERK”职位办事员的姓名及其部门名称,部门的人数  *****重点
    select a.ename,a.job,b.dname,b.deptno,c.totlal
    from emp a
    join dept b
    on a.deptno=b.deptno and a.job="CLERK"
    left join (select deptno,count(*) as totlal from emp group by deptno) c
    on a.DEPTNO=c.DEPTNO
    
    #20.列出最低薪金大于1500的各种工作,及从事此工作的全部雇员人数按照工作岗位分组求最小值
    select job,count(*) 
    from emp 
    group by job
    having(min(sal)>1500)
    
    select a.minsal,a.job,a.total from (select min(sal) minsal,job,count(*) as total from emp group by job) a
    where minsal>1500
    
    #21.列出在部门“SALES”销售部 工作的员工姓名 假定不知道销售部的部门编号
    select ename from emp a
    join (select deptno from dept where dname='SALES') b
    on a.DEPTNO=b.DEPTNO
    
    #22.列出薪金高于平均薪金的所有员工,所在部门,上级领导,雇员的工资等级
    select a.ename,b.dname,c.ename "领导",d.grade
    from emp a
    join dept b
    on a.deptno=b.deptno
    left join emp c
    on a.mgr=c.empno
    join salgrade d
    on a.sal between d.LOSAL and d.HISAL
    join (select avg(sal) as sumsal from emp) e
    on a.sal>e.sumsal
    
    #23.列出与“SCOTT”从事相同工作的所有员工及部门名称
    select a.ename,a.job,b.dname
    from emp a
    join dept b
    on a.deptno=b.deptno
    where a.job=(select job from emp where ename="SCOTT")
    and a.ename != "SCOTT"
    
    select a.*,b.dname from
    (select * from emp where job=(select job from emp where ename="SCOTT")) a
    join dept b
    on a.deptno=b.deptno
    and a.ename <> "SCOTT"
    
    #24.列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金
    select ename,sal from emp 
    where sal in(select distinct sal from emp where deptno=30)
    
    #25.列出薪金高于在部门30中的所有员工的薪金的员工姓名和薪金 部门名称
    select a.ename,a.sal,b.dname
    from emp a
    join dept b
    on a.deptno=b.deptno
    where a.sal>(select max(sal) from emp where deptno=30)
    
    #26.列出在每个部门工作的员工数量,平均工资和平均服务期限
    #timestampdiff(类型,开始时间,结束时间)
    select b.*,count(a.ename),ifnull(avg(a.sal),0),avg(timestampdiff(year,a.hiredate,now())) "平均服务期限"
    from emp a
    right join dept b
    on a.deptno=b.deptno
    group by b.deptno
    
    #
    select b.*,count(a.ename),ifnull(avg(a.sal),0),avg(year(curdate())-year(a.hiredate)) "平均服务期限"
    from emp a
    right join dept b
    on a.deptno=b.deptno
    group by b.deptno
    
    #日期转换DATE_FORMAT(日期,格式) 
    #大写Y:2021 小写y:21 | 大写M:April 小写m:04 | 大写D:6th 小写d:06
    select DATE_FORMAT(now(),'%Y-%m-%d')
    
    #27.列出所有员工的姓名,部门名称和工资
    select a.ename,b.dname,a.sal
    from emp a
    join dept b
    on a.deptno=b.deptno
    
    #28.列出所有部门的详细信息和人数
    select a.*,count(b.empno)
    from dept a
    left join emp b
    on a.deptno=b.deptno
    group by a.deptno
    
    #29.列出各种工作的最低工资及从事此工作的雇员姓名
    select a.ename,a.job,a.sal from emp a
    join (select min(sal) minsal from emp group by job) b
    on a.sal=b.minsal
    
    #30.列出各个部门的MANAGER(领导)的最低薪金
    select deptno,min(sal)
    from emp
    where job="MANAGER"
    group by deptno
    
    -- #外加列出各个部门的非普通员工的最低薪金
    -- select ename,deptno,min(sal) from emp 
    -- where empno in(select distinct mgr from emp where mgr is not null)
    -- group by deptno
    -- 
    -- #离谱有问题
    -- select a.deptno,a.empno,a.ename,max(a.sal) from emp a
    -- join (select distinct mgr from emp where mgr is not null) b
    -- on a.empno=b.mgr
    -- group by a.deptno
    
    
    #31.列出所有员工的年工资 按年薪从低到高排序
    select ename,(sal+ifnull(comm,0))*12 as yearsal
    from emp
    order by yearsal
    
    #32.求出员工领导的薪水超过3000 的员工名称与领导
    select a.ename,b.ename "领导" from emp a
    join emp b
    on a.mgr=b.empno
    where b.sal>3000
    
    #33.求出部门名称中,带‘S’字符的部门员工的工资合计、部门人数
    select a.deptno,a.dname,sum(b.sal),count(b.ename) 
    from dept a
    left join emp b
    on a.deptno=b.deptno
    where a.dname like "%s%"
    group by b.deptno
    
    select d.deptno,d.dname,d.loc,count(e.ename),sum(e.sal)
    from emp e
    right join dept d
    on e.deptno=d.deptno
    where d.dname like "%s%"
    group by d.deptno
    
    select a.deptno,sum(a.sal),count(*) from emp a
    join (select deptno from dept where dname like "%S%") b
    on a.deptno=b.deptno
    group by a.deptno
    
    #34.给任职日期超过30年的员工加薪10%
    update emp set sal=sal*1.1
    where curdate()-hiredate>30
    
    update emp set sal=sal*1.1
    where timestampdiff(year,hiredate,now())
    
    
    

    个人学习如有错误还请指出 感谢!

    展开全文
  • BSQL Hacker是由Portcullis实验室开发的,BSQL Hacker 是一个SQL自动注入工具(支持SQL盲注),其设计的目的是希望能对任何的数据库进行SQL溢出注入。 BSQL Hacker的适用群体是那些对注入有经验的使用者和那些想进行...
  • 主要介绍了避免sql注入,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  •  所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令,比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的,这类...
  • SQL注入攻击的总体思路是: 1.发现SQL注入位置; 2.判断后台数据库类型; 3.确定XP_CMDSHELL可执行情况 4.发现WEB虚拟目录 5. 上传JSP木马; 6.得到管理员权限; 一、SQL注入漏洞的判断 一般来说,SQL注入一般存在于...
  • 这篇文章是来自于B站动力节点的MySQL视频 链接动力节点-MySQL_MySQL教程 不过这个视频可能不是很新了(因为用的是txt排版…) 下面是相关的表数据 mysql> desc dept; (部门表) +--------+-------------+------+---...

    查询

    这篇文章是来自于B站动力节点的MySQL视频 链接动力节点-MySQL_MySQL教程
    不过这个视频可能不是很新了(因为用的是txt排版…)

    下面是相关的表数据

    mysql> desc dept; (部门表)
    +--------+-------------+------+-----+---------+-------+
    | Field  | Type        | Null | Key | Default | Extra |
    +--------+-------------+------+-----+---------+-------+
    | DEPTNO | int(2)      | NO   | PRI | NULL    |       |		部门编号
    | DNAME  | varchar(14) | YES  |     | NULL    |       |		部门名称
    | LOC    | varchar(13) | YES  |     | NULL    |       |		部门位置
    +--------+-------------+------+-----+---------+-------+
    
    mysql> desc emp;(员工表)
    +----------+-------------+------+-----+---------+-------+
    | Field    | Type        | Null | Key | Default | Extra |
    +----------+-------------+------+-----+---------+-------+
    | EMPNO    | int(4)      | NO   | PRI | NULL    |       |	员工编号
    | ENAME    | varchar(10) | YES  |     | NULL    |       |	员工姓名
    | JOB      | varchar(9)  | YES  |     | NULL    |       |	工作岗位
    | MGR      | int(4)      | YES  |     | NULL    |       |	上级领导编号
    | HIREDATE | date        | YES  |     | NULL    |       |	入职日期
    | SAL      | double(7,2) | YES  |     | NULL    |       |	月薪
    | COMM     | double(7,2) | YES  |     | NULL    |       |	补助/津贴
    | DEPTNO   | int(2)      | YES  |     | NULL    |       |	部门编号
    +----------+-------------+------+-----+---------+-------+
    
    mysql> desc salgrade; (工资等级表)
    +-------+---------+------+-----+---------+-------+
    | Field | Type    | Null | Key | Default | Extra |
    +-------+---------+------+-----+---------+-------+
    | GRADE | int(11) | YES  |     | NULL    |       |		等级
    | LOSAL | int(11) | YES  |     | NULL    |       |		最低薪资
    | HISAL | int(11) | YES  |     | NULL    |       |		最高薪资
    +-------+---------+------+-----+---------+-------+
    

    条件查询

    找出工资在1100-3000的员工,包括1100,3000

    select ename,sal from where sal >= 1100 and sal <= 3000;
    select ename,sal from emp where sal between 1100 and 300;//闭区间
    

    可以用于字符串:

    select ename from emp where ename between 'A' and 'C';//左闭右开
    

    找出哪些人津贴为NULL或0:

    select ename,sal,comn from emp where comn is null or comn = 0;//is not null;
    

    注意:运算符的优先级不确定的时候加小括号:

    找出薪资大于1000的并且部门编号是20或30部门的员工。

    select ename,sal,deptno from emp where sal > 1000 and (deptno = 20 or deptno = 30);
    

    in等同于or:表示集合

    找出工作岗位是MANAGER和SALESMAN的员工?

    select ename,job from emp where job = 'SALEMAM' or job = 'MANAGER';
    select ename,job from emp where job in ('SALEMAM','MANAGER');//in后面的值不是区间,是具体的值
    

    not in :不在这几个值当中。

    模糊查询like

    • %代表任意多个字符,_代表任意一个字符
    select ename from emp where ename like '%O%';
    select ename from emp where ename like '%\_%';//转义字符(含_的)
    select ename from emp where ename like '%T';//名字中最后一个为T
    

    排序order by

    默认是升序asc,desc表示降序

    select ename , sal from emp order by sal;
    select ename , sal from emp order by 2;//按第二列排
    select ename , sal from emp order by sal desc;
    

    按照工资的降序排列,当工资相同的时候再按照名字的升序排列:

    select ename,sal from emp order by sal desc , ename asc;//前面的占主导地位,前面相等再调用后面的。
    

    例如:找出工作岗位是SALESMAN的员工,并要求按照薪资的降序排列。

    select 
    	ename,job,sal
    from
    	emp
    where
    	job = 'SALEMAN'
    order by 
    	sal desc;
    

    分组函数

    多行处理函数;都是对“某一组”数据进行操作的。

    • count 计数
      • count(distinct job)–distinct(去重)
    • sum 求和
    • avg 平均
    • max
    • min

    记住:所有的分组函数都是对“某一组”数据进行操作的

    select sum(sal) from emp;==>忽略NULL
    select count(*) from emp;//总人数ename 
    select sum(comm) from emp;
    select sum(comm) from emp where comm is not null; // 不需要额外添加这个过滤条件。sum函数自动忽略NULL。
    
    • 分组函数自动忽略NULL
    • 数学计算含NULL,则结果为NULL
    • ifnull(可能为NULL 的数据,被当作什么处理) ,单行处理函数。
    • 分组函数还有另一个名字:多行处理函数
      多行处理函数的特点:输入多行,最终输出的结果是1行
    //ifnull() 空处理函数
       ifnull(可能为NULL的数据,被当做什么处理) : 属于单行处理函数。
    select ename ,ifnull(comn,0) as comn from emp;
    //计算每个员工的年薪
    select ename ,(sal+ifnull(comn,0))*12 as yearsal from emp;
    

    找出工资大于平均工资的员工:

    select ename,sal from emp where sal > avg(sal);//ERROR

    //1.找出平均工资      2.找出高于平均工资的员工
    select ename ,sal from emp where sal > (select avg(sal) from emp);//子查询
    

    注意:SQL语句中的分组函数不可以直接使用在where子句当中

    ==>因为group by 是在where后面执行

    //一个完整的SQL语句:
    select		5
    	..
    from		1
    	..
    where		2
    	..//无法用分组函数
    group by	3    之后才能用分组函数
    	..
    having 		4
    	..
    order by	6
    	..
    

    count(*)与count(某个字段)

    • count(*) :不是统计某个字段中数据的个数,而是统计总记录条数。(和某个字段无关)

    • count(comn):表示统计comn字段中不为NULL的数据总数量。

    group by 和 having

    group by :按照某个字段或者某些字段进行分组。

    having:对分组后的数据进行再次过滤。

    案例:
    找出每个工作岗位的最高薪资
    select max(sal),job from emp group by job;
    

    注意:

    1. 当一条语句中有group by 的话,select后面只能跟分组函数和参与分组的字段

    2. 分组函数一般都会和group by联合使用,这也是为什么它被称为分组函数的原因。

    3. 并且任何一个分组函数(count sum avg max min)都是在group by语句执行结束之后才会执行的。

    4. 当一条sql语句没有group by的话,整张表的数据会自成一组。

    select ename ,max(sal),job from group by job;//ERROR

    求每个工作岗位的平均薪资:

    select job,avg(sal) from emp group by job;
    

    找出每个部门不同工作岗位的最高薪资:(联合分组)

    select deptno,job,max(sal) from emp group by deptno,job;
    

    找出每个部门的最高的薪资,要求显示薪资大于2500的数据:分步写

    select max(sal),depton from emp group by deptno having max(sal) > 2900;//效率低
    select max(sal),depton from emp where sal > 2900 group by deptno;
    

    建议能够使用where过滤的尽量用where

    找出每个部门的平均薪资,要求显示薪资大于2500的数据(having对分组数据过滤)

    select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
    

    总结一个完整的DQL语句怎么写?

    select			5
    	..
    from			1	
    	..
    where			2
    	..
    group by		3
    	..
    having			4
    	..
    order by		6
    	..
    // limit       7	
    

    关于查询结果集的去重?

    select distinct job from emp; // distinct关键字去除重复job记录。
    

    记住:distinct只能出现在所有字段的最前面。

    统计岗位的数量?

    select count(distinct job) from emp;
    

    连接查询

    笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。

    select 
    	e.ename,d.dname
    from
    	emp e
    join    //inner join
    	dept d
    on
    	e.deptno = d.deptno;
    

    内连接之非等值连接

    INNER JOIN 只会保留两个表都存在的数据(还记得之前的交集吗),这看起来意味着一些数据的丢失,在某些场景下会有问题.

    最大的特点是:连接条件中的关系是非等量关系。

    找出每个员工的工资等级,要求显示员工名、工资、工资等级。

    select 
    	e.ename,e.sal,s.grade
    from
    	emp e
    join            // inner可以省略
    	salgrade s
    on
    	e.sal between s.losal and s.hisal;
    

    自连接

    最大的特点是:一张表看做两张表。自己连接自己

    案例:找出每个员工的上级领导,要求显示员工名和对应的领导名。

    员工的领导编号 = 领导的员工编号

    select 
    	a.ename as '员工名',b.ename as '领导名'
    from
    	emp a
    inner join
    	emp b
    on
    	a.mgr = b.empno;
    

    外连接

    内连接:
    假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。
    AB两张表没有主副之分,两张表是平等的。

    外连接:
    假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。

    外连接的分类?
    左外连接(左连接):表示左边的这张表是主表。
    右外连接(右连接):表示右边的这张表是主表。

    外连接最重要的特点是:主表的数据无条件的全部查询出来

    找出哪个部门没有员工?

    select 
    	d.*
    from
    	emp e
    right join
    	dept d
    on
    	e.deptno = d.deptno
    where
    	e.empno is null;
    

    三张表的连接查询


    A
    join
    B
    join
    C
    on

    表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接。

    案例:找出每一个员工的部门名称以及工资等级:

    select 
    	e.ename,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;
    查询结果:
    +--------+------------+-------+
    | ename  | dname      | grade |
    +--------+------------+-------+
    | SMITH  | RESEARCH   |     1 |
    | ALLEN  | SALES      |     3 |
    | WARD   | SALES      |     2 |
    | JONES  | RESEARCH   |     4 |
    | MARTIN | SALES      |     2 |
    | BLAKE  | SALES      |     4 |
    | CLARK  | ACCOUNTING |     4 |
    | SCOTT  | RESEARCH   |     4 |
    | KING   | ACCOUNTING |     5 |
    | TURNER | SALES      |     3 |
    | ADAMS  | RESEARCH   |     1 |
    | JAMES  | SALES      |     1 |
    | FORD   | RESEARCH   |     4 |
    | MILLER | ACCOUNTING |     2 |
    +--------+------------+-------+
    

    找出每一个员工的部门名称、工资等级、以及上级领导。<把上面的合起来>

    select 
    	e.ename '员工',d.dname,s.grade,e1.ename '领导'
    from
    	emp e
    join
    	dept d
    on
    	e.deptno = d.deptno
    join
    	salgrade s
    on
    	e.sal between s.losal and s.hisal
    left join
    	emp e1
    on
    	e.mgr = e1.empno;
    

    子查询

    1. where子句中使用子查询

    案例:找出高于平均薪资的员工信息。

    select * from emp where sal > (select avg(sal) from emp);
    

    2. from后面嵌套子查询

    找出每个部门平均薪水的等级。

    第一步:找出每个部门平均薪水(按照部门编号分组,求sal的平均值)
    select deptno,avg(sal) as avgsal from emp group by deptno;

    第二步:将以上的查询结果当做临时表t,让t表和salgrade s表连接,条件是:t.avgsal between s.losal and s.hisal

    select 
    	t.*,s.grade
    from
    	(select deptno,avg(sal) as avgsal from emp group by deptno) t
    join
    	salgrade s
    on
    	t.avgsal between s.losal and s.hisal; 
    

    找出每个部门平均的薪水等级。

    第一步:找出每个员工的薪水等级。
    select e.ename,e.sal,e.deptno,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
    第二步:基于以上结果,继续按照deptno分组,求grade平均值。

    select 
    	e.deptno,avg(s.grade)
    from 
    	emp e 
    join 
    	salgrade s 
    on 
    	e.sal between s.losal and s.hisal
    group by
    	e.deptno;
    

    3. 在select后面嵌套子查询

    案例:找出每个员工所在的部门名称,要求显示员工名和部门名。

    select 
    	e.ename,d.dname
    from
    	emp e
    join
    	dept d
    on
    	e.deptno = d.deptno;
    //总和
    select 
    	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
    from 
    	emp e;
    

    union

    (可以将查询结果集相加)

    案例:找出工作岗位是SALESMAN和MANAGER的员工

    select ename,job from emp where job = 'MANAGER'
    union
    select ename,job from emp where job = 'SALESMAN';
    

    limit

    1. limit是mysql特有的,其他数据库中没有,不通用。(Oracle中有一个相同的机制,叫做rownum)

    2. limit取结果集中的部分数据,这时它的作用。

    3. 语法机制:
      limit startIndex, length
      startIndex表示起始位置,从0开始,0表示第一条数据。
      length表示取几个

    通用的标准分页sql

    每页显示3条记录:
    第1页:0, 3
    第2页:3, 3
    第3页:6, 3
    第4页:9, 3
    第5页:12, 3

    每页显示pageSize条记录:

    第pageNo页:(pageNo - 1) * pageSize, pageSize

    pageSize是:是每页显示多少条记录
    pageNo是:显示第几页

    java代码{
    int pageNo = 2; // 页码是2
    int pageSize = 10; // 每页显示10条

    limit (pageNo - 1) * pageSize, pageSize

    }

    展开全文
  • SQL数据库不同的优化方法,SQL命令因为语法简单、操作高效受到了很多用户的欢迎。但是,SQL命令的效率受到不同的数据库功能的限制,特别是在计算时间方面,再加上语言的高效率也不意味着优化会更容易,所以每个...

    SQL数据库不同的优化方法,SQL命令因为语法简单、操作高效受到了很多用户的欢迎。但是,SQL命令的效率受到不同的数据库功能的限制,特别是在计算时间方面,再加上语言的高效率也不意味着优化会更容易,所以每个数据库都需要依据实际情况做特殊调整。

    同时,这也是为什么我们要尽可能多的了解不同的方法来优化或增强数据库,以确保最佳性能的原因。不同职位的开发人员针对SQL数据库的优化也会有所不同,例如在某些方面的优化就会与数据库管理员(DBA)有所不同。在大型公司中,开发人员和数据库管理员往往需要一起工作,而在这些情况下,两队之间很可能发生冲突。所有我们要通过多种方式来调整SQL数据库。

    Java培训教程:SQL数据库不同的优化方法

    1.适当的索引

    索引基本上是一个数据结构,有助于加快整个数据检索过程。唯一索引是一种索引,可以创建不重叠的数据列。正确的索引能够更快地访问数据库,但是过多的索引或无索引反而会造成不好的结果,如果没有索引,那么处理会非常慢。

    2.仅检索相关数据

    指定数据需要检索的精度。使用命令和LIMIT来代替SELECT,调整数据库时,使用需要的数据集来代替整个数据集是非常有必要的,尤其是当你的数据源非常大的时候,指定所需要用的数据集将节省大部分时间。

    命令用于从列指定数据,LIMIT子句可以被用于强制SELECT语句返回指定的记录数。它们两的组合避免了未来系统出现问题,此外,数据集的限定也在一定程度上减少了对数据库的进一步优化。

    3.摆脱相关的子查询

    相关子查询基本上取决于父查询或外部查询。这种搜索是逐行完成的,这意味着它会降低整个过程的速度。通常如果WHERE来自于从外部查询的命令,应用子查询运行每一行,由父查询返回,从而减慢整个进程并降低数据库效率的话,更好的调整数据库的方式是INNERJOIN命令。当然在某些情况下,使用相关的子查询至关重要。

    4.根据要求使用或避免临时表

    如果代码可以以简单的方式写,绝对不要让临时表复杂化。当然,如果一个数据具有需要多次查询的具体程序,在这种情况下,建议使用临时表。临时表通常由子查询来交替。

    5.避免编码循环

    避免编码循环是非常需要的,因为它会拖慢整个序列。通过使用具有单个行的唯一UPDATE或INSERT命令来避免编码循环,并且通过WHERE命令确保不更新存储的数据,以便在找到匹配且预先存在的数据的情况下实现。

    6.执行计划

    优化器创建的执行计划工具在调优SQL数据库方面发挥主要作用。它们也有助于创建适当的索引。虽然它的主要功能是以图形方式显示检索数据的各种方法。但这反过来也有助于创建所需的索引并执行其他所需的步骤来优化数据库。

    当然除此之外还有许多其他方法可以以有效的方式来调整其SQL数据库。

    展开全文
  • 主要介绍了mybatis教程之动态sql语句,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • 005-动力节点-MYSQL视频-SQL语句分类.wmv 004-动力节点-MYSQL视频-表.wmv 003-动力节点-MYSQL视频-MYSQL安装.wmv 002-动力节点-MYSQL视频-DBMS、SQL、DB关系.wmv 001-动力节点-MYSQL视频-概述.wmv SSM框架 目录...
  • 有些时候,sql语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息。使用Oracle的序列、mysql的函数生成Id。...

    有些时候,sql语句where条件中,需要一些安全判断,例如按某一条件查询时如果传入的参数是空,此时查询出的结果很可能是空的,也许我们需要参数为空时,是查出全部的信息。使用Oracle的序列、mysql的函数生成Id。这时我们可以使用动态sql。

    下文均采用mysql语法和函数(例如字符串链接函数CONCAT)。

    selectKey 标签

    在insert语句中,在Oracle经常使用序列、在MySQL中使用函数来自动生成插入表的主键,而且需要方法能返回这个生成主键。使用myBatis的selectKey标签可以实现这个效果。

    下面例子,使用mysql数据库自定义函数nextval('student'),用来生成一个key,并把他设置到传入的实体类中的studentId属性上。所以在执行完此方法后,边可以通过这个实体类获取生成的key。

    select nextval('student')

    INSERT INTO STUDENT_TBL(STUDENT_ID,

    STUDENT_NAME,

    STUDENT_SEX,

    STUDENT_BIRTHDAY,

    STUDENT_PHOTO,

    CLASS_ID,

    PLACE_ID)

    VALUES (#{studentId},

    #{studentName},

    #{studentSex},

    #{studentBirthday},

    #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=orgapacheibatistypeBlobTypeHandler},

    #{classId},

    #{placeId})

    调用接口方法,和获取自动生成key

    StudentEntity entity = new StudentEntity();

    entity.setStudentName("黎明你好");

    entity.setStudentSex(1);

    entity.setStudentBirthday(DateUtil.parse("1985-05-28"));

    entity.setClassId("20000001");

    entity.setPlaceId("70000001");

    this.dynamicSqlMapper.createStudentAutoKey(entity);

    System.out.println("新增学生ID: " + entity.getStudentId());

    selectKey语句属性配置细节:

    属性

    描述

    取值

    keyProperty

    selectKey 语句生成结果需要设置的属性。

    resultType

    生成结果类型,MyBatis 允许使用基本的数据类型,包括String 、int类型。

    order

    1:BEFORE,会先选择主键,然后设置keyProperty,再执行insert语句;

    2:AFTER,就先运行insert 语句再运行selectKey 语句。

    BEFORE

    AFTER

    statementType

    MyBatis 支持STATEMENT,PREPARED和CALLABLE 的语句形式, 对应Statement ,PreparedStatement 和CallableStatement 响应

    STATEMENT

    PREPARED

    CALLABLE

    if标签

    if标签可用在许多类型的sql语句中,我们以查询为例。首先看一个很普通的查询:

    SELECT * from STUDENT_TBL ST

    WHERE ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName}),'%')

    但是此时如果studentName或studentSex为null,此语句很可能报错或查询结果为空。此时我们使用if动态sql语句先进行判断,如果值为null或等于空字符串,我们就不进行此条件的判断,增加灵活性。

    参数为实体类StudentEntity。将实体类中所有的属性均进行判断,如果不为空则执行判断条件。

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    WHERE

    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')

    AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}

    AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}

    AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}

    AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}

    AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}

    使用时比较灵活, new一个这样的实体类,我们需要限制那个条件,只需要附上相应的值就会where这个条件,相反不去赋值就可以不在where中判断。

    public void select_test_2_1() {

    StudentEntity entity = new StudentEntity();

    entity.setStudentName("");

    entity.setStudentSex(1);

    entity.setStudentBirthday(DateUtil.parse("1985-05-28"));

    entity.setClassId("20000001");

    //entity.setPlaceId("70000001");

    List list = this.dynamicSqlMapper.getStudentList_if(entity);

    for (StudentEntity e : list) {

    System.out.println(e.toString());

    }

    }

    if + where 的条件判断

    当where中的条件使用的if标签较多时,这样的组合可能会导致错误。我们以在3.1中的查询语句为例子,当java代码按如下方法调用时:

    @Test

    public void select_test_2_1() {

    StudentEntity entity = new StudentEntity();

    entity.setStudentName(null);

    entity.setStudentSex(1);

    List list = this.dynamicSqlMapper.getStudentList_if(entity);

    for (StudentEntity e : list) {

    System.out.println(e.toString());

    }

    }

    如果上面例子,参数studentName为null,将不会进行STUDENT_NAME列的判断,则会直接导“WHERE AND”关键字多余的错误SQL。

    这时我们可以使用where动态语句来解决。这个“where”标签会知道如果它包含的标签中有返回值的话,它就插入一个‘where'。此外,如果标签返回的内容是以AND 或OR 开头的,则它会剔除掉。

    上面例子修改为:

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')

    AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}

    AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}

    AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}

    AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}

    AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}

    if + set 的更新语句

    当update语句中没有使用if标签时,如果有一个参数为null,都会导致错误。

    当在update语句中使用if标签时,如果前面的if没有执行,则或导致逗号多余错误。使用set标签可以将动态的配置SET 关键字,和剔除追加到条件末尾的任何不相关的逗号。

    使用if+set标签修改后,如果某项为null则不进行更新,而是保持数据库原值。如下示例:

    UPDATE STUDENT_TBL

    STUDENT_TBL.STUDENT_NAME = #{studentName},

    STUDENT_TBL.STUDENT_SEX = #{studentSex},

    STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},

    STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},

    STUDENT_TBL.CLASS_ID = #{classId}

    STUDENT_TBL.PLACE_ID = #{placeId}

    WHERE STUDENT_TBL.STUDENT_ID = #{studentId};

    if + trim代替where/set标签

    trim是更灵活的去处多余关键字的标签,他可以实践where和set的效果。

    trim代替where

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')

    AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}

    AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}

    AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}

    AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}

    AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}

    trim代替set

    UPDATE STUDENT_TBL

    STUDENT_TBL.STUDENT_NAME = #{studentName},

    STUDENT_TBL.STUDENT_SEX = #{studentSex},

    STUDENT_TBL.STUDENT_BIRTHDAY = #{studentBirthday},

    STUDENT_TBL.STUDENT_PHOTO = #{studentPhoto, javaType=byte[], jdbcType=BLOB, typeHandler=org.apache.ibatis.type.BlobTypeHandler},

    STUDENT_TBL.CLASS_ID = #{classId},

    STUDENT_TBL.PLACE_ID = #{placeId}

    WHERE STUDENT_TBLSTUDENT_ID = #{studentId}

    choose (when, otherwise)

    有时候我们并不想应用所有的条件,而只是想从多个选项中选择一个。而使用if标签时,只要test中的表达式为true,就会执行if标签中的条件。MyBatis提供了choose 元素。if标签是与(and)的关系,而choose比傲天是或(or)的关系。

    choose标签是按顺序判断其内部when标签中的test条件出否成立,如果有一个成立,则choose结束。当choose中所有when的条件都不满则时,则执行otherwise中的sql。类似于Java 的switch 语句,choose为switch,when为case,otherwise则为default。

    例如下面例子,同样把所有可以限制的条件都写上,方面使用。choose会从上到下选择一个when标签的test为true的sql执行。安全考虑,我们使用where将choose包起来,放置关键字多于错误。

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%')

    AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER}

    AND ST.STUDENT_BIRTHDAY = #{studentBirthday, jdbcType=DATE}

    AND ST.CLASS_ID = #{classId, jdbcType=VARCHAR}

    AND ST.CLASS_ID = #{classEntity.classId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeId, jdbcType=VARCHAR}

    AND ST.PLACE_ID = #{placeEntity.placeId, jdbcType=VARCHAR}

    AND ST.STUDENT_ID = #{studentId, jdbcType=VARCHAR}

    foreach

    对于动态SQL 非常必须的,主是要迭代一个集合,通常是用于IN 条件。List 实例将使用“list”做为键,数组实例以“array” 做为键。

    foreach元素是非常强大的,它允许你指定一个集合,声明集合项和索引变量,它们可以用在元素体内。它也允许你指定开放和关闭的字符串,在迭代之间放置分隔符。这个元素是很智能的,它不会偶然地附加多余的分隔符。

    注意:你可以传递一个List实例或者数组作为参数对象传给MyBatis。当你这么做的时候,MyBatis会自动将它包装在一个Map中,用名称在作为键。List实例将会以“list”作为键,而数组实例将会以“array”作为键。

    这个部分是对关于XML配置文件和XML映射文件的而讨论的。下一部分将详细讨论Java API,所以你可以得到你已经创建的最有效的映射。

    参数为array示例的写法

    接口的方法声明:

    public List getStudentListByClassIds_foreach_array(String[] classIds);

    动态SQL语句:

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    WHERE STCLASS_ID IN

    #{classIds}

    测试代码,查询学生中,在20000001、20000002这两个班级的学生:

    @Test

    public void test7_foreach() {

    String[] classIds = { "20000001", "20000002" };

    List list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_array(classIds);

    for (StudentEntity e : list) {

    System.out.println(e.toString());

    }

    }

    参数为list示例的写法

    接口的方法声明:

    public List getStudentListByClassIds_foreach_list(List classIdList);

    动态SQL语句:

    SELECT ST.STUDENT_ID,

    ST.STUDENT_NAME,

    ST.STUDENT_SEX,

    ST.STUDENT_BIRTHDAY,

    ST.STUDENT_PHOTO,

    ST.CLASS_ID,

    ST.PLACE_ID

    FROM STUDENT_TBL ST

    WHERE STCLASS_ID IN

    #{classIdList}

    测试代码,查询学生中,在20000001、20000002这两个班级的学生:

    @Test

    public void test7_2_foreach() {

    ArrayList classIdList = new ArrayList();

    classIdList.add("20000001");

    classIdList.add("20000002");

    List list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_list(classIdList);

    for (StudentEntity e : list) {

    System.out.println(e.toString());

    }

    }

    展开全文
  • 什么是动态SQL? 动态SQL即为SQL语句的拼接,形成较为复杂的SQL语句,以此达到用户的查询目的。动态SQL标签有<if> <where> <choose> <foreach> 等 <if>标签 当test的值为true时,...
  • │037-where后面嵌套子查询.avi│038-from后面嵌套子查询.avi│039-select后面嵌套子查询.avi│040-union的用法.avi│041-limit以及通用分页SQL.avi│042-表的创建.avi│043-向表中插入数据.avi│044-向表中插入数据...
  • bjpowernode.sql

    2020-12-28 20:40:19
    动力节点 sql bjpowernode.sql
  •  002-动力节点-MYSQL视频-DBMS、SQL、DB关系.wmv  003-动力节点-MYSQL视频-MYSQL安装.wmv  004-动力节点-MYSQL视频-表.wmv  005-动力节点-MYSQL视频-SQL语句分类.wmv  006-动力节点-MYSQL视频-数据准备.wmv...
  • 动力节点MySQL数据库视频 百度云 网盘 下载集数合计:86集视频教程详情描述:A005《动力节点MySQL数据库视频》课程目录:001-动力节点-MYSQL视频-概述.wmv002-动力节点-MYSQL视频-DBMS、SQL、DB关系.wmv003-动力节点-...
  • 教程名称: 【动力节点】Oracle从入门到精通视频教程_数据库实战精讲本套Java视频中讲解了Oracle数据库基础、搭建Oracle数据库环境、SQL*Plus命令行工具的使用、标准SQL、Oracle数据核心-表空间、Oracle数据库常用...
  • 文章目录第一章 MySQL基础MySQL的安装与配置DB、DBMS、SQL的关系表(table)SQL语句的分类导入数据第二章 MySQL高级第三章 MySQL优化第四章 34道作业题 第一章 MySQL基础 MySQL的安装与配置 下载 MySQL Installer ...
  • 教程名称: 【动力节点】Java经典教程_JDBC视频教程 本套Java视频教程中讲解了Java编程语言如何连接数据库,对数据库中的数据进行增删改查操作,适合于已经学习过Java编程基础以及数据库的同学。Java教程中阐述了...
  • 常见的聚合操作跟sql server一样,有:count,distinct,group,mapReduce。 <1> count count是最简单,最容易,也是最常用的聚合工具,它的使用跟我们C#里面的count使用简直一模一样。 <2> distinct 这个操作相信...
  • 我们都知道redis追求的是简单,快速,高效,在这种情况下也就拒绝了支持window平台,学sqlserver的时候,我们知道事务还算是个比较复杂的东西,所以这要是照搬到redis中去,理所当然redis就不是那么简单纯碎的东西了...
  • MySQL Oracle PLSQL SQL优化等各种数据库教程视频,资料+源码
  • 我们知道sql server能够做到读写分离,双机热备份和集群部署,当然mongodb也能做到,实际应用中我们不希望数据库采用单点部署,如果碰到数据库宕机或者被毁灭性破坏那是多么的糟糕。 一:主从复制 1: 首先看看模型...
  • 教程名称: 【动力节点】Java项目实战视频教程之EGov(外汇)项目该项目纯授课时间为21天,包含大部分JAVA WEB知识。压缩包内部包含了PD数据库建模文件,项目数据初始化文件,sql源文件,最终版本源代码项目包,...
  • 今天遇到了一个比较有意思的问题,从服务器上封装好的java.sql.timestamp对象返回到本地客户端程序后与数据库中的时间相差了整整14个小时。因为跟客户的时差是14个小时,所以大体怀疑是时差问题。
  • JDBC (B站动力节点杜老师) 1.JDBC是什么 2.jdbc的本质是什么? 多态: ​ Animal a = new Cat ( ) --》面向抽象编程 父类型的引用 指向 子类型的对象 3.开发前准备工作 4.jdbc 过程 注册驱动方式两种 ...

空空如也

空空如也

1 2 3 4
收藏数 79
精华内容 31
热门标签
关键字:

动力节点sql