SQL语句

 任何含有NULL的表达式结果都是NULL

--创建表create
create table tablename{
        columnname1 datatype (NOT Null)(primary keys),
        columnname2 datatype ,
        ....
        };

--改变表结构alter
alter table tablename add column columnname datatype;            //在表中添加一列

alter table tablename drop column columnname datatype;            //在表中添加一列

--删除数据对象drop
drop table tablename;                     //删除表
drop view  tableview;                     //删除视图
drop index tableindex;                    //删除索引

--添加数据insert
insert into tablename (column1,column2)  values (value1,value2);
如果insert语句中没有列出列名，那么value子句应包含与表中所列相同顺序的所有列的值(字符要加单引号)

--删除数据delete                  (condition : columnname=value)
delete from tablename where condition and condition ..    //从表中删除满中condition的行
condition子句是可选的，如果condition没有指定，则会删除所有行.

--更新数据 update
update tablename set columnname1=value1 ,columnname2=value2 where condition

--从表中检索数据select
select * from tablename ;             //选取表中所有列
select columnname1,columnname2,.. from tablename ;          //从表中选取指定列
select columnname column from tablename ;//给columnname起别名COLUMN
select columnname "column" frome tablename;//给columnname起别名column
select columnname1||columnname2  from tablenaem;//列出columnname1与columnname2的字符串组合
select columnname||'abcde' from tablename; //在columnname值后加字符串abcde
select columnname||'abc''de' from tablename; //在columnname值后加字符串abc'de (两个单引号代替一个单引号)

--条件语句where        (having)
select * from tablename where condition
六大关系运算符{=,!=(<>),<,>,<=,>=}
                                       在计数时，where子句本身也在select运行是运行，而计数在选择种逻辑分组之后获得，所以在分组之后要用having添加条件

--排序order by
order by columnname ;//升序（ASC）
order by columnname Desc;//降序（Desc）

--分组group by
group byt columnname;

 

~~~~~聚合函数
五个重要的聚全函数{sum,avg,max,min,count}

--总合sum
select sum(column) from tablename  ;    //列出指定列的总合

--平均数avg
select avg(column) from tablename  ;     //列出指定列的平均数

--最小值min
select min(column) from tablename  ;     //列出指定列的最小值

--最大值max
select max(column) from tablename  ;     //列出指定列的最大值

--统计count
select count(column) from tablename;     //列出指定列的数目

主键(primary)，外键(foreign)[唯一(unique)]，约束(checks)

--逻辑运算符(not,and,or)
not的运算优先级比（not,or）的高

--子查询
select * from (selcet .....) where condition

--连接[内连接，左外连接，右外连接，全外连接，自连接]
内连接     select column1,column2 from tablename1  tablename2 where condition
           select column1,column2 from tablename1 inner join tablename2 on condition
左外连接   select column1,column2 from tablename1 left outer join tablename2 on condition
右外连接   select column1,column2 from tablename1 rigth  outer join tablename2 on condition
全外连接   select column1,column2 from tablename1 full outer join tablename2 on condition
自连接     select column1,column2 from tablename tablename1 left join tablename tablename2 on condition

--连合(union,nnion all,intersect,minus)
union --重复部分只写一遍
union all--重复部分写两遍
intersect --交集
minus --左圆去除重复部分
select id,姓名 from system.zzy_oneself union  select id,姓名 from system.zzy_one;
select id,姓名 from system.zzy_oneself union all select id,姓名 from system.zzy_one;
select id,姓名 from system.zzy_oneself intersect select id,姓名 from system.zzy_one;
select id,姓名 from system.zzy_oneself minus  select id,姓名 from system.zzy_one

/*聚集函数*/
select avg(sum) from system.ut_penalty;
select count(sum) from system.ut_penalty;
select sum(sum) from system.ut_penalty;
select max(sum) from system.ut_penalty;
select min(sum) from system.ut_penalty;
/*数值函数*/
select abs(-200) from system.ut_penalty;
select abs(400) from system.ut_penalty;
select floor(45.04568) from system.ut_penalty;
select floor(-45.04568) from system.ut_penalty;
select mod(9,2) from system.ut_penalty;
select mod(-9,-2) from system.ut_penalty;
select power(2,2) from system.ut_penalty;
select power(-2,2) from system.ut_penalty;
select power(2,-2) from system.ut_penalty;
select power(-2,-2) from system.ut_penalty;
select round(45.236,2) from system.ut_penalty;
select round(45.236,-1) from system.ut_penalty;
/*字符函数*/
select instr('aslkaslkasl k aslk','as',2,2) from system.ut_penalty;
select length(trim('      12356    ')) from system.ut_penalty;
select lpad('asd',20,'%') from system.ut_penalty;
select replace ('1213141516','14','89') from system.ut_penalty;
select translate ('1213141516','14','89') from system.ut_penalty;
select lower('123456ASDFRTG') from system.ut_driver;
select ltrim('1213141516','123') from system.ut_driver;
select upper('adfwfgs123') from system.ut_driver;
select substr('asdeftgyhujikol',5,5) from system.ut_driver;
select initcap('asdf') from system.ut_driver;
/*转化函数*/
select to_date('2009-04-23 10:59:30','YYYY-MM-DD HH24:MI:SS')from system.ut_driver;
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')from system.ut_penalty;
/*其他函数*/
select name,nvl(parentid,'$$$') from system.zzy_tree ;        空值变为$$$
select name,nvl2(parentid,parentid,'$$$') from system.zzy_tree ;          空值变为$$$
select * from system.zzy_tree where lnnvl(system.zzy_tree.parentid<3);    与下一条互补
select * from system.zzy_tree where system.zzy_tree.parentid<3;

------数据类型
  标量:数字型(number,binary_ingeger,PLS_integer),
       字符型(char,varchar2,nvarchar2,string)，
       布尔型(boolean),
       日期型(date,stamp--时间托)
  复合:
  引用:
  lob: