MySQL高级之外键、表关联、数据导入及导出
文章目錄
- MySQL高級
- 外鍵(foreign key)
- 嵌套查詢(子查詢)
- 多表查詢
- 連接查詢
- 數據導入
- 數據導出
- 表的復制
- 鎖(自動加鎖和釋放鎖)
- MySQL高級-重點掌握
- 外鍵
- 嵌套查詢(子查詢)
- 多表查詢
- 連接查詢
- 鎖
- 數據導入
- 索引
- 面試題
MySQL高級
外鍵(foreign key)
-
定義
讓當前表字段的值在另一個表的范圍內選擇(foreign key 功能 : 建立表與表之間的某種約束的關系,由于這種關系的存在,能夠讓表與表之間的數據,更加的完整,關連性更強)
-
語法
foreign key(參考字段名) references 主表(被參考字段名) on delete 級聯動作 on update 級聯動作 -
使用規則
1、主表、從表字段數據類型要一致
2、主表被參考字段 :KEY的一種,一般為主鍵
- 示例
表1、繳費信息表(財務)
id 姓名 班級 繳費金額 1 唐伯虎 AID19 300 2 點秋香 AID19 300 3 祝枝山 AID19 300表2、學生信息表(班主任) – 做外鍵關聯
stu_id 姓名 繳費金額1 唐伯虎 3002 點秋香 300- 刪除外鍵
- 創建外鍵
- 級聯動作
- 已有表添加外鍵
嵌套查詢(子查詢)
定義
把內層的查詢結果作為外層的查詢條件
語法格式
select ... from 表名 where 條件(select ....);示例
1、把攻擊值小于平均攻擊值的英雄名字和攻擊值顯示出來select name,attack from sanguo where attack<(select avg(attack) from sanguo) 2、找出每個國家攻擊力最高的英雄的名字和攻擊值(子查詢)select name,attack from sanguo where (country,attack) in (select country ,max(attack) from sanguo group by country);多表查詢
sql腳本資料:join_query.sql
mysql -uroot -p123456 mysql>source /home/tarena/join_query.sql create database if not exists db1 character set utf8; use db1;create table if not exists province( id int primary key auto_increment, pid int, pname varchar(15) )default charset=utf8;insert into province values (1, 130000, '河北省'), (2, 140000, '陜西省'), (3, 150000, '四川省'), (4, 160000, '廣東省'), (5, 170000, '山東省'), (6, 180000, '湖北省'), (7, 190000, '河南省'), (8, 200000, '海南省'), (9, 200001, '云南省'), (10,200002,'山西省');create table if not exists city( id int primary key auto_increment, cid int, cname varchar(15), cp_id int )default charset=utf8;insert into city values (1, 131100, '石家莊市', 130000), (2, 131101, '滄州市', 130000), (3, 131102, '廊坊市', 130000), (4, 131103, '西安市', 140000), (5, 131104, '成都市', 150000), (6, 131105, '重慶市', 150000), (7, 131106, '廣州市', 160000), (8, 131107, '濟南市', 170000), (9, 131108, '武漢市', 180000), (10,131109, '鄭州市', 190000), (11,131110, '北京市', 320000), (12,131111, '天津市', 320000), (13,131112, '上海市', 320000), (14,131113, '哈爾濱', 320001), (15,131114, '雄安新區', 320002);create table if not exists county( id int primary key auto_increment, coid int, coname varchar(15), copid int )default charset=utf8;insert into county values (1, 132100, '正定縣', 131100), (2, 132102, '浦東新區', 131112), (3, 132103, '武昌區', 131108), (4, 132104, '哈哈', 131115), (5, 132105, '安新縣', 131114), (6, 132106, '容城縣', 131114), (7, 132107, '雄縣', 131114), (8, 132108, '嘎嘎', 131115);- 笛卡爾積(不推薦使用)
- 多表查詢
- 示例
連接查詢
- 內連接(結果同多表查詢,顯示匹配到的記錄)(相比笛卡爾積優先使用)
- 左外連接
以 左表 為主顯示查詢結果
select 字段名 from 表1 left join 表2 on 條件 left join 表3 on 條件; eg1 : 顯示 省 市 詳細信息(要求省全部顯示)select province.pname,city.cname from province left join city on province.pid=city.cp_id;- 右外連接
用法同左連接,以右表為主顯示查詢結果
select 字段名 from 表1 right join 表2 on 條件 right join 表3 on 條件; eg1 : 顯示 省 市 詳細信息(要求市全部顯示)select province.pname,city.cname from province right join city on province.pid=city.cp_id;- 連接查詢中on和where的區別:
- 在使用連接查詢時,on and和on where條件的區別如下:
1、on條件是在生成臨時表時使用的條件,它不管on中的條件是否為真,都會返回(左邊或右邊)表中符合條件的記錄。
2、where條件是在臨時表生成好后,再對臨時表進行過濾的條件。這時已經沒有left
join(right join)的含義(必須返回左(右)邊表的記錄)了,條件不為真的就全部過濾掉,on后的條件用來生成左右表關聯的臨時表,where后的條件對臨時表中的記錄進行過濾。
3.在使用內連接時,on and 與on where 沒有區別,都是對表進行過濾操作
數據導入
掌握大體步驟
source 文件名.sql
作用
把文件系統的內容導入到數據庫中
語法(方式一)
load data infile “文件名”
into table 表名
fields terminated by “分隔符”
lines terminated by “\n”
示例
scoretable.csv文件導入到數據庫db2的表
語法(方式二)
source 文件名.sql
數據導出
作用
將數據庫中表的記錄保存到系統文件里
語法格式
select … from 表名
into outfile “文件名”
fields terminated by “分隔符”
lines terminated by “分隔符”;
練習
1、把sanguo表中英雄的姓名、攻擊值和國家三個字段導出來,放到 sanguo.csv中select name,attack,country from sanguo into outfile "/var/lib/mysql-files/sanguo.csv" fields terminated by "," lines terminated by "\n"; 2、將mysql庫下的user表中的 user、host兩個字段的值導出到 user2.txt,將其存放在數據庫目錄下注意
1、導出的內容由SQL查詢語句決定 2、執行導出命令時路徑必須指定在對應的數據庫目錄下表的復制
1、表能根據實際需求復制數據
2、復制表時不會把KEY屬性復制過來
語法
create table 表名 select 查詢命令;練習
1、復制sanguo表的全部記錄和字段,sanguo2create table sanguo2 select * from country.sanguo 2、復制sanguo表的 id,name,country 三個字段的前3條記錄,sanguo4create table sanguo4 select id,name,country from country.sanguo limit 3;注意
常規分表套路:
用戶ID int % 表數量
用戶名ASCII%表數量
經典案例:用戶表分表
復制表的時候不會把原有表的 KEY 屬性復制過來
復制表結構
create table 表名 select 查詢命令 where false;鎖(自動加鎖和釋放鎖)
全程重點,理論和鎖分類及特點
點贊并發解決方案->(mvcc多版本控制):
?
目的
解決客戶端并發訪問的沖突問題
鎖類型分類
讀鎖(共享鎖):select 加讀鎖之后別人不能更改表記錄,但可以進行查詢 寫鎖(互斥鎖、排他鎖):加寫鎖之后別人不能查、不能改鎖粒度分類
表級鎖 :myisam
行級鎖 :innodb
MySQL高級-重點掌握
外鍵
原理
讓當前表字段的值在另一張表的范圍內去選擇使用規則
1、數據類型要一致 2、主表被參考字段必須為KEY的一種 : PRI級聯動作
1、cascade : 刪除 更新同步(被參考字段) 2、restrict(默認) : 不讓主表刪除 更新 3、set null : 刪除 更新,從表該字段值設置為NULL嵌套查詢(子查詢)
定義
把內層的查詢結果作為外層查詢的條件
多表查詢
笛卡爾積
多表查詢不加where條件,一張表的每條記錄分別和另一張表的所有記錄分別匹配一遍
連接查詢
分類(先執行on條件再連接)
1、內連接(表1 inner join 表2 on 條件) 2、外連接(表1 left|right join 表2 on 條件)1、左連接 :以左表為主顯示查詢結果2、右連接 :以右表為主顯示查詢結果語法
select 表名.字段名 from 表1 inner join 表2 on 條件;鎖
1、目的 :解決客戶端并發訪問的沖突問題
2、鎖分類
1、鎖類型 : 讀鎖 寫鎖 2、鎖粒度 : 行級鎖(InnoDB) 表級鎖(MyISAM)數據導入
方式一(使用source命令)
mysql> source /home/tarena/xxx.sql方式二(使用load命令)
1、將導入文件拷貝到數據庫搜索路徑中show variables like 'secure%'; 2、在數據庫中創建對應的表 3、執行數據導入語句索引
定義
對數據庫表中一列或多列的值進行排序的一種結構(BTree)優點
加快數據的檢索速度缺點
1、占用實際物理存儲空間 2、索引需動態維護,消耗資源,降低數據的維護速度分類及約束
1、普通索引(MUL): 無約束 2、唯一索引(UNI):字段值不允許重復,但可為NULL 3、主鍵(PRI) :字段值不允許重復,不可為NULL 4、外鍵 :讓當前表字段的值在另一張表的范圍內選擇面試題
1、把 /etc/passwd 文件的內容導入到數據庫的表中
tarena:x:1000:1000:tarena,,,:/home/tarena:/bin/bash 1. sudo cp /etc/passwd /var/lib/mysql-files/ 2.建表 creat table u_p(username varchar(20),password char(1),uid int,gid int,comment varchar(50),homedir varchar(50),shell varchar(30))charset=utf8; 3.導入: load data infile "/var/lib/mysql-file/passwd" into table u_p field terminate by ":" line terminate by "\n";總結
以上是生活随笔為你收集整理的MySQL高级之外键、表关联、数据导入及导出的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: 我们能做出量子计算机却至今摸不透量子力学
- 下一篇: 简谈 Xilinx FPGA 原理及结构