Oracle中的emp、dept、bonus及salgrade表的新建及MySQL中的三种注释形式

最新推荐文章于 2023-03-20 16:50:08 发布

陈瑞红的博客

最新推荐文章于 2023-03-20 16:50:08 发布

阅读量820

点赞数 2

分类专栏： Oracle MySQL

本文链接：https://blog.csdn.net/wendy_yuanyuan/article/details/89137485

版权

MySQL 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

Oracle

0 篇文章 0 订阅

订阅专栏

/*下面会包含三种mysql的注释形式
1、#...
2、"-- ..."（--后需加一个空格）
3、*/

#1、创建dept表;插入dept表中的值
CREATE TABLE dept (
deptno INT ( 2 ) NOT NULL,
dname VARCHAR ( 14 ),
loc VARCHAR ( 13 ),
CONSTRAINT pk_dept PRIMARY KEY ( deptno )
);

INSERT INTO dept ( deptno, dname, loc )
VALUES
   ( 10, 'ACCOUNTING', 'NEW YORK' ),
   ( 20, 'RESEARCH', 'DALLAS' ),
   ( 30, 'SALES', 'CHICAGO' ),
   ( 40, 'OPERATIONS', 'BOSTON' );

/*2、创建emp表;
插入emp表中的值
*/
SELECT * FROM emp;
CREATE TABLE emp (
empno INT ( 4 ) NOT NULL PRIMARY KEY,
ename VARCHAR ( 10 ),
job VARCHAR ( 9 ),
mgr INT ( 4 ),
hiredate DATE,
sal FLOAT ( 7, 2 ),
comm FLOAT ( 7, 2 ),
deptno INT ( 2 ),
CONSTRAINT fk_deptno FOREIGN KEY ( deptno ) REFERENCES dept ( deptno )
);

INSERT INTO EMP ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
VALUES
   ( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 ),
   ( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30 ),
   ( 7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30 ),
   ( 7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20 ),
   ( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30 ),
   ( 7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30 ),
   ( 7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10 ),
   ( 7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 3000, NULL, 20 ),
   ( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-07', 5000, NULL, 10 ),
   ( 7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30 ),
   ( 7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 1100, NULL, 20 ),
   ( 7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30 ),
   ( 7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20 ),
   ( 7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10 );

-- 3. 创建bonus表
SELECT * FROM bonus;
CREATE TABLE bonus ( ename VARCHAR ( 10 ), job VARCHAR ( 9 ), sal INT, comm INT );

-- 4、创建salgrade表
SELECT * FROM salgrade;
CREATE TABLE salgrade ( grade INT NOT NULL AUTO_INCREMENT, losal INT, hisal INT, PRIMARY KEY ( grade ) );

INSERT INTO SALGRADE ( grade, losal, hisal )
VALUES
   ( 1, 700, 1200 ),
   ( 2, 1201, 1400 ),
   ( 3, 1401, 2000 ),
   ( 4, 2001, 3000 ),
   ( 5, 3001, 9999 );