Oracle SQL基础

Oracle SQL基础

SQL的分类

1、数据库查询语言（DQL）

Data Query Language基本结构是由SELECT子句，FROM子句，WHERE 子句组成的查询块，代表关键字为select。

2、数据库操作语言（DML）

Data Manipulation Language，用户通过它可以实现对数据库的基本操作，代表关键字为insert、delete 、update。

3、数据库定义语言（DDL）

Data Denifition Language，数据定义语言DDL用来创建数据库中的各种对象，创建、删除、修改表的结构，比如表、视图、索引、同义词、聚簇等，代表关键字为create、drop、alter。

和DML相比，DML是修改数据库表中的数据，而 DDL 是修改数据中表的结构。

4、事务控制语言（TCL）

Trasactional Control Languag经常被用于快速原型开发、脚本编程、GUI和测试等方面，代表关键字为commit、rollback。

5、数据控制语言（DCL）

Data Control Language，数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等，代表关键字为grant、revoke。

范式Normal Form（数据库设计范式）是符合某一种级别的关系模式的集合。

数据库概念

范式

第一范式：

数据库表中的字段都是单一属性的，不可再分。

这个单一属性由基本类型构成，包括整型、小数型、字符型、日期型等。

第二范式:

所有非关键字段都完全依赖于任意一组候选关键字字段（主键）。

如学生表，

符合2NF的，但是不符合3NF，因为存在如下决定关系：

(学号) → (所在学院) → (学院地点，学院电话)

即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。

它也会存在数据冗余、更新异常、插入异常和删除异常的情况。

第三范式:

在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖，则符合第三范式。

所谓传递函数依赖，指的是如果存在"A → B → C"的决定关系，则C传递函数依赖于A。

3NF：

可以把学生关系表分为如下两个表：

学生：(学号，姓名,年龄，所在学院)；

学院：(学院，地点，电话)。

这样的数据库表是符合第三范式的。

但实际使用过程中，为了避免一些频繁关联系统开销，数据库设计也允许一些适当的冗余。

学生表

学院表

事务

数据库事务( transaction)是访问并操作数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

事务特性（ACID）

· 原子性(Atomicity)：一个事务里面所有包含的SQL语句是一个执行整体，不可分割，要么都做，要么都不做。

· 一致性(Consistency)：事务开始时，数据库中的数据是一致的，事务结束时，数据库的数据也应该是一致的。

· 隔离性(Isolation)：是指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力，隔离性可以防止事务的并发执行时，由于他们的操作命令交叉执行而导致的数据不一致状态。

· 持久性 (Durability) : 是指当事务结束后，它对数据库中的影响是永久的，即便系统遇到故障的情况下，数据也不会丢失。

脏读

当一个事务修改数据时，另一事务读取了该数据，但是第一个事务由于某种原因取消对数据修改，使数据返回了原状态，这是第二个事务读取的数据与数据库中数据不一致，这就叫脏读。

不可重复读

是指一个事务读取数据库中的数据后，另一个事务则更新了数据，当第一个事务再次读取其中的数据时，就会发现数据已经发生了改变，这就是不可重复读取。不可重复读取所导致的结果就是一个事务前后两次读取的数据不相同。

幻读

如果一个事务基于某个条件读取数据后，另一个事务插入了满足条件的数据并且提交了，这时第一个事务再次读取数据时，根据搜索的条件返回了不同的行，这就是幻读。

4个事务的隔离级别：

锁

为什么要加锁：

加锁的目的，其实是为了保证数据的一致性。 当多个线程并发访问某个数据时，加锁，可以保证这个数据在任何时刻最多只有一个线程在访问，保证数据的完整性和一致性。

锁的分类：

1.按照锁粒度划分，可以将锁划分成 行锁，页锁和表锁。

行锁

行锁，就是按照行的粒度对数据进行锁定，锁定粒度小，发生锁冲突概率低，可以实现并发都高，但是对于锁的开销比较大，加上会比较慢，容易出现死锁的情况。

页锁

页锁就是页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录，当我们使用页锁的时候，会出现数据浪费的现象，页锁的开销介于表锁行锁之间。

表锁

表锁就是对数据进行锁定，锁定粒度很大，发送锁的概率很高，数据访问的并发度。不过好处在于对锁的使用开销小，加锁会很快。

2.按照数据库管理角度划分，可以将锁分成排他锁和共享锁。

