# MySQL復習
## 復習前的準備
1. 確認已安裝MySQL服務器并啟動mysqld服務
<!--2. 確認已安裝ecshop,并且ecshop的數據庫名為shop-->
2. 新建數據庫，并將如下SQL導入到數據庫中
```
create table goods (
goods_id mediumint(8) unsigned primary key auto_increment,
goods_name varchar(120) not null default '',
cat_id smallint(5) unsigned not null default '0',
brand_id smallint(5) unsigned not null default '0',
goods_sn char(15) not null default '',
goods_number smallint(5) unsigned not null default '0',
shop_price decimal(10,2) unsigned not null default '0.00',
market_price decimal(10,2) unsigned not null default '0.00',
click_count int(10) unsigned not null default '0'
) engine=myisam default charset=utf8;
insert into `goods` values (1,'kd876',4,8,'ecs000000',1,1388.00,1665.60,9),
(4,'諾基亞n85原裝充電器',8,1,'ecs000004',17,58.00,69.60,0),
(3,'諾基亞原裝5800耳機',8,1,'ecs000002',24,68.00,81.60,3),
(5,'索愛原裝m2卡讀卡器',11,7,'ecs000005',8,20.00,24.00,3),
(6,'勝創kingmax內存卡',11,0,'ecs000006',15,42.00,50.40,0),
(7,'諾基亞n85原裝立體聲耳機hs-82',8,1,'ecs000007',20,100.00,120.00,0),
(8,'飛利浦9@9v',3,4,'ecs000008',1,399.00,478.79,10),
(9,'諾基亞e66',3,1,'ecs000009',4,2298.00,2757.60,20),
(10,'索愛c702c',3,7,'ecs000010',7,1328.00,1593.60,11),
(11,'索愛c702c',3,7,'ecs000011',1,1300.00,0.00,0),
(12,'摩托羅拉a810',3,2,'ecs000012',8,983.00,1179.60,13),
(13,'諾基亞5320 xpressmusic',3,1,'ecs000013',8,1311.00,1573.20,13),
(14,'諾基亞5800xm',4,1,'ecs000014',1,2625.00,3150.00,6),
(15,'摩托羅拉a810',3,2,'ecs000015',3,788.00,945.60,8),
(16,'恒基偉業g101',2,11,'ecs000016',0,823.33,988.00,3),
(17,'夏新n7',3,5,'ecs000017',1,2300.00,2760.00,2),
(18,'夏新t5',4,5,'ecs000018',1,2878.00,3453.60,0),
(19,'三星sgh-f258',3,6,'ecs000019',12,858.00,1029.60,7),
(20,'三星bc01',3,6,'ecs000020',12,280.00,336.00,14),
(21,'金立 a30',3,10,'ecs000021',40,2000.00,2400.00,4),
(22,'多普達touch hd',3,3,'ecs000022',1,5999.00,7198.80,16),
(23,'諾基亞n96',5,1,'ecs000023',8,3700.00,4440.00,17),
(24,'p806',3,9,'ecs000024',100,2000.00,2400.00,35),
(25,'小靈通/固話50元充值卡',13,0,'ecs000025',2,48.00,57.59,0),
(26,'小靈通/固話20元充值卡',13,0,'ecs000026',2,19.00,22.80,0),
(27,'聯通100元充值卡',15,0,'ecs000027',2,95.00,100.00,0),
(28,'聯通50元充值卡',15,0,'ecs000028',0,45.00,50.00,0),
(29,'移動100元充值卡',14,0,'ecs000029',0,90.00,0.00,0),
(30,'移動20元充值卡',14,0,'ecs000030',9,18.00,21.00,1),
(31,'摩托羅拉e8 ',3,2,'ecs000031',1,1337.00,1604.39,5),
(32,'諾基亞n85',3,1,'ecs000032',4,3010.00,3612.00,9);
CREATE TABLE `brand` (
`brand_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`brand_name` varchar(60) NOT NULL DEFAULT '',
`brand_logo` varchar(80) NOT NULL DEFAULT '',
`brand_desc` text NOT NULL,
`site_url` varchar(255) NOT NULL DEFAULT '',
`sort_order` tinyint(3) unsigned NOT NULL DEFAULT '50',
`is_show` tinyint(1) unsigned NOT NULL DEFAULT '1',
PRIMARY KEY (`brand_id`),
KEY `is_show` (`is_show`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `brand` VALUES (1,'諾基亞','1240803062307572427.gif','公司網站：http://www.nokia.com.cn/\n\n客服電話：400-880-0123','http://www.nokia.com.cn/',50,1),
(2,'摩托羅拉','1240802922410634065.gif','官方咨詢電話：4008105050\n售后網點：http://www.motorola.com.cn/service/carecenter/search.asp ','http://www.motorola.com.cn',50,1),
(3,'多普達','1240803144788047486.gif','官方咨詢電話：4008201668\n售后網點：http://www.dopod.com/pc/service/searchresult2.php ','http://www.dopod.com ',50,1),
(4,'飛利浦','1240803247838195732.gif','官方咨詢電話：4008800008\n售后網
點：http://www.philips.com.cn/service/mustservice/index.page ','http://www.philips.com.cn ',50,1),
(5,'夏新','1240803352280856940.gif','官方咨
詢電話：4008875777\n售后網點：http://www.amobile.com.cn/service_fwyzc.asp ','http://www.amobile.com.cn',50,1),
(6,'三星','1240803412367015368.gif','官方咨詢電話：8008105858\n售后網點：http://cn.samsungmobile.com/cn/support/search_area_o.jsp ','http://cn.samsungmobile.com',50,1),
(7,'索愛','1240803482283160654.gif','官方咨詢電話：4008100000\n售后網點：http://www.sonyericsson.com/cws/common/contact?cc=cn&lc=zh ','http://www.sonyericsson.com.cn/',50,1),
(8,'LG','1240803526904622792.gif','官方咨詢電話：4008199999\n售后網點：http://www.lg.com.cn/front.support.svccenter.retrieveCenter.laf?hrefId=9 ','http://cn.wowlg.com',50,1),
(9,'聯想','1240803578417877983.gif','官方咨詢電話：4008188818\n售后網點：http://www.lenovomobile.com/service/kf-wanglou.asp','http://www.lenovomobile.com/',50,1),
(10,'金立','','官方咨詢電話：4007796666\n售后網點：http://www.gionee.net/service.asp ','http://www.gionee.net',50,1),
(11,' 恒基偉業','1240803736391383580.gif','官方咨詢電話：4008899126\n售后網點：http://www.htwchina.com/htwt/wexiu.shtml ','http://www.htwchina.com',50,1);
CREATE TABLE `comment` (
`comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`comment_type` tinyint(3) unsigned NOT NULL DEFAULT '0',
`id_value` mediumint(8) unsigned NOT NULL DEFAULT '0',
`email` varchar(60) NOT NULL DEFAULT '',
`user_name` varchar(60) NOT NULL DEFAULT '',
`content` text NOT NULL,
`comment_rank` tinyint(1) unsigned NOT NULL DEFAULT '0',
`add_time` int(10) unsigned NOT NULL DEFAULT '0',
`ip_address` varchar(15) NOT NULL DEFAULT '',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0',
`parent_id` int(10) unsigned NOT NULL DEFAULT '0',
`user_id` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`comment_id`),
KEY `parent_id` (`parent_id`),
KEY `id_value` (`id_value`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `comment` VALUES
(1,0,12,'ecshop@ecshop.com','ecshop','很好，我很喜歡',5,1242107120,'0.0.0.0',1,0,1),
(2,0,22,'ecshop@ecshop.com','ecshop','這個我不是很適合我',5,1242107295,'0.0.0.0',0,0,1);
DROP TABLE IF EXISTS `feedback`;
CREATE TABLE `feedback` (
`msg_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
`user_name` varchar(60) NOT NULL DEFAULT '',
`user_email` varchar(60) NOT NULL DEFAULT '',
`msg_title` varchar(200) NOT NULL DEFAULT '',
`msg_type` tinyint(1) unsigned NOT NULL DEFAULT '0',
`msg_status` tinyint(1) unsigned NOT NULL DEFAULT '0',
`msg_content` text NOT NULL,
`msg_time` int(10) unsigned NOT NULL DEFAULT '0',
`message_img` varchar(255) NOT NULL DEFAULT '0',
`order_id` int(11) unsigned NOT NULL DEFAULT '0',
`msg_area` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`msg_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO `feedback` VALUES
(1,0,1,'ecshop','ecshop@ecshop.com','三星SGH-F258什么時候到',4,0,'三星SGH-F258什么時候有貨',1242107197,'',0,0);
create table category (
cat_id smallint unsigned auto_increment primary key,
cat_name varchar(90) not null default '',
parent_id smallint unsigned
)engine myisam charset utf8;
INSERT INTO `category` VALUES
(1,'手機類型',0),
(2,'CDMA手機',1),
(3,'GSM手機',1),
(4,'3G手機',1),
(5,'雙模手機',1),
(6,'手機配件',0),
(7,'充電器',6),
(8,'耳機',6),
(9,'電池',6),
(11,'讀卡器和內存卡',6),
(12,'充值卡',0),
(13,'小靈通/固話充值卡',12),
(14,'移動手機充值卡',12),
(15,'聯通手機充值卡',12);
CREATE TABLE `result` (
`name` varchar(20) DEFAULT NULL,
`subject` varchar(20) DEFAULT NULL,
`score` tinyint(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into result
values
('張三','數學',90),
('張三','語文',50),
('張三','地理',40),
('李四','語文',55),
('李四','政治',45),
('王五','政治',30);
create table a (
id char(1),
num int
)engine myisam charset utf8;
insert into a values ('a',5),('b',10),('c',15),('d',10);
create table b (
id char(1),
num int
)engine myisam charset utf8;
insert into b values ('b',5),('c',15),('d',20),('e',99);
create table m(
mid int,
hid int,
gid int,
mres varchar(10),
matime date
)engine myisam charset utf8;
create table t (
tid int,
tname varchar(20)
)engine myisam charset utf8;
insert into m
values
(1,1,2,'2:0','2006-05-21'),
(2,2,3,'1:2','2006-06-21'),
(3,3,1,'2:5','2006-06-25'),
(4,2,1,'3:2','2006-07-21');
insert into t
values
(1,'國安'),
(2,'申花'),
(3,'布爾聯隊');
create table mian ( num int) engine myisam;
insert into mian values
(3),
(12),
(15),
(25),
(23),
(29),
(34),
(37),
(32);
create table user (
uid int primary key auto_increment,
name varchar(20) not null default '',
age smallint unsigned not null default 0
) engine myisam charset utf8;
```
## 基礎知識
### 數據庫的連接
```
mysql -h -u -p
-h host 主機
-u usernmae 用戶名
-p password 密碼
```
例如：
```
mysql -h127.0.0.1 -uroot -paaaaaa
```
### 庫級知識
1. 顯示數據庫: `show databases;`
```
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| app_blogcurder |
| mysql |
| performance_schema |
| shop |
+--------------------+
5 rows in set (0.00 sec)
```
2. 選擇數據庫: `use dbname;`
```
mysql> use test;
Database changed
```
**另外：** 通過 `\s` 查看當前所選擇的數據庫
```
mysql> \s
mysql Ver 14.14 Distrib 5.5.29, for Linux (x86_64) using readline 5.1
Connection id: 3
Current database: test
Current user: root@localhost
SSL: Not in use
```
3. 創建數據庫:
```
create database dbname charset utf8;
```
*建議指定字符集，羅在工作中遇到一個關于不同類型數據庫導入到處字符集的問題*
```
mysql> create database test charset utf8;
Query OK, 1 row affected (0.00 sec)
```
4. 刪除數據庫: `drop database dbname;`
```
mysql> drop database test;
Query OK, 0 rows affected (0.15 sec)
```
### 表級操作
#### 顯示庫下面的表
```
show tables;
```
**例如**
```
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)
```
#### 查看表的結構
```
desc tableName;
```
#### 查看表的創建過程
```
show create table tableName;
```
#### 創建表
```
create table tbName (
列名稱1 列類型 [列參數] [not null default ],
....列2...
....
列名稱N 列類型 [列參數] [not null default ]
)engine myisam/innodb charset utf8/gbk;
```
**例如**
```
create table user (
id int auto_increment,
name varchar(20) not null default '',
age tinyint unsigned not null default 0,
index id (id)
)engine=innodb charset=utf8;
```
> **注:**
> innodb是表引擎,也可以是myisam或其他。
> 但最常用的是myisam和innodb,charset 常用的有utf8,gbk;
#### 修改表
1. 修改表之增加列
```
alter table tbName add 列名稱１ 列類型 [列參數] [not null default] [comment '備注'] [after filedName];
```
> add之后的舊列名之后的語法和創建表時的列聲明一樣
**舉例**
```
alter table user add phone char(11) not null default '' comment '手機號碼' after `name`;
```
2. 修改表之修改列 **add**
I. `change`
```
alter table tbName change 舊列名 新列名 列類型 [列參數] [not null default] [comment '備注'] [after filedName];
```
> 注:舊列名之后的語法和創建表時的列聲明一樣
**舉例**
```
alter table user change phone tel char(11) not null default '' comment '手機號碼' after `name`;
```
II. `modify`
```
alter table tbName modify phone 列類型 [列參數] [not null default] [comment '備注'] [after filedName];
```
3. 修改表之減少列 **drop**
```
alter table tbName drop 列名稱;
```
```
mysql> alter table user drop tel;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
```
4. 修改表之增加主鍵
```
alter table tbName add primary key(主鍵所在列名);
```
**例:**`alter table goods add primary key(id)` 該例是把主鍵建立在id列上
5. 修改表之刪除主鍵
`alter table tbName drop primary key;`
6. 修改表之增加索引
`alter table tbName add [unique|fulltext] index 索引名(列名);`
7. 修改表之刪除索引
`alter table tbName drop index 索引名;`
8. 清空表的數據
`truncate tableName;`
### MySQL中的列類型
#### 列類型
##### 整型
**tinyint** (0~255/-128~127)
**smallint** (0~65535/-32768~32767)
**mediumint**
**int**
**bigint** (參考手冊11.2)
> **參數解釋**
> unsigned 無符號(不能為負)
> zerofill 0填充 M 填充后的寬度
**舉例**
```
tinyint unsigned;
tinyint(6) zerofill;
```
##### 數值型
浮點型:**float** **double**
> 格式:float(M,D) unsigned,zerofill;
##### 字符型
**char(m)** 定長
**varchar(m)** 變長
**text**
| 列 | 實存字符i | 實占空間 | 利用率 |
| -- | -- | -- | -- |
| char(M) | 0<=i<=M | M | i/m<=100% |
| varchar(M) | 0<=i<=M | i+1,2 | i/i+1/2<100% |
##### 日期時間類型
| 列類型 | 解釋 |
| -- | -- |
| year | YYYY 范圍:1901~2155. 可輸入值2位和4位(如98,2012) |
| date | YYYY-MM-DD 如:2010-03-14 |
| time | HH:MM:SS 如:19:26:32 |
| datetime | YYYY-MM-DD HH:MM:SS 如:2010-03-14 19:26:32 |
| timestamp | YYYY-MM-DD HH:MM:SS 特性:不用賦值,該列會為自己賦當前的具體時間 |
### 增刪改查基本操作
#### 插入數據
```
insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列
insert into 表名 values (,,,,); -- 插入所有列
insert into 表名 values -- 一次插入多行
(val1,val2……),
(val1,val2……),
(val1,val2……);
```
#### 修改數據
```
update tablename
set
col1=newval1,
col2=newval2,
...
...
colN=newvalN
where 條件;
```
#### 刪除數據
```
delete from tablenaeme where 條件;
```
#### select 查詢
1. 條件查詢**where**
```
a. 條件表達式的意義，表達式為真，則該行取出
b. 比較運算符 = ，!=，< > <= >=
c. like , not like ('%'匹配任意多個字符,'_'匹配任意單個字符)
in , not in , between and
d. is null , is not null
```
2. 分組 **group by**
一般要配合5個聚合函數使用:`max`,`min`,`sum`,`avg`,`count`
3. 篩選 **having**
4. 排序 **order by**
5. 限制取出條目 **limit**
### 連接查詢
#### 左連接 left join
```
.. left join .. on table A left join table B on tableA.col1 = tableB.col2 ;
```
**例句:**
```
select 列名 from table A left join table B on tableA.col1 = tableB.col2;
```
#### 右鏈接: right join
與左連接相同
#### 內連接: inner join
> 左右連接都是以在左邊的表的數據為準,沿著左表查右表.
> 內連接是以兩張表都有的共同部分數據為準,也就是左右連接的數據之交集
### 子查詢
#### where 型子查詢
內層sql的返回值在where后作為條件表達式的一部分
**例句**
```
select * from tableA where colA = (select colB from tableB where ...);
```
#### from 型子查詢
內層sql查詢結果,作為一張表,供外層的sql語句再次查詢
**例句**
```
select * from (select * from ...) as tableName where ...
```
#### exists 型子查詢
exists子查詢就是對外層表進行循環，再對內表進行內層查詢。和in ()差不多，但是它們還是有區別的。主要是看兩個張表大小差的程度。
若子查詢表大則用exists（內層索引），子查詢表小則用in（外層索引）;
## 查詢知識
> 以下查詢基于ecshop網站的商品表(goods)
> 在練習時可以只取部分列,方便查看。
### 基礎查詢 where
查出滿足以下條件的商品
#### 主鍵為32的商品
```
select goods_id,goods_name,shop_price
from goods
where goods_id=32;
```
#### 不屬第3欄目的所有商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id != 3;
```
#### 本店價格高于3000元的商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
where shop_price > 3000;
```
#### 本店價格低于或等于100元的商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
where shop_price <= 100;
```
#### 取出第4欄目或第11欄目的商品(不許用or)
```
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id in(4,11);
```
##### 使用or查詢
```
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id = 4 or cat_id = 11;
```
#### 取出100<=價格<=500的商品(不許用and)
```
select goods_id,cat_id,goods_name,shop_price
from goods
where shop_price between 100 and 500;
```
##### 使用and查詢
```
select goods_id,cat_id,goods_name,shop_price
from goods
where (shop_price >=100) and (shop_price <= 500);
```
#### 取出不屬于第3欄目且不屬于第11欄目的商品(and,或not in分別實現)
##### 使用and查詢
```
select goods_id,cat_id,goods_name,shop_price
from goods
where (cat_id != 3) and (cat_id != 11);
```
##### 使用not in查詢
```
select goods_id,cat_id,goods_name,shop_price
from goods
where cat_id not in (3,11);
```
#### 取出價格大于100且小于300,或者大于4000且小于5000的商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
where (300 > shop_price > 100) or (5000 > shop_price > 4000);
```
#### 取出第3個欄目下面價格<1000或>3000,并且點擊量>5的系列商品
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
where (cat_id = 3) and (3000>shop_price or shop_price>1000) and (click_count>5);
```
#### 取出名字以"諾基亞"開頭的商品
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
where goods_name like "諾基亞%";
```
#### 取出名字為"諾基亞Nxx"的手機
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
where goods_name like "諾基亞N__";
```
#### 取出名字不以"諾基亞"開頭的商品
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
where goods_name not like "諾基亞%";
```
#### 取出第3個欄目下面價格在1000到3000之間,并且點擊量>5 "諾基亞"開頭的系列商品
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
where cat_id = 3 and shop_price > 1000 and shop_price < 3000 and click_count > 5 and goods_name like '諾基亞%';
```
### 分組查詢 group by
#### 查出最貴的商品的價格
```
select max(shop_price) from goods;
```
#### 查出最大(最新)的商品編號[goods_id最大為最新]
```
select max(goods_id) from goods;
```
#### 查出最便宜的商品的價格
```
select min(shop_price) from goods;
```
#### 查出最舊(最小)的商品編號
```
select min(goods_id) from goods;
```
#### 查詢該店所有商品的庫存總量
```
select sum(goods_number)
from goods;
```
#### 查詢所有商品的平均價
```
select avg(goods_number)
from goods;
```
#### 查詢該店一共有多少種商品
```
select count(*)
from goods;
```
### having與group綜合運用查詢
#### 查詢該店的商品比市場價所節省的價格
```
select market_price-shop_price as sheng
from goods;
```
#### 查詢每個商品所積壓的貨款(提示:庫存*單價)
```
select shop_price*goods_number
from goods;
```
#### 查詢該店積壓的總貨款
```
select sum(shop_price*goods_number)
from goods;
```
#### 查詢該店每個欄目下面積壓的貨款
```
select cat_id,sum(shop_price*goods_number)
from goods
group bu cat_id;
```
#### 查詢比市場價省錢200元以上的商品及該商品所省的錢(where和having分別實現)
##### where實現
```
select goods_id,goods_name,market_price,shop_price,market_price-shop_price as sheng
from goods
where market_price-shop_price > 200
```
##### having實現
```
select goods_id,goods_name,market_price,shop_price,market_price-shop_price as sheng
from goods
having sheng > 200;
```
#### 查詢積壓貨款超過2W元的欄目,以及該欄目積壓的貨款
```
select cat_id,sum(goods_number*shop_price) as chao
from goods
group by cat_id having chao > 20000;
```
#### where-having-group綜合練習題
有如下表及數據
| name | subject | score |
| -- | -- | -- |
| 張三 | 數學 | 90 |
| 張三 | 語文 | 50 |
| 張三 | 地理 | 40 |
| 李四 | 語文 | 55 |
| 李四 | 政治 | 45 |
| 王五 | 政治 | 30 |
要求:查詢出2門及2門以上不及格者的平均成績?
```
select name,sum(score < 60) as gk ,avg(score) as pj
from stu
group by name having gk >=2;
```
### order by 與 limit查詢
#### 按價格由高到低排序
```
select goods_id,cat_id,goods_name,shop_price,click_count
from goods
order by shop_price desc;
```
#### 按欄目由低到高排序,欄目內部按價格由高到低排序
```
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id ,shop_price desc;
```
#### 取出價格最高的前三名商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
order by shop_price desc limit 0,3;
```
#### 取出點擊量前三名到前5名的商品
```
select goods_id,cat_id,goods_name,shop_price
from goods
order by click_count desc limit 2,3;
```
### 連接查詢
#### 取出所有商品的商品名,欄目名,價格
##### 使用左連接查詢
```
select g.goods_name,c.cat_name,shop_price
from goods as g
left join category as c on g.cat_id = c.cat_id;
```
##### 可以使用如下語句
```
select g.goods_name,c.cat_name,shop_price
from goods as g,category as c
where g.cat_id = c.cat_id;
```
#### 取出第4個欄目下的商品的商品名,欄目名,價格
##### 左連接查詢
```
select g.goods_name,c.cat_name,g.shop_price
from goods as g
left join category as c on g.cat_id=c.cat_id where g.cat_id = 4;
```
##### 可以使用下列語句
```
select g.goods_name,c.cat_name,g.shop_price
from goods as g ,category as c where g.cat_id=c.cat_id and g.cat_id = 4;
```
#### 取出第4個欄目下的商品的商品名,欄目名,與品牌名
```
select goods_name,cat_name,brand_name from
goods left join category
on goods.cat_id=category.cat_id
left join brand
on goods.brand_id=brand.brand_id
where goods.cat_id = 4;
```
### union查詢
#### 把comment,feedback兩個表中的數據,各取出4列,并把結果集union成一個結果集.
```
select comment_id,user_id,user_name,email,content,add_time from comment limit 4
union
select msg_id,user_id,user_name,user_email,msg_content,msg_time from feedback limit4;
```
### 子查詢
#### 查詢出最新一行商品(以商品編號最大為最新,用子查詢實現)
```
select goods_id,goods_name
from goods
where goods_id = (select max(goods_id) from goods);
```
#### 查詢出編號為19的商品的欄目名稱(用左連接查詢和子查詢分別)
##### 左連接實現
```
select g.goods_name,g.cat_id,cat_name
from goods as g
left join category as c on g.cat_id = c.cat_id
where g.goods_id = 19;
```
##### 子查詢實現
```
select cat_id,cat_name
from category as c
where cat_id = (select cat_id from goods where goods_id= 19 );
```
##### 普通查詢方式實現
```
select c.cat_id,cat_name
from category as c,goods as g
where c.cat_id =g.cat_id and g.goods_id= 19 ;
```
#### 用where型子查詢把goods表中的每個欄目下面最新的商品取出來
```
select goods_name,cat_id
from goods
where goods_id in (select max(goods_id) from goods group by cat_id);
```
#### 用from型子查詢把goods表中的每個欄目下面最新的商品取出來
```
select *
from (select goods_id,cat_id,goods_name from goods order by goods_id desc) as t group by cat_id;
```
#### 用exists型子查詢,查出所有有商品的欄目
```
select *
from category
where exists (select * from goods where goods.cat_id=category.cat_id);
```
- 寫在前面
- MySQL的使用
- MySQL多表同時刪除方案
- MySQL跨表、多表更新SQL語句總結
- MySQL存儲引擎
- 安裝
- 常規方式編譯安裝MySQL
- 采用cmake方式編譯安裝MySQL
- 使用rpm包安裝MySQL
- 使用yum方式安裝MySQL
- 采用二進制方式免編譯安裝MySQL
- 多實例的安裝
- 什么是多實例
- 多實例的作用、問題以及應用場景
- 多實例安裝01【推薦】
- 多實例官方安裝方案02
- 啟動、用戶和權限管理
- 單實例MySQL的啟動和關閉的方法
- 設置及修改MySQL root用戶密碼
- 找回丟失的MySQL root用戶密碼
- 創建MySQL用戶及用戶權限管理
- 基礎命令的操作
- MySQL庫和表相關操作
- MySQL中的索引操作
- MySQL常用命令
- MySQL的錯誤代碼
- MySQL復習秘籍
- 備份與恢復
- 備份
- 恢復
- mysqlbinlog命令
- 服務日志
- 主從復制
- 主從復制部署配置問題匯總
- 主從復制讀寫分離
- 災難恢復
- 配置phpmyadmin連接多實例MySQL
- 其他相關
- Sphinx實驗
- 中文分詞技術
- MySQL語句大全
- 用戶創建、權限、刪除
- 數據庫與表顯示、創建、刪除
- 表復制及備份還原
- 數據庫表中數據操作
- 修改表的列與表名
- 修改表中的數據
- 查詢表
- 日志
- 批量修改Mysql表引擎為InnoDB的方法
- 數據庫抽象層 PDO
- PDO對象常用方法
- PDO 事務處理
- PDO 與 MySQLi 二者效率簡單比較
- 大小寫敏感性 lower_case_table_names
- CentOS7安裝MySQL5.7密碼查看與修改