一、常用sql语句
聚合函数COUNT（*）计算元组个数COUNT（列名）对一列中的值计算个数SUM（列名）求某一列值的总合AVG（列名）求某一列值的平均值MAX（列名）MIN（列名）--按照本门号从高到底，工资从高到底查询员工表select * from personnel order by section desc,sal desc;--求各个部门工资的平均值select s.name, avg(p.sal) from personnel p inner join section s on p.section = s.id group by s.name --求各个部门工资的和select s.name, sum(p.sal) from personnel p inner join section s on p.section = s.id group by s.name--求工资大于财务部平均工资的员工的信息select * from personnel where sal > (select avg(sal) from personnel where section = (select id from section where name = '财务部')) --查询寻所有部门4的工作岗位，在根据工作岗位查询所有在这些工作呢岗位工作呢的员工 select p.name,p.sal,j.name, s.name from personnel p inner join job j on p.job = j.id inner join section s on p.section = s.id where p.job in (select job from personnel where section = 4 group by job) --各部门工资大于部门平均工资的员工信息select * from personnel p where sal > (select avg(sal) from personnel where p.section = section);--各部门工资大于部门平均工资的员工数量和部门编号select count(*),p.section from personnel p where sal > (select avg(sal) from personnel where p.section = section) group by p.section; --排序 select * from personnel order by id desc --插入 insert into section (id,name) values(5,'市场部'); --删 delete section where id = 5; --改 update section set name = '尼玛' where id = 5; --分页select * from (select e1.*, rownum r from (select * from employee) e1 where rownum < 5) where r > 2--别名select s.name as "部门",avg(sal) as "平均工资" from personnel p inner join section s on p.section = s.id group by s.name--建表create table text( id number(6) primary key, name varchar2(20) not null, age number(6) not null, manager number(6))tablespace users;--插入数据insert into text (id,name,age,manager) values (106,'A',30,104);insert into text (id,name,age,manager) values (109,'B',19,104);insert into text (id,name,age,manager) values (104,'A',20,111);insert into text (id,name,age,manager) values (107,'D',35,109);insert into text (id,name,age,manager) values (112,'E',25,120);insert into text (id,name,age,manager) values (119,'F',45,null);//选修了java课程的学生的id和姓名select s.studentid, s.name from student s inner join sc x on s.studentid = x.studentid where x.courseid = (select courseid from course where coursename = 'Java');--查看熊敏选修了那几门课程select c.coursename from course c inner join sc x on c.courseid = x.courseid where x.studentid = (select studentid from student where name = '熊敏');--打印选课数大于3的学生的id和姓名select stu.studentid, stu.name from student stu where (select count(*) from sc where studentid = stu.studentid) > 3;--:列出所有年龄比所属主管年龄大的人的ID 和名字?select em.id,em.name from text em where em.age > (select age from text where em.manager = id);--删除相同数据delete from text t where t.id > (select min(x.id) from text x where t.name = x.name);--用exists代替inselect * from employee e where e.section in (select id from section) order by id;select * from employee e where exists (select id from section s where e.section = s.id) order by id;--exists与in比较select * from 表A where exists(select * from 表B where 表B.id=表A.id) --这句相当于 select * from 表A where id in (select id from 表B)
?
二、游标、触发器、存储过程、函数、包......
打开输出选项 set serveroutput on;关闭输出选项 set serveroutput off;一、游标的应用实例1：declare id number;--申明变量接受游标里查出来的值 department varchar2(20); --申明一个游标:aaaa cursor aaaa is select studentid,name from student where name = '熊敏';begin --打开游标 open aaaa; --判断是否返回记录 if aaaa%notfound then dbms_output.put_line('没有找到相应的记录'); else --从游标中读取数据 fetch aaaa into id,department; dbms_output.put_line(id || ',' || department); end if; close aaaa;end;实例2：--游标循环读取数据declare --申明 id number; --变量 name varchar2(20); cursor user_table is select studentid,name from student; --申明游标：user_tablebegin --开始 for user_tables in user_table loop --循环读取，user_tables为临时变量 id := user_tables.studentid; name := user_tables.name; dbms_output.put_line('学员编号：' || id || '学员姓名：' || name); end loop;end; --结束二、创建和调用过程 --无参过程pro_01 create or replace procedure pro_01 isbegin insert into job (id,name) values (4,'总经理'); end; --调用过程（在命令行中调） exec pro_01;--有参过程pro_02create or replace procedure pro_02 ----申明五个个输入参数，----输入字符串不让加长度 ( student_id in number, student_name in varchar2, student_sex in varchar2, student_age in number, student_department in varchar2 ) as begin insert into student values(student_id,student_name,student_sex,student_age,student_department); end;--调用exec pro_02(7,'林文伟','男',23,'学习部');三、函数--创建一个返回插入条数信息的函数--申明5个参数的函数--函数名function_insert_student--返回值run_numbercreate or replace function function_insert_student( student in number, stu_name in varchar2, sex in varchar2, age in number, department in varchar2)return number as ret_number number :=0;begin insert into student values(student,stu_name,sex,age,department); ret_number := ret_number + 1; commit; return ret_number; end;--调用函数 declare --申明一个变量接受函数返回值 receive_number number; begin receive_number :=function_insert_student(9,'王正国','男',23,'学习部'); dbms_output.put_line('一共插入--' || receive_number || '条数据。');end;四、包--创建包头 create or replace package pack_age as --申明过程 procedure my_procedure(studentid number,stu_name varchar2,sex varchar2,age number,department varchar2); --申明函数 function my_function(studentid number,stu_name varchar2,sex varchar2,age number,department varchar2) return number; end pack_age; --创建包体 create or replace package body pack_age as --实现过程 procedure my_procedure(studentid number,stu_name varchar2,sex varchar2,age number,department varchar2) is begin insert into student values (studentid,stu_name,sex,age,department); commit; end my_procedure; --函数的实现 function my_function(studentid in number,stu_name in varchar2,sex in varchar2,age in number,department in varchar2) return number as return_number number := 0; begin insert into student values (studentid,stu_name,sex,age,department); return_number := return_number + 1; commit; return return_number; end my_function; end pack_age; --包的调用 declare --申明变量接收函数的返回值 num_number number; begin pack_age.my_procedure(10,'黄广','男',23,'学习部'); num_number := pack_age.my_function(11,'黄云生','男',23,'学习部'); end;五、视图--创建视图 create or replace view my_view as select * from student; --调用视图 select * from my_view;六、触发器1、测试表结构 create table a( id number(6), s_name varchar2(30))create table b( id number(6),s_name varchar2(30))2、--创建出发器-- bck_trg--单向表a插入数据的时候同时向表b里插入数据--insert into b values (1,'熊敏') create or replace trigger bck_trg after insert on a for each rowbegin insert into b values (1,'熊敏');end;--测试insert into a values(1,'熊敏');--复制表数据的写法create or replace trigger tig_test after insert on a declarebegin insert into b (select * from a where a.id=(select max(a.id) from a ));end tig_test;--将插入的信息插入到目标表create or replace trigger tig_a after insert on a for each rowdeclare -- 在这里申明变量begin insert into b(id,s_name) values(:NEW.ID,:NEW.S_NAME);end tig_a;--将A表删除的数据插入到B表create or replace trigger tig_a after delete on a for each rowdeclare -- local variables herebegin insert into b(id,s_name) values(:old.ID,:old.S_NAME);end tig_a;?