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 XXXXXXXXXXX
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,'唐伯虎', "AID06",300),
(2,"点秋香", "AID06",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(参考字段) referencees 主表(被参考字段)
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
create table jftab2 select * from jftab where false;
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. 找出每个国家攻击力最高的英雄的名字和攻击值
BUG: select name,gongji from MOSHOU.sanguo
where
gongji in(select max(gongji) from sanguo group by country);
## 无BUG
select name,gongji from MOSHOU.sanguo
where
(country,gongji) in(select country,max(gongji) from sanguo group by country);
4. 多表查询
1. 两种方式
1. select 字段名列表 from 表名列表;(笛卡尔积)
t1: name -> "A1" "A2" "A3"
t2: name -> "B1" "B2"
select * from t1,t2;
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,city.c_name,xian.x_name 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 as sheng,city.c_name as city
from sheng left join city
on sheng.s_id=city.cfather_id;
4. 显示省市县的详细信息
select sheng.s_name as sheng,city.c_name as city,xian.x_name as xian
from sheng left join city
on sheng.s_id=city.cfather_id
left join xian
on city.c_id = xian.xfather_id;
2. 右连接
1. 以右表为主显示查询结果
2. select 字段名 from
表1 right join 表2 on 条件
right join 表3 on 条件;
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
7. 数据恢复
1. 命令格式(Linux终端)
mysql -uroot -p 目标库名 < ***.sql
2. 从所有库备份中恢复某个库(--one-database)
mysql -uroot -p --one-database 目标库名 < all.sql
3. 示例
1. 在db4.sheng 新增一条记录
2. 在db4中新建一张表 t888
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;
with grant option # 这句表示新添加的用户名有往下授权的权限
权限列表：all privileges、select、insert
库.表 ：*.* 所有库的所有表
3. 示例
1. 添加授权用户tiger，密码123，对所有库的所有表有所有权限
grant all privileges on *.* to "zcc"@"%"
identified by "123" with grant option;
2. 添加用户rabbit,对db4库有所有权限
grant all privileges on db4.* to "rabbit"@"%"
idetified by "123" with grant option;
