天天看點
傳回

mysql---多表查詢問題說明：

多個表之間通過外鍵限制

外鍵特點：

從表外鍵的值是對主表鍵的引用。

從表外鍵類型，必須與主表主鍵類型一緻

聲明外鍵限制：

alter table produnct add constraint key_fk(外鍵名稱) foreign key category_id(從表外鍵字段名) references category(cid);

外鍵名稱用于删除外鍵限制時使用，也可不設定，一般建議“_fk”為結尾。

删除外鍵限制：

alter table product drop foreign key key_fk;

注意：

          在不解除外鍵限制時，主表不能直接删除與從表有限制關系的資料資訊。後面解決。

多表間的關系

  • 一對一關系
  • 一對多關系
  • 多對多關系

（1）、一對一關系

這種關系即多個表具有相同的主鍵，實際中用的并不多，因為完全可以将這種關系的合并為同一張表。

（2）、一對多關系

在多的一方建立外鍵（外鍵指向一的主鍵）

母親與孩子的關系：母親，孩子兩個實體
母親表：ID(P),名字，年齡，性别
孩子表：ID(P),名字，年齡，性别

以上關系：一個媽媽可以在孩子表中找到多條記錄（也可能是一條），但是一個孩子隻能找到一個媽媽是一種典型的一對多的關系。
但是以上設計：解決了實體的設計表問題，但是沒有解決關系問題，孩子找不到母親，母親也找不到孩子

解決方案：在某一張表中增加一個字段，能夠找到另外一張表中的記錄:在孩子表中增加一個字段指向母親表，因為孩子表的記錄隻能比對到一條母親表的記錄。
母親表：ID(P),名字，年齡，性别
孩子表：ID(P),名字，年齡，性别，母親表ID（母親表主鍵）

（3）、多對多關系

一對表中（A）的一條記錄能夠對應另外一張表（B）中的多條記錄；同時B表中的一條記錄也能對應A表中的多條記錄

通過中間表。中間表是多的一方，是以多的一方要包含指向一的一方的外鍵。是以中間表，作為兩邊多的一方，中間表要包含兩邊的外鍵，兩個外鍵分别指向兩張表的主鍵。

老師和學生
老師表 T_ID(P),姓名，性别
學生表 S_ID(P),姓名，性别
以上設計方案：實作了實體的設計，但是沒有維護實體的關系一個老師教過多個學生，一個學生也被多個老師教過

解決方案：增加一張中間關系表
老師與學生的關系表：ID(P),T_ID,S_ID 
老師表與中間表形成一對多的關系，而中間表是多表；維護了能夠唯一找到一表的關系；
同樣的學生表與中間表也是一個一對多的關系; 
學生找老師：找出學生ID--->中間表尋找比對記錄（多條）--->老師表比對（一條）
老師找學生：找出老師ID--->中間表尋找比對記錄（多條）--->學生表比對（一條）

例子參考：https://www.cnblogs.com/panxuejun/p/5977064.html

多表查詢：

多表查詢有如下幾種：

（1）、合并結果集：UNION、UNION ALL

在資料庫中，union和union all關鍵字都是将兩個結果集合并為一個，但這兩者從使用和效率上來說都有所不同。

union在進行表連結後會篩選掉重複的記錄，是以在表連結後會對所産生的結果集進行排序運算，删除重複的記錄再傳回結果。

如： 

 select * from test_union1 

   union 

 select * from test_union2 

這個SQL在運作時先取出兩個表的結果，再用排序空間進行排序，删除重複的記錄，最後傳回結果集，如果表資料量大的話可能會導緻用磁盤進行排序。 

而union all隻是簡單的将兩個結果合并後就傳回。這樣，如果傳回的兩個結果集中有重複的資料，那麼傳回的結果集就會包含重複的資料了。 

     從效率上說，union all要比union快很多，是以，如果可以确認合并的兩個結果集中不包含重複的資料的話，那麼就使用union all，如下： 

select * from test_union1 

union all 

