MySQL函数，事务学习--ThirdDay

一. 统计年薪案例

年薪统计

#公司福利不错13薪，年底双薪，
#统计员工的年薪=sal*13+comm*13
 SELECT *,sal + IFNULL(comm, 0) AS 月薪 FROM emp;
 SELECT *,(sal+IFNULL(comm,0))\*13 年薪 FROM emp ;
 SELECT *,sal\*13+IFNULL(comm,0)\*13 年薪 FROM emp;#用0替换掉null

二.aggregation聚合函数

根据一列统计结果

最大值 max/最小值min

#聚合函数：
#求最高薪
SELECT ename,sal FROM emp ORDER BY sal DESC LIMIT 1;
SELECT MAX(sal) FROM emp;
#求最低薪
SELECT ename,sal FROM emp ORDER BY sal ASC LIMIT 1;
SELECT MIN(sal) FROM emp;

求和 sum/ 求平均值avg

#求和sum  求平均数 avg
DELETE FROM emp WHERE empo=600;
SELECT SUM(sal) FROM emp;
SELECT SUM(sal+IFNULL(comm,0)) 工资总和 FROM emp;
SELECT AVG(sal) FROM emp;
SELECT AVG(sal+IFNULL(comm,0)) 平均工资 FROM emp;

count求个数

#求个数count(*/字段名) 缺点：字段名为空则不统计
SELECT COUNT(ename) 员工总数 FROM emp;#低效
SELECT COUNT(IFNULL(comm,0)) 员工总数 FROM emp;#低效
SELECT COUNT(*) 员工总数 FROM emp;#都统计--低效
SELECT COUNT(1) 员工总数 FROM emp;#都统计--高效

案例：

#统计工资大于8000的员工人数
SELECT COUNT(1) FROM emp WHERE sal>8000;

分组group by

#分组group by
#统计每个部门的平均薪资
#什么时候分组？当查询结果中出现了聚合列和非聚合列时
#按照什么分组合理？通常按照费非聚合列分组
#什么是聚合列？使用了聚合函数max()、avg()、sum()、avg()、count()
SELECT deptno,AVG(sal) FROM emp 
GROUP BY deptno;#按照部门编号分组
#统计每个岗位的最高工资
SELECT job,MAX(sal) FROM emp GROUP BY job;
#统计每年的入职人数
SELECT YEAR(hiredate),COUNT(1) 每年入职人数 FROM emp GROUP BY YEAR(hiredate);

having

#having的作用：用来对分组后的数据，进一步过滤
#统计每个部门的平均工资，要平均工资大于10000
SELECT deptno,AVG(sal) FROM emp 
GROUP BY deptno 
HAVING AVG(sal)>10000;
#统计每个岗位的最高薪资，只要>8000的记录
SELECT job,MAX(sal) FROM emp GROUP BY job HAVING MAX(sal)>8000;
SELECT job,MAX(sal) FROM emp 
#先过滤再分组高效，但是where里不能用聚合函数
#where MAX(SAL)>8000
GROUP BY job 
HAVING MAX(sal)>8000 
ORDER BY MAX(SAL)DESC;
#统计每年入职的人数,只要人数>1的记录
SELECT YEAR(hiredate),COUNT(1) 每年入职人数 FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1)>1;

#统计每年入职的人数,只要2017之后的记录
SELECT YEAR(hiredate),COUNT(1) FROM emp 
WHERE YEAR(hiredate)>2017 #只是不能出现聚合函数，写在where里，高效
GROUP BY YEAR(hiredate);
#having YEAR(hiredate)>2017 #使用的过滤条件必须是查到的结果，低效
#where 后面要跟的是数据表里的字段，where针对数据库文件的发挥作用。
#having子句中可以使用字段别名，而where不能使用
#having筛选时，只能根据select子句中可出现的字段（数据）来进行条件设定。

三.事务Transaction

1.什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。保证SQL语句,要么全执行成功,要么全失败

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

给A的账户减去100元；
update 账户表 set money=money**-100** where name=‘A’;

给B的账户加上100元。
update 账户表 set money=money**+100** where name=‘B’;

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么B的账户没有加上100元，而A却减去了100元，在现实生活中这肯定是不允许的。

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

2.事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。是指多条SQL是一个原子,密不可分.如果都正确,就操作了数据.如果有错误的都会发生回滚,回到事务执行之前
**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。保证了数据的一致性和完整性.
**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。保证多线程并发时的数据安全,多个操作之间是被隔离的.
**持久性：**事务处理结束后，对数据的CDU的影响是持久生效的，即便系统故障也不会丢失。

隔离级别：读未提交 读已提交 可重复读 串行化
从前往后，性能越来越差，安全性越来越高，MySQL默认是可重复读

3.测试

先开启事务,然后执行一批SQL,再结束事务

mysql> start transaction; #开启事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(18,'java','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept values(19,'java2','shanghai2');
Query OK, 1 row affected (0.00 sec)
mysql> commit; #提交事务
Query OK, 0 rows affected (0.03 sec)

四.字段约束

1.默认约束和检查约束

#默认约束：给指定字段设置默认值
CREATE TABLE f(
id INT PRIMARY KEY AUTO_INCREMENT,#主键，自动递增
sex VARCHAR(10) DEFAULT '男'#设置默认值
);
#检查约束：给指定字段设置合法值的检查规则
CREATE TABLE g(
id INT PRIMARY KEY AUTO_INCREMENT,
age INT 
CHECK(age>0 AND age <200)#设置检查
);

2.外键约束

#外键约束:通过指定的sql语句描述两张表的关系
#约束：子表添加记录时id必须取自主表
#怎么确定主表和子表？子表中有外键的sql
#主表
CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
sex VARCHAR(10)
);
#关联的子表
CREATE TABLE tb_user_address(
user_id INT PRIMARY KEY AUTO_INCREMENT,
address VARCHAR(20),
#表明和哪种表的哪个字段有关系
#FOREIGN KEY(本表的主键) REFERENCES 关联表(主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id)
);

五.索引

1.概述：

为了提高数据库的查询效率

2.测试

#查看索引
show index for emp；

#注：接MySQL索引，SQL语句学习–FourthDay