MySQL修炼之路四

た入场券 2022-12-28 06:22 220阅读 0赞

# 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;