实验4 数据查询-mysql_实验四 数据库查询

1、  查询Departments表中的所有记录；

Use  yggl;

Select  *  from  departments;

2、  查询Employees表中员工的姓名，地址和电话；

Select name,address,phonenumber

From employees;

3、  查询Employees表中员工的部门号和性别，要求消除重复行；

Select distinct departmentid,sex

From employees;

4、  查询EmployeeID为000001的员工地址和电话，要求显示列名为address、telephone；

Select address as address,phonenumber as telephone

From employees

Where employeeid = ‘000001’;

5、  查询Employees表中员工的姓名和性别，要求性别为1时显示为“男”，为0时显示为“女”；

Select name,

Case

When  sex=1  then ‘男’

When  sex=0  then ‘女’

End as sex

From employees;

6、  计算Salary表中所有员工收入的平均值、最大值和最小值；

Select  avg(income),max(income),min(income)

From salary;

7、  计算所有员工的总支出；

Select sum(outcome) as ‘总支出’

From salary;

8、  计算员工总数；

Select  count(*)  as ‘员工总数’

From  employees;

9、  显示女员工的地址和电话；

Select name as ‘姓名’,address as ‘地址’,phonenumber as ‘电话’

From  employees

Where sex =0;

10、  查询月收入高于2000元的员工编号；

Select employeeid

From salary

Where income>2000;

11、  查询1970年以后出生的员工的姓名和地址；

Select name as’姓名’，address as ‘地址’

From employees

Where  birthday >’197-12-31’;

12、  查询地址中含有“中山”的员工编号和部门号；

Select name as’姓名’，employeeid as ‘员工编号’,departmentid  as ‘部门号’

From employees

Where address like ‘中山%’;

13、  查询员工编号中倒数第二个数字为0的姓名、地址和学历；

Select name,address,education

From employees

Where employeeid like ‘%0_’;

14、  找出所有收入在2000-3000元之间的员工编号和姓名；

Select name  as ’姓名’，salary.employeeid  as ‘员工编号’

From employees,salary

Where employees.employeeid=salary.employeeid

And income>2000 and income<3000;

15、  查询所有在部门“1”或“2” 工作的员工编号。

Select name as’姓名’，employeeid as ‘员工编号’

From employees

Where departmentid=1 or departmentid=2;