【MySQL】创建【Oracle】的SCOTT用户的表和数据
xixuefeng
MySQL, MySQL-DEV, Oracle, SQL&Function
2017-09-18 9:42:21
1,350 次浏览
Oracle&MySQL'SQL
【MySQL】创建【Oracle】的SCOTT用户的表和数据已关闭评论
由于最近想写一点儿关于Oracle与MySQL之间SQL语句的比对写法,由于对Oracle比较了解,SCOTT用户对使用Oracle的小伙伴来说再熟悉不过了,所以,在MySQL测试时,我们也采用Oracle SCOTT 下的表和数据。
在MySQL的命令行下创建表和导入数据
1:创建数据库
1 |
mysql> CREATE DATABASE `scott` CHARACTER SET 'utf8'; |
2:创建表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
create table scott.bonus ( ename varchar(10), job varchar(9), sal decimal(10,2), comm decimal(10,2) ) ; create table scott.dept ( deptno int(2) , dname varchar(14), loc varchar(13) ) ; alter table scott.dept add constraint pk_dept primary key (deptno); create table scott.emp ( empno int(4) not null, ename varchar(10), job varchar(9), mgr int(4), hiredate datetime, sal decimal(7,2), comm decimal(7,2), deptno int(2) ) ; alter table scott.emp add constraint pk_emp primary key (empno); alter table scott.emp add constraint fk_deptno foreign key (deptno) references scott.dept (deptno); create table scott.salgrade ( grade int, losal int, hisal int ) ; |
3:导入数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
insert into scott.dept (deptno, dname, loc) values (10, 'ACCOUNTING', 'NEW YORK'); insert into scott.dept (deptno, dname, loc) values (20, 'RESEARCH', 'DALLAS'); insert into scott.dept (deptno, dname, loc) values (30, 'SALES', 'CHICAGO'); insert into scott.dept (deptno, dname, loc) values (40, 'OPERATIONS', 'BOSTON'); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7369, 'SMITH', 'CLERK', 7902, str_to_date('17-12-1980', '%d-%m-%Y'), 800, null, 20); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALESMAN', 7698, str_to_date('20-02-1981', '%d-%m-%Y'), 1600, 300, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7521, 'WARD', 'SALESMAN', 7698, str_to_date('22-02-1981', '%d-%m-%Y'), 1250, 500, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7566, 'JONES', 'MANAGER', 7839, str_to_date('02-04-1981', '%d-%m-%Y'), 2975, null, 20); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALESMAN', 7698, str_to_date('28-09-1981', '%d-%m-%Y'), 1250, 1400, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7698, 'BLAKE', 'MANAGER', 7839, str_to_date('01-05-1981', '%d-%m-%Y'), 2850, null, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7782, 'CLARK', 'MANAGER', 7839, str_to_date('09-06-1981', '%d-%m-%Y'), 2450, null, 10); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7788, 'SCOTT', 'ANALYST', 7566, str_to_date('19-04-1987', '%d-%m-%Y'), 3000, null, 20); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7839, 'KING', 'PRESIDENT', null, str_to_date('17-11-1981', '%d-%m-%Y'), 5000, null, 10); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7844, 'TURNER', 'SALESMAN', 7698, str_to_date('08-09-1981', '%d-%m-%Y'), 1500, 0, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7876, 'ADAMS', 'CLERK', 7788, str_to_date('23-05-1987', '%d-%m-%Y'), 1100, null, 20); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7900, 'JAMES', 'CLERK', 7698, str_to_date('03-12-1981', '%d-%m-%Y'), 950, null, 30); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7902, 'FORD', 'ANALYST', 7566, str_to_date('03-12-1981', '%d-%m-%Y'), 3000, null, 20); insert into scott.emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (7934, 'MILLER', 'CLERK', 7782, str_to_date('23-01-1982', '%d-%m-%Y'), 1300, null, 10); insert into scott.salgrade (grade, losal, hisal) values (1, 700, 1200); insert into scott.salgrade (grade, losal, hisal) values (2, 1201, 1400); insert into scott.salgrade (grade, losal, hisal) values (3, 1401, 2000); insert into scott.salgrade (grade, losal, hisal) values (4, 2001, 3000); insert into scott.salgrade (grade, losal, hisal) values (5, 3001, 9999); |
4:小结
1)MySQL与Oracle中数据类型的区别
Oracle的varchar2对应MySQL的varchar;
Oracle的number(5)对应MySQL的int(5);
Oracle的number(10,2)对应MySQL的decimal(10,2);
2)转换函数(字符串转换为日期型,以’2008-08-08’为例)
Oracle:to_date(‘2008-08-08′,’yyyy-mm-dd’)
MySQL:str_to_date(‘2008-08-08’, ‘%Y-%m-%d’)