MySQL笔记---DQL语言

最新推荐文章于 2024-05-02 01:04:40 发布
最新推荐文章于 2024-05-02 01:04:40 发布
阅读量237 收藏 3
点赞数
分类专栏： MySQL 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhkey123/article/details/110822430
版权

文章目录

一、基础查询

一、语法

slelect 查询列表
from 表名;

二、特点

  1. 查询列表可以是字段、常量、表达式、函数，也可以是多个
  2. 通过select查询完的结果 ，是一个虚拟的表格，不是真实存在

三、示例

  1. 查询单个字段

     select 字段名 from 表名;

  2. 查询多个字段

     select 字段名,字段名 from 表名;

  3. 查询所有字段

     select * from 表名;

  4. 查询常量

     select 常量值;
 ==注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要==

  5. 查询函数

     select 函数名(实参列表);

  6. 查询表达式

     select 100/1234;

  7. 起别名

     方式一：使用as
 SELECT 100%98 AS 结果;
 SELECT last_name AS 姓,first_name AS 名 FROM employees;

 方式二：使用空格
 SELECT last_name 姓,first_name 名 FROM employees;

  8. 去重

     select distinct 字段名 from 表名;
 
 #案例：查询员工表中涉及到的所有的部门编号
 SELECT DISTINCT department_id FROM employees;

  9. +号的作用
    功能：运算符，做加法运算

     select 100+90; 两个操作数都为数值型，则做加法运算
 select '123'+90;只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算
 select 'john'+90;	如果转换失败，则将字符型数值转换成0
 select null+10; 只要其中一方为null，则结果肯定为null

  10. 【补充】concat函数
    功能：拼接字符

    select concat(字符1，字符2，字符3,...);

#案例：查询员工名和姓连接成一个字段，并显示为 姓名
SELECT
	CONCAT(last_name,first_name) AS 姓名
FROM
	employees;

  11. 【补充】ifnull函数
    功能：判断某字段或表达式是否为null，如果为null 返回指定的值，否则返回原本的值

    select ifnull(commission_pct,0) from employees;

  12. 【补充】isnull函数
    功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0

二、条件查询

一、语法：

select 
	查询列表
from
	表名
where
	筛选条件;

二、筛选的分类

  1. 按条件表达式筛选
    简单条件运算符：> < = != <> >= <=

     #案例1：查询工资>12000的员工信息
 SELECT
 	*
 FROM
 	employees
 WHERE
 	salary > 12000;

 #案例2：查询部门编号不等于90号的员工名和部门编号
 SELECT
 	last _name,departments
 FROM
 	employees
 WHERE
 	department_id <>90;

  2. 按逻辑表达式筛选
    逻辑运算符：
    作用：用于连接条件表达式
    && || !
    and or not

    &&和and：两个条件都为true，结果为true，反之为false
    ||或or： 只要有一个条件为true，结果为true，反之为false
    !或not： 如果连接的条件本身为false，结果为true，反之为false

     #案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
 SELECT
 	last_name,salary,commision_pct
 FROM
 	employees
 WHERE
 	salary >=10000  && salary<=20000;

 #案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
 SELECT
 	*
 FROM
 	employes
 WHERE
 	NOT (department_id >=90 && department_id<=110) || salary>15000;

  3. 模糊查询

    1. like
      特点：一般搭配通配符使用，可以判断字符型或数值型
      通配符：%任意多个字符，_任意单个字符

       #案例1：查询员工名中包含字符a的员工信息
 selsect * 
 from employees 
 where last_name like '%a%';

 #案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资
 select last_name,salary 
 from employees 
 where last_name like '__e_a%';

 #案例3：查询员工名中第二个字符为_的员工名
 select last_name from employees last_name like '_$_%' escape '$';

    2. between and
      ①使用between and 可以提高语句的简洁度
      ②包含临界值
      ③两个临界值不要调换顺序

       #案例1：查询员工编号在100到120之间的员工信息
 select * from employees where employee_id between 100 and 120;
 select * from employees where employee_id>=100 and employee_id<=120;

    3. in
      含义：判断某字段的值是否属于in列表中的某一项
      特点：
      ①使用in提高语句简洁度
      ②in列表的值类型必须一致或兼容
      ③in列表中不支持通配符

       #案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
 select 
 	last_name,job_id 
 from 
 	employees 
 where 
 	job_id in ('IT_PROG','AD_VP','AD_PRES');

    4. is null，is not null
      =或<>不能用于判断null值
      is null或is not null 可以判断null值

       #案例1：查询没有奖金的员工名和奖金率
 selsect
 	last_name,commission_pct
 from
 	employees
 where
 	commission_pct is null;
 	
 #案例1：查询有奖金的员工名和奖金率
 selsect
 	last_name,commission_pct
 from
 	employees
 where
 	commission_pct is not null;

    5. 补充：安全等于 <=>

       #案例1：查询没有奖金的员工名和奖金率
 select
 	last_name,commission_pct
 from
 	employees
 where
 	commission_pct <=> null;
 	
 #案例2：查询工资为12000的员工信息
 select
 	*
 from
 	emplyees
 where
 	salary <=> 12000;

    6.is null pk <=>
    IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
    <=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

