mysql数据库的基本操作(连接查询、外键、合并结果集、子查询、视图、备份还原)

目录：
1.连接查询
2.外键
3.外键约束
4.外键约束模式
5.联合查询(合并结果集)
6.子查询
7.标量子查询
8.列子查询
9.行子查询
10.表子查询
11.exists子查询
12.视图
13.查看视图    
14.视图的使用    
15.视图的修改和删除
16.视图的意义
17.视图数据操作
18.视图--算法
19.数据表备份
20.单表数据备份    
21.SQL备份
22.增量备份

1.连接查询     <--返回目录
    * SQL连接查询分类：内连接，外连接，自然连接，交叉连接
    
    交叉连接
    * 交叉连接：cross join,从一张表中循环取出一条记录，每条记录都去另外一张表进行匹配，
        最终形成的结果叫：【笛卡尔积】
        select * from 左表 cross join 右表;<==>select * from 左表, 右表;
    
    * 实际查询时不会用交叉连接
    
    内连接
    * [inner] join:从左表中取出一条记录，取右表中与所有的记录进行匹配，匹配必须
        是某个在左表中与右表中相同最终才会保留结果，否则不保留。
        
    * 例子：
        select s.字段名,c.字段名
        from tb_stu as s
        inner join tb_class as c
        on s.id = c.id;

    外连接
    * 外连接：以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接；
        不能匹配on条件，其他表的字段置空null

    * 例子：
        select s.字段名,c.字段名
        from tb_stu as s
        left/right outer join tb_class as c
        on s.id = c.id;

    5.自然连接
    * natural join 自然内连接
    * natural left/right join 自然外连接
    * 自然连接字段使用同名字段作为连接条件；连接之后会合并同名字段。
    
