Mysql数据库学习笔记 练习用数据库建表查询语句--2020-2-10

/*
SQLyog Ultimate v8.32
MySQL - 5.0.67-community-nt : Database - mysqldb

---

*/

/*!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/mysqldb /*!40100 DEFAULT CHARACTER SET utf8 */;

USE mysqldb;

/*Table structure for table dept */

DROP TABLE IF EXISTS dept;

CREATE TABLE dept (
DEPTNO int(2) NOT NULL auto_increment,
DNAME varchar(14) default NULL,
LOC varchar(13) default NULL,
PRIMARY KEY (DEPTNO)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8;

/*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 int(4) NOT NULL auto_increment,
ENAME varchar(10) default NULL,
JOB varchar(9) default NULL,
MGR int(4) default NULL,
HIREDATE date default NULL,
SAL double(7,2) default NULL,
COMM double(7,2) default NULL,
DEPTNO int(2) default NULL,
PRIMARY KEY (EMPNO),
KEY FK_DEPTNO (DEPTNO),
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES dept (DEPTNO)
) ENGINE=InnoDB AUTO_INCREMENT=8889 DEFAULT CHARSET=utf8;

/*Data for the table emp */

insert into emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800.00,NULL,20),(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600.00,300.00,30),(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250.00,500.00,30),(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975.00,NULL,20),(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250.00,1400.00,30),(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850.00,NULL,30),(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450.00,NULL,10),(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-04-19’,3000.00,NULL,20),(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000.00,NULL,10),(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500.00,0.00,30),(7876,‘ADAMS’,‘CLERK’,7788,‘1987-05-23’,1100.00,NULL,20),(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950.00,NULL,30),(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000.00,NULL,20),(7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300.00,NULL,10);

/*Table structure for table salgrade */

DROP TABLE IF EXISTS salgrade;

CREATE TABLE salgrade (
GRADE int(1) default NULL,
LOSAL int(5) default NULL,
HISAL int(5) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*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);

/*!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 */;