MySQL查询题目

MySQL查询题目

2024 年 06 月 04 日

340 次浏览

3851字数

```sql
数据准备：
create database  test2;
use test2;
-- 创建部门表
create table dept(
	deptno int primary key,  -- 部门编号
	dname varchar(20), -- 部门名称
	loc varchar(20)  -- 部门地址
);
-- 给部门表添加数据
insert into dept values(10,'accounting','new york'),(20,'research','dallas'),(30,'sales','chigago'),(40,'operations','boston');

-- 创建员工表 
create table emp(
	empno int primary key, -- 员工编号 
	ename varchar(20), -- 员工姓名 
	job varchar(9), -- 员工工作 
	mgr int, -- 员工直属领导编号 
	hiredate date, -- 入职时间
	sal double, -- 员工工资 
	comm double, -- 员工奖金 
	deptno int -- 对应dept表的外键
);

-- 添加 部门 和 员工 之间的主外键关系
alter table emp add CONSTRAINT foreign key(deptno) REFERENCES dept(deptno);

-- 给员工表添加数据 
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman', 7698, '1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566, 'jones','manager' , 7839,'1981-04-02' ,2975,null, 20);
insert into emp values(7654,'martin','salesman', 7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager', 7839, '1981-06-09' , 2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3900,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13', 1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null, 20);
insert into emp values(7934, 'miller', 'clerk',7782,'1981-01-23',1300,null,10);

-- 创建工资等级表 
create table salgrade(
	grade int, -- 工资等级 
	losal double, -- 最低工资 
	hisal double  -- 最高工资 
);

-- 给工资等级表添加数据 
insert into salgrade values (1, 700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

-- 练习
-- 1、返回工资高于 30部门所有员工工资水平的员工信息
-- select * from emp where deptno in(select deptno from emp where sal>30);
SELECT * FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30);
SELECT * FROM emp WHERE sal > all(SELECT sal FROM emp WHERE deptno = 30);

-- 2、返回员工工作及其从事此工作的最低工资
-- select job from emp;
-- select min(sal) from emp;
select  job,min(sal) sal from emp group by job ;

-- 3、计算出员工的年薪，并且以年薪排序
select sal,comm from emp;
-- ifnull(comm,0) comm没有就用0代替
select ename,(sal*12+ifnull(comm,0)) year_sal from emp order by year_sal ;

-- 4、返回工资处于第四级别的员工的姓名
-- select hisal from salgrade where grade='4';
-- select losal from salgrade where grade='4';
select * from emp where sal between (select losal from salgrade where grade='4') and (select hisal from salgrade where grade='4');

select * from emp where sal >= (select losal from salgrade where grade='4') and sal<=(select hisal from salgrade where grade='4');

-- 5、返回工资为二等级的职员名字、部门所在地
-- select * from emp,salgrade;
-- select * from emp,salgrade,dept where grade='2';

select ename,loc 
from (select * from emp where sal between (select losal from salgrade where grade='4') and (select hisal from salgrade where grade='4')) a,dept b 
where a.deptno=b.deptno;

select ename,dname from emp join dept 
on emp.deptno=dept.deptno 
and (sal between (select losal from salgrade where grade='2') and (select hisal from salgrade where grade=2));

```
 
 
-- exists 子查询关键字
select * from emp a where exists(select * from emp b where a.age>60);
select * from emp a where exists(select * from dept where a.dept_id=dept.deptno);

-- 自关联查询
-- 创建表,并建立自关联约束
create table t_sanguo(
    eid int primary key ,
    ename varchar(20),
    manager_id int,
 foreign key (manager_id) references t_sanguo (eid)  -- 添加自关联约束
);
-- 添加数据 
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);

select a.ename '人物',b.ename '上级' from t_sanguo a,t_sanguo b where a.manager_id=b.eid;

MySQL查询题目