一 MySQL函数

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

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

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

1.1 字符串函数

常用

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

导入数据

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

练习

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

1.2 日期函数

常用

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

练习

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

1.3 数字函数

常用

  1. 1.  函数：ABS(x)
  2.     描述：返回 x 的绝对值　　
  3.     实例：select abs(-10);
  5. 2.  函数：CEIL(x) | FLOOR(x)
  6.     描述：向上（下）取整
  7.     实例：select ceil(1.5);
  9. 3.  函数：MOD(x,y)
  10.     描述：返回x mod y的结果，取余
  11.     实例：select mod(5,4);
  13. 4.  函数：RAND()
  14.     描述：返回 0 到 1 的随机数
  15.     实例：select rand();
  17. 5.  函数：ROUND(x)
  18.     描述：四舍五入
  19.     实例：select round(1.2345);
  21. 6.  函数：TRUNCATE(x,y)
  22.     描述：返回数值 x 保留到小数点后 y 位的值
  23.     实例：select truncate(5633.123324,2);

练习

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

1.4 高级函数

1.4.1 CASE表达式

  • 相当于java中swtich语句

语法

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

练习

  1. -- 查询每个员工的工资等级并排序
  2.     -- 工资等级在1显示为 '努力赚钱'
  3.     -- 工资等级在2显示为 '小康生活'
  4.     -- 工资等级在3显示为 '可以娶媳妇'
  5.     -- 工资等级在4显示为 '可以买车'
  6.     -- 工资等级在5显示为 '可以买房'
  7.     -- 工资等级不在以上列表中显示为  '土豪'
  9. -- 1.确定几张表
  10. SELECT * FROM emp e INNER JOIN salarygrade sg;
  12. -- 2.确定连接条件
  13. SELECT * FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
  15. -- 3.确定显示字段
  16. SELECT e.ename,e.`salary`,sg.`grade` FROM emp e INNER JOIN salarygrade sg ON e.`salary` BETWEEN sg.`losalary` AND sg.`hisalary`;
  18. -- 4.确定业务条件
  19. SELECT e.ename,e.`salary`,
  20.     CASE sg.`grade` 
  21.         WHEN 1 THEN '努力赚钱'
  22.         WHEN 2 THEN '小康生活'
  23.         WHEN 3 THEN '可以娶媳妇'
  24.         WHEN 4 THEN '可以买车'
  25.         WHEN 5 THEN '可以买房'
  26.         ELSE '土豪'
  27.     END AS '生活状态'
  28. 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中三元运算符

语法

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

练习

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

二 MySQL综合练习

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

三 事务安全 TCL【理解】

3.1 概述

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

应用场景：用户转账

准备数据

  1. -- 创建库
  2. create database day21_pro;
  4. -- 使用库
  5. use day21_pro;
  7. -- 创建数据表
  8. CREATE TABLE account (  -- 账户表
  9.     id INT PRIMARY KEY AUTO_INCREMENT,
  10.     `name` VARCHAR(32),
  11.     money DOUBLE 
  12. );
  14. -- 添加数据
  15. INSERT INTO account (`name`, money) VALUES ('蝴蝶姐', 1000), ('罗志祥', 1000);

模拟转账

  1. -- 罗志祥扣钱（转出）
  2. UPDATE account SET money = money -100 WHERE  id = 2;
  4. -- 机器故障了
  7. -- 蝴蝶姐加强（转入）
  8. UPDATE account SET money = money + 100 WHERE id = 1;

3.2 操作事务【掌握】

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

  1. 1. 开启事务
  2.         begin
  4. 2. 提交事务
  5.         commit
  7. 3. 回滚事务
  8.         rollback

1588563849352.png

转账成功

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

转账失败

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

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

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

模拟转账

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

3.3 事务工作原理

注意：

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

1588565465224.png

3.4 保存（回滚）点【了解】

  1. 当事务开启后，一部分sql执行成功，添加一个保存点，后续操作报错了，回滚到保存点，保证之前的操作可以成功提交
  1. 1. 设置保存点
  2.         savepoint 保存点名;
  4. 2. 回滚到保存点
  5.         rollback to 保存点名;

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

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

3.5 事务特性 ACID【面试题】

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

3.6 事务隔离性

  1. 多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
  1. 1. 脏读【必须要避免的....】
  2.         一个事务中，读取到另一个事务，未提交的数据
  4. 2. 不可重复读
  5.         一个事务中，二次读取的内容不一致，另外一个事务做了update操作
  7. 3. 幻读
  8.         一个事务中，二次读取的数量不一致，另外一个事务做了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