oracle的sql训练

  • 查询语句的基本语法
  • 实例准备
  • 基础训练
    • 无条件查询
    • 去重查询
    • 多表查询
    • 条件查询
    • 排序
    • 模糊查询
    • between使用
    • in
    • 日期处理
    • 聚集函数的使用
    • 多表聚集
    • 分组
    • 连接查询
    • 子查询
  • 综合训练

查询语句的基本语法

select [all|distinct] 字段名from 表where 条件group by 分组条件 having 分组后的条件order by 排序字段 排序规则（desc降序 asc 升序）

实例准备

操作环境如果没有下载oracle，那么可以使用oracle在线来训练
sql的数据准备
因为其一次会话断开后，数据也会删除。数据也可以自建一个脚本，每一次训练就启动这个脚本，不用重复建表了。

基础训练

无条件查询

查询customers当前所有客户信息。

select * from customers;

查询books图书ISBN、书名title、批发价cost以及零售价retail信息。

select ISBN,title,cost,retail from books;

去重查询

查询books所有图书的种类category。

select distinct category from books;

多表查询

查询books,author图书ISBN、图书名title及作者名name信息。

select b.ISBN,b.title,a.namefrom books b,authors a,bookauthor ba where b.ISBN = ba.ISBN and a.author_id=ba.author_id;

条件查询

查询出版日期在2009年1月1日之后的图书信息。

select * from books where pubdate > to_date('2009-1-1','yyyy-mm-dd');

排序

查询所有图书ISBN、图书名、出版日期，并按出版日期降序排序。

select ISBN,title,pubdate from books order by pubdate desc;

查询所有图书ISBN、图书名、出版社、出版日期，按批发价格排序，对于批发价相同的，再按零售价排序。

select ISBN,title,name,pubdate from books,publishers where books.publisher_id = publishers.publisher_id order by cost desc,retail desc;

模糊查询

查询图书名以“数据库”开头的图书信息。

select * from books where title like '数据库%';

查询所有书名中包含“Oracle”的图书信息。

select * from books where title like '%Oracle%';

between使用

查询出版日期在2007年1月至2009年1月的所有图书。

select * from books where pubdate between to_date('2007-1','yyyy-mm') and to_date('2009-1','yyyy-mm');

in

查询“电子工业出版社”和“清华大学出版社”出版的图书。

select title from books,publishers where books.publisher_id = publishers.publisher_id and name in ('清华大学出版社','电子工业出版社');

日期处理

查询所有发货日期比订货日期晚７天的订单信息。

select * from orders where orderdate + 7=shipdate;

聚集函数的使用

统计各类图书的数量，平均零售价格、平均批发价格。

select count(*),avg(cost),avg(retail) from books;

多表聚集

统计各个出版社出版图书的数量、最高批发价格、最高零售价格、最低批发价格和最低零售价格。

select count(*),max(cost),min(cost),max(retail),min(retail) from books,publishers where books.publisher_id = publishers.publisher_id group by name ;

分组

统计每个客户的订单数量。

select count(*),customer_id from orders group by customer_id;

统计每个作者编写的图书数量。

select count(*) from bookauthor,books where books.ISBN=bookauthor.ISBN group by author_id;

统计各类图书的总种数、平均批发价格、平均零售价格以及最高批发价格、最高零售价格。

select count(*),avg(cost),avg(retail),max(cost),max(retail) from books group by category;

连接查询

左连接：
left join 以左表为主，将符合条件的数据插入到左表中。
如果没有符合的也不会删除，如下面客户如果没有订单，直接使用连接则会删除没有订单的客户。右连接相反，不过感觉用一个就行。
查询所有客户及其订单信息。

select * from customers left join orderson orders.customer_id=customers.customer_id;

查询客户编号、客户名、订单编号、订货日期、发货日期、所订图书编号、数量。

select c.customer_id,name,o.order_id,orderdate,shipdate,ISBN,quantity from customers c,orders o,orderitem oi where c.customer_id=o.customer_id and o.order_id = oi.order_id;

子查询

查询电子工业出版社出版的图书信息。

select * from books where publisher_id =(select publisher_id from publishers where name ='电子工业出版社');

综合训练

感觉有的我写出来效率不高，不过还能能满足要求。
统计各个出版社图书总种数

select publisher_id,(select count(*) from books b where b.publisher_id =p.publisher_id) count from publishers p;