共享锁，也叫读锁，或者S锁，共享锁锁定的资源可以被其他用户读取，但不能修改。在进行 SELECT 的时候，会将对象进行共享锁锁定，当数据读取完毕之后，就会释放共享锁，这样就可以保证数据在读取时不被修改。

排他锁也叫做独占锁，写锁或者 X 锁，排他锁锁定的数据只允许进行锁定操作的事务使用，其他事务无法对已锁定的数据进行查询或者修改。

3.从程序员角度进行划分，可以分为乐观锁，悲观锁。

乐观锁适合读操作多的场景，相对来说写的操作⽐较少。它的优点在于程序实现，不存在死锁问题，不过适⽤场景也会相对乐观，因为它阻⽌不了除了程序以外的数据库操作。

悲观锁适合写操作多的场景，因为写的操作具有排它性。采⽤悲观锁的⽅式，可以在数据库层⾯阻⽌其他事务对该数据的操作权限，防⽌读-写和写-写的冲突。

视图

视图特点：

•简单：因为视图是查询语句执行后返回的已经过滤好的复合条件的结果集，所以使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件。

•安全：使用视图的用户只能访问他们被允许查询的结果集，对于表的权限管理并不能限制到某个行或者某个列，但是通过视图就可以简单的实现。

•数据独立:一旦视图的结构被确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

根据数据库的功能，可以在数据库设计器中创建四种索引：单列索引、唯一索引、主键索引、位图索引。

索引优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

索引缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

Schema

一般来说数据库的schema指的是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。

为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的schema名等于用户名，并作为该用户缺省schema。所以schema集合看上去像用户名。

SQL语句操作

连接Oracle数据库，在cmd命令行

sqlplus / as sysdba
1

退出sql命令行：

exit
1

创建用户kkk，密码也为kkk

create user kkk identified by kkk;
1

改密码为88888888

alter user kkk identified by 88888888;
1

给予普通用户的权限

grant connect,resource to kkk;
1

用用户名和密码连接

connect kkk/88888888
1

查看当前用户

show user
1

授予dba权限

grant dba to kkk;
1

创建角色，名字为manager

create role manager;
1

给创建的角色赋予权限

grant create table,create view to manager;
1

授予kkk用户manager的角色

grant manager to kkk;
1

退出exit，用自己的用户登录。

sqlplus kkk/88888888
1

创建表dept，并定义字段

create table dept(
    deptno number(2),
    dname varchar2(14),
    loc varchar2(13),
    create_date date);
1
2
3
4
5

查看表结构

describe dept;
1

插入表数据（按顺序）

insert into dept values(1,'yuinjisuan','beijing','28-8月-22');

insert into dept values(2,'caiwu','shanghai','29-8月-22');

 insert into dept values(3,'jishubu','shenzhen','30-8月-22');
1
2
3
4
5

提交

commit;
1

查看表全部数据

select * from dept;
1

建表时，使用默认系统时间

create table dept2 (
    deptno number(2)
    ,dname varchar2(14)
    ,loc varchar2(13)
    ,create_date date default sysdate);
1
2
3
4
5

插入数据（可以不按顺序，指定插入）

insert into dept2(deptno,dname,loc) values (1,'yunjisuan','beijing');
insert into dept2(deptno,dname,loc)  values (2,'caiwubu','shanghai');
insert into dept2(deptno,dname,loc)  values (3,'jishubu','shenzhen');
1
2
3

提交，插入数据后要进行commit，否则会插入失败

commit；
1

查看表全部数据

select * from dept2;
1

可使用sqldeveloper操作oracle数据库

左上角加号新建连接

用户名填刚才的kkk

密码为之前的88888888

主机名填localhost

在cmd中查看服务名和端口号

lsnrctl status
1

填入端口号，并在SID中填入服务名，测试成功便可连接。

约束

行级约束（在行后加约束）

create table employees_pk(
    employee_id number constraint emp_emp_id_pk primary key,
    last_name VARCHAR2(25) not null,
    email varchar2(25),
    salary number(8,2),
    hire_date date not null);
1
2
3
4
5
6

表级约束

create table employees_pk2(
employee_id number(6),
last_name VARCHAR2(25),
email varchar2(25),
salary number(8,2),
commission_pct number(8,2),
hire_date date not null,
CONSTRAINT emp_emp_id_pk2 primary key(employee_id));
1
2
3
4
5
6
7
8

唯一约束（保证数据唯一，但可以为空）

（主键约束保证数据唯一，但不能为空）