select * from test_union2 

 使用 union 組合查詢的結果集有兩個最基本的規則：

  • 所有查詢中的列數和列的順序必須相同。
  • 資料類型必須相容

（2）、連接配接查詢

交叉連接配接查詢（一般不用）

select * from A,B

内連接配接查詢（使用關鍵字inner join --inner可以省略）

  • 隐式内連接配接：select * from A,B where 條件;
  • 顯式内連接配接：selcet * from A inner join B on 條件;

外連接配接查詢：（使用關鍵字outer join --outer 可以省略）

  • 左外連接配接：left outer join           select * from A left outer join B on 條件;
  • 右外連接配接： right outer join        select * from A right outer join B on 條件;

示例：

emp表：

CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT
) ;

INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'NEW YORK');
INSERT INTO `dept` VALUES ('20', 'RESEARCH', 'DALLAS');
INSERT INTO `dept` VALUES ('30', 'SALES', 'CHICAGO');
INSERT INTO `dept` VALUES ('40', 'OPERATIONS', 'BOSTON');

dept 表：

CREATE TABLE dept(
deptno  INT,
dname   varchar(14),
loc   varchar(13)
);

INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800',
null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20',
'1600', '300', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250',
'500', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975',
null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28',
'1250', '1400', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850',
null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450',
null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000',
null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000',
null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN',
'7698', '1981-09-08', '1500', '0', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100',
null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950',
null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000',
null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300',
null, '10');

 執行如下SQL語句：

select * from emp,dept;
mysql---多表查詢問題說明：

 使用外鍵關系作為條件去除無用資訊，即内連接配接：

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;
mysql---多表查詢問題說明：

有時，我們隻需要自己需要的列：

SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
mysql---多表查詢問題說明：

 上面的連接配接語句就是内連接配接，而SQL标準的内連接配接寫法為：

SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;

 外連接配接：

  • 特點：查詢出的結果存在不滿足條件的可能

a.左外連接配接：

SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

 左連接配接先查詢左表（即以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示為：null

mysql---多表查詢問題說明：

b.右外連接配接：

SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

 右連接配接先查詢右表（即以右表為主），然後查詢左表，左表中滿足條件的顯示出來，不滿足條件的顯示為：null

mysql---多表查詢問題說明：

（3）、子查詢

一個select語句中包含另一個完整的select語句。

子查詢就是嵌套查詢，即select中包含select，如果一條語句中存在兩個，或者兩個以上select。那麼就是子查詢語句。

注意：

子查詢的位置：

a.where後，作為條為被查詢的一條件的一部分

b.from後，作表

當子查詢出現在where後作為條件時，還可以使用如下的關鍵字：

a.any

b.all

子查詢結果集的形式：

a.單行單列（用于條件）

b.單行多列（用于條件）

c.多行單列（用于條件）

d.多行多列（用于表）

示例：

1、工資高于JONES的工資

第一步：查詢JONES的工資

SELECT sal FROM emp WHERE ename='JONES';

第二步:查詢工資高于JONES的工資

SELECT * FROM emp WHERE sal > (第一步);

結果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES');

 2、工資高于30号部門所有人的員工資訊

第一步：查詢30部門的所有人工資

SELECT sal FROM emp WHERE deptno=30;

第二步：查詢工資高于30号部門所有人的員工資訊

SELECT * FROM emp WHERE sal > ALL (???)

結果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

 子查詢作為條件

子查詢形式為多行單列（當子查詢結果集形式為多行單列時可以使用ALL或ANY關鍵字）

問題說明：

一、主從表資料更新問題

當兩張表沒有建立任何關系的時候，那麼可以随意删除其中任何一張表中的任何記錄，但是一旦把兩張表建立了關系(主外鍵限制)之後，那麼不能删除主表中的資料(這些資料内容在從表中有關聯關系的資料)，隻想執行删除(更新操作)，那麼就會出現下圖中的錯誤。

mysql---多表查詢問題說明：

