07-MYSQL多表查詢

来源:https://www.cnblogs.com/zmy-520131499/archive/2018/12/07/10083848.html
-Advertisement-
Play Games

今日任務 完成對MYSQL資料庫的多表查詢及建表的操作 教學目標 掌握MYSQL中多表的創建及多表的查詢 掌握MYSQL中的表關係分析並能正確建表 昨天內容回顧: ​ 資料庫的創建 : create database 資料庫的名 character set 字元集 collate 校對規則 ​ 數據 ...


 今日任務

 完成對MYSQL資料庫的多表查詢及建表的操作



 教學目標

 掌握MYSQL中多表的創建及多表的查詢

 掌握MYSQL中的表關係分析並能正確建表



昨天內容回顧:

​    資料庫的創建 : create database 資料庫的名 character set 字元集  collate 校對規則

​    資料庫的刪除: drop database 資料庫名

​    修改: alter database 資料庫 character set 字元集(utf8)

​    查詢:  show databases;

​          show create database 資料庫的名字

​          select database();

​    切換資料庫 :

​            use 資料庫的名字

​    

​    表結構的操作:

​        創建:  create table 表名(    

​                列名 列的類型  列的約束,

​                列名 列的類型  列的約

​            )

​            列的類型: char / varchar   

​            列的約束: 

​                    primary key 主鍵約束

​                    unique : 唯一約束

​                    not null 非空約束

​              自動增長 : auto_increment

​         刪除 :  drop table 表名

​        修改:   alter table 表名 (add, modify, change , drop)

​            rename table 舊表名  to 新表名

​            alter table 表名 character set 字元集

​       查詢表結構:

​            show tables; 查詢出所有的表

​            show create table 表名: 表的創建語句, 表的定義

​            desc 表名:　表的結構



​    表中數據的操作

​        插入：　 insert into 表名(列名,列名) values(值1,值2);

​        刪除:        delete from 表名 [where 條件]

​        修改:        update 表名 set 列名='值' ,列名='值' [where 條件];

​        查詢:    select [distinct]  * [列名1,列名2] from 表名 [where 條件]

​              as關鍵字: 別名

​               where條件後面:

​        

​                    關係運算符:  > >= < <= !=  <>

​                        --判斷某一列是否為空:  is null    is not null

​                        in 在某範圍內

​                        between...and

​                    邏輯運算符: and or not

​                    模糊查詢:  like    

​                            _ : 代表單個字元

​                            %: 代表的是多個字元

​                    分組: group by 

​                    分組之後條件過濾:  having

​                    聚合函數: sum()  ,avg() , count()  ,max(), min()

​                    排序: order by  (asc 升序, desc 降序)


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

 需求:

分類表和商品表之間是不是有關係? 如果有關係,在資料庫中如何表示這種關係

```sql
create table category(
  cid int primary key auto_increment,
  cname varchar(10),
  cdesc varchar(31)
);

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 category;
select cname,cdesc from category;

--所有商品
1.商品ID
2.商品名稱
3.商品的價格
4.生產日期
5.商品分類ID

商品和商品分類 : 所屬關係
create table product(
    pid int primary key auto_increment,
      pname varchar(10),
      price double,
      pdate timestamp,
      cno int
);

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

//插入數據會失敗
insert into product values(null,'充氣的',1,null,12);

```



 技術分析:

- 多表之間的關係如何來維護

  外鍵約束: foreign key

  - 給product中的這個cno 添加一個外鍵約束

    alter table product add foreign key(cno)  references  category(cid);

  - 自己挖坑

  - 從分類表中,刪除分類為5信息,

    - delete from category where cid =5;  //刪除失敗
    - 首先得去product表, 刪除所有分類ID5  商品

- 建資料庫原則:

  - 通常情況下,一個項目/應用建一個資料庫


