利用mysql wampserver创建数据库及表格

方法一：（没看到具体要求忘记加学号了，方法二重新建表了）

1. 点击mysql wampserver菜单栏下的第二个项目“SQL”，进入数据结构语言编辑器；

输入CREATE DATABASE EXERCISES;语句，点击右下角执行创建数据库EXERCISES，

右栏数据树可以看到新出现数据库EXERCISES分支

 

图 5 创建数据库exercises

1. 点击左栏数据库EXERCISES分支，这时在点击菜单栏的SQL，再在里面输入语句就是在数

据库EXERCISES下执行的。根据题目要求创建2个表格EMP和DEPT。

图 6 利用图形化页面在已建的数据库下建表

图 7 建表成功

1. 写入数据，表格创建成功后可以在左栏数据库exercises下看到新创建的表格emp,点击后在点菜单栏的sql即可在表格下读写数据。输入INSERT INTO `emp`(`EMPNO`, `ENAME`, `JOB`, `MGR`, `HIREDATE`, `SAL`, `COMM`, `DEPTNO`) VALUES (7369,'SMITH','CLERK',7902,1990-12-17,13750,NULL,30)

图 8 插值

具体语句如下：(注意文档直接复制可能出现符号问题，作业附带建表sql文件)

DROP TABLE IF EXISTS EMP2019274072;         -- 删除同名表
DROP TABLE IF EXISTS DEPT2019274072;
CREATE TABLE DEPT2019274072                    -- 创建表格
       (DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
	);
CREATE TABLE EMP2019274072
       (EMPNO int(4)  not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE  DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
	);
  -- 赋值
INSERT INTO DEPT2019274072 ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'LONDON'); 
INSERT INTO DEPT2019274072 ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'PRESTON'); 
INSERT INTO DEPT2019274072 ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'LIVERPOOL'); 
INSERT INTO DEPT2019274072 ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'STAFFORD'); 
INSERT INTO DEPT2019274072 ( DEPTNO, DNAME, LOC ) VALUES ( 
50, 'MARKETING', 'LUTON'); 
commit;      -- 事务提交执行
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', 'CLERK', 7902,  '1990-12-17'
, 13750, NULL, 20); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7499, 'ALLEN', 'SALESMAN', 7698,  '1989-02-20'
, 19000, 6400, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7521, 'WARD', 'SALESMAN', 7698,  '1993-02-22'
, 18500, 4250, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7566, 'JONES', 'MANAGER', 7839,  '1989-04-02'
, 26850, NULL, 20); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7654, 'MARTIN', 'SALESMAN', 7698,  '1997-09-28'
, 15675, 3500, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7698, 'BLAKE', 'MANAGER', 7839,  '1990-05-01'
, 24000, NULL, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7782, 'CLARK', 'MANAGER', 7839,  '1988-06-09'
, 27500, NULL, 10); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19'
, 19500, NULL, 20); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7839, 'KING', 'PRESIDENT', NULL,  '1983-11-17'
, 82500, NULL, 10); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7844, 'TURNER', 'SALESMAN', 7698,  '1992-09-08'
, 18500, 6250, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7876, 'ADAMS', 'CLERK', 7788,  '1996-05-23'
, 11900, NULL, 20); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7900, 'JAMES', 'CLERK', 7698,  '1995-12-03'
, 12500, NULL, 30); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7902, 'FORD', 'ANALYST', 7566,  '1991-12-03'
, 21500, NULL, 20); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7934, 'MILLER', 'CLERK', 7782,  '1995-01-23'
, 13250, NULL, 10); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
3258, 'GREEN', 'SALESMAN', 4422,  '1995-07-24'
, 18500, 2750, 50); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
4422, 'STEVENS', 'MANAGER', 7839,  '1994-01-14'
, 24750, NULL, 50); 
INSERT INTO EMP2019274072 ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
6548, 'BARNES', 'CLERK', 4422,  '1995-01-16'
, 11950, NULL, 50); 
Commit;

 4.全部输入如图所示

图 9 建成表emp

5.同例建表dept如下

图 10 表dept

方法2：调用mysql控制台编写

1. 点击右下角绿色的warmserver下标，选mysql-mysql控制台进入，无密码回车进入

图 11 控制台打开

1. 指定将使用的数据库test    use test;

将建表及插值的相关语句以.sql文件形式（xujiani2019274072.sql）保存于E盘（路径不可有中文）,通过控制台导入,导入语句为source E:\ xujiani2019274072.sql;

图 12 建表数据导入

 

图 13 表emp

图 14 表dept