MySQL筆記複習（例項全）

mysql複習

一:複習前的準備

1:確認你已安裝wamp

2:確認你已安裝ecshop,並且ecshop的資料庫名為shop

二基礎知識:

1.資料庫的連線

mysql -u -p -h

-u 使用者名稱

-p 密碼

-h host主機

2:庫級知識

2.1 顯示資料庫: show databases;

2.2 選擇資料庫: use dbname;

2.3 建立資料庫: create database dbname charset utf8;

2.3 刪除資料庫: drop database dbname;

3: 表級操作:

3.1 顯示庫下面的表

show tables;

3.2 檢視錶的結構:

desc tableName;

3.3 檢視錶的建立過程:

show create table tableName;

3.4 建立表:

　createtable tbName (

列名稱1　列型別　[列引數]　[notnull default ],

....列2...

....

列名稱N　列型別　[列引數]　[notnull default ]

)engine myisam/innodb charset utf8/gbk

3.4的例子:

create table user (

id intauto_increment,

name varchar(20)not null default '',

agetinyint unsigned not null default 0,

index id (id)

)engine=innodbcharset=utf8;

注:innodb是表引擎,也可以是myisam或其他,但最常用的是myisam和innodb,

charset 常用的有utf8,gbk;

3.5 修改表

3.5.1 修改表之增加列:

alter table tbName

add 列名稱１　列型別　[列引數]　[not null default ]　#(add之後的舊列名之後的語法和建立表時的列宣告一樣)

3.5.2 修改表之修改列

alter table tbName

change 舊列名新列名列型別　[列引數]　[notnull default ]

(注:舊列名之後的語法和建立表時的列宣告一樣)

3.5.3 修改表之減少列:

alter table tbName

drop 列名稱;

3.5.4 修改表之增加主鍵

alter table tbName add primary key(主鍵所在列名);

例:altertable goods add primary key(id)

該例是把主鍵建立在id列上

3.5.5 修改表之刪除主鍵

alter table tbName　drop primary key;

3.5.6 修改表之增加索引

alter table tbName add [unique|fulltext] index 索引名(列名);

3.5.7 修改表之刪除索引

alter table tbName drop index 索引名;

3.5.8 清空表的資料

truncate tableName;

4:列型別講解

列型別:

整型:tinyint(0~255/-128~127) smallint (0~65535/-32768~32767) mediumint int bigint (參考手冊11.2)

引數解釋:

unsigned 無符號(不能為負) zerofill 0填充 M 填充後的寬度

舉例:tinyintunsigned;

tinyint(6) zerofill;

數值型

浮點型:floatdouble

格式: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 特性:不用賦值,該列會為自己賦當前的具體時間

5:增刪改查基本操作

5.1 插入資料

insert into 表名(col1,col2,……)values(val1,val2……); -- 插入指定列

insert into 表名values (,,,,); -- 插入所有列

insert into 表名values -- 一次插入多行

(val1,val2……),

(val1,val2……);

5.3修改資料

update tablename

set

col1=newval1,

col2=newval2,

...

colN=newvalN

where 條件;

5.4，刪除資料 deletefrom tablenaeme where 條件;

5.5， 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

6: 連線查詢

6.1，左連線

.. left join ..on

table A left jointable B on tableA.col1 = tableB.col2 ;

例句:

select 列名from table A left join table B on tableA.col1 = tableB.col2

2. 右連結:right join

3. 內連線: inner join

左右連線都是以在左邊的表的資料為準,沿著左表查右表.

內連線是以兩張表都有的共同部分資料為準,也就是左右連線的資料之交集.

7 子查詢

where 型子查詢:內層sql的返回值在where後作為條件表示式的一部分

例句:select * from tableA where colA = (select colB from tableB where ...);

from 型子查詢:內層sql查詢結果,作為一張表,供外層的sql語句再次查詢

例句:select* from (select * from ...) as tableName where ....

8: 字符集

客服端sql編碼character_set_client

伺服器轉化後的sql編碼 character_set_connection

伺服器返回給客戶端的結果集編碼 character_set_results

快速把以上3個變數設為相同值: set names 字符集

