实训总结-mysql-day03
数据库复杂查询
非等值连接查询
非等值连接查询，是在查询时不需要指定查询的连接条件，两个表进行笛卡尔运算(两个表的行之间组合排列)，然后通过条件筛选符合规则的数据
# 查询员工的工资以及对应的工资级别 betweeen and 从小到大
select e.last_name,e.salary,jd.grade_level from employees as e,job_grades as jd where e.salary between jd.lowest_sal and jd.highest_sal;
# 查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别 _匹配任意一个字符 %匹配任意多个字符 模糊查询
select e.last_name,e.salary,jd.grade_level from employees as e,job_grades as jd where e.salary between jd.lowest_sal and jd.highest_sal and e.last_name like "__a_e%";连接查询
连接查询(sql 99 提供的标椎)
内连接(等值连接):内连接不受连接顺序影响结果 join on
表一 [inner] join 表二 on 连接条件 (表一 join 表二 on)
外连接查询
左外连接(left [outer] join on)
主表与从表：外连接的前置表为主表，后置表为从表
左外连接时，查询出主表全部字段信息，与主表关联的从表信息，存在则被查询出来，不存在的话用 null 值替代
表一 left [outer] join 表二 on 连接条件
右外连接(right [outer] join on)
右外连接与左表连接类似。唯一相反的是，以右表作为主表，右表查询出来全部信息，左表不存在的用null值替代。
所有右外连接查询均可替换左外连接查询，改变的是表出现的先后位置。
表一 right [outer] join 表二 on 连接条件
全连接(full [outer] join on)
全连接是不被MySQL所支持的。支持全连接查询的有oracle，SqlServer。
全连接是指左右表进行笛卡尔运算，没有主从表之分，连接结果，不存在的列值均用null值去替代。
/**
内连接 表1 [inner] join 表2 on 连接条件(把两张表联系起来，通过连接条件) join on 表一 [inner] join on 表二 where 条件 :和等值连接效果相似，主要是把连接条件提取出来，不过等值连接是笛卡尔运算(inner join on 连接条件),不存在连接顺序的影响
左外链接 left [outer] join on 要求(从表为空要展示,要不要出现连接为空 NULL 的数据,内连接不会出现不出现公共部分的字段)
左表为主表，右表为从表，主表在前(查询效果：主表数据全部查询出来，从表数据也都查询出来，不存在值的字段用null表示)
右外连接 rigth [outer] join on
右边连接的表为主表，左边的表为主表，主表在后(查询效果:右表，主表的数据全部查询出来，左表从表数据也都查询出来，不存在的字段用null表示)
全外连接 full [outer] join on MySQL不支持，oracle和sqlServer支持(两边表中数据都查询出来，不存在的字段用null表示)
*/
# 内连接 (不存在连接顺序的影响)
# 查询员工名、部门名
select e.last_name,d.department_name from employees as e INNER JOIN departments as d on e.department_id=d.department_id;
select e.last_name,d.department_name from employees as e JOIN departments as d on e.department_id = d.department_id;
-- 存在可以省略的inner inner join 表 on 条件
SELECT e.last_name,d.department_name from employees as e join departments as d on e.department_id=d.department_id;
# 查询有奖金的员工名、部门名
select e.last_name,d.department_name from employees as e INNER JOIN departments as d on e.department_id=d.department_id where e.commission_pct is not NULL;
select e.last_name,d.department_name from employees as e JOIN departments as d on e.department_id=d.department_id where e.commission_pct is NOT NULL;
SELECT e.last_name,d.department_name,e.commission_pct from employees as e JOIN departments as d on e.department_id = d.department_id where e.commission_pct is not NULL;
# 查询城市名、员工名和部门名(多连接 表1 join 表二 on 条件 join 表三 on 条件) 多表的内连接
select l.city,e.last_name,d.department_name from employees as e inner join departments as d on e.department_id = d.department_id join locations as l on d.location_id=l.location_id;
select l.city,e.last_name,d.department_name from employees as e inner JOIN departments as d on e.department_id = d.department_id inner join locations as l on d.location_id = l.location_id;
# 左外连接 left [outer] join on (存在主从表受主表从表顺序的影响) 需要想出怎样的连接，在根据连接结果编写过滤条件，主表数据出现但不一定为一，笛卡尔运算
# 查询没有男朋友的女神名称
use girls;
select b.`name`,t.boyName from beauty as b left join boys as t on b.boyfriend_id= t.id where t.boyName is NULL;
# 查询哪个城市没有部门(location字段必须全部为主包含)
use myemployees;
select l.city,d.department_name from locations as l left join departments as d on l.location_id=d.location_id where d.department_id is NULL;
select l.city from locations as l left join departments as d on l.location_id=d.location_id where d.department_id is NULL;
# 查询哪个工种没有员工
SELECT j.job_id,count(*) from jobs as j left JOIN employees as e on j.job_id=e.job_id GROUP BY j.job_id HAVING count(*)=0 or COUNT(*) is NULL;
select * from jobs as j left JOIN employees as e on j.job_id=e.job_id where e.employee_id is NULL;
# 查询员工名和上级领导的名字 自连接 本表连本表 内连接
SELECT e.last_name 员工,m.last_name as 领导 from employees as e JOIN employees as m on e.manager_id=m.employee_id;
SELECT e.last_name 员工,m.last_name as 领导 from employees as e ,employees as m where e.manager_id=m.employee_id;
# 查询编号>3的女神的男朋友信息，如果有则列出详细，如果没有，用null填充
use girls;
select * from beauty as b left join boys as m on b.boyfriend_id= m.id where b.id > 3;
# 查询哪个城市没有部门 location 表全部 左外连接 从表空值为null distinct 去除列重复数据
select l.city from locations as l left OUTER JOIN departments as d on l.location_id = d.location_id where d.department_id is null;
select distinct l.city from locations as l left join departments as d on l.location_id=d.location_id where d.department_id is null;
# 查询部门名为SAL或IT的员工信息 left join 左外连接 左边主表。查询出主表全部，从表没有的用null表示
select d.department_name,e.last_name from departments as d LEFT JOIN employees as e on d.department_id=e.department_id where d.department_name="SAL" OR d.department_name="IT";
select d.department_name,e.last_name from departments as d LEFT JOIN employees as e on d.department_id=e.department_id where d.department_name in ("SAL","IT");
select d.department_name,e.last_name from departments as d LEFT JOIN employees as e on d.department_id=e.department_id where d.department_name in("SAL","IT");
# 选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号， 自连接
SELECT e.last_name as "employees",e.employee_id as "Emp#", m.last_name as "manager" ,m.employee_id as "Mgr#" from employees as e LEFT JOIN employees as m on e.manager_id=m.employee_id where e.last_name="kochhar";子查询
子查询：是在查询中嵌套查询。嵌套的查询语句被称为子查询语句。外部的查询被称为主查询语句，子查询的结果集通常被用作外查询的条件集
子查询：
单行子查询:
单行子查询就是子查询出来的结果集为单行，常用 > ,<,,!=,=,<> 进行条件判断
多行子查询:
多行子查询就是子查询查出的结果集为多行，常用 in ，not in，any ,all进行条件判断
重难点：子查询出来的结果集可以当做虚拟表(临时视图)来进行使用。在使用子查询出来的数据当表使用时需要为表提供别名。
/**
子查询
概念：嵌套在查询中的查询。外部查询称为主查询，内部查询称为子查询
注意事项：
子查询在主查询之前执行，执行结果常作为主查询的条件集
子查询放在小括号内
放在条件右侧
分类
单行子查询：子查询的结果是一行记录 常用 > < = != <> 条件比较
多行子查询：子查询返回的是多行记录 常用in not in any all 条件比较
子查询当做虚拟表使用：虚拟表也必须有表名
*/
# 单行子查询(子查询出的结果为单行)
# 谁的工资比Abel高
select salary from employees where last_name="Abel";
select last_name from employees where salary >(select salary from employees where last_name="Abel")
# 返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资
select last_name,salary from employees where job_id =(SELECT job_id from employees where employee_id=141) and salary>(select salary from employees where employee_id=143);
# 返回公司工资最少的员工的last_name,job_id和salary
select min(salary) from employees;
SELECT last_name,job_id,salary from employees where salary =(select min(salary) from employees);
# 查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id,min(salary) from employees GROUP BY department_id HAVING min(salary)>(select min(salary) from employees where department_id=50);
# 返回location_id是1400或1700的部门中的所有员工姓名 in
select department_id from departments as d where d.location_id in(1400,1700);
select last_name from employees where department_id in (select department_id from departments as d where d.location_id in(1400,1700));
# 返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员工号、姓名、job_id 以及salary any条件
select salary from employees where job_id="IT_PROG";
select last_name,job_id,salary from employees where salary < any (select salary from employees where job_id ="IT_PROG") and job_id <> "IT_PROG";
# 返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary all条件
select salary from employees where job_id = "IT_PROG";
select last_name,job_id,salary from employees where salary < all (select salary from employees where job_id="IT_PROG") and job_id != "IT_PROG";
# 查询和Zlotkey相同部门的员工姓名和工资
select department_id from employees where last_name = "Zlotkey";
select last_name,salary from employees where department_id = (select department_id from employees where last_name = "Zlotkey");
# 查询工资比公司平均工资高的员工的员工号，姓名和工资
select avg(salary) from employees;
SELECT employee_id,last_name,salary from employees where salary > (select avg(salary) from employees);
# 查询工资比各个部门平均工资高的员工的员工号，姓名和工资,把查询结果当做新表 重要
select avg(salary),department_id from employees GROUP BY department_id HAVING department_id is not NULL;
select employee_id,last_name,salary from employees as e,(select avg(salary) as avgSalary,department_id from employees GROUP BY department_id HAVING department_id is not NULL) as d where e.department_id=d.department_id and e.salary>d.avgSalary;
# 查询姓名中包含字母u的员工在相同部门的员工的员工号和姓名 distinct 去重 %匹配任意多个字符_匹配任意一个字符
select distinct department_id from employees where last_name like "%u%";
select employee_id,last_name,salary from employees where department_id in (select distinct department_id from employees where last_name like "%u%");
# 查询在部门的location_id为1700的部门工作的员工的员工号
select department_id from departments where location_id=1700;
select employee_id from employees where department_id in (select department_id from departments where location_id=1700);
# 求部门建立在上海的的员工工号和姓名 嵌套子查询 重要
select location_id from locations where city="Seattle"
select employee_id,last_name from employees where department_id in (select department_id from departments where location_id=(select location_id from locations where city="Seattle"));
# 查询管理者是King的员工姓名和工资
select employee_id from employees where last_name = "King";
select last_name,salary from employees where manager_id in (select employee_id from employees where last_name = "King");
# 查询管理者是King的员工最高工资和员工姓名 并列子查询
select max(salary) from employees where manager_id in (select employee_id from employees where last_name = "King");
select last_name from employees where manager_id in (select employee_id from employees where last_name = "King") and salary=(select max(salary) from employees where manager_id in (select employee_id from employees where last_name = "King"))
# 查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名 cancat 连接查询结果列
select max(salary) from employees;
select concat(first_name,".",last_name) as "姓.名" from employees where salary = (select max(salary) from employees);
# 显示员工表的每个部门的最大工资，工资平均值
select max(salary),AVG(salary) from employees GROUP BY department_id;
# 查询部门的员工个数>5的部门编号和员工个数，并按员工个数降序 desc降序
select count(*) as "人数",department_id from employees GROUP BY department_id HAVING count(*) > 5 order by "人数" desc;
# 查询工资最低的员工信息: last_name, salary
select min(salary) from employees;
select last_name,salary from employees where salary=(select min(salary) from employees);
# 查询平均工资最低的部门信息
select avg(salary) as avg_salary from employees GROUP BY department_id;
select min(avg_salary) as min_avg_salary from (select avg(salary) as avg_salary from employees GROUP BY department_id) as avgsalary;
select d.* from employees as e join departments as d on e.department_id = d.department_id GROUP BY e.department_id HAVING avg(salary)=(select min(avg_salary) as min_avg_salary from (select avg(salary) as avg_salary from employees GROUP BY department_id) as avgsalary);
# 查询平均工资最低的部门信息和该部门的平均工资
select d.*,avg(salary) as "平均工资" from employees as e join departments as d on e.department_id=d.department_id GROUP BY department_id HAVING avg(salary)=(select min(avg_salary) as min_avg_salary from (select avg(salary) as avg_salary from employees GROUP BY department_id) as avgsalary);
# 查询平均工资最高的 job 信息
select avg(salary) from employees GROUP BY job_id;
select max(avg_salary) as max_avg_salary from (select avg(salary) as avg_salary from employees GROUP BY job_id) as avg_job_salary;
select j.*,avg(salary) from employees as e join jobs as j on e.job_id=j.job_id GROUP BY job_id HAVING avg(salary) = (select max(avg_salary) as max_avg_salary from (select avg(salary) as avg_salary from employees GROUP BY job_id) as avg_job_salary);
# 查询平均工资高于公司平均工资的部门有哪些
select avg(salary) from employees;
select department_id,avg(salary) from employees GROUP BY department_id HAVING avg(salary) > (select avg(salary) from employees);
# 查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
select avg(salary) as avg_dep_salary from employees GROUP BY department_id;
select max(avg_dep_salary) from (select avg(salary) as avg_dep_salary from employees GROUP BY department_id) as avg_salary;
-- 求平均最大的管理者id
select e.department_id from employees as e GROUP BY e.department_id HAVING avg(salary) = (select max(avg_dep_salary) from (select avg(salary) as avg_dep_salary from employees GROUP BY department_id) as avg_salary);
select DISTINCT e.manager_id from employees as e where e.department_id = (select e.department_id from employees as e GROUP BY e.department_id HAVING avg(salary) = (select max(avg_dep_salary) from (select avg(salary) as avg_dep_salary from employees GROUP BY department_id) as avg_salary)) and e.manager_id is NOT NULL;
select e.* from employees as e where e.employee_id=(select DISTINCT e.manager_id from employees as e where e.department_id = (select e.department_id from employees as e GROUP BY e.department_id HAVING avg(salary) = (select max(avg_dep_salary) from (select avg(salary) as avg_dep_salary from employees GROUP BY department_id) as avg_salary)) and e.manager_id is NOT NULL);分页查询
分页查询(limit)
通过分页查询来实现数据库表中数据的分页读取，提高检索速度，所谓分页就是对结果集进行了一个切分。
分页的关键字为limit
limit 5 : 从0号位为开始取前5条记录
limit 11,10: 从11号位置开始，往后取10条记录，通常配合角标算法，实现前端分页数据的效果
注意点：分页的索引与数组列表索引下标一样从0开始
/**
limit 分页子句
#LIMIT 条目数
意思：从第一条开始，显示指定条目数的数据,
#LIMIT 起始条目索引，条目数
意思：从指定的起始索引的条目开始，显示指定条目数的数据
起始索引下标为0,
*/
# 查询员工表的中前五行，从0号位置向后取指定条数
select * from employees limit 5;
select * from employees limit 0,5;
# 查询员工表 的第11条到第20条 11条的下标索引是10，因为是从0开始的下标，后面的参数为向后取的条数
select * from employees limit 10,10;
# 查询员工表的第15条到30条 因为是从0开始的下标，后面的参数为向后取的条数
select * from employees limit 14,15;总结
以上就是mysql实训第三天的总结。需要注意的点是all ,any,is null ,is not null,not的使用，外连接的定义和join on ，left join on,right join on的使用，desc代表降序，asc代表升序，数据库 默认为asc升序。