Oracle SQL 基础

SQL> select * from chengyu;

        ID NAME                        SAL
---------- -------------------- ----------
         1 cry                        2600

SQL> select concat (name,sal)from chengyu;

CONCAT(NAME,SAL)
------------------------------------------------------------
cry2600

SQL> select initcap(name) from chengyu;

INITCAP(NAME)
--------------------
Cry


SQL> select upper(name)from chengyu;

UPPER(NAME)
--------------------
CRY

SQL> select lower(name)from chengyu;

LOWER(NAME)
--------------------
cry

SQL>
SQL> select substr(name,0,length(name)-1) from chengyu;

SUBSTR(NAME,0,LENGTH(NAME)-1)
----------------------------------------
cr
SQL>
SQL> select ceil(13.8) ||'oracle'|| ceil(13.3) from dual;

CEIL(13.8)
----------
14oracle14

SQL>
SQL> select floor(13.8) ||'oracle'|| floor(13.3) from dual;

FLOOR(13.8
----------
13oracle13

SQL>


SQL> select round(23.456) || ' ' || round(23.456,2) || ' ' ||
  2  round(34.567) from dual;

ROUND(23.45
-----------
23 23.46 35

SQL>
SQL> select trunc(12.345,2) from dual;

TRUNC(12.345,2)
---------------
          12.34

SQL> select months_between(to_date('2008-08-15','YYYY-MM-DD'),
  2  to_date('2006-11-11','YYYY-MM-DD'))
  3  as mb
  4  from dual;

        MB
----------
21.1290323

SQL>
SQL> update chengyu set
  2  sj = to_date('2008-08-19','YYYY-MM-DD')
  3  where id = 1;

已更新 1 行。

SQL> commit;
SQL> select next_day(sysdate,1)from dual;

NEXT_DAY(SYSDA
--------------
23-8月 -09
SQL> select to_char(sysdate) from dual;

TO_CHAR(SYSDAT
--------------
19-8月 -09

SQL> select nvl(name,'kong') from chengyu;

NVL(NAME,'KONG')
--------------------
cry

select * from tab where rownum < 1000 ;

SQL> select rownum lvl, substr('木炭abcd1234.34ef火锅gh', rownum, 1) x
2 from dual connect by rownum <= length('木炭abcd1234.34ef火锅gh') ;

host cls 清屏

SQL> create table xx
		as 
		select name , sal from chengYu
		where 1=1;(1=2)

SQL> insert into tab
		select * from chengYu;

		insert into tab (id,name)
			select id,name from tab2;

Enterprise Manager Database Control URL - (orcl) :
http://rain:1158/em

数据库配置文件已经安装到 e:\oracle\product\10.2.0,同时其他选定的安装组件也已经安装到 
e:\oracle\product\10.2.0\db_1。
iSQL*Plus URL 为:
http://rain:5560/isqlplus

iSQL*Plus DBA URL 为:
http://rain:5560/isqlplus/dba

ALTER TABLE "SYSTEM"."DEPT" 
    RENAME COLUMN "ID" TO "IDD"

oracle varchar2 是字节的意思 例如：定义一个人名，一般是 varchar2（8）

SQL> select d.deptname,max(sal)
  2  from dept d,emp e
  3   where d.id = to_number(d_id)
  4  group by d.deptname;

DEPTNAME               MAX(SAL)
-------------------- ----------
项目二组                   4000
项目三组                   3500
项目一组                   4500

oracle 创建主键
	create table cryprimarykey
		(
			   id integer primary key,
			   name varchar(8)
		);