数据库深化2

目录

常用查询比较分析

嵌套查询和自联接

关于count统计

case when表达式的应用

大数据量的查询

sql语句顺序

---

常用查询比较分析

嵌套查询和自联接

select first_name,salary
from employees
where employee_id=(
   select manager_id
   from employees
   where employee_id=206
)

where子句中嵌套一个查询语句，查询效率低

可以替换为，自联接查询

select m.first_name,m.salary
from employees e join employees m
on e.manager_id=m.employee_id
where e.employee_id=206

将一张表看作两张表，进行join on联接，消除嵌套，提高了查询效率

关于count统计

select count(*)
from employees
where commission_pct is not null;

select count(1)
from employees
where commission_pct is not null;

 这两中count中不同的值的写法，在使用中结果不会有任何区别。count（*）表示统计所有符合后续筛选条件的行数，不排除null的行，count（1）也一样不排除null，因此结果上不会有任何区别。

但是阿里编程规范里有

 严禁使用count（1或者2这种常量）或者count（employee_id）这种列名的来替换count(*)。

关于count(列名)会统计该列不为空的行，即会排除null，两个列明则两列都不为null才会被统计。

他们的效率

count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。

因此在列为主键的情况下，主键肯定不为空的，count（主键）要比count(*)效率高。

还有一点，将count应用于两张表，比如

select count(*) from employee ,department

 将会将结果排列组合，因此得到的行数应该是两个表查出来数据的乘积。

case when表达式的应用

select first_name,salary,
      (case when salary>=10000 then '偏高'
            when salary>=8000 then '中等'
            else '偏低' end) level
from employees

 这个语句用的很少，写一下，作用就是跟java的switch case一样，符合不同条件的替换为不同的值，这里查出工资大于等于10000的会显示为偏高，8000-10000的显示为中等，以此类推。

将case与统计很好的结合

select  sum(case when salary>=10000 then 1 else 0 end ) '大于等于10000',
        sum(case when salary<10000 then 1 else 0 end ) '小于10000'
from employees

 就会有以上这种语句，sum（1）与count（1）是一样的，都是假设有一列，值全都是1，统计这一列不为空或者不为0的行。那么用case就可以控制这一列是0（空）还是1，用来排除一些行。如上代码一样。

大数据量的查询

咱肯定不可能把整张表（假设这张表有几百万条数据）的数据全部查询返回去。

方法一：分页查询limit

<!-- 查询第一页数据，每页十条-->
select * from employee limit 10,10 

 这种limit方法固然可以减少查询返回的数据，但是查询还是全表查询。每次查一整张表，效率极其低下。

方法二：虚表

select * from table(select employee_id from employee order by employee_id limit 10,10) e1 join employee e2 on e1. employee_id=e2.employee_id

 先查询整个表的id主键，并且分页减少数据量，然后再查询需要的数据。这样的好处是减少了查询量，对于数据量很大的时候，可以增加查询效率。

sql语句顺序

select(7)
from (1)
join (3)
on (2)
where(4)
group by(5) 
having(6)
order by (8)
limit(9)

 括号内为顺序。首先执行的是from，要知道你想查或者操作哪张表。然后是on，链接条件，然后是join联接，再之后是where子句，筛选条件，在这个基础上以及得到了很多行了，然后将这些行根据group by子句的内容，进行分组，然后根据having语句的进一步筛选函数的条件，最后select哪几个字段。这里其实基本就结束了，再看看有没有排序order by条件，有，就按条件给结果排一下序，最后看看有没有limit，有limit就按limit的，分页，然后把要的那一页返回，其余舍弃。