数据表查询操作

-- 建立数据库
create database ch_5;
-- 链接数据库
use ch_5;

-- 新建数据表dept
create table dept
(
d_no int primary key auto_increment,
d_name varchar(50) not null,
d_location varchar(100)
)engine=InnoDB;

-- 新建数据表employee
create table employee
(
e_no int primary key,
e_name varchar(100) not null,
e_gender char(2),
dept_no int not null,
e_job varchar(100)not null,
e_salary smallint not null,
hiredate date not null,
constraint fk_employee_dept foreign key(dept_no) references dept(d_no)
)engine=InnoDB;

-- 插入数据  

insert into dept values(10,'ACCOUNTING','ShangHai');

insert into dept values(20,'RESEATCH','BeiJing');

insert into dept values(30,'SALES','ShenZhen');

insert into dept values(40,'OPERATIONS','FuJian');

insert into employee values(1001,'SMITH','m',20,'CLERK',800,'2005-11-12');

insert into employee values(1002,'ALLEN','m',30,'SALESMAN',1600,'2003-5-12');

insert into employee values(1003,'WARD','F',30,'SALESMAN',1250,'2005-11-12');

insert into employee values(1004,'JONDS','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,'BLAKS','m',30,'MANAGER',2850,'1997-02-15');

insert into employee values(1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12');

insert into employee values(1008,'SCOTT','m',20,'CLERK',3000,'2003-05-12');

insert into employee values(1009,'KING','m',10,'PRESIDENT',5000,'1995-01-01');

insert into employee values(10010,'TURNER','m',30,'SALESMAN',1500,'1997-10-12');

insert into employee values(10011,'ADAMS','m',20,'CLERK',1100,'1999-10-05');

insert into employee values(10012,'JAMES','m',10,'CLERK',950,'2008-06-15');

-- 在employee表中，查询所有记录的e_no,e_name，e_salary字段值
select e_no,e_name,e_salary from employee;

 

-- 在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或S开头的员工的信息
select * from employee where e_name regexp '^[as]';

-- 在employee表中，查询到目前为止，工龄大于等于15的员工信息
select * from employee where year(CURDATE()) -year(hiredate)>=15;

-- 字段e_no进行升序排序  DESC 默认降序
select  * from employee ORDER BY e_no DESC;

-- 字段e_job和e_no进行升序排序  ASC默认升序
select  * from employee ORDER BY e_job,e_no ASC;

-- 统计所以工资
select COUNT(e_salary) from employee;

-- 计算平均工资
select AVG(e_salary) from employee;

-- 计算同一工作岗位的平均工资
SELECT     e_job,avg(e_salary) as '平均工资' from employee GROUP BY e_job;

-- 统计同一岗位平均工资高于2500
SELECT     e_job,avg(e_salary) as '平均工资' from employee GROUP BY e_job HAVING AVG(e_salary)>2500;

-- 使用limit 显示从首行开始的四行
select * from employee LIMIT 4;

-- 使用limit 显示从第四行后开始的四行
select * from employee LIMIT 4,4;

-- 使用limit 显示从第八行后开始的四行
select * from employee LIMIT 8,4;