oracle课堂笔记--第十天
SQL
select查询语句
First_name(姓） last_name(名）
- 查看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;
