SQL聚合函数、分组、事务、字段约束、索引。

一,统计年薪
#统计员工的年薪
SELECT sal,comm,(sal+comm)*12 FROM emp
SELECT sal,comm,
(sal+IFNULL(comm,0))*16 AS 年薪 #as用来设置别名
FROM emp

二,聚合函数
–1,概述
把一列的值全都取出来,聚合起来,分析最大值,最小值,平均值,求和,求个数
常见的聚合函数: max   min   avg    sum   count 

–2,测试
#聚合函数:把查出来的列聚合起来分析数据
#求最高薪max 
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1
SELECT MAX(sal) FROM emp #获取sal这列里的最大值
#求最低薪min 
SELECT sal FROM emp ORDER BY sal LIMIT 1
SELECT MIN(sal) MIN FROM emp
#求和sum 
SELECT SUM(sal) FROM emp
#求平均数avg
SELECT AVG(sal) FROM emp
SELECT MAX(sal),MIN(sal),SUM(sal),AVG(sal) 
FROM emp
#求总个数count
SELECT COUNT(comm) FROM emp#不统计comm字段值是null的-低效
SELECT COUNT(*) FROM emp#都统计
SELECT COUNT(1) FROM emp#都统计-高效
#统计工资>8000的人数
SELECT COUNT(1) FROM emp 
WHERE sal>8000
#统计2019年入职的人数
SELECT COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019

三,分组
–1,测试
#什么时候必须分组?当查询结果中出现了聚合列和非聚合列时
#按照什么分组合理?通常按照非聚合列分组
#什么是聚合列非聚合列?使用了聚合函数max min sum avg count的就是聚合列
#统计每个部门的平均薪资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno #按照部门编号分组
#统计每个岗位的最高薪资
SELECT MAX(sal),job FROM emp
GROUP BY job#按照岗位分组
#统计每年的入职人数
#count是聚合函数,year只是普通函数
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)#按照非聚合列分组

#having的作用:用来对分组后的数据,进一步过滤
#统计每个部门的平均薪资,只要>10000的记录
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno
HAVING AVG(sal)>10000
#统计每个岗位的最高薪资,只要>8000的记录
SELECT job,MAX(sal) FROM emp
#先过滤再分组高效,但是where里不能用聚合函数
#where MAX(sal)>8000  #会报错
GROUP BY job
HAVING MAX(sal)>8000
ORDER BY MAX(sal) #升序排序

#统计每年的入职人数,只要人数>1的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
GROUP BY YEAR(hiredate)
HAVING COUNT(1) > 1 
#不能改成where,后面出现了聚合函数

#统计每年的入职人数,只要2017年以后的记录
SELECT COUNT(1),YEAR(hiredate) FROM emp
WHERE YEAR(hiredate) > 2017 #高效,只是where里不能出现聚合函数
GROUP BY YEAR(hiredate)
#having YEAR(hiredate)> 2017 
#having里使用的过滤条件必须是查到的结果

四,事务
-1,概述
保证SQL语句,要么全执行成功,要么全失败
有四个特征ACID:
A是原子性: 是指多条SQL是一个原子,密不可分.如果都正确,就操作了数据.如果有错误的都会发生回滚,回到事务执行之前.
C是一致性: 保证了数据的一致性和完整性.
I是隔离性: 保证多线程并发时的数据安全,多个操作之间是被隔离的.
D是持久性: 是指对数据CDU的影响是持久生效的.
隔离级别:读未提交 读已提交 可重复读 串行化
从前往后,性能越来越差,安全性越来越高.MySQL默认是可重复读

-2,测试
先开启事务,然后执行一批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,外键约束
#默认约束:给指定字段设置默认值
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)#设置检查
)

#外键:通过指定的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 FROM emp