JavaEE 之 數據庫技術 MySQL(2)

資料 ? 青牛 ? 于 2019-06-28 16:35:28 ? 564 閱讀

第5章 SQL語句(DQL)

1) DDL 數據定義語言 表=java類 每一條數據 都是 這個表的一個對象

2) DML語句 數據操作語言 添加 修改 刪除

數據的查詢 = 數據分析

5.1DQL準備工作和語法

5.1.1準備工作

創建商品表:
商品表 product
商品編號 主鍵 自增
商品名稱 字符
商品價格 浮點型
商品類別ID --> 在某些時刻 一個表的一個數據的id就能代表這個表的這條數據的全部

代碼 :
file

添加測試數據:
file

5.1.2 DQL語法:

select [distinct] * | 列名,列名 from 表 where 條件;

5.2 簡單查詢

  1. 查詢所有的商品
    file

  2. 查詢商品名和商品價格.
    file

3.1 表別名:
file

3.2 列別名:
file

  1. 去掉重復值

file
5.查詢結果是表達式(運算查詢):將所有商品的價格+10元進行顯示.
file

5.3 條件查詢 java if結構其實是一樣
file

查詢商品名稱為“三只松鼠堅果炒貨零食特產每日堅果開心果100g/袋”的商品所有:

file
查詢價格為299商品
file

查詢價格不是800的所有商品
file

查詢商品價格大于60元的所有商品
file

查詢商品價格在2000到10000之間所有商品
file

查詢商品價格小于2000或大于10000的所有商品
file

查詢商品價格等于 306830 28512 的商品
file
查詢含有 '霸' 字的所有商品

file
查詢以'三'開頭的所有商品
file

查詢第二個字為'想'的所有商品
file

商品沒有分類的商品
file

查詢有分類的商品
file

5.4 排序查詢

通過order by語句,可以將查詢出的結果進行排序。暫時放置在select語句的后。

格式:

SELECT * FROM 表名 ORDER BY 排序字段 ASC|DESC;

ASC 升序 (默認)

DESC 降

1.使用價格排序(降序)
file

2.在價格排序(降序)的基礎上,以分類排序(降序)
file

3.顯示商品的價格(去重復),并排序(降序)
file

5.5 聚合查詢

之前我們做的查詢都是橫向查詢,它們都是根據條件一行一行的進行判斷,而使用聚合函數查詢是縱向查詢,它是對一列的值進行計算,然后返回一 個單一的值;另外聚合函數會忽略空值。

說白了,聚合查詢就是先把表的數據聚在一起,統一進行計算后,再得出一個結果的查詢方式;

我們學習如下五個聚合函數:

count:統計指定列不為NULL的記錄行數;

sum:計算指定列的數值和,如果指定列類型不是數值類型,那么計算結果為0;

max:計算指定列的大值,如果指定列是字符串類型,那么使用字符串排序運算;

min:計算指定列的小值,如果指定列是字符串類型,那么使用字符串排序運算;

avg:計算指定列的平均值,如果指定列類型不是數值類型,那么計算結果為0;

1 查詢商品的總條數
file

2 查詢價格大于2000商品的總條數
file

3 查詢分類為 1 的所有商品的總和

file
4 查詢分類為2所有商品的平均價格
file

5 查詢商品的大價格和小價格
file

5.6 分組查詢

分組查詢是指使用group by字句對查詢進行分組。

格式:

SELECT 字段1,字段2… FROM 表名GROUP BY分組字段 HAVING 分組條件;

分組操作中的having子語句,是用于在分組后對數據進行過濾的,作用類似于where條件。

having與where的區別:
having是在分組后對數據進行過濾
where是在分組前對數據進行過濾
having后面可以使用分組函數(統計函數)
where后面不可以使用分組函數

1 統計各個分類商品的個數

file

第6章 SQL導出與導入

6.1 SQL導出

數據庫的備份是指將數據庫轉換成對應的sql文件

6.1.1 可視化工具導出

雙擊打開要備份的數據庫,右鍵選擇 "轉存儲SQL文件" ,然后選擇SQL文件的存放位置即可;
file
file

此處需要注意的是我們打開SQL后需要關注以下:
file

此處顯示了備份數據庫的名稱

file
此處顯示了編碼規則

6.2 SQL導入

SQL導入的前提是你必須先創建一個一模一樣的數據庫才可以導入SQL語句,所以此處就用上了我們上面的那些

6.2.1可視化工具導入SQL

  1. 創建數據庫
    file

  2. 在創建好的數據庫上右鍵運行SQL文件

file
file
點擊 "開始" 按鈕 進行數據的導入;

第7章 多表操作(多表的查詢)

7.1 前情回顧

回顧 : mysql 安裝 基本語法

SQL語言 :

