【MySQL练习总结】

前情回顾

之前我给大家分享了数据库的基础语言，包括创建表、修改、添加数据、还有查询等等操作。接下来呢，就是收获成果的时候啦，后面会给大家分享新的知识，现在就跟着我一起练习一下叭。

表的准备

准备四张表：dept(部门表),emp(员工表),salgrade(薪资等级表),bonus(奖金表)

创建表

create table DEPT(    DEPTNO int(2) not null,    DNAME  VARCHAR(14),    LOC    VARCHAR(13)  );  alter table DEPT    add constraint PK_DEPT primary key (DEPTNO);  create table EMP  (    EMPNO    int(4) primary key,    ENAME    VARCHAR(10),    JOB      VARCHAR(9),    MGR      int(4),    HIREDATE DATE,    SAL      double(7,2),    COMM     double(7,2),    DEPTNO   int(2)  );  alter table EMP    add constraint FK_DEPTNO foreign key (DEPTNO)    references DEPT (DEPTNO);   create table SALGRADE  (    GRADE int primary key,    LOSAL double(7,2),    HISAL double(7,2)  );  create table BONUS  (    ENAME VARCHAR(10),    JOB   VARCHAR(9),    SAL   double(7,2),    COMM  double(7,2)  );  

插入数据

insert into DEPT (DEPTNO, DNAME, LOC)  values (10, 'ACCOUNTING', 'NEW YORK');  insert into DEPT (DEPTNO, DNAME, LOC)  values (20, 'RESEARCH', 'DALLAS');  insert into DEPT (DEPTNO, DNAME, LOC)  values (30, 'SALES', 'CHICAGO');  insert into DEPT (DEPTNO, DNAME, LOC)  values (40, 'OPERATIONS', 'BOSTON');  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  insert into SALGRADE (GRADE, LOSAL, HISAL)  values (1, 700, 1200);  insert into SALGRADE (GRADE, LOSAL, HISAL)  values (2, 1201, 1400);  insert into SALGRADE (GRADE, LOSAL, HISAL)  values (3, 1401, 2000);  insert into SALGRADE (GRADE, LOSAL, HISAL)  values (4, 2001, 3000);  insert into SALGRADE (GRADE, LOSAL, HISAL)  values (5, 3001, 9999);  

查看表

select * from dept; -- 部门表：dept:department 部分 ，loc - location 位置select * from emp;-- 员工表：emp:employee 员工   ,mgr :manager上级领导编号，hiredate 入职日期  firedate 解雇日期 ，common：补助-- deptno 外键 参考  dept - deptno字段-- mgr 外键  参考  自身表emp - empno  产生了自关联select * from salgrade;-- losal - lowsal-- hisal - highsalselect * from bonus;

大家一定要多多练习哦，这样才能掌握学到的新知识，下期跟大家一起练习表的操作。Practice Makes Perfect——熟能生巧！！！