Java程式設計基礎32——MySQL多表聯查

扎瓦發表於2018-10-17

SQL 會建立多表及多表的關係

1.多表之間的關係如何來維護
- 新增外來鍵約束: foreign key
- alter table product add foreign key(cno) references category(cid);
- 從分類表中,刪除分類為5資訊,
  - delete from category where cid =5; //刪除失敗
  - 首先得去product表, 刪除所有分類ID5 商品
2.建資料庫原則:
- 通常情況下,一個專案/應用建一個資料庫
3.多表之間的建表原則
- 一對多:
  - 建表原則: 在多的一方增加一個外來鍵,指向一的一方
- 多對多:
  - 建表原則: 將多對多轉成一對多的關係,建立一張中間表
- 一對一: 不常用, 拆表操作
  - 建表原則: 將兩張表合併成一張表
  - 將兩張表的主鍵建立起關係
  - 將一對一的關係當作一對多的關係去處理
網上商城表例項的分析: 使用者購物流程

使用者表 (使用者的ID,使用者名稱,密碼,手機)

create table user(
    uid int primary key auto_increment,
      username varchar(31),
      password varchar(31),
      phone  varchar(11)
);

insert into user values(1,`zhangsan`,`123`,`13811118888`);

訂單表 (訂單編號,總價,訂單時間 ,地址,外來鍵使用者的ID)

create table orders(
      oid int primary key auto_increment,
    sum int not null,
    otime timestamp,
    address varchar(100),
    uno int,
    foreign key(uno) references user(uid)
);
  
insert into orders values(1,200,null,`黑馬前臺旁邊小黑屋`,1);
insert into orders values(2,250,null,`黑馬後臺旁邊1702`,1);

商品表 (商品ID, 商品名稱,商品價格,外來鍵cno)

create table product(
    pid int primary key auto_increment,
      pname varchar(10),
      price double,
      cno int,
      foreign key(cno) references category(cid)
);

insert into product values(null,`小米mix4`,998,1);
insert into product values(null,`錘子`,2888,1);
insert into product values(null,`阿迪王`,99,2);
insert into product values(null,`老村長`,88,3);
insert into product values(null,`勁酒`,35,3);
insert into product values(null,`小熊餅乾`,1,4);
insert into product values(null,`衛龍辣條`,1,5);
insert into product values(null,`旺旺大餅`,1,5);

訂單項: 中間表(訂單ID,商品ID,商品數量,訂單項總價)

create table orderitem(
    ono int,
      pno int,
      foreign key(ono) references orders(oid),
      foreign key(pno) references product(pid),
      ocount int,
      subsum double
);
--給1號訂單新增商品 200塊錢的商品
insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,101,100);

--給2號訂單新增商品 250塊錢的商品 ()
insert into orderitem values(2,5,1,35);
insert into orderitem values(2,3,3,99);

商品分類表(分類ID,分類名稱,分類描述)

create table category(
    cid int primary key auto_increment,
      cname varchar(15),
      cdesc varchar(100)
);

insert into category values(null,`手機數碼`,`電子產品,黑馬生產`);
insert into category values(null,`鞋靴箱包`,`江南皮鞋廠傾情打造`);
insert into category values(null,`香菸酒水`,`黃鶴樓,茅臺,二鍋頭`);
insert into category values(null,`酸奶餅乾`,`娃哈哈,蒙牛酸酸乳`);
insert into category values(null,`饞嘴零食`,`瓜子花生,八寶粥,辣條`);

主鍵約束: 預設就是不能為空, 唯一
- 外來鍵都是指向另外一張表的主鍵
- 主鍵一張表只能有一個
唯一約束: 列面的內容, 必須是唯一, 不能出現重複情況, 為空
- 唯一約束不可以作為其它表的外來鍵
- 可以有多個唯一約束

使用商城表完成對商品資訊的多表查詢

多表查詢

-- 笛卡爾積,查出來的是兩張表的乘積
select * from product,category;

-- 過濾出有意義的資料
select * from product,category where cno = cid;
select * from product p,category c where p.cno = c.cid;

-- 內連線查詢
-- 隱式內連線: 在查詢結果上做的where條件過濾
select * from product p,category c where p.cno = c.cid;
-- 顯式內連線: 帶著條件查結果,執行效率高
select * from product p inner join category c on p.cno = c.cid;

-- 左外連線:會將左表中的所有資料都查詢出來,如果右表中沒有對應的資料,用null代替
select * from product p left outer join category c on p.cno = c.cid;
-- 右外連線:
select * from product p right outer join category c on p.cno = c.cid;

分頁查詢

-- 起始索引: startIndex  = (index-1)*3
-- 第一個引數是索引,第二個引數顯示的個數
select * from product limit 0,3;
select * from product limit 3,3;

子查詢(瞭解的內容,非常重要)

-- 查詢出(商品名稱,商品分類名稱)資訊
    -- 左連線
    select * from product p left outer join category c on p.cno = c.cid;
-- 查詢分類名稱為手機數碼的所有商品
    select * from product where cno = (select cid from category where cname =`手機數碼`);

練習題

按照商品分類的名稱統計商品的個數:

-- 查詢分類名稱為手機數碼的所有商品
select * from product where cno = (select cid from category where cname =`手機數碼`);

-- 查詢出(商品名稱,商品分類名稱)資訊
  -- 左連線
    select * from product p left join category c on p.cno = c.cid;
    select p.pname,c.cname from product p left join category c on p.cno = c.cid;
  -- 子查詢
    select pname,(select cname from category c where p.cno = c.cid) as 分類名稱 from product p;

查詢1號訂單的訂單項資訊和商品資訊

多表查詢練習資料

員工資訊表

