MYSQL笔记加练习-dql部分 1-5章

/**一、数据库的好处
    #1、持久化数据到本地
    2、结构化查询
二、关系型数据库（SQL）和非关系型数据库（NOSQL）的差异
(1)数据储存方式不同
    1）关系型：表格式，表内用行和列，表间可以彼此关联
    2）非关系型数据：大块组合在一起，像文档、键值对或者图结构
(2)扩展方式不同
    1）关系型：纵向扩展，提高计算机处理能力
    2）非关系型：横向扩展，分布式存储、增加节点
(3)对事物的支持性不同
    1）复杂数据稳定查询--关系型，质，mysql
    2）大数据量的处理---非关系型，量（大规模、高并发）、Redis
*/

# DQL 语言
#进阶1：基础查询
/*
语法：
    select 查询列表 AS 别名
    from 表名
 

修饰查询列表的
1、查询列表可以是：表中的字段(列)、常量值、表达式函数
    1.1 字段---最好用英文单引号，也可以用双引号
    （1）多个字段，用逗号隔开
    （2）所有字段：*，（列顺序不可指定，为默认顺序）
    1.2 常量值
    1.3 表达式
    快捷键：F12 格式化
2、查询的结果是一个虚拟的表格
3、起别名：
    （1）AS 别名
    （2）使用 空格 + 别名
    便于理解，
4、去重
    使用distinct
    (1)在字段名前面加上distinct,但对所有列生效,    
    （2）只能放在第一个查询字段前,但是是作用于所有的列，即除非指定的列完全相同，不然所有的列都会显示

5、+ 号的功能
    5.1 MySQL中只有运算符的功能
    （1）只要其中一方为null，结果肯定为null
    （2）只要其中一方为字符型，将试图将字符型数值转换为字符型
    （2.1）转换成功，继续做加法运算
    （4）转换失败，则将字符型数组转换成为0
        SELECT (100 + "120") ;    #连接字符串，220
        SELECT (100 + "2HH") ;    #连接字符串，102
6、连接多个字段 CONCAT(字段1，字段2，字段3)

7.ISnull（exp1,exp2） 函数
    exp1为null，则返回exp2
*/

USE mysql;    #指定使用的数据库        
USE `myemployees`; # ‘’,着重符号，表示字段，一般可以省略，但如果字段和关键字重合，为了区分需要加上着重符号

SELECT  DISTINCT `first_name` “fn”, #为字段起别名
CONCAT(`email`,job_id,IFNULL(`manager_id`,0)),
100 AS "z1",    
`salary` *100     AS "zz"    #查询字段、表达式、常量值
FROM `employees`;

SELECT (100 + "120") ;    #连接字符串，220
SELECT (100 + "HH") ;    #连接字符串，102

#进阶2：条件查询
/*
语法：
    select 查询列表 AS 别名     # 执行顺序2313，先检查表名，在读取筛选条件，然后查询列表
    from 表名
    where 筛选条件
分类：
    1、筛选
    （1）条件运算符：>  <  =  !=或<>  >=  <=
    （2）逻辑运算符 &&  ||  !
            AND  OR  NOT
    （3）模糊查询
        1）LIKE (包含)
          (1)一般和通配符一起使用
            %，任意多个字符，包含0个 ----不包含null
            _,任意单个字符
        2）BETWEEN AND （区间）
            `salary` BETWEEN 12000 AND 10000；=》`salary` <= N 12000 AND `salary`>= 10000
            （1）包含临界值
            （2）小于左边，大于右边，所以不能调换顺序
        3）IN （判断某字段的值是否属于其中之一，可以混合多字段的值） 
            （1）in 里面的字符类型必须一致或者兼容
            （2）
        4）IS NULL/IS NOT NULL
            （1）判断为null 或者not null
            （2）不要用 = 号判断null
            （3）is 字段值，用法也是错误的
            （4）安全等于 <=>,既可以判断null，也可以判断普通类型，缺点是可读性较差，一般不建议使用
其他：
    （1）escape $ 定义转义字符
    （2）安全等于 <=>,既可以判断null，也可以判断普通类型
*/

