项目6 实施图书借阅网站数据操作

6.1 任务1——数据基本操作

--- 6.2.1 插入数据 ---
-- 例 6.1 --

INSERT INTO USERS(ID,NAME,PWD) VALUES(1,'Mary','Mary');
INSERT INTO USERS VALUES(2,'Lily','Lily');
-- 插入全部列值，可以列出所有的列名，也可以不写列名 --
SELECT * FROM USERS;
-- 查看表中所有数据 --
INSERT INTO USERS(ID,NAME) VALUES(3,'Tony');
-- 插入部分列值，需要列出插值的列名，同时，插入的值需要跟前面的列相对应。 --
SELECT * FROM USERS;
-- 查看表中所有数据 --

--- 6.2.2 修改数据 ---
-- 例6.2 --

UPDATE USERS SET PWD = '123456' WHERE Name = 'Mary';
SELECT Name,Pwd FROM USERS;
-- 将用户Mary的密码修改为123456 --

--- 6.2.3 删除数据 ---
-- 例6.3 --

DELETE FROM USERS WHERE ID = 1;
-- 删除表USERS中列ID等于1的数据 --

-- 例6.4 --

DELETE FROM USERS;
-- 删除表USERS中全部数据 --

--- 6.2.4 合并数据 ---
-- 例6.5 --

CREATE TABLE NEW_BOOK
(
    ISBN CHAR(13),
    BookNo VARCHAR2(20),
    BookName VARCHAR2(30),
    BookWriter VARCHAR2(20),
    BookPublish VARCHAR2(30),
    Price Number(6,2),
    PublishDate Date,
    BookTypeID Number(6)
);
-- 创建表NEW_BOOK，表结构同BOOK表。 --
INSERT INTO BOOK(ISBN,BookNo,BookName,BookWriter,BookPublish,Price,PublishDate) VALUES ('9787730223456','TP211.1-3','Oracle数据库应用开发','王岩','北京理工大学出版社',32.5,date'2018-09-12');
INSERT INTO BOOK(ISBN,BookNo,BookName,BookWriter,BookPublish,Price,PublishDate) VALUES ('9787730224478','TP211.2-1','Java语言程序设计','王岩','清华大学出版社',35,date'2015-05-11');
-- 先往BOOK表中插入两条数据，此处未插入BOOKTYPEID的列值 --
INSERT INTO NEW_BOOK(ISBN,BookNo,BookName,BookWriter,BookPublish,Price,PublishDate) VALUES ('9787730223456','TP211.1-31','Oracle数据库应用开发','王岩','北京理工大学出版社',35,date'2019-03-14');
INSERT INTO NEW_BOOK(ISBN,BookNo,BookName,BookWriter,BookPublish,Price,PublishDate) VALUES ('9787730234578','TP211.2-11','C语言程序设计','王鹏','北京理工大学出版社',39,date'2019-09-12');
-- 如果插入BOOK表的BOOKTYPEID列值，必须先要在BOOKTYPE表的BOOKTYPEID列插入对应的列值，保证主外键列值相匹配或者外键值为NULL。 --
SELECT ISBN,BookName,BookPublish,Price,PublishDate FROM BOOK;
SELECT ISBN,BookName,BookPublish,Price,PublishDate FROM NEW_BOOK;
-- 查看两个表中的数据 --

MERGE INTO BOOK B
    USING NEW_BOOK NB
    ON (B.ISBN = NB.ISBN)
    WHEN MATCHED THEN
    UPDATE SET
    B.BOOKNO = NB.BOOKNO, B.BOOKNAME = NB.BOOKNAME, B.BOOKWRITER = NB.BOOKWRITER, B.BOOKPUBLISH = NB.BOOKPUBLISH, B.PRICE = NB.PRICE,
    B.PUBLISHDATE = NB.PUBLISHDATE, B.BOOKTYPEID = NB.BOOKTYPEID
    WHEN NOT MATCHED THEN
    INSERT(B.ISBN, B.BOOKNO, B.BOOKNAME, B.BOOKWRITER, B.BOOKPUBLISH, B.PRICE, B.PUBLISHDATE, B.BOOKTYPEID)
    VALUES(NB.ISBN, NB.BOOKNO, NB.BOOKNAME, NB.BOOKWRITER, NB.BOOKPUBLISH, NB.PRICE, NB.PUBLISHDATE, NB.BOOKTYPEID);
    -- 上述代码中BOOK B是为BOOK表起别名为B，NEW_BOOK NB是为NEW_BOOK表起别名为NB，以方便使用表 --
