--创建员工信息表 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); --将scott里面的emp数据导入此表 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) --查询编号不重复的 ); --删除编号大于5的员工信息 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; --上级3(分页查询) --查询员工薪水从高到低的排序为3-5的记录 SELECT * FROM (SELECT e.*,rownum rn FROM (SELECT * FROM employee ORDER BY sal DESC) e) WHERE rn>=3 AND rn<=5; --网上找的分页查询 --分页查询1 select * from ( select a.*,rownum rn from ( select * from scott.emp ) a ) where rn between 3 and 5; --分页查询2 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 --使用分析函数对员工表进行查询 --DENSE_RANK必须大写 select * from ( select employee.*,DENSE_RANK( ) over (partition by deptno order by sal desc) "DENSE_RANK" from employee )e where DENSE_RANK=2; --上机5 --round函数(四舍五入) /*如何使用 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; --列出至少有一个雇员的所有部门 --distinct(查找重复的列) 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='程序员'; --列出各种工作类别的最低薪金,显示最低薪金大于15000的记录 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; --2:得到上月第一天与上月最后一天 SELECT to_char( last_day(add_months(SYSDATE, -2)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -1)),'yyyymmdd') lastday FROM dual; --3:得到上上个月第一天与上上个月最后一天 SELECT to_char( last_day(add_months(SYSDATE, -3)) + 1 ,'yyyymmdd') firstday ,to_char(last_day(add_months(SYSDATE, -2)),'yyyymmdd') lastday FROM dual; --4:得到下个月第一天与下个月最后一天 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 ====================================================== */ --1.创建订单表 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), --销售代表id promotion_id number(6) --推广员id ) --创建客户表 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; --1.查询客户表中所有不重复的分地域(distinct) select distinct nls_territory from customers; --2.在订单表中找出总定价在1000-6000之间的订单号 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 ); --3.在顾客表(Customers)中找出所在地域为AMERICA的客户经理名称(Employee表的ename列)和薪水(employee表的sal列)。 select c.ename,c.sal from customers a join employee c on a.account_mgr_id=c.empno where nls_territory='AMERICA' --4.在顾客表(Customers)中找出所在地域为AMERICA、山东济南、山西吕梁和中国北京的客户编号及语言。 select customer_id,nls_languages from Customers where nls_territory='AMERICA' or nls_territory='山东济南' or nls_territory='中国北京'; --5.在顾客表(Customers)中找出姓氏首字母为”F”的客户编号和邮箱。 select customer_id 客户编号,cust_email 邮箱 from customers where cust_last_name like 'F%'; --6.查出所有客户姓名和所下的订单编号 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 --从1开始 increment by 1 --每次加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;
nice,有的人可能之前没学过数据库,比如说my sql ,sql server 等等,直接上来就是oracle,所以,推荐一部视频,即使你没有学过sql server什么的,或者学过没深入理解的,那么,请点击这里, 密码: 3ydr,一共46节课,足足够你学会oracle了,拿走不谢!!!