Oracle实训笔记

1.常见数据库有哪些？
大型数据库:(关系型）
Oracle , DB2
中小型数据库:(关系型）
MySQL, SQLServer
小型数据库:
Access(关联型数据库)
2.Oracle数据库常见对象有哪些？
表，视图，索引，序列，存储过程，触发器。。。
3.Oracle数据库语言分类：

1. DDL: 数据定义语言
   create ,alter, drop ,truncate(清空表数据)
2. DML: 数据操控语言：对数据
   insert ,delete ,update
3. DQL：数据查询语言
   select
4. DCL：数据控制语言
   grant ,revoke
5. TCL：事务控制语言(对显示提交有效)
   commit(对DML语句进行确认提交，DDL是隐式提交，DML是显 示提交) ,rollback ,savepoint(rollback to savepoint2)
   4.Oracle数据库中的数据类型：
   1）数字类型：
   number (n) ,number(n,m)（长度，位数）
   2）字符类型：
   char(n):表示固定长度为n,插入的数据长度不足n个用空格补齐
   varchar(n):可变长
   varchar2(n):与varchar一样，Oracle特有
   3）日期：
   date
   5.常见命令
   –1.创建表
   create table person(
   id number(4),
   name varchar(10),
   sex varchar(2),
   birthday date
   );
   –查看表结构
   desc person;
   Oracle数据库约束类型：
   1.主键约束：primary key
   唯一且非空
   2.唯一约束：unique
   值不能重复
   3.非空约束： not null
   不为空
   4.检查约束：check
   是否符合条件
   5.外键约束：foreign key
   若表中有主键或唯一约束时，则可以作为外键存在
   级联删除
   on delete cascade(父,子表一并删除）
   alter table child add constraint child_c2_fk foreign key(c2) references parent (c1) on delete cascade;
   create table c (
   c1 number(10) primary key,
   c2 number(10)
   );
   insert into c(c1,c2) values(1,2);
   insert into c(c1,c2) values(1,2);
   create table cc (
   c1 number(10) primary key,
   c2 number(10) unique,
   c3 number(10）
   );
   insert into cc(c1,c2,c3) values(1,2,3);
   insert into cc(c1,c2,c3) values(2,3,3);
   create table ccc (
   c1 number(10) primary key,
   c2 number(10) unique,
   c3 number(10) not null
   );
   insert into ccc(c1,c2,c3) values(2,3,’’)
   create table person(
   id number (2) PRIMARY KEY,–列级
   name varchar(20）unique,
   sex varchar(5) ,
   birhday date,
   constraint p_s_ck check(sex in (‘男’，‘女’)）–表级
   );
   insert into person(id,sex) values (1,‘nu’);
   insert into person(id,sex) values (1,‘女’);
   create table d(
   c1 number(10) primary key,
   c2 number(10) unique,
   c3 number(10) constraint d_c3_ck check (c3>10)
   );
   insert into d values(1,2,3);
   insert into d values(1,2,30);
   create table d1(
   c1 number(10) primary key,
   c2 number(10) unique,
   c3 number(10) ,
   constraint d_c3_ck check ((c2+c3)>10)
   );
   insert into d1 values(1,2,3);
   create table parent(
   c1 number (10) primary key,
   c2 number (10),
   c3 number (10)
   );
   create table child(
   c1 number (10) primary key,
   c2 number (10),
   c3 number (10),
   constraint child_c2_fk–(名字） foreign key(c2) references parent(c1)
   );
   insert into child values(1,1,3);
   insert into parent values(1,2,3);
   insert into parent values(4,2,3);
   insert into child values(2,4,3);

查询：
insert into emp_sunyn values(1011,‘赵薇’,‘salesman’,13000,null,‘26-11月-19’,1008,30);
select sysdate from dual; --查询系统时间格式
insert into emp_sunyn(empno,ename,deptno) values(1012,‘黄晓明’,20);
select empno,ename,salary from emp_sunyn;
select ename,salary,salary12 year_sal from emp_sunyn;
select ename,salary,bonus,salary12+bonus year_sal from emp_sunyn;
select ename,salary,bonus,salary12+nvl(bonus,0) year_sal from emp_sunyn;
select ename,salary,bonus,salary12+bonus year_sal from emp_sunyn;
–nvl(字段,值）处理空值
注意： nvl()处理的两个参数类型必须保持一致
select ename,salary,bonus,salary12+nvl(bonus,0) year_sal from emp_sunyn;
–查询该公司有哪些职位
–distinct去重 只能跟在select后面，如果对多个字段去重，则实现的是联合唯一去重
select distinct job,deptno from emp_sunyn;
–条件查询
–查询10 部门中有哪些员工？
select empno,ename,deptno from emp_sunyn where deptno = 10;
–查询员工薪资在5000-10000之间的员工信息？
–between…and :闭区间
select * from emp_sunyn where salary between 5000 and 10000;
–查询那些员工的薪资是5000，8000，10000？
–in(列表)：
select * from emp_sunyn where salary in (5000,8000,10000);
–模糊查询：
–like：
– %：0个或多个字符
– _：1个字符
– escape：转义字符 ‘’
-----查询员工职位中包含’s’这个字符的员工信息
select * from emp_sunyn where job like ‘%s%’;
-----查询职位中第二个字符是’n’的员工信息？
select * from emp_sunyn where job like ‘_n%’;
-----查询数据库中表名以emp_开头的表有多少？
select count() from user_tables where table_name like ‘EMP_%’ escape ‘’;
–常见函数
–数字：round(),trunc()
–字符：lower(),upper(),inticap(),length()…
select length(’ asd ‘) from dual;
select length(trim(’ asd ') )from dual;
–组函数(聚合函数）:
—count(),sum(),avg(),max(),min()
—其他函数
—decode(判断条件，匹配1，值1，匹配2，值2…默认值)
—分组 group by
—select后面出现的字段，如果没有被组函数处理，则必须出现在group by 后面
—查询每个部门的最高薪资和最低薪资？
select deptno,max(salary),min(salary) from emp_sunyn group by deptno;
select deptno,max(salary),min(salary),ename from emp_sunyn group by deptno,ename;
----薪资总和大于20000的部门数据？
select sum(salary), deptno from emp_sunyn group by deptno having sum(salary)>20000;
----排序 order by :升序asc,降序desc
— 注意：在执行排序操作时 ，null空值最大
----查询员工薪资按降序排列?
select ename,salary from emp_sunyn order by salary desc;
—语法顺序：
— select …from…where…group by…having…order by

–1.子查询：实现查询嵌套，并将子查询的结果作为著查询的条件进行再一次的查询
–查询哪位员工的薪资比张无忌高？
select ename ,salary from emo_sunyn where ename=‘张无忌’；
select ename from emp_sunyn where salary >(
select ename ,salary from emp_sunyn where ename=‘张无忌’);
select job from emp_sunyn where deptno=(select deptno from dept_sunyn where dname=‘developer’);
insert into emp_sunyn(ename) values (‘张无忌’)；
–查询哪为员工的薪资比张无忌高？
–all：所有值中最大值 any：所有值中的最小值
select ename from emp_sunyn where salary >all(select salary from emp_sunyn where ename=‘张无忌’);
select ename from emp_sunyn where salary >any(select salary from emp_sunyn where ename=‘张无忌’);
–哪位员工与小苍是同部门的，并且列出除小苍之外的员工的姓名？
select ename from emp_sunyn where deptno=(select deptno from emp_sunyn where ename=‘小苍’)and ename<>‘小苍’;
—查询哪些员工是张无忌的下属？
select ename from emp_sunyn where mgr in (select empno from emp_sunyn where ename=‘张无忌’);
–查询员工的姓名与职位，这些员工所在部门的平均薪资大于5000？
select ename,job from emp_sunyn where deptno in(select deptno from emp_sunyn group by deptno having avg(nvl(salary,0))>5000);
—关联子查询：sql语句并不是独立存在，需要依赖主查询传来的参数进行查询
—哪些员工的薪资比本部门的平均薪资低？
select ename ,salary,deptno from emp_sunyn e where salary <(select avg(nvl(salary,0)) from emp_sunyn where deptno =e.deptno);
–exists:判断子查询是否返回数据，有数据返回则为True，反之false；并不关心子查询的结果。
–查询哪些员工是其他人的经理？
select ename from emp_sunyn e where exists(select ename from emp_sunyn where mgr=e.empno);
select ename from emp_sunyn where empno in（select distinct mgr from emp_sunyn)；
–查询哪些员工不是其他人的经理？
select ename from emp_sunyn e where not exists(select ename from emp_sunyn where mgr=e.empno);
—注意：当表中数据有null时，not exists与not in不能互换

1.内连接：
表1 join 表2 on 条件
内连接的结果集：两个表的内容有相同的
–查询员工的姓名和所在的部门名字和城市？
select ename,dname,location from emp_sunyn e join dept_sunyn d on e.deptno = d.deptno;
–查询员工的姓名和他的领导的姓名？
select ename,ename from emp_sunyn e1 join emp_sunyn e2 on e1.mgr in e2.empno;
2.外连接：
左外连接：
表1 left [outer] join 表2 on 条件
驱动表 匹配表
左外连接结果集：驱动表中所有的数据与匹配表中与之匹配上的数据组成的集合
右外连接：
表1 right [outer] join 表2 on 条件
匹配表 驱动表
右外连接结果集：匹配表中所有的数据与驱动表中与之匹配上的数据组成的集合
全外连接：
表1 all [outer] join 表2 on 条件
全外连接结果集：不管驱动表中所有的数据与匹配表中的数据是否匹配上所有数据的集合
–查询员工的姓名及所有部门的信息？
select ename,d.deptno,dname,location from emp_sunyn e right join dept_sunyn d on e.deptno = d.deptno;
3.视图：
作用：简化查询：隐藏部分数据
视图操作与表相同
试图并不包括任何数据只是基表的投影
语法：
create view 视图名 as select…
–创建一个包含20部门的员工信息的视图？
create view a as select ename from emp_sunyn deptno=20;
–修改数据
update emp_sunyn set ename=‘小兰’ where ename=‘小苍’;
–删除数据
delete from emp_sunyn where ename=‘小兰’;
rollback;
索引：
作用：提高查询效率
语法：
create [unique] index 索引名 on 表名 (字段名) ;
若表中有主键约束或唯一约束，则索引会自动创建
create index idx_emp on emp_sunyn(empno);
create index idx_score on score (sid,cid);–联合索引
序列：
Oracle，DB2数据库提供的对象
保证主键值或唯一值的唯一性：自动生成的整数（自动增长）：将序列作为表中的主键
语法：
create sequence 序列名;
create sequence my_seq;
–查询序列数值
select my_seq.nextval from dual;
select my_seq.currval from dual;
create sequence my_seq_1 start with 10 increment by 2;
insert into student(sid,sname) values (my_seq.nextval,‘Lily’);
drop sequence my_seq;
存储过程：
DECLARE
声明定义变量，类型，游标。。。
BEGIN
功能实现
EXCEPTION
处理异常
END:
SET SERVEROUTPUT ON;–每次重启sql developer时运行此语句一次
–输出HELLOWORLD
BEGIN
DBMS_OUTPUT.PUT_LINE(‘helloworld’);
END;

----触发器
select empno from emp_sunyn where ename=‘张三丰’;
DECLARE
v_empno number (10);–v_empno emp_
begin
select empno into v_empno from emp_sunyn where ename=‘张三丰’;
DBMS_OUTPUT.PUT_LINE(‘员工的编号是’+v_empno);
END;
—使用loop循环实现输出1-5之间的平方数
DECLARE
i number :=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(i||‘的平方数为：’||ii);
I := I+1;
EXIT WHEN I>5;
END LOOP;
END;
—使用for循环实现10的阶乘
DECLARE
N NUMBER :=1;
BEGIN
FOR V_COUNT IN 1…10 LOOP
N:= NV_COUNT;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘10的阶乘为：’||N);
END;
DECLARE
X NUMBER :=0;
BEGIN
<<RE_LOOP>>
X :=X+1;
DBMS_OUTPUT.PUT_LINE(X);
IF X < 3 THEN
GOTO RE_LOOP;
END IF;
END;
–定义游标，查询员工表中所有的数据
DECLARE
V_EMP EMP_SUNYN%ROWTYPE;
TYPE EMPEF IS REF CURSOR RETURN employees%ROWTYPE;
CURSOR_EMP EMPREF
BEGIN
OPEN CURSOR_EMP FOR SELECT *FROM EMP_SUNYN;
FETCH CURSOR_EMP INTO V_EMP;
WHILE CURSOR_EMP%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(‘员工编号:’||V_EMP.EMPNO);
FETCH CURSOR_EMP INTO V_EMP;
END LOOP;
CLOSE CURSOR_EMP;
END;
–存储过程
语法：
create [or replace] procedure procedure_name[([IN|OUT|IN OUT]DATATYPE…)]
IS|AS
DECLARE --声明
BEGIN --执行
END PROCEDURE_NAME;
调用：
1)EXEC 存储过程名；
2)BEGIN
存储过程名；
END;
删除存储过程
DROP PROCEDURE PROCEDURE_NAME;