MySQL6天笔记——day02

Dear 丶 2023-02-22 10:57 21阅读 0赞

### 文章目录 ###

*  MySQL数据库
 *   *  DQL语言
     *   *  基础查询
         *  条件查询
         *  排序查询
         *  常见函数
         *   *  单行函数
             *  分组函数
         *  分组查询
     *  欢迎斧正！！！

# MySQL数据库 #

> 全部SQL源文件链接：https://pan.baidu.com/s/1wc51qkVetSRybFzcIYGBIg  
> 提取码：3wpt

## DQL语言 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]

### 基础查询 ###

*  语法：select 查询列表 from 表名;
 *  类似于System.out.println(打印东西);
 *  特点：
    
     *  1.查询列表可以是：表中的字段、常量值、表达式、函数
     *  2.查询的结果可以是一个虚拟表格;
 *  案例

USE myemployees;
    
    #1.查询表中的单个字段
    SELECT last_name FROM employees;
    
    #2.查询表中多个字段
    SELECT last_name,salary,email FROM employees;
    
    #3.查询表中的所有字段
    SELECT * FROM employees;
    
    #4.查询常量
    # select 常量值;
    # 注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
    SELECT 100;
    SELECT 'join';
    
    #5.查询函数
    #select 函数名(实参列表);
    SELECT VERSION();
    
    #6.查询表达式 
    SELECT 100%98;
    
    #7.起别名
    /* 1.便于理解 2.如果要查询的字段有重名的情况,使用别名区分 */
    #方式一:使用AS
    SELECT 100%98 AS 结果;
    SELECT last_name AS 姓,first_name AS 名 FROM employees;
    
    #方式二:使用空格
    SELECT last_name 姓,first_name 名 FROM employees;
    
    #案例:查询salary,结果显示 out put
    SELECT salary AS "out put" FROM employees;
    
    #8.去重
    # select distinct 字段名 from 表名;
    #案例:查询员工表中涉及的所有部门编号
    SELECT DISTINCT department_id FROM employees;
    
    #9.+号的作用
    #案例:查询员工的名和姓,并显示为姓名
    /* java中的+号: 1.运算符:两个操作数都为数据型 2.连接符:只要有一个操作数为字符串 mysql中的+号: 只能作为运算符 select 100+90; 两个操作数都为数值型,做加法运算 select '123+90';其中一方为字符型,试图将字符型数值转换为数值型 如果转换成功,则继续做加法运算 select 'john'+90; 如果转换失败,则将字符型数值转换成0 select null+0; 只要其中一方为null,则结果肯定为null. */
    SELECT last_name+first_name AS 姓名 FROM employees; 
    
    #10.【补充】concat函数 
    /* 功能：拼接字符 select concat(字符1，字符2，字符3,...); */
    SELECT CONCAT('a','b','c') AS 结果 FROM employees;
    
    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 筛选条件;
 *  分类
    
        一、按条件表达式筛选
        	条件运算符:> < = != <> >= <= <=>安全等于
        二、按逻辑表达式筛选
        	逻辑运算符:&& || |
        	and or not
        	
        	&& 和 and:两个条件都为true，结果为true，反之为false
        	|| 和 or:只要有一个条件为true，结果为true，反之为false
        	! 或 not:如果连接的条件本身为false，结果为true，反之为false	
        	
        三、模糊查询
        	like:一般搭配通配符使用，可以判断字符型或数值型
        	通配符：%任意多个字符，_任意单个字符
        	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:查询工资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 department_id <90 OR department_id>110 OR salary>15000;
    
    
    #三、模糊查询
    
    #1.like
    
    #案例1:查询员工名中包含字符a的员工信息
    SELECT * FROM employees WHERE last_name LIKE '%a%';
    
    #案例2:查询员工名中第三个字符为b，第五个字符为a的员工名和工资
    SELECT last_name,salary FROM employees WHERE last_name LIKE '__b_a%';
    
    #案例3:查询员工名种第二个字符为_的员工名
    SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
    
    #2.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;
    
    /*注意事项： 1.提高语句简洁度 2.包含临界值 3.两个临界值不能调换顺序 */
    
    #3.in
    /* 含义:判断某字段的值是否属于in列表中的某一项 特点: 1.使用in提高语句简洁度 2.in列表的值类型必须一致或兼容 */
    #案例1:查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
    
    SELECT last_name,job_id FROM employees WHERE job_id='IT_PROG' OR job_id='AD_PRES' OR job_id='AD_VP';
    
    SELECT last_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_PRES','AD_VP');
    
    #4.is null
    /* =或<>不能用于判断null值 is null 或 is not null 可以判断null值 */
    #案例1:查询没有奖金的员工名和奖金率
    
    SELECT last_name,commission_pct FROM employees WHERE commission_pct IS 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 last_name,commission_pct FROM employees WHERE salary <=> 12000;
    
    #is null PK <=>
    # 普通类型的数值 null值 可读性
    # is null × √ √
    # <=> √ √ ×

