建表

create table 表名(
       字段名 类型(长度) primary key, --约束条件
       字段名 类型(长度)        
       );

插入（行）

==================插入单条数据=========================
insert into table_1 values(280,6,2000,3000,4000,5000,6000);
===============用 union 插入多条数据==================
insert into table_1 
select 50,‘公关部‘,‘*‘ 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，select * from deal_info where deal_time is not null
6.or、and:满足多个条件

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

having子句

Having 子句与where子句的功能类似，都是对行进行筛选。
where搜索条件是在分组操作之前对记录进行筛选，然后再由group BY 对筛选后符合条件的行进行分组；而Having搜索条件则是对分组操作之后得到的行进行筛选操作。

1.Where 子句用来筛选From 子句中指定的操作所产生的行；

2.Group By 子句用来分组Where子句的输出；

3.Having 子句用来从分组的结果中筛选行。

没有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 返回短的日期格式

常用转换函数：

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个字符

row_number() over() -- 分组排序
row_number() over(partition by 分组列 order by 排序列 desc)
--over()里头的分组以及排序的执行晚于 where 、group by、  order by 的执行。

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 是您希望添加的间隔数--对于未来的时间，此数是正数，对于过去的时间，此数是负数。

sql常用语句整理