MySql----------------1代码展示

最新推荐文章于 2024-03-30 21:55:51 发布
最新推荐文章于 2024-03-30 21:55:51 发布
阅读量113 收藏
点赞数
文章标签： mysql 数据库 java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lhjava1234/article/details/126960812
版权 
#房屋类型表
CREATE TABLE IF NOT EXISTS hos_house(hmid INT PRIMARY KEY auto_increment COMMENT'出租房屋编号',
                                    uid INT NOT NULL COMMENT'客户编号',
																		sid INT NOT NULL COMMENT'街道编号',
																		htid INT NOT NULL COMMENT'房屋类型编号',
																		price DECIMAL(20)  NOT NULL DEFAULT 0 COMMENT '每月租金',
																		topic VARCHAR(20) NOT NULL COMMENT'标题',
																		contents VARCHAR(20) NOT NULL COMMENT'描述');																			
																			
#添加表数据
INSERT INTO hos_house(hmid,uid,sid,htid,price,topic,contents)VALUES(null,6,4,4,10000,'北京','北京天安门'),																																		(null,4,2,3,8000,'南京','南京北路'),
																																		(null,2,4,3,4000,'东京','东京西路'),	
																																		(null,3,3,2,3000,'上海','浦东新区'),
																																		(null,1,2,1,2000,'河南','郑州'),
																																		(null,2,1,4,1000,'海南','海南海滩'),
																																		(null,4,1,2,1000,'北京','故宫'),
																																		(null,5,2,3,1009,'北京','长城'),
																																		(null,4,3,1,200,'郑州','金水区'),
																																		(null,6,4,4,100,'北京','体育馆');
#显示所有员工的姓名，部门号和部门名称
SELECT e.last_name,e.department_id,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;


#员工信息表
SELECT * FROM employees;
#工资等级表
SELECT * FROM job_grades;
#查询员工工资等级
SELECT e.first_name,e.last_name,salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#查询员工姓名员工id及管理者的姓名id  自连接
SELECT com.employee_id,com.last_name,man.manager_id,man.last_name
FROM employees com,employees man
WHERE com.manager_id = man.employee_id;

SELECT com.employee_id,com.last_name,man.employee_id,man.last_name
FROM employees com,employees man
WHERE com.manager_id = man.employee_id;



#排序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id ASC;
#降序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id DESC;

#分页
SELECT employee_id,last_name,salary
FROM employees
ORDER BY employee_id
LIMIT 100,3;



SELECT * FROM job_grades;
SELECT * FROM employees;
SELECT * FROM departments;


#查询所有员工last_name,department_name 左外链接
SELECT last_name,department_name
FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;

#查询员工last_name,department_name
SELECT last_name,department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

#内链接
SELECT employee_id,department_name
FROM employees e JOIN departments d 
ON e.department_id = d.department_id;
#左外链接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
#右外链接
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id;
#  
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE d.department_id IS NULL;
#
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE e.department_id IS NULL;
#满外连接
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d 
ON e.department_id = d.department_id;
WHERE e.department_id IS NULL;


#查询工资比公司平均工资高员工及工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > (SELECT AVG(salary)FROM employees);

#查询工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name,job_id,salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary >ALL (
                SELECT salary
								FROM employees
								WHERE job_id = 'SA_MAN'
								);



#1，查询员工工资大于本部门平均工资的员工的lastname,salary,department_id 相关子查询

SELECT last_name,salary,department_id
FROM employees e1 
WHERE salary > (
                 SELECT AVG(salary)
								 FROM employees e2 
								 WHERE e2.department_id = e1.department_id);


#2，在from中声明子查询
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id

SELECT e1.last_name,e1.salary,e1.department_id
FROM employees e1,(SELECT department_id,AVG(salary) asal
										FROM employees
										GROUP BY department_id) tb_e2												
WHERE e1.department_id = tb_e2.department_id	
AND e1.salary > tb_e2.asal;										


#查询员工的id,salary,按照department_name排序 在order by 中声明子查询
SELECT employee_id,salary
FROM employees e 
ORDER BY(
            SELECT department_name
						FROM departments d 
						WHERE e.department_id = d.department_id)ASC;


SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2<=(SELECT COUNT(1)
          FROM job_history j
					WHERE e.employee_id = j.employee_id);

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name,e.department_id,d.department_name
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;


