首页 > 代码库 > 《oracle语句》
《oracle语句》
select查询语句
查看用户名下的表，解锁hr用户：
用sys身份进入
方法一：[oracle@node1 ~]$ sqlplus / as sysdba
方法二：SQL> conn / as sysdba
查看身份
SQL> show user
USER is "SYS"
SQL> select table_name from dba_tables where owner=‘HR‘;
SQL> select * from hr.employees;
SQL> alter user hr account unlock identified by hr; 解锁用户
切换用户hr
$ sqlplus hr/hr或者SQL> conn hr/hr
SQL> show user
SQL> select * from tab;
SQL> desc employees 查看employees表结构
所要查询信息显示的表的格式
Select 列 *表示所有列 多条列：列1，+空格列2
From 表
where行
练习：
查看scott用户名下的表，解锁scott用户：
Scott 用户/tiger口令
- 使用sqlplus的全屏编辑功能：
$ echo $EDITOR
SQL> select * from hr.employees;
SQL> ed
SQL> / 执行
- 基础select语句：
SQL> select LAST_NAME,SALARY*(1+COMMISSION_PCT)*12 from employees;
【可以给SALARY*(1+COMMISSION_PCT)*12起个别名，格式：原名+空格+新别名】
LAST_NAME SALARY*(1+COMMISSION_PCT)*12
------------------------- ----------------------------
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
Baer
Higgins
Gietz
King
Kochhar
SQL> select * from employees;
SQL> desc employees
SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;
SQL> desc departments
SQL> select department_id, department_name from departments;
SQL> select distinct DEPARTMENT_ID from employees;
SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees; 原名 别名
SQL> select first_name||‘, ‘||last_name from employees;
SQL> select first_name||‘, ‘||last_name fullname from employees;
练习：
输出下列员工信息：
Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …
答：
SQL> select first_name, last_name,‘employeeid is:‘||employee_id||
‘,at department:‘||DEPARTMENT_ID||‘,total salary:‘||salary*12 from employees;
Alana,Walsh
employeeid is:196,at department:50,total salary:37200
Kevin,Feeney
employeeid is:197,at department:50,total salary:36000
- 使用连字符构造语句：
SQL> select table_name from user_tables;
SQL> select ‘grant select on hr.‘||table_name||‘ to scott;‘ from user_tables;
SQL> spool /home/oracle/grant.sql
SQL> select ‘grant select on hr.‘||table_name||‘ to scott;‘ from user_tables;
SQL> spool off
$ vi /home/oracle/grant.sql 去除没用的——行
SQL> @/home/oracle/grant.sql
- 单引号的处理：
SQL> select ‘I‘m teaher‘ from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select ‘I‘‘m teaher‘ from dual;
SQL> select q‘{I‘m teaher}‘ from dual; {},[],<>,()都可以
除去重复值：distinct
SQL>select deoatments_id from employees;
SQL>select distinct deoatments_id from employees;
where和order by
数字条件：
SQL> select salary from employees where employee_id=100;
字符串大小写敏感：
SQL> select last_name, salary from employees where last_name=‘King‘;
SQL> select last_name, salary from employees where lower(last_name)=‘king‘;
SQL> select table_name, tablespace_name from user_tables where table_name=‘EMPLOYEES‘;
日期是格式敏感： alter session :只是针对当前会话
SQL> alter sysdate from dual;
SQL> alter session set nls_date_format=‘RR-Mon-dd‘;
SQL> alter sysdate from dual;
SQL> select last_name from employees where hire_date=‘2006-05-23‘; 报错
SQL> select last_name from employees where hire_date=‘23-MAY-06‘;
SQL> select last_name from employees where hire_date=to_date(‘2006-05-23‘, ‘yyyy-mm-dd‘);
解释一下日期格式，方便统一
区间查询：
SQL> select last_name from employees where salary>=3400 and salary<=4000;
SQL> select last_name from employees where salary between 3400 and 4000;
SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;
in：
SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;
SQL> select last_name from employees where department_id in (30, 40, 50);
通配符：
SQL> select last_name, job_id from employees where job_id like ‘%\_MAN‘ escape ‘\‘;
【\:转义符，屏蔽\后面 的符号的特殊含义，让其符号只是表示普通的含义】
null作为条件：
SQL> select last_name from employees where commission_pct is null;
SQL> select last_name from employees where commission_pct is not null;
and/or/not：
SQL> select last_name, job_id, department_id, salary from employees where job_id=‘SA_REP‘ or department_id=50 and salary>=8000;
SQL> select last_name, job_id, department_id, salary from employees where (job_id=‘SA_REP‘ or department_id=50) and salary>=8000;
排序：
SQL> select last_name, salary from employees order by salary;
SQL> select last_name, salary from employees order by salary desc;
SQL> select last_name, salary from employees order by last_name;
SQL> select last_name, hire_date from employees order by hire_date;
SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;
SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;
SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;
Or 和 and同时出现，优先and，在or
练习：
选择部门30中的雇员
SQL> select last_name, department_id from employees where department_id=‘30‘;
列出所有职员(CLERK)的姓名、编号和部门
SQL> select last_name, employee_id, department_id from employees;
找出薪金大于5000的雇员
SQL> select last_name from employees where salary>5000;
找出奖金高于0.1的雇员
SQL> select last_name from employees where COMMISSION_PCT>0.1;
找出部门50中的所有员工和部门30中的经理的详细资料
SQL> select * from employees where department_id=‘50‘ or department_id=‘30‘ and job_id like ‘man%‘;
找出收取奖金的雇员的不同工作职位 每种职位显示一次
SQL> select distinct job_id from employees where commission_pct>0;
找出不收取奖金或收取的工资低于5000的雇员
SQL> select last_name from employees where commission_pct is null or salary<5000;
显示last_name不带有‘R‘的雇员姓名
SQL> select last_name from employees where last_name not like‘R%‘;
《oracle语句》