Oracle数据库学习总结

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

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

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

4.CONN username/password; 命令可以建立用户的连接，需要注意的是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||'员工卡号是'||empno  FROM 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 比里面的最大值小

=ANY 与IN用法相同

>ALL 比里面的最大值大

<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(address 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;