三、排序查询

一、语法

select 查询列表
from 表
【where 筛选条件】
order by 排序列表  【asc升序）|desc（降序）】

二、特点

  1. asc代表的是升序，desc代表的是降序
    如果不写，默认是升序

三、示例

  1. 按单个字段排序

     #案例1：查询员工信息，要求工资从高到低排序
 SELECT * FROM employees ORDER BY salary DESC;

  2. 添加筛选条件再排序

     #案例2：查询部门员工编号>=90的员工信息，按入职时间的先后排序
 	SELECT * 
 	FROM employees 
 	WHERE department_id>=90 
 	ORDER BY hiredate ASC;

  3. 按表达式排序

     #案例3：按年薪的高低显式员工的信息和年薪
 SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪
 FROM employees
 ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

  4. 按别名排序

     #案例：查询员工信息 按年薪升序
 SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
 FROM employees
 ORDER BY 年薪 ASC;

  5. 按函数排序

     #案例：查询员工名，并且按名字的长度降序
 SELECT LENGTH(last_name),last_name 
 FROM employees
 ORDER BY LENGTH(last_name) DESC;

  6. 按多个字段排序

     #案例：查询员工信息，要求先按工资降序，再按employee_id升序
 SELECT *
 FROM employees
 ORDER BY salary DESC,employee_id ASC;

四、常见函数

一、概述

  1. 功能：类似于java中的方法
  2. 好处：提高重用性和隐藏实现细节
  3. 调用：select函数名(实参列表);

