Oracle数据库学习总结

2020-03-02 22:11:52 来源：范文大全收藏下载本文

Oracle数据库学习总结

1.set linesize xx; 设置行间距，常用数值有100，200，300

2.set pagesize xx; 设置每页显示行数

3.ed x ; 表示新建一个x.sql文件，通过文件编辑SQL语句，然后用@x命令可以调用刚才的命令

4.CONN username/paword; 命令可以建立用户的连接，需要注意的是sys用户是超级管理员，连接是时需要在末尾加上AS SYSDBA 以系统管理员的身份进行连接

5.如果表是归某个用户特有的，在查询的时候需要加上用户名即以用户名.表名的格式查询

6.SHOW USER; 命令可以显示当前连接的用户名

7.SELECT * FROM tab; 可以显示当前用户下的所有数据表

8.“ / ”表示重复执行上一次的SQL命令操作

9.SELECT xx别名,xx 别名 FROM xx; 搜索指定列名，并指定别名，方便显示

10.关键字DISTINCT 可以消除重复值如 SELECT DISTINCT xx FROM xx;

11.Oracle中提供的字符串连接操作，使用“||”表示，相当于Java的\"+\"普通字符用\" \' \" 括起来

如: SELECT\'员工姓名是\'||ename||\'员工卡号是\'||empnoFROM emp;

12.查询语句 BETWEEN xx AND xx 是包括边界的

13.查询日期的时候要加上\'\'把日期引起来

例如：SELECT * FROM emp WHERE hiredate BETWEEN \'1-1月 -81\' AND \'08-9月 -81\';

14.模糊查询中“%”可以匹配任意长度的内容，“_”可以匹配一个长度的内容，如果没输入模糊查询关键字，那么默认查询全部数据，like关键字可以用在任何地方，可以匹配数字、字符、日期等。

15.SQL中不等于可以用\"\"或者\"!=\"表示

16.ORDER BY语句中 ASC表示升序，DESC表示降序，在没指定的时候默认按照升序排序

17.Oracle中的单行函数有如下，默认的所有的函数都要到表中执行，加上关键字DUAL只会产生一个临时表