- 多表之間的建表原則

  - 一對多 : 商品和分類

    - 建表原則: 在多的一方添加一個外鍵,指向一的一方的主鍵

      ​

  - 多對多: 老師和學生, 學生和課程

    建表原則: 建立一張中間表,將多對多的關係,拆分成一對多的關係,中間表至少要有兩個外鍵,分別指向原來的那兩張表

    ​

  - 一對一: 班級和班長, 公民和身份證, 國家和國旗

    - 建表原則:  

      - 將一對一的情況,當作是一對多情況處理,在任意一張表添加一個外鍵,並且這個外鍵要唯一,指向另外一張表
      - 直接將兩張表合併成一張表
      - 將兩張表的主鍵建立起連接,讓兩張表裡面主鍵相等

    - 實際用途: 用的不是很多.    (拆表操作  )

      - 相親網站: 
        - 個人信息 : 姓名,性別,年齡,身高,體重,三圍,興趣愛好,(年收入,  特長,學歷, 職業, 擇偶目標,要求)
        - 拆表操作 : 將個人的常用信息和不常用信息,減少表的臃腫, 


- 網上商城表實例的分析:  用戶購物流程

- 用戶表 (用戶的ID,用戶名,密碼,手機)

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

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

    ```sql
    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,商品數量,訂單項總價)

  ```sql
  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,分類名稱,分類描述)

    ```sql
    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,'饞嘴零食','瓜子花生,八寶粥,辣條');
    ```






- 多表之間的關係如何維護: 外鍵約束 :   foreign key
- 添加一個外鍵: alter table product add foreign key(cno)  references category(cid);
  - ​        foreign key(cno) references category(cid)
  - 刪除的時候, 先刪除外鍵關聯的所有數據,再才能刪除分類的數據
- 建表原則:
  - 一對多:
    - 建表原則: 在多的一方增加一個外鍵,指向一的一方
  - 多對多:
    - 建表原則: 將多對多轉成一對多的關係,創建一張中間表
  - 一對一: 不常用, 拆表操作
    - 建表原則:  將兩張表合併成一張表
      - 將兩張表的主鍵建立起關係
      - 將一對一的關係當作一對多的關係去處理




主鍵約束: 預設就是不能為空, 唯一

-  外鍵都是指向另外一張表的主鍵
-  主鍵一張表只能有一個

唯一約束:  列面的內容, 必須是唯一, 不能出現重覆情況, 為空

- 唯一約束不可以作為其它表的外鍵
- 可以有多個唯一約束



一對多 : 建表原則: 在多的一方添加一個外鍵,指向一的一方

多對多: 建表原則:

​        拆成一對多

​        創建一張中間表, 至少要有兩個外鍵, 指向原來的表

一對一: 建表原則: 合併一張表, 將主鍵建立關係 , 將它當作一對多的情況來處理


- 資料庫客戶端軟體

  ​
 使用商城表完成對商品信息的多表查詢

 需求分析:

在我們的商城案例中,我的訂單中包含很多信息.打開我的訂單需要去查詢表

 技術分析:

 多表查詢

- 交叉連接查詢  笛卡爾積

- 內連接查詢

- 左外連接

- 右外連接  


 分頁查詢

- 每頁數據數據3

- 起始索引從0 

- 第1頁: 0

- 第2頁: 3

  起始索引:  index 代表顯示第幾頁 頁數從1開始

  每頁顯示3條數據

  startIndex  = (index-1)*3

  ​

第一個參數是索引 

第二個參數顯示的個數

select * from product limit 0,3;

select * from product limit 3,3;



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

查詢出(商品名稱,商品分類名稱)信息

```sql

```

  查詢分類名稱為手機數位的所有商品

```sql
select * from product where cname ='手機數位';

```

 練習題

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

```sql
 
```

- 查詢1號訂單的訂單項信息和商品信息

```sql

```


 多表查詢練習數據

- 員工信息表

```sql
--員工信息表
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);
```

- 部門信息表

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

- 基本查詢

