欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mysql-day3-查询总结二

程序员文章站 2024-01-11 15:24:52
...

实训总结-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%";

连接查询

mysql-day3-查询总结二

连接查询(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升序。