儲存引擎engine=1\2

1 Myisam 速度快不支援事務回滾

2 Innodb 速度慢支援事務,回滾

①開啟事務 start transaction

②執行sql;

③提交,同時生效\回滾commit\rollback

觸發器trigger

監視地點:表

監視行為:增刪改

觸發時間:after\before

觸發事件:增刪改

建立觸發器語法

create triggertgName

after/beforeinsert/delete/update

on tableName

for each row

sql; -- 觸發語句

刪除觸發器:droptrigger tgName;

索引

提高查詢速度,但是降低了增刪改的速度,所以使用索引時,要綜合考慮.

索引不是越多越好,一般我們在常出現於條件表示式中的列加索引.

值越分散的列，索引的效果越好

索引型別

primary key主鍵索引

index 普通索引

unique index 唯一性索引

fulltext index 全文索引

綜合練習:

連線上資料庫伺服器

建立一個gbk編碼的資料庫

建立商品表和欄目表,欄位如下:

商品表:goods

goods_id　--主鍵,

goods_name -- 商品名稱

cat_id -- 欄目id

brand_id -- 品牌id

goods_sn -- 貨號

goods_number -- 庫存量

shop_price -- 價格

goods_desc　--商品詳細描述

欄目表:category

cat_id --主鍵

cat_name -- 欄目名稱

parent_id -- 欄目的父id

建表完成後,作以下操作:

刪除goods表的goods_desc欄位,及貨號欄位

並增加欄位:click_count -- 點選量

在goods_name列上加唯一性索引

在shop_price列上加普通索引

在clcik_count列上加普通索引

刪除click_count列上的索引

對goods表插入以下資料:

+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+

+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+

| 1 |KD876 | 4 | 8 | ECS000000 | 10 | 1388.00 | 7 |

| 4 | 諾基亞N85原裝充電器 | 8 | 1 | ECS000004 | 17 | 58.00 | 0 |

| 3 | 諾基亞原裝5800耳機 | 8 | 1 | ECS000002 | 24| 68.00 | 3 |

| 5 | 索愛原裝M2卡讀卡器 | 11 | 7 | ECS000005 | 8 | 20.00 | 3 |

| 6 | 勝創KINGMAX記憶體卡 | 11 | 0 | ECS000006 | 15| 42.00 | 0 |

| 7 | 諾基亞N85原裝立體聲耳機HS-82 | 8 | 1 | ECS000007 | 20 | 100.00 | 0 |

| 8 | 飛利浦9@9v | 3 | 4 | ECS000008 | 17 | 399.00 | 9 |

| 9 | 諾基亞E66 | 3 | 1 | ECS000009 | 13 | 2298.00 | 20 |

| 10 | 索愛C702c | 3 | 7 | ECS000010 | 7 | 1328.00 | 11 |

| 11 | 索愛C702c | 3 | 7 | ECS000011 | 1 | 1300.00 | 0 |

| 12 | 摩托羅拉A810 | 3 | 2 | ECS000012 | 8| 983.00 | 14 |

| 13 | 諾基亞5320XpressMusic | 3 | 1 | ECS000013 | 8 | 1311.00 | 13 |

| 14 | 諾基亞5800XM | 4 | 1 | ECS000014 | 4 | 2625.00 | 6 |

| 15 | 摩托羅拉A810 | 3 | 2 | ECS000015 | 3| 788.00 | 8 |

| 16 | 恆基偉業G101 | 2 | 11 | ECS000016 | 0| 823.33 | 3 |

| 17 | 夏新N7 | 3 | 5 | ECS000017 | 1 | 2300.00 | 2 |

| 18 | 夏新T5 | 4 | 5 | ECS000018 | 1 | 2878.00 | 0 |

| 19 | 三星SGH-F258 | 3 | 6 | ECS000019 | 0 | 858.00 | 7 |

| 20 | 三星BC01 | 3 | 6 | ECS000020 | 13 | 280.00 | 14 |

| 21 | 金立A30 | 3 | 10 | ECS000021 | 40 | 2000.00 | 4 |