### 排序查询 ###

*  引入：select \* from employees;
 *  语法：select 查询列表 from 表 【where 筛选条件】 order by
 *  特点：
    
     *  1.asc代表的是升序，desc代表降序，不写默认为升序
     *  2.order by子句中可以支持单个字段、多个字段、表达式、函数、别名
     *  3.order by子句一般是放在查询语句的最后面,limit子句除外
 *  案例

#案例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 ASC;
    
    #案例3:按年薪的高低显示员工的信息和年薪【按表达式排序】
    SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪 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 salary*12*(1+IFNULL(commission_pct,0)) 年薪 DESC;
    
    #案例5:按姓名的长度显示员工的姓名和工资【按函数排序】
    SELECT LENGTH(last_name) 字节长度,last_name,salary
    FROM employees
    ORDER BY LENGTH(last_name) DESC;
    
    #案例6:查询员工共信息,要求按工资排序，再按员工编号排序【按多个字段排序】
    SELECT * FROM employees
    ORDER BY salary ASC,employee_id DESC;

### 常见函数 ###

*  概念：类似于Java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
 *  优点：1.隐藏了实现细节 2.提高了代码的重用性
 *  语法：select 函数名(实参列表) 【from 表】;
 *  特点：
    
     *  1.叫什么(函数名)
     *  2.干什么(函数功能)
 *  分类:
    
     *  1.单行函数，如concat、length、ifnull等
     *  2.分组函数，做统计使用

#### 单行函数 ####

*  单行函数分类：字符函数、数学函数、日期函数、其他函数、流程控制函数

> 字符函数具体案例：

#一.字符函数
    #1.length 获取参数值的字节值
    SELECT LENGTH('subei');
    SELECT LENGTH('鬼谷子qwe');
    
    SHOW VARIABLES LIKE '%char%';
    
    #2.concat 拼接字符串
    SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
    
    #3.upper:变大写、lower：变小写
    
    SELECT UPPER('ton');
    SELECT LOWER('ton');
    
    #示例：将姓变大写，名变小写，然后拼接
    SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;
    
    
    #4.substr、substring
    #注意:索引从1开始
    
    #截取从指定所有处后面的所以字符
    SELECT SUBSTR('吴刚伐桂在天上',4) out_put;
    
    #截取从指定索引处指定字符长度的字符
    SELECT SUBSTR('吴刚伐桂在天上',1,2) out_put;
    
    #案例:姓名中首字符大写,其他字符小写，然后用_拼接,显示出来
    SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) out_put FROM employees;
    
    #5.instr:获取子串第一次出现的索引,找不到返回0
    SELECT INSTR('MySQL技术进阶','技术') AS out_put;
    
    #6.trim:去前后空格
    
    SELECT LENGTH(TRIM(' 霍山 ')) AS out_put;
    
    SELECT TRIM('+' FROM '++++李刚+++刘邦+++') AS out_put;
    
    #7.lpad:用指定的字符实现左填充指定长度
    SELECT LPAD('梅林',8,'+') AS out_put;
    
    #8.rpad:用指定的字符实现右填充指定长度
    SELECT RPAD('梅林',5,'&') AS out_put;
    
    #9.replace:替换
    SELECT REPLACE('莉莉伊万斯的青梅竹马是詹姆','詹姆','斯内普') AS out_put;

> 数学函数具体案例：

#1.round:四舍五入
    SELECT ROUND(1.45);
    SELECT ROUND(1.567,2);
    
    #2.ceil:向上取整,返回>=该参数的最小整数
    SELECT CEIL(1.005);
    SELECT CEIL(-1.002);
    
    #3.floor:向下取整,返回<=该参数的最大整数
    SELECT FLOOR(-9.99);
    
    #4.truncate:截断
    SELECT TRUNCATE(1.65,1);
    
    #5.mod:取余
    SELECT MOD(10,3);
    
    #6.rand:获取随机数，返回0-1之间的小数
    SELECT RAND();

> 日期函数具体案例：

#1.now:返回当前系统时间+日期
    SELECT NOW();
    
    #2.year:返回年
    SELECT YEAR(NOW());
    SELECT YEAR(hiredate) 年 FROM employees;
    
    #3.month:返回月
    #MONTHNAME:以英文形式返回月
    SELECT MONTH(NOW());
    SELECT MONTHNAME(NOW());
    
    #4.day:返回日
    #DATEDIFF:返回两个日期相差的天数
    SELECT DAY(NOW());
    SELECT DATEDIFF('2020/06/30','2020/06/21');
    
    #5.str_to_date:将字符通过指定格式转换成日期
    SELECT STR_TO_DATE('2020-5-13','%Y-%c-%d') AS out_put;
    
    #6.date_format:将日期转换成字符
    SELECT DATE_FORMAT('2020/6/6','%Y年%m月%d日') AS out_put;
    SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
    
    #7.curdate:返回当前日期
    SELECT CURDATE();
    
    #8.curtime:返回当前时间
    SELECT CURTIME();

