在开始(并且几乎完成(学校的数据库项目后,我发现我应该使用Oracle Express而不是MySQL。因此,当我将我从mysql编写的代码引入oracle时,一切都停止了工作。我对oracle数据库不太熟悉,但我认为它们的语法与MySqls非常相似。
此文件中是否有任何结构在 oracle 数据库中有所不同?
你们能看到的任何东西都会有所帮助。谢谢!
CREATE SCHEMA IF NOT EXISTS marys_limo_service;
use marys_limo_service;
DROP TABLE IF EXISTS Driver;
CREATE TABLE Driver(
d_id int,
d_name varchar(50),
d_contact varchar(30),
CONSTRAINT Driver_d_id PRIMARY KEY(d_id)
);
DROP TABLE IF EXISTS Limo;
CREATE TABLE Limo(
l_id int,
l_callsign varchar(12),
l_type varchar(40),
CONSTRAINT Limo_pk PRIMARY KEY(l_id)
);
DROP TABLE IF EXISTS Aclient;
CREATE TABLE Aclient (
c_id int,
c_name varchar(50),
c_contact varchar(30),
c_methpmt varchar(20),
CONSTRAINT Aclient_pk PRIMARY KEY(c_id)
);
DROP TABLE IF EXISTS Qualify;
CREATE TABLE Qualify (
q_id int,
l_id int,
d_id int,
CONSTRAINT Qualify_q_id_PK PRIMARY KEY(q_id),
CONSTRAINT Qualify_l_id_FK FOREIGN KEY(l_id) REFERENCES Limo(l_id),
CONSTRAINT Qualify_d_id_FK FOREIGN KEY(d_id) REFERENCES Driver(d_id)
);
DROP TABLE IF EXISTS Rental;
CREATE TABLE Rental (
r_id INT,
r_date DATE,
r_fee FLOAT,
c_id INT,
q_id INT,
CONSTRAINT Rental_r_id_PK PRIMARY KEY(r_id),
CONSTRAINT Rental_c_id_FK FOREIGN KEY(c_id) REFERENCES Aclient(c_id),
CONSTRAINT Rental_q_id_FK FOREIGN KEY(q_id) REFERENCES Qualify(q_id)
);
INSERT INTO Driver
VALUES
(1,"alf","9022332332"),
(2,"bob","9022322323");
INSERT INTO Limo
VALUES
(1,"Car One", "stretch limo"),
(2,"Car Two", "hummer limo"),
(3,"Car Three", "armored personnel carrier");
INSERT INTO Qualify
VALUES
(1,1,1),
(2,2,1),
(3,3,1),
(4,1,2),
(5,2,2);
INSERT INTO Aclient
VALUES
(1,"ann","9022332332", "cash"),
(2,"bub","9022322323", "CC");
INSERT INTO Rental
VALUES
(1, "2015/3/21", 550, 2, 1),
(2, "2015/3/21", 1000, 1, 5),
(3, "2015/3/20", 2050, 1, 3),
(4, "2015/3/19", 550, 1, 1),
(5, "2015/3/20", 500, 2, 4);
COMMIT;
-- Q.1
INSERT INTO marys_limo_service.driver
VALUES
(3,"Cal","9024919999"),
(4,"Dan","9024914545");
COMMIT;
-- Q.2
INSERT INTO Qualify
values(6,1,3),
(7,2,3),
(8,3,3),
(9,3,4);
select * from Qualify
-- Q.3
UPDATE Rental
SET q_id = 4
where
r_id = 1;
UPDATE Rental
SET q_id = 3
WHERE
r_id = 4;
select * from rental
-- Q.4
CREATE TEMPORARY TABLE Replace_contents
as (SELECT l_id, d_id FROM Qualify
WHERE d_id != 1);
在 Oracle 中,特权用户(如 SYS(可以创建其他用户。那看起来像这样:
SQL> connect sys/pwd as sysdba
Connected.
SQL> select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> create user so_test identified by test
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant create session, create table, create view to so_test;
Grant succeeded.
现在,以新创建的用户身份连接并运行代码(我修复了(;稍后我将发布您可以重用的代码。Oracle中没有drop if exists
,所以我只是删除这些表(注意FK约束!(并忽略删除错误。
其他错误包括:
- 使用
varchar2
,而不是varchar
(这不完全是一个错误,但是 - Oracle 推荐它( - 字符串使用单引号,而不是双引号
- 指定日期格式掩码(例如
to_date('2015/03/21', 'yyyy/mm/dd')
(或使用日期文本(例如date '2015-03-21'
( 或alter session
并指定所需的格式 - 使用分号终止所有命令
-
insert into
应分别用于每一行,除非您使用insert all
或insert into ... select ... from
好的,我们开始:
SQL> connect so_test/test
Connected.
SQL> -- drop them first, because of FK constraints
SQL> drop table rental;
drop table rental
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop table qualify;
drop table qualify
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> -- Now, your code, fixed
SQL> DROP TABLE Driver;
DROP TABLE Driver
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Driver(
2 d_id int,
3 d_name varchar2(50),
4 d_contact varchar2(30),
5 CONSTRAINT Driver_d_id PRIMARY KEY(d_id)
6 );
Table created.
SQL> DROP TABLE Limo;
DROP TABLE Limo
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Limo(
2 l_id int,
3 l_callsign varchar2(12),
4 l_type varchar2(40),
5 CONSTRAINT Limo_pk PRIMARY KEY(l_id)
6 );
Table created.
SQL> DROP TABLE Aclient;
DROP TABLE Aclient
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Aclient (
2 c_id int,
3 c_name varchar2(50),
4 c_contact varchar2(30),
5 c_methpmt varchar2(20),
6 CONSTRAINT Aclient_pk PRIMARY KEY(c_id)
7 );
Table created.
SQL> DROP TABLE Qualify;
DROP TABLE Qualify
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Qualify (
2 q_id int,
3 l_id int,
4 d_id int,
5 CONSTRAINT Qualify_q_id_PK PRIMARY KEY(q_id),
6 CONSTRAINT Qualify_l_id_FK FOREIGN KEY(l_id) REFERENCES Limo(l_id),
7 CONSTRAINT Qualify_d_id_FK FOREIGN KEY(d_id) REFERENCES Driver(d_id)
8 );
Table created.
SQL> DROP TABLE Rental;
DROP TABLE Rental
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Rental (
2 r_id INT,
3 r_date DATE,
4 r_fee FLOAT,
5 c_id INT,
6 q_id INT,
7 CONSTRAINT Rental_r_id_PK PRIMARY KEY(r_id),
8 CONSTRAINT Rental_c_id_FK FOREIGN KEY(c_id) REFERENCES Aclient(c_id),
9 CONSTRAINT Rental_q_id_FK FOREIGN KEY(q_id) REFERENCES Qualify(q_id)
10 );
Table created.
SQL> INSERT INTO Driver VALUES (1,'alf','9022332332');
1 row created.
SQL> INSERT INTO Driver VALUES (2,'bob','9022322323');
1 row created.
SQL>
SQL> INSERT INTO Limo VALUES
2 (1,'Car One', 'stretch limo');
1 row created.
SQL> INSERT INTO Limo VALUES
2 (2,'Car Two', 'hummer limo');
1 row created.
SQL> INSERT INTO Limo VALUES
2 (3,'Car Three', 'armored personnel carrier');
1 row created.
SQL>
SQL> INSERT INTO Qualify VALUES
2 (1,1,1);
1 row created.
SQL> INSERT INTO Qualify VALUES
2 (2,2,1);
1 row created.
SQL> INSERT INTO Qualify VALUES
2 (3,3,1);
1 row created.
SQL> INSERT INTO Qualify VALUES
2 (4,1,2);
1 row created.
SQL> INSERT INTO Qualify VALUES
2 (5,2,2);
1 row created.
SQL>
SQL> INSERT INTO Aclient VALUES
2 (1,'ann','9022332332', 'cash');
1 row created.
SQL> INSERT INTO Aclient VALUES
2 (2,'bub','9022322323', 'CC');
1 row created.
SQL> alter session set nls_date_format = 'yyyy/mm/dd';
Session altered.
SQL> INSERT INTO Rental VALUES
2 (1, '2015/3/21', 550, 2, 1);
1 row created.
SQL> INSERT INTO Rental VALUES
2 (2, '2015/3/21', 1000, 1, 5);
1 row created.
SQL> INSERT INTO Rental VALUES
2 (3, '2015/3/20', 2050, 1, 3);
1 row created.
SQL> INSERT INTO Rental VALUES
2 (4, '2015/3/19', 550, 1, 1);
1 row created.
SQL> INSERT INTO Rental VALUES
2 (5, '2015/3/20', 500, 2, 4);
1 row created.
SQL>
SQL> COMMIT;
Commit complete.
SQL>
SQL> -- Q.1
SQL> INSERT INTO driver VALUES
2 (3,'Cal','9024919999');
1 row created.
SQL> INSERT INTO driver VALUES
2 (4,'Dan','9024914545');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> -- Q.2
SQL> INSERT INTO Qualify values
2 (6,1,3);
1 row created.
SQL> INSERT INTO Qualify values
2 (7,2,3);
1 row created.
SQL> INSERT INTO Qualify values
2 (8,3,3);
1 row created.
SQL> INSERT INTO Qualify values
2 (9,3,4);
1 row created.
SQL>
SQL> select * from Qualify;
Q_ID L_ID D_ID
---------- ---------- ----------
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 1 3
7 2 3
8 3 3
9 3 4
9 rows selected.
SQL>
SQL>
SQL> -- Q.3
SQL>
SQL> UPDATE Rental
2 SET q_id = 4
3 where
4 r_id = 1;
1 row updated.
SQL>
SQL> UPDATE Rental
2 SET q_id = 3
3 WHERE
4 r_id = 4;
1 row updated.
SQL> select * from rental;
R_ID R_DATE R_FEE C_ID Q_ID
---------- ---------- ---------- ---------- ----------
1 2015/03/21 550 2 4
2 2015/03/21 1000 1 5
3 2015/03/20 2050 1 3
4 2015/03/19 550 1 3
5 2015/03/20 500 2 4
SQL>
SQL> -- Q.4
SQL> drop table replace_contents;
drop table replace_contents
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE global TEMPORARY TABLE Replace_contents
2 as SELECT l_id, d_id FROM Qualify
3 WHERE d_id != 1;
Table created.
SQL>
可以重用的代码:
-- drop them first, because of FK constraints
drop table rental;
drop table qualify;
-- Now, your code, fixed
DROP TABLE Driver;
CREATE TABLE Driver(
d_id int,
d_name varchar2(50),
d_contact varchar2(30),
CONSTRAINT Driver_d_id PRIMARY KEY(d_id)
);
DROP TABLE Limo;
CREATE TABLE Limo(
l_id int,
l_callsign varchar2(12),
l_type varchar2(40),
CONSTRAINT Limo_pk PRIMARY KEY(l_id)
);
DROP TABLE Aclient;
CREATE TABLE Aclient (
c_id int,
c_name varchar2(50),
c_contact varchar2(30),
c_methpmt varchar2(20),
CONSTRAINT Aclient_pk PRIMARY KEY(c_id)
);
DROP TABLE Qualify;
CREATE TABLE Qualify (
q_id int,
l_id int,
d_id int,
CONSTRAINT Qualify_q_id_PK PRIMARY KEY(q_id),
CONSTRAINT Qualify_l_id_FK FOREIGN KEY(l_id) REFERENCES Limo(l_id),
CONSTRAINT Qualify_d_id_FK FOREIGN KEY(d_id) REFERENCES Driver(d_id)
);
DROP TABLE Rental;
CREATE TABLE Rental (
r_id INT,
r_date DATE,
r_fee FLOAT,
c_id INT,
q_id INT,
CONSTRAINT Rental_r_id_PK PRIMARY KEY(r_id),
CONSTRAINT Rental_c_id_FK FOREIGN KEY(c_id) REFERENCES Aclient(c_id),
CONSTRAINT Rental_q_id_FK FOREIGN KEY(q_id) REFERENCES Qualify(q_id)
);
INSERT INTO Driver VALUES (1,'alf','9022332332');
INSERT INTO Driver VALUES (2,'bob','9022322323');
INSERT INTO Limo VALUES
(1,'Car One', 'stretch limo');
INSERT INTO Limo VALUES
(2,'Car Two', 'hummer limo');
INSERT INTO Limo VALUES
(3,'Car Three', 'armored personnel carrier');
INSERT INTO Qualify VALUES
(1,1,1);
INSERT INTO Qualify VALUES
(2,2,1);
INSERT INTO Qualify VALUES
(3,3,1);
INSERT INTO Qualify VALUES
(4,1,2);
INSERT INTO Qualify VALUES
(5,2,2);
INSERT INTO Aclient VALUES
(1,'ann','9022332332', 'cash');
INSERT INTO Aclient VALUES
(2,'bub','9022322323', 'CC');
alter session set nls_date_format = 'yyyy/mm/dd';
INSERT INTO Rental VALUES
(1, '2015/3/21', 550, 2, 1);
INSERT INTO Rental VALUES
(2, '2015/3/21', 1000, 1, 5);
INSERT INTO Rental VALUES
(3, '2015/3/20', 2050, 1, 3);
INSERT INTO Rental VALUES
(4, '2015/3/19', 550, 1, 1);
INSERT INTO Rental VALUES
(5, '2015/3/20', 500, 2, 4);
COMMIT;
-- Q.1
INSERT INTO driver VALUES
(3,'Cal','9024919999');
INSERT INTO driver VALUES
(4,'Dan','9024914545');
COMMIT;
-- Q.2
INSERT INTO Qualify values
(6,1,3);
INSERT INTO Qualify values
(7,2,3);
INSERT INTO Qualify values
(8,3,3);
INSERT INTO Qualify values
(9,3,4);
select * from Qualify;
-- Q.3
UPDATE Rental
SET q_id = 4
where
r_id = 1;
UPDATE Rental
SET q_id = 3
WHERE
r_id = 4;
select * from rental;
-- Q.4
drop table replace_contents;
CREATE global TEMPORARY TABLE Replace_contents
as SELECT l_id, d_id FROM Qualify
WHERE d_id != 1;