学习大数据DAY10 SQL基础语法10

目录

什么是事务

事务的开始和结束

Oracle 的事务保存点功能

自己判断什么时候提交什么时候回滚

通过逻辑程序判断

--用户表

--写入用户

1.隐式事务

Oracle锁表解锁 system用户登录

1.查看被锁的表

2.查看那个用户那个进程造成死锁

3.杀掉进程

--复制查询结果执行杀死进程

模拟等待锁定

模拟死锁

两个事务并发访问数据库数据时可能存在的问题

1. 幻想读：

2. 不可重复读取：

3. 脏读：

同义词

数据字典

数据导入导出

with..as 子查询

作业（超市管理系统）

---

SQL基础语法（九）

什么是事务

事务：它是数据库在执行一系列操作时，保证所有的操作都正确完成，要么都执行，要么都不执行，保证数据的完整性。

ACID必须具备以下四个属性，简称ACID 属性：

A:原子性（Atomicity）：事务是一个完整的操作。事务的各步操作是不可分的（原子

的）；要么都执行，要么都不执行。

C:一致性（Consistency）：一个查询的结果必须与数据库在查询开始时的状态保持一致（读不等待写，写不等待读）。

I:隔离性（Isolation）：数据库中每一个用户的操作都是互不影响的，对于其他会话来

说，未完成的（也就是未提交的）事务必须不可见。

D:持久性（Durability）：事务一旦提交完成后，数据库就不可以丢失这个事务的结果，数据就永久的保存到数据库中。 

举例：张三账户1000元，李四账户0元

所谓原子性：张三给李四转钱，张三转钱，李四收钱，这两步要么同时发生，要么都不发生，这两步就像一个原子一样，不可分割。

所谓一致性：张三与李四一共1000元，转完之后还是1000元，就跟能量守恒一样，转钱前和后保持一致隔离性：张三给李四转钱，王五也给李四转钱，这就是两个事务要隔离开来

oracle的安全机制进行增删改操作之后，先更新到缓冲区，进行第一次校验，如果使用commit再次提交，才会真正的更新到数据库

事务的开始和结束

事务采用隐性的方式，起始于session的第一条DML语句，

事务结束于：

1）COMMIT（提交）或ROLLBACK（回滚）

2）DDL语句被执行（提交）

3）DCL语句被执行（提交）

4）用户退出SQLPLUS（正常退出是提交，非正常退出是回滚）

5）服务器故障或系统崩溃（回滚）

