創(chuàng)建表
例子:
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,'勝創(chuàng)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,'恒基偉業(yè)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);
如圖：
image
2、Where 條件查詢
①where expression
用法:expression為真,則該行取出
運用場合：
各種條件查詢場合,如按學號查學生,按價格查商品,按發(fā)布時間查新聞等
②select 5種子句 之where常用運算符
image
③select 5種子句 之where 匹配
like 模糊匹配 % 通配任意字符 _ 通配單一字符
2.1 取出goods表價格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from goods where shop_price <= 100;
結果：
image
2.2 取出第4欄目和第11欄目的商品(不許用or)
select goods_id,cat_id,goods_name,shop_price from goods where cat_id in (4, 11);
image
2.3 取出100<=價格<=500的商品(不許用and)
select goods_id,cat_id,goods_name,shop_price from goods where shop_price between 100 and 500;*
image
2.4取出名字以"諾基亞"開頭的商品
select goods_id,cat_id,goods_name,shop_price from goods where goods_name like '諾基亞%';
image
2.5取出名字為"諾基亞Nxx"的手機
select goods_id,cat_id,goods_name,shop_price from goods where goods_name like '諾基亞n__'; *注意是由兩個下滑線
image
2.6查出本店價格比市場價格省200元以上的商品
elect goods_id, goods_name,abs(market_price-shop_price) as discount from goods
where (market_price-shop_price)>200;
這里查詢要出錯，因為 market_price和shop_price的字段是decimal（10，2）unsignedunsigned字段相減，不能為負數，解決方案，把decimal(10,2) unsigned 改為decimal(10,2)
alter table goods modify column shop_price decimal(10,2);alter table goods modify column market_price decimal(10,2);有效位10位，保留2位小數，
image
3、group by 分組
①select 5種子句 之group與統(tǒng)計函數
max : 求最大 min : 求最小 sum : 求總和 avg : 求平均 count:求總行數
②select 5種子句 之group介紹
group by
作用:把行 按 字段 分組
語法:group by col1,col2,...colN
運用場合：
常見于統(tǒng)計場合,如按欄目計算帖子數, 統(tǒng)計每個人的平均成績等.
3.1 group與統(tǒng)計函數
max: 求最大值
min: 求最小值
sum: 求總和
avg: 求平均
count: 求總行數（count不能識別null）
3.2 計算每一種商品的庫存數量之和
select goods_name,sum(goods_number) from goods group by goods_name;
image
4、having 篩選
①select 5種子句 之having介紹
having 與where異同點
having 與where類似，可篩選數據
where后的表達式怎么寫，having就怎么寫
where針對表中的列發(fā)揮作用,查詢數據
having針對查詢結果中的列發(fā)揮作用,篩選數據
4.1 查詢每個欄目下，積壓的貨款，且篩選出積壓金額>20000的欄目
select cat_id,sum(goods_number*shop_price) as sumMoney from goods group by cat_id
having sumMoney > 20000
image
5、order by 排序
①select 5種子句 之order排序
Order by 排序功能
按一個或多個字段對查詢結果進行排序
用法:order by col1,col2,col3
知識點的運用場合描述
各種排序場合,如新聞按點擊量排序,商品按價格排序等
默認排序:升續(xù)排列
5.1 按欄目升序排列，同一欄目下的商品，再按商品的價格降序排列
asc代表升序，desc代表降序
select goods_id,cat_id,goods_name,shop_price
from goods
order by cat_id asc, shop_price DESC
image
6、limit 限制結果條數
①select 5種子句 之limit 介紹
Limit 限制條數
limit [offset,] N,限制結果取N條
用法: limit [偏移量,],取出條目
知識點的運用場合描述
分頁應用中最為典型,如第1頁取1-20條,第2頁取21-40條.
6.1 取出欄目3下，價格降序排列的前10條數據
select goods_id, cat_id, goods_name, shop_price
from goods
where cat_id = 3
order by shop_price asc
limit 10;
image
6.2 查詢本店商品價格從高到底排序的第三名到第五名的商品
select goods_id, goods_name, shop_price
from goods
order by shop_price
desc
limit 2, 3;
limit 2,3中，2代表偏移量，從3個開始數，3代表要3條數據。
image
7、where子查詢
7.1 查詢每個欄目下goods_id最大的商品
1、首先查出每個欄目下的goods_id
select max(goods_id),cat_id from goods
group by cat_id
2、把上面的查詢結果作為where的子句
select goods_id, goods_name from goods
where goods_id in
(select max(goods_id) from goods group by cat_id);
image
8、from子查詢
8.1 查詢每個欄目下goods_id最大的商品
1、首先查出每個欄目下商品價格從高到底排序的結果
select goods_id, goods_name from goods order by cat_id, shop_ price desc
2、把上面的查詢結果作為from的子句
select * from
(select goods_id,cat_id, goods_name from goods order by cat_id desc, goods_id desc)
as tmp group by cat_id;
image
9、exists子查詢
需要再建一張表，結合上面的goods表練習
create table category(
cat_id int auto_increment primary key,
cat_name varchar(20) not null default ''
)engine myisam charset utf8;
插入數據
insert into category(cat_name)values ('手機類型'),('CDMA手機'),('GSM手機'),('3G手機'),('雙模手機'),('手機配件'),('充電器'),('耳機'),('電池'),('讀卡器和內存'),('充值卡'),('小靈通/固話充值卡'),('移動手機充值卡'),('聯通手機充值卡');
image
9.1 把欄目下有商品的商品欄目取出來（不是每個cat_id里都有商品）
select cat_id,cat_name from category where exists
(select * from goods where goods.cat_id = category.cat_id);
image
