Oracle 预备知识
1.Oracle数据库中查询操作的基本语法
SELECT [ALL|DISTINCT]column_name[,expression…]
FROM table1_name[,table2_name,view_name,…]
[WHERE condition]
[GROUP BY column_name1[,column_name2,…] [HAVING group_condition]]
[ORDER BY column_name2 [ASC|DESC][,column_name2,…]];
2.常用的统计（聚集）函数
| 函 数 | 格 式 | 功 能 |
|---|---|---|
| COUNT | COUNT([DISTINCT|ALL] *) | 返回结果集中记录个数 |
| COUNT | COUNT([DISTINCT|ALL] column) | 返回结果集中非空记录个数 |
| AVG | AVG([DISTINCT|ALL] column) | 返回列或表达式的平均值 |
| MAX | MAX([DISTINCT|ALL] column ) | 返回列或表达式的最大值 |
| MIN | MIN([DISTINCT|ALL] column ) | 返回列或表达式的最小值 |
| SUM | SUM([DISTINCT|ALL] column ) | 返回列或表达式的总和 |
| STDDEV | STDDEV(column) | 返回列或表达式的标准差 |
| VARIANCE | VARIANCE(column) | 返回列或表达式的方差 |
- 除了COUNT(*)函数外，其他的统计函数都不考虑返回值或表达式为NULL的情况。
- 聚集函数只能出现在目标列表达式、ORDER BY子句、HAVING子句中，不能出现在WHERE子句和GROUP BY子句中。
- 默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用DISTINCT选项。
- 如果对查询结果进行了分组，则聚集函数的作用范围为各个组，否则聚集函数作用于整个查询结果。
创建表和数据
CREATE TABLE customers(
customer_id NUMBER(2) PRIMARY KEY,
name CHAR(10) NOT NULL,
phone VARCHAR(15) NOT NULL,
email VARCHAR(15),
address VARCHAR(30),
code VARCHAR(10)
);
CREATE TABLE publishers(
publisher_id NUMBER(2) PRIMARY KEY,
name VARCHAR(32),
contact CHAR(10),
phone VARCHAR(15)
);
CREATE TABLE books(
isbn VARCHAR(15) PRIMARY KEY,
title VARCHAR(15),
pubdate DATE,
publisher_id NUMBER(2) REFERENCES publishers(publisher_id),
cost NUMBER(4,2),
retail NUMBER(4,2),
category VARCHAR(15)
);
CREATE TABLE authors(
author_id NUMBER(2) PRIMARY KEY,
name VARCHAR(15));
CREATE TABLE bookauthor(
isbn VARCHAR(15) REFERENCES books(isbn),
author_id NUMBER(2) REFERENCES authors(author_id),
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 NOT NULL,
shipaddress VARCHAR(30),
shipcode VARCHAR(10)
);
CREATE TABLE orderitem(
ttem_id NUMBER(2) PRIMARY KEY,
order_id NUMBER(2) REFERENCES orders(order_id),
isbn VARCHAR(15) REFERENCES books(isbn),
quantity NUMBER(2)
);
瞎写的数据
INSERT INTO customers VALUES(1,'小李','13300000000','[email protected]','东莞','122121');
INSERT INTO customers VALUES(2,'小刘','13300000000','[email protected]','上海浦东','122121');
INSERT INTO customers VALUES(3,'小王','13300000000','[email protected]','北京大学','122121');
INSERT INTO customers VALUES(4,'小张','13300000000','[email protected]','清华大学','122121');
insert into customers values(5,'小宋','123456','[email protected]','湖南大学','421000' );
insert into customers values(6,'小岳','234567','[email protected]','北京中关村','421000' );
insert into publishers values(1,'东四出版社','给予','345678');
insert into publishers values(2,'清华大学出版社','清华','345678');
insert into publishers values(3,'电子工业出版社','电工','345678');
insert into books values('0001','数据结构',sysdate,1,25.25,30.00,'计算机类');
insert into books values('0002','算法设计',to_date('2009-01-01','yyyy-mm-dd'),1,34.24,42.00,'计算机类');
insert into books values('0003','python',to_date('1996-12-03','yyyy-mm-dd') ,1,34.24,41.00,'计算机类');
insert into books values('0004','数据库大全',to_date('1990-12-03','yyyy-mm-dd') ,2,34.24,90.00,'计算机类');
insert into books values('0005','Oracle解读',to_date('2007-02-03','yyyy-mm-dd') ,3,34.40,90.00,'计算机类');
insert into books values('0006','java',to_date('2007-02-03','yyyy-mm-dd') ,1,34.40,90.00,'计算机类');
insert into books values('0007','大海',sysdate,1,25.25,30.00,'文学类');
insert into books values('0008','非我',sysdate,1,25.25,30.00,'哲学类');
insert into authors values(1,'廖雪峰');
insert into authors values(2,'唐三');
insert into bookauthor values('0001',1);
insert into bookauthor values('0002',2);
insert into orders values(1,1,sysdate,sysdate,'新华书店','421000');
insert into orders values(2,2,to_date ( '2007-11-20 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ),to_date ( '2007-12-30 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ),'新华书店','421000');
insert into orders values(3,3,sysdate,sysdate,'电子工业出版社','421000');
insert into orders values(4,4,to_date ( '2007-12-20 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ),to_date ( '2007-12-21 18:31:34' , 'YYYY-MM-DD HH24:MI:SS' ),'清华大学出版社','421000');
insert into orderitem values(1,1,'0001',20);
insert into orderitem values(2,2,'0002',20);
查询
以下查询基于图书销售系统表
（1） 查询当前所有客户信息。
select * from customers;
（2） 查询图书ISBN、书名、批发价以及零售价信息。
select isbn,title,cost,retail from books;
（3） 查询所有图书的种类。
select DISTINCT category from books;
（4） 查询图书ISBN、图书名及作者名信息。
select books.isbn,books.title,authors.name from books inner join bookauthor on books.isbn=bookauthor.isbn join authors on authors.author_id=bookauthor.author_id;
（5） 查询所有订单信息。
select * from orders;
（6） 查询所有出版社信息。
select * from publishers;
（7） 查询所有订单明细信息。
select * from orderitem;
（8） 查询出版日期在2009年1月1日之后的图书信息。
select * from books where pubdate > to_date('2009-01-01','yyyy-mm-dd');
（9） 查询所有图书ISBN、图书名、出版日期，并按出版日期降序排序。
select isbn,title,pubdate from books order by pubdate desc;
（10） 查询所有图书ISBN、图书名、出版社、出版日期，按批发价格排序，对于批发价相同的，再按零售价排序。
select * from books inner join publishers on books.publisher_id=publishers.publisher_id order by cost,retail;
（11） 查询图书名以“数据库”开头的图书信息。
select * from books where title like '数据库%';
（12） 查询所有书名中包含“Oracle”的图书信息。
select * from books where title like '%Oracle%';
（13） 查询出版日期在2007年1月至2009年1月的所有图书。
select * from books where pubdate >= to_date('2007-01-01','yyyy-mm-dd') and pubdate <= to_date('2009-1-31','yyyy-mm-dd');
（14） 查询“电子工业出版社”和“清华大学出版社”出版的图书。
select * from books join publishers on books.publisher_id=publishers.publisher_id and (publishers.name like '清华大学出版社' or publishers.name like '电子工业出版社');
（15） 查询所有订单信息，按订单日期排序。对于订单日期相同的，再按发货日期排序。
select * from orders order by orderdate,shipdate;
（16） 查询所有发货日期比订货日期晚７天的订单信息。
select * from orders where (to_number(shipdate-orderdate))>=7;
（17） 统计各类图书的数量，平均零售价格、平均批发价格。
select category,count(*),avg(retail),avg(cost) from books GROUP BY category;
（18） 统计各个出版社出版图书的数量、最高批发价格、最高零售价格、最低批发价格和最低零售价格。
select name,count(*),max(cost),max(retail),min(cost),min(retail) from books join publishers on books.publisher_id =publishers.publisher_id GROUP BY name;
（19） 统计每个客户的订单数量。
select name,count(*) from orders join customers on orders.customer_id=customers.customer_id group by name;
（20） 统计每个作者编写的图书数量。
select name,count(*) from bookauthor join authors on authors.author_id=bookauthor.author_id group by name;
