oralce数据库的基本操作
Oracle 基本操作
一、用户管理
1. 创建用户
sql >create user 用户名identified by 密码；(密码必须以字母开头，如果以字母开头，它不会创建用户)
2. 修改密码
sql >password 新密码
二、Oracle 表的管理
Oracle表的操作有多种方法，sql*plus是oracle自带的工具软件，主要用于执行sql语句,pl\sql块；pl/sql developer 属于第三方软件,主要用于开发，测试，优化oracle pl/sql 的存储过程比如: 触发器,此软件oracle不带，需要单独安装。在实际应用过程中pl/sql developer的应用较为简便，因此以下用此为例讲解。
1.表名和列的命名规则
?必须以字母开头
?长度不能超过30个字符
?不能使用oracle的保留字
?只能使用如下字符 A-Z，a-z，0-9，$,#等
2.Oracle表的常用数据类型
1）字符型
char（）定长最大2000个字符，不足补空格。
例：char(10) ‘小韩’，前四个字符放‘小韩’，后添6个空格补全。
varchar2()变长最大4000个字符。
说明：char 查询的速度极快但浪费空间，查询比较多的数据时用；varchar2（）节省空间。
2）数字型
number范围 -10的38次方到 10的38次方，可以表示整数，也可以表示小数。
例：number(5,2) 表示一位小数有5位有效数，2位小数。范围：-999.99到999.99 3）日期类型
date包含年月日和时分秒 oracle默认格式1-1月-1999
3. 创建表
1）用pl/sql developer手动创建表
2）用sql语句创建表
create table student ( --表名
xh number(4), --学号
xm varchar2(20), --姓名
sex char(2), --性别
birthday date, --出生日期
sal number(7,2) --奖学金
);
4.修改表
1）添加一个字段
SQL>alter table student add (classId number(2));
2）修改一个字段的长度
SQL>alter table student modify (xm varchar2(30));
3）删除一个字段（不建议）
SQL>alter table student drop column sal;
4）修改表的名字
SQL>rename student to stu;
5）删除表
SQL>drop table student;
5.添加数据
1）所有字段都插入数据
SQL>INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05', 10); 说明：日期的格式是系统默认的（‘dd-mon-yy’），可以修改（临时修改，如果要永久修改则需要修改注册表）
SQL>ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd';
SQL>INSERT INTO student VALUES ('A002', 'MIKE', '男', '1905-05-06', 10); 2）插入部分字段
SQL>INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 3）插入空值
SQL>INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);
说明：添加空值与不添加值是有区别的。
6.修改数据
1）修改一个字段
SQL>UPDATE student SET sex = '女' WHERE xh = 'A001';
2）修改多个字段
SQL>UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';
7.删除数据
1）SQL>DELETE FROM student;
说明：删除所有记录，表结构还在，写日志，数据可以恢复的，速度慢。
2）SQL>DROP TABLE student;
说明：删除表的结构和数据，不能恢复。
三、Oracle 表的查询
在此所用实例，来自于oracle数据库自带的scott用户的emp表和dept表。1.基本查询
1）查看表结构
SQL>DESC emp;
2）查询所有列
SQL>SELECT * FROM dept;
说明：慎用select * ，若表记录很多，则反应很慢。
3）查询指定列
SQL>SELECT ename, sal, job, deptno FROM emp;
说明：如何取消重复行
SQL>SELECT DISTINCT deptno, job FROM emp;
注意：oracle对内容的大小写是区分的，所以ename='SMITH'和ename='smith'是不同的。
4）使用算术表达式 nvl （处理null）
SQL>SELECT sal*13+nvl(comm, 0)*13 "年薪" , ename, comm FROM emp;
说明：nvl(comm, 0)含义，若comm为空，则用0计算；若comm不为空，则用comm的值计算。
5）使用where子句（条件查询）
SQL>SELECT * FROM emp WHERE sal > 3000; --显示工资高于3000的员工
6）使用like操作符
说明：%表示0到多个字符，_表示任意单个字符
SQL>SELECT ename,sal FROM emp WHERE ename like 'S%';--显示首字符为S的员工姓名和工资
SQL>SELECT ename,sal FROM emp WHERE ename like '_ _O%'; --显示第三个字符为大写O的所有员工的姓名和工资
7）where条件中使用in
SQL>SELECT * FROM emp WHERE empno in (7844, 7839,123,456); --显示empno 为7844, 7839,123,456 的雇员情况
8）使用逻辑操作符号
SQL>SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; --查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J。
9）使用order by 字句（排序）
默认：asc （升序）
SQL>SELECT * FROM emp ORDER by sal; --按照工资的从低到高的顺序显示雇员的信息
10）使用列的别名排序
SQL>select ename, (sal+nvl(comm,0))*12 "年薪" from emp order by "年薪" asc; --年薪排序（表中没有年薪这个字段）
2.复杂查询
1）数据分组
（1）分组函数包括：max，min，avg，sum，count
SQL>select ename, sal from emp where sal=(select max(sal) from emp); --显示所有员工中最高工资的员工
SQL>SELECT * FROM emp e where sal > (SELECT A VG(sal) FROM emp); --显示工资高于平均工资的员工信息
（2）group by 和having子句联合使用
group by用于对查询的结果分组统计
having子句用于限制分组显示结果
SQL>SELECT A VG(sal), MAX(sal), deptno FROM emp GROUP by deptno; --显示每个部门的平均工资和最高工资
SQL>SELECT A VG(sal), MAX(sal), deptno FROM emp GROUP by deptno having
A VG(sal) < 2000; --显示平均工资低于2000的部门号和它的平均工资
对数据分组的总结：
1 分组函数只能出现在选择列表、having、order by子句中(不能出现在where中)
2 如果在select语句中同时包含有group by, having, order by 那么它们的顺序是group by, having, order by
3 在选择列中如果有列、表达式和分组函数，那么这些列和表达式必须有一个出现在group by 子句中，否则就会出错。
如SQL>SELECT deptno, A VG(sal), MAX(sal) FROM emp GROUP by deptno
HA VING A VG(sal) < 2000;
这里deptno就一定要出现在group by 中
2）多表查询
多表查询是指基于两个和两个以上的表或是视图的查询。规定：多表查询的条件是至少不能少于表的个数-1 才能排除笛卡尔集。（如果有N张表联合查询，必须得有N-1个条件，才能避免笛卡尔集合）
SQL>SELECT d.dname, e.ename, e.sal FROM emp e, dept d WHERE e.deptno =
d.deptno and
e.deptno = 10; --显示部门号为10的部门名、员工名和工资
SQL>SELECT e.ename, e.sal, s.grade FROM emp e, salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal; --显示各个员工的姓名，工资及工资的级别
3）自连接
自连接是指在同一张表的连接查询。（可以看做是两张同样的表）
SQL>SELECT worker.ename, boss.ename FROM emp worker,emp boss WHERE worker.mgr = boss.empno AND worker.ename = 'FORD'; --显示员工‘FORD’的上级
4）子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。
(1)单行子查询
单行子查询是指只返回一行数据的子查询语句
例：显示与SMITH同部门的所有员工
思路：
1 查询出SMITH的部门号
SQL>select deptno from emp WHERE ename = 'SMITH';
2 显示
SQL>SELECT * FROM emp WHERE deptno = (select deptno from emp WHERE ename = 'SMITH');
数据库在执行sql 是从左到右扫描的，如果有括号的话，括号里面的先被优先执行。
（2）多行子查询
多行子查询指返回多行数据的子查询
查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
SQL>SELECT * FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10); --（注意：不能用job=..，因为等号=是一对一的）
（3）在多行子查询中使用all操作符
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号。SQL>SELECT ename, sal, deptno FROM emp WHERE sal > all (SELECT sal FROM emp WHERE deptno = 30);
或SQL>SELECT ename, sal, deptno FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
（4）多行子查询中使用any操作符
显示工资比部门30的任意一个员工的工资高的员工姓名、工资和部门号SQL>SELECT ename, sal, deptno FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);
（5）多列子查询
单行子查询是指子查询只返回单列、单行数据，多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询是指查询返回多个列数据的子查询语句。
例：查询与SMITH的部门和岗位完全相同的所有雇员
SQL>SELECT * FROM emp WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'SMITH');
5）合并查询
有时在实际应用中，为了合并多个select语句的结果，可以使用集合操作符号union，union all，intersect，minus 。多用于数据量比较大的数据局库，运行速度快。
（1）union
该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中重复行。
SQL>SELECT ename, sal, job FROM emp WHERE sal >2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
（2）union all
该操作符与union相似，但是它不会取消重复行，而且不会排序。
（3）intersect
使用该操作符用于取得两个结果集的交集。
（4）minus
使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据。