-- dual表的存在,主要是为了能够运行函数,以满足语法需求
SQL> desc dual;
Name Type Nullable Default Comments
----- ----------- -------- ------- --------
DUMMY VARCHAR2(1) Y
SQL> select * from dual;
DUMMY
-----
X
-- 没有访问任何表,导致sysdate运行失败
SQL> select sysdate;
select sysdate
ORA-00923: FROM keyword not found where expected
SQL> select sysdate from dual;
SYSDATE
-----------
2006-1-17 2
-- 起别名 (alias)
SQL> select sysdate thisisnowtime from dual;
THISISNOWTIME
-------------
2006-1-17 20:
SQL> desc tbl_student;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
STU_NO CHAR(3)
STU_NAME VARCHAR2(30)
STU_AGE INTEGER
STU_HEIGHT NUMBER(5,2) Y
STU_MARK NUMBER(5,3) Y
SQL> alter table tbl_student add stu_birth date;
Table altered
SQL> desc tbl_student;
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
STU_NO CHAR(3)
STU_NAME VARCHAR2(30)
STU_AGE INTEGER
STU_HEIGHT NUMBER(5,2) Y
STU_MARK NUMBER(5,3) Y
STU_BIRTH DATE Y
-- 插入数据,直接从sysdate获得时间
SQL> insert into tbl_student values('010','mike',13,130.5,97.5,sysdate);
1 row inserted
SQL> select * from tbl_student;
STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001 mary 14 131.50
002 david 14 132.50
003 tom 15 137.50
006 kent 14 136.50
009 jenny 15 136.50
010 mike 13 130.50 97.500 2006-1-17 2
6 rows selected
SQL> insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00');
insert into tbl_student values('010','mike',13,130.5,97.5,'2000-1-1 10:00:00')
ORA-01861: literal does not match format string
-- 使用to_date完成字符串到日期的转化
SQL> insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into tbl_student values('010','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'))
ORA-00001: unique constraint (TEST1.SYS_C005269) violated
SQL> insert into tbl_student values('011','mike',13,130.5,97.5,to_date('2000-1-1 10:00:00','yyyy-mm-dd hh24:mi:ss'));
1 row inserted
SQL> select * from tbl_student;
STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001 mary 14 131.50
002 david 14 132.50
003 tom 15 137.50
006 kent 14 136.50
009 jenny 15 136.50
010 mike 13 130.50 97.500 2006-1-17 2
011 mike 13 130.50 97.500 2000-1-1 10
7 rows selected
SQL> select to_char(sysdate,'yyyy/mm/ss') from dual;
TO_CHAR(SYSDATE,'YYYY/MM/SS')
-----------------------------
2006/01/46
SQL> select to_char(sysdate,'yyyy/mm/dd') from dual;
TO_CHAR(SYSDATE,'YYYY/MM/DD')
-----------------------------
2006/01/17
-- 使用to_char完成日期到字符串的转化
SQL> select to_char(sysdate,'yyyy/mm/dd') nowdate from dual;
NOWDATE
----------
2006/01/17
--转小写
SQL> select lower('AbcDD') from dual;
LOWER('ABCDD')
--------------
abcdd
--转大写
SQL> select upper('AbcDD') from dual;
UPPER('ABCDD')
--------------
ABCDD
--切割掉左右空格
SQL> select trim('AbcDD ') from dual;
TRIM('ABCDD')
-------------
AbcDD
-- 左侧补0
SQL> select lpad('1',5,'0') from dual;
LPAD('1',5,'0')
---------------
00001
-- 右侧补0
SQL> select rpad('1',5,'0') from dual;
RPAD('1',5,'0')
---------------
10000
SQL> select lpad('1',5,' ') from dual;
LPAD('1',5,'')
--------------
1
-- 胶水符 (glue),类似于concat函数
SQL> select 'dfrz'||lpad('1',5,'0') from dual;
'DFRZ'||LPAD('1',5,'0')
-----------------------
dfrz00001
SQL> select 'dfrz'||lpad('1',5,'0') empid from dual;
EMPID
---------
dfrz00001
SQL> select * from tbl_student;
STU_NO STU_NAME STU_AGE STU_HEIGHT STU_MARK STU_BIRTH
------ ------------------------------ --------------------------------------- ---------- -------- -----------
001 mary 14 131.50
002 david 14 132.50
003 tom 15 137.50
006 kent 14 136.50
009 jenny 15 136.50
010 mike 13 130.50 97.500 2006-1-17 2
011 mike 13 130.50 97.500 2000-1-1 10
7 rows selected
--五大聚合函数,count(),sum(),max(),min(),avg()
SQL> select count(*) from tbl_student;
COUNT(*)
----------
7
SQL> select max(stu_height) from tbl_student;
MAX(STU_HEIGHT)
---------------
137.5
SQL> select min(stu_height) from tbl_student;
MIN(STU_HEIGHT)
---------------
130.5
SQL> select avg(stu_height) from tbl_student;
AVG(STU_HEIGHT)
---------------
133.64285714285
SQL> select sum(stu_height) from tbl_student;
SUM(STU_HEIGHT)
---------------
935.5
SQL> select sum(stu_height)/avg(stu_height) from tbl_student;
SUM(STU_HEIGHT)/AVG(STU_HEIGHT
------------------------------
7
SQL> select sum(stu_height)/count(stu_height) from tbl_student;
SUM(STU_HEIGHT)/COUNT(STU_HEIG
------------------------------
133.642857142857