方法一:(没看到具体要求忘记加学号了,方法二重新建表了)
- 点击mysql wampserver菜单栏下的第二个项目“SQL”,进入数据结构语言编辑器;
输入CREATE DATABASE EXERCISES;语句,点击右下角执行创建数据库EXERCISES,
右栏数据树可以看到新出现数据库EXERCISES分支
图 5 创建数据库exercises
- 点击左栏数据库EXERCISES分支,这时在点击菜单栏的SQL,再在里面输入语句就是在数
据库EXERCISES下执行的。根据题目要求创建2个表格EMP和DEPT。
图 6 利用图形化页面在已建的数据库下建表
图 7 建表成功
- 写入数据,表格创建成功后可以在左栏数据库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控制台编写
- 点击右下角绿色的warmserver下标,选mysql-mysql控制台进入,无密码回车进入
图 11 控制台打开
- 指定将使用的数据库test use test;
将建表及插值的相关语句以.sql文件形式(xujiani2019274072.sql)保存于E盘(路径不可有中文),通过控制台导入,导入语句为source E:\ xujiani2019274072.sql;
图 12 建表数据导入
图 13 表emp
图 14 表dept