sql学习笔记

57次阅读

共计 6382 个字符，预计需要花费 16 分钟才能阅读完成。

查询最近一小时的数据：
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.20residual, 0.10residual, 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 ;

正文完
sql
发表至：无分类
2019-04-22
 0