sql常用语句整理（oracle）
建表
create table 表名(
字段名 类型(长度) primary key, --约束条件
字段名 类型(长度)
);
插入（行）
==================插入单条数据=========================
insert into table_1 values(280,6,2000,3000,4000,5000,6000);
===============用 union 插入多条数据==================
insert into table_1
select 50,'公关部','taiwan' from dual
union
select 60,'研发部','japan' from dual
union
select 70,'培训部','uk' from dual
==================插入一个查询结果集==========================
insert into table_1 select 条件 from table_2;
--备份一个表
==================向表中插入一个常量结果集=====================
INSERT INTO table_1 SELECT 's100106','卢俊义','男',23,
TO_DATE('2009-8-9 08:00:10','YYYY-MM-DD HH24:MI:SS'),'1001'
FROM DUAL;
select ... from dual,dual 表在系统中只有一行一列，为了select…from 的语法完整性而使用。
SELECT vale1, value2 into Table2 from Table1
--创建目标表table2并把table1中的数据复制到table2，table2 表不存在
select into from 和 insert into select都是用来复制表，两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建。insert into select from 要求目标表存在。
插入（列），更新
删除列：alter table 表名 drop column 列名;
添加列：alter table 表名 add 列名 类型（长度）;
修改列属性：alter table 表名 modify 列名 类型（长度）;
创建约束：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束内容
更新数据：UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
删除表
1、drop (删除表)：删除内容和定义，释放空间。简单来说就是把整个表去掉.以后要新增数据是不可能的,除非新增一个表。无法进行回滚操作。
drop table 表名称
2、truncate (清空表中的数据)：删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。无法进行回滚操作。
truncate table 表名称
3、delete (删除表中的数据)：delete 语句用于删除表中的行。delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。
delete from 表名称 where 列名称 = 值
拼接两个表的查询结果
(1)内连接：即等值连接
--简易写法
select a.字段名1,a.字段名2,d.字段名3 from table_1 a,table_2 d
where a.字段名4=d.字段名4 and 筛选条件
===========sql/92标准写法（推荐),INNER 可以省略==================
SELECT a.字段名1,a.字段名2,d.字段名3
FROM table_1 a INNER JOIN table_2 d ON a.字段名4=d.字段名4
WHERE 筛选条件
(2)LEFT OUTER JOIN：在内连接的基础上加上主表中的未匹配数据
(3)RIGHT OUTER JOIN ：在内连接的基础上加上被连接表的不匹配数据
(4)FULL OUTER JOIN:结合的LEFT JOIN，RIGHT JOIN的结果
--简易写法
select a.字段名1,a.字段名2,d.字段名3 from table_1 a,table_2 d where a.字段名4(+)=d.字段名4
--(+)：Oracle 专用的联接符，在条件中出现在左边指右外联接，出现在右边指左外
联接。
=================sql/92标准写法（推荐),outer 可以省略====================
select a.字段名1,a.字段名2,d.字段名3 from table_1 a right join table_2 d on a.字段名4(+)=d.字段名4；
select a.字段名1,a.字段名2,d.字段名3 from table_1 a left join table_2 d on a.字段名4(+)=d.字段名4 where 条件；
select a.字段名1,a.字段名2,d.字段名3 from table_1 a full join table_2 d on a.字段名4(+)=d.字段名4 where 条件；
(5)Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
(6)Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
实例：
select empno as 编码,ename as 名称,nvl(mgr,deptno) as 上级编码 from emp where sal>=2000
union all
select deptno as 编码，dname as 名称,null as 上级编码 from dept
--当数据集列不够时可以用null来填充该列的值
备份
1. 备份表
create table [备份名] as select * from [表名];
--根据结果集创建一个新表
2. 恢复表
insert into org_group select * from [备份名] ;
去除重复数据
一、数据库中的去重操作（删除数据库中重复记录的SQL语句）主要有三种方法
（1）、rowid方法
delete from table_1 a where rowid>(select min(rowid) from table_1 b where a.字段名1=b.字段名1)
/*根据字段名1删除重复数据*/
ROWID 伪列返回的就是该行的物理地址，ROWID 值可以唯一的标识表中的一行。
ROWNUM 标识的是查询结果中的行的次序，可以作为限制返回行数的条件。
（2）、group by 方法
适用于单独对某列进行去重。推荐使用group by。因为distinct会导致全表扫描，而group by如果索引建的恰当的话，会有性能上的提高。
--查数据，列出表中的重复的记录数，并按照字段1分组后找出表中字段1列出现次数大于一次的。
Select 字段1 from 表 Group by 字段1 Having count(字段1)>1
--删除表中字段1列所有重复的数据
Delete from 表 Group by 字段1 Having count(字段1)>1
（3）、distinct方法
两条记录或者多条记录的每一个字段值完全相同，这种情况去重复最简单，用关键字distinct就可以去掉。
SELECT DISTINCT 字段名 FROM 表名;
查询
where条件
1.数字比较：> < >= <= != <> ^=
2.between ..and.. 值在两者之间
3.in，exists：值在列表中
4.like：字符串的模糊匹配
5.is null:值为null，（is not null）
6.or、and:满足多个条件
having子句
Having 子句与where子句的功能类似，都是对行进行筛选。
where搜索条件是在分组操作之前对记录进行筛选，然后再由group BY 对筛选后符合条件的行进行分组；而Having搜索条件则是对分组操作之后得到的行进行筛选操作。
1.Where 子句用来筛选From 子句中指定的操作所产生的行；
2.Group By 子句用来分组Where子句的输出；
3.Having 子句用来从Group By的结果中筛选行。
没有Group By子句的情况下，使用Where子句会更高效。因为Where 子句能够事先把不必要的数据过滤掉，从而减少了在执行select时数据处理量。
但是，有些数据事先并不知道是否需要过滤掉，要根据结果才能确定，就必须使用having子句解决
统计
select 字段名,count(1) from 表 group by 字段名
count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）
select 字段1,sum(字段2) from 表 where 条件语句 group by 字段1
自关联查询
自连接查询其实等同于连接查询，需要两张表，只不过它的左表（父表）和右表（子表）都是自己。做自连接查询的时候，是自己和自己连接，分别给父表和子表取两个不同的别名，然后附上连接条件。
select * from emp t1,emp t2 where t1.mgr=t2.empno;
条件逻辑（筛选）
case函数：Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
第一种格式 : 简单Case函数
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2.
......
else 默认值 end
第二种 格式 :Case搜索函数
case
when 列名= 条件值1 then 选项1
when 列名=条件值2 then 选项2.
......
else 默认值 end
嵌套查询
在 SELECT、UPDATE、DELETE 语句内部可以出现 SELECT 语句。内部的 SELECT 语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时表；包括exists、in、any、all等
--单行单行单列子查询
select 字段名1,字段名2 from table_1
where 字段名3 比较运算符 (select 字段 from table_2 where 条件 )
比较运算符：=、>、<、>=、<=、<>等
All：只有当其所有数据都满足条件时，条件才成立
Any：只要有一条数据满足条件，条件就成立
=ANY：表示与子查询中的每个元素进行比较，功能与IN类似（然而<>ANY不等价于NOT IN）
>ANY：比子查询中返回结果的最小的要大（还包含了>=ANY）
<ANY：比子查询中返回结果的最大的要小（还包含了<=ANY）
ALL有如下三种使用形式:
ALL操作符有以下三种用法：
<>ALL：等价于NOT IN（但是=ALL并不等价于IN）
>ALL：比子查询中最大的值还要大（还包含了>=ALL）
<ALL：比子查询中最小的值还要小（还包含了<=ALL）
例子：
select 字段名1,字段名2 from table_1
where 字段名3 比较运算符 any(select 字段 from table_2 where 条件 )
with……as……
在比较复杂，数据量大的情况下，为了提高查询效率，需要用到一些子查询。一层一层嵌套的子查询不方便，显得sql语句比较繁琐，这时可以使用with语句，with as 相当于虚拟视图，其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，很多查询通过这种方法都可以提高速度。
--针对一个别名
with tmp as (select * from tb_name)
--针对多个别名
with
tmp as (select * from tb_name),
tmp2 as (select * from tb_name2),
tmp3 as (select * from tb_name3),
…
相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499)
select * from e;
--相当于建了e、d临时表
with
e as (select * from scott.emp),
d as (select * from scott.dept)
select * from e, d where e.deptno = d.deptno;
二、with的相关总结
1.with子句只能被select查询块引用，一般在with查询用到多次情况下。在引用的select语句之前定义,同级只能定义with关键字只能使用一次,多个用逗号分割。
2.with子句的返回结果存到用户的临时表空间中，只做一次查询，反复使用,提高效率。
3.在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。
4.最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割,with 子句的查询必须用括号括起来
5.前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。
6.with查询的结果列有别名，引用的时候必须使用别名或*。
oracle函数
格式化字符串:
yyyy-mm-dd,HH24:MI:SS,
ddd 年中的第几天
day 星期几，中文显示
WW 年中的第几个星期
W 该月中第几个星期
DL 返回长的日期格式
DS 返回短的日期格式
OFFSET X 跳过X个数据
LIMIT Y 是选取Y个数据
LIMIT X,Y X表示跳过X个数据，读取Y个数据
当LIMIT和OFFSET组合使用的时候，LIMIT后面只能有一个参数，
表示要取的的数量，OFFSET表示要跳过的数量 。
常用转换函数：
TO_CHAR(d|n[,fmt])
--把日期和数字转换为制定格式的字符串。fmt 是格式化字符串，使用双引号对非格式化字符进行引用
TO_DATE(x [,fmt])
--把一个字符串以设置的格式转换为一个日期类型
TO_NUMBER(x[,fmt])
--把一个字符串以设置的格式转换为一个数字
常用日期函数：
sysdate
select to_char(sysdate,'yyyy-mm-dd HH24:MI:SS') from dual
--日期+数字，表示若干天之后的日期。
SELECT SYSDATE,TO_CHAR(SYSDATE+1,'yyyy-mm-dd HH24:MI:SS') FROM DUAL;
--日期-数字，表示若干天之前的日期。
--日期-日期，表示两个日期间的天数，但是肯定是大日期-小日期
SELECT SYSDATE,TO_CHAR(SYSDATE-1/24,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --减1小时
SELECT SYSDATE,TO_CHAR(SYSDATE-1/24/60,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --减1分钟
SELECT SYSDATE,TO_CHAR(SYSDATE-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --减1秒
TRUNC（number,num_digits） --用于截取时间或者数值，返回指定的值
Number 需要截尾取整的数字；Num_digits 用于指定取整精度的数字。
Num_digits 的默认值为 0。
select trunc(sysdate) from dual ;--2011-3-18 今天的日期为2011-3-18
select trunc(sysdate, 'mm') from dual ; --2011-3-1 返回当月第一天.
select trunc(sysdate,'yy') from dual; --2011-1-1 返回当年第一天
select trunc(sysdate,'dd') from dual; --2011-3-18 返回当前年月日
select trunc(sysdate,'yyyy') from dual; --2011-1-1 返回当年第一天
select add_months(to_date('2018-01-01','yyyy-mm-dd'),12)-1 from dual; -- 2018-12-31 返回当年第一天
select trunc(sysdate,'d') from dual ; --2011-3-13 (星期天)返回当前星期的第一天
select trunc(sysdate, 'mi') from dual ; --2011-3-18 14:41:00 TRUNC()函数没有秒的精确
SELECT to_date('20180818','yyyymmdd')-to_date('20180727','yyyymmdd') FROM dual;
ADD_MONTHS(d,n)
--在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期。d 表示日期，n 表示要加的月数。n可以是负数。
ROUND(d[,fmt])
--返回一个fmt格式的四舍五入日期值，d 是日期，fmt是格式模型。默认 fmt 为 DDD，即月中的某一天。
LAST_DAY(d)
--返回指定日期当月的最后一天。
EXTRACT(fmt FROM d)
--提取日期中的特定部分。fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。
常用字符函数：
concat(str1,str2) --str1与str2字符串连接
replace(str1,str2,str3)
--str3替换str1中出现的所有str2，返回新的字符串，如果有某个参数为NULL，此函数返回NULL
/*并不会修改数据库中原始值*/
substr(x,a[,b]) --返回字符串中的指定的字符
这些字符从字符串的第a个位置开始，长度为b个字符；
如果a是负数，则从x字符串的末尾开始算起；如果b省略，则将返回一直到字符串末尾的所有字符
substrb('字符串',a,b) --按字节截取,一个汉字占两个字节
COALESCE()，NVL()函数,空值转换
1.COALESCE ( expression1, expression2 );
2.COALESCE ( expression1, expression2, ... expression-n );
--第二种可以包含n个表达式，表示如果第一个不为空取第一个，否则判断下一个
NVL(x,value)
--如果 x 为空，返回 value，否则返回 x。
NVL2(x,value1,value2)
--如果 x 非空，返回 value1，否则返回 value2。
常用聚合函数：一个集合（集或者多重集）为输入、返回单个值的函数
avg(x) --返回x的平均值，avg函数在计算时，不包含任何值为 null 的资料。
count(x) --返回统计的行数
max(x) --作用在同一列的一个Group上面的
min(x) --作用在同一列的一个Group上面的
sum(x) --返回x的总计值
length('字符串') --length按字符计，汉字、英文、数字都是1个字符
分析函数（开窗函数）
简介:开窗函数不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。在 ISO SQL 规定了这样的函数为开窗函数，在 Oracle 中则被称为分析函数。
开窗函数格式： 函数名(列) OVER(选项)
OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。
OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。
PARTITION BY 子句： OVER 关键字后括号中的可以使用 PARTITION BY 子句来定义行的分区来供进行聚合计算。与 GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。
ORDER BY子句：开窗函数中可以在OVER关键字后的选项中使用ORDER BY子句来指定排序规则，而且有的开窗函数还要求必须指定排序规则。
高级开窗函数/ 排名的实现ROW_NUMBER();rank() ,dense_rank()
rank(),dense_rank()语法：
RANK ( ) OVER ( [partition_clause] order_by_clause )
dense_RANK ( ) OVER ( [partition_clause] order_by_clause )
【功能】聚合函数RANK 和 dense_rank 主要的功能是计算一组数值中的排序值。
【参数】dense_rank与rank()用法相当，
【区别】dence_rank在并列关系是，相关等级不会跳过。rank则跳过
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名。
row_number() 函数语法：
ROW_NUMBER()
【语法】ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number() 返回的主要是“行”的信息，并没有排名
【参数】
oracle锁表的原因，及解决方案
锁表的原因 ：
第一种、 A程序执行了对 tableA 的 insert ，并还未 commite时，B程序也对tableA 进行insert 则此时会发生资源正忙的异常 就是锁表；锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）
解决方法：以SYSDBA登陆数据库，执行以下语句
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.OBJECT_ID;
--查看被锁的表
select a.session_id,b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;
--查看那个用户那个进程照成死锁,查看连接的进程 ,查出锁定表的sid,serial#
杀掉进程 sid,serial#
alter system kill session'153,28830';
还有一种，在外键上没有加索引引起的死锁，由于程序在主子表上删除数据，缺少索引导致行级锁升级为表级锁，最终导致大量的锁等待和死锁。
sqlserver常用日期函数
DATEADD(datepart,number,date)
getdate() --获得日期／时间信息。
datepart是日期的格式化字符串，date 参数是合法的日期表达式。number 是您希望添加的间隔数--对于未来的时间，此数是正数，对于过去的时间，此数是负数。
