数据库基础②

❣💞❣ 数据库基础②

---

目录
1、92语法 联表查询
2、Rowid与Rownum数据库的对象
3、创建表、插入、修改、删除(约束的添加）
4、事务
5、DML （Insert、upDate、delete)
6、数据截断：Truncate
7、视图
8、索引
9、序列工具

---

一、92语法 联表查询

1、交叉连接：corss join

-- 交叉连接
select * from emp cross join dept;

2、自然连接 natural join

注意：如果出现多个同名字段则都做等值连接

-- 内连接
select * from emp natural join dept;

3、using连接：join using(同名字段)

-- using
select * from emp e join dept d using(deptno);

4、等值非等值连接join on

注意；join....on 同名字段需指定限定词

-- join on
select * from emp e join emp d on e.mgr=d.empno;

5、左连接left join on

-- left  join左连接
select * from emp e left join emp d on e.mgr=d.empno;

6、右连接right join on

-- right join 右连接
select * from emp e right join emp d on e.mgr=d.empno;

---

二、Rowid与Rownum数据库的对象

1、rowid作为伪列

-- rowid
select ename ,sal,empno,rowid from emp ;

-- 去重
select * from tb_student;
minus
select distinct id,name,course,score from tb_student;
--rowid

select id,name,course,score,max(rowid)from tb_student group by id,name,course,score;
select id, name, course, score
  from (select id, name, course, score, max(rowid)
          from tb_student
         group by id, name, course, score);
-- rowid去重通过rowid进行获取         
select *
  from tb_student
 where rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
                  
select *
  from tb_student
 where not rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
                  
                  
delete from tb_student
 where not rowid in (select max(rowid)
                   from tb_student
                  group by id, name, course, score);
                  
select * from tb_student;

2、rownum结果集中行的序号，从一开始

分页

-- rownum 
select deptno,dname,loc,rownum ro from dept;

-- 分页显示
select empno,ename ,sal,rownum from emp;
select *
  from (select empno, ename, sal, rownum ro from emp)
 where ro >= 4
   and ro <= 6;

---

三、创建表、插入、修改、删除

1、创建表和约束的添加

create table shop(shopno number(10),shopname varchar(10));
select * from shop
insert into shop values (1000,'薯片');

主键约束，外键约束，唯一约束，非空约束，检查约束，默认约束

-- 创建表主键，外键，非空约束，唯一约束，检查约束，默认约束
-- user表
create table tb_users(
       userno number(4) primary key,
       username varchar2(3 char) unique );
-- shop表
create table shop(
       shopno number(10) primary key,
       shopname varchar2(10) not null,
       shopprice number(10),
       shoptime date default(sysdate),
       userno number(4)references tb_users(userno));
       
-- 插入数据      
insert into tb_users values (1011,'邵正淳');
insert into tb_users values (1012,'沈子杰');
--显示数据
select * from tb_users
select * from shop
--插入数据
insert into shop values (1000111111,'薯片',1000,sysdate,1011);
insert into shop values (1000111121,'辣条',1000,sysdate,1012);
insert into shop values (1000111112,'薯片',1000,sysdate,1011);

---

---

2、添加约束的方式

• 普通的创建表时添加

  -- shop表
  create table shop(
         shopno number(10) primary key,
         shopname varchar2(10) not null,
         shopprice number(10),
         shoptime date default(sysdate),
         userno number(4)references tb_users(userno));
  

• add添加

  add constraints 约束名 primary key(字段名)
  

• alter追加

  alter table student add constraints 约束名 primary key(字段名)
  

3、表的拷贝

• 拷贝表结构

create table xx_emp as select * from emp where 1！=1;
select * from xx_emp;

---

• 拷贝表结构+内容

  create table yy_emp as select * from emp where deptno=20;
  select * from yy_emp;
  

---

4、表的删除和注释

• 注释：

comment on table tb_users is '用户表'

• 删除表

  • 默认删除从表，再删除主表
  • 删除主表的同时级联删除主从表之间的约束

  drop table shop;
  drop table tb_users;
  

5、修改表

