delete emp_tian where empno = 1234; delete emp_tian where deptno =10; delete emp_tian; rollback;
set salary = salary +10000 where deptno = 10;
select ename,salary from emp_tian where deptno = 10; rollback;
select ename,salary from emp_tian where deptno = 10;
----------------------------------------制造大表------------------------------------------------- ------------------------------------循环插入--自我复制--------------------------------------- insert emp_bak_tian (select*from emp_tian);
insert emp_bak_tian (select*from emp_bak_tian);
--------------------删除重复的数据 仅试用重复记录较多的表----------------------- create table emp_tian2 as(select distinct empno,ename,salary from emp_tian); select*from emp_tian2;
------------------------------------------重命名----------------------------------------------------- drop table emp_tian1;
rename emp_tian2 to emp_tian1;
---------------------删除重复的数据 重复记录较少的表 ----------------------- create table emp_tian3 as ( select*from emp_tian ); insert into emp_tian3(ename,salary) values('傻逼',2000); select*from emp_tian3;
---- 1 --------查询重复数据中的地址最大的记录----------------
select max(rowid) from emp_tian3 group by empno,ename,salary ---- 2 -------删除地址不等于最大地址值的重复记录---------------- select*from emp_tian3;
select empno,ename,salary, rowid from emp_tian3; delete emp_tian3 where rowid not in (
select max(rowid) from emp_tian3 group by empno,ename,salary );
select*from emp_tian3;
commit/rollback ----------和事务(Transaction)相关的语句 ---事务开始
DML:insert / update /delete --加锁 ---事务结束
commit/rollback --解锁
-------------------如果操作已经加锁的信息，会刮起，直到得到相应的锁----------------------------- ---------------------------开始事务---------------------------- update account
set money = menry - 500 ; where id = 'A';
update money = money + 500 where id = 'A';
if(都成功) commit; else
rollback;
-------------------------事务结束----------------------------- 正常退出会话： 自动commit 异常退出会话： 自动rollback DDL操作：提交之前的操作
set antocommit on ----设置自动提交(所有DML操作都会触发) savepoint a ---设置书签(标记)
create table fa (id number); -----事务的起点，不能在rollback中回滚 insert into fa values(1); insert into fa values(2); insert into fa values(3); insert into fa values(4); insert into fa values(5); insert into fa values(6); select *from fa;
rollback ; --表还在数据没了 select *from fa;
create table fa (id number); -----事务的起点，不能在rollback中回滚 insert into fa values(1); insert into fa values(2); savepoint a; --书签 insert into fa values(3); insert into fa values(4); savepoint b; --书签 insert into fa values(5); insert into fa values(6); savepoint c; --书签 insert into fa values(7); select *from fa;
rollback to a;--表中还剩下a之前的数据 select *from fa;
--假如执行 rollback to b,c点就不存在了，不能在rollback to c , a点仍然存在，可以继续
rollback to a
连接数据库的工具: --首选,命令行工具 sqlplus --图形工具
pl/sql developer(非官方,免费) toad(非官方,收费)
sql developer(官方,免费,11g以上)
补充练习: procedure 过程
PL/SQL: Procedure Language / SQL
函数(function) 过程(procedure) 包(package) 触发器(trigger)
---------------------------输入班号,输出最高分的学生名字和总成绩-------------------------------------- create or replace procedure cal_tian(
p_class_id in number, p_student_name out char, p_total_score out number ) is begin
select student_name, total_score into p_student_name, p_total_score from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = p_class_id group by stu.student_name order by total_score desc) where rownum < 2; end; /
--------------------------打开输出,默认值是off SQL>set serveroutput on
---------------------匿名块,用来测试过程或函数 SQL>declare
p_student_name char(20); p_total_score number;
begin
cal_tian(&no, p_student_name, p_total_score); --向控制台输出变量值,System.out.print dbms_output.put_line(p_student_name); dbms_output.put_line(p_total_score); end; /
--可以用&abc符号表示在运行时输入变量值 select * from emp_tian where deptno = &abc;
----------------改进版,输入共多少个班,输出每个班的最高成绩的学生姓名和总分------------------ ------------假设是班号是1-p_class_num create or replace procedure cal_tian1( p_class_num in number) is
p_student_name char(20); p_total_score number; begin
for i in 1..p_class_num loop
select student_name, total_score into p_student_name, p_total_score from (
select stu.student_name,
sum(per.test_score) total_score
from t_student_tian stu join t_performance_tian per on stu.student_id = per.student_id where stu.class_id = i
group by stu.student_name order by total_score desc) where rownum < 2; dbms_output.put_line
(p_student_name || ', ' || p_total_score); end loop; end; /
SQL>exec cal_tian1(2); --2个班
------------------------------DDL操作 数据定义语言----------------------------------------------------- ----------------------------DDL:create /drop /alter / truncate--------------------------------------- alter :修改结构 drop :删除结构+数据(如果有) update:修改数据 delete:删除数据
==================================================================== 一.??????约束条件-----(数据表中数据必须遵循的规
则)????????????????????
1】关键(必要)的数据为空 学号，姓名，性别为空 2】用于个体区分的唯一数据重复 学号重复
3】数据不正常 年龄1000，只有3个班，班级写25，性别不为男女之一
--------------------------------------------------五大约束条件---------------------------------------------- primary key 主键 pk foreign key 外键 fk not null 非空 un unique 唯一 uk check 检查 ck
-------------------------------------------建表时，建立主键约束条件---------------------------------------- -------------------------------------------id列受限--------------------------------------------- create table student_tian( id number(4) primary key, name char(10) );
insert into student_tian(id,name) values(1001,'tian');
----报错 ORA-00001 unique constraint violated 唯一的约束被违反
------------------------------------constraint 约束------------------------------------------------- insert into student_tian(id,name) values(1001,'yang');
----报错 ORA-01400 cannot insert null into (\--------------------------------------------------------------登录用户-----------报名----------列号
insert into student_tian(name) values('asdas');
某些数据库：主键自增长(降低程序员负担) mysql / sql server Oracle数据库: 序列(主键发生器) 向外产生不重复的数字
-----------------------------------------name列 非空约束------------------------------------------ drop table student_tian; create table student_tian( id number(4) primary key, name char(10) not null );
insert into student_tian(id,name) values(1001,'tian');
---报错 ORA-01400 cannot insert null into (\------------------------------------------------------------登录用户-------------报名------------列号 insert into student_tian(id)