统计每个客户订货（书）的总量。

select customer_id,(select sum(quantity) from orders o,orderitem oiwhere o.customer_id = c.customer_id and oi.order_id = o. order_id) sum from customers c ; select c.customer_id,sum(quantity) from customers c left join orders o on o.customer_id=c.customer_id left join orderitem oion o.order_id = oi.order_id group by c.customer_id;

查询每个客户订购的每本图书的编号、名称、数量、批发价格、零售价格

select c.customer_id,b.ISBN,oi.quantity,b.title,cost,retail from customers c left join orders o on o.customer_id = c.customer_id left join orderitem oi on oi.order_id=o.order_id left join books b on b.ISBN=oi.ISBN;

查询作者多于一个的图书编号、图书名称。

select ISBN,title from books b where (select count(*) from bookauthor a where a.ISBN = b.ISBN)>1

查询客户名为“张三”的客户的订单信息（客户名字、所有订单号、购买的书名及数量）。

select name,o.order_id,title,quantity from orders o,customers c,orderitem oi,books b where name = '张三' and c.customer_id=o.customer_id and oi.order_id=o.order_id and b.ISBN = oi.ISBN;

查询2009年1月10所有订单的明细信息。

select * from orderitem where order_id=(select order_id from orders where to_char(orderdate,'yyyy-mm-dd')='2009-1-10');

查询比清华大学出版的某本图书价格高的电子工业出版社出版的图书信息。

select * from books where publisher_id in(select publisher_id from publishers where name ='电子工业出版社') and retail > any (select retail from books b,publishers p where name ='清华大学出版社' andb.publisher_id =p.publisher_id);

查询图书批发价格比本出版社出版的图书的平均图书批发价格高的图书信息。

select * from books a where cost > (select avg(cost) from books b where b.publisher_id = a.publisher_id);

查询各个出版社出版的图书中批发价格最高的图书信息。

select * from books b,(select max(cost) c,publisher_id from books group by publisher_id) a where b.cost = a.c and b.publisher_id = a.publisher_id;

查询订货量最高的前3种图书编号、名称、订货量、作者以及出版日期。
不考虑一本书由多个作者书写，别名任意启的

select b.ISBN,b.title,a.name,b.pubdate, al.total from books b,authors a,bookauthor ba,( select * from (select ISBN,sum(orderitem.quantity) as total from orderitem group byISBN order by total desc) where rownum<=3 ) al where b.ISBN=al.ISBN and al.ISBN=ba.ISBN and ba.author_id=a.author_id
listagg(”组合的列“，”区分符“)
select b.ISBN,title,a.quantity,pubdate,(select listagg(name,',') from authors ,bookauthor ba where ba.author_id = authors.author_id and ba.ISBN = b.ISBN) authorfrom (select * from (select sum(quantity) quantity,ISBN from orderitem group by ISBN order by quantity desc)where rownum <=3) a,books bwhere a.ISBN=b.ISBN;

查询2009年1月1日到2009年3月1日之间订货的图书信息。

select * from books where ISBN in (select ISBN from orderitem where order_id in (select order_id from orders where shipdate between to_date('2009-01-01','yyyy-mm-dd') and to_date('2009-03-01','yyyy-mm-dd')))

查询书名中包含“Oracle”的图书的订货信息。

select * from orderitem,orders where orders.order_id=orderitem.order_id and ISBN in(select ISBN from books where title like '%oracle%' )

查询每类图书的销售总量。

select category,sum(a.q) quantity from books b,(select sum(quantity) q,ISBN from orderitem group by ISBN) a where a.ISBN=B.ISBN group by category

查询每个客户订购的所有图书的批发总价格，零售总价格

select c.customer_id,sum(cost),sum(retail) from customers cleft join (select customer_id,cost,retail from orders o,orderitem oi,books b where o.order_id=oi.order_id and b.ISBN=oi.ISBN ) m on c.customer_id=m.customer_idgroup by c.customer_id;

建表语句

