Mysq基本语法练习以及连表查询语法

最新推荐文章于 2022-03-06 00:45:59 发布

头顶一点云

最新推荐文章于 2022-03-06 00:45:59 发布

阅读量188

点赞数

分类专栏： mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/HBliucheng/article/details/114140148

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql练习题

基础部分
进阶部分
sql练习

基础部分

建表语句

CREATE TABLE `dept` (
  `deptno` int NOT NULL COMMENT '部门编号',
  `dname` varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门名称',
  `loc` varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '部门所在地',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表';

INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');


CREATE TABLE `emp` (
  `empno` int NOT NULL COMMENT '员工编号',
  `ename` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT ' 员工姓名',
  `job` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '工作岗位',
  `mgr` int DEFAULT NULL COMMENT '直属领导',
  `hiredate` date DEFAULT NULL COMMENT '入职时间',
  `sal` double DEFAULT NULL COMMENT '工资',
  `comm` double DEFAULT NULL COMMENT '奖金',
  `deptno` int DEFAULT NULL COMMENT '所属部门',
  PRIMARY KEY (`empno`),
  KEY `EMP` (`deptno`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表';

INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-2-20', 1600, 300, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-2-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-4-2', 2975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-9-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-5-1', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-6-9', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-7-3', 3000, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 6000, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-9-8', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-7-13', 1100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-3', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-3', 6000, NULL, 20);
INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1981-1-23', 4000, NULL, 10);




CREATE TABLE `salgrade` (
  `grade` int DEFAULT NULL COMMENT '薪资等级',
  `losal` double DEFAULT NULL COMMENT '最低工资',
  `hisal` double DEFAULT NULL COMMENT '最高工资'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='工资等级表';
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);


#======================基础查询============================

# 查询表里所有内容
select *from emp
# 查询名字和月薪
select ename,sal from emp
# 计算出名字和年薪
select ename,sal*12 from emp
#计算员工的日薪(按30天计)
select *, sal/30 as daySal from emp
# 计算总收入
select ename,sal*12+(CASE WHEN comm is null then 0 else comm end) income  from emp 


#====================去重DISTINCT===================================
# 去重
select DISTINCT deptno from emp
select DISTINCT deptno,job from emp


#====================where条件查询======================================

#查询部门编号为30的部门的员工详细信息
select *from emp where deptno=30
#查询从事clerk工作的员工的编号、姓名以及其部门号
select empno,ename,deptno  from emp where job="clerk"

#查询奖金多于基本工资的员工的信息
select * from emp where comm>sal
#查询奖金多于基本工资60%的员工的信息
select * from emp where comm>sal*0.6
#查询部门编号为10的部门经理和部门编号为20的部门中工作为CLERK的职员信息
select *from emp where (deptno=10 and job="MANAGER") or (deptno=20 and job="CLERK")
#查询部门编号为10的部门经理或部门编号为20的部门工作为CLERK的职员信息或者既不是经理也不是CLERK但是工资高于2000的员工信息
 select * from emp where (deptno=10 and job="MANAGER") or (deptno=20 and job="CLERK") or (job NOT IN ("MANAGER","CLERK")and sal>2000)
#查询获得奖金的员工的信息
 select * from emp where comm>0
#查询奖金少于100或者没有获得奖金的员工的信息
select * from emp where comm<100 or comm is null or comm=0


#======================模糊查询=====================================

# 查询姓名中不包含R字符的员工信息。
select * from emp where ename not LIKE "%K%"
#查询姓名以A、B、S开头的员工的信息
select *from emp  where ename like "A%" or ename like "B%" or ename like "S%"
# 查询第二个字母为K的员工信息
select * from emp where ename not LIKE "_K%"


#=======================排序===============================

#查询员工的详细信息并按姓名排序
select *from emp ORDER BY  ename DESC
#查询员工的信息并按工作降序工资升序排列
select * from emp order by job desc,sal ASC


#========================函数==================================

# 常用函数 trim() length() substring()
# 查询找到姓名长度为6个字符的员工的信息
select * from emp where LENGTH(ename) =6
# 组函数 sum() min()avg()coiunt(*)
# 求出最高薪资
 select max(sal),deptno from emp
# 求平均薪资
select avg(sal) from emp
# 求员工人数
select count(1) from emp

#=====================group by 使用================================

#求每个部门最高薪资
 select max(sal),deptno from emp GROUP BY deptno
# 求每个部门薪资最高的那个人
select e. ename ,e.sal ,e.deptno from emp e  JOIN (select max(sal) sal,deptno from emp GROUP BY deptno) d on d.deptno=e.deptno and d.sal=e.sal


#=====================having 对分组进行限制=======================================

# 按照部门编号分组，求分组后的每一个部门的平均薪水
select avg(sal),deptno avg_sal from emp GROUP BY deptno
# 求平均薪水大于2000的部门
select avg(sal) avg_sal,deptno  from emp GROUP BY deptno HAVING avg_sal>2000

进阶部分

#==========================子查询===============================
# 一个select 语句中嵌套一个另外的select语句(可以看做临时表)

#查询工资多于smith的员工信息。
select * from emp where sal>(select sal from emp where ename="smith")

# 查询比平均工资高的人
select ename ,sal from emp where sal > (select avg(sal) from emp)

#求每个部门高工资最高的那个人并显示他们的姓名、工资、部门编号
select e. ename ,e.sal ,e.deptno from emp e  JOIN (select max(sal) sal,deptno from emp GROUP BY deptno) d on d.deptno=e.deptno and d.sal=e.sal

#======================直连接===============================
# 给一张表取不同别名，当成两张表用

# 查询部门员工的的名字和他所在部门的经理的名字
select e1.ename ,e2.ename mrg_name from emp e1,emp e2 where e1.mgr=e2.empno

#=======================表连接(内连接)================================
# 查询部门员工的的名字和他所在部门的经理的名字

select e1.ename ,e2.ename mrg_name from emp e1 join emp e2 on e1.mgr=e2.empno

# ========================左连接============================= 
# 左表为主，右表为辅，右表与左表未关联的数据置空处理

## 查询员工所属的部门 
select e.ename,d.dname from emp e left join dept d on  d.deptno=e.deptno

#查询拥有员工的部门的部门名和部门号
select DISTINCT d.deptno,d.dname from dept d left JOIN emp e on d.deptno=e.deptno WHERE e.ename is not null


# ========================右连接============================= 
# 右表为主，左表为辅，左表与右表未关联的数据置空处理
# 查询员工所属的部门
select e.ename,d.dname from dept d right join emp e on  d.deptno=e.deptno

sql练习

☺如有错误或者更优的sql请留言指教☺

1，查询部门的平均薪水等级
实现方式很多

# 考虑 部门没人情况 使用内连接
select e.deptno, d.dname,s.grade from salgrade s 
join (select avg(sal) avg_sal,deptno from emp GROUP BY deptno) e 
on   e.avg_sal BETWEEN  s.losal and s.hisal
 join dept  d on e.deptno=d.deptno

在这里插入图片描述

# 考虑部门没人情况 使用左连接
select es.deptno, d.dname,es.grade,es.avg_sal from  dept d left join 
(select e.deptno,s.grade,e.avg_sal from salgrade s 
join (select avg(sal) avg_sal,deptno from emp e GROUP BY deptno) e 
on   e.avg_sal BETWEEN  s.losal and s.hisal) es ON d.deptno=es.deptno

在这里插入图片描述

2, 那些人是领导

SELECT e.ename from emp e join (select DISTINCT(mgr) mgrno from emp) em on e.empno=em.mgrno

在这里插入图片描述
3，平均薪水最高的部门编号与名称

# 步骤
# 1，求每个部门的的平均薪水
select avg(sal) avg_sal,deptno from emp group by deptno
# 2,求出上表步骤1中的最大值
select max(avg_sal) max_avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) d
# 使用子查 查询步骤1中的表，条件查询出最大值，天剑就是步骤二的结果
select avg_d.deptno,avg_d.avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) avg_d 
where avg_d.avg_sal=(select max(avg_sal) max_avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) max_avg_d)
# 使用和部门表连表查询查出最终结论
select d.deptno,d.dname,max_sal_res.avg_sal from dept d join (
select avg_d.deptno,avg_d.avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) avg_d 
where avg_d.avg_sal=(select max(avg_sal) max_avg_sal from (select avg(sal) avg_sal,deptno from emp group by deptno) max_avg_d)
) max_sal_res on d.deptno=max_sal_res.deptno

