Oracle数据库SQL重要语法总结

【SQL语句分类】

DQL—数据查询语言（SELECT）

DML—数据操作语言（INSERT/ UPDATE/ DELETE/ MERGE）

DDL—数据定义语言(CREATE/ ALTER/ DROP/ TRUNCATE)

DCL—数据控制语言（GRANT/ REVOKE）

TCL—事务控制语句（COMMIT/ ROLLBACK/ SAVEPOINT）

 

【查询基础】

1、使用连接符将属性值连起来：select ename || ' ''s job is '||job as job from emp;

         【注：这里要注意s前面的两个单引号，如果使用一个会出现问题】

2、取消重复（distinct） ：select distinct job from emp;

3、取别名：select ename as "姓名" from emp;

4、查询表结构： desc emp;

5、条件查询where +　运算符

(1) between ...and...

(2) in(*, *, *, ....)

(3)like   ，用于匹配字符串  （'%' //匹配任意多个字符； '_'     //匹配一个字符）

(4) is null

(5) AND, OR, NOT IN/ between/ like/， is  not  null

6、条件显示查询结果

（1）ORDER ＢＹ：　desc(降序)；asc (默认升序)     [null升序排在最后]

 

【函数】

1、字符函数

（1）      LOWER(column|expression);  UPPER();   INITCAP()//首字母大写

（2）      CONCAT,连接两个值，等同于||

                CONCAT(column1|expression1,column2|expression2);

（3）      SUBSTR(‘String’, 1, 3) = ‘Str’//取从第1个位置开始，len=3的子串

                【注：oracle的字符串默认是从第1个位置开始】

（4）      LENGTH(‘String’) = 6

（5）      INSTR(s1, s2, [n1], [n2]) //返回s1中，子串s2从n1开始，第n2次出现的位置，默认n1、n2 = 1；   INSTR(‘String’, ‘r’) = 3

（6）      LPAD(sal, 10, ‘*’) =******50000;   RPAD();      【左对齐，右对齐】

（7）      TRIM(‘S’ FROM ‘SSMITH’) =MITH  //去除字符串头尾的字符‘S‘

                 使用参数：TRIM(LEADING | TAILING | BOTH 'S'  FROM  'SSMITH')

                 LEADING：去除头部字符； TAILING:去除尾部字符；  BOTH：头尾都去除

（8）      REPLACE(‘abc’. ‘b’, ‘d’) = ‘adc’   //替换

（9）      To_char(str, [fm])     将属性转变成字符串，也可以自己添加转换的格式（具体格式需要看属性的具体类型）

                 Select ename,to_char(hiredate, ‘yyyy-mm-dd’) as d from emp;

 

2、数字函数

(1) ROUND(列名|表达式, n),   将值四舍五入到小数点后第n位

(2) TRUNC(列名|表达式, n),    截取到小数点后第n位（不四舍五入）

(3) MOD(列名|表达式, n),    表达式对n取余

 
3、日期函数

(1)SYSDATE    //返回当前时间

        select  to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') as time from DUAL;

(2)MONTHS_BETWEEN  //返回两个日期相隔的月数

         select ename,hiredate,

         round(months_between(to_date('01-10-3050','dd-mm-yyyy') , hiredate)) as months  from emp;

(3)ADD_MONTHS(date, n)     //在指定日期的基础上加上相应的月数

(4)NEXT_DAY(date,num)    

       返回某一个日期的下一个指定日期，求出date之后一周内某天num的日期，num可以是一个有效的表示星期几的数字，1为星期天

        也可以直接输入如下所述的数据：NEXT_DAY(date, '星期一')

(5)LAST_DAY(date)   //返回date所在月的最后一天

(6) ROUND(date,  ['CC' | 'YY' | 'MM' | 'DD' | 'HH24' | 'MI' | 'SS' ])日期的四舍五入，默认格式是DD

(7)TRUNC(date[,’fmt’])   //截取, 默认格式是DD，将date截取为最近的天