6）shutdowm immediate(回滚）

在一个事务里如果某个DML语句失败，之前其他任何DML语句将保持完好，而且不会提交！

Oracle 的事务保存点功能

savepoint命令允许在事务进行中设置一个标记（保存点），这个标记可以控制rollback的效果，即在一个事务中回滚掉最近的部分dml语句，

保留下保存点之前的的dml语句，并使事务本身继续执行(考点）。也就是说回滚到保存点这个动作并不使事务结束。

commit 提交

rollback 回滚

保存点：savepoint 可以把在保存点之后的事务给撤销掉

操作完之后-》savepoint 保存点名字

回退到保存点-》rollback to 保存点名字

--事务，开另个登录窗口测试修改，不提交其他窗口看不到修改

select * from emp;

update emp set sal=5000 where empno=7369;

savepoint sp1;

update emp set sal=3300 where empno=7369;

savepoint sp2;

update emp set sal=2200 where empno=7369;

--rollback to XXX 不会使事务结束。

rollback to sp2;

rollback to sp1;

rollback to sp2;

commit;

自己判断什么时候提交什么时候回滚

update account set money=money-200 where id=1;

update account set money=money+200 where id=2;

rollback;

update account set money=money-50 where id=1;

update account set money=money+50 where id=2;

commit;

通过逻辑程序判断

--用户表

create table account(

id number(4) not null,

name varchar(30),

money number(10,2),

constraint pk_id primary key(id),

constraint ck_money check(money>=0)

);

--写入用户

insert into account values(1,'张三',100);

insert into account values(2,'李四',50);

commit;

select * from account;

declare

begin

update account set money=money-50 where id=1;

update account set money=money+50 where id=2;

dbms_output.put_line('转账成功');

commit;

exception

when others then

dbms_output.put_line('转账失败');

rollback;

end;

1.隐式事务

1.sqlplus 可以设置set autocommit on(它会自动地提单事务，不需要手动调用commit)

2.执行create、drop、grant、revoke等操作时，数据库会自动提交事务

2.显式事务

1.sqlplus 设置set autocommit off(执行增删改时，需要调用commit语句来提交事务)

2.rollback 事务回滚

可以使用锁机制，但是要避免死锁

数据库中两种基本类型的锁：排它锁和共享锁。当数据被上了排它锁，就不能被其他事

务读取和修改；而上了共享锁，只能被其他事务读取，但是不能被修改数据库。

update emp set sal=sal*1.1 where deptno=10;--行级排他锁

查看自己的sessionid

select userenv('sid') from dual;

解决锁定冲突

1）执行commit或者rollback结束事务

2）终止会话

3）管理员杀死进程

Oracle锁表解锁 system用户登录

既然清楚了锁表如何形成的，那么我们就要开始解锁。解锁的方法很简单，通过Sql语句就可实现。

1.查看被锁的表

Select b.owner,b.object_name,a.session_id,a.locked_mode

From v$locked_object a,dba_objects b

Where b.object_id = a.object_id;

2.查看那个用户那个进程造成死锁

SELECT s.sid, q.sql_text

FROM v$sqltext q, v$session s

WHERE q.address = s.sql_address AND s.sid = &sid -- 这个&sid 是第一步查询出来的

ORDER BY piece;--查看导致锁死的SQL

Select

b.username,b.sid,b.serial#,logon_time

From v$locked_object a,v$session b

Where a.session_id = b.sid order by b.logon_time;

3.杀掉进程

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock" FROM v$session

WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

--复制查询结果执行杀死进程

模拟等待锁定

--session 1: update emp set sal=3000 where deptno=10

--session 2: update emp set sal=4000 where deptno=10

一直等待（除非 session 1 commit 或 rollback）

--session3:system执行三个步骤，忘记提交的回滚，后面执行的提交

模拟死锁

--T1时刻

--事务1：session1执行

update emp set sal=sal1.1 where deptno=10;

--事务2：session2执行

update emp set sal=3000 where deptno=20

--T2时刻

--事务1：session1执行

update emp set sal=3000 where deptno=20

--事务2：session2执行

update emp set sal=sal1.1 where deptno=10;

--session3:system操作同上等待死锁

两个事务并发访问数据库数据时可能存在的问题

1. 幻想读：

  事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录

并commit，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。

2. 不可重复读取：

  事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录并commit，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。

3. 脏读：

  事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。

同义词

同义词：它是给数据库中的对象起一个别名，可以和使用原对象一样去使用同义词

同义词一般是给原对象取一个简单易记的名字，管理员system创建同义词

创建语法：

create or replace synonym 同义词名 for 对象名;

示例：

create synonym e for scott.emp; --给表创建同义词

select * from e;

select * from scott.emp;

create or replace synonym de for scott.dept_emp; --给视图创建同义词

select * from de;

同义词可以用来做只读用户，同义词可以用来做只读用户提高安全性

Oracle数据库中提供了同义词管理的功能。

同义词是数据库方案对象的一个别名，经常用于简化对象访问和提高对象访问的安全性。

在使用同义词时，Oracle数据库将它翻译成对应方案对象的名字。与视图类似，同义词并不占用实际存储空间，

只在数据字典中保存了同义词的定义。在Oracle数据库中的大部分数据库对象，如表、视图、同义词、序列、存储过程、包等等，数据库管理员都可以根据实际情况为他们定义同义词

数据字典

数据库字典：是数据库自己维护一组表，它存放了用户创建的所有对象的信息。

数据库字典视图：就是数据库字典视图

dba_:管理员数据库字典视图

user_:用户的数据库字典视图

all_:所有用户都可见的数据库字典视图

select * from user_tables;

select * from user_tab_cols;

select object_name from user_objects where object_type='TABLE';

select object_name from user_objects where object_type='INDEX';

select * from dba_data_files;

数据字典对比查询表：

https://blog.51cto.com/u_16065168/6253456

数据导入导出

数据的迁移和备份，导入导出，虚拟机cmd进行操作

exp 账号/密码@数据库名 file=文件存放路径 full=y --全库导出，命令行语句，不需要登

录数据，只需要在命令行运行

--按用户导出：

exp scott/oracle@orcl file=c:\test\exp_0512_scott.bak OWNER=scott

select * from emp;

drop table emp;

--导入

imp scott/oracle@orcl file=c:\test\exp_0711_scott.bak ignore=y

select * from emp;

with..as 子查询

with..as关键字，是以‘with’关键字开头的sql语句，在实际工作中，我们经常会遇到同一个查询sql会同时查询多个相同的结果集，即sql一模一样，这时候我们可以将这些相同的sql抽取出来，使用with..as定义。with..as相当于一张中间表，可以简单理解为sql片段。

--with as 可以理解为一张临时表或者理解成sql片段，在多次查询语句相同的时候可以抽取出来，达到'一次解析，多次使用'

--如果每个部分都去执行一遍的话，则成本比较高，可以使用with as短语，则只要执行一遍即可

with temp as (select avg(sal) a,max(deptno) m from emp)

select * from emp

where sal<(select a from temp) and deptno=(select m from temp) 

--with as 非常适合在union 语句中

--注意：with as 语句最后面不能加分号，否则报缺失select关键字错误。

with temp1 as

(select 'female' sex, 'zhangsan' stu_name from dual),

temp2 as

(select 'male' sex, 'lisi' stu_name from dual),

temp3 as

(select 'female' sex, 'wangwu' stu_name from dual)

select *

from temp1

union all

select *

from temp2

union all

select * from temp3

--前面定义的with..as语句可以在后面定义的with..as语句使用

with temp1 as

(select 'female' sex, 'zhangsan' stu_name from dual),

temp2 as

(select 'male' sex, 'lisi' stu_name from dual),

temp3 as

(select * from temp2)

select *

from temp1

union all

select *

from temp2

union all

select * from temp3

作业（超市管理系统）

准备工作：创建表空间和临时表空间，用户绑定，权限，根据超市管理系统需求excel提供内容建表约束及插入测试数据

建表准备在上篇。

--1. 在货物库存表的goodsid建立索引

create index wzid on stock(goodsid);

--2. 在货物类型表的货物名称列添加唯一约束

alter table goods modify g_name unique;

--3. 将货物类型表的各种物品，在库存表插入一条记录，要求每个字段都填写完整，保证数量都在100以上。

create sequence ssid

start with 1

increment by 1 ;

insert into stock(s_id,goodsid)

select ssid.nextval,goods.g_id from goods;

update stock set sl=101,productime=sysdate;

--4. 查询出货物表中过期的所有物品

select g_id,g_name from goods

left join stock on g_id=goodsid

where productime+qualitydate<sysdate;

--5. 查询货物表中10天之内过期的所有物品

select g_id,g_name from goods

left join stock on g_id=goodsid

where (productime+qualitydate) between sysdate and sysdate+10;

--6. 于当前时间新进货脉动50瓶，可口可乐50瓶，在出账表和库存表插入相应记录

update stock set sl=sl+50 

where goodsid in

(

select g_id from goods

where g_name in('脉动','可口可乐')

);



create sequence onid

start with 1

increment by 1 ;



insert into oncome(o_id,goodsid,stockid,sl,allprice)

select onid.nextval,goodsid,s_id,50,inprice*50

from stock left join goods on goodsid=g_id

where g_name in('脉动','可口可乐');



--7. 于当前时间卖出泰山一包，在进账表插入相应记录，在库存表修改相应记录

update stock set sl=sl-1

where goodsid in

(

select g_id from goods

where g_name='泰山'

);



create sequence inid

start with 1

increment by 1 ;



insert into income(i_id,goodsid,stockid,sl,discount,allprice)

select inid.nextval,goodsid,s_id,1,1,outprice*1

from stock left join goods on goodsid=g_id

where g_name='泰山';



--8. 查询库存内所有物品的总价，进货价计算

select sum(sl*inprice)

from stock left join goods on goodsid=g_id;



--9. 查询酒水类物品的仓库保有量最大的物品id和总价

select goodsid,sl*inprice from stock

left join goods on goodsid=g_id

where g_type='g02' and sl in

(

select max(sl) from stock

where goodsid in

(

select g_id from goods

where g_type='g02'

)

);

--10. 查询价格大于5元低于10元的所有物品名称

select g_name from goods

where inprice>5 and inprice<10 ;

--11. 查询名称中有山字的所有物品

select g_name from goods

where g_name like '%山%';

--12. 查询进货日期是今天的所有进货记录

select * from oncome

where to_char(intime,'yyyymmdd')=to_char(sysdate,'yyyymmdd');

--13. 查询名称长度最大的物品名称

select g_name from goods

where length(g_name) in

(

select max(length(g_name)) from goods

);

--14. 汇总各类型物品的库存，其中烟和酒水单独统计，其他物品展示为其他

select sum(case when g_type='g01' then sum(sl) end) as 烟,

sum(case when g_type='g02' then sum(sl) end) as 酒水,

  sum(case when g_type not in('g01','g02') then sum(sl) end) as 其他

from stock left join goods on goodsid=g_id group by g_type;

--15. 更新可比克薯片的名称，将名称改为可比克薯片（袋装）

update goods set g_name='可比克薯片（袋装）'

where g_name='可比克薯片';

--16. 将各类物品根据价格倒序排列展示

select * from goods order by outprice asc;

--17. 查询出今天的销售额，毛利润

select sum(allprice) as 销售额,sum(allprice)-sum(sl*inprice) as 毛利润

from income left join goods on goodsid=g_id

where to_char(outtime,'yyyymmdd')=to_char(sysdate,'yyyymmdd');



/*18. 根据每种物品类型（类型包括很多物品）昨天销售的累计数量，求出最大的数量的

类型名称及总数量*/



select t_value,sum(sl)

from income left join goods on goodsid=g_id

left join t_dic on g_type=t_key

where to_char(outtime,'yyyymmdd')=to_char(sysdate-1,'yyyymmdd')

group by t_value having sum(sl) in

(

select max(sum(sl)) 

from income left join goods on goodsid=g_id

group by g_type

);



--19. 查询出烟类，近10天的销售情况，展示出物品名称，卖出数量，总价格，毛利润

select g_name as 物品名称,sum(sl) as 卖出数量,sum(allprice) as 总价格,sum(allprice)-sum(sl*inprice) as 毛利润

from income left join goods on goodsid=g_id

where g_type='g01' and outtime between sysdate-10 and sysdate 

group by g_name;

--20. 根据天数分组，汇总近10天每天的销售情况，毛利润和总价格

select to_date(to_char(outtime,'yyyymmdd'),'yyyymmdd'),sum(allprice) as 总价格,sum(allprice-sl*inprice) as 毛利润

from income left join goods on goodsid=g_id

where outtime between sysdate-10 and sysdate

group by to_date(to_char(outtime,'yyyymmdd'),'yyyymmdd');

/*21. 创建视图，展示当月销售物品中，分类统计各类物品的销售数量，总进价，总卖出

价，毛利润*/

create or replace view IncomeGoods

as

select g_type,sum(sl) as 销售数量,sum(sl*inprice) as 总进价,sum(allprice) as 总卖出价,sum(allprice)-sum(sl*inprice) as 毛利润

from income left join goods on goodsid=g_id

where to_char(outtime,'mm')=to_char(sysdate,'mm')

group by g_type;



select * from IncomeGoods;

--22. 在物品表里查出利润率最大的物品

select *

from goods

where (outprice-inprice)/inprice in

(

select max((outprice-inprice)/inprice)

from goods

);

--23. 因为某人讲价，于当前时间，以8折卖出馒头50个，在进账表和库存表做出相应修改

update stock set sl=sl-50

where goodsid in

(

select g_id from goods

where g_name='馒头'

);



insert into income(i_id,goodsid,stockid,sl,discount,allprice)

select inid.nextval,goodsid,s_id,50,0.8,50*outprice*0.8

from stock left join goods on goodsid=g_id

where g_name='馒头';

原力大数据某测试题

完成作业后自习时间闲暇之余看的题目，老师前面发的，不是必做。

1. SQL查询语句的大体框架

Select......from......where......group by......having......order by......

1. select 和 group by 的执行顺序

Group by 先执行，select后执行。

1. 哪个函数可以返回排名

Row_number(),rank(),dense_rank().

1. 一个叫team的表，里面只有一个字段name, 一共有4条记录，分别是a,b,c,d对应四个球队，现在四个球队进行比赛，用一条sq|语句显示所有的可能比赛组合。

Select t1.name,t2.name

from team t1 join team t2 on t1.name>t2.name;

Ps:此题不会，看的答案。

5、表table，有字段id、sex、company、age。

1. 统计A公司的男女人数

Select count(sex)

from table

Where company=’A’

group by sex;

1. 统计各公司的男性平均年龄，并且仅保留平均年龄在30岁以上的人

Select avg(age)

from table

where sex=’男’ 

group by company

Having avg(age)>30;

6、数据库SCHOOL里面有两张数据表，分别是student表和score_a1表，表的内容如下：

1. student

ID	NAME	CLASS	AGE
123	张小明	A1	17
164	黄丽丽	A1	18
187	陈小	A1	18
127	李三	A2	17
201	许新	A2	19

1. score_a1

COURSE	ID	SCORE
数学	123	51
英语	123	92
语文	123	80
数学	164	91
英语	164	58
语文	164	93
数学	187	61
语文	187	62

请根据数据表的内容写出正确的SQL语句

1. 求A1班和A2班学生的平均年龄

Select avg(age) from student

Group by class;

2）写出查询结果如下的sql语句

id	name	course	score
164	黄丽丽	数学	91
164	黄丽丽	英语	58
164	黄丽丽	语文	93

Select id,name,course,score from score_a1

Left join student on score_a1.id=student.id

Where id=164;