/*
MySQL没有真正意义上的表空间管理
oracle创建表空间
create tablespace bookspace
datafile 'c:\oracledata\bookspace.dbf'
size 5m autoextend on next 1m
maxsize 50m;
*/
/*
drop table lend;
drop table book;
drop table card;
drop table student;
drop table type;
create tablespace bookspace
datafile 'c:\oracledata\bookspace.dbf'
size 5m autoextend on next 1m
maxsize 50m;
--图书类别表
create table type
(
typeid number(10) primary key,
typename varchar2(20) unique not null
) tablespace bookspace;
--图书信息表
create table book
(
bookid number(10) primary key,
booknumber char(8) unique not null,
bookname varchar2(30) not null,
bookpress varchar2(40) not null,
bookprice number(8,2) not null,
typeid number(10) default 1 not null,
booktime date not null,
constraint book_type foreign key(typeid) references type(typeid)
) tablespace bookspace;
--学生信息表
create table student
(
studentid number(10) primary key,
studentnumber char(7) unique not null,
studentname varchar2(10) not null,
studentsex char(1) not null check(studentsex in ('M','F'))
) tablespace bookspace;
--借书证信息表
create table card
(
cardid number(10) primary key,
cardnumber char(9) unique not null,
studentid number(10) unique not null,
registertime date not null,
constraint card_student foreign key (studentid) references student(studentid)
) tablespace bookspace;
--书籍借阅信息表
create table lend(
lendid number(10) primary key,
cardid number(10) not null,
bookid number(10) not null,
lendtime date not null,
constraint lend_card foreign key(cardid) references card(cardid),
constraint lend_book foreign key(bookid) references book(bookid)
) tablespace bookspace;

delete from lend;
delete from book;
delete from card;
delete from type;
delete from student;
insert into type values(1,'history');
insert into type values(2,'geography');
insert into type values(3,'english');
insert into type values(4,'chinese');
insert into type values(5,'math');

insert into book values(1,'90001000','chinese history','peopele university press',220.00,1,to_date('2002/4/1','yyyy/mm/dd'));
insert into book values(2,'90001002','world history','beijin university press',20.00,1,to_date('2002/5/1','yyyy/mm/dd'));
insert into book values(3,'90001010','american history','beijin university press',120.00,1,to_date('2002/6/1','yyyy/mm/dd'));
insert into book values(4,'80001100','chinese geograph','peopele university press',220.00,2,to_date('2002/7/1','yyyy/mm/dd'));
insert into book values(5,'80001002','asian geograph','qinhua university press',20.00,2,to_date('2003/8/1','yyyy/mm/dd'));
insert into book values(6,'10001010','american english','beijin university press',110.00,3,to_date('2006/9/1','yyyy/mm/dd'));
insert into book values(7,'90003010','go around america','beijin university press',120.00,3,to_date('2007/3/1','yyyy/mm/dd'));

insert into student values(1,'1990011','zhang','M');
insert into student values(2,'1990012','wang','F');
insert into student values(3,'1990013','li','M');
insert into student values(4,'1992011','xu','M');
insert into student values(5,'1992021','wu','M');
insert into student values(6,'1993031','john','F');
insert into student values(7,'1994041','my','F');

insert into card values(1,'199007145',1,to_date('2012/9/1','yyyy/mm/dd'));
insert into card values(3,'199007146',2,to_date('2012/9/1','yyyy/mm/dd'));
insert into card values(2,'199007148',3,to_date('2013/9/1','yyyy/mm/dd'));
insert into card values(4,'199007149',5,to_date('2013/9/1','yyyy/mm/dd'));
insert into card values(5,'199007155',4,to_date('2011/9/1','yyyy/mm/dd'));

insert into lend values(1,1,1,to_date('2013/11/1','yyyy/mm/dd'));
insert into lend values(2,1,2,to_date('2013/11/1','yyyy/mm/dd'));
insert into lend values(3,1,3,to_date('2013/11/11','yyyy/mm/dd'));
insert into lend values(6,2,5,to_date('2013/11/1','yyyy/mm/dd'));
insert into lend values(4,2,6,to_date('2013/2/4','yyyy/mm/dd'));
insert into lend values(5,2,3,to_date('2014/2/1','yyyy/mm/dd'));
insert into lend values(7,3,6,to_date('2014/2/1','yyyy/mm/dd'));
insert into lend values(8,4,3,to_date('2014/2/16','yyyy/mm/dd'));

select * from type;
select * from book;
select * from lend;
select * from student;
select * from card;
*/
DROP TABLE IF EXISTS `type`;
CREATE TABLE `type`
(
  typeid   BIGINT PRIMARY KEY,
  typename VARCHAR(20) UNIQUE NOT NULL
);

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`
(
  gradeid   BIGINT PRIMARY KEY,
  gradename VARCHAR(20) UNIQUE NOT NULL
);

DROP TABLE IF EXISTS `book`;
CREATE TABLE `book`
(
  bookid     BIGINT PRIMARY KEY,
  booknumber CHAR(8) UNIQUE   NOT NULL,
  bookname   VARCHAR(30)      NOT NULL,
  bookpress  VARCHAR(40)      NOT NULL,
  bookprice  DECIMAL(8, 2)    NOT NULL,
  typeid     BIGINT DEFAULT 1 NOT NULL,
  booktime   DATE             NOT NULL,
  CONSTRAINT book_type FOREIGN KEY (typeid) REFERENCES type (typeid)
);

DROP TABLE IF EXISTS `manager`;
CREATE TABLE `manager`
(
  managerid    BIGINT PRIMARY KEY,
  realname     VARCHAR(10)      NOT NULL,
  sex          CHAR(2)          NOT NULL CHECK ( studentsex IN ('男', '女') ),
  username     VARCHAR(16)      NOT NULL,
  password     VARCHAR(16)      NOT NULL,
  registertime DATE             NOT NULL,
  gradeid      BIGINT DEFAULT 1 NOT NULL,
  CONSTRAINT manager_grade FOREIGN KEY (gradeid) REFERENCES grade (gradeid)
);

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
  studentid     BIGINT PRIMARY KEY,
  studentnumber char(7) UNIQUE NOT NULL,
  studentname   VARCHAR(10)    NOT NULL,
  studentsex    CHAR(2)        NOT NULL CHECK ( studentsex IN ('男', '女') )
);

DROP TABLE IF EXISTS `card`;
CREATE TABLE `card`
(
  cardid       BIGINT primary key,
  cardnumber   CHAR(9) UNIQUE NOT NULL,
  studentid    BIGINT         NOT NULL,
  registertime DATE           NOT NULL,
  CONSTRAINT card_student FOREIGN KEY (studentid) REFERENCES student (studentid)
);

DROP TABLE IF EXISTS `lend`;
CREATE TABLE `lend`
(
  lendid   BIGINT PRIMARY KEY,
  cardid   BIGINT NOT NULL,
  bookid   BIGINT NOT NULL,
  lendtime DATE   NOT NULL,
  CONSTRAINT lend_card FOREIGN KEY (cardid) REFERENCES card (cardid),
  CONSTRAINT lend_book FOREIGN KEY (bookid) REFERENCES book (bookid)
);

insert into type
values (1, 'history');

insert into type
values (2, 'geography');

insert into type
values (3, 'english');

insert into type
values (4, 'chinese');

insert into type
values (5, 'math');

insert into book
values (1, '90001000', 'chinese history', 'peopele university press', 220.00, 1, date('2002/4/1'));
insert into book
values (2, '90001002', 'world history', 'beijin university press', 20.00, 1, date('2002/5/1'));
insert into book
values (3, '90001010', 'american history', 'beijin university press', 120.00, 1, date('2002/6/1'));
insert into book
values (4, '80001100', 'chinese geograph', 'peopele university press', 220.00, 2,
        date('2002/7/1'));
insert into book
values (5, '80001002', 'asian geograph', 'qinhua university press', 20.00, 2, date('2003/8/1'));
insert into book
values (6, '10001010', 'american english', 'beijin university press', 110.00, 3, date('2006/9/1'));
insert into book
values (7, '90003010', 'go around america', 'beijin university press', 120.00, 3,
        date('2007/3/1'));

insert into student
values (1, '1990011', 'zhang', 'M');
insert into student
values (2, '1990012', 'wang', 'F');
insert into student
values (3, '1990013', 'li', 'M');
insert into student
values (4, '1992011', 'xu', 'M');
insert into student
values (5, '1992021', 'wu', 'M');
insert into student
values (6, '1993031', 'john', 'F');
insert into student
values (7, '1994041', 'my', 'F');

insert into card
values (1, '199007145', 1, date('2012/9/1'));
insert into card
values (3, '199007146', 2, date('2012/9/1'));
insert into card
values (2, '199007148', 3, date('2013/9/1'));
insert into card
values (4, '199007149', 5, date('2013/9/1'));
insert into card
values (5, '199007155', 4, date('2011/9/1'));

insert into lend
values (1, 1, 1, date('2013/11/1'));
insert into lend
values (2, 1, 2, date('2013/11/1'));
insert into lend
values (3, 1, 3, date('2013/11/11'));
insert into lend
values (6, 2, 5, date('2013/11/1'));
insert into lend
values (4, 2, 6, date('2013/2/4'));
insert into lend
values (5, 2, 3, date('2014/2/1'));
insert into lend
values (7, 3, 6, date('2014/2/1'));
insert into lend
values (8, 4, 3, date('2014/2/16'));

select *
from type;

select *
from book;

select *
from lend;

select *
from student;

select *
from card;
/**********************************************************************************************************************/
/*
 mysql没有oracle的NUMBER
 oracle的VARCHAR叫VARCHAR2
 oracle的创建表语句是用大括号括起来的，而不是圆括号
 oracle不需要在表名上面加` `
 oracle只有to_date('2007/3/1','yyyy/mm/dd')，没有date('2007/3/1')
 oracle表别名不能用AS，只有结果集字段可以用AS
 oracle几乎不用``而是用''
 MySQL没有create or 的语法
 */

/*
oracle创建表空间
create tablespace bookspace
datafile 'c:\oracledata\bookspace.dbf'
size 5m autoextend on next 1m
maxsize 50m;
*/

#显示所有的图书类型和相关的书籍（要求右外连接）（显示字段：类型名称、图书编号、图书名称）
/*
显示所有的图书类型和相关的书籍（要求右外连接）
ORACLE:
SELECT typename,bookid,bookname
FROM book RIGHT OUTER JOIN type USING(typeid);
*/
SELECT book.typeid,book.booknumber,book.bookname
FROM book
       RIGHT OUTER JOIN type ON book.typeid = type.typeid
WHERE book.typeid IS NOT NULL;

#输出每本书的价格范围，当价格在(0,100]中，输出'low';价格在(100,150]中，输出'middle'；价格大于150时，输出'high'。(显示字段：图书名称和价格范围）
/*
实验例题：写一段SQL语句，输出每本书的价格范围，当价格在[100，0）中，输出 ‘P1’；价格在[150，100）中，输出‘P2’；价格大于150时，输出‘P3’
SELECT BOOKNAME , BOOKPRICE,
(
CASE
  WHEN(BOOKPRICE BETWEEN 0 AND 100 )
  THEN ’P1’
   WHEN(BOOKPRICE BETWEEN 100 AND 150 )
  THEN ’P2’
   WHEN(BOOKPRICE >150 )
  THEN ’P3’
  END
) prompt
FROM BOOK ;
*/
SELECT bookname,
       bookprice,
       (
         CASE
           WHEN (bookprice > 0 AND bookprice <= 100)
             THEN 'low'
           WHEN (bookprice > 100 AND bookprice <= 150)
             THEN 'middle'
           WHEN (BOOKPRICE > 150)
             THEN 'high'
           END
         ) prompt
FROM book;

#查询各个图书类别中，哪些图书的价格低于其所属图书类别的平均价格（显示字段：图书名称、价格以及所属图书类别的平均价格）
/*
实验例题：查询各个图书种类中，那些图书的价格高于其所在图书种类的平均价格
SELECT *
FROM BOOK OUTER
WHERE  BOOKPRICE >(
SELECT AVG(BOOKPRICE) FROM BOOK  INNER
WHERE INNER.TYPEID = OUTER.TYPEID );
*/
SELECT a.bookname,
       a.bookprice,
       (SELECT AVG(b.bookprice) FROM book AS b WHERE a.typeid = b.typeid) AS 'groupAvg'
FROM book AS a
WHERE a.bookprice > (SELECT AVG(b.bookprice) FROM book AS b WHERE a.typeid = b.typeid);

# 价格最高和价格次高的图书和图书名称
/*
实验例题：价格次高的图书名称和图书编号
SELECT booknumber,bookname
FROM book
WHERE book.bookprice=(SELECT max(bookprice)   FROM book where bookprice<(SELECT max(bookprice) FROM book))
*/
SELECT bookname,bookprice
FROM book
WHERE bookid IN (SELECT bookid
                 FROM book
                 WHERE book.bookprice =
                       (SELECT max(bookprice)
                        FROM book
                        where bookprice < (SELECT max(bookprice) FROM book)))
   OR bookid IN
      (SELECT bookid
       FROM book
       WHERE book.bookprice = (SELECT max(bookprice) FROM book))
ORDER BY bookprice DESC;

#编写存储过程，完成以下功能：针对type表做如下操作：参数为typeid和typename，如果对应typeid在数据库中不存在，则添加新纪录，记录内容为参数的值；否则依据输入的typeid修改对应类型的typename
/*
实验例题：针对type表做如下操作：参数为typeid和typename的值，如果typeid不存在，则新添加记录，否则依据输入的值修改已有类型的typename
create or replace procedure procexec1(TYPE_ID in type.typeid%type, TYPE_Name in type.typename%type) is A  NUMBER;
begin
  SELECT COUNT(TYPEID) INTO A  FROM TYPE WHERE TYPEID=TYPE_ID;
  IF A=0 THEN
     INSERT INTO TYPE  VALUES(TYPE_ID,TYPE_NAME);
  ELSE
    UPDATE TYPE SET TYPENAME=TYPE_Name WHERE TYPEID=TYPE_ID;
  END IF;
end procexec1;
*/
# oracle和mysql在存储过程上有很多语法差别：
# oracle的参数列表要这样写([变量名] [IN] [数据库字段名]%[表名]) [is A  NUMBER];后面这个应该是声明过程中用到的变量A
# oracle可以用INTO将SQL结果赋值给变量，在随后的语句中即能使用，但MySQL中要使用SET @变量名完成这一操作
# MySQL不允许end + procedure名的语法
DROP PROCEDURE IF EXISTS procexec1;
# noinspection SqlUnused
CREATE PROCEDURE procexec1(IN typeId BIGINT, IN typeName VARCHAR(20))
BEGIN
  SET @A = (SELECT COUNT(type.typeid) AS countAns FROM type WHERE type.typeid = typeId = 0);
  IF @A THEN
    INSERT INTO TYPE VALUES (typeId, typeName);
  ELSE
    UPDATE type SET type.typename=typeName WHERE type.typeid = typeId;
  END IF;
END;
CALL procexec1(11, 'cjh');

#编写函数，完成以下功能：输入价格的范围，返回在这个范围内的图书数量
/*
实验例题：编写函数，完成的功能为：输入价格的范围，返回在这个范围内的图书的数量。
create or replace function funexec5(lowprice in book.bookprice%type,highprice in book.bookprice%type) return number as ret number;
begin
    select  count(bookid) into ret  from book where bookprice between lowprice and highprice;
    return ret;
end funexec5;
 */
# oracle的函数和存储过程的语法很相似，参数列表的形式为([变量名] [IN] [数据库字段名]%[表名]) return [返回类型] as [返回的变量名] [返回的变量的类型]
# oracle可以用INTO将SQL结果赋值给变量，在随后的语句中即能使用，但MySQL中要使用SET @变量名完成这一操作
# oracle在参数列表后面声明过return内容后就不用在函数体内写明返回了
# MySQL不允许end + function名的语法
DROP FUNCTION IF EXISTS funexec5;
CREATE FUNCTION funexec5(lowPrice DECIMAL, highPrice DECIMAL) RETURNS DECIMAL
BEGIN
  SET @ret = (SELECT COUNT(bookid)
              FROM book
              WHERE bookprice BETWEEN lowPrice AND highPrice);
  return @ret;
end;
select funexec5(100, 120);

#新建序列和触发器，当对type表新增记录不指定typeid字段取值时可以序列自动填充typeid字段内容
/*2018年12月18日 等19号老师发oracle的相关参考内容再更*/

#编写视图，显示图书ID、图书名称和图书价格打八折后的价格。针对这个视图，编写触发器，当用户通过视图修改价格时，图中表中价格为输入价格的1.25倍。
/*2018年12月18日 等19号老师发oracle的相关参考内容再更*/