二、单行函数

  1. 字符函数

    concat:拼接字符串

     SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

    substr:截取子串
    注意：索引从1开始

     #截取从指定索引处后面所有字符
 SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;
 
 #截取从指定索引处指定字符长度的字符
 SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

    upper:变大写
    lower：变小写

     SELECT UPPER('john');
 SELECT LOWER('joHn');
 #示例：将姓变大写，名变小写，然后拼接
 SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;
 
 #案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
 SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))  out_put
 FROM employees;

    replace：替换

     SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

    length：获取字节长度

     SELECT LENGTH('john');
 SELECT LENGTH('张三丰hahaha');

    trim:去前后空格

     SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;

 SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

    lpad：用指定的字符实现左填充指定长度
    如果填充的长度小于字符的长度，则将字符长度变为要填充的长度

     SELECT LPAD('殷素素',2,'*') AS out_put;#殷素

    rpad：用指定的字符实现右填充指定长度

     SELECT RPAD('殷素素',12,'ab') AS out_put;

    instr:返回子串第一次出现的索引，如果找不到返回0

     SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

  2. 数学函数
    ceil:向上取整，返回>=该参数的最小整数

     SELECT CEIL(-1.02); # -1

    floor：向下取整，返回<=该参数的最大整数

     	SELECT FLOOR(-9.99); # -10

    round：四舍五入

     SELECT ROUND(-1.55);# -2
 SELECT ROUND(1.567,2);# 1.57

    mod:取余

     SELECT MOD(10,-3); # 1
 SELECT 10%3; # 1

    truncate:截断

     SELECT TRUNCATE(1.69999,1);

    rand:获取随机数，返回0-1之间的小数

     SELECT RAND();
 #获取0-10之间整数
 SELECT TRUNCATE(RAND()*10,0);

  3. 日期函数
    now：返回当前日期+时间

     SELECT NOW();# 2020-12-20 17:31:18

    year:返回年

     SELECT YEAR(NOW()); # 2020

    month：返回月

     SELECT MONTH(NOW()); # 12

    day:返回日

     SELECT DAY(NOW()); # 20

    curdate:返回当前系统日期，不包含时间

     SELECT CURDATE(); # 2020-12-20

    curtime：返回当前系统时间，不包含日期

     SELECT CURTIME(); # 17:38:16

    str_to_date:将字符转换成日期

     SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put; # 1998-03-02

    date_format:将日期转换成字符

     SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put; # 20年12月20日

    hour:小时
    minute:分钟
    second：秒

    datediff：两个日期相差的天数

     SELECT DATEDIFF(NOW(),'1999-5-13'); # 7892

  4. 其他函数

     SELECT VERSION(); # 8.0.22
 SELECT DATABASE();# myemployees
 SELECT USER() # root@localhost

  5. 流程控制函数

    if函数：if else的效果

     SELECT IF(10<5,'大','小'); # 小

    case函数的使用一： switch case 的效果
    格式：
    case 要判断的字段或表达式
    when 常量1 then 要显示的值1或语句1;
    when 常量2 then 要显示的值2或语句2;
    …
    else 要显示的值n或语句n;
    end

     /*案例：查询员工的工资，要求
 
 部门号=30，显示的工资为1.1倍
 部门号=40，显示的工资为1.2倍
 部门号=50，显示的工资为1.3倍
 其他部门，显示的工资为原工资
 */
 
 SELECT salary 原始工资,department_id,
 CASE department_id
 WHEN 30 THEN salary*1.1
 WHEN 40 THEN salary*1.2
 WHEN 50 THEN salary*1.3
 ELSE salary
 END AS 新工资
 FROM employees;

    case 函数的使用二：类似于 多重if
    格式：
    case
    when 条件1 then 要显示的值1或语句1
    when 条件2 then 要显示的值2或语句2
    。。。
    else 要显示的值n或语句n
    end

     #案例：查询员工的工资的情况
 如果工资>20000,显示A级别
 如果工资>15000,显示B级别
 如果工资>10000，显示C级别
 否则，显示D级别
 
 
 SELECT salary,
 CASE 
 WHEN salary>20000 THEN 'A'
 WHEN salary>15000 THEN 'B'
 WHEN salary>10000 THEN 'C'
 ELSE 'D'
 END AS 工资级别
 FROM employees;

三、分组函数

  1. 功能： 用作统计使用，又称为聚合函数或统计函数或组函数

  2. sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

     SELECT SUM(salary) FROM employees;
 SELECT AVG(salary) FROM employees;
 SELECT MAX(salary) FROM employees;
 SELECT MIN(salary) FROM employees;
 SELECT COUNT(salary) FROM employees;

  3. 特点

    • 以上五个分组函数都忽略null值，除了count(*)
    • sum和avg一般用于处理数值型
      max、min、count可以处理任何数据类型
    • 都可以搭配distinct使用，用于统计去重后的结果
    • count的参数可以是：字段、* 、常量值（一般放1）；
      建议使用count(*)

