sql学习笔记

查询最近一小时的数据：

select (current_date - to_date( '2015-1-1 15:14:44', 'yyyy-mm-dd hh24:mi:ss')) * 24 from dual ;--0.256666666666667

假定还书逾期要罚款0.2。要使用一条Insert语句记录这条罚款：

INSERT INTO libraryReturn(member, book, returnDate, fine)

VALUES('jerry', 'book01', CURRENT_DATE,

IFNULL(
      SELECT 0.2 fine FROM libraryLoan
      WHERE membername='jim' AND book='book01'
      GROUP BY member, book
      HAVING MAX(due_Date) < CURRENT_DATE
      )

)

mysql和oracle分别用ifNull(arg, n)，nvl(arg, n)来替换null。

oracle中：select e.empno , (select nvl(comm , 0) from emp where empno = e.empno ) from emp e;

LOLI订房时要插入一条 sql 到roomBooking：

INSERT INTO roomBooking(whn, wht, who)

SELECT (DATE '2014-12-31', 'motel', 'LOLI')
 FROM DUAL
 WHERE NOT EXISTS(
           SELECT who FROM roomBooking
           WHERE whn = DATA '2014-12-31'
           AND wht = 'motel');

避免冗余计算：

SELECT income, overhead, 
           (income-income*overhead/100) AS residual, 
           0.20*(income-income*overhead/100) AS Est, 
           0.10*(income-income*overhead/100) AS Admin,
           0.05*(income-income*overhead/100) AS Rsrv
 FROM contract;
 可以改进为：

SELECT income, overhead, 0.20 residual, 0.10 residual, 0.05*residual FROM (

SELECT income, overhead, (income-income*overhead/100) AS residual

);

通配符：%表示任意长度的字符，_表示任意单个字符。如下语句获取开头三个字母和结尾三个字母相同的单词：

SELECT word FROM words WHERE word LIKE CONCAT ('%', SUBSTR(word ,1, 3)) AND LENGTH(word ) > 3;

SELECT * FROM persons p WHERE p. NAME IS NULL;

select to_char (sysdate, 'yyyy-dd-mm') "date" from dual;

select e.ename , e.hiredate from emp e where to_char (e.hiredate, 'yyyy') = '1980';

select round (months_between( sysdate, hire_date )) "worded_month" from employees;

字符控制函数：

initcap('helloworld')（单词首字母大写)

instr('helloworld', 'l')（某个字母首次出现的位置）

replace('helloworowld', 'ow', 'A')（用A替换所有ow）

lpad(salary, 8, ' ')、rpad(salary, 8, ' ')-- 24000、24000 （用*补足8位长度）

select trim('o' from 'helloworld') from dual;（截调第一个o）

select substr('helloworld', -3) from dual;--rld

select substr('helloworld', 1, 3) from dual;--hel

数值函数：

mod(1600, 300)--100（取余）

round(35.8254, 2)、round(35.8254, -1)、round(55.8254, -3)、round(55.8254, -2)--35.83、40、0、100（四舍五入）

trunc(35.8254, 2)、trunc(35.8254, -1)、round(55.8254, -3)--35.82、30、0（截断）

日期函数：

一个日期减去数字仍为日期、两个日期相减返回的是它们之间的天数、可用数字除以24来向日期中减去或加上天数

month_between(sysdate, hire_date)两个日期之间的月数

add_months(hire_date, 2)向指定的日期加上若干月

next_day()指定日期对应的下一个星期对应的日期 eg:select next_day(sysdate, '星期日') from dual;

last_day()本月的最后一天 eg:select e.last_name , e.hire_date from employees e where e.hire_date = last_day(e.hire_date );

ROUND/TRUNC:

Assume SYSDATE = '25-JUL-95':

ROUND(SYSDATE ,'MONTH') 01-AUG -95

ROUND(SYSDATE ,'YEAR') 01-JAN -96

TRUNC(SYSDATE ,'MONTH') 01-JUL -95

TRUNC(SYSDATE ,'YEAR') 01-JAN -95

yyyy 年 mm 月 dd 日 day 星期 hh 小时 mi 分钟 ss 秒

转换函数：（to_char和to_number可以看成是相逆的）

select to_char ( 12345678.9, '999,999,999.99' ) from dual; --12,345,678.90

select to_char ( 12345678.9, '000,000,000.00' ) from dual; --012,345,678.90

select to_char ( 12345678.9, '$999,999,999.99' ) from dual; --$12,345,678.90

select to_char ( 12345678.9, 'L999,999,999.99' ) from dual; --￥12,345,678.90

select to_number ( '￥12,345,678.90', 'L999,999,999.99' ) from dual ;--12345678.9

select to_number ( '$12,345,678.90', '$999,999,999.99' ) from dual ;--12345678.9

通用函数：

nvl(exp1, exp2)：exp1为空时输出exp2

nvl(exp1, exp2, exp3)：exp1不为空时输出exp2，为空输出exp3

nullif(exp1, exp2)：相等返回null，不等返回exp1

