Oracle---day02

1.exists(查询语句)：存在的意思 当做布尔值来处理，有结果返回true没有返回false

　--查询有员工的部门信息
　　select * from dept d1 where exists(select * from emp e1 where e1.deptno=d1.deptno)
　　select * from dept d1 where d1.deptno in (select deptno from emp)

2.rownum：伪列，系统自动生成的一列，代表行号。rownum是Oracle中特有的默认值为1，查询出一条结果就+1。因此不能做大于判断，只能做小于判断。（还有一个rowid，存放每一条记录的真实物理地址）

　--找到工资最高的前三名员工
　　select * from (select * from emp order by sal desc) where rownum<=3;

　--使用rownum实现分页查询，Oracle中只能用子查询实现分页查询
　　select * from (select rownum hanghao, emp.* from emp) ee where ee.hanghao between 6 and 10;      --每页5条记录，第2页

3.子查询

　1.单行子查询--查询员工表中薪水大于本部门平均薪水的员工信息
　　select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno);

　2.多行子查询--统计每年入职的员工个数将结果横着显示
　　select
　　　　sum(cc) "total",
　　　　sum(case yy when '1980' then cc end) "1980",
　　　　sum(case yy when '1981' then cc end) "1981",
　　　　sum(case yy when '1982' then cc end) "1982",
　　　　sum(case yy when '1987' then cc end) "1987",
　　from (select to_char(hiredate, 'yyyy') yy count(1) cc from emp group by to_char(hiredate, 'yyyy'));

4.集合运算

　1.并集：union：去除并集过后重复的值，并根据第一列排序，，union all：不会去重，也不会排序（或是在条件判断中使用 or 关键字）
　　--查询工资大于1500，或20号部门下的员工
　　select * from emp where sal > 1500
　　union
　　select * from emp where deptno=20;

　2.交集：intersect  （或是在条件判断中使用 and 关键字）
　　--查询工资大于1500，且20号部门下的员工
　　select * from emp where sal>1500
　　intersect
　　select * from emp where deptno=20

　3.差集：minus
　　--查询1981年入职的员工，不包括总裁和经理
　　select * from emp where to_char(hiredate,'yyyy') = '1981'
　　minus
　　select * from emp where job = 'PRESIDENT' or job = 'MANAGER'

5.Oracle体系结构

　数据库-->数据库实例-->表空间(用户在里面创建表)-->数据文件

　1.创建表空间，通常新建一个项目就会创建一个表空间，在表空间中创建用户，用户再创建表
　　语法：create tablespace 表空间名字
　　　　    datafile '文件路径'     -----服务器上的路径
　　　　    size 初始大小
　　　　　autoextend on   --自动扩展
　　　　　next 每次扩展的大小

　　切换到系统用户，创建表空间
　　　create tablespace handong
　　　datafile ‘c:handong.dbf’
　　　size 100m
　　　autoextend on
　　　next 10m

　　删除表空间---只能删除逻辑关系，不会删除磁盘上的文件
　　　drop tablespace handong;

　2.创建用户：语法：
　　　　　　　　　　create user 用户名
　　　　　　　　　　identified by 密码
　　　　　　　　　　defualt tablespace 表空间名

　　创建用户并给用户授权：
　　　create user xiaoming
　　　identified by 123456
　　　defualt tablespace handong

　　授权：grant connect to xiaoming

　　 权限：connect：是授予最终用户的典型权利，最基本的权力，能够连接到ORACLE数据库中，并在对其他用户的表有访问权限时，做SELECT、UPDATE、INSERTT等操作
　　　　　 resource：是授予开发人员的，能在自己的方案中创建表、序列、视图等
　　　　　  dba：是授予系统管理员的，拥有该角色的用户就能成为系统管理员了，它拥有所有的系统权限

--单表约束
create table student(
       sid number(10) primary key,
       sname varchar2(10) unique,
       age number(4) not null,
       sex varchar2(4) check(sex in ('男','女','人妖'))
)

insert into student values(2011, '田瑞', 22, '人妖')
insert into student values(211, '田', 22, '男')

select * from student

--多表约束，，外键约束
--商品分类表
create table category(
       cid number primary key,
       cname varchar2(10)
); 
--商品表
create table product(
       pid number primary key,
       pname varchar(10),
       cno number
);

insert into category values(1,'手机数码');
insert into product values(1,'华为p30',10);

truncate table product; -- 删除所有数据

--添加外键约束
alter table product add foreign key(cno) references category(cid);
--添加外键约束，使用级联关联--联级删除
alter table product add foreign key(cno) references category(cid) on delete cascade;


select * from category;
select * from product;

4.修改表---添加列，修改列类型，修改列名，删除列，重命名表名

--创建学生表
create table stu(
       stuid number(10),
       sname varchar2(10)
)
--删除表
drop table stu;
--修改表名
rename stu to student;
rename student to stu;

--增加一列
alter table stu add sex varchar2(2);

--增加多列  类似创建表
alter table stu add(
      sex varchar2(2),
      phone varchar2(11)
)

--删除列
alter table stu drop column sex;

--修改列类型
alter table stu modify sex varchar2(4);

--修改列名
alter table stu rename column phone to mobile

5.数据的增删改：

/*
     插入数据：
           insert into table values()
     使用子查询插入数据：
           insert into table 查询语句
*/

--将emp中10号部门的员工插入到emp1中
insert into emp1 select * from emp where deptno=10;


/*
    更新数据：
         update tablename set 列名=列值 [where条件]
*/
update emp1 set ename = 'LUOZIYUE' where ename = 'CLARK';


/*
     删除数据：
         delete from 表名 [where条件]
         
      delete和truncate 的区别
      
      delete:           truncate:
      DML               DDL
      逐条删除          先删除表再创建表
      支持事务操作      不支持事务操作 
                        执行效率高一些  
      
*/
delete from emp1 where empno=7782;

6.其他     

　　事务：就是一系列操作要么都成功，要么都失败
　　　四大特性：原子性，隔离性，持久性，一致性
　　SQL分类：
　　　　DDL：数据定义语言；修改定义表结构————create，drop，alter，truncate
　　　　DML：数据操作语言；操作表中数据————update，insert，delete
　　　　DCL：数据控制语言————grant
　　　　DQL：数据查询语言————select