五、分组查询

  1. 语法：

     SELECT 分组函数，列（要求出现在grout by的后面）
 FROM 表
 【WHERE 筛选条件】
 GROUP BY 分组的列表
 【ORDER BY 子句】

    注：分组查询的列表比较特殊，要求是分组函数和group by后出现的字段

  2. 特点：

    1. 分组查询中的筛选条件分为两类
    数据源位置关键字
    分组前筛选原始表group by子句的前面
    分组后筛选分组后的结果集group by子句的后面

    分组函数做条件一定是放在having子句中
    能用分组前筛选的，优先考虑使用分组前筛选

    1. group by子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），表达式或函数（用的较少）
    2. 也可以添加排序（排序放在整个分组查询的最后）

  3. 举例：

     #引入：查询每个部门的平均工资
 SELECT AVG(salary),department_id
 FROM employees
 GROUP BY department_id;
 
 
 #案例1：查询每个工种的最高工资
 SELECT MAX(salary),job_id
 FROM employees
 GROUP BY job_id;
 
 #案例2：查询每个位置上的部门个数
 SELECT COUNT(*),location_id
 FROM departments
 GROUP BY location_id;

    添加筛选条件

     #案例1：查询邮箱中包含a字符的，每个部门的平均工资
 SELECT AVG(salary),department_id
 FROM employees
 WHERE email LIKE '%a%'
 GROUP BY department_id;
 
 #案例2：查询有奖金的每个领导手下员工的最高工资
 SELECT MAX(salary),manager_id
 FROM employees
 WHERE commission_pct is NOT NULL
 GROUP BY manager_id;

    添加分组后的筛选条件

     #案例1：查询哪个部门的员工个数>2
 #① 查询每个部门员工数
 SELECT COUNT(*),department_id
 FROM employees
 GROUP BY department_id;
 
 #② 根据①的结果进行筛选，查询出员工数>2的部门
 SELECT COUNT(*),department_id
 FROM employees
 GROUP BY department_id
 HAVING COUNT(*)>2;
 
 #案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
 SELECT MAX(salary),job_id
 FROM employees
 WHERE commission_pct IS NOT NULL
 GROUP BY job_id
 HAVING MAX(salary)>12000;
 
 #案例3：查询领导编号>120的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
 SELECT MIN(salary),manager_id
 FROM employees
 WHERE manager_id >120
 GROUP BY manager_id
 HAVING MIN(salary)>5000;

    按表达式或函数分组

     #案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
 SELECT COUNT(*) c,LENGTH(last_name) len_name
 FROM employees
 GROUP BY len_name
 HAVING c>5;

    按多个字段分组

     #案例：查询每个部门每个工种的员工的平均工资
 SELECT AVG(salary),department_id,job_id
 FROM employees
 GROUP BY department_id,job_id;

    添加排序

     #案例：查询每个部门，每个工种的员工的平均工资，并且按平均工资的高低显示
 SELECT AVG(salary),department_id,job_id
 FROM employees
 WHERE department_id IS NOT NULL
 GROUP BY department_id,job_id
 ORDER BY AVG(salary) DESC;

六、连接查询