drop table orderitem;drop table orders;drop table bookauthor;drop table books;drop table authors;drop table publishers;drop table customers;CREATE TABLE customers(customer_id NUMBER(2) PRIMARY KEY,name CHAR(10) NOT NULL,phone VARCHAR2(15) NOT NULL,email VARCHAR2(15),address VARCHAR2(30),code VARCHAR2(10)) ;CREATE TABLE publishers(publisher_id NUMBER(2) PRIMARY KEY,name VARCHAR2(15),contact CHAR(10),phone VARCHAR2(15)) ;CREATE TABLE authors(author_id NUMBER(2) PRIMARY KEY,name VARCHAR2(15)) ;CREATE TABLE books(ISBN VARCHAR2(15) PRIMARY KEY,title VARCHAR2(15),pubdate DATE,publisher_id NUMBER(2) REFERENCES publishers(publisher_id),cost NUMBER(4,2),retail NUMBER(4,2),category VARCHAR2(15)) ;CREATE TABLE bookauthor(ISBN VARCHAR2(15) REFERENCES books(ISBN),author_id NUMBER(2) REFERENCES authors(author_id),CONSTRAINT IA_pk PRIMARY KEY(ISBN,author_id)) ;CREATE TABLE orders(order_id NUMBER(2) PRIMARY KEY,customer_id NUMBER(2) REFERENCES customers(customer_id),orderdate DATE NOT NULL,shipdate DATE,shipaddress VARCHAR2(30),shipcode VARCHAR2(30)) ;CREATE TABLE orderitem(item_id NUMBER(2) PRIMARY KEY,order_id NUMBER(2) REFERENCES orders(order_id),ISBN VARCHAR2(15) NOT NULL REFERENCES books(ISBN),quantity NUMBER(2)) ;insert allinto customers values(01,'张三','17608410945','14575265@q.com','beijin',null)into customers values(02,'lisi','15236456421','16554516@o.com','shanghai','1001')into customers values(03,'wangwu','14536456421','14456416@q.com','hunan','1002')into customers values(04,'zhaoliu','17636546421','1545646@oqq.com','shangxi','1003')into publishers values(01,'邹老板','xiaozou','12726456421')into publishers values(02,'马儿','xiaoma','15212456421')into publishers values(03,'清华大学出版社','xiaoxue','13236456421')into publishers values(04,'电子工业出版社','xiaowang','14536456421')into books values('01','oracle',to_date('2018-1-16 12:01:00','yyyy-mm-dd HH24:MI:SS'),04,50,80,'tech')into books values('02','mysql' ,to_date('2007-8-25 15:10:00','yyyy-mm-dd HH24:MI:SS'),01,80,90,'computer')into books values('03','redis' ,to_date('2019-5-06 9:16:00' ,'yyyy-mm-dd HH24:MI:SS'),03,44.2,89.9,'new')into books values('04','server',to_date('2020-6-11 14:13:00','yyyy-mm-dd HH24:MI:SS'),02,30,50,'math')into books values('06','数据库1',to_date('2020-6-11 14:13:00','yyyy-mm-dd HH24:MI:SS'),03,46,65,'tech')into books values('07','数据库2',to_date('2020-6-11 14:13:00','yyyy-mm-dd HH24:MI:SS'),04,45,65,'tech')into authors values(01,'zouzi')into authors values(02,'zhangs')into authors values(03,'liliu')into bookauthor values('02',01)into bookauthor values('04',02)into bookauthor values('01',03)into bookauthor values('01',02)into orders values(01,02,to_date('2022-10-16 12:01:00','yyyy-mm-dd HH24:MI:SS'),to_date('2022-12-16 12:01:00','yyyy-mm-dd HH24:MI:SS'),'tianjin','1003')into orders values(02,04,to_date('2022-9-16 12:01:00','yyyy-mm-dd HH24:MI:SS') ,to_date('2022-11-25 15:10:00','yyyy-mm-dd HH24:MI:SS'),'beijin','1001')into orders values(03,02,to_date('2009-1-2 12:00:00','yyyy-mm-dd HH24:MI:SS') ,to_date('2022-10-7 12:00:00','yyyy-mm-dd HH24:MI:SS'),'bei','1002')into orders values(04,01,to_date('2022-9-15 12:01:00','yyyy-mm-dd HH24:MI:SS') ,to_date('2022-12-25 15:10:00','yyyy-mm-dd HH24:MI:SS'),'cs','1004')into orderitem values(01,01,'03',70)into orderitem values(02,02,'01',65)into orderitem values(03,03,'02',60)into orderitem values(04,02,'03',78)into orderitem values(05,04,'06',52)into orderitem values(06,04,'04',59)select * from dual;