在这里插入图片描述

4，求平均薪水的登记最低的部门的部门名称
注意事项，有可能两个部门的平均薪水在同一个等级

# 1 按照部门求出平均薪水
select deptno, avg(sal) avg_sal from emp GROUP BY deptno
# 步骤1中的表 与薪水等级表联查，
select avg_d.deptno,avg_d.avg_sal,s.grade from (select e.deptno, avg(e.sal) avg_sal from emp e GROUP BY e.deptno)as avg_d 
LEFT JOIN salgrade s 
on avg_d.avg_sal BETWEEN s.losal AND s.hisal 
#步骤2 中表查询出最低等级再与部门表联查得出最终结论
select dep_min_grade.deptno,dep_min_grade.avg_sal,dep_min_grade.grade,d.dname from (select avg_d.deptno,avg_d.avg_sal,s.grade from (select e.deptno, avg(e.sal) avg_sal from emp e GROUP BY e.deptno)as avg_d 
LEFT JOIN salgrade s 
on avg_d.avg_sal BETWEEN s.losal AND s.hisal) dep_min_grade 
left join dept d on dep_min_grade.deptno=d.deptno
where grade = (select min(grade) from (select avg_d.deptno,avg_d.avg_sal,s.grade from (select e.deptno, avg(e.sal) avg_sal from emp e GROUP BY e.deptno)as avg_d 
LEFT JOIN salgrade s 
on avg_d.avg_sal BETWEEN s.losal AND s.hisal) sal_min_grade)

在这里插入图片描述

5，比普通员工的最高薪水还要高的经理名称


# 查询所有经理编号
select DISTINCT(mgr) emp_mgr from emp where mgr is not null
# 查询所有普通员工最高薪水
select max(sal) from emp where empno not in (select DISTINCT(mgr) emp_mgr from emp where mgr is not null)

# 比普通员工的最高薪水还要高的经理名称
select ename from emp where 
sal>(select max(sal) from emp where empno not in (select DISTINCT(mgr) emp_mgr from emp where mgr is not null)
) and empno in (select DISTINCT(mgr) emp_mgr from emp where mgr is not null)

在这里插入图片描述

头顶一点云

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Mysq基本语法练习以及连表查询语法

#查询部门编号为30的部门的员工详细信息select *from emp where deptno=30#查询从事clerk工作的员工的编号、姓名以及其部门号select empno,ename,deptno from emp where job="clerk"#查询奖金多于基本工资的员工的信息select * from emp where comm>sal#查询奖金多于基本工资60%的员工的信息select * from emp where comm>sal*0.6#查询部门
复制链接

扫一扫