MySQL_基础_DQL数据查询语言

查询语法

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table
    [[connect_type] JION table2 ON connect_condition]
    [WHERE condition]
    [GROUP BY columns]
    [HAVING condition]
    [ORDER BY columns (ASC | DESC)]
    [LIMIT { [offset,] row_count | row_count OFFSET offset }];

语句顺序：
    查询语句书写顺序：SELECT –> FROM -> JION -> ON -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
    查询语句执行顺序：FROM -> JION -> ON -> WHERE -> GROUP BY -> HAVING -> SELECT –> ORDER BY -> LIMIT

基本查询

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table;

特点：
    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 last_name AS 姓,first_name AS 名 FROM employees;
    SELECT last_name 姓,first_name 名 FROM employees;

    8、消除重复数据，DISTINCT
    SELECT DISTINCT department_id FROM employees;

    9、+号的作用
    SELECT 100+90;            # 两个操作数都为数值型，则做加法运算
    SELECT 'john'+90;         # 一方为字符型，试图转换成数值型，转换成功则做加法运算，转换失败则转换成0再运算
    SELECT null+10;           # 一方为null，结果为null

    10、字符串连接
    SELECT CONCAT('a','b','c') AS 结果;

条件查询

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table
    [WHERE condition];

分类：
    1、条件运算符
        > < = != <> >= <=         # 判断普通值
        <=>                       # 安全等于，即可以判断null值，又可以判断普通值，与is null比较可读较低

    2、逻辑运算符
        &&或AND                   # 逻辑并，两个条件都为true，结果为true，反之为false
        ||或OR                    # 逻辑或，只要有一个条件为true，结果为true，反之为false
        !或NOT                    # 逻辑否，如果连接的条件本身为false，结果为true，反之为false

    3、模糊查询
        LIKE                      # 模糊查询，% 任意多个字符，_ 任意单个字符
        BETWEEN AND               # 在两个值之间(包含边界)
        IN                        # 等于值列表中的一个
        IS (NOT) NULL             # 判断null值

案例：
    1、按条件表达式筛选
        案例1：查询工资>12000的员工信息
        SELECT * FROM employees WHERE salary>12000;
            
        案例2：查询部门编号不等于90号的员工名和部门编号
        SELECT last_name,department_id FROM employees WHERE department_id<>90;

        案例3：查询没有奖金的员工名和奖金率
        SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;

        案例4：查询工资为12000的员工信息
        SELECT last_name,salary FROM employees WHERE salary <=> 12000;

    2、按逻辑表达式筛选
        案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
        SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

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

    3、模糊查询
        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 '$';

        BETWEEN AND：
            案例1：查询员工编号在100到120之间的员工信息
            SELECT * FROM employees WHERE employee_id >= 100 AND employee_id<=120;
            SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

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

        IS (NOT) NULL：
            案例1：查询没有奖金的员工名和奖金率
            SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

            案例2：查询有奖金的员工名和奖金率
            SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

排序查询

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table
    [WHERE condition]
    [ORDER BY columns (ASC | DESC)];

特点：
    1、ASC升序（默认，可省略），DESC降序
    2、ORDER BYy子句可以支持 单个字段、别名、表达式、函数、多个字段
    3、ORDER BY子句在查询语句的最后面，除了LIMIT子句

案例：
    1、按单个字段排序
        案例：查询员工信息，要求先按工资降序
        SELECT * FROM employees ORDER BY salary DESC;

    2、按多个字段排序
        案例：查询员工信息，要求先按工资降序，再按employee_id升序
        SELECT * FROM employees ORDER BY salary DESC,employee_id ASC;

    3、添加筛选条件再排序
        案例：查询部门编号>=90的员工信息，并按员工编号降序
        SELECT * FROM employees WHERE department_id>=90 ORDER BY employee_id DESC;

    4、按表达式排序
        案例：查询员工信息 按年薪降序
        SELECT *,salary*12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

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

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

函数查询

