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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/116971.html
標籤：MySQL