(8)EXTRACT:返回日期类型中指定年、月、日

      EXTRACT(YEAR | MONTH | DAY from  hiredate)

 

4、转换函数

(1) TO_CHAR(date | number [, ‘fmt’]) 把日期类型、数字类型转换为字符类型

 

          常用日期格式说明：

         YYYY：4位数字表示年份

         YY：2位数字表示年份，但无世纪转换

         RR：2位数字表示年份，有世纪转换

                当前年份0-49：指定年份0-49（当前世纪），指定年份50-99（上一个实际）

                当前年份50-99：指定年份0-49（下一个世纪），指定年份59-99（当前世纪）

         YEAR：年份的英文拼写

         MM：2位数字表示月份

         MONTH：月份的英文拼写

         DY：星期的英文前三位字母

         DAY：星期的英文拼写

         D：数字表示一星期的第几天，星期天 = 1

         DD：数字表示一月中的第几天

         DDD：数字表示一年中的第几天

         AM/ PM：上下午表示

         HH/  HH12/  HH24：小时

         MI：分钟

         SS：秒、

 

         TH：显示数字表示的英文序数词

         SP：显示数字表示的拼写

         SPTH：显示数字表示的序数词的拼写

 

         数字的具体格式

         9：一位数字

         0：一位数字或前导0

         $：显示为美元符号

         L：显示按照区域设置的本地货币符号

         . ：小数点

         ，：千分割符

 

【注意：进行数字类型到字符类型转换时，格式中的宽度一定要超过实际列宽度，否则会显示###，小数点后如果小于实际宽度，进行四舍五入处理。】

 select ename,to_char(sal,'$99,999.00') as salary from emp;

      

(2)TO_DATE（）    转换为时间格式

         select  to_date('2006-10-10','yyyy-mm-dd') + 15  from  DUAL;

 

5、通用函数

(1)NVL(expression1, expression2),         如果expression1为空，显示expression2

         【注意：数据格式可以是日期、字符、数字，数据类型必须匹配】

(2)NVL2(expression1,  expression2,  expression3),

         如果expression1不为空，显示expression2，如果为空，显示expression3

(3)NULLIF(expression1, expression2),         如果两个参数不相等，返回第一个参数，如果相等，返回空

(4)COALESCE(expression1, expression2, …..,expression n),         返回第一个不为空的参数

(5) CASE的使用

         selectename, deptno,

         (case deptno

         when 10 then '部门10'

         when20 then '部门20'

         when 30 then '部门30'

         else '无'

         end)dept

         from emp;

 

         也可以使用case判断取值范围

         selectename, sal,

         (case

         when sal < 1000 then '少'

         when sal > 1000 then '多'

         else '刚好'

         end) as level1

         from emp;

 

 
【多表查询】

1、 等值连接

select ename, job from dept, emp where dept.deptno = emp.deptno;

为表格取别名

select ename, job from dept D,  emp E where D.deptno = E.deptno;

【注意：如果已经定义了表别名，就只能使用别名，不能使用原名】

2、 外连接

左外连接： select ename, dname from emp,  dept where emp.deptno =dept.deptno(+);

右外连接： select ename, dname from emp, dept where emp.deptno(+) =dept.deptno;

【注意：没有+的表，会把内容显示完全】

3、 交叉连接CROSS  JOIN（生成笛卡尔积）

select ename, dname from emp CROSS  JOIN dept;

4、 自然连接 NATURAL  JOIN

【连接条件：两个表中的值和数据类型都相同的同名列，如果列名相同数据类型不同则报错】

5、 USING子句

USING(column_name)

使用条件：两个表中存在两个以上相同的列（若只有1列，自然连接即可）

【注意：不要与自然连接NATURAL  JOIN重复使用,  】

    select ename, deptno from emp join  dept using(deptno);   --别忘记添加括号 和  关键字join

6、 ON子句

        select ename,dname from emp join dept on emp.deptno = dept.deptno;

