本文目錄：
一. SQL語句
第二，SQL約束
三。SQL語句(DQL)
四。多表操作
動詞（verb的縮寫）多表關系實戰
不及物動詞多表查詢
一. SQL語句
1.SQL概述
SQL語句簡介
數據庫不懂JAVA語言，但是我們也需要和數據庫交互。這時候就需要用到SQL語句了，SQL語句就是數據庫的代碼。結構化查詢語言(SQL)是所有關系數據庫管理系統都應該遵循的規范。不同的數據庫廠商都支持SQL語句，但都有獨特的內容。
SQL語句分類
SQL分類：
定義語言：簡稱DDL(數據定義語言)，用于定義數據庫對象：數據庫、表、列等。關鍵詞：創建、更改、刪除等。
數據操作語言：簡稱DML(Data Manipulation Language)，用于更新數據庫中表格的記錄。關鍵詞：插入、刪除、更新等
數據控制語言：簡稱DCL(Data Control Language)，用于定義數據庫的訪問權限和安全級別，創建用戶。
查詢語言：簡稱DQL(Data Query Language)，用于查詢數據庫中表格的記錄。關鍵詞：選擇、從、從哪里等。
SQL常規語法
SQL語句可以寫成單行或多行，以分號結尾。
您可以使用空格和縮進來增強語句的可讀性。
MySQL數據庫的SQL語句不區分大小寫，建議使用大寫關鍵字。
示例：SELECT * FROM user。您也可以使用/* */來完成注釋。
我們在MySQL中經常使用的數據類型如下
* *類型名稱* *
解釋
int(整數)
整數類型
兩倍
十進制
十進制
指定整數的小數類型和小數長度。
日期
類型，格式為yyyy-MM-dd，包含年、月、日，但不包含時、分、秒。
日期時間
鍵入，格式為yyyy-mm-ddhh3360mm3360ss，包括年、月、日、時、分。
時間戳
類型，時間戳
可變字符(米)
類型，m是0到65535之間的整數。
2.DDL的數據庫操作：數據庫
創建數據庫
格式：
創建數據庫名稱；
創建數據庫名稱字符集字符集；
例如：
e>#創建數據庫 數據庫中數據的編碼采用的是安裝數據庫時指定的默認編碼 utf8CREATE DATABASE webdb_1;
#創建數據庫 并指定數據庫中數據的編碼
CREATE DATABASE webdb_2 CHARACTER SET utf8;
查看數據庫
查看數據庫MySQL服務器中的所有的數據庫:
show databases;查看某個數據庫的定義的信息:
show create database 數據庫名;例如：
show create database webdb_1;刪除數據庫
drop database 數據庫名稱;例如：
drop database webdb_2;使用數據庫
- 查看正在使用的數據庫:
select database();其他的數據庫操作命令
切換數據庫：
use 數據庫名;例如：use webdb_1;3. DDL之表操作：table
創建表
- 格式：
create table 表名(
字段名 類型(長度) [約束],
字段名 類型(長度) [約束],
...
);
類型：
varchar(n) 字符串
int 整形
double 浮點
date 時間
timestamp 時間戳
約束：(詳情查看第四章)
primary key 主鍵，被主鍵修飾字段中的數據，不能重復、不能為null。- 例如：創建分類表
CREATE TABLE category (
cid INT primary key, #分類ID
cname VARCHAR(100) #分類名稱
);
查看表
- 查看數據庫中的所有表：
格式：show tables;- 查看表結構：
格式：desc 表名;
例如：desc category;刪除表
- 格式：drop table 表名;
例如：drop table category;修改表結構格式
alter table 表名 add 列名 類型(長度) [約束];
作用：修改表添加列.
例如：#1，為分類表添加一個新的字段為 分類描述 varchar(20)
ALTER TABLE category ADD `desc` VARCHAR(20);alter table 表名 modify 列名 類型(長度) 約束;
作用：修改表修改列的類型長度及約束.
例如：#2, 為分類表的描述字段進行修改，類型varchar(50) 添加約束 not null
ALTER TABLE category MODIFY `desc` VARCHAR(50) NOT NULL;- alter table 表名 change 舊列名 新列名 類型(長度) 約束;作用：修改表修改列名.
例如：#3, 為分類表的分類名稱字段進行更換 更換為 description varchar(30)
ALTER TABLE category CHANGE `desc` description VARCHAR(30);alter table 表名 drop 列名;
作用：修改表刪除列.
例如：#4, 刪除分類表中description這列
ALTER TABLE category DROP description;rename table 表名 to 新表名;
作用：修改表名
例如：#5, 為分類表category 改名成 category2
RENAME TABLE category TO category2;alter table 表名 character set 字符集(了解);
作用：修改表的字符集
例如：#6, 為分類表 category 的編碼表進行修改，修改成 gbk
ALTER TABLE category CHARACTER SET gbk;4. DML數據操作語言
插入表記錄：insert
- 語法：
-- 向表中插入某些字段
insert into 表 (字段1,字段2,字段3..) values (值1,值2,值3..);
-- 向表中插入所有字段,字段的順序為創建表時的順序
insert into 表 values (值1,值2,值3..);- 注意：
- 值與字段必須對應，個數相同，類型相同
- 值的數據大小必須在字段的長度范圍內
- 除了數值類型外，其它的字段類型的值必須使用引號引起。（建議單引號）
- 如果要插入空值，可以不寫字段，或者插入null。
- 例如：
INSERT INTO category(cid,cname) VALUES('c001','電器');
INSERT INTO category(cid,cname) VALUES('c002','服飾');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');
INSERT INTO category(cid,cname) VALUES('c004','書籍');
INSERT INTO category(cid) VALUES('c005');
INSERT INTO category(cname,cid) VALUES('耗材','c006');更新表記錄：update
用來修改指定條件的數據，將滿足條件的記錄指定列修改為指定值
語法：
update 表名 set 字段名=值,字段名=值,...;update 表名 set 字段名=值,字段名=值,... where 條件;- 更新符合條件記錄的指定字段
- 更新所有記錄的指定字段
注意：
- 列名的類型與修改的值要一致.
- 修改值得時候不能超過最大長度.
- 除了數值類型外，其它的字段類型的值必須使用引號引起
刪除記錄：delete
- 語法：
delete from 表名 [where 條件];5. DOS操作數據亂碼解決
我們在dos命令行操作中文時，會報錯
insert into category(cid,cname) values(‘c010’,’中文’);
ERROR 1366 (HY000): Incorrect string value: '\xB7\xFE\xD7\xB0' for column 'cname' at row 1錯誤原因:因為mysql的客戶端設置編碼是utf8,而系統的cmd窗口編碼是gbk
- 查看MySQL內部設置的編碼
show variables like 'character%'; 查看所有mysql的編碼
需要修改client、connection、results的編碼一致（GBK編碼）
解決方案1：在cmd命令窗口中輸入命令，此操作當前窗口有效，為臨時方案。
set names gbk;解決方案2：安裝目錄下修改my.ini文件，重啟服務所有地方生效。
二、SQL約束
1. 主鍵約束
PRIMARY KEY 約束唯一標識數據庫表中的每條記錄。
- 主鍵必須包含唯一的值。
- 主鍵列不能包含 NULL 值。
- 每個表都應該有一個主鍵，并且每個表只能有一個主鍵。
添加主鍵約束
- 方式一：創建表時，在字段描述處，聲明指定字段為主鍵：
CREATE TABLE persons
(
id_p int PRIMARY KEY,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)- 方式二：創建表時，在constraint約束區域，聲明指定字段為主鍵：
- 格式：
[constraint 名稱] primary key (字段列表) - 關鍵字constraint可以省略，如果需要為主鍵命名，constraint不能省略，主鍵名稱一般沒用。
- 字段列表需要使用小括號括住，如果有多字段需要使用逗號分隔。聲明兩個以上字段為主鍵，我們稱為聯合主鍵。
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT pk_personID PRIMARY KEY (firstname,lastname)
)- 方式三：創建表之后，通過修改表結構，聲明指定字段為主鍵：
- 格式：
ALTER TABLE persons ADD [CONSTRAINT 名稱] PRIMARY KEY (字段列表)
CREATE TABLE persons
(
firstname varchar(255),
lastname varchar(255),
address varchar(255),
city varchar(255)
)
ALTER TABLE persons ADD PRIMARY KEY (firstname,lastname)刪除主鍵約束
如需撤銷 PRIMARY KEY 約束，請使用下面的 SQL：
ALTER TABLE persons DROP PRIMARY KEY2. 自動增長列
我們通常希望在每次插入新記錄時，數據庫自動生成字段的值。
我們可以在表中使用 auto_increment（自動增長列）關鍵字，自動增長列類型必須是整形，自動增長列必須為鍵(一般是主鍵)。
- 下列 SQL 語句把 "persons" 表中的 "p_id" 列定義為 auto_increment 主鍵
CREATE TABLE persons
(
p_id int PRIMARY KEY AUTO_INCREMENT,
lastname varchar(255),
firstname varchar(255),
address varchar(255),
city varchar(255)
)- 向persons添加數據時，可以不為p_id字段設置值，也可以設置成null，數據庫將自動維護主鍵值：
INSERT INTO persons (firstname,lastname) VALUES ('Bill','Gates')
INSERT INTO persons (p_id,firstname,lastname) VALUES (NULL,'Bill','Gates')- 擴展：默認AUTO_INCREMENT 的開始值是 1，如果希望修改起始值，請使用下列 SQL 語法：
ALTER TABLE persons AUTO_INCREMENT=100- 面試題
問：針對auto_increment ，刪除表中所有記錄使用 delete from 表名 或使用 truncate table 表名，二者有什么區別？
刪除方式：
delete 一條一條刪除，不清空auto_increment記錄數。
truncate 直接將表刪除，重新建表，auto_increment將置為零，從新開始。3. 非空約束
NOT NULL 約束強制列不接受 NULL 值。
NOT NULL 約束強制字段始終包含值。這意味著，如果不向字段添加值，就無法插入新記錄或者更新記錄。
- 下面的 SQL 語句強制 "id_p" 列和 "lastname" 列不接受 NULL 值：
CREATE TABLE persons
(
id_p int NOT NULL,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)4. 唯一約束
UNIQUE 約束唯一標識數據庫表中的每條記錄。UNIQUE 和 PRIMARY KEY 約束均為列或列集合提供了唯一性的保證。PRIMARY KEY 擁有自動定義的 UNIQUE 約束。請注意，每個表可以有多個 UNIQUE 約束，但是每個表只能有一個 PRIMARY KEY 約束。
添加唯一約束
與主鍵添加方式相同，共有3種
- 方式一：創建表時，在字段描述處，聲明唯一：
CREATE TABLE persons
(
id_p int UNIQUE,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255)
)- 方式二：創建表時，在約束區域，聲明唯一：
CREATE TABLE persons
(
id_p int,
lastname varchar(255) NOT NULL,
firstname varchar(255),
address varchar(255),
city varchar(255),
CONSTRAINT 名稱 UNIQUE (Id_P)
)- 方式三：創建表后，修改表結構，聲明字段唯一：
ALTER TABLE persons ADD [CONSTRAINT 名稱] UNIQUE (Id_P)刪除唯一約束
- 如需撤銷 UNIQUE 約束，請使用下面的 SQL：
ALTER TABLE persons DROP INDEX 名稱- 如果添加唯一約束時，沒有設置約束名稱，默認是當前字段的字段名。
5. 默認約束
default 約束 用于指定字段默認值。當向表中插入記錄時，如果沒有明確的為字段賦值，則自動賦予默認值。
- 添加默認約束，在創建表時候添加
CREATE TABLE t_user(user_id INT(10) DEFAULT 3);- 刪除默認約束
ALTER TABLE t_user MODIFY user_id INT(10);三、SQL語句(DQL)
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 條件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;3. 條件查詢
| 比較運算符 | < <= = = <> | 大于、小于、大于(小于)等于、不等于 |
|---|---|---|
| BETWEEN ...AND... | 顯示在某一區間的值(含頭含尾) | |
| IN(set) | 顯示在in列表中的值，例：in(100,200) | |
| LIKE ‘張pattern’ | 模糊查詢，Like語句中，% 代表零個或多個任意字符，_ 代表一個字符， 例如：first_name like '_a%'; | |
| IS NULL | 判斷是否為空 | |
| 邏輯運行符 | and | 多個條件同時成立 |
| or | 多個條件任一成立 | |
| not | 不成立，例：where not(salary>100); |
- 練習
#查詢商品名稱為“花花公子”的商品所有信息：
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 NULL4. 排序查詢
通過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;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;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;四、多表操作
實際開發中，一個項目通常需要很多張表才能完成。例如：一個商城項目就需要分類表(category)、商品表(products)、訂單表(orders)等多張表。且這些表的數據之間存在一定的關系，接下來我們將在單表的基礎上，一起學習多表方面的知識。
1. 表與表之間的關系
- 一對一關系
- 常見實例：客戶和訂單,商品和分類
- 一個訂單只能對應一個客戶,一個商品只能對應一個分類
- 一對多關系：
- 常見實例：客戶和訂單，分類和商品，部門和員工.
- 一個客戶端可以對應多個訂單
- 多對多關系：
- 常見實例：學生和課程、用戶和角色
- 多對多關系建表原則：需要創建第三張表,中間表中至少兩個字段，這兩個字段分別作為外鍵指向各自一方的主鍵.
2. 外鍵約束
現在我們有兩張表“分類表”和“商品表”，為了表明商品屬于哪個分類，通常情況下，我們將在商品表上添加一列，用于存放分類cid的信息，此列稱為：外鍵
此時“分類表category”稱為：主表，“cid”我們稱為主鍵?！吧唐繁韕roducts”稱為：從表，category_id稱為外鍵。我們通過主表的主鍵和從表的外鍵來描述主外鍵關系，呈現就是一對多關系。外鍵特點：
- 從表外鍵的值是對主表主鍵的引用。
- 從表外鍵類型，必須與主表主鍵類型一致。
聲明外鍵約束
語法：
alter table 從表 add [constraint][外鍵名稱] foreign key (從表外鍵字段名) references 主表 (主表的主鍵);
[外鍵名稱]用于刪除外鍵約束的，一般建議“_fk”結尾
alter table 從表 drop foreign key 外鍵名稱- 使用外鍵目的：
- 保證數據完整性
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. 多對多
分析
- 商品和訂單多對多關系，將拆分成兩個一對多。
- 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';五、多表關系實戰
1. 實戰1：省和市
- 方案1：多張表，一對多
2. 實戰2：用戶和角色
- 多對多關系
六、多表查詢
提供表結構如下：
# 分類表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
#商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架標記為：1表示上架、表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);1. 初始化數據
#分類
INSERT INTO category(cid,cname) VALUES('c001','家電');
INSERT INTO category(cid,cname) VALUES('c002','服飾');
INSERT INTO category(cid,cname) VALUES('c003','化妝品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','聯想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海爾',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真維斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','勁霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈兒',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');2. 多表查詢
- 交叉連接查詢(基本不會使用-得到的是兩個表的乘積) [了解]
- 語法：
select * from A,B;
- 內連接查詢(使用的關鍵字 inner join -- inner可以省略)
隱式內連接：
select * from A,B where 條件;顯示內連接：
select * from A inner join B on 條件;
#1.查詢哪些分類的商品已經上架
#隱式內連接
SELECT DISTINCT c.cname
FROM category c , products p
WHERE c.cid = p.category_id AND p.flag = '1';
#內連接
SELECT DISTINCT c.cname
FROM category c INNER JOIN products p
ON c.cid = p.category_id
WHERE p.flag = '1';
- 外連接查詢(使用的關鍵字 outer join -- outer可以省略)
- 左外連接：left outer join
select * from A left outer join B on 條件;- 右外連接：right outer join
select * from A right outer join B on 條件;
#2.查詢所有分類商品的個數
#左外連接
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
SELECT cname,COUNT(category_id)
FROM category c LEFT OUTER JOIN products p
ON c.cid = p.category_id
GROUP BY cname;
下面通過一張圖說明連接的區別:
3. 子查詢
子查詢：一條select語句結果作為另一條select語法一部分（查詢條件，查詢結果，表等）。語法：select ....查詢字段 ... from ... 表.. where ... 查詢條件
#3 子查詢, 查詢“化妝品”分類上架商品詳情
#隱式內連接
SELECT p.*
FROM products p , category c
WHERE p.category_id=c.cid AND c.cname = '化妝品';
#子查詢
###作為查詢條件
SELECT *
FROM products p
WHERE p.category_id =
(
SELECT c.cid FROM category c
WHERE c.cname='化妝品'
);
###作為另一張表
SELECT *
FROM products p ,
(SELECT * FROM category WHERE cname='化妝品') c
WHERE p.category_id = c.cid;
子查詢練習：
#查詢“化妝品”和“家電”兩個分類上架商品詳情
SELECT *
FROM products p
WHERE p.category_id in
(SELECT c.cid
FROM category c
WHERE c.cname='化妝品' or c.name='家電'
);--END--
原文
509006974