测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。
sql语句学习全攻略
创建表
1、创建student和score表
create or replace table employees(
empID INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empName VARCHAR(20) NOT NULL , #姓名
sex VARCHAR(4) , #性别
birth date, #出生日期
deptID INT(20) , #部门编码
jobs VARCHAR(20) , #工作岗位
firJob date, #开始工作的日期
hiredate date,#开始工作的日期
politicalStatus VARCHAR(20) , # 政治面貌
leader INT(10) #领导编码
);
create or replace table salary(
sid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
empID INT(10) not null,
salary INT(10) not null,#工资
lastedit date #上次调薪日期
);
create or replace table departments(
deptid INT(10) NOT NULL UNIQUE PRIMARY KEY ,
deptname VARCHAR(20) not null,#部门名称
faterdeptid INT(10) # 上级部门编码
);
问题列表
单表查询:
1、显示所有职工的基本信息。
2、查询所有职工所属部门的部门号,不显示重复的部门号。
3、求出所有职工的人数。
4、列出最高工和最低工资。
5、列出职工的平均工资和总工资。
6、创建一个只有职工号、姓名和参加工作的新表,名为工作日期表。
7、显示所有女职工的年龄
8、列出所有姓刘的职工的职工号、姓名和出生日期。
9、列出1990年以前出生的职工的姓名、参加工作日期。
10、列出总人数大于4的部门号和总人数。
11、列出所有陈姓和李姓的职工姓名。
12、列出所有部门号为1002和1003的职工号、姓名。
13、将职工表worker中的职工按出生的先后顺序排序。
14、求出各部门党员的人数。
多表查询:
1、列出每名职工的职工号、姓名和部门名。
2、列出市场部的所有女职工的姓名和政治面貌。
3、显示所有职工的姓名、部门名和工资数。
4、显示所有职工的职工号、姓名、部门名和工资,并按部门名顺序排列。
5、显示各部门名和该部门的所有职工平均工资。
6、显示所有平均工资高于1200的部门名和对应的平均工资。
7、查询刘欣所在的部门。
8、查询部门名为财务处的职工的情况。
9、列出工资在1000-2000之间的所有职工姓名。
10、显示工资最高的前3名职工的职工号和姓名。
数据检索-单表查询(参考答案)
1、显示所有职工的基本信息
select * from employees
2、查询所有职工所属部门的部门号,不显示重复的部门号
select distinct e.deptID from employees e
1、从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
SELECT id,name,department FROM student;
3、求出所有职工的人数
select count(1) from employees
4、列出最高工和最低工资
select max(s.salary) as '最高工资',min(s.salary) as '最低工资' from salary s
5、列出职工的平均工资和总工资
select AVG(s.salary) as '最高工资',SUM(s.salary) as '最低工资' from salary s
6、创建一个只有职工号、姓名和参加工作的新表,名为工作日期表
Create table emp2 (select e.empID,e.empName,e.firJob from employees e);
在其他数据库中,可以尝试使用:
select e.empID,e.empName,e.firJob into newtable from employees e;
7、显示所有女职工的年龄
select e.empID, e.empName, (DATEDIFF(CURDATE(),e.birth) DIV 365) as '年龄' from employees e where e.sex = '女';
8、列出所有姓刘的职工的职工号、姓名和出生日期
select e.empID,e.empName,e.birth from employees e where e.empName like '张%'
9、列出1990年以前出生的职工的姓名、参加工作日期
select e.empName,e.firJob from employees e where YEAR(e.firJob)<1990
10、列出总人数大于4的部门号和总人数
select e.deptID, count(1) from employees e group by e.deptID having count(1)>4
10、列出总人数大于4的部门号和总人数
select e.deptID, count(1) from employees e group by e.deptID having count(1)>4
10、列出总人数大于4的部门号和总人数
select e.deptID, count(1) from employees e group by e.deptID having count(1)>4
11、列出所有陈姓和李姓的职工姓名
select e.empName from employees e where e.empName like'张%' or e.empName like'李%'
12、列出所有部门号为1002和1003的职工号、姓名
select e.empID, e.empName from employees e where e.deptID in (1002,1003);
13、将职工表worker中的职工按出生的先后顺序排序
select * from employees e order by e.birth asc
14、求出各部门党员的人数
select e.deptID,count(1) from employees e where e.politicalStatus = '党员' GROUP BY e.deptID
数据检索-多表查询(参考答案)
1、列出每名职工的职工号、姓名和部门名
select e.empID, e.empName, d.deptname from employees e,departments d where e.deptID = d.deptid
2、列出市场部的所有女职工的姓名和政治面貌
select e.empName,e.politicalStatus,d.deptname from employees e,departments d where e.deptID = d.deptid and e.sex= '女' and d.deptname = '市场部'
3、显示所有职工的姓名、部门名和工资数
select e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID
4、显示所有职工的职工号、姓名、部门名和工资,并按部门名顺序排列
select e.empid, e.empName,d.deptname,s.salary from employees e LEFT JOIN departments d on e.deptID = d.deptid LEFT JOIN salary s on e.empID = s.empID ORDER BY e.deptID
5、显示各部门名和该部门的所有职工平均工资
SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname
6、显示所有平均工资高于1200的部门名和对应的平均工资
SELECT d.deptname, AVG(s.salary) from departments d LEFT JOIN employees e on d.deptid = e.deptID LEFT JOIN salary s on e.empID = s.empID GROUP BY d.deptname having AVG(s.salary)>1200
7、查询刘欣所在的部门
select d.deptname from employees e, departments d where e.deptID = d.deptid and e.empName = '刘欣'
或者
select d.deptname from departments d where d.deptid =(select deptid from employees e where e.empName ='刘欣')
8、查询部门名为财务处的职工的情况
select * from employees e,departments d where e.deptID = d.deptid and d.deptname ='财务处'
或者
select * from employees e where e.deptid =(select deptid from departments d where d.deptname ='财务处')
9、列出工资在1000-2000之间的所有职工姓名
select e.empName,s.salary from salary s,employees e where s.salary between 1000 and 2000 and s.empID = e.empID
10、显示工资最高的前3名职工的职工号和姓名
select e.empID, e.empName from salary s, employees e where s.empID = e.empID ORDER BY s.salary desc LIMIT 3