SELECT ISBN,BookName,BookPublish,Price,PublishDate FROM BOOK;
-- 查看BOOK表中数据 --

6.3 任务2——基本查询

--- 6.3.2 单表数据检索 ---
-- 例6.6 --

SELECT * FROM USERS;
-- 查看表USERS中所有数据 --

-- 例6.7 --

SELECT NAME,PWD FROM USERS;
-- 在SELECT语句中查询指定的列名 --

--- 6.3.3 条件检索 ---
--- 1.使用比较运算符 ---
-- 例6.8 --

SELECT ISBN,BOOKNAME FROM BOOK WHERE BOOKNAME = 'C语言程序设计'; -- 查询《C语言程序设计》一书的ISBN。 --
-- Oracle数据库系统默认的日期格式为“dd-mm月-YY”。对于日期型数据，日期越早，其值越小；日期越晚，其值越大。 --

-- 例6.9 --

SELECT BOOKNAME,PUBLISHDATE FROM BOOK
WHERE PUBLISHDATE>'1-1月-2018';
-- 查询2018年1月1号以后出版的图书信息。 --

--- 2.使用字符比较符 ---
-- 例6.10 --

SELECT ISBN,BOOKNAME FROM BOOK WHERE BOOKNAME LIKE '%程序设计%';
-- 从表BOOK中查询书名包含“程序设计”的图书信息 --

-- 例6.11 --

SELECT BOOKNAME,BOOKWRITER FROM BOOK WHERE BOOKWRITER LIKE '王%';
-- 从表BOOK中查询所有姓“王”的作者出版的图书。 --

-- 例6.12 --

SELECT BOOKNAME,BOOKWRITER FROM BOOK WHERE BOOKWRITER LIKE '_岩%';
-- 从表BOOK中查询所有姓名第二个字是“岩”的作者出版的图书 --

--- 3.使用逻辑运算符 ---
-- 例6.13 --

SELECT BOOKNAME,PUBLISHDATE FROM BOOK
WHERE BOOKNAME LIKE'%程序设计%' and PUBLISHDATE >'31-12月-18';
-- 从表BOOK中查询书名含有“程序设计”且2018年12月31日之后出版的图书信息 --

-- 例6.14 --

SELECT BOOKNAME,PUBLISHDATE FROM BOOK
WHERE BOOKNAME LIKE '%程序设计%' or PUBLISHDATE >'31-12月-18';
-- 从表BOOK中查询书名含有“程序设计”或2018年12月31日之后出版的图书信息 --

-- 例6.15 --

SELECT BOOKNAME,BOOKPUBLISH FROM BOOK
WHERE NOT BOOKPUBLISH = '北京理工大学出版社';
-- 从表BOOK中查询不是北京理工大学出版社出版的图书信息 --

--- 4.使用IN操作符 ---
-- 例6.16 --

SELECT BOOKNAME,BOOKPUBLISH FROM BOOK
WHERE BOOKPUBLISH IN('北京理工大学出版社','清华大学出版社');
-- 从表BOOK中查询清华大学出版社和北京理工大学出版社出版的图书信息 --

-- 例6.17 --

INSERT INTO BOOK(ISBN,BookNo,BookName,BookWriter,BookPublish,Price,PublishDate) VALUES ('9787730224459','TP211.3-1','数据库原理及应用','田元','电子工业出版社',45,date'2012-10-15');
-- 向BOOK中插入一条记录 --
SELECT BOOKNAME,BOOKPUBLISH FROM BOOK
WHERE BOOKPUBLISH NOT IN('北京理工大学出版社','清华大学出版社');
-- 查询 --

--- 5.使用BETWEEN……AND检索指定范围的值 ---
-- 例6.18 --

SELECT BOOKNAME,PRICE FROM BOOK
WHERE PRICE BETWEEN 35 AND 45;
-- 从表BOOK中查询图书单价在35~45之间（包含35和45）的图书信息。 --

--- 6.使用NULL检索空值 ---
-- 例6.19 --

SELECT BOOKNAME,BOOKTYPEID FROM BOOK
WHERE BOOKTYPEID IS NULL;
-- 从表BOOK中查询图书类别编号即BOOKTYPEID列为空值的图书信息 --

-- 例6.20 --

SELECT BOOKNAME,BOOKTYPEID FROM BOOK
WHERE BOOKTYPEID IS NOT NULL;
-- 从表BOOK中查询图书类别编号即BOOKTYPEID列为非空值的图书信息 --

6.4 任务3——设置结果集格式

