ib6DPtQx

1/*
2MySQL没有真正意义上的表空间管理
3oracle创建表空间
4create tablespace bookspace
5datafile 'c:\oracledata\bookspace.dbf'
6size 5m autoextend on next 1m
7maxsize 50m;
8*/
9/*
10drop table lend;
11drop table book;
12drop table card;
13drop table student;
14drop table type;
15create tablespace bookspace
16datafile 'c:\oracledata\bookspace.dbf'
17size 5m autoextend on next 1m
18maxsize 50m;
19--图书类别表
20create table type
21(
22typeid number(10) primary key,
23typename varchar2(20) unique not null
24) tablespace bookspace;
25--图书信息表
26create table book
27(
28bookid number(10) primary key,
29booknumber char(8) unique not null,
30bookname varchar2(30) not null,
31bookpress varchar2(40) not null,
32bookprice number(8,2) not null,
33typeid number(10) default 1 not null,
34booktime date not null,
35constraint book_type foreign key(typeid) references type(typeid)
36) tablespace bookspace;
37--学生信息表
38create table student
39(
40studentid number(10) primary key,
41studentnumber char(7) unique not null,
42studentname varchar2(10) not null,
43studentsex char(1) not null check(studentsex in ('M','F'))
44) tablespace bookspace;
45--借书证信息表
46create table card
47(
48cardid number(10) primary key,
49cardnumber char(9) unique not null,
50studentid number(10) unique not null,
51registertime date not null,
52constraint card_student foreign key (studentid) references student(studentid)
53) tablespace bookspace;
54--书籍借阅信息表
55create table lend(
56lendid number(10) primary key,
57cardid number(10) not null,
58bookid number(10) not null,
59lendtime date not null,
60constraint lend_card foreign key(cardid) references card(cardid),
61constraint lend_book foreign key(bookid) references book(bookid)
62) tablespace bookspace;
63
64delete from lend;
65delete from book;
66delete from card;
67delete from type;
68delete from student;
69insert into type values(1,'history');
70insert into type values(2,'geography');
71insert into type values(3,'english');
72insert into type values(4,'chinese');
73insert into type values(5,'math');
74
75insert into book values(1,'90001000','chinese history','peopele university press',220.00,1,to_date('2002/4/1','yyyy/mm/dd'));
76insert into book values(2,'90001002','world history','beijin university press',20.00,1,to_date('2002/5/1','yyyy/mm/dd'));
77insert into book values(3,'90001010','american history','beijin university press',120.00,1,to_date('2002/6/1','yyyy/mm/dd'));
78insert into book values(4,'80001100','chinese geograph','peopele university press',220.00,2,to_date('2002/7/1','yyyy/mm/dd'));
79insert into book values(5,'80001002','asian geograph','qinhua university press',20.00,2,to_date('2003/8/1','yyyy/mm/dd'));
80insert into book values(6,'10001010','american english','beijin university press',110.00,3,to_date('2006/9/1','yyyy/mm/dd'));
81insert into book values(7,'90003010','go around america','beijin university press',120.00,3,to_date('2007/3/1','yyyy/mm/dd'));
82
83insert into student values(1,'1990011','zhang','M');
84insert into student values(2,'1990012','wang','F');
85insert into student values(3,'1990013','li','M');
86insert into student values(4,'1992011','xu','M');
87insert into student values(5,'1992021','wu','M');
88insert into student values(6,'1993031','john','F');
89insert into student values(7,'1994041','my','F');
90
91insert into card values(1,'199007145',1,to_date('2012/9/1','yyyy/mm/dd'));
92insert into card values(3,'199007146',2,to_date('2012/9/1','yyyy/mm/dd'));
93insert into card values(2,'199007148',3,to_date('2013/9/1','yyyy/mm/dd'));
94insert into card values(4,'199007149',5,to_date('2013/9/1','yyyy/mm/dd'));
95insert into card values(5,'199007155',4,to_date('2011/9/1','yyyy/mm/dd'));
96
97insert into lend values(1,1,1,to_date('2013/11/1','yyyy/mm/dd'));
98insert into lend values(2,1,2,to_date('2013/11/1','yyyy/mm/dd'));
99insert into lend values(3,1,3,to_date('2013/11/11','yyyy/mm/dd'));
100insert into lend values(6,2,5,to_date('2013/11/1','yyyy/mm/dd'));
101insert into lend values(4,2,6,to_date('2013/2/4','yyyy/mm/dd'));
102insert into lend values(5,2,3,to_date('2014/2/1','yyyy/mm/dd'));
103insert into lend values(7,3,6,to_date('2014/2/1','yyyy/mm/dd'));
104insert into lend values(8,4,3,to_date('2014/2/16','yyyy/mm/dd'));
105
106select * from type;
107select * from book;
108select * from lend;
109select * from student;
110select * from card;
111*/
112DROP TABLE IF EXISTS `type`;
113CREATE TABLE `type`
114(
115  typeid   BIGINT PRIMARY KEY,
116  typename VARCHAR(20) UNIQUE NOT NULL
117);
118
119DROP TABLE IF EXISTS `grade`;
120CREATE TABLE `grade`
121(
122  gradeid   BIGINT PRIMARY KEY,
123  gradename VARCHAR(20) UNIQUE NOT NULL
124);
125
126DROP TABLE IF EXISTS `book`;
127CREATE TABLE `book`
128(
129  bookid     BIGINT PRIMARY KEY,
130  booknumber CHAR(8) UNIQUE   NOT NULL,
131  bookname   VARCHAR(30)      NOT NULL,
132  bookpress  VARCHAR(40)      NOT NULL,
133  bookprice  DECIMAL(8, 2)    NOT NULL,
134  typeid     BIGINT DEFAULT 1 NOT NULL,
135  booktime   DATE             NOT NULL,
136  CONSTRAINT book_type FOREIGN KEY (typeid) REFERENCES type (typeid)
137);
138
139DROP TABLE IF EXISTS `manager`;
140CREATE TABLE `manager`
141(
142  managerid    BIGINT PRIMARY KEY,
143  realname     VARCHAR(10)      NOT NULL,
144  sex          CHAR(2)          NOT NULL CHECK ( studentsex IN ('男', '女') ),
145  username     VARCHAR(16)      NOT NULL,
146  password     VARCHAR(16)      NOT NULL,
147  registertime DATE             NOT NULL,
148  gradeid      BIGINT DEFAULT 1 NOT NULL,
149  CONSTRAINT manager_grade FOREIGN KEY (gradeid) REFERENCES grade (gradeid)
150);
151
152DROP TABLE IF EXISTS `student`;
153CREATE TABLE `student`
154(
155  studentid     BIGINT PRIMARY KEY,
156  studentnumber char(7) UNIQUE NOT NULL,
157  studentname   VARCHAR(10)    NOT NULL,
158  studentsex    CHAR(2)        NOT NULL CHECK ( studentsex IN ('男', '女') )
159);
160
161DROP TABLE IF EXISTS `card`;
162CREATE TABLE `card`
163(
164  cardid       BIGINT primary key,
165  cardnumber   CHAR(9) UNIQUE NOT NULL,
166  studentid    BIGINT         NOT NULL,
167  registertime DATE           NOT NULL,
168  CONSTRAINT card_student FOREIGN KEY (studentid) REFERENCES student (studentid)
169);
170
171DROP TABLE IF EXISTS `lend`;
172CREATE TABLE `lend`
173(
174  lendid   BIGINT PRIMARY KEY,
175  cardid   BIGINT NOT NULL,
176  bookid   BIGINT NOT NULL,
177  lendtime DATE   NOT NULL,
178  CONSTRAINT lend_card FOREIGN KEY (cardid) REFERENCES card (cardid),
179  CONSTRAINT lend_book FOREIGN KEY (bookid) REFERENCES book (bookid)
180);
181
182insert into type
183values (1, 'history');
184
185insert into type
186values (2, 'geography');
187
188insert into type
189values (3, 'english');
190
191insert into type
192values (4, 'chinese');
193
194insert into type
195values (5, 'math');
196
197insert into book
198values (1, '90001000', 'chinese history', 'peopele university press', 220.00, 1, date('2002/4/1'));
199insert into book
200values (2, '90001002', 'world history', 'beijin university press', 20.00, 1, date('2002/5/1'));
201insert into book
202values (3, '90001010', 'american history', 'beijin university press', 120.00, 1, date('2002/6/1'));
203insert into book
204values (4, '80001100', 'chinese geograph', 'peopele university press', 220.00, 2,
205        date('2002/7/1'));
206insert into book
207values (5, '80001002', 'asian geograph', 'qinhua university press', 20.00, 2, date('2003/8/1'));
208insert into book
209values (6, '10001010', 'american english', 'beijin university press', 110.00, 3, date('2006/9/1'));
210insert into book
211values (7, '90003010', 'go around america', 'beijin university press', 120.00, 3,
212        date('2007/3/1'));
213
214insert into student
215values (1, '1990011', 'zhang', 'M');
216insert into student
217values (2, '1990012', 'wang', 'F');
218insert into student
219values (3, '1990013', 'li', 'M');
220insert into student
221values (4, '1992011', 'xu', 'M');
222insert into student
223values (5, '1992021', 'wu', 'M');
224insert into student
225values (6, '1993031', 'john', 'F');
226insert into student
227values (7, '1994041', 'my', 'F');
228
229insert into card
230values (1, '199007145', 1, date('2012/9/1'));
231insert into card
232values (3, '199007146', 2, date('2012/9/1'));
233insert into card
234values (2, '199007148', 3, date('2013/9/1'));
235insert into card
236values (4, '199007149', 5, date('2013/9/1'));
237insert into card
238values (5, '199007155', 4, date('2011/9/1'));
239
240insert into lend
241values (1, 1, 1, date('2013/11/1'));
242insert into lend
243values (2, 1, 2, date('2013/11/1'));
244insert into lend
245values (3, 1, 3, date('2013/11/11'));
246insert into lend
247values (6, 2, 5, date('2013/11/1'));
248insert into lend
249values (4, 2, 6, date('2013/2/4'));
250insert into lend
251values (5, 2, 3, date('2014/2/1'));
252insert into lend
253values (7, 3, 6, date('2014/2/1'));
254insert into lend
255values (8, 4, 3, date('2014/2/16'));
256
257select *
258from type;
259
260select *
261from book;
262
263select *
264from lend;
265
266select *
267from student;
268
269select *
270from card;
271/**********************************************************************************************************************/
272/*
273 mysql没有oracle的NUMBER
274 oracle的VARCHAR叫VARCHAR2
275 oracle的创建表语句是用大括号括起来的，而不是圆括号
276 oracle不需要在表名上面加` `
277 oracle只有to_date('2007/3/1','yyyy/mm/dd')，没有date('2007/3/1')
278 oracle表别名不能用AS，只有结果集字段可以用AS
279 oracle几乎不用``而是用''
280 MySQL没有create or 的语法
281 */
282
283/*
284oracle创建表空间
285create tablespace bookspace
286datafile 'c:\oracledata\bookspace.dbf'
287size 5m autoextend on next 1m
288maxsize 50m;
289*/
290
291#显示所有的图书类型和相关的书籍（要求右外连接）（显示字段：类型名称、图书编号、图书名称）
292/*
293显示所有的图书类型和相关的书籍（要求右外连接）
294ORACLE:
295SELECT typename,bookid,bookname
296FROM book RIGHT OUTER JOIN type USING(typeid);
297*/
298SELECT book.typeid,book.booknumber,book.bookname
299FROM book
300       RIGHT OUTER JOIN type ON book.typeid = type.typeid
301WHERE book.typeid IS NOT NULL;
302
303#输出每本书的价格范围，当价格在(0,100]中，输出'low';价格在(100,150]中，输出'middle'；价格大于150时，输出'high'。(显示字段：图书名称和价格范围）
304/*
305实验例题：写一段SQL语句，输出每本书的价格范围，当价格在[100，0）中，输出 ‘P1’；价格在[150，100）中，输出‘P2’；价格大于150时，输出‘P3’
306SELECT BOOKNAME , BOOKPRICE,
307(
308CASE
309  WHEN(BOOKPRICE BETWEEN 0 AND 100 )
310  THEN ’P1’
311   WHEN(BOOKPRICE BETWEEN 100 AND 150 )
312  THEN ’P2’
313   WHEN(BOOKPRICE >150 )
314  THEN ’P3’
315  END
316) prompt
317FROM BOOK ;
318*/
319SELECT bookname,
320       bookprice,
321       (
322         CASE
323           WHEN (bookprice > 0 AND bookprice <= 100)
324             THEN 'low'
325           WHEN (bookprice > 100 AND bookprice <= 150)
326             THEN 'middle'
327           WHEN (BOOKPRICE > 150)
328             THEN 'high'
329           END
330         ) prompt
331FROM book;
332
333#查询各个图书类别中，哪些图书的价格低于其所属图书类别的平均价格（显示字段：图书名称、价格以及所属图书类别的平均价格）
334/*
335实验例题：查询各个图书种类中，那些图书的价格高于其所在图书种类的平均价格
336SELECT *
337FROM BOOK OUTER
338WHERE  BOOKPRICE >(
339SELECT AVG(BOOKPRICE) FROM BOOK  INNER
340WHERE INNER.TYPEID = OUTER.TYPEID );
341*/
342SELECT a.bookname,
343       a.bookprice,
344       (SELECT AVG(b.bookprice) FROM book AS b WHERE a.typeid = b.typeid) AS 'groupAvg'
345FROM book AS a
346WHERE a.bookprice > (SELECT AVG(b.bookprice) FROM book AS b WHERE a.typeid = b.typeid);
347
348# 价格最高和价格次高的图书和图书名称
349/*
350实验例题：价格次高的图书名称和图书编号
351SELECT booknumber,bookname
352FROM book
353WHERE book.bookprice=(SELECT max(bookprice)   FROM book where bookprice<(SELECT max(bookprice) FROM book))
354*/
355SELECT bookname,bookprice
356FROM book
357WHERE bookid IN (SELECT bookid
358                 FROM book
359                 WHERE book.bookprice =
360                       (SELECT max(bookprice)
361                        FROM book
362                        where bookprice < (SELECT max(bookprice) FROM book)))
363   OR bookid IN
364      (SELECT bookid
365       FROM book
366       WHERE book.bookprice = (SELECT max(bookprice) FROM book))
367ORDER BY bookprice DESC;
368
369#编写存储过程，完成以下功能：针对type表做如下操作：参数为typeid和typename，如果对应typeid在数据库中不存在，则添加新纪录，记录内容为参数的值；否则依据输入的typeid修改对应类型的typename
370/*
371实验例题：针对type表做如下操作：参数为typeid和typename的值，如果typeid不存在，则新添加记录，否则依据输入的值修改已有类型的typename
372create or replace procedure procexec1(TYPE_ID in type.typeid%type, TYPE_Name in type.typename%type) is A  NUMBER;
373begin
374  SELECT COUNT(TYPEID) INTO A  FROM TYPE WHERE TYPEID=TYPE_ID;
375  IF A=0 THEN
376     INSERT INTO TYPE  VALUES(TYPE_ID,TYPE_NAME);
377  ELSE
378    UPDATE TYPE SET TYPENAME=TYPE_Name WHERE TYPEID=TYPE_ID;
379  END IF;
380end procexec1;
381*/
382# oracle和mysql在存储过程上有很多语法差别：
383# oracle的参数列表要这样写([变量名] [IN] [数据库字段名]%[表名]) [is A  NUMBER];后面这个应该是声明过程中用到的变量A
384# oracle可以用INTO将SQL结果赋值给变量，在随后的语句中即能使用，但MySQL中要使用SET @变量名完成这一操作
385# MySQL不允许end + procedure名的语法
386DROP PROCEDURE IF EXISTS procexec1;
387# noinspection SqlUnused
388CREATE PROCEDURE procexec1(IN typeId BIGINT, IN typeName VARCHAR(20))
389BEGIN
390  SET @A = (SELECT COUNT(type.typeid) AS countAns FROM type WHERE type.typeid = typeId = 0);
391  IF @A THEN
392    INSERT INTO TYPE VALUES (typeId, typeName);
393  ELSE
394    UPDATE type SET type.typename=typeName WHERE type.typeid = typeId;
395  END IF;
396END;
397CALL procexec1(11, 'cjh');
398
399#编写函数，完成以下功能：输入价格的范围，返回在这个范围内的图书数量
400/*
401实验例题：编写函数，完成的功能为：输入价格的范围，返回在这个范围内的图书的数量。
402create or replace function funexec5(lowprice in book.bookprice%type,highprice in book.bookprice%type) return number as ret number;
403begin
404    select  count(bookid) into ret  from book where bookprice between lowprice and highprice;
405    return ret;
406end funexec5;
407 */
408# oracle的函数和存储过程的语法很相似，参数列表的形式为([变量名] [IN] [数据库字段名]%[表名]) return [返回类型] as [返回的变量名] [返回的变量的类型]
409# oracle可以用INTO将SQL结果赋值给变量，在随后的语句中即能使用，但MySQL中要使用SET @变量名完成这一操作
410# oracle在参数列表后面声明过return内容后就不用在函数体内写明返回了
411# MySQL不允许end + function名的语法
412DROP FUNCTION IF EXISTS funexec5;
413CREATE FUNCTION funexec5(lowPrice DECIMAL, highPrice DECIMAL) RETURNS DECIMAL
414BEGIN
415  SET @ret = (SELECT COUNT(bookid)
416              FROM book
417              WHERE bookprice BETWEEN lowPrice AND highPrice);
418  return @ret;
419end;
420select funexec5(100, 120);
421
422#新建序列和触发器，当对type表新增记录不指定typeid字段取值时可以序列自动填充typeid字段内容
423/*2018年12月18日 等19号老师发oracle的相关参考内容再更*/
424
425#编写视图，显示图书ID、图书名称和图书价格打八折后的价格。针对这个视图，编写触发器，当用户通过视图修改价格时，图中表中价格为输入价格的1.25倍。
426/*2018年12月18日 等19号老师发oracle的相关参考内容再更*/