foreign key mysql 备份 权限

1、外键(foreign key)

  1、定义 ：让当前表字段的值在另一个表的范围内选择

  2、语法

    foreign key(参考字段名)

    references 主表(被参考字段名)

    on delete 级联动作

    on update 级联动作

  3、使用规则

    1、主表、从表字段数据类型要一致

    2、主表被参考字段 ：主键

  4、示例

    表1、缴费信息表(财务)

      id  姓名    班级   缴费金额

       1  唐伯虎  AID06     300

       2  点秋香  AID06     260

       3  祝枝山  AID06     250

 

    表2、学生信息表(班主任)

      id   姓名   缴费金额

       1   唐伯虎   300

       2   点秋香   260

       3 XXXXXXXXXXXXXXXXX

    1、创建表

     表1(主表)：

       create table jftab(

       id int primary key,

       name varchar(15),

       class char(5),

       money int

       );

 

       insert into jftab values

       (1,"唐伯虎","AID06",300),

       (2,"点秋香","AID06",260),

       (3,"祝枝山","AID06",250);

     表2(从表)、

       create table bjtab(

       stu_id int,

       name varchar(15),

       money int,

       foreign key(stu_id) references jftab(id)

       on delete cascade

       on update cascade

       );

       insert into bjtab values

       (1,"唐伯虎",300),(2,"点秋香",260);

  5、删除外键

    alter table 表名 drop foreign key 外键名;

    外键名 ：show create table 表名;

  6、级联动作

    1、cascade

      数据级联删除、更新(参考字段)

    2、restrict(默认)

      从表有相关联记录,不允许主表操作

    3、set null

      主表删除、更新,从表相关联记录字段值为NULL

  7、已有表添加外键

    alter table 表名 add

    foreign key(参考字段) references 主表(被参考字段)

    on delete ...

    on update ...

2、表的复制

  1、语法

    create table 表名 select .. from 表名 where 条件;

  2、示例

    1、复制MOSHOU.sanguo表的全部记录和字段,sanguo2

      create table sanguo2

      select * from MOSHOU.sanguo;

    2、复制MOSHOU.sanguo表的前3条记录,sanguo3

      create table sanguo3

      select * from MOSHOU.sanguo limit 3;

    3、复制MOSHOU.sanguo表的id,name,country三个字段的前5条记录,sanguo4

      create table sanguo4

      select id,name,country from MOSHOU.sanguo limit 5;

  3、复制表结构

    create table 表名 select * from 表名 where false;

    1、复制 jftab 的表结构,jftab2

      desc jftab2;

      desc jftab;

  4、注意

    复制表的时候不会把原表的 键(key) 属性复制过来

3、嵌套查询(子查询)

  1、定义 ：把内层的查询结果作为外层的查询条件

  2、语法格式

    select ... from 表名 where 条件(select ....);

  3、示例

    1、把攻击值小于平均攻击值的英雄名字和攻击值显示出来

      1、先计算平均值

        select avg(gongji) from MOSHOU.sanguo;

      2、找到 < 平均值

        select name,gongji from MOSHOU.sanguo

         where gongji<平均值;

      子查询：

        select name,gongji from MOSHOU.sanguo

        where

        gongji<(select avg(gongji) from MOSHOU.sanguo);

    2、找出每个国家攻击力最高的英雄的名字和攻击值

      

       select name,gongji from sanguo

       where

       gongji in(select max(gongji) from sanguo group by country);

     ## 无BUG

       select name,gongji from sanguo

       where

       (country,gongji) in

       (select country,max(gongji) from sanguo group by country);

       蜀国：100

       魏国：200

       吴国：300

       吴国：三个英雄  300  200 100

