Oracle数据库学习总结
1.set linesize xx; 设置行间距，常用数值有100，200，300
2.set pagesize xx; 设置每页显示行数
6.SHOW USER; 命令可以显示当前连接的用户名
7.SELECT * FROM tab; 可以显示当前用户下的所有数据表
8.“ / ”表示重复执行上一次的SQL命令操作
9.SELECT xx别名,xx 别名 FROM xx; 搜索指定列名，并指定别名，方便显示
10.关键字DISTINCT 可以消除重复值 如 SELECT DISTINCT xx FROM xx;
如: SELECT? '员工姓名是'||ename||'员工卡号是'||empno? FROM emp;
12.查询语句 BETWEEN xx AND xx 是包括边界的
13.查询日期的时候要加上''把日期引起来
?? 例如：SELECT * FROM emp WHERE hiredate BETWEEN '1-1月 -81' AND '08-9月 -81';
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 中提供了以下日期函数支持：
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 自然连接 自动进行关联字段匹配 可以消除笛卡尔积
?? 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;
?