/*
*数据库对象
*/
--表操作
--创建表
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_people_total.nextval 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_id,e.employee_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 employee 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_name) 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);
|
|
收藏
淘帖
踩
