Oracle数据库整理笔记
来源:互联网 发布:windows 10桌面太亮 编辑:程序博客网 时间:2024/05/22 11:43
create table employee ( empno number(4) not null, ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) ); select * from employee; insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1,'房钊','程序员',1,'2016-02-05',3000,2000,1); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(2,'房钊','开发人员',3,'2017-06-05',5000,2000,2); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(3,'房钊','测试人员',1,'2014-11-05',8000,5000,2); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(4,'房钊','运维人员',2,'2017-02-02',10000,2300,1); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(5,'房钊','发布',3,'2016-02-05',6300,36000,3); insert into employee select * from scott.emp; commit; select * from employee; create table bumen ( buno number(2), buname varchar2(50) ); select * from bumen; insert into bumen(buno,buname) values(1,'开发部'); insert into bumen(buno,buname) values(2,'测试部'); insert into bumen(buno,buname) values(3,'设计部'); insert into bumen(buno,buname) values(4,'运维部'); insert into bumen(buno,buname) values(5,'管理部'); insert into bumen(buno,buname) values(7902,'财务部'); insert into bumen(buno,buname) values(7968,'福利部'); insert into bumen(buno,buname) values(7698,'奖金部'); insert into bumen(buno,buname) values(7839,'娱乐部'); insert into bumen(buno,buname) values(7566,'搬砖部'); insert into bumen(buno,buname) values(7788,'前台部'); insert into bumen(buno,buname) values(7566,'看看部'); insert into bumen(buno,buname) values(7782,'2222部'); insert into bumen(buno,buname) values(7902,'1111部'); insert into bumen(buno,buname) values(7839,'物流部'); insert into bumen(buno,buname) values(7788,'废物部'); commit; select * from bumen; create table jinli ( jno number(4), jname varchar2(50) ); commit; select * from employee; insert into jinli(jno,jname)values(1,'房钊'); insert into jinli(jno,jname)values(2,'房钊'); insert into jinli(jno,jname)values(3,'杜永杰'); insert into jinli(jno,jname)values(4,'王帅帅'); insert into jinli(jno,jname)values(5,'张思淼'); insert into jinli(jno,jname)values(7839,'李白'); insert into jinli(jno,jname)values(7499,'杜甫'); insert into jinli(jno,jname)values(7521,'王伟'); insert into jinli(jno,jname)values(7566,'王安石'); insert into jinli(jno,jname)values(7654,'冯巩'); insert into jinli(jno,jname)values(7698,'曾巩'); insert into jinli(jno,jname)values(7782,'欧阳修'); insert into jinli(jno,jname)values(7788,'王杰'); insert into jinli(jno,jname)values(7844,'马强'); insert into jinli(jno,jname)values(7876,'刘三单'); insert into jinli(jno,jname)values(7876,'李安'); insert into jinli(jno,jname)values(7900,'曹春喜'); insert into jinli(jno,jname)values(7902,'付婷婷'); insert into jinli(jno,jname)values(7934,'孙中天'); insert into jinli(jno,jname)values(7369,'李昂'); insert into jinli(jno,jname)values(7499,'金钟管'); insert into jinli(jno,jname)values(7521,'张瑞前'); insert into jinli(jno,jname)values(7566,'五保前'); insert into jinli(jno,jname)values(7654,'张何婷'); insert into jinli(jno,jname)values(7369,'孙浩'); select * from jinli; delete from jinli where jno>5 delete from employee where rowid not in ( select max(rowid) from employee group by empno having (count(empno)>1) union select max(rowid) from employee group by empno having (count(empno)=1) ); delete from employee where empno >5; select * from employee; select * from bumen; alter table employee add constraint pk_empno primary key (empno); alter table bumen add constraint pk_buno primary key (buno); alter table jinli add constraint pk_jno primary key (jno); alter table employee add constraint fk_deptno foreign key(deptno) references bumen(buno); alter table employee add(empTel_no varchar2(12), empAdress varchar2(20) ); alter table employee drop(empTel_no,empAdress); select * from employee order by sal desc; SELECT * FROM (SELECT e.*,rownum rn FROM (SELECT * FROM employee ORDER BY sal DESC) e) WHERE rn>=3 AND rn<=5; select * from ( select a.*,rownum rn from ( select * from scott.emp ) a ) where rn between 3 and 5; select * from ( select a.*,rownum rn from ( select * from scott.emp )a where rownum <=5 ) where rn >=3; select * from (select a.*,rownum rn from (select * from employee order by sal desc) a) where rn>=2 and rn<=5 select * from ( select employee.*,DENSE_RANK( ) over (partition by deptno order by sal desc) "DENSE_RANK" from employee )e where DENSE_RANK=2; /*如何使用 Oracle Round 函数 (四舍五入) 描述 : 传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果。 SELECT ROUND(number, [ decimal_places ] ) FROM DUAL 参数: number : 欲处理之数值 decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ) Sample : select round(123.456, 0) from dual; 回传 123 select round(123.456, 1) from dual; 回传 123.5 select round(123.456, 2) from dual; 回传 123.46 select round(123.456, 3) from dual; 回传 123.456 select round(-123.456, 2) from dual; 回传 -123.46 */ select ename "姓名",to_char(round (hiredate,'yyyy'),'YYYY')||'年度' 入职年度 from employee; select distinct deptno from employee; select * from employee where sal >(select sal from employee where ename='张三'); select ename,job,bumen.buname from employee,bumen where employee.deptno=bumen.buno and job='程序员'; select job, min(sal) from employee group by job having min(sal)>1500; select * from employee where hiredate=last_day(hiredate); /* oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期. 1:得到当前月第一天与最后一天 */ select to_char(trunc(sysdate,'MONTH'),'yyyymmdd') firstday , to_char(last_day(trunc(sysdate,'MONTH')),'yyyymmdd') lastday from dual; SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -1)),'yyyymmdd') lastday FROM dual; SELECT to_char( last_day(add_months(SYSDATE, -3)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -2)),'yyyymmdd') lastday FROM dual; SELECT to_char( last_day(add_months(SYSDATE, 0)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, 1)),'yyyymmdd') lastday FROM dual; /* ====================================================== 简答题p38 ====================================================== */ create table orders ( order_id number(12) primary key, order_date date not null, order_mode varchar2(8) not null, customer_id number(6) not null, order_status number(2), order_total number(8,2), sales_rep_id number(6), promotion_id number(6) ) create table customers ( customer_id number(6) not null, cust_fiest_name varchar2(20) not null, cust_last_name varchar2(20) not null, nls_languages varchar2(3), nls_territory varchar2(30), credit_limit number(9,2), cust_email varchar2(30), account_mgr_id number(6), marital_status varchar2(30), gender char(1) ) alter table customers modify (gender char(2)); alter table customers modify (nls_languages varchar2(10)); select * from customers insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('1','虎虎','王','汉语','山西吕梁','3000','wanghuhu@163.com',1,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('2','雄雄','穆','英语','山西吕梁','8000','muxiongxiong@163.com',2,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('3','永杰','杜','俄语','中国北京','1568','duyongjie@163.com',1,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('4','娇娇','宋','法语','山西太原','6000','songjiaojiao@163.com',3,'已婚','女'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('5','思淼','张','汉语','山东济南','6320','zahngsimiao@163.com',3,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','春熙','曹','法语','山东济南','3600','caochunxi@163.com',2,'已婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','儿子','王','法语','AMERICA','56000','america@163.com',3,'已婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('8','孙子','Fssdfs','韩语','中国北京','6320','Fssdfs@163.com',4,'未婚','女'); select * from customers select * from orders; insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(1,'2017-02-09','网上下单',2,1,323.23,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(2,'2016-11-09','上门购买',1,2,56.00,2,1); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(3,'2017-12-20','熟人推荐',3,1,6000,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(4,'2015-12-02','网上下单',5,2,365,2,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(5,'2017-12-09','上门购买',3,1,3210,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(6,'2014-11-11','网上下单',3,1,630,2,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(7,'2017-01-01','上门购买',2,1,6300,1,2); commit; select * from orders; select distinct nls_territory from customers; select order_id 订单编号,b.cust_fiest_name 顾客姓氏,c.ename 经理名字 from orders a join customers b on a.customer_id=b.customer_id join employee c on b.account_mgr_id=c.empno where order_id in ( select order_id from orders where order_total>=1000 and order_total<=6000 ); select c.ename,c.sal from customers a join employee c on a.account_mgr_id=c.empno where nls_territory='AMERICA' select customer_id,nls_languages from Customers where nls_territory='AMERICA' or nls_territory='山东济南' or nls_territory='中国北京'; select customer_id 客户编号,cust_email 邮箱 from customers where cust_last_name like 'F%'; select cust_last_name,cust_fiest_name,b.order_id from customers a join orders b on a.customer_id =b.customer_id where a.customer_id in ( select customer_id from orders ); /* 2. 根据员工表(Employee表)编写一个语句,只有当最低工资少于 5000 而且最高工资超过7000时,才显示部门 ID 以及该部门支付的最低工资和最高工资。 */ select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资 from employee group by deptno having min(sal)<5000 and max(sal)>7000; /* 3. 根据员工表(Employee表)编写一个语句,显示各部门的每个工作类别中支付的最高工资 */ select deptno,job,max(sal) from employee group by deptno,job order by deptno,job; select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') from dual; select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual; select * from dual; select SYS_CONTEXT('USERENV','TERMINAL') from dual; select TO_DATE ('2013-1-1','yyyy-mm-dd') from dual; select to_date('21210213','yyyy-mm-dd') from dual; select max(rowid) from student group by sage,sname having (count(sage||sname)>1); union select max(rowid) from student group by sname,sage having (count(sage||sname)=1); /* 创建自增长 */ create table grade ( id number(2) not null primary key, name varchar2(12) ) create sequence grade_add start with 1 increment by 1 nocycle create or replace trigger grade_c before insert on grade for each row when (new.id is null) begin select grade_add.nextval into:new.id from dual; end; insert into grade(name) values('12班'); insert into grade (name) values('13班'); insert into grade (name) values('14班'); insert into grade(name) values('15班'); insert into grade (name) values('16班'); insert into grade (name) values('17班'); select * from grade;
create table employee ( empno number(4) not null, ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) ); select * from employee; insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1,'张一','程序员',1,'2016-02-05',3000,2000,1); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(2,'张二','开发人员',3,'2017-06-05',5000,2000,2); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(3,'张三','测试人员',1,'2014-11-05',8000,5000,2); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(4,'张四','运维人员',2,'2017-02-02',10000,2300,1); insert into employee(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(5,'张五','发布',3,'2016-02-05',6300,36000,3); insert into employee select * from scott.emp; commit; select * from employee; create table bumen ( buno number(2), buname varchar2(50) ); select * from bumen; insert into bumen(buno,buname) values(1,'开发部'); insert into bumen(buno,buname) values(2,'测试部'); insert into bumen(buno,buname) values(3,'设计部'); insert into bumen(buno,buname) values(4,'运维部'); insert into bumen(buno,buname) values(5,'管理部'); insert into bumen(buno,buname) values(7902,'财务部'); insert into bumen(buno,buname) values(7968,'福利部'); insert into bumen(buno,buname) values(7698,'奖金部'); insert into bumen(buno,buname) values(7839,'娱乐部'); insert into bumen(buno,buname) values(7566,'搬砖部'); insert into bumen(buno,buname) values(7788,'前台部'); insert into bumen(buno,buname) values(7566,'看看部'); insert into bumen(buno,buname) values(7782,'2222部'); insert into bumen(buno,buname) values(7902,'1111部'); insert into bumen(buno,buname) values(7839,'物流部'); insert into bumen(buno,buname) values(7788,'废物部'); commit; select * from bumen; create table jinli ( jno number(4), jname varchar2(50) ); commit; select * from employee; insert into jinli(jno,jname)values(1,'穆雄雄'); insert into jinli(jno,jname)values(2,'成志龙'); insert into jinli(jno,jname)values(3,'杜永杰'); insert into jinli(jno,jname)values(4,'王帅帅'); insert into jinli(jno,jname)values(5,'张思淼'); insert into jinli(jno,jname)values(7839,'李白'); insert into jinli(jno,jname)values(7499,'杜甫'); insert into jinli(jno,jname)values(7521,'王伟'); insert into jinli(jno,jname)values(7566,'王安石'); insert into jinli(jno,jname)values(7654,'冯巩'); insert into jinli(jno,jname)values(7698,'曾巩'); insert into jinli(jno,jname)values(7782,'欧阳修'); insert into jinli(jno,jname)values(7788,'王杰'); insert into jinli(jno,jname)values(7844,'马强'); insert into jinli(jno,jname)values(7876,'刘三单'); insert into jinli(jno,jname)values(7876,'李安'); insert into jinli(jno,jname)values(7900,'曹春喜'); insert into jinli(jno,jname)values(7902,'付婷婷'); insert into jinli(jno,jname)values(7934,'孙中天'); insert into jinli(jno,jname)values(7369,'李昂'); insert into jinli(jno,jname)values(7499,'金钟管'); insert into jinli(jno,jname)values(7521,'张瑞前'); insert into jinli(jno,jname)values(7566,'五保前'); insert into jinli(jno,jname)values(7654,'张何婷'); insert into jinli(jno,jname)values(7369,'孙浩'); select * from jinli; delete from jinli where jno>5 delete from employee where rowid not in ( select max(rowid) from employee group by empno having (count(empno)>1) union select max(rowid) from employee group by empno having (count(empno)=1) ); delete from employee where empno >5; select * from employee; select * from bumen; alter table employee add constraint pk_empno primary key (empno); alter table bumen add constraint pk_buno primary key (buno); alter table jinli add constraint pk_jno primary key (jno); alter table employee add constraint fk_deptno foreign key(deptno) references bumen(buno); alter table employee add(empTel_no varchar2(12), empAdress varchar2(20) ); alter table employee drop(empTel_no,empAdress); select * from employee order by sal desc; SELECT * FROM (SELECT e.*,rownum rn FROM (SELECT * FROM employee ORDER BY sal DESC) e) WHERE rn>=3 AND rn<=5; select * from ( select a.*,rownum rn from ( select * from scott.emp ) a ) where rn between 3 and 5; select * from ( select a.*,rownum rn from ( select * from scott.emp )a where rownum <=5 ) where rn >=3; select * from (select a.*,rownum rn from (select * from employee order by sal desc) a) where rn>=2 and rn<=5 select * from ( select employee.*,DENSE_RANK( ) over (partition by deptno order by sal desc) "DENSE_RANK" from employee )e where DENSE_RANK=2; /*如何使用 Oracle Round 函数 (四舍五入) 描述 : 传回一个数值,该数值是按照指定的小数位元数进行四舍五入运算的结果。 SELECT ROUND(number, [ decimal_places ] ) FROM DUAL 参数: number : 欲处理之数值 decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ) Sample : select round(123.456, 0) from dual; 回传 123 select round(123.456, 1) from dual; 回传 123.5 select round(123.456, 2) from dual; 回传 123.46 select round(123.456, 3) from dual; 回传 123.456 select round(-123.456, 2) from dual; 回传 -123.46 */ select ename "姓名",to_char(round (hiredate,'yyyy'),'YYYY')||'年度' 入职年度 from employee; select distinct deptno from employee; select * from employee where sal >(select sal from employee where ename='张三'); select ename,job,bumen.buname from employee,bumen where employee.deptno=bumen.buno and job='程序员'; select job, min(sal) from employee group by job having min(sal)>1500; select * from employee where hiredate=last_day(hiredate); /* oracle的日期函数last_day 意思是得到每月的最后一天,用这个函数,我们可以得到各种不同的日期. 1:得到当前月第一天与最后一天 */ select to_char(trunc(sysdate,'MONTH'),'yyyymmdd') firstday , to_char(last_day(trunc(sysdate,'MONTH')),'yyyymmdd') lastday from dual; SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -1)),'yyyymmdd') lastday FROM dual; SELECT to_char( last_day(add_months(SYSDATE, -3)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -2)),'yyyymmdd') lastday FROM dual; SELECT to_char( last_day(add_months(SYSDATE, 0)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, 1)),'yyyymmdd') lastday FROM dual; /* ====================================================== 简答题p38 ====================================================== */ create table orders ( order_id number(12) primary key, order_date date not null, order_mode varchar2(8) not null, customer_id number(6) not null, order_status number(2), order_total number(8,2), sales_rep_id number(6), promotion_id number(6) ) create table customers ( customer_id number(6) not null, cust_fiest_name varchar2(20) not null, cust_last_name varchar2(20) not null, nls_languages varchar2(3), nls_territory varchar2(30), credit_limit number(9,2), cust_email varchar2(30), account_mgr_id number(6), marital_status varchar2(30), gender char(1) ) alter table customers modify (gender char(2)); alter table customers modify (nls_languages varchar2(10)); select * from customers insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('1','虎虎','王','汉语','山西吕梁','3000','wanghuhu@163.com',1,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('2','雄雄','穆','英语','山西吕梁','8000','muxiongxiong@163.com',2,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('3','永杰','杜','俄语','中国北京','1568','duyongjie@163.com',1,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('4','娇娇','宋','法语','山西太原','6000','songjiaojiao@163.com',3,'已婚','女'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('5','思淼','张','汉语','山东济南','6320','zahngsimiao@163.com',3,'未婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','春熙','曹','法语','山东济南','3600','caochunxi@163.com',2,'已婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('6','儿子','王','法语','AMERICA','56000','america@163.com',3,'已婚','男'); insert into customers (customer_id,cust_fiest_name,cust_last_name,nls_languages,nls_territory,credit_limit,cust_email,account_mgr_id,marital_status,gender)values('8','孙子','Fssdfs','韩语','中国北京','6320','Fssdfs@163.com',4,'未婚','女'); select * from customers select * from orders; insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(1,'2017-02-09','网上下单',2,1,323.23,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(2,'2016-11-09','上门购买',1,2,56.00,2,1); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(3,'2017-12-20','熟人推荐',3,1,6000,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(4,'2015-12-02','网上下单',5,2,365,2,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(5,'2017-12-09','上门购买',3,1,3210,1,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(6,'2014-11-11','网上下单',3,1,630,2,2); insert into orders(order_id,order_date,order_mode,customer_id,order_status,order_total,sales_rep_id,promotion_id) values(7,'2017-01-01','上门购买',2,1,6300,1,2); commit; select * from orders; select distinct nls_territory from customers; select order_id 订单编号,b.cust_fiest_name 顾客姓氏,c.ename 经理名字 from orders a join customers b on a.customer_id=b.customer_id join employee c on b.account_mgr_id=c.empno where order_id in ( select order_id from orders where order_total>=1000 and order_total<=6000 ); select c.ename,c.sal from customers a join employee c on a.account_mgr_id=c.empno where nls_territory='AMERICA' select customer_id,nls_languages from Customers where nls_territory='AMERICA' or nls_territory='山东济南' or nls_territory='中国北京'; select customer_id 客户编号,cust_email 邮箱 from customers where cust_last_name like 'F%'; select cust_last_name,cust_fiest_name,b.order_id from customers a join orders b on a.customer_id =b.customer_id where a.customer_id in ( select customer_id from orders ); /* 2. 根据员工表(Employee表)编写一个语句,只有当最低工资少于 5000 而且最高工资超过7000时,才显示部门 ID 以及该部门支付的最低工资和最高工资。 */ select deptno 部门编号,max(sal) 最高工资,min(sal) 最低工资 from employee group by deptno having min(sal)<5000 and max(sal)>7000; /* 3. 根据员工表(Employee表)编写一个语句,显示各部门的每个工作类别中支付的最高工资 */ select deptno,job,max(sal) from employee group by deptno,job order by deptno,job; select to_char(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') from dual; select to_char(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS') from dual; select * from dual; select SYS_CONTEXT('USERENV','TERMINAL') from dual; select TO_DATE ('2013-1-1','yyyy-mm-dd') from dual; select to_date('21210213','yyyy-mm-dd') from dual; select max(rowid) from student group by sage,sname having (count(sage||sname)>1); union select max(rowid) from student group by sname,sage having (count(sage||sname)=1); /* 创建自增长 */ create table grade ( id number(2) not null primary key, name varchar2(12) ) create sequence grade_add start with 1 increment by 1 nocycle create or replace trigger grade_c before insert on grade for each row when (new.id is null) begin select grade_add.nextval into:new.id from dual; end; insert into grade(name) values('12班'); insert into grade (name) values('13班'); insert into grade (name) values('14班'); insert into grade(name) values('15班'); insert into grade (name) values('16班'); insert into grade (name) values('17班'); select * from grade;