SQL> drop profile password_history 【casade】

注意：文件删除后，用这个文件去约束的那些用户通通也都被释放了。 加了casade，就会把级联的相关东西也给删除掉

第6讲 建表

oracle支持的数据类型?

字符类

char 定长 最大2000个字符。

varchar2(20) 变长 最大4000个字符。

clob(character large object) 字符型大对象 最大4G

char 查询的速度极快浪费空间，查询比较多的数据用。

varchar 节省空间

数字型

number范围 -10的38次方 到 10的38次方 可以表示整数，也可以表示小数 number(5,2)

表示一位小数有5位有效数，2位小数

日期类型

date 包含年月日和时分秒 oracle默认格式 1-1月-1999

timestamp 这是oracle9i对date数据类型的扩展。可以精确到毫秒。

图片

blob 二进制数据 可以存放图片/声音 4G 一般来讲，在真实项目中是不会把图片和声音真的往数据库里存放，一般存放图片、视频的路径，如果安全需要比较高的话，则放入数据库。 建表

--学生表

create table student ( ---表名

xh number(4), --学号 xm varchar2(20), --姓名 sex char(2), --性别

birthday date, --出生日期 sal number(7,2) --奖学金 修改表

添加一个字段

SQL>ALTER TABLE student add (classId NUMBER(2)); 修改一个字段的长度

SQL>ALTER TABLE student MODIFY (xm VARCHAR2(30)); 修改字段的类型/或是名字（不能有数据） 不建议做 SQL>ALTER TABLE student modify (xm CHAR(30));

删除一个字段 不建议做(删了之后，顺序就变了。加就没问题，应为是加在后面)

SQL>ALTER TABLE student DROP COLUMN sal; 修改表的名字 很少有这种需求 SQL>RENAME student TO stu; 删除表

SQL>DROP TABLE student;

第7讲 添加删除

添加数据

所有字段都插入数据

INSERT INTO student VALUES ('A001', '张三', '男', '01-5月-05', 10); oracle中默认的日期格式‘dd-mon-yy’ dd日子（天） mon 月份 yy 2位的年 ‘09-6月-99’ 1999年6月9日

从自己复制，加大数据量 大概几万行就可以了 可以用来测试sql语句执行效率 INSERT INTO users (userId,UNAME,UPASSW) SELECT * FROM users;

修改日期的默认格式（临时修改，数据库重启后仍为默认；如要修改需要修改注册表）

ALTER SESSION SET NLS_DATE_FORMAT ='yyyy-mm-dd'; 插入部分字段

INSERT INTO student(xh, xm, sex) VALUES ('A003', 'JOHN', '女'); 插入空值

INSERT INTO student(xh, xm, sex, birthday) VALUES ('A004', 'MARTIN', '男', null);

查询student表里birthday为null的记录

正确写法：select * from student where birthday is null; 如果要查询birthday不为null,则应该这样写：

select * from student where birthday is not null;

修改数据 修改一个字段

UPDATE student SET sex = '女' WHERE xh = 'A001'; 修改多个字段

UPDATE student SET sex = '男', birthday = '1984-04-01' WHERE xh = 'A001';

update aaa set job=null where name='SCOTT' update aaa set job='CLERK' where job is null;

修改含有null值的数据，不要用 = null 而是用 is null；但把值修改成空值，还是要=null。

删除数据

DELETE FROM student;

删除所有记录，表结构还在，写日志，可以恢复的，速度慢。 Delete 的数据可以恢复。 savepoint a; --创建保存点 DELETE FROM student;

rollback to a; --恢复到保存点

一个有经验的DBA，在确保完成无误的情况下要定期创建还原点。 DROP TABLE student; --删除表的结构和数据；

delete from student WHERE xh = 'A001'; --删除一条记录；

truncate TABLE student; --删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快

第8讲 查询

五：oracle表查询(1)

oracle表基本查询 简单的查询语句 查看表结构 DESC emp; 查询所有列

SELECT * FROM dept; 切忌动不动就用select *

SET TIMING ON; 打开显示操作时间的开关，在下面显示查询时间。 SELECT COUNT (*) FROM users;统计行数

查询指定列

SELECT ename, sal, job, deptno FROM emp; 如何取消重复行DISTINCT

SELECT DISTINCT deptno, job FROM emp; 查询SMITH所在部门，工作，薪水

SELECT deptno,job,sal FROM emp WHERE ename = 'SMITH';

注意：oracle对内容的大小写是区分的，所以ename='SMITH'和ename='smith'是不同的

使用算术表达式

问题：如何显示每个雇员的年工资？

SELECT sal*13+nvl(comm, 0)*13 \年薪\ 使用列的别名 \年薪\

使用nvl函数来处理null值 如何连接字符串（||）

SELECT ename || ' is a ' || job FROM emp; 使用where子句

问题：如何显示工资高于3000的 员工？ SELECT * FROM emp WHERE sal > 3000; 问题：如何查找1982.1.1后入职的员工？

SELECT ename,hiredate FROM emp WHERE hiredate >'1-1月-1982'; 问题：如何显示工资在2000到3000的员工？

SELECT ename,sal FROM emp WHERE sal >=2000 AND sal <= 3000;

如何使用like操作符

%：表示0到多个字符 _：表示任意单个字符

如何显示第三个字符为大写O的所有员工的姓名和工资？ SELECT ename,sal FROM emp WHERE ename like '__O%';

在where条件中使用in

问题：如何显示empno为7844, 7839,123,456 的雇员情况？ SELECT * FROM emp WHERE empno in (7844, 7839,123,456); 使用is null的操作符

问题：如何显示没有上级的雇员的情况？ SELECT * FROM emp WHERE mgr is null;

第9讲 查询2 逻辑操作符、分组函数 使用逻辑操作符号

问题：查询工资高于500或者是岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J？

SELECT * FROM emp WHERE (sal >500 or job = 'MANAGER') and ename LIKE 'J%'; 使用order by 字句 默认asc

问题：如何按照工资的从低到高的顺序显示雇员的信息？ SELECT * FROM emp ORDER by sal;

问题：按照部门号升序而雇员的工资降序排列 SELECT * FROM emp ORDER by deptno, sal DESC;

使用列的别名排序 问题：按年薪排序

select ename, (sal+nvl(comm,0))*12 \年薪\ from emp order by \年薪\ asc; 中文别名需要使用“”号（双引号）圈中,英文不需要“”号

oracle表复杂查询

数据分组 ——max（最大），min（最小）， avg（平均）， sum（总和）， count（记数） 问题 ：最高工资那个人是谁？

错误写法：select ename, sal from emp where sal=max(sal);

正确写法：select ename, sal from emp where sal=(select max(sal) from emp);

注意：select ename, max(sal) from emp;这语句执行的时候会报错，说ORA-00937：非单组分组函数。因为max是分组函数，而ename不是分组函数.......

但是select min(sal), max(sal) from emp;这句是可以执行的。因为min和max都是分组函数，就是说：如果列里面有一个分组函数，其它的都必须是分组函数，否则就出错。这是语法规定的问题。

显示工资高于平均工资的员工信息

SELECT * FROM emp where sal > (SELECT AVG(sal) FROM emp);