含义： 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象： 表1有m行，表2有n行，结果=m*n行

  1. 分类：
    在这里插入图片描述

  2. sql92标准： 仅仅支持内连接

    • 多表等值连接的结果为多表的交集部分

    • n表连接，至少需要n-1个连接条件

    • 多表的顺序没有要求

    • 一般需要为表起别名

    • 可以搭配前面介绍的所有子句使用，比如排序、分组、筛选

        #案例1：查询女名对应的男名
  SELECT NAME,boyName
  FROM beauty,boys
  WHERE beauty.boyfriend_id=boys.id;
  
  #案例2：查询员工名对应的部门名
  SELECT last_name,department_name
  FROM employees,departments
  WHERE employees.department_id=departments.department_id;
  
  
  #2、为表起别名
  /*
  ①提高与举荐解读
  ②区分多个重名的字段
  
  注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定
  */
  #查询员工名、工种号、工种名
  SELECT last_name,e.job_id,job_title
  FROM employees e,jobs j
  WHERE e.job_id=j.job_id;
  
  #3、两个表的顺序可以调换
  
  #查询员工名、工种号、工种名
  SELECT last_name,e.job_id,job_title
  FROM jobs j,employees e
  WHERE e.job_id=j.job_id;
  
  #4、可以加筛选
  
  #案例1：查询有奖金的员工名、部门名
  SELECT last_name,department_name
  FROM employees e,departments d
  WHERE e.department_id=d.department_id 
  AND commission_pct IS NOT NULL
  
  #案例2：查询城市名中第二个字符为o的部门名和城市名
  SELECT department_name,city
  FROM departments d,locations l
  WHERE d.location_id=l.location_id
  AND city LIKE '_o%';
  
  #5、可以加分组
  
  #案例1：查询每个城市的部门个数
  SELECT COUNT(*) 个数,city
  FROM departments d,locations l
  WHERE d.location_id=l.location_id
  GROUP BY city;
  
  #案例2：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
  SELECT department_name,d.manager_id,MIN(salary)
  FROM employees e,departments d
  WHERE e.department_id=d.department_id
  AND commission_pct IS NOT NULL
  GROUP BY department_name,d.manager_id;
  
  
  #6、可以加排序
  
  #案例：查询每个工种的工种名和员工个数，并且按员工个数降序
  SELECT job_title,COUNT(*)
  FROM jobs j,employees e
  WHERE j.job_id=e.job_id
  GROUP BY job_title
  ORDER BY COUNT(*) DESC;
  
  
  #7、可以实现三表查询
  
  #案例：查询员工名、部门名和所在的城市
  
  SELECT last_name,department_name,city
  FROM employees e,departments d,locations l
  WHERE e.department_id=d.department_id 
  AND d.location_id=l.location_id;
  
  
  
  #2、非等值连接
  
  #案例1：查询员工的工资和工资级别
  
  SELECT salary,grade_level
  FROM employees e,job_grades g
  WHERE salary BETWEEN lowest_sal AND highest_sal;
  
  
  #3、自连接
  
  #案例：查询员工名和上级的名称
  SELECT e.last_name 员工名,m.last_name 领导名
  FROM employees e,employees m
  WHERE e.manager_id=m.employee_id;

  3. sql99语法： 通过join关键字实现连接

    1. 语法：

       select 字段，...
 from 表1
 【inner|left outer|right outer|cross】join 表2 on  连接条件
 【inner|left outer|right outer|cross】join 表3 on  连接条件
 【where 筛选条件】
 【group by 分组字段】
 【having 分组后的筛选条件】
 【order by 排序的字段或表达式】

    2. 内连接

       SELECT 查询列表
 FROM 表1 别名
 INNER JOIN 表2 别名
 ON 连接条件;

      分类：
      等值
      非等值
      自连接
      特点：
      ①添加排序、分组、筛选
      ②inner可以省略
      ③筛选条件放在where后面，连接条件放在on后面，提高可读性，便于阅读
      ④inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集

       #1、等值连接
 
 #案例1：查询员工名，部门名
 SELECT last_name,department_name
 FROM employees e
 INNER JOIN departments d
 ON e.department_id=d.department_id;
 
 #案例2：查询名字中包含e的员工名和工种名（添加筛选）
 SELECT last_name,job_title
 FROM employees e
 INNER JOIN jobs j
 ON e.job_id=j.job_id
 WHERE last_name LIKE '%e%';
 
 #案例3：查询部门个数>3的城市名和部门个数（添加分组+筛选）
 SELECT city,COUNT(*) 部门个数
 FROM locations l
 INNER JOIN departments d
 ON l.location_id=d.location_id
 GROUP BY city
 HAVING COUNT(*)>3;
 
 #案例4：查询哪个部门的员工个数>3的部门和员工个数，并按个数降序（添加排序）
 SELECT department_name,COUNT(*)
 FROM departments d
 INNER JOIN employees e
 ON e.department_id=d.department_id
 GROUP BY department_name
 HAVING COUNT(*)>3
 ORDER BY COUNT(*) DESC;
 
 #案例5：查询员工名、部门名、工种名，并按部门名降序（添加三表连接）
 SELECT last_name,department_name,job_title
 FROM employees e
 INNER JOIN departments d ON e.department_id=d.department_id
 INNER JOIN jobs j ON e.job_id=j.job_id
 ORDER BY department_name DESC;
 
 
 #二）非等值连接
 
 #查询员工的工资级别
 SELECT salary,grade_level 工资级别
 FROM employees e
 JOIN job_grades g
 ON salary BETWEEN g.lowest_sal AND g.highest_sal;
 
 #查询工资级别的个数>20的个数，并且按工资级别降序
 SELECT grade_level,COUNT(*)
 FROM employees e
 JOIN job_grades g
 ON e.salary BETWEEN g.lowest_sal AND g.highest_sal
 GROUP BY grade_level
 HAVING COUNT(*)>20
 ORDER BY grade_level DESC;
 
 
 #三）自连接
 
 #查询员工的名字、上级的名字
 SELECT e1.last_name,e2.last_name
 FROM employees e1
 JOIN employees e2
 ON e1.manager_id=e2.employee_id;
 
 #查询姓名中包含字符k的员工的名字、上级的名字
 SELECT e1.last_name,e2.last_name
 FROM employees e1
 JOIN employees e2
 ON e1.manager_id=e2.employee_id
 WHERE e1.last_name LIKE '%k%';

    3. 外连接
      应用场景： 用于查询一个表中有，另一个表中没有的记录
      特点：

      1. 外连接的查询结果为主表中的所有记录
        如果从表中有和它匹配的，则显示匹配的值
        如果从表中没有和他匹配的，则显示null
        外连接查询结果=内连接查询结果+主表中有二从表中没有的记录

      2. 左外连接，left join左边的是主表
        右外连接，right join右边的是主表

      3. 左外和右外交换两个表的顺序，可以实现同样的效果

      4. 全外连接=内连接的结果+表1中有但表2中没有的+表2中有但表1没有的

         #引入：查询男朋友不在男生表的女生名
 
 #左外连接
 SELECT be.name
 FROM beauty be
 LEFT OUTER JOIN boys bo
 ON be.boyfriend_id=bo.id
 WHERE bo.id IS NULL;
 
 #右外连接
 SELECT be.name
 FROM boys bo
 RIGHT OUTER JOIN beauty be
 ON be.boyfriend_id=bo.id
 WHERE bo.id IS NULL;
 
 
 #案例1：查询哪个部门没有员工
 
 #左外
 SELECT department_name
 FROM departments d
 LEFT OUTER JOIN employees e
 ON  e.department_id=d.department_id
 WHERE e.employee_id IS NULL;
 
 #右外
 SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON  e.department_id=d.department_id
 WHERE e.employee_id IS NULL;
 
 #全外
 USE girls
 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.boyfriend_id=bo.id;
 
 #交叉连接
 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;

