/*
*数据库对象
*/
--表操作
--创建表
create table department_24(department_id number(8),department_name varchar(20),
manager_id number(8),local_address varchar(20));
--使用子查询创建表
create table department_24_temp(department_id,department_name) as select department_id,department_name from department_24;
create table department_24_temp as select department_id,department_name from department_24;
create table department_24_temp as select * from department_24;
--创建薪水等级表
create salary_level(grade_level varchar(8),lowest_sal number(8,2),highest_sal number(8,2));
--修改表
--增加表的字段
alter table department_24_temp add(manager_id number(8),local_address number(8));
--修改表的字段
alter table department_24_temp modify(local_address varchar(20));
--删除表的字段
alter table department_24_temp drop(local_address);
--重命名表
rename department_24_temp to renam;
-
-删除表
drop table renam;
--系统表
--察看用户能够使用的数据库对象
select object_type from user_objects;
--产看system用户所能使用的表
select table_name from user_tables;
--约束Constraints
--not null/unique/primary key
create table department_24(department_id number(8) constraint 部门编号不能为空 not null,department_name varchar(20),
manager_id number(8),local_address varchar(20),
constraint dept_24_unique unique(department_id),
constraint dept_24_pri_key primary key(department_id));
--外键约束
create table employee_24(employee_id number(8),employee_name varchar(20),hire_date date,job_name varchar(20),
salary number(8,2),commisson_pct number(2,2),department_id number(8),manager_id number(8),
constraint emp_dept_24 foreign key(department_id) references department_24(department_id),
constraint emp_sal_24 check(salary >2000));
--增加约束
alter table employee_24 add constraint emp_24_pri primary key(employee_id);
alter table employee_24 modify(employee_name not null);
--启用或者禁用约束
--alter emp_sql_24 disable;
--删除约束
alter table employee_24 drop constraint emp_sal_24;
alter table department_24 drop primary key cascade;
--索引
--创建索引
create index emp_salary_24_idx
on employee_24(salary);
--删除索引
drop index emp_salary_24_idx ;
--同义词
-
-创建同义词
create public synonym e_24 for employee_24;
--删除同义词
drop public synonym e_24;
--序列
create sequence hospital_people_total
minvalue -1
maxvalue 999999999999999999999999999
start with 0
increment by 1;
--取得序列的下一个值
select hospital_val from dual;
--取得序列的当前值
select hospital_people_total.currval from dual;
--视图
--创建一个数据来源于一个基表的视图
create
or replace view emp_1(employee_id, employee_name, salary)
as select employee_id, employee_name, salary from employee where department_id = 1;
--创建一个数据来源于一个基表的视图并限制对视图的dml操作
create or replace view emp_3(department_name,manager_id, employee_id, employee_name, salary)
as select d.department_name, d.manager_ployee_id, e.employee_name, e.salary
from employee e , department d where d.department_id = 2
--限制对视图的dml操作
with read only;
create or replace view emp_dept_1(minsalary,maxsalary,avgsalary)
as
select min(salary) a,max(salary) b,avg(salary) c
from employee e, department d
where d.department_id = 1 and d.department_id = e.department_id
with read only;
TOP-N分析法
select employee_name,salary,job_name, rownum
from (select employee_name,salary,job_name from employee order by salary )--行内视图
where rownum<=3;
/*
*数据操作与事务控制
*/
--数据操作
--insert
insert into department_24(department_id, department_name) values(3,'工程部');
insert into department_24 values(4,'网络部',null,null);
--使用一条sql语句添加多条记录
insert into department_24 values(&department_id,&department_name,&manager_id,&local_address);
--update
update employee_24 set salary = 5555.55, commission_pct = .33;
--delete 暂时删除数据
delete department_24 where department_id > 3
--truncate 永久删除数据(DDL)
truncate table employee_24;
--merge
merge into department_24_copy copy
using department_24 src
on(src.department_id = copy.department_id) --比较字段
when matched then
update set department_name = src.department_name,
manager_id = src.manager_id,
local_address = src.local_address
when not matched then
insert values(src.department_id,src.department_name,src.manager_id,src.local_address);
--事务控制
--设置自动提交
set autocommit on;
--savepoint
delete department_24 where department_id>3;
savepoint undo1;
delete department_24_angel;
savepoint undo2;
rollback to undo1;
commit;
--创建用户
create user chenlong identified by draglong default tablespace users temporary tablespace temp;
--赋予权限
grant connect,resource,dba to test;
commit;
/*
* select
*/
--带算数表达式的select
select employee_name,salary,salary *12 from emmployee;
--带连接表达式的select(特殊字符用单引号包含)
select employee_name ||'的职位为:= ' || job_name from employee;
--空值运算
select salary,commisson_pct,employee_name||'的年终奖金为:='||salary * (1+commisson_Pct) from employee;
--字段别名
select salary 薪水,commisson_pct 奖金百分比,employee_name||'的年终奖金为:='||salary * (1+commisson_Pct) 年终奖 from employee;
--带比较条件的select
select employee_name, job_name, salary from employee where salary > 5500;
--between ..and
select employee_name, salary from emp
loyee where salary between 4000 and 6000;
--in
select employee_name, salary from employee where salary in (4444, 6000);
--like
select employee_name, salary from employee where job_name like '%架构师';
select employee_name,job_name, salary from employee where job_name like '软件%';
select employee_name,job_name, salary from employee where job_name like '软件___';
--and/or/not
select employee_name, job_name, salary from employee where commisson_pct is  null and job_name <>'战斗大师';
--order by
select employee_name,salary,hire_date from employee order by salary,hire_date desc,employee_id;
/*
*SQL函数
*/
--字符函数
select employee_name,concat(employee_name,concat('的职位: ',job_name)) a, length(employee_name) b
from employee where lower(employee_name)= 'xusanduo';
select employee_name,concat(employee_name,concat('的职位: ',job_name)) a, length(employee_na
me) b
from employee where employee_name= UPPER('xusanduo');
--日期函数
--取得系统当前时间
select sysdate from dual;
alter session set nls_language='simplified chinese';
alter session set nls_language='american';
--TO_CHAR()：将日期转换为字符(一般用于将数据库中日期字段值检索到界面时)
--日期
select employee_name ||'的入职时间为:='||to_char(hire_date,'YYYY"年"MM"月"DD"日"') from employee;
select concat(employee_name,concat('的入职时间为:=',to_char(hire_date,'YYYY"年"MM"月"DD"日"'))) from employee;
select employee_name ||'的入职时间为:='||to_char(hire_date,'YYYY"年"MM"月"DD"日"') from employee;
--时间
select employee_name ||'的入职时间为:='||to_char(hire_date,'YYYYTH"年"MMTH"月"DD"日"AMHH24:MI:SS') from employee;
--TO_CHAR()：将数字转换为字符
select employee_name,to_char(salary,'00,999.99L') from employee;
--to_number()：将字符转换为数字
select to_char('123456','99,999,999.99') from dual;
--to_date()：将字符转换为日期
insert into employee values(122,'test',to_date('2008年03月28日','YYYY"年"MM"月"DD"日"'),null,null,null,null,null);
sql查询语句实例大全--为空处理函数
--nvl
select employee_name,salary,commisson_pct,salary*(1+nvl(commisson_pct,0)) from employee;
--nvl2：如果条件非空，则取得第一个值，否则取得第二个值
select employee_name||'是'||nvl2(commisson_pct,'被提拔人员','将下岗人员') from employee;
--nullif
select employee_name,employee_id,manager_id, nullif(employee_id,manager_id) from employee;
--coalesce：取得表达式列表中第一个非空值
select employee_name,salary,commisson_pct, coalesce(salary,commisson_pct,12) from employee;
--case表达式
select employee_name, job_name,salary,case job_name
when '软件工程师' then salary*0.2
when '软件架构师' then salary*0.1
else 100
end "薪水提升比例" from employee;
--decode函数
select employee_name,salary,job_name,
decode(job_name,'软件工程师',salary*0.2,
'软件架构师',salary*0.1,
100)
"薪水提升比例" from employee;
-
-分组函数
--常用组函数
select max(salary),min(salary),sum(salary),avg(salary),count(salary),count(*) from employee;
--计算所有有奖金的员工的平均奖金
select avg(commisson_pct) from employee;
--计算所有员工的平均奖金
select avg(nvl(commisson_pct,0)) from employee;
--group by：对数据进行分组
select department_id,round(avg(salary),2) from employee group by department_id;
select department_id,job_name,max(salary),round(avg(salary),2) from employee group by department_id,job_name order by max(salary);
--having ：对分组后的数据进行过滤
select department_id,job_name,max(salary),round(avg(salary),2)
from employee  group by department_id,job_name  having max(salary) > 5000 order by max(salary);
--完整的select语句
select job_name, sum(salary) from employee
where job_name not like '%工程师'
group by job_name
having sum(salary) >4000
order by sum(salary);
/*
* 多表连接与子查询
*/
-
-多表连接
--等值连接
select d.department_ployee_name,e.job_name, e.salary from department d, employee e
where d.department_id = e.department_id and d.department_name = '开发部';
--非等值连接
ployee_name,e.ade_level
from employee e, salary_level s
where e.salary between s.lowest_sal and highest_sal;
--外连接
--左外连接：查所有部门的员工信息以及没有员工的部门信息
select d.department_ployee_name,e.salary
from employee e, department d
where e.department_id(+) = d.department_id;
--右外连接
--查所有员工的部门信息以及不属于任何部门的员工信息
select d.department_ployee_name,e.salary
from employee e, department d
where e.department_id= d.department_id(+);
--自连接：查每个员工与管理者之间的关系
ployee_name ||'的管理者的编号是:'||ployee_id||'；姓名为:'||ployee_name from employee worker, employee manager
where worker.manager_id = ployee_id;
--sql99新的连接
--cross join
select d.department_name, e.employee_name, e.salary from employee e cross join department d;
--natural join：基于两个表中所有同名字段的等值连接
select d.department_name, e.employee_name, e.salary from employee e natural join department d;
--join..using()：使用特定的同名字段作为等值连接条件
select d.department_name, e.employee_name, e.salary from employee e join department d using(department_id);
--join .. on()：基于同名或者不同名的字段的等值连接
select d.department_name, e.employee_name, e.salary from employee e join department d on(d.department_id = e.employee_id);
--left outer join
select d.department_ployee_name,e.salary
from  department d left outer join employee e
on e.department_id = d.department_id;
--right outer join
select d.department_ployee_name,e.salary
from  em
ployee e left outer join department d
on e.department_id = d.department_id;
--full outer join
select d.department_ployee_name,e.salary
from  employee e full outer join department d
on e.department_id = d.department_id;
-
-子查询
--单行子查询
select employee_name, salary, job_name from employee
where salary >(select salary from employee where employee_name = '刘斌');
--出现在having子句中的子查询：查出所有部门中最低薪水比一号部门的最低薪水要高的部门以及最低薪水信息
select department_id, min(salary) from employee group by department_id
having min(salary) > (select min(salary) from employee where department_id = 1);
--查出所有其他职位中小于所有软件工程师的最高薪水的员工信息
--any (>  <)
select employee_id, employee_name, job_name, salary from employee
where salary < any (select salary from employee where job_name = '软件工程师') and
job_name <> '软件工程师';
--all (> <)
--查小于所有软件工程师职位的最低薪水的其他职位的员工信息
select employee_id, employee_name, job_name, salary from employee
where salary < all (select salary from employee where job_name = '软件工程师') and
job_name <> '软件工程师';
/**
*
*pl/sql
*/
/**
*变量
*/
--set serveroutput on;--设置在屏幕上显示打印输出内容
--简单变量
declare
e_id binary_integer := 10;
e_sal number(8,2) := 888.88;
e_hire date  := sysdate -8;
e_valid boolean := true;
begin
dbms_output.put_line('员工的编号为:=' ||e_id);
dbms_output.put_line('员工的薪酬为:=' ||e_sal);
dbms_output.put_line('员工的入职时间为:=' ||e_hire);
--dbms_output.put_line('员工是否合格:= '||e_valid);
end;
/
--table类型变量
declare
type table_type is table of varchar(20) index by binary_integer;
t_name table_type;
begin
t_name(1):= 'chenlong';
t_name(2):= 'dragonlong';
dbms_output.put_line(t_name(1));
dbms_output.put_line(t_name(2));
end;
/
--record类型变量
declare
type record_type is record(
e_id number(8),
e_name varchar(20),
e_sal number(8,2));
emp_record record_type;
begin
emp_record.e_id := 12;
emp_record.e_name := '陈厚宇';
emp_record.e_sal:= 8888.88;
dbms_output.put_line(emp_record.e_id);
dbms_output.put_line(emp_record.e_name);
dbms_output.put_line(emp_record.e_sal);
end;
/
--%type属性声明变量
declare
e_ployee_name%type;
e_remark  ployee_remarks%type;
e_sal number(8,2);
e_max_sal e_sal%type;
begin
e_name := '陈龙';
e_remark := '备注';
e_sal := 888.88;
e_max_sal := 444.44;
dbms_output.put_line(e_name);
dbms_output.put_line(e_remark);
dbms_output.put_line(e_sal);
dbms_output.put_line(e_max_sal);
end;
/
--%rowtype属性定义变量
declare
emp_record employee%rowtype;
begin
plo
yee_id := '12';
ployee_name := '任露晶';
ployee_jp := 'japane';
ployee_phone := '8883232';
ployee_remarks := '备注';
dbms_output.put_line(ployee_id);
dbms_output.put_line(ployee_name);
dbms_output.put_line(ployee_jp);
dbms_output.put_line(ployee_phone);
dbms_output.put_line(ployee_remarks);
end;
/
-
-pl/sql中的select语句
declare
emp_record employee%rowtype;
begin
select employee_id,employee_name into ployee_id,ployee_name from employee;
dbms_output.put_line(ployee_id);
dbms_output.put_line(ployee_name);
--dbms_output.put_line(ployee_jp);
--dbms_output.put_line(ployee_phone);
--dbms_output.put_line(ployee_remarks);
end;
/
--pl/sql中的insert、delete、update
begin
insert into employee values('4','chenhouyu','chy','1','0721-1212112','beizhu');
dbms_output.put_line('数据插入成功');
update employee set employee_name = 'banana' where employee_id = '3';
dbms_output.put_line('数据修改成功');
delete from employee where duty_id = '01';
dbms_output.put_line('数据删除成功');
commit;
end;
/
declare
e_ployee_name%type;
begin
select employee_name into e_name from employee where employee_name='best';
if e_name = 'best' then
update employee set job_name = '系统架构师',department_id = 3 where employee_name = 'best';
end if;
end;
/
declare
-- e_ploye_id%type;
e_date employee.hire_date%type;
s_date e_date%type;
begin
select hire_date,sysdate into e_date, s_date from employee where employe_id =  4;
if s_date - e_date > 260 and s_date - e_date < 400 then
dbms_output.put_line('该员工属于老员工');
elsif s_date -  e_date > 400 then
dbms_output.put_line('该员工属于创业员工');
else
dbms_output.put_line('该员工属于普通员工');
end if;
end;
/
--loop循环
declare
v_id circle.id%type := 1;
v_pe%type := 'A';
begin
loop
insert into circle values(v_id,v_type);
v_id := v_id +1;
v_type:= v_type || v_id;
commit;
exit when v_id >5;
end loop;
end;
/
--for循环
declare
p_circle circle%rowtype;
v_count number;
begin
select count(*) into v_count from circle;
for c_count in 1.. v_count loop
if c_count > 3 then
exit;
end if;
select * into p_circle from circle where id= c_count;
dbms_output.put_line(p_circle.id);
dbms_output.put_line(pe);
dbms_output.put_line('************************');
end loop;
end;
/
-
-while
declare
p_circle circle%rowtype;
v_count number;
v_id number:= 1;
begin
select count(*) into v_count from circle;
while v_id <= v_count loop
select * into p_circle from circle where id=  v_id;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。