Oracle笔记之二

 

-----创建表-------

Creat table student ( age int(3) ,  name varchar2(20) showdate date);

----加主键约束----：

ALTER TABLE ADMIN.STUDENT

ADD CONSTRAINT STUDENT_PK  PRIMARY KEY(ID);

----非空设定---

ALTER TABLE ADMIN.STUDENT MODIFY(AGE  NOT NULL);

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

Distinic 不重复

Nvl(sex,'0'

----序列----- sequence

CREATE SEQUENCE ADMIN.ID_SQU

START WITH 1

INCREMENT BY 1

MINVALUE 0

MAXVALUE 100

NOCACHE

NOCYCLE

NOORDER

---

----加一列----

ALTER TABLE YBL.XUANKE

 ADD (beiyong  VARCHAR2(20));

删除一列

ALTER TABLE ADMIN.DIANHUABEN DROP COLUMN ID;

给列修改名字

ALTER TABLE ADMIN.STUDENT

RENAME COLUMN CLASS_ID TO CLASSid;

修改表名

rename xiao2 to xiao

 

 

Set unused column

 

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

清空数据  truncate table

 

 

 

create table person(id int not null ,name varchar2(40) not null,qq varchar2(9),phone varchar(20) not null,sex varchar2(10),group_id int)

 

两个表

select p.name, t.name from tel_group t,person p where t.id=p.group_id

 

insert into person (id,name,sex,qq,phone,group_id)

 

 

insert into tel_group(id,name)values (group_seq.nextval,'大学')；

---------------限制条件----------

Set line  100 (默认是80)出现的最多的列数

Show feedback 显示查询时显示的行数

Set heading off 设置标题的开关

Set  arrayssize 10 一次显示 10 行，最多5000

Set page （24）一页出现的行数

Set long

把多个字符串连接起来

 distinct过滤到重复的

比较运算

大于>

小于 < 

不等于 = !或<>

Between     and 

Not

Where age   [not]in(23,33,43)

Like '%aa%' '___a' 一个_表示一个字符

如果需要转义字符 /%表示 %

别名   select name as mynam 或者select name myname from student

排序 order by 1 desc 表示根据第一列排序

 

显示行号

              每个表都有一个虚列ROWNUM，它用来显示结果中记录的行号。我们在查询中也可以显示这个列。

SELECT rownum,ename FROM emp;

All

select * from emp where sal>all(1000,1500,2000);

Not exists

select '存在的雇员SMITH' from dual where exists(select * from emp where ename='SMITH')

---------- 缓寸区 ------

List  缓寸区 刚才输入的命令 执行： run  /  或者r 

1 select name,

2 age,phone

3 from student

先 2 定位到第2行  再 a  bb  就把bb加到了第2行

删除del 3 把第三行删除

修改一行 change 或者简写为c

先定位1 定位到第一行

Change /name/myname

 

生成脚本 save d:/a.sql

调用脚本Get d:/a.sql 再 / 或者 直接 start d:/a.sql start可以用@替换

Ed回车可以编辑

 

spool d:/aa 保存自己的操作包括结果都保存到aa文件夹

 

spool off 停止保存

-------函数------

 

函数名称	功    能	实  例	结  果
ascii	获得字符的ASCII码	Ascii('A')	65
chr	返回与ASCII码相应的字符	Chr(65)	A
lower	将字符串转换成小写	lower ('SQL Course')	sql course
upper	将字符串转换成大写	upper('SQL Course')	SQL COURSE
initcap	将字符串转换成每个单词以大写开头	initcap('SQL course')	Sql Course
concat	连接两个字符串	concat('SQL', ' Course')	SQL Course
substr	给出起始位置和长度，返回子字符串	substr('String',1,3)	Str
length	求字符串的长度	length('Wellcom')	7
instr	给出起始位置和出现的次数，求子字符串在字符串中出现的位置	instr('String', 'r',1,1)	3
lpad	用字符填充字符串左侧到指定长度	lpad('Hi',10,'-')	--------Hi
rpad	用字符填充字符串右侧到指定长度	rpad('Hi',10,'-')	Hi--------
trim	在一个字符串中去除另一个字符串	trim('S' FROM 'SSMITH')	MITH
replace	用一个字符串替换另一个字符串中的子字符串	replace('ABC', 'B', 'D')	ADC

 

 

 转换成小写select  lower(name) from student

转换成大写 upper

select * from dual;--虚的表格

Initcap 把首字母大写

 concat 连接字符串

Substr (name,m,n)把name的m 到n个截下来 Substr (name,m)

Select substr(''helloworld',2,5)

 Length 查询长度

Instr('helloworld',3,2) from dual

Trim 去掉两头的空格

 

 

 

Replace

select replace('hhhhlllhhh','h','a')from dual   ---aaaalllaaa

Round 四舍五入

select round(23.66644,1)from dual 精确到小数典后一位---23.7

select round(23.6664,2)from dual 精确到小数典后二位---  23.67

 

Trunc 直接截取  select trunc(23.66644,2)from dual 23.66

Mod (m,n)m/n取余

------时间的操作

 

系统时间sysdate

Select sysdate from dual

Month_between

To_date(sysdate,''yy-MM-dd)

---Select Month_between(''10-23-02','10-23-08') from dual

Add_month(月，日)

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

Varchar可以自动转换成number的

Char Varchar---→-date

Number--→-Char Varchar-

Date --→-Char Varchar-

To_char()

select to_char(sysdate,'YYYY-MM-DD HH:mm:ss') from dual

To_number()

To_date()

insert into classinfo values(4,'oracle班','sys',to_date('2008-05-01','YYYY-MM-DD'))

 

 

运  算	功  能	实  例
[NOT] BETWEEN…AND…	用于测试是否在范围内	Select * from emp Where sal between 1000 and 2000
[NOT] IN (…)	用于测试是否在列表中	Select*from emp Where job in('CLERK', 'SALESMAN','ANYLYST')
[NOT] LIKE	用于进行模式匹配	Select * from emp Where ename like '%A%'
IS [NOT] NULL	用于测试是否为空值	Select * from emp Where comm is not null
ANY SOME	同列表或查询中的每一个值进行比较，测试是否有一个满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等	Select * from emp Where sal<any(select sal from emp where deptno=10)

 

 

0 前导0 ￥$l 显示本地货币号

Mi 显示千位符号

 

 

-------空值处理-------

空值和任何数操作都是空 的

select name ,nvl(phone,0) from student;

NAME	NVL(PHONE,0)
tom	0

 

decode(

name,

'tom',age*2,

'张三',age*5,

Age*8

 

)意义：name 是tom的age*2 name是'张三的age*5, 其他的Age*8

 

Nvl2(name,a,b)name如果是空的就变成ａ不是空则为b

 

 

 分组函数

Count avg sum

Max mix

Select count(*) from student

Group by中 不能用where 可以用having代替

Select class_id ,avg(age) from student group by class_id having avg(age)>20

 

Select s.name ,c.name from student  s,class_info  c where s.class_id=c.id order by c.id;

加号放在少的那边 就能把所有的都显示出来

-------链接----

Select a.id,b.name from student a left outer join class_info b on(a.id=b.id) 或者用加号

 left outer==左边加加号 左外连接

Right outer = 右边加号  右外连接

Full outer join

Select table_name from user_tables

 

建立表空间

CREATE TABLESPACE "MYSPACE1"

    LOGGING

    DATAFILE 'E:/ORACLE/ORADATA/ORA250/MYSPACE1.ora' SIZE 5M

    AUTOEXTEND

    ON NEXT  5M MAXSIZE  200M EXTENT MANAGEMENT LOCAL SEGMENT

    SPACE MANAGEMENT  AUTO

Create tablespace "xiaogang"  Datafile 'e:/oracle/admin/xiaogang.dbf' Size 15M  AUTOEXTEND ON NEXT  5M MAXSIZE  200M

显示用户下有多少表

Select * from user_tables

创建用户：aaa 密码 bbb

CREATE USER "AAA"  PROFILE "DEFAULT"

    IDENTIFIED BY "bbbb" DEFAULT TABLESPACE "USERS"

    ACCOUNT UNLOCK;

GRANT "CONNECT" TO "AAA" WITH ADMIN OPTION;

GRANT "DBA" TO "AAA" WITH ADMIN OPTION;