SELECT `first_name`
FROM`employees`
WHERE `first_name` LIKE "%L" AND `first_name` LIKE "_$_an%q" ESCAPE '$'; ## ‘\’ 是 SQL默认的转义字符,推荐使用escape 说明自定义转义符

#查询工号176的员工的姓名、部门号、年薪
SELECT `first_name`,
`last_name`,
`department_id`,
salary*12*(1+IFNULL(`commission_pct`,0)) AS 年薪
FROM`employees`
WHERE `job_id` = 176;

#选择工资中有奖金的员工的姓名、部门号、年薪
SELECT 
    `first_name`,
    `department_id`,
    `salary`
FROM
    `employees`
WHERE
    `commission_pct` IS NOT NULL;
    
# 进阶三：排序查询
 # 按需求按顺序显示，可以设置多个规则，前面的规则优先
/*
语法：
    select 查询列表 AS 别名     # 执行顺序2313，先检查表名，在读取筛选条件，然后查询列表
    from 表名
    【where 筛选条件】
    order by 排序列表 【asc/desc】 #可选升序、降序
特点：
    （1）desc 降序，asc 升序
    （2）可以对字段、表达式、函数排序
    （3）可以设置多个规则，前面的规则优先
    （4）一般放在查询子句的最后（除了limit）
    
其他：
    （1）LENGTH()，函数，获取字符长度
    (2)注意用英文字符
*/

#按工资排序（字段）
SELECT 
    `first_name`,
    `department_id`,
    `salary`
FROM
    `employees`
WHERE
    `commission_pct` IS NOT NULL
ORDER BY salary DESC    ; #降序排序

#按年工资排序（表达式、别名）
SELECT 
    `first_name`,
    `department_id`,
    `salary`*12 年薪
FROM
    `employees`
WHERE
    `commission_pct` IS NOT NULL
ORDER BY 年薪 DESC    ; #降序排序

#先按名字升序，再按年工资降序排序
SELECT 
    `first_name`,
    `department_id`,
    `salary`*12 年薪
FROM
    `employees`
WHERE
    `commission_pct` IS NOT NULL
ORDER BY LENGTH(`first_name`),年薪 DESC; #升序排序

# 进阶三：常见函数
 # 类似于Java的方法，对外暴露方法名
 /*
语法：
    select 函数名(实参列表)     # 执行顺序2313，先检查表名，在读取筛选条件，然后查询列表
    【from 表名】
    【where 筛选条件】
    order by 排序列表 【asc/desc】 #可选升序、降序
特点：
    （1）函数名
    （2）干什么
分类：
    （1）单行函数
        concat() 连接字符串、length()
    （2）分组函数
具体：
    单行函数：
    一、字符函数
        (1)length(字符型)，返回字符长度
            select length('张z')  //3+1=4个字符
        (2)concat(c,c1) 连接字符串
        (3)upper(),lower() 转大写、小写
        （4）substr()
            substr("sss",7),截取从第7个字符开始的后面字符
            substr("sss",7，1),截取从第7个字符开始的指定长度 1 的字符（非字节长度）---涉及方法的重载
        （5）INSTR 查找字符出现的起始索引，找不到返回0
            select instr('sasa','a') //2
        （6）trim() --去掉前后空格
            trim('aa' from "aaaaab") //ab
         (7) lpad 左填充至指定长度--无则填充，有则截断
              rpad 又填充至指定长度--无则填充，有则截断
              SELECT LPAD("aaa",4,"*");
          （8）replace()
      
其他：
    （1）SQL的索引从1开始
    （2）concat()
    
    三 日期函数
        (1)STR_TO_DATE 将字符串转换成为日期
            SELECT STR_TO_DATE('2070年8月5日','%y年%m月%d日'); //null 告诉计算机输入的字符串格式,格式无法识别输出null
            SELECT STR_TO_DATE('2070.8.6','%Y.%m.%d'); // 2070-8-6
        (2)DATE _FORMAT 将日期转换成为字符串
            SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')；//21年08月02日 告诉计算机输出的日期格式

    四 其他函数
        VERSINON();
        DATABASE()
        USER();当前函数
    
    五、流程控制函数
        1、if()
            if(10>5,'大'，'小')
        2、case()
            使用1：switch case 的效果(判断等值)
            case 要判断的字段或表达式
            when 常量1 then 要显示的值1或语句1
            when 常量2 then 要显示的值2或语句2
            。。。
            else 要显示的值n或语句n
            end
            
            使用2：类似于多重if（判断区间）
            case
            when 条件1 then 要显示的值1或语句1
            when 条件1 then 要显示的值1或语句1
            。。。
            else 要显示的值n或语句n
            end

*/
            
