SQL(五)

最新推荐文章于 2024-03-25 15:25:37 发布

欧麦噶没有昵称

最新推荐文章于 2024-03-25 15:25:37 发布

阅读量367

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/iostream992/article/details/106100566

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

SQL(五)

0.学习目标

掌握DML语句和事务控制
掌握修改表以及约束
掌握序列
掌握视图
掌握索引
了解数据字典
了解oracle中权限控制

1.DML

数据查询语言(DQL)

用于检索数据库中的数据，主要是SELECT语句，它在操作数据库时较为频繁
数据操纵语言（DML）
用于改变数据库中的数据，主要包括INSERT、UPDATE和DELETE 3条语句
事物控制语句（TCL）
用于维护数据的一致性，包括COMMIT,ROLLBACK,SAVEPOINT 3条语句。
数据定义语句（DDL）
用于建立、修改、删除数据库对象
数据控制语言（DCL）
用于执行权限授予和权限收回操作，主要包括GRANT、REVOKE两条命令

2.单表CRUD

准备工作建表语句

create table t_user( 
  id number,
  name varchar2(50) constraint user_name_nn not null,
  email varchar2(50),
  gender varchar2(1),
  age number,
  birthday date,
  constraint user_id_pk primary key(id), 
  constraint user_email_un unique(email), 
  constraint user_gender_ck check(gender in ('f','m'))
);                          
                                
-- drop table t_user;
select table_name from user_tables;

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nS9VsbcL-1589357361947)(C:\Users\zzb\Desktop\笔记\oracle-day4 .assets\image-20200512152721820.png)]$

2.1 insert

语法

insert into 表名(列名1,列名2,列名3 ...) values(对应值1,对应值2,对应值3 ...);

insert into 表名 values(对应值1,对应值2,对应值3 ...);

插入不带列名

insert into t_user values(1,'zzb','zzb@qq.com','f',15,'11-8月-2020');

--按照表列的顺序依次插入数据,违反顺序或者是任意一种约束插入失败

插入时带列名

insert into t_user(id,name,email,gender,age,birthday) values(1,'zzb','zzb@qq.com','f',15,'11-8月-2020');

插入时列名也可以随意交换位置，只要数据与列相对应即可

insert into t_user(name,id,email,gender,age,birthday) values('zzb',2,'zzb@qq.com','f',15,'11-8月-2012');

插入时列名可以不用写完
```
insert into t_user(id,name,gender) values(3,'lisi','f');
```
其他未写的列则插入null值。需要注意的是 unique,check约束可以为null,但是主键约束和非空约束不能为null。
特殊插入

可以把select查询结果插入到表中，前提是查询列数据类型的顺序和插入列的数据类型顺序保持一致。

同时查询的结果也要满足约束条件
```
insert into t_user(id,name,brithday) 
select id,last_name,start_date
from s_emp;
```

2.2 delete

语法

delete from 表名 where 列名 = 条件;

delete from 表名;

删除id为1的数据
```
delete from t_user where id = 1;
```
删除整个表里面的数

注意:只是删除表里面的数据，并没有删除表
```
delete from t_user;
```

2.3 update

语法

update 表名 列名 = 值,列名 = 值 where 列名 = 条件;
update 表名 列名 = 值,列名 = 值;

修改表中id为1的数据，将名字改成 那小子真帅,年纪改成21
```
update t_user set name = '那小子真帅',age = 21 where id = 1;
```
将表里面所有的名字改成 那小子真帅,年纪改成21
```
update t_user set name = '那小子真帅',age = 21;
```

3.主外键关系CRUD

准备工作

create table t_customer( 
    id number,
    name varchar2(20) constraint customer_name_nn not null, 
    constraint customer_id_pk primary key(id)
);

create table t_order( 
	id number, 
	price number, 
	customer_id number, 
	constraint order_id_pk primary key(id),
	constraint order_cid_fk foreign key(customer_id) references t_customer(id)
);

3.1 insert

注意，在有主外键关系的情况下,一定要先插入没有外键的一方，再插入有外键的一方。

因为外键列的值必须要在另外一张表中的主键列出现过。否则插入不进去

插入 t_customer

insert into t_customer values(1,'tom');
insert into t_customer values(2,'jerry');
insert into t_customer values(3,'terry');

插入 t_order

insert into t_order values(1,3000,1);
insert into t_order values(2,3000,2);
insert into t_order values(3,3000,2);
-- 上面能够插入成功是因为 外键 外键列中的值 在 t_customer表中的主键列出现过。同时外键列的值可以重复

-- 外键列也可以为空，如果是在建表的时候手动给外键列添加了非空约束，那么就不能为空
insert into t_order values(1,3000,null);

-- 插入失败，因为外键列的值 6 并没有在主键列中存在过
insert into t_order(id,price,customer_id) values(4,5000,6);

3.2 update

可以修改外键列的值，但是修改后的值也必须在另外一张表中的主键列中存在。

修改t_order中id为1的数据的customer_id 为 3

update t_order set customer_id = 3  where id = 1;

修改t_order中id为1的数据的customer_id 为 null

update t_order set customer_id = null  where id = 1;

修改t_order中id为1的数据的customer_id 为 1000

update t_order set customer_id = 1000  where id = 1;

-- 修改失败，因为在t_customer中的主键列没有 1000的值

3.3 delete

当删除的最好是先删除有外键的一方在删除无外键的一方。

因为如果先删除无外键的一方，那么就会导致另外一张表里面的外键列的值不知道怎么处理从而导致报错。

delete from t_order;

删除删除成功。因为这个表里面的数据不对对t_customer的数据产生任何进行影响。

再次插入数据进行测试：

insert into t_order values(1,3000,1);
insert into t_order values(2,3000,2);
insert into t_order values(3,3000,2);

删除 t_customer 中id为3的数据。

delete from t_customer where id = 3;

删除成功，因为id为3并没有在 t_order里面充当了外键。所以删除后不会对t_order表里面的数据产生影响。

删除 t_customer 中id为1的数据。

delete from t_customer where id  = 2;

删除失败，因为在 t_customer里面id为2的数据,在t_order里面充当外键。所以当把id为2的数据删除以后。

t_order不知道怎么处理外键列customer_id 中为2的数据。因此报错。

要想不报错，可以先删除有外键的一方，在删除没有外键的一方。

但是如果非要直接删除没有外键的一方，那么就需要在建表的时候告诉有外键的一方，当没有外键的一方删除数据时，有外键的一方该怎么处理。

处理方式有三种:

on delete no action 默认就是这种
on delete cascade 级联操作，当删除数据时，另外一张表中也删除相关数据
on delete set null 级联操作，当删除数据时，将另外一张表中相关的外键数据设置为空

3.3.1 默认方式

on delete no action

已上面 t_order,t_customer为例

delete from t_customer where id = 2;

报错,错误信息显示为: 违反完整约束条件- 已找到子记录

3.3.2 on delete cascade

需要在建表的时候指定外键的操作

create table t_teacher(
  id number primary key,
    name varchar2(255)
);
create table t_student(
  id number primary key,
  name  varchar2(255),
  teacher_id number references t_teacher(id) on delete cascade
);

insert into t_teacher(1,'wangzh');
insert into t_teacher(2,'xiezl');

insert into t_student(1,'天使1号',1);
insert into t_student(2,'天使2号',1);
insert into t_student(3,'天使3号',2);

删除 t_teacher表中id为1的数据

delete from t_teacher where id = 1;

发现 t_student中外键为1的数据也直接被删除了

3.3.3 on delete set null

create table tbl_hunsband(
  id number primary key,
    name varchar2(255)
);
create table tbl_wife(
  id number primary key,
  name  varchar2(255),
  hunsband_id number reference tbl_hunsband(id) on delete set null
);

insert into tbl_hunsband(1,'pantf');
insert into tbl_hunsband(2,'liangwei');

insert into tbl_hunsband(1,'xiaotao',1);
insert into t_student(2,'niurui',2);

删除 t_hunsband 表中 id为1的数据

delete from tbl_student where id = 1;

发现 t_wife表中相关外键数据置空了。

4.数据库事务

事务简介
- 事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。
- 这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行。
- 事务是一组不可再分割的操作集合。
产生事务的语句
- 只有DML(insert,delte,update)语句才会产生事务,其他语句不会产生事务。
- DML语句执行的时候,如果当前有事务,就使用这个事务,如果没有,执行的DML语句就会产生一个新的事务
- commit/rollback/DDL(create,drop)语句都可以把当前事务给结束掉
事务的提交与数据
- commit/DDL结束事务是把这个事务给提交。
  - 事务提交是指:这个事务里面所有的操作都生效到数据库中
  - 回滚事务是指: 这个事务里面所有的操作都撤销。
- 如果一个客户端对一张表进行DML操作，但是没有提交事务，那么其他的客户端就不能对这个表进行DML操作(手动测试)。
- 事务案例
DML 语句一定要提交/回滚事物
事务特征(ACID)
- Automic 原子性
  
  整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  
  例如：银行转账，从 A 账户转 100 元至 B 账户，分为两个步骤：从 A 账户取 100 元；存入 100 元至 B 账户。这两步要么一起完成，要么一起不完成
- Consistency 一致性
  
  在事务开始之前和事务结束以后，数据库数据的一致性约束没有被破坏；即当事务 A 与 B 同时运行，无论 A，B 两个事务的结束顺序如何，数据库都会达到统一的状态。
  
  例如：银行转账，从 A 账户转 100 元至 B 账户，无论账户之间转了多少次，两个人的钱的和与转账之前钱的和是一致的。
- Isolation 隔离性
  
  隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。事务独立
  
  对于任意两个并发的事务T1和T2 , 在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发执行-
- Durability 持久性
  
  当某个事务一旦提交，无论数据库崩溃还是其他未知情况，该事务的结果都能够被持久化保存下来。
事务并发所带来的问题

当并发访问数据库时，那么数据库会进行事务隔离操作。以保证获取数据的准确性。但是当高并发的情况话，事务隔离可能会带来以下问题
- 脏读
- 不可重复读
- 幻读
脏读

主要是update操作所引起的。一个事务A读取到了事务B修改但是还未提交的数据。
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rzmh1wmw-1589357361965)(C:\Users\zzb\Desktop\笔记\oracle-day4 .assets\image-20200513153016047.png)]$

当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个其他的事务来访问该数据，就会有可能造成两个事务得到的数据不一致。

例如：用户A向用户B转账100 元
```
update account set  money=money - 100 where name = 'A';  -- 此时 A 通知 B 但是没有提交事务
```
当执行了第一条sql语句 , 此时 A通知B 说钱已经转了。那么B查看账户钱确实到了,这就是脏读。

事后 A 进行事务回滚。那么当 B再次查看，钱没了。

正常情况下当 A转了钱以后，没有提交事务，那么 B 是看不到账户多了 100的,但是由于发生了脏读，那么看见了。

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tFHAFDup-1589357361966)(C:\Users\zzb\Desktop\笔记\oracle-day4 .assets\image-20200513152617026.png)]$
不可重复读

主要update操作引起。事务A在第一次读数据和第二次读数据之间,有另一个事务B把这个数据更改并提交了，所以就出现了事务A里面读数据俩次,但是读到的结果是不同的。
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qIEzG8ZN-1589357361968)(C:\Users\zzb\Desktop\笔记\oracle-day4 .assets\image-20200513153045899.png)]$
事务T1 在读取数据据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发生了不可重复读。

不可重复读和脏读的区别是，脏读是读取了一个事务修改但是并没提交的数据。不可重复读是读取了前一个事务已经提交的数据。

但其实某些情况下，不可重复读反而不是问题，多读取数据不一致，那么以最后一次为主。但是特殊情况例外：例如 A B读取同一个数据，但是读取的结果确实不一样的，那么就是导致分歧。
幻读

主要是insert/delete引起。事务A第一次用where条件筛选出了10条数据，事务A第二次用通样的where条件筛选出的却是11条数据，因为事务B在事务A的第一次和第二次查询之间进行了插入操作，并且插入的这个数据满足事务A的where筛选条件，就好像产生幻觉一样，这就是发生了幻读
$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aYKnulHi-1589357361970)(C:\Users\zzb\Desktop\笔记\oracle-day4 .assets\image-20200513153121670.png)]$

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不
同），所不同的是不可童复读査询的都是同一个据项，而幻读针对的是一批数据整体(比如数据的个数)。
事务隔离级别

为了避免上述问题的出现，数据库中设置了事务隔离级别。通过设置事务隔离级别从而去避免这些问题的发生
- read-uncommitted 读未提交级别最低不提交也能读
- read-committed 读已提交提交以后才能读解决了脏读
- repeatable-read 可重复读解决了脏读和不可重复度
- serializable 串行化解决所有问题
级别越高，那么解决的问题也就越多，但是同样的消耗的资源也比较大。

例如串行化就是采用锁表的机制(类似于java中的锁机制),使其他线程在所外面等待，所以到底选择设置哪种事务隔离级别跟实际需求有关系。
设置事务隔离级别

并不是所有的关系型数据库都支持这四种隔离级别。oralce就仅支持读已提交和串行化

而mysql数据库就支持四种。

图中红色部分是oracle不支持的。oracle默认的事务隔离级别就是 读已提交。

当然在后面我们学习Spring框架的时候还会再次讲事务隔离级别。

保存回滚点

--DML语句
savepoint A;
--DML 语句 
savepoint B;
--DML语句3
rollback to A;
--回滚到A

上述例子就是并不是回滚所有的事务，而是回滚到回滚点A

5.修改表约束

update 属于 DML语句，修改表中的数据

alter 属于 DDL 语句, 修改对象结构。

准备工作

create table t_user(
	id number constraint user id pk primary key, 
	name varchar2(100), 
	salary number
);

添加新的一列
```
alter table t_user add brithday date;
```

删除一列

alter table t_user drop column birthday;

给表添加约束

alter table t_user add contraint user_name_un unique(name);

给表删除约束

alter table t_user drop contraint user_name_un;

修改表的名字
```
rename t_user to mytest;
```

修改表中某列类型

alter table mytest modify(name varchar2(20));

让约束失效

alter table t_user disable constraint user_id_pk cascade;

截断表

truncate table t_user;

相当于

delete from t_user
commit;

注释(了解)

给表添加注释

comment on table t_user is '用户表';

给列添加注释

comment on column t_user.id is '用户编号';

当然上述所有操作可以利用plsql developer进行图形化界面操作。

6.序列

介绍

序列也是数据库中一个独立对象。其作用主要用来帮助表去创建自动增长的主键。oracle数据库所特有的对象。
创建序列
```
create sequence 序列名
```

序列操作仅有两个

查看序列的当前值
```
select 序列名.currval from dual;
```
查看序列下一个值
```
select 序列名.nextval from dual;
```

如果想要在创建序列时指定更多的属性,可以使用以下这个

create sequence test_seq
increment by 2
start with 10
maxvalue 100
nocycle      
nocache 10;

删除序列
```
drop sequence 序列名;
```

序列去产生主键

insert into t_user values(test_seq.nextval,'tom',15);

7.视图

简介

视图也是数据库一个对象。视图是提取一张表或者多张表的数据生成一个映射。操作视图从而达到操作原表的效果，方便数据管理和安全操作。
作用

1.隐藏表中的重要数据

2.代替比较长的sql语句
视图分类
- 简单视图
- 复杂视图

简单视图

基于单表所创建的视图，且创建时不含任何函数和数据分组操作。通过修改简单视图从而达到操作原表的效果

先赋予权限，因为普通用户没有创建视图的权限
```
grant create view to jd2001_2005;
```

创建视图

create or replace view 视图名字
as 
sql语句


create or replace  view v_test
as
select id,last_name,dept_id
from s_emp
where id < 10;

删除视图
```
drop view 视图名字
```

对视图进行CRUD操作

select * from v_test;

-- 发现 s_emp表也插入了数据
insert into v_test(id,last_name,dept_id) values(100,'tomson',null);

--发现delete 表也删除了数据
delete from v_test where id = 100;

-- 发现delete表也更新了数据
update v_test set dept_id = 3 where id = 10;

commit;

如果想要创建只读的简单视图，那么就可以采用 with read only

create or replace view vv_test
as
select *
from s_emp
where id < 10
with read only;
-- 这个视图只能用来查询不能修改

创建复杂视图

复杂视图是不能够通过修改视图数据从而达到修改原表的数据

复杂视图可以通过两张表或者单张表创建。同时也可以利用函数和分组进行创建
```
create or replace view vvv_test
as
select avg(salary),dept_id
from s_emp
group  by dept_id;
```
只能用来查询。

8.索引

介绍
- 索引也是数据库中的一个对象,主要是用来提高查询速度
- 类似书的目录结构
- 索引是与表关联的一个对象,能提高查询速度。
- 索引直接指向包含查询之的行位置，减少磁盘IO
- 索引和表是独立的物理结构
- oracle会自动使用并维护索引,插入，删除，更新表以后会自动更新索引
索引创建
- 自动创建
  
  当在表中指定可 PK 或者 unique约束就会自动创建唯一值索引
  
  这也就是为什么根据主键查询会比较快
- 用户创建和删除
  
  用户可以自己手动创建非唯一值索引,提高数据访问效率
```
create index 索引名
on 表名(列名);

create index emp_index
on s_emp(last_name);

drop index 索引名;
```
  注意如果没有大量数据，是测试不出来的。