UPPER(\'xxx\') 将小写转换为大写

LOWER(\'xxx\') 将大写转换为小写

INITCAP(\'xxx\')将首字母大写

CONCAT(\'\',\'\')字符串连接

SUBSTR(\'xxx\',x,x)字符串截取，从0或1开始截取效果是一样的，因为Oracle比较智能,要是输入的参数为负数，则表示倒着截取

LENGTH(\'xxx\')字符串长度

REPLACE(\'xxx\',\'x\',\'x\')字符串替换

ROUND(xxx,xx)四舍五入 xxx需要四舍五入的数值，xx保留的小数位，可以加负数 TRUNC(xxx)截断操作，默认小数点后的全部截断，也可以指定小数点保留位数如TRUNC(789.536,2)得到的结果是789.53，也可以加负数如TRUNC(789.536,-2) 结果是700

18.SELECT sysdate FROM DUAL; 可以求出当前的日期

19.Oracle 中提供了以下日期函数支持：

MONTHS_BETWEEN()求出给定日期范围的月数

ADD_MONTHS(xxx,xxx)在指定日期加上指定的月数

NEXT_DATE(xxx,\'\')求出下一个给定日期数

TO_CHAR()可以将年、月、日进行分割

例如

TO_CHAR(hirdate,\'yyyy\') year,TO_CHAR(hirdate,\'mm\')months,TO_CHAR(hirdate,\'dd\')day 还可以对时间进行格式化输出如TO_CHAR(hirdate,\'yyyy-mm-dd\')

TO_CHAR(hirdate,\'fmyyyy-mm-dd\')可以去掉前导0

TO_CHAR()还可以对数字进行格式化如

SELECT ename,TO_CHAR(SAL,\'99,999\') FROM emp;

注意：一定要用9来表示

$表示美元符号，L表示Local的缩写，以本地语言进行金额显示

TO_NUMBER()将字符串变为数字

TO_DATE()将字符串变为Date类型例如 SELECT TO_DATE(\'2009-12-8\',\'yyyy-mm-dd\') FROM dual;

TO_NVL()可以将NULL的内容变为指定的内容

DECODE()相当于Java的if else else语句

例如SELECT DECODE(1,1,\'内容是1\',2,\'内容是2\',3,\'内容是3\') FROM dual; 将输入内容是1

20.左右连接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e,dept d WHERE

e.deptno(+)=d.deptno; 此例中是右连接，以deptno表为准。

21.SQL1999语法

CROSS JOIN 交叉连接会产生笛卡尔积

NATURAL JOIN 自然连接自动进行关联字段匹配可以消除笛卡尔积

USING 子句：直接关联操作列如 SELECT * FROM emp e JOIN dept USING(deptno) WHERE deptno=30;

ON 子句用户自己编写连接条件

LETF JOIN/RIGHT JOIN 左右连接

SELECT e.empno,e.ename,d.deptno,d.dname,d.loc FROM emp e right OUTER JOIN dept d ON(e.deptno=d.deptno);

22.分组查询GROUP BY 放在where之后

常见的组函数有

COUNT();

MAX();

MIN();

AVG();

SUM();

用法如下：SELECT deptno,count(empno) FROM emp GROUP BY deptno;

语法：SELECT deptno,empno,count(empno) FROM emp GROUP BY deptno;是错误的，原因是使用分组函数的时候，不能出现分组函数和分组条件以外的字段。

语法：SELECT deptno,count(empno) FROM emp；是错误的，原因是不使用分组的时候，则只能单独使用分组函数

分组函数只能在分组中使用，不允许子啊where语句中个使用，要使用个分组条件可以加上HAVING

例如：SELECT deptno,avg(sal) FROM emp GROUP BY deptno having avg(sal)>2000; 注意：分组函数可以嵌套使用，但是在组函数嵌套使用的时候不能再出现分组条件的查询语句

如下语法是错误的：SELECT deptno,max(avg(sal)) FROM emp GROUP BY deptno; 不能出现deptno

如下语法是正确的：SELECT max(avg(sal)) FROM emp GROUP BY deptno;

23.子查询中

>ANY 比里面的最小值大

=ANY 与IN用法相同

>ALL 比里面的最大值大

24.表复制

CREATE TABLE myemp AS SELECT * FROM emp; 既复制表结构，又复制表内容

CREATE TABLE myemp AS SELECT * FROM emp where 1=2; 后面的条件不可能成立，只复制表结构

25.Oracle 中常用的数据类型

VARCHAR、VARCHAR2 代表一个字符串，有长度限制，为255

NUMBER 分为两种

1)NUMBER(n) 代表一个整数，数字的长度是n,可以使用INT

2)NUMBER(m,n) 代表一个小数，小数长度为n，整数长度为m-n，可以使用FLOAT

DATE 代表日期的类型，日期要按照标准的日期格式进行存放

CLOB 大对象，表示大文本数据,一般可以存放4G的文本

BLOB 大对象，表示二进制数据最大可以存放4G，例如存放歌曲、电影、图片

26.表的创建

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200),

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\'

);

27.插入数据

INSERT INTO person(pid,name,birthdate,age) VALUES(\'222\',\'里斯

\',TO_DATE(\'1989-02-09\',\'yyyy-mm-dd\'),45);

28.更改表中数据

增加表结构：ALTER TABLE person ADD(addre VARCHAR2(50) DEFAULT \'暂无地址\');

修改已存在的列：ALTER TABLE person MODIFY(name VARCHAR2(40) DEFAULT \'无名氏\');

29.表的重命名（只能在Oracle中使用）

RENAME XXX TO XXX;

30.约束（主要分为5类）

1）主键约束主键表示是一个唯一的标识。本身不能为空

2）唯一约束在一个表中只允许建立一个主键约束，而其他列如果不希望重复值的话，则可以使用唯一约束

3）检查约束检出一个列的内容是否合法

4）非空约束

5）外键约束在两张表中进行约束的操作删除时应该先删除子表，再删除父表创建主键：

语法1

CREATE TABLE person(

pid VARCHAR2(18) PRIMARY KEY,

name VARCHAR2(200),

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\'

);

语法2：

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200),

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\',

CONSTRAINT p_id PRIMARY KEY(pid)

);

创建非空约束

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\'

);

创建唯一约束

语法一

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200) UNIQUE NOT NULL,

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\'

);

语法二

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4),

sex VARCHAR2(2) DEFAULT \'男\',

CONSTRAINT p_name UNIQUE(name),

);

创建检查约束

语法一

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4) NOT NULL CHECK(age BETWEEN 0 AND 150),

sex VARCHAR2(2) DEFAULT \'男\' CHECK(sex IN (\'男\',\'女\',\'中\')),

);

语法二

CREATE TABLE person(

pid VARCHAR2(18),

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4) NOT NULL ,

sex VARCHAR2(2) DEFAULT \'男\' ,

CONSTRAINT p_age CHECK(age BETWEEN 0 AND 150),

CONSTRAINT p_sex CHECK(sex IN (\'男\',\'女\',\'中\'))

);

创建外键约束

--person表

CREATE TABLE person(

pid VARCHAR2(18) PRIMARY KEY,

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4) NOT NULL ,

sex VARCHAR2(2) DEFAULT \'男\'

);

--book表

CREATE TABLE book(

bid NUMBER PRIMARY KEY,

bname VARCHAR2(30),

bprice NUMBER(5,2),

pid VARCHAR2(18),

CONSTRAINT b_pid_fk FOREIGN KEY(pid) REFERENCES person(pid)

);

对于删除，应该先删除book表再删除person表

也可以使用级联删除,强制删除某张表

DROP TABLE person CASCADE CONSTRAINT;

设置外键约束级联删除

--person表

CREATE TABLE person(

pid VARCHAR2(18) PRIMARY KEY,

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4) NOT NULL ,

sex VARCHAR2(2) DEFAULT \'男\'

);

--book表

CREATE TABLE book(

bid NUMBER PRIMARY KEY,

bname VARCHAR2(30),

bprice NUMBER(5,2),

pid VARCHAR2(18),

CONSTRAINT b_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE

);

31.修改约束

如果一张表已经建立完成之后，则可以为其添加约束

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称约束类型(约束字段);

关于约束名称的命名最好要统一：

PRIMARY KEY ：主键字段_PK

UNIQUE:字段_UK

CHECK:字段_CK

FOREIGH KEY:父字段_子字段_FK

例如：

DROP TABLE person;

CREATE TABLE person(

pid VARCHAR2(18) ,

name VARCHAR2(200) NOT NULL,

birthdate DATE,

age NUMBER(4) NOT NULL ,

sex VARCHAR2(2) DEFAULT \'男\'

);

ALTER TABLE person ADD CONSTRAINT pid_PK PRIMARY KEY(pid);

ALTER TABLE person ADD CONSTRAINT name_UK UNIQUE(name);

ALTER TABLE person ADD CONSTRAINT age_CK CHECK(age BETWEEN 1 AND 150); CREATE TABLE book(

bid NUMBER ,

bname VARCHAR2(30),

bprice NUMBER(5,2),

pid VARCHAR2(18)

);

ALTER TABLE book ADD CONSTRAINT book_PK PRIMARY KEY(bid);

ALTER TABLE book ADD CONSTRAINT pid_FK FOREIGN KEY(pid) REFERENCES person(pid); 删除约束

ALTER TABLE person DROP CONSTRAINT name_UK;

ALTER TABLE person DROP CONSTRAINT age_CK;

学习oracle数据库的总结(图文整理)（定稿）

《Oracle数据库学习总结.doc》

将本文的Word文档下载到电脑，方便收藏和打印

推荐度：

点击下载文档

Oracle数据库学习总结

相关推荐