数据库从零开始的学习day03

分组 group by

group by 表示分组，having 类似于 where 过滤返回结果

分组后过滤条件 having

#分组 group by
#查询 每个部门里 的最高薪和姓名
# 查询时，出现了聚会列（用了聚合函数的属性）和非聚合列，通常要按非聚合列分组
SELECT deptno, ename, MAX(sal) from emp GROUP BY deptno;

#查询 每种岗位的平均工资和岗位名称
select avg(sal),job from emp group by job;
#查询 每个部门的平均工资
select avg(sal),deptno from emp group by deptno;
#查询 部门 出现的 次数
SELECT deptno, COUNT(1) from emp GROUP BY deptno;
#having 在分组后的结果中继续添加条件
select avg(sal),job from emp group by job HAVING job = '员工';
SELECT avg(sal),job from emp where job = '员工';

#统计部门出现的次数 -- 进一步统计出现次数大于1的
SELECT count(1) c, deptno from emp 
GROUP BY deptno
HAVING c > 1;

SELECT deptno, avg(sal)a FROM emp GROUP BY deptno HAVING a > 10000;

事务 transaction

概念：是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

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

*事务的4给特性ACID

• 原子性：一个事务中所有的操作，必须一次性完成，如果中途中断了，就会回滚到最初始的状态
• 一致性：在事务的开始之前和结束之后，数据库完整性没有被破坏，这表示写入的数据必须是符合实现定制的条件这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：事务可以多个同时进行，并且不会交叉影响其他事务
• 持久性：事务结束后，对数据库的修改是永久的

事务的隔离级别

读未提交：性能最后，数据安全性差

读提交：Oracle 默认的隔离级别 性能较好 安全性较差

可重复读：Mysql 的默认级别 性能较差 安全性较好2

串行化：安全性最高，但是表级的锁，效率低

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

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

在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了！

表强化 6约束 constraints

外键约束 forgrein key

把两张表之间的关系，通过两个表的主键来表示

**默认约束 ** default

给指定的字段设置默认值

检查约束 check

给字段添加检查约束，符合条件才能执行

# default 设置默认约束
CREATE TABLE H (
	ID INT PRIMARY KEY auto_increment,
	sex char(10) DEFAULT 'femel'
);
# 检查约束
CREATE TABLE test01 (
	id INT PRIMARY KEY auto_increment,
	age int
	CHECK(age > 0 AND age <= 200)
);
#外键约束
#主表
CREATE TABLE tb_user (
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(100) not NULL,
	age INT not NULL
);

#子表
CREATE TABLE tb_user_addr (
	user_id INT PRIMARY KEY auto_increment,
	address VARCHAR(255),
	FOREIGN KEY (user_id) REFERENCES tb_user(id)
);
#子表中的记录和主表关联，无法删除主表中的被子表关联的数据。
#不能在子表中添加主表中没有的数据
#外键		 子表字段	   参考		 主表	主表字段
foreign key (user_id) reference tb_user(id)

索引

概述：是一种数据结构，用来提高查找数据的效率。

• 好处：提高查询效率
• 坏处：索引本质是一张表，如果表体积大就比较占内存

主键本身没有索引

分类

• 单值索引：一个索引只包含一个列
• 复合索引：一个索引包含多个列
• 唯一索引：一个索引包含多个列，要求列的值不能相同

创建索引

​ 索引 索引名 表名（字段名）

• create index loc_index on dept(loc);

查看索引

• show index from dept;

explain

select * from dept;

跟踪查询的步骤

#查看索引
SHOW INDEX from dept;

# 创建索引
CREATE INDEX dname_index on dept(dname);

show index from dept;
#删除索引
alter table dept DROP INDEX indexs;

EXPLAIN
SELECT * FROM dept WHERE dname LIKE'%a%';

#创建单值索引 	
alter table dept add unique(dname);

#创建复合索引
alter table dept add INDEX indexs(dname,loc);
alter table dept add UNIQUE indexs(dname,loc);