要想删除主表中與從表有關聯關系的資料，可以這麼做：

  1. 解除主從表的限制關系
  2. 先删除從表中與主表有關系的資料[ThinkPad1] ，再删除主表中的資料。

另外，從表也不能添加主表中不存在的資料！

mysql---多表查詢問題說明：

二、關于子查詢問題

    一般子查詢會存在于兩張關聯的表中，那麼我們可以先把帶有條件的那張表與另外一張表的關聯關系字段查出來，并作為另外一張表查詢的條件值，然後再次進行查詢。

    例子：查詢“化妝品”分類上架商品詳情。

先查主表的主鍵(根據指定的條件<化妝品，cname[ThinkPad2] >)

    select cid  from category where panme=’化妝品’

查詢所有商品的詳細資訊(根據分類id查詢)：

select * from product where category_id=cid[ThinkPad3] 

select * from product where category_id=( select cid  from category where cname=’化妝品’);

三、分頁查詢問題

    分頁查詢使用的是limit關鍵字進行查詢。它後面有兩個參數。第一個參數[ThinkPad4] 是起始的位置，第二個參數是每頁需要顯示的條目數。

舉例：商品表中有10條記錄，現在需要進行分頁顯示，每頁顯示3條資料。現在需要檢視第二頁的資料。那麼應該使用的sql語句是：

    select * from product limit 3[ThinkPad5] ,3;

mysql---多表查詢問題說明：

執行分頁查詢語句後，顯示的結果如下：

mysql---多表查詢問題說明：

四、關于mysql編碼問題

1 檢視MySQL編碼

  SHOW VARIABLES LIKE 'char%';

mysql---多表查詢問題說明：

因為當初安裝時指定了字元集為UTF8，是以所有的編碼都是UTF8。

  1. character_set_client：你發送的資料必須與client指定的編碼一緻！！！伺服器會使用該編碼來解讀用戶端發送過來的資料；
  2. character_set_connection：通過該編碼與client一緻！該編碼不會導緻亂碼！當執行的是查詢語句時，用戶端發送過來的資料會先轉換成connection指定的編碼。但隻要用戶端發送過來的資料與client指定的編碼一緻，那麼轉換就不會出現問題；
  3. character_set_database：資料庫預設編碼，在建立資料庫時，如果沒有指定編碼，那麼預設使用database編碼；
  4. character_set_server：MySQL伺服器預設編碼；
  5. character_set_results：響應的編碼，即查詢結果傳回給用戶端的編碼。這說明用戶端必須使用result指定的編碼來解碼；

2 控制台編碼

修改character_set_client、character_set_results、character_set_connection為GBK，就不會出現亂碼了。但其實隻需要修改character_set_client和character_set_results。

控制台的編碼隻能是GBK，而不能修改為UTF8，這就出現一個問題。用戶端發送的資料是GBK，而character_set_client為UTF8，這就說明用戶端資料到了伺服器端後一定會出現亂碼。既然不能修改控制台的編碼，那麼隻能修改character_set_client為GBK了。

伺服器發送給用戶端的資料編碼為character_set_result，它如果是UTF8，那麼控制台使用GBK解碼也一定會出現亂碼。因為無法修改控制台編碼，是以隻能把character_set_result修改為GBK。

  1. 修改character_set_client變量：set character_set_client=gbk;
  2. 修改character_set_results變量：set character_set_results=gbk;

設定編碼隻對目前連接配接有效，這說明每次登入MySQL提示符後都要去修改這兩個編碼，但可以通過修改配置檔案來處理這一問題：配置檔案路徑：D:\Program Files\MySQL\MySQL Server 5.1\ my.ini

mysql---多表查詢問題說明：

3 MySQL工具

使用MySQL工具是不會出現亂碼的，因為它們會每次連接配接時都修改character_set_client、character_set_results、character_set_connection的編碼。這樣對my.ini上的配置覆寫了，也就不會出現亂碼了。

mysql mysql查詢 多表查詢
上一篇: HashMap源碼解析和設計解讀
下一篇: Redis--狂神說Redis基礎彙總(完結)

繼續閱讀