oracle课堂笔记--第十天

SQL

select查询语句

First_name(姓） last_name(名）

1. 查看hr用户名下的表，解锁hr用户：

$ sqlplus / as sysdba或SQL> conn / as sysdba

SQL> show user

SQL> select table_name from dba_tables where owner='HR';查看

SQL> select * from hr.employees;

SQL> alter user hr account unlock identified by hr;   解锁

$ sqlplus hr/hr或者SQL> conn hr/hr

SQL> show user

SQL> select * from tab;

SQL> desc employees   （表）

 

 

使用sqlplus的全屏编辑功能：

 

$ echo $EDITOR

 

SQL> select * from hr.employees;

 

SQL> ed

 

SQL> / 执行

 

基础select语句：

 

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;

 

使用连字符构造语句：

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; []<>()都可以

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';

日期是格式敏感：

SQL> alter session set nls_date_format='RR-Mon-dd';

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;

 

转载于:https://www.cnblogs.com/Matilda/p/7246762.html