MySQL进阶操作

末蓝、 2022-06-01 02:14 263阅读 0赞

# MySQL进阶 #

## 00.简介 ##

*  实体与实体之间的3中对应关系，一对一，一对多，多对多
 *  视图用于完成查询语句的封装
 *  事务保证复杂的增删改查操作有效
 *  为了提高查询速度，通过索引实现

## 01.关系 ##

*  建立成绩表score
    
        id
        学生
        科目
        成绩
 *  学生列应该存放什么信息？
 *  学生列的数据从学生表引用，关系也是一条数据；根据三大范式要求，这里引用学生表的id
 *  同理，科目表也应该引自科目表（subject）中的数据
 *  建立表的语句如下：
    
        create table scores(
        id int auto_increment primary key,
        score decimal(5,2),
        stuid int,
        subid int
        );

## 02.外键 ##

*  如何保证成绩表中stuid及subid有效？
 *  stuid可以通过外键约束进行有效性验证，即stuid的值必须在学生表的id列中出现过
 *  完整的创建成绩表的指令如下：
    
        create table scores(
        id int auto_increment primary key,
        score decimal(5,2),
        stuid int,
        subid int，
        foreign key(stuid) references students(id),
        foreign key(subid) references subjects(id)
        );

## 03.外键的级联操作 ##

*  删除学生数据时，如果改id在其他表中（例如成绩表）已经存在，则会抛出异常
 *  推荐使用逻辑删除
 *  可以创建表时指定级联操作
 *  语法如下：
    
        aalter table scores add constraint stu_sco foreign key(stuid) references students(id)
 *  级联操作的类型包括：
    
        restrict（限制）：默认值，抛异常
        cascade（级联）：如果主表的记录删除，则从表中相关联的记录被删除
        set null：将外键设置为空
        no action：什么都不做

## 04.连接查询 ##

*  查询每个学生每个科目的分数
 *  学生姓名来源于学生表，科目来源于科目表，成绩来源于成绩表，如何将三个表放在一起查询结果显示在一个结果集中，用连接查询
 *  关键：找到对应的关系
    
        students表的id------scores表的stuid
        subjetcs表的id------scores表的subid
 *  查询语句：
    
        select students.name,subjects.title,scores.score
        from scores
        inner join students on scores.stuid=students.id
        inner join subjects on scores.subid=subjects.id
 *  连接查询的三种分类：
    
        表A inner join 表B：表A和表B匹配的行会出现在结果中
        表A left  join 表B：表A和表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
        表A right join 表B：表A和表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
 *  练习
 *  查询学生的姓名、平均分
    
        students.name
        avg(scores.score)
        关系:score.stuid=students.id
        分组:students.name
        
        select students.name,avg(scores.score)
        from scores
        inner join students on scores.suid=students.id
        group by students.name;
 *  查询男生的姓名、总分
    
        students.name
        sum(scores.score)
        条件：sex=1 男生
        关系：scores.stuid = students.id
        分组：students.name
        
        select students.name,sum(scores.score)
        from scores
        inner join students on scores.stuid=students.id
        where students.sex=1
        group by students.name;
 *  查询科目的名称、平均分
    
        subjects.title
        avg(scores.score)
        关系：scores.subid = subjects.id
        分组：subjects.title
        
        select subjects.title,avg(scores.score)
        from scores
        inner join subjects on scores.subid=subjects.id
        group by subjects.title;
 *  查询未删除科目的名称、最高分、平均分
    
        subjects.title
        max(scores.score)
        avg(scores.score)
        条件：isdel=0 未删除
        关系：scores.subid = subjects.id
        分组：subjects.title
        
        select subjects.title,max(scores.score),avg(scores.score)
        from scores
        inner join subjects on scores.subid=subjects.id
        where subjects.isdel=0
        group by subjects.title;

## 05.自关联 ##

*  建立省表
    
        id
        title
 *  建立市表
    
        id
        title
        proid（外键，连接省表的id）
 *  两表的数据量统一，不会频繁的增加或减少，单独的生成两张不同的表会增加开销，故将两表合成一张表
 *  观察两表结构发现市表比省表多一个字段，字段不可缺少，若并为一张表，则省份的proid为null
 *  定义表areas，结构如下：
    
        id
        title
        pid
 *  省对应的pid为null，城市对应的pid则填写省份的id
 *  这就是自关联，表中的某一列，关联了这个表中的另一列，但是他们的业务逻辑是不同的
 *  同理，该表可扩展区县划分（区县对应的pid为城市的id）
 *  创建语句如下：
    
        create table areas(
        aid int primary key auto_increment not null,
        atitle varchar(20),
        pid int,
        foreign key(pid) references areas(id)
        );
 *  从网上找到sql脚本，在mysql命令行中键入以下指令添加数据
    
        mysql -uroot -p
        password:******
        
        use dbname
        source areas.sql
 *  查询一共多少数据
    
        select count(*)
        from areas;
 *  查询一共多少省
    
        select count(*)
        from areas
        where pid is null;
 *  查询安徽省有哪些城市：
    
        select title from areas where pid=(
        select id from areas where title='安徽省'
        );
        
        select city.* from areas as city
        inner join areas as province on city.pid=province.id
        where province.title='安徽省';

## 06.视图 ##

*  对于复杂的查询，在多次使用过，维护是一件麻烦的时间
 *  解决方案；定义视图
 *  视图本质上就是对查询的一个封装
 *  定义视图：
    
        create view view_name as
        select * from ...
 *  查询视图：
    
        select * from view_name;
 *  视图的本质是生成逻辑上的一张表

## 07.事务 ##

*  当一个业务逻辑需要多个sql语句完成，将这些语句集合在一起，称为一个事务
 *  使用事务可以完成退回的功能，保证业务逻辑的正确性
 *  事务四大特性（ACID）：
    
        原子性：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么全部不执行1
        一致性：几个并行执行的事务，其执行结果与无论以哪种方式串行执行事务，都一致
        隔离性：事务的执行不受到其他事物的干扰
        持久性：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障
 *  要求：表的类型必须是innodb或者bdb类型，才可使用事务
 *  修改表的引擎
    
        alter table tbname engine=innodb;
 *  事务语句：
    
        开始begin;
        提交commit;
        回滚rollback;
 *  示例：
    
        步骤1：打开两个终端，连接mysql，使用同一个数据库，操作同一张表
        终端1：
        select * from students;
        ------------------------
        终端2：
        begin;
        insert into students(sname) values('张飞');
        
        
        
        步骤2
        终端1：
        select * from students;
        
        
        
        步骤3
        终端2：
        commit;
        ------------------------
        终端1：
        select * from students;

## 08.索引 ##

*  在图书馆中，如何找到一本书？
 *  当数据库中，数据量越大，查找数据越慢
 *  索引能提高查找效率
 *  主键和唯一索引，都是索引，可以提高查询速度
 *  选择列的数据类型：
    
        越小的数据类型越好；
        简单的数据类型更好；
        尽量避免NULL；
 *  索引分为单列索引和组合索引
    
        单列索引，即一个索引只包含单个列，一个表可以有多个单列索引
        组合索引，即一个索引包含多个列
 *  查看索引
    
        show index from tbname;
 *  创建索引：
    
        create index indexname on tbname(字段(length));
 *  删除索引
    
        drop index indexname on tbname;
 *  缺点：
    
        降低更新表的速度，更新表的同时索引文件也被更新
        索引文件占据磁盘空间