mysql之學習秘籍 [html] <pre> 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 創建表: create table tbName ( 列名稱1 列類型 [列參數] [not null default ], ....列2... .... 列名稱N 列類型 [列參數] [not null default ] )engine myisam/innodb charset utf8/gbk 3.4的例子: 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; 3.5 修改表 3.5.1 修改表之增加列: alter table tbName add 列名稱１ 列類型 [列參數] [not null default ] #(add之後的舊列名之後的語法和創建表時的列聲明一樣) 3.5.2 修改表之修改列 alter table tbName change 舊列名 新列名 列類型 [列參數] [not null default ] (注:舊列名之後的語法和創建表時的列聲明一樣) 3.5.3 修改表之減少列: alter table tbName drop 列名稱; 3.5.4 修改表之增加主鍵 alter table tbName add primary key(主鍵所在列名); 例:alter table 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 填充後的寬度 舉例: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 特性:不用賦值,該列會為自己賦當前的具體時間 5:增刪改查基本操作 5.1 插入數據 insert into 表名(col1,col2,……) values(val1,val2……); -- 插入指定列 insert into 表名 values (,,,,); -- 插入所有列 insert into 表名 values -- 一次插入多行 (val1,val2……), (val1,val2……), (val1,val2……); 5.3修改數據 update tablename set col1=newval1, col2=newval2, ... ... colN=newvalN where 條件; 5.4，刪除數據 delete from 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 join table 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 監視地點:表 監視行為:增 刪 改 觸發 觸發事件:增 刪 改 創建觸發器語法 create trigger tgName after/before insert/delete/update on tableName for each row sql; -- 觸發語句 刪除觸發器:drop trigger 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表插入以下數據: +----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+ | goods_id | goods_name | cat_id | brand_id | goods_sn | goods_number | shop_price | click_count | +----------+------------------------------+--------+----------+-----------+--------------+------------+-------------+ | 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 | 諾基亞5320 XpressMusic | 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 | 多普達Touch HD | 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 where goods_id=32; 1.2:不屬第3欄目的所有商品 select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id!=3; 1.3:本店價格高於3000元的商品 select goods_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_price between 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 and shop_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_count from 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_count from 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_name like '諾基亞N__'; 1.13:取出名字不以"諾基亞"開頭的商品 select goods_id,cat_id,goods_name,shop_price from ecs_goos where goods_name not 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 and click_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:查詢所有商品的平均價 select avg(shop_price) from ecs_goods; 2.7:查詢該店一共有多少種商品 select count(*) from ecs_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 from ecs_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 from ecs_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 | | 李四 | 1 | | 王五 | 1 | | 趙六 | 0 | | 趙六 | 0 | | 趙六 | 0 | +------+------------+ 9 rows in set (0.00 sec) mysql> #計算每個人的掛科科目 mysql> select name,sum(score < 60) from stu group by name; +------+-----------------+ | name | sum(score < 60) | +------+-----------------+ | 張三 | 2 | | 李四 | 2 | | 王五 | 1 | | 趙六 | 0 | +------+-----------------+ 4 rows in set (0.00 sec) #同時計算每人的平均分 mysql> select name,sum(score < 60),avg(score) as pj from 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 order by shop_price desc; 4.2:按發布時間由早到晚排序 select goods_id,goods_name,add_time from ecs_goods order by add_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 order by shop_price desc limit 3; 4.5:取出點擊量前三名到前5名的商品 select goods_id,goods_name,click_count from ecs_goods order by 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 賽程表 <table border="1"> <tr> <td>字段名稱</td><td>字段類型</td><td>描述</td> </tr> <tr><td>matchID</td><td>int</td><td>主鍵</td> </tr> <tr> <td>hostTeamID</td><td>int</td><td>主隊的ID</td> </tr> <tr> <td>guestTeamID</td><td>int</td><td>客隊的ID</td> </tr> <tr> <td>matchResult</td><td>varchar(20)</td><td>比賽結果，如（2:0）</td> </tr> <tr><td>matchTime</td><td>date</td><td>比賽開始時間</td> </tr> </table> Team 參賽隊伍表 <table border="1"> <tr> <td>字段名稱</td><td>字段類型</td><td>描述</td> </tr> <tr><td>teamID</td><td>int</td><td>主鍵</td> </tr> <tr> <td>teamName</td><td>varchar(20)</td><td>隊伍名稱</td> </tr> </table> 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.tname as gname,matime -> from -> m left join t as t1 -> on m.hid = t1.tid -> left join t as t2 -> on m.gid = t2.tid; +------+----------+------+------+----------+------------+ | hid | hname | mres | gid | gname | matime | +------+----------+------+------+----------+------------+ | 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 union all 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 where goods_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 where goods_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 from ecs_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 where id=new.gid CREATE trigger tg3 after delete on ord for each row update goods set goods_number=good_number+old.num where id=old.gid CREATE trigger tg4 after update on ord for each row update goods set goods_number=goods_number+old.num-new.num where id=old.gid </pre> <h3>2012-03-25更新,添加了面試案例</h3>