7、 左外连接：LEFT OUTER JOIN

       select ename, dname from emp  left outer  join dept on emp.deptno  = dept.deptno;

8、 右外连接：RIGHT OUTER JOIN
9、 全连接：FULL OUTER JOIN

       select ename, dname from emp  full outer join  dept on emp.deptno = dept.deptno;

【集合运算】

1、 UNION联合运算

      去掉重复行，默认排序

      select ename,sal, deptno from emp

       where sal > 1500

       UNION

      select ename, sal, deptno from emp

      where deptno in(10, 20);

2、 UNION ALL

      不去重复，默认情况下不排序

 

【分组函数】

1、 MIN/ MAX

      可以对数字，也可以对字符

       selectmin(sal), max(sal) from emp;

      select min(ename), max(ename) from emp;

2、 SUM/ AVG

      select sum(sal), avg(sal) from emp;      

3、 COUNT

      select count(*)from emp;

4、 DISTINCT取消重复

      select  count(distinct deptno)  from emp;

5、 空值处理问题

      select avg(comm)from emp;  //空值不算在内

      select avg(nvl(comm, 0))  from emp;  //把空值算成0

6、 GROUP BY

      select deptno,sum(sal) from emp group by deptno;

      select job,  mgr, avg(sal) from emp group by job, mgr order by job;

【注意：group by字句可以不出现在select中；select子句中的出现的飞分组函数列必须在GROUP BY子句中出现】

7、  HAVING子句（完成组函数筛选判断） 

      select job, avg(sal)  from emp where avg > 1000 group by avg;

      (错误，此处不能使用where)

      select job, avg(sal) from emp group by job  having avg(sal) > 1000;

      (改为使用having， having中不能使用别名)   

 

【select的执行过程：FROM--WHERE--GROUP BY--HAVING --SELECT--ORDER BY】

 

 

【子查询】

1、  单行子查询：使用单行操作符，where可以出现在having中

2、  多行子查询，多行操作符：IN, ANY, ALL

3、  子查询中有空值

例如：select ename, sal from  emp

      where empno not in ( select mgr  from emp where mgr is not null);

      结果为：未选定行

      原因：子查询中返回值中包含有空值（恰好NOT IN操作符对空值不忽略）

4、  EXISTS, NOT EXISTS

   EXISTS子查询如果有记录找到，子查询语句不会继续进行，返回true

例如：select ename from emp  e

      where exists(select '1' from emp where manager_id = e.empno)

      这里‘1’只是占位用，无实际意义【为表取别名  不能用as】

      NOT EXISTS 操作符运算方法与NOT IN不同，只会返回true 或false，不会返回空值，所以不需要考虑子查询中的空值问题

 

  
【数据操作DML】

1、  INSERT插入

      insert into dept values(60, 'qqq', 'xxx');

      insert into dept(deptno, loc) values(70, 'sss');

      insert into hhh select * from eee where XXX(条件)

2、UPDATE

   update emp set deptno = 10 where ename ='SCOTT';

3、  DELETE 删除

      delete from emp where empno = 7788;

      delete 不能删除被其他表引用的数据（违反完整性约束条件的数据）

4、  MERGE 合并数据

      根据指定条件执行插入或者更新操作。

      如果条件满足执行更新操作，否则执行插入操作。

      merge into T T1

      using(select '1001' as a, 2 as b from fual)T2

      on(T1.a = T2.a)

      when matched then

      update set T1.b = T2.b

      when not matched then

      insert(a,b) values(T2.a, T2.b);

5、  ROWID

      是表中虚拟的列,包含该行数据的物理位置信息

      select rowid, dname from dept;

      //AAAMgxAAEAAAAAMAAA qqq

      进行快速定位

6、  事务的特征：ACID

原子性Atomicity：独立的工作单元，要么全都成功，要么全都失败

一致性Consistency：一旦事务完成，不管成功还是失败，整个系统处于操作规则的统一状态，即数据不会损坏

