MySQL学习二

前言：本文主要是对SQL中的DQL数据查询进行一些案例的操作，希望对大家有所帮助。

一、创建数据表与插入数据

1.1 创建数据表

1.1.1 员工表

CREATE TABLE `emp` (
  `empno` int NOT NULL,
  `ename` varchar(20) DEFAULT NULL,
  `job` varchar(20) DEFAULT NULL,
  `mgr` int DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` double DEFAULT NULL,
  `comm` int DEFAULT NULL,
  `deptno` int DEFAULT NULL,
  PRIMARY KEY (`empno`)
) 

1.1.2 部门表

CREATE TABLE `dept` (
  `deptno` int NOT NULL,
  `dname` varchar(20) DEFAULT NULL,
  `loc` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`deptno`)
)

1.1.3 工资等级表

CREATE TABLE `salgrade` (
  `grade` int NOT NULL,
  `losal` int DEFAULT NULL,
  `hisal` int DEFAULT NULL,
  PRIMARY KEY (`grade`)
)

1.2 给每张表添加对应的数据

1.2.1 给部门表插入数据

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');

1.2.2 给员工表插入数据

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (2121, '%cai10', 'CLEAK', null, null, null, null, null);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (3131, 'S_ssTY', null, null, null, null, null, null);
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