MYSQL:查询数据

一、学习目标

1. 了解基本查询语句
2. 掌握表单查询的方法
3. 掌握如何使用几何函数的查询
4. 掌握连接查询的方法
5. 掌握如何使用子查询
6. 熟悉合并查询结果
7. 熟悉如何为表和字段取别名
8. 掌握如何使用正则表达式查询
9. 掌握数据表的查询操作技巧和方法

二、实验内容

根据不同条件对表进行查询操作，掌握数据表的查询语句。Employee、dept表结构以及表中的记录如下表所示

employee表结构

dept表结构

employee表中的记录

dept表中的记录

步骤如下：

①创建数据表employee和dept

②将指定记录分别插入两个表中

③在employee表中，查询所有记录的e_no、e_name和e_salary字段值

④在employee表中，查询dept_no等于10和20的所有记录

⑤在employee表中，查询工资范围在800~2500之间的员工信息

⑥在employee表中，查询部门编号为20的部门中的员工信息

⑦在employee表中，查询每个部门最高工资的员工信息

⑧查询员工BLAKE所在部门和部门所在地

⑨使用连接查询，查询所有员工的部门和部门信息

⑩在employee表中，计算每个部门各有多少名员工

⑪在employee表中，计算不同类型职工的总工资数

⑫在employee表中，计算不同部门的平均工资

⑬在employee表中，查询工资低于1500的员工信息

⑭在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资从高到低排列 

⑮在employee表中，查询员工姓名以字母‘A’或‘S’开头的员工的信息

⑯在employee表中，查询到目前为止工龄大于等于18年的员工信息

1.

create table dept(d_no int(11) not null primary key unique ,d_name varchar(50) not null,d_location varchar(100));create index d_no on employee(dept_no);
/*索引,没索引给不了外键*/create table employee(e_no int(11) primary key not null unique,e_name varchar(50) not null,e_gender char(2),dept_no int(11) not null,e_job varchar(50) not null,e_salary int(11) not null,hireDate date not null ,foreign key(dept_no) references dept(d_no));

 2.

insert into dept(d_no,d_name,d_location)values(10,'ACCOUNTING','ShangHai'),
(20,'RESEARCH','BeiJing'),
(30,'SALES','ShenZhen'),
(40,'OPERATIONS','FuJian');insert into employee(e_no,e_name,e_gender,dept_no,e_job,e_salary,hireDate)values(1001,'SMITH','m',20,'CLERK',800,'2005-11-12'),(1002, 'ALLEN', 'f', 30, 'SALESMAN', 1600, '2003-05-12'),(1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12'),(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18'),(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12'),(1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15'),(1007,'CLARK','m',10,'MANAGER',2450,'2002-09-12'),(1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12'),(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01'),(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12'),(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05'),(1012,'JAMES','f',30,'CLERK',950,'2008-06-15');

 3.

select e_no,e_name,e_salary from employee;

4.

select * from employee where dept_no=10 or dept_no=20;

5.

 select * from employee where e_salary between 800 and 2500;

6.

select * from employee where dept_no=20;

7.

 select employee.* from 
(select dept_no as dno,max(e_salary)as salary from employee group by dept_no)as a 
inner join employee 
on employee.dept_no=a.dno and employee.e_salary=a.salary;

8.

select d_name,d_location from dept where d_no in (select dept_no from employee where e_name='BLAKE');

9.

 select d_no,d_name,d_location from deptinner join employeeon employee.dept_no=dept.d_no;

10.

 select dept.d_no,count(*)from employee join dept on employee.dept_no=dept.d_nogroup by dept.d_no;

11.

 select e_job,sum(e_salary)from employeegroup by e_job;

 12.

 select dept_no,avg(e_salary)from employeegroup by dept_no;

13.

select * from employee where e_salary<1500;

14.

 select * from employee order by dept_no desc;select * from employee order by e_salary desc;

 15.

 select * from employeewhere e_name like'A%'or e_name like'S%';

16.

select * from employee where hireDate<=(date_sub(curdate(),interval 18 year));