隔离性Isolation:不会被其他事务干扰，所以事务应被隔离起来

持久性Durability：一旦事务提交，对数据库中的数据改变就是永久的

7、  事务的控制

显示控制：显示提交（commit），显示回滚（rollback）

隐式控制：隐式提交（执行DDL或DCL语句，从SQL*PLUS使用EXIT或ＱＵＩＴ命令退出），隐式回滚（从SQL*PLUS强行退出，客户端到服务器端异常中断，系统崩溃）

8、  事务：

      显示提交：commit(commit之后，rollback就没有意义了)

      显示回滚：rollback

      设立保存点：savepoint//   (返回保存点  ：rollback to)

      例如：

      insert into dept values(60, 'xxx', 'xxx');

      savepoint insert_a;

      insert into dept values(80, 'xxx', 'xxx');

       rollback to insert_a;

      即取消insert intodept values(80, 'xxx', 'xxx')；操作

【表和约束】

1、  命名：

（1）       必须由字母开头，长度在1-30字符之间

（2）       名字中只能包含A-Z，a-z，0-9，_（下划线），$, #

（3）       不能使用保留字

（4）       名字大小写不敏感

2、  建表

      create table dossier(

      id number(4),

      cname  varchar2(20),

      birthday date,

      stature  number(3),

      weight  number(5,2),

      country_code  char(2)  default '01');

 

      create table user_table(

      user_id number(9) PRIMARY KEY,

      user_name  varchar2(50),

      user_passwd  varchar2(50) NOT NULL,

      user_state varchar2(1) NOT NULL);

 

(用子查询的方式创建表)

      create table dept10

      as

      select empno, ename, sal + 1000 as newsal

      from emp

      where  deptno = 10;

 

3、添加列

不能指定添加位置，成为最后一列

     alter table dossier add(sex char(1));

 

更改列属性

    alter table dossier modify(sex char(2));

 

默认值

     alter table dossier modify (sex default '男');

 

 

删除列

1、alter table dossier drop column sex;

2、alter table dossier drop (country_code);

 

3、删除表

   drop table table_nale

重命名表

   rename  dossier to haha;

 

4、约束

NOT NULL非空

UNIQUE唯一值

PRIMARY KEY主码

FOREIGN KEY外表中列的引用

CHECK指定一个必须为真的条件

 

5、  相关数据字典

select table_name from user_tables;

查询数据字典结构

desc user_tables;

6、  数据说明补充，char，varchar，varchar2

 (1)varchar和varchar2的区别在于后者把所有字符都占两字节，前者只对汉字和全角等字符占两字节。

（2）nvarchar和nvarchar2的区别和上面一样，   与上面区别在于是根据Unicode   标准所进行的定义的类型，通常用于支持多国语言类似系统的定义。   

1.char    char的长度是固定的，比如说，你定义了char(20),即使你你插入abc，不足二十个字节，数据库也会在abc后面自动加上17个空格，以补足二十个字节；    char是区分中英文的，中文在char中占两个字节，而英文占一个，所以char(20)你只能存20个字母或10个汉字。    char适用于长度比较固定的，一般不含中文的情况   

 2.varchar/varchar2    varchar是长度不固定的，比如说，你定义了varchar(20),当你插入abc，则在数据库中只占3个字节。    varchar同样区分中英文，这点同char。    varchar2基本上等同于varchar，它是oracle自己定义的一个非工业标准varchar，不同在于，varchar2用null代替varchar的空字符串    varchar/varchar2适用于长度不固定的，一般不含中文的情况  

3.nvarchar/nvarchar2   nvarchar和nvarchar2是长度不固定的    nvarchar不区分中英文，比如说：你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合，这个20定义的是字符数而不是字节数    nvarchar2基本上等同于nvarchar，不同在于nvarchar2中存的英文字母也占两个字节   nvarchar/nvarchar2适用于存放中文