精华内容
下载资源
问答
  • 高级查询

    2018-06-11 17:05:24
    高级查询后台操作步骤: 1)在ProductDAO接口中编写高级查询的方法 public interface ProductDAO { /** * 高级查询 * @param name 商品名称:productName LIKE '%name值%' * @param minSalePrice 最低价格...

    高级查询后台操作步骤:

    1)在ProductDAO接口中编写高级查询的方法

    public interface ProductDAO {
    	/**
    	 * 高级查询
    	 * @param name              商品名称:productName LIKE '%name值%'
    	 * @param minSalePrice      最低价格:salePrice>minPrice
    	 * @param maxSalePrice      最高价格:salePrice<maxPrice
    	 * @return
    	 */
    	List<Product> query(String name,BigDecimal minSalePrice,BigDecimal maxSalePrice);
    	
    }

    2)

    public class ProductDAOImpl implements ProductDAO{
    
    	public List<Product> query(String name, BigDecimal minSalePrice, BigDecimal maxSalePrice) {
    		StringBuilder sql=new StringBuilder(80);
    		sql.append("SELECT * FROM product WHERE 1=1 ");
    		//封装占位符参数
    		List<Object> parameters=new ArrayList<>();
    		//商品名称
    		if(StringUtils.isNoneBlank(name)) {
    			sql.append("AND productName LIKE ?");
    			parameters.add("%"+name+"%");
    		}
    		//最低价格
    		if(minSalePrice!=null) {
    			sql.append("AND salePrice>=?");
    			parameters.add(minSalePrice);
    		}
    		//最高价格
    		if(maxSalePrice!=null) {
    			sql.append("AND salePrice<=?");
    			parameters.add(maxSalePrice);
    		}
    		System.out.println("SQL="+sql);
    		System.out.println("参数="+parameters);
    		return JdbcTemplate.query(sql.toString(), new BeanListHandler<>(Product.class), parameters.toArray());
    	}
    

     

    @Test
    	public void testQuery() {
    			List<Product> list=dao.query("鼠", new BigDecimal("50"),null);
    			System.out.println(list.size());
    			for(Product p:list) {
    				System.out.println(p.getId()+p.getBrand());
    			}
    		
    	}

    缺陷:如果查询条件过多,此时参数就很多-->把多个查询条件封装到查询对象QueryObject中

     

    缺陷:在上述查询过程中,为了避免判断哪一个条件是第一个,而使用了where 1=1

              但是使用where 1=1会影响性能,因为不能使用索引来查询了

              可以手动判断但是可以使用一种更方便的:使用Apache common-lang组件中的StringUtils.join方法,

    把集合中每一个元素使用特定的字符串给连接起来。

    public class ProductQueryObject {
    	private String name;
    	private BigDecimal minSalePrice;
    	private BigDecimal maxSalePrice;
    	public ProductQueryObject(String name, BigDecimal minSalePrice, BigDecimal maxSalePrice) {
    		this.name = name;
    		this.minSalePrice = minSalePrice;
    		this.maxSalePrice = maxSalePrice;
    	}
    	public ProductQueryObject() {
    	
    	}
    	public String getName() {
    		return name;
    	}
    	public void setName(String name) {
    		this.name = name;
    	}
    	public BigDecimal getMinSalePrice() {
    		return minSalePrice;
    	}
    	public void setMinSalePrice(BigDecimal minSalePrice) {
    		this.minSalePrice = minSalePrice;
    	}
    	public BigDecimal getMaxSalePrice() {
    		return maxSalePrice;
    	}
    	public void setMaxSalePrice(BigDecimal maxSalePrice) {
    		this.maxSalePrice = maxSalePrice;
    	}
    	//参数集合
    	private List<Object> parameters=new ArrayList<>();
    	//查询条件集合
    	private List<String> conditions=new ArrayList<>();
    	//返回查询条件
    	public String getQuery() {
    		StringBuilder sql=new StringBuilder(80);
    		//商品名称
    		if(StringUtils.isNotBlank(name)) {
    			conditions.add("productName LIKE ?");
    			parameters.add("%"+name+"%");
    		}
    		//最低价格
    		if(minSalePrice!=null) {
    			conditions.add("salePrice>=?");
    			parameters.add(minSalePrice);
    		}
    		//最高价格
    		if(maxSalePrice!=null) {
    			conditions.add("SalePrice<=?");
    			parameters.add(maxSalePrice);
    		}
    		if(conditions.size()==0) {
    			return "";
    		}
    		String queryString=StringUtils.join(conditions.toArray(), " AND ");
    		return sql.append(" WHERE ").append(queryString).toString();
    	}
    	
    	//返回查询条件中的占位符参数值
    	public List<Object> getParameters(){
    		return parameters;
    	}
    public List<Product> query(ProductQueryObject qo) {
    		String sql="SELECT * FROM product"+qo.getQuery();  
    		System.out.println(sql);
    		return JdbcTemplate.query(sql, new BeanListHandler<>(Product.class), qo.getParameters().toArray());
    	}

     

    高级查询的前台设计

     

    在list.jsp中增加表单

    <form action="product" method="post">
    		商品名称<input type="text" name="name" value="${qo.name }"><br>
    		商品价格<input type="text" name="minSalePrice" value="${qo.minSalePrice }">到<input type="text" name="maxSalePrice" value="${qo.maxSalePrice }">
    		<input type="submit" value="提交查询" style="background-color:blue">
    </form>
    public class ProductServlet2 extends HttpServlet{
    	
    	private static final long serialVersionUID = 1L;
    	private ProductDAO2 dao;
    	private ProductDirDAO2 dirDAO;
    	public void init() {
    		dao=new ProductDAOImpl2();
    	}
    	@Override
    	protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    		req.setCharacterEncoding("UTF-8");
    		ProductQueryObject qo=new ProductQueryObject(); 
    		this.reqToObject(qo,req);
    		req.setAttribute("qo", qo);
    		List<Product> products=dao.query(qo);
    		req.setAttribute("products", products);//
    	    req.getRequestDispatcher("/WEB-INF/list.jsp").forward(req, resp);
    	}
    	
    	
    	private void reqToObject(ProductQueryObject qo, HttpServletRequest req) {
    		String name=req.getParameter("name");
    		String minSalePrice=req.getParameter("minSalePrice");
    		String maxSalePrice=req.getParameter("maxSalePrice");
    		if(StringUtils.isNotBlank(name)) {
    			qo.setName(name);
    		}
    		if(StringUtils.isNotBlank(minSalePrice)) {
    			qo.setMinSalePrice(new BigDecimal(minSalePrice));
    		}
    		if(StringUtils.isNotBlank(maxSalePrice)) {
    			qo.setMaxSalePrice(new BigDecimal(maxSalePrice));
    		}
    	}
    	
    }
    

     

     

     

     

     

    展开全文
  • Oracle高级查询

    千次阅读 2016-11-06 23:49:10
    概述高级查询在数据库的开发过程中应用广泛,本博文将从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询,最后典型案例的应用。sql窗口修改已执行的sqled表示编辑上个sql / 表示执行上个sql分组查询分组...

    概述

    高级查询在数据库的开发过程中应用广泛,本博文将从分组查询、多表查询和子查询三个方面介绍Oracle的高级查询,最后典型案例的应用。

    sql窗口修改已执行的sql

    ed表示编辑上个sql
    / 表示执行上个sql

    这里写图片描述


    分组查询

    分组函数的概念

    分组函数作用于一组数据,并对一组数据返回一个值。

    这里写图片描述


    分组函数的语法

    这里写图片描述


    常用分组函数

    • avg
    • sum
    • min
    • max
    • count
    • wm_contact 行转列

    更多及用法请参考oracle函数


    常见分组函数的使用

    avg()/sum()

    求出员工的平均工资和工资总和。

    SQL> select avg(sal) , sum(sal) from emp ;
     
      AVG(SAL)   SUM(SAL)
    ---------- ----------
    2073.21428      29025
     
    

    min()/max()

    SQL> select min(sal), max(sal) from emp;
     
      MIN(SAL)   MAX(SAL)
    ---------- ----------
           800       5000
     
    SQL> 
    

    count()

    SQL> select count(1) from emp;
     
      COUNT(1)
    ----------
            14
    

    distinct 关键字

     
    SQL> select distinct(deptno)  from emp;
     
    DEPTNO
    ------
        30
        20
        10
    

    wm_concat()行转列

    SQL> select deptno   部门 ,wm_concat(ename) 部门总的员工 from emp  group by deptno;
     
      部门 部门总的员工
    ---- --------------------------------------------------------------------------------
      10 CLARK,MILLER,KING
      20 SMITH,FORD,ADAMS,SCOTT,JONES
      30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
     
    

    这里写图片描述

    wm_concat不同版本的区别

    这里写图片描述

    10.2.0.4以前,wm_concat返回的是varchar2,10.2.0.5开始,是CLOB.


    nvl()/nvl2()

    分组函数会自动忽略空值, nvl()函数可以使分组函数不忽略空值

    NVL (expr1, expr2)
    【功能】若expr1为NULL,返回expr2;expr1不为NULL,返回expr1。

    NVL2 (expr1, expr2, expr3)
    【功能】expr1不为NULL,返回expr2;expr2为NULL,返回expr3。
    expr2和expr3类型不同的话,expr3会转换为expr2的类型


    group by

    这里写图片描述


    语法

    这里写图片描述

    • 在select列表中所有未包含在函数中的列都应该包含在group by子句中,否则会抛出 ora-00937 not a singel-group group function。
    select  a, b, c ,avg(d)
    from table_name 
    group by a, b ,c ;
    
    • 包含在group by子句中的列,不必包含在select列表中。
    select avg(sal) 
    from emp 
    group by deptno;
    

    使用多个列分组

    这里写图片描述

    按照部门、不同的职位,统计员工的工资总和。

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

    这里写图片描述

    先按照deptno分组,再按照job分组,如果都一样,则是同一组数据。


    过滤分组-having子句的使用以及和where的区别

    这里写图片描述


    having子句语法

    这里写图片描述


    having子句和where的区别

    • where子句中不能使用组函数,having后可以使用;
    • 当where和having可以通用的情况下,优先使用where,效率更高
      where 先过滤后分组
      having 先分组后过滤
      优先使用where

    举例:
    这里写图片描述


    在分组函数中使用order by

    这里写图片描述

    select deptno , avg(sal) 
    from emp 
    group by deptno
    order by avg(sal) ;--按表达式排序
    
    select deptno , avg(sal) 平均工资 
    from emp 
    group by deptno
    order by 平均工资  ;--按别名排序
    
    
    select deptno , avg(sal) 平均工资 
    from emp 
    group by deptno
    order by 2  ; --按序号排序,表示第二列。 如果只有2列,不能出现比2大的值
    

    分组函数的嵌套

    栗子: 求部门平均工资的最大值

    1. 先求出部门的平均工资
    2. 再求出平均工资中的最大值
    select max(avg(sal)) from emp group by deptno;
    

    包含在group by子句中的列,不必包含在select列表中。


    group by语句的增强

    这里写图片描述

    分析一下这个报表

    第一个红框内的是  按照部门和职位统计 工作总和
    select deptno,job,sum(sal) from emp group by deptno,job order by deptno;
    
    第二个小篮筐是 部门工资的总和
    select deptno ,sum(sal) from  emp group by deptno order by deptno;
    
    第三个总计是 工资总和 
    
    select sum(sal) from emp ;
    
    
    

    整合一下:

    select  * from (
    select deptno , job, sum(sal) from emp group by deptno ,  job
    union all 
    select deptno ,null ,sum(sal) from  emp group by deptno
    union all 
    select  null ,null ,sum(sal) from emp  )  order by deptno, job ;
    

    这里写图片描述

    我们可以通过oracle提供的rollup函数来简化书写的过程。

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

    可以得到同样的结果

    这里写图片描述


    理解rollup

    rollup官方文档

    select a, b, c, sum( d )
    from t
    group by rollup(a, b, c);
    

    等效于

    select * from (
    select a, b, c, sum( d ) from t group by a, b, c 
    union all
    select a, b, null, sum( d ) from t group by a, b
    union all
    select a, null, null, sum( d ) from t group by a
    union all
    select null, null, null, sum( d ) from t
    )
    

    引申 GROUPING SETS 、 CUBE 、GROUPING

    数据

    create table students
    (id number(15,0),
    area varchar2(10),
    stu_type varchar2(2),
    score number(20,2));
    insert into students values(1, '111', 'g', 80 );
    insert into students values(1, '111', 'j', 80 );
    insert into students values(1, '222', 'g', 89 );
    insert into students values(1, '222', 'g', 68 );
    insert into students values(2, '111', 'g', 80 );
    insert into students values(2, '111', 'j', 70 );
    insert into students values(2, '222', 'g', 60 );
    insert into students values(2, '222', 'j', 65 );
    insert into students values(3, '111', 'g', 75 );
    insert into students values(3, '111', 'j', 58 );
    insert into students values(3, '222', 'g', 58 );
    insert into students values(3, '222', 'j', 90 );
    insert into students values(4, '111', 'g', 89 );
    insert into students values(4, '111', 'j', 90 );
    insert into students values(4, '222', 'g', 90 );
    insert into students values(4, '222', 'j', 89 );
    commit;
    
    
    

    GROUPING SETS

    GROUPING SETS官方文档

    select id,area,stu_type,sum(score) score 
    from students
    group by grouping sets((id,area,stu_type),(id,area),id)
    order by id,area,stu_type;
    
    

    理解grouping sets

    select a, b, c, sum( d ) from t
    group by grouping sets ( a, b, c )
    

    等效于

    select * from (
    select a, null, null, sum( d ) from t group by a
    union all
    select null, b, null, sum( d ) from t group by b 
    union all
    select null, null, c, sum( d ) from t group by c 
    )
    

    CUBE

    CUBE 官方文档

    select id,area,stu_type,sum(score) score 
    from students
    group by cube(id,area,stu_type)
    order by id,area,stu_type;
    
    

    理解cube

    select a, b, c, sum( d ) from t
    group by cube( a, b, c)
    

    等效于

    select a, b, c, sum( d ) from t
    group by grouping sets( 
    ( a, b, c ), 
    ( a, b ), ( a ), ( b, c ), 
    ( b ), ( a, c ), ( c ), 
    () )
    
    

    GROUPING

    GROUPING官方文档

    从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null。

    如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!

    select decode(grouping(id),1,'all id',id) id,
    decode(grouping(area),1,'all area',to_char(area)) area,
    decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,
    sum(score) score
    from students
    group by cube(id,area,stu_type)
    order by id,area,stu_type; 
    

    多表连接

    多表查询

    这里写图片描述


    笛卡尔积

    这里写图片描述

    这里写图片描述

    • 列等于两个表列数的和
    • 行等于两个表行数的乘积

    笛卡尔积中并不是全部正确的数据,要根据连接条件进行筛选。

    比如刚才的dept和emp, 满足连接条件emp.deptno=dept.deptno才是正确的数据。

    在实际运行环境下,应避免使用笛卡儿积全集。
    连接条件至少有n-1个。


    ORACLE表的四种连接方式

    等值连接

    通过两个表具有相同意义的列,可以建立相等连接条件。
    只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。

    这里写图片描述

    select a.empno, a.ename, a.sal, b.dname
      from emp a, dept b
     where a.deptno = b.deptno;
    

    这里写图片描述


    不等值连接

    两个表中的相关的两列进行不等连接,
    比较符号一般为>,<,…,between… and…(小值在前 大值灾后)

    这里写图片描述

    select e.empno, e.ename, e.sal, s.grade
      from emp e, salgrade s
     where e.sal between s.losal and s.hisal;
    

    这里写图片描述


    外连接

    对于外连接,Oracle中可以使用“(+)”来表示,还可以使用LEFT/RIGHT/FULL OUTER JOIN 。

    外连接就是为了解决:通过外链接,把对于连接条件上不成立的记录,仍然包含在最后的结果中.


    右外连接

    A) 左条件(+) = 右条件;
      代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。

    此时也称为"右外连接".另一种表示方法是:

     SELECT ... FROM1 RIGHT OUTER JOIN2 ON 连接条件
    

    出现在表2中的字段,如果表1不存在该值,依然输出


    左外连接

    B) 左条件 = 右条件(+);
      代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。
     
      此时也称为"左外连接"

     SELECT ... FROM1 LEFT OUTER JOIN2 ON 连接条件
    

    存在表1的数据,如果表2不存在,依然输出


    这里写图片描述

    数据说明:
    部门表有个id为40的部门。而员工表中却没有deptno=40的员工。

    
    select   p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  ,dept p
    where e.deptno (+)= p.deptno --右外连接 , 显示右边的表 不能匹配的信息   注意等号两侧表的顺序
    group by   p.deptno , p.dname
    order by p.deptno;
    
    
    select   p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  ,dept p
    where  p.deptno = e.deptno (+) --左外连接 , 显示左边的表 不能匹配的信息  注意等号两侧表的顺序
    group by   p.deptno , p.dname
    order by p.deptno;
    
    
    
    select  p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from emp e  right  join dept p  on e.deptno = p.deptno 
     group by   p.deptno , p.dname
    order by p.deptno;
    
    ---不推荐这样写,因为dept不是主表。
    select  p.deptno  部门号, p.dname 部门名称, count(e.empno) 部门人数
    from dept p  left  join  emp e  on e.deptno = p.deptno 
     group by   p.deptno , p.dname
    order by p.deptno;
    
    

    这里写图片描述


    自连接

    自连接核心:通过别名,将同一张表视为多张表 ,多表做笛卡儿相等连接。

    这里写图片描述

    数据说明 mgr字段
    这里写图片描述

    select a.empno 员工工号,
           a.ename 员工姓名,
           a.mgr   领导工号,
           b.empno 领导工号,
           b.ename 领导姓名
      from emp a
      join emp b
        on a.mgr = b.empno
     order by a.empno;
    

    这里写图片描述


    自连接存在的问题和解决办法

    问题:不适合大量数据的表

    自连接不适合大量数据的表:因为查询同一个表看做多个表,他们的笛卡尔全集的记录数至少为 行数的平方 。如果看做3个表这是立方关系。假设emp有1亿条数据…做自连接的话 可想而知

    解决办法:层次查询connetct by
    层次查询概述

    oracle中的select语句可以用START WITH…CONNECT BY PRIOR子句实现递归查询,connect by 是结构化查询中用到的.

    基本语法:
    SELECT [LEVEL],column,expression, ...
    
    FROM table
    
    [WHERE conditions]
    
    [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];
    
    
    • level是伪列,代表树的层级,根节点level为1,子节点为2等。

    • from后面只能是一个表或一个视图。

    • where条件可以限制查询返回的行,但不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响.注意,彻底剪枝条件应放在connect by(connect by之后也可跟过滤条件,它将该条件节点后的所有子孙后代一并去除不显示,如:connect by prior employee_id=manager_id and employee_id>10),单点剪掉条件应放在where子句(入层后不输出)。因为connect by的优先级要高于where,也就是sql引擎先执行connect by。

    • start_condition定义层次化查询的起点,如employee_id=1。

    • prior_condition定义父行和子行之间的关系,如父子关系定义为employee_id=manager_id,表示父节点employee_id和子节点manager_id之间存在关系。如果不加PRIOR关键字则不会进行递归,只是相当于一个过滤条件,只能得到根节点。

    另外,该关键字可放在前列前,也可放在后列前,放在哪列前哪列就是根节点

    如果connect by prior中的prior被省略,则查询将不进行深层递归,只能得到根节点。

    这里写图片描述

    select
     level,--是oracle中的伪列,其实在emp表中并没有该字段
     empno, 
     ename, 
     sal, 
     mgr
      from emp
     start with mgr is null  /**只有根节点可以用 is null 这种写法 ,或者 empno=7839 也表示是从根节点开始 .当然也可以从任意节点开始遍历,获取特定子树 .比如遍历  JONES下面的所有子节点 empno=7566**/
     connect by prior empno = mgr
     order by 1;--按照第一个字段排序 即按照levle排序
    

    connect by prior empno = mgr 等号左右两侧的字段顺序不要搞反了。表示父节点empno 和子节点mgr 之间存在关系。

    这里写图片描述

    connect by生成序列:

    如生成一个1到10的序列:

    SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
    

    其原理在于:省略start with则以所以点为根节点,而dual表只有一行所有只有一个节点,而connect by则对所有输入内容进行遍历。

    上面的方法受制于rownum伪列的限制,想得到指定始尾的序列我们也可以借助level伪列,如:select level from dual where level >= 5 connect by level <= 10;

    自连接和层次查询各有利弊,看使用场景。层次查询:不存在多表查询,但是查询结果没有自查询直观。


    子查询

    子查询概述

    这里写图片描述


    语法

    这里写图片描述


    分类

    子查询分为 单行子查询 和 多行子查询


    这里写图片描述

    select * from emp where sal>(select sal from emp where ename='SCOTT') 
    

    子查询需要注意的10个问题

    1. 不要忘记子查询语法中的小括号
    2. 形成良好的子查询的书写风格
    3. 可以使用子查询的位置:Where,select,having,from
    4. 不可以使用子查询的位置:group by
    5. 强调:from后面的子查询
    6. 主查询和子查询可以不是一张表
    7. 一般不在自查询中,使用排序;但是在Top-N分析问题中,必须对子查询排序
    8. 一般先执行子查询,再执行主查询;但相关子查询例外
    9. 单行子查询只能使用单行操作符;多行子查询只能多行操作符
    10. 注意:子查询中是Null值的问题

    子查询语法中的小括号问题

    子查询必须有小括号,否则会抛出ora-00936 :missing expression

    这里写图片描述


    子查询的书写风格问题

    注意换行和缩进


    可以使用子查询的位置

    可以使用子查询的位置:Where,select,having,from

    where:

    select  * 
    from emp 
    where sal > (select sal 
                           from emp 
                           where ename = 'SCOTT');
    

    select:

    这里写图片描述


    having:

    select   deptno , avg(sal)
    	from emp 
    		group by deptno
    			having avg(sal) > (select max(sal)
                       from emp
    	                   where deptno=30);
    

    having不能换成where, 因为where后面不能使用分组函数。


    from:

    select * 
    	from (
           select a.empno ,a.ename ,a.deptno 
    	       from emp a);
    

    不可以使用子查询的位置

    不可以在group by 后使用子查询

    这里写图片描述


    from后面的子查询

    这里写图片描述


    这里写图片描述

    select *
    from (select empno,ename,sal from emp);
    

    这里写图片描述

    select * 
    from (select empno,ename,sal,12*sal 年薪 from emp);
    

    主查询和子查询可以不是同一张表

    这里写图片描述

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

    当然也可以使用多表查询的方式:

    select e.*
    from emp e,dept d
    where e.deptno=d.deptno and d.dname='SALES' ;
    

    理论上应该尽量使用多表查询,因为上面的子查询有两个from语句,所以要对数据库访问查询两次,而下面的多表查询只访问了一次!这是理论上的结论,并没有考虑实际比如多表查询中产生的笛卡尔积的大小,具体情况还是要具体对待。


    子查询的排序问题

    这里写图片描述

    select rownum,empno,ename,sal
    from (select * from emp order by sal desc)
    where rownum<=3;
    

    rownum 行号,oracle提供的伪列。

    将排序后的表作为一个集合放到from()中 生成一个新表
    重新再查询rownum 就可以让rownum也实现排序了


    行号需要注意的两个问题

    • 1、行号永远按照默认的顺序生成
    • 2、行号只能使用<,<=;不能使用>,>=

    针对1的情况 举个栗子:
    这里写图片描述

    我们按照工资排序下,在看下rownum的顺序

    这里写图片描述

    即使用order by排序,也不会打乱rownum默认生成行号的顺序 。


    针对2的情况 举个栗子:

    这里写图片描述

    可以看到 当使用rownum >号时,获取到的结果为空。


    主查询和子查询的执行顺序

    一般先执行子查询,再执行主查询;但相关子查询例外。

    那什么是相关子查询呢?

    相关子查询的典型结构如下:

    select columnlist
      from table1 t1
     where column2 in 
     (select column3 from table2 t2 where t2.column3 =     t1.column4)
    

    也就是说在子查询中使用到了外查询的表和相关的列。

    这样无法像嵌套子查询一样一次将子查询的结果计算出来然后再和外查询挨个比对,相关子查询对于外部查询的每一个值都会有一个结果与其对应,其计算的过程是这样的:

    • 1.扫描外查询的第一条记录
    • 2.扫描子查询,并将第一条记录的对应值传给子查询,由此计算出子查询的结果
    • 3.根据子查询的结果,返回外查询的结果。
    • 4.重复上述动作,开始扫描外查询的第二条记录,第三条记录,直至全部扫描完毕

    这里写图片描述

    select empno,
           ename,
           sal,
           (select avg(sal) from emp where deptno = e.deptno) avgsal
      from emp e
     where sal > (select avg(sal) from emp where deptno = e.deptno);
    
    

    这里写图片描述


    单行子查询和多行子查询

    单行子查询:插叙结果为一个
    多行查询:查询结果为两个或两个以上

    单行子查询可以使用单行操作符(也可以使用in啊)。
    多行子查询只能使用多行操作符。

    这里写图片描述

    单行操作符
    这里写图片描述


    多行子查询
    这里写图片描述

    多行操作符
    这里写图片描述


    单行子查询栗子

    这里写图片描述

    select * from emp e 
           where e.job = (select job from emp b where b.empno=7566)
                and 
                e.sal > (select sal from emp c where c.empno=7782)
    

    这里写图片描述


    这里写图片描述

    select deptno, min(sal)
      from emp
     group by deptno
    having min(sal) > (select min(sal) from emp b where b.deptno = 20)
    

    这里写图片描述


    非法使用单行操作符

    这里写图片描述


    多行子查询栗子

    多行操作符 in
    这里写图片描述

    这里写图片描述

    多行操作符 any

    这里写图片描述

    找出员工中,只要比部门号为30的员工中的任何一个员工的工资高的员工信息。也就是说只要比部门号为30的员工中的那个工资最少的员工的工资高就满足条件。

    any取的是集合的最小值。

    select *
      from emp
     where sal > any (select sal from emp b where b.deptno = 30);
    

    或者
    单行操作符表示

    select *
      from emp
     where sal > any (select sal from emp b where b.deptno = 30);
    

    这里写图片描述

    多行操作符 all

    这里写图片描述

    max取的是集合的最大值。

    
    select *
      from emp
     where sal >  all (select sal from emp b where b.deptno = 30);
    
    select *
      from emp
     where sal >  (select max(sal) from emp b where b.deptno = 30);
    

    这里写图片描述


    子查询中的空值问题 null

    单行子查询的null问题

    这里写图片描述


    多行子查询的null问题

    先看下emp的数据

    这里写图片描述

    in相当于 =ANY
    not in 相当于 <>ALL(其中如果子查询返回值有NULL,则<>NULL当然没有结果)

    ORACLE官方文档:
    这里写图片描述

    这里写图片描述

    select * from emp where empno not in (select mgr from emp where mgr is not null);
    

    这里写图片描述


    案例

    案例1

    这里写图片描述

    select  rn , empno, ename, sal
      from (select rownum rn, empno, ename, sal
              from (select empno, ename, sal from emp order by sal desc) t1
             where rownum <= 8)  t2
     where t2.rn >= 5;
    

    这里写图片描述


    案例2

    这里写图片描述

    最开始用的相关子查询做的,

    select empno,
           ename,
           sal,
           (select avg(sal) from emp where deptno = e.deptno) avgsal
      from emp e
     where sal > (select avg(sal) from emp where deptno = e.deptno);
    

    现在用多表查询的方式实现下

    select e.empno, e.ename, e.sal, s.deptno, s.avgsal
      from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) s
     where e.deptno = s.deptno
       and e.sal > s.avgsal
    

    在pl/sql中,选中sql,按F5查看执行计划
    这里写图片描述

    这里写图片描述

    可以看到 相关子查询的效果更好一些。


    案例3

    统计员工的入职年份

    使用函数方式

     select count(*) Total,
            sum(decode(to_char(hiredate, 'YYYY'), '1980', '1', '0')) "1980",
            sum(decode(to_char(hiredate, 'YYYY'), '1981', '1', '0')) "1981",
            sum(decode(to_char(hiredate, 'YYYY'), '1982', '1', '0')) "1982",
            sum(decode(to_char(hiredate, 'YYYY'), '1987', '1', '0')) "1987"
       from emp;
    

    使用子查询和dual伪表

     select (select count(*) from emp) Total,
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1980') "1980",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1981') "1981",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1982') "1982",
            (select count(*) from emp where to_char(hiredate, 'YYYY') = '1987') "1987"
       from dual;
    
    

    这里写图片描述


    展开全文
  • SQL Server T-SQL高级查询

    万次阅读 2019-02-10 09:56:59
    高级查询在数据库中用得是最频繁的,也是应用最广泛的。

    高级查询在数据库中用得是最频繁的,也是应用最广泛的。

    Ø 基本常用查询

    –select
    select * from student;

    –all 查询所有
    select all sex from student;

    –distinct 过滤重复
    select distinct sex from student;

    –count 统计
    select count(*) from student;
    select count(sex) from student;
    select count(distinct sex) from student;

    –top 取前N条记录
    select top 3 * from student;

    –alias column name 列重命名
    select id as 编号, name ‘名称’, sex 性别 from student;

    –alias table name 表重命名
    select id, name, s.id, s.name from student s;

    –column 列运算
    select (age + id) col from student;
    select s.name + ‘-’ + c.name from classes c, student s where s.cid = c.id;

    –where 条件
    select * from student where id = 2;
    select * from student where id > 7;
    select * from student where id < 3;
    select * from student where id <> 3;
    select * from student where id >= 3;
    select * from student where id <= 5;
    select * from student where id !> 3;
    select * from student where id !< 5;

    –and 并且
    select * from student where id > 2 and sex = 1;

    –or 或者
    select * from student where id = 2 or sex = 1;

    –between … and … 相当于并且
    select * from student where id between 2 and 5;
    select * from student where id not between 2 and 5;

    –like 模糊查询
    select * from student where name like ‘%a%’;
    select * from student where name like ‘%[a][o]%’;
    select * from student where name not like ‘%a%’;
    select * from student where name like ‘ja%’;
    select * from student where name not like ‘%[j,n]%’;
    select * from student where name like ‘%[j,n,a]%’;
    select * from student where name like ‘%[^ja,as,on]%’;
    select * from student where name like ‘%[ja_on]%’;

    –in 子查询
    select * from student where id in (1, 2);

    –not in 不在其中
    select * from student where id not in (1, 2);

    –is null 是空
    select * from student where age is null;

    –is not null 不为空
    select * from student where age is not null;

    –order by 排序
    select * from student order by name;
    select * from student order by name desc;
    select * from student order by name asc;

    –group by 分组
    按照年龄进行分组统计
    select count(age), age from student group by age;
    按照性别进行分组统计
    select count(), sex from student group by sex;
    按照年龄和性别组合分组统计,并排序
    select count(
    ), sex from student group by sex, age order by age;
    按照性别分组,并且是id大于2的记录最后按照性别排序
    select count(), sex from student where id > 2 group by sex order by sex;
    查询id大于2的数据,并完成运算后的结果进行分组和排序
    select count(
    ), (sex * id) new from student where id > 2 group by sex * id order by sex * id;

    –group by all 所有分组
    按照年龄分组,是所有的年龄
    select count(*), age from student group by all age;

    –having 分组过滤条件
    按照年龄分组,过滤年龄为空的数据,并且统计分组的条数和现实年龄信息
    select count(*), age from student group by age having age is not null;

    按照年龄和cid组合分组,过滤条件是cid大于1的记录
    select count(*), cid, sex from student group by cid, sex having cid > 1;

    按照年龄分组,过滤条件是分组后的记录条数大于等于2
    select count(*), age from student group by age having count(age) >= 2;

    按照cid和性别组合分组,过滤条件是cid大于1,cid的最大值大于2
    select count(*), cid, sex from student group by cid, sex having cid > 1 and max(cid) > 2;
    Ø 嵌套子查询

    子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择,而包含子查询的语句也成为外部查询或外部选择。
    

    from (select … table)示例

    将一个table的查询结果当做一个新表进行查询
    select * from (
    select id, name from student where sex = 1
    ) t where t.id > 2;
    上面括号中的语句,就是子查询语句(内部查询)。在外面的是外部查询,其中外部查询可以包含以下语句:

     1、 包含常规选择列表组件的常规select查询
    
     2、 包含一个或多个表或视图名称的常规from语句
    
     3、 可选的where子句
    
     4、 可选的group by子句
    
     5、 可选的having子句
    

    示例

    查询班级信息,统计班级学生人生
    select , (select count() from student where cid = classes.id) as num
    from classes order by num;

    in, not in子句查询示例

    查询班级id大于小于的这些班级的学生信息
    select * from student where cid in (
    select id from classes where id > 2 and id < 4
    );

    查询不是班的学生信息
    select * from student where cid not in (
    select id from classes where name = ‘2班’
    )
    in、not in 后面的子句返回的结果必须是一列,这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id;

    exists和not exists子句查询示例

    查询存在班级id为的学生信息
    select * from student where exists (
    select * from classes where id = student.cid and id = 3
    );

    查询没有分配班级的学生信息
    select * from student where not exists (
    select * from classes where id = student.cid
    );
    exists和not exists查询需要内部查询和外部查询进行一个关联的条件,如果没有这个条件将是查询到的所有信息。如:id等于student.id

    some、any、all子句查询示例

    查询班级的学生年龄大于班级的学生的年龄的信息
    select * from student where cid = 5 and age > all (
    select age from student where cid = 3
    );

    select * from student where cid = 5 and age > any (
    select age from student where cid = 3
    );

    select * from student where cid = 5 and age > some (
    select age from student where cid = 3
    );
    Ø 聚合查询

    1、 distinct去掉重复数据

    select distinct sex from student;
    select count(sex), count(distinct sex) from student;
    2、 compute和compute by汇总查询

    对年龄大于的进行汇总
    select age from student
    where age > 20 order by age compute sum(age) by age;

    对年龄大于的按照性别进行分组汇总年龄信息
    select id, sex, age from student
    where age > 20 order by sex, age compute sum(age) by sex;

    按照年龄分组汇总
    select age from student
    where age > 20 order by age, id compute sum(age);

    按照年龄分组,年龄汇总,id找最大值
    select id, age from student
    where age > 20 order by age compute sum(age), max(id);
    compute进行汇总前面是查询的结果,后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式,可以添加的信息如下:

     a、 可选by关键字。它是每一列计算指定的行聚合
    
     b、 行聚合函数名称。包括sum、avg、min、max、count等
    
     c、 要对其执行聚合函数的列
    
     compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。
    

    3、 cube汇总

    cube汇总和compute效果类似,但语法较简洁,而且返回的是一个结果集。

    select count(), sex from student group by sex with cube;
    select count(
    ), age, sum(age) from student where age is not null group by age with cube;
    cube要结合group by语句完成分组汇总

    Ø 排序函数

    排序在很多地方需要用到,需要对查询结果进行排序并且给出序号。比如:

    1、 对某张表进行排序,序号需要递增不重复的

    2、 对学生的成绩进行排序,得出名次,名次可以并列,但名次的序号是连续递增的

    3、 在某些排序的情况下,需要跳空序号,虽然是并列

    基本语法

    排序函数 over([分组语句] 排序子句[desc][asc])
    排序子句 order by 列名, 列名
    分组子句 partition by 分组列, 分组列

    row_number函数

    根据排序子句给出递增连续序号

    按照名称排序的顺序递增
    select s.id, s.name, cid, c.name, row_number() over(order by c.name) as number
    from student s, classes c where cid = c.id;

    rank函数函数

    根据排序子句给出递增的序号,但是存在并列并且跳空

    顺序递增
    select id, name, rank() over(order by cid) as rank from student;

    跳过相同递增
    select s.id, s.name, cid, c.name, rank() over(order by c.name) as rank
    from student s, classes c where cid = c.id;

    dense_rank函数

    根据排序子句给出递增的序号,但是存在并列不跳空

    不跳过,直接递增
    select s.id, s.name, cid, c.name, dense_rank() over(order by c.name) as dense
    from student s, classes c where cid = c.id;

    partition by分组子句

    可以完成对分组的数据进行增加排序,partition by可以与以上三个函数联合使用。

    利用partition by按照班级名称分组,学生id排序
    select s.id, s.name, cid, c.name, row_number() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;

    select s.id, s.name, cid, c.name, rank() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;

    select s.id, s.name, cid, c.name, dense_rank() over(partition by c.name order by s.id) as rank
    from student s, classes c where cid = c.id;

    ntile平均排序函数

    将要排序的数据进行平分,然后按照等分排序。ntile中的参数代表分成多少等分。

    select s.id, s.name, cid, c.name,
    ntile(5) over(order by c.name) as ntile
    from student s, classes c where cid = c.id;
    Ø 集合运算

    操作两组查询结果,进行交集、并集、减集运算

    1、 union和union all进行并集运算

    –union 并集、不重复
    select id, name from student where name like ‘ja%’
    union
    select id, name from student where id = 4;

    –并集、重复
    select * from student where name like ‘ja%’
    union all
    select * from student;
    2、 intersect进行交集运算

    –交集(相同部分)
    select * from student where name like ‘ja%’
    intersect
    select * from student;
    3、 except进行减集运算

    –减集(除相同部分)
    select * from student where name like ‘ja%’
    except
    select * from student where name like ‘jas%’;
    Ø 公式表表达式

    查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。那么公式表表达式可以解决这个问题。

    我们可以将公式表表达式(CET)视为临时结果集,在select、insert、update、delete或是create view语句的执行范围内进行定义。

    –表达式
    with statNum(id, num) as
    (
    select cid, count(*)
    from student
    where id > 0
    group by cid
    )
    select id, num from statNum order by id;

    with statNum(id, num) as
    (
    select cid, count(*)
    from student
    where id > 0
    group by cid
    )
    select max(id), avg(num) from statNum;
    Ø 连接查询

    1、 简化连接查询

    –简化联接查询
    select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;
    2、 left join左连接

    –左连接
    select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;
    3、 right join右连接

    –右连接
    select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;
    4、 inner join内连接

    –内连接
    select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;

    –inner可以省略
    select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id;
    5、 cross join交叉连接

    –交叉联接查询,结果是一个笛卡儿乘积
    select s.id, s.name, c.id, c.name from student s cross join classes c
    –where s.cid = c.id;
    6、 自连接(同一张表进行连接查询)

    –自连接
    select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;
    Ø 函数

    1、 聚合函数

    max最大值、min最小值、count统计、avg平均值、sum求和、var求方差

    select
    max(age) max_age,
    min(age) min_age,
    count(age) count_age,
    avg(age) avg_age,
    sum(age) sum_age,
    var(age) var_age
    from student;
    2、 日期时间函数

    select dateAdd(day, 3, getDate());–加天
    select dateAdd(year, 3, getDate());–加年
    select dateAdd(hour, 3, getDate());–加小时
    –返回跨两个指定日期的日期边界数和时间边界数
    select dateDiff(day, ‘2011-06-20’, getDate());
    –相差秒数
    select dateDiff(second, ‘2011-06-22 11:00:00’, getDate());
    –相差小时数
    select dateDiff(hour, ‘2011-06-22 10:00:00’, getDate());
    select dateName(month, getDate());–当前月份
    select dateName(minute, getDate());–当前分钟
    select dateName(weekday, getDate());–当前星期
    select datePart(month, getDate());–当前月份
    select datePart(weekday, getDate());–当前星期
    select datePart(second, getDate());–当前秒数
    select day(getDate());–返回当前日期天数
    select day(‘2011-06-30’);–返回当前日期天数
    select month(getDate());–返回当前日期月份
    select month(‘2011-11-10’);
    select year(getDate());–返回当前日期年份
    select year(‘2010-11-10’);
    select getDate();–当前系统日期
    select getUTCDate();–utc日期
    3、 数学函数

    select pi();–PI函数
    select rand(100), rand(50), rand(), rand();–随机数
    select round(rand(), 3), round(rand(100), 5);–精确小数位
    –精确位数,负数表示小数点前
    select round(123.456, 2), round(254.124, -2);
    select round(123.4567, 1, 2);
    4、 元数据

    select col_name(object_id(‘student’), 1);–返回列名
    select col_name(object_id(‘student’), 2);
    –该列数据类型长度
    select col_length(‘student’, col_name(object_id(‘student’), 2));
    –该列数据类型长度
    select col_length(‘student’, col_name(object_id(‘student’), 1));
    –返回类型名称、类型id
    select type_name(type_id(‘varchar’)), type_id(‘varchar’);
    –返回列类型长度
    select columnProperty(object_id(‘student’), ‘name’, ‘PRECISION’);
    –返回列所在索引位置
    select columnProperty(object_id(‘student’), ‘sex’, ‘ColumnId’);
    5、 字符串函数

    select ascii(‘a’);–字符转换ascii值
    select ascii(‘A’);
    select char(97);–ascii值转换字符
    select char(65);
    select nchar(65);
    select nchar(45231);
    select nchar(32993);–unicode转换字符
    select unicode(‘A’), unicode(‘中’);–返回unicode编码值
    select soundex(‘hello’), soundex(‘world’), soundex(‘word’);
    select patindex(’%a’, ‘ta’), patindex(’%ac%’, ‘jack’), patindex(‘dex%’, ‘dexjack’);–匹配字符索引
    select ‘a’ + space(2) + ‘b’, ‘c’ + space(5) + ‘d’;–输出空格
    select charIndex(‘o’, ‘hello world’);–查找索引
    select charIndex(‘o’, ‘hello world’, 6);–查找索引
    select quoteName(‘abc[]def’), quoteName(‘123]45’);
    –精确数字
    select str(123.456, 2), str(123.456, 3), str(123.456, 4);
    select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
    select difference(‘hello’, ‘helloWorld’);–比较字符串相同
    select difference(‘hello’, ‘world’);
    select difference(‘hello’, ‘llo’);
    select difference(‘hello’, ‘hel’);
    select difference(‘hello’, ‘hello’);
    select replace(‘abcedef’, ‘e’, ‘E’);–替换字符串
    select stuff(‘hello world’, 3, 4, ‘ABC’);–指定位置替换字符串
    select replicate(‘abc#’, 3);–重复字符串
    select subString(‘abc’, 1, 1), subString(‘abc’, 1, 2), subString(‘hello Wrold’, 7, 5);–截取字符串
    select len(‘abc’);–返回长度
    select reverse(‘sqlServer’);–反转字符串

    select left(‘leftString’, 4);–取左边字符串
    select left(‘leftString’, 7);
    select right(‘leftString’, 6);–取右边字符串
    select right(‘leftString’, 3);
    select lower(‘aBc’), lower(‘ABC’);–小写
    select upper(‘aBc’), upper(‘abc’);–大写
    –去掉左边空格
    select ltrim(’ abc’), ltrim(’# abc#’), ltrim(’ abc’);
    –去掉右边空格
    select rtrim(’ abc ‘), rtrim(’# abc# '), rtrim(‘abc’);
    6、 安全函数

    select current_user;
    select user;
    select user_id(), user_id(‘dbo’), user_id(‘public’), user_id(‘guest’);
    select user_name(), user_name(1), user_name(0), user_name(2);
    select session_user;
    select suser_id(‘sa’);
    select suser_sid(), suser_sid(‘sa’), suser_sid(‘sysadmin’), suser_sid(‘serveradmin’);
    select is_member(‘dbo’), is_member(‘public’);
    select suser_name(), suser_name(1), suser_name(2), suser_name(3);
    select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
    select is_srvRoleMember(‘sysadmin’), is_srvRoleMember(‘serveradmin’);
    select permissions(object_id(‘student’));
    select system_user;
    select schema_id(), schema_id(‘dbo’), schema_id(‘guest’);
    select schema_name(), schema_name(1), schema_name(2), schema_name(3);
    7、 系统函数

    select app_name();–当前会话的应用程序名称
    select cast(2011 as datetime), cast(‘10’ as money), cast(‘0’ as varbinary);–类型转换
    select convert(datetime, ‘2011’);–类型转换
    select coalesce(null, ‘a’), coalesce(‘123’, ‘a’);–返回其参数中第一个非空表达式
    select collationProperty(‘Traditional_Spanish_CS_AS_KS_WS’, ‘CodePage’);
    select current_timestamp;–当前时间戳
    select current_user;
    select isDate(getDate()), isDate(‘abc’), isNumeric(1), isNumeric(‘a’);
    select dataLength(‘abc’);
    select host_id();
    select host_name();
    select db_name();
    select ident_current(‘student’), ident_current(‘classes’);–返回主键id的最大值
    select ident_incr(‘student’), ident_incr(‘classes’);–id的增量值
    select ident_seed(‘student’), ident_seed(‘classes’);
    select @@identity;–最后一次自增的值
    select identity(int, 1, 1) as id into tab from student;–将studeng表的烈属,以/1自增形式创建一个tab
    select * from tab;
    select @@rowcount;–影响行数
    select @@cursor_rows;–返回连接上打开的游标的当前限定行的数目
    select @@error;–T-SQL的错误号
    select @@procid;
    8、 配置函数

    set datefirst 7;–设置每周的第一天,表示周日
    select @@datefirst as ‘星期的第一天’, datepart(dw, getDate()) AS ‘今天是星期’;
    select @@dbts;–返回当前数据库唯一时间戳
    set language ‘Italian’;
    select @@langId as ‘Language ID’;–返回语言id
    select @@language as ‘Language Name’;–返回当前语言名称
    select @@lock_timeout;–返回当前会话的当前锁定超时设置(毫秒)
    select @@max_connections;–返回SQL Server 实例允许同时进行的最大用户连接数
    select @@MAX_PRECISION AS ‘Max Precision’;–返回decimal 和numeric 数据类型所用的精度级别
    select @@SERVERNAME;–SQL Server 的本地服务器的名称
    select @@SERVICENAME;–服务名
    select @@SPID;–当前会话进程id
    select @@textSize;
    select @@version;–当前数据库版本信息
    9、 系统统计函数

    select @@CONNECTIONS;–连接数
    select @@PACK_RECEIVED;
    select @@CPU_BUSY;
    select @@PACK_SENT;
    select @@TIMETICKS;
    select @@IDLE;
    select @@TOTAL_ERRORS;
    select @@IO_BUSY;
    select @@TOTAL_READ;–读取磁盘次数
    select @@PACKET_ERRORS;–发生的网络数据包错误数
    select @@TOTAL_WRITE;–sqlserver执行的磁盘写入次数
    select patIndex(’%soft%’, ‘microsoft SqlServer’);
    select patIndex(‘soft%’, ‘software SqlServer’);
    select patIndex(’%soft’, ‘SqlServer microsoft’);
    select patIndex(’%so_gr%’, ‘Jsonisprogram’);
    10、 用户自定义函数

    查看当前数据库所有函数

    –查询所有已创建函数
    select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
    and type in(‘fn’, ‘if’, ‘tf’);

    创建函数

    if (object_id(‘fun_add’, ‘fn’) is not null)
    drop function fun_add
    go
    create function fun_add(@num1 int, @num2 int)
    returns int
    with execute as caller
    as
    begin
    declare @result int;
    if (@num1 is null)
    set @num1 = 0;
    if (@num2 is null)
    set @num2 = 0;
    set @result = @num1 + @num2;
    return @result;
    end
    go
    调用函数
    select dbo.fun_add(id, age) from student;

    –自定义函数,字符串连接
    if (object_id(‘fun_append’, ‘fn’) is not null)
    drop function fun_append
    go
    create function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
    returns nvarchar(2048)
    as
    begin
    return @args + @args2;
    end
    go

    select dbo.fun_append(name, ‘abc’) from student;

    修改函数

    alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
    returns nvarchar(1024)
    as
    begin
    declare @result varchar(1024);
    –coalesce返回第一个不为null的值
    set @args = coalesce(@args, ‘’);
    set @args2 = coalesce(@args2, ‘’);;
    set @result = @args + @args2;
    return @result;
    end
    go

    select dbo.fun_append(name, ‘#abc’) from student;

    返回table类型函数

    –返回table对象函数
    select name, object_id, type from sys.objects where type in (‘fn’, ‘if’, ‘tf’) or type like ‘%f%’;

    if (exists (select * from sys.objects where type in (‘fn’, ‘if’, ‘tf’) and name = ‘fun_find_stuRecord’))
    drop function fun_find_stuRecord
    go
    create function fun_find_stuRecord(@id int)
    returns table
    as
    return (select * from student where id = @id);
    go

    select * from dbo.fun_find_stuRecord(2);

    展开全文
  • MySql高级查询

    千次阅读 2019-08-18 15:44:12
    DQL高级查询 多表查询(关联查询,连接查询) 1.笛卡儿积 emp表15条记录,dept表4条记录。 连接查询的笛卡尔积为60条记录。 2.内连接 不区分主从表,与连接顺序无关,两张表均满足条件则出现结果集中 ...
    •  DQL高级查询

    •   多表查询(关联查询,连接查询)

             1.笛卡儿积    

                emp表15条记录,dept表4条记录。

                连接查询的笛卡尔积为60条记录。

             2.内连接

                不区分主从表,与连接顺序无关,两张表均满足条件则出现结果集中

    --where子句
    select * from emp,dept
    where emp.deptno = dept.deptno
    --inner join…on…
    select * from emp
    inner join dept
    on emp.deptno = dept.deptno
    --inner join…using…
    select * from emp
    INNER JOIN dept
    using(deptno)

             3.自然连接

                 寻找俩表中字段名称相等,数据类型相同的字段进行连接,会自动去重重复列(如果有多个字段符合要求,那么他们会被作为自然连接的条件)

    --自然连接(等值连接,表的字段名称必须相同,去除重复行)
    select * from emp NATURAL JOIN dept;

            4.外连接

                 有主从表之分,与连接顺序有关。以驱动表为依据,匹配表依次进行查询;匹配表中找不到数据,则以null填充。

    --左外连接 left [outer] join  .... ON...
    select * from emp
     left join dept
     on emp.deptno = dept.deptno;
    
    on也可以换位using()

             5.自连接

                  同一个表里面的数据相关联

    --查询所有的员工的姓名和上级领导的姓名  emp(内连接)
    select e1.ename ename ,e2.ename mgrname from emp e1,emp e2
    where e1.mgr=e2.empno
    
    select e1.ename ename ,e2.ename mgrname from emp e1
    left join emp e2
    on e1.mgr=e2.empno
    •  子查询(嵌套查询)

            1.单行子查询

                 子查询的结果返回一行

    select dname from dept where deptno = (select deptno from emp where empno=7788);

            2.多行子查询

                 查询的结果返回一个集合

    --查询工资大于2000的员工的部门名称
      select dname from dept where deptno =any(select deptno from emp where sal > 2000);
      ANY   ALL
      =ANY 含义就是in   >any  大于最小值   <any  小于最大值
      >all 大于最大值    <all 小于最小值

            案例:

    --查询大于所在部门的平均工资的员工信息。
      --关联查询
       1.分组每个部门平均工资
       select * from emp e,(select deptno,avg(sal) avg from emp group by deptno) e1
       where e.deptno = e1.deptno and e.sal > e1.avg
      --子查询(主查询可以将数据传递给子查询)
       select * from emp e where sal > (select avg(sal) from emp e1 where e1.deptno = e.deptno)
       1.先执行主查询,将deptno传给子查询
       2.子查询拿到deptno,计算查询的结果,返回给主查询
       3.主查询拿到子查询返回的结果执行查询
    
    --查询薪水大于2000  emp 的部门名称  dept
    
       select dname from dept where deptno in(
       select deptno from emp where sal > 2000);
       
       select dname from dept d where EXISTS(
       select * from emp e where sal > 2000 and d.deptno = e.deptno)
    

               in和exists的区别

               1.IN
                  主查询的条件字段和子查询返回字段必须一致。
                  先执行子查询,将返回子查询的结果给主查询,再执行主查询
               2.EXISTS
                  主查询不需要出现条件字段
                  先执行主查询,将主查询的表的字段传给子查询,如果在子查询找到相应结果,
                  返回true,将该结果显示在结果集中。否则返回false
    •   联合查询

                 1.UNION

                     并集,所有的内容都查询,重复的显示一次

    select * from emp where deptno = 20 
    union   
    select * from emp where sal > 2000

                 2.UNION ALL

                     并集,所有的内容都显示,包括重复的

    • 事务

    •      存储引擎

           Mysql的核心就是存储引擎,DBMS借助于引擎实现增删改查操作。

           Mysql有多种存储引擎,不同引擎在事务的支持,查询的效率,索引策略等方面有不同。

           InnoDB是事务型数据库的首选,执行安全性数据库,行锁定和外键。mysql5.5之后默认使用。

           MyISAM插入速度和查询效率较高,但不支持事务。

           MEMORY将表中的数据存储在内存中,速度较快。

          

    •  什么是事务

          事务指逻辑上的一组操作,组成这组操作的各个单元,要么全成功,要么全不成功。

    •  事务的ACID特性

          原子性:指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。 

          一致性:事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。

          隔离性:事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

          持久性:指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。

    •  事务的实现  tcl commit rollback

            查看事务  show variables like '%autocommit%'

            a)mysql数据库默认是自动提交

                set autocommit=0; 不自动提交

                set autocomiit=1;自动提交

            b)手动开启事务

                 start transaction/begin;

            c)手动提交或则回滚

                 commit;

                 rollback;

                 savepoint;保存点,恢复必须在事务提交之前。事务一旦提交,所有的保存点全部失效。

              注意:DDL操作会隐式事务提交

    -- 关闭自动提交
    set autocommit=0;
    -- 显式开始事务
    start TRANSACTION; 
    -- DML
    update account set money = money - 20 where name = 'ls';
    -- 保存点
    -- SAVEPOINT a;
    delete from aa;
    update account set money = money + 20 where name = 'zs';
    -- 提交
    -- commit;
    -- 回滚
    commit;
    -- 不起效
    -- ROLLBACK to a;
    
    •  JDBC如何控制事务

     

    •  事物的隔离级别

               赃读:指一个事务读取了另一个事务未提交的数据。

              不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。一个事务读取到了另一个事务提交后的数据。(update)

               虚读(幻读):是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。              (insert)

     

              数据库通过设置事务的隔离级别防止以上情况的发生:

               * 1、READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。

               * 2、READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。(oracle默认的)

               * 4、REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。(mysql默认)

              * 8、SERIALIZABLE: 避免赃读、不可重复读、虚读。

               级别越高,性能越低,数据越安全

     

               mysql中:

               查看当前的事务隔离级别:SELECT @@TX_ISOLATION;

               更改当前的事务隔离级别:SET TRANSACTION ISOLATION LEVEL 四个级别之一。

               设置隔离级别必须在事务之前

     

     

    •  JDBC控制事务的隔离级别

         Connection接口:

         

         设置隔离级别:必须在开启事务之前。

         Connection.setTransactionIsolation(int level);

    •  存储程序

    •  概念:存储程序指的一组存储和执行在数据库服务器端的程序。
    •  分类    1.存储过程   2.存储函数  3.触发器
    •  存储过程

              1.基本语法

    CREATE PROCEDURE sel_emp(参数列表)
        BEGIN
         --操作
      END;

             2.使用

    无参的存储过程:
    CREATE PROCEDURE sel_emp()
        BEGIN
         select * from emp where deptno = 10;
      END;
    --存储过程的调用
    call sel_emp();
    
    有参的存储过程:
    --根据部门编号查询员工信息
      CREATE PROCEDURE sel_emp2(dno int)
      BEGIN
        select * from emp where deptno=dno;
      END;
     --调用存储过程
      call sel_emp2(30);
    
    --根据员工编号查询员工的名称
      CREATE PROCEDURE sel_emp3(eno int,OUT name varchar(20))
      BEGIN
        select ename into name from emp where empno=eno;
      end;
      --调用
      call sel_emp3(7788,@name);
      select @name;
    
    --根据员工编号查询所在部门的编号
      CREATE PROCEDURE sel_emp4(INOUT eno int)
      BEGIN
        select deptno into eno from emp where empno = eno;
      END;
        
      --调用
      set @eno = 7788;
      call sel_emp4(@eno);
      select @eno;
    
    --分支语句
    CREATE PROCEDURE cal_score(score int)
            BEGIN
            -- 声明变量
            DECLARE levels varchar(20);
            -- 分支
                    IF score >= 90 THEN
              -- 赋值
              set levels = '优秀';
            ELSEIF score >= 80  THEN
              set levels = '良好';
            ELSE
              set levels = '不通过';
            END IF;
            -- 输出
            select levels;
    END;
    
    --while循环
    create PROCEDURE calc()
      BEGIN
        -- 声明两个变量
        DECLARE sum int;
        DECLARE count int;
        -- 初始化
        set sum = 0;
        set count = 1;
        -- 循环
        while count <=100 DO
          set sum = sum + count;
          set count = count + 1;
        END WHILE;
    
        SELECT sum;
      END;
    
      --LOOP
      create PROCEDURE calc1()
      BEGIN
        -- 声明两个变量
        DECLARE sum int;
        DECLARE count int;
        -- 初始化
        set sum = 0;
        set count = 1;
        -- 循环
       lip:LOOP
          set sum = sum + count;
          set count = count + 1;
          IF count > 100 THEN
            LEAVE lip;
          END IF;
       END LOOP;
        SELECT sum;
      END;
        
    call calc1();
    
    create PROCEDURE calc3()
      BEGIN
        -- 声明两个变量
        DECLARE sum int;
        DECLARE count int;
        -- 初始化
        set sum = 0;
        set count = 1;
        -- 循环
       REPEAT
          set sum = sum + count;
          set count = count + 1;
          UNTIL count > 100
       END REPEAT;
        SELECT sum;
      END;
    

             3.参数模式

                 in:外部传进存储过程

                 out:传出

                 inout:传进传出

            4.游标

    --查询所有员工的姓名
      create PROCEDURE emp_cursor4()
      BEGIN
        DECLARE name varchar(20);
        DECLARE DONE boolean default 0;
        -- 声明游标类型变量存储所有员工的名称
        DECLARE emp_cursor CURSOR for select ename from emp;  
        -- 结束设置状态码为1  
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;  
        -- 打开游标
        open emp_cursor;
        -- 获取游标中维护的值
        lip:LOOP
          FETCH emp_cursor into name;
          IF DONE THEN
            leave lip;
          END IF;
          select name;
        END LOOP;
        -- 关闭游标
        close emp_cursor;
      END;
    •  存储函数
    --函数(确定的不变的 DETERMINISTIC  Not )
      create FUNCTION emp_func()
      RETURNS VARCHAR(20)
    DETERMINISTIC
      BEGIN
         DECLARE name varchar(20);
         select ename into name from emp where empno = 7788;
         RETURN name;
      END;
    
      select emp_func();
    
    函数有返回值  return
    存储过程可以单独使用;但是函数只能作为语句的一部分。

     

     

    展开全文
  • MySQL 高级查询

    2017-06-29 23:08:06
    高级查询虽说也是基础,但相较于单表,高级查询的逻辑性更高,语句也更为复杂,更容易出错,在实际的开发中大量用到,由此掌握高级查询尤为重要。 如果想从多个表查询比较复杂的信息,就会使用高级查询实现。常见的...
  • MySQL高级查询

    千次阅读 2018-09-13 13:36:59
    高级查询 高级查询的格式: select ... 聚合函数 from 表名 where ... group by ... having ... order by ... limit ... order by 给查询结果排序 语法格式: ... order by 字段名 ASC/DESC; ASC:默认,...
  • MYSQL高级查询 一、常用的高级查询语句 连接查询:如果条件和结果分布于多张表,使用多表连接 子查询:(如果最终结果在一张表中,优先选择子查询,再考虑连接查询)嵌套查询,将一个查询结果作为另一个查询条件或...
  • Mysql高级查询

    2019-02-22 19:22:46
    高级查询(连接查询):查询多张表或者结果集 结果连接 等值连接 非等值连接 内连接:内外键互换不会影响结果集,哪个表在前面哪个表的内容就在前面,显示两者共有的 外连接:内外键互换会影响结果集,显示第一...
  • sql server 高级查询语句小结 讲述sql高级查询语句小结
  • SSM第五讲 动态SQL与高级查询

    万次阅读 2020-08-17 21:26:49
    动态SQL与高级查询 动态sql 高级查询 一. 动态SQL(重点) 1. 动态SQL是什么 就是相对与固定SQL。就是通过传入的参数不一样,可以组成不同结构的SQL语句. 这种根据参数的条件修改SQL结构的SQL语句,我们称为动态SQL...
  • 简单查询 、按条件查询 、高级查询 、为表和字段取别名
  • java 高级查询

    千次阅读 2018-08-11 23:46:50
    一.概述  对于java中,前端对于多条件的查询,往往参数过多,如果后端在接收参数的时候不进行...高级查询的封装示例  1.数据库建表 DROP TABLE IF EXISTS `product`; CREATE TABLE `product` ( `id` bigint(11...
  • SQL高级查询.pdf

    2011-11-16 15:02:41
    SQL高级查询.pdf SQL高级查询.pdf SQL高级查询.pdf SQL高级查询.pdf
  • 除去Windchill系统的高级查询,分页大家都可以百度百度就找到栗子了,windchill圈子比较小,资料也比较少,贴一个分页查询的栗子: /** * 分页查询 * @param conditional * @return * @throws WTException ...
  • MySQL高级查询习题

    千次阅读 热门讨论 2020-12-03 17:34:22
    MySQL高级查询习题 提示: emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno所属部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) 工资 = 薪金 +...
  • 高级查询(mysql)

    千次阅读 2021-01-30 21:31:34
    高级查询 > 其他+V:w891123884 MySQL提供了几种高级查询语句,用于复杂场所下进行多表一起查询,这样就会用到内连接查询、外连接查询、自然连接查询、交叉连接查询和联合查询,以满足日常业务查询的需求,从而更...
  • 数据库的高级查询

    2019-06-10 20:57:28
    1-高级查询 1.聚合函数的使用 2.分组查询的应用 3.Having子句的使用
  • SQL高级查询与子查询操作 MySQL基础查询语句 1.Select*from 表 order by 字段[ASC默认/DESC]; 【排序】 升序(从小到大)ASC,降序(从大到小)DESC ·已知字段进行排列 ·不知道字段用数字表示第几个...
  • 实验6 数据查询--高级查询 一、实验目的 1.掌握查询结果排序的方法。 2.掌握排序结果进行计算的方法。 3.掌握排序结果分组的方法。 4.掌握排序结果分组后再选择的方法。 二、实验要求 应用SELECT语句对数据库eshop...
  • SQL高级查询

    2016-09-08 09:13:02
    SQL高级查询聚合函数查询 count(*) count() sum() avg() max() min() 利用聚合函数产生虚拟字段因为要用这些聚合函数,所以要配合select使用,因此有select *, (select avg(SALARY) from MEMBER) as AVG_SALARY from...
  • MySQL高级查询——连接查询实例详解

    万次阅读 多人点赞 2016-05-13 17:08:37
    如果想从多个表查询比较复杂的信息,就会使用高级查询实现。常见的高级查询包括多表连接查询、内连接查询、外连接查询与组合查询等,今天我们先来学习最常用、面试也很容易被问到的连接查询。 我们今天以一个简单的...
  • 1. crtl+f查找,在code处输入你... 2. 高级查询/模糊查询:点击Advanced 标签页,勾选查询项目,选择对比符号,表达式值支持“通配符”;点击Find now按钮。 3. 右击找到的表,点击”find in diagram"即可。 ...
  • MongoDB高级查询语句

    千次阅读 2016-09-26 11:23:15
    本文介绍MongoDB的高级查询语句MongoDB支持的查询语言非常强大,语法规则类似于面向对象的查询语言,可以实现类似关系数据库单表查询的绝大部分功能,并且由于MongoDB可以支持复杂的数据结构,不受二维表形式的限制...
  • 实现后台高级查询(高级版)

    千次阅读 2018-01-04 19:19:38
    //商品的高级查询对象,封装了商品高级查询表单的数据。 public class ProductQueryObject extends BaseQueryObject{ private List conditions = new ArrayList(); //拼接查询条件的SQL public String getQuery
  • SQLServer高级查询

    千次阅读 2018-11-09 18:53:47
    #SQLServer高级查询 SQL Top select top n * from 表名 SQL Like select 列名 from 表名 where 列名 like 模式(按照我们写的规矩查询) SQL 通配符 详情在下方 SQL In select 字段 from 表名 where 字段...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 25,790
精华内容 10,316
关键字:

高级查询