SELECT INSTR('sasa','a') ;
SELECT LPAD("aaa",4,"*");
SELECT REPLACE("abababc","e","cdcd");
SELECT STR_TO_DATE('20.8.6','%Y.%m.%d');
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');

#查询员工工资
/*
部门号=30，工资为1.1倍
部门号=40，工资为1.3倍
其他部门，工资为原始倍
*/
SELECT `department_id`,`salary` 原始工资,
CASE `department_id`
WHEN 80 THEN `salary`*1.1
WHEN 50 THEN `salary`*1.3
ELSE salary 
END
AS 实际工资
FROM `employees`;


/*#五 分组函数
    #功能：用作统计使用，又称为聚合函数或统计函数或组函数
    
    分类：
    sum（） 求和
    avg（） 平均值
    max（） 最大值
    min（） 最小值
    count（） 计算个数
    
    1、简单使用
        sum(salary) //分组函数与单行函数的区别，只有一个值，而非一列值
    2、特点
        （1）参数类型支持：
        sum()\avg() ---处理 数值型，忽略null值
        max()\min() ---处理任何类型，忽略null值
        count() ----处理任何类型，忽略null值
        （2）以上分组函数都忽略null值
        （3）可以和distinct 关键字配合使用
            sum(distinct salary)//去重之后求和
        （4）count()函数的详细介绍
            SELECT COUNT(*) FROM employees; #统计行数,107
            SELECT COUNT(1) FROM employees; #统计1的行数,107 --->  统计行数
            效率：INNODB存储引擎下，2者的效率差不多，比count（字段）高一些，一般count（*）用的多
        （6）和分组查询同一查询的字段有限制
            和分组函数同一查询的字段要求是group by 以后的字段
其他函数：
    DATEDIFF(MAXDATE,MINDATE) 获得日期的差值
*/
SELECT COUNT(*) FROM employees; #统计行数,107
SELECT COUNT(1) FROM employees; #统计1的行数,107 --->  统计行数
SELECT COUNT(*),`first_name` FROM employees; #不正确
SELECT CONCAT(COUNT(*),`first_name`) AS ljie FROM employees;

/*进阶五 分组查询
    GROUP BY 子句：将表中的数据分成若干个组
语法：
语法：
    select 分组函数，列（要求是group by 后面的出现字段）    # 此时查询列表是有条件的
    【where 筛选条件】 //where一定是放在from后面
    group by 
    order by 排序列表 【asc/desc】 #可选升序、降序

*/
SELECT `manager_id`
FROM `employees`
WHERE `last_name` = 'K_ing';

#不分组，仅仅1行，但和分组函数配合查询的字段不符合要求，没有意义
SELECT `last_name`,MAX(`manager_id`) 
FROM `employees`;

#按多个字段分组
#查询相同领导相同职级的员工的薪水的最大值
SELECT `job_id`,MAX(`salary`),`manager_id`
FROM `employees`
GROUP BY `last_name`; 

/*
select 查询
from 来源
where 分组前筛选
group by 分组
having 分组后筛选
order by

 

*/