常用函数：
    字符函数：LENGTH、CONCAT、SUBSTR、REPLACE、INSTR、TRIM、LPAD、RPAD、UPPER、LOWER...
    数字函数：ROUND、CEIL、FLOOR、ABS、POWER、TRUNCATE、MOD..
    日期函数：NOW、CURDATE、CURTIME、YEAR、MONTH、DAY、HOUR、MINUTE、SECOND、STR_TO_DATE、DATE_FORMAT..
    流程控制函数：IFNULL、NULLIF、IF、CASE..
    其他函数：VERSION、DATABASE、USER..
    聚合函数：SUM、AVG、MIN、MAX、COUNT..

案例：
    1、字符函数
        SELECT LENGTH('hello');                         # 取字符串长度，结果：5
        SELECT CONCAT('Hello','world');                 # 连接字符串，结果：Helloworld
        SELECT SUBSTR('abcdefg',3,2);                   # 截取字符串，结果：cd
        SELECT REPLACE('jack and jue','j','bl');        # 替换字符串，结果：black and blue
        SELECT INSTR('worldworld','r');                 # 查找子串第一次索引，结果：3
        SELECT TRIM('*' FROM '***hello***world***');    # 去除前后字符串，结果：hello***world
        SELECT LPAD('abc',10,'*');                      # 左填充，结果：*******abc
        SELECT RPAD('abc',10,'*');                      # 右填充，结果：abc*******
        SELECT UPPER('sun');                            # 转换为大写，结果：SUN
        SELECT LOWER('FUN');                            # 转换为小写，结果：fun

    2、数学函数：
        SELECT ROUND(100.256,2);                        # 四舍五入，结果：100.26
        SELECT CEIL(44.778);                            # 向上取整，结果：45
        SELECT FLOOR(100.2);                            # 向下取整，结果：100
        SELECT ABS(-15);                                # 取绝对值，结果：15
        SELECT POWER(4,2);                              # m的n次幂，结果：16
        SELECT TRUNCATE(100.256,2);                     # 截断，结果：100.25
        SELECT MOD(10,3);                               # 取余数，结果：1
        SELECT RAND();                                  # 0-1之间随机数，结果：0.341342691650002

    3、日期函数：
        SELECT NOW();                                   # 返回当前系统日期时间
        SELECT CURDATE();                               # 返回当前系统日期
        SELECT CURTIME();                               # 返回当前系统时间
        SELECT YEAR(NOW());                             # 返回当前系统的年
        SELECT MONTH(NOW());                            # 返回当前系统的月
        SELECT DAY(NOW());                              # 返回当前系统的日
        SELECT HOUR(NOW());                             # 返回当前系统的时
        SELECT MINUTE(NOW());                           # 返回当前系统的分
        SELECT SECOND(NOW());                           # 返回当前系统的秒
        SELECT STR_TO_DATE('1980-01-01','%Y-%c-%d');    # 将字符转换成日期
        SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');        # 将日期转换成字符
        SELECT DATEDIFF('2020-01-01', NOW());           # 返回两日期相差天数

    4、流程控制函数                        
        SELECT IFNULL(NULL,123);                                              # 如果expr1不是NULL,返回expr1,否则返回expr2
        SELECT NULLIF(1,2);                                                   # 如果expr1=expr2，返回NULL，否则返回expr1
        SELECT IF(1<2,'yes','no');                                            # 如果expr1是真, 返回expr2, 否则返回expr3
        SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;    # 枚举这个字段所有可能的值
        SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;                    # 判断字段范围

    5、其他函数：
        SELECT VERSION();                               # 返回MySql版本
        SELECT DATABASE();                              # 返回当前数据库
        SELECT USER();                                  # 返回当前用户
        SELECT PASSWORD('123456');                      # 返回该字符的密码形式
        SELECT MD5('123456');                           # 返回该字符的MD5加密形式
        SELECT UUID();                                  # 返回UUID

    6、聚合函数
        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 [column,] group function(column), ...
    FROM table
    [WHERE condition]
    [GROUP BY columns]
    [HAVING condition]
    [ORDER BY columns (ASC | DESC)];

