mysql下创建scott用户

金同学 2023-05-16

185

/* 功能：创建 scott 数据库中的 dept 表 */ 
CREATE TABLE dept ( 
	deptno INT UNSIGNED auto_increment PRIMARY KEY COMMENT '部门编号', 
	dname VARCHAR ( 15 ) COMMENT '部门名称', 
	loc VARCHAR ( 50 ) COMMENT '部门所在位置' 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '部门表';

/* 功能：创建 scott 数据库中的 emp 表 */
CREATE TABLE emp (
	empno INT UNSIGNED auto_increment PRIMARY KEY COMMENT '雇员编号',
	ename VARCHAR ( 15 ) COMMENT '雇员姓名',
	job VARCHAR ( 10 ) COMMENT '雇员职位',
	mgr INT UNSIGNED COMMENT '雇员对应的领导的编号',
	hiredate DATE COMMENT '雇员的雇佣日期',
	sal DECIMAL ( 7, 2 ) COMMENT '雇员的基本工资',
	comm DECIMAL ( 7, 2 ) COMMENT '奖金',
	deptno INT UNSIGNED COMMENT '所在部门',
	FOREIGN KEY ( deptno ) REFERENCES dept ( deptno ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '雇员表';

/* 功能：创建数据库 scott 中的 salgrade 表，工资等级表 */
CREATE TABLE salgrade ( 
	grade INT UNSIGNED COMMENT '工资等级', 
	losal INT UNSIGNED COMMENT '此等级的最低工资', 
	hisal INT UNSIGNED COMMENT '此等级的最高工资'
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '工资等级表';

/* 功能：创建数据库 scott 的 bonus 表，工资表 */
CREATE TABLE bonus (
	ename VARCHAR ( 10 ) COMMENT '雇员姓名',
	job VARCHAR ( 9 ) COMMENT '雇员职位',
	sal DECIMAL ( 7, 2 ) COMMENT '雇员工资',
	comm DECIMAL ( 7, 2 ) COMMENT '雇员资金' 
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '工资表';

/* 功能：插入数据库 scott 中表 dept 的初始化数据 */
INSERT INTO dept VALUES( 10, 'ACCOUNTING', 'NEW YORK' );
INSERT INTO dept VALUES( 20, 'RESEARCH', 'DALLAS' );
INSERT INTO dept VALUES( 30, 'SALES', 'CHICAGO' );
INSERT INTO dept VALUES( 40, 'OPERATIONS', 'BOSTON' );

/* 功能：插入数据库 scott 中表 emp 的初始数据 */
INSERT INTO emp VALUES( 7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20 );
INSERT INTO emp VALUES( 7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30 );
INSERT INTO emp VALUES( 7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30 );
INSERT INTO emp VALUES( 7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20 );
INSERT INTO emp VALUES( 7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30 );
INSERT INTO emp VALUES( 7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30 );
INSERT INTO emp VALUES( 7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10 );
INSERT INTO emp VALUES( 7788, 'SCOTT', 'ANALYST', 7566, '87-7-13', 3000, NULL, 20 );
INSERT INTO emp VALUES( 7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10 );
INSERT INTO emp VALUES( 7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30 );
INSERT INTO emp VALUES( 7876, 'ADAMS', 'CLERK', 7788, '87-7-13', 1100, NULL, 20 );
INSERT INTO emp VALUES( 7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30 );
INSERT INTO emp VALUES( 7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 3000, NULL, 20 );
INSERT INTO emp VALUES( 7934, 'MILLER', 'CLERK', 7782, '1982-1-23', 1300, NULL, 10 );

/* 功能：插入数据库 scott 中表 salgrade 的初始数据 */
INSERT INTO salgrade VALUES( 1, 700, 1200 );
INSERT INTO salgrade VALUES( 2, 1201, 1400 );
INSERT INTO salgrade VALUES( 3, 1401, 2000 );
INSERT INTO salgrade VALUES( 4, 2001, 3000 );
INSERT INTO salgrade VALUES( 5, 3001, 9999 );复制