DDL語句 : 對表 數據庫 等存儲結構的操作 創建一個結構 create xxx 修改一個結構 alter xxxx 摧毀一個結構 drop xxx 查詢某個結構 show xxxx desc table
DML語句 : 對數據本身的操作
數據存儲 insert 插入 inert into 表 (列) values (值)
更新數據 update update 表 set 列(字段) = 值 where 限定條件
刪除數據 delete from 表 where 限定條件
拓展 : 刪除表的所有數據的時候
1) delete from 表 查一條 刪一條 記錄一個受影響行數
2) truncate table 表 摧毀表結構 重新創建表結構
總結 : 當刪除表的所有數據的時候 如果數據量非常大的時候(1億條以上) truncate table

DQL語句
select 列 from 表
拓展 :
按條件查詢
排序 order by
聚合(count sum avg max min) + 分組 group by --> 以后的數據分析行業來說是非常重要的
但是不得不說 對數據分組 永遠都是查詢成本非常高的活 數據量越大 分組消耗的性能也越大

問題 :
數據庫是干什么的?
存儲數據
mysql 大數據庫管理系統 ORACLE DB2
根據業務創建自己的小數據庫
我們把同類的數據放在一起 然后起一個類型名稱 (表)
person
dog
同類型的數據 可以放在一個表里 不同類型的數據應該分別存儲在不同的表里
怎么區分是同類型的數據呢?
看數據的屬性(表的字段)
1 孫建國 男 24 北京市
2 趙文明 男 24 河北省
id name sex age from
哈士奇 趙鐵柱
create table dog id name type color mastername

7.2 多表查詢

實際開發中,一個項目通常需要很多張表才能完成。

例如:京東在展示商品的時候就需要很多表,比如

分類表(category)、

商品表(products)、
file

那么如果我們做一個業務需要多張表提供數據的話,那么這些表之間肯定是有聯系的,所以我們就來探討一下表與表的聯系?

7.2.1 表與表之間的關系

7.2.1.1 一對多關系:

常見實例:客戶和訂單,分類和商品,部門和員工 學生和班級.

一對多建表原則:在從表(多方)創建一個字段,字段作為外鍵指向主表(一方)的主鍵.
file

舉個例子:

班級與學生

原始 : 19xx年

數據庫開發出來 : 所有的數據都保存在一起
file

這種數據存儲方法我們可以看出 所屬班級這一列的數據隨著學生的增多會大量重復,這時候大家就要像一個問題,這種重復的數據會占用很大的存儲空 間,那么有沒有解決辦法呢?

后來 :

  1. 創建一個班級表(主表)
    file

班級表中 Java大數據11班的編號是1 也就是說在某些場合下 班級編號為1的班級 = Java大數據11班 所以在保存學生的時候我們只保存班級編號就可以了

  1. 保存學生
    file

此時我們用一個數組代替了具體的名稱,這樣的好處就是 數字占用的字節數比漢子要小了很多,所以浪費的空間也小了很多

但是大家要注意一個問題,就是此時學生表中有一列專門存儲的班級表的主鍵值,那么這一列對于學生表來說叫做外鍵列

包含外鍵的表 叫做外鍵表 外鍵對應的表的主鍵所在的表我們叫做主鍵表

外鍵表的外鍵列的值與主鍵表的主鍵值是對應的,那么這種對應關系我們就叫他 多對一 關系

在多對一關系中 多的一方(包含外鍵)叫做從表 一的一方叫做 主表

我們了解了多對一關系,那么在實際應用中我們該如何應用這種設計方案呢?
1) 確定好你要設計實體
2) 確定好 實體之間關系
3) 做好主外鍵關聯

舉個例子 員工 與 部門 表結構
1) 抽取實體設計 員工(實體) 部門(實體) --> 外鍵一定是存在于從表中
2) 分清關系 一個員工 只能屬于一個部門 但是 一個部門下有多個員工
3) 一定是先寫主鍵表 部門表 再創建 外鍵表 員工表
部門表
主鍵 deptID
員工表
N列 外鍵 deptID

7.2.2.2 一對一關系:(特殊的多對一)

如果在一對多關系中 多的一方 也變成了一 那么兩個實體之間就是一對一關系了

現實生活中 一對一設計其實并不太多,因為一對一關系是沒有必要分表設計的,舉個例子:

我們知道一個人有一個身份證,有一份檔案,拿身份證舉例,我們會這么設計人員表

file
如果一對一設計成兩張表就很尷尬了

首先創建身份證號表

file
在創建人員表
file

通過觀察大家就可以很明顯的發現 在實際的開發中應用不多.因為一對一可以創建成一張表.

7.2.2.3 多對多關系: 當 多對一關系中 一的一方變成 多 那么這個時候就會出現一種新關系 多對多關系

舉個例子 : 數據庫設計經常會應用到兩個實體 叫做 用戶 和 角色

什么是用戶 : 就是身處某個環境中的人

什么是 角色 : 就是身處某個環境中你扮演的那個身份,比如

同樣是你,在學校這個環境你就是學生,這里面 你是用戶 你的角色是學生

再比如說你在家這個環境中你就是子女,這里面 你是用戶 你的角色是子女

