sql函数学习1

用户解锁 重设密码

alter user hr account unlock;

alter user hr identified by hr;

sql语句

转义

SQL> select employee_id,job_id,salary

  2  from employees

  3  where job_id like 'SA/_%' ESCAPE '/';

order by默认是升序(asc)

order by可使用别名

SQL> select employee_id,last_name,salary*12 annsal

  2  from employees

  3  order by annsal; 

order by可使用字段名的位置序号

SQL> select employee_id,last_name,salary*12 annsal

  2  from employees

  3  order by 3; 

order by按从左到右的顺序进行排列

SQL> select last_name,department_id,salary

  2  from employees

  3  order by department_id,salary desc;

可以这样来规定null值的位置

SQL> select last_name,department_id,salary

  2  from employees

  3  order by department_id,salary desc nulls fisrt(nulls last);

定义变量，如果是字符串和日期要加''

  1  select * from employees

  2* where employee_id = &employee_num

&只是做简单的变量替换，sql语句本身不做任何检查，所以输入的时候要保证格式正确性，&和变量之间不

能有空格

SQL> l

  1  select employee_id,last_name,job_id,&column_name

  2  from employees

  3  where &condition

  4* order by &order_column

Enter value for column_name: salary

old   1: select employee_id,last_name,job_id,&column_name

new   1: select employee_id,last_name,job_id,salary

Enter value for condition: salary>15000

old   3: where &condition

new   3: where salary>15000

Enter value for order_column: last_name

old   4: order by &order_column

new   4: order by last_name

set verify on/off 可以把old/new那两行关掉

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY

----------- ------------------------- ---------- ----------

        102 De Haan                   AD_VP           17000

        100 King                      AD_PRES         24000

        101 Kochhar                   AD_VP           17000

&和&&的区别

SQL> select employee_id,last_name,&column_name

  2  from employees

  3  order by &column_name;

Enter value for column_name: salary

old   1: select employee_id,last_name,&column_name

new   1: select employee_id,last_name,salary

Enter value for column_name: salary

old   3: order by &column_name

new   3: order by salary

&column_name让输入了2次

SQL> l

  1  select employee_id,last_name,&column_name

  2  from employees

  3* order by &column_name

SQL> 1 select employee_id,last_name,&&column_name

SQL> /

Enter value for column_name: salary

old   1: select employee_id,last_name,&&column_name

new   1: select employee_id,last_name,salary

old   3: order by &column_name

new   3: order by salary

用&&输入一次就行了，他会保存这个变量，以后再出现都是一样的值

'fmyyyy-mm-dd' fm的作用

SQL> select employee_id,to_char(hire_date,'yyyy-mm-dd')

  2  from employees

  3  where last_name='Higgins';

EMPLOYEE_ID TO_CHAR(HI

----------- ----------

        205 1994-06-07

SQL> l

  1  select employee_id,to_char(hire_date,'yyyy-mm-dd')

  2  from employees

  3* where last_name='Higgins'

SQL> 1 select employee_id,to_char(hire_date,'fmyyyy-mm-dd')

SQL> /

EMPLOYEE_ID TO_CHAR(HI

----------- ----------

        205 1994-6-7

'fxmonth dd,yyyy' fx的作用

没有fx，不严格的转换

SQL> select to_date('july 4,2007','month dd,yyyy')from dual;

TO_DATE('

---------

04-JUL-07

有fx，严格转换

SQL> select to_date('July 4,2007','fxMonth DD,YYYY') from dual;

select to_date('July 4,2007','fxMonth DD,YYYY') from dual

               *

ERROR at line 1:

ORA-01862: the numeric value does not match the length of the format item

SQL> select to_date('july 04,2007','fxmonth dd,yyyy')from dual;

TO_DATE('

---------

04-JUL-07

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11517198/viewspace-558302/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11517198/viewspace-558302/