SELECT job_id,location_id
FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE e.department_id = 90;


# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name,department_name,d.location_id,city 
FROM employees e 
LEFT JOIN departments d 
ON e.department_id = d.department_id
LEFT JOIN locations l 
ON d.location_id = l.location_id
WHERE commission_pct IS NOT NULL;


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name,job_id,e.department_id,department_name,city
FROM employees e,departments d ,locations l
WHERE e.department_id = d.department_id
AND  d.location_id = l.location_id
AND l.city = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive'




# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100


SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager,
mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;


# 7.查询哪些部门没有员工
SELECT d.department_id
FROM departments d LEFT JOIN employees e 
ON e.department_id = d.department_id
WHERE e.department_id IS NULL

# 8. 查询哪个城市没有部门
SELECT l.location_id,l.city
FROM locations l LEFT JOIN departments d
ON l.`location_id` = d.`location_id`
WHERE d.`location_id` IS NULL

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id = d.`department_id`
AND d.`department_name` IN ('Sales','IT');





#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = 'Zlotkey')


#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id,last_name,salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary)
FROM employees)


#3.选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary >ALL (SELECT salary
FROM employees
WHERE job_id = 'SA_MAN')

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id =ANY (SELECT DISTINCT department_id    #DISTINCT 去重语句
FROM employees
WHERE last_name LIKE '%u%');


#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE location_id = 1700)


#6.查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN (SELECT employee_id
FROM employees
WHERE last_name = 'king'
);

#7.查询工资最低的员工信息: last_name, salary
SELECT last_name,salary
FROM employees
WHERE salary = (SELECT MIN(salary)
FROM employees)


#8.查询平均工资最低的部门信息

SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) = (SELECT MIN(avg_sa)
											FROM (SELECT AVG(salary) avg_sa 
																FROM employees
																GROUP BY department_id) avg_ta )
)




SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) <=ALL (SELECT AVG(salary) avg_sa 
																FROM employees
																GROUP BY department_id)
)




SELECT *
FROM departments
WHERE department_id = (SELECT department_id
FROM employees 
GROUP BY department_id
HAVING AVG(salary) =(
												SELECT AVG(salary) avg_sa 
												FROM employees
												GROUP BY department_id
												ORDER BY avg_sa
												LIMIT 0,1		)

);


SELECT d1.*
FROM departments d1 ,(
											SELECT department_id d,AVG(salary) a
											FROM employees
											GROUP BY department_id
											ORDER BY a
											LIMIT 0,1) tab_a_b
WHERE d1.department_id = tab_a_b.d;





#9.查询平均工资最低的部门信息和该部门的平均工资（相关子查询）


SELECT d1.*,(SELECT AVG(salary)FROM employees WHERE  department_id = d1.department_id )svg_sal
FROM departments d1 ,(
											SELECT department_id d,AVG(salary) a
											FROM employees
											GROUP BY department_id
											ORDER BY a
											LIMIT 0,1) tab_a_b
WHERE d1.department_id = tab_a_b.d;


#10.查询平均工资最高的 job 信息

SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (
SELECT MAX(avg_sal)
FROM (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY job_id) t_job_avg_sal));


SELECT j.* 
FROM jobs j,(
SELECT job_id,AVG(salary) avg_sal
FROM employees
GROUP BY job_id
ORDER BY avg_sal DESC
LIMIT 0,1 )t_job_avg_sal
WHERE j.job_id = t_job_avg_sal.job_id

#11.查询平均工资高于公司平均工资的部门有哪些?

SELECT department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING AVG(salary)>(
										SELECT AVG(salary)
										FROM employees)


#12.查询出公司中所有 manager 的详细信息
#方式一 自连接
#方式二 IN
#方式三 EXISTS
SELECT employee_id,last_name,job_id,department_id
FROM employees e1 
WHERE EXISTS(
               SELECT*
							 FROM employees e2 
							 WHERE e1.employee_id = e2.employee_id);



#13.各个部门中 最高工资中最低的那个部门的 最低工资是多少?
SELECT MIN(salary)
FROM employees
WHERE department_id = (SELECT department_id
												FROM employees
												GROUP BY department_id
												HAVING MAX(salary) = (
																						SELECT MIN(max_sal)
																						FROM(
																						SELECT MAX(salary) max_sal 
																						FROM employees
																						GROUP BY department_id) t_dept_max_sal))


																						