案例：
    1、简单分组
        案例：查询每个工种的员工平均工资
        SELECT AVG(salary),job_id FROM employees GROUP BY job_id;

    2、分组前筛选
        案例：查询邮箱中包含a字符的 每个部门的最高工资
        SELECT MAX(salary),department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

    3、分组后筛选
        案例：查询哪个部门的员工个数>5
        SELECT COUNT(*),department_id FROM employees GROUP BY department_id HAVING COUNT(*)>5;

    4、多个字段分组
        案例：查询每个工种每个部门的最低工资,并按最低工资降序
        SELECT MIN(salary),job_id,department_id FROM employees GROUP BY department_id,job_id ORDER BY MIN(salary) DESC;

分页查询

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table
    [WHERE condition]
    [GROUP BY columns]
    [HAVING condition]
    [ORDER BY columns (ASC | DESC)]
    [LIMIT { [offset,] row_count | row_count OFFSET offset }];

注意：
    offset代表起始索引，默认0

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

    2、查询第11条——第25条
    SELECT * FROM  employees LIMIT 10,15;
    SELECT * FROM  employees LIMIT 15 OFFSET 10;

连接查询

语法：
    SELECT * | { [DISTINCT] column | expression [alias], ... }
    FROM table
    [[connect_type] JION table2 ON connect_condition]
    [WHERE condition]
    [GROUP BY columns]
    [HAVING condition]
    [ORDER BY columns (ASC | DESC)]
    [LIMIT { [offset,] row_count | row_count OFFSET offset }];

含义：
    又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：
    表1 有m行，表2 有n行，结果=m*n行

    发生原因：没有有效的连接条件
    如何避免：添加有效的连接条件

分类：
    按年代分类：
        sql92标准：仅仅支持内连接
        sql99标准：支持内连接+外连接（左外和右外）+交叉连接
    
    按功能分类：
        内连接：[INNER] JOIN ON 
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接：LEFT [OUTER] JOIN
            右外连接：RIGHT [OUTER] JOIN
            全外连接：FULL [OUTER] JOIN（MySQL不支持）
        交叉连接：CROSS JOIN（笛卡尔集）

说明：
    内连接：
        特点：
            1、内连接结果 = 多表的交集部分
            2、n表连接至少需要n-1个连接条件
            3、多表的顺序没有要求
            4、inner可以省略
            5、筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
            6、inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
        
    外连接：
        特点：
            1、外连接结果 = 内连接结果+主表中有而从表没有的记录
            2、left join左边的是主表，right join右边的是主表，full join两边都是主表
            3、左外和右外交换两个表的顺序，可以实现同样的效果 
            4、一般用于查询除了交集部分的剩余的不匹配的行
            5、全外连接结果 = 内连接的结果+表1中有但表2没有的+表2中有但表1没有的

案例：
    内连接：
        1、等值连接
            案例1.查询员工名、部门名
            SQL92语法：SELECT last_name,department_name FROM departments d,employees e WHERE e.`department_id`=d.`department_id`;
            SQL99语法：SELECT last_name,department_name FROM departments d JOIN  employees e ON e.`department_id`=d.`department_id`;

            案例2：查询员工名、部门名和所在的城市
            SQL92语法：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`;
            SQL99语法：SELECT last_name,department_name,city FROM employees e 
                        INNER JOIN departments d ON e.`department_id`=d.`department_id`
                        INNER JOIN locations l ON d.`location_id`=l.`location_id`;

        2、非等值连接
            案例：查询员工的工资和工资级别
            SQL92语法：SELECT salary,grade_level FROM employees e,job_grades g WHERE e.`salary` BETWEEN g.`lowest_sal` 
                        AND g.`highest_sal`;
            SQL99语法：SELECT salary,grade_level FROM employees e JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` 
                        AND g.`highest_sal`;

        3、自连接
            案例：查询员工名和上级的名称
            SQL92语法：SELECT e.last_name,m.last_name FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;
            SQL99语法：SELECT e.last_name,m.last_name FROM employees e JOIN employees m ON e.`manager_id`=m.`employee_id`;
    
    外连接：
        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;
        
        2、交叉连接（笛卡尔集）
        案例：查询两表数据
        笛卡尔集：SELECT d.*,e.* FROM departments d,employees e;
        交叉连接：SELECT d.*,e.* FROM departments d CROSS JOIN employees e;

 