CREATE TABLE employees_uq(
    employee_id      NUMBER(6)
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL  
    ,CONSTRAINT emp_email_uk UNIQUE(email));
1
2
3
4
5
6
7
8

外键约束

create table t_customer(   	
	id number primary key  
	,name varchar2(200) not null
);

insert into t_customer values (10,'Fay');
insert into t_customer values (11,'Whalen');
insert into t_customer values (12,'Higgins');
insert into t_customer values (13,'Ciets');
commit;

create table t_order(   
	id number primary key   
	,content varchar2(200) not null 
	,customer_id number   
	,foreign key(customer_id) references t_customer(id)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

删除表时，若表中字段为其他表的外键，直接删除失败
级联删除此表和有外键约束的表

drop table t_customer cascade constraints;
1

检查约束

CREATE TABLE employees_ck(
    employee_id      NUMBER(6)
    ,last_name        VARCHAR2(25) NOT NULL
    ,email            VARCHAR2(25)
    ,salary           NUMBER(8,2) CONSTRAINT emp_salary_min CHECK (salary > 0)
    ,commission_pct   NUMBER(8,2)
    ,hire_date        DATE NOT NULL);
1
2
3
4
5
6
7

联合约束必须使用表级约束来声明
非空约束(not null)必须使用行级约束
例如联合唯一约束：

create table student(   
	id number primary key,  
	class varchar2(50) not null,   
	name varchar2(50) not null,   
	unique(class,name)
);
1
2
3
4
5
6

给表添加字段，alter 表名 add 字段名 类型

alter table t_user add birthday date;
1

给表添加约束

alter table t_user add constraint user_name_uq unique(name);
1

删除表中的约束

alter table t_user drop constraint user_name_uq;
1

修改表名

rename t_user to mytest;
1

修改字段长度

alter table t_user modify name varchar2(200);
1

删除表（表结构和数据都被删除）

drop table t_user;
1

清空表（表结构还在，数据被删除）

truncate table dept;
1

索引

建表时创建主键索引

CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY
    ,first_name  VARCHAR2(20)
    ,last_name   VARCHAR2(25)
    );
1
2
3
4
5

建表后创建索引

CREATE INDEX idx_emp_id_last_name ON NEW_EMP(employee_id, last_name);
1

建表后删除索引

DROP INDEX idx_emp_id_last_name;
1

查看索引信息

SELECT * FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP';
1

新增数据

insert into employees values (200,'King','king2@qq.com',3000,2,TO_DATE('8月 03, 2022', 'MON DD, YYYY'));
1

修改数据

update employees set salary=salary-200;
select * from employees where employee_id=200;
update employees set salary=salary-200 where employee_id=200;
1
2
3

删除数据

delete from employees where salary>4000;
1

查询数据

select * from employees;
1

查询指定字段

select last_name ,salary from employees;
1

运算操作

select employee_id,last_name ,salary, 12*(salary+100) from employees;
1

拼接字段值，字段别名

SELECT last_name ||' salary is '||salary  AS "Employee Salary" FROM   employees;
1

比较运算符

select * from employees where salary>3000;

select * from employees where last_name='Kochhar';

SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ;

SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id IN (100, 101, 201) ;
1
2
3
4
5
6
7
8
9

使用Like操作进行模式匹配
_代表一个字符
%代表0或者多个字符

查询首字母为大写S的名字

SELECT	*
FROM 	employees
WHERE 	last_name LIKE 'S%' ;
1
2
3

查询第二个字母为o的名字

SELECT *
FROM   employees
WHERE  last_name LIKE '_o%' ;
1
2
3

查询空值

SELECT last_name, commission_pct
FROM   employees
WHERE  commission_pct IS NULL ;
1
2
3

逻辑操作符，包含ch子串

SELECT employee_id, last_name,  salary
FROM employees
WHERE salary >= 5000
AND last_name LIKE '%ch%' ;
1
2
3
4

排序

查询所有结果中前五行的信息

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;
1
2
3
4

查询所有结果中，先跳过前5行后，展示后面的5条数据

SELECT employee_id, last_name
FROM employees
ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
1
2
3
4

组函数

查询8月29日的平均工资，最大工资，最小工资，工资总和

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees
WHERE hire_date ='29-8月-22';
1
2
3

查看表中的所有记录数

SELECT COUNT(*)
FROM   employees;
1
2

使用DISTINCT关键字去重

SELECT COUNT(DISTINCT last_name)
FROM   employees;
1
2