SELECT MIN(salary)
FROM employees e , (
SELECT department_id,MAX(salary) max_sal 
FROM employees
GROUP BY department_id
ORDER BY max_sal ASC
LIMIT 0,1
)t_dept_max_sal 
WHERE e.department_id = t_dept_max_sal.department_id;



#14.查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (SELECT DISTINCT manager_id
FROM employees
WHERE department_id = (SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (SELECT MAX(avg_sal)
FROM (
				SELECT AVG(salary) avg_sal 
				FROM employees 
				GROUP BY department_id) t_dept
)
));

SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id IN (
SELECT DISTINCT manager_id
FROM employees e,(
SELECT department_id,AVG(salary) avg_sal 
FROM employees
GROUP BY department_id
ORDER BY avg_sal  DESC
LIMIT 0,1) t_dept_avg_sal
WHERE e.department_id = t_dept_avg_sal.department_id);

#15. 查询部门的部门号，其中不包括job_id是"ST_CLERK"的部门号
SELECT department_id
FROM departments
WHERE department_id NOT IN(
SELECT DISTINCT department_id
FROM employees
WHERE job_id = 'ST_CLERK');


SELECT department_id
FROM departments d 
WHERE NOT EXISTS (
							SELECT *
							FROM employees e 
							WHERE d.department_id = e.department_id
							AND e.job_id = 'ST_CLERK')

 

 

