mysql md 123456_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;