SQL练习数据采用ORACLE中的经典数据

/*
SQLyog 企业版 - MySQL GUI v8.14 
MySQL - 8.0.40 : Database - scott
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`scott` /*!40100 DEFAULT CHARACTER SET utf8mb3 */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `scott`;

/*Table structure for table `dept` */

DROP TABLE IF EXISTS `dept`;

CREATE TABLE `dept` (
  `DEPTNO` varchar(32) NOT NULL COMMENT '部门编号',
  `DNAME` varchar(32) DEFAULT NULL COMMENT '部门名称',
  `LOC` varchar(128) DEFAULT NULL COMMENT '所在地址',
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `dept` */

insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values ('10','ACCOUNTING','NEW YORK'),('20','RESEARCH','DALLAS'),('30','SALES','CHICAGO'),('40','OPERATIONS','BOSTON');

/*Table structure for table `emp` */

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (
  `EMPNO` varchar(32) NOT NULL COMMENT '员工编码',
  `ENAME` varchar(32) DEFAULT NULL COMMENT '员工姓名',
  `JOB` varchar(32) DEFAULT NULL COMMENT '岗位',
  `MGR` varchar(32) DEFAULT NULL COMMENT '上级领导',
  `HIRDATE` datetime DEFAULT NULL COMMENT '入职日期',
  `SAL` double DEFAULT NULL COMMENT '薪资',
  `COMM` double DEFAULT NULL COMMENT '奖金',
  `DEPTNO` varchar(32) DEFAULT NULL COMMENT '所属部门',
  PRIMARY KEY (`EMPNO`),
  KEY `FK_emp` (`DEPTNO`),
  CONSTRAINT `FK_emp` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `emp` */

insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIRDATE`,`SAL`,`COMM`,`DEPTNO`) values ('7369','SMITH','CLERK','7902','1980-12-17 00:00:00',800,NULL,'20'),('7499','ALLEN','SALESMAN','7698','1981-02-20 00:00:00',1600,300,'30'),('7521','WARD','SALESMAN','7698','1981-02-22 00:00:00',1250,500,'30'),('7566','JONES','MANAGER','7839','1981-04-02 00:00:00',2975,NULL,'20'),('7654','MARTIN','SALESMAN','7698','1981-09-28 00:00:00',1250,1400,'30'),('7698','BLAKE','MANAGER','7839','1981-05-01 00:00:00',2850,NULL,'30'),('7782','CLARK','MANAGER','7839','1981-06-09 00:00:00',2450,NULL,'10'),('7788','SCOTT','ANALYST','7566','1987-04-19 00:00:00',3000,NULL,'20'),('7839','KING','PRESIDENT',NULL,'1981-11-17 00:00:00',5000,NULL,'10'),('7844','TURNER','SALESMAN','7698','1981-09-08 00:00:00',1500,0,'30'),('7876','ADAMS','CLERK','7788','1987-05-23 00:00:00',1100,NULL,'20'),('7900','JAMES','CLERK','7698','1981-12-03 00:00:00',950,NULL,'30'),('7902','FORD','ANALYST','7566','1981-12-03 00:00:00',3000,NULL,'20'),('7934','MILLER','CLERK','7782','1982-01-23 00:00:00',1300,NULL,'10');

/*Table structure for table `salgrade` */

DROP TABLE IF EXISTS `salgrade`;

CREATE TABLE `salgrade` (
  `GRADE` int DEFAULT NULL,
  `LOSAL` double DEFAULT NULL,
  `HISAL` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

/*Data for the table `salgrade` */

insert  into `salgrade`(`GRADE`,`LOSAL`,`HISAL`) values (1,700,1200),(2,1201,1400),(3,1401,2000),(4,2001,3000),(5,3001,9999);

/*Table structure for table `t_book` */

DROP TABLE IF EXISTS `t_book`;

CREATE TABLE `t_book` (
  `id` int NOT NULL AUTO_INCREMENT,
  `bookName` varbinary(32) DEFAULT NULL COMMENT '书籍名称',
  `price` double DEFAULT NULL COMMENT '单价',
  `releaseDate` datetime DEFAULT NULL COMMENT '出版日期',
  `typeId` int DEFAULT NULL COMMENT '所属类别',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3;

/*Data for the table `t_book` */

insert  into `t_book`(`id`,`bookName`,`price`,`releaseDate`,`typeId`) values (1,'Think in Java',99.99,'0000-00-00 00:00:00',1),(2,'C语言设计之美',99.8,'0000-00-00 00:00:00',1),(3,'上林赋',9.99,NULL,2),(4,'三毛',2,NULL,3),(5,'邪骨咒',3,NULL,3),(6,'皇太极',5,NULL,NULL);

/*Table structure for table `t_booktype` */

DROP TABLE IF EXISTS `t_booktype`;

CREATE TABLE `t_booktype` (
  `id` int NOT NULL AUTO_INCREMENT,
  `typeName` varchar(32) DEFAULT NULL,
  `orderNum` int DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

/*Data for the table `t_booktype` */

insert  into `t_booktype`(`id`,`typeName`,`orderNum`) values (1,'编程',1),(2,'文学',2),(3,'恐怖',3);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;