```sql
--所有員工的信息
--薪資大於等於1000並且小於等於2000的員工信息
--從員工表中查詢出所有的部門編號
--查詢出名字以A開頭的員工的信息
--查詢出名字第二個字母是L的員工信息
--查詢出沒有獎金的員工信息
--所有員工的平均工資
--所有員工的工資總和
--所有員工的數量
--最高工資
--最少工資
--最高工資的員工信息
--最低工資的員工信息
```

- 分組查詢

```sql
--每個部門的平均工資
```

- 子查詢

```sql
-- 單行子查詢(> < >= <= = <>)
    -- 查詢出高於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. 查詢出各個部門薪水最高的員工所有信息


 面試題

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

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

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 騰訊雲Mac圖床插件
    背景 隨著博客越寫越多，難免會遇到需要插入圖片來說明的情況。 圖床選擇 首先調研了市面上的圖床服務，本著穩定長期的目標，過濾掉了打一槍換一個地方的野雞小網站，剩餘比較靠譜的優缺點如下。 |圖床|優點|缺點| | | | | |騰訊雲|免費 無需功能變數名稱|未來可能會收費| |七牛|免費|需要功能變數名稱和備案| ...
  • Zabbix常見觸發器表達式
    Zabbix trigger是zabbix 進行告警通知的設定條件 ，當監控獲取的值觸發了設定的條件時，會按照觸發器的設定，執行相應的action 操作 。在zabbix中為了比較方便的設定各種條件，zabbix為我們設計了相應的函數和操作符 。 一、創建觸發器觸發器可以是和模板關聯的，也可以是和主 ...
  • Digital Ocean搭建梯子簡略教程
    一 註冊賬號和申請 1 Digital Ocean網址https://cloud.digitalocean.com，可直接通過郵箱註冊，會向郵箱發送驗證碼。在註冊後必須先充值5美元才可以使用，充值時可選擇paypal或者信用卡支付。 2 關於優惠。如果直接點擊官網鏈接註冊是沒有優惠的。如果你是在校大 ...
  • linux每日命令(37)：top命令
    top命令是Linux下常用的性能分析工具，能夠實時顯示系統中各個進程的資源占用狀況，類似於Windows的任務管理器。下麵詳細介紹它的使用方法。top是一個動態顯示過程,即可以通過用戶按鍵來不斷刷新當前狀態.如果在前臺執行該命令,它將獨占前臺,直到用戶終止該程式為止.比較準確的說,top命令提供了 ...
  • vsftp虛擬用戶方式訪問
    需求：外部人員需要對公司伺服器上某個文件夾內容進行讀寫操作 文件目錄信息：/opt/abc drwxr-xr-x 9 www www 4096 12月 4 13:02 abc #註意最初abc的www用戶組沒有寫許可權 為了後面的ftp用戶能夠對此文件具備寫操作，需要添加www用戶組的寫許可權： chm ...
  • 學習筆記—MapReduce
    MapReduce是什麼 MapReduce是一種分散式計算編程框架，是Hadoop主要組成部分之一，可以讓用戶專註於編寫核心邏輯代碼，最後以高可靠、高容錯的方式在大型集群上並行處理大量數據。 MapReduce的存儲 MapReduce的數據是存儲在HDFS上的，HDFS也是Hadoop的主要組成 ...
  • redis 系列20 伺服器下
    二. serverCron函數 2.3 更新伺服器每秒執行命令次數 serverCron函數中的trackOperationsPerSecond函數會以每100毫秒一次的頻率執行，這個函數以抽樣計算的方式，估算並記錄伺服器在最近一秒鐘處理的命令請求數量，這個值可以通過info status命令的in ...
  • linux配置MySql表名不區分大小寫
    1、Linux下mysql安裝完後是預設：區分表名的大小寫，不區分列名的大小寫；2、用root帳號登錄後，在/etc/my.cnf中的[mysqld]後添加添加lower_case_table_names=1，重啟MYSQL服務，這時已設置成功：不區分表名的大小寫；lower_case_table_ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類