--- 6.4.1 消除重复行 ---
-- 例6.21 --

SELECT DISTINCT BOOKPUBLISH FROM BOOK;
-- 从表BOOK中查询图书都是哪些出版社出版的（不显示重复的出版社名） --

--- 6.4.2 设置列别名 ---
-- 例6.22 --

SELECT BOOKNAME AS 书名, BOOKPUBLISH AS 出版社 FROM BOOK;
-- 在查询结果中，将BOOKNAME和BOOKPUBLISH列名显示为书名和出版社 --

--- 6.4.3 排序结果集 ---
-- 例6.23 --

SELECT BOOKNAME,PRICE FROM BOOK
ORDER BY PRICE ASC;
-- 在查询图书信息时，按照图书单价进行升序排序 --

-- 例6.24 --

SELECT BOOKNAME,PRICE FROM BOOK
ORDER BY PRICE DESC;
-- 在查询图书信息时，按照图书单价进行降序排序 --

6.5 任务4——分组查询

--- 6.5.1 聚合函数 ---
--- 1.使用COUNT(*)统计所有行数 ---

-- 例6.25 --
SELECT COUNT(*)FROM BOOK;
-- 查询表BOOK中的图书总数 --

--- 2.使用COUNT(列名)统计该列非空值行数 ---

-- 例6.26 --
SELECT COUNT(BOOKNAME)FROM BOOK;
-- 按书名统计图书总数 --

-- 例6.27 --
SELECT COUNT(BOOKTYPEID)FROM BOOK;
-- 按图书类型编号统计图书总数 --

--- 3.使用SUM(列名)统计该列非空值总和 ---

SELECT SUM(PRICE)FROM BOOK;
-- 统计BOOK表中所有图书单价总和 --

--- 4.使用AVG(列名)统计该列非空值的平均值 ---

-- 例6.29 --
SELECT AVG(PRICE)FROM BOOK;
-- 统计所有图书的平均单价 --

--- 5.使用MAX(列名)统计该列最大值 ---

-- 例6.30 --
SELECT MAX(PRICE)FROM BOOK;
-- 统计所有图书的最高单价 --

--- 6.使用MIN(列名)统计该列最小值 ---

-- 例6.31 --
SELECT MIN(PRICE)FROM BOOK;
-- 统计所有图书的最低单价 --

--- 6.5.2 使用GROUP BY字句 ---
-- 例6.32 --

SELECT BOOKPUBLISH,AVG(PRICE)FROM BOOK
GROUP BY BOOKPUBLISH;
-- 分类统计每个出版社出版图书的平均单价 --

--- 6.5.3 使用HAVING字句 ---
-- 例6.33 --

SELECT BOOKPUBLISH,AVG(PRICE)FROM BOOK
GROUP BY BOOKPUBLISH
HAVING AVG(PRICE) >40;
-- 分类统计每个出版社出版图书的平均单价，仅显示平均单价大于40的信息 --

--- 6.5.4 使用ORDER BY字句 ---
-- 例6.34 --

SELECT BOOKPUBLISH,AVG(PRICE)FROM BOOK
GROUP BY BOOKPUBLISH
ORDER BY AVG(PRICE);
-- 分类统计每个出版社出版图书的平均单价，升序显示 --

---- 6.6 任务5——连接查询 ----
--- 6.6.1 内连接 ---
-- 例6.35 --

SELECT BOOKNAME,BOOKTYPENAME
FROM BOOK JOIN BOOKTYPE
ON BOOK.BOOKTYPEID = BOOKTYPE.BOOKTYPEID;
-- 查询图书信息时，显示书名及所属类型名 --

--- 6.6.2 外连接 ---
--- 1.左外连接 ---
-- 例6.36 --

SELECT BOOKNAME,BOOKTYPENAME
FROM BOOK LEFT JOIN BOOKTYPE
ON BOOK.BOOKTYPEID = BOOKTYPE.BOOKTYPEID;
-- 查询图书信息时，显示所有书名及所属图书类型名信息。对于没有类型的图书，也将其书名显示出来。 --

--- 2.右外连接 ---
-- 例6.37 --

SELECT BOOKNAME,BOOKTYPENAME
FROM BOOK RIGHT JOIN BOOKTYPE
ON BOOK.BOOKTYPEID = BOOKTYPE.BOOKTYPEID;

-- 3.完全外连接 --
-- 例6.38 --

SELECT BOOKNAME,BOOKTYPENAME
FROM BOOK FULL JOIN BOOKTYPE
ON BOOK.BOOKTYPEID = BOOKTYPE.BOOKTYPEID;

