数据表查询操作

案例测试：employee，dept表结构以表的记录

创建数据库，连接数据库

创建表：

CREATE TABLE dept
(
 d_no INT PRIMARY KEY AUTO_INCREMENT,
 d_name  VARCHAR(50),
 d_location VARCHAR(100)
 )ENGINE=InnoDB;
 
 CREATE TABLE employee
 (
 e_no   INT PRIMARY KEY,
 e_name VARCHAR(10) NOT NULL,
 e_gender CHAR(2) NOT NULL,
 dept_no INT NOT NULL,
 e_job VARCHAR(100) NOT NULL,
 e_salary SMALLINT NOT NULL,
 hireDate DATE,
 CONSTRAINT dno_fk FOREIGN KEY (dept_no)REFERENCES dept(d_no)
 )ENGINE=InnoDB;
 
 INSERT INTO dept VALUES (10,'ACCOUTING','ShangHai'),(20,'RESEARCH','Beijig'),(30,'SALES','Shenzhen'),(40,'OPERATIONS','FuJian');
 
INSERT INTO employee VALUES(1001,'SMITH','m',20,'CLERK',800,'2005-11-12');
INSERT INTO employee VALUES(1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12');
INSERT INTO employee VALUES(1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12');
INSERT INTO employee VALUES(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18');
INSERT INTO employee VALUES(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12');
INSERT INTO employee VALUES(1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15');
INSERT INTO employee VALUES(1007,'CLARK','m',10,'MANAGER',2450,'2002-9-12');
INSERT INTO employee VALUES(1008,'SCOTT','m',20,'ANALYST',3000,'2003-09-12');
INSERT INTO employee VALUES(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01');
INSERT INTO employee VALUES(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12');
INSERT INTO employee VALUES(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05');
INSERT INTO employee VALUES(1012,'JAMES','m',30,'CLERK',950,'2008-06-15');

SELECT * FROM dept;

SELECT * FROM employee;

- SELECT e_no，e_name，e_salary字段值
SELECT e_no,e_name,e_salary;

-- 在employee表中，查询dept_no等于10和20的所有记录
SELECT * FROM employee WHERE dept_no IN(10,20);

--在employee表中，查询工资为800·2500的员工信息。
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;

--在employee表中，查询部门编号为20的部门中的员工信息
SELECT * FROM employee WHERE dept_no=20;

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

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

--使用连接查询，查询所有员工的部门和部门信息。
SELECT e_no,e_name,dept_no,d_name,d_location FROM employee,dept WHERE dept.d_no=employee.dept_no;

--在employee表中，计算每个部门各有多少名员工。
SELECT dept_no,COUNT(*)FROM employee GROUP BY dept_no;

-- 在employee表中，计算不同类型职工的总工资数。
SELECT e_job,SUM(e_salary) FROM employee GROUP BY e_job;

--在employee表中，计算不同部门的平均工资。
SELECT dept_no,AVG(e_salary) FROM employee GROUP BY dept_no;

-- 在employee表中，查询工资低于1500的员工信息。
SELECT * FROM employee WHERE e_salary < 1500;

--在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。
SELECT e_name,dept_no,e_salary FROM employee ORDER BY dept_no DESC, e_salary DESC;

--在employee表中，查询员工姓名以字母’A',或'B'开头的员工信息。
SELECT * FROM employee WHERE e_name REGEXP '^[as]';

-- 在employee表中，查询到目前为止。工龄大于等于15年的员工信息。
SELECT * FROM employee where YEAR(CURDATE()) -YEAR(hireDate) >= 15;