mysql高级day02

索引

索引 了解:

1. 什么是索引
	 索引可以理解为新华字典的目录, 作用是 大大大的提高查询效率.

2. 常见索引
		主索引(主键 primary key)
		唯一索引(唯一键 unique key)
		普通索引(普通键 key)
		全文索引(fulltext)

3. 体会一把索引的好处, 顺便学习如何看一条查询sql执行效率是否高
		explain select查询语句;
		explain select * from student where id=2;
		explain select * from student where name='关羽';

外键高级

外键 了解

概念: 外键约束指两个表之间的跨表的条件约束


要求:
  1. 表引擎必须为 innodb
  2. 分为主表(parent table)和从表(child table), 从表引用主表数据;
  3. 要求主表和从表关联字段的数据类型严格一致.(大小,类型是否有无符号 都要一致)


外键约束作用:
  主要是保证主表和从表数据的完整性:
  1. 从表的关联字段不能随意添加数据,必须以主表为准
  2. 主表的关联字段不能随意修改和删除(从表如果使用就不能随意动)

外键使用的语法:
  1. 添加外键
     alter table 从表名 add [constraint `约束名`] foreign key (从表关联字段)
     references 主表名(主表关联字段) [高级语法];

    案例: 学生表中添加外键
    alter table student add foreign key(class_id) references class(class_id);

    # 查看外键是否添加成功
    show create table student;
    CREATE TABLE `student` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(20) NOT NULL COMMENT '姓名',
      `sex` char(1) NOT NULL COMMENT '性别',
      `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
      `class_id` int(10) unsigned DEFAULT NULL COMMENT '班级id',
      PRIMARY KEY (`id`),
      KEY `class_id` (`class_id`), # 普通键 自动添加
      CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 COMMENT='students'

    # 体验外键的好处

2. 删除外键
    alter table 从表名 drop foreign key 约束名字;

    删除学生表中的外键;
    alter table student drop foreign key `student_ibfk_1`;
    alter table student drop foreign key `student_ibfk_2`;
    alter table student drop key `class_id`;


3 高级语法
    alter table 从表名 add [constraint `约束名`] foreign key (从表关联字段)
     references 主表名(主表关联字段)
     [on update 模式 on  delete 模式];

    模式三种：
        1. 默认模式：strict 严格
        2. 级联模式：cascade , 如果主表字段发生改变， 从表跟着一起改变
        3. 置空模式：set null, 如果主表字段发生改变， 从表设置为空

    学生表添加一个外键: 要求主表更新从表一起更新,主表删除从表设置为null
    alter table student add foreign key (class_id) references class(class_id)
    on update cascade on delete set null ;

事务

事务:
		

概念: 事务是指连续不可分割的多条sql语句,要么全部都执行成功,要么全部回滚到初始状态.


作用: 事务是保证数据完整性,一致性,安全性的重要技术.


事务操作 三步:
1. 开启事务: begin; start transaction;
2. 提交事务: commit;
3. 回滚事务: rollback;




案例:
	八戒给貂蝉每日转200元
	添加一个money字段:  alter table student add money decimal(7,2) default 0 comment '零花钱';


八戒给貂蝉每日转200元:
# 开启事务
start transaction;
# 八戒钱少200	
update student set money=money-200 where name="猪八戒";
# 貂蝉钱多200	
update student set money=money+200 where name="貂蝉";
# 提交
commit;



# 开启事务
start transaction;
# 八戒钱少200	
update student set money=money-200 where name="猪八戒";
# 貂蝉钱多200	
update student set money=money+200 where name="貂蝉";
# 回滚
rollback;


事务的特点，ACID

事务的四个特性：原子性，一致性，隔离性，持久性！
1）原子性（Atomicity）
	事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2）一致性（Consistency）
	事务前后数据的完整性必须保持一致。
3）隔离性（Isolation）
	多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。
4）持久性（Durability）
	一个事务一旦被提交，它对数据库中的数据改变就是永久性的。

实体间关系

三种关系:
一对一:
      使用相同的主键即作为各自主键也作用关联字段
一对多:
       在多的一方的表中设计一个字段保存一的一方的主键id.
多对多:
        新建一张中间关系表,保存两个表的主键id



实现多对多关系:

班级表和讲师表

讲师表:
create table teacher(
t_id int UNSIGNED PRIMARY KEY auto_increment comment '主键id',
t_name VARCHAR(50) not null comment '姓名',
t_age tinyint UNSIGNED not null comment '年龄'
)engine=innodb charset=utf8 comment '讲师表';
insert into teacher(t_name,t_age) VALUES
("唐老师",30),
("郭老师",30),
("强老师",20);

# 创建班级讲师中间关系表
create table class_teacher(
class_id int UNSIGNED not null comment '班级id',
t_id int UNSIGNED not null comment '讲师id'
)engine=innodb charset=utf8 comment '班级讲师中间关系表';

insert into class_teacher(class_id,t_id) VALUES
(3,1),
(3,2),
(3,3),
(2,1),
(4,1),
(5,1);


# 案例: 查询唐老师带过哪些班级
select teacher.t_name,c.class_name
from teacher
join class_teacher as ct on teacher.t_id=ct.t_id
join class as c on ct.class_id=c.class_id
where teacher.t_name = "唐老师";

select t.t_name,c.class_name
from teacher as t
join class_teacher as ct on t.t_id=ct.t_id
join class as c on c.class_id=ct.class_id
where t.t_name = "唐老师";

select t.t_name,c.class_name
from teacher as t
join class_teacher as ct on t.t_id=ct.t_id
join class as c on ct.class_id=c.class_id
where t.t_name="唐老师";