| 22 | 多普達TouchHD | 3 | 3 | ECS000022 | 0 | 5999.00 | 15 |

| 23 | 諾基亞N96 | 5 | 1 | ECS000023 | 8| 3700.00 | 17 |

| 24 |P806 | 3 | 9 | ECS000024 | 148| 2000.00 | 36 |

| 25 | 小靈通/固話50元充值卡 | 13 | 0 | ECS000025 | 2 | 48.00 | 0 |

| 26 | 小靈通/固話20元充值卡 | 13 | 0 | ECS000026 | 2 | 19.00 | 0 |

| 27 | 聯通100元充值卡 | 15 | 0 | ECS000027 | 2 | 95.00 | 0 |

| 28 | 聯通50元充值卡 | 15 | 0 | ECS000028 | 0| 45.00 | 0 |

| 29 | 移動100元充值卡 | 14 | 0 | ECS000029 | 0| 90.00 | 0 |

| 30 | 移動20元充值卡 | 14 | 0 | ECS000030 | 9| 18.00 | 1 |

| 31 | 摩托羅拉E8 | 3 | 2 | ECS000031 | 1| 1337.00 | 5 |

| 32 | 諾基亞N85 | 3 | 1 | ECS000032 | 1 | 3010.00 | 9 |

+----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+

三查詢知識

注:以下查詢基於ecshop網站的商品表(ecs_goods)

在練習時可以只取部分列,方便檢視.

1: 基礎查詢 where的練習:

查出滿足以下條件的商品

1.1:主鍵為32的商品

select goods_id,goods_name,shop_price

from ecs_goods

wheregoods_id=32;

1.2:不屬第3欄目的所有商品

select goods_id,cat_id,goods_name,shop_price from ecs_goods

where cat_id!=3;

1.3:本店價格高於3000元的商品

selectgoods_id,cat_id,goods_name,shop_price from ecs_goods

where shop_price>3000;

1.4:本店價格低於或等於100元的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price <=100;

1.5:取出第4欄目或第11欄目的商品(不許用or)

select goods_id,cat_id,goods_name,shop_price from ecs_goods

where cat_id in(4,11);

1.6:取出100<=價格<=500的商品(不許用and)

select goods_id,cat_id,goods_name,shop_price from ecs_goods

where shop_pricebetween 100 and 500;

1.7:取出不屬於第3欄目且不屬於第11欄目的商品(and,或not in分別實現)

select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3 and cat_id!=11;

select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);

1.8:取出價格大於100且小於300,或者大於4000且小於5000的商品()

select goods_id,cat_id,goods_name,shop_price from ecs_goods where shop_price>100 andshop_price <300 or shop_price >4000 and shop_price <5000;

1.9:取出第3個欄目下面價格<1000或>3000,並且點選量>5的系列商品

select goods_id,cat_id,goods_name,shop_price,click_countfrom ecs_goods where

cat_id=3 and (shop_price <1000 or shop_price>3000)and click_count>5;

1.10:取出第1個欄目下面的商品(注意:1欄目下面沒商品,但其子欄目下有)

select goods_id,cat_id,goods_name,shop_price,click_countfrom ecs_goods

where cat_id in(2,3,4,5);

1.11:取出名字以"諾基亞"開頭的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goods where goods_name like '諾基亞%';

1.12:取出名字為"諾基亞Nxx"的手機

select goods_id,cat_id,goods_name,shop_price from ecs_goods

where goods_namelike '諾基亞N__';

1.13:取出名字不以"諾基亞"開頭的商品

select goods_id,cat_id,goods_name,shop_price from ecs_goos

where goods_namenot like '諾基亞%';

1.14:取出第3個欄目下面價格在1000到3000之間,並且點選量>5"諾基亞"開頭的系列商品

select goods_id,cat_id,goods_name,shop_price from ecs_goods where

cat_id=3 and shop_price>1000 and shop_price <3000 andclick_count>5 and goods_name like '諾基亞%';

select goods_id,cat_id,goods_name,shop_price from ecs_goods where

shop_price between 1000 and 3000 and cat_id=3 and click_count>5 and goods_name like '諾基亞%';

