温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

Oracle学习笔记之第八节sql语句(开发课学生指南051)

发布时间:2020-07-18 15:15:21 来源:网络 阅读:464 作者:guosmile 栏目:关系型数据库

 开发课 做练习 学生指南051 les01 ppt

 oracle经常两表连接,叫第三范式,如带有ID性质的东西

 以下是sql语句的命令

 select * from departments; 部门表

select * from employees; 员工表

select employee_id,rowid,rownum from employees

where employee_id>=200;  64进制内部运算 rownum本质查询第几行


select employee_id,rowid,rownum from employees

where rownum<=5; 查看前5行


select * from employees e where e.department_id=80; 这部门里的人都有提成 

select last_name,12*salary*(1+commission_pct) from employees; 查看年收入

select last_name,12*salary*(1+nvl(commission_pct,0)) from employees; 查看年收入,处理空行的显示

select * from employees where manager_id is null; 关于空的处理

select last_name,12*salary*(1+nvl(commission_pct,0)) as anaualsal  from employees;进行列别名处理

select last_name,12*salary*(1+nvl(commission_pct,0)) "Anaualsal"  from employees;如果要区分大小写要加双引号

select last_name ||'xxxx'|| job_id from employees; 连起来显示

select department_name || q'[ department's manager id:]' || manager_id as "Department and Manager" from departments;    q'#xxx# 也可以, 后面就是你想要的东西

select DISTINCT department_id from employees; 有除重必须排序(空有显示,178号员工没部门) 



create table t05101_distinct (a varchar2(10),b varchar2(10));

insert into t05101_distinct values ('A','B');

select * from t05101_distinct;

select distinct a,b from t05101_distinct;

insert into t05101_distinct values ('A','B1');  除重是除掉整行

select distinct a,b from t05101_distinct;

select * from user_tab_cols tc where tc.TABLE_NAME='LOCATIONS'; 查看用户范围内的所有表的所有列

方法2:sqlplus命令

sqlplus /nolog

conn hr/oracle_4U

describe  locations  查看表结构




小提示:选中SQL 按F5看执行计划

 

les02 ppt

oracle变量有6种+1形参,两种宿主


select * from employees where rownum=1;

alter session set nls_date_format='YYYY-MM-DD';修改当前会话默认格式

select last_name from employees where hire_date = to_date('17-JUN-07','DD-MON-RR');


select * from employees e where e.employee_id in (select manager_id from employees);查看多少人是老板管过人

select * from employees e where e.employee_id not in (select manager_id from employees);这个例子是错误的。不能not in 因为里头有空值取反还是空

select * from employees e where e.employee_id not in (select manager_id from employees where manager_id is not null);这样才有显示89个人



create table t05102_a (a varchar2(10));

insert into t05102_a values ('A');

insert into t05102_a values ('A1');

insert into t05102_a values ('%');

insert into t05102_a values ('_');

insert into t05102_a values ('_1');

select * from t05102_a;

select * from t05102_a where a like 'A%'; A打头的东西

select * from t05102_a where a like 'A_';查看A打头的东西


select * from t05102_a where a like '\%%' escape '\'; 查看%号

select * from t05102_a where a like '\_%' escape '\';查看下划线打头的

insert into t05102_a values('''');插入单引号

insert into t05102_a values(chr(39)||1); man ascii查看得来的


create table t05102_b (a number,b number);

insert into t05102_b values(1,999);

insert into t05102_b values(1,0);

insert into t05102_b values(2,999);

insert into t05102_b values(2,0);

select * from t05102_b order by a,b;先按a排列在按照b排列

select * from t05102_b order by a desc ,b desc;   

select a "X", b "Y" from t05102_b order by "X" desc,"Y" desc;别名,在order by世界里无所谓是“X”还是a都可以,但是正常语句的sql语句的别名不行


替换变量

select employee_id,salary from employees where employee_id=100;

select employee_id,salary from employees where employee_id=&S_1; 可以进出弹窗,自己选择ID号

select last_name,salary from employees where last_name like '&S_1%';可以进出弹窗

sqlplus /nolog

select  salary from employees where employee_id=&&s_2;问两次后永远都是这个

define 默认在这了

undefine s_2 取消

select  &&s2,salary from employees where employee_id=&s_2;

set verify off 配置这个后就不会有旧的新的,环境变量


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI