day021

sqlYog工具----->改变表
---->快速改变字段属性.

没有去重的需求一般不用set,
list和map用得更多

or的效率比and低,因为查的东西更多

模糊查询中’X%'最高效,因为不是X开头的元素直接不看.

######################################################

								<统计案例>

入职统计
#2015年以前入职的老员工
SELECT * FROM emp WHERE DATE_FORMAT(hiredate,’%Y-%m-%d’)<‘2015-01-01’;
SELECT * FROM emp WHERE YEAR(hiredate)<2015

公司福利不错13薪，年底双薪，统计员工的年薪=sal13+comm13
SELECT empno,ename,job,sal13+comm13 FROM emp;
SELECT empno,ename,job,sal13+comm13 as 年薪 FROM emp;–用as给列起个别名
SELECT empno,ename,job,sal13+comm13 年薪 FROM emp; --as也可以省略
select ename, sal+comm from emp
select ename, sal , comm, sal+ifnull(comm,0) from emp–用0替换掉null

别名:AS XXXXX,觉得本来的列名太长时用
显示工作历史
SELECT *,YEAR(NOW())-YEAR(hiredate)AS YEAR FROM EMP;

聚合 aggregation 根据一列统计结果

count 输出行数
select count() from emp --底层优化了
select count(1) from emp --效果和一样
—因为统计的只是主键,高效,()里的数字没有意义,负数小数都不影响
select count(字段) from emp --慢,只统计非NULL的

max / min
select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组

sum / avg
select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数

聚合列 合 非聚合列 绝对不要写一起!!!会只输出一行.
必须分组

######################################################

分组 group
用于对查询的结果进行分组统计,统计一组数据中的最大值,最小值…
使用关键字group by表示分组, having 子句类似where过滤返回的结果

SELECT deptno,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno; #按照deptno分组----部门1的最高薪是,部门1的平均薪是

SELECT job,MAX(sal),AVG(sal) FROM emp
GROUP BY job; #按照job分组----岗位1的最高薪是,岗位1的平均薪是

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job; #deptno和job都满足的

SELECT YEAR(hiredate),COUNT(1) FROM emp
GROUP BY YEAR(hiredate); #利用count(1)来统计各组的数据条数(此处为每年的入职员工数)

注意:这里一次查询中同时存在 聚合函数产生的聚合列 和 非聚合列 必须分组!!! 否则报错!
(且此时必须按非聚合列分组,否则报错)

having:完成分组后的过滤

SELECT deptno, AVG(sal) AS 平均薪资 FROM emp # as 起别名
GROUP BY deptno
HAVING AVG(sal)>8000; #分组查询 进一步对结果过滤 (此处为平均工资>8000的才获取)

SELECT YEAR(hiredate),COUNT(1) FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1)>1; #查每年入职的人数,只要人数>1的

SELECT YEAR(hiredate),COUNT(1) FROM emp (WHERE里不允许出现聚合函数)
WHERE YEAR(hiredate)>2010 #但可以写普通函数
GROUP BY YEAR(hiredate);

######################################################

DB_BASIC结束

######################################################

							<事务 transaction>

数据库事务(Database Transaction)
是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

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

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

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。
事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则，
这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，
隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别
事务隔离分为不同级别，包括
读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好

读提交（read committed） Oracle默认的隔离级别

可重复读（repeatable read）MySQL默认的隔离级别,安全性较好,性能一般

串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高,不能并发

查询mysql的隔离级别
在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

开启事务：start transaction；
结束事务：commit（提交事务）或rollback（回滚事务）。

START TRANSACTION ;
INSERT INTO DEPT VALUES(NULL,‘TEST1’,‘十一区’);
INSERT INTO DEPT VALUES(NULL,‘TEST2’,‘十二区’);

↓ ↓ ↓

START TRANSACTION ;
INSERT INTO DEPT VALUES(NULL,‘TEST1’,‘十一区’);
INSERT INTO DEPT VALUES(NULL,‘TEST2’,‘十二区’);
COMMIT;

显示录入了两次数据,这是因为第一次的语法是正确的.
YOG工具结果显示的是临时存储区的数值.
命令行界面的结果才是真实的数据库存储状态.

回滚 rollback
#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚
BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事务回滚，就不会再提交了

约束 constraints
非空约束 not null

唯一约束 unique
Name字段创建了唯一约束，插入数据时数据库会进行检查，如果插入的值相同，就会检查报错：

主键约束 primary key
主键是一条记录的唯一标识，具有唯一性，不能重复

外键约束 foreign key

默认约束 default
CREATE TABLE H(
ID INT PRIMARY KEY AUTO_INCREMENT, #主键自增
SEX VARCHAR(10) DEFAULT 1 #默认约束
)

检查约束 check 检查字段的值是否合法
CREATE TABLE I(
ID INT PRIMARY KEY AUTO_INCREMENT,
AGE INT,
CHECK(AGE>0 AND AGE<18) #检查约束,不合法时会报错
)