一道面試題

有如下表和陣列

把num值處於[20,29]之間,改為20

num值處於[30,39]之間的,改為30

mian表

+------+

| num |

+------+

| 3 |

| 12 |

| 15 |

| 25 |

| 23 |

| 29 |

| 34 |

| 37 |

| 32 |

| 45 |

| 48 |

| 52 |

+------+

練習題:

把good表中商品名為'諾基亞xxxx'的商品,改為'HTCxxxx',

提示:大膽的把列看成變數,參與運算,甚至呼叫函式來處理 .

substring(),concat()

2 分組查詢group:

2.1:查出最貴的商品的價格

select max(shop_price) from ecs_goods;

2.2:查出最大(最新)的商品編號

select max(goods_id) from ecs_goods;

2.3:查出最便宜的商品的價格

select min(shop_price) from ecs_goods;

2.4:查出最舊(最小)的商品編號

select min(goods_id) from ecs_goods;

2.5:查詢該店所有商品的庫存總量

select sum(goods_number) from ecs_goods;

2.6:查詢所有商品的平均價

selectavg(shop_price) from ecs_goods;

2.7:查詢該店一共有多少種商品

select count(*) fromecs_goods;

2.8:查詢每個欄目下面

最貴商品價格

最低商品價格

商品平均價格

商品庫存量

商品種類

提示:(5個聚合函式,sum,avg,max,min,count與group綜合運用)

select cat_id,max(shop_price) from ecs_goods group by cat_id;

3 having與group綜合運用查詢:

3.1:查詢該店的商品比市場價所節省的價格

select goods_id,goods_name,market_price-shop_price as j

from ecs_goods ;

3.2:查詢每個商品所積壓的貨款(提示:庫存*單價)

select goods_id,goods_name,goods_number*shop_price from ecs_goods

3.3:查詢該店積壓的總貨款

select sum(goods_number*shop_price) from ecs_goods;

3.4:查詢該店每個欄目下面積壓的貨款.

select cat_id,sum(goods_number*shop_price) as k fromecs_goods group by cat_id;

3.5:查詢比市場價省錢200元以上的商品及該商品所省的錢(where和having分別實現)

select goods_id,goods_name,market_price-shop_price as k from ecs_goods

where market_price-shop_price >200;

select goods_id,goods_name,market_price-shop_price as k from ecs_goods

having k >200;

3.6:查詢積壓貨款超過2W元的欄目,以及該欄目積壓的貨款

select cat_id,sum(goods_number*shop_price) as k fromecs_goods group by cat_id

having k>20000

3.7:where-having-group綜合練習題

有如下表及資料

+------+---------+-------+

| name | subject | score |

+------+---------+-------+

| 張三 | 數學 | 90 |

| 張三 | 語文 | 50 |

| 張三 | 地理 | 40 |

| 李四 | 語文 | 55 |

| 李四 | 政治 | 45 |

| 王五 | 政治 | 30 |

+------+---------+-------+

要求:查詢出2門及2門以上不及格者的平均成績

## 一種錯誤做法

mysql> select name,count(score<60) as k,avg(score)from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 張三 | 3| 60.0000 |

| 李四 | 2 | 50.0000 |

+------+---+------------+

2 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score)from stu group by name;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 張三 | 3| 60.0000 |

| 李四 | 2| 50.0000 |

| 王五 | 1| 30.0000 |

+------+---+------------+

3 rows in set (0.00 sec)

mysql> select name,count(score<60) as k,avg(score)from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 張三 | 3| 60.0000 |

| 李四 | 2| 50.0000 |

+------+---+------------+

2 rows in set (0.00 sec)

#加上趙六後錯誤暴露

mysql> insert into stu

-> values

-> ('趙六','A',100),

-> ('趙六','B',99),

-> ('趙六','C',98);

Query OK, 3 rows affected (0.05 sec)

Records: 3 Duplicates: 0 Warnings: 0

#錯誤顯現

mysql> select name,count(score<60) as k,avg(score)from stu group by name having k>=2;

+------+---+------------+

| name | k | avg(score) |

+------+---+------------+

| 張三 | 3 | 60.0000 |

| 李四 | 2 | 50.0000 |

| 趙六 | 3 | 99.0000 |

+------+---+------------+

3 rows in set (0.00 sec)

#正確思路,先檢視每個人的平均成績

mysql> select name,avg(score) from stu group by name;

+------+------------+

| name | avg(score) |

+------+------------+

| 張三 | 60.0000 |

| 李四 | 50.0000 |

| 王五 | 30.0000 |

| 趙六 | 99.0000 |

+------+------------+

4 rows in set (0.00 sec)

mysql> # 看每個人掛科情況

mysql> select name,score < 60 from stu;

+------+------------+

| name | score < 60 |

+------+------------+

| 張三 | 0 |

| 張三 | 1 |

| 李四 | 1 |

| 王五 | 1 |

| 趙六 | 0 |

+------+------------+

9 rows in set (0.00 sec)

mysql> #計算每個人的掛科科目

mysql> select name,sum(score < 60) from stu group byname;

+------+-----------------+

| name | sum(score < 60) |

+------+-----------------+

| 張三 | 2 |

| 李四 | 2 |

| 王五 | 1 |

| 趙六 | 0 |

+------+-----------------+

4 rows in set (0.00 sec)

#同時計算每人的平均分

mysql> select name,sum(score < 60),avg(score) as pjfrom stu group by name;

+------+-----------------+---------+

| name | sum(score < 60) | pj |

+------+-----------------+---------+

| 張三 | 2 | 60.0000 |

| 李四 | 2 | 50.0000 |

| 王五 | 1 | 30.0000 |

| 趙六 | 0 | 99.0000 |

+------+-----------------+---------+

4 rows in set (0.00 sec)

#利用having篩選掛科2門以上的.

mysql> select name,sum(score < 60) as gk ,avg(score)as pj from stu group by name having gk >=2;

+------+------+---------+

| name | gk |pj |

+------+------+---------+

| 張三 | 2 |60.0000 |

| 李四 | 2 |50.0000 |

+------+------+---------+

2 rows in set (0.00 sec)

4: order by 與limit查詢

4.1:按價格由高到低排序

select goods_id,goods_name,shop_price from ecs_goods orderby shop_price desc;

4.2:按釋出時間由早到晚排序

select goods_id,goods_name,add_time from ecs_goods order byadd_time;

4.3:接欄目由低到高排序,欄目內部按價格由高到低排序

select goods_id,cat_id,goods_name,shop_price from ecs_goods

order by cat_id,shop_price desc;

4.4:取出價格最高的前三名商品

select goods_id,goods_name,shop_price from ecs_goods orderby shop_price desc limit 3;

4.5:取出點選量前三名到前5名的商品

select goods_id,goods_name,click_count from ecs_goods orderby click_count desc limit 2,3;

5 連線查詢

5.1:取出所有商品的商品名,欄目名,價格

select goods_name,cat_name,shop_price from

ecs_goods left join ecs_category

on ecs_goods.cat_id=ecs_category.cat_id;

5.2:取出第4個欄目下的商品的商品名,欄目名,價格

select goods_name,cat_name,shop_price from

ecs_goods left join ecs_category

on ecs_goods.cat_id=ecs_category.cat_id

where ecs_goods.cat_id = 4;

5.3:取出第4個欄目下的商品的商品名,欄目名,與品牌名

select goods_name,cat_name,brand_name from

ecs_goods left join ecs_category

on ecs_goods.cat_id=ecs_category.cat_id

left join ecs_brand

on ecs_goods.brand_id=ecs_brand.brand_id

where ecs_goods.cat_id = 4;

5.4: 用友面試題

根據給出的表結構按要求寫出SQL語句。

Match 賽程表

欄位名稱	欄位型別	描述
matchID	int	主鍵
hostTeamID	int	主隊的ID
guestTeamID	int	客隊的ID
matchResult	varchar(20)	比賽結果，如（2:0）
matchTime	date	比賽開始時間

Team 參賽隊伍表

欄位名稱	欄位型別	描述
teamID	int	主鍵
teamName	varchar(20)	隊伍名稱

Match的hostTeamID與guestTeamID都與Team中的teamID關聯

查出2006-6-1 到2006-7-1之間舉行的所有比賽，並且用以下形式列出：

拜仁 2：0 不來梅2006-6-21

mysql> select * from m;

+-----+------+------+------+------------+

| mid | hid |gid | mres | matime |

+-----+------+------+------+------------+

| 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 |

+-----+------+------+------+------------+

4 rows in set (0.00 sec)

mysql> select * from t;

+------+----------+

| tid | tname |

+------+----------+

| 1 | 國安 |

| 2 | 申花 |

| 3 | 傳智聯隊|

+------+----------+

3 rows in set (0.00 sec)

mysql> select hid,t1.tname as hname ,mres,gid,t2.tnameas gname,matime

-> from

-> m left joint as t1

-> on m.hid =t1.tid

-> left join tas t2

-> on m.gid =t2.tid;

+------+----------+------+------+----------+------------+

+------+----------+------+------+----------+------------+

| 1 | 國安 | 2:0 | 2 | 申花 | 2006-05-21 |

| 2 | 申花 | 1:2 | 3 | 傳智聯隊| 2006-06-21 |

| 3 | 傳智聯隊| 2:5 | 1 | 國安 | 2006-06-25 |

| 2 | 申花 | 3:2 | 1 | 國安 | 2006-07-21 |

+------+----------+------+------+----------+------------+

4 rows in set (0.00 sec)

6 union查詢

6.1:把ecs_comment,ecs_feedback兩個表中的資料,各取出4列,並把結果集union成一個結果集.

6.2:3期學員碰到的一道面試題

A表:

+------+------+

| id | num |

+------+------+

| a | 5 |

| b | 10 |

| c | 15 |

| d | 10 |

+------+------+

B表:

+------+------+

| id | num |

+------+------+

| b | 5 |

| c | 15 |

| d | 20 |

| e | 99 |

+------+------+

mysql> # 合併 ,注意all的作用

mysql> select * from ta

-> union all

-> select *from tb;

+------+------+

| id | num |

+------+------+

| a | 5 |

| b | 10 |

| c | 15 |

| d | 10 |

| b | 5 |

| c | 15 |

| d | 20 |

| e | 99 |

+------+------+

要求查詢出以下效果:

+------+----------+

| id | sum(num) |

+------+----------+

| a | 5 |

| b | 15 |

| c | 30 |

| d | 30 |

| e | 99 |

+------+----------+

參考答案:

mysql> # sum,group求和

mysql> select id,sum(num) from (select * from ta unionall select * from tb) as tmp group by id;

+------+----------+

| id | sum(num) |

+------+----------+

| a | 5 |

| b | 15 |

| c | 25 |

| d | 30 |

| e | 99 |

+------+----------+

5 rows in set (0.00 sec)

7: 子查詢:

7.1:查詢出最新一行商品(以商品編號最大為最新,用子查詢實現)

select goods_id,goods_name from

ecs_goods wheregoods_id =(select max(goods_id) from ecs_goods);

7.2:查詢出編號為19的商品的欄目名稱(用左連線查詢和子查詢分別)

7.3:用where型子查詢把ecs_goods表中的每個欄目下面最新的商品取出來

select goods_id,goods_name,cat_id from ecs_goods wheregoods_id in (select max(goods_id) from ecs_goods group by cat_id);

7.4:用from型子查詢把ecs_goods表中的每個欄目下面最新的商品取出來

select * from (select goods_id,cat_id,goods_name fromecs_goods order by goods_id desc) as t group by cat_id;

建立觸發器:

CREATE trigger tg2

after insert on ord

for each row

update goods set goods_number=goods_number-new.num whereid=new.gid

CREATE trigger tg3

after delete on ord

for each row

update goods set goods_number=good_number+old.num whereid=old.gid

CREATE trigger tg4

after update on ord

for each row

update goods set goods_number=goods_number+old.num-new.numwhere id=old.gid

2012-03-25更新,新增了面試案例

MySQL筆記複習（例項 全）

相關文章

MySQL筆記複習（例項全）