分组GROUP BY，按字段进行分组，前面查询的字段必须出现在GROUP BY后面

SELECT last_name, AVG(salary)
FROM employees
GROUP BY last_name ;
1
2
3

表连接

FULL JOIN全连接:它表示返回两个表全部记录,表示两个表的所有记录

select * 
from employees em1
full join employees_pk em2
on em1.employee_id=em2.employee_id;
1
2
3
4

INNER JOIN内连接：它表示返回两个表或记录集连接字段的匹配记录,表示两个表中相互包含的部分

LEFT JOIN左连接：左外连接又叫左连接,意思是包含左边表所有记录,右边所有的匹配的记录,如果没有则用空补齐.换句话说就是,列出左边表全部的,及右边表符合条件的,不符合条件的以空值代替

RIGHT JOIN右连接

Oracle练习题

-- BORROW 借书统计表 ： CNO 借书卡号，BNO 书编号，OUTDATE 借出日期，RDATE 归还日期
CREATE TABLE BORROW
	 (  
	 CARD_NO NUMBER(10),  
	 BOOK_NO NUMBER(20),  
	 OUT_DATE   DATE, 
	 RETURN_DATE DATE,
	 CONSTRAINT TB_PK_BORROW PRIMARY KEY(CARD_NO,BOOK_NO,OUT_DATE)
	 ) ;
 
INSERT INTO BORROW VALUES (10000,1000001,SYSDATE-60,SYSDATE-50);
INSERT INTO BORROW VALUES (10000,1000002,SYSDATE-50,SYSDATE-40);
INSERT INTO BORROW VALUES (10000,1000003,SYSDATE-40,SYSDATE-30);
INSERT INTO BORROW VALUES (10000,1000004,SYSDATE-30,SYSDATE-20);
INSERT INTO BORROW VALUES (10000,1000015,SYSDATE-20,SYSDATE-10);
INSERT INTO BORROW VALUES (10000,1000017,SYSDATE-60,SYSDATE-50);
INSERT INTO BORROW VALUES (10001,1000001,SYSDATE-50,SYSDATE-40);
INSERT INTO BORROW VALUES (10002,1000001,SYSDATE-40,SYSDATE-30);
INSERT INTO BORROW VALUES (10100,1000001,SYSDATE-30,SYSDATE-20);
INSERT INTO BORROW VALUES (10109,1000001,SYSDATE-15,'');
INSERT INTO BORROW VALUES (10001,1000019,SYSDATE-10,SYSDATE);
INSERT INTO BORROW VALUES (10100,1000015,SYSDATE-8,SYSDATE);
INSERT INTO BORROW VALUES (10109,1000013,SYSDATE-30,SYSDATE-20);
INSERT INTO BORROW VALUES (10101,1000007,SYSDATE-30,SYSDATE-5);
INSERT INTO BORROW VALUES (10106,1000007,SYSDATE-15,'');
INSERT INTO BORROW VALUES (10109,1000016,SYSDATE-20,SYSDATE-10);
INSERT INTO BORROW VALUES (10001,1000016,SYSDATE-15,SYSDATE-5);
INSERT INTO BORROW VALUES (10001,1000013,SYSDATE-35,SYSDATE-25);
INSERT INTO BORROW VALUES (10105,1000016,SYSDATE-20,SYSDATE-10);
INSERT INTO BORROW VALUES (10107,1000013,SYSDATE-35,SYSDATE-25);
INSERT INTO BORROW VALUES (10107,1000014,SYSDATE-35,SYSDATE-25);
INSERT INTO BORROW VALUES (10008,1000014,SYSDATE-35,SYSDATE-25);
COMMIT;



-- CARD借书卡：  CNO 卡号，NAME 姓名，CLASS 班级 
CREATE TABLE CARD
	 (  
	 CARD_NO NUMBER(10),  
	 NAME VARCHAR(100),  
   CLASS   VARCHAR2(100),
   CONSTRAINT TB_PK_CARD PRIMARY KEY(CARD_NO)
	 ) ;
	 