> 其他函数具体案例：

#version 当前数据库服务器的版本
    SELECT VERSION();
    
    #database 当前打开的数据库
    SELECT DATABASE();
    
    #user当前用户
    SELECT USER();
    
    #password('字符')：返回该字符的密码形式
    SELECT PASSWORD('a');
    
    #md5('字符'):返回该字符的md5加密形式
    SELECT MD5('a');

> 流程控制函数具体案例：

#1.if函数: if else效果
    
    SELECT IF(10<5,'大','小');
    
    SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金！！！','有奖金!!!') 备注 FROM employees;
    
    #2.case函数
    #使用一:switch case 的效果
    /* java中 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 salary,
    CASE
    WHEN salary>20000 THEN 'A'
    WHEN salary>15000 THEN 'B'
    WHEN salary>10000 THEN 'C'
    ELSE 'D'
    END AS 工资等级
    FROM employees;

#### 分组函数 ####

*  功能：用作统计使用，又称为聚合函数或统计函数或组函数
 *  分类：sum 求和、avg 平均值、max 最大值、min最小值count 计算个数
 *  特点:
    
     *  1.sum和avg一般用于处理数值型  
        max、min、count可以处理任何数据类型
     *  2.以上分组函数都忽略null
     *  3.都可以搭配distinct使用，实现去重的统计  
        select sum(distinct 字段) from 表;
     *  4.count函数  
        count(字段)：统计该字段非空值的个数  
        count(\*):统计结果集的行数
     *  5.和分组函数一同查询的字段，要求是group by后出现的字段

#1.简单使用
    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;
    
    SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
    FROM employees;
    
    #2.参数支持哪些数据类型
    
    SELECT SUM(last_name),AVG(last_name) FROM employees;
    SELECT SUM(hiredate),AVG(hiredate) 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 commission_pct FROM employees;
    
    SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,AVG(commission_pct)/107 FROM employees;
    
    SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
    
    SELECT COUNT(commission_pct) FROM employees;
    
    #4.和distinct搭配
    
    SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
    
    SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;
    
    #5.count函数详解
    
    SELECT COUNT(salary) FROM employees;
    SELECT COUNT(*) FROM employees;
    SELECT COUNT(1) FROM employees;
    /* 效率上： MyISAM存储引擎，count(*)最高 InnoDB存储引擎，count(*)和count(1)效率>count(字段) */
    
    #6.和分组函数一同查询的字段有限制
    
    SELECT AVG(salary),employee_id FROM employees;

### 分组查询 ###

*  语法:
    
        select 分组函数,分组后的字段
        from 表
        【where 筛选条件】
        group by 分组的字段
        【having 分组后的筛选】
        【order by 排序列表】
    
     *  注意:查询列表必须特殊,要求是分组函数和group by后出现的字段
 *  特点:
    
     *  1.分组查询中的筛选条件分为两类
        
            使用关键字	筛选的表	位置
            分组前筛选	where		原始表		group by的前面
            分组后筛选	having		分组后的结果	group by的后面
            1.分组函数做条件肯定是放在having子句中
            2.能用分组前筛选的，就优先考虑使用分组前筛选
     *  2.group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(使用较少)
     *  3.也可以添加排序(排序放在整个分组查询的最后)
 *  案例

#引入:查询每个部门的平均工资
    SELECT AVG(salary) FROM employees;
    
    #案例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
    #1.查询每个部门的员工个数
    SELECT COUNT(*),department_id FROM employees
    GROUP BY department_id;
    
    #2.根据1的结果进行筛选，查询哪个部门的员工个数大于2
    SELECT COUNT(*),department_id FROM employees
    GROUP BY department_id HAVING COUNT(*)>2;
    
    
    #案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 
    #1.查询每个工种有奖金的员工的最高工资 
    SELECT MAX(salary),job_id FROM employees 
    WHERE commission_pct IS NOT NULL GROUP BY job_id; 
    
    #2.根据结果继续筛选，最高工资>12000 
    
    SELECT MAX(salary), job_id FROM employees 
    WHERE commission_pct IS NOT NULL GROUP BY job_id 
    HAVING MAX(salary)>12000; 
    
    #按表达式或函数分组
    
    #案例:按员工姓名的长度分组,查询每一组的员工个数,筛选员工个数>5
    
    #1.查询每个长度的员工个数 
    SELECT COUNT(*),LENGTH(last_name) len_name 
    FROM employees GROUP BY LENGTH(last_name); 
    
    #2.添加筛选条件
    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 GROUP BY department_id,job_id
    ORDER BY AVG(salary) DESC;

## 欢迎斧正！！！ ##

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200703100542907.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzQ2MTUzOTQ5,size_16,color_FFFFFF,t_70#pic_center