索引创建规则：
- 列经常作为where子句的限定条件或者作为连接条件
- 列包含的数据量很大，并且很多非空的值。
- 两个或者更多列频繁的组合在一起作为where的限定条件或连接条件
- 列总是作为搜索条件
- 索引査出的数据量占2%〜4%
- 索引不是越多越好，不是索引越多越能加速查找。
- 要索引的表不经常进行修改操作
索引的种类
- 唯一值索引 unique index
- 非唯一值索引 nounique index
- 单行索引 single index
- 多行索引 concatenated index

9.权限控制

创建用户(只能在system用户下创建)

create user 用户名 identified by 密码;

删除用户
```
drop user 用户名 cascade;
```

赋予权限

grant 权限 to 用户名

-- 把建序列和建表的权限付给zhangsan
grant  create view,create table to zhangsan;

角色

角色是一组权限的集合

connect角色就包含了用户的登录登出权限等
resource角色包含了对对象的操作。但是不包含对视图对象的操作
```
grant resource,connect to zhangsan;
```

将某个用户的权限赋值给另外一个用户

grant 权限 on 对象 to 用户名

grant select on jd2001_2005.s_emp to zzb;

conn zzb/zzb;

select * from jd2001_2005.s_emp

回收权限

revoke 权限 on 对象 to 用户名

revoke select on jd2001_2005.s_emp from zzb;

10.同义词

介绍

同义词也是数据库中的对象。通过上面的例子我们发现，当一个用户的权限赋予了另外一个用户的权限。例如
```
grant select on jd2001_2005.s_emp to zzb;

conn zzb/zzb;

select * from jd2001_2005.s_emp
```
那么去查询还是需要用户名.s_emp
那么我们想想哑表为什么不需要这样而是直接使用 dual.
因为哑表就是一个同义词。
同义词作用&分类

作用:隐藏表里面原来的信息
分为:私有同义词和公共同义词

私有同义词

给表创建一个私有同义词(普通用户没有创建同义词的权限)

create synonym 同义词名
for 表名;

create synonym sy_emp
from s_emp;

--将查看同义词权限付给zzb
grant select on jd2001_2005.sy_emp to zzb;
-- 收回权限
revoke select on jd2001_2005.sy_emp to zzb;
-- 删除同义词
drop synonym 名字

conn zzb/zzb

select * from jd2001_2005.syemp;

公共同义词

创建公共同义词就必须切换到system用户，普通用户没有创建公共同义词的权限

create public synonym 同义词名
for 用户名.表名;

create public synonym syn_region
for jd2001_2005.s_region;


-- 让所有人都可以查询到这个同义词
grant select on syn_region to public;

conn zzb/zzb;
select * from syn_region;

11.数据字典(了解)

简介

用来让使用者来了解数据库里面的一些信息例如：对象信息或者用户信息
- 数据字典在数据库安装时已经就创建了
- 数据字段里面的数据被数据库服务器自动维护,更新
常用的数据字典(都是视图)

USER开头的视图里面存放着用户自己拥有的对象
ALL开头的视图存放着用户有权限查看的对象
DBA开头的视图存放着数据库所有的对象
V$开头的视图存放数据库运行的一些性能属性数据
User开头的数据字典

包含当前用户所拥有的相关对象信息。
查询用户拥有的所有表的名字
select table_ name from user_ tables;

查询出 s emp表中的列及其对应的约束名字
select constraint name, column_ name
from user cons_columns
where table_ name = ‘S_EMP’;
all 开头的数据字典

包含当前用户有权限访问的所有对象的信息
查到当前用户有权限访问的对象
select table_ name from all tables;
dba开头的数据字典

只能是有dba权限的用户查询,能查到数据库中所有对象
select table_ name from dba_tables; (sys system)

欧麦噶没有昵称

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL(五)

0.学习目标掌握DML语句和事务控制掌握修改表以及约束掌握序列掌握视图掌握索引了解数据字典了解oracle中权限控制1.DML数据查询语言(DQL)用于检索数据库中的数据，主要是SELECT语句，它在操作数据库时较为频繁数据操纵语言（DML）用于改变数据库中的数据，主要包括INSERT、UPDATE和DELETE 3条语句事物控制语句（TCL）用于维护数据的一致性，包括COMMIT,ROLLBACK,SAVEPOINT 3条语句。数据定义语句（DDL）用于
复制链接

扫一扫