4、多表查询

  1、两种方式

   1、select 字段名列表 from 表名列表; (笛卡尔积)

    t1 : name -> "A1"  "A2"  "A3"

    t2 : name -> "B1"  "B2"

    select * from t1,t2;

    +------+-------+

    | name | name2 |

    +------+-------+

    | A1   | B1    |

    | A1   | B2    |

    | A2   | B1    |

    | A2   | B2    |

    | A3   | B1    |

    | A3   | B2    |

    +------+-------+

   2、... where 条件;

  2、练习

    1、显示省和市的详细信息

      河北省  石家庄市

      河北省  廊坊市

      湖北省  武汉市

      select sheng.s_name,city.c_name from sheng,city

      where

      sheng.s_id=city.cfather_id;

    2、显示省市县详细信息

      select sheng.s_name as sheng,city.c_name as city,xian.x_name as xian from sheng,city,xian

      where

      sheng.s_id=city.cfather_id and

      city.c_id=xian.xfather_id;

5、连接查询

  1、内连接

    1、语法格式

      select 字段名 from

      表1 inner join 表2 on 条件

      inner join 表3 on 条件;

    2、显示省市详细信息

      select sheng.s_name,city.c_name from sheng

      inner join city on sheng.s_id=city.cfather_id;

    3、显示省市县详细信息

     select sheng.s_name,city.c_name,xian.x_name from

     sheng inner join city

     on sheng.s_id=city.cfather_id

     inner join xian

     on city.c_id=xian.xfather_id;

  2、外连接

    1、左连接

      1、以 左表 为主显示查询结果

      2、select 字段名 from

         表1 left join 表2 on 条件

          left join 表3 on 条件;

      3、显示省市详细信息

        select sheng.s_name,city.c_name from sheng

         left join city

         on sheng.s_id=city.cfather_id;

    2、右连接

      用法同左连接,以右表为主显示查询结果

6、数据备份(mysqldump,在Linux终端中操作)

  1、命令格式

    mysqldump -u用户名 -p 源库名 > ~/***.sql

  2、源库名的表示方式

    --all-databases  备份所有库

    库名             备份单个库

    -B 库1 库2 库3   备份多个库

    库名 表1 表2 表3 备份指定库的多张表

  3、示例

    1、备份所有库,放到 mydata 目录下:all.sql

      mysqldump -uroot -p --all-databases > all.sql

    2、备份 db4 库中的 sheng city xian 三张表,scx.sql

      mysqldump -uroot -p db4 sheng city xian > scx.sql

    3、备份 MOSHOU 和 db4 库, md.sql

      mysqldump -uroot -p -B MOSHOU db4 > md.sql

    4、备份 db4 库, db4.sql

      mysqldump -uroot -p db4 > db4.sql

7、数据恢复

  1、命令格式(Linux终端)

    mysql -uroot -p 目标库名 < ***.sql

  2、从所有库备份中恢复某一个库(--one-database)

    mysql -uroot -p --one-database 目标库名 < all.sql

    mysql -uroot -p --one-database db4 < all.sql

  3、示例

    1、在db4.sheng新增一条记录   

    2、在db4中新建一张表 t888,里面插入1条记录

    3、从db4.sql中恢复db4库

  4、注意

    1、恢复库时如果恢复到原库会将表中数据覆盖,新增表不会删除

    2、数据恢复时如果恢复的库不存在,则必须先创建空库

8、MySQL的用户账户管理

  1、开启MySQL远程连接

    1、sudo -i

    2、cd /etc/mysql/mysql.conf.d/

    3、subl mysqld.cnf

       #bind-address = 127.0.0.1  ctrl + s保存

    4、/etc/init.d/mysql restart

  2、添加授权用户

    1、用root用户登录mysql

      mysql -uroot -p123456

    2、授权

      grant 权限列表 on 库.表 to "用户名"@"%"

      identified by "密码" with grant option;

     

      权限列表：all privileges 、select 、insert

      库.表 ： *.*  所有库的所有表

  3、示例

    1、添加授权用户tiger,密码123,对所有库的所有表有所有权限

      grant all privileges on *.* to "tiger"@"%" identified by "123" with grant option;

    2、添加用户rabbit,对db4库有所有权限  

      grant all privileges on db4.* to "rabbit"@"%" identified by "123" with grant option;