sql的基本函数

oracle201

---

---

sql的基本函数

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2015-01-08 20:01:44

SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss ssxff6') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD
--------------------------------
2015-01-08 20:03:08 08.045234

在创建表的时候并不会并不会为表创建段，可以通过：

select segment_name,partition_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments where segment_name like '%STUDENT%';

是查询不到的，这是oracle默认是在插入数据以后才会分配段。

insert into student values(10000,'zhansan','2014-08-28');

再去查询就会有结果的：

SQL> select segment_name,partition_name,segment_type,tablespace_name,bytes,blocks,extents from user_segments where segment_name like '%STUDENT%';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
BYTES BLOCKS EXTENTS
---------- ---------- ----------
STUDENT
TABLE USERS
65536 8 1

插入时间格式有默认的格式：

SQL> select * from NLS_SESSION_PARAMETERS where PARAMETER like 'NLS_DATE_FORMAT';

PARAMETER
------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
yyyy-mm-ss

ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-ss';//当前会话有效

create table tab1 as select * from table2 where 1 > 2；//创建相同的表结构但是数据不会被复制。

truncate table student<=>delete from student的区别：

1. truncate是默认带了commit，而delete没有

2. truncate的清空是不被记录到日志中，而delete会写入日志中可以找回数据

insert into student select * from student where....；

update student set sname='A''B' where sname ='A';//插入单引号需要两个'

SQL> update student set sname='"zhangsan"' where sname='zhang''san';

1 row updated.

SQL> update student set sname='张三' where sname='"zhangsan"';

1 row updated.

SQL> select length(sname) from student;

LENGTH(SNAME)
-------------
4
4

SQL>

oracle排序可以使用列的序号进行排序：

SQL> select * from student order by 1,2;

SNO SNAME BIRTH
---------- ------------------------------ ----------
10000 张三▒ 2015-01-00
10001 lisi 1992-08-28

SQL>

在oracle中使用“是很少的但是在：

SQL> select sno as "学 号",sname from student;

学▒号▒▒ SNAME
------------ ------------------------------
10000 张三▒
10001 lisi

SQL>中使用了

SQL> select snoas '学号',sname from student;
select sno as '学号▒' ,sname from student
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

SQL> select '学号是：'||sno || '姓名是：' ||sname from student;

'学号是：'||SNO||'姓名是：'||SNAME
--------------------------------------------------------------------------------
学号是：10000姓名是：张三▒
学号是：10001姓名是：lisi

SQL>//连接字符

------------------------------------------

SQL> select round(12.23,1) from dual;

ROUND(12.23,1)//四舍五入
--------------
12.2

SQL> select trunc(12.23,1) from dual;

TRUNC(12.23,1)//截取不带四舍五入的
--------------
12.2

SQL>

------------------------------------------

SQL> select nvl(' ','is null'),nvl('','is null') from dual;

N NVL('',
- -------
is null

SQL>//说明' '这个不算是空''这个才是空

------------------------------------------

SQL> select nvl2(' ','is null','is not null'),nvl2('','is null','is not null') from dual;

NVL2('' NVL2('','IS
------- -----------
is null is not null

SQL>

------------------------------------------
SQL> select nullif(1,1) from dual;

NULLIF(1,1)
-----------


SQL> select nullif(1,21) from dual;

NULLIF(1,21)//判断是否相等
------------
1

SQL>

相等连接：

1. select tab1.tname,tab2.tname from tab1 inner join tab2 on tab1.tname=tab2.tname ;

2. select tab1.tname,tab2.tname from tab1,tab2 where tab2.tname=tab1.tname;

做外连接：

1. select tab1.tname,tab2.tname from tab1left outtertab2 on tab2.tname=tab1.tname ;

2. select tab1.tname,tab2.tname from tab1,tab2 where

tab1.tname = tab2.tname(+);

全外连接：

1. select tab1.tname ,tab2.tname from tab1 full outter tab2

on tab1.tname = tab2.tname;

集合操作符：

SQL> select * from student
2 union//去除重复集
3 select * from student where sno =10000;

SNO SNAME BIRTH
---------- ------------------------------ ------------
10000 张三▒ 14-JAN-15
10001 lisi 01-AUG-92

SQL> select * from student
2 union all//全集
3 select * from student where sno=10000;

SNO SNAME BIRTH
---------- ------------------------------ ------------
10000 张三▒ 14-JAN-15
10001 lisi 01-AUG-92
10000 张三▒ 14-JAN-15

SQL> select * from student
2 minus//差集
3 select * from student where sno=10000;

SNO SNAME BIRTH
---------- ------------------------------ ------------
10001 lisi 01-AUG-92

SQL> select * from student
2 intersect//交集
3 select * from student where sno=10000;

SNO SNAME BIRTH
---------- ------------------------------ ------------
10000 张三▒ 14-JAN-15

SQL>

重命名列：

SQL> alter table student rename column birth to sbirth;

Table altered.

SQL>