七、子查询

含义： 出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
在这里插入图片描述

  1. where或having后面
    支持：标量子查询、列子查询、行子查询
    特点：
    ①子查询放在小括号内
    ②子查询一般放在条件的右侧
    ③标量子查询，一般搭配着单行操作符使用：> < >= <= = <>
    列子查询，一般搭配着多行操作符使用：in、any/some、all
    ④子查询优先于主查询执行，主查询用到了子查询的结果

     #1.标量子查询
 
 #案例1：谁的工资比Abel高？
 
 #①查询Abel的工资
 SELECT salary
 FROM employees
 WHERE last_name='Abel';
 
 #②查询员工的信息，满足salary>①
 SELECT *
 FROM employees
 WHERE salary>(
 	SELECT salary
 	FROM employees
 	WHERE last_name='Abel'
 );
 
 #案例2：返回job_id与141号员工相同，salary比143号员工多的员工姓名，job_id和工资
 SELECT last_name,job_id,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
 );
 
 #案例3：返回公司工资最少的员工的last_name,job_id和salary
 
 SELECT last_name,job_id,salary
 FROM employees
 WHERE salary=(
 	SELECT MIN(salary)
 	FROM employees
 );
 
 #案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
 
 SELECT MIN(salary),department_id
 FROM employees
 GROUP BY department_id
 HAVING MIN(salary)>(
 	SELECT MIN(salary)
 	FROM employees
 	WHERE department_id=50
 );
 
 
 #2.列子查询（多行子查询）
 #案例1：返回location_id是1400或1700的部门中的所有员工姓名
 SELECT department_id
 FROM departments
 WHERE location_id IN (1400,1700);
 
 SELECT last_name
 FROM employees
 WHERE department_id IN (
 		SELECT department_id
 		FROM departments
 		WHERE location_id IN (1400,1700)
 );
 
 #案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
 SELECT salary
 FROM employees
 WHERE job_id='IT_PROG';
 
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < ANY(
 	SELECT salary
 	FROM employees
 	WHERE job_id='IT_PROG'
 ) AND job_id<>'IT_PROG';
 
 #或
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary <(
 	SELECT MAX(salary)
 	FROM employees
 	WHERE job_id='IT_PROG'
 ) AND job_id<>'IT_PROG';
 
 #案例3：返回其它工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id 以及salary
 
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary < ALL(
 	SELECT salary
 	FROM employees
 	WHERE job_id='IT_PROG'
 ) AND job_id<>'IT_PROG';
 
 #或
 SELECT employee_id,last_name,job_id,salary
 FROM employees
 WHERE salary <(
 	SELECT MIN(salary)
 	FROM employees
 	WHERE job_id='IT_PROG'
 ) AND job_id<>'IT_PROG';
 
 
 #3、行子查询（结果集一行多列或多行多列）
 #案例1：查询员工编号最小且工资最高的员工信息
 SELECT *
 FROM employees
 WHERE (employee_id,salary)=(
 		SELECT MIN(employee_id),MAX(salary)
 		FROM employees
 );

  2. select后面

     #案例：查询每个部门的员工个数
 SELECT d.*,(
 	SELECT COUNT(*)
 	FROM employees e
 	WHERE e.department_id=d.department_id
 ) 个数
 FROM departments d;

  3. from后面
    将子查询结果充当一张表，要求必须起别名

     #案例：查询每个部门的平均工资的工资等级

 SELECT ag_dep.*,g.grade_level
 FROM (
 	SELECT AVG(salary) ag,department_id
 	FROM employees
 	GROUP BY department_id
 ) ag_dep
 INNER JOIN job_grades g
 ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

  4. exists后面（相关子查询）
    语法：
    exists（完整的查询语句）
    结果：1或0

     #案例1：查询有员工的部门名
 SELECT department_name
 FROM departments d
 WHERE EXISTS(
 	SELECT *
 	FROM employees e
 	WHERE d.department_id=e.department_id
 );