lhjava1234
关注
数据库-子查询分页查询-例题
sunqianLOL的博客
09-06 247
#子查询 #一、含义： 嵌套在其他语句内部的 SELECT 语句称为查询或内查询， 外面的语句可以是 INSERT、DELETE、SELECT等，一般 SELECT 语句作为外部语句较多 外面如果为 SELECT 语句，则此语句称为外查询或主查询 #二、分类 #1.按出现的位置 SELECT 后面： 仅仅支持标量子查询 FROM 后面 表子查询 WHERE 或 HAVING 后面 标量子查询 行子查询 列子查询 EXISTS 后面 标量子查询 行子查询 列子查询 表子查询 #2.按结果集的行列 标量子查询(
P48 第09章_子查询练习题
weixin_54671087的博客
11-28 2001
第09章_子查询练习题 #1.查询和Zlotkey相同部门的员工姓名和工资 SELECT last_name,salary FROM employees WHERE department_id IN( SELECT department_id FROM employees WHERE last_name='Zlotkey' ) #2.查询工资比公司平均工资高的员工的员工号，姓名和工资。 SELECT employee_id,last_name,salary FROM employee
MySQL查询案例题_MYSQL子查询例题以及答案
weixin_30118071的博客
01-19 188
More Subqueries QuizzesAbove is the ERD for the database again - it might come in handy as you tackle the quizzes below. You should write your solution as a subquery or subqueries, not by finding one ...
Mysql查询 子查询练习题
just_learing的博客
04-04 2272
Mysql查询 子查询练习题
MySQL子查询篇（精选20道子查询练习题）
全网粉丝10W+、全栈领域优质创作者、掘金、阿里云等社区博客专家、专注于全栈领域和毕业项目实战
07-24 2752
同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做不相关子查询。from型的子查询子查询是作为from的一部分，子查询要用()引起来，并且要给这个子查询取别名，把它当成一张“临时的虚拟的表”来使用。先通过子查询获得工资最少的工资数，然后把这个当成筛选条件获取是谁的工资这么低。...
gt-jdbc-mysql-2.6.1.jar.zip_css_gt-jdbc-mysql
09-20
标题中的"gt-jdbc-mysql-2.6.1.jar.zip...总的来说，`gt-jdbc-mysql-2.6.1.jar`是一个用于连接GeoServer到MySQL数据库的JDBC驱动，通过这个驱动，GeoServer可以处理和展示存储在MySQL中的地理空间数据，服务于GIS应用。
mysql-connector-java-5.1.47.zip
04-12
例如，以下是一个简单的示例代码，展示了如何使用MySQL Connector/J连接数据库： ```java import java.sql.*; public class MySQLConnectionExample { public static void main(String[] args) { String url = ...
MySQL-python-1.2.3.win32-py2.7 MySQL-python-1.2.3.win-amd64-py2.7
03-08
上述代码展示了如何连接到MySQL服务器，执行SQL查询并打印结果。 5. **关键功能**： - 数据库连接：创建与MySQL服务器的连接。 - 游标对象：用于执行SQL语句并获取结果。 - SQL操作：支持所有基本的SQL命令，如...
mysql-connector-java-5.1.46.rar
05-28
以下是一个基本的Java代码示例，展示如何使用MySQL Connector/J建立数据库连接： ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class ...
【MySQL】详解20道例题带你学习子查询(1)，实战篇
最新发布
weixin_58134620的博客
03-30 962
解题思路：分别查询141号员工的manager_id和department_id，并且最终要出去141号员工本身。
子查询练习
uzi2200的博客
08-11 335
子查询练习 45.列出比王海涛薪资高的员工，显示姓名薪资 select name,sal from emp where sal>(select sal from emp where name='王海涛'); 46.列出与刘沛霞从事相同职业的员工，显示姓名、职位 select name,job from emp where job=(select job from emp where name='刘沛霞'); 列出薪资比大数据部门（已知部门编号为30）所有员工薪资都高的员工信息显示员工姓名、薪资和部
子查询（经典题型）
The_Best_Hacker的博客
08-23 847
【一】相关介绍 学生表 student（sid,sname,birthday,sex） 老师表 teacher(tid,tname) 课程表 course（cid,cname,tid） 成绩表 sc(sid,cid,score) 【二】题目 1、查询2号课程成绩比1号课程成绩低的学生的学号、姓名 方式一： select a.sname,b.sid from stu...
子查询及子查询练习题
weixin_47267628的博客
11-20 1078
子查询 概念：查询中嵌套查询，称嵌套查询为子查询 1.查询工资最高的员工信息 2.查询员工信息，并且工资等于9000的员工 3.使用子查询查询工资最高的员工基本信息 -- 子查询区 -- 查询员工最高的工资 SELECT MAX(salary) FROM emp; -- 查询员工最高工资的员工信息 SELECT * FROM emp WHERE salary = 9000; -- 由于上述查询员工最高工资的基本，太繁琐，所以使用子查询 SELECT * FROM emp WHERE salary=(S
练习题2——子查询经典例题
LuM523的博客
12-17 3034
上网课写的作业，半个小时的课，硬生生写了一个半多小时。记录一下，方便以后容易想起来 #子查询经典案例 1. 查询工资最低的员工信息: last_name, salary 2. 查询平均工资最低的部门信息 3. 查询平均工资最低的部门信息和该部门的平均工资 4. 查询平均工资最高的 job 信息 5. 查询平均工资高于公司平均工资的部门有哪些? 6. 查询出公司中所有 manager 的详细...
MySQL之子查询练习题
程序柳的博客
11-03 1046
子查询的结果是一个值的时候 子查询结果只要是单个值，肯定在WHERE后面作为条件 SELECT 查询字段 FROM 表 WHERE 字段[= > < <>]（子查询）; 子查询结果是单列多行的时候 子查询结果只要是单列，肯定在WHERE后面作为条件 子查询结果是单列多行，结果集类似于一个数组，父查询使用IN运算符 SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）; 子查询的结果是多行多列 子查询结果只要是多行多列，肯定在FROM后面作为表 SELECT ...
子查询练习（数据库）
vgfvgf的博客
03-26 376
子查询练习
【MySQL】详解20道例题带你学习子查询，偷偷做卷王
知识记录
12-23 2682
这几天终于把康师傅的mysql学完了，今天回顾了一下子查询，发现做题还是很不熟练，索性把这些例题发到自己的博客上，记录一下自己的学习生活，有什么错误的地方还请指正！！！✔
MySql子查询练习题带答案
qq_45667680的博客
07-23 2958
– 子查询 CREATE TABLE emp(-- emp表是employee雇员信息表 empno INT,-- empno就是雇员的员工编号，employee number ename VARCHAR(50),-- ename员工姓名，emlployee name job VARCHAR(50),-- job员工的工作是什么比如推销员，经理等 mgr INT,-- mgr.上级编号 hiredate DATE,-- hiredate受雇日期 sal DECIMAL(7,2),-- sal薪金
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值