精华内容
下载资源
问答
  • 五、MySQL中DQL语言

    2020-08-06 16:03:02
    MySQL中DQL语言MySQL的DQL语言基础查询条件查询排序查询常见函数分组查询连接查询子查询Exist + 子查询(相关子查询)In 子查询分页查询练习题目3Exer: 获取email的username查询语句的所有关键字以及执行先后顺序...

    系列目录:
    下一篇:六、MySQL中DML语言(增删改)


    MySQL的DQL语言

    1. 基础查询

    1. 语法: SELECT 要查询的东西 【FROM 表名】; CONCAT
      类似于Java中 :System.out.println(要打印的东西);
    2. 特点:
      ①通过select查询完的结果 ,是一个虚拟的表格,不是真实存在
      ②要查询的东西: 可以是常量值、可以是表达式、可以是字段、可以是函数

    1.查询多个字段

    SELECT last_name, salary FROM employees;

    2.查询全部字段(顺序和原本表中相同)

    SELECT * FROM employees;
    如果是关键字,比如SELECT NAME FROM employees; NAME加上单引号!

    3.查询常量(注意:字符型和日期型的常量值必须用单引号引起来,数值型不需要)

    SELECT 100; SELECT ‘john’;

    4.查询函数

    SELECT VERSION();

    5.查询表达式

    SELECT 100%98;

    6.起别名;

    1)方便理解;2)如果field重名,使用别名可以区分开。

    6.1使用AS

    SELECT 100%98 AS res;

    6.2 使用空格

    SELECT 100%98 res;
    案例 查询salary,显示结果为out put
    SELECT salary AS “out put” FROM employees;

    7.去重复(select distinct a,b from 表明是不可以的,因为可能导致两边去重后行数不同)

    SELECT DISTINCT department_id FROM employees;

    8.+号的作用,IFNULL,

    mysql中的+号,仅仅可以当作运算符,不能当作连接符。
    select 100+90; 两个操作数都为数值型,则做加法运算
    select ‘123’+90;只要有一个为字符型,试图把字符型转换为数值型;如果成功,继续加法运算;
    如果失败,字符型数值转换为0;
    select null+10; 只要有一方为null,则结果肯定为null;
    案例:查询员工名和姓连接成一个字段,并显示为 姓名
    concat可以连接的语句有的field可能有null,因此使用IFNULL(commision_pct, 0) AS 奖金率
    ISNULL 返回指定的field是不是null,如果是null,返回1.

    SELECT CONCAT(last_name, ' ' ,IFNULL(first_name, empty)) AS 姓名
    FROM
    	employees;
    

    2. 条件查询

    3. 排序查询

    4. 常见函数

    DATEDIFF 日期差距

    DATEDIFF(NOW(), ‘1988-1-1’) > 0 用来判断当前时间是否大于1988-1-1号后

    5. 分组查询

    6. 连接查询

    6.1 sql99内连接 join

    语法:表1 inner join (中inner可以省略) 表2 on 连接条件
    特点:内连接的结果=多表的交集

    6.2 外连接 OUTER JOIN

    语法:left | right | full [outer] join on 连接条件
    特点:

    1. 查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
    2. left join 左边的就是主表,right join右边的是主表。full join两边都是主表。
    3. 一般用于查询除了交集部分的剩余的不匹配的行。

    6.3 交叉连接 CROSS JOIN

    语法:select field etc from 表1 别名 cross join 表2 别名
    特点:类似于笛卡尔乘积,表1有4行,表2有11行,则结果为44行。

    7. 子查询 SubQuery

    1. 含义:嵌套在其他语句内部的select语句叫做子查询或内查询。
    2. 分类:
      1. 按出现位置
        select后面:仅支持标量subquery
        from后面:表子查询
        where或having后面(重点):多行一列,一行一列,行subquery
        exists后面:4个subquery都行。
      2. 结果集的行列
        标量子查询(单行subquery):一行一列
        列subquery:多行一列
        行subquery:多行多列
        表subquery:多行多列

    7.1 Exist + 子查询(相关子查询)

    Exist的子查询不同于其他子查询,有Exist的子查询是先执行主查询,再执行子语句。因为子语句被叫做相关子查询。举个栗子,下面代码的子查询就是用到了主查询中departmetns d这个表。

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

    7.2 In 子查询

    当一个子查询的结果是一列一行或者一列多行的时候,就无脑使用In。
    例题:查询管理者叫King的员工姓名和工资
    叫做King的可能有多个人,所以可能有多行。

    # 1. query manager named king
    SELECT employee_id
    FROM employees
    WHERE last_name = 'King'
    
    # 2. query which employees' manager_id = query 1.
    SELECT last_name, salary
    FROM employees
    WHERE manager_id IN (
    	#query 1
    )
    

    7.3 子查询经典题目

    1. 查询平均工资最低的部门信息
    # 1. 获取工资最低的部门id
    SELECT department_id 
    FROM employees
    GROUP BY department_id
    ORDER BY AVG(salary)
    LIMIT 1;
    
    # 2. 获取部门信息
    SELECT * 
    FROM department
    WHERE department_id = 
    {
    	# 1. subquery
    }
    

    8. 分页查询 LIMIT

    • 应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
    • 语法:limit [offset], size; offset要现实条目的其实索引(起始索引从0开始)(可选),size表示要显示的条目个数。
    • 特点:
      • limit语句放在查询语句之后
      • 公式:第几页和每页显示items数在limit语句中的关系
        要显示的第几页page,每页的条目数size。 limit (page-1)*size, size;
        size = 10
        page start_index
        1 0
        2 10
        3 20
    • 栗子1:查询前5条员工信息 SELECT * FROM employees LIMIT 0, 5 或者 SELECT * FROM employees LIMIT 5 起始从0开始,可以省略0。

    练习题目3

    Exer: 获取email的username

    练习题目3.1

    • 获取邮箱名有一定难度
      substr(email, 1, instr(email, @)-1) 用户名
    • 最后答案
      SELECT substr(email, 1, instr(email, @)-1) 用户名 from stuinfo;

    查询语句的所有关键字以及执行先后顺序

    SELECT后面的Field可以说是order by之前最后执行的。

    在这里插入图片描述

    9. 联合查询 UNION

    1. 应用场景:要查询的结果来自于多个表,且多个表没有直接的连接关系,含义但查询的信息field一致时,使用联合查询。
    2. union 联合 合并:将多条查询语句的结果合并成一个结果
    3. 举例:
      联合查询案例1
    4. 语法:
      查询语句1
      UNION
      查询语句2
    5. 特点:
      1. 要求查询的field类型和顺序一致,并且field数量一致。
      2. UNION默认去重复,UNION ALL可以包含重复项。

    10. 查询总结

    1. 语法
    select 查询列表 					# 7
    from1 别名 					# 1 
    连接类型 join2 别名 			# 2
    on 连接条件 						# 3
    where 筛选条件 					# 4
    group by 分组列表 				# 5
    having 筛选(group by的field) 	# 6
    order by 排序 					# 8
    limit 起始条目索引,条目数; 		# 9
    
    展开全文
  • SQL中DQL语言笔记

    2021-08-03 20:46:41
    SQL中DQL语言笔记
  • DQL语言(一)

    2020-08-14 08:53:13
    DQL语言 DQL数据查询语言 SELECT语法 查询所有学生信息(所有列,效率低) select * from student; 查询指定列(学号,姓名)不区分大小写 SELECT studentno,studentname FROM student; 取别名 关键字as,也...

    DQL语言

    DQL数据查询语言

    SELECT语法

    查询所有学生信息(所有列,效率低)

    在这里插入图片描述

    select * from student;
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMoGHcIC-1597365958916)(C:\Users\ZMZDMX\AppData\Local\Temp\1597282545165.png)]

    查询指定列(学号,姓名)不区分大小写

    SELECT studentno,studentname FROM student;
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WTIC91oA-1597365958917)(C:\Users\ZMZDMX\AppData\Local\Temp\1597282678305.png)]

    取别名 关键字as,也可以用空格

    SELECT studentno AS 学号,studentname AS 姓名 FROM student;
    
    • 使用as也可以为表取别名,在多表查询时用于区分
    SELECT studentno AS 学号,studentname AS 姓名 FROM student AS s;
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3Ui0pUQT-1597365958919)(C:\Users\ZMZDMX\AppData\Local\Temp\1597283717143.png)]

    • distinct去除重复项,默认为all
    • select查询中可以使用表达式

    满足条件的查询

    select studentno,studentresult from result
    where studentresult between 95 and 100;
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fZsYRPFC-1597365958919)(assets/1597284776731.png)]

    比较运算符

    • like接合使用的通配符:%(0到任意个字符) _ (一个字符)

      select StudentNo,StudentName
      from student
      where studentname like '李%';
      
    • 转义符 \ 能不能使用我自己的转义符号呐

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rTlfxc1p-1597365958920)(assets/1597285518082.png)]

    • in 在多个值匹配相同的字符

    • null 空不能用=,和null必须写is或者is not

      • 注意:如果默认为非空,删除内容后不是null,而是=’'来进行匹配

      [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zGav6GmO-1597365958921)(assets/1597286568438.png)]

    链接查询

    • 内连接 .inner join查询两个表中的结果集中的交集

    • SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
      FROM `student`  s
      INNER JOIN `result`  r
      ON r.`StudentNo`=s.`StudentNo`
      
    • 外连接 outer join

      • 左外连接 left join以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以null填充

      • 右外连接right join 与上面相反

        [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-otBGBEMY-1597365958922)(assets/1597288792447.png)]

    • 自连接

    
    
    CREATE TABLE categorg(
    category_id INT(11) AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(10),
    pid INT(10)
    );
    
    
    INSERT INTO `categorg`(`category_name`,pid)
    VALUES('软件开发',0),('美术设计',0),
    ('数据库继承',1),('ps基础',2),('色彩搭配',2),
    ('php基础',1),('一起学java',1);
    
    select
      a.`category_name` as '父栏目',
      b.`category_name` as '子栏目'
    from
      `categorg` a
      join `categorg` b
      on b.`pid`=a.`category_id`
    where a.pid = 0 ;
    

    • 等值连接 非等值连接
    等值连接
    SELECT s.`StudentNo`,`StudentName`,`SubjectNo`,`StudentResult`
    FROM `student`  s,`result`  r
    WHERE r.`StudentNo`=s.`StudentNo`;
    

    小知识点

    round() 四舍五入
    rand()随机数

    用in进行子查询

    SELECT * FROM student WHERE `StudentNo` IN (SELECT ROUND(RAND()*2+1011));
    

    笛卡尔集合,非常消耗内存,不建议使用
    在这里插入图片描述

    case 字段 when 值 then 操作 else 操作 end;

    在这里插入图片描述

    多个情况 case 字段 when 值 then 操作 when 值 then 操作 else 操作 end;

    全连接 union:联合并去重 union all:联合不去重

    展开全文
  • DQL语言(SELECT)用法

    2021-02-19 08:23:19
    DQL语言(SELECT)用法
  • MySQL数据库DQL语言

    2020-06-17 16:01:00
    文章目录前言什么是DQL语言?案例注意基础查询案例+号的作用过滤和排序数据过滤排序order by总结 前言 前面博客写了,mysql安装以及mysql的账号管理,四大引擎,DLL语言等等。。 今天我们来看DQL语言 MySql...

    前言

    前面博客写了,mysql安装以及mysql的账号管理,四大引擎,DLL语言等等。。
    今天我们来看DQL语言

    MySql数据库安装与卸载https://blog.csdn.net/licmi/article/details/106690664.
    MySQL账号管理+四大引擎+DDL语言https://blog.csdn.net/licmi/article/details/106750643.

    什么是DQL语言?

    DQL是数据查询语言,数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE
    子句组成的查询块:
    SELECT <字段名表>
    FROM <表或视图名>
    WHERE <查询条件>
    下面我们来看案例:

    案例

    注意

    SQL 语言大小写不敏感。 • SQL 可以写在一行或者多行
    关键字不能被缩写也不能分行
    各子句一般要分行写。
    使用缩进提高语句的可读性。

    基础查询案例

    基本查询语法:

    select 查询列表 from 表名;
    
    
    类似于:System.out.println(打印东西);
    
    特点:
    
    1、查询列表可以是:表中的字段、常量值、表达式、函数
    2、查询的结果是一个虚拟的表格
    
    #1.查询表中的单个字段
    
    SELECT last_name FROM t_mysql_employees;
    
    #2.查询表中的多个字段
    SELECT last_name,salary,email FROM t_mysql_employees;
    
    #3.查询表中的所有字段
    
    #方式一:
    SELECT 
        `employee_id`,
        `first_name`,
        `last_name`,
        `phone_number`,
        `last_name`,
        `job_id`,
        `phone_number`,
        `job_id`,
        `salary`,
        `commission_pct`,
        `manager_id`,
        `department_id`,
        `hiredate` 
    FROM
        t_mysql_employees ;
    #方式二:  
     SELECT * FROM t_mysql_employees;
     
     #4.查询常量值
     SELECT 100;
     SELECT 'john';
     
     #5.查询表达式
     SELECT 100%98;
     
     #6.查询函数
     
     SELECT VERSION();
     
     
     #7.起别名
     /*
     ①便于理解
     ②如果要查询的字段有重名的情况,使用别名可以区分开来
     
     */
     #方式一:使用as
    SELECT 100%98 AS 结果;
    SELECT last_name AS 姓,first_name AS 名 FROM t_mysql_employees;
    
    #方式二:使用空格
    SELECT last_name 姓,first_name 名 FROM t_mysql_employees;
    
    
    #案例:查询salary,显示结果为 out put
    SELECT salary AS "out put" FROM t_mysql_employees;
    
    
    #8.去重
    
    
    #案例:查询员工表中涉及到的所有的部门编号
    SELECT DISTINCT department_id FROM t_mysql_employees;
    
    +号的作用
    java中的+号:
    ①运算符,两个操作数都为数值型
    ②连接符,只要有一个操作数为字符串
    
    mysql中的+号:
    仅仅只有一个功能:运算符
    
    select 100+90; 两个操作数都为数值型,则做加法运算
    select '123'+90;只要其中一方为字符型,试图将字符型数值转换成数值型
    			如果转换成功,则继续做加法运算
    select 'john'+90;	如果转换失败,则将字符型数值转换成0
    
    select null+10; 只要其中一方为null,则结果肯定为null
    

    过滤和排序数据

    过滤

    使用WHERE 子句,将不满足条件的行过滤掉。WHERE 子句紧随 FROM 子句。

    一、按条件表达式筛选
    简单条件运算符:> < = != <> >= <=

    #案例1:查询工资>12000的员工信息
    SELECT 
    	*
    FROM
    	t_mysql_employees
    WHERE
    	salary>12000;
    	
    	
    #案例2:查询部门编号不等于90号的员工名和部门编号
    SELECT 
    	last_name,
    	department_id
    FROM
    	t_mysql_employees
    WHERE
    	department_id<>90;
    

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

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

    #案例1:查询工资z在10000到20000之间的员工名、工资以及奖金
    SELECT
    	last_name,
    	salary,
    	commission_pct
    FROM
    	t_mysql_employees
    WHERE
    	salary>=10000 AND salary<=20000;
    #案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
    SELECT
    	*
    FROM
    	t_mysql_employees
    WHERE
    	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;
    

    三、模糊查询
    like
    between and
    in
    is null
    特点:
    ①一般和通配符搭配使用
    通配符:
    % 任意多个字符,包含0个字符
    _ 任意单个字符

    #案例1:查询员工名中包含字符a的员工信息
    
    select 
    	*
    from
    	employees
    where
    	last_name like '%a%';#abc
    #案例2:查询员工名中第三个字符为e,第五个字符为a的员工名和工资
    select
    	last_name,
    	salary
    FROM
    	t_mysql_employees
    WHERE
    	last_name LIKE '__n_l%';
    
    #案例3:查询员工名中第二个字符为_的员工名
    
    SELECT
    	last_name
    FROM
    	t_mysql_employees
    WHERE
    	last_name LIKE '_$_%' ESCAPE '$';
    

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

    #案例1:查询员工编号在100到120之间的员工信息
    
    SELECT
    	*
    FROM
    	t_mysql_employees
    WHERE
    	employee_id <= 120 AND employee_id>=100;
    #--------------------------------------between方式
    SELECT
    	*
    FROM
    	t_mysql_employees
    WHERE
    	employee_id BETWEEN 100 AND 120;
    

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

    #案例:查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    
    SELECT
    	last_name,
    	job_id
    FROM
    	t_mysql_employees
    WHERE
    	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';
    
    
    #------------------
    
    SELECT
    	last_name,
    	job_id
    FROM
    	t_mysql_employees
    WHERE
    	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');
    

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

    #案例1:查询没有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	t_mysql_employees
    WHERE
    	commission_pct IS NULL;
    
    
    #案例1:查询有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	t_mysql_employees
    WHERE
    	commission_pct IS NOT NULL;
    
    

    安全等于 <=>

    #案例1:查询没有奖金的员工名和奖金率
    SELECT
    	last_name,
    	commission_pct
    FROM
    	t_mysql_employees
    WHERE
    	commission_pct <=>NULL;
    	
    	
    #案例2:查询工资为12000的员工信息
    SELECT
    	last_name,
    	salary
    FROM
    	t_mysql_employees
    
    WHERE 
    	salary <=> 12000;
    	
    
    #is null pk <=>
    
    IS NULL:仅仅可以判断NULL值,可读性较高,建议使用
    <=>    :既可以判断NULL值,又可以判断普通的数值,可读性较低
    
    排序order by

    语法:
    select 查询列表
    from 表名
    【where 筛选条件】
    order by 排序的字段或表达式;

    特点:
    1、asc代表的是升序,可以省略
    desc代表的是降序

    2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段

    3、order by子句在查询语句的最后面,除了limit子句

    #1、按单个字段排序
    SELECT * FROM t_mysql_employees ORDER BY salary DESC;
    
    #2、添加筛选条件再排序
    
    #案例:查询部门编号>=90的员工信息,并按员工编号降序
    
    SELECT *
    FROM t_mysql_employees
    WHERE department_id>=90
    ORDER BY employee_id DESC;
    
    
    #3、按表达式排序
    #案例:查询员工信息 按年薪降序
    
    
    SELECT *,salary*12*(1+IFNULL(commission_pct,0))
    FROM t_mysql_employees
    ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
    
    
    #4、按别名排序
    #案例:查询员工信息 按年薪升序
    
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM t_mysql_employees
    ORDER BY 年薪 ASC;
    
    #5、按函数排序
    #案例:查询员工名,并且按名字的长度降序
    
    SELECT LENGTH(last_name),last_name 
    FROM t_mysql_employees
    ORDER BY LENGTH(last_name) DESC;
    
    #6、按多个字段排序
    
    #案例:查询员工信息,要求先按工资降序,再按employee_id升序
    SELECT *
    FROM t_mysql_employees
    ORDER BY salary DESC,employee_id ASC;
    
    #1.查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
    
    SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
    FROM t_mysql_employees
    ORDER BY 年薪 DESC,last_name ASC;
    
    
    #2.选择工资不在8000到17000的员工的姓名和工资,按工资降序
    SELECT last_name,salary
    FROM t_mysql_employees
    
    WHERE salary NOT BETWEEN 8000 AND 17000
    ORDER BY salary DESC;
    
    #3.查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序
    
    SELECT *,LENGTH(email)
    FROM t_mysql_employees
    WHERE email LIKE '%e%'
    ORDER BY LENGTH(email) DESC,department_id ASC;
    

    总结

    今天就到这里了如果有什么不对的地方欢迎大家在评论区留言交流改进!!

    展开全文
  • 文章目录DQL语言的学习基础查询:条件查询:排序查询:常见函数:分组函数:分组查询:连接查询:子查询:分页查询:union联合查询: DQL语言的学习 DQL:(Data Query Language):数据查询语言 作为案例的数据库...

    DQL语言的学习

    • DQL:(Data Query Language):数据查询语言

    作为案例的数据库中的表的结构

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aXqmbITC-1599475471894)(C:\Users\Zhijiaxin\AppData\Roaming\Typora\typora-user-images\1599441257611.png)]

    基础查询:
    语法:
    select 查询列表 from 表名
    特点:
    1.查询列表可以是:表中的字段、常量值、表达式、函数
    2.查询的结果是一个虚拟的表格
    
    #1.查询表中的单个字段
    select last_name from employees;
    
    #2.查询表中的多个字段
    select last_name,salary,email from employees;
    
    #3.查询表中的所有字段
    select * from employees;
    
    #4.查询常量值
    select 100;
    select 'john';
    
    #5.查询表达式
    select 100*98;
    
    #6.查询函数
    select VERSION();
    
    #7.起别名
    /*
    ①.便于理解
    ②.如果要查询的字段有重名的情况,使用别名可以区分开来
    */
    
    #方式一:使用AS
    select 100%98 AS 结果;
    select last_name AS,first_name ASfrom employees;
    
    #方式二:使用空格
    select last_name 姓,first_name 名 from employees;
    
    select salary "out put" from employees; (出现歧义加上双引号)
    
    #8.去重
    
    #案例:查询员工表中涉及到的所有的部门编号
    SELECT DISTINCT department_id from employees;
    
    mysql中的+号:
    仅仅有一个功能:运算符
    
    SELECT 100+90;两个操作数都为数值型,则做加法运算
    select '123' + 90;   其中一方为字符型,试图将字符型数值转换成数值型
    				     如果转换成功,则继续做加法运算
    select 'john'+90;    如果转换失败,则将字符型数值转换成0
    select null+10;      只要其中一方为null,则结果肯定为null
    
    #案例:查询员工名和姓连接成一个字段,并显示为姓名
    select concat(last_name,' ',first_name) as 姓名 from employees;
    
    条件查询:
    语法: select 查询列表 from 表名 where 筛选条件;(执行顺序:先查表名再筛选条件之后进行查询)
    分类: 一、按条件表达式筛选  条件运算符: > < = != <> >= <= 
         二、按逻辑表达式筛选     作用:用于连接条件表达式    逻辑运算符: && || ! and or not 
         三、模糊查询    like       between and      in       is null
    
    #一、按条件表达式筛选
    
    #案例1:查询工资>12000的员工信息
    select * from employees where salary > 12000;
    
    #案例2:查询部门编号不等于90号的员工名和部门编号
    select last_name,department_id from employees where department_id != 90;(!= 也可以换成<>)
    
    #二、按逻辑表达式筛选
    
    #案例1:查询工资在10000到20000之间的员工名、工资以及奖金
    select last_name,salary,commission_pct from employees where salary >= 10000 and salary <= 20000;
    
    #案例2:查询部门编号不是在90到110之间,或者工资高于15000的员工信息
    select * from employees where !(department_id >= 90 and department_id <= 110) or salary >= 15000;
    
    #三、模糊查询
    /*
    like
    特点:
    ①:一般和通配符搭配使用
    	通配符:
    	% 任意多个字符,包含0个字符
    	_ 任意单个字符
    between and
    in 
    is null | is not null
    */
    #1.like 
    
    #案例1:查询员工名中包含字符a的员工信息
    select * 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 where last_name like '_\_%';(使用转义字符)
    select last_name from employees where last_name like '_$_%' ESCAPE '$';(指定任意字符为转义字符)
    
    #2.between and
    /*
    ①使用between and可以提高语句的简洁度
    ②包含临界值
    ③两个临界值不要调换顺序(完全等价于大于等于左边的值,小于等于右边的值)
    */
    
    #案例1:查询员工编号在100到120之间的员工信息
    select * from employees where employee_id between 100 and 120;
    
    #3.in
    /*
    含义:判断某字段的值是否属于in列表中的某一项
    特点:
    ①使用in提高语句简洁度
    ②in列表的值类型必须一致或兼容
    ③不支持通配符的使用
    select last_name,job_id from employees where job_id in('IT_PROG','AD_%')(不支持这种写法,通配符与like相搭配使用,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
    /*
    =或<>不能用于判断null值
    is null 或 is not null 可以判断null值
    */
    
    #案例1:查询没有奖金的员工名和奖金率
    select last_name,commission_pct from employees where commission_pct is null;(=号不能用来判断null)
    select last_name,commission_pct from employees where commission_pct is not null;
    
    #安全等于  <=>
    
    #案例1:查询没有奖金的员工名和奖金率
    select last_name,commission_pct from employees where commission_pct <=> null;
    
    #案例2:查询工资为12000的员工信息
    select * from employees where salary <=> 12000;
    
    #is null 与 <=>的比较
    /*
    is null:仅仅可以判断null值,可读性较高,推荐使用
    <=>:既可以判断null值,又可以判断普通的数值,可读性较低
    */
    
    #查询员工号为176的员工的姓名和部门号和年薪
    #IFNULL(expr1,expr2) 如果expr1为NULL,则用expr2的值来代替
    select last_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) AS 年薪 from employees where employee_id=176;
    
    测试:
    #查询员工号为176的员工的姓名和部门号和年薪
    select last_name,department_id,salary * 12 * (1+IFNULL(commission_pct,0)) AS 年薪 from employees where employee_id=176;
    
    #查询没有奖金,且工资小于18000的salary,last_name
    select last_name,salary from employees where commission_pct is null and salary < 18000;
    
    #查询employees表中,job_id 不为 'IT' 或者工资为12000的员工信息
    select * from employees where salary = 12000 or job_id <>'IT';
    
    #查询部门departments表的结构
    desc departments;
    
    #查询部门department表中涉及到了哪些位置编号
    select distinct location_id from departments;
    
    select * from employees;
    select * from employees where commission_pct like '%%' and last_name like '%%';
    #(区别在于like不能查出为null的字段)
    
    排序查询:
    #进阶3:排序查询
    
    /*
    语法:
    select 查询列表 from 表 where 筛选条件 order by 排序列表 asc | desc
    特点:
    			1.asc代表的是升序,desc代表的是降序
    			如果不写,默认是升序
    			2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
    			3.order by字句一般是放在查询语句的最后面,limit子句除外
    			4.执行顺序:先找表,后进行条件筛选,在进行查询最后进行排序
    
    */
    
    #案例1:查询员工信息:要求工资从高到低排序
    select * from employees order by salary desc;
    #工资由低到高排序
    select * from employees order by salary;
    
    #案例2:查询部门编号>=90的员工信息,按入职时间的先后进行排序[添加筛选条件]
    select * from employees where department_id >= 90 order by hiredate;
    
    #案例3:按年薪的高低显示员工的信息和年薪[按表达式排序]
    select *,salary * 12 * (1+IFNULL(commission_pct,0)) as 年薪 from employees order by salary * 12 * (1+IFNULL(commission_pct,0));
    
    #案例4:按年薪的高低显示员工的信息和年薪[按年薪排序]
    select *,salary * 12 * (1+IFNULL(commission_pct,0)) as 年薪 from employees order by 年薪;
    
    #案例5:按姓名的长度显示员工的姓名和工资[按函数排序]
    select length(last_name) as 字节长度,last_name,salary from employees order by LENGTH(last_name) desc;
    
    #案例6:查询员工信息,要求先按工资升序,再按员工编号降序[按多个字段排序]
    select * from employees order by salary asc,employee_id desc;
    
    常见函数:
    #进阶4:常见函数
    /*
    概念:类似于Java中的方法,将一组逻辑语句封装在方法体中,对外暴露方法名
    好处:1、隐藏了实现细节  2、提高代码的重用性 
    调用:select 函数名(实参列表) from 表;
    特点:
    		①叫什么(函数名)
    		②干什么(函数功能)
    
    分类:
    		1、单行函数:
    		如 concat、length、ifnull等
    		2、分组函数
    		功能:做统计使用,又称为统计函数、聚合函数、组函数
    		1
    常见函数:
    		字符函数:length、concat、substr、instr、trim、upper、lower、lpad、rpad、replace
    		
    		数学函数:round、ceil、floor、truncate、mod
    		
    		日期函数:now、curdate、curtime、year、month、monthname、day、hour、minute、second、str_to_date、date_format
    		
    		其他函数:version、database、user
    		
    		控制函数if、case
    */
    
    #一、字符函数
    #1.length 获取参数值的字节个数
    select length('john');
    select length('张三丰哈哈哈');
    
    show VARIABLES LIKE '%char%';
    
    #2.concat 拼接字符串
    select CONCAT(last_name,'_',first_name) 姓名 from employees;
    
    #3.upper、lower
    select UPPER(last_name) from employees;
    select LOWER(first_name) from employees;
    
    #示例:将姓变大写,名变小写,然后拼接
    select CONCAT(UPPER(last_name),'_',LOWER(first_name)) from employees;
    
    #4.substr、substring
    #注意:索引从1开始
    #截取从指定索引处后面所有字符
    select SUBSTR('李莫愁爱上了陆展元',7) out_put;
    
    #截取从指定索引处指定字符长度的字符
    select SUBSTR('李莫愁爱上了陆展元',1,3) out_put;
    
    #案例:姓名中首字符大写,其他字符小写然后用_拼接,显示出来
    select CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) as 姓名 from employees;
    
    #5.instr 返回子串第一次出现的索引,如果找不到返回0
    select INSTR('杨不悔爱上了殷六侠','殷六侠') as out_put;
    
    #6.trim
    select LENGTH(TRIM('    张翠山     ')) as out_put;
    
    select TRIM('a' from 'aaaaaaaa张aaaaa翠山aaaaaaaaaaaaaaaaaaaaa') as out_put;
    
    #7.lpad 用指定的字符实现左填充指定长度
    select LPAD('殷素素',10,'*') as out_put;
    
    #8.rpad 用指定的字符实现右填充指定长度
    select RPAD('殷素素',12,'ab') as out_put;
    
    #9.replace 替换
    select replace('张无忌爱上了周芷若张无忌爱上了周芷若','周芷若','赵敏') as out_put;
    
    #二、数学函数
    
    #round 四舍五入
    
    select ROUND(1.65);
    select ROUND(-1.45);
    select ROUND(1.567,2);
    
    #ceil 向上取整,返回>=该参数的最小整数
    
    select CEIL(1.52);
    select CEIL(1.00);
    select CEIL(-1.52);
    
    #floor 向下取整,返回<=该参数的最大整数
    select FLOOR(9.99);
    select FLOOR(-9.99);
    
    #truncate 截断
    select TRUNCATE(1.65,1);
    
    #mod 取余
    select MOD(10,3);
    select MOD(-10,-3);
    
    #三、日期函数
    #now 返回当前系统日期+时间
    select NOW();
    
    #curdate 返回当前系统日期,不包含时间
    select CURDATE();
    
    #curtime 返回当前时间,不包含日期
    select CURTIME();
    
    #可以获取指定的部分,年、月、日、小时、分钟、秒
    select YEAR(NOW());
    select YEAR('2000-3-5');
    select YEAR(hiredate)from employees;
    
    select MONTH(NOW());
    select MONTHNAME(NOW());
    
    #STR_TO_DATE(str,format) 将字符通过指定的格式转换成日期
    select STR_TO_DATE('2000-3-5','%Y-%c-%d') as out_put;
    
    #查询入职日期为1992-4-3的员工信息
    select * from employees where hiredate = '1992-4-3';
    select * from employees where hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
    
    #data_format 将日期转换成字符
    select DATE_FORMAT(NOW(),'%y年%m月%d日') as out_put;
    
    #查询有奖金的员工名和入职日期(xx月/xx日 xx年)
    select last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') AS 入职时间 from employees where commission_pct is not null;
    
    #四、其他函数
    select VERSION();
    select DATABASE();
    select USER();
    
    #五、流程控制函数
    #1.if函数:if else 的效果
    
    select IF(10>5,'大','小');
    
    select last_name,commission_pct,IF(commission_pct is null,'没奖金,哈哈','有奖金,嘻嘻') 备注 from employees;
    
    #2.case函数的使用一:switch case 的效果
    /*
    switch (变量或表达式){
    				case 常量1: 语句1: break;
    				...
    				default:语句n:break;
    }
    
    mysql中
    
    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;
    
    #3.case 函数的使用二:类似于 多重if
    /*
    java中:
    if(条件1){
    			语句1;
    }else if(条件2){
    			语句2;
    }
    ...
    else{
    			语句n;
    }
    
    mysql中:
    case 
    when 条件1 then 要显示的值1或 语句1;
    when 条件2 then 要显示的值2或 语句2;
    ...
    else 要显示的值n或语句n;
    end
    */
    
    #案例:查询员工的工资的情况
    /*
    如果工资>20000,显示A级别
    如果工资>15000,显示B级别
    如果工资>10000,显示C级别
    否则,显示D级别
    */
    select last_name,salary,
    case
    when salary>20000 then 'A'
    when salary>15000 then 'B'
    when salary>10000 then 'C'
    else 'D'
    end as '级别'
    from employees;
    
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uTlOEXRZ-1599475471897)(C:\Users\Zhijiaxin\AppData\Roaming\Typora\typora-user-images\1599206721020.png)]

    分组函数:
    #二、分组函数
    /*
    功能:用作统计使用,又称为聚合函数或统计函数或组函数
    
    分类:
    sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
    特点:
    1、sum 、 avg 一般用于处理数值型
    	 max 、 min 、 count 可以处理任何类型
    
    2、以上分组函数都忽略null值
    
    3、可以和distinct搭配实现去重的运算
    
    4、count函数的单独介绍
    一般使用count(*)用作统计行数
    
    5、和分组函数一同查询的字段要求是group by后的字段
    
    */
    
    #1、简单的使用
    select SUM(salary) from employees;
    select AVG(salary) from employees;
    select MIN(salary) from employees;
    SELECT MAX(salary) from employees;
    SELECT COUNT(salary) from employees;
    select COUNT(commission_pct) from employees;
    
    select SUM(salary),AVG(salary) 平均,MAX(salary) 最大,MIN(salary) 最小,COUNT(salary) 个数 from employees;
    
    #2、参数支持哪些类型
    
    select SUM(last_name) , AVG(last_name) from employees;#不会报错,但没有意义
    
    select MAX(last_name),MIN(last_name) from employees;
    
    select MAX(hiredate),MIN(hiredate) from employees;
    
    select COUNT(commission_pct) from employees;
    select COUNT(last_name) from employees;
    
    #3、忽略null
    select SUM(commission_pct) , AVG(commission_pct) from employees;
    select MAX(commission_pct) , MIN(commission_pct) from employees;
    
    #4、和distinct搭配
    select SUM(DISTINCT salary),SUM(salary) from employees;
    
    select COUNT(DISTINCT salary) from employees;
    
    #5、count函数的详细介绍
    select COUNT(salary) from employees;
    
    select COUNT(*) from employees;#用来统计行数
    
    select COUNT(1) from employees;#相当于在表中加了一列常量值用来统计行数
    
    /*
    效率:
    MYISAM存储引擎下(MySQL5.1之前的存储引擎),count(*)的效率高
    INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些
    */
    
    #6、和分组函数一同查询的字段要求是group by后的字段
    
    练习:
    select MAX(salary),MIN(salary),AVG(salary),SUM(salary) from employees;
    
    select YEAR(MAX(hiredate)),MONTH(MAX(hiredate)),DAY(MAX(hiredate)) from employees;
    
    select YEAR(MIN(hiredate)),MONTH(MIN(hiredate)),DAY(MIN(hiredate)) from employees;
    
    select DATEDIFF('2020-9-4','2020-9-1');
    
    select DATEDIFF(NOW(),'2000-3-5');
    
    select NOW();
    
    select DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence from employees;
    
    select COUNT(1) from employees where department_id=90;
    
    #注:datediff(expr1,expr2) 可以用来查询两个日期相差的天数
    
    分组查询:
    #进阶5:分组查询
    /*
    语法:
    				select 分组函数,列(要求出现在group by的后面) from 表 where 筛选条件 group by 分组的列表 order by 子句;
    注意:
    				查询列表必须特殊,要求是分组函数和group by后出现的字段
    				
    特点:
    			1、分组查询中的筛选条件分为两类
    							数据源			   位置					 关键字
    			分组前筛选		 原始表			group by子句的前面		where
    			分组后筛选		 分组后的结果集	  group by子句的后面		  having
    			
    			①分组函数做条件肯定是放在having子句中
    			②能用分组前筛选的,就优先考虑使用分组前筛选
    			
    			2、group by子句支持单个字段分组,多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数使用较少
    			3、也可以添加排序(排序放在整个分组查询的最后)
    */
    #引入:查询每个部门的平均工资
    select DISTINCT department_id,AVG(salary) from employees group by department_id;
    
    #案例1:查询每个工种的最高工资
    select job_id,MAX(salary) from employees group by job_id order by salary desc;
    
    #案例2:查询每个位置上的部门个数
    select COUNT(*),location_id from departments GROUP BY location_id;
    
    #添加筛选条件
    #案例1:查询邮箱中包含a字符的,每个部门的平均工资
    select AVG(salary),department_id,email 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 order by salary asc;
    
    #添加复杂的筛选条件
    
    #案例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 group GROUP BY job_id;
    
    #②根据①结果继续筛选,最高工资>12000
    select job_id,MAX(salary) from employees where commission_pct is not null group by job_id having MAX(salary)>12000;
    
    #案例3:查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
    select manager_id,MIN(salary) from employees where manager_id>102 GROUP BY manager_id having MIN(salary)>5000;
    
    #按表达式或函数分组
    
    #案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5的有哪些
    
    #①查询每个长度的员工个数
    select COUNT(*),LENGTH(last_name) len_name from employees GROUP BY LENGTH(last_name);
    
    
    #②添加筛选条件
    select LENGTH(last_name) len_name,COUNT(*) c from employees GROUP BY LENGTH(last_name) having COUNT(*)>5 order by COUNT(*);
    select LENGTH(last_name) len_name,COUNT(*) c from employees GROUP BY len_name having c>5 order by COUNT(*);#mysql中支持group by子句和order by子句后面使用别名
    
    #按多个字段分组
    #案例:查询每个部门每个工种的员工的平均工资
    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 having AVG(salary)>10000 order by AVG(salary) desc;
    
    #练习
    #1、查询各job_id的员工工资的最大值、最小值、平均值、总和,并按job_id升序
    select MAX(salary),MIN(salary),AVG(salary),SUM(salary) from employees GROUP BY job_id ORDER BY job_id asc;
    
    select MAX(salary)-MIN(salary) diffrence from employees;
    
    select MAX(salary) from employees;
    
    select MIN(salary) from employees where manager_id is not null group by manager_id having MIN(salary)>=6000;
    
    select department_id,COUNT(*),AVG(salary) from employees GROUP BY department_id order by AVG(salary) desc;
    
    select COUNT(*),job_id from employees group by job_id;
    
    连接查询:
    select * from beauty;
    
    select * from boys;
    #进阶6:连接查询
    /*
    含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
    
    笛卡尔乘积现象:表1 有m行 , 表2 有n行, 结果=m*n行
    发生原因:没有有效的连接条件
    如何避免:添加有效的连接条件
    
    分类:
    				按年代分类:
    				sql92标准:仅仅支持内连接在mysql中不支持外连接
    				sql99标准[推荐]:支持内连接+外连接(左外连接和右外连接)+交叉连接
    				
    				按功能分类:
    								内连接
    												等值连接
    												非等值连接
    												自然连接
    								外连接
    												左外连接
    												右外连接
    												全外连接
    								交叉连接
    */
    #笛卡尔积的错误情况
    select name,boyName from boys,beauty where beauty.boyfriend_id=boys.id;
    
    #一、sql92标准
    #1、等值连接
    /*
    ① 多表等值连接的结果为多表的交集部分
    ② n表连接,至少需要n-1个连接条件
    ③ 多表的顺序没有要求
    ④ 一般需要为表起别名
    ⑤ 可以搭配前面介绍的所有子句使用,比如排序、分组、筛选
    */
    #案例1:查询女神名和对应的男神名
    select name,boyName from boys,beauty 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 AS e,jobs where employees.job_id=jobs.job_id;(注意给表起了别名,就不能再使用表名)
    select last_name,e.job_id,job_title from employees AS e,jobs where e.job_id=jobs.job_id;
    
    #3、两个表的顺序是否可以调换
    select last_name,e.job_id,job_title from jobs,employees AS e where e.job_id=jobs.job_id;
    
    #4、可以加筛选条件
    
    #案例:查询有奖金的员工名、部门名
    select last_name,department_name from employees,departments where employees.department_id=departments.department_id and commission_pct is not null;
    
    #案例2:查询出城市中第二个字符为o的部门名和城市名
    select department_name,city from locations,departments where departments.location_id=locations.location_id and city like '_o%';
    
    #5、可以加分组
    
    #案例1:查询每个城市的部门个数
    select COUNT(*),city from departments,locations where departments.location_id=locations.location_id GROUP BY locations.city;
    
    #案例2:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
    select department_name,employees.manager_id,MIN(salary) from employees,departments where employees.department_id=departments.department_id and commission_pct is not null group by department_name,departments.manager_id;
    
    #6、可以加排序
    
    #案例:查询每个工种的工种名和员工的个数,并且按员工个数降序
    select job_title,COUNT(*) from employees,jobs where employees.job_id=jobs.job_id group by job_title order by COUNT(*) desc;
    
    #7、可以实现三表连接
    
    #案例:查询员工名、部门名和所在的城市
    select last_name,department_name,city from employees,departments,locations where employees.department_id=departments.department_id and departments.location_id=locations.location_id;
    
    #查询所有部门的所有员工及部门名称
    select last_name,e.department_id,department_name from employees e,departments d where e.department_id=d.department_id;
    
    #查询90号部门员工的job_id和90号部门的location_id
    select e.job_id,d.location_id,e.department_id from employees e,departments d,locations l where e.department_id=90 and e.department_id=d.department_id and d.location_id=l.location_id;
    
    select job_id,location_id from employees e,departments d where e.department_id=d.department_id and e.department_id=90;
    
    #选择所有有奖金的员工的e姓名,部门名,location_id以及city
    select e.last_name,d.department_name,l.location_id,l.city from employees e,departments d,locations l where e.commission_pct is not null and e.department_id=d.department_id and d.location_id=l.location_id;
    
    #选择city在Toronto工作的员工的姓名,工作编号,部门编号和部门名
    select e.last_name,e.job_id,e.department_id,d.department_name,l.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';
    
    
    #查询每个工种、每个部门的部门名、工种名和最低工资
    select d.department_name,j.job_title,MIN(salary) from departments d,jobs j,employees e where e.department_id=d.department_id and e.job_id=j,job_id group by job_title,department_name;
    
    #查询每个国家下的部门个数大于2的国家编号
    select COUNT(*) 部门个数,country_id from departments d,locations l where d.location_id=l.location_id group by country_id having COUNT(*)>2;
    
    #选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号
    select e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#" from employees e,employees m where e.manager_id=m.employee_id and e.last_name='kochhar';
    
    #二、sql99语法
    /*
    语法:
    				select 查询列表 from 表1 别名[连接类型] join 表2 别名 on 连接条件 where 筛选条件 group by 分组 having 筛选条件 order by 排序列表;
    分类:
    内连接(重点介绍):inner
    				
    外连接
    				左外连接(重点):left[outer]
    				右外连接(重点):right[outer]
    				全外连接:full[outer]
    交叉连接:cross
    
    */
    
    #一) 内连接
    /*
    select 查询列表 from 表1 别名 [inner] join 表2 别名 on 连接条件 where 筛选条件 group by 分组列表 having 分组后的筛选 order by 排序列表 limit 子句;
    
    分类:
    等值
    非等值
    自连接
    
    特点:
    				①添加排序,分组,筛选
    				②inner可以省略
    				③筛选条件放在where后面,连接条件放在on后面,提高分离性,便于阅读
    				④inner join连接和sql92语法中的等值连接效果是一样的,都是查询多表的交集
    */
    
    
    #1、等值连接
    
    #1.查询员工名、部门名
    select last_name,d.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 e.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 inner join job_grades j on e.salary BETWEEN j.lowest_sal and j.highest_sal;
    
    #查询每个工资级别的个数>20的个数,并且按工资级别降序
    select salary,grade_level,COUNT(*) from employees e inner join job_grades j on e.salary BETWEEN j.lowest_sal and j.highest_sal group by grade_level having COUNT(*)>20 order by grade_level desc;
    
    #三) 自连接
    #查询姓名中包含字符k的员工的名字、上级的名字
    select e.last_name 员工,m.last_name 领导 from employees e inner join employees m on e.manager_id=m.employee_id where e.last_name like '%k%';
    
    #二、外连接
    
    /*
    应用场景:用于查询一个表中有,另一个表中没有的
    
    特点:
    1、外连接的查询结果为主表中的所有记录,如果从表中有和它匹配的,则显示匹配的值,如果从表中没有和它匹配的,则显示Null
    	 外连接查询结内连接结果+主表中有而从表没有的记录
    2、左外连接,left join左边的是主表
    	 右外连接,right join右边的是主表
    3、左外和右外交换两个表的顺序,可以实现同样的效果
    4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
    */
    #引入:查询男朋友不在男神表的女神名
    select b.name from beauty b left outer join boys bo on b.boyfriend_id=bo.id where bo.id is null;
    
    #案例1:查询哪个部门没有员工
    #左外
    select d.* ,e.employee_id from departments d left outer join employees e on d.department_id = e.department_id where e.employee_id is null;
    
    #右外
    select d.*,e.employee_id from employees e right outer join departments d on d.department_id = e.department_id where e.employee_id is null;
    
    #全外(mysql中不支持全外连接)
    use girls;
    select b.*,bo.* from beauty b full OUTER join boys bo on b.boyfriend_id = bo.id;
    
    #交叉连接(使用99语法实现笛卡尔乘积)
    use girls;
    select b.*,bo.* from beauty b cross join boys bo;
    
    #sql92和sql99的对比
    /*
    功能:sql99支持的较多
    可读性:sql99实现连接条件和筛选条件的分离,可读性较高
    */
    
    #测试
    #一、查询编号>3的女神的男朋友信息,如果有则列除详细,如果没有,用null填充(左外连接)
    use girls;
    select b.id,b.name,bo.* from beauty b left outer join boys bo on b.boyfriend_id=bo.id where b.id > 3;
    
    #二、查询哪个城市没有部门
    use myemployees;
    select city from locations l left outer join departments d on l.location_id=d.location_id where d.department_id is null;
    
    #三、查询部门名为SAL或IT的员工信息
    #(使用内连接当SAL或IT下没有员工时就无法查出)
    select e.*,d.department_name from employees e inner join departments d on e.department_id=d.department_id where d.department_name in ('SAL','IT');
    
    #(使用外连接,通常情况下对主从表进行区分看查询的条件一般为主表,以主表去从表中进行匹配,再进行查询)
    select e.*,d.department_name from departments d left join employees e on d.department_id=e.department_id where d.department_name in ('SAL','IT');
    
    子查询:
    #进阶7:子查询
    /*
    含义:出现在其他语句中的select语句,称为子查询或内查询
    外部的查询语句,称为主查询或外查询
    
    分类:
    按子查询出现的位置:
    				select后面:
    									 仅仅支持标量子查询
    				from后面:
    									 支持表子查询
    				where或having后面: √
    									 标量子查询 √
    									 列子查询 √
    									 
    									 行子查询
    									 
    				exists后面(相关子查询)
    									 表子查询
    按结果集的行列数不同:
    				标量子查询(结果集只有一行一列)
    				列子查询(结果集只有一列多行)
    				行子查询(结果集有一行多列,也可以是多列多行)
    				表子查询(结果集一般为多行多列)
    */
    
    
    #一、where或having后面
    /*
    1、标量子查询(单行子查询)
    2、列子查询(多行子查询)
    
    3、行子查询(多列多行)
    
    特点:
    ①子查询放在小括号内
    ②子查询一般放在条件的右侧
    ③标量子查询,一般搭配着单行操作符使用
    > < >= <= = <>
    
    ④子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
    
    列子查询,一般搭配着多行操作符使用
    in、any/some、all
    */
    
    #1、标量子查询
    
    #案例1:谁的工资比Abel高
    
    #①查询Abel的工资
    select salary from employees whe,salaryre last_name='Abel';
    
    #②查询员工的信息,满足 salary > ① 结果
    select last_name,salary from employees where salary > (select salary from employees where last_name='Abel');
    
    #案例2:返回job_id与141号员工相同,salary比143号员工多的员工 姓名,job_id和工资
    select job_id from employees where employee_id=141;
    select 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_bame,job_id和salary
    select MIN(salary) from employees;
    select last_name,job_id,salary from employees where salary = (select MIN(salary) from employees);
    
    #案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
    select MIN(salary) from employees where department_id-50; 
    
    select department_id,MIN(salary) from employees group by department_id having MIN(salary) > (select MIN(salary) from employees where department_id =50);
    
    #非法使用标量子查询(> < 属于单行操作符)
    select department_id,MIN(salary) from employees group by department_id having MIN(salary) > (select salary from employees where department_id =50);
    
    #列子查询(多行子查询)
    /*
    搭配多行操作符
    in/not in:等于列表中的任意一个
    any/some:和子查询返回的某一个值比较
    all:和子查询返回的所有值比较
    */
    
    #返回location_id是1400或1700的部门中的所有员工姓名
    #先查询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 d where location_id in (1400,1700));
    #或
    select last_name from employees where department_id = any(select department_id from departments d where location_id in (1400,1700));
    #(in与=any not in与<>all 可以起到同样的作用)
    #返回其他工种中比job_id为'IT_PROG'任一工资低的员工的员工号、姓名、Job_id以及salary
    #先查询job_id为'IT_PROG'部门的任一工资
    select salary from employees where job_id='IT_PROG';
    
    select employee_id,last_name,job_id,salary from employees where salary <any (select DISTINCT 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 DISTINCT MAX(salary) from employees where job_id='IT_PROG') and job_id<>'IT_PROG';
    
    #返回其它工种中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id以及salary
    select DISTINCT salary from employees where job_id='IT_PROG';
    
    select employee_id,last_name,job_id,salary from employees where salary <all(select distinct 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 distinct MIN(salary) from employees where job_id='IT_PROG') and job_id<>'IT_PROG';
    
    #3、行子查询(结果集一行多列或多行多列)
    #案例:查询员工编号最小并且工资最高的员工信息
    select * from employees where (employee_id,salary)=(select MIN(employee_id),MAX(salary) from employees);
    
    select MIN(employee_id) from employees;
    select MAX(salary) from employees;
    
    select * from employees where employee_id=(select MIN(employee_id) from employees) and salary=(select MAX(salary) from employees);
    
    
    #二、select后面
    
    /*
    仅支持标量子查询
    */
    #案例:查询每个部门的员工个数
    select d.*,(select COUNT(*) from employees e where e.department_id=d.department_id) 个数 from departments d;
    
    #案例2:查询员工号=102的部门名
    select (select department_name from departments d inner join employees e on d.department_id=e.department_id where e.employee_id=102) 部门名;
    
    #三、from后面
    
    /*
    将子查询结果充当一张表,要求必须起别名
    */
    
    #案例:查询每个部门的平均工资的工资等级
    #查询每个部门的平均工资
    
    select AVG(salary),department_id from employees GROUP BY department_id;
    
    #②连接①的结果集和job_grades表,筛选条件平均工资between lowest_sal and highest_sal;
    select ag_dep.ag,ag_dep.department_id,grade_level from (select AVG(salary) ag,department_id from employees GROUP BY department_id) ag_dep inner join job_grades on ag_dep.ag BETWEEN lowest_sal and highest_sal;
    
    #四、exists后面(相关子查询)
    /*
    语法:
    exists(完整的查询语句)
    结果:
    1或0
    */
    
    select EXISTS(select employee_id from employees where salary=30000);
    
    #案例1:查询有员工的部门名
    select department_name from departments d where EXISTS(select * from employees e where d.department_id=e.department_id);
    
    #案例2:查询下没有女朋友的男神信息
    
    #in
    select bo.* from boys bo where bo.id not in(select boyfriend_id from beauty)
    
    #exists
    select bo.* from boys bo where not EXISTS(select boyfriend_id from beauty where bo.id=b.boyfriend_id);
    
    #测试:
    #1.查询和Zlotkey相同部门的员工姓名和工资
    select last_name,salary from employees e where department_id=(select department_id from employees where last_name='Zlotkey') and last_name<>'Zlotkey';
    
    #2.查询工资比公司平均工资高的员工的员工号,姓名和工资
    select AVG(salary) from employees;
    
    select employee_id,last_name,salary from employees where salary > (select AVG(salary) from employees);
    
    #3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名和工资
    #①查询各部门的平均工资
    select AVG(salary) from employees group by department_id;
    
    #②连接①结果集和employees表,进行筛选
    select e.department_id,employee_id,last_name,salary from employees e inner join(select AVG(salary) ag,department_id from employees group by department_id) ag_dep on e.department_id = ag_dep.department_id where salary > ag_dep.ag;
    
    #4.查询姓名中包含字母u的员工所在相同部门的员工的员工号和姓名
    select DISTINCT department_id from employees where last_name like '%u%';
    
    select employee_id,last_name from employees where department_id in (select DISTINCT department_id from employees where last_name like '%u%');
    
    #5.查询在部门的location_id为1700的部门工作的员工的员工号
    select department_id from departments where location_id=1700;
    
    select employee_id,last_name from employees where department_id in (select department_id from departments where location_id=1700);
    
    #6.查询管理者是King的员工姓名和工资
    select employee_id from employees where last_name='K_ing';
    
    select last_name,salary from employees where manager_id in (select employee_id from employees where last_name='K_ing');
    
    #7.查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列名为姓.名
    select MAX(salary) from employees;
    
    select CONCAT(first_name,last_name) AS "姓.名" from employees where salary = (select MAX(salary) from employees);
    
    分页查询:
    #进阶8:分也查询 √
    /*
    
    应用场景:当要显示的数据,一页显示不全,需要分页提交sql请求
    语法:
    			select 查询列表 [from 表 [join type] join 表2 on 连接条件 where 筛选条件 group by 分组字段 having 分组后的筛选 order by 排序的字段] limit [offset,]size;
    			offset 要显示条目的起始索引(起始索引从0开始)
    			size 要显示的条目个数
    
    特点:
    			①limit语句放在查询语句的最后 执行顺序:先走from 后的表,在走join 进行连接,之后 on 连接条件 ,接下来where 筛选条件,group by 分组字段,having 分组后的筛选,之后进行select 查询列表,然后进行排序 order by 排序字段,最后执行limit子句
    			②公式
    			要显示的页数 page,每页的条目数 size
    			
    			select 查询列表 from 表 limit (page-1)*size,size;
    */
    
    #案例1:查询前五条员工信息
    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 0,10;
    
    
    union联合查询:
    #进阶9:联合查询
    /*
    union 联合 合并:将多条查询语句的结果合并成一个结果
    语法:查询语句1 union 查询语句2 union...
    
    应用场景:
    要查询的结果来自于多个表,且多个表没有直接得连接关系,但查询的信息一致时
    
    特点:
    1、要求多条查询语句的列数是一致的!
    2、要求多条查询语句的查询的每一列的类型和顺序是一致的!
    3、union关键字默认去重,如果使用union all可以包含重复项
    */
    
    #引入的案例:查询部门编号>90或邮箱中包含a的员工信息
    select * from employees where email like '%a%' UNION select * from employees where department_id > 90;
    
    
    展开全文
  • MySql中的DQL语言

    2020-12-11 16:22:08
    一、基础查询 关键字select Select 查询字段 from 表名 查询常量值、表达式、函数 起别名: a) Select表达式as 别名 b) Select 字段 别名 from b表名 c) 别名如果有特殊字符 建议加双引号 去重: 在查询字段前加上...
  • MySQL之DQL语言

    2020-04-02 23:57:14
    MySQL之DQL语言 一、DQL之基础查询 四、MySQL之常见函数 以上就是关于mysql中DQL语言的介绍,如果有不当之处或者遇到什么问题,欢迎在文章下面留言~ 如果你想了解更多关于MySQL的内容,可以查看:MySQL学习目录 转载...
  • DQL语言的学习(一)

    2020-07-28 14:44:25
    DQL语言的学习(一) 一、基础查询 特点: 1、查询的可以是:表中的字段、常量值、表达式、函数 2、查询的结果是个虚拟的表格 1.查询单个字段 SELECT last_name FROM table_name; 2.查询多个字段 SELECT last_name,...
  • DQL语言的学习(六)

    2020-07-30 11:11:13
    DQL语言的学习(六) 六、连接查询 内连接:等值链接、非等值连接、自连接 外连接:左外连接、右外连接、全外连接(不支持) 交叉连接 (一)sql92标准 等值连接
  • DQL语言基础

    2020-09-26 14:54:53
    DQL(Data Query Language 数据查询语言),存在的意义。 查询数据库数据,如SELECT语句。 简单的单表查询或多表的复杂查询和嵌套查询。 是数据库语言中最核心,最重要的语句。 使用频率最高的语句。 ...
  • 1.实现sql语言中DQL语言,实现简单查询、条件查询、排序查询等。 (1)检索grade表当中的所有信息。   (2)检索grade表数学成绩大于100的所有人信息。 String path = request.getContextPath(); String ...
  • DQL语言的历史

    2018-11-16 13:56:24
    没错,哪怕是一门新语言,它也有自身的演变历史。最近,一名叫做JD Curtis的技术工程师介绍了DQL诞生的来龙去脉,它并不是凭空产生的,而是有很久的技术积累和酝酿。 当然以下文章来自于我的半吊子翻译,文章很长,...
  • Sql入门--------DQL语言

    2015-06-26 20:30:59
    一、select语句 select [ *| distinct column1,column2 ] from table [ where ] [ order by ];distinct:去除结果的重复行 and:并且 or:或 注意; (1)order by子句里的字段可以缩写为一个整数,表示字段在关键字...
  • DQL语言查询数据

    2021-02-26 14:15:42
    DQL语言查询数据1.DQL语言1.select 语法2.select语法详解MySQL函数 1.DQL语言 DQL:Data Query Language,数据查询语言 1.select 语法 SELECT [ALL | DISTINCT] { * | table.* | [ table.field1 [ as alias1] [, ...
  • 1 DQL语言 DQL —— Data Query Language数据库查询语言(查) 1.1 基础查询 语法:select 查询列表 from 表名; 特点: 查询列表可以是:表中的字段、常量值、表达式、函数,也可以是多个 查询结果是一个虚拟表 ...
  • select 要查询的内容 【from 表名】

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 25,453
精华内容 10,181
关键字:

dql语言