八、分页查询

应用场景： 当要显示的数据，一页显示不全，需要分页提交sql请求
语法：

select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序后的字段】
limit offset，size;

	offset要显示条目的起始索引（起始索引从0开始）
	size要要显示的条目个数

特点：
①limit语句放在查询语句的最后
②公式
要显示的页数是page，每页的条目数size

	select 查询列表
	from 表
	limit (page-1)*size,size;

举例：

#案例1：查询前5条员工信息
SELECT *
FROM employees
LIMIT 0,5;

#案例2：查询第11条-第25条员工信息
SELECT *
from employees
LIMIT 10,15;

#案例3：有奖金的员工信息，且工资较高的前10名显示出来
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10;

九、联合查询

union 联合 合并： 将多条查询语句合并成一个结果

语法：
查询语句1
UNION
查询语句2
UNION
查询语句2
UNION
…

应用场景：
要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时

特点：
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all可以包含重复项

#引入的案例：查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id>90 OR email LIKE '%a%';

#或
SELECT * FROM employees WHERE department_id>90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';
zhkey123
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL学习笔记之DQL(六)
01-20
查询语句(DQL)的使用 我们在演示之前先建一个表，如下： CREATE TABLE student02( id INT, NAME VARCHAR(32), age INT, sex VARCHAR(16), address VARCHAR(16), math INT, english INT ); 然后插入一些数据...
MySQL-DQL笔记
qq_24433919的博客
01-17 303
目录MySql-DQL笔记简单查询条件查询between... and...NULL MySql-DQL笔记 简单查询 查询员工姓名和年薪（月薪*12） # 查询员工姓名和年薪（月薪*12） select ename, month_sal * 12 as year_sal from emp; select ename, month_sal * 12 'year_sal' from emp; select * from emp; as 可以给列取别名, as 可以省略。 MySql可以用双
MySQL学习笔记-DQL
Aaaabt的博客
05-17 174
MySQL学习笔记-DQL 去重 distinct 作用：去除select查询出来的结果中重复的数据，并重复的数据只显示一条 select distinct id from student; -- 重复数据可去重 数据库的列(表达式) select version() -- 查询系统版本(函数) select 1+1 as result -- 计算 (表达式) select @@auto_increment_increment -- 查询自增的步长 (变量) 数据库中的表达式：文本值，列，nul
MYSQL 学习笔记DQL-2
m0_53245055的博客
03-24 51
select sum(age) from emp where workaddress='西安';IN(20,21,22,23) 表示在这个范围内。执行顺序:WHERE > 聚合函数 > HAVING。SELECT 聚合函数（字段列表）FROM 表名;起始索引=（查询页码-1）*每页显示记录数。HAVING 后面是分组过滤后的条件。ASC 升序 默认值。count 统计数量。
MySQL笔记----自用
ketemstion的博客
12-06 703
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 文章目录前言一、MySQl是什么？二、MySQL注释1.单行注释2.读入数据三、MySQL语句分类1.DDL：操作数据库2.DDL：操作数据库表3.DML： 对表中的数据进行增删改4.DQL：对表中的数据进行查询5.DCL： 对数据库进行权限控制四、约束五、数据库设计六、多表查询七、事务总结 前言 提示：这里可以添加本文要记录的大概内容： 例如：随着人工智能的不断发展，机器学习这门技术也越来越重要，很多人都开启了学习机器学习，本文就介绍了
MySQL笔记2--DQL
weixin_45502414的博客
02-21 378
IFNULL函数与ISNULL函数的区别 IFNULL函数 功能：判断某字段或表达式是否为null，如果为null返回指定的值，否则返回原本的值。 ISNULL函数 功能：判断某字段或表达式是否为null，如果是，则返回1，否则返回0. 进阶三、排序查询 /*语法： select查询列表 from表 （where筛选条件） order by排序列表 （asc（升序）|desc（降序）） 特点： 1、asc代表的是升序，desc代表的是降序。如果不写，默认是升序 2、or...
mysql笔记--数据库查询语言 DQL
ohMyGodSun的博客
10-15 135
DQL：数据库查询语言。关键字：SELECT … FROM … WHERE。 DDL ：数据库模式定义语言。关键字：CREATE，DROP，ALTER。 DML：数据操纵语言。关键字：INSERT、UPDATE、DELETE。 DCL：数据控制语言 。关键字：GRANT、REVOKE。 TCL：事务控制语言。关键字：COMMIT、ROLLBACK、SAVEPOINT。 DDL,DML,DCL,DQ...
【跟上黑马学sql】学习笔记-MySQL-DQL-条件查询
weixin_67842001的博客
06-03 122
#创建emp1表 create table emp1 ( id int comment '编号', workno varchar(10) comment '工号', name varchar(5) comment '姓名', gender char(1) comment '性别', age tinyint unsigned comment '年龄', idcard char(18) comment '身份证号', workaddress varc
MySQL数据库笔记-基础篇
yjnlovepy的博客
05-14 1368
数据库是数据存储的仓库，数据是有组织的进行存储简称DataBase（DB）数据库管理系统，操纵和管理数据库的大型软件简称DataBase Management System（DBMS）SQL是操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准简称：Structured Query Language（SQL）可以利用sql来操作数据库管理系统，用数据库管理系统来创建、操作一个数据库或者利用sql来操作数据库管理系统，从而来操作数据库当中的数据主流的关系型数据库管理系统。
MySql学习-DQL
好好学习的博客
07-27 167
一个萌新的学习笔记
DQL语言---MySQL学习
qq_53790682的博客
07-15 183
MySQL中的DQL语言学习笔记
MySQL笔记整理.doc
07-12
数据库，简而言之可视为电子化的文件柜——存储电子文件...这里有我整理的一些关于数据库的笔记（MySQL），其中包括了DDL、DML、DQL、DCL等知识，内容相对基础，适合小白用于复习使用，或者参照数据库视频教学等学习。
MySQL个人学习笔记
04-10
个人学习MySQL的一套学习笔记，记录了我所学的各种MySQL语句及其知识要点，采用思维导图的方式记录，逻辑清晰，分类准确，方便查找所需的知识点，也方便随时打开查阅，可以拿来复习、学习、或者查阅相应语句，也可以...
MySQL5000字超详细笔记
01-08
这份详尽的MySQL笔记是一个宝贵的资源，非常适合那些想要深入理解和掌握MySQL数据库管理系统的人。它全面覆盖了MySQL的关键特性和操作，从基本概念、数据类型和表操作，到高级功能如事务控制、分布式存储和索引优化...
MySQL核心技术学习笔记
12-16
MySQL核心技术学习笔记，包括从MySQL产品的安装到DQL语言的学习、DML语言的学习、DDL语言的学习、TCL语言的学习、视图、存储过程等，以及相关案例实现语句等等。
手撕spring框架（3）
dzend的专栏
05-01 623
在Spring框架中，BeanPostProcessor是一个接口，它允许在Spring容器实例化bean之后和调用bean的初始化方法之前对bean进行自定义处理。BeanPostProcessor接口定义了两个方法：postProcessBeforeInitialization和postProcessAfterInitialization。通过实现这两个方法，我们可以在bean初始化前后做一些额外的操作，比如修改bean的属性值、添加额外的逻辑等。
常用SQL命令
最新发布
wenxiaoba的博客
05-02 1135
常用SQL语句使用说明和示例
oracle pl/sql 如何让sql windows 显示行号
alva_xu的博客
04-30 475
其实很简单。
优化SQL的方法
weixin_44139651的博客
04-26 1592
使用join实现，但不适合join太多表，阿里巴巴开发者手册的规定，join表的数量不应该超过3个，join表数量太多时，会导致mysql在选错索引。一般SQL优化第一考虑的是索引优化，可使用explain命令，查看MySQL的执行计划，确认SQL是否有走索引。相对连接查询，子查询使用in关键字实现，具有结构化，相对简单，但是需要创建和删除临时表，增加资源消耗。索引可提升SQL效率，但索引需要额外的存储空间，而且还会有一定的性能消耗。left join，两个表的交集，以及左表剩余的数据，左表为驱动表。
mysql DML DQL
03-28
MySQL是一种常用的关系型数据库管理系统，支持多种操作语言，包括数据操作语言（DML）和数据查询语言（DQL）。 DML（Data Manipulation Language）是用于对数据库中的数据进行操作的语言。常见的DML操作包括插入...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值