--- 6.6.3 交叉连接 ---
-- 例6.39 --

SELECT BOOKNAME,BOOKTYPENAME
FROM BOOK CROSS JOIN BOOKTYPE;
-- 显示所有图书可能的类型集合 --

--- 6.6.4 自连接 ---
-- 例6.40 --

create table Employee
(
    id number,
    name varchar2(20),
    leaderid number
);
-- 创建员工表Employee --
insert into Employee values(1,'Lily',NULL);
insert into Employee values(2,'Maly',1);
insert into Employee values(3,'Song',1);
-- 插入数据 --
select e.Name,em.Name as leadername
from Employee e left join Employee em
on e.leaderid = em.id
where e.Name = 'Song';
-- 查询员工Song的领导姓名 --

6.7 任务6——集合查询

--- 6.7.1 使用UNION ALL操作符 ---
-- 例6.41 --

SELECT ISBN,BOOKNAME FROM BOOK
UNION ALL
SELECT ISBN,BOOKNAME FROM NEW_BOOK;
-- 查询BOOK和NEW_BOOK表中的所有图书编号和书名 --

--- 6.7.2 使用UNION操作符 ---
-- 例6.42 --

SELECT ISBN,BOOKNAME FROM BOOK
UNION
SELECT ISBN,BOOKNAME FROM NEW_BOOK;
-- 查询BOOK和NEW_BOOK表中的所有图书编号和书名（去掉重复行） --

--- 6.7.3 使用INTERSECT操作符 ---
-- 例6.43 --

SELECT ISBN,BOOKNAME FROM BOOK
INTERSECT
SELECT ISBN,BOOKNAME FROM NEW_BOOK;
-- 查询BOOK表和NEW_BOOK表中共同拥有的图书编号和书名 --

--- 6.7.4 使用MINUS操作符 ---
-- 例6.44 --

SELECT ISBN,BOOKNAME FROM BOOK
MINUS
SELECT ISBN,BOOKNAME FROM NEW_BOOK;
-- 查询在BOOK中而不在NEW_BOOK表中的图书ISBN号和书名 --

6.8 任务7——子查询

--- 6.8.1 单行子查询 ---
-- 例6.45 --

SELECT ISBN,BOOKNAME,PRICE FROM BOOK
WHERE PRICE = (SELECT MAX(PRICE)FROM BOOK);
-- 查询单价最高的图书ISBN和书名信息 --

--- 6.8.2 多行子查询 ---
--- 1.使用IN操作符 ---
-- 例6.46 --

SELECT ISBN,BOOKNAME,BOOKPUBLISH,PRICE
FROM BOOK OUTER
WHERE PRICE IN(SELECT MAX(PRICE)
            FROM BOOK INNER
            WHERE OUTER.BOOKPUBLISH = INNER.BOOKPUBLISH)
ORDER BY PRICE;
-- 检索每个出版社中单价最高的图书信息 --

--- 2.使用ANY操作符 ---
-- 例6.47 --

SELECT ISBN,BOOKNAME,BOOKPUBLISH,PRICE FROM BOOK
WHERE PRICE >ANY(SELECT AVG(PRICE)FROM BOOK GROUP BY BOOKPUBLISH);
-- 检索单价高于任何一个出版社出版图书平均价格的图书信息 --
SELECT AVG(PRICE) FROM BOOK GROUP BY BOOKPUBLISH;
-- 每个出版社出版图书平均价格查询 --

--- 3.使用ALL操作符 ---
-- 例6.48 --

SELECT ISBN,BOOKNAME,BOOKPUBLISH,PRICE FROM BOOK
WHERE PRICE >ALL(SELECT AVG(PRICE)FROM BOOK GROUP BY BOOKPUBLISH);
-- 检索单价高于所有出版社出版图书平均价格的图书信息 --

--- 6.8.3 多列子查询 ---
-- 例6.49 --

SELECT BOOKNAME,BOOKPUBLISH,PRICE FROM BOOK
WHERE(BOOKPUBLISH,PRICE) IN (SELECT BOOKPUBLISH,MAX(PRICE)
    FROM BOOK GROUP BY BOOKPUBLISH);
-- 检索每个出版社所有出版的单价最高的图书信息 --
署名-非商业性使用-禁止演绎 4.0 国际 协议（CC BY-NC-ND 4.0）
最后修改：2022 年 12 月 23 日
© 禁止转载
如果觉得我的文章对你有帮助，可以点个赞再走喔