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<d:\a.sql】
         >注意:不要打分号,不要登录mysql,直接在cmd下运行

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

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

上一篇:mysql数据库的基本操作(主键、自增、唯一键、索引、范式、蠕虫复制、主键冲突、5大子句)


下一篇:树莓派与windows互传文件