coalesce(exp1, exp2, exp...)：exp1为空返回exp2，exp2为空返回下一个。。。以此类推

条件表达式：

case exp when exp_1 then return_1 when exp_2 then return_2 else return_n end;

decode(exp , exp1, return_1, exp2 , return_2, default);

eg:

select e.first_name ,
      decode(e.job_id ,
             'AD_PRES', 'A',
             'ST_MAN', 'B',
             'IT_PROG', 'C',
             'SA_REP', 'D',
             'ST_CLERK', 'E',
             'F')
from employees e;
select e.first_name ,
       ( case e.job_id
         when 'AD_PRES ' then 'A'
         when 'ST_MAN' then 'B'
         when 'IT_PROG' then 'C'
         when 'SA_REP' then 'D'
         when 'ST_CLERK' then 'E'
         else 'F'
       end)
from employees e;

多表查询：

下面两条效果一样：

select e.first_name , d.department_name from employees e, departments d where e.department_id = d.department_id(+);

select e.first_name , d.department_name from employees e left outer join departments d on e.department_id = d.department_id ;

sql99语法：

cross join（会出现笛卡尔集）、natural join（自动匹配两个表中所有名字相同的列）

join...using()：会将指定的列作为两个表的连接条件，要求列名一致

select e.first_name , d.department_name from employees e join departments d using (department_id);

多行子查询：

in：等于列表中的任意一个

any：和子查询返回的某一个值比较，即所有的都要满足

all：和子查询返回的所有值比较

创建和管理表：

查看用户定义的表：select * from user_tables;

产看用户定义的各种数据库对象：select * from user_objects;

查看用户定义的表、同义词、视图、序列：select * from user_catalog;

创建表：create table dept(id number ( 7), name varchar ( 25));

根据表创建表：create table employees2 as select * from employees;

修改表：alter table employees modify (last_name varchar2( 50 ));

重命名表：alter table employees rename to employee2; rename employees to employees2;

清空表：delete my_employee ; delete from my_employee; truncate table my_employee;（前两个可以回滚，第三个不行）

删除表的一列：alter table employees drop column first_name;

添加新的一列：alter table aa add(column_add date);

重命名一个列：alter table tt rename column phone_number to pn ;

数据处理：（insert、update、delete操作都可以回滚）

向表中插入数据：insert into my_emp select * from employees; insert into my_emp( ..) values (..);

约束：（UNIQUE、NOTNULL、PRIMARY KEY、FOREIGN KEY、CHECK）声明为unique的可以添加null值。带有primary key和unique约束的列数据库自动添加索引

建表的时候加约束：

create table emp(
       id number ( 8),
       name varchar ( 23) constraint emp_name_uk unique null ,
       birth date ,
       salary number ( 8, 2 ) constraint emp_salary_ck check( salary > 2000 ),
       dept_id number (8),
       constraint emp_id_pk primary key(id ),
       constraint emp_dept_id_fk foreign key(dept_id ) references departments(department_id ) on delete cascade
       --on delete cascade、on delete set null。级联置空和级联删除
       --父表中作为子表外键的列如果删除则子表对应的外键置空或者整列删除
);

添加约束：除了添加not null的约束要用modify之外，其他约束的添加都用add

alter table emp modify(birth constraint emp_birth_nn not null);

alter table emp add constraint emp_name_uk unique(name );

使约束失效、有效：alter table empp disable constraint empp_name_uk;

alter table empp enable constraint empp_name_uk;

用命令行的方式查询约束：select constraint_name , constraint_type, search_condition from user_constraints where table_name = 'employees';

视图：（相当于存储起来的select语句）with read only设置视图为只读

赋予Scott创建视图的权限：grant create view to scott ;

创建视图：create or replace view emp_vu as select e.last_name , d.department_name from employees e join departments d on e.department_id = d.department_id with read only;

创建复杂视图：create or replace view emp_vu as select e.department_id , avg( e.salary ) "sal_avg" fromemployees e group by department_id ;

更新：update emp_vu set last_name = 'AAAA' where department_name = 'IT' ;

TOP-N写法：（注意：对于rownum只能使用<或<=，其他的均不返回任何数据）

select * from ( select employee_id , salary from employees order by salary desc ) where rownum <=10 ;

查询工资在前20-30的员工：

select *
    from (
           select rownum rn, employee_id , last_name, salary
            from ( 
                select employee_id, last_name, salary from employees order by salary desc 
                )
           )
      where rn <= 30 and rn > 20;

序列：（序列有nextval、currval两个伪列）（序列出现裂缝的情况：系统异常、回滚、多个表使用同一个序列）

create sequence emp_seq
  increment by 1
  start with 1
  maxvalue 10
  minvalue 1
  cycle    --是否需要循环
  cache 2   --是否缓存登陆

修改序列（只能修改非start with的信息）：

序列的使用：insert into emp( id, name , salary) values(emp_seq.nextval , 'xuanzang', 22.2);

索引：

create index employees_email_idx on employees (email);

drop index employees_email_idx;

同义词（synonym）：

create synonym emp for employees ;