• rename x to y
• alter table x rename column z to y

---

四、事务

什么是事务：指作为单个逻辑工作单元执行的一组相关操作这些操作要求全部完成或者完全不完成

作用：使用事务是为了保证数据安全有效。

1、事物的特点ACID：

• 原子性:要么都成功，要么都失败。
• 一致性：事务完成时，必须在相关表中的到反映。
• 隔离性：事务应在另一个事务对数据的修改前后。
• 持久性：保证事务对数据库的修改是持久有效的，发生故障也不失去。

2、事务隔离级别：

	脏读	不可复读	幻读
Read uncommitted （insert)	√	√	√
Read committed(update)	×	√	√
Repeatable read（update）	×	×	√
Serializable(串行读)（insert）	×	×	×

3、事务的开启与结束

• 自动做增删改操作的时候默认开启事务
• 可以手动开启事务
• 结束：
  • 成功提交：commit|DCL|DDL|正常关闭客户端|自动提交
  • 失败回滚：roll back回滚|意外退出

---

五、DML （Insert、upDate、delete)

• Insert 插入数据

  -- 插入信息
  create table yy_emp as select * from emp where deptno=20;
  select * from yy_emp;
  
  insert into yy_emp values(7111,'ABCD','LEADER',8888,sysdate,30000,1000,40);
   
  insert into yy_emp select * from emp where deptno=30;
  

  ---

• UpDate 更新数据

  -- 更新数据
  update yy_emp set comm=0.00 where deptno=20;
  

  

---

• Delete 删除记录
  • 当要删除的时普通表的数据时—>OK
  • 当要删除的时从表中的记录时---->OK
  • 当要删除的时主表中含有从表引用数据时：①删除主表中的没被从表中数据引用的主数据—>OK
  • ②要删除主表被引用的从表数—>NO
    • 可以删除从表数据再删除主表数据（默认方式i）
    • 删除主表中数据后面加on delete cascade
    • 或者加on delete set null

--删除数据
delete shop 
delete shop where userno=1011;
delete tb_users where userno=1011;

---

六、数据截断：Truncate

• 截断表中的所有数据

• 截断的主表中数据，直接再表结构上检查被从表引用，不允许截断

• 删除所有数据的效果delete可以删除所有

• trancate 与delete的区别

  • 相同点:truncate和不带where子句的delete, 以及drop都会删除表内的数据

  • 不同点:

    • 1.truncate和 delete只删除数据不删除表的结构(定义) drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态。

    • 2.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发。 truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。

    • 3.delete语句不影响表所占用的extent, 高水线(high w2atermark)保持原位置不动，显然drop语句将表所占用的空间全部释放 。truncate 语句缺省情况下将空间释放到 minextents个 extent,除非使用reuse storage; truncate会将高水线复位(回到最开始).

    • 4.速度,一般来说: drop> truncate > delete

    • 5.安全性:小心使用drop 和truncate,尤其没有备份的时候.

---

---

七、视图

• 逻辑视图：数据来自于数据源
• 物理视图：能存储数据
• 封装简化

1、创建视图

create view xx as (select * from emp where sal>1000) [with only read];

2、授权和回收

grant dba to SCOTT;
revoke dba from SCOTT;

3、删

drop view xx;

---

八、索引

• 为表中的字段添加索引
• 相当于目录
• 根据这个字段大量的查询，少量增删，加快查询效率
• 数据量较大，适用索引
• 索引是数据库的对象之一，需要维护
• Oracle自动为主键索引
• 唯一性较好的建立索引
• 索引的使用是透明的，创建和删除不影响查询语句，不影响使用

1、创建索引和删除

-- 创建
create index index_001 on emp(empno,ename);
select empno,ename from emp where sal>1500;
-- 删除
drop index index_001;

---

九、序列工具

• 序列第一次获取必须获取nextval最新值（序列名.nextval)
• 序列名.currval：获取序列工具当前值
• 序列值一旦开始，无法恢复

1、创建序列工具

create sequence seq strat with 10 increment by 10;

2、删除序列工具

drop sequence seq;