子查询

含义：
    嵌套在其他语句内部的select语句称为子查询或内查询
    外部语句可以是insert、update、dalete、select等，一般select作为外部语句较多
    外部如果为select语句称为主查询或外查询

分类：
    按子查询出现的位置：
        SELECT后面：支持标量子查询
        FROM后面：支持表子查询
        WHERE或HAVING后面：支持标量子查询、列子查询、行子查询
        EXISTS后面：支持标量子查询、列子查询、行子查询、表子查询
            
    按结果集的行列数不同：
        标量子查询（结果集只有一行一列）
        列子查询（结果集只有一列多行）
        行子查询（结果集有一行多列）
        表子查询（结果集一般为多行多列）

特点：
    1、子查询放在小括号内
    2、子查询一般放在条件的右侧
    3、标量子查询，一般搭配着单行操作符使用> < >= <= = <>
    4、列子查询，一般搭配着多行操作符使用 (NOT) IN、ANY|SOME、ALL
        (NOT) IN：等于列表中的任意一个
        ANY|SOME：和子查询返回的某一个值比较
        ALL：和子查询返回的所有值比较
    5、子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

案例：
    1、标量子查询
        案例1：查询公司工资最少的员工的last_name,job_id和salary
        SELECT last_name,job_id,salary FROM employees WHERE salary=(
            SELECT MIN(salary) FROM employees
        );
        
        案例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
        );

    2、列子查询
        案例1：查询location_id是1400或1700的部门中的所有员工姓名
        SELECT last_name FROM employees WHERE department_id IN(
            SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700)
        );
        # 或
        SELECT last_name FROM employees WHERE department_id =ANY(
            SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700)
        );
        
        案例2：查询location_id不是1400或1700的部门中的所有员工姓名
        SELECT last_name FROM employees WHERE department_id NOT IN(
            SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700)
        );
        # 或
        SELECT last_name FROM employees WHERE department_id <>ALL(
            SELECT DISTINCT department_id FROM departments WHERE location_id IN(1400,1700)
        );

        案例3：查询其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
        SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY(
            SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
        ) AND job_id<>'IT_PROG';
        # 或
        SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<(
            SELECT MAX(salary) FROM employees WHERE job_id = 'IT_PROG'
        ) AND job_id<>'IT_PROG';

        案例4：查询其它工种中比job_id为‘IT_PROG’工种所有工资低的员工的员工号、姓名、job_id 以及salary
        SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ALL(
            SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
        ) AND job_id<>'IT_PROG';
        # 或
        SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<(
            SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG'
        ) AND job_id<>'IT_PROG';

    3、行子查询
        案例：查询员工编号最小并且工资最高的员工信息
        SELECT * FROM employees WHERE employee_id=(
            SELECT MIN(employee_id) FROM employees
        )AND salary=(
            SELECT MAX(salary) FROM employees);
        # 或
        SELECT * FROM employees WHERE (employee_id,salary)=(
            SELECT MIN(employee_id),MAX(salary) FROM employees
        );
    
    4、表子查询
        案例：查询有员工的部门名
        SELECT department_name FROM departments d WHERE EXISTS(
            SELECT * FROM employees e WHERE d.`department_id`=e.`department_id`
        );

联合查询

语法：
    SELECT1 ...
    UNION [ALL | DISTINCT]
    SELECT2 ...
    ...

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

特点：
    1、要求多条查询语句的查询列数是一致的！
    2、要求多条查询语句的查询的每一列的类型和顺序最好一致
    3、union关键字默认去重，如果使用union all 可以包含重复项
    4、多个select语句联合查询后的结果字段，都是以第一个select语句的字段为准

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

    # 或
    SELECT * FROM employees  WHERE email LIKE '%a%'
    UNION
    SELECT * FROM employees  WHERE department_id>90;