SQL(Oracle)的练习

oracle的sql训练

查询语句的基本语法

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

实例准备

基础训练

无条件查询

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

select*from customers;

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

select ISBN,title,cost,retail from books;

去重查询

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

selectdistinct category from books;

多表查询

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

select b.ISBN,b.title,a.name
from 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 orderby pubdate desc;

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

select ISBN,title,name,pubdate from books,publishers where books.publisher_id = publishers.publisher_id orderby 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;

聚集函数的使用

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

selectcount(*),avg(cost),avg(retail)from books;

多表聚集

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

selectcount(*),max(cost),min(cost),max(retail),min(retail)from books,publishers where books.publisher_id = publishers.publisher_id groupby name ;

分组

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

selectcount(*),customer_id from orders groupby customer_id;

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

selectcount(*)from bookauthor,books where books.ISBN=bookauthor.ISBN groupby author_id;

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

selectcount(*),avg(cost),avg(retail),max(cost),max(retail)from books groupby category;

连接查询

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

select*from customers leftjoin orders  on 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,(selectcount(*)from books b where b.publisher_id =p.publisher_id) count from publishers p;

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

select customer_id,(selectsum(quantity)from orders o,orderitem oi  where 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 leftjoin orders o on o.customer_id=c.customer_id leftjoin orderitem oi  on o.order_id = oi.order_id groupby c.customer_id;

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

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

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

select ISBN,title from books b where(selectcount(*)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 ='清华大学出版社'and  b.publisher_id =p.publisher_id);

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

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

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

select*from books b,(selectmax(cost) c,publisher_id from books groupby 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 groupby  ISBN orderby 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) author
from(select*from(selectsum(quantity) quantity,ISBN from orderitem groupby ISBN orderby quantity desc)where rownum <=3) a,books b
where 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,(selectsum(quantity) q,ISBN from orderitem groupby ISBN) a where a.ISBN=B.ISBN groupby category

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

select c.customer_id,sum(cost),sum(retail)from customers c
leftjoin(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_id
groupby c.customer_id;

建表语句

droptable orderitem;droptable orders;droptable bookauthor;droptable books;droptable authors;droptable publishers;droptable customers;CREATETABLE customers(
    customer_id NUMBER(2)PRIMARYKEY,
    name CHAR(10)NOTNULL,
    phone VARCHAR2(15)NOTNULL,
    email VARCHAR2(15),
    address VARCHAR2(30),
    code VARCHAR2(10));CREATETABLE publishers(
    publisher_id NUMBER(2)PRIMARYKEY,
    name VARCHAR2(15),
    contact CHAR(10),
    phone VARCHAR2(15));CREATETABLE authors(
    author_id NUMBER(2)PRIMARYKEY,
    name VARCHAR2(15));CREATETABLE books(
    ISBN VARCHAR2(15)PRIMARYKEY,
    title VARCHAR2(15),
    pubdate DATE,
    publisher_id NUMBER(2)REFERENCES publishers(publisher_id),
    cost NUMBER(4,2),
    retail NUMBER(4,2),
    category VARCHAR2(15));CREATETABLE bookauthor(
    ISBN VARCHAR2(15)REFERENCES books(ISBN),
    author_id NUMBER(2)REFERENCES authors(author_id),CONSTRAINT IA_pk PRIMARYKEY(ISBN,author_id));CREATETABLE orders(
    order_id NUMBER(2)PRIMARYKEY,
    customer_id NUMBER(2)REFERENCES customers(customer_id),
    orderdate DATENOTNULL,
    shipdate DATE,
    shipaddress VARCHAR2(30),
    shipcode VARCHAR2(30));CREATETABLE orderitem(
    item_id NUMBER(2)PRIMARYKEY,
    order_id NUMBER(2)REFERENCES orders(order_id),
    ISBN VARCHAR2(15)NOTNULLREFERENCES books(ISBN),
    quantity NUMBER(2));insertallinto customers values(01,'张三','17608410945','[email protected]','beijin',null)into customers values(02,'lisi','15236456421','[email protected]','shanghai','1001')into customers values(03,'wangwu','14536456421','[email protected]','hunan','1002')into customers values(04,'zhaoliu','17636546421','[email protected]','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;