Oracle数据库设计学习总结

SQL 笔记

ch3_cn

1.数据类型记录

char(n) 定长字符

varchar(n) 可变长字符

numeric(p,d) 定点数，总位数p，小数点后位数q

float(n) n位浮点数

2.建表

create table table_name(
      id    varchar(8),
      dept_name    varchar(30),
      grade  numeric(2,1),
      primary key (id),
      foreign key(dept_name) references department);

3.表的更新

--插入（1）
insert into course         
values ('CS-437', 'Database Systems', 'Comp. Sci.');
--插入（2）
insert into course (course_id, title, dept_name, credits)
values ('CS-437', 'Database Systems', 'Comp. Sci.', null);
--更新(一般)
update instructor
set salary = salary * 1.05
where salary < 70000;
--更新（case表达式）
UPDATE instructor
   SET salary =
          CASE
             WHEN salary <= 100000 THEN salary * 1.05
             ELSE salary * 1.03
          END
--删除(系统一行一行删)
delete from student (where)...
--删除(删除内容定义，释放空间)
drop table student

4.表的查询

select A1,A2,...,An
from r1,r2,...,rm
where p

Ai 属性 ri表 P逻辑谓词（sql中大小写不敏感）

5.是否去重及其他常见

--去重
select distinct name
from student
--不去重
select all name
from student
--所有属性
select * from student
--Null
--is null 和 is not null

6.字符串匹配运算like 通配符

% 若干任意字符

__ 一个任意字符

\ 转义前缀符

7.排序

（1）desc 降序 asc 升序（默认）

（2）多列排序（第一优先级，第二优先级……）

（3）可用数字指代（第几列）

8.between 比较运算（Oracle不支持）

x between a and b 相当于 x >= a and x <= b

9.集合运算

（1）并 union （或，和）

（2）交 intersect （都，且）

（3）差 except (oracle用minus代替) （a有，但b没有）

10.聚集函数

avg :平均值（只能用于数字）

min :最小值

max :最大值

sum :求和（只能用于数字）

count :计数（会忽略空值行）

11.聚集函数常见子句

（1）having 子句和 group by 子句

--查询所有平均工资超42000的院系和平均工资
select dept_name, avg (salary) as avg_salary
from instructor
group by dept_name
having avg (salary) > 42000;

Tips : where子句在分组之前过滤，having子句在分组之后过滤

12.嵌套子查询 标量子查询

（1）SQL提供嵌套子查询的机制。子查询就是在select-from-where 表达式中嵌入另外一个查询。

（2）标量子查询：返回单值的子查询。

13.集合成员 集合比较

（1）in 和 not in

（2）some（<,  小于等于,  >,大于等于,  =,不相等 ）

（3）all （<,  小于等于,  >,大于等于,  =,不相等 ）

14.空集测试

（1）exists r —> r不为空

（2）not exists r —> r为空

（3）使用not exists 替代not in ， not exists 的执行效率更高

15.With子句（Mysql 不支持）

(1)with子句提供一个方法，可以定义一个临时的视图，这个关系只用于 with子句存在的这个查询中。

(2)当你需要对子查询多次引用的时，用with子句，否则没有必要。

--查询每年课程的授课人次超过本课程开设院在本年开设课程的修课总人数
--1/3的课程名，年份，修课人次，本院课程总修课人次，所占百分比
WITH ta
     AS (  SELECT title,
                  year,
                  dept_name,
                  COUNT (*) cnt
             FROM takes NATURAL JOIN course
         GROUP BY title, year, dept_name),
     tb
     AS (  SELECT dept_name, year, SUM (cnt) total
             FROM ta
         GROUP BY dept_name, year)
SELECT ta.title,
       ta.year,
       ta.dept_name,
       cnt,
       total,
       ROUND (cnt / total * 100, 0) || '%' ratio
  FROM ta, tb
 WHERE     ta.dept_name = tb.dept_name
       AND ta.year = tb.year
       AND ta.cnt * 3 > total;

16. 交叉表查询

--Oracle特有
SELECT *
  FROM scores PIVOT (MAX (score) FOR
  course IN ('C', 'DB', 'English'));
--通用
select name,
max(case course when 'C' then score else null end) C,
max(case course when 'DB' then score else null end) DB,
max(case course when 'Eng' then score else null end) Eng
from scores
group by name;

（1）case表达式，oralce可以用decode函数，mysql可以用if函数替代

ch4_cn

1.联结

（1）内联 Inner join    联结 join    外联 Outer join