那么此處有一個問題,從上面的例子我們可以看出一個用戶可以由多個角色,反過來一個角色有可以被多個用戶所擁有,那么這時候我們該怎么表示這種 關系呢?

假如 現在給大家這么幾個角色

角色表
file

用戶表
file

表示關系 : 李磊 韓梅梅 趙文明 都是學生 韓梅梅計算機課代表 趙文明是班長

關系設計

file
file
上述的關系設計已經違反了列的存值特性,一行記錄肯定保存不了多個值!所以此時這種設計就不行了

那么如果想正確的表示角色和用戶的關系怎么辦呢?這時你需要再創建一張表,叫中間表

file
多對多關系:

常見實例:學生和老師、用戶和角色

多對多關系建表原則:需要創建第三張表,中間表中至少兩個字段,這兩個字段分別作為外鍵指向各自一方的主鍵.
file

7.3 外鍵約束

現在我們有兩張表“分類表”和“商品表”,為了表明商品屬于哪個分類,通常情況下,我們將在商品表上添加一列,用于存放分類c_id的,此 列稱為:外鍵
file

此時“分類表category”稱為:主表,“cid”我們稱為主鍵?!吧唐繁韕roducts”稱為:從表,c_id稱為外鍵。

我們通過主表的主鍵和從表的外鍵來描述主外鍵關系,呈現就是一對多關系。

外鍵特點:

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

從表外鍵類型,必須與主表主鍵類型一致。

聲明外鍵約束

語法:alter table 從表 add [constraint] [外鍵名稱] foreign key (從表外鍵字段名) references 主表 (主表的主鍵);

[外鍵名稱] 用于刪除外鍵約束的,一般建議“_fk”結尾

alter table 從表 drop foreign key 外鍵名稱

使用外鍵目的:保證數據完整性

7.4 一對多操作

7.4.1 分析
file

category分類表,為一方,也就是主表,必須提供主鍵cid

products商品表,為多方,也就是從表,必須提供外鍵c_id

7.4.2 實現:分類和商品

清空商品類別表和商品表(不清空的話是創建不了外鍵約束的)
file

設置商品表的外鍵cid 不為null
file

修改商品分類表的主鍵(商品分類表主鍵是varchar的不能和晚間匹配,必須修改)

file
添加約束
file

7.4.3 驗證外鍵約束

1 向分類表中添加數據
file

2 向商品表中添加已經存在的商品類別的商品
file

3 向商品表中添加不存在的商品類別的商品 (報錯,違反了主外鍵約束)

file
file
4 刪除指定分類(分類被商品使用) -- 執行異常(因為要刪除的商品分類被外檢表引用,所以會報錯)
file
file

7.5 多對多

7.5.1 分析

多對多我們可以拆分成兩個一對多去做,根據我們之前的分析如果創建多對多關系的話必須有兩個實體表和一個中間表 中間表和兩個實體表之前保持多對一關聯?

7.5.2 實現:用戶與角色

創建用戶表
file

創建角色表
file

創建中間表
file

創建中間表與用戶表外鍵
file

創建中間表與角色表外鍵
file

創建中間表的聯合主鍵(可省略)
file

7.5.3 測試多對多關系

1 向用戶表中添加數據
file

2 向角色表中添加數據
file

3 向中間表添加數據(數據存在)
file

4 刪除中間表的數據
file

5 向中間表添加數據(數據不存在) -- 執行異常
file
file

6 刪除用戶表或者角色表數據 -- 執行異常
file

7.6 注意

主外鍵約束除了可以幫助我們保證數據的完整性,同時也限制了數據操作的靈活性,此處大家要注意自己公司的具體要求,如果要求數據完整那就必須創 建主外鍵約束如果要求保證操作靈活那么除了主鍵約束外不可以創建外鍵約束,大家要根據具體需求自己把控?

版權聲明:原創作品,允許轉載,轉載時務必以超鏈接的形式表明出處和作者信息。否則將追究法律責任。來自海牛部落-青牛,http://hainiubl.com/topics/36803
點贊
成為第一個點贊的人吧 :bowtie:
回復數量: 0
    暫無評論~~
    • 請注意單詞拼寫,以及中英文排版,參考此頁
    • 支持 Markdown 格式, **粗體**、~~刪除線~~、`單行代碼`, 更多語法請見這里 Markdown 語法
    • 支持表情,可用Emoji的自動補全, 在輸入的時候只需要 ":" 就可以自動提示了 :metal: :point_right: 表情列表 :star: :sparkles:
    • 上傳圖片, 支持拖拽和剪切板黏貼上傳, 格式限制 - jpg, png, gif,教程
    • 發布框支持本地存儲功能,會在內容變更時保存,「提交」按鈕點擊時清空
    Ctrl+Enter
    江西快三下期预测 福建体彩36选7走势图近100期 幸运农场官方 街机捕鱼单机版破解版 浙江20选5专家杀号 幸运赛车APP 山西11选5分布走势图 大嘴刨幺手机版官方下载 香港赛马会公式专家 二分彩万位是什么意思 新疆11选5基本走势图