2.外键     <--返回目录
    * 外键：foreign key,如果一张表中有一个字段引用另外一张表的主键，那么将该表字段称为外键；
        一张表可以有多个外键。
    * 创建表的时候增加外键：在所有的表字段之后，使用foreign key(外键字段) references 外部表(主键字段)
    
    * 例子：
        create table tb_class(   -- 主表
            id int primary key AUTO_INCREMENT comment ‘主键‘,    
            cname varchar(20) comment ‘班级名称‘
        )charset utf8;
        insert into tb_class values(null,‘java一班‘),(null,‘java二班‘),(null,‘java三班‘);
        select * from tb_class;

        create table tb_stu(  -- 从表
            id int primary key auto_increment,
            uname varchar(20),
            gender varchar(10),
            cid int,
            foreign key(cid) REFERENCES tb_class(id)    -- 定义外键
        )charset utf8;
        
        show create table tb_stu;======>结果：MyISAM不支持外键，所有查询创建语句中没有外键，只有索引
        CREATE TABLE `tb_stu` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `uname` varchar(20) DEFAULT NULL,
          `gender` varchar(10) DEFAULT NULL,
          `cid` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `cid` (`cid`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8
        
    * 修改表时指定外键： constraint [k?n?stre?nt]
    alter table tb_stu add [constraint fk_stu_cid] foreign key(cid) references tb_class(id)；

    * 删除外键
        - 外键不可更改，只能删除后新增
            alter table tb_stu drop foreign key 外键名;
            如果只是删除外键，索引key还存在

3.外键约束     <--返回目录
    * 外键默认的作用有两点：一个对父表(主表)，一个对子表(从表，即外键字段所在的表)
    * 对子表约束：子表数据进行写操作时，如果对于的外键字段在父表中找不到对应的匹配，那么操作或失败(约束子表数据操作)
    * 对父表约束：对父表数据进行写操作(删、改都必须涉及主键本身)，如果对应的主键在子表中已经被数据引用，
        那么不允许操作。

    * 外键条件：
        外键要存在，首先必须保证表的存储引擎是innodb；如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果。
        外键字段的字段类型(类类型)必须与父表的主键类型完全一致

    * 我使用mysql 5.1，默认ENGINE=MyISAM，所以创建外键无效

4.外键约束模式     <--返回目录
    * 所谓外键约束：就是指外键的作用
    * 之前所讲的外键作用，是默认的作用；其实可以通过对外键的需求，进行定制操作
    * 外键约束有三种约束模式：都是针对父表的约束
        district：严格模式，父表不能删除或更新一个已经被子表数据引用的记录
        cascade：级联模式，父表的操作，对应子表关联的数据也跟着操作
        set null：置空模式，父表操作之后，子表对应的数据(外键字段)被置空
    * 通常一个合理的做法(约束模式)：删除的时候子表置空，更新的时候子表级联操作
        语法：constraint 约束名 foreign key(外键字段) references 父表(主键字段) on delete set null on update cascade;
    * 外键置空的前提条件：外键字段允许为空，如果不满足条件，外键无法创建
    
    * 外键虽然很强大，能够进行各种约束；但是对于PHP来讲，外键的约束降低了PHP对数据的可控性；
        通常在实际开发中，很少使用外键来处理
    
    * 创建外键约束
        CREATE TABLE `tb_class` (     -- 主表
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
          `cname` varchar(20) DEFAULT NULL COMMENT ‘班级名称‘,
          PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;


        create table tb_stu(      -- 从表
            id int primary key AUTO_INCREMENT,
            sname varchar(20) comment ‘姓名‘,
            cid int,
            constraint fk_stu_class foreign key(cid) REFERENCES tb_class(id)
        )engine=INNODB charset=utf8;    
        -- 引擎使用INNODB创建表失败  原因是主表ENGINE=MyISAM
        Error Code : 1005
        Can‘t create table ‘test1.tb_stu‘ (errno: 150)

5.联合查询(合并结果集)     <--返回目录
    * 多条select语句构成，每一条select语句获取的字段数必须严格一致，但与列类型无关
    * 语法：
        select 语句1
        union [union选项]
        select 语句2...

    * union选项
        all:保留所有(不管重复)
        distinct:去重，默认
    
    * 联合查询的意义：
        1）查询同一张表，但是需求不同：如查询学生信息，男生年龄升序，女生年龄降序；
        2）多表查询：多张表的结构是完全一样的，保存的数据(结构)也是一样的
        
    * 例子：查询学生信息，男生年龄升序，女生年龄降序    
        drop table if exists tb_stu;
        create table tb_stu(
            id int primary key AUTO_INCREMENT,
            sname varchar(20) comment ‘姓名‘,
            gender varchar(20),
            age int
        ) charset=utf8;

        insert into tb_stu values(null,‘张三1‘,‘男‘,floor(rand()*20+20));-- 多次执行下面两句，插入多条记录
        insert into tb_stu values(null,‘张三1‘,‘女‘,floor(rand()*20+20));

        -- 联合查询里面使用order by需要满足：select语句加(),并且要有limit
        (select * from tb_stu where gender = ‘男‘ order by age asc limit 99999)
        union
        (select * from tb_stu where gender = ‘女‘ order by age desc limit 99999)

6.子查询     <--返回目录
    * 子查询：sub query，查询是在某个查询结果之上进行的（一条select语句内部包含了另外一条select语句）
    * 子查询分类：
        按位置分类：
            from 子查询：子查询出现在from之后
            where 子查询
            exists 子查询
        * 按结果分类：根据子查询得到的数据进行分类(任何一个查询得到的结果集都可以理解为二维表)
            标量子查询：子查询得到的结果是一行一列
            列子查询：子查询得到的结果是一行多列
            行子查询：子查询得到的结果是多行多列
                上面几个出现的位置都是在where之后
            表子查询：子查询得到的结果是是多行多列，出现的位置是在from之后    
            
7.标量子查询     <--返回目录
    * 需求：班级名字为‘java一班‘(并不知该班对应的id)，获取该班所有学生信息    
        CREATE TABLE `tb_class` (
          `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键‘,
          `cname` varchar(20) DEFAULT NULL COMMENT ‘班级名称‘,
          PRIMARY KEY (`id`)
        ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

        drop table tb_stu;
        create table tb_stu(
            id int primary key AUTO_INCREMENT,
            sname varchar(20) comment ‘姓名‘,
            cid int
        ) charset=utf8;

        insert into tb_stu values(null,‘张三1‘,floor(rand()*3+1)); --多次执行该语句

        select * from tb_stu where cid = (select id from tb_class where cname = ‘java一班‘);
        
8.列子查询     <--返回目录
    * 查询java一班和java二班所有学生信息
        select * from tb_stu where cid in (select id from tb_class where cname in(‘java一班‘,‘java二班‘));
    * any,some,all
        =any  等价于 in
    
9.行子查询     <--返回目录
    * 查询年龄最大，身高最高的的学生
        drop table tb_stu;
        create table tb_stu(
            id int primary key AUTO_INCREMENT,
            sname varchar(20) comment ‘姓名‘,
            age int comment ‘年龄‘,
            height int comment ‘身高‘
        ) charset=utf8;
        insert into tb_stu values(null,‘张三1‘,20,180),(null,‘张三2‘,30,170),(null,‘张三3‘,50,190),(null,‘张三4‘,10,160);
        -- 方法一：
        select * from tb_stu
        where age = (select max(age) from tb_stu)
        and height = (select max(height) from tb_stu);
        -- 方法二：构造行元素(age,height)
        select * from tb_stu
        where (age,height) = (select max(age),max(height) from tb_stu);
        
10.表子查询     <--返回目录
    * 需求：查询每个班身高最高的学生信息
        * 方法一：
        drop table tb_stu;
        create table tb_stu(
            id int primary key AUTO_INCREMENT,
            sname varchar(20) comment ‘姓名‘,
            age int comment ‘年龄‘,
            height int comment ‘身高‘,
            cid int comment ‘班级id‘
        ) charset=utf8;
        insert into tb_stu values(null,‘张三1‘,20,180,2),(null,‘张三2‘,30,170,1),(null,‘张三3‘,50,190,3),(null,‘张三4‘,10,160,2);
        insert into tb_stu values(null,‘张三1‘,20,120,2),(null,‘张三2‘,30,150,1),(null,‘张三3‘,50,130,3),(null,‘张三4‘,10,150,2);
        select * from tb_stu;

        select * from tb_stu
        where (cid,height) = any (select cid,max(height) from tb_stu group by cid);
            
        * 方法二：    
        select * from (select * from tb_stu order by height desc) as s group by cid;
        
11.exists子查询     <--返回目录
    * exists:是否存在的意思。exists子查询就是用来判断某些条件是否满足(跨表),exists是接在where之后，
        exists返回的结果只有0或1
    * 例子：查询所有的学习信息，前提是班级存在    
        select * from tb_stu where exists(select * from tb_class); -- exists(select * from tb_class)结果是1
        select * from tb_stu where exists(select * from tb_class where id=1000); -- exists(select * from tb_class where id=1000)结果是0
    
12.视图     <--返回目录
    * 视图：view，是一种有结构(有行有列)但是没有结果(结构中不真实放数据)的虚拟表，虚拟表的结构来源不是自己定义，
        而是从对应的【基表】中产生(视图的数据来源)
    * 创建视图的语法：    
        create view 视图名 as select语句

    * 例子：    
    create view v1 as select * from emp;
    create view v2 as select * from emp left join dept where emp.id = dept.id; -- 有重名字段，创建失败；可以对重名字段重命名

13.查看视图     <--返回目录
    * show tables;   -- 创建的视图也会显示
    * desc 视图名;
    * show create table/view 视图名;

14.视图的使用     <--返回目录
    * select * from 视图名;
    * 视图的执行：本质就是执行封装的select语句

15.视图的修改和删除     <--返回目录
    * 视图本身不可修改，但是视图的来源是可以修改的；修改视图就是修改视图封装的select语句
        alter view 视图名 as 新select语句;
    * 删除视图(视图不存储数据，可以删除；但是不建议随意删除)
        drop view 视图名;

16.视图的意义     <--返回目录
    1）视图可以复用SQL语句：将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作。
    2）数据安全：视图操作主要是针对查询的，如果对视图结构进行处理(删除),不会影响基本数据
    3）视图往往是在大项目中使用，而是多系统使用；封装基表数据，对外提供有用的数据，隐藏关键的数据，数据安全
    4）视图可以对外提供友好型：不同的视图提供不同的数据，对外好像专门设计
    5）视图可以更好(容易)进行权限控制
    
17.视图数据操作     <--返回目录
    * 视图新增数据
        不能向多表视图(视图来源于多张基表)新增数据
        单表视图是可以新增数据的，并且会影响基表(即新增的数据插入到基表)
    * 注意：如果视图中剔除基表的某个字段，但是该字段not null且没有显示指定默认值，则出错
        
    * 视图删除视图
        多表视图不能执行删除记录操作
        单表视图可以执行删除记录操作 delete from 视图名 [where语句];
        
    * 更新数据
        多表/单表视图都可以更新数据
        例子：    
        create view my_v4 as select * from tb_stu where age >30 with check option;
        -- with check option:视图更新数据后仍然要满足age>30
        注意：需要更新的数据都是视图中有的数据
        
18.视图--算法     <--返回目录
    * 视图算法
        undefined:未定义，默认的，这不是一种实际的使用算法，只是告诉系统没有定义算法，系统自己看着办
        temptable:临时表算法，系统应该先执行视图的select语句，后执行外部查询语句
        merge:合并算法，系统先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行。
    
    * 视图算法选择：如果视图的select语句中会包含一个查询子句(五子句)，而且很有可能顺序比外部的查询语句要靠后，
        一定要使用算法temptable，其他情况可以不用指定，使用默认即可
    
    * 创建视图时指定算法    
        create algorithm=temptable view my_v6 as select语句;    
        
19.数据表备份     <--返回目录
    * 数据备份还原的方式：数据表备份、单表数据备份、SQL备份、增量备份
    
    * mysql存储引擎：免费的两种，innodb和myisam；其他的收费    
    * 查看mysql的版本：select @@version;    show  variables like ‘version‘;
        
    * innodb和myisam的数据存储方式：
        innodb：只有表结构，数据全部存储到ibdata1文件中
        myisam:数据、表和索引分开存储  （.frm结构文件  .MYD数据  .MYI索引）
                                            frame         data      index
    * 数据表备份：不需要通过SQL来备份，直接进入到数据库文件夹复制对应的表结构以及数据文件，以后还原的时候，
        直接将备份的内容放进去即可。
        - 数据备份还原有前提条件：根据不同的存储引擎有不同的区别    
        - 这种文件备份通常【适用于myisam存储引擎】，直接复制这三个文件即可
        
20.单表数据备份         <--返回目录
    * 每次只能备份一张表，只能备份数据(表结构不能备份)；
        通常的使用：将表的数据进行导出到文件

    * select */字段列表 into outfile 文件路径 [fields 字段处理 lines 行处理] from 数据源;  -- 前提是外部文件不存在。
        select * into outfile ‘D:/temp/sql/a.txt‘ from emp;  -- 注意：temp/sql目录必须存在，而a.txt不存在

    * fields 字段处理:
        enclosed by:字段使用什么内容包裹，默认是空字符串
        terminated by:字段以什么结束，默认是 "\t",即tab键
        escaped by:特殊符号用什么方式处理，默认是"\\"，即反斜杠转义
        
    * lines 行处理：
        starting by:每行以什么开始，默认是空字符串
        terminated by:每行以什么结束，默认是"\r\n" ，即换行符

    * 数据还原：将一个在外部保存的数据重新恢复到表中
    load data infile 文件路径 into table 表名(字段列表) fields 字段处理 lines 行处理;
    
    
    * 例子：
        备份：
        select * into outfile ‘D:/temp/sql/a.txt‘ fields enclosed by ‘"‘ terminated by ‘|‘ lines starting by ‘start:‘ from emp;
        结果：
            start:"1"|"张三"|"10"
            start:"2"|"张三"|"20"
            start:"3"|"张三"|"40"
        还原：（数据库utf8,txt也是utf8,但是还原后有乱码）    
        load data infile ‘D:/temp/sql/a.txt‘ into table emp fields enclosed by ‘"‘ terminated by ‘|‘ lines starting by ‘start:‘;
    
21.SQL备份     <--返回目录
    * SQL备份：系统会对表结构及数据进行处理，变成对应的SQL语句，还原时只要执行SQL指令即可；
    
    1）数据库导出sql脚本（备份）back-up
         >mysqldump -u密码 -p密码 数据库名 [表名列表]>生成的脚本文件的路径（文件名后缀为.sql）
         >例如：【mysqldump -uroot -p123 mydb3>d:\a.sql】   (没登录前使用该命令)
         >注意：不要打分号，不要登录mysql，直接在cmd下运行
         >注意：生成的脚本文件中不包含create database语句

    2）执行sql脚本（恢复）  restore[r??st?:(r)]
        第一种方式：（未登录状态）
         >mysql -u用户名 -p密码 数据库<脚本文件路径
         >例如：
              * 先删除mydb1，再重新创建mydb1库
              *【mysql -uroot -p123 mydb1         >注意：不要打分号，不要登录mysql，直接在cmd下运行

        第二种方式：
         >登录mysql
         >source sql脚本路径
         >例如：
             *先删除mydb1库，在重新创建mydb1
             *切换到mydb1库
             *【source c:\mydb1.sql】
    
22.增量备份     <--返回目录
    * 不是针对数据或者SQL指令进行备份，是针对mysql服务器的日志文件进行备份
    * 指定时间段开始进行备份，备份数据不会重复，而且所有的操作都会备份，大项目都用增量备份。
---