ITPub博客

首页 > Linux操作系统 > Linux操作系统 > sql函数学习1

sql函数学习1

原创 Linux操作系统 作者:casper0511 时间:2009-03-02 17:45:49 0 删除 编辑
用户解锁 重设密码
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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: sql函数学习2
请登录后发表评论 登录
全部评论

注册时间:2008-12-08

  • 博文量
    34
  • 访问量
    69010