oracle979,Oracle

alter session set nls_date_language='american'; 修改日期格式

Mysql端口 3306

取工资前五名

select rownum rn,ename,salary from (select rownum rn, empno,ename,salary from emp_info order by salary desc) where rn <=5;

create table dept_department(

deptno number(2),

dname char(20),

locationchar(20));

insert into dept_department values (10, 'developer', 'beijing');

insert into dept_department values (20, 'account', 'shanghai');

insert into dept_department values (30, 'sales', 'guangzhou');

insert into dept_department values (40, 'operartions', 'tianjin');

commit;

select * from dept_department;

create table emp_info(

empno number(4),

ename varchar2(20),

job varchar2(15),

salary number(7,2),

bonus number(7,2),

hiredatedate,

mgr number(4),

deptno number(10));

insert into emp_info values (1001, '张无忌', 'Manager', 10000, 2000, '12-MAR-10', 1005, 10);

insert into emp_info values (1002, '苍老师', 'Analyst', 8000, 1000, '01-APR-11', 1001, 10);

insert into emp_info values (1003, '李玉', 'Analyst', 9000, 1000, '11-APR-10', 1001, 10);

insert into emp_info values (1004, '郭芙蓉', 'Programmer', 5000, null, '01-JAN-11', 1001, 10);

insert into emp_info values (1005, '张三丰', 'President', 15000, null, '15-MAY-08', null, 20);

insert into emp_info values (1006, '燕小六', 'Manager', 5000, 400, '01-FEB-09', 1005, 20);

insert into emp_info values (1007, '陆无双', 'clerk', 3000, 500, '12-MAR-09', 1006, 20);

insert into emp_info values (1008, '黄蓉', 'Manager', 5000, 500, '12-MAR-10', 1005, 10);

insert into emp_info values (1009, '韦小宝', 'salesman', 4000, null, '24-MAR-10', 1008, 30);

insert into emp_info values (1010, '郭靖', 'salesman', 4500, 500, '10-MAR-09', 1008, 30);

create table bank_user(

id varchar2(2),

acount numner(10));

insert into bank_user values('A',4000);

insert into bank_user values('B',100);

commit;

update bank_user set acount=

case id when 'A' then acount - 3000

when 'B' then acount + 3000

else acount

end; //后面不能跟列别名

Oracle中单引号转义，两个单引号表示一个单引号 ''s 表示's

select ename ||'''s total salary is'|| (salary+nvl(bonus,0))*12||'.' from emp_info

where

select ename, salary from emp_info where salary>5000;

工资大于5000，求年新

select ename, (salary+nvl(bonus,0))*12 "Salary "from emp_info where salary>5000;

年薪大于6000

select ename, salary*12 "Total Salary" from emp_info where salary>5000;

from -- > where --> select , where后面不可以跟列别名

salary建索引

salary*12>60000用不上索引

salary > 5000用索引

把职位是manager的人列出来

把职位是sale的人列出来

from --> where --> group --> having --> select

where后面可以跟 列名 表达式

job建索引

where upper(job) = 'MANAGER'用不上索引

where job = 'Manag