实战演练——数据库查询操作

第一步，先创建一个数据库使用数据库：

create databases test;

use test;

第二步，创建两个表：

DROP TABLE IF EXISTS dept; -- 列表如果存在先进行删除
CREATE TABLE dept(
  d_no int(11) NOT NULL AUTO_INCREMENT,
  d_name varchar(100) NOT NULL,
  d_location char(2) DEFAULT NULL,
  PRIMARY KEY(d_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS employee;  -- 列表如果存在先进行删除
CREATE TABLE employee_01 (
  e_no int(11) NOT NULL,
  e_name varchar(100) NOT NULL,
  e_gender char(2) DEFAULT NULL,
  dept_no int(11) NOT NULL,
  e_job varchar(100) NOT NULL,
  e_salary smallint(6) NOT NULL,
  hireDate date NOT NULL,
  PRIMARY KEY (e_no),
  KEY fk_dept_employee_01 (dept_no),
  CONSTRAINT fk_dept_employee_01 FOREIGN KEY (dept_no) REFERENCES dept (d_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第三步，插入dept和employee的数据：

-- 插入dept中的数据
INSERT INTO dept VALUES ('20', 'RESEARCH', 'BeiJing');
INSERT INTO dept VALUES ('30', 'SALES', 'ShenZhen');
INSERT INTO dept VALUES ('40', 'OPERATIONS', 'FuJian');

-- 插入employee中的数据
INSERT INTO employee_01 VALUES ('1001', 'SMITH', 'm', '20', 'CLERK', '800', '2015-11-12');
INSERT INTO employee_01 VALUES ('1002', 'ALLEN', 'f', '30', 'SALESMAN', '1600', '2003-05-12');
INSERT INTO employee_01 VALUES ('1003', 'WARD', 'f', '30', 'SALESMAN', '1250', '2003-05-12');
INSERT INTO employee_01 VALUES ('1004', 'JONES', 'm', '20', 'MANAGER', '2975', '2003-05-12');
INSERT INTO employee_01 VALUES ('1005', 'MANTIN', 'm', '30', 'SALESMAN', '1250', '1998-05-18');
INSERT INTO employee_01 VALUES ('1006', 'BLAKE', 'f', '30', 'MANAGER', '2850', '2001-06-12');
INSERT INTO employee_01 VALUES ('1008', 'SCOTT', 'm', '20', 'ANALYST', '3000', '2002-09-12');
INSERT INTO employee_01 VALUES ('1010', 'TURNER', 'f', '30', 'SALESMAN', '1500', '1997-10-12');
INSERT INTO employee_01 VALUES ('1011', 'ADAMS', 'm', '20', 'CLERK', '1100', '1999-10-05');
INSERT INTO employee_01 VALUES ('1012', 'JAMES', 'f', '30', 'CLERK', '950', '2008-06-15');

第四步，查询

-- 查询e_no,e_name,e_salary字段
SELECT e_no,e_name,e_salary FROM employee_01;

-- 查询dept_no 等于10,20的记录
SELECT * FROM employee_01 WHERE dept_no IN(10,20);

-- 查询工资800到2500
SELECT * FROM employee_01 WHERE e_salary BETWEEN 600 AND 2500;

-- 查询编号为20的部门的员工信息
SELECT * FROM employee_01 WHERE dept_no = 20;

-- 查询每个部门的最高工资的员工信息
SELECT dept_no,MAX(e_salary) FROM employee_01 GROUP BY dept_no;

-- 查询BLAKE所在部门和所在地
SELECT d_no, d_location FROM dept WHERE d_no=(SELECT dept_no FROM employee_01 WHERE e_name='BLAKE');

-- 查询所有员工的部门和部门信息
SELECT e_no,e_name,d_no,d_name,d_location FROM employee_01,dept WHERE dept.d_no=employee_01.dept_no;

-- 查询计算每个部门有多少员工
SELECT dept_no,COUNT(*) FROM employee_01 GROUP BY dept_no;

-- 查询不同类型职工的总工资
SELECT e_job,SUM(e_salary) FROM employee_01 GROUP BY e_job;

-- 查询不同部门的平均工资
SELECT dept_no,AVG(e_salary) FROM employee_01 GROUP BY dept_no;

-- 查询工资低于1500员工信息
SELECT e_name,dept_no,e_salary FROM employee_01 ORDER BY dept_no DESC,e_salary DESC;

-- 查询员工姓名以‘A‘’S‘开头的员工信息
SELECT * FROM employee_01 WHERE e_name REGEXP '^[as]';

-- 查询工领大于15年的员工信息
SELECT * FROM employee_01 WHERE YEAR(CURDATE()) -YEAR(hireDate) >=15;

以下图片展示：

第二步：

第三步：

第四步：