--員工資訊表
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 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);
INSERT INTO emp values(7981,`MILLER`,`CLERK`,7788,`1992-01-23`,2600,500,20);

部門資訊表

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

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`);

基本查詢

--所有員工的資訊
--薪資大於等於1000並且小於等於2000的員工資訊
--從員工表中查詢出所有的部門編號
--查詢出名字以A開頭的員工的資訊
--查詢出名字第二個字母是L的員工資訊
--查詢出沒有獎金的員工資訊
--所有員工的平均工資
--所有員工的工資總和
--所有員工的數量
-- 最高工資
    select max(sal) from emp;
-- 最少工資
    select min(sal) from emp;
-- 最高工資的員工資訊
    select * from emp where sal = (select max(sal) from emp);
-- 最低工資的員工資訊
    select * from emp where sal = (select min(sal) from emp);

分組查詢

--每個部門的平均工資

子查詢

-- 單行子查詢(> < >= <= = <>)
    -- 查詢出高於10號部門的平均工資的員工資訊
    
-- 多行子查詢(in  not in any all)    >any  >all
    -- 查詢出比10號部門任何員工薪資高的員工資訊

-- 多列子查詢(實際使用較少)   in
    -- 和10號部門同名同工作的員工資訊
-- Select接子查詢
    -- 獲取員工的名字和部門的名字
-- from後面接子查詢
    -- 查詢emp表中經理資訊
-- where 接子查詢
    -- 薪資高於10號部門平均工資的所有員工資訊
-- having後面接子查詢
    -- 有哪些部門的平均工資高於30號部門的平均工資

-- 工資>JONES工資
-- 查詢與SCOTT同一個部門的員工
-- 工資高於30號部門所有人的員工資訊
-- 查詢工作和工資與MARTIN完全相同的員工資訊
-- 有兩個以上直接下屬的員工資訊
-- 查詢員工編號為7788的員工名稱,員工工資,部門名稱,部門地址

SQL查詢的綜合案例

查詢出高於本部門平均工資的員工資訊
列出達拉斯加工作的人中,比紐約平均工資高的人
查詢7369員工編號,姓名,經理編號和經理姓名
查詢出各個部門薪水最高的員工所有資訊

面試題

CREATE TABLE test(
  name CHAR(20),
  kecheng CHAR(20),
  fenshu CHAR(20)
);

INSERT INTO test VALUES(`張三`,`語文`,81),
(`張三`,`數學`,75),
(`李四`,`語文`,76),
(`李四`,`數學`,90),
(`王五`,`語文`,81),
(`王五`,`數學`,82);

--請用一條Sql語句查處分數大於80的學生

MySQL多表關聯查詢
2018-01-27
MySql
MySQL資料庫基礎——多表查詢：子查詢
2020-10-30
MySql資料庫
史上最簡單MySQL教程詳解（基礎篇）之多表聯合查詢
2018-06-09
MySql
Java程式設計基礎31——MySql資料庫
2019-01-19
Java程式設計MySql資料庫
Java程式設計基礎
2016-12-07
Java程式設計
MYSQL 怎麼 LEFT JOIN 多表聯查
2018-11-23
MySql
mysql中的多表關聯查詢
2020-11-20
MySql
Java 基礎02Java程式設計基礎
2019-03-03
Java程式設計
【Java基礎】通用程式設計
2016-06-28
Java程式設計
MSSQL基礎教程：多表格查詢
2009-03-27
SQL
《java程式設計基礎》java的基礎知識（三）
2020-12-23
Java程式設計
Java-MySql-函式、多表查詢
2020-12-09
JavaMySql函式
JAVA網路程式設計基礎
2021-12-21
Java程式設計
Java 基礎程式設計筆記
2021-09-09
Java程式設計筆記
Java程式設計基礎33——JDBC
2019-01-19
Java程式設計JDBC
Java併發程式設計基礎
2021-10-25
Java程式設計
多表聯合查詢 - 基於註解SQL
2021-06-02
SQL
MySQL多表查詢
2020-07-19
MySql
【MySQL】多表查詢
2018-06-18
MySql
Java基礎——程式設計之路的開始，Java基礎知識
2014-11-02
Java程式設計
《java程式設計基礎》例題5.6
2020-10-22
Java程式設計
Java入門之基礎程式設計
2022-03-31
Java程式設計
Java 基礎（十六）網路程式設計
2017-11-17
Java程式設計
java程式設計師程式設計筆試基礎學習
2018-03-27
Java程式設計師筆試
JPA多表關聯查詢
2018-05-15
thinkPHP多表聯合查詢
2014-09-04
PHP
Java程式設計基礎29——JavaSE總結
2019-01-19
Java程式設計
Java程式設計基礎17——集合(List集合)
2019-01-19
Java程式設計
Java程式設計基礎05——方法（函式）
2019-01-19
Java程式設計函式
Java基礎之多執行緒程式設計
2019-01-14
Java執行緒程式設計
《java程式設計基礎》方法的過載
2020-10-26
Java程式設計
Java擴充-網路程式設計基礎
2020-11-29
Java程式設計
Java多執行緒程式設計基礎
2018-02-22
Java執行緒程式設計
JAVA程式設計師面試32問
2006-03-29
Java程式設計師面試
程式設計基礎
2017-09-26
程式設計
MySQL 多表關聯刪除
2016-11-18
MySql
java 設計模式基礎
2019-08-30
Java設計模式
JPA 之多表聯合查詢
2020-08-18

Java程式設計基礎32——MySQL多表聯查

SQL 會建立多表及多表的關係

使用商城表完成對商品資訊的多表查詢

多表查詢

分頁查詢

子查詢(瞭解的內容,非常重要)

練習題

多表查詢練習資料

面試題

相關文章