INSERT INTO CARD VALUES (10000,'张斯瑞','X01');
INSERT INTO CARD VALUES (10001,'王图','X01');
INSERT INTO CARD VALUES (10002,'豆豆','X01');
INSERT INTO CARD VALUES (10003,'杨康','X01');
INSERT INTO CARD VALUES (10004,'杨过','X01');
INSERT INTO CARD VALUES (10005,'郭靖','X01');
INSERT INTO CARD VALUES (10006,'黄蓉','X01');
INSERT INTO CARD VALUES (10007,'萧峰','X01');
INSERT INTO CARD VALUES (10008,'段誉','X01');
INSERT INTO CARD VALUES (10009,'王语嫣','X01');
INSERT INTO CARD VALUES (10100,'李福','C01');
INSERT INTO CARD VALUES (10101,'韦小宝','C01');
INSERT INTO CARD VALUES (10102,'陈珂','C01');
INSERT INTO CARD VALUES (10103,'福尔康','C01');
INSERT INTO CARD VALUES (10104,'胡斐','C01');
INSERT INTO CARD VALUES (10105,'任盈盈','C01');
INSERT INTO CARD VALUES (10106,'令狐冲','C01');
INSERT INTO CARD VALUES (10107,'风清扬','C01');
INSERT INTO CARD VALUES (10108,'岳不群','C01');
INSERT INTO CARD VALUES (10109,'左冷禅','C01');
COMMIT;


-- BOOKS 图书：  BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数  
CREATE TABLE BOOKS
	 (   
	 BOOK_NO NUMBER(20),  
	 BOOK_NAME VARCHAR(100),  
	 AUTHOR  VARCHAR(100),
	 PRICE   NUMBER(10,2),
	 QUANTITY  NUMBER(10),
	 CONSTRAINT TB_PK_BOOKS PRIMARY KEY(BOOK_NO)
	 ) ;

INSERT INTO BOOKS VALUES (1000001,'红楼梦','曹雪芹',220.5,25);
INSERT INTO BOOKS VALUES (1000002,'水浒','施耐庵',120.0,5);
INSERT INTO BOOKS VALUES (1000003,'三国演义','罗贯中',200.5,15);
INSERT INTO BOOKS VALUES (1000004,'西游记','吴承恩',180.5,5);
INSERT INTO BOOKS VALUES (1000005,'笑傲江湖','金庸',35.5,5);
INSERT INTO BOOKS VALUES (1000006,'天龙八部','金庸',35.5,5);
INSERT INTO BOOKS VALUES (1000007,'三体','刘慈欣',100.5,5);
INSERT INTO BOOKS VALUES (1000008,'消费者行为','迈克尔-R-所罗门',150.5,30);
INSERT INTO BOOKS VALUES (1000009,'影响力','西奥迪尼',160.5,25);
INSERT INTO BOOKS VALUES (1000010,'失控','凯文凯利',160.5,25);
INSERT INTO BOOKS VALUES (1000011,'投资学原理','布拉德福特,托马斯,史蒂文',180.5,25);
INSERT INTO BOOKS VALUES (1000012,'组织行为学','罗宾斯',110.5,25);
INSERT INTO BOOKS VALUES (1000013,'计算方法','孙陶然',100.5,25);
INSERT INTO BOOKS VALUES (1000014,'计算方法习题集','安佳',80.5,25);
INSERT INTO BOOKS VALUES (1000015,'网络','赵玉平',150.5,25);
INSERT INTO BOOKS VALUES (1000016,'组合数学','王德平',80.5,25);
INSERT INTO BOOKS VALUES (1000017,'计算机网络','吕一林',210.5,25);
INSERT INTO BOOKS VALUES (1000018,'数据库','拉里埃里森',180.5,25);
INSERT INTO BOOKS VALUES (1000019,'TCP/IP 网络协议','吉姆-兰德尔',190.5,25);
INSERT INTO BOOKS VALUES (1000020,'JAVA编程','雷德-海斯帝',200.5,25);
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100

1、 找出借书超过5本的读者,输出借书卡号及所借图书册数。

-- COUNT(*)查询有多少行借书记录，或者可以使用COUNT(BOOK_NO)
SELECT CARD_NO AS "借书卡号",COUNT(*) AS "所借图书册数" FROM BORROW
GROUP BY CARD_NO 
HAVING COUNT(BOOK_NO)>5;
1
2
3
4

2、 查询借阅了"红楼梦"一书的读者，输出姓名及班级。

方法一：嵌套查询，嵌套语句在生产中使用较少，性能较差

-- 第三步:查读者的姓名和班级
SELECT NAME,CLASS FROM CARD WHERE CARD_NO IN(
    -- 第二步:查哪些卡号借了红楼梦
    SELECT a.CARD_NO FROM BORROW a WHERE a.BOOK_NO IN(
        -- 第三步:查红楼梦的BOOK_NO
        SELECT BOOK_NO FROM BOOKS WHERE BOOK_NAME = '红楼梦'
    )
);
1
2
3
4
5
6
7
8

