成神之路-ORACLE基本操作

acle 基本操作

一、用户管理  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 AVG(sal) FROM emp); --显示工资高于平均工资的员工信息  
（2）group by 和 having子句联合使用 group by用于对查询的结果分组统计 having子句用于限制分组显示结果
SQL>SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno; --显示每个部门的平均工资和最高工资
SQL>SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP by deptno having AVG(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, AVG(sal), MAX(sal) FROM emp GROUP by deptno HAVING AVG(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
   使用该操作符用于取得两个结果集的差集，他只会显示存在第一个集合中，而不存在第二个集合中的数据