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

扎瓦發表於2018-10-17
Java程式設計MySql

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查詢的綜合案例
  1. 查詢出高於本部門平均工資的員工資訊
  2. 列出達拉斯加工作的人中,比紐約平均工資高的人
  3. 查詢7369員工編號,姓名,經理編號和經理姓名
  4. 查詢出各個部門薪水最高的員工所有資訊

面試題

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

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

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

相關文章