今日是MySQL的第四篇,難度會稍微加大，加油！ 開始吧！ 1、外鍵(foreign key) 1.定義：讓當前表欄位的值在另一個表的範圍內選擇 2.語法： foreign key(參考欄位名) references 主表(被參考欄位名) on delete 級聯動作 on update 級聯動作 ...
今日是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;
本小結完！
