数据库常见相关问题

  

1.SQL中in有什么作用？NOT IN呢？

exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：
  select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要
exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
  select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)  
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

而not exists 和not in 分别是exists 和in 的 对立面。

exists （sql 返回结果集为真）  

not exists (sql 不返回结果集为真）

2.子查询中exists/ALL/ANY有什么区别？如何使用？

in,exists

in表示值是否存在子查询结果集中

select * from A where name in(select name from B) 

exists是表示子查询是否返回结果，而不管返回的具体内容。

select *from A where exists( select 1 from B where name='liu');

 

in是“=any的简写”；

not in是“<>all”的简写

 

any是表示子查询结果中任意一个，all表示子查询结果中的所有。

>any表示只要大于子查询结果中的任一个，表达式就成立，=any表示等于子查询中的任一个,相当于in.

>all表示要大于子查询结果中的所有，才会返回true,  not in 相当于“<>all”.

 

当ALL、ANY或SOME操作符与"="比较操作符配合使用时，子查询可以是一个数据表子查询。此时，你需要使用一个数据行构造器来提供与子查询所返回的数据行进行比较的比较值。

eg:select *from A where (name,num)=any(select name1,num1 from Bwhere name='john');

 

in在子查询不返回数据的时候,为false,子查询结果中有null的时候，null不会用于比较。

any 同样在子查询不返回数据的时候,为false,子查询结果中有null的时候，null不会用于比较。

all在子查询不返回数据的时候,为true,子查询结果中有null的时候，不会返回数据。

not in 或not exists来代替.

not in 不等于<>any,相当于<>all,

<>any是只要不等于其中的任意一个，就成立

1.where与having有什么作用？有什么区别？在联表时使用on和使用where在执行效果和顺序上会不会有区别？

sql语句的执行过程是：from-->where-->group by -->having --- >order by --> select;

ON 、WHERE、HAVING都能通过限制条件筛选数据，但他们的使用及其不同。下面我们来分析三者之间的区别。

1.      ON 和WHERE

        所有的查询都回产生一个中间临时报表，查询结果就是从返回临时报表中得到。ON和WHERE后面所跟限制条件的区别，主要与限制条件起作用的时机有关，ON根据限制条件对数据库记录进行过滤，然后生产临时表；而WHERE是在临时表生产之后，根据限制条件从临时表中筛选结果。

           因为以上原因，ON和WHERE的区别主要有下：

1） 返回结果：在左外（右外）连接中，ON会返回左表（右表）中的所有记录；而WHERE中，此时相当于inner join，只会返回满足条件的记录（因为是从临时表中筛选，会过滤掉不满足条件的）。

2） 速度：因为ON限制条件发生时间较早，临时表的数据集要小，因此ON的性能要优于WHERE。

2.      HAVING和WHERE

         HAVING和WHERE的区别也是与限制条件起作用时机有关，HAVING是在聚集函数计算结果出来之后筛选结果，查询结果只返回符合条件的分组，HAVING不能单独出现，只能出现在GROUP BY子句中。；而WHERE是在计算之前筛选结果，如果聚集函数使用WHERE，那么聚集函数只计算满足WHERE子句限制条件的数据,例如：

1. SELECT COUNT(id) FROM db_equip WHERE tb_equip_type = ‘2’;  

           Count计算的结果是首先筛选设备类型为2的的设备，然后统计设备类型为2类型的数量。

       在使用和功能上，HAVING和WHERE有以下区别：

1）  HAVING不能单独出现，只能出现在GROUP BY子句之中；WHERE即可以和SELECT等其他子句搭配使用，也可以和GROUP BY子句搭配使用，WHERE的优先级要高于聚合函数高于HAVING。

2） 因为WHERE在聚集函数之前筛选数据，HAVING在计算之后筛选分组，因此WHERE的查询速度要比HAVING的查询速度快。

3.      总结

        ON、WHERE、HAVING的主要差别是其子句中限制条件起作用时机引起的，ON是在生产临时表之前根据条件筛选记录，WHERE是从生产的临时表中筛选数据，而HAVING是对临时表中满足条件的数据，进行计算分组之后，通过HAVING限制语句筛选分组，返回结果是满足HAVING子句限制的分组。

SQL语句的聚合函数有哪些？

1. AVG  返回指定组中的平均值，空值被忽略。
    例：select  prd_no,avg(qty) from sales group by prd_no
 
2. COUNT  返回指定组中项目的数量。
      例：select  count(prd_no) from sales 
 
3. MAX  返回指定数据的最大值。
      例：select  prd_no,max(qty) from sales group by prd_no 
 
4. MIN  返回指定数据的最小值。
      例：select  prd_no,min(qty) from sales group by prd_no
 
5. SUM  返回指定数据的和，只能用于数字列，空值被忽略。
      例：select  prd_no,sum(qty) from sales group by prd_no
 
6. COUNT_BIG  返回指定组中的项目数量，与COUNT函数不同的是COUNT_BIG返回bigint值，而COUNT返回的是int值。
     例：select  count_big(prd_no) from sales
 
7. GROUPING  产生一个附加的列，当用CUBE或ROLLUP运算符添加行时，输出值为1.当所添加的行不是由CUBE或ROLLUP产生时，输出值为0.
     例：select  prd_no,sum(qty),grouping(prd_no) from sales group by prd_no with rollup
 
8. BINARY_CHECKSUM  返回对表中的行或表达式列表计算的二进制校验值，用于检测表中行的更改。
     例：select  prd_no,binary_checksum(qty) from sales group by prd_no
 
9. CHECKSUM_AGG  返回指定数据的校验值，空值被忽略。
     例：select  prd_no,checksum_agg(binary_checksum(*)) from sales group by prd_no
 
10. CHECKSUM  返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。
 
11. STDEV  返回给定表达式中所有值的统计标准偏差。
     例：select  stdev(prd_no) from sales
 
12. STDEVP  返回给定表达式中的所有值的填充统计标准偏差。
     例：select  stdevp(prd_no) from sales
 
13. VAR  返回给定表达式中所有值的统计方差。
     例：select  var(prd_no) from sales
 
14. VARP  返回给定表达式中所有值的填充的统计方差。
     例：select  varp(prd_no) from sales

Statement和PreparedStatement的区别

1、 PreparedStatement接口继承Statement， PreparedStatement 实例包含已编译的 SQL 语句，所以其执行速度要快于 Statement 对象。2、作为 Statement 的子类，PreparedStatement 继承了 Statement 的所有功能。三种方法 execute、 executeQuery 和 executeUpdate 已被更改以使之不再需要参数 3、在JDBC应用中,如果你已经是稍有水平开发者,你就应该始终以PreparedStatement代替 Statement.也就是说,在任何时候都不要使用Statement. 基于以下的原因: 一.代码的可读性和可维护性. 虽然用PreparedStatement来代替Statement会使代码多出几行,但这样的代码无论从可读性还是可维护性上来说.都比直接用Statement的代码高很多档次: stmt.executeUpdate("insert into tb_name(col1,col2,col2,col4) values ('"+var1+"','"+var2+"',"+var3+",'"+var4+"')");//stmt是Statement对象实例 perstmt = con.prepareStatement("insert intotb_name (col1,col2,col2,col4) values (?,?,?,?)"); perstmt.setString(1,var1); perstmt.setString(2,var2); perstmt.setString(3,var3); perstmt.setString(4,var4); perstmt.executeUpdate(); //prestmt是 PreparedStatement 对象实例 不用我多说,对于第一种方法.别说其他人去读你的代码,就是你自己过一段时间再去读,都会觉得伤心. 二.PreparedStatement尽最大可能提高性能. 语句在被DB的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中(相当于一个涵数)就会得到执行.这并不是说只有一个Connection中多次执行的预编译语句被缓存,而是对于整个DB中,只要预编译的语句语法和缓存中匹配.那么在任何时候就可以不需要再次编译而可以直接执行.而statement的语句中,即使是相同一操作,而由于每次操作的数据不同所以使整个语句相匹配的机会极小,几乎不太可能匹配.比如: insert into tb_name (col1,col2) values('11','22'); insert into tb_name (col1,col2) values('11','23'); 即使是相同操作但因为数据内容不一样,所以整个个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存. 当然并不是所以预编译语句都一定会被缓存,数据库本身会用一种策略,比如使用频度等因素来决定什么时候不再缓存已有的预编译结果.以保存有更多的空间存储新的预编译语句. 三.最重要的一点是极大地提高了安全性. 即使到目前为止,仍有一些人连基本的恶义SQL语法都不知道. String sql = "select * from tb_name wherename= '"+varname+"' and passwd='"+varpasswd+"'"; 如果我们把[' or '1' ='1]作为varpasswd传入进来.用户名随意,看看会成为什么? select * from tb_name = '随意' and passwd = '' or '1' = '1'; 因为'1'='1'肯定成立,所以可以任何通过验证.更有甚者: 把[';drop table tb_name;]作为varpasswd传入进来,则: select * from tb_name = '随意' and passwd = '';drop table tb_name;有些数据库是不会让你成功的,但也有很多数据库就可以使这些语句得到执行. 而如果你使用预编译语句.你传入的任何内容就不会和原来的语句发生任何匹配的关系.只要全使用预编译语句,你就用不着对传入的数据做任何过虑.而如果使用普通的statement,有可能要对drop,;等做费尽心机的判断和过虑.