SQL学习笔记1

sql知识点学习

1.month_between

 ---------month_between-------
select months_between(sysdate,to_date('20190920','yyyy-mm-dd')) from dual;
select add_months(sysdate,2) from dual;
select Next_day(sysdate,'星期三') from dual;
select last_day(sysdate) from dual;
select last_day(to_date('20190803','yyyy-mm-dd')) from dual;
select to_char(sysdate,'yyyy') from dual; 
select to_char(sysdate,'yyyy-mm-dd') from dual; 
select to_char(sal,'L999,999,999') from emp; 
select to_char(sysdate,'D') from dual;--//返回星期 
Select to_date('20090210','yyyyMMdd') from dual; 

高级查询开窗函数

 ------------高级查询 开窗函数--------------
 select ename,sal,max(sal) over(), min(sal) over() from emp; 
 select ename,sal,sum(sal) over(), sum(sal) over(order by ename) from  --；连续求和
 select deptno,sal ,sum(sal) over (partition by deptno order by ename) as s from emp;--分部门连续求和

数据字典

-----------数据字典
 select table_name from all_tables where owner = 'SCOTT' --查询用户scott下的所有表
 select * from all_tab_columns where table_name = 'emp' --查询表emp的所有字段
 select * from all_constraints where table_name = 'EMP' --查询表的约束
 select * from sys.all_ind_columns where table_name = 'EMP' --查询表的索引列
 select table_name ,comments from dictionary where table_name like '%TABLE%';  --数据字段视图

DDL该表表结构

create table student(
sid number(10),
sname varchar2(10)
)tablespace tt;--创建表的时候指定表空间，先得创建表空间
--子查询创建表
create table myemp as select * from emp;--创建表的表结构以及数据
create table myemp as select * from emp where empno = '';--筛选出表中需要的数据
create table myemp as select * from emp where 1=2 --只创建表的结构
select * from myemp
--添加字段
alter table table_name add table_column data_type;
--改变字段
alter table table_name rename column table_column to new_table_column
--删除字段
alter table table_name drop column column_name
--清空表数据
truncate table table_name
--删除表
drop table table_name
--重命名表
rename table_name to new_table_name

索引

--创建索引
create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
--删除索引
drop index pk_dept1;
--SQL优化
--删除一张表的重复记录 可以使用唯一字段 
--methhod1
delete from table where id not in (select min(id) from t group by name,age)
--method2
delete from t where id in(Select distinct a2.id from t a1,t a2 where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age);
--methos3
delete from t a1 where not exists(select *  
from t a2  where a1.id>a2.id and a1.name=a2.name and a1.age=a2.age );