Oracle SQL 基础复习：表管理、数据操作与多表查询

1
SELECT *
2
FROM tab;

1
SELECT column_name,
2
       data_type
3
FROM user_tab_columns
4
WHERE table_name = 'STUDENT';

1
INSERT INTO lecturer (...)
2
VALUES (1000, ...);

1
VALUES (2000, ...);

1
插入的主键值是否已经存在
2
业务上是否应该更新旧记录
3
是否误把新增操作写成重复插入

1
INSERT INTO student
2
VALUES (
3
    30001,
4
    TO_DATE('12-FEB-2002', 'DD-MON-YYYY'),
5
    'Alice',
6
    'Brown',
7
    'Melbourne',
8
    '3000',
9
    'Australia',
10
    5000.00,
11
    TO_DATE('15-JUL-2026', 'DD-MON-YYYY')
12
);

1
TO_DATE('12-MAR-2001 16:15', 'DD-MON-YYYY HH24:MI')

1
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI')
2
FROM dual;

1
ALTER TABLE student ADD (
2
    streetaddress VARCHAR2(70),
3
    suburb        VARCHAR2(40)
4
);

1
ALTER TABLE student DROP (citty);

1
ALTER TABLE student ADD email VARCHAR2(50);

1
ALTER TABLE student MODIFY (
2
    city VARCHAR2(40)
3
);

1
ALTER TABLE student ADD email VARCHAR2(50);
2
ALTER TABLE student DROP COLUMN postcode;

1
UPDATE student
2
SET streetaddress = '12 New St'
3
WHERE studentno = 30001;

1
UPDATE student
2
SET streetaddress = '12 New St';

1
SELECT *
2
FROM student
3
WHERE studentno = 30001;

1
COMMIT;

1
INSERT / UPDATE / DELETE 只是产生修改
2
COMMIT 才是确认保存
3
ROLLBACK 用于撤销未提交修改

1
CREATE TABLE subject AS
2
SELECT *
3
FROM dtaniar.subject;

1
CREATE TABLE student AS SELECT * FROM dtaniar.student;
2
CREATE TABLE lecturer AS SELECT * FROM dtaniar.lecturer;
3
CREATE TABLE lecture AS SELECT * FROM dtaniar.lecture;
4
CREATE TABLE tutor AS SELECT * FROM dtaniar.tutor;
5
CREATE TABLE lab AS SELECT * FROM dtaniar.lab;
6
CREATE TABLE student_enrolment AS SELECT * FROM dtaniar.student_enrolment;
7
CREATE TABLE lab_signup AS SELECT * FROM dtaniar.lab_signup;

1
SELECT COUNT(*)
2
FROM student;

1
SELECT subjectcode,
2
       name
3
FROM subject
4
WHERE semester = 1;

1
SELECT fname,
2
       lname,
3
       dob,
4
       feepaid
5
FROM student
6
WHERE dob > TO_DATE('31-DEC-1990', 'DD-MON-YYYY')
7
  AND dob < TO_DATE('01-JAN-1995', 'DD-MON-YYYY');

1
SELECT s.studentno,
2
       s.fname,
3
       s.lname,
4
       se.subjectcode
5
FROM student s
6
JOIN student_enrolment se
7
  ON s.studentno = se.studentno
8
WHERE se.subjectcode IN ('CSE21DB', 'CSE31DB', 'CSE41FDB')
9
ORDER BY se.subjectcode,
10
         s.studentno;

1
SELECT l.staffno,
2
       l.title,
3
       l.fname,
4
       l.lname,
5
       le.subjectcode,
6
       le.lectday,
7
       le.lecttime,
8
       le.venue
9
FROM lecturer l
10
LEFT JOIN lecture le
11
  ON l.staffno = le.staffno
12
ORDER BY l.staffno,
13
         le.lectday,
14
         le.lecttime;

1
SELECT l.staffno,
2
       l.title,
3
       l.fname,
4
       l.lname
5
FROM lecturer l
6
WHERE NOT EXISTS (
7
    SELECT *
8
    FROM lecture le
9
    WHERE le.staffno = l.staffno
10
);

1
对每一个 lecturer
2
检查 lecture 表里是否存在对应 staffno
3
如果不存在，就返回这个 lecturer

1
SELECT AVG(salary) AS averagesalary
2
FROM lecturer;

1
SELECT MIN(salary) AS minimumsalary,
2
       MAX(salary) AS maximumsalary
3
FROM lecturer;

1
SELECT SUM(l.duration * t.salaryperhour) AS totalweeklycost
2
FROM lab l
3
JOIN tutor t
4
  ON l.tutorno = t.tutorno
5
WHERE l.subjectcode IN ('CSE21DB', 'CSE31DB', 'CSE41FDB');

1
SELECT s.subjectcode,
2
       s.name,
3
       s.semester,
4
       COUNT(DISTINCT l.tutorno) AS numberoftutors
5
FROM subject s
6
JOIN lab l
7
  ON s.subjectcode = l.subjectcode
8
GROUP BY s.subjectcode,
9
         s.name,
10
         s.semester
11
ORDER BY s.subjectcode,
12
         s.semester;

1
s.subjectcode
2
s.name
3
s.semester

1
lab.tutorno → tutor.tutorno
2
tutor.studentno → student.studentno
3
lab.labno → lab_signup.labno

1
1. 先确定最终要显示哪些字段
2
2. 找出这些字段分别来自哪些表
3
3. 找出表和表之间的连接条件
4
4. 决定使用 INNER JOIN 还是 LEFT JOIN
5
5. 加 WHERE 过滤条件
6
6. 如需统计，再加 GROUP BY
7
7. 最后加 ORDER BY 排序

1
查看 schema 中已有表
2
创建表并设置主键
3
插入完整数据和部分数据
4
处理主键重复错误
5
使用 Oracle 日期函数
6
修改表结构
7
理解 CHAR 和 VARCHAR2 的区别
8
提交事务
9
从已有 schema 复制表
10
使用 JOIN 和 NOT EXISTS 查询关联数据
11
使用聚合函数和 GROUP BY 做统计