测试进阶sql

一、oracle常用数据类型

二、日期查询

 select * from table where  date_create >to_date('2019-03-12 20:07:19','yyyy-mm-dd hh24:mi:ss')

三、sql执行顺序

查询中用到的关键词主要包含六个，并且他们的书写顺序依次为： select>from>where>group by>having>order by，其中select和from是必须的，其他关键词是可选的
这六个关键词的执行顺序与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行：from>where>group by>having>select>order by

每个步骤都会为下一个步骤生成一个虚拟表，这个虚拟表将作为下一个执行步骤的输入，参考来源：https://blog.csdn.net/dz77dz/article/details/115111559

四、聚合函数（avg、count、sum）

聚合函数只能在以下位置作为表达式使用：
（1）select语句的选择列表（子查询或外部查询）
（2）compute或compute by 子句
（3）having子句

---COUNT：如果列上存在null，那么null是不会进行统计

　　　　　　如果使用DISTINCT，那么列上如果有重复，重复的记录也不会统计。

SELECT CONUT(*),COUNT(empo),COUNT(ename),COUNT(comm),COUNT(DISTNCT job) FROM emp;

---COUNT(*|DISTINCT 列)      求出全部的记录数

　SUM(列)                              求出总和，操作的列是数字

　AVG(列)                               平均值

　MAX(列)                              最大值

　MIN(列)                               最小值

五、窗口函数

用于分区排序，用在select关键字后面。窗口函数关键字是 row_number () over ()

示例如下，partition by 是分区关键字，order by 是排序关键字

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

-- 查询学生平均成绩及其名次  
select 学号 ,avg(成绩),
row_number () over( order by avg(成绩) desc)
from test.score
group by 学号  ;

-- 按各科成绩进行排序，并显示排名
select 课程号 ,
row_number () over(partition by 课程号 order by 成绩 ) as 排序
from test.score ;

-- -查询每门功成绩最好的前两名学生姓名
select a.课程号 ,b.姓名 ,a.成绩,a.ranking​ from 
(    select 课程号 ,学号 ,成绩 ,
    row_number () over(partition by 课程号 order by 成绩 desc) as ranking​
    from  test.score) as a 
inner join test.student b on a.学号 =b.学号 
where a.ranking​ <3 ;

六、case when

1、--简单Case函数  

CASE sex  

WHEN '1' THEN '男'  

WHEN '2' THEN '女'  

ELSE '其他' END  

2、--Case搜索函数 ，简单Case函数的写法相对比较简洁，但是和Case搜索函数相比，功能方面会有些限制，比如写判断式

CASE WHEN sex = '1' THEN '男'  

WHEN sex = '2' THEN '女'  

ELSE '其他' END 

3、在使用mysql的过程中出现判断null时遇到的问题的解决办法：https://blog.csdn.net/Dax1n/article/details/83037839，如下示例

SELECT
	bill_id,
	( CASE id IS NULL WHEN TRUE THEN a_id ELSE b_id END ) AS id 
FROM  TABLE
WHERE   aaa = '123';

4、使用pymysql模块拿到的时间问题

SELECT
	CAST( begin_time AS CHAR ) AS begin_time 
FROM TABLE 
WHERE  is_valid = "1"

5、MySQL的where条件字符串区分大小写的问题   https://blog.csdn.net/lz6363/article/details/107441840
在查询语句where条件字符串字段前加上关键字：BINARY

七、if语句

SELECT
     SUM(IF(test_field=0, 1, 0.5 ))
FROM
     Table 

解释：如果test_field=0，则显示1，否则显示0.5，最后求和

八、limit和offset

mysql里分页一般用limit来实现
1. select* from article LIMIT 1,3
2. select * from article LIMIT 3 OFFSET 1
上面两种写法都表示取2,3,4三条条数据
　　当 limit后面跟一个参数的时候，该参数表示要取的数据的数量；
　　当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

九、重复记录查询

select * from tablename where 重复字段1,重复字段2 in

(select 重复字段1,重复字段2 from tablename group by 重复字段1,重复字段2 having count(*)>1)

十、练习

sql经典面试题：https://zhuanlan.zhihu.com/p/38354000

十一、复制表

1. create table table1 as select  * from table2  where 1=2;
创建一个表结构与table2一模一样的表，只复制结构不复制数据；
2.create  table table1  as  select *   from table2  ;
  创建一个表结构与table2一模一样的表,复制结构同时也复制数据；
3.create  table table1(columns_a,columns_b) as select  columns1,columns2  from table2; 
创建一个表结构与table2一模一样的表,复制结构同时也复制数据,但是指定新表的列名，这种格式也用于复制table2部分字段；

后面两种格式，如果后面跟上合适的查询条件，可以只复制符合条件的数据到新的表中，例如：

create  table table1  as  select *   from table2  where columns1>=1;

可能的情况下，建议用第二种方式，这种方式不仅能一次完成建表和插入数据，而且效率高，特别是当复制的数据量比较大时，可以避免大量数据存在回滚空间中等待提交。

十二、创建视图

create or replace view   创建的视图名  as
select  可查看的字段名
from  库名.表名
where 条件

十三、with  as用法

with  a  as（ select *from  table where data>sysdate）

select * from a