mysql查詢DQL&多表關系

• 能夠使用SQL陳述句查詢資料
• 能夠使用SQL陳述句進行條件查詢
• 能夠使用SQL陳述句進行排序
• 能夠使用聚合函式
• 能夠使用SQL陳述句進行分組查詢
• 能夠完成資料的備份和恢復
• 能夠使用可視化工具連接資料庫,操作資料庫
• 能夠說出多表之間的關系及其建表原則
• 能夠理解外鍵約束

一、SQL陳述句(DQL)

1.1、DQL準備作業和語法

準備作業

#創建商品表：
create table product( 
  pid int primary key,
  pname varchar(20),
  price double,
  category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'聯想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海爾',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真維斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'勁霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈兒',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你棗',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飄飄奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

語法

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

1.2、簡單查詢

練習

#查詢所有的商品 
select * from product; 

#查詢商品名和商品價格. 
select pname,price from product; 

#別名查詢.使用的關鍵字是as（as可以省略的）.表別名: 
select * from product as p; 

#別名查詢.使用的關鍵字是as（as可以省略的）.列別名: 
select pname as pn from product; 

#去掉重復值. 
select distinct price from product; 

#查詢結果是運算式（運算查詢）：將所有商品的價格+10元進行顯示. 
select pname,price+10 from product;

1.3 條件查詢

練習

#查詢商品名稱為“花花公子”的商品所有資訊： 
SELECT * FROM product WHERE pname = '花花公子';

#查詢價格為800商品 

SELECT * FROM product WHERE price = 800;

#查詢價格不是800的所有商品
SELECT * FROM product WHERE price != 800 SELECT * FROM product WHERE price <> 800 SELECT * FROM product WHERE NOT(price = 800);

#查詢商品價格大于60元的所有商品資訊
SELECT * FROM product WHERE price > 60; 

#查詢商品價格在200到1000之間所有商品
SELECT * FROM product WHERE price >= 200 AND price <=1000; SELECT * FROM product WHERE price BETWEEN 200 AND 1000;

#查詢商品價格是200或800的所有商品
SELECT * FROM product WHERE price = 200 OR price = 800; SELECT * FROM product WHERE price IN (200,800);

#查詢含有'霸'字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%'; 

#查詢以'香'開頭的所有商品
SELECT * FROM product WHERE pname LIKE '香%'; 

#查詢第二個字為'想'的所有商品
SELECT * FROM product WHERE pname LIKE '_想%'; 

#商品沒有分類的商品
SELECT * FROM product WHERE category_id IS NULL; 

#查詢有分類的商品
SELECT * FROM product WHERE category_id IS NOT NULL;

1.4、排序查詢

? 通過order by陳述句，可以將查詢出的結果進行排序，暫時放置在select陳述句的最后，

• 格式:

SELECT * FROM 表名 ORDER BY 排序欄位 ASC|DESC; #ASC 升序 (默認) #DESC 降序

• 練習：

#使用價格排序(降序)
SELECT * FROM product ORDER BY price DESC;

#在價格排序(降序)的基礎上，以分類排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;

#顯示商品的價格(去重復)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

1.5、聚合查詢

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

今天我們學習如下五個聚合函式：

• count：統計指定列不為NULL的記錄行數；
• sum：計算指定列的數值和，如果指定列型別不是數值型別，那么計算結果為0；
• max：計算指定列的最大值，如果指定列是字串型別，那么使用字串排序運算；
• min：計算指定列的最小值，如果指定列是字串型別，那么使用字串排序運算；
• avg：計算指定列的平均值，如果指定列型別不是數值型別，那么計算結果為0；

練習：

#查詢商品的總條數
SELECT COUNT(*) FROM product;

#查詢價格大于200商品的總條數
SELECT COUNT(*) FROM product WHERE price > 200;

#查詢分類為'c001'的所有商品的總和
SELECT SUM(price) FROM product WHERE category_id = 'c001'; 

#查詢分類為'c002'所有商品的平均價格
SELECT AVG(price) FROM product WHERE category_id = 'c002'; 

#查詢商品的最大價格和最小價格
SELECT MAX(price),MIN(price) FROM product;

1.6、分組查詢

? 分組查詢是指使用group by字句對查詢資訊進行分組，

• 格式：

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

? 分組操作中的having子陳述句，是用于在分組后對資料進行過濾的，作用類似于where條件，

• having與where的區別:

  • having是在分組后對資料進行過濾.

    where是在分組前對資料進行過濾

  • having后面可以使用分組函式(統計函式)

    where后面不可以使用分組函式，

練習：

#統計各個分類商品的個數
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;

#統計各個分類商品的個數,且只顯示個數大于1的資訊
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;

二、SQLyog（MySQL圖形化開發工具）

• 安裝：

  提供的SQLyog軟體為免安裝版，可直接使用

• 使用：

  輸入用戶名、密碼，點擊連接按鈕，進行訪問MySQL資料庫進行操作

? 在Query視窗中，輸入SQL代碼，選中要執行的SQL代碼，按F8鍵運行，或按執行按鈕運行，

三、SQL備份與恢復

3.1、SQL備份

? 資料庫的備份是指將資料庫轉換成對應的sql檔案

MySQL命令備份

? 資料庫匯出sql腳本的格式：

mysql dump -u用戶名 -p密碼 資料庫名>生成的腳本檔案路徑

例如:

mysql dump -uroot -proot day04>d:\day03.sql

? 以上備份資料庫的命令中需要用戶名和密碼，即表明該命令要在用戶沒有登錄的情況下使用

可視化工具備份

? 選中資料庫，右鍵 ”備份/匯出” ， 指定匯出路徑，保存成.sql檔案即可，

3.2、SQL恢復

? 資料庫的恢復指的是使用備份產生的sql檔案恢復資料庫，即將sql檔案中的sql陳述句執行就可以恢復資料庫內容，

MySQL命令恢復

? 使用資料庫命令備份的時候只是備份了資料庫內容，產生的sql檔案中沒有創建資料庫的sql陳述句，在恢復資料庫之
前需要自己動手創建資料庫，

• 在資料庫外恢復

  • 格式: mysql -uroot -p密碼 資料庫名 < 檔案路徑
  • 例如: mysql -uroot -proot day03<d:\day03.sql

• 在資料庫內恢復

  • 格式: source SQL腳本路徑
  • 例如: source d:\day03.sql
  • 注意:使用這種方式恢復資料，首先要登錄資料庫.

可視化工具恢復

? 資料庫串列區域右鍵“從SQL轉儲檔案匯入資料庫”， 指定要執行的SQL檔案，執行即可，

四、多表操作

? 實際開發中，一個專案通常需要很多張表才能完成，例如：一個商城專案就需要分類表(category)、商品表(products)、訂單表(orders)等多張表，且這些表的資料之間存在一定的關系，接下來我們將在單表的基礎上，一起學習多表方面的知識，

4.1、表與表之間的關系

• 一對多關系：
  • 常見實體：客戶和訂單，分類和商品，部門和員工，
  • 一對多建表原則：在從表(多方)創建一個欄位，欄位作為外鍵指向主表(一方)的主鍵.

• 多對多關系：
  • 常見實體：學生和課程、用戶和角色
  • 多對多關系建表原則：需要創建第三張表,中間表中至少兩個欄位，這兩個欄位分別作為外鍵指向各自一方的主鍵.

• 一對一關系：(了解)
  • 在實際的開發中應用不多.因為一對一可以創建成一張表.
  • 兩種建表原則：
    • 外鍵唯一：主表的主鍵和從表的外鍵（唯一），形成主外鍵關系，外鍵唯一unique，
    • 外鍵是主鍵：主表的主鍵和從表的主鍵，形成主外鍵關系，

4.2、外鍵約束

現在我們有兩張表“分類表”和“商品表”，為了表明商品屬于哪個分類，通常情況下，我們將在商品表上添加一列，

用于存放分類cid的資訊，此列稱為：外鍵

? 此時“分類表category”稱為：主表，“cid”我們稱為主鍵，“商品表products”稱為：從表，category_id稱為外鍵，我

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

• 外鍵特點：

  • 從表外鍵的值是對主表主鍵的參考，
  • 從表外鍵型別，必須與主表主鍵型別一致，

• 宣告外鍵約束

語法： 
alter table 從表 add [constraint][外鍵名稱] foreign key (從表外鍵欄位名) references 主表 (主 
表的主鍵); 

[外鍵名稱]用于洗掉外鍵約束的，一般建議“_fk”結尾 
alter table 從表 drop foreign key 外鍵名稱;

• 使用外鍵目的：
  • 保證資料完整性

4.3、一對多操作

分析

• category分類表，為一方，也就是主表，必須提供主鍵cid
• products商品表，為多方，也就是從表，必須提供外鍵category_id

實作：分類和商品

#創建分類表 
create table category( 
	cid varchar(32) PRIMARY KEY , 
	cname varchar(100) -- 分類名稱 
);
# 商品表 
CREATE TABLE `products` ( 
	`pid` varchar(32) PRIMARY KEY , 
	`name` VARCHAR(40) , 
	`price` DOUBLE 
);
#添加外鍵欄位 
alter table products add column category_id varchar(32); 
#添加約束 
alter table products add constraint product_fk foreign key (category_id) references 
category (cid); 

操作

#1 向分類表中添加資料 
INSERT INTO category (cid ,cname) VALUES('c001','服裝'); 
#2 向商品表添加普通資料,沒有外鍵資料，默認為null 
INSERT INTO products (pid,pname) VALUES('p001','商品名稱'); 
#3 向商品表添加普通資料，含有外鍵資訊(category表中存在這條資料) 
INSERT INTO products (pid ,pname ,category_id) VALUES('p002','商品名稱2','c001'); 
#4 向商品表添加普通資料，含有外鍵資訊(category表中不存在這條資料) -- 失敗,例外 
INSERT INTO products (pid ,pname ,category_id) VALUES('p003','商品名稱2','c999'); 
#5 洗掉指定分類(分類被商品使用) -- 執行例外
DELETE FROM category WHERE cid = 'c001'; 

4.4、多對多

分析

• 商品和訂單多對多關系，將拆分成兩個一對多，
• products商品表，為其中一個一對多的主表，需要提供主鍵pid
• orders 訂單表，為另一個一對多的主表，需要提供主鍵oid
• orderitem中間表，為另外添加的第三張表，需要提供兩個外鍵oid和pid

實作：訂單和商品

#商品表[已存在] 
#訂單表 
create table `orders`( 
	`oid` varchar(32) PRIMARY KEY , 
	`totalprice` double #總計 
);
#訂單項表 
create table orderitem( 
	oid varchar(50),-- 訂單id 
	pid varchar(50)-- 商品id 
);
#訂單表和訂單項表的主外鍵關系 
alter table `orderitem` add constraint orderitem_orders_fk foreign key (oid) references 
orders(oid); 
#商品表和訂單項表的主外鍵關系 
alter table `orderitem` add constraint orderitem_product_fk foreign key (pid) 
references products(pid); 
#聯合主鍵（可省略） 
alter table `orderitem` add primary key (oid,pid);

操作

#1 向商品表中添加資料 
INSERT INTO products (pid,pname) VALUES('p003','商品名稱'); 
#2 向訂單表中添加資料 
INSERT INTO orders (oid ,totalprice) VALUES('x001','998'); 
INSERT INTO orders (oid ,totalprice) VALUES('x002','100'); 
#3向中間表添加資料(資料存在) 
INSERT INTO orderitem(pid,oid) VALUES('p001','x001'); 
INSERT INTO orderitem(pid,oid) VALUES('p001','x002'); 
INSERT INTO orderitem(pid,oid) VALUES('p002','x002'); 
#4洗掉中間表的資料 
DELETE FROM orderitem WHERE pid='p002' AND oid = 'x002'; 
#5向中間表添加資料(資料不存在) -- 執行例外 
INSERT INTO orderitem(pid,oid) VALUES('p002','x003'); 
#6洗掉商品表的資料 -- 執行例外 
DELETE FROM products WHERE pid = 'p001';

標籤：其他

上一篇：直播電商爆火的2020年，營銷世界發生了哪些變化？

下一篇：Sql Server資料庫讀寫分離配置