方法二：一般使用关联查询。

SELECT c.NAME,c.CLASS
FROM BORROW a,BOOKS b,CARD c
WHERE a.CARD_NO = c.CARD_NO
AND b.BOOK_NO = a.BOOK_NO
AND b.BOOK_NAME = '红楼梦';
1
2
3
4
5

3、 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。（设定10天为还书期限）

SELECT CARD_NO,BOOK_NO,OUT_DATE,RETURN_DATE
FROM BORROW
WHERE OUT_DATE IS NOT NULL
AND RETURN_DATE IS NULL
-- SYSDATE为当前日期
AND SYSDATE > OUT_DATE + 10;
1
2
3
4
5
6

注：查询当前时间，精确到秒

SELECT TO_CHAR(SYSDATE,'yyyymmdd hh24:mi:ss') FROM DUAL;
1

4、 查询书名包括"网络"关键词的图书，输出书号、书名、作者。

SELECT BOOK_NO,BOOK_NAME,AUTHOR FROM BOOKS
WHERE BOOK_NAME LIKE '%网络%';
1
2

5、 查询现有图书中价格最高的图书，输出书名及作者。

SELECT BOOK_NAME,AUTHOR FROM BOOKS
WHERE PRICE IN (
	SELECT MAX(PRICE) FROM BOOKS
);
1
2
3
4

6、 查询借阅图书的前5名

SELECT * FROM(
 	SELECT BOOK_NO,COUNT(OUT_DATE)
 	FROM BORROW
 	GROUP BY BOOK_NO
 	-- 2代表按照第二个字段COUNT(OUT_DATE)排序
 	ORDER BY 2 DESC
) 
WHERE ROWNUM < 6;
1
2
3
4
5
6
7
8

7、 从BOOKS表中查看当前无人借阅的图书记录。

SELECT BOOK_NO,BOOK_NAME FROM BOOKS
WHERE BOOK_NO NOT IN(
	SELECT BOOK_NO FROM BORROW
);
1
2
3
4

8、查询过期还书（设定10天为还书期限）

SELECT CARD_NO,BOOK_NO,OUT_DATE,RETURN_DATE
FROM BORROW
WHERE OUT_DATE IS NOT NULL
AND RETURN_DATE IS NOT NULL
AND RETURN_DATE > OUT_DATE + 10;
1
2
3
4
5

9、显示"X01"班学生的借书信息（只要求显示姓名和书名）。

SELECT c.NAME,b.BOOK_NAME
FROM BORROW a,BOOKS b,CARD c
WHERE a.BOOK_NO = b.BOOK_NO
AND a.CARD_NO = c.CARD_NO
AND c.class = 'X01';
1
2
3
4
5

10、按班级统计和书名统计借书情况。

SELECT c.CLASS,b.BOOK_NAME,COUNT(a.OUT_DATE)
FROM BORROW a,BOOKS b,CARD c
WHERE a.BOOK_NO = b.BOOK_NO
AND a.CARD_NO = c.CARD_NO
GROUP BY c.CLASS,b.BOOK_NAME;
1
2
3
4
5

11、写出每本书当前被借出的数量，未借出的统计为0

方法一：右连接

SELECT b.BOOK_NO,b.BOOK_NAME,COUNT(a.OUT_DATE)
FROM BORROW a
RIGHT JOIN BOOKS b ON a.BOOK_NO = b.BOOK_NO
GROUP BY b.BOOK_NO,b.BOOK_NAME
ORDER BY 3;
1
2
3
4
5

方法二：左连接

SELECT b.BOOK_NO,b.BOOK_NAME,COUNT(a.OUT_DATE)
FROM BOOKS b
LEFT JOIN BORROW a ON b.BOOK_NO = a.BOOK_NO
GROUP BY b.BOOK_NO,b.BOOK_NAME
ORDER BY 3;
1
2
3
4
5

12、用一条SQL实现查询四大名著累计被借阅次数

SELECT b.BOOK_NO,b.BOOK_NAME,COUNT(a.OUT_DATE)
FROM BOOKS b
LEFT JOIN BORROW a ON b.BOOK_NO = a.BOOK_NO
WHERE b.BOOK_NAME IN ('红楼梦','水浒','西游记','三国演义')
GROUP BY b.BOOK_NO,b.BOOK_NAME
ORDER BY 3;
1
2
3
4
5
6