美文网首页
5/04day46_MySQL函数&事务

5/04day46_MySQL函数&事务

作者: 蹦蹦跶跶的起床啊 | 来源:发表于2020-05-15 21:49 被阅读0次

    回顾

    1. 多表查询
        笛卡尔积
            二张表记录的乘积,又称为交叉连接
        内连接
            查询二张表的交集
            select ... from 左表 [inner] join 右表 on 连接条件;
        左外连接
            查询左表的全部,再去匹配右右表,有显示数据,没有显示null
            select ... from 左表 left [outer] join 右表 on 连接条件;
        子查询
            一条select执行的结果,作为另一条select语法的一部分
            查询结果为单列,在where后面作为条件使用
            查询结果为多列,一般在from后面作为虚拟表记录    
        自关联
            在某些表中,数据也分为一对多关系(例如:员工、菜单)
            
    2. 多表案例
        多表查询会产生笛卡尔积
        连接条件=表个数-1
        步骤
            1)确定那些表?
            2)确定连接条件?
            3)确定显示字段?
            4)确定业务条件?
    
    3. 用户权限DCL
        进入公司有专门的DBA,为每一名员工分配一个账户(一些权限...)
    
    4. 备份与还原
        推荐使用图形化工具
    

    MySQL函数&事务

    今日目标

    1. mysql函数
    
    2. mysql综合练习【重点】
    
    3. 事务安全 TCL
    

    一 MySQL函数

    为了简化操作,mysql提供了大量的函数给程序员使用(比如你想输入当前时间,可以调用now()函数)

    函数可以出现的位置:插入语句的values()中,更新语句中,删除语句中,查询语句及其子句中。

    菜鸟教程:https://www.runoob.com/mysql/mysql-functions.html

    1.1 字符串函数

    常用

    1.  函数:CONCAT(s1,s2...sn)
        描述:字符串 s1,s2 等多个字符串合并为一个字符串
        实例:select concat('传智播客','-','黑马程序员');
        
    2.  函数:CHAR_LENGTH(str)
        描述:返回字符串 str 的字符数
        实例:select char_length('你好,树先生');
        
    3.  函数:LENGTH(str)
        描述:返回字符串 s 的字节数
        编码:UTF8(一个中文字符占3个字节)
        实例:select length('你好,树先生h');
        
    4.  函数:UCASE(s) | UPPER(s)
        描述:将字符串转换为大写
        实例:select ucase('itcast');
        
    5.  函数:LCASE(s) | LOWER(s)
        描述:将字符串转换为小写
        实例:select lcase('ITHEIMA');
        
    6.  函数:LOCATE(s1,s)
        描述:从字符串 s 中获取 s1 的开始位置
        注意:从1开始
        实例:select locate('hei','itheima');
        
    7.  函数:TRIM(str) | LTRIM(str) | RTRIM(str)
        描述:字符串去空格
        实例:select trim('   莘莘学子    ');
        
    8.  函数:REPLACE(s,s1,s2)
        描述:将字符串 s2 替代字符串 s 中的字符串 s1
        实例:select replace('abc','b','x');
        
    9.  函数:SUBSTR(s, start, length)
        描述:从字符串 s 的 start 位置截取长度为 length 的子字符串
        注意:从1开始
        实例:select substr('itcast','2','3');
        
    10. 函数:STRCMP(str1,str2)
        描述:比较字符串大小,左大于右时返回1,左等于右时返回0,,左小于于右时返回-1,
        实例:select strcmp('a','b');
    

    导入数据

    通过sqlyog,实现sql脚本的导入....
    

    练习

    -- 1.将所有员工的昵称改为大写
    SELECT UCASE(nickname) FROM emp;
    
    -- 2.显示所有员工的姓氏,截取
    SELECT ename,SUBSTR(ename,1,1) FROM emp;
    
    
    -- 3.显示所有员工姓名字符长度
    SELECT CHAR_LENGTH(ename) FROM emp;
    
    -- 4.显示所有员工姓名字节长度
    SELECT LENGTH(ename) FROM emp;
    
    -- 5.将所有姓李的员工,姓氏替换为li
    SELECT REPLACE(ename,'李','li') FROM emp;
    
    
    -- 6.将所有员工的姓名和昵称拼接在一起
    SELECT CONCAT(ename,nickname) FROM emp;
    
    

    1.2 日期函数

    常用

    1.  函数:NOW() | CURDATE() | CURTIME()
        描述:获取系统当前日期时间、日期、时间
        实例:select now();
        
    2.  函数:YEAR(DATE) | MONTH(DATE) | DAY(DATE)
        描述:从日期中选择出年、月、日
        实例:select year(now());
        
    3.  函数:LAST_DAY(DATE)
        描述:返回月份的最后一天
        实例:select last_day(now());
        
    4.  函数:ADDDATE(DATE,n) | SUBDATE(DATE,n)
        描述:计算起始日期 DATE 加(减) n 天的日期
        实例:select subdate(now(),10);
        
    5.  函数:QUARTER(DATE)
        描述:返回日期 DATE 是第几季节,返回 1 到 4
        实例:select quarter(now());
        
    6.  函数:DATEDIFF(d1,d2)
        描述:计算日期 d1->d2 之间相隔的天数
        实例:select datediff(now(),'1999-1-1');
        
    7.  函数:DATE_FORMAT(d,f)
        描述:按表达式 f的要求显示日期 d
        实例:select date_format(now(),'%Y-%m-%d');
    

    练习

    -- 1.统计每个员工入职的天数
    SELECT ename,DATEDIFF(NOW(),joindate) FROM emp;
    
    -- 2.统计每个员工的工龄
    SELECT ename,DATEDIFF(NOW(),joindate)/365 FROM emp;
    
    -- 3.查询2011年入职的员工
    SELECT * FROM emp WHERE YEAR(joindate) = '2011';
    
    -- 4.统计入职10年以上的员工信息
    SELECT * FROM emp WHERE DATEDIFF(NOW(),joindate)/365 >10;
    
    

    1.3 数字函数

    常用

    1.  函数:ABS(x)
        描述:返回 x 的绝对值  
        实例:select abs(-10);
        
    2.  函数:CEIL(x) | FLOOR(x)
        描述:向上(下)取整
        实例:select ceil(1.5);
        
    3.  函数:MOD(x,y)
        描述:返回x mod y的结果,取余
        实例:select mod(5,4);
        
    4.  函数:RAND()
        描述:返回 0 到 1 的随机数
        实例:select rand();
        
    5.  函数:ROUND(x)
        描述:四舍五入
        实例:select round(1.2345);
        
    6.  函数:TRUNCATE(x,y)
        描述:返回数值 x 保留到小数点后 y 位的值
        实例:select truncate(5633.123324,2);
    

    练习

    -- 1.统计每个员工的工龄,超过半年的算一年
    SELECT ename,ROUND( DATEDIFF(NOW(),joindate)/365) FROM emp;
    
    -- 2.统计每个部门的平均薪资,保留2位小数
    SELECT dept_id,TRUNCATE( AVG(salary),2 )FROM emp GROUP BY dept_id;
    
    -- 3.统计每个部门的平均薪资,小数向上取整
    SELECT dept_id,CEIL( AVG(salary) )FROM emp GROUP BY dept_id;
    
    -- 4.统计每个部门的平均薪资,小数向下取整
    SELECT dept_id,FLOOR( AVG(salary) )FROM emp GROUP BY dept_id;
    
    

    1.4 高级函数

    1.4.1 CASE表达式

    • 相当于java中swtich语句

    语法

        SELECT 
            CASE [字段,值] 
                WHEN 判断条件1 
                    THEN 希望的到的值1
                WHEN 判断条件2 
                    THEN 希望的到的值2
                ELSE 前面条件都没有满足情况下得到的值 
            END
        FROM
            table_name;
    

    练习

    -- 查询每个员工的工资等级并排序
        -- 工资等级在1显示为 '努力赚钱'
        -- 工资等级在2显示为 '小康生活'
        -- 工资等级在3显示为 '可以娶媳妇'
        -- 工资等级在4显示为 '可以买车'
        -- 工资等级在5显示为 '可以买房'
        -- 工资等级不在以上列表中显示为  '土豪'
        
    -- 1.确定几张表
    SELECT * FROM emp e INNER JOIN salarygrade sg;
    
    -- 2.确定连接条件
    SELECT * FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
    
    -- 3.确定显示字段
    SELECT e.ename,e.`salary`,sg.`grade` FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
    
    -- 4.确定业务条件
    SELECT e.ename,e.`salary`,
        CASE sg.`grade` 
            WHEN 1 THEN '努力赚钱'
            WHEN 2 THEN '小康生活'
            WHEN 3 THEN '可以娶媳妇'
            WHEN 4 THEN '可以买车'
            WHEN 5 THEN '可以买房'
            ELSE '土豪'
        END AS '生活状态'
    FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary` ORDER BY sg.`grade` ASC;
    

    1.4.2 IF表达式

    • 相当于java中三元运算符

    语法

    SELECT IF(1 > 0,'真','假') from 表名;
    

    练习

    -- 工资+奖金大于20000的员工 显示家有娇妻,否则显示单身狗
    SELECT ename,IF(salary+IFNULL(bonus,0) > 20000,'家有娇妻','单身狗') AS 家里有啥 FROM emp;
    

    二 MySQL综合练习

    -- 1.计算员工的日薪(按30天),保留二位小数
    SELECT ename,TRUNCATE(salary/30,2) FROM emp;
    
    -- 2.计算出员工的年薪(12月),并且以年薪排序 降序
    SELECT ename,(salary + IFNULL(bonus,0)) * 12 AS 年薪 FROM emp ORDER BY 年薪 DESC;
    
    -- 3.找出奖金少于5000或者没有获得奖金的员工的信息
    SELECT * FROM emp WHERE IFNULL(bonus,0) < 5000;
    
    
    -- 4.返回员工职务名称及其从事此职务的最低工资
    -- 4.1 确定几张表
    SELECT * FROM emp e INNER JOIN job j;
    -- 4.2 确定连接条件
    SELECT * FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
    -- 4.3 确定显示字段
    SELECT j.`jname` FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
    -- 4.4 确定业务条件(分组+最低工资)
    SELECT j.`jname`,MIN(e.`salary`) FROM emp e INNER JOIN job j ON e.`job_id` = j.`id` GROUP BY j.`jname`;
    
    
    
    -- 5.返回工龄超过10年,且2月份入职的员工信息
    SELECT * FROM emp WHERE DATEDIFF(NOW(),joindate)/365 > 10 AND MONTH(joindate) = 2;
    
    
    
    -- 6.返回与 林冲 同一年入职的员工
    SELECT YEAR(joindate) FROM emp WHERE ename = '林冲';
    SELECT * FROM emp WHERE YEAR(joindate) = (SELECT YEAR(joindate) FROM emp WHERE ename = '林冲');
    
    
    
    -- 7.返回每个员工的名称及其上级领导的名称(自关联)
    SELECT a.`ename`,b.`ename` FROM emp  a LEFT OUTER JOIN emp b ON a.`mgr` = b.`id`;
    
    
    
    -- 8.返回工资为二等级(工资等级表)的职员名字(员工表)、部门名称(部门表)
    -- 8.1 确定几张表
    SELECT * FROM emp e
        INNER JOIN dept d
        INNER JOIN salarygrade sg;
    -- 8.2 确定连接条件
    SELECT * FROM emp e
        INNER JOIN dept d ON e.`dept_id` = d.`id`
        INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
    
    -- 8.3 确定显示字段
    SELECT sg.`grade`,e.`ename`,d.`dname` FROM emp e
        INNER JOIN dept d ON e.`dept_id` = d.`id`
        INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
        
    -- 8.4 确定业务条件
    SELECT sg.`grade`,e.`ename`,d.`dname` FROM emp e
        INNER JOIN dept d ON e.`dept_id` = d.`id`
        INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`
        WHERE sg.`grade` = 2;
        
    
    -- 9.涨工资:董事长2000 经理1500 其他800
    -- 9.1 确定几张表和连接条件
    SELECT * FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
    -- 9.2 显示字段(case表达式)
    SELECT e.`ename`,j.`jname`,e.`salary` AS 涨前,
           CASE j.`jname`
        WHEN '董事长' THEN e.salary + 2000
        WHEN '经理' THEN e.salary + 1500
        ELSE e.salary + 800
           END AS 涨后 
        FROM emp e INNER JOIN job j ON e.`job_id` = j.`id`;
    
    

    三 事务安全 TCL【理解】

    3.1 概述

    如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

    应用场景:用户转账

    准备数据

    -- 创建库
    create database day21_pro;
    
    -- 使用库
    use day21_pro;
    
    -- 创建数据表
    CREATE TABLE account (  -- 账户表
        id INT PRIMARY KEY AUTO_INCREMENT,
        `name` VARCHAR(32),
        money DOUBLE 
    );
    
    -- 添加数据
    INSERT INTO account (`name`, money) VALUES ('蝴蝶姐', 1000), ('罗志祥', 1000);
    

    模拟转账

    -- 罗志祥扣钱(转出)
    UPDATE account SET money = money -100 WHERE  id = 2;
    
    -- 机器故障了
    
    
    -- 蝴蝶姐加强(转入)
    UPDATE account SET money = money + 100 WHERE id = 1;
    

    3.2 操作事务【掌握】

    3.2.1 手动提交事务【手动操作mysql使用方案】

    1. 开启事务
            begin
            
    2. 提交事务
            commit
            
    3. 回滚事务
            rollback
    
    1588563849352.png

    转账成功

    -- 1. 开启事务
        begin;
    -- 2. 罗志祥扣钱
        UPDATE account SET money = money -100 WHERE  id = 2;
    -- 3. 蝴蝶姐加钱
        UPDATE account SET money = money + 100 WHERE id = 1;
    -- 4. 提交事务
        commit;
    

    转账失败

    -- 1.开启事务
        begin;
    -- 2.罗志祥扣钱
        UPDATE account SET money = money -100 WHERE  id = 2;
    -- 3.机器故障
    
    -- 4.回滚事务
        rollback;
    

    3.2.2 自动提交事务【在JDBC将自动提交关闭】

    * 默认情况下,在MySQL中每一条DML(增删改)语句,就是一个独立的事务
    
    * 查看MySQL是否开启自动提交
            show variables like 'autocommit';
            
    * 临时关闭自动提交(手动)
            set autocommit=off;
    

    模拟转账

    -- 1.罗志祥扣钱
        UPDATE account SET money = money -100 WHERE  id = 2;
        
    -- 2.改为手动提交/回滚
        commit / rollback ;
    

    3.3 事务工作原理

    注意:

    在同一个事务中,出现bug(异常),必须执行rollback命令,不然会影响同一个事务中下一次提交

    1588565465224.png

    3.4 保存(回滚)点【了解】

    ​ 当事务开启后,一部分sql执行成功,添加一个保存点,后续操作报错了,回滚到保存点,保证之前的操作可以成功提交

    1. 设置保存点
            savepoint 保存点名;
            
    2. 回滚到保存点
            rollback to 保存点名;
    

    场景:罗志祥同时约5个小姐姐,如果前二个成功扣款了设置保存点,保证前二位能够上门服务....

    -- 1.开启事务
        begin;
    -- 2.罗志祥扣钱一次(凤姐)
        UPDATE account SET money = money -100 WHERE  id = 2;
    -- 3.罗志祥扣钱二次(芙蓉姐姐)
        UPDATE account SET money = money -100 WHERE  id = 2;
    -- 4.设置一个保存点
        savepoint ol;
    -- 5.罗志祥扣钱三次(石榴姐)
        UPDATE account SET money = money -100 WHERE  id = 2;
    -- 6.机器故障
    
    -- 7.回滚点保存点
        rollback to ol;
    -- 8.提交事务
        commit;
    

    3.5 事务特性 ACID【面试题】

    1. 原子性:A atomicity
            如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。
            
    2. 一致性:C consistency
            事务在执行前后,保证数据的一致性
            
    3. 隔离性:I isolation【重点】
            多个事务之间,相互独立,互不干扰....
    
    4. 持久性:D durability
            事务一旦成功提交,保存到磁盘文件,不可逆....
    

    3.6 事务隔离性

    ​ 多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题。

    1. 脏读【必须要避免的....】
            一个事务中,读取到另一个事务,未提交的数据
            
    2. 不可重复读
            一个事务中,二次读取的内容不一致,另外一个事务做了update操作
    
    3. 幻读
            一个事务中,二次读取的数量不一致,另外一个事务做了insert、delete操作
    

    3.6.1 MySQL数据库隔离级别

    级别 名字 隔离级别 脏读 不可重复读 幻读 数据库默认隔离级别
    1 读未提交 read uncommitted
    2 读已提交 read committed Oracle和SQL Server
    3 可重复读 repeatable read MySQL
    4 串行化 serializable

    性能角度:1>2>3>4

    安全角度:4>3>2>1

    综合考虑:2 or 3

    3.6.2 演示隔离级别产生的问题【课上听一听】

    1. 查看当前数据库隔离级别
            show variables like '%isolation%';
            
    2. 临时修改隔离级别
            set session transaction isolation level 级别字符串;
    

    准备模拟二个事务窗口:A窗口、B窗口

    1. 演示脏读
        设置数据库隔离级别 read uncommitted;
            set session transaction isolation level read uncommitted;
    
    1588574979356.png
    2. 解决脏读问题(引出:不可重复读问题)
        设置数据库隔离级别 read committed;
            set session transaction isolation level read committed;
    
    1588575246485.png
    3. 解决不可重复读问题(出现幻读问题)
        设置数据库隔离级别 repeatable read;
            set session transaction isolation level repeatable read;
    
    1588575464733.png 1588575751527.png
    4. 解决幻读问题
        设置数据库隔离级别 serializable;
            set session transaction isolation level serializable;
    
    1588575900641.png

    相关文章

      网友评论

          本文标题:5/04day46_MySQL函数&事务

          本文链接:https://www.haomeiwen.com/subject/hfahohtx.html