认识mysql(4)
今日是mysql的第四篇,难度会稍微加大，加油！
开始吧！
1、外键(foreign key)
1.定义：让当前表字段的值在另一个表的范围内选择
2.语法：
foreign key(参考字段名)
references 主表(被参考字段名)
on delete 级联动作
on update 级联动作
以上的语法连在一起看
3.使用规则
主表、从表字段数据类型要一致
主表被参考字段：主键
4.示例
表1、缴费信息表(财务)
id 姓名 班级 缴费金额
1 唐伯虎 ad06 300
2 点秋香 ad06 200
3 祝枝山 ad06 100
表2、学生信息表(班主任)
id 姓名 缴费金额
1 唐伯虎 300
2 点秋香 260
3 xxx xxx
....................
创建表
表1(主表)
create table jftab(
id int primary key,
name varchar(15),
class char(5),
money int
);
插入数据
insert into jftab values
(1,"唐伯虎","ad06",300),
(2,"点秋香","ad06",260),
(3,"祝枝山","ad06",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 frreign key 外键名;
查看外键:show create table 表名;
6、级联动作
cascade
数据级联删除、更新(参考字段)
restrict(默认)
从表有相关联记录，不允许主表操作
set null
主表删除、更新，从表相关联记录字段值为null
7、已有表添加外键
alter table 表名 add for foreeign key(参考字段) references 主表(被参考字段)
on delete ...
on update ...
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;
复制表结构
create table 表名 select * from 表名 where false;
注意：
复制表的时候不会把原表的键(key)属性复制过来
3、嵌套查询
定义 ：把内层的查询结果作为外层的查询条件
语法格式
select ... from 表名 where 条件(select ....);
示例
把攻击值小于平均攻击值的英雄名字和攻击值显示出来
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);
4、多表查询
select 字段名列表 from 表名列表; (笛卡尔积)
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.语法格式
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、外连接
以 左表 为主显示查询结果
select 字段名 from 表1 left join 表2 on 条件
left join 表3 on 条件;
显示省市详细信息
select sheng.s_name,city.c_name from sheng
left join city
on sheng.s_id=city.cfather_id;
右连接
用法同左连接,以右表为主显示查询结果
6、数据备份(mysqldump,在linux终端中操作)
命令格式
mysqldump -u用户名 -p 源库名 > ~>***.sql
源库名的表示方式
--all-databases 备份所有库
-b 库1 库2 库3 备份多个库
库名 表1 表2 表3 备份指定库的多张表
示例:
备份所有库,放到 mydata 目录下:all.sql
mysqldump -uroot -p --all-databases > all.sql
备份 db4 库中的 sheng city xian 三张表,scx.sql
mysqldump -uroot -p db4 sheng city xian > scx.sql
备份 moshou 和 db4 库, md.sql
mysqldump -uroot -p -b moshou db4 > md.sql
备份 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
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、添加授权用户
